mysql基础知识-需求案例1

”种一棵树最好是十年前，其次是现在“，结合个人十多年IT基础架构领域摸爬滚打的经验来看，数据库领域潜力无限，大有可为。运维领域知识面需要广，更需要专，数据库是我选择做专做深的方向。

DDL-数据库操作

-- 查看所有数据库实例
show databases ;
-- 创建数据库
create database my_test;
-- 使用具体数据库实例
use my_test;
-- 显示数据库实例下所有表
show tables ;

-- DDL-表操作
-- 查询表结构
desc empploy;
-- 查询建表语句
show create table empploy;

-- 设计一张员工信息表，涉及数据类型
create table emp
(
    id        int comment '编号',
    workno    varchar(10) comment '员工工号',
    name      varchar(50) comment '员工姓名',
    sex       char(1) comment '性别',
    age       tinyint unsigned comment '年龄',
    lifeno    char(18) comment '身份证号',
    entrydate date comment '入职时间'
)comment '员工信息表';

-- 给emp表增加一个字段
alter table emp add nickname varchar(20) comment '昵称';
alter table empploy add workaddress varchar(50) comment '工作地址';
-- 修改emp表name字段为username
alter table emp change name username varchar(30) comment '员工姓名';
-- 修改表名emp为employ
alter table emp rename to empploy;
-- 删除表empploy
drop table if exists empploy;

-- 删除指定表，并重新创建（该表中数据也被删除）
truncate table empploy;

DML-数据操作语言

-- 为了能够插入中文字符，需要设置整个表的字符集为utf8mb4
alter table empploy convert to character set utf8mb4;
-- 向empploy表中插入多行数据
insert into empploy(id, workno, username, sex, age, lifeno, entrydate, nickname) values (1,'0001','jackgu','1',20,'330387654312831283','2022-08-10','jack');
insert into empploy values (1,'0001','zhangsan','男',21,'330387654312831281','2022-08-10','jack','地址北京');
insert into empploy values (2,'0002','lisi','女',22,'330387654312831283','2022-08-10','jack','上海');
insert into empploy values (3,'0003','wangwu','男',23,'330387654312831283','2022-08-10','jack','天津');
insert into empploy values (4,'0004','zhaoliu','男',24,'330387654312831283','2022-08-10','jack','杭州');
insert into empploy values (5,'0005','laoqi','女',25,'330387654312831283','2022-08-10','jack','广州');
insert into empploy values (6,'0006','erha','男',26,'330387654312831283','2022-08-10','jack','上海');
insert into empploy values (7,'0007','guaiguai','女',27,'330387654312831283','2022-08-10','jack','北京');
insert into empploy values (8,'0008','wangwang','男',28,'330387654312831283','2022-08-10','jack','杭州');
insert into empploy values (9,'0009','xuanxuan','女',29,'330387654312831283','2022-08-10','jack','天津');
insert into empploy values (10,'0010','yueyue','男',30,'330387654312831283','2022-08-10','jack','广州');
insert into empploy values (11,'0011','风清扬','女',25,NULL,'2022-08-10','江湖大佬','天津');
insert into empploy values (12,'0012','郭靖','男',35,'33038219871123456x','2022-07-10','傻郭靖','西安');

update empploy set sex = '男' where id=1;
-- 修改empploy表中的一行数据
update empploy set username = 'zhangwuji' where id = 1;
-- 删除empploy表中指定的数据
delete from empploy where lifeno = '';
select * from empploy;

DQL-数据查询语言

-- 查询年龄小于等于23岁的员工
select * from empploy where age <= 23;
-- 查询身份证号信息为空的员工
select * from empploy where lifeno is null ;
-- 查询性别为女且年龄在20到25岁之间的员工
select * from empploy where sex = '女' and age between 20 and 25;
-- 查询身份证信息最后一位为X的的员工信息
select * from empploy where lifeno like '%x';
-- 查询姓名为两个字的员工
select * from empploy where username like '__';

聚合函数案例练习

-- 统计所有员工数量
select count(*) from empploy;
-- 统计所有员工的平均年龄
select avg(age) from empploy;
-- 统计员工的最大年龄
select max(age) from empploy;
-- 统计员工的最小年龄
select min(age) from empploy;
-- 统计上海地区员工的年龄之和
select sum(age) from empploy where workaddress='上海';

分组查询案例练习

-- 按照性别分组，分别统计男性和女性员工的数量
select sex,count(*) from empploy group by sex;
-- 按照性别分组，分别统计男性和女性员工的平均年龄
select sex,avg(age) from empploy group by sex;

-- 查询年龄小于30的员工，并按照地址进行分组，获取员工数量大于等于2的分组
select workaddress,count(*) from empploy where age<30 group by workaddress having count(*) >= 2;

排序查询案例练习

-- 根据年龄对公司员工进行升序排序
select * from empploy order by age asc ;
-- 根据入职时间，对公司员工进行降序排序
select * from empploy order by entrydate desc ;
-- 按照年龄对公司员工进行升序排序，年龄相同，按照入职时间进行降序排序
select * from empploy order by age asc,entrydate asc ;

分页查询案例练习

-- 查询第一页员工信息，每页信息5条记录
select * from empploy limit 0,5;
select * from empploy limit 5;
-- 查询第二页员工信息，每页信息5条记录
select * from empploy limit 5,5;
-- 查询第三页员工信息，每页信息5条记录
select * from empploy limit 10,5;

DQL语句练习

-- 查询年龄为20，21，22，23岁的女性员工
   select * from empploy where sex = '女' and age between 20 and 23;
-- 查询性别为男，并且年龄为 20-30 岁（含）以内的，姓名为三个字的员工
   select * from empploy where sex = '男' and age between 20 and 30 and username like '___';
-- 统计员工表中，年龄小于60岁的，男性员工和女性员工的人数
   select sex,count(*) from empploy where age < 60 group by sex;
-- 查询年龄小于等于30岁员工姓名和年龄，并对结果按照年龄进行升序排序，如果年龄相同则按照入职时间降序排序
   select username,age from empploy where age <= 30 order by age asc,entrydate desc ;
-- 查询性别为男，并且年龄为 20-30 岁（含）以内的前5个员工，对查询的结果按照年龄升序排序，年龄相同按入职时间升序排序
   select * from empploy where sex = '男' and (age between 20 and 30) order by age desc ,entrydate desc limit 5 ;

DCL语句练习

-- 创建用户itcast，只能够在当前主机localhost访问，初始密码Zysz!@123456
    create user 'itcast'@'localhost' identified by 'Zysz!@123456';
-- 创建用户test，可以在任意访问和登录数据库，密码Zysz!@123456
    create user 'test'@'%' identified by 'Zysz!@123456';
-- 修改用户test的密码为1234
    alter user 'test'@'%' identified with mysql_native_password by 'Zy!@1234';
-- 删除itcast@localhost用户
    drop user 'itcast'@'localhost';

-- 查询权限
    show grants for 'test'@'%';
-- 授予权限
    grant all on my_test.* to 'test'@'%';
-- 撤销权限
    revoke all on my_test.* from 'test'@'%';

函数练习

**-- 字符串函数**

-- concat 拼接字符串
   select concat('hello','mysql!');
-- lower 将字符串全部转为小写
   select lower('HELLO');
-- upper 将字符串全部转为大写
   select upper('Hello Mysql!');
-- lpad 用字符串pad对字符串左边进行填充
   select lpad('01',5,'-');
-- rpad 用字符串pad对字符串右边进行填充
   select rpad('01','5','-');
-- trim 去掉字符串头尾的空格
   select trim(' hello mysql ');
-- substring(str,start,len) 返回从start起len长度的字符串
   select substring('hello mysql!',1,7);

use my_test;
-- 由于业务需求变更，企业员工的工号统一要求为5位数，不足的在前面用0填充
   update empploy set workno = lpad(workno,5,'0');

**-- 数值函数**
-- ceil 向上取整
   select ceil(1.9);
-- floor 向下取整
   select floor(1.5);
-- mod 返回x/y的模
   select mod(4,4);
-- rand 返回0-1内的随机数
   select rand();
-- round 求参数x的四舍五入的值，保留y位小数
   select round(2.3445,2);

-- 通过数据库的函数，生成一个6位数的随机验证码
   select lpad(round(rand()*1000000,0),6,'0');

**-- 日期函数**
-- now()
   select now();
-- YEAR,MONTH,DAY
   select YEAR(now());
   select MONTH(now());
   select DAY(now());
-- date_add
   select date_add(now(),interval 70 DAY );
-- datediff
   select datediff('2022-5-20','2022-10-23');

-- 案例：查询所有员工的入职天数，并按照入职天数进行倒序排序
   select username,datediff(curdate(),entrydate) as entrydate from empploy order by entrydate desc ;

**-- 流程控制函数**
-- if
   select if(true,'ok','error');
-- ifnull
   select ifnull('ok','Default');
   select ifnull('','Default');
   select ifnull(null,'Default');
-- case when then else end
-- 需求：查询empploy员工姓名和工作地址（北京/上海---->一线城市，其他----->二线城市）
   select
       username,
       (case  workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
   from empploy;

作者：谷会于（转载请获本人授权，并注明作者与出处）

本文由 mdnice 多平台发布