基础
建表语句
# create table emp(
# id int comment '编号',
# worknum varchar(10) comment '工号',
# name varchar(10) comment '姓名',
# gender char(1) comment '性别',
# age tinyint unsigned comment '年龄',
# idcard char(18) comment '身份证号',
# workaddress varchar(50) comment '工作地址',
# entrydate date comment '入职时间'
# )comment '员工表';
# drop table emp;
insert into emp(id, worknum, name, gender, age, idcard, workaddress, entrydate) VALUES
(1,'1','柳岩','女',20,'123456789012345678','北京','2000-01-01'),
(2,'2','张无忌','男',18,'123456789012345677','北京','2001-01-01'),
(3,'3','韦一笑','男',38,'123456789012345676','上海','2002-01-01'),
(4,'4','赵敏','女',18,'123456789012345675','北京','2003-01-01'),
(5,'5','小昭','女',16,'123456789012345674','上海','2004-01-01'),
(6,'6','杨逍','男',28,'123456789012345673','北京','2005-01-01'),
(7,'7','范瑶','男',40,'123456789012345672','北京','2006-01-01'),
(8,'8','黛绮丝','女',38,'123456789012345671','天津','2007-01-01'),
(9,'9','范凉凉','女',45,'123456789012345670','北京','2008-01-01'),
(10,'10','陈友谅','男',53,'123456789012345618','上海','2009-01-01'),
(11,'11','张士诚','男',55,'123456789012345628','江苏','2010-01-01'),
(12,'12','常遇春','男',32,'123456789012345638','北京','2011-01-01'),
(13,'13','张三丰','男',88,'123456789012345648','江苏','2012-01-01'),
(14,'14','灭绝','女',65,'123456789012345658','西安','2013-01-01'),
(15,'15','胡青牛','男',70,'123456789012345668','西安','2014-01-01'),
(16,'16','周芷若','女',18,null,'北京','2015-01-01');
DQL查询
1.DQL条件查询
#select name,worknum,age from emp;
#1.查询年龄等于88的员工
#select *from emp where age=88;
#2.查询年龄小于20的员工信息
#select * from emp where age<20;
#3.查询年龄小于等于20的员工信息
#select * from emp where age<=20;
#4.查询没有身份证号的员工信息
#select * from emp where idcard is null;
#5.查询有身份证号的员工信息
#select * from emp where idcard is not null;
#6.查询年龄不等于88的员工信息
#select * from emp where age !=88;
#7.查询年龄在15岁(包含)到20岁(包含)之间的员工信息
#select * from emp where age>=15 && age<=20;
#select * from emp where age between 15 and 20;
#8.查询性别为 女 且年龄小于25岁的员工信息
#select * from emp where gender='女'and age<25;
#9.查询年龄等于18或20或40的员工信息
#select * from emp where age=18 or age =40 or age=20;
#select * from emp where age in(18,20,40);
#10.查询姓名为两个字的员工信息 _ % 一个%可以代替好几个字符，一个下划线只会代替一个字符，所以用下划线
#select * from emp where name like '__';
#11.查询身份证号最后一位是X的员工信息
#select * from emp where idcard like '%7';
2.聚合函数
#聚合函数
#1.统计该企业员工数量
#select count(*) from emp;
#select count(id) from emp;
#2.统计该企业员工的平均年龄
#select avg(age) from emp;
#3.统计该企业员工的最大年龄
#select max(age) from emp;
#4.统计该企业员工的最小年龄
#select min(age) from emp;
#5.统计西安地区员工的年龄之和
#select sum(age) from emp where workaddress='西安';
3.分组查询
#分组查询
#1.根据性别分组，统计男性员工 和 女性员工的数量
#select gender,count(*) from emp group by gender;
#2.根据性别分组，统计男性员工 和 女性员工的平均年龄
#select gender,avg(age) from emp group by gender;
#3.查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址。
select workaddress,count(*) address_count from emp where age<45 group by workaddress having count(*)>=3;
4.排序查询
# 排序查询
# 1.根据年龄对公司的员工进行升序排序
#select * from emp order by age asc;
#2.根据入职时间，对员工进行降序排序。
#select * from emp order by entrydate DESC ;
#3.根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序
select *from emp order by age asc,entrydate desc;
5分页查询
#分页查询
#1.查询第1页员工数据，每页展示10条记录。
#select * from emp limit 0,10;
#select * from emp limit 10;
#2.查询第2页员工数据，每页展示10条记录。(页面-1)*页展示记录数
select * from emp limit 10,10;
6.DQL案例练习
#DQL 语句练习
#1.查询年龄为20，21，22，23岁的女性员工信息。
select * from emp where gender='女' and age in(20,21,22,23);
#2.查询性别为 男，并且年龄在 20~40岁（含）以内的姓名为三个字的员工。
#select * from emp where gender='男' and age between 20 and 40 and name like '___';
#3.统计员工表中，年龄小于60岁的，男性员工和女性员工的人数
#select gender,count(*) from emp where age <60 group by gender;
#4.查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同则按入职时间降序排序
select name,age from emp where age<=35 order by age asc ,entrydate desc ;
#5.查询性别为男，且年龄在20~40岁（含）以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
select * from emp where gender ='男' and age between 20 and 40 order by age asc,entrydate asc limit 5;
DCL
1用户管理
#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';
函数
1.1字符串函数
#concat
select concat('hello','Mysql');
#lower
select lower('HEllo');
#upper
select upper('heLLO');
#lpad
select lpad('01',5,'-');
#rpad
select rpad('01',5,'-');
#trim 去除头部和尾部的空格
select trim(' Hell0 Mysql ');
#substring
select substring('Hello Mysql',1,5);
2.数值函数
#数值函数
#ceil
select ceil(1.4);
#floor
select floor(1.6);
#mod
select mod(6,4);
#rand
select rand();
#round
select round(2.345,2);
#案例 通过数据库的函数，生成一个六位数的随机验证码
select lpad(round(rand()*1000000,0),6,0);
3.日期函数
#日期函数
#1.curdate()
select curdate();
#2.curtime()
select curtime();
#3.now()
select now();
#4.year,month,day
select year(now());
select month(now());
select day(now());
#5.date_add
select date_add(now(),interval 70 day);
#6.datediff
select datediff('2023-7-28','2003-3-12');
#案例-查询所有员工的入职天数，并根据入职天数倒叙排序
select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc ;
4.流程控制函数
#流程控制函数
#1.if
select if(true,'OK','Error');
#2.ifnull
select ifnull('ok','Default');
#3.case when then else and
#需求：查询emp表的员工姓名和工作地址（北京/上海----> 一线城市，其他----> 二线城市）
select
name,
(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp
事务
create table salgrade(
grade int,
losal int,
hisal int
)comment '薪资等级表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);
create table account(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '姓名',
money int comment '余额'
)comment '账户表';
insert into account(id,name,money)values (null,'张三',2000),(null,'李四',2000);
# 恢复数据
update account set money=2000 where name='张三'or name='李四';
select @@autocommit; #查询默认的提交方式，默认为1(自动)
set @@autocommit=0; # 设置手动提交
-- 转账操作(张三给李四转账1000元)
start transaction ;
# 1.查询张三余额账户
select * from account where name='张三';
# 2.将张三余额减1000
update account set money =money-1000 where name='张三';
# 3.将李四余额加1000
update account set money =money+1000 where name='李四';
# 提交事物
commit;
# 回滚事物
rollback ;
#事物的四大特性
# 原子性 一致性 隔离性 持久性 ACID
2175
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
