mysql
MySQL
1.什么是mysql
SQL是操作所有关系型数据库的编程语言,统一标准
MySQL是一个中小关系型数据库管理平台
2.DDL
2.1如何创建数据库
- create datebase [ if not exists] 数据库名
- 图形化工具
2.2如何创建一个表
create 表名(
字段1 字段类型 [约束] [comment 字段1注解]
...
字段n 字段类型 [约束] [common 字段n注解]
)[common 表注解];
2.3约束
not null (非空)
unique(唯一)
primary key 主键(非空唯一)
default(默认值)
2.4常见数据类型
数字类型:tynyint(-128~127 ) int(4字节) bigint(8字节)
字符串类型:char(长度)
varchar(长度)存入字符串长度小于指定长度时调整长度为存入字符串 长度节省资源
日期类型:date(年月日) datetime(年月日 时分秒)
3.DML语句
3.1添加数据(insert)
3.1.1指定字段添加数据:
insert into 表名(字段列表) values(值列表)
-- 1. 为emp 表的 username, name, gender 字段插入值
insert into emp(username,name,gender,update_time,creat_time)
values ('cs1','陈帅',1,now(),now());
3.1.2全部字段添加数据:
insert into 表名 (全部字段列表) values (全部值列表)
-- 2. 为 emp 表的 所有字段插入值
insert into emp(username,name,gender,update_time,creat_time)
values ('cs1','陈帅',1,now(),now());
3.1.3批量添加数据(指定字段)
insert into 表名(字段列表) values (值列表),(值列表)
-- 3. 批量为 为 emp 表的 username , name , gender 字段插入数据
insert into emp(username,name,gender,update_time,creat_time) values
('cs9','陈帅',1,now(),now()),
('cs4','陈帅',1,now(),now()),
('cs5','陈帅',1,now(),now()),
('cs6','陈帅',1,now(),now()),
('cs7','陈帅',1,now(),now()),
('cs8','陈帅',1,now(),now());
3.14批量添加数据(全部字段)
insert into 表名(全部字段列表) values (全部值列表),(全部值列表)
insert into emp (id, username, password, name, gender, image, job,
entrydate, update_time, creat_time, age)
values (null,'cs2','666','陈帅2',1,'123789',1,'2021-12-23',now(),now()),
(null,'cs3','666','陈帅3',1,'123789',1,'2021-12-23',now(),now());
3.2更新数据
update 表名 set 字段1=值1,字段2=值2,......[where 条件];
-- 1. 将 emp 表的ID为1员工 姓名name字段更新为 '张三'
update emp set name='张三' where id=1;
-- 2. 将 tb_emp 表的所有员工的入职日期更新为 '2010-01-01'
update emp set entrydate='2010-01-01';
-- 3. 将 tb_user 表的ID为1的用户年龄加5岁
update emp set age=18 where id=1;
3.3删除数据
delete from 表名 [where 条件];
-- 1. 删除 tb_emp 表中 ID为1的员工
delete from emp where id=1;
-- 2. 删除 tb_emp 表中的所有员工
delete from emp;
4.DQL语句
4.1基本查询
select 字段名 [as 别名] from 表名
-- 1. 查询员工所有信息
select id, username, password, name, gender, image, job, entrydate,
create_time, update_time
from tb_emp;
-- 2. 查询员工的姓名name、性别gender、入职日期entrydate
select name,gender,entrydate
from tb_emp;
-- 3. 查询员工的姓名name、性别gender、入职日期entrydate, 并将entrydate取别名为joindate
--- as 关键字可以省略
select name,gender,entrydate as joindate
from tb_emp;
-- 4. 查询所有员工的职位并去除重复值 -- distinct
select distinct job
from tb_emp;
4.2条件查询
select 字段名 from 表名 [where 条件]
-- 1. 查询 姓名 为 杨逍 的员工
select *
from tb_emp where name='杨逍';
-- 2. 查询在 id小于等于5 的员工信息
select *
from tb_emp where id<=5;
-- 3. 查询 没有分配职位 的员工信息 -- 判断 null , 用 is null
select *
from tb_emp where job is null ;
-- 4. 查询 有职位 的员工信息 -- 判断 不是null , 用 is not null
select *
from tb_emp where job is not null ;
-- 5. 查询 密码不等于 '123456' 的员工信息 <>, !=
select *
from tb_emp where password!='123456';
-- 6. 查询入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
select *
from tb_emp where entrydate between '2000-01-01' and '2010-01-01';
-- 7. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息
select *
from tb_emp where entrydate>='2000-01-01'and entrydate<='2010-01-01' and gender=2 ;
-- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息select *
from tb_emp where job in (2,3,4);
4.3聚合查询
- count(字段名):统计数量
- max(字段名):统计最大值
- min(字段名)统计最小值
- avg(字段名)统计平均值
- sum(字段名):对某一列求和
-- 1. 统计该企业员工数量 -- count(字段) -- 注意：该字段中的null值不参与聚合函数运算
select count(id)
from tb_emp;
-- 2. 统计该企业员工 ID 的平均值
select avg(id)
from tb_emp;
-- 3. 统计该企业最早入职的员工的入职日期
select min(entrydate)
from tb_emp;
-- 4. 统计该企业最近入职的员工的入职日期
select max(entrydate)
from tb_emp;
-- 5. 统计该企业员工的 ID 之和
select sum(id)
from tb_emp;
说明:聚合函数是对一列数据进行计算,null不参于计算
4.5分组查询
关键字:group by 字段名 [having 条件]
select 字段列表 from 表名 [where 条件] [group by 字段名 [having 分组后条件]]
-- 1. 根据性别分组统计男性和女性员工的数量 -- group by 字段名
select gender,count(*)
from tb_emp group by gender;
-- 2. 先查询入职时间在 '2015-01-01' (包含)以前的员工,并对结果根据职位分组,获取员工数量大于等于2的职位 -- group by 字段名 having 分组后条件
select job,count(*)
from tb_emp where entrydate<='2015-01-01'group by job having count(*)>=2;
4.6排序查询
关键字: order by 字段名 排序规则,字段名 排序规则...
select 字段列表 from 表名 [where 条件] [group by 字段名 having 分组后条件] order by 字段名 排序规则,字段名 排序规则,...
排序规则：asc表示升序，默认值可以不写。desc表示降序
-- ========== 排序查询 ==========
-- 1. 根据入职时间, 对员工进行升序排序 -- 默认升序, asc可以省略的
select *
from tb_emp order by entrydate asc ;
-- 2. 根据入职时间, 对员工进行降序排序 b
select *
from tb_emp order by entrydate desc ;
-- 3. 根据入职时间对公司的员工进行升序排序 ， 入职时间相同 , 再按照 ID 进行降序排序
select *
from tb_emp order by entrydate asc,update_time desc ;
4.7分页查询
关键字:limit 起始索引,每页条数
select 字段列表 from 表名 [where 条件] [group by 字段名 having 分组后条件] [order by 字段名 排序规则,字段名 排序规则,...] [limit 起始索引,每页条数 ]
查询第1页员工数据, 每页展示5条记录
select *
from tb_emp limit 0,5 ;
-- 2. 查询第2页员工数据, 每页展示5条记录
select *
from tb_emp limit 5,5 ;
-- 2. 查询第3页员工数据, 每页展示5条记录
select *
from tb_emp limit 10,5 ;
-- 2. 查询第4页员工数据, 每页展示5条记录
select *
from tb_emp limit 15,5 ;
补充:
-- ========== 案例 ==========
/*
案例1 : 查询员工
1.1 根据输入的 员工姓名、员工性别、入职时间 搜索满足条件的员工信息。
1.2 其中 员工姓名，支持模糊匹配； 性别 进行精确查询 ；入职时间 进行范围查询。
1.3 支持分页查询。
1.4 并对查询的结果，根据最后修改时间进行倒序排序。
*/
select *
from tb_emp where name like '张%'and gender=1 and entrydate between '2000-01-01'and'2015-12-31' order by update_time desc limit 0,10;
-- 案例2. 男性与女性员工的人数统计 (1:男性员工, 2:女性员工)
-- if(表达式, true value, false value)：当表达式为true时，取值true value；当表达式为false时，取值false value
select if(gender=1,'男性员工','女性员工') AS 性别,count(*)AS 人数
from tb_emp group by gender;
-- 案例3. 员工职位人数统计 (1:班主任, 2:讲师, 3:教研主管, 4:学工主管, 无职位)
-- case 表达式 when value1 then result1 [when value2 then value2 ...] [else result] end
select (case job
when 1 then '版主任'
when 2 then '讲师'
when 3 then '教研主管'
when 4 then '学工主管'
else '未分配职位'
end)AS '职位', count(*)AS '人数'
from tb_emp group by job;
5.多表设计
- 一对多,在多的一方添加外键指向另一方主键
- 一对一,在任意一方添加外键指向另一方主键,外键要unique
- 多对多,创建中间关系表,在中间关系表中至少要有两列作为外键指向多对多双方主键
6.多表查询
- 连接查询
内连接: 隐式:select 字段列表 from 表1,表2 where 条件 ...;
-- 隐式内连接
select e.name,d.name
from tb_emp as e ,tb_dept as d where d.id=e.dept_id;
显式:select 字段列表 from 表1 [inner] join 表2 on 条件;
-- 显式内连接
select e.name,d.name
from tb_emp e inner join tb_dept d on e.dept_id=d.id;
查询结果:两个表的交集数据,也就是主外键能连接的数据
外连接:
左外连接:select 字段列表 from 表名 left join 表2 on 条件;
-- 左外连接
select e.name,d.name
from tb_emp e left join tb_dept d on
e.dept_id=d.id;
右外连接:select 字段列表 from 表名 right join 表2 on 条件;
-- 右外连接
select e.name,d.name
from tb_dept d right join tb_emp e on
e.dept_id=d.id;
查询结果:一个表的全部数据和另一个表的交集数据
- 子查询
sql语句中嵌套select语句,叫做子查询
三种情况:
- 单行单列
select 字段列表 from 表名 where 字段名=(子查询);子查询结果:单行单列
- 需求1：查询 "教研部" 的所有员工信息
select *
from tb_emp where dept_id=(select id from tb_dept where name='教研部');
select *
from tb_emp
where entrydate>(select entrydate from tb_emp where name='方东白');
- 多行单列
select 字段列表 from 表名 where 字段名=(子查询);子查询结果:多行单列
from tb_emp where dept_id in(select id from tb_dept where name in ('教研部','咨询部'));
- 多行多列
select 字段列表 from 表名 where 字段名=(子查询);子查询结果:多行多列
-- 需求4：查询与 "韦一笑" 的
select *
from tb_emp e1,(select entrydate,job
from tb_emp where name='韦一笑' ) e2 where e1.entrydate=e2.entrydate and e1.job=e2.job;
-- 需求5：查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门名称
select *
from tb_emp where entrydate>='2006-01-01';
select e.id, e.username, e.password, e.name, e.gender, e.image, e.job, e.entrydate,d.name, e.create_time, e.update_time
from tb_dept d ,(select *
from tb_emp where entrydate>='2006-01-01') e
7.多表查询问题的解决思路
第一步,分析数据操作需要用到哪些表
第二步,分析使用什么查询方式(连接查询还是子查询)
浙公网安备 33010602011771号