这是Jane的第 19 篇笔记
![]()
Jane笔记前言：
mysql数据库是最开始入门学的，觉得很简单，其实越深入会越难！
![]()
day01
###数据库相关
###查询所有数据库
show databases;
###创建数据库
-格式：create database 数据库名；
create database db1;
###查看数据库详情
- 格式：show create database 数据库名；
show create database db1;
###创建数据库指定字符集
-格式：create database 数据库名 character set utf8; (utf8/gbk)
create database db2 character set gbk;
###删除数据库
-格式：drop database 数据库名；
drop database db2;
###使用数据库
-格式：use 数据库名；
use db1;
###表相关
- 必须先使用数据库后才能执行表相关SQL
###创建表
- 格式：create table 表名（字段1名，字段1类型，字段2名，字段2类型）；
create table person(name varchar(10),age int);
###查询所有表
- 格式：show tables;
###练习 创建学生表（student) 添加 姓名 年龄 语文（chinese) 数学（math) 外语（english)
create table student(name varchar(10),age int,chinese int,math int,english int);
###查询单个表详情
-格式：show create table person;
###数据表的引擎
- innodb:支持数据库的高级操作，如事物，外键等。
- myisam:只支持基础的增删改查。
###创建表的时候指定引擎和字符集
- 格式：create table 表名（字段1名，字段1类型，字段2名，字段2类型）
engine=myisam charset=gbk; (myisam/innodb) （gbk/utf8)
create table t_person(name varchar(10),age int) engine=myisam charset=gbk;
show create table t_person;
###查看表字段信息
- 格式：desc 表名；
desc person;
###删除表；
- 格式：drop table 表名；
drop table t_person;
###修改表
1.修改表名
- rename table 原名 to 新名；
2.修改引擎和字符集
- alter table stu engine=myisam charset=gbk;
3.添加表字段
-最后添加：alter table 表名 add 字段名 字段类型；
-最前面添加：alter table 表名 add 字段名 字段类型 first;
- 在xxx的后面添加：alter table 表名 add 字段名 字段类型 after 字段名；
- 测试:
在person最后面添加一个gender字符串类型，在最前面添加一个id整数，在name后面添加一个money
alter table person add gender varchar(2);
alter table person add id int first;
alter table person add money int after name;
4.删除字段
- 格式：alter table 表名 drop 字段名；
alter table person drop money;
5.修改字段名称和类型
- 格式：alter table 表名 change 原字段名 新字段名 字段类型；
alter table person change gender money int;
6.修改字段类型和位置
- 格式：alter table 表名 modify 字段名 字段类型 first/（after xxx);
alter table person modify money int after id;
###练习
###数据相关sql
- 准备保存数据的表：
create table student(id int,name varchar(10),age int,gender varchar(2));
1.插入数据
- 全表格式：insert into 表名 values(值1，值2，值3）；
insert into student values(1,'Tom',18,'男');
-指定字段格式：insert into 表名 （字段1，字段2，字段...) values(值1 ，值2...);
insert into student (id,name) value (2,'刘备');
- 批量插入：insert into 表名 values(值1，值2，值3）,(值1，值2，值3）,(值1，值2，值3）;
-批量指定字段格式：insert into 表名 （字段1，字段2，字段...) values(值1 ，值2...)，(值1 ，值2...) (值1 ，值2...)，(值1 ，值2...);
- insert into student values(3,'aa',23,'aaa'),(4,'bb',21,'bbb');
- insert into student values(3,'唐生'，20，'男'），(4,'唐四'，34，'男');
- insert into student (id,name，age) values (2,'刘备'),(3,'刘备'，35）;
2.查询数据
select*from student;
select name from student;
select name,age from student;
- 条件查询
select*from student where id20;
4.修改数据
- insert into student values(3,'傻子',20,'男’）;
- update student set age=500 where id=4;
- update student set age=15,gender='男' where id=6;
- update student set age=10;
###数据相关练习
1.创建hero表 id name 类型（type) money
2.插入以下几条数据
1 孙悟空 大爷 6888
2 诸葛亮 法师 18888
3 小乔 法师 5888
3.修改所有1888价格为2888
怎么访问别人写的数据库？
1.创建远程访问的root用户
- 'root'@'192.24.33'
2.数据库进行用户授权
truncate截断表？？
结构化查询语言。。。
技术术语：
database
schema
mysql中都指的是数据库
table表
row一行数据
record一条记录
column列
field字段
存储引擎
innodb(默认） 支持事物，外键，只有表级锁
memory
myisam
day02
###主键约束
- 什么是主键：用来表示数据唯一性的字段称为主键
- 约束：约束就是对表字段的限制约束
create table t1(id int primary key,name varchar(10));
insert into t1 values(1,'dd');//成功
insert into t1 values(1,'sd');//失败，id不能重复
insert into t1 values(unll,'dsd');//失败，id不能为null
自增 auto_increment
create table t2(id int primary key auto_increment,name varchar(10));
insert into t2 values(null,'lucy');
insert into t2 values(null,'hehheje');
insert into t2 values(10,'hdhh');
insert into t2 values(null,'lisdhh');
自增会在曾经出现的最大值的基础上+1
自增数值不会因为删除数据减少
使用delete删除整个表的数据时，自增数值不会清零
###注释 comment
- 创建表明字段的时候可以通过添加comment给字段添加注释
create table t3(id int primary key auto_increment comment '这是主键字段',age int coment '这是年龄字段');//////
mysql> create table t3(id int primary key auto_increment comment '哈哈哈哈',age int comment '男男女');
show create table t3;
###
- ` :用来修饰表名和字段名的，可以省去
- ' : 用来修饰字符串的
mysql> create table `t4`(`id` int,`name` varchar(10));
##数据冗余
- 如果表设计不够合理，随着数据量增多出现了大量的重复数据称为数据冗余，通过拆分表的形式解决冗余问题
练习：
##事物
create table person(id int primary key auto_increment,name varchar(10),money int);
insert into person values(null,'超人',50);
insert into person values(null,'钢铁侠',3000);
update person set money=5050 where id=1;
update person set money=25000 where id=2;
什么是事物：数据库中执行SQL语句的工作单元，此工作单元不可拆分，能够保证全部成功或全部失败。
查看数据库自动提交的状态
show variables like '%autocommit%';
设置自动提交的开关 0：关闭 1：开启
set autocommit=0/1;
1.mysql> update person set money=5050 where id=1;
2.先在当前窗口执行 select*from person 查看是否改变，给果是修改了因为查看的是内存中的数据
3.此时打开另一个终端，使用db2 然后 select*from person 查看是否改变，结果是没有改变因为此时查看的是数据库文件中的数据，没提交就不会改变
4.回到原窗口再执行转账的另一部分
mysql> update person set money=2500 where id=2;
###事物回滚 rollback;此指令会将数据库中的数据回滚到上次提交的点
步骤：
1.在关闭了自动提交的窗口，修改超人的钱，修改后查询，由于查询到的是内存中数据所有数据是修改过的，此时执行roollback,则数据会回滚到修改前的值
保存回滚点：savepoint s1;
回滚到某个回滚点：rollback to s1;
##SQL分类
###DDL Date Definition Language(数据定义语言）
- 包括：create,drop,alter,truncate
- 不支持事物，不能通过事物回滚 //删库到跑路
#truncate
- 删除表并创建一个相同的空表，此时表中的自增数值清零
- 格式：truncate table 表名；
- truncate:删除表创建新表 drop:删除表 delete:只删除数据
效率：drop>truncate>delete
###DML:Date Manipulation Language(数据操作语言）
- 包括：insert update dalete select(DQL)
-支持事物
###DQL:Data Query Language(数据查询语言）
- 只包括select
###TCL:Transaction Contral Language(事物控制语言）
- 包括：commit rollback savepoint rollback to
###DCL:Date Control Language(数据控制语言）
- 分配用户权限的相关sql
###数据库的数据类型
###整型
- 常用类型：int(m) bigint(m),m代表显示长度
create table t_int(num int(10) zerofill);
insert into t_int values(123);
select*from t_int;
###浮点型
- 常用类型：double(m,d) m代表总长度 d代表小数长度 76.232 m=5 d=3,
decimal(m,d):超高精度小数，当需要涉及超高精度运算的时候使用此类型
###固定长度
- char:固定长度 char(10) 'abc' 占10 优势：执行效率高 最大255
- varchar:长度可变 varchar(10) 'abc' 占3 优势：节省空间，用的比较多 最大65535，超过255建议用text
- test:长度可变 最大值65535 用于保存大文本
###日期类型
- date:只保存年月日
- time:只保存时分秒
- datetime:保存年月日时分秒，默认值为null，最大值 9999-12-31
- timestamp:保存年月日时分秒 保存距离1970年1月1日8点的毫秒数,默认值为当前时间，最大值2038年1月19号
create table t_date(d1 date,d2 time,d3 datetime,d4 timestamp);
insert into t_date values('2018-04-23',null,null,null);
insert into t_date values(null,'12:38:45','2018-05-12 12:38:33',null);
-个人电脑
show tables;
###is null 和 is not null
1.查询没有奖金的员工信息
select * from emp where comm is null;
2.查询有奖金的员工信息
select * from emp where comm is not null;
###别名
select ename from emp;
select ename as '名字' from emp;
select ename '名字' from emp;
select ename 名字 from emp;
###去重 distinselect
select job from emp;
- 去掉重复的数据
select distinct job from emp;
###比较运算符: > < >= 2000
order by a desc;
2.查询商品表中每个分类的平均单价，要求显示平均单价低于100的信息
select category_id,avg(price) a from t_item
group by category_id
having a199999;
4.查询emp表中 工资在1000-3000之间的员工，每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门信息，按照平均工资升序排序
select deptno,sum(sal),avg(sal) a from emp
where sal between 1000 and 3000
group by deptno
having a>=2000
order by a;
5.查询emp表中名字不是以s开头，每个职位的人数，工资总和，最高工资，过滤掉平均工资高于3000的职位，根据人数升序排序，如果一致则根据工资总和降序排序
select job,count(*) c,sum(sal) s,max(sal) from emp
where ename not like 's%'
group by job
having avg(sal)(select avg(sal) from emp where deptno=20);
3.查询和Jones做相同工作的员工信息
select job from emp where ename='jones'
select * from emp where job=(select job from emp where ename='jones');
4.查询最悲惨员工（工资最低 ）的同事们的信息
select min(sal) from emp;//得到最低工资
select deptno from emp where sal=(select min(sal) from emp);//通过最低工资找到最低工人的部门编号
select * from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp));//通过部门编号得到此部门所有的人
select * from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp))and sal!=(select min(sal)from emp);//把最低工资的人排除掉
5.查询最后入职的员工信息
select * from emp where hiredate=(select max(hiredate) from emp);
6.查询King这个哥们儿的部门名称是什么（需要用到dept表）
select dname from dept where deptno=(select deptno from emp where ename='King');
7.查询名字不包含a 并且工资高于10号部门平均工资的员工信息
select * from emp where ename not like '%a%' and sal>(select avg(sal) from emp where deptno=10);
8.查询有员工的部门详情（需要用到部门表）
select distinct deptno from emp;
select * from dept where deptno in( select distinct deptno from emp);
9.（扩展题）：查询平均工资最高的部门信息
select avg(sal) a from emp group by deptno order by a desc limit 0,1;//得到最高的平均工资(平均工资降序取第一个)
select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1);//通过最高的平均工资得到对应的部门编号
select * from dept where deptno in(select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1));//通过部门编号去部门表查询部门详情
子查询可以写在的位置
1.写在where或having 后面 当查询条件的值
2.写在创建表的时候
create table t_emp as(select * from emp where sal create table userinfo(nick varchar(10),qq varchar(10),phone varchar(10),uid int);
mysql> insert into user values(null,'libai','admin')，(null,'liubei','123456');
mysql> insert into userinfo values('李白','6666888','13838383388',1),('刘备','333444','133333333',2);
查询李白的用户名和密码
select u.username,u.password
from user u join userinfo ui
on u.id=ui.uid
where ui.nick='李白';
查询每个用户的用户名和昵称
select u.username,ui.nick
from user u join userinfo ui
on u.id=ui.uid;
查询 liubei 的所有数据
select*
from user u join userinfo ui
on u.id=ui.uid
where u.username='liubei ';
###一对多
- 什么是一对多：AB两张表 A表中的一条数据对应B表中的多条，同时B表中的一条数据对应A表中的一条，此时两张表的关系为一对多。
- 应用场景：部门表和员工表，分类表和商品表，用户表和收获地址
- 如何建立关系：在两张表中多的表中添加外键指向另外一张表的主键
select e.name,e.sal,d.name
from emp e join dept d
on e.deptid=d.id;
select e.sal,d.loc
from emp e join dept d
on e.deptid=d.id
where e.name='路飞';
select e.*
from emp e join dept d
on e.deptid= d.id
where d.name='英雄部';
###多对多
- 什么是多对多：AB两张表：A表中一条数据对应B表中多条数据，同时B表中一条数据对应表中的多条数据，就是多对多
create table teacher(id int primary key auto_increment,name varchar(10));
create table student(id int primary key auto_increment,name varchar(10));
create table t_s(tid int,sid int);
insert into teacher values(null,'传奇老师'),(null,'苍老师');
insert into student values(null,'刘德华'),(null,'张学友'),(null,'小明'),(null,'小红');
insert into t_s values(1,1),(1,2),(2,1),(2,2),(2,3),(2,4);
查询每个学生对应的老师姓名
select s.name,t.name
from student s join t_s ts
on s.id=ts.sid
join teacher t
on t.id=ts.tid;
查询刘德华的老师姓名
select s.name,t.name
from student s join t_s ts
on s.id=ts.sid
join teacher t
on t.id=ts.tid
where s.name='刘德华';
查询苍老师的学生信息
select s.name
from student s join t_s ts
on s.id=ts.sid
join teacher t
on t.id=ts.tid
where t.name='苍老师';
###自关联
- 什么是自关联：当前表的外键指向自己表的主键这种成为自关联
- 应用场景：用于保存有层级关系，并且不确定有多少层的数据，如：员工和上级领导，部门和上级领导，分类和上级分类。
- 可以保存一对一或一对多的数据
##连接方式和关联关系的区别
- 连接方式：包括等值连接 内连接 外连接，是指关联查询的查询方式
- 关联关系：指表设计时两张表之间存在的逻辑关系包括 一对一，一对多和多对多
###表设计案例：权限管理
1.创建三张主表，用户表user:id,name 角色表role:id,name 权限表module:id,name
2.创建两张关系表：用户-角色关系表 u_r:uid,rid 角色-权限关系表r_m:rid,mid
create table user(id int primary key auto_increment,name varchar(10));
create table role(id int primary key auto_increment,name varchar(10));
create table module(id int primary key auto_increment,name varchar(10));
create table u_r(uid int,rid int);
create table r_m(rid int,mid int);
insert into user (name) values('刘德华'),('张学友'),('凤姐');
insert into module (name) values('男浏览'),('男评论'),('男发帖'),('男删帖'),('女浏览'),('女评论'),('女发帖'),('女删贴');
insert into role (name) values('男会员'),('男管理员'),('女游客'),('女会员');
insert into r_m values(1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(2,4),(3,5),(4,5),(4,6),(4,7);
insert into u_r values(1,1),(2,2),(3,4),(3,1);
1.查询刘德华所拥有的权限名称
- 1）子查询法
- 得到刘德华的id
select id from user where name='刘德华';
- 通过用户id得到对应的角色
select rid from u_r where uid=(select id from user where name='刘德华');
- 通过角色id找到对应的权限id
select mid from r_m where rid in ( select rid from u_r where uid=(select id from user where name='刘德华'));
- 通过权限id得到权限的名字
select name from module where id in( select mid from r_m where rid in ( select rid from u_r where uid=(select id from user where name='刘德华')));
-2)内连接法
select m.name
from user u join u_r ur
on u.id=ur.uid
join r_m rm
on rm.rid=ur.rid
join module m
on rm.mid=m.id
where u.name='刘德华';
2.查询拥有男浏览权限的用户有那些
select u.name
from user u join u_r ur
on u.id=ur.uid
join r_m rm
on rm.rid=ur.rid
join module m
on rm.mid=m.id
where m.name='男浏览';
3.查询每个用户拥有的权限名称
select u.name,m.name
from user u join u_r ur
on u.id=ur.uid
join r_m rm
on rm.rid=ur.rid
join module m
on rm.mid=m.id;
day06
面试作业：
mysql> create database db6;
create database db6 character set gbk/utf8;
mysql> use db6;
1.创建交易流水表trade(id,tim,money,type,pid)
create table trade(id int primary key auto_increment,time date,money int,type varchar(10),pid int);
2.创建任务表person(id,name,gender,rel)
create table person(id int primary key auto_increment,name varchar(10),gender varchar(5),rel varchar(5));
3.插入数据：
- 刘德华 男 亲戚 收 现金 500 给他发了50 现金
- 杨幂 女 亲戚 收100 给她发了2000 微信
- 马云 男 同事 收50000 给他发了10 支付宝
- 特朗普 男 朋友 收1000 给他发了100 微信
- 貂蝉 女 朋友 给她发了2000 现金
insert into person values(null,'刘德华','男','亲戚'),(null,'杨幂','女','亲戚'),(null,'马云','男','同事'),(null,'特朗普','男','朋友'),(null,'貂蝉','女','朋友');
mysql> insert into trade values(null,'2018-3-10',500,'现金',1),(null,'2018-3-11',-50,'现金',1),(null,'2018-3-12',100,'微信',2),(null,'2018-3-13',-2000,'微信',2),(null,'2018-3-14',5000,'支付宝',3),(null,'2018-3-15',-10,'支付宝',3),(null,'2018-3-16',1000,'微信',4),(null,'2018-3-17',-100,'微信',4),(null,'2018-3-18',-2000,'现金',5);
1.统计2018年2月15日到现在的所有红包收益
select sum(money) from trade where time>str_to_date('2018年2月15号','%Y年%c月%d号');
2.查询2018年2月15日到现在 金额大于100 所有女性亲戚的名字和金额
select p.name,t.money
from trade t join person p
on t.pid=p.id
where time>str_to_date('2018年2月15号','%Y年%c月%d号')
and t.money not between -100 and 100
and p.gender='女'
and p.rel='亲戚';
3.查询三个平台分别收入的红包金额
select type, sum(money) from trade
where money>0
group by type;
###视图
- 什么时视图：数据库中存在的表和视图都是其内部的对象，视图可以理解成是虚拟的表，数据来自原表，视图本质上就是取代了一段sql语句
- 为什么使用视图：因为有些数据查询的sql语句比较长，每次书写比较麻烦，使用视图可以起到sql语句重用的作用，提高开发效率，可以隐藏敏感信息。
- 格式：create view 试图名 as 子查询；
- 创建10号部门员工的视图
create view v_emp_10 as (select * from emp where deptno=10);
类似//create table t_emp_10 as (select * from emp where deptno=10);
- 创建没有工资的员工的信息
create view v_emp_nosal as(select ename,job,deptno,mgr from emp);
练习：
1.创建部门为20号部门并且工资小于3000的视图
create view v_emp_20 as(select * from emp where deptno=20 and sal10));
insert into t4 values(2,4);
###外键约束
1.字段的值可以为null，可以重复，但是不能是不存在的值
2.被依赖的数据不能先删除
3.被依赖的表不能先删除
- 测试外键约束
1.创建部门表
create table dept(id int primary key auto_increment,name varchar(10));
create table emp(id int primary key auto_increment,name varchar(10),deptid int,constraint fk_dept foreign key(deptid) references dept(id));
- 格式：在创建表最后一个字段后面添加 constraint 约束名 foregin key (外键字段名称) references 被依赖的表名（被依赖的字段名）
- 插入数据
insert into dept values(null,'神仙'),(null,'妖怪');
- 测试：
insert into emp values(null,'悟空',1);//成功
insert into emp values(null,'赛亚人',3);//失败！不存在的数据
delete from dept where id=1;//失败 被关联的数据
###索引
- 什么是索引：索引是数据库中用来提高查询效率的技术，类似于目录
- 为什么使用：如果不使用，查询数据时回依次遍历每一个保存数据的磁盘块，直到找到目标数据为止，使用索引后，磁盘块会以树桩结构保存，查询数据时回大大降低磁盘块的访问量，从而提高查询效率。
- 原理图 见ppt
![ ] （01.png）
- 索引是越多越好吗？
因为索引会占用储存空间，只对常用的查询字段创建索引。
-有索引就一定好吗？
如果数据量小的话，添加索引反而会降低查询效率
-索引的分类（了解）
1.聚集索引（聚簇索引）：通过主键创建的索引称为聚集索引，一个表只能有一个聚集索引，添加了主键约束的表会自动创建聚集索引，聚集索引的树状结构中保存了数据。
2.非聚集索引：通过非主键字段创建的索引称为非聚集索引，一个表可以有多个，树状结构中不保存数据只保存指针（磁盘块地址）。
- 如何创建非聚集索引
###导入索引测试数据
1.linux系统 把文件解压到桌面
在终端执行：
- 学生机（linux）source /home/soft01/桌面/item_backup.sql;
2.window系统 把文件解压到D盘根目录
在命令中执行
source d:/item_backup.sql;
- 测试：
show tables;//看 是否有item2
select count(*) from item2;//查看是否是172万多条数据
select * from item2 where title='100';//看查询时间是多少？ 0.85秒
###如何创建索引
- 格式：create index 索引名 on 表名(字段名（长度));
-给title字段添加索引
create index index_item2_title on items(title);
- 再次查询看时间多少？提高近30倍
select * from item2 where title='100';//看查询时间是多少？ 0.02秒
###查看索引
show index from item2;
###删除索引
- 格式：drop index 索引名称 on 表名;
drop index index_item2_title on item2;
###复合索引
- 通过多个字段创建的索引称为复合索引
- 应用场景：频繁使用某几个字段作为查询条件的时候，可以为这几个字段创建复合索引
- 创建 标题和价格的索引
create index index_item2_title_price on item2(title,price);
###索引总结
1.索引是提高查询效率的技术
2.索引占存储空间不是越多越好
3.数据量小时使用索引会较低效率
4.聚集索引：只有一个 添加主键约束后自动创建，保存数据
5.非聚集索引：多个 非主键字段创建 没有数据只保存指针
6.尽量不要在频繁修改的表上面添加索引
###事物
- 数据库中执行sql语句的工作单位，不可拆分，可以保证同一业务中的所有sql语句全部成功或全部失败
- 事物的ACID特性
- Atomicity:原子性，原子不可拆分，保证全部成功，全部失败。
- Consistency:一致性，从一个一致状态到另一个一致状态。
- Isolation: 隔离性，多个事物之间互不影响
- Durability:持久性，事物完成后数据提交到数据库文件中，持久保存。
###事物相关指令
show variable like '%autocommit%';
set autocommit=0/1;
commit;
rollback;
savepoint s1;
rollback to s1;
SQL回顾
###day01
1.数据库相关
- show databases;
- create database db1 character set utf8/gbk;
- show create database db1;
- use db1;
2.表相关
- create table t1 (id int ,name varchar(10))
- show tables;
- show create table t1;
- desc t1;
- drop table t1;
- rename
3.数据相关
- insert into t1 value(值1，值2) values(值1，值2),(值1，值2);
- select * from t1;
- update t1 set age=29 where id=10;
- delete from t1 where id=10;
###day02
- comment
- ` 和'
- 冗余
- 重复数据
- 拆分表
- sql分类
1.DDL:数据定义语言
2.DML:数据操作语言
3.DQL:数据查询语言
4.TCL:事物控制语言
5.DCL:数据控制语言
- 数据类型
1.整数：
2.浮点数
3.字符串
4.日期
5.其它
###day03
1.is null 和 is not null
2.别名
3.distinct
4.比较运算符:< > >= 1000 and age=10;
100 10 90 190
100 5 105
inner join 交集
left join
right join
1 a 1 q
2 b 2 w
3 c 3 e
4 d 4 r
5 e 6 t
case when
select id,name,sex(
case
sex
when 1 then 'man'
when 2 then 'women'
else '人妖'
end)性别（别名）
from user
select eneme,job,sal
decode(
job,
'boss',sal*1.2,
'manager',sal*1.1,
'salesman',sal*1.05，
sal)from emp
完
程序媛笔记一个认真记笔记的无聊有料的公号我的愿望是成为一名优秀的程序媛！！！
|
|
转播
