MySQL 练习
create table user
(
username varchar(256) null comment '用户昵称',
id bigint auto_increment comment 'id'
primary key,
userAccount varchar(256) null comment '账号',
avatarUrl varchar(1024) null comment '用户头像',
gender tinyint null comment '性别',
userPassword varchar(512) not null comment '密码',
phone varchar(128) null comment '电话',
email varchar(512) null comment '邮箱',
userStatus int default 0 not null comment '状态 0 - 正常',
createTime datetime default CURRENT_TIMESTAMP null comment '创建时间',
updateTime datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
isDelete tinyint default 0 not null comment '是否删除',
userRole int default 0 not null comment '用户角色 0 - 普通用户 1 - 管理员',
planetCode varchar(512) null comment '星球编号'
)
comment '用户';
INSERT INTO `user` VALUES (NULL, 2, 'yupi', 'https://gw.alipayobjects.com/zos/rmsportal/KDpgvguMpGfqaHPjicRK.svg', 0, '70592fdc7c74e715e07713a5e6177d18', NULL, NULL, 0, '2023-05-03 12:25:21', '2023-05-19 23:46:41', 0, 0, NULL);
INSERT INTO `user` VALUES (NULL, 3, 'yupi2', 'https://gw.alipayobjects.com/zos/rmsportal/KDpgvguMpGfqaHPjicRK.svg', 0, '70592fdc7c74e715e07713a5e6177d18', NULL, NULL, 0, '2023-05-03 17:23:18', '2023-05-19 23:46:41', 0, 1, NULL);
INSERT INTO `user` VALUES ('suxh', 4, 'suxh', 'https://gw.alipayobjects.com/zos/rmsportal/KDpgvguMpGfqaHPjicRK.svg', 1, '211a1ab694e44776a4fc64f3de5260dc', NULL, NULL, 0, '2023-05-07 16:36:27', '2023-05-19 23:46:41', 0, 1, NULL);
INSERT INTO `user` VALUES (NULL, 5, 'suxh2', 'https://gw.alipayobjects.com/zos/rmsportal/KDpgvguMpGfqaHPjicRK.svg', 1, '211a1ab694e44776a4fc64f3de5260dc', NULL, NULL, 0, '2023-05-07 16:46:07', '2023-05-19 23:46:40', 0, 0, NULL);
INSERT INTO `user` VALUES (NULL, 6, 'suxh3', 'https://gw.alipayobjects.com/zos/rmsportal/KDpgvguMpGfqaHPjicRK.svg', 1, '211a1ab694e44776a4fc64f3de5260dc', NULL, NULL, 0, '2023-05-07 16:46:49', '2023-05-19 23:46:41', 0, 0, NULL);
INSERT INTO `user` VALUES (NULL, 7, 'suxh4', 'https://gw.alipayobjects.com/zos/rmsportal/KDpgvguMpGfqaHPjicRK.svg', 1, '211a1ab694e44776a4fc64f3de5260dc', NULL, NULL, 0, '2023-05-19 20:38:35', '2023-05-19 23:46:41', 0, 0, NULL);
INSERT INTO `user` VALUES (NULL, 9, 'yupi3', NULL, NULL, '70592fdc7c74e715e07713a5e6177d18', NULL, NULL, 0, '2023-05-20 13:56:51', '2023-05-20 13:56:51', 0, 0, NULL);
INSERT INTO `user` VALUES (NULL, 11, 'suxh5', NULL, NULL, '211a1ab694e44776a4fc64f3de5260dc', NULL, NULL, 0, '2023-05-20 14:11:53', '2023-05-20 14:11:53', 0, 0, '21070');
INSERT INTO `user` VALUES (NULL, 12, 'suxh6', NULL, NULL, '211a1ab694e44776a4fc64f3de5260dc', NULL, NULL, 0, '2023-05-20 15:20:09', '2023-05-20 15:20:09', 0, 0, '1');
INSERT INTO `user` VALUES (NULL, 13, 'suxh7', NULL, NULL, '211a1ab694e44776a4fc64f3de5260dc', NULL, NULL, 0, '2023-05-20 15:58:41', '2023-05-20 15:58:41', 0, 0, '11111');
INSERT INTO `user` VALUES ('suxh8', 14, 'suxh8', 'https://gw.alipayobjects.com/zos/rmsportal/KDpgvguMpGfqaHPjicRK.svg', NULL, '211a1ab694e44776a4fc64f3de5260dc', NULL, NULL, 0, '2023-05-20 16:36:25', '2023-05-20 16:59:09', 0, 0, '1234');
show databases; -- 查看所有数据库
select database(); -- 查看当前数据库
create database if not exists demo1; -- 创建数据库 假如不存在创建
create database demo2 default charset=utf8mb4; -- 创建数据库 设置字符集
drop database if exists demo2; -- 删除数据库 假如存在再删除
use demo1; -- 使用数据库
show tables; -- 查看所有表
desc user; -- 查看表结构
show create table user; -- 查看表创建语句
tinyint -- 小整数
smallint -- 小中整数
mediumint -- 中等整数
int integer -- 大整数
bigint -- 大整数
float -- 单精度浮点数
double -- 双精度浮点数
decimal -- 任意精度数值
char -- 字符串
varchar -- 可变长度字符串
tinyblob -- 短二进制字符串
tinytext-- 短文本字符串
blob -- 二进制字符串长文本
text -- 文本字符串长文本
mediumblob -- 中等长度二进制字符串
mediumtext -- 中等长度文本字符串
longblob -- 长二进制字符串
longtext -- 长文本字符串
date -- 日期
time -- 时间
year -- 年
datetime -- 日期时间
timestamp -- 时间戳
create table tmp(
id int primary key auto_increment comment 'id',
name varchar(256) not null comment '姓名',
age tinyint unsigned not null comment '年龄',
gender char(1) comment '性别',
email varchar(256) not null comment '邮箱'
)comment '员工表'; -- 创建表语句
alter table tmp add column phone2 varchar(256) not null comment '电话' after email; -- 添加列
alter table tmp add column workaddress varchar(256) not null comment '工作地址' after email;
alter table tmp modify column phone2 char(254) not null comment '电话' after email; -- 修改列数据类型
alter table tmp change column phone2 phone3 varchar(256) not null comment '电话' after email; -- 修改列
alter table tmp drop column phone3; -- 删除列
alter table tmp rename tmp2;
alter table tmp2 rename to tmp; -- 修改表名
drop table if exists tmp; -- 删除表
truncate table tmp; -- 删除指定表 并重新创建表
desc tmp; -- 表结构
show tables; -- 显示库里所以表名
insert into tmp values (1,'yupi',20,'1','1234567890@qq.com','北京');
insert into tmp (id, name, age, gender, email, workaddress) values (2,'yupi',18,'0','1234567890@qq.com','河北');
insert into tmp (id, name, age, gender, email, workaddress) values (3,'yupi',18,'1','1234567890@qq.com','河北'),(4,'yupi',18,'0','1234567890@qq.com','北京'),(5,'yupi',18,'1','1234567890@qq.com','山西');
select * from tmp;
update tmp set age=20 where id=1;
delete from tmp where id=1;
select id,name,gender from tmp ;
select id as "序列",name as "姓名" from tmp;
select distinct name from tmp;
select id,name,age from tmp where age in(18,20);
SELECT * FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE country = 'USA'
); -- 子查询 来自美国的全部订单
select * from tmp where name like '%yu%';
select count(*) from tmp;
select count(name) from tmp;
select sum(age) from tmp;
select avg(age) from tmp;
select gender,avg(age) from tmp where age>=18 group by gender; -- 年龄大于等于18 男女平均年龄 以性别分组
select gender,count(*) from tmp group by gender; -- 男女总人数 以性别分组
select gender,avg(age) from tmp group by gender; -- 平均年龄 以性别分组
select workaddress,count(*) workaddress_count from tmp where age = 18 group by workaddress having workaddress_count>=2; -- 有2个以上员工的地区 并且年龄等于18
select workaddress,gender,count(*) '数量' from tmp group by gender, workaddress; -- 各地男女的数量
select * from tmp order by age asc ;
select * from tmp order by age desc ;
select * from emp order by age asc , entrydate desc; -- 根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
select * from tmp limit 2,4; -- 从第2条开始显示4条
select * from emp where age in(20,21,22,23);
select * from emp where gender="男" and age between 20 and 40 and name like "___";
select gender "性别", count(age) "年龄小于60岁的员工人数"from emp where age < 60 group by gender;
select name,age,entrydate from emp where age<=35 order by age asc ,entrydate desc ;
select * from emp where gender="男" and age between 20 and 40 order by age asc ,entrydate asc limit 5 ;
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);
ceil(x) -- 向上取整
floor(x) -- 向下取整
mod(x,y) -- 返回x/y的模(取余)
rand() -- 返回0~1内的随机数
round(x,y) -- 求参数x的四舍五入的值，保留y位小数
select lpad(round(rand()*1000000 , 0), 6, '0'); -- 随机数乘于1000000取整数位数为6位，左填充0，得到6位随机数
curdate() -- 返回当前日期
curtime() -- 返回当前时间
now() -- 返回当前日期和时间
year(date) -- 获取指定date的年份
month(date) -- 获取指定date的月份
day(date) -- 获取指定date的日期
date_add(date, interval 时间间隔) -- 返回(data+时间间隔的时间值)
select date_add(now(), interval 70 year );
select date_add(now(), interval 1 day );
select date_format(now(), '%Y-%m-%d %H:%i:%s');
select name,workaddress,( case workaddress when '北京' then '一线城市'else '二线城市' end )as '工作地址' from tmp;
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 '性别'
); -- id自增主键，姓名唯一，年龄大于0小于120，状态默认为1
CREATE TABLE dept(
id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
name varchar(10) NOT NULL UNIQUE COMMENT '部门'
)comment '部门表';
alter table tmp add constraint fk_tmp_dept_id foreign key(depe_id) references dept(id);
alter table tmp drop foreign key fk_tmp_dept_id;
select t.name as "姓名",t.workaddress as "工作地址",d.name as "部门" from tmp t join dept d on t.dept_id = d.id;
select t.name ,d.name from tmp t ,dept d where t.dept_id = d.id;
select t.* ,d.name from tmp t ,dept d where t.dept_id = d.id;
select * from tmp where dept_id = (select id from dept where name = '研发部')
select id from dept where name='市场部' or name='销售部';
select * from tmp where dept_id in (select id from dept where name='市场部' or name='销售部');
-- 子查询返回的结果是一列（多行），这种子查询称为列子查询
-- 常用操作符 描述
in -- 在指定的集合范围之内，多选一
not in -- 不在指定的集合范围之内
any -- 子查询返回列表中，有任意一个满足即可
some -- 与ANY等同，使用SOME的地方都可以使用ANY
all -- 子查询返回列表的所有值都必须满足
查询比 财务部 所有人工资都高的员工信息
分解为以下两步:
①. 查询所有 财务部 人员工资
select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');
②. 比 财务部 所有人工资都高的员工信息
select * from emp where salary > all ( select salary from emp where dept_id =(select id from dept where name = '财务部') );
案例:
A. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
分解为两步执行:
①. 查询 "鹿杖客" , "宋远桥" 的职位和薪资
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';
②. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name ='鹿杖客' or name = '宋远桥' );
B. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
分解为两步执行:
①. 入职日期是 "2006-01-01" 之后的员工信息
select * from emp where entrydate > '2006-01-01';
②. 查询这部分员工, 对应的部门信息;
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;
参与讨论
(Participate in the discussion)
参与讨论