mysql语句笔记

offer快到碗里来gopher未上岸

已于 2022-12-03 12:02:52 修改

阅读量216

点赞数

文章标签： mysql 数据库

于 2022-11-20 15:02:01 首次发布

本文链接：https://blog.csdn.net/m0_65314003/article/details/127947424

版权

DQL

1. NULL

参与运算/在IN或NOT IN语句里：结果都为null
条件查询（不参与）：无法比较，所以一般结果不会返回。如果需要返回，可以加上：IS NULL/ IS NOT NULL
条件查询（参与）：
1. 分组函数中的count(*)/count(1)/count(0)；
2. 安全等于形式取反 <=>；
3. 外连接时的主表；
条件查询并且含null

寻找用户推荐人
写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都不是 2。
```
select name
from customer
where referee_id <> 2 or referee_id is null
#或者
SELECT name
FROM customer
WHERE NOT referee_id <=> 2;
```

2. 单行处理函数

这些为数据处理函数，在select语句中执行条件判断对筛选出来的值再判断筛选。为单输入单输出。

控制语句

IF(条件, true:…, false:…)/CASE…WHEN…THEN…ELSE…END;
str_to_date/date_format
ifnull(x,value)

IF/CASE使用

1.计算特殊奖金
写出一个SQL 查询语句，计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以’M’开头，那么他的奖金是他工资的100%，否则奖金为0。
2.变更性别
请你编写一个 SQL 查询来交换所有的 ‘f’ 和 ‘m’ （即，将所有 ‘f’ 变为 ‘m’ ，反之亦然），仅使用单个 update 语句，且不产生中间临时表。
```
SELECT
    employee_id, 
CASE WHEN 
    MOD(employee_id, 2) = 1 AND LEFT(name,1)!='M' THEN salary ELSE 0 END AS bonus 
FROM
	Employees 
ORDER BY 
    employee_id;
#或者
select 
	employee_id, if (employee_id%2 != 0 and name not like "M%",salary,0 ) as bonus 
from 
	Employees 
order by 
	employee_id
```

运算

四舍五入 ROUND(a)
绝对值 ABS(a)
MOD(x,y)
CEILING/FLOOR(a)
SIGN(a)
RAND()
SQRT(a)
POW(x,y)

字符串

拼接 CONCAT(s1,s2)
截取 SUBSTR(s,start,length)
从左/右取 LEFT/RIGHT(str, length)
大小写 UPPER,LOWER
长度 LENGTH(str)
TRIM(s) 去除空格
REVERSE(str)
INSERT(str1,start,length,str2) 用str2替换str1的部分，若str2长度超过则返回原值。
REPLACE(str1,a,b)，用字符b替换str1里的所有字符a
STR_TO_DATE(str,‘%d,%m,%Y’)字符串转日期类型

日期

获取日期

系统日期/时间/日期+时间：CURDATE()/CURTIME()/NOW()
年/月/日/：YEAR(date),MONTH(date),
指定格式输出：DATE_FORMAT(date,%format)
- 例如：DATE_FORMAT(‘2017-11-15 21:45:00’,‘%h:i% %p %M %D %Y’)➡️
- 09:i PM November 15th 2017

计算日期

DATEDIFF(date1,date2)：日期相减（年-月-日）前-后=天数
TIMESTAMPDIFF(UNIT, date1,date2):UNIT为day时，计算相差多少天
DATE_ADD/DATE_SUB(date, INTERVAL 1 year/month/day/hour/minute/second)
TO_DAYS(date) 从公元0年到date的天数

3. 多行处理函数

常见的5个：count/sum/avg/max/min
额外补充的几个：

GROUP_CONCAT(str)：可以将合并里的各个字符串合并起来展示。除了加str里面还可以加上其他的字段，比如用","进行分割：GROUP_CONCAT(DISTINCT product ORDER BY product ASC SEPARATOR ‘,’)。

通常用在有group by出现时，对group by好的数据，在里面筛选出值。
这里参考mysql中group by用法是什么

SELECT name FROM test GROUP BY name

请添加图片描述
先执行group by 把相同的合并，合并完后若没有多行处理函数，则默认返回相同数据的排序第一的值。加上多行处理函数（聚合函数）可以取到其他值。

对于group by后面有多个值，会把多个值看成一个整体来进行合并。

SELECT name FROM test GROUP BY name,number

请添加图片描述

1.查询近30天活跃用户数
请写SQL查询出截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。

select 
    activity_date as day, COUNT(DISTINCT user_id) AS active_users
from 
    Activity 
where 
    activity_date <= '2019-07-27' and activity_date > '2019-06-27'
group by
    activity_date

2.按日期分组销售产品
编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。

SELECT
    sell_date, count(distinct(product)) AS num_sold, GROUP_CONCAT(DISTINCT product ORDER BY product ASC SEPARATOR ',') AS products
FROM 
    Activities
GROUP BY
    sell_date
ORDER BY
    sell_date

4. 联合查询

两张表连接时产生笛卡尔积即查询结果条数为两张表条数的乘积。例如顾客与订单表：

mysql> select * from customers;
+----+-------+
| id | name  |
+----+-------+
|  1 | Joe   |
|  2 | Henry |
|  3 | Sam   |
|  4 | Max   |
+----+-------+
4 rows in set (0.00 sec)

mysql> select * from orders;
+----+------------+
| id | customerId |
+----+------------+
|  1 |          3 |
|  2 |          1 |
+----+------------+
2 rows in set (0.00 sec)

mysql> select * from customers,orders;
+----+-------+----+------------+
| id | name  | id | customerId |
+----+-------+----+------------+
|  1 | Joe   |  2 |          1 |
|  1 | Joe   |  1 |          3 |
|  2 | Henry |  2 |          1 |
|  2 | Henry |  1 |          3 |
|  3 | Sam   |  2 |          1 |
|  3 | Sam   |  1 |          3 |
|  4 | Max   |  2 |          1 |
|  4 | Max   |  1 |          3 |
+----+-------+----+------------+
8 rows in set (0.00 sec)

1. 交集部分

直接加where，即先查出笛卡尔积的数据，再在里面进行条件查询返回。

mysql> select * from customers c,orders o where c.id=o.id;
+----+-------+----+------------+
| id | name  | id | customerId |
+----+-------+----+------------+
|  1 | Joe   |  1 |          3 |
|  2 | Henry |  2 |          1 |
+----+-------+----+------------+

2. 交集+左半边表的部分

用到联合查询left join，交集部分+左表。取完交集的部分后，把左表剩余的没有返回的数据带着返回，多出来的值全为null。

mysql> select * from customers c left join orders o on c.id=o.id;
+----+-------+------+------------+
| id | name  | id   | customerId |
+----+-------+------+------------+
|  1 | Joe   |    1 |          3 |
|  2 | Henry |    2 |          1 |
|  3 | Sam   | NULL |       NULL |
|  4 | Max   | NULL |       NULL |
+----+-------+------+------------+
4 rows in set (0.00 sec)

3.左半边表的部分

即只取出交集后补齐的那部分数据，此时可以利用补齐后的数据，它拼接多出来的值为null这个条件查出来。

mysql> select * from customers c left join orders o on c.id=o.id where o.id is null;
+----+------+------+------------+
| id | name | id   | customerId |
+----+------+------+------------+
|  3 | Sam  | NULL |       NULL |
|  4 | Max  | NULL |       NULL |
+----+------+------+------------+
2 rows in set (0.00 sec)

4.所有部分

分别查两张表，用union拼接，可以进行去重。

DDL

数据类型

int/float

通常bool类型使用TINYINT表示

字节数	类型	显示长度
1	TINYINT	4
2	SMALLINT	6
3	MEDIUMINT	8
4	INT	11
8	BIGINT	20
4	FLOAT	–
8	DOUBLE	–

字符串

显示长度可自定义，通常账户名可以指定20，标题128，描述内容256，内容8192等

字节数	类型
0~255	CHAR
0~65535	VARCHAR
0~65535	BLOB
0~65535	TEXT

定义了显示长度后，若配合zerofill，会自动补0到指定长度。但是长度超过了但是不超过数值的最大值，依然可以显示。
请添加图片描述

char:固定长度，显示长度(M)为显示的字符数（5.0以后的mysql），不是字节数。当不足时用空格填充，检索时不显示空格，检索时不用动态分配空间，速度快，但是如果有的变量没有用满固定长度的字符，会浪费空间。
varchar：可变长度，保存时只保存需要的字符数，再加1/2个字节来记录长度（小于255加1字节，大于加2字节）。需要动态分配空间，检索速度慢。但是节省空间。
BLOB:二进制大对象，一般用来存储图片、视频等
TEXT:存储文章等。

时间

字节数	类型	格式
3	DATE	YYYY-MM-DD
3	TIME	HH:MM:SS
8	DATETIME	YYYY-MM-DD HH:MM:SS
4	TIMESTAMP	YYYYMMDD HHMMSS

建表时使用 ON UPDATE CURRENT_TIMESTAMP 表示这条数据每次变更时，这个字段都会更新成CURRENT_TIMESTAMP

CREATE TABLE

COLLATE排序规则

对于mysql字符类型的字段，需要有一个COLLATE来告知mysql如何对该列进行排序和比较以及索引查找。排序规则的字段组成：charset_collation

charset: 常用的是utf8/utf8mb4。utf8支持3bytes，对于一些4bytes的文字可能无法支持，要使用utf8mb4。
collation定义编码标准：一般general_ci和unicode_ci对中文无影响。ci:case insensitive 大小写不敏感。具体描述可以看以下sql接口给出的：

所以一般我们在建表最后会加上DEFAULT CHARSET = utf8mb4 COLLATE utf8mb4_general_ci

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8mb3  | UTF-8 Unicode                   | utf8mb3_general_ci  |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.02 sec)