MySql学习笔记06

课程回顾

1. 一对一关联 案例1：查询每个员工的名字和主管领导的名字

   select e.ename 员工姓名,m.ename 领导姓名
   from emp e join emp m
   on e.mgr=m.empno;

    

   案例2： 查询主管领导名字为blake的所有员工名字 select e.ename 员工姓名,m.ename 领导姓名 from emp e join emp m on e.mgr=m.empno where m.ename='blake' 案例3：查询有商品的分类信息及上级分类信息 往titemcategory表内部插入以下数据 id 162 name 办公用品 id 229 name 文具 id 913 name 户外用品 -插入数据 insert into titemcategory (id,name) values(162,'办公用品'),(229,'文具'),(913,'户外用品'); -查询所有有商品的分类id SELECT DISTINCT categoryid FROM titem WHERE categoryid IS NOT NULL -查询分类详情 select * from titemcategory where id in (SELECT DISTINCT categoryid FROM titem WHERE categoryid IS NOT NULL) -查询上级分类 select n.*,c.name 上级分类名称 from (select * from titemcategory where id in (SELECT DISTINCT categoryid FROM titem WHERE categoryid IS NOT NULL)) n join titemcategory c on n.parentid=c.id

   -查询有商品的分类信息及上级分类信息 实现步骤：1.通过子查询查到有商品的分类id 2.把当前分类和上级分类通过内连接建立关系 3.然后把分类的id作为筛选条件 -简单写法 select c.*,m.name 上级分类 from titemcategory c join titemcategory m on c.parentid=m.id where c.id in (SELECT DISTINCT categoryid FROM titem WHERE categoryid IS NOT NULL);

2. 一对多关联

   部门 员工 分类 商品 用户 地址 userid -一对多的表中 在多的表中添加关系字段

   案例1：查询每种分类下所对应的所有商品

   select *
   from t_item_category c left join t_item i
   on c.id=i.category_id

    

   案例2：查询部门平均工资 大于 所有员工的平均工资的所有部门下的所有员工

   SELECT * FROM emp 
   WHERE deptno IN (SELECT deptno FROM emp
   WHERE deptno IS NOT NULL
   GROUP BY deptno
   HAVING AVG(sal)>(SELECT AVG(sal) FROM emp))

    

   案例3：上题结果上再多显示出 部门的名字 select n.*,d.dname from(SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM emp WHERE deptno IS NOT NULL GROUP BY deptno HAVING AVG(sal)>(SELECT AVG(sal) FROM emp)))n join dept d on n.deptno=d.deptno

3. 多对多关联 -多对多需要单独通过新建的关系表保存数据

   create table teacher(id int primary key auto_increment,name varchar(20));
   
   insert into teacher values(null,'唐僧'),(null,'赵本山'),(null,'刘老师'); create table student(id int primary key autoincrement,name varchar(10)); insert into student values(null,'八戒'),(null,'悟空'),(null,'宋小宝'),(null,'小沈阳'),(null,'小明'),(null,'小红'); create table ts(tid int,sid int); insert into t_s values(1,1),(1,2),(2,3),(2,4),(3,5),(3,6),(1,5),(2,5); 案例1：查询每个老师对应的所有学生信息 1. 子查询 select name from student where id in (select sid from ts where tid in(select id from teacher)) 2. 内连接 SELECT n.name tname,s.name FROM (SELECT * FROM teacher t JOIN ts ts ON t.id=ts.tid)n JOIN student s ON n.sid=s.id

    

   案例2：查询 小明 对应的所有老师 

   SELECT n.name tname,s.name FROM (SELECT * FROM teacher t JOIN t_s ts ON t.id=ts.tid)n JOIN student s ON n.sid=s.id where s.name='小明'

    

   案例3：刘老师 对应的所有学生信息 

   SELECT n.name tname,s.name FROM (SELECT * FROM teacher t JOIN t_s ts ON t.id=ts.tid)n JOIN student s ON n.sid=s.id where n.name='刘老师';

    

视图

视图概述

-数据库中存在多种对象，表和视图都是数据库中的对象 **表和视图不能重名**
-视图是张虚拟的表，是通过sql的查询语句查询的结果集的表保存成了一个视图 
-视图中的数据 会随着真实表中的数据改变而改变
-视图实际上就是代表了一部分sql语句

-创建视图

-格式：create view 视图名 as 子查询；

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

案例：创建emp表的部门是20 工资小于3000的 视图
    只保存名字 工资 部门id

create view v_emp_20 as (select ename,sal,deptno from emp
    where deptno=20 and sal<3000);

案例：创建emp表 每个部门的工资平均值 的视图

create view v_emp_dept_avg as (select deptno,avg(sal) from emp group by deptno)

 

-如果创建视图的时候使用了别名，那视图以后的操作只认识别名

-视图的使用方式 和 table 一样 -格式：select * from 视图名

 select * from v_emp_10;

 

• 修改视图 -格式：create or replace view 原视图名 as 新的子查询

  create or replace view v_emp_10 as
  (select * from emp where deptno=10 and sal<3000);

   

• 视图的分类 -视图分为：简单视图和复杂视图 -简单视图：在创建视图的子查询中 不包含：关联查询、去重、函数、分组的视图称为简单视图 -复杂视图：和简单视图相反

• 对视图进行dml操作，只针对简单视图可以使用 因为复杂查询通常情况只是为了浏览数据的，不需要也不能进行增删改的操作

视图的字段 遵循原表字段的约束 - 视图的数据污染 如果往视图中插入一条在视图中不现实 但是在原表中显示的数据，称之为数据污染，不建议这样操作 只有insert命令才会出现数据污染，因为update和delete命令只能操作视图中有的数据

• with check option create view vemp10 as select * from emp where deptno=10 with check option;

• 视图的作用

  1. 重用子查询 提高开发效率
  2. 限制数据的访问 比如：可以创建一个视图把原表的敏感信息过滤掉即可 

     create view v_emp as select empno,ename,deptno,comm,job from emp

• 工作中对视图一般只进行DQL,不使用DML

• 删除视图

  drop view vemp10;

   

  -案例：创建一个复杂视图：显示部门的平均工资，最高工资，最低工资，工资总和，部门员工人数；

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

   

索引原理

索引概述

1. 索引是用来提高查询速度的技术
2. 如果不使用索引mysql会从第一条数据依次往后查询，如果数据量很大，非常耗时
3. 添加索引可以提交查询性能，但是如果数据量很小，添加索引可能会降低效率

4. 索引的生成是数据库内部生成，如果查询数据时发现某个字段已经添加过索引会自动使用

5. 复合索引

   创建索引时如果设置多个索引字段 则称为复合索引

6. 创建表的时候直接添加索引

   create table t11 (id int,name varchar(10), age int, index index_name(name(10)));

7. 更改表的索引 alter table t11 add index index_age(age);

8. 删除索引

   drop index index_age on t11;

索引总结

1. 索引不是越多越好 
2. 数据量小的时候不需要用到索引
3. 经常出现在 where、order by、 distinct的字段添加索引，效果会更好
4. 不要在经常做增删改的表中添加索引

MySQL 约束

唯一约束(UNIQUE)

如果数据库中的数据需要保证唯一性则添加此约束
CREATE TABLE T1( ID INT, AGE INT UNIQUE, NAME VARCHAR(20));

 

非空约束(NOT NULL)

如果某个字段的值不能为null 需要使用此约束

create table t2(id int,age int not null,name varchar(10));
-以下两种都不可以
insert into t2 (id,name)values(2,'a');
insert into t2 values(3,null,'b');

 

默认约束(DEFAULT)

如果需要让某个字段插入数据时有默认值时使用此约束
create table t3 (id int,age int default 0,name varchar(10));

 

主键约束(PRIMARY KEY)

1. 非空、唯一
2.如何添加主键
-创建表时添加主键
    create table t4(id int primary key,age int );
    create table t5(id int,age int,primary key(id));
-创建表之后添加主键
    alter table t6 add primary key(id);
    alter table t6 modify id int primary key
-删除主键约束
    alter table t3 drop primary key;
-自增约束 auto_increment
1.当字段赋值为null的时候，字段自动增长 
2.如果删除了某一条数据 自增的数值不回减少
3.如果插入数据时指定了一个特别大的数，下次自增则从这个数基础上+1

 

外键约束(FOREIGN KEY)

1. 工作中，除非特殊情况，一般不使用外键，使用代码通过逻辑进行限制
2. 外键约束是保证一个或两个表之间数据的一致性和完整性的
3. 表的外键通常使用的是另一张表的主键
4. 外键可以重复、可以是null、但不可以是另一张表不存在的数据
5. 使用外键约束的条件 -必须保证两张表使用相同的存储引擎 -存储引擎必须是innodb，myisam不支持外键约束 -外键和关联字段必须有相似的数据类型，数字长度必须相同 -外键约束对应的字段必须创建索引，如果不存在索引，mysql会自动创建索引

创建外键约束

1. mysql支持外键的列级语法，但是没有效果，是为了保证和sql的规范的一致性
2. 表级约束的添加方式
-格式：CONSTRAINT 约束名 FOREIGN KEY(当前表添加约束的字段名) REFERENCES 关联表表名(关联表的字段名)
create table classes(id int primary key auto_increment,name varchar(20));
insert into classes values(null,'一班'),(null,'二班');
CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,         
name VARCHAR(20),                                
class_id INT,
CONSTRAINT fk_class_id FOREIGN KEY(class_id) REFERENCES classes(id)
);
1.创建班级（classes）表 id name
2.创建学生表（student）id name classid
给classid添加外键约束 
3.先往班级表中插入数据 然后往学生表插入数据，

 

测试插入错数据 和删除 被关联的班级数据

CHECK约束

mysql语法支持，但是没有效果
create table t7(id int,name varchar(10),
age int,check(age>20));

 

什么是事务

事务是一组原子性的 SQL 查询, 或者说是一个独立的工作单元. 在事务内的语句, 要么全部执行成功, 要么全部执行失败

事务的 ACID 性质

**必须背下以下4点 面试常考**
-原子性：最小的单元，不可分割
-一致性： 保证sql执行的一致 要么都成功，要么都失败，保证执行前后数据一致
-隔离性：多个事务并发时，互不影响
-持久性：commit提交之后，数据保存到数据库中

 

MySQL事务

1.查看事务是否是自动提交
    show variables like '%autocommit%';
2.关闭自动提交
    set autocommit=0;

begin 开始事务
commit 提交事务
rollback 回滚事务

 

事务案例

转账案例：

1.创建user表
    create table user(id int,name varchar(20), money int);
    insert into user values(1,'苍老师',2000)，(2,'刘老师',100);
2.转账sql
    update user set money=money+500 where id=2;
    update user set money=money-500 where id=1;

 

课程回顾： 1.什么是视图 实际上视图就是一段sql语句 2.视图中数据实际上都是原表中的数据，如果修改视图中的数据 原表数据跟着更改 3.避免出现数据污染 可以通过添加 with check option 避免出现数据污染 4.视图通常只进行查询操作 5.视图作用：子查询复用、限制数据的访问

6.索引index 需要掌握 创建命令和删除命令 7.约束：unique、not null、primary key、default foreign key，check 8.事务 背下来四大特性： 原子性、一致性、隔离性、持久性

day01: 数据库相关的sql：show databases create database db1 character set utf8 show create database db1 drop database db1; 表相关： create table show tables; show create table t1; desc t1; rename table t1 to t2; alter table t1 change age age2 int; alter table t1 modify age long first/after id; add age int; drop age; drop table t1;

insert update delete select

day02: 1.主键 自增 not null 注释：comment 2.去重 distinct 3.事务 4.sql分类 ddl 数据定义语言 create alter drop truncate:删除表内所有数据，内部实现原理：先删除表再创建一个新表 执行效率高 dml 数据操作语言 insert update delete select 支持事务 dql 数据查询语言：select tcl 事务控制语言：begin commit rollback dcl 数据控制语言：控制权限 5.数据类型 整数： int bigint 浮点数： double decimal（m,d） 6.字符串： char varchar text 7.日期：date time datetime timestamp

day03: 聚合函数：sum count avg min max 字符串：charlength instr(x,y) locate(x,y) insert(str,begin,length,newStr) lower upper left right substring(str,begin,length) trim() replace(str,old,new); repeat(str,2); reverse 日期相关：now date(now()) time(now()) extract(year from now()) month day time hour second minute dateformat(now(),'%Y-%m-%d %H-%i-%s') strtodate('','')

数学：+ - * / % mod(2,3)
数学函数： floor  round() rand()

练习

1.案例：创建一张表customer2，id number(4),
name varchar2(50)，password varchar2(50)
,age number(3),address varchar2(50),修改
customer2表的时候设置主键约束
pk_id_name_cus2修饰id和name列。




2.案例：创建一张book3表,id number(4),
name varchar2(50),author varchar2(50),
pub varchar2(50),numinput number(10)。
修改book3的时候，设置主键约束
pk_id_name_b3修饰id和name列，设置唯一约束uq_author_pub_b3修饰author和pub列



3.案例:删除temp中的唯一约束uk_name_pwd

4.案例：在book表中author和pub列上添加索引index_author和index_pub


5.案例：删除book中在pub和author上的索引


6.案例：创建一个视图emp_view1，查询emp表中所有的数据，查询语句作为视图emp_view1


7.案例：创建一个视图dept_view,查询dept表中所有的数据，查询语句作为视图dept_view


8.案例：创建一个视图emp_view2,查询emp表中所有员工的编号，姓名，职位，工资，上级领导的编号以及工资的等级，该等级的最低工资和最高工资,查询语句作为emp_view2


9.案例：查询emp表中10,20号部门员工的编号，姓名，职位，工资，所属部门的编号,使用查询语句来修改视图emp_view1


10.案例：删除视图emp_view1,emp_view2