MySQL - 笔记2

一、函数

1. 字符串函数

concat(s1,s2...sn) #字符串拼接，将s1,s2...sn拼接成一个字符串

lpad(str, n, pad)  #左填充 用字符串pad对str的左边进行填充，达到n个字符串的长度

rpad(str, n, pad)  #右填充 用字符串pad对str的右边进行填充，达到n个字符串的长度

trim(str)          #去掉字符串头部和尾部的空格

substring(str, start, len)    #返回字符串str从start位置起len个长度的字符串

        使用案例：

select concat('a','b');
# 返回‘ab’

select rpad('y',3,'*');
#返回 y**

select lpad('y',3,'*');
#返回 **y

select trim('   sfsifo 8900   ');
#返回 sfsifo 8900

select substring('abcdef',2,2);
#返回 bc

2. 数值函数

ceil(x)    #向上取整

floor(x)   #向下取整

mod(x,y)   #返回x模y的值

rand()     #返回0-1内的随机数

round(x,y) #对x四舍五入，保留y位小数

        使用例子：生成一个六位数的随即验证码

 select lpad(round(rand() * 1000000,0),6,'0');  #返回473597

3. 日期函数

curdate()    #返回当前日期

curtime()    #返回当前时间

now()        #返回当前日期和时间

year(date) / month(date) / day(date)  #获取指定date的年份/月份/日期

date_add(date, interval expr type)    #返回一个日期/时间值加上一个时间间隔expr后的时间值

datediff(date1, date2)    #返回两个日期相隔天数

select curdate();
# 2023-04-30

select curtime();
# 22:30:06

select now();
# 2023-04-30 22:30:13 

select year(now());
# 2023

select month(now());
# 4

select day(now());
# 30

select date_add(now(), interval 30 day);
# 2023-05-30 22:43:11

select datediff(now(), '2020-01-01');
# 1215

4. 流程函数

if(value, t, f)
# 如果value时true返回t，否则返回f

ifnull(value1, value2)
# 如果value不为空，返回value1，否则返回value2

case when [val1] then [res1] ... else [default] end
# 如果val1为true，返回res1，否则返回default

case [expr] when [val1] then [res1] ... else [default] end
# 如果expr值等于val1，返回res1，否则返回default

二、约束

        概念：作用于字段，用于限制存储在表中的数据

        目的：保证数据库在数据的正确、有效性和完整性

        分类：

约束	描述	关键字
非空约束	该字段数据不能为null	not null
唯一约束	所有数据是唯一的、不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	default
检查约束	保证字段值满足某条件	check
外键约束	用来让两张表的数据之间建立连接	foreign key

        举个例子

id	主键并且自动增长 primary key，auto_increment
name	不为空且唯一 not null, unique
age	0<年龄<=120 check
status	如果没有指定的值，默认为1 default

• 外键约束

        建立外键关联两种方式

1. 建表的时候就建立外键

create table xxx（

        ......

        [constraint] [外键名称] foreign key (外键字段名) references 主表（主表列明）

）；

2. 建表后建立外键

alter table 表名 add constraint 外键名称 foreign key （外键字段名）references 主表（主表列明）；

删除外键

alter table 表名 drop foreign key 外键名称

删除/更新行为

no action	类似restrict， 先检查是否有外键，有则不允许删除/更新
restrict	同上
cascade	如有外键，则也删除更新外键在子表中的记录
set null	父表中删除对应记录时，首先检查记录是否有对应外键，有，则设子表中外键值为null
set default	父表变更时，子表将外键列设置成默认值

alter table 表名 add constraint 外键名称 foreign key （外键字段名）references 主表（主表列明） on update cascade on delete cascade;

三、多表查询

笛卡尔积

两个集合A和B的所有组合情况。多表查询时，需要消除无效的笛卡尔积。

例子：A表存放3个用户信息(1,2,3)，B表存放5个用户所居住的区（a,b,c,d,e）, 已知每个用户就居住在5个区当中的一个区。

          执行select * from A,B;会得到

1	a
1	b
1	c
1	d
1	e
2	a
2	b
2	c
2	d
2	e
3	a
3	b
3	c
3	d
3	e

 如上表，假设1就住在a，但查询结果也返回了大量不匹配的答案，此时需要消除笛卡尔积。

多表关系

•  一对多（多对一）

        案例：部门与员工的关系

                   一个部门对应多个员工，一个员工对应一个部门

                   实现：在多的一方建立外键，指向一的一方的主键

•  多对多

        案例：学生与课程的关系

                   一个学生可选多门课程，一门课也可被多个学生选

                   实现：建立一张中间表，中间表至少包含两个外键，分别关联两方主键

•  一对一

        案例：用户与用户详情

                   多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升效率。

                   实现：在任意一方加入外键，关联另一方的主键，并设置外键为唯一unique。

多表查询分类

• 内连接

        查询A、B表的交集

#隐式内连接
select 字段列表 from 表1，表2 where 条件;

#显示内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件;

• 外连接

        左外连接：查询左表所有数据

select 字段列表 from 表1 left [outer] join 表2 on 条件;

        右外连接：查询右表所有数据

select 字段列表 from 表1 right [outer] join 表2 on 条件;

        自连接：当前表与自身连接的查询，自连接必须使用表别名

                自连接查询可以是内连接查询也可以是外连接查询

select 字段列表 from 表A 别名A join 表A 别名B on 条件;

• 联合查询union

        把多次查询结果合并形成新的查询结果集

select 字段列表 from 表A
union [all]
select 字段列表 from 表B;

对于联合查询的多张表的列数必须保持一致，字段类型也需保持一致。

union all 会将全部数据直接合并在一起，union会对合并之后的数据去重。

• 子查询

        概念：SQL中嵌套了select语句

select * from t1 where column1 = (select column1 from t2);

        子查询外部的语句可以是insert/update/delete/select

根据查询结果不同：

        标量子查询（子查询结果为单个值）：=, <, >, >=, <=，<>

        列子查询（子查询结果为一列）: in, not in, any, some, all

        行子查询（子查询结果为一行）:=, <>, in, not in

        表字查询（子查询结果为多行多列）: in