目录
一、函数
1. 字符串函数
命令如下所示:
-- concat
select concat("Hello" , " MySQL");//Hello MySQL
-- lower
select lower("Hello");//hello
-- upper
select upper("Hello");//HELLO
-- lpad
select lpad('01",5,"-");//---01
-- rpad
select rpad('01",5,"-");//01---
-- trim
select trim(" Hello MySQL ");//Hello MySQL
-- substring
select substring("Hello MySQL",1,5);//Hello;首位索引为1
案例练习:
命令如下所示:
-- 1.由于业务需求变更,企业员工的工号,统一5位数,目前不足5位数的全部在前面补0。比如:1号员工的工号应该为00001,
update emp set workno = lpad(workno,5,"0");
输出如下所示:
2. 数值函数
命令如下所示:
-- ceil
select ceil(1.1);//2
-- floor
select floor(1.9);//1
-- mod
seLect mod(3,4);//3%4=3
-- rond
select rand();
-- round
select round(2.345,2);//2.35
案例练习:
命令如下所示:
--若rand生成的数为0.0135566则需要右边补零
select lpad(round(rand()*1000000 , 0),6,"0');
3. 日期函数
命令如下所示:
-- curdate()
select curdate();
-- curtime()
select curtime();
-- now ()
select now();
-- YEAR MONTH DAY
select YEAR(now());
select MONTH(now());
seleet DAY(now());
-- date_add
select date_add(now(),INTERVAL 70 DAY);
select date_add(now(),INTERVAL 70 MONTH);
select date_add(now(),INTERVAL 70 YEAR);
-- datediff
select datediff("2021-12-01", "2021-10-01");//60;前减后
案例练习:
命令如下所示:
select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;
输出如下所示:
4. 流程函数
命令如下所示:
-- if
select if(true,'Ok','Error');//OK
-- ifnull
select ifnull('Ok','Default');//OK
select ifnull(' ','Default');//空
select ifnull(null, 'Default');//Default
-- case when then else end
--需求:查bemp表的员工姓名和工作地址〔北京/上海---->一线城市,其他---->二线城市)
select
name,
( case workaddress when'北京' then '一线城市' when ' 上海' then '一线城市' else '二线城市' end ) as '工作地址
from emp;
案例练习:
生成表:
如下所示:
命令如下所示:
select
id,
name,
(case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end)'数学",
(case when english >= 85 then '优秀' when english >=60 then‘及格' else '不及格' end)'英语',
(case when chinese >= 85 then '优秀' when chinese >=60 then‘及格'else '不及格' end)'语文
from score;
输出如下所示:
5. 总结
二、约束
1. 概述
2. 约束演示
案例练习:
命令如下所示:
create table user(
id int primary key auto_increment comment "主键",
name varchar(10) not null unique conment"姓名",
age int check ( age > 0 && age c= 120 ) comment"年龄",
status char(1]default "1"comment"状态",
gender char(1) comment"性别"
)comment '用户表';
--插入数据
insert into user(name ,age,status,gender) values ('Tom1',19,'1','男'),('Tom2',25,0','男');
insert into user(name,age,status,gender) values ('Tom3',19,'1','男');
insert into user(name,age,status,gender) values (null,19,'1','男');
insert into user(name,age,status,gender) values ('Tom5',19,'1','男');
insert into user(name,age,status,gender) values ('Tom4',80,"1','男');
insert into user(name,age,status,gender) values ('Tom5',-1,'1','男');
insert into user(name,age,status,gender) values ('Tom5',121,'1','男');
insert into user(name,age,gender) values ('Tom5',120,'男');
输出如下所示:
图形化界面创建如下所示:
补充:
3. 外键约束
命令如下所示:
--准备数据
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称',
)comment '部门表';
INSERT INTO dept (id,name) VALUES (1,'研发部'),(2,'市场部'),(3,'财务部'),(6,'销售部'),(5,'总经办');
create table emp(
id int auto_increment conment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
ept_id int comment '部门ID'
)comment '员工表';
INSERT INTo emp (id,name,age,job, salary,entrydate,managerid,dept_id) VALUES
(1,'金庸',66,'总裁' ,2000,'280-01-01',null,5),
(2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),
(3,‘杨道',33,'开发',8400, '2000-11-03',2,1),
(4,'韦一笑',48,'开发' ,11000,'2682-82-85',2,1),
(5,'常遇春',4,'开发' ,10500,'2004-09-87',3,1),
(6,'小昭',19,'程序员鼓励师' ,6600,'2004-10-12',2,1);
上述两表并未建立关联
3.1 添加外键
命令如下所示:
--准备数据
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称',
)comment '部门表';
INSERT INTO dept (id,name) VALUES (1,'研发部'),(2,'市场部'),(3,'财务部'),(6,'销售部'),(5,'总经办');
create table emp(
id int auto_increment conment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
ept_id int comment '部门ID'
)comment '员工表';
INSERT INTo emp (id,name,age,job, salary,entrydate,managerid,dept_id) VALUES
(1,'金庸',66,'总裁' ,2000,'280-01-01',null,5),
(2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),
(3,‘杨道',33,'开发',8400, '2000-11-03',2,1),
(4,'韦一笑',48,'开发' ,11000,'2682-82-85',2,1),
(5,'常遇春',4,'开发' ,10500,'2004-09-87',3,1),
(6,'小昭',19,'程序员鼓励师' ,6600,'2004-10-12',2,1);
--添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
输出如下所示:
3.2 删除外键
命令如下所示:
--准备数据
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称',
)comment '部门表';
INSERT INTO dept (id,name) VALUES (1,'研发部'),(2,'市场部'),(3,'财务部'),(6,'销售部'),(5,'总经办');
create table emp(
id int auto_increment conment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
ept_id int comment '部门ID'
)comment '员工表';
INSERT INTo emp (id,name,age,job, salary,entrydate,managerid,dept_id) VALUES
(1,'金庸',66,'总裁' ,2000,'280-01-01',null,5),
(2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),
(3,'杨逍',33,'开发',8400, '2000-11-03',2,1),
(4,'韦一笑',48,'开发' ,11000,'2682-82-85',2,1),
(5,'常遇春',4,'开发' ,10500,'2004-09-87',3,1),
(6,'小昭',19,'程序员鼓励师' ,6600,'2004-10-12',2,1);
--添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
--删除外键
hlter table emp drop foreign key fk_emp_dept_id;
输出如下所示:
3.3 外键删除更新行为
命令如下所示:
--准备数据
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称',
)comment '部门表';
INSERT INTO dept (id,name) VALUES (1,'研发部'),(2,'市场部'),(3,'财务部'),(6,'销售部'),(5,'总经办');
create table emp(
id int auto_increment conment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
ept_id int comment '部门ID'
)comment '员工表';
INSERT INTo emp (id,name,age,job, salary,entrydate,managerid,dept_id) VALUES
(1,'金庸',66,'总裁' ,2000,'280-01-01',null,5),
(2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),
(3,'杨逍',33,'开发',8400, '2000-11-03',2,1),
(4,'韦一笑',48,'开发' ,11000,'2682-82-85',2,1),
(5,'常遇春',4,'开发' ,10500,'2004-09-87',3,1),
(6,'小昭',19,'程序员鼓励师' ,6600,'2004-10-12',2,1);
--添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
--删除外键
hlter table emp drop foreign key fk_emp_dept_id;
--外键的删除和更新行为
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;
输出如下所示:
图形化界面设置如下所示: