SQL语句

最新推荐文章于 2023-12-18 00:24:02 发布

N_a_n

最新推荐文章于 2023-12-18 00:24:02 发布

阅读量444

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/N_a_n/article/details/105309738

版权

创建表

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 |
+----+---------+---------------+-------+----------+

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


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 |
+---------+----------------+

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

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 |
+---------+----------+-----------+

查询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 |
+---------+-----------+

查询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是在分组后进行筛选。

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

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)

查询出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;

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

获取每个用户下单的最大金额及下单的年份，输出：用户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;

N_a_n

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL语句

文章目录创建表增删改查分组分组的执行逻辑子查询连接查询内连接（inner join）自连接自然连接（natural join）外连接左外连接（left join）右外连接（right join）全外连接组合查询约束条件limit排序过滤通配符函数DISTINCT创建表create table mytable( # id int类型不为空自增 id INT Not null AUTO_I...
复制链接

扫一扫