Mysql数据库命令大全(三）

目录

alter 用法补充： 

函数

字符串函数

数值函数

日期函数

流程函数

约束

主键约束

外键约束

1). 添加外键

2). 删除外键

1.CASCADE

2.SET NULL

---

alter 用法补充： 

1．掌握修改表结构的方法；
 增加列
   alter table 表名 add 列名 列类型
   alter table 表名 add 列名 列类型 after 列名
 修改列类型
   alter table 表名 modify 列名 新类型
 修改列
   alter table 表名 change 列名 新字段名 字段选项 ;
 调整列位置
   alter table 表名 change 列名 新列名 字段选项 after字段名
 删除列
   alter table 表名 drop column 字段名
2．插入记录；
insert into 表名 values

函数

字符串函数

 

# A. concat : 字符串拼接
select concat('Hello' , ' MySQL');
# B. lower : 全部转小写
select lower('Hello');
# C. upper : 全部转大写
select upper('Hello');
# D. lpad : 左填充
select lpad('01', 5, '-');
# E. rpad : 右填充
select rpad('01', 5, '-');
# F. trim : 去除空格
select trim('   Hello MySQL ');
# G. substring : 截取子字符串
select substring('Hello MySQL',1,5);

由于业务需求变更，企业员工的工号，统一为 5 位数，目前不足 5 位数的全部在前面补 0 。比如： 1 号员工的工号应该为00001

update emp set workno = lpad(workno, 5, '0');

数值函数

 

# A. ceil：向上取整
select ceil(1.1);
# B. floor：向下取整
select floor(1.9);
# C. mod：取模
select mod(7,4);
# D. rand：获取随机数
select rand();
# E. round：四舍五入
select round(2.344,2);

通过数据库的函数，生成一个六位数的随机验证码

select lpad(round(rand()*1000000 , 0), 6, '0');

思路：获取随机数可以通过rand() 函数，但是获取出来的随机数是在 0-1 之间的，所以可以在其基础

上乘以 1000000 ，然后舍弃小数部分，如果长度不足 6 位，补 0

日期函数

 

# A. curdate：当前日期
select curdate();
 
# B. curtime：当前时间
select curtime();
 
# C. now：当前日期和时间
select now();
 
# D. YEAR , MONTH , DAY：当前年、月、日
select YEAR(now());
select MONTH(now());
select DAY(now());
 
# E. date_add：增加指定的时间间隔
select date_add(now(), INTERVAL 70 YEAR );
 
# F. datediff：获取两个日期相差的天数
select datediff('2021-10-01', '2021-12-01');
 
 

 

查询所有员工的入职天数，并根据入职天数倒序排序

思路： 入职天数，就是通过当前日期 - 入职日期，所以需要使用 datediff 函数来完成

select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays 
desc;

流程函数

# A. if
select if(false, 'Ok', 'Error');
 
# B. ifnull
select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');
 
# C. case when then else end
select
name,
( case workaddress when '北京' or '上海'  then '一线城市'else
'二线城市' end ) as '工作地址'
from emp;
 
 

约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据

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

主键约束

CREATE TABLE tb_user(
id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
name varchar(10) NOT NULL UNIQUE COMMENT '姓名' ,
age int check (age > 0 && age <= 120) COMMENT '年龄' ,
status char(1) default '1' COMMENT '状态',
gender char(1) COMMENT '性别'
);

 

 

外键约束

创建两表

1). 添加外键

CREATE TABLE 表名 (
字段名 数据类型 ,
...
[CONSTRAINT] [ 外键名称 ] FOREIGN KEY ( 外键字段名 ) REFERENCES 主表 ( 主表列名 )
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY ( 外键字段名 )
REFERENCES 主表 ( 主表列名 ) ;

 为 empp 表的 dept_id 字段添加外键约束 , 关联 dept 表的主键 id

alter table empp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

2). 删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称 ;

alter table emp drop foreign key fk_emp_dept_id;

删除/更新行为

 

语法

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY ( 外键字段 ) REFERENCES
主表名 ( 主表字段名 ) ON UPDATE CASCADE ON DELETE CASCADE;

 由于 NO ACTION 是默认行为，我们前面语法演示的时候，已经测试过了，就不再演示了，这里我们再演示其他的两种行为：CASCADE 、 SET NULL 。

1.CASCADE

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on 
update cascade on delete cascade ;

现象是主副表同步

2.SET NULL

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on 
update set null on delete set null ;

我们删除id为1的数据，发现父表的记录是可以正常的删除的，父表的数据删除之后，再打开子表 empp，我们发现子表empp 的dept_id字段，原来dept_id为1的数据，现在都被置为NULL了。