SQL常用语句笔记1-阿里云开发者社区

https://github.com/QInzhengk/Math-Model-and-Machine-Learning

1.常用MySQL命令

# 查看所有数据库
SHOW DATABASES;
# 切换指定数据库
USE test;
# 查看当前库中所有的表
SHOW TABLES;
# 查看表结构
DESC departments;
# 查看当前所处的数据库
SELECT DATABASE();
# 查看当前登陆用户
SELECT USER();
# 查看版本
SELECT VERSION();

2.语法规范

关键字不区分大小写，但建议关键字大写表名、列名建议小写每条命令最好用分号结尾每条命令根据需要，可以进行缩进或换行最好是关键字单独占一行

3.语句分类

数据查询语言（Data Query Language, ）DQL 负责进行数据查询而不会对数据本身进行修改的语句。数据定义语言 (Data Definition Language,）DDL 负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成数据操纵语言（Data Manipulation Language,）DML 负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。数据控制语言 (Data Control Language) 它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。

MySQL索引

索引是帮助MySQL高效获取数据的数据结构

索引数据结构：二叉树、红黑树、hash表、B-Tree

普通索引：最基本的索引，没什么限制。
唯一索引：索引列的值必须唯一，但允许有空值。
主键索引：一种特殊的索引，一个表只能有一个主键，不允许有空值。
组合索引：指多个字段上创建的索引，使用组合索引遵循最左前缀原则。
全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值比较。

数据查询语言

基础查询

# 查单个字段
select dept_name from departments;
# 查多个字段
select name, email from employees;
# 查所有字段
select * from departments;
# 使用表达式
select date, employee_id, basic+bonus from salary;
# 查询函数，统计salary共有多少行记录
select count(*) from salary;
# 使用别名，字段名和别名之间可以用空格或关键字AS与as指定别名
select dept_id 部门编号, dept_name AS 部门名 from departments;
# 去重 distinct
select dept_id from employees;
select distinct dept_id from employees;
# 使用concat函数进行字符串拼接
select concat(name, '-', phone_number) from employees;

条件查询

select * from departments where dept_id>3; 
select * from departments where dept_id<3;
select * from departments where dept_id=3;
select * from departments where dept_id!=3;
select * from departments where dept_id>=3;
select * from departments where dept_id<=3;
select * from departments where dept_id>1 and dept_id<5;
select * from departments where dept_id<3 or dept_id>6;
select * from departments where not dept_id<=6;

模糊查询

like: 包含
between x and y : 在x和y之间的
in：在列表中的
is null：为空，相当于python的None
is not null：非空
%匹配0到多个任意字符
_匹配一个字符

select name, email from employees where name like '张%';
select name, email from employees where name like '张_';
select * from departments where dept_id between 3 and 5;
select * from departments where dept_id in (1, 3, 5, 8);
# 匹配部门名为空的记录
select * from departments where dept_name is null;
# 查询部门名不为空的记录
select * from departments where dept_name is not null;

排序（默认升序）

select name, birth_date from employees where birth_date>'19980101';
# 默认升序排列
select name, birth_date from employees where birth_date>'19980101' order by birth_date;
# 降序排列
select name, birth_date from employees where birth_date>'19980101' order by birth_date desc;

函数

字符函数

LENGTH(str)：返字符串长度，以(字节)为单位

select length('abc');
select length('你好');
select name, email, length(email) from employees where name='李平';

CONCAT(s1,s2，...): 返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL

select concat(dept_id, '-', dept_name) from departments;

UPPER(str)和UCASE(str): 将字符串中的字母全部转换成大写

select name, upper(email) from employees where name like '李%';

LOWER(str)和LCASE(str):将str中的字母全部转换成小写

SUBSTR(s, start, length): 从子符串s的start位置开始，取出length长度的子串，位置(从1)开始计算

select substr('hello world', 7);
# 取子串，下标从7开始取出3个
select substr('hello world', 7, 3);

INSTR(str,str1)：返回str1参数，在str参数内的位置

# 子串在字符串中的位置
select instr('hello world', 'or');
select instr('hello world', 'ol');

TRIM(s): 返回字符串(s删除了两边空格之后的字符串)

select trim('  hello world.  ');

LEFT(str, length) ：从左开始截取字符串，length 是截取的长度。

group_concat语法

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

数学函数

ABS(x)：返回x的绝对值

select abs(-10);

MOD(x,y): 返回x被y除后的余数

select mod(10, 3);

CEIL(x)、CEILING(x): 返回不小于x的最小整数

select ceil(10.1);

FLOOR(x): 返回不大于x的最大整数

select floor(10.9);

ROUND(x)、ROUND(x,y): 前者返回最接近于x的整数，即对x进行四舍五入；后者返回最接近x的数，其值保留到小数点后面y位，若y为负值，则将保留到x到小数点左边y位

select round(10.6666);返回最接近于x的整数，即对x进行四舍五入
select round(10.6666, 2);返回最接近x的数，其值保留到小数点后面y位

日期和时间函数

CURDATE()、CURRENT_DATE(): 将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定

select curdate();当前日期按照"YYYY-MM-DD"
select curdate() + 0;格式根据函数用在字符串或是数字语境中而定

NOW(): 返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定

select now();式为"YYYY_MM-DD HH:MM:SS"
select now() + 0;具体格式根据函数用在字符串或数字语境中而定

UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date): 前者返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数，后者返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数

select unix_timestamp();

FROM_UNIXTIME(date): 和UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间

select from_unixtime(0);

MONTH(date)和MONTHNAME(date):前者返回指定日期中的月份，后者返回指定日期中的月份的名称

select month('20211001120000');返回指定日期中的月份
select monthname('20211001120000');返回指定日期中的月份的名称

DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d): DAYNAME(d)返回d对应的工作日的英文名称，如Sunday、Monday等；DAYOFWEEK(d)返回的对应一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d对应的工作日索引，0表示周一，1表示周二

select dayname('20211001120000');返回星期*
select dayname('20211001');

WEEK(d): 计算日期d是一年中的第几周

select week('20211001');

DAYOFYEAR(d)、DAYOFMONTH(d)：前者返回d是一年中的第几天，后者返回d是一月中的第几天

select dayofyear('20211001');

YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time): YEAR(date)返回指定日期对应的年份，范围是1970到2069；QUARTER(date)返回date对应一年中的季度，范围是1到4；MINUTE(time)返回time对应的分钟数，范围是0~59；SECOND(time)返回制定时间的秒值

select year('20211001');返回指定日期对应的年份
select quarter('20211001');回date对应一年中的季度

datediff(日期1, 日期2)：得到的结果是日期1与日期2相差的天数。如果日期1比日期2大，结果为正；如果日期1比日期2小，结果为负。

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
1
SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
-31

流程控制函数

IF(expr,v1,v2): 如果expr是TRUE则返回v1，否则返回v2

select if(3>0, 'yes', 'no');
select name, dept_id, if(dept_id=1, '人事部', '非人事部')  from employees where name='张亮';

IFNULL(v1,v2): 如果v1不为NULL，则返回v1，否则返回v2

select dept_id, dept_name, ifnull(dept_name, '未设置') from departments;
insert into departments(dept_id) values(9);
select dept_id, dept_name, ifnull(dept_name, '未设置') from departments;

CASE expr (WHEN v1)( THEN r1) [WHEN v2 THEN v2] [ELSE rn] END: 如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn

select dept_id, dept_name,
case dept_nam
when '运维部' then '技术部门'
when '开发部' then '技术部门'
when null then '未设置'
else '非技术部门'
end as '部门类型'
from departments;
select dept_id, dept_name,
case 
when dept_name='运维部' then '技术部门'
when dept_name='开发部' then '技术部门'
when dept_name is null then '未设置'
else '非技术部门'
end as '部门类型'
from departments;

分组函数

用于统计，又称为聚合函数或统计函数

# sum/min/count/avg
select employee_id, max(basic+bonus) from salary where employee_id=10 and year(date)=2018;

分组查询

语法格式

查询列表必须是分组函数和出现在（GROUP BY）后面的字段
通常而言，分组前的数据筛选放在where子句中，分组后的数据筛选放在having子句中

SELECT 字段名1(要求出现在group by后面)，分组函数(),……
FROM 表名
WHERE 条件
GROUP BY 字段名1，字段名2
HAVING 过滤条件
ORDER BY 字段;
查询每个部门的人数
select dept_id, count(*) from employees group by dept_id;
查询每个部门中年龄最大的员工
select dept_id, min(birth_date) from employees group by dept_id;
查询每个部门入职最晚员工的入职时间
select dept_id, max(hire_date) from employees group by dept_id;
统计各部门使用tedu.cn邮箱的员工人数
select dept_id, count(*) from employees where email like '%@tedu.cn' group by dept_id;
+---------+----------+
| dept_id | count(*) |
+---------+----------+
|       1 |        5 |
|       2 |        2 |
|       3 |        4 |
|       4 |       32 |
|       5 |        7 |
|       6 |        5 |
|       7 |       15 |
|       8 |        1 |
+---------+----------+
8 rows in set (0.00 sec)
查看员工2018年工资总收入，按总收入进行降序排列
select employee_id, sum(basic+bonus) as total from salary where year(date)=2018 group by employee_id order by total desc;
查询部门人数少于10人
select dept_id, count(*) from employees where count(*)<10 group by dept_id;
ERROR 1111 (HY000): Invalid use of group function
select dept_id, count(*) from employees group by dept_id having count(*)<10;
+---------+----------+
| dept_id | count(*) |
+---------+----------+
|       1 |        8 |
|       2 |        5 |
|       3 |        6 |
|       6 |        9 |
|       8 |        3 |
+---------+----------+
5 rows in set (0.00 sec)

查询结果中如果有where，group by（包含having），order by，使用的顺序group by（包含having）必须在where之后，order by之前。

连接查询

也叫多表查询。常用于查询字段来自于多张表

如果直接查询两张表，将会得到笛卡尔积
select name, dept_name from employees, departments;
通过添加有效的条件可以进行查询结果的限定
select name, dept_name from employees, departments where employees.dept_id=departments.dept_id;

语法格式

SELECT 字段... 
FROM 表1 [AS] 别名 [连接类型]
JOIN 表2 [AS] 别名
ON 连接条件
WHERE 分组前筛选条件
GROUP BY 分组
HAVING 分组后筛选条件
ORDER BY 排序字段

内连接

select 查询列表
from 表1 别名
inner join 表2 别名 on 连接条件
inner join 表3 别名 on 连接条件
[where 筛选条件]
[group by 分组]
[having 分组后筛选]
[order by 排序列表]

等值连接

查询每个员工所在的部门名，使用别名。两个表中的同名字段，必须指定表名

select name, d.dept_id, dept_name
from employees as e
inner join departments as d
on e.dept_id=d.dept_id;

查询2018年总工资大于30万的员工，按工资降序排列

select name, sum(basic+bonus) as total from employees as e
inner join salary as s
on e.employee_id=s.employee_id
where year(s.date)=2018
group by name
having total>300000
order by total desc;

非等值连接 between ... and ...（前面包括后面不包括）

创建表语法：

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)
mysql> use test;
mysql> create table age_grade
    -> (
    -> id int, #主键。仅作为表的行号
    -> grade char(1), #工资级别，共ABCDE五类
    -> low int, #该级别最低工资
    -> high int, #该级别最高工资
    -> primary key (id));

向表中插入数据语法：

INSERT INTO 表名称 VALUES (值1, 值2,....);
insert into age_grade values
(1, 'A', 5000, 8000),
(2, 'B', 8001, 10000),
(3, 'C', 10001, 15000);

查询2018年12月员工各基本工资级别的人数

select grade, count(*)
from salary as s
inner join wage_grade as g
on s.basic between g.low and g.high
where year(date)=2018 and month(date)=12
group by grade;

查询2018年12月员工基本工资级别，员工需要显示姓名

select name, date, basic, grade
from salary as s
inner join employees as e
on s.employee_id=e.employee_id
inner join wage_grade
on basic between low and high
where date='20181210'
order by grade, basic;

SQL常用语句笔记1

https://github.com/QInzhengk/Math-Model-and-Machine-Learning

1.常用MySQL命令

2.语法规范

3.语句分类

MySQL索引

数据查询语言

基础查询

条件查询

模糊查询

排序（默认升序）

函数

字符函数

数学函数

日期和时间函数

流程控制函数

分组函数

分组查询

连接查询

内连接

等值连接

非等值连接 between ... and ...（前面包括后面不包括）

创建表语法：

向表中插入数据语法：

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

SQL常用语句 笔记1

1.常用MySQL命令

2.语法规范

3.语句分类

MySQL索引

数据查询语言

基础查询

条件查询

模糊查询

排序（默认升序）

函数

字符函数

数学函数

日期和时间函数

流程控制函数

分组函数

分组查询

连接查询

内连接

等值连接

非等值连接 between ... and ...（前面包括后面不包括）

创建表语法：

向表中插入数据语法：

热门文章

最新文章

相关课程

相关电子书

相关实验场景

SQL常用语句笔记1