数据库学习随笔

数据库基本了解

DataBaseManagementSystem 数据库管理系统(软件) ，用于管理保存数据的文件集合，用于和程序员进行交互，常见的DBMS有：Oracle MySQL DB2 SQLServer Sqlite ，DBMS具备网络访问能力

数据库的分类

关系型数据库：
经过数学理论验证可以将现实生活中的各种关系保存到数据库，这种就称为关系型数据库。保存数据以表为单位

非关系型数据库：
一般都是为了解决某些特定场景的问题比如：缓存，高并发访问，Redis数据库（以key-value形式保存数据）

常见的关系型数据库

MySql：
属于Oracle公司的产品，08被Sun公司收购，09年Sun公司被Oracle收购，开源免费，被收购后发布5.5版本使用Oracle的部分技术，性能提高了30%以上，用户量增多，计划把MySQL闭源 原MySQL程序员离开Oracle创建了MariaDB 老板女儿Maria。市场排名第一

Oracle: 排名第二，闭源 性能最高 收费最贵

DB2： IBM公司产品 闭源项目

SQLServer： 微软公司产品 闭源

Sqlite： 轻量级数据库，安装包几十k，应用在嵌入式设备或移动设备上

数据库的基本操作

打开终端链接数据库

在终端执行：

mysql -uroot -p

然后输入密码

和数据库相关的SQL

查询所有数据库

show databases;

创建数据库指定字符集

create database 数据库名 character set gbk/utf8;

产看数据库详情

show create database 数据库名;

删除数据库

drop database 数据库名;

使用数据库

use 数据库名;

和表相关的SQL

查询所有表

show tables;

创建表时指定表的引擎和字符集

create table 表名(字段1名 字段1的类型,字段2名 字段2的类型,.....) engine=myisam charset=gbk;

表的引擎

innodb： 支持数据库的高级操作如：外键、事务等，默认引擎

myisam： 只支持基础的增删改查操作

查看表的详情

show create table 表名;

查看表的字段

desc 表名;

删除表

drop table 表名;

修改表

1. 修改表名

  rename table 原名 to 新名;

2. 修改表的引擎和字符集

  alter table 表名 engine=myisam/innodb charset=utf8/gbk;

3. 添加表字段

最后面添加：

  alter table 表名 add 字段名 字段类型;

最前面：

   alter table 表名 add 字段名 字段类型 first;

xxx的后面：

   alter table 表名 add 字段名 字段类型 after xxx;

4. 删除表字段

   alter table 表名 drop 字段名;

5. 修改表字段的名字和类型

   alter table 表名 change 原字段名 新字段名 新字段类型;

6. 修改表字段的类型和位置

   alter table 表名 modify 字段名 类型 位置;
   alter table hero modify age int first(after xxx);

数据相关

插入数据

全字段插入：

insert into emp values(3,'刘备',28,6000),(4,'张飞',20,5000),(5,'关羽',25,9000);

指定字段插入数据：

insert into emp (name,age) values('悟空',500),('八戒',400),('沙僧',200);

查询数据

查询全部数据的全部字段信息

select * from 表名;

查询指定信息

select name,age from 表名;

添加查询条件

select * from 表名 where age<25;

修改数据

修改Tom的年龄为33

update 表名 set age=33 where name='Tom';

删除数据

删除id=1的员工

delete from 表名 where id=1;

PS:中文字符问题

set names gbk;

主键约束 primary key

主键： 用于表示数据唯一性的字段称为主键

约束： 是给表字段添加的限制条件

主键约束： 限制主键字段值不能重复并且非空 (唯一且非空)

自增： auto_increment

1. 当字段值为null的时候值会自己增长

2. 自增字段值也可以手动赋值

3. 增长规则：从曾经出现的最大值基础上+1

4. 自增数值只增不减 (delete清空表 自增数值并不清零)

主键约束+自增 唯一且非空

primary key auto_increment

注释 comment

创建表声明字段的时候给字段添加的介绍

create table 表名(id int primary key auto_increment comment '这是一个主键',name varchar(10) comment '这是员工的姓名');

`和’

`： 用于修饰表名和字段名 可以省略

’： 用于修饰字符串

数据冗余

如果表设计不够合理，保存大量数据的同时可能随之会出现大量重复数据，这些重复数据的现象就称为数据冗余，通过拆分表的形式解决冗余问题

事务

什么是事务：事务是数据库中执行SQL语句的最小工作单元，可以保证事务内的多条SQL语句要么全部成功，要么全部失败。

事务的ACID特性：

1. Atomicity： 原子性， 最小不可拆分 保证全部成功或全部失败

2. Consistency： 一致性，从一个一致状态到另外一个一致状态

3. Isolation：隔离性，多个事务之间互相隔离互不影响

4. Durability:持久性， 事务提交后 数据持久保存到数据库文件中

查看数据库自动提交的状态：

show variables like '%autocommit%';

关掉自动提交 0关闭 1开启：

set autocommit=0;

测试转账：

create table person(id int, name varchar(10),money int);
insert into person values(1,'超人',500),(2,'钢铁侠',10000);

关掉自动提交：

set autocommit=0;

1. 先让超人+2000

   update person set money=2500 where id=1;

2. 开启另一个终端 验证 此时数据库文件中的数据并没改掉

3. 让钢铁侠-2000

   update person set money=8000 where id=2;

4. 执行提交

   commit;

回滚：rollback

将内存中的修改回滚到上次提交（commit）的点

update person set money=100 where id=1;
rollback;

保存回滚点：savepoint

update person set money=100 where id=1;
savepoint s1;
update person set money=200 where id=1;
savepoint s2;
rollback to s1;

SQL分类

1. DDL 数据定义语言 包括：create alter drop truncate 不支持事务

2. DML 数据操作语言 包括：insert delete update select(DQL) 支持事务

3. DQL 数据查询语言 包括：select

4. TCL 事务控制语言 包括：commit rollback savepoint 。。。

5. DCL 数据控制欲要 分配用户权限相关SQL

数据类型

1. 整数： int(m) 和bigint m代表显示长度 需要结合zerofill使用

2. 浮点数：double(m,d) m代表总长度 d代表小数长度 decimal超高精度浮点数

3. 字符串：char固定长度 执行效率高 最大255 varchar可变长度 节省空间 最大65535超高255建议使用text

4. 日期： date保存年月日 time保存时分秒 datetime：9999-12-31 默认为null timestamp：最大2038-1-19 默认值为当前时间

is null 和 is not null

查询没有年龄信息的所有员工信息

select * from 表明 where age is null;

查询有年龄信息的所有员工信息

select * from 表明 where age is not null;

别名

将查询到的员工姓名name改成‘姓名’

select ename from 表名;
select ename as '姓名' from 表名; 
select ename '姓名' from 表名;
select ename 姓名,sal 工资 from 表名;

去重 distinct

查询emp表中出现的所有职位job

select distinct job from 表名;

比较运算符 >,<,>=,<=,=,!=和<>

1. 查询工资小于等于1600的所有员工的姓名和工资

   select ename,sal from 表名 where sal<=1600;

2. 查询部门编号是20的所有员工姓名、职位和部门编号deptno

   select ename,job,deptno from 表名 where deptno=20;

3. 查询职位是manager的所有员工姓名和职位

   select ename,job from 表名 where job='manager';

4. 查询部门不是10号部门的所有员工姓名和部门编号(两种写法)

   select ename,deptno from 表名 where deptno!=10;
   
   select ename,deptno from 表名 where deptno<>10;

5. 查询商品表t_item中单价price等于23的商品信息

   select title,price from 表名 where price=23;

6. 查询商品表中单价不等于8443的商品信息

   select * from 表名 where price!=8443;

and 和 or

and等效java中的&&

or等效java中的||

查询工资大于2000并且是10号部门的员工信息

select * from 表名 where sal>2000 and deptno=10;

in

查询emp表中工资是5000，1500，3000的员工信息

select * from 表名 where sal=5000 or sal=1500 or sal=3000;
select * from 表名 where sal in(5000,1500,3000);

between x and y 包含x和y

查询工资在2000到3000之间的员工姓名和工资

select ename,sal from 表名 where sal>=2000 and sal<=3000;
select ename,sal from 表名 where sal between 2000 and 3000;

模糊查询 like

_：代表单个未知字符

%：代表0或多个未知字符

举例：

1. 以a开头的字符串 a%

2. 以m结尾 %m

3. 包含x %x%

4. 第二个字符是a _a%

5. 倒数第三个字母是m %m__

6. 以a开头并且倒数第二个字母是b a%b_

案例：

1. 查询名字中包含a的所有员工姓名和工资

select ename,sal from emp where ename like '%a%';

2. 查询标题中包含记事本的商品标题及商品价格

select title,price from t_item where title like '%记事本%';

3. 查询单价低于100的记事本(title包含记事本)

select * from t_item where title like '%记事本%' and price<100;

4. 查询单价在50到200之间的得力商品(title包含得力)

select * from t_item where price between 50 and 200 and title like '%得力%';

5. 查询商品分类为238和917的商品信息

select * from t_item where category_id in(238,917);

6. 查询有赠品的商品信息 （卖点sell_point中包含赠字）

select * from t_item where sell_point like '%赠%';

7. 查询有图片image的得力商品信息

select * from t_item where image is not null and title like '%得力%';

8. 查询和得力无关的商品信息(title不包含得力)

select * from t_item where title not like '%得力%';

9. 查询价格在50到100以外的商品信息

select * from t_item where price not between 50 and 100;

排序 order by

order by 关键字， by后面写排序的字段名称 默认是升序， asc升序 desc降序

1. 查询所有员工的姓名和工资按照工资升序排序

   select ename,sal from emp order by sal;

分页查询 limit

limit 跳过的条数,请求的数量（每页的数量）

1. 查询员工表中工资降序的第3页的4条数据

   select * from emp order by sal desc limit 8,4;

concat()函数

把concat内部的参数拼接到一起

1. 查询员工姓名和工资，要求工资单位是元

   select ename,concat(sal,'元') 工资 from emp;
   

数值计算 + - * / %（mod(7,2)等效 7%2）

1. 查询商品表中每个商品的单价，库存及总价值（单价*库存）

select price,num,price*num from t_item;

2. 查询员工表中每个员工的姓名，工资，及年终奖(五个月的工资)

select ename,sal,sal*5 年终奖 from emp;

日期相关函数

1. 获取当前的年月日时分秒

select now();

2. 获取当前的日期 current

select curdate();

3. 获取当前的时间

select curtime();   

4. 从年月日时分秒中提取年月日

select date(now());

5. 从年月日时分秒提取时分秒

select time(now());

6. 从年月日时分秒中提取时间分量 年 月 日 时 分 秒

extract(year from now())

extract(month from now())

extract(day from now())

extract(hour from now())

extract(minute from now())

extract(second from now())

select extract(year from now());

查询员工表中的所有员工姓名和入职的年份

select ename,extract(year from hiredate) from emp;

7. 日期格式化 date_format(时间,格式)

%Y:四位年 2018
%y:两位年 18
%m:两位月 05
%c:一位月 5
%d:日
%H: 24小时
%h: 12小时
%i: 分
%s: 秒
select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');

查询商品名称和商品的上传日期(格式：x年x月x日)

select title,date_format(created_time,'%Y年%m月%d日') from t_item; 

把非标准的日期字符串转成标准的时间格式 str_to_date(时间字符串,格式)

14.08.2018 08:00:00 
select str_to_date('14.08.2018 08:00:00','%d.%m.%Y %H:%i:%s');

ifnull(x,y)函数

age=ifnull(x,18) 如果x的值为null 则age=18 如果不为null则 age=x

1. 修改员工表中奖金为null的值为0

update emp set comm=ifnull(comm,0);

聚合函数：sum(),max(),min(),avg(),count(*)

用于对多条数据进行统计

1. 求和 sum(字段名)

查询emp中10号部门的工资总和

select sum(sal) from emp where deptno=10;

2. 平均值 avg(字段名)

查询emp表中所有员工的工资平均值

select avg(sal) from emp;

3. 最大值 max(字段名)

查询30号部门的员工的最高奖金

select max(comm) from emp where deptno=30;

4. 最小值 min(字段名)

查询商品表中价格最便宜的商品的单价

select min(price) from t_item;

5. 统计数量 count(字段名) 一般使用count(*)

统计30号部门有多少人

select count(*) from emp where deptno=30;

和字符串相关函数

1. 获取字符串的长度 char_length(str);

​ 获取所有员工的姓名和姓名的字符长度

select ename,char_length(ename) from emp;

2. 获取字符串在另外一个字符串中出现的位置 instr(str,substr)

select instr('abcdefg','d');

3. 插入字符串 insert(str,start,length,newStr)

select insert('abcdefg',3,2,'m');  //abmefg

4. 转大写 转小写

select upper('abc'),lower('NBA');

5. 左边截取和右边截取

select left('abcdefg',2) , right('abcdefg',2);

6. 去两端空白

select trim(' a b ');

7. 截取字符串

select substring('abcdefg',3,2);

8. 重复 repeat(str,count)

select repeat('ab',2); 

9. 替换 replace(str,old,new)

select replace('This is mysql','my','your');

10. 反转 reverse(str)

select reverse('abc');

数学相关函数

1. 向下取整 floor(num)

select floor(3.84);  //3

2. 四舍五入 round(num);

select round(3.84); // 4
round(num,m) m代表小数位数
select round(3.84567,3); //3.846

3. 非四舍五入 truncate(num,m)

select truncate(3.84567,3);

4. 随机数 rand() 0-1 5-10 0-5

select floor(rand()*6) + 5;
3-8  0-5    select floor(rand()*6) +3;

分组查询

分组查询通常和聚合函数结合使用，以组为单位进行统计

一般情况下，题目中每个xxx 就在group by后面写xxx

having

where后面只能写普通字段的条件不能写聚合函数

having后面可以写普通字段条件也可以写聚合函数，但是推荐在having后面只写聚合函数

having写在group by 的后面

select * from 表名 where … group by xxx having … order by … limit …;

1. 查询每个部门的平均工资，要求平均工资大于2000

   select deptno,avg(sal) a from emp 
   group by deptno having a>2000;

2. 查询每个分类的平均单价，过滤掉平均单价低于100的。

   select category_id,avg(price) a from t_item 
   group by category_id
   having  a>=100;

子查询

1. 查询emp表工资最高的员工信息

   select max(sal) from emp;//5000
   select * from emp where sal=5000;

子查询：

   select * from emp where sal=(select max(sal) from emp);

2. 查询emp表中工资高于平均工资的员工信息

   select * from emp where sal>(select avg(sal) from emp);

子查询可以写在什么位置

1. 写在where或having后面 当做查询条件的值

2. 写在创建表的时候 把查询结果保存成一张新的表

​ create table emp_20 as (select * from emp where deptno=20);

3. 写在from后面 当成一个虚拟表 *必须有别名*

select * from emp where deptno=20;
	
select ename,sal from (select * from emp where deptno=20) newtable;

关联查询

同时查询多张表的数据称为关联查询

1. 查询每一个员工的姓名和对应的部门名称

select e.ename,d.dname

from emp e,dept d

where e.deptno=d.deptno;

2. 查询在new york工作的员工信息

select *

from emp e,dept d

where e.deptno=d.deptno 

and d.loc='new york';

3. 查询商品标题和所对应的分类名称

select i.title,c.name

from t_item i,t_item_category c

where i.category_id=c.id;

笛卡尔积

如果关联查询不写关联关系则结果为两张表的乘积，这个乘积称为笛卡尔积。

笛卡尔积为一种错误的查询结果，切记工作中不要出现

等值连接和内连

等值连接和内连接查询到的内容一样，都为两张表中有关联关系的数据(交集部分)

等值连接：

select * from A,B where A.x=B.x and A.age=18;

内连接：

select * from A [inner] join B on A.x=B.x where A.age=18;

外连接

内连接和等值连接查询到的是交集部分的数据，外连接查询到的是某一张表的全部数据+另外一张表的交集数据

左/右外连接：

select * from A left/right join B on A.x=B.x where A.age=18;

关联查询总结

关联查询的查询方式： 等值连接 内连接和外连接

如果想查询的数据为两张表的交集数据使用等值连接或内连接(推荐)

如果查询的数据是一张表的全部数据和另外一张表的交集数据则使用外连接

表设计之关联关系

一对一

什么是一对一关系： 有AB两张表，其中A表的一条数据对应B表的一条数据，同时B表的一条数据也对应A表中的一条数据

应用场景：
用户表和用户信息扩展表、商品表和商品信息扩展表

外键：
表中用于建立关系的字段称为外键，一张表有可能有多个外键，但只会有一个主键

如何建立关系：
在从表中添加外键指向主表的主键

练习： 创建表保存以下数据 表名 user 和 userinfo

user

用户名	密码
wukong	abcd
wzt	admin
Superman	123456

userinfo

昵称	性别	地址
悟空	男	花果山
武则天	女	大陆
超人	男	铁岭

create table user(id int primary key auto_increment,username varchar(10),password varchar(10));

create table userinfo(userid int,nick varchar(10),gender varchar(5), loc varchar(20));

insert into user values(null,'wukong','abcd'),(null,'wzt','admin'),(null,'superman','123456');

insert into userinfo values(1,'悟空','男','花果山'),(2,'武则天','女','大陆'),(3,'超人','男','铁岭');

1. 查询每个用户名对应的昵称

select u.username,ui.nick
from user u join userinfo ui
on u.id=ui.userid;

2. 查询超人的用户名

select u.username

from user u join userinfo ui

on u.id=ui.userid where ui.nick='超人';

3. 查询性别是男的用户名和密码

select u.username,u.password

from user u join userinfo ui

on u.id=ui.userid where ui.gender='男';

4. 查询是否存在 用户名：wukong 密码：abc的用户 (查询符合条件的数据条数)

select count(*) from user where username='wukong' and password='abc';

一对多

什么是一对多：
有AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的一条

应用场景：
用户表和部门表，商品表和分类表

如何建立关系：
在多的一端添加外键指向另外一张表的主键

练习：创建emp表和dept表

create table emp(id int primary key auto_increment,name varchar(10),deptid int);

create table dept(id int primary key auto_increment,name varchar(10));

insert into dept values(null,'神仙'),(null,'妖怪');

insert into emp values(null,'猪八戒',1),(null,'白骨精',2),(null,'蜘蛛精',2);

1. 查询每个员工的姓名和对应的部门名称

select e.name,d.name

from emp e join dept d

on e.deptid=d.id;

2. 查询妖怪部的员工姓名

select e.name

from emp e join dept d

on e.deptid=d.id

where d.name='妖怪';

多对多

什么是多对多：
有AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的多条

应用场景：
老师表和学生表

如何建立关系：
通过第三张关系表保存两张主表的关系

练习：创建老师表，学生表和关系表

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);

1. 往以上表中保存苍老师的学生小刘和小丽，传奇老师的学生小刘，小王和小丽

insert into teacher values(null,'苍老师'),(null,'传奇老师');

insert into student values(null,'小刘'),(null,'小王'),(null,'小丽');

insert into t_s values(1,1),(1,3),(2,1),(2,2),(2,3);

2. 查询每个学生姓名和对应的老师姓名

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;

3. 查询苍老师的学生都有谁

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='苍老师';

4. 查询小丽的老师是谁

select 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='小丽';

自关联

在当前表中添加外键外键的值指向当前表的主键，这种关联方式称为自关联

create table person(id int primary key auto_increment,name varchar(10),mgr int);
保存以下数据： 如来->唐僧->悟空->猴崽子
insert into person values(null,'如来',null),(null,'唐僧',1),
(null,'悟空',2),(null,'猴崽子',3);

1. 查询每个人的名字和上级的名字

select p.name,m.name 上级

from person p left join person m

on p.mgr=m.id;

表设计案例：权限管理

实现权限管理功能需要准备三张主表和两张关系表

创建表：

create table user (id int,name varchar(10));

create table role (id int,name varchar(10));

create table module (id int,name varchar(10));



create table u_r (uid int, rid int);

create table r_m (rid int, mid int);

插入数据：

insert into user values(1,'刘德华'),(2,'凤姐');

insert into role values(1,'男游客'),(2,'男会员'),(3,'女游客'),(4,'女管理员');

insert into module values(1,'男浏览'),(2,'男发帖'),(3,'女浏览'),(4,'女发帖'),(5,'女删帖');

保存角色和权限的关系：

insert into r_m values(1,1),(2,1),(2,2),(3,3),(4,3),(4,4),(4,5);

保存用户和角色的关系 刘德华男会员和女游客 凤姐：女管理员和男游客

insert into u_r values(1,2),(1,3),(2,1),(2,4);

1. 查询每个用户的权限有哪些

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 m.id=rm.mid;

2. 查询凤姐的权限

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 m.id=rm.mid where u.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 m.id=rm.mid where m.name='男浏览';

练习

1. 设计表

交易时间、交易金额、交易类型、姓名、性别、关系

流水表：

create table trade(id int primary key auto_increment,time date,money int,type varchar(5),person_id int);

人物表：

create table person(id int primary key auto_increment,name varchar(10),gender varchar(5),rel varchar(5));

如果考虑数据冗余 可以把交易类型 和关系 单独拆分到一张表当中

2. 插入数据

insert into person values(null,'刘德华','男','亲戚'),
(null,'杨幂','女','亲戚'),(null,'马云','男','同事'),
(null,'特朗普','男','朋友'),(null,'貂蝉','女','朋友');

insert into trade values(null,'2018-08-20',-20,'微信',1),

(null,'2018-04-20',500,'现金',2),

(null,'2018-05-10',-50,'现金',2),

(null,'2018-05-20',50000,'支付宝',3),

(null,'2018-06-20',-5,'支付宝',3),

(null,'2018-06-21',2000,'微信',4),

(null,'2018-08-20',-20000,'微信',5);

3. 统计2018年2月15号到现在的所有红包收益

select sum(money) from trade where time>str_to_date('2018年2月15号','%Y年%c月%d号');

4. 查询2018年2月15号到现在金额大于100，所有女性亲戚的名字和红包金额

select p.name,t.money

from trade t join person p

on t.person_id=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='亲戚';

5. 查询三个平台分别收入的红包金额

select type,sum(money) from trade 

where money>0 

group by type;

视图

数据库中包含多种对象，表和视图都是数据库中的对象，视图可以理解成一张虚拟的表，视图本质就是取代了一段sql查询语句

为什么使用视图：
因为有些数据的查询需要写大量的SQL语句，每次书写比较麻烦，通过使用视图相当于把当了的SQL查询语句进行保存，下次从视图中查询就不用再次书写大量SQL语句，从而提高开发效率。隐藏敏感字段

视图格式：

create view 视图名 as (子查询);

create view v_emp_10 as (select * from emp where deptno=10);

delete from emp where sal=1300;

select * from v_emp_10;

1. 创建一个没有工资的视图

create view v_emp_nosal as (select empno,ename,comm,mgr from emp);

2. 创建视图，视图中显示每个部门的工资总和，平均工资，最高工资，最低工资

create view v_emp_info as (select deptno,sum(sal),avg(sal),max(sal),min(sal) from emp group by deptno);

视图的分类：

简单视图：
创建视图的时候不包含：去重、分组、函数、关联查询的视图称为简单视图，可以对视图中的数据进行增删改查

复杂视图：
和简单视图相反，只能进行查询操作

简单视图的增删改操作 操作方式和操作table一样

插入数据：

insert into v_emp_10 (empno,ename,deptno) values (10011,'悟空',10);

insert into v_emp_10 (empno,ename,deptno) values (10012,'八戒',20);(数据污染)

往视图中插入一条在视图中不显示但是在原表中显示的数据，称为数据污染

通过 with check option 关键字解决数据污染问题

create view v_emp_20 as (select * from emp where deptno=20) with check option;

测试：

insert into v_emp_20 (empno,ename,deptno) values (10013,'刘备',20); //成功
insert into v_emp_20 (empno,ename,deptno) values (10014,'张飞',30); //失败

删除和修改：
只能操作视图中存在的数据

别名：
如果创建视图时使用别名 则 操作视图时只能使用别名

create view v_emp_30 as (select ename name from emp where deptno=30);
select * from v_emp_30 where ename='james';

视图总结：

1. 视图时数据库中的对象，可以理解成一张虚拟的表，本质就是一段SQL语句

2. 作用： 重用SQL，隐藏敏感字段

3. 分类：简单视图(不包含去重 分组 函数 关联查询，可以增删改查)和复杂视图(反之，查询)

4. 通过with check option 解决数据污染

5. 删除和修改时 只能操作视图中存在的数据

6. 起了别名 只能用别名

约束

约束：
约束是创建表时给字段添加的限制条件

非空约束 not null

字段值不能为null

create table t1(id int,age int not null);
insert into t1 values(1,18);//成功
insert into t1 values(2,null);//失败

唯一约束 unique

字段的值不能重复

create table t2(id int,age int unique);
insert into t2 values(1,20);//成功
insert into t2 values(2,20);//失败

检查约束 check

语法支持,但是没有效果

create table t5(id int,age int check(age>10));
insert into t5 values(1,5);

外键约束

外键约束作用： 为了保证两个表之间的关系正确建立

1. 插入数据时外键值可以为null，可以重复，但是不能是另外一张表不存在的数据

2. 被依赖的表不能被先删除

3. 被依赖的数据不能先删除

如何使用外键：

1. 创建部门表

create table dept(id int primary key auto_increment,name varchar(10));

2. 创建员工表

create table emp(id int primary key auto_increment,name varchar(10),deptid int, constraint fk_dept foreign key(deptid)references dept(id));

介绍： constraint 约束名称 foreign key(外键字段名)references 表名(字段名)

测试：

insert into dept values(null,'神仙'),(null,'妖怪');

insert into emp values(null,'悟空',1);//成功

insert into emp values(null,'八戒',1);//成功

insert into emp values(null,'超人',3);//失败

drop table dept;//失败    

delete from dept where id=2;//成功

delete from dept where id=1;//失败

索引

什么是索引： 索引是数据库中提高查询效率的技术，类似于字典的目录

为什么使用索引： 如果不使用索引数据会零散的保存在每一个磁盘块当中，查询数据时需要挨个的遍历每一个磁盘块查找数据，如果数据量超级大，遍历每一个磁盘块是件非常耗时的事情，添加索引后，会将磁盘块以树桩结构进行保存，查询数据时会有目的性的访问部分磁盘块，因为访问的磁盘块数量降低所以能起到提高查询效率的作用

索引是越多越好吗？

​ 不是，因为索引会占磁盘空间，通过某个字段创建的索引可能永远用不上，则这个索引完全没有存在的意义，只需要对查询时频繁使用的字段创建索引

有索引就一定好吗？

不一定，如果数据量小使用索引反而会降低查询效率

索引的分类(了解)

1. 聚集索引(聚簇索引)： 通过主键创建的索引为聚集索引，添加了主键约束的表会自动添加聚集索引，聚集索引的树桩结构中保存了数据

2. 非聚集索引：通过非主键字段创建的索引叫做非聚集索引，树桩结构中只保存了数据所在磁盘块的地址并没有数据。

导入数据：

source 路径

测试：

1. 导入item_backup.sql数据库

2. show tables; 看是否有item2这张表

3. select count(*) from item2; 看是否有172万条数据

4. select * from item2 where title=‘100’; //看一下耗时 1.1秒

创建索引的格式：

create index 索引名 on 表名(字段名[(字符长度)]);
create index i_item_title on item2(title);  

创建完后继续执行

select * from item2 where title='100';  //看一下耗时 0.02秒 

查看索引：

show index from item2;

mysql##### 删除索引：

drop index 索引名 on 表名;

drop index i_item_title on item2;

复合索引

通过多个字段创建的索引称为复合索引

格式：

create index 索引名 on 表名(字段1,字段2);

频繁使用多个字段进行数据查询时为了提高查询效率可以创建复合索引

select * from item2 where title='100' and price<100;

create index i_item2_title_price on item2(title,price); 

总结：

1. 索引是用于提高查询效率的技术，类似目录

2. 索引会占用磁盘空间不是越多越好

3. 如果数据量小的话 添加索引会降低查询效率

4. 尽量不要在频繁改动的表上添加索引

group_concat() 分组连接函数

1. 查询员工表中 每个部门的所有员工工资 要求所有工资显示到一条数据中

select deptno,group_concat(sal) from emp group by deptno;

2. 查询员工表中每个部门 的员工姓名和对应的工资 要求显示到一条数据中

select deptno,group_concat(ename,'：',sal) from emp group by deptno;