mysql学习 week3

DQL结尾

执行顺序

 可以参考week2分组查询部分

DQL小结

DQL语句

select 
    字段列表    》    字段名 [as] 别名
from
    表名
where
    条件列表      》        and or between in like      
group by
    分组字段列表        
having
    分组后条件列表
order by
    排序字段列表            》asc  desc
limit
    分页参数            》起始索引（从0开始）  ，   每页展示记录数

DCL数据控制语言

        用来管理数据库 用户、控制数据库的 访问权限

DCL——管理用户

use mysql;--使用mysql数据库
1.查询用户 --等同于DQL查询表中数据
select *from user;

2.创建用户
create user '用户名'@'主机名' identified by '密码';

3.修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

4.删除用户
drop user '用户名'@'主机名';

练习

1.创建用户 itcast ，只能够在当前主机localhost访问，密码123456
create user 'itcast'@'localhost' identified by '123456';

2.创建用户 heima ，可以在任意主机访问主句看，密码 123456
create user 'heima'@'%' identified by '123456';

3.修改用户 heima 的访问密码为 1234
alter user 'heima'@'%' identified with mysql_native_password by '1234';

4.删除itcast@localhost 用户
drop user 'itcast'@'localhost'

注意：

        在主机名中使用’%‘代表可以从任意主机访问

        SQL的开发人员对DCL的操作较少，主要是数据管理员DBA使用

DCL——权限控制

1.查询权限
show grants for '用户名'@'主机名';

2.授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

3.撤销权限
revoke 权限列表 on 数据库名.表名 form '用户名'@'主机名';

注意：

        1.多个权限中间“，”进行间隔

        2.授权时数据库和表名可以使用“*”进行通配，代表所有

函数部分

字符串函数

练习

1.企业员工工号统一为6位数，不足6位数的在前面补0
update emp set workno = lpad(workno,6,0);
--更新数据，使用update 

 数值函数

 练习

2.通过数据库函数，生成一个六位数的随机验证码
select lpad(round(rand() * 1000000, 0),6,0);
--select可以直接输出后面函数，理解为print
/*生成六位随机数，首先通过rand生成0-1之间数，再乘以1000000使其大小在0-999999之间
再通过lpad确保六位数*/

日期函数

 练习

3.查询所有员工的入职天数，并根据入职天数倒序排序
select name,datediff(now(),entrydate) as 'entrydates' from emp 
order by entrydates desc;

 流程控制函数

null值不是字符串 空“ ” 

4.查询emp表中员工姓名和工作地址（北京、上海-->一线城市    其他-->二线城市）
select name,
       case workaddress when '北京' then '一线城市'
                        when '上海' then '一线城市'
            else '二线城市' end as '工作地址'
from emp;

 创建练习对应表

create table score(
id int comment 'ID',
name varchar(20) comment '姓名',
math int comment '数学',
english int comment '英语',
chinese int comment '语文'
) comment '学员成绩表';
insert into score(id, name, math, english, chinese) 
VALUES (1, 'Tom', 67, 88, 95), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);

练习

5.统计各个学员成绩，>=85，展示优秀    > =60,展示及格    否则不及格
select name,
(case when math >=85 then '优秀'
      when math >=60 then '及格'
      else '不及格' end) '数学',--‘数学’为给标题，也可以用as来进行给标题
(case when english >=85 then '优秀'
      when english >=60 then '及格'
      else '不及格' end) '英语',
(case when chinese >=85 then '优秀'
      when chinese >=60 then '及格'
      else '不及格' end) '语文'
from score;

约束部分

概念

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

目的

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

分类

普通约束

create table user(
    id int primary key auto_increment comment '主键',
    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 '性别'
)comment '用户表';

 外键约束部分 

 概念

外键用来让两张表之间的数据建立连接，从而保证数据的一致性和完整性

具有外键的表为子表，外键关联的表为父表（主表）

相关语法

建立外键示例

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, '财务部'), (4,
'销售部'), (5, '总经办');
create table emp(
id int auto_increment comment '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',
dept_id int comment '部门ID'
)comment '员工表';
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),(2, '张无忌', 20,
'项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),(4, '韦一笑', 48, '开
发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 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);

--删除外键
alter table emp drop foreign key fk_emp_dept_id;

外键删除更新行为

前两个行为为默认行为 

 cascade

alter table emp 
add constraint fk_emp_dept_id foreign key (dept_id) references  dept(id) 
on update cascade on DELETE cascade;--更新和删除时规则都为cascade

setnull

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

总结