Sql知识点

#Sql操作

数据库操作（增，删，查）

show databases;                          //查看所有数据库
create database 数据库名称;               //增加数据库
drop database 数据库名称;                 //删除数据库
use 数据库名称                            //无分号, 选择要操作的数据库

数据库表操作（增、删、查）

在要操作某个数据库中的表时，一定要先使用数据库

use 数据库名称  //无分号

表操作

show tables；                                              //查看所有的表
select * from 表名;                                        //查看表中的数据
create table 表名（字段名称 字段类型）;                       //添加一张数据库中的表
create table 表名（字段名称 字段类型，字段名称 字段类型，...）;  //添加一张数据库中的表

举例：

create table student（
       id int,
       name varchar(4),//varchar类型不能为空
       sex varchar（2）,
       age int,
       schooldate date,//date是时间类型
）

删除一张表

drop table 表名;

数据库表结构操作（增、删、改、查）

查看表结构

desc 表名;

对表的结构进行修改

alter table 表名 add 字段名 字段类型;                   //增加字段
alter table 表名 add （字段名 字段类型,字段名 字段类型）; //增加多个字段
alter table 表名 drop 字段名;                         //删除字段
alter table 表名 modify 字段名 要修改之后的字段类型      //修改字段类型

举例：

create table book（
    id int,
    name varchar(10),
    pressdate date,
    price double(5,2)//5位数，小数点后占两位，整数占三位
）

数据库表的数据操作（增、删、改）

insert into 表名(字段名称，字段名称) values(对应的字段值，对应的字段值);//增加表中数据
insert into 表名 values(表中所有对应字段名称的字段值);
delete from 表名;                                                 //清空表中数据
detete from 表名 where 判断条件;                                   //删除符合条件的数据
update 表名 set 字段名=新的字段值;                                  //修改表中对应字段名的所有的值
update 表名 set 字段名=新的字段值 where 判断条件;                    //修改表中满足判断条件的对应字段名的值
update 表名 set 字段名=新的字段值，字段名=新的字段值 where 判断条件;

Sql约束

约束在创建表时建立
唯一约束
unique

create table stu(number int unique ,name varchar(10));//number不可重复

可以创建两个标号为空的数据，因为任何一个null都不等于另一个null

insert into stu(number, name) values(null,'tom');
insert into stu(number, name) values(null,'jim'); 

非空约束
not null

reate table stu(number int not null ,name varchar(10));//number不可为空

同一个字段加多个约束
不需要逗号

create table stu(num int unique not null,name varchar(10));

主键约束
非空约束和唯一约束的组合称为主键约束
primary key

create table stu(num int primary key,name varchar);

一般数字类型主键配合主键自动增长策略
插入数据时只需要插入除主键之外的数据就行
auto_increment

create table stu(num int primary key auto_increment,name varchar(10));

外键约束
外键必须是另一张表中的主键
**foreign key(要设为外键的字段名) refernces 另一张表名（另一张表中设为主键的值）
**

create table class(num int primary key auto_increment,name varchar(10));
create table student(num int primary key auto_increment,name varchar(10),classnum int,foreign key(classnum) references class(num));

sql查询

Sql基本查询操作

查询语句的格式

select [字段列表，表达式，函数] from 表名

改掉查询之后的表名

select [字段列表，表达式，函数] '要改的名字' from 表名
select ename '姓名',sal '工资' from emp;

select 字段列表[字段1，字段2，字段n] from 表名

select ename,sal from emp;

select 表达式[算数表达式] from 表名

select ename,sal*12 from emp;

数据库去重,distinct关键字

select distinct dept from emp;

Sql条件查询

单条件查询

where sal = 1600;
where sal = '1978/03/15';
where sal [>,<,<>] 1600;//工资大于，小于，不等于1600

多条件查询
1.并且 and

select * from emp where sal > 1600 and ename = 'zzz';

2.或者 or

select * from emp where sal = 1600 or sal = 800 or sal = 950;//查找工资等于1600,800,950的记录
select * from emp where sal in(1600,800,950);//等同上条
select * from emp where sal not in(1600,800,950)//查找工资不等于1600,800,950,的记录

3.在哪个范围内between and

select * from emp between 1600 and 3000;//查找工资水平在1600到3000之间的记录 

4.空永远不等于空
null和null做判断，永远为false

//查找没有福利的记录
select * from emp where comm = null;//这是错误的写法
select * from emp where comm is null;//这是正确的写法
//查找有福利的记录
select * from emp where comm is not null;

sql函数

1.数学函数

pi()           //返回pi的值（圆周率）
floor(x)       //去掉小数部分取整,sal为23.56
   select floor(sal) from emp where ename = 'Tom';//查询值为23
ceiling(x)     //进一取整
   select ceiling(sal) from emp where ename = 'Tom';//查询值为24
round(x，y)    //以小数点后y位四舍五入，y为负，则倒数y位变为0，倒数y+1位四舍五入
truncate(x,y)  //保留小数点后y位，舍去y位往后的数值，y为负，则倒数y位变为0

2.聚合函数（分组函数）

avg(col)       //返回指定列的平均值
count(col)     //返回指定列中非null 值/列的个数（函数参数为*时不忽略）
min(col)       //返回列中的最小值
max(col)       //返回列中的最大值
sum(col)       //返回列中的所有值之和

2.字符串函数

concat(s1,s2..sn)   //将s1，s2..sn连接成字符串
     select concat(ename,'的工资是：',sal) from emp;
ltrim(str)          //去掉字符串开头空格
rtrim(str)           //去掉字符串末尾空格
trim(str)           //去掉字符串首尾空格
length(str)         //查询字符串的长度
substring(x,y)      //截取字符串，从x开始的y个字符

3.日期和时间函数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tPqERUkP-1577413226857)(https://i.imgur.com/UmFHMVp.png)]
4.条件判断函数
5.系统信息函数
6.加密函数
7.格式化函数

sql分组函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cS0WJE5Y-1577413226859)(https://i.imgur.com/XFsjoBc.png)]
别名
可以给字段，表达式，函数，表起别名

//查看每个班级成绩的平均值1.给每个成绩分组2.计算每个组的平均值
select avg(achievement) '成绩',classnum from student group by classnum;

不在分组函数中的字段 必须在group by的后面
当要在已命名分组函数中写判断条件时，不用where 用 having。

//查看平均值大于80的 班级成绩的平均值1.给每个成绩分组2.计算每个组的平均值3.计算平均值大于80的
select avg(achievement) '成绩',classnum from student group by classnum having 成绩 > 80;

sql子查询

就是一个查询的结果可以作为另一个查询的数据源或者条件

1.将查询结果作为另一个查询的条件
查看成绩最高的人的名字
将成绩最高作为筛选条件（一层组函数）

select name,achievement from student where achievement = (select max(achievement) from student);

2.将查询结果作为另一个查询的数据源
查询结果做表时，必须起别名
emp表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sUbWwry1-1577413226860)(https://i.imgur.com/PvVq8kH.png)]
查询平均工资最大的部门是哪个（两层组函数）
组函数不能嵌套

select max(avg(sal)) deptno from emp group by deptno;//这是错误的

(1).查询每个部门的平均工资,(2).查看最大平均工资的部门

//查询最大的平均工资
select max(平均工资) from (select avg(sal) '平均工资',deptno from emp group by deptno) avg_table;
//查询最大的平均工资的部门
select 平均工资 from (select avg(sal) '平均工资',deptno from emp group by deptno) avg_table 
where 平均工资 = (select max(平均工资) from (select avg(sal) '平均工资',deptno from emp group by deptno) avg_table);

sql多表查询

连接	代码
左连接	left join
右连接	right join
内连接	inner join
全连接	full join

a表

id	name
1	张3
2	李四
3	王武

b表

id	name	parent_id
1	23	1
2	34	2
3	34	4

左连接

select a.*,b.* from a left join b on a.id=b.parent_id
结果
张3   1     23    1
李四  2     34    2
王武  null  null  null

右连接

select a.*,b.* from a right join b on a.id=b.parent_id
结果
张3   1  23  1
李四  2  34  2
null  3  34  4

内连接

select a.*,b.* from a inner join b on a.id=b.parent_id
结果
张3  1  23  1
李四 2  34  2

全连接

select a.*,b.* from a full join b on a.id=b.parent_id
结果
张3     1     23    1
李四    2     34    2
null    3     34    4
王武    null  null  null

dept表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5amdeQef-1577413226861)(https://i.imgur.com/B4IpBlP.png)]
1.查询员工的姓名和员工所在部门的名称

select ename,dname from emp,dept where emp.deptno = dept.deptno;
selece ename,dname from emp join dept on emp.deptno = dept.deptno;

salgrade表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IcKrRs0y-1577413226863)(https://i.imgur.com/OQ2fK9u.png)]
2.查询员工的姓名和员工所在部门的名称及工资等级

select ename,dname,grade from emp join dept join salgrade on
emp.deptno = dept.deptno and emp.sal beween losal and hisal;

3.查询员工的姓名和员工经理人的姓名
做单表的多表查询

select e.ename,m.ename from emp e join emp m on e.empto = m.mar;

左外联合：
join前加left：左边表中如果存在记录没有匹配到还想显示出来
右外联合：
join前加right：右边表中如果存在记录没有匹配到还想显示出来

作业：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-a3sZKnhl-1577413226864)(https://i.imgur.com/zGn0GFx.png)]

Sqlserve操作

sql读取系统日期和时间的方法如下：
–获取当前日期(如：yyyymmdd)

select CONVERT (nvarchar(12),GETDATE(),112)

–获取当前日期(如：yyyymmdd hh:MM:ss)
select GETDATE()

–获取当前日期(如：yyyy-mm-dd)
Select Datename(year,GetDate())+’-’+Datename(month,GetDate())+’-’+Datename(day,GetDate())

–获取当前日期(如：yyyy/mm/dd)
select DATENAME(YEAR,GETDATE())+’/’+DATENAME(MONTH,GETDATE())+’/’+DATENAME(DAY,GETDATE())

–获取几种日期
select DATENAME(YEAR,GETDATE()) --年份(YYYY)
select DATENAME(YY,GETDATE())

select DATENAME(MM,GETDATE()) --月份
select DATENAME(DD,GETDATE()) --日期
select dateName(hh,getdate()) --获取小时
select DATENAME(MI,GETDATE()) --获取分钟
select DATENAME(SECOND,GETDATE()) --获取秒
select DATENAME(WEEK,GETDATE()) --获取当前星期(周)是这一年中的第几个星期(周)
select DATENAME(WEEKDAY,GETDATE()) --星期几

*select convert(char(8),getdate(),108) as yourtime–获取当前时间
SELECT convert(char(10),getdate(),120) as yourdate–获取当前日期
SELECT left(convert(char(10),getdate(),120),4) as youryear–获取当前年份