文章目录

sql基础知识1、sql基本掌握语句介绍1、mysql的语句书写顺序2、mysql的内部执行顺序 2、基本sql语句1、创建数据库3、备份sql server4、创建新表5、删除新表6、增加一个列7、添加主键8、创建索引9、创建视图10、修改数据库名字11、几个简单的基本sql语句 3、进阶sql语句1、几个高级查询运算词2、连接查询3、分组查询1、基本使用语法2、基本sql使用3、案例使用1、单字段分组2、多字段分组3、分组前筛选数据4、分组后筛选数据5、分组后排序6、where & group by & having & order by & limit 一起协作7、需要展示多列，字段又不属于分组中

sql基础知识

1、sql基本掌握语句介绍

1、mysql的语句书写顺序

select <要返回的数据列> from <表名> <join, left join, right join...> join <join表> on <join条件> where <where条件> group by <分组条件> having <分组后的筛选条件> order by <排序条件> limit <行数限制>

然而

2、mysql的内部执行顺序

from <表名> # 笛卡尔积 on <筛选条件> #对笛卡尔积的虚表进行筛选 <join, left join, right join...> join <join表> #指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中 where <where条件> #对上述虚表进行筛选 group by <分组条件> #分组 <sum()等聚合函数> #用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的 having <分组筛选> #对分组后的结果进行聚合筛选 select <返回数据列表> #返回的单列必须在group by子句中，聚合函数除外 distinct order by <排序条件> #排序 limit <行数限制>

部分解释：

from：select * from table_1, table_2; 与 select * from table_1 join table_2; 的结果一致，都是表示求笛卡尔积；

用于直接计算两个表笛卡尔积，得到虚拟表VT1，这是所有select语句最先执行的操作，其他操作时在这个表上进行的，也就是from操作所完成的内容

on: 从VT1表中筛选符合条件的数据，形成VT2表；join: 将该join类型的数据补充到VT2表中，例如left join会将左表的剩余数据添加到虚表VT2中，形成VT3表；若表的数量大于2，则会重复1-3步；where: 执行筛选，（不能使用聚合函数）得到VT4表；group by: 对VT4表进行分组，得到VT5表；其后处理的语句，如select，having，所用到的列必须包含在group by条件中，没有出现的需要用聚合函数；having: 筛选分组后的数据，得到VT6表；select: 返回列得到VT7表；distinct: 用于去重得到VT8表；order by: 用于排序得到VT9表；limit: 返回需要的行数，得到VT10；

2、基本sql语句

1、创建数据库

CREATE DATABASE database-name

####2、删除数据库

drop database database-name

3、备份sql server

--- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' --- 开始 备份 BACKUP DATABASE pubs TO testBack

4、创建新表

create table tabname(col1 type1 [not null] [primary key], col2 type2 [not null], ..) --根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only

5、删除新表

drop table tabname

6、增加一个列

Alter table tabname add column col type --注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

7、添加主键

Alter table tabname add primary key(col) --说明：删除主键： Alter table tabname drop primary key(col)

8、创建索引

create [unique] index idxname on tabname(col….) --删除索引：drop index idxname

9、创建视图

create view viewname as select statement --删除视图：drop view viewname

10、修改数据库名字

sp_renamedb 'old_name', 'new_name'

11、几个简单的基本sql语句

选择：select * from table1 where 范围条件 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料! 排序：select * from table1 order by field1,field2 [desc] --默认降序（大->小)，[asc]升序 总数：select count(1) as totalcount from table1 求和：select sum(field1) as sumvalue from table1 平均：select avg(field1) as avgvalue from table1 最大：select max(field1) as maxvalue from table1 最小：select min(field1) as minvalue from table1

3、进阶sql语句

1、几个高级查询运算词

A： UNION 运算符 UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 示例： -- A表和B表都有编号，名字两个字段，A表有10条数据，其中一条是（2，老段），B表有10条数据，其中一条是（2，老段），把两张表数据合起来的sql select * from A UNION select * from B --（会有两条（2，老段）数据） select * from A UNION ALL select * from B --（只有一条（2，老段）数据） -- 一般只作为全连接查询使用

---

B： EXCEPT 运算符 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。 C： INTERSECT 运算符 INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。

注：以上三个关键词使用运算词的几个查询结果行必须是一致的。

2、连接查询

A、left （outer） join： 左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。 SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT JOIN b ON a.a = b.c B：right （outer） join: 右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。 SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a RIGHT JOIN b ON a.a = b.c C：full/cross （outer） join （oracle）： 全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录,在mysql中可用UNION解决,不足的列补空 SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT JOIN b ON a.a = b.c UNION select a.a, a.b, a.c, b.c, b.d, b.f from a RIGHT JOIN b ON a.a = b.c D: inner join on 内连接查询：根据id条件查出来共有的部分 SQL: SELECT a.a, a.b, a.c, b.c, b.d, b.f FROM A INNER JOIN B ON a.c = b.c;

3、分组查询

1、基本使用语法

SELECT column, group_function,... FROM table [WHERE condition] GROUP BY group_by_expression [HAVING group_condition];

说明：

group_function：聚合函数。

group_by_expression：分组表达式，多个之间用逗号隔开。

group_condition：分组之后对数据进行过滤。

分组中，select后面只能有两种类型的列：

出现在group by后的列或者使用聚合函数的列

聚合函数

函数名称作用max查询指定列的最大值min查询指定列的最小值count统计查询结果的行数sum求和，返回指定列的总和avg求平均值，返回指定列数据的平均值

2、基本sql使用

select * from employee; +------+------+--------+------+------+-------------+ | num | d_id | name | age | sex | homeaddr | +------+------+--------+------+------+-------------+ | 1 | 1001 | 张三 | 26 | 男 | beijinghdq | | 2 | 1002 | 李四 | 24 | 女 | beijingcpq | | 3 | 1003 | 王五 | 25 | 男 | changshaylq | | 4 | 1004 | Aric | 15 | 男 | England | +------+------+--------+------+------+-------------+ select * from employee group by sex; +------+------+--------+------+------+------------+ | num | d_id | name | age | sex | homeaddr | +------+------+--------+------+------+------------+ | 2 | 1002 | 李四 | 24 | 女 | beijingcpq | | 1 | 1001 | 张三 | 26 | 男 | beijinghdq | +------+------+--------+------+------+------------+ 根据sex字段来分组，sex字段的全部值只有两个('男'和'女')，所以分为了两组 当group by单独使用时，只显示出每组的第一条记录 所以group by单独使用时的实际意义不大

group by + group_concat()

分组后，根据分组结果，使用**group_concat()**来放置每一组的某字段的值的集合

select sex from employee group by sex; +------+ | sex | +------+ | 女 | | 男 | +------+ select sex,group_concat(name) from employee group by sex; +------+--------------------+ | sex | group_concat(name) | +------+--------------------+ | 女 | 李四 | | 男 | 张三,王五,Aric | +------+--------------------+ select sex,group_concat(d_id) from employee group by sex; +------+--------------------+ | sex | group_concat(d_id) | +------+--------------------+ | 女 | 1002 | | 男 | 1001,1003,1004 | +------+--------------------+

group by + 集合函数

通过**group_concat()**的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个"值的集合"做一些操作

select sex,group_concat(age) from employee group by sex; +------+-------------------+ | sex | group_concat(age) | +------+-------------------+ | 女 | 24 | | 男 | 26,25,15 | +------+-------------------+ 分别统计性别为男/女的人年龄平均值 select sex,avg(age) from employee group by sex; +------+----------+ | sex | avg(age) | +------+----------+ | 女 | 24.0000 | | 男 | 22.0000 | +------+----------+ 分别统计性别为男/女的人的个数 select sex,count(sex) from employee group by sex; +------+------------+ | sex | count(sex) | +------+------------+ | 女 | 1 | | 男 | 3 | +------+------------+

group by + having

(1) having 条件表达式：用来分组查询后指定一些条件来输出查询结果 (2) having作用和where一样，但having只能用于group by

select sex,count(sex) from employee group by sex having count(sex)>2; +------+------------+ | sex | count(sex) | +------+------------+ | 男 | 3 | +------+------------+

group by + with rollup

with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和

select sex,count(age) from employee group by sex with rollup; +------+------------+ | sex | count(age) | +------+------------+ | 女 | 1 | | 男 | 3 | | NULL | 4 | +------+------------+ select sex,group_concat(age) from employee group by sex with rollup; +------+-------------------+ | sex | group_concat(age) | +------+-------------------+ | 女 | 24 | | 男 | 26,25,15 | | NULL | 24,26,25,15 | +------+-------------------+

3、案例使用

准备数据

drop table if exists t_order; -- 创建订单表 create table t_order( id int not null AUTO_INCREMENT COMMENT '订单id', user_id bigint not null comment '下单人id', user_name varchar(16) not null default '' comment '用户名', price decimal(10,2) not null default 0 comment '订单金额', the_year SMALLINT not null comment '订单创建年份', PRIMARY KEY (id) ) comment '订单表'; -- 插入数据 insert into t_order(user_id,user_name,price,the_year) values (1001,'路人甲Java',11.11,'2017'), (1001,'路人甲Java',22.22,'2018'), (1001,'路人甲Java',88.88,'2018'), (1002,'刘德华',33.33,'2018'), (1002,'刘德华',12.22,'2018'), (1002,'刘德华',16.66,'2018'), (1002,'刘德华',44.44,'2019'), (1003,'张学友',55.55,'2018'), (1003,'张学友',66.66,'2019'); mysql> select * from t_order; +----+---------+---------------+-------+----------+ | id | user_id | user_name | price | the_year | +----+---------+---------------+-------+----------+ | 1 | 1001 | 路人甲Java | 11.11 | 2017 | | 2 | 1001 | 路人甲Java | 22.22 | 2018 | | 3 | 1001 | 路人甲Java | 88.88 | 2018 | | 4 | 1002 | 刘德华 | 33.33 | 2018 | | 5 | 1002 | 刘德华 | 12.22 | 2018 | | 6 | 1002 | 刘德华 | 16.66 | 2018 | | 7 | 1002 | 刘德华 | 44.44 | 2019 | | 8 | 1003 | 张学友 | 55.55 | 2018 | | 9 | 1003 | 张学友 | 66.66 | 2019 | +----+---------+---------------+-------+----------+ 9 rows in set (0.00 sec)

1、单字段分组

**需求：**查询每个用户下单数量，输出：用户id、下单数量，如下：

mysql> SELECT user_id 用户id, COUNT(id) 下单数量 FROM t_order GROUP BY user_id; +----------+--------------+ | 用户id | 下单数量 | +----------+--------------+ | 1001 | 3 | | 1002 | 4 | | 1003 | 2 | +----------+--------------+ 3 rows in set (0.00 sec)

2、多字段分组

**需求：**查询每个用户每年下单数量，输出字段：用户id、年份、下单数量，如下：

mysql> SELECT user_id 用户id, the_year 年份, COUNT(id) 下单数量 FROM t_order GROUP BY user_id , the_year; +----------+--------+--------------+ | 用户id | 年份 | 下单数量 | +----------+--------+--------------+ | 1001 | 2017 | 1 | | 1001 | 2018 | 2 | | 1002 | 2018 | 3 | | 1002 | 2019 | 1 | | 1003 | 2018 | 1 | | 1003 | 2019 | 1 | +----------+--------+--------------+ 6 rows in set (0.00 sec)

3、分组前筛选数据

分组前对数据进行筛选，使用where关键字

**需求：**需要查询2018年每个用户下单数量，输出：用户id、下单数量，如下：

mysql> SELECT user_id 用户id, COUNT(id) 下单数量 FROM t_order t WHERE t.the_year = 2018 GROUP BY user_id; +----------+--------------+ | 用户id | 下单数量 | +----------+--------------+ | 1001 | 2 | | 1002 | 3 | | 1003 | 1 | +----------+--------------+ 3 rows in set (0.00 sec)

4、分组后筛选数据

分组后对数据筛选，使用having关键字

**需求：**查询2018年订单数量大于1的用户，输出：用户id，下单数量，如下：

方式1：

mysql> SELECT user_id 用户id, COUNT(id) 下单数量 FROM t_order t WHERE t.the_year = 2018 GROUP BY user_id HAVING count(id)>=2; +----------+--------------+ | 用户id | 下单数量 | +----------+--------------+ | 1001 | 2 | | 1002 | 3 | +----------+--------------+ 2 rows in set (0.00 sec)

方式2：

mysql> SELECT user_id 用户id, count(id) 下单数量 FROM t_order t WHERE t.the_year = 2018 GROUP BY user_id HAVING 下单数量>=2; +----------+--------------+ | 用户id | 下单数量 | +----------+--------------+ | 1001 | 2 | | 1002 | 3 | +----------+--------------+ 2 rows in set (0.00 sec)

where和having的区别

where是在分组（聚合）前对记录进行筛选，而having是在分组结束后的结果里筛选，最后返回整个sql的查询结果。

可以把having理解为两级查询，即含having的查询操作先获得不含having子句时的sql查询结果表，然后在这个结果表上使用having条件筛选出符合的记录，最后返回这些记录，因此，having后是可以跟聚合函数的，并且这个聚集函数不必与select后面的聚集函数相同。

5、分组后排序

需求：获取每个用户最大金额，然后按照最大金额倒序，输出：用户id，最大金额，如下：

mysql> SELECT user_id 用户id, max(price) 最大金额 FROM t_order t GROUP BY user_id ORDER BY 最大金额 desc; +----------+--------------+ | 用户id | 最大金额 | +----------+--------------+ | 1001 | 88.88 | | 1003 | 66.66 | | 1002 | 44.44 | +----------+--------------+ 3 rows in set (0.00 sec)

6、where & group by & having & order by & limit 一起协作

where、group by、having、order by、limit这些关键字一起使用时，先后顺序有明确的限制，语法如下：

select 列 from 表名 where [查询条件] group by [分组表达式] having [分组过滤条件] order by [排序条件] limit [offset,] count;

**需求：**查询出2018年，下单数量大于等于2的，按照下单数量降序排序，最后只输出第1条记录，显示：用户id，下单数量，如下：

mysql> SELECT user_id 用户id, COUNT(id) 下单数量 FROM t_order t WHERE t.the_year = 2018 GROUP BY user_id HAVING count(id)>=2 ORDER BY 下单数量 DESC LIMIT 1; +----------+--------------+ | 用户id | 下单数量 | +----------+--------------+ | 1002 | 3 | +----------+--------------+ 1 row in set (0.00 sec)

7、需要展示多列，字段又不属于分组中

需求：获取每个用户下单的最大金额及下单的年份，输出：用户id，最大金额，年份

两种写法：

mysql> SELECT user_id 用户id, price 最大金额, the_year 年份 FROM t_order t1 WHERE (t1.user_id , t1.price) IN (SELECT t.user_id, MAX(t.price) FROM t_order t GROUP BY t.user_id); +----------+--------------+--------+ | 用户id | 最大金额 | 年份 | +----------+--------------+--------+ | 1001 | 88.88 | 2018 | | 1002 | 44.44 | 2019 | | 1003 | 66.66 | 2019 | +----------+--------------+--------+ 3 rows in set (0.00 sec) mysql> SELECT user_id 用户id, price 最大金额, the_year 年份 FROM t_order t1,(SELECT t.user_id uid, MAX(t.price) pc FROM t_order t GROUP BY t.user_id) t2 WHERE t1.user_id = t2.uid AND t1.price = t2.pc; +----------+--------------+--------+ | 用户id | 最大金额 | 年份 | +----------+--------------+--------+ | 1001 | 88.88 | 2018 | | 1002 | 44.44 | 2019 | | 1003 | 66.66 | 2019 | +----------+--------------+--------+ 3 rows in set (0.00 sec) 上面第1种写法，比较少见，in中使用了多字段查询

建议：在写分组查询的时候，最好按照标准的规范来写，select后面出现的列必须在group by中或者必须使用聚合函数。