mysql详细笔记

mysql详细笔记

1.安装

1.windows

2.windos安装

2.数据库基本操作

1.创建数据库

• 1.语法

create {database | schema}[if not exists]<数据库名>[default charset set<字符集>][default collate<校验规则>];

语法规则说明：

• []代表内容可选，其余是必选内容

• {}或者|的内容为必选项，必须其中一项

• <>这里代表着需要自己要填写的内容

• 2.实例

创建school数据库

create database if not exists  school 
default character set utf8 default collate utf8_bin;

注意:

collate utf8_bin是 以二进制值比较，也就是区分大小写，collate是核对的意思
uft-8_general_ci  一般比较，不区分大小写

2.修改数据库

• 1.语法

alter database <数据库名>[default charset set<字符集>][default collate<校验规则>];

• 2.实例

1. 修改数据库的字符集信息为utf8

alter database school2 default character set utf8;

3.删除数据库

drop database school2;

4.切换库

use testdb;

5.数据库的存储引擎

SHOW ENGINES;

6.查看数据库

1.查看所有数据库

show databases;

2.查看某个数据库的建库语句

show create database school2;

3.查看当前数据库名

select DATABASE();

7.查看版本号

select VERSION();

3.数据库表的基本操作

1.创建表

1.语法

create table[<库名>]<表名>(
	<类名> <数据类型> [<列级完整性约束条件>]
    [,<类名> <数据类型> [<列级完整性约束条件>]][,...n]
    [,<表级完整性约束条件>][,...n]
)

1. 列级完整性约束条件

   是对字段值进行限制条件或说明，包括

   • not null

   • unique

   • default

     插入默认值时，可以使用default，或者不填这个字段

   • AUTO_INCREMENT 自动增长

     插入自认值时，可以使用null，或者不填这个字段,也可以指定自增长的起始值和间隔值。

     -- 自增长从哪里开始
     alter table person auto_increment   =   1000; 
     

2. 表级完整性约束条件

   • unique约束

   • primary key

     • 如果只包含一个主键字段，primary key约束也可以放在字段类型后

       字段名 数据类型 primary key
       

     • 主键包括两行以上字段

     [constraint 约束名] primary key(<字段1,字段2[...n]>);
     

   • foreign key

     • 如果只包含一个外键字段，foreign kry约束也可以放在字段类型后,这样会失效，不可取！！！

     字段名 数据类型 references 对应主键所在表(对应主键字段)
     

     • 外键包括两行以上字段

       [constraint 约束名] foreign key(<外键字段名>) references 对应主键所在表(对应主键字段)[on update {no action | set null | cascade | RESTRICT }][on delete {no action | set null | cascade | RESTRICT }]
       

     • 设置外键时，当父表发生变化时，子表的变化，称为级联约束

       • on delete :当父表记录被删除时
         • RESTRICT：有外键关系约束时，拒绝父表记录的更新和删除操作。
         • no action 设置它时，自动变为RESTRICT
         • set null 子表外键为null
         • cascade 子表外键和父表保持一直,如果被删，子表记录直接删除
         • 默认就是 RESTRICT
       • on update ：当父表记录被修改时
       • 无论设置什么，都不能直接删去父表，会有外键约束阻拦

2.实例

• 班级

  create table 班级(
  	班级名称 varchar(10) primary key,-- 主键就一个字段
  	所属学院 varchar(10) not null,
  	辅导员 varchar(8),
  	自习室 varchar(12)
  );
  

• 学生

  -- 所在班级 varchar(10) references 班级(班级名称)这样会失效！！！！！不可取
  create table 学生(
  	学号 varchar(8),
  	姓名 varchar(10) not null,
  	性别 enum('男','女'),
  	年龄 tinyint default 18,
  	所在班级 varchar(10),
  	籍贯 varchar(10),
  	primary key(学号),-- 一个字段或多个字段也可以采用这种方式
  	foreign key(所在班级) references 班级(班级名称) on update restrict -- 外键也同样如此
  );
  

• 课程

  create table 课程(
  	课程号 char(3) primary key,
  	课程名称 char(20) unique not null, -- 唯一 和 非空约束
  	先修课程 char(3),
  	课程性质 enum('专业课','选修课'), -- 采用了枚举
  	学分 tinyint
  );
  

• 选课

  create table 选课(
  	学号 varchar(11),
  	课程号 varchar(10),
  	成绩 tinyint,
  	primary key(学号,课程号),
  	foreign key (学号) references 学生(学号),
   	Constraint 选课_fk2 foreign key (课程号) references 课程表(课程号)
  );
  

  如果没有Constraint 选课_fk2，则就系统生成随机名

2.查看表结构

1. 查看表数据结构

DESCRIBE/EXPLAIN/desc 学生

2.查看建表语句

show create table 学生;

3.查看数据库中的所有表

show tables;

3.修改表结构

1.修改表名

• 语法

  alter table<原表名> rename to <新表名>
  

• 实例

  alter table 学生 rename 学生2;
  

2.修改字段名和类型

• 语法

  • change 修改字段名 数据类型

  alter table <表名> change <原字段段名> <新字段名> <新数据类型>
  

  • 修改字段类型 modify

    alter table<表名> modify <字段名> <新数据类型>
    

• 实例

  • change

    alter table `学生2` change 学号 学号2 varchar(10);
    

  • modify

    alter TABLE `学生2` modify 学号2  VARCHAR(10);
    

3.添加字段

• 语法

  alter table<表名> add <字段名> <数据类型>[列级别约束]
  

• 实例

  -- 添加字段，可以在添加字段时，同时添加约束
  alter table 学生2 add  新添加 VARCHAR(11) not null;
  

4.删除字段

• 语法

  alter table<表名> drop <字段名>
  

• 实例

  alter TABLE 学生2 DROP  新添加;
  
  
  
  

5.添加或删除外键约束

• 语法

  • 添加

  alter table <表名> add [constraint <约束名>] foreign key(<外键字段名>) references <对应主键所在表>(<对应主键字段名>) 
  

  • 删除

  alter table<表名> drop foreign key<约束名>
  

• 实例

  • 添加

    -- 添加和删除外键
    create table 选课表2(
    	学号 varchar(6),
    	课程号 varchar(10),
    	成绩 tinyint,
    	primary key(学号,课程号)
    );
    
    
    alter table `选课表` add CONSTRAINT name1 foreign KEY(学号) references `学生2`(学号);
    

  • 删除

    alter table 选课表2 drop foreign key name1;
    

4.删除表

• 语法

  drop table<表名>
  

• 实例

  drop table 选课表2;
  

4.数据表数据的基本操作

1.insert

1.添加完整记录

• 语法

insert into <表名> values(字段1,字段值2,...字段值n);

其中，字段值1到字段值n分别对应表中1个到n个字段，插入的记录值的分量顺序与表中的顺序一致，分量的数目与表中字段数目一致，分量的数据类型要与表中的数据类型一致

-- 向学生中添加一条完整的记录
insert into 学生 values('090101','周雷','男',20,'19信管','湖北武汉');

2.指定添加记录字段的顺序

可以在表名后指定字符的顺序,使加入操作更加灵活，这时插入记录的分量值的顺序要和指定字段顺序保持一致。

--
insert into 学生(学号,姓名,所在班级,性别,籍贯,年龄)
values('090102','刘阳','19信管','女','河南郑州',21); 

3.添加一条不完整的记录

添加记录也可以是部分字段有值的一条不完整的记录

4.一次性插入多个值

insert into 表名
values(记录值1),(记录值2),...(记录值n);

• 实例

insert into 班级 values
	('软件222','计算机','张三','乐群楼1'),
	('软件223','计算机','张三','乐群楼1');

2.update

• 语法

  • 单个值修改

  update <表名> set 字段 = 字段值 where 筛选条件
  

  • 多个值修改

  update <表名> set 字段1 = 字段值,字段2 = 字段值 where 筛选条件
  

• 实例

  • 单个值修改

    update 班级 set 所属学院 = "计算机与软件" where 班级名称 = '软件221';
    

  • 多个值修改

    update 班级 set 所属学院 = "计算机与软件", 辅导员 = 'ww' 
     where 班级名称 = '软件221';
    

3.delete

• 语法

  • 带删选条件

  delete from <表名> where 筛选条件
  

  • 不带筛选条件，则会把所有整个表中的删掉

• 实例

  delete from 班级 where 班级名称 = '软件221';
  

4.带子查询的数据插入、删除和修改

1.带子查询的数据插入

可以将一个子查询的结果插入一个表中

• 语法

  insert into 表名[{字段列表}] <子查询>
  

  当表名后的字段值省略时，子查询的目标列要与表中的字段数目，顺序和数据类型一致

  -- 创建一个和学生表结构相同的表男学生表,
  -- 将学生表中的那学生的记录插入到男学生表中
  insert into 男学生
  select * from `学生`
  where 性别 = '男';
  
  -- 创建一个包含班级和平均年龄两个字段的表--班级平均表
  -- 从学生中查找每个班级学生的平均年龄,将查询结果加入新表中
  insert into 班级平均年龄
  select 所在班级,avg(年龄)
  from `学生`
  group by 所在班级;
  

2.带有子查询的数据修改

-- 将选课表中的大学英语课程的成绩提高10%
update `选课`
set 成绩 = 成绩*1.1
where 课程号 in (
	select 课程号 from `课程`
	where `课程名称` = '大学英语'
)

-- 将学生表中的信息与计算机的学生年龄减掉2岁
update `学生`
set 年龄 =年龄 - 2
where 所在班级 in (
	select 班级名称 from `班级`
	where 所属学院 = '信息与计算机'
)

3.带有子查询的数据删除

-- 将课程表中没有学生选修的课程的记录删除
delete from 课程 
where `课程号` not in(
	select 课程号 from 选课
)

-- 将选课表中选修高等数学的课程的记录删除
delete from `选课`
where 课程号 in (
		select 课程号 FROM
		课程 where 课程名称 = '高等数学'
)

4. 数据类型

1.正数

2.浮点数和定点数

3.日期和时间

4.文本和字符串

5.二进制字符串

6.数值运算符

5.函数

1.数学函数

2.字符串函数

3.日期和时间函数

4.条件判断函数

5.系统信息函数

6.加/解密函数

7.其他函数

8.自定义函数

6.查询函数

• 查询语句的基本格式

  select 目标字段 | 字段表达式或函数
  from 数据源
  [where <元组选择条件>]
  [group by <分组字段>][having <组选择条件>]
  [order by <排序字段> [asc | desc]]
  

  • 其中select和from为必选字段，其他为可选字段

  • select

    • distinct去重

  • where

  • order by子句

    • 对查询结果进行排序，排序字段可以是一个或者多个

      字段1[asc | desc],字段名2[asc | desc][,...字段名n][asc | desc]
      

1.操作符号

1.算数操作符

2.比较运算符

用作比较操作符连接的表达式，其返回值只能是1(其结果为真),0(比较结果为假)，或null(比较结果不确定)

• in/not in

  <字段> [not] in (列表值 | 自查寻)
  

  • 字段值在列表或结果集中，返回1，否则，返回0

• between…and…

  <字段> [not] between 值1 and 值2
  

  • 在值1和值2之间比较，包含两个范围值

• is null / is not null

  <字段> is [not] null
  

• like操作符

  <字段>[not] like 通配符表达式
  

  • %匹配任意数目，_只能匹配单个字符

• exists

  [not] exists(<子查询>)
  

  • 判断子查询的结果集是[不是]为空，若是[不是]为空，则返回1

3.逻辑操作符

用于逻辑操作

• and

• or

• not

• any

  <字段><比较运算符> any(列表值 | 子查询)
  

  将字段值与列表值或子查询中的任意一个值比较，入满足条件，返回1，否则，返回0，示例：

  年龄 >= any(select 年龄 from 学生 where 性别 = '男')
  

• all

  <字段> <比较运算符> all (列表值 | 子查询)
  

  将字段值和列表值或子查询结果集中所有值进行比较

4.集合操作

• union

  列表值1 | 子查询 union 列表值2 | 子查询2
  

  会进行去重，降低速度

• union all

  不会去重

5.其他运算符

• 代表所有字段的缩写

• all

  放在查询语句selsect子句的目标列前，说明查询结果保留重复行

• distinct

  放在查询语句selsect子句的目标列前，说明查询结果去掉重复行

数据准备

• 班级

  INSERT INTO `班级` VALUES ('19大英1', '外语', '刘俊飞', 'J06-311');
  INSERT INTO `班级` VALUES ('19大英2', '外语', '刘俊飞', 'J06-312');
  INSERT INTO `班级` VALUES ('19电商1', '管理', '王小丽', 'J07-101');
  INSERT INTO `班级` VALUES ('19电商2', '管理', '王小丽', 'J07-102');
  INSERT INTO `班级` VALUES ('19法语', '外语', '代晶', 'J06-313');
  INSERT INTO `班级` VALUES ('19会计1', '管理', '王婷', 'J07-201');
  INSERT INTO `班级` VALUES ('19会计2', '管理', '王婷', 'J07-202');
  INSERT INTO `班级` VALUES ('19计科1', '信息与计算机', '章英', 'J06-301');
  INSERT INTO `班级` VALUES ('19计科2', '信息与计算机', '章英', 'J06-302');
  INSERT INTO `班级` VALUES ('19计科3', '信息与计算机', '刘建', 'J06-303');
  INSERT INTO `班级` VALUES ('19市营', '管理', '王小丽', 'J07-103');
  INSERT INTO `班级` VALUES ('19信管', '信息与计算机', '刘建', 'J06-401');
  

• 课程

  INSERT INTO `课程` VALUES ('c01', '高等数学', NULL, '公共必修', 4);
  INSERT INTO `课程` VALUES ('c02', '计算机基础', NULL, '公共必修', 3);
  INSERT INTO `课程` VALUES ('c03', 'C程序设计', 'c02', '公共必修', 3);
  INSERT INTO `课程` VALUES ('c04', '大学英语', NULL, '公共必修', 4);
  INSERT INTO `课程` VALUES ('c05', '数据结构', 'c03', '专业必修', 4);
  INSERT INTO `课程` VALUES ('c06', '数据库原理', 'c03', '专业必修', 3);
  INSERT INTO `课程` VALUES ('c07', '音乐欣赏', NULL, '选修', 2);
  INSERT INTO `课程` VALUES ('c08', '论文写作指导', NULL, '选修', 2);
  

• 选课

  INSERT INTO `选课` VALUES ('060101', 'c01', 99);
  INSERT INTO `选课` VALUES ('060101', 'c02', 99);
  INSERT INTO `选课` VALUES ('060101', 'c03', 99);
  INSERT INTO `选课` VALUES ('060101', 'c04', 99);
  INSERT INTO `选课` VALUES ('060101', 'c05', 99);
  INSERT INTO `选课` VALUES ('060102', 'c01', 81);
  INSERT INTO `选课` VALUES ('060102', 'c03', NULL);
  INSERT INTO `选课` VALUES ('060102', 'c04', 92);
  INSERT INTO `选课` VALUES ('070101', 'c01', 50);
  INSERT INTO `选课` VALUES ('070101', 'c02', 86);
  INSERT INTO `选课` VALUES ('070101', 'c06', 90);
  INSERT INTO `选课` VALUES ('070103', 'c04', 52);
  INSERT INTO `选课` VALUES ('070103', 'c06', 47);
  INSERT INTO `选课` VALUES ('070301', 'c01', 87);
  INSERT INTO `选课` VALUES ('070301', 'c04', 83);
  INSERT INTO `选课` VALUES ('080101', 'c02', 73);
  INSERT INTO `选课` VALUES ('080101', 'c07', 69);
  

• 学生

  INSERT INTO `学生` VALUES ('060101', '王小辉', '男', 20, '19计科1', '湖北武汉');
  INSERT INTO `学生` VALUES ('060102', '吴珍萍', '女', 20, '19计科1', '江西南昌');
  INSERT INTO `学生` VALUES ('060103', '吴鑫', '男', 19, '19计科2', '湖北武汉');
  INSERT INTO `学生` VALUES ('070101', '孙晓英', '女', 21, '19电商1', '河南郑州');
  INSERT INTO `学生` VALUES ('070102', '林小慧', '女', 20, '19电商1', '湖南长沙');
  INSERT INTO `学生` VALUES ('070103', '吴俊', '男', 19, '19电商1', '湖北武汉');
  INSERT INTO `学生` VALUES ('070301', '李露', '男', 21, '19会计1', '江西南昌');
  INSERT INTO `学生` VALUES ('070302', '张小慧', '女', 22, '19会计1', '湖北黄石');
  INSERT INTO `学生` VALUES ('070303', '李霖', '男', 21, '19会计2', '江西南昌');
  INSERT INTO `学生` VALUES ('080101', '江毅飞', '男', 20, '19大英1', '湖北黄石');
  INSERT INTO `学生` VALUES ('080102', '郑文曦', '女', 20, '19大英1', '湖北随州');
  INSERT INTO `学生` VALUES ('080103', '万凌飞', '男', 20, '19大英1', '湖北黄石');
  INSERT INTO `学生` VALUES ('080104', '胡梦', '女', 18, '19大英1', '江西南昌');
  INSERT INTO `学生` VALUES ('1001', '小张1', '男', 21, '19大英1', '湖北武汉');
  INSERT INTO `学生` VALUES ('111', '111', '男', 20, NULL, NULL);
  INSERT INTO `学生` VALUES ('555', '5555', NULL, 12, NULL, NULL);
  INSERT INTO `学生` VALUES ('666', '666', NULL, 20, NULL, NULL);
  
  
  
  

1.单表查询

1.不带where

• 输出所有字段

  select * from 课程;
  

• 输出指定字段

• 使用all或distinct,默认是不去重的

  select all 所属学院 from 班级;
  select distinct 所属学院 from 班级;
  select  所属学院 from 班级;
  

• select子句使用字段表达式或函数

  select 学号,成绩*1.4 from 选课;
  select avg(年龄) from 学生;
  

• 为表或字段取别名

2.带where条件的查询

• 使用比较操作符的调价查询

  select * from 选课 where 成绩<60;
  

• 使用in操作符

  select 学号,籍贯 from 学生 where 籍贯 in('湖北武汉','江西南昌');
  

• between … and…

  select 学号,年龄 from 学生 where 年龄 between 19 and 21;
  

• like

  select 学号,姓名 from 学生 where 姓名 like '吴%';
  

• and 或者 or

  select 学号 from 选课 where 课程号 = 'c04' and 成绩 > 90;
  select 学号,课程号 from 选课 where 课程号 = 'c04' or 课程号 = 'c05';
  

  • 不能使用and查询既选修了“c04”又选修了“c05”课程的学号，下面的例子永远是空集，可以使用多表连接查询

    select 学号,课程号 from 选课 where 课程号 = 'c04' and 课程号 = 'c05';
    

3.排序

2.多表连接查询

• 内连接

  只会保留连个表中匹配的结果

  • 等值连接

    判断两个表的关联字段的值是否相等来连接

    若要在查询结果中去除重复的字段可是使用自然连接

  • 自身连接

    自己和自己连接

• 外连接

  还会保留不匹配的结果

1.内连接

• 等值连接

  • 语法

    from 表1 [inner] join 表2 on 表1.字段1 = 表2.字段2
    

  • 实例

    -- 查询选修了课程的学生的基本信息和选课信息
    select * from 学生 join 选课 on 学生.学号 = 选课.`学号`;
    -- 关系中不允许有同名的字段,因此在查询的结果中,选课表中的学号会自动更名
    

• 自然连接

  自然连接是一种特殊的等值连接，它要求两个表中的关联字段必须同名，并在查询结果中去掉重复的字段

  • 语法

    from 表1 nature join 表2
    

  • 实例

    -- 查询选修了课程的学生的基本信息和选课信息，并去掉重复字段
    select * from 学生 natural join 选课;
    
    -- 选修了高等数学的学生的学号和成绩，结果按成绩降序排列
    select 学号,成绩 from `课程` join 选课 on 课程.课程号 = 选课.`课程号`
    where 课程名称 = '高等数学' order by 成绩 desc;
    -- 选修了高等数学的学号和姓名
    select c.学号,姓名 from 课程 a join 选课 b on a.课程号 = b.`课程号`
    join 学生 c on c.学号 = b.`学号`
    where 课程名称 = '高等数学' order by 成绩 desc;
    select * from 课程;
    select distinct 课程号 from 课程;
    

2.外连接

• 左外连接：在查询结果中保留左表中的非匹配记录

• 右外连接：在查询匹配中保留右表中的非匹配记录

• 全外连接：在查询结果中保留两个表中的非匹配记录，mysql不支持。

• 实例

  -- 查找选修了课程信息的学生的基本信息和选课信息，查询结果要求包含学生表中的非匹配记录
  select * from 学生 left join 选课 on 学生.`学号` = 选课.`学号`;
  select * from 选课 right join 学生 on 学生.`学号` = 选课.`学号`;
  

3.自身连接

一个表自己根自己相连，为了区分，通常会给表取不同的别名

-- 查找每门课程的课程号、课程名、先修课的课程号及课程名
select a.课程号,a.课程名称,b.课程号 选修课程号,b.课程名称 选修课程名称
from 课程 a join 课程 b on a.先修课程 = b.课程号;
-- 查询既选修了c04又选修了c05课程的学生的学号
select a.学号 from 选课 a join `选课` b on a.学号 = b.`学号` 
and a.`课程号` = 'c04' and b.课程号 = 'c05';

4.总结

多表连接，也可以不用join,直接进行多表联查

-- 查询既选修了c04又选修了c05课程的学生的学号
select a.学号 from 选课 a , `选课` b where a.学号 = b.`学号` 
and a.`课程号` = 'c04' and b.课程号 = 'c05';

3.子查询

书写的顺序和执行的顺序是相反的，书写是从外层到内层，执行是从内层到外层。

1.子查询的运用

• 使用in操作符的子查询

  -- 查找选修课高等数学的学生的学号和姓名，结果按成绩降序排列
  select 学号,`姓名`
  from 学生 where 学号 IN(
  select 学号 from 选课 where 课程号 IN(
  select 课程号 from 课程 where 课程名称 = '高等数学'));
  
  -- 查找既选修了c04又选修了c05课程的学号
  select 学号 from 选课 
  where 课程号 = 'c04' and 学号 in(
  	select 学号 from 选课 where 课程号 = 'c05' -- 子查询后面不用加;
  );
  
  -- 查找没有选课的学生的学号和姓名
  select 学号,姓名 from 学生 where 学号 not in(
  	select 学号 from 选课
  );
  
  -- 查找只有男同学选修的课程课程号
  select 课程号 from 选课 where 课程号 not in(
  	select 课程号 from 选课 where 学号 in(
  		select 学号 from 学生 where 性别 = '女'
  	)
  )
  

• 使用比较运算符的子查询

  -- 查找年龄比吴俊大的男学生的学号和年龄
  select 学号,`年龄`
  from `学生`
  where 性别 = '男'
  and 年龄 > (
  	select 年龄 from `学生`
  	where 姓名  = '吴俊'
  )
  
  -- 查询选修了c04课程的学生中，`成绩`比李露高的学生的学号和成绩
  select 学号,成绩 from 选课 where 
  课程号 = 'c04' and 成绩 > (select 成绩 from 选课 
  	where 课程号 = 'c04' and 学号 in (
  		select 学号 from `学生`
  		where 姓名 = '李露'
  	)
  ) 
  
  -- 查找和孙晓英在同一个班级的学生的学号,`姓名`
  select 学号,姓名 from `学生`
  where `所在班级` in (
  	select 所在班级 from 学生 where 姓名 = '孙晓英' 
  )
  
  
  
  
  

• 用any,all操作符的子查询

  在嵌套查询中,any和all操作符必须与比较运算符配合使用，语法格式如下：

  <字段> <比较运算符> [any | all]<子查询>
  

  • 实例

  
  -- 查找学生表中比19电商1班所有学生年龄都小的学生的学号和年龄
  select 学号,年龄 from 学生 
  where 年龄 < all(select 年龄 from 学生 where 所在班级 = '19电商1')
  
  -- 查找选修了c04课程的学生中,成绩最高的学生的学号
  select 学号,成绩 from 选课 
  where 课程号 = 'c04' and 成绩 >= all(
  	select 成绩 from 选课 where 课程号 = 'c04'
  )
  

• exists操作符中的子查询

  当使用exists的子查询集不为空时，会返回一个逻辑真值true,说明外层查询数据表的当前记录满足查询条件，该记录的指定字段会被输出；当使用exists的子查询结果集为空时，会返回一个逻辑值false,说明当前的记录查询不满足查询条件，该记录的指定字段将不被输出。

-- 查找选修了c04课程学生姓名 in语句
select 姓名 from 学生 where 学号 in (
	select 学号 from 选课 where 课程号  = 'c04'
)
-- exists 语句
select 姓名 from 学生 where  exists(
	select * from 选课 where 课程号 = 'c04' and 学号 = 学生.`学号`
)
/**
	外层查询的学生表中的记录指针首先指向第一条学生记录，已该学生的学号字段
值'060101'执行内层查询,如果内层查询满足where子句条件(课程号 = 'c04' and 学号 = '060101')的记录存在，则输出该学生的姓名字段的值，否则就不输出,然后，学生表中的记录指针指向第二条学生记录，按照同样的过程，知道处理完学生表中的所有记录。
因此，他们子查询的执行个数取决于外层查询的数据表中的记录数
	由于exists的子查询只返回真和假，因此指定列名是没有意义的，所以，在子查询中
	通常使用*代表目标列
**/

-- 查找没有选修c04课程的学生的姓名  in
select 姓名 from 学生 where 学号 not in(
	select 学号 from 选课 where 课程号 = 'c04'
)
-- exists
select 姓名 from 学生 where  not exists(
	select * from 选课 where 课程号 = 'c04' and 学号 = 学生.学号
)

-- 查找全是男生选修的课程的课程名
select * from 选课  where `课程号` = 'c08';

select * from 课程 
where 课程号 not in(
	select 课程号 from `选课`
	where 学号 in (
		select 学号 from 学生 
		where 性别 = '女'
	)
)
select *  from 课程 
where 课程号 in (
	select 课程号 from 选课 
) and 课程号  not in(
	select 课程号 from `选课`
	where 学号 in (
		select 学号 from 学生 
		where 性别 = '女'
	)
)

select 课程名称 from 课程 
where 课程号 in (
	select 课程号 from 选课 
) and   not exists (
	select * from `选课`
	where 课程号 = 课程.`课程号` and 学号 in (
		select 学号 from 学生 
		where 性别 = '女'
	)
)

-- 查找没有被选的课
select * from 课程 where 课程号 not in(
	select 课程号 from 选课 
)

select * from 课程 where  not exists(
	select 课程号 from 选课 where 课程号 = `课程`.`课程号`
)


-- 查找没有选修学号为'080101'的学生所选的课程的学生的姓名
select 姓名 from `学生`
where 学号 in(
	select 学号 from 选课 
) and not exists(
	select * from 选课 where 学号 = 学生.学号 and `课程号`
	in (
		select 课程号 from 选课 where 学号 = '080101'
	)
)

select 姓名 from 学生 
where  学号 in(
	select 学号 from 选课 
) and 学号  not in(
	select 学号 from 选课 where 课程号 in(
		select 课程号 from 选课 where 学号 = '080101'
	)
) 

2.Sql语句中IN和exists的区别及应用

• in

  确定给定的值是否与子查询或列表中的值匹配。in在查询的时候，首先查询子查询的表，然后将内部表和外部表做一个笛卡尔积，然后按照条件进行筛选，所以相对于表较小的时候，in的速度较快。

  具体的sql如下：

  -- 查找选修课课程号c01(高等数学)的学生的学号和姓名，结果按成绩降序排列
  select 学号,`姓名`
  from 学生 where 学号 IN(
  select 学号 from 选课 where 课程号 = 'c01');
  

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4IHu2PaT-1670048055647)(mysql详细笔记.assets/6.3.2.1png)]

执行流程：

首先，在数据库内部，查询子查询，执行如下代码：

select 学号 from 选课 where 课程号 = 'c01'

此时，将查询到的结果和原有的学生表做做一个笛卡尔积

然后，再根据 学生.学号 IN 选课.学号的条件，进行筛选，（即学生的写好和选课的学号是否相等，将不相等的删除），最后保留符合条件的数据。

• exists

  指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的，匹配上就将结果放入结果集合中。

语句：

-- 查找选修课课程号c01(高等数学)的学生的学号和姓名，结果按成绩降序排列
-- in的写法
select 学号,`姓名`
from 学生 where 学号 IN(
select 学号 from 选课 where 课程号 = 'c01');
-- exists的写法
select 学号,`姓名`
from 学生 where exists(
select * from 选课 where 课程号 = 'c01' and 学号 = 学生.`学号`);

语句的执行结果和上面的in的执行结果是一样的。

但是,不一样的是他们的执行流程完全不一样：

使用exists关键字进行查询的时候，首先，我们查询的不是子查询的内容，而是查我们的主查询的表，也就是说，我们先执行的sql语句是：

select 学号,`姓名`
from 学生

然后，根据表中的每一条记录，执行以下语句，依次去判断where后面的条件是否成立：

exists(
select * from 选课 where 课程号 = 'c01' and 学号 = 学生.`学号`);

如果成立，则返回true，不成立则返回false。如果返回的是true的话，自该行结果保留，如果返回结果是falase的话，则删除该行，最后将得到的结果返回。

• 区别和应用场景：

  in和exists的区别：如果子查询得出的结果集的记录较少，主查询中的表较大,且子查询又有索引时应该使用in,反之，如果外层的主查询的记录主查询记录较少，子查询中的表大，主查询又有索引的时，使用exists。其实，我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动，先被访问，如果是in,那么先执行子查询，所以我们会以驱动的快速返回为目标，那么就会考虑到索引和结果集的关系了，另外，in不对null进行处理。

  ​ in是把外表和内表做hash连接，而exists是对外表做loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in的效率高的说法是不准确的。

• not in和not exists

  如果查询语句使用了not in，那么内外表都进行全表扫描，没有使用到索引；而not exists的子查询依然能用到表上的索引。所以无论是哪个表大，用not exists都比not in快。

  • 1.对于not exists查询，内表存空值对结果查询没有影响；对于not in查询，内表存在空值将导致最终最终的查询结果为空。
  • 2.对于not exists查询，外表存在空值，存在空值的那条记录最终会被输出；对于not in查询，外表存在空值，存在空值的那条记录最终将被过滤，其他数据不受影响。

3.子查询的局限性

每个查询块的数据源只有一个表,因此在最外层select子句中，目标列只能来自一个表，但往往需要输出多个表字段，这时需要连接查询。

5.聚合函数

sql聚合函数也称为统计函数，它可以出现在select子句中，出现在having子句中建立组选择条件

• count(* | 字段名)

统计表中记录的个数或非空字段的行数

-- 统计表中共有多少个班级
select count(*) 班级数 from 班级;

-- 统计班级表中辅导员共有几位
-- 直接使用count(*) count(辅导员)进行统计，会将重复值计入
select count(distinct 辅导员) 辅导员人数 from 班级;

-- 统计课程中公共必须课有几门
select count(*) 公共必修课门数 from 课程 where 课程性质 = '公共必修';

• avg(字段表达式)

求字段表达式值(数值型)的平均值

• sum(字段表达式)

  求字段表达式的值(数值型)的总和

• max(字段表达式)

  求字段表达式的值(数值型)的最大值

• min(字段表达式)

  求字段表达式的值(数值型)的最小值

-- 统计选修课程表中c04课程最高分和平均分
select max(成绩),avg(成绩) 平均分
from `选课`
where 课程号 = 'c04';

6.group by实现分组查询

1.普通group by

对数据先进行分组，然后对每组数据进行统计

order by子句提供对数据分组功能，可以按一个或多个列的值进行分组，分组后可以聚合函数就可以以组为单位记性统计，每组的统计结果都会输出。

• 实例

  -- 在学生表中按籍贯统计学生人数
  select 籍贯,count(*) 人数 from `学生`
  group by 籍贯;
  
  -- 按课程统计每门课的平局分
  -- select * 只取每个数组的第一个
  select 课程号,avg(成绩) from  选课 group by 课程号; 
  
  -- 统计每个班的男生人数,先使用where进行筛选，然后使用order by进行分组
  select 所在班级,count(*) from 学生 where 性别 = '男' group by `所在班级`;
  

• 多个值进行分组

-- 对多个值进行分组
-- 每个班级中的男生和女生的人数
select 所在班级,性别, count(*) from 学生 group by 所在班级,性别;

-- 每个班级中的男生人数大于两个
select 所在班级,性别, count(*) from 学生 group by 所在班级,性别 having 性别 = '男' and count(*) > 2;

2.having子句的分组条件查询

-- 统计选课人数超过3人的课程的课程号和选课人数
-- having 子句可以对分组之后的条件进行筛选
select 课程号,count(*) 选课人数 from `选课`
group by 课程号 having count(1) > 3;

-- 查找选课平均分在80以上的学生的学号和平局分
select 学号,avg(成绩) from 选课 group by 学号
having avg(成绩) > 80;

-- 查找选课成绩都在80分以上的学生的学号
select 学号 from 选课 group by 学号 having min(成绩) > 80;

5.合并查询

2.聚合函数查询

6.边和字段查询

7.正则查询

8.索引

1.索引简介

1.定义

索引是帮助mysql高效的获取数据的数据结构，可以加快查询的速度。

2.作用

• 1.加快查询速度

• 2.加快表之间的连接速度

  表之间的连接，本质上是连接字段上的在表中的查找，因此也可以加快速度

3.分类

• 1.普通索引

• 2.唯一索引 unique

  创建唯一约束时，会自动创建唯一索引，名称

• 3.主键索引 primary key

  只能有一个，不能自己键，只能通过创建主键的方式间接的建立主键索引，它还叫局簇索引。

• 复合索引

  复合索引要求必须引用第一个字段，且字段必须从第一个开始连续，这样才可以使用。

4.索引使用的原则

• 缺点

  • 不仅更新表中的数据，还要更新表中所有索引数据
  • 索引也会占据一定的空间

• 原则

  • 经常为查询条件的字段、经常作为排序或分组的字段以及表之间关联的字段建立索引
  • 经常更新的字段
  • 表中记录较少的字段
  • 选择出现频率次数多的字段
  • 要经常维护表中的索引。

2.创建索引

1.创建表时同时创建索引

• 语法

  1. 单列普通/唯一索引

  [unique] index [索引名](属性名[(长度)][asc | desc])
  

  • 长度：索引的长度
  2. 多列普通/唯一索引

  [unique] index [索引名](属性1[(长度))][asc | desc],属性名2[(长度)][asc | desc]，...属性名n[(长度)][asc | desc])
  

• 案例

  create table course(
  	cno char(3) primary key,
  	cname varchar(10),
  	credit tinyint,
  	term tinyint,
  	index credit_index(credit desc),-- 普通索引
  	unique index cname_index(cname desc),-- 唯一索引
  	index term_credit(term,credit) -- 复合索引;
  );
  

2.在已建好的表上创建索引

• 语法

  create index 索引名 on 表名(属性名[(长度)][asc | desc])
  

• 实例

  	create index 学生_所在班级 on 学生(所在班级 asc);
  	create unique index 学生_姓名 on 学生(姓名 desc);
  	create index 选课_课程号成绩 on 选课(课程号 desc,成绩 desc);
  

3.索引查看

• 使用explain来查看查询语句有没有使用索引或者使用哪个索引

  -- 查看所有学生的信息，结果按学号降序排序
  explain select 学号 from 学生 where 姓名 like'%张%';
  

  • type ：显示连接使用了何种类型，从最好到最差的连接类型为const、eq_reg、ref、range、index、all
  • key： 表示连接使用的索引，如果为null，表示没有使用索引。可以使用use | force index(索引名)来强制使用索引。
  • key len:索引的长度
  • ref： 索引的哪一列被使用了

  注意：

  ​ 这里使用了学生_姓名索引

• 主键索引

  explain select * from 学生 order by 学号 desc;
  

  学号是主键

• 多个索引

  explain select 学号,成绩 from 选课 where 课程号 = 'c04' order by 成绩 desc;
  

  每个筛选条件，都会查看是否具有索引

  explain select 成绩 from 选课 where 学号 = '1' and 课程号 = '1';
  

• 强制使用某个索引

  -- 强制使用索引
  explain select * from 学生 force index(学生_所在班级) where `所在班级` = '软件191';
  

• 查看某个表的所有索引

  • 语法

    show index from [数据库.]表名
    

  • 实例

    show index from 学生;
    

4.删除索引

• 语法

  drop index <索引表> on <表名>
  

• 实例

  drop index  选课_fk2 on 选课
  

5.索引失效

• 违反最左前缀法则
• 在索引上做任何操作
• 索引范围条件右边的列
• 尽量使用覆盖索引
• 使用不等于
• like以统配符开头
• 字符串不加单引号索引失效
• or连接
• order by
• group by

9.存储过程和函数

1.创建存储过程和函数

2.调用存储过程和函数

3.查看存储过程和函数

10.视图

1.视图概念

视图是数据库中根据子模式设计的虚拟表，数据库中中存放视图定义的语句，其数据存储在对应的表中，因此视图不像表中表和索引那样需要占存储空间。用户可以根据个性化的需求，从一个表或多个表中抽取字段部分定义成视图 ，这样用户不必看到这个数据库，而只关心视图中对自己有用的数据，大大简化了用户的操作。用户还可以通过视图设置不同的权限，让其他用户无权查看或修改视图，这样也提高了数据库系统的安全性。

​ 为了让不同的用户只看到自己需要并有权看到的内容，mysql提供了视图机制。视图是由数据库中的一个或多个表导出的虚表，其中并不存放实际数据，只存结构及与原始表结构之间的关系。他可以对应一个基本表部分元组或部分字段这样的视图可以修改相关基本表中相对应字段值的传送器；它也可一是一个表使用聚合函数或表达式，分组操作，排序操作等查询操作形成的，当调用这个视图时，会自动对表执行这些操作并返回结果，其功能相当于一个查询器；它还可以是多个表按连接查询或子查询的方式抽取部分数据形成的，这也相当于一个查询器。

​ 视图中的数据依赖其对应表中的数据，且在引用视图时动态生成，一旦表中的数据发生改变，显示在视图的数据也会发生改变。

• 好处

  不必重新编写复杂的sql,直接打开视图即可获取需要的数据。

  • 简化用户操作

    只让用户看到和使用多表连接后的虚表

  • 实现数据库系统的安全性

  • 提高数据逻辑独立性

    • 大表分解成小表
    • 两个小表合并成大表

2.创建视图

• 语法

  create view<视图名> [<字段名列表>]
  as <子查询>
  [with check option]
  

  • 表和视图中共享相同的名称空间，所以视图名不能和已有的表名相同
  • 若省掉字段名列表，视图中的列名与子查询select子句的目标列名一致；当子查询的目标列式聚合函数等，需要在定义视图时指定视图的字段名
  • with check option是可选选项，表示对视图进行更新时，必须满足子查询的where子句中的条件

• 实例

  -- 在学生表上创建一个在湖北武汉籍学生的视图湖北武汉学生，
  -- 包含学号,姓名,`籍贯`
  create view 湖北武汉学生
  as select 学号,姓名,`籍贯`
  from `学生`
  where 籍贯 = '湖北武汉'
  with check option;
  
  -- 创建视图时,并未带with check option选项,所以通过
  -- 视图插入记录时，不必满足where的条件
  insert into 湖北武汉学生 values('080106','肖晶','河南郑州');
  
  -- 在学生和班级两个表中建立一个信息与计算机学院学生的视图,包含学号
  -- 姓名，性别，和班级四列
  create view 信息与计算机学院学生
  as 
  select 学号,姓名,性别,所在班级 班级
  from 学生 join `班级`
  on 学生.所在班级 = 班级.`班级名称`
  where 所属学院 = '信息与计算机'
  WITH check option;
  
  -- 在学生和选课表上建立一个按班级统计学生总成绩和平均成绩视图 各班学生总成绩和平均成绩
  -- 包含班级,总成绩，平均成绩三列 
  create view 各班学生总成绩和平均成绩
  as 
  select 所在班级,sum(成绩),avg(成绩)
  from 学生 join 选课 on 学生.学号 = 选课.`学号`
  group by 所在班级;
  
  

3.更新视图

对视图的更新其实就是对表的更新，因为视图是虚拟表，对视图执行插入，修改和删除操作时，都是转换成对应基本表的操作。对于定义时带了with check option选项的视图，插入和修改时要保证数据满足子查询where子句的条件。

-- 在湖北武汉学生中,将学号070103的学生姓名改为吴俊
update 湖北武汉学生
set 姓名 = '吴a'
where 学号 = '070103';

select * from 学生 where 学号 = '060103';

-- 在建立的视图中湖北武汉学生中,将学号 = '060103'的学生删除
delete from 湖北武汉学生 where 学号 = '060103';

• 不支持一下的几种视图更新操作

  • 由两个以上的表导出的视图

    insert into 信息与计算机学院学生 values('090104','王小俊','男','19计科2');
    

  • 视图定义的子查询中使用了union,distinct,group by或having等关键字

    
    create view 选课学生总分
    as
    select 学号,sum(成绩) 总分 from 选课 
    group by 学号;
    
    select * from 选课学生总分 where 学号 = '060101';
    
    update  选课学生总分 set 总分 = 500 where 学号 = '060101';
    

  • 视图定义的子查询是嵌套查询，且内层查询中的表和外层查询中的表相同

  • 在一个不允许更新的视图上建立视图

4.删除视图

drop view 选课学生总分;

这个表被删除后，由基本表导出的所有视图(定义)虽没有被删除，但均无法使用，为防止用户在使用时出错，要将这些失效的视图用删除语句删除。

5.在视图中查询数据

-- 在信息与计算机学院学生中，找到男学生的学号和姓名
select 学号,姓名 from 信息与计算机学院学生 where 性别 = '男';

-- 在各班总成绩和平均成绩中查找19电商1班学生的总成绩和平均成绩。
select * from 各班学生总成绩和平均成绩 where 所在班级 = '19电商1';

11.触发器

​ 触发器都与mysql数据表有关的数据库对象，在满足定义时触发，并执行触发器中定义的语句集合，触发器的这种特性可以应用在数据库端确保数据完整性。

​ 触发器是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，而是由事件触发。例如，当对一个表中进行操作(insert、delete、update)时就会激活它执行。

• 触发器语法四要素
  • 触发地点(table) 触发器对哪个表起作用
  • 触发事件（insert/update/delete）:对表执行什么操作触发。
  • 触发时间(after/before) 在表执行相应的操作前还是操作后触发
  • 触发器的程序本体(begin…and)：触发后执行的动作

​ 每个表中的每个事件每次都只允许一个触发器，所以，每个表最多支持6个触发器，即3个触发事件*2个触发时间。

1.创建触发器

1.基本

• 语法

  create trigger <触发器名> <before | after>
  <insert | update | delete>
  on <表名> for each row
  <触发器主题>
  

  • 触发器名指的是触发器名称，触发器在当前数据库中必须具有唯一的名称，如果在特定的某个数据库中创建，名称前应该加上数据库的名称。

  • insert | delete | update

    • insert

      将新行插入表时激活触发器

    • delete

      删除某一行数据时激活触发器，如delete和replace

    • update

      更改表中某一行数据时触发激活器

  • before | after

    若希望验证数据是否满足条件，则使用before选项；若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用after选项。

  • 表名

    与触发器关联的表名，必须是永久性表，不能将触发器与临时表或视图关联起来。

    在该表上触发事件发生时才会激活触发器，同一个表不能拥有两个具有相同触发时刻和事件的触发器。

  • 触发器主体

    指的触发器动作主体，包含触发器激活时将要执行的mysql语句，如果是多条语句，可是使用begin…and符合语句结构。

  • for each row

    代表行触发器

• 实例

-- 在学生表中创建一个触发器，每次插入一个学生记录，将保存学生人数
-- 的变量num的值加1
set @num = 10;-- 定义会话变量
select count(1) into @num from 学生;
select @num;

create trigger 学生_after_insert after insert on `学生`
for each ROW
set @num = @num+1;
show triggers;

insert into 学生(学号,姓名,性别)
values('090103','高林军','男'),('090104','何静','女');

2.利用触发器实现数据完整性

​ 触发器可以实现复杂的数据完整性约束，如对一个表执行行插入、修改、或删除操作时，需要级联更新另一个表中相关记录的指定字段值。在mysql中，由于不支持check约束，还需要用触发器实现check约束的功能。

​ mysql中，触发器不能使用显示或隐式开始或结束语句，如start transction、commit或rollback。因此，要实现回滚事务的操作，将更新后的表数据再还原更新前的状态，只能利用old或new关键字来访问受触发器影响的行获列，这两个关键字在insert、update和delete触发器中的含义不一样。

• insert触发器中，new用来表示将要（before）或已经（after）插入的新数据。
• update触发器中，old用来用来表示将要或已经被修改的数据，new用来表示将要或已经修改为的新数据
• delete触发器中，old用来表示将要或已经被删除的原数据。

​ insert中只能使用new,delete中只能使用old,update则两者都可以使用。

• 实例

-- 在学生表中创建一个触发器，要求插入的学生记录年龄在16-25岁
-- 如果带插入的记录不满足条件,则将年龄修改为20
create trigger 学生_before_insert before insert on `学生`
for each ROW
BEGIN
	if new.年龄 not between 16 and 25 THEN
		set new.年龄  = 20;
	end if;
end;

-- 另外一种写法,因为在插入后，又要将其修改，默认不能操作，方法不可行
create trigger 学生_after_insert after insert on `学生`
for each ROW
BEGIN
	if new.年龄 not between 16 and 25 THEN
			update 学生 set 年龄 = 20 where 学号 = new.学号;
	end if;
end;

show triggers;
drop trigger  学生_before_insert;
insert into 学生(学号,姓名,年龄)
values('090105','张三',99);



-- 在选课上创建触发器,要求一个学生最多选修5门课
-- 如果学生已经选修了5门课，那么当继续往选课表中插入数据时该学生的选课记录时
-- 操作记录失败，触发器应该选择after insert类型，由于mysql不能支持用rollback
-- 撤销事物，只能先执行插入操作，在将刚插入的记录删除。
create trigger 选课_after_insert after insert on `选课`
for each ROW
BEGIN
	if(select count(1) from 选课  where 学号 = new.学号 ) >= 6 THEN
		delete from 选课 where 学号 = new.学号 and 课程号 = new.课程号;
	end if;
end;
show triggers;
drop trigger 选课_after_insert;

select count(1) from 选课 where 学号 = '060101';


insert into 选课 values('060101','c05',77); -- 成功
insert into 选课 values('060101','c06',77); -- 失败

• 实现多个表中的完整性约束

  -- 在选课表中创建一个触发器，当表中插入一条触发器,当表中插入一条新记录
  -- 也就是某个选修了一门课，那么该学生成绩统计表中对应的课程数的字段值+1
  create trigger 选课_after_insert after insert on `选课`
  for each ROW
  BEGIN
  	update 学生成绩统计 set 课程数 = 课程数+1 where 学号 = new.学号;
  end;
  show triggers;
  drop trigger 选课_after_insert;
  
  insert into 选课 values('060101','c06',77); 
  
  
  -- 在选课中创建一个触发器,某个学生的成绩从不及格变为及格时，该学生成绩统计
  -- 的学分进行相应的更改
  
  
  create trigger 选课_after_update after update on 选课 
  for each row 
  begin 
  	if old.成绩 < 60 and new.成绩 >=60 THEN
  		update 学生成绩统计 set 已修学分 = 已修学分 +
  			(select 学分 from 课程 where 课程号 = new.课程号)
  			where 学号 = new.学号;
  	elseif( old.成绩 >= 60 and new.成绩 < 60) THEN
  			update 学生成绩统计 set 已修学分 = 已修学分 -
  			(select 学分 from 课程 where 课程号 = new.课程号)
  			where 学号 = new.学号;
  	end if;
  end;
  
  update 选课 set 成绩 = 22 where 学号 = '060101' and 课程号 = 'c04';
  
  

2.查看触发器

show triggers;

• 不能查询指定触发器

3.删除触发器

drop trigger 触发器名;

4.注意

• 触发器是已表中的记录为单位的，每一行的插入、修改、或删除都会使相应的触发器激活一次。例如一个表中插入100条记录，那么insert触发器会被执行100次，那么整个过程是非常耗时间的，所以在频繁跟新的表上，不要创建索引.(insert、update、delete)
• 触发器可能会关联到另外一个或几个表，使用触发器或导致数据库负担增加一倍或好几倍。因此，在设计触发器时，一定要尽量简化主体sql语句，以提高触发器效率，减轻负荷。
• 触发器不需要参数，不需要显示调用，在对指定表执行指定操作才能能触发，其他时候用户无法感知到，增加了系统的复杂性。

12.锁

13.事务

12.用户管理

1.权限表

2.账户管理

3.权限管理

4.访问控制

13.数据备份与恢复

1.数据备份

2.数据恢复

3.数据库迁移

4.表的导入/导出

14.mysql日志

1.日志简介

2.二进制日志

3.错误日志

4.通用查询日志

5.慢查询日志

15.性能优化

1.优化查询

2.优化数据结构

3.优化服务器

16.mysql复制

1.概述

2.windows环境复制

3.linux环境复制

4.日常管理和维护

5.主从服务器切换

17.mysql集群

1.概述

2.linux环境群安装和配置

3.管理集群

4.维护集群

5.windows环境配置集群

18.读写分离mysql

1.概述

2.安装和配置

3.实现读写分离

18.mysql中的变量值问题

insert into 选课 values(‘060101’,‘c05’,77); – 成功
insert into 选课 values(‘060101’,‘c06’,77); – 失败

* 实现多个表中的完整性约束

  ~~~sql
  -- 在选课表中创建一个触发器，当表中插入一条触发器,当表中插入一条新记录
  -- 也就是某个选修了一门课，那么该学生成绩统计表中对应的课程数的字段值+1
  create trigger 选课_after_insert after insert on `选课`
  for each ROW
  BEGIN
  	update 学生成绩统计 set 课程数 = 课程数+1 where 学号 = new.学号;
  end;
  show triggers;
  drop trigger 选课_after_insert;
  
  insert into 选课 values('060101','c06',77); 
  
  
  -- 在选课中创建一个触发器,某个学生的成绩从不及格变为及格时，该学生成绩统计
  -- 的学分进行相应的更改
  
  
  create trigger 选课_after_update after update on 选课 
  for each row 
  begin 
  	if old.成绩 < 60 and new.成绩 >=60 THEN
  		update 学生成绩统计 set 已修学分 = 已修学分 +
  			(select 学分 from 课程 where 课程号 = new.课程号)
  			where 学号 = new.学号;
  	elseif( old.成绩 >= 60 and new.成绩 < 60) THEN
  			update 学生成绩统计 set 已修学分 = 已修学分 -
  			(select 学分 from 课程 where 课程号 = new.课程号)
  			where 学号 = new.学号;
  	end if;
  end;
  
  update 选课 set 成绩 = 22 where 学号 = '060101' and 课程号 = 'c04';
  

2.查看触发器

show triggers;

• 不能查询指定触发器

3.删除触发器

drop trigger 触发器名;

4.注意

• 触发器是已表中的记录为单位的，每一行的插入、修改、或删除都会使相应的触发器激活一次。例如一个表中插入100条记录，那么insert触发器会被执行100次，那么整个过程是非常耗时间的，所以在频繁跟新的表上，不要创建索引.(insert、update、delete)
• 触发器可能会关联到另外一个或几个表，使用触发器或导致数据库负担增加一倍或好几倍。因此，在设计触发器时，一定要尽量简化主体sql语句，以提高触发器效率，减轻负荷。
• 触发器不需要参数，不需要显示调用，在对指定表执行指定操作才能能触发，其他时候用户无法感知到，增加了系统的复杂性。

12.锁

13.事务

12.用户管理

1.权限表

2.账户管理

3.权限管理

4.访问控制

13.数据备份与恢复

1.数据备份

2.数据恢复

3.数据库迁移

4.表的导入/导出

14.mysql日志

1.日志简介

2.二进制日志

3.错误日志

4.通用查询日志

5.慢查询日志

15.性能优化

1.优化查询

2.优化数据结构

3.优化服务器

16.mysql复制

1.概述

2.windows环境复制

3.linux环境复制

4.日常管理和维护

5.主从服务器切换

17.mysql集群

1.概述

2.linux环境群安装和配置

3.管理集群

4.维护集群

5.windows环境配置集群

18.读写分离mysql

1.概述

2.安装和配置

3.实现读写分离

18.mysql中的变量值问题