SQL语句大全

一、DDL 数据库、表增删查改

DDL操作数据库

创建数据库 create database 数据库名 character set 字符编码; 查看数据库: 查看所有数据库: show databases; 查看数据库结构: show create database 数据库名; 删除数据库 drop database 数据库名; 修改数据库 alter database 数据库名 character set 字符编码;

注意:1.数据库名不能修改 2.只能修改数据库的编码,是utf8,不是utf-8 其他操作: 切换数据库: use 数据库名; 查看正在使用的数据库: select database();

DDL操作表

创建表

create table 表名( 字段名 字段类型 [约束], 字段名 字段类型 [约束], ........ 字段名 字段类型 [约束] );

类型

1. 整型 一般使用int 或者bigint

2. 浮点/双精度型

   • 默认的范围 float或者double

   • 指定范围 float(M,D) eg: float(4,2) 表达的范围: -99.99~99.99

3. 字符串

   • 固定长度 char(n) eg: char(20), 最大能存放20个字符. ‘aaa ’, 还是占20个字符的空间

   • 可变长度 varchar(n) eg:varchar(20), 最大能存放20个字符. ‘aaa’, 占3个字符的空间

   一般使用varchar(n) 节省空间; 如果长度(eg:身份证)是固定的话 可以使用char(n) 性能高一点

4. 关于大文件

   • 一般在数据库里面很少存文件的内容, 一般存文件的路径

   • 一般不使用二进制存, 使用varchar(n)存文件的路径

5. 日期

   • DATE 只有日期

   • DATETIME 日期和时间

约束

• 即规则,规矩 限制;

• 作用：保证用户插入的数据保存到数据库中是符合规范的

约束	约束关键字
主键	primary key
唯一	unique
非空	not null

约束种类:

• not null: 非空 ; eg: username varchar(40) not null username这个字段不能为空,必须要有数据

• unique:唯一约束, 后面的数据不能和前面重复; eg: cardNo varchar(18) unique; cardNo字段不能出现重复的数据

• primary key；主键约束(非空+唯一); 一般用在表的id列上面. 每一张表基本上都有id列的, id列作为记录的唯一标识的

• auto_increment: ==自动增长,必须是设置了primary key之后,才可以使用auto_increment==

• id int primary key auto_increment id不需要我们自己维护了, 插入数据的时候直接插入null, 自动的增长进行填充进去, 避免重复了.

注意:

1. 先设置了primary key 再能设置auto_increment

2. 只有当设置了auto_increment 才可以插入null 自己维护 否则插入null,会报错

3. 一般开发中id主键会设置为自动增长

查看表

查看所有的表: show tables;
查看表的定义结构: desc  表名；

修改表

• 增加一列: alter table 表名 add 字段名 字段类型 字段约束;

• 修改列的类型约束:alter table 表名 modify 字段名 字段类型 字段约束;

• 修改列的名称，类型，约束:alter table 表名 change 旧字段名 新列名 字段类型 字段约束;

• 删除一列: alter table 表名 drop 字段名;

• 修改表名: rename table 旧表名 to 新表名;

删除表

drop table 表名

二、DML 记录增删改

插入指定列: insert into 表名(列名,列名,...) values(值,值,...); 插入所有列: insert into 表名 values(值,值,...);

更新记录 update 表名 set 字段名=值,字段名=值,... where 条件;

删除记录

• 方式一: delete from 表名 where 条件;

• 方式二: `truncate table 表名;

三、DQL 记录查询

查询所有的列: select * from 表名;

查询某张表特定列: select 字段名,字段名,... from 表名;

去重查询: select distinct 字段名 from 表名;

别名查询: select 字段名 as 别名,字段名 as 别名,... from 表名 as 别名; -- as是可以省略的

列运算查询(+,-,*,/等): select 列运算 from 表名;

基本条件查询: select ... from 表名 where 条件;

比较运算符: > >= < <= = <>

between...and... 范围

in(值,值,...) 范围

like 模糊 _ : 匹配一个字符

% : 匹配0个到多个字符(大于等于0个)

逻辑运算符 and or not

排序查询

方式一: select ... from 表名 order by 字段名 [asc|desc]; 方式二: select ... from 表名 order by 字段名 [asc|desc],字段名 [asc|desc];

聚合函数

聚合函数	作用
max(列名)	求这一列的最大值
min(列名)	求这一列的最小值
avg(列名)	求这一列的平均值
count(列名)	统计这一列有多少条记录
sum(列名)	对这一列求总和

SELECT 聚合函数(列名) FROM 表名;

分组查询

select ... from 表名 [where 条件] [group by 分组字段] [having 条件]

例子

-- 1. 练习:根据性别分组,统计男生的总人数和女生的总人数
-- 单独分组 没有意义,因为 返回每一组的第一条记录
select * from student group by sex;
-- 分组的目的一般为了做统计使用, 所以经常和聚合函数一起使用
select count(*) from student group by sex;
-- 分组查询如果不查询出分组字段的值,就无法得知结果属于那组
select sex,count(*) from student group by sex;
​
-- 2. 练习根据性别分组, 统计每一组学生的总人数 > 5的(分组后筛选)
select sex,count(*) from student group by sex having count(*)>5;

1. 注意事项

   单独分组 没有意义,因为 返回每一组的第一条记录

   分组的目的一般为了做统计使用, 所以经常和聚合函数一起使用

   分组查询如果不查询出分组字段的值,就无法得知结果属于那组

2. where和having的区别

子名	作用
where 子句	1) 对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，即先过滤再分组。2) where后面不可以使用聚合函数
having字句	1) having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。2) having后面可以使用聚合函数

分页查询

select ... from 表名 limit a,b; a:从哪里开始查询, 从0开始计数 ,省略a不写,默认就是从0开始 b:查询的数量【固定的,自定义的】 分页查询规律: limit (页码-1)*每页显示的记录数,每页显示的记录数

四、DCL

待完善

五、常见函数

1、if相关函数 if、ifnull

• 1.if(expr1,expr2,expr3) 如果 expr1 是TRUE，则 IF()的返回值为expr2; 否则返回值则为 expr3。if() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

  • 需求: 查询姓名,年龄,性别,如果性别为1,就显示1,否则就显示0

    select uname,age,if(sex=1,1,0) from t_user;
    select uname,age,if(sex,1,0) from t_user;

• 2.ifnull(expr1,expr2) 假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。ifnull()的返回值是数字或是字符串，具体情况取决于其所使用的语境。

  • 需求: 查询姓名,年龄,性别,如果性别为null,就显示2

    select uname,age,ifnull(sex,2) from t_user;

2、字符串函数

• 函数:

  1.concat(str1, str2, ...)   字符串连接函数，可以将多个字符串进行连接
  2.concat_ws(separator, str1, str2, ...) 可以指定间隔符将多个字符串进行连接；
  3.upper(str) 得到str的大写形式
  4.lower(str)  得到str的小写形式
  5.trim(str)  将str两边的空白符移除
  6.substr(str,pos)、substring(str,pos) ; substr(str,pos,len)、substring(str,pos,len) 截取字符串

  -- - 需求:使用concat查询t_user表中的uname,显示格式为: 你好,uname
  select concat("你好,",uname) from t_user;
  ​
  -- - 需求:使用concat_ws查询t_user表中的uname,显示格式为: 你好,uname
  select concat_ws(",","你好",uname) from t_user;
  ​
  -- - 需求:使用upper查询查询t_user表中的uname
  select upper(uname) from t_user;
  ​
  -- - 需求:使用lower查询查询t_user表中的uname
  select lower(uname) from t_user;
  ​
  -- - 需求:使用trim查询查询t_user表中的uname
  select trim(uname) from t_user;
  ​
  -- - 需求:获取helloworld从第二个字符开始的完整子串
  select substr("helloworld",2);
  select substring("helloworld",2);
  ​
  -- - 需求:获取helloworld从第二个字符开始,长度为4的子串
  select substr("helloworld",2,4);
  select substring("helloworld",2,4);
  ​

3、时间日期函数

• 函数

  current_date()  获取当前日期，如 2019-10-18
  current_time()  获取当前时：分：秒，如：15:36:11
  now()  获取当前的日期和时间，如：2019-10-18 15:37:17

  -- 1.获取当前的日期
  select current_date;
  ​
  -- 2.获取当前的时间
  select current_time;
  ​
  -- 3.获取当前的日期和时间
  select now();

4、数值函数

• 函数:

  abs(x) 获取数值x的绝对值
  ceil(x)  向上取整，获取不小于x的整数值
  floor(x) 向下取整，获取不大于x的整数值
  pow(x, y) 获取x的y次幂
  rand()  获取一个0-1之间的随机浮点数  

  -- - 需求: 获取-11的绝对值
  select abs(-11);
  ​
  -- - 需求: 获取大于3.14的最小整数
  select ceil(3.14);
  ​
  -- - 需求: 获取小于3.14的最大整数
  select floor(3.14);
  ​
  -- - 需求: 获取2的5次幂
  select pow(2,5);
  ​
  -- - 需求: 获取一个0-100之间的随机数
  select rand()*100;