目录
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了。