mysql日常管理事件_随笔

mysql日常管理事件

数据库处理(重点)

基础部分(必会):

建库建表
增删改查
索引聚合(索引 *** 作、聚合 *** 作)
高级查询
表关联关系
连接查询
数据类型
外键设置

高级部分(面试)

视图
函数存储过程
事务控制
优化
拆表
备份
用户创建
权限管理

数据存储

人工管理阶段

缺点：数据存储量有限，共享处理麻烦， *** 作容易混乱

文件管理阶段（.txt .doc .xls）

优点：数据可以长期保存,可以存储大量的数据,使用简单。

缺点：数据一致性差,数据查找修改不方便,数据冗余度可能比较大。

数据库管理阶段

优点：数据组织结构化降低了冗余度,提高了增删改查的效率,容易扩展,方便程序调用处理

缺点：需要使用sql 或者其他特定的语句，相对比较专业

基本概念

数据库：按照数据一定结构，存储管理数据的仓库。数据库是在数据库管理系统管理和控制下，在一定介质上的数据集合。
数据库管理系统：管理数据库的软件，用于建立和维护数据库。

关系型数据库和非关系型数据库

关系型：采用关系模型（二维表）来组织数据结构的数据库，如Oracle 、SQL_Server、 MySQL
非关系型：不采用关系模型组织数据结构的数据库，如：MongoDB、Redis

什么是SQL

结构化查询语言(Structured Query Language)，一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

数据库管理

1、查看已有库

show databases;

2、查看当前所存在的库

select database();

3、创建库

create database 库名 [character set utf8];

如：create database user character set utf8 collate utf8_bin;

CREATE DATAbase DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

解析1：mysql中有utf8和utf8mb4两种编码，在mysql中请大家忘记utf8，永远使用utf8mb4。这是mysql的一个遗留问题，mysql中的utf8最多只能支持3bytes长度的字符编码，对于一些需要占据4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行。

解析2：所谓utf8mb4_unicode_ci和utf8_bin，其实是用来排序的规则，用collate进行配置。

注意：库名的命名

数字、字母、下划线,但不能使用纯数字
库名区分字母大小写
不要使用特殊字符和mysql关键字

4、切换库

use 库名;

5、删除库

drop database 库名;

数据表管理

确定存储内容
明确字段构成
确定字段数据类型

基础数据类型

数字类型：

整数类型：INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT
浮点类型：FLOAT，DOUBLE，DECIMAL
比特值类型：BIT

注意：

对于准确性要求比较高的东西，比如money，用decimal类型减少存储误差。声明语法是DECIMAL(M,D)。M是数字的最大数字位数，D是小数点右侧数字的位数。比如 DECIMAL(6,2)最多存6位数字，小数点后占2位,取值范围-9999.99到9999.99。
比特值类型指0，1值表达2种情况，如真，假

字符串类型：

普通字符串： CHAR，VARCHAR
存储文本：TEXT
存储二进制数据： BLOB
存储选项型数据：ENUM，SET

注意：

char：定长，即指定存储字节数后，无论实际存储了多少字节数据，最终都占指定的字节大小。默认只能存1字节数据。存取效率高。
varchar：不定长，效率偏低，但是节省空间，实际占用空间根据实际存储数据大小而定。必须要指定存储大小 varchar(50)
enum用来存储给出的多个值中的一个值,即单选，enum('A','B','C')
set用来存储给出的多个值中一个或多个值，即多选，set('A','B','C')

6、创建表

create table 表名(字段名数据类型约束,字段名数据类型约束,...字段名数据类型约束);

建表案例：mysql 建表语句及完整案例_大蛇王的博客-CSDN博客_mysql建表语句

解析：

字段约束

如果你想设置数字为无符号则加上 UNSIGNED
如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在 *** 作数据库时如果输入该字段的数据为NULL ，就会报错。
DEFAULT 表示设置一个字段的默认值
COMMENT 增加字段说明
AUTO_INCREMENT 定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY 关键字用于定义列为主键。主键的值不能重复,且不能为空。

查看数据表

        show tables；

查看表结构

        desc 表名;

查看数据表创建信息

        show create table 表名；

删除表

        drop table 表名;

7、插入(insert)

insert into 表名 values(值1，值2...),(值1，值2...),...;
insert into 表名 (字段1,字段2...) values (值1，值2...),(值1，值2...);

8、查询(select)

select * from 表名 [where 条件];
select 字段1,字段2 from 表名 [where 条件];

9、where子句

算数运算符

select * from class where age % 2 = 0;

比较运算符
select * from class where age > 8;
select * from class where age between 8 and 10;
select * from class where age in (8,9);
逻辑运算符
select * from class where sex='m' and age>9;

10、更新表记录(update)

update 表名 set 字段1=值1,字段2=值2,... where 条件;

注意:update语句后如果不加where条件,所有表记录全部更新

11、删除表记录（delete）

delete from 表名 where 条件;

注意:delete语句后如果不加where条件,所有记录全部清空

12、表字段的 *** 作(alter)

语法：alter table 表名执行动作;

添加字段(add)

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

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

        alter table 表名 add 字段名数据类型 after 字段名;

删除字段(drop)

        alter table 表名 drop 字段名;

修改数据类型(modify)

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

修改字段名(change)

        alter table 表名 change 旧字段名新字段名新数据类型;

13、时间类型数据

日期： DATE
日期时间： DATETIME，TIMESTAMP
时间： TIME
年份：YEAR

时间格式

        date ："YYYY-MM-DD"

        time ："HH:MM:SS"

        datetime ："YYYY-MM-DD HH:MM:SS"

        timestamp ："YYYY-MM-DD HH:MM:SS"

创建带时间的表语句：

create table marathon (id int primary key auto_increment,athlete varchar(32),birthday date,registration_time datetime,performance time);

日期时间函数

        now() 返回服务器当前日期时间,格式对应datetime类型

时间 *** 作

        时间类型数据可以进行比较和排序等 *** 作，在写时间字符串时尽量按照标准格式书写
  select * from marathon where birthday>='2000-01-01';
  select * from marathon where birthday>="2000-07-01" and performance<="2:30:00";

高级查询语句

模糊查询

LIKE用于在where子句中进行模糊查询，SQL LIKE 子句中使用百分号%来表示任意0个或多个字符，下划线_表示任意一个字符。
SELECt field1, field2,...fieldN FROM table_name WHERe field1 LIKE condition1
select * from class where name like 'A%';
as 用法

在sql语句中as用于给字段或者表重命名
 select name as 姓名,age as 年龄 from class;
 select * from class as cls where cls.age > 17;
排序

ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

使用 ORDER BY 子句将查询数据排序后再返回数据：
SELECt field1, field2,...fieldN from table_name1 where field1 ORDER BY field1 [ASC [DESC]]
默认情况ASC表示升序，DESC表示降序
select * from class where sex='m' order by age desc;
复合排序：对多个字段排序，即当第一排序项相同时按照第二排序项排序
select * from class order by score desc,age;
限制

LIMIT 子句用于限制由 SELECt 语句返回的数据数量或者 UPDATE,DELETE语句的 *** 作数量带有 LIMIT 子句的 SELECT 语句的基本语法如下：
SELECT column1, column2, columnN FROM table_name WHERe field LIMIT [num] [OFFSET num]
 # 查询班级男生第三名
select * from cls where sex='m' order by score desc limit 1 offset 2;
联合查询

UNIOn *** 作符用于连接两个以上的 SELECt 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

UNIOn *** 作符语法格式：
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERe conditions]
UNIOn [ALL | DISTINCT]
SELECt expression1, expression2, ... expression_n
FROM tables
[WHERe conditions];
默认UNIOn后为 DISTINCT表示删除结果集中重复的数据。如果使用ALL则返回所有结果集，包含重复数据。
select * from class where sex='m' UNIOn ALL select * from class_1 where age > 9;
子查询

        定义：当一个语句中包含另一个select 查询语句，则称之为有子查询的语句

        子查询使用位置：

        1、from 之后，此时子查询的内容作为一个新的表内容，再进行外层select查询

        注意：需要将子查询结果集重命名一下，方便where子句中的引用 *** 作
select name from (select * from class where sex='m') as s where s.score > 90;
2、where子句中，此时select查询到的内容作为外层查询的条件值
 select *  from class where age = (select age from class where name='Tom');
 select * from class where name in (select name from hobby);
查询过程
(5)SELECt DISTINCT                      
(1)FROM   JOIN  ON 
(2)WHERe 
(3)GROUP BY 
(4)HAVINg 
(6)ORDER BY 
(7)LIMIT 

高级查询练习

在sanguo下创建数据报表 sanguo
创建库：

CREATE DATAbase sanguo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

字段：id  name  gender  country  attack  defense

创建表：

use sanguo;
create table sanguo(
id int primary key auto_increment,
name varchar(30),
gender enum('男','女'),
country enum('魏','蜀','吴'),
attack smallint,
defense tinyint
);

插入数据：

insert into sanguo
values (1, '曹 *** ', '男', '魏', 256, 63),
       (2, '张辽', '男', '魏', 328, 69),
       (3, '甄姬', '女', '魏', 168, 34),
       (4, '夏侯渊', '男', '魏', 366, 83),
       (5, '刘备', '男', '蜀', 220, 59),
       (6, '诸葛亮', '男', '蜀', 170, 54),
       (7, '赵云', '男', '蜀', 377, 66),
       (8, '张飞', '男', '蜀', 370, 80),
       (9, '孙尚香', '女', '蜀', 249, 62),
       (10, '大乔', '女', '吴', 190, 44),
       (11, '小乔', '女', '吴', 188, 39),
       (12, '周瑜', '男', '吴', 303, 60),
       (13, '吕蒙', '男', '吴', 330, 71);

查找练习
1. 查找所有蜀国人信息，按照攻击力倒序排名

select * from sanguo where country = '蜀' order by attack desc;

2. 将赵云攻击力设置为360，防御设置为70

update sanguo set attack = 360, defense = 70 where name = '赵云';

3. 吴国英雄攻击力超过300的改为300，最多改2个

update sanguo set attack = 300 where country = '吴' and attack > 300 limit 2;

4. 查找攻击力超过200的魏国英雄名字和攻击力并显示为姓名，攻击力

select name as 姓名,attack as 攻击力 from sanguo
where
country ='魏' and attack > 200;

5. 所有英雄按照攻击力降序排序，如果相同则按照防御生序排序

select * from sanguo order by attack desc, defense;

6. 查找名字为3个字的姓名

select * from sanguo where name like "___";  # 3个下划线_

7. 查找攻击力比魏国最高攻击力的人还要高的蜀国英雄

select * from sanguo where country = '蜀' and attack > 
(select attack from sanguo where country = '魏' order by attack desc limit 1);

8. 找到魏国防御力排名2-3名的英雄

select * from sanguo where country = '魏'
order by defense desc limit 2 offset 1;

9. 查找所有女性角色中攻击力大于180的和男性中攻击力小于250的

select * from sanguo where gender = '女' and attack > 180 
union 
select * from sanguo where gender = '男' and attack < 250;

聚合 *** 作

聚合 *** 作指的是在数据查找基础上对数据的进一步整理筛选行为，实际上聚合 *** 作也属于数据的查询筛选范围。

聚合函数
方法功能avg(字段名)该字段的平均值max(字段名)该字段的最大值min(字段名)该字段的最小值sum(字段名)该字段所有记录的和count(字段名)统计该字段记录的个数
1 : 找出表中的最大攻击力的值？
select max(attack) from sanguo;
2 : 表中共有多少个英雄？
select count(name) as number from sanguo;
3 : 蜀国英雄中攻击值大于200的英雄的数量 ?
select count(*) from sanguo where attack > 200;
注意：此时select 后只能写聚合函数，无法查找其他字段，除非该字段值全都一样。

聚合分组

group by : 给查询的结果进行分组

1 : 计算每个国家的平均攻击力
select country,avg(attack) from sanguo group by country;
2 : 对多个字段创建分组，此时多个字段都相同时为一组
select gender,country,count(*) from sanguo  group by gender,country;
3 : 所有国家的男英雄中英雄数量最多的前2名的国家名称及英雄数量
select country,count(id) as number from sanguo 
where gender='男'
group by country order by number DESC limit 2;
注意：使用分组时select 后的字段为group by分组的字段和聚合函数，不能包含其他内容。group by也可以同时依照多个字段分组，如group by A，B 此时必须A,B两个字段值均相同才算一组。

聚合筛选

having语句 : 对分组聚合后的结果进行进一步筛选
1 : 找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力
select country,avg(attack) from sanguo 
group by country
having avg(attack) > 105
order by avg(attack) DESC limit 2;
注意

having语句必须与group by联合使用。
having语句存在弥补了where关键字不能与聚合函数联合使用的不足,where只能 *** 作表中实际存在的字段。

去重语句

distinct语句 : 不显示字段重复值

1 : 表中都有哪些国家

select distinct country from sanguo;

2 : 计算一共有多少个国家

select count(distinct country) from sanguo;

索引 *** 作

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

优缺点

优点：加快数据检索速度,提高查找效率
缺点：占用数据库物理存储空间，当对表中数据更新时,索引需要动态维护,降低数据写入效率

注意：

通常我们只在经常进行查询 *** 作的字段上创建索引
对于数据量很少的表或者经常进行写 *** 作而不是查询 *** 作的表不适合创建索引

索引分类

普通(MUL)

普通索引：字段值无约束,KEY标志为 MUL

唯一索引(UNI)

唯一索引(unique) ：字段值不允许重复,但可为 NULL,KEY标志为 UNI

主键索引（PRI）

一个表中只能有一个主键字段, 主键字段不允许重复,且不能为NULL，KEY标志为PRI。通常设置记录编号字段id,能唯一锁定一条记录

索引创建

创建表时直接创建索引

create table 表名(
字段名 数据类型，
字段名 数据类型，
index 索引名(字段名),
index 索引名(字段名),
unique 索引名(字段名)
);

在已有表中创建索引：

create [unique] index 索引名 on 表名(字段名);

create unique index name_index on class(name);

主键索引添加

alter table 表名 add primary key(id);

查看索引

1、desc 表名; --> KEY标志为：MUL 、UNI。

2、show index from 表名;

删除索引

drop index 索引名 on 表名;

alter table 表名 drop primary key;  # 删除主键

扩展： 借助性能查看选项去查看索引性能

set  profiling = 1； 打开功能 （项目上线一般不打开）

show profiles  查看语句执行信息

外键约束

约束 : 约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、关联性
foreign key 功能 : 建立表与表之间的某种约束的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强，为了具体说明创建如下部门表和人员表
主表和从表：若同一个数据库中，B表的外键与A表的主键相对应，则A表为主表，B表为从表。

foreign key 外键的定义语法：
[ConSTRAINT symbol] FOREIGN KEY（外键字段） 
REFERENCES tbl_name (主表主键)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
创建部门表
CREATE TABLE dept (id int PRIMARY KEY auto_increment,dname VARCHAr(50) not null);
创建人员表
1、创建表时直接建立外键
CREATE TABLE person (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(32) NOT NULL,
  age tinyint unsigned,
  salary decimal(10,2),
  dept_id int,
  constraint dept_fk foreign key(dept_id) references dept(id)
);
2、建立表后增加外键
alter table person add constraint dept_fk foreign key(dept_id) references dept(id);
注意：

并不是任何情况表关系都需要建立外键来约束，如果没有类似上面的约束关系时也可以不建立。
从表的外键字段数据类型与指定的主表主键应该相同。

通过外键名称解除外键约束
alter table person drop foreign key dept_fk;
查看外键名称
show create table person;
注意：删除外键后发现desc查看索引标志还在，其实外键也是一种索引，需要将外键名称的索引删除之后才可以。

级联动作

restrict(默认) : on delete restrict on update restrict

当主表删除记录时，如果从表中有相关联记录则不允许主表删除
当主表更改主键字段值时，如果从表有相关记录则不允许更改

cascade ：数据级联更新 on delete cascade on update cascade

当主表删除记录或更改被参照字段的值时,从表会级联更新

set null : on delete set null on update set null

当主表删除记录时，从表外键字段值变为null
当主表更改主键字段值时，从表外键字段值变为null

表关联关系

当我们应对复杂的数据关系的时候，数据表的设计就显得尤为重要，认识数据之间的依赖关系是更加合理创建数据表关联性的前提。一对多和多对多是常见的表数据关系：

一对多关系

        一张表中有一条记录可以对应另外一张表中的多条记录；但是反过来，另外一张表的一条记录只能对应第一张表的一条记录，这种关系就是一对多或多对一

        举例：一个人可以拥有多辆汽车，每辆车登记的车主只有一人。

多对多关系

        一对表中（A）的一条记录能够对应另外一张表（B）中的多条记录；同时B表中的一条记录也能对应A表中的多条记录

        举例：一个运动员可以报多个项目，每个项目也会有多个运动员参加,这时为了表达多对多关系需要单独创建关系表。

E-R模型图

定义

E-R模型(Entry-Relationship)即实体-关系数据模型,用于数据库设计用简单的图(E-R图)反映了现实世界中存在的事物或数据以及他们之间的关系

实体、属性、关系

        实体

                1、描述客观事物的概念

                2、表示方法：矩形框

                3、示例：一个人、一本书、一杯咖啡、一个学生

        属性

                1、实体具有的某种特性

                2、表示方法：椭圆形

        示例

                学生属性：学号、姓名、年龄、性别、专业 ...

                感受属性：悲伤、喜悦、刺激、愤怒 ...

        关系

                1、实体之间的联系

                2、一对多关联(1:n)

                3、多对多关联(m:n)

E-R图的绘制

矩形框代表实体,菱形框代表关系,椭圆形代表属性

表关联查询

如果多个表存在一定关联关系，可以多表在一起进行查询 *** 作，其实表的关联整理与外键约束之间并没有必然联系，但是基于外键约束设计的具有关联性的表往往会更多使用关联查询查找数据。

添加测试数据：

CREATE TABLE `class` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` char(50) COLLATE utf8_bin NOT NULL,
  `age` tinyint unsigned NOT NULL,
  `sex` enum('m','w','o') COLLATE utf8_bin DEFAULT NULL,
  `score` float DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;


INSERT INTO `class` VALUES (1,'Lily',19,'w',88),
(2,'Tom',18,'w',88),
(3,'Emma',19,'w',91),
(4,'Lucy',21,'w',55),
(5,'James',20,'m',66),
(6,'Abby',20,'w',66),
(7,'Alex',19,'w',92),
(10,'Tonny',18,'m',61),
(11,'Tonnr',18,'m',62),
(12,'Tonnp',18,'m',61),
(13,'Tonni',18,'m',61),
(14,'Tonni',18,'m',61),
(15,'Tonni',18,'m',61),
(16,'Tonni',18,'m',61);



CREATE TABLE `hobby` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_bin NOT NULL,
  `hobby` set('sing','dance','draw') COLLATE utf8_bin DEFAULT NULL,
  `level` char(1) COLLATE utf8_bin DEFAULT NULL COMMENT 'A, B or C',
  `price` decimal(7,2) DEFAULT NULL,
  `sex` enum('m','w','o') COLLATE utf8_bin DEFAULT NULL,
  `remark` text COLLATE utf8_bin COMMENT '备注信息',
  `phone` char(16) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;


INSERT INTO `hobby` VALUES 
(1,'Joy','sing,dance','A',56000.00,NULL,'骨骼惊奇，练舞奇才',NULL),
(2,'Emma','sing','B',26000.89,NULL,'天籁之音',NULL),
(3,'Lily','draw','C',9800.99,NULL,'当代达芬奇',NULL),
(4,'Ben','dance,draw',NULL,40000.00,NULL,NULL,NULL),
(5,'Tom','dance',NULL,12000.00,NULL,NULL,NULL),
(6,'Levi','draw',NULL,9900.00,NULL,NULL,NULL);



CREATE TABLE `dept` (
  `id` int NOT NULL AUTO_INCREMENT,
  `dname` varchar(50) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;

INSERT INTO `dept` VALUES (1,'技术部'),(2,'销售部'),
(3,'市场部'),(4,'行政部'),(5,'财务部'),(6,'总裁办公室');



CREATE TABLE `person` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) COLLATE utf8_bin NOT NULL,
  `age` tinyint unsigned DEFAULT NULL,
  `salary` decimal(8,2) DEFAULT NULL,
  `dept_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dept_fk` (`dept_id`),
  ConSTRAINT `dept_fk` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;


INSERT INTO `person` VALUES (1,'Lily',29,20000.00,2),
(2,'Tom',27,16000.00,1),(3,'Joy',30,28000.00,1),
(4,'Emma',24,8000.00,4),(5,'Abby',28,17000.00,NULL),
(6,'Jame',32,22000.00,NULL),(7,'Alex',30,18200.00,5);

简单多表查询

多个表数据可以联合查询，语法格式如下：

select 字段1,字段2... from 表1,表2... [where 条件]

笛卡尔积现象就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。

select * from class,hobby;

select * from class, hobby where class.name = hobby.name;

内连接

内连接查询只会查找到符合条件的记录，其实结果和表关联查询是一样的,官方更推荐使用内连接查询。

SELECt 字段列表
    FROM 表1  INNER JOIN  表2
ON 表1.字段 = 表2.字段;

select * from person inner join dept on person.dept_id =dept.id;

左连接 : 左表全部显示，显示右表中与左表匹配的项

SELECt 字段列表
    FROM 表1  LEFT JOIN  表2
ON 表1.字段 = 表2.字段;

select * from person left join dept on person.dept_id =dept.id;

# 查询每个部门员工人数
select dname,count(name) from dept
left join person on 
dept.id=person.dept_id group by dname;

右连接：右表全部显示，显示左表中与右表匹配的项

SELECt 字段列表
    FROM 表1  RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;

select * from person right join dept on person.dept_id =dept.id;

注意：我们尽量使用数据量大的表作为基准表,放在前面。

综合联系

测试数据

CREATE TABLE `class` (
  `cid` int NOT NULL AUTO_INCREMENT,
  `caption` char(4) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;

INSERT INTO `class` VALUES
(1,'三年二班'),(2,'三年三班'),(3,'三年一班');


CREATE TABLE `course` (
  `cid` int NOT NULL AUTO_INCREMENT,
  `cname` varchar(16) COLLATE utf8_bin NOT NULL,
  `teacher_id` int DEFAULT NULL,
  PRIMARY KEY (`cid`),
  KEY `teacher_id` (`teacher_id`),
  ConSTRAINT `course_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ON DELETe CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;


INSERT INTO `course` VALUES (1,'生物',1),(2,'体育',1),(3,'物理',2);


CREATE TABLE `score` (
  `sid` int NOT NULL AUTO_INCREMENT,
  `student_id` int DEFAULT NULL,
  `course_id` int DEFAULT NULL,
  `number` int NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `student_id` (`student_id`),
  KEY `course_id` (`course_id`),
  ConSTRAINT `score_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ON DELETE CASCADE ON UPDATE CASCADE,
  ConSTRAINT `score_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;

INSERT INTO `score` VALUES (1,1,1,60),(2,1,2,59),(3,2,2,100),(4,3,2,78),(5,4,3,66);


CREATE TABLE `student` (
  `sid` int NOT NULL AUTO_INCREMENT,
  `sname` varchar(32) COLLATE utf8_bin NOT NULL,
  `gender` enum('male','female','others') COLLATE utf8_bin NOT NULL DEFAULT 'male',
  `class_id` int DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `class_id` (`class_id`),
  ConSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;


INSERT INTO `student` VALUES
(1,'钢蛋','female',1),(2,'铁锤','female',1),
(3,'山炮','male',2),(4,'彪哥','male',3);


CREATE TABLE `teacher` (
  `tid` int NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;


INSERT INTO `teacher` VALUES (1,'波多老师'),(2,'苍老师'),(3,'小泽老师');

1. 查询每位老师教授的课程数量

select teacher.tname, count(course.cname) from teacher
left join course on
teacher.tid = course.teacher_id
group by teacher.tname;

2. 查询学生的信息及学生所在班级信息

select student.sid, student.sname, class.caption
from student left join class
on class.cid = student.class_id;

3. 查询各科成绩最高和最低的分数,形式 : 课程ID  课程名称 最高分  最低分

select course.cid as 课程ID, course.cname as 课程名称,
max(score.number) as 最高分, min(score.number) as  最低分
from course left join score
on course.cid = score.course_id
group by course.cid, course.cname;

4. 查询平均成绩大于85分的所有学生学号,姓名和平均成绩

select student.sid, student.sname, avg(score.number)
from student left join score
on student.sid = score.student_id
group by student.sid, student.sname
having avg(score.number) > 85;

5. 查询课程编号为2且课程成绩在80以上的学生学号和姓名

select student.sid, student.sname
from student left join score
on student.sid = score.student_id
where score.course_id=2 and score.number > 80;

6. 查询各个课程及相应的选修人数

select course.cname, count(score.course_id)
from course left join score
on course.cid = score.course_id
group by course.cname;

7. 查询每位学生的姓名，所在班级和各科平均成绩

  select student.sname, class.caption, avg(score.number)
  from student left join class
  on student.class_id = class.cid
  left join score
  on student.sid = score.student_id
  group by student.sname, class.caption;

视图

视图概念

视图是存储的查询语句,当调用的时候,产生结果集,视图充当的是虚拟表的角色。其实视图可以理解为一个表或多个表中导出来的表，作用和真实表一样，包含一系列带有行和列的数据视图中，用户可以使用SELECt语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户 *** 作方便，并保障数据库系统安全，如果原表改名或者删除则视图也失效。

创建视图
语法结构：

CREATE [OR REPLACE] VIEW [view_name] AS [SELECT_STATEMENT];

释义：

CREATE VIEW： 创建视图
OR REPLACE : 可选，如果添加原来有同名视图的情况下会覆盖掉原有视图
view_name ： 视图名称
SELECT_STATEMENT ：SELECT语句

例子：
create view good_stu as select name,age,score from class where score > 85;
视图表的增删改查 *** 作

视图的增删改查 *** 作与一般表的 *** 作相同，使用insert update delete select即可，但是原数据表的约束条件仍然对视图产生作用。

查看现有视图
show full tables in stu where table_type like 'VIEW';
删除视图

drop view [IF EXISTS] 视图名；

IF EXISTS 表示如果存在，这样即使没有指定视图也不会报错。
drop view if exists good_stu;
修改视图

参考创建视图，将create关键字改为alter
alter view  good_stu as select name,age,score from class;
视图作用

作用

是对数据的一种重构，不影响原数据表的使用。

简化高频复杂 *** 作的过程，就像一种对复杂 *** 作的封装。

提高安全性，可以给不同用户提供不同的视图。

让数据更加清晰。

缺点

视图的性能相对较差，从数据库视图查询数据可能会很慢。

函数和存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合，调用存储过程和函数可以简化应用开发工作，提高数据处理的效率。

函数创建

delimiter 自定义符号

create function 函数名(形参列表) returns 返回类型　　-- 注意是retruns
begin
　　函数体   -- 若干sql语句，但是不要直接写查询
　　return val;
end  自定义符号
delimiter ;

释义：
        delimiter 自定义符号 是为了在函数内些语句方便，制定除了;之外的符号作为函数书写结束标志,一般用$$或者//

形参列表 ： 形参名 类型   类型为mysql支持类型

返回类型:  函数返回的数据类型,mysql支持类型即可

函数体： 若干sql语句组成

return: 返回指定类型返回值

例子 1、无参数的函数调用

delimiter $$
create function st() returns int 
begin 
return (select score from class order by score desc limit 1); 
end $$
delimiter ;

select st();

例子 2、含有参数的函数调用

delimiter $$
create function queryNameById(uid int) 
returns varchar(20)
begin
return  (select name from class where id=uid);
end $$
delimiter ;

select queryNameById(1);

设置变量
- 定义用户变量： set @[变量名] = 值；使用时用@[变量名]。
- 定义局部变量：在函数内部设置 declare [变量名] [变量类型]; 局部变量可以使用set赋值或者使用into关键字。

存储过程创建

创建存储过程语法与创建函数基本相同，但是没有返回值。
delimiter 自定义符号　

create procedure 存储过程名(形参列表)
begin
　   存储过程　　　　-- sql语句构成存储过程语句集
end  自定义符号

delimiter ;
释义：
delimiter 自定义符号 是为了在函数内些语句方便，制定除了;之外的符号作为函数书写结束标志

形参列表 ：[ IN | OUT | INOUT ] 形参名 类型
         in 输入，out  输出，inout 可以输入也可以输出

存储过程： 若干sql语句组成，如果只有一条语句也可以不写delimiter和begin,end
例子 1、存储过程创建和调用
delimiter $$
create procedure st() 
begin 
    select name,age from class; 
    select name,score from class order by score desc; 
end $$
delimiter ;

call st();
存储过程三个参数的区别

IN 类型参数可以接收变量也可以接收常量，传入的参数在存储过程内部使用即可，但是在存储过程内部的修改无法传递到外部。

OUT 类型参数只能接收一个变量，接收的变量不能够在存储过程内部使用（内部为NULL），但是可以在存储过程内对这个变量进行修改。因为定义的变量是全局的，所以外部可以获取这个修改后的值。

INOUT类型参数同样只能接收一个变量，但是这个变量可以在存储过程内部使用。在存储过程内部的修改也会传递到外部。

例子 2、分别将参数类型改为IN OUT INOUT 看一下结果区别
delimiter $$
create procedure p_out ( OUT num int )
begin
    select num;
    set num=100;
    select num;
end $$

delimiter ;

set @num=10;
call p_out(@num)
存储过程和存储函数 *** 作

        1、调用存储过程

        语法：

                call 存储过程名字（[存储过程的参数[,……]])

        2、调用存储函数

        语法：

                select 存储函数名字（[函数的参数[,……]])

3、使用show create语句查看存储过程和函数的定义

        语法：

                show create {procedure|function} 存储过程或存储函数的名称

        4、查看所有函数或者存储过程
        select name,type from mysql.proc where db='stu';
5、删除存储过程或存储函数

语法：
        DROp {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

函数和存储过程区别

函数有且只有一个返回值，而存储过程不能有返回值。
函数只能有普通参数，而存储过程可以有in,out,inout多个类型参数。
存储过程中的语句功能更丰富，实现更复杂的业务逻辑，可以理解为一个按照预定步骤调用的执行过程，而函数中不能展示查询结果集语句，只是完成查询的工作后返回一个结果，功能针对性比较强。
存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。

事务概述

MySQL 事务主要用于处理 *** 作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，如果 *** 作就必须同时 *** 作成功，如果有一个不成功则所有数据都不动。这时候数据库 *** 作语句就构成一个事务。事务主要处理数据的增删改 *** 作。

定义

一件事从开始发生到结束的过程

作用

确保数据 *** 作过程中的数据完整和使用安全。

事务 *** 作

1、开启事务

          mysql>begin;

2、开始执行事务中的若干条SQL命令（增删改）

3、终止事务，若begin之后使用commit提交事务或者使用rollback进行事务回滚。

        mysql>commit; # 事务中SQL命令都执行成功,提交到数据库,结束!

        mysql>rollback; # 有SQL命令执行失败,回滚到初始状态,结束!

注意：事务 *** 作只针对数据 *** 作。rollback不能对数据库，数据表结构 *** 作恢复。

事务四大特性

原子性（atomicity）

一个事务必须视为一个不可分割的最小工作单元，对于一个事务来说，不可能只执行其中的一部分 *** 作,整个事务中的所有 *** 作要么全部提交成功，要么全部失败回滚

一致性（consistency）

事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏。

隔离性（isolation）

数据库允许多个并发事务同时对其数据进行读写和修改的能力，而多个事务相互独立。隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

持久性（durability）

一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

事务隔离级别

事务四大特性中的隔离性是在使用事务时最为需要注意的特性，因为隔离级别不同带来的 *** 作现象也有区别

隔离级别

读未提交：read uncommitted

事物A和事物B，事物A未提交的数据，事物B可以读取到这里读取到的数据叫做“脏数据” 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

读已提交：read committed

事物A和事物B，事物A提交的数据，事物B才能读取到这种隔离级别高于读未提交换句话说，对方事物提交之后的数据，我当前事物才能读取到这种级别可以避免“脏数据” 这种隔离级别会导致“不可重复读取”

可重复读：repeatable read

事务A和事务B，事务A提交之后的数据，事务B读取不到事务B是可重复读取数据这种隔离级别高于读已提交 MySQL默认级别虽然可以达到可重复读取，但是会导致“幻像读”

数据库优化

数据库设计范式
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

各种范式呈递次规范，越高的范式数据库冗余越小。但是范式越高也意味着表的划分更细，一个数据库中需要的表也就越多，此时多个表联接在一起的花费是巨大的，尤其是当需要连接的两张或者多张表数据非常庞大的时候，表连接 *** 作几乎是一个噩梦，这严重地降低了系统运行性能。所以通常数据库设计遵循第一第二第三范式，以避免数据 *** 作异常,又不至于表关系过于复杂。

范式简介：

第一范式：数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等组合的数据项。简单来说要求数据库中的表示二维表，每个数据元素不可再分。

例如：在国内的话通常理解都是姓名是一个不可再拆分的单位，这时候就符合第一范式；但是在国外的话还要分为FIRST NAME和LAST NAME，这时候姓名这个字段就是还可以拆分为更小的单位的字段，就不符合第一范式了。

第二范式：第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分，所有属性依赖于主属性。即选取一个能区分每个实体的属性或属性组，作为实体的唯一标识，每个属性都能被主属性筛选。其实简单理解要设置一个区分各个记录的主键就好了。

第三范式：在第二范式的基础上属性不传递依赖，即每个属性不依赖其他非主属性。要求一个表中不包含已在其它表中包含的非主关键字信息。其实简单来说就是合理使用外键，使不同的表中不要有重复的字段就好了。

MySQL存储引擎

1、查看所有存储引擎
   mysql> show engines;

2、查看已有表的存储引擎
   mysql> show create table 表名;

3、创建表指定
   create table 表名(...)engine=MyISAM;

4、已有表指定
   alter table 表名 engine=InnoDB;
常用存储引擎特点

InnoDB

        1. 支持行级锁,仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行 *** 作。

        2. 支持外键、事务、事务回滚

        3. 表字段和索引同存储在一个文件中

                1. 表名.frm ：表结构
                 2. 表名.ibd : 表记录及索引文件

        MyISAM

        1. 支持表级锁,在锁定期间，其它进程无法对该表进行写 *** 作。如果你是写锁，则其它进程则读也不允许

        2. 表字段和索引分开存储

                1. 表名.frm ：表结构

                2. 表名.MYI : 索引文件(my index)

                3. 表名.MYD : 表记录(my data)
如何选择存储引擎
1. 执行查 *** 作多的表用 MyISAM(使用InnoDB浪费资源)
2. 执行写 *** 作多的表用 InnoDB
    CREATE TABLE test_db(
    id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(30) DEFAULT NULL,
    sex varchar(2) DEFAULT NULL,
    PRIMARY KEY (id)
    )ENGINE=MyISAM;

字段数据类型和键的选择

数据类型优先程度数字类型 --> 时间日期类型 --> 字符串类型
同一级别占用空间小的 --> 占用空间大的
1、字符串在查询比较排序时数据处理慢
2、占用空间少，数据库占磁盘页少，读写处理就更快
Innodb如果不设置主键也会自己设置隐含的主键，所以最好自己设置
尽量设置占用空间小的字段为主键
建立外键会自动建立索引，在表关联查询时建议使用外键子段作为关联条件
外键虽然可以保持数据完整性，但是会降低数据导入和 *** 作效率，增加维护成本

explain语句

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:

表的读取顺序
数据读取 *** 作的 *** 作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
explain select * from class where id < 5;
explain select * from class where id = 5;
EXPLAIN主要字段解析：

table：显示这一行的数据是关于哪张表的
type：这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL，一般来说，得保证查询至少达到range级别，最好能达到ref。
type中包含的值：
        - system、const： 可以将查询的变量转为常量. 如id=1; id为 主键或唯一键.
        - eq_ref： 访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或唯一键)
        - ref： 访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生 
        - range： 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西，并且该字段上建有索引时发生的情况
        - index： 以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描 
        - ALL： 全表扫描，应该尽量避免
possible_keys：显示可能应用在这张表中的索引。如果为空，表示没有可能应用的索引。
key：实际使用的索引。如果为NULL，则没有使用索引。
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
rows：MySQL认为必须检索的用来返回请求数据的行数

SQL优化

尽量选择数据类型占空间少，在where ，group by，order by中出现的频率高的字段建立索引

尽量避免使用 select * ...;用具体字段代替 * ,不要返回用不到的任何字段

尽量控制使用自定义函数

查询最后添加 LIMIT 会停止全表扫描

尽量避免 NULL 值判断,否则会进行全表扫描，默认值为空时可以用默认0代替

优化前：select number from t1 where number is null;

优化后：select number from t1 where number=0;

尽量避免 or 连接条件,否则会放弃索引进行全表扫描，可以用union代替

优化前：select id from t1 where id=10 or id=20;

优化后： select id from t1 where id=10 union all select id from t1 where id=20;

尽量避免使用 in 和 not in,否则会全表扫描

优化前：select id from t1 where id in (1,2,3,4);

优化后：select id from t1 where id between 1 and 4;

表的拆分

垂直拆分：表中列太多，分为多个表，每个表是其中的几个列。将常查询的放到一起，blob或者text类型字段放到另一个表

水平拆分：减少每个表的数据量，通过关键字进行划分然后拆成多个表

数据库安全和管理

（1）表的复制

表能根据实际需求复制数据
复制表时不会把KEY属性复制过来

语法
create table 表名 select 查询命令;
（2）数据库备份

备份文档：1--MySQL基础知识内容_moyuanbomo的博客-CSDN博客

添加用户和授权

1. 用root用户登录mysql

mysql -u root -p

2. 添加用户 % 表示自动选择可用IP

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

3. 权限管理
  
   - 增加权限

grant 权限列表 on 库.表 to "用户名"@"%" identified by "密码" with grant option;

   - 删除权限

revoke insert,update,select on 库.表 from 'user'@'%';

4. 刷新权限

flush privileges;

5. 删除用户

drop user "用户名"@"%"

权限列表

all privileges ，select ，insert ，update，delete，alter，create，drop等。
库.表 ： *.* 代表所有库的所有表

示例

1. 创建用户

mysql>create user  'work'@'%'  identified by '123';

mysql>flush privileges;

2. 添加授权用户work,密码123,对所有库的所有表有所有权限

mysql>grant all privileges on *.* to 'work'@'%' identified by '123' with grant option;

mysql>flush privileges;

3. 添加用户duty，密码123,对books库中所有表有查看，插入权限

mysql>grant select,insert on books.* to 'duty'@'%' identified by '123' with grant option;
 
mysql>flush privileges;

4. 删除work用户的删除权限

 mysql>revoke delete on *.* from "work"@"%";

5. 删除用户duty

drop user "duty"@"%";

欢迎分享，转载请注明来源：内存溢出

原文地址: https://outofmemory.cn/zaji/5672669.html

mysql日常管理事件

发表评论

评论列表（0条）