SQL语句

创建表

create table mytable(
	# id int类型 不为空 自增
	id INT Not null AUTO_INCREMENT,
	# 默认值为1
	col1 INT NOT NULL default 1,
	# 变长字符串类型，最长为45个字符，可以为空
	col2 varchar(45) null,
	# 日期类型，可以为空
	col3 DATE null,
	# 设置主键
	primary key(id)
) ;

增删改查

# 修改表
# 添加列
alter table mytable
add col char(20); 
# 删除列
alter table mytable
drop column col;
# 删除表
drop table mytable;

# 插入数据
# 普通插入
insert into mytable(col1,col2)
value(val1,val2);
# 插入检索的数据
insert into mytable(col1,col2)
select col1,col2 from mytable2;
# 将一个表的内容插入到一个新表
create table newtable as
select * from mytable;

# 更新数据
update mytable
set col = val
where id = 1;

# 删除
delete from mytable 
where id = 1;

分组

要求：select后面只能有两种类型的列

• group by 中的列
• 使用聚合函数的列
  意思是select的列要么是group by中的列，要么是聚合函数的列，否则会报错。

分组的执行逻辑

如：select count(*) from tab1 where publi_id=‘1000000141’ and cent_id=‘3702000001’ group by comp_id

它是先根据where条件查询出所有的数据，然后按照group by 来对comp_id进行分组，分完组后count会对每组的每条数据进行汇总

# 创建表
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 |
+----+---------+---------------+-------+----------+

1. 查序每个用户订单数量（单字段分组）

mysql> select user_id,count(user_id)
    -> from t_order
    -> group by user_id;
+---------+----------------+
| user_id | count(user_id) |
+---------+----------------+
|    1001 |              3 |
|    1002 |              4 |
|    1003 |              2 |
+---------+----------------+

2. 查询每个用户每年的订单数量（多字段分组）

mysql> select user_id,the_year,count(user_id)
    -> from t_order
    -> group by user_id,the_year;
+---------+----------+----------------+
| user_id | the_year | count(user_id) |
+---------+----------+----------------+
|    1001 |     2017 |              1 |
|    1001 |     2018 |              2 |
|    1002 |     2018 |              3 |
|    1002 |     2019 |              1 |
|    1003 |     2018 |              1 |
|    1003 |     2019 |              1 |
+---------+----------+----------------+

# 或者
mysql> select user_id,the_year,count(id)
    -> from t_order
    -> group by user_id,the_year;
+---------+----------+-----------+
| user_id | the_year | count(id) |
+---------+----------+-----------+
|    1001 |     2017 |         1 |
|    1001 |     2018 |         2 |
|    1002 |     2018 |         3 |
|    1002 |     2019 |         1 |
|    1003 |     2018 |         1 |
|    1003 |     2019 |         1 |
+---------+----------+-----------+

3. 查询2018年用户的订单树（分组前筛选数据）

mysql> select user_id,count(id)
    -> from t_order
    -> where the_year = 2018
    -> group by user_id;
+---------+-----------+
| user_id | count(id) |
+---------+-----------+
|    1001 |         2 |
|    1002 |         3 |
|    1003 |         1 |
+---------+-----------+

4. 查询2018年订单大于1的用户（分组后筛选）

mysql> select user_id,count(id)
    -> from t_order
    -> where the_year = 2018
    -> group by user_id
    -> having count(id)>=2;
+---------+-----------+
| user_id | count(id) |
+---------+-----------+
|    1001 |         2 |
|    1002 |         3 |
+---------+-----------+

where是在分组前进行筛选，having是在分组后进行筛选。

5. 获得每个用户的最大金额，并倒序排序（分组后排序）

mysql> select user_id id,MAX(price) price
    -> from t_order
    -> group by user_id
    -> order by price desc;
+------+-------+
| id   | price |
+------+-------+
| 1001 | 88.88 |
| 1003 | 66.66 |
| 1002 | 44.44 |
+------+-------+
3 rows in set (0.02 sec)

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

mysql> select user_id id,count(id)
    -> from t_order
    -> where the_year = 2018
    -> group by user_id
    -> order by count(id) desc
    -> limit 1;
+------+-----------+
| id   | count(id) |
+------+-----------+
| 1002 |         3 |
+------+-----------+
1 row in set (0.00 sec)

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;

注意：
写法上面必须按照上面的顺序来写。

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

mysql> select
         user_id 用户id, max(price) 最大金额, the_year 年份
       FROM t_order t
       GROUP BY t.user_id;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'javacode2018.t.the_year' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

上面的sql报错了，原因因为the_year不符合上面说的2条规则（select后面的列必须出现在group by中或者使用聚合函数），而sql_mode限制了这种规则，我们看一下sql_mode的配置：

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

sql_mode中包含了ONLY_FULL_GROUP_BY，这个表示select后面的列必须符合上面的说的2点规范。

可以将ONLY_FULL_GROUP_BY去掉，select后面就可以加任意列了，我们来看一下效果。
修改mysql中的my.ini文件：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

重启mysql，再次运行，效果如下：

mysql> select
          user_id 用户id, max(price) 最大金额, the_year 年份
        FROM t_order t
        GROUP BY t.user_id;
+----------+--------------+--------+
| 用户id   | 最大金额     | 年份   |
+----------+--------------+--------+
|     1001 |        88.88 |   2017 |
|     1002 |        44.44 |   2018 |
|     1003 |        66.66 |   2018 |
+----------+--------------+--------+
3 rows in set (0.03 sec)
看一下上面的数据，第一条88.88的年份是2017年，我们再来看一下原始数据：

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)

对比一下，user_id=1001、price=88.88是第3条数据，即the_year是2018年，但是上面的分组结果是2017年，结果和我们预期的不一致，此时mysql对这种未按照规范来的列，乱序了，mysql取的是第一条。

正确方式

# 方法1
mysql> select user_id,price,the_year
    -> from t_order t,(select user_id id,MAX(price) pc from t_order group by user_id) t2
    -> where t.user_id = t2.id and t.price = t2.pc;
+---------+-------+----------+
| user_id | price | the_year |
+---------+-------+----------+
|    1001 | 88.88 |     2018 |
|    1002 | 44.44 |     2019 |
|    1003 | 66.66 |     2019 |
+---------+-------+----------+
# 方法2
mysql> select user_id,price,the_year
    -> from t_order
    -> where (user_id,price)
    -> IN
    -> (select user_id,MAX(price) from t_order group by user_id);
+---------+-------+----------+
| user_id | price | the_year |
+---------+-------+----------+
|    1001 | 88.88 |     2018 |
|    1002 | 44.44 |     2019 |
|    1003 | 66.66 |     2019 |
+---------+-------+----------+
3 rows in set (0.00 sec)

子查询

Mysql子查询的主要思路为拆分问题，把问题查分成小问题，然后再通过子查询的方式将小问题合并成大问题。
https://blog.csdn.net/qq_26594041/article/details/89438382

连接查询

关联查询的思路：1. 先确定所连接的表。 2. 在确定所要查询的字段。 3. 最后确定连接条件和连接方式。

连接用于查询多张表，使用join关键字，并且条件语句使用ON而不是where，连接可以替代子查询并且比子查询的效率一般要快
可以用AS给列名，表名，计算字段起别名以简化sql语句以及连接相同表。

内连接（inner join）

又称等值连接，使用inner join关键字

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。

SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;

自连接

特殊的内连接，即连接的两张表都是自己

自然连接（natural join）

自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。

内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。

SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;

外连接

保留了没有关联的那些行

左外连接（left join）

左外连接是保留了左表的没有关联的行

练习题：

右外连接（right join）

右外连接是保留了右表的没有关联的行

全外连接

左右没有关联的行都保留
注：Mysql不支持全外连接

组合查询

union all是重复会输出
union是重复只输出一次

主要的作用是将两个查询的数据记录合并，所以要求属性列个数一致，如果不一致，那么会报错。如果一致，如果属性列名不同，那么会按照第一个查询的属性列输出。

mysql> select answer from answer where id = 1
    -> union
    -> select answer,userName fome answer where id = 2;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'answer where id = 2' at line 5

mysql> select answer from answer where id = 1
    -> union
    -> select answer from answer where id = 2;
+--------+
| answer |
+--------+
| 123    |
+--------+

mysql> select answer from answer where id = 1
    -> union all
    -> select answer from answer where id = 2;
+--------+
| answer |
+--------+
| 123    |
| 123    |
+--------+

mysql> select answer from answer where id = 1
    -> union all
    -> select userName from answer where id = 2;
+--------+
| answer |
+--------+
| 123    |
| 789    |
+--------+

约束条件

limit

限制返回的行数，可以有两行参数，第一个为起始行，从0开始，第二个是返回的总行数。

例：返回前五行
select * from mytable
limit 5;
返回第3-5行
select * from mytable
limit 2，3；

排序

ASC：升序
DESC：降序

过滤

就是where 后接的

等于（=），不等于（!=或<>）,小于（<），大于（>），小于等于（<=或!>）,大于等于（>=或!<），两个值之间（BETWEEN），是否为空（is null）

AND和OR用于连接，优先AND
IN用于匹配一组值，其后也可以接一个select，从而匹配子查询的一组值。
NOT用于否定一个条件。

通配符

也是过滤，在where后，只能用于文本字段

• % 匹配>=0个任意字符
• _ 匹配1个任意字符
• [] 可以匹配集合内字符，例如[ab]，匹配a或b，用脱节字符^可以对其进行否定，就是不匹配集合内字符

使用like进行通配符匹配

select * from mytable
where col like ‘[^ab]%’;
不已a和b开头的任意文本

不要滥用通配符，通配符位于开头匹配会非常慢

函数

AVG()：返回某列的平均值
COUNT()：返回某列行数
MAX()：返回某列最大值
MIN()：返回某列最小值
SUM()：返回某列之和
AVG()会忽略NULL行
使用distinct可以汇总不同的值

select AVG(distinct coll) as avg_col
from mytable;

DISTINCT

相同的值只会出现一次，它作用于所有列，也就是说所有列的值都相同才算相同

select DISTINCT col1,col2
from mytable;