SQL语句大全（适用于新手查询）

注：sql不区分大小写，本人习惯看小写，所以以下基本为小写.

数据库的增删选查

1. 查看数据库：

 `show databases；`

2. 创建数据库：

create database 数据库名称；

3. 选择数据库：

 `use 数据库名称；`

4. 删除数据库：

 `drop database 数据库名称;``

数据表的增删改查

1. 创建数据表：

 create table 表名（字段名1 数据类型 约束条件；字段名2 数据类型 约束条件；...）；`

2. 查看当前数据库中所有表：

 show tables；

3. 查看表结构：

 desc 表名；

4. 删除数据表：

 drop table 表名；

5. 修改表名:`

 alter table 旧表名 rename 新表名;

6. 修改字段名:

 alter table 表名 change 旧字段名 新字段名 新字段数据类型;

7. 修改字段类型:`

 alter table 表名 modify 字段名 新字段类型;

8. 添加字段:`

 alter table 表名 add 字段名 数据类型;

9. 修改字段的排列位置:

 alter table 表名 modify 字段名 字段类型 after 字段名;

10. 删除字段:`

 alter table 表名 drop city;

需要注意的是：建表之前要先选择进入数据库：use 数据库名称;约束条件可不写

常用数据类型

1. 数值型：int（大整数型，默认长度11，即int（11））
   float(单精度浮点型，默认float（10，2）表示最多十个数字，其中有两位小数)
   decimal（十进制小数型，适合金额、价格等，默认decimal（10，0））
2. 字符串型：char（定长字符串，默认为1）
   varchar（变长字符串，必须指定长度）
   text（长文本字符串型，最大长度65535，不能指定长度）
3. 日期时间型：date（YYYY-MM-DD）
   datetime（YYYY-MM-DD HH:MM:SS）
   time（HH:MM:SS）
   timestamp（时间戳）

约束条件

1. 主键约束：primary key(非空不重复)
2. 非空约束：not null(不能为空)
3. 唯一约束：unique(不能重复)
4. 自增字段：auto_increment(自动增长)
5. 默认约束:default(默认值)
6. 外键约束:foreign key(与主键相对应)
7. 设置外键约束:foreign key(字段名) references 主表名(字段名);

数据操作语言DML

1. 指定字段名插入:

 insert into 表名(字段名1,字段名2,...)values(...),(...),...;

2. 不指定字段名插入:

 insert into 表名 values(...),(...),...;

3. 批量导入数据:

load data infile "文件路径.csv" into table 表名 fields terminated by ',' ignore 1 lines;

4. 更新数据:

 update 表名 set 字段名1=字段值1

5. 删除数据:

delete form 表名;truncate 表名;

注: delete和truncate的区别：
delete可以添加where子句删除表中部分数据，truncate只能删除表中全部数据
delete删除表中数据保留表结构，truncate直接把表删除(drop table)然后再创建一张新表(create table)，执行速度比delete快。

数据查询语言DQL

1. 全表查询:

 select * from 表名;

2. 别名设置:`

 select 字段名 as 列别名 from 原表名 as 表别名;(as可隐藏)

3. 查询不重复记录:`

 selsect distinct 字段名 from 表名;

4. 条件查询:`

 select * from 表名 where 查询条件;

5. 空值查询:`

 select * from 表名 where 空值字段 is null;非空值在is后面加not;

6. 模糊查询:

 select * from 表名 where 字段名 like 通配符;

常用通配符:百分号%(匹配多个字符);下划线_(匹配一个字符)

7. 查询结果排序:order by; asc升序,desc降序(默认升序);

select * from 表名 order by 字段1 排序方向,字段2 排序方向,...;

8. 限制查询:limit

select * from 表名 limit [偏移量,] 行数;

9. 分组查询:group by

select * from 表名[ where 查询条件] group by 分组字段1[,分组字段2,…];

10. 分组后筛选:having

select * from 表名[ where 查询条件] group by 分组字段1 having 筛选条件;

select语句执行顺序

 from → where → group by → having → select → order by → limit

select语句书写顺序

 select → from → where → group by → having → order by → limit

运算符优先级

()→算术运算符→比较运算符→逻辑运算符

常用聚合函数

1. 平均值:avg()
2. 计数:count()
3. 最大值:max()
4. 最小值:min()
5. 求和:sum()

多表查询

1. 内连接:inner join

select * from 表1 [ inner] join 表2 on 表1.key=表2.key;

2. 左连接:left join

  select * 表1 left join 表2 on 表1.key=表2.key;

3. 右连接:right join

  select * from 表1 right join 表2 on 表1.key=表2.key;

4. 联合查询:
   union(去重);union all(不去重)

select * from 表名 union select * from 表名;

子查询

1.子查询操作符:
in(在其中)
any(任何一个)
all(全部)
2.查询基本工资高于公司平均工资的员工信息

select * from emp where sal>(select avg(sal) from emp);

3,行子查询
查询与张三同部门同职位的员工

select empno,ename,job,deptno 
from emp
where (deptno,job)=(select deptno,job from emp where ename='张三') and ename<>'张三';

4,列子查询
查询普通员工的工资等级

select empno,ename,sal,grade
from emp left join salgrade on sal between losal and hisal
where empno not in (select distinct mgr from emp where mgr is not null);

其他常用函数

1. 字符串函数
   concat(str1,str2,…)#合并成长字符串
   instr(str,substr)#返回substr在str中第一次出现的位置
   left(str,len)#返回str的左端len个字符
   upper(str)#返回大写的str
   lower(str)#返回小写的str
   将每位员工的姓名首字母转换为大写

select concat(upper(left(ename,1)),lower(mid(ename,2))) from emp;

2. 数学函数
   round(n,d)#返回n的四舍五入值,保留d位小数
3. 时期时间函数
   date_format(date,format)#根据format字符串格式化date值
   select date_format(‘20-07-16 12:00:00’,’%Y-%m-%d’);
   2020-07-16
   curdate()#返回当前日期值
   curtime()#返回当前时间值
   now()#返回当前日期时间
   datediff(日期1,日期2)#返回结束日日期1和起始日日期2之间的天数
   unix_timestamp()#返回一个unix时间戳
4. 转换函数
   cast(expression AS data_type)#将expression转换成另外一个不同的数据类型
5. 分组合并函数
   group_concat()
6. 逻辑函数
   ifnull(字段名,value)#判断该字段是否为空值,如果为空值,返回第二个参数,不为空返回原来的值
   if()
   case when () then () else() end
7. 开窗函数

语法:开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [< 细分窗口>])

between frame_start and frame_end(滑动窗口)

current row 边界是当前行，一般和其他范围关键字一起使用
unbounded preceding 边界是分区中的第一行
unbounded following 边界是分区中的最后一行
expr preceding 边界是当前行减去expr的值
expr following 边界是当前行加上expr的值

select *,avg(sal) over(partition by deptno order by hiredate rows between 1 preceding and 1 following) 部门平均工资 from emp;

8. 序号函数
   row_number()#显示分区中不重复不间断的序号

dense_rank()#显示分区中重复不间断的序号

rank()#显示分区中重复间断的序号

主要应用于排名,如果比较的值都不相等,可以使用row_number(),如果存在比较的值相等,看需求使用dense_rank()和rank()

第一次写博客,整理了一些有关sql的内容,可能有些地方写错了或者手误打错了呜呜,欢迎各位大佬多多指正啦,如有问题和疑问的都可以私聊我哟!