发布日期: 2021-03-30

一、注意事项

sql语法以分号结尾，并且不区分大小写
所有非数字的值都要加单引号
#为注释
sql语句执行顺序

1)from
2)join
3)on
4)where
5)group by
6)having
7)select
8)order by
9)limit

重点：所以在select子句处起的别名不能再where子句中使用

sql语言书写顺序

select    查询列表
from     表1 别名
join     表2 别名
where    分组前条件筛选
group by 分组字段
having    分组后条件筛选
order by 排序字段 asc/desc
limit    分页索引，每页查询数

二、基本CRUD

1、查询

select name,salary*12 from users where salary is not null;

2、修改

update users set age=18 where name='李四' ;

3、增加

insert into users (name,age) values ('王五',19);
insert into users values ('王五',19); #字段名可省略，但不建议这么写

#同时插入多条数据
INSERT INTO users(username,PASSWORD,phone,adress) VALUES('aaa','123','123','123'),('bbb','123','123','123');

4、删除

delete from users where name='李四';

5、清空表

TRUNCATE TABLE users2

三、关系、逻辑表达式

1、关系表达式

< > <= >= 不解释

不等于：<> 或者 !=

#以下2句sql等价
select * from users where name<>'张三';
select * from users where name!='张三';

2、逻辑表达式

and or not 对应（&& || ！）与或非

推荐用and、or、not

#以下两两等价
select * from users where name<>'张三' and name!='李四';
select * from users where name<>'张三' && name!='李四';

select * from users where name<>'张三' or name!='李四';
select * from users where name<>'张三' || name!='李四';

select * from users where not(name<>'张三' and name!='李四');
select * from users where !(name<>'张三' and name!='李四');

3、null的判断

对于非null类型，用!=或<>
对于null类型，用is null 或 is not null

而<=>（安全等于）可判断null和非null，但是可读性差

4、ifnull

ifnull（字段名，如果该字段为null则取该值）

#查询users表中所有用户的姓名、性别，如果性别列为空，则默认为'男'
select name,ifnull(gender,'男') gender from users;

四、常用关键字

1、模糊查询 like

通常与通配符一起使用

%：代表任意数量的字符（包括0个）

_：代表一个字符

#查询姓名以张开头的
select * from users where name like '张%';

#查询姓名包含'文'的
select * from users where name like '%文%';

#查询姓名以张开头的2个字的名字
select * from users where name like '张_';

2、条件范围 in

字段名 in(‘常量1’,’常量2’)

字段名 not in(‘常量1’,’常量2’)

判断条件是否在in后面的集合内

#查询姓名为张三或李四的用户
select * from users where name in ('张三','李四');

#查询姓名不是张三或李四的用户
select * from users where name not in ('张三','李四');

3、范围查询 between and

#查询年龄在10到20岁的人,包括10和20，10<=age<=20
select * from users where age between 10 and 20;

#也可用来查询日期范围,包括25秒和27秒
select * from users where birthday BETWEEN '2020-12-22 21:12:25' AND '2020-12-22 21:12:27';

#注意：当时间范围只有年月日时，将不包括最大日期，22日<=birthday<23日
#我的理解是不写时分秒时，默认为00:00:00所以不包含最后一天
#当'2020-12-23 00:00:00'或'2020-12-23'可查询到，而23日的其他时间均不可查询到
#该语句查询的是22日一整天+'2020-12-23 00:00:00'
select * from users where birthday BETWEEN '2020-12-22' AND '2020-12-23';

4、排序查询

order by 字段名 asc或desc

升序（默认）：asc

降序：desc

排序列表可以使单个字段，多个字段，表达式，函数，列数，及以上组合

#按年龄升序(默认升序)
select * from users order by age;

#按年龄降序
select * from users order by age desc; 

#按姓名的字符字典排序(支持英文名，不支持中文拼音首字母排序)
select * from users order by name;

#按姓名的长度进行降序排序
select * from users order by length(name) desc

#多字段排序
#先按年龄升序排序，年龄一样的数据按姓名长度排序
select * from users order by age,length(name) desc;

#按指定列的字符字典排序
#按第二列（第二个字段）的字符字点排序
select * from users order by 2;

按中文拼音首字母排序

#在处理使用Mysql时，数据表采用utf8字符集，使用中发现中文不能直接按照拼音排序

#如果数据表tbl的某字段name的字符编码是latin1_swedish_ci
select * from users order by birary(name) asc;

#如果数据表tbl的某字段name的字符编码是utf8_general_ci
SELECT name FROM users ORDER BY CONVERT(name USING gbk) COLLATE gbk_chinese_ci ASC;

5、去重`distinct`

SELECT DISTINCT age FROM users;        #查出所有用户的年龄，相同的age只会出现一次
SELECT COUNT(DISTINCT age) FROM users;        #查出所有不重复的age的数据条数

6、分页`limit`

select * from users limit 开始索引,每页数据条数;

索引从0开始
开始索引=(当前页-1)*每页查询条数

五、单行函数

1、字符函数

（1）拼接字符`concat`

concat（字符串1，字符串2，字符串3）

select concat('hello,',name,age) 备注 from users;

备注
hello,张三18

（2）获取字节长度`length`

SELECT NAME,LENGTH(NAME) 姓名长度  FROM users;

name    姓名长度
张三           6
李四        6
lily     4

一个中文占用3字节，字母占用一字节

（3）获取字符长度`char_length`

select name,char_length(name) 姓名长度 from users;

name    姓名长度
张三           2
李四        2
lily     4

（4）截取子串`substring`

substring（字符串，开始索引，截取字符数）

可简写substr，截取字符数可省略，默认截取到最后

注意：mysql索引从1开始，java从开始

select substring('mysql必知必会',1,5) 截取;

截取
mysql

（5）从左右截取子串`left/right`

left('字符串',截取长度)

select left('我爱mysql',2) 左截取;

左截取
我爱

（6）获取字符第一次出现索引`instr`

select instr('学习数据库啊数据库','数据库') 索引;

索引
3

（7）去前后空格`trim`

不去除中间的

也可用于去除前后指定的字符（x为要去除的字符）

trim('x' from '字符串')

select trim('  my sql ') 去前后空格;

去前后空格
my sql

select trim('我' from '我我我 你我他我我我') 去除我;

去除我
 你我他

（8）填充

lpad/rpad 左/右填充

lapd('原字符串',填充后字符长度,'用于填充的字符串')

select lpad('mysql',10,'爱') 填充;

填充
爱爱爱爱爱mysql

当用于填充的字符串与原字符串总字符大于指定的填充后的字符串时，会对用于填充的字符串进行截取后填充

当原字符串长度大于指定字符串长度时，会对原字符串进行截取，并且也不会进行填充

（9）大小写转化`upper/lower`

select upper('abc') 大写;

大写
ABC

（10）比较两字符大小`strcmp`

strcmp('str1','str2)

str1>str2时返回1，相等返回0，否则返回-1

select strcmp('abc','acc') 比较;

比较
-1

2、数学函数

（1）绝对值`ABS`

select abs(-2.4);

（2）向上/下取整`ceil/floor`

select ceil(-1.09);        #-1,向上取整
select floor(-1.09);     #-2,向下取整

（3）四舍五入`round`

select round(1.873232);        #2,四舍五入
select round(1.871353,2);    #1.87,保留小数点后2位

（4）截断`truncate`

select truncate(1.8712345,1);    #1.8,只保留小数点后一位

（5）取余`mod`

select mod(-10,3);        #-1，相当于-10%3

3、日期函数

（1）当前日期/时间

select now();        #2021-04-01 20:13:00
select curdate();    #2021-04-01        仅日期
select curtime();    #20:13:00        仅时间

（2）获取年月日时分秒

详见下一篇文章MySQL时间转化函数

SELECT YEAR(NOW()) 年;            #2021    当前年份
SELECT YEAR('1998-1-1') 年;        #1998
SELECT MONTH(NOW()) 月;            #4        当前月份

（3）日期之差`datediff`

select datediff('2021-04-01','2021-04-11');        #10,单位天

（4）日期格式化

#日期转化为指定格式字符串
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');
-> 21年04月01日

#指定格式的字符串转化为日期
SELECT STR_TO_DATE('2015-01-01', '%Y-%m-%d');
-> 2015-01-01

format的格式都列出来：

%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 字符% )

TIME_FORMAT(time,format)：
具体用法和DATE_FORMAT()类似,但TIME_FORMAT只处理小时、分钟和秒(其余符号产生一个NULL值或0)

4、流程控制函数

（1）`if`函数

SELECT IF(10<5,'对','错');    #类似三元运算符，10<5?'对':'错'
-> 错

SELECT
        t.cost_type,
        if(u1.REAL_NAME is null,u2.nick_name,u1.REAL_NAME) creator,
        t.create_date,
        t.remark
FROM
        t_settle_log t
left join 
        t_sys_user u1 on u1.id = t.creator
left join 
        t_wx_user u2 on u2.id = t.creator
WHERE
        t.cbill_id = #{id}
order by  
        t.create_date desc

（2）`case`函数

用法1–switch

相当于java的switch

mysql中

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end

/*案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资*/

SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

用法2–多重if

mysql中：

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end

/*案例：查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示C级别
否则，显示D级别*/

SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

六、聚合函数和分组查询

1、聚合函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：
sum 求和、avg 平均值、max 最大值、min 最小值、count 计算非空个数

特点：
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略null值

3、可以和distinct搭配实现去重的运算

4、count函数的单独介绍，一般使用count(*)用作统计行数

5、和分组函数一同查询的字段要求是group by后的字段

SELECT SUM(salary) FROM employees;        #求表中所有salary的和
SELECT AVG(salary) FROM employees;        #求表中所有salary的平均值
SELECT MIN(salary) FROM employees;        #求表中所有salary的最小值
SELECT MAX(salary) FROM employees;        #求表中所有salary的最大值

SELECT COUNT(salary) FROM employees;    #求表中salary不为空的数据条数
SELECT COUNT(DISTINCT age) FROM users;        #查出所有不重复的age的数据条数

2、分组查询

select 查询列表
from 表
where 筛选条件
group by 分组的字段
having 分组后筛选条件
order by 排序的字段

查询列表一般是分组函数和分组字段

关键字	筛选对象	效率
where	筛选的是查询的数据库中的表（原始表）	高
having	筛选的是group by后的结果集（临时表）	低

/*查询用户中，小于18的年龄中，哪几个年龄的人数大于10个*/
select age,count(*) 
from users
where age<18
group by age 
having count(*)>10

七、连接

sql92语法和 sql99语法区别
功能：sql99支持的较多
可读性：sql99实现连接条件（join）和筛选条件（where）的分离，可读性较高

1、内连接

（1）等值连接

sql92语法

SELECT last_name,department_name
FROM departments d，employees e
WHERE e.`department_id` = d.`department_id`;

sql99语法

#案例.查询员工名、部门名
SELECT last_name,department_name
FROM departments d
JOIN  employees e
ON e.`department_id` = d.`department_id`;

（2）非等值连接

sql92语法

/*查询 工资级别是A 的员工的 工资和工资级别*/
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';

sql99语法

SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
where g.`grade_level`='A';

（3）自连接

sql92语法

 #查询员工的名字、上级的名字
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

sql99语法

  #查询员工的名字、上级的名字
 SELECT e.last_name,m.last_name
 FROM employees e
 JOIN employees m
 ON e.`manager_id`= m.`employee_id`;

2、外连接

（1）左外连接

查询左表全部数据，以及右表中符合条件的数据

 SELECT b.*,bo.*
 FROM boys bo
 LEFT OUTER JOIN beauty b
 ON b.`boyfriend_id` = bo.`id`
 WHERE b.`id` IS NULL;

（2）右外连接

查询右表的全部数据，以及左表中符合条件的数据

SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

（3）全连接

FULL JOIN 或 FULL OUTER JOIN（全连接）。

完整外部连接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

注意：MySQL是不支持全外的连接的，这里给出的写法适合Oracle和DB2。但是可以通过左外和右外求合集来获取全外连接的查询结果。

 SELECT b.*,bo.*
 FROM beauty b
 FULL OUTER JOIN boys bo
 ON b.`boyfriend_id` = bo.id;

3、联合查询

Union 或 Union all

用来连接2条查询语句的结果集，这两个结果集要求有相同的列数、数据类型、字段名及字段顺序。

union可以去除重复数据，union all查询全部。

SELECT id,SERIAL FROM payment1
UNION ALL
SELECT id,SERIAL FROM payment2;

八、子查询

1、exists

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
EXISTS 指定一个子查询，检测行的存在。

SELECT c.CustomerId,CompanyName FROM Customers c
WHERE EXISTS(
    SELECT OrderID 
    FROM Orders o 
    WHERE o.CustomerID=c.CustomerID
    )

2、子查询分类

按子查询出现的位置：

select后面：
    仅仅支持标量子查询
from后面：
    支持表子查询
where或having后面：★
    标量子查询（单行） √
    列子查询  （多行） √

    行子查询

exists后面（相关子查询）
    表子查询

按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

where或having后面

1、标量子查询（单行子查询）
2、列子查询（多行子查询）

3、行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用

< >= <= = <>

列子查询，一般搭配着多行操作符使用
in、any/some、all

# in：判断某字段是否在指定列表中
select * from users where age in(10,20,30)
select * from users where age in(select age from man)

# any/some：判断某字段的值是否满足其中任意一个,以下2句等价
select * from users where age > any(select age from man)
select * from users where age > (select min(age) from man)

# all：判断某字段是否满足以下所有值
select * from users where age > all(select age from man)
select * from users where age > (select max(age) from man)

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

⑤将子查询结果充当一张表，要求必须起别名（from后面）

九、正则表达式

判断开头

-- 查询以name字段以 ·手· 开头的数据
select * from pms_category where name regexp '^手'

匹配结尾

-- 查询以name字段以 ·机· 结尾的数据
select * from pms_category where name regexp '机$'

. 匹配任意单个字符，可以匹配除了换行符之外的任意字符

手套 不会被匹配到，但是真皮手套会被匹配到

select * from pms_category where name regexp '.手'

[…] 匹配括号中的任意单个字符是否出现

匹配name字段中含有 手 或者 电 的数据

select * from pms_category where name regexp '[手电]'

[^…] 注意^只有在[]内才是取反的意思，在其他地方都是开始匹配，匹配是否含有括号之外的字符

以下语句中，手机不会被匹配到，而苹果手机则会被匹配到

select * from pms_category where name regexp '[^手机]'

a* 匹配0个或多个a，包含空字符串，可以作为占位符，有没有指定字符都可以匹配到数据

-- 匹配t和b之间包含0个或多个a的字符
select 'tab' regexp 'ta*b'
-- 这两条语句都能匹配成功
select '' regexp 'a*'
-- 匹配b前面出现0个或多个ta
select 'tab' regexp '(ta)*b'

a+ 匹配1个或多个a，不能是0个

-- 匹配成功
select 'tab' regexp 'ta+b'
-- 匹配失败
select 'tb' regexp 'ta+b'

a? 匹配0个或1个a，不能匹配多个

a|b 匹配a或者b

select 'a' regexp 'a|b'
-- 是否以a或者b开头
select 'ann' regexp '^(a|b)'

a{m} 匹配到m个a

-- 匹配到a、x中含3个u的字符
select 'auuux' regexp 'au{3}x'

-- 匹配3个或以上个u
select 'auuuux' regexp 'au{3,}x'

-- 匹配到3个到5个u,包含3个和5个u
select 'auuuuux' regexp 'au{3,5}x'

(ab) 将ab看做一个整体去结合其他的运算符

更多的扩展判断见正则表达式文章

十、数据类型

1、货币

DECIMAL(9,2)

9表示被用于存储值的总的位数（包含小数点后的数字），2代表被用于存储小数点后的位数。

以上写法范围是从-9999999.99 到 9999999.99

当实际存储小数位数大于设置的值2时，会四舍五入保留到指定的位数，如存入1.999，存储后为2.00

十一、通过 in 查询且排序

当通过 in 进行查询时，默认是不会排序的，需要加上一下语句

order by field(排序字段,值1,值2)

select * from t_user
where id in (5,1)
order by field(id,5,1)

十二、建表

1、数据类型

（1）数值类型

整数类型	字节	范围	用途
tinyint	1	-128~127	很小的整数
smallint	2	-32768~32767
mediumint	24位	-8388608~8388607
int	32位	-2147483648~2147483647
bigint	64位	±9.22*10的18次方

浮点数类型	字节	说明
float	4	单精度
double	8	双精度
decimal		用于金钱，不会出现精度损失

（2）字符类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TEXT	0-65 535 bytes	长文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

char和varchar区别

写法	M的意思	特点	空间的耗费	效率
char(M)	最大的字符数，可省略，默认为1	固定长度的字符	比较耗费	高
varchar(M)	最大的字符数，不可以省略	可变长度的字符	比较节省	低

（3）日期类型

类型	字节	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01/9999-12-31	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-2038	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

（4）二进制类型

类型	大小	用途
BLOB	0-65 535 bytes	二进制大对象（图片，音视频）
MEDIUMBLOB	0-16 777 215 bytes	二进制形式
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大对象

数据库

MySQL建库建表

2021-04-07 数据库

数据库

计算机组成原理

2021-03-07 计算机基础

计算机基础

MySQL基本语法

一、注意事项

二、基本CRUD

1、查询

2、修改

3、增加

4、删除

5、清空表

三、关系、逻辑表达式

1、关系表达式

2、逻辑表达式

3、null的判断

4、ifnull

四、常用关键字

1、模糊查询 like

2、条件范围 in

3、范围查询 between and

4、排序查询

5、去重distinct

6、分页limit

五、单行函数

1、字符函数

（1）拼接字符concat

（2）获取字节长度length

（3）获取字符长度char_length

（4）截取子串substring

（5）从左右截取子串left/right

（6）获取字符第一次出现索引instr

（7）去前后空格trim

（8）填充

（9）大小写转化upper/lower

（10）比较两字符大小strcmp

2、数学函数

（1）绝对值ABS

（2）向上/下取整ceil/floor

（3）四舍五入round

（4）截断truncate

（5）取余mod

3、日期函数

（1）当前日期/时间

（2）获取年月日时分秒

（3）日期之差datediff

（4）日期格式化

4、流程控制函数

（1）if函数

（2）case函数

用法1–switch

用法2–多重if

六、聚合函数和分组查询

1、聚合函数

2、分组查询

七、连接

1、内连接

（1）等值连接

（2）非等值连接

（3）自连接

2、外连接

（1）左外连接

（2）右外连接

（3）全连接

3、联合查询

八、子查询

1、exists

2、子查询分类

九、正则表达式

十、数据类型

1、货币

十一、通过 in 查询且排序

十二、建表

1、数据类型

（1）数值类型

（2）字符类型

（3）日期类型

（4）二进制类型

5、去重`distinct`

6、分页`limit`

（1）拼接字符`concat`

（2）获取字节长度`length`

（3）获取字符长度`char_length`

（4）截取子串`substring`

（5）从左右截取子串`left/right`

（6）获取字符第一次出现索引`instr`

（7）去前后空格`trim`

（9）大小写转化`upper/lower`

（10）比较两字符大小`strcmp`

（1）绝对值`ABS`

（2）向上/下取整`ceil/floor`

（3）四舍五入`round`

（4）截断`truncate`

（5）取余`mod`

（3）日期之差`datediff`

（1）`if`函数

（2）`case`函数