常用SQL语句速查
一、数据库操作
创建数据库
create database DB_NAME charset=utf-8;
使用数据库
use DB_NAME;
删除数据库
drop database DB_NAME;
查看创库sql语句
show create database DB_NAME;
创建表
create table TABLE_NAME;
修改表--添加字段
alter table TABLE_NAME add COLUMN_NAME TYPE CONSTRAINT;
修改表--修改 字段类型 和 约束
alter table TABLE_NAME modify COLUMN_NAME TYPE CONSTRAINT;
修改表--修改 字段名 、字段类型 和 约束
alter table TABLE_NAME change OLD_NAME NEW_NAME TYPE CONSTRAINT;
修改表--删除字段
alter table TABLE_NAME drop FIELD;
删除表
drop table TABLE_NAME;
查看创表sql语句
show create database TABLE_NAME;
二、表操作
查询数据
select * from 表名; 或者 select 列1,列2,... from 表名;
插入数据
insert into 表名 values (...) 或者 insert into 表名 (列1,...) values(值1,...);
修改数据
update 表名 set 列1=值1,列2=值2... where 条件;
删除数据
delete from 表名 where 条件;
三、关键字
条件查询 where
比较运算符
- 等于: =
- 大于: >
- 大于等于: >=
- 小于: <
- 小于等于: <=
- 不等于: != 或 <>
select * from students where id > 3;
select * from students where is_delete=0;
逻辑运算符
-- and 查询编号大于3的女同学
select * from students where id > 3 and gender=0;
-- or 查询编号小于4或没被删除的学生
select * from students where id < 4 or is_delete=0;
-- not 查询年龄不在10岁到15岁之间的学生
select * from students where not (age >= 10 and age <= 15);
模糊查询
-
like 是模糊查询关键字
-
% 表示任意多个任意字符
-
_ 表示一个任意字符
-- 查询姓黄的学生
select * from students where name like '⻩%';
-- 查询姓黄并且“名”是一个字的学生
select * from students where name like '⻩_';
-- 查询姓黄或叫靖的学生
select * from students where name like '⻩%' or name like '%靖';
范围查询
- between .. and .. 表示在一个连续的范围内查询
-- 查询编号为3至8的学生
select * from students where id between 3 and 8;
-- 查询编号不是3至8的男生
select * from students where (not id between 3 and 8) and gender='男';
-
in 表示在一个非连续的范围内查询
-
空判断
-
判断为空使用: is null
-
判断非空使用: is not null
-- 查询没有填写身高的学生 select * from students where height is null; -
注意:
-
不能使用 where height = null 判断为空
-
不能使用 where height != null 判断非空
-
null 不等于 '' 空字符串
别名
as 给表中字段 或 表名 起别名
select id as 序号, name as 名字, gender as 性别 from students;
去重
distinct 去除重复数据行
select distinct name, gender from students;
排序查询 order by
-
asc 升序
-
desc 降序
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
语法说明:
-
先按照列1进行排序，如果列1的值相同时，则按照 列2 排序，以此类推
-
asc从小到大排列，即升序
-
desc从大到小排序，即降序
-
默认按照列值从小到大排列（即asc关键字）
分页查询 limit
select * from 表名 limit start,count
说明:
-
limit是分页查询关键字
-
start表示开始行索引，默认是0
-
count表示查询条数
已知每页显示m条数据，求第n页显示的数据
提示: 关键是求每页的开始行索引
select * from students limit (n-1)*m,m
聚合函数
count
表示求指定列的总行数
-- 返回总⾏数，包含null值记录;
select count(*) from students;
max
表示求指定列的最大值
-- 查询⼥⽣的编号最⼤值
select max(id) from students where gender = 2;
min
表示求指定列的最小值
-- 查询未删除的学⽣最⼩编号
select min(id) from students where is_delete = 0;
sum
表示求指定列的和
-- 查询男⽣的总⾝⾼
select sum(height) from students where gender = 1;
-- 平均⾝⾼
select sum(height) / count(*) from students where gender = 1;
avg
表示求指定列的平均值
-- 求男⽣的平均⾝⾼, 聚合函数不统计null值，平均⾝⾼有误
select avg(height) from students where gender = 1;
-- 求男⽣的平均⾝⾼, 包含⾝⾼是null的
select avg(ifnull(height,0)) from students where gender = 1;
分组查询 group by
语法格式
GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]
-
列名: 是指按照指定字段的值进行分组。
-
HAVING 条件表达式: 用来过滤分组后的数据。
-
WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果
group by的使用
单个字段
-- 根据gender字段来分组
select gender from students group by gender;
多个字段
-- 根据name和gender字段进⾏分组
select name, gender from students group by name, gender;
group by + group_concat()**的使用
group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割
-- 根据gender字段进⾏分组， 查询gender字段和分组的name字段信息
select gender,group_concat(name) from students group by gender;
group by +** 聚合函数的使用
-- 统计不同性别的⼈的平均年龄
select gender,avg(age) from students group by gender;
-- 统计不同性别的⼈的个数
select gender,count(*) from students group by gender;
group by + having的使用
having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by
-- 根据gender字段进⾏分组，统计分组条数⼤于2的
select gender,count(*) from students group by gender having count(*)>2;
group by + with rollup的使用
with rollup的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果
-- 根据gender字段进⾏分组，汇总总⼈数
select gender,count(*) from students group by gender with rollup;
-- 根据gender字段进⾏分组，汇总所有⼈的年龄
select gender,group_concat(age) from students group by gender with rollup;
连接查询
内连接查询
查询语法格式
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2
示例：
-- 使用内连接查询学生表与班级表
select * from students as s inner join classes as c on s.cls_id = c.id;
左连接查询
查询语法格式
select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2
示例：
-- 使用左连接查询学生表与班级表
select * from students as s left join classes as c on s.cls_id = c.id;
右连接查询
查询语法格式
select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2
示例：
-- 使用右连接查询学生表与班级表
select * from students as s right join classes as c on s.cls_id = c.id;
自连接查询
查询语法格式
select 字段 from 表1 inner join 表1 on 表1.字段1 = 表1.字段2
示例：
-- 使用自连接查询
select c.id, c.title, c.pid, p.title from areas as c inner join areas as p on c.pid = p.id where p.title = '⼭西省';
子查询
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那
个select语句则称为主查询.
主查询和子查询的关系：
-
子查询是嵌入到主查询中
-
子查询是辅助主查询的,要么充当条件,要么充当数据源
-
子查询是可以独立存在的语句,是一条完整的 select 语句
示例:
查询大于平均年龄的学生
select * from students where age > (select avg(age) from students);
查询学生在班的所有班级名字
select name from classes where id in (select cls_id from students where cls_id is n ot null);
查找年龄最大,身高最高的学生
select * from students where (age, height) = (select max(age), max(height) from st udents);
