目录

一、数据库操作

1.查看数据库版本号

2.创建数据库

3.查看指定的数据库

4.查看所有的数据库

5.删除指定的数据库

6.使用指定的数据库

7.数据库存储引擎介绍

二、数据库表说明

1.数据库表常见的列类型

2.数据库表的字段属性

三、数据库表操作

1.创建数据库表

2.查看表的定义

3.查看创建表的语句

4.修改表名

5.修改表字段名称

6.修改表字段数据类型

7.增加表字段

8.删除表字段

9.删除表 

四、数据库表的约束

1.唯一约束

2.非空约束

3.主键约束

4.外键约束

5.默认值约束

6.自增约束

五、数据表数据操作

1.插入数据

2.更新数据

3.删除操作

六、数据表数据查询

1.基本语法

2.数据准备

3.基本查询

4.按字段查询

5.字段别名操作

6.数据去重

7.聚合统计

8.条件查询

9.分组查询

10.排序查询

11.限制查询

12.关联查询

七、数据库的事务

1.事务的四大特性

2.事务并发的问题

3.事务隔离级别

八、数据库的索引

1.索引的分类

2.索引的删除

3.索引分析 

九、数据库的权限管理

1.查看用户

 2.创建用户

 3.赋予权限

 4.更改密码

 5.删除用户

---

一、数据库操作

1.查看数据库版本号

select version();

2.创建数据库

create database 数据库名称

3.查看指定的数据库

show create database 数据库名称

4.查看所有的数据库

show databases

5.删除指定的数据库

drop database 数据库名称  

6.使用指定的数据库

use 数据库名称 

7.数据库存储引擎介绍

数据库存储引擎指的是具有存储、处理和保护数据的核心服务

①. 显示支持的引擎

show engines;

②. 常用的InnoDB和MyISAM区别(下面关于表操作都是基于InnoDB实现)

InnoDB支持事务、数据行的锁定、外键约束

MyISAM的表空间小、速度较快、支持全文索引

二、数据库表说明

1.数据库表常见的列类型

	数据类型	描述
数值类型	int	整数
	mediumint	中等的整数
	smallint	小的整数
	tinyint	非常小的整数
	bigint	大的整数
	float	浮点数
	double	双精度浮点数
	decimal	精确的数值
字符串类型	char	固定大小的字符串
	varchar	可变的字符串
	tinytext	非常小的文本
	text	文本
	longtext	大的文本
二进制类型	blob	二进制数据
	mediumblob	中等的二进制数据
	tinyblob	非常小的二进制数据
	longblob	大的二进制数据
时间类型	date	日期格式(YYYY-MM-DD)
	time	时间格式(HH：mm：ss)
	datetime	时间(YYYY-MM-DD HH：mm：ss)
	timestamp	时间戳
	year	年份

2.数据库表的字段属性

字段属性	描述
AUTO_INCREMENT	自动增长，默认在上一条记录的数值上加1
zerofill	不足位数,自动填充0
unsigned	无符号
NOT NULL	限制字段的内容不能为空
DEFAULT	设置默认值.若插入数据为空，则按默认值填充

三、数据库表操作

1.创建数据库表

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(60) NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `sex` int(1) DEFAULT '1' COMMENT '性别(1是男 0是女 )',
  `seat` int(3) unsigned zerofill NOT NULL COMMENT '座位',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

2.查看表的定义

desc 表名称;

3.查看创建表的语句

show create table 表名称;

4.修改表名

alter table 旧表名称 rename to 新表名称;

 

5.修改表字段名称

alter table 表名 change 旧字段 新字段 列属性[属性];

6.修改表字段数据类型

alter table 表名称 modify 字段名 列属性[属性];

7.增加表字段

alter table 表名称 add 字段名 列属性[属性];

8.删除表字段

alter table 表名称 drop 字段名;

9.删除表 

drop table 表名;

 

四、数据库表的约束

1.唯一约束

语法:字段名 数据类型 unique

描述:表的列或列组合不能重复,保证数据的唯一性

        唯一约束不允许出现重复的值,但是可以为多个空值

        在一个表内可以有多个唯一约束,多个列组合的约束

2.非空约束

语法:字段名 数据类型 NOT NULL

描述:限制字段的值不能为空

3.主键约束

语法:字段名 数据类型 primary key

描述:每个表最多只允许一个主键

        表的列不允许重复,也不允许出现空值

4.外键约束

语法:alter table 从表表名 add constraint 外键名称 foreign key(从表外键字段) references 主表表名(主键字段)

描述:用于多张表之间的约束

5.默认值约束

语法:字段名 数据类型 DEFAULT 默认值

描述:表中定义了默认值约束,若插入数据为空,则按默认值填充

6.自增约束

语法:字段名 数据类型 AUTO_INCREMENT

描述:自增约束，默认在上一条记录的数值上加1

五、数据表数据操作

1.插入数据

INSERT INTO 表名称（字段名1,字段名2,...) VALUES (值 1,值 2,...);

2.更新数据

UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];

WHERE条件语句

操作符	含义
=	等于
<>或!=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
AND	和
OR	或
BETWEEN…AND…	指定范围内
IN	指定集合内
NOT IN	不在集合内
IS NULL	为空
IS NOT NULL	不为空
LIKE	模糊匹配

3.删除操作

DELETE FROM 表名 [WHERE 条件表达式];

六、数据表数据查询

1.基本语法

 SELECT selection_list      //字段
  FROM table_list         //表名称
  WHERE condition        //条件搜索
  GROUP BY grouping_columns  //对结果的分组
  HAVING condition      //分组后的行条件
  ORDER BY sorting_columns //对结果的排序
  LIMIT offset_start, row_count  //结果条数限制

2.数据准备

INSERT INTO student (id, name, age, sex, seat, birthday) VALUES (1, '小明', 20, 1, 001, '2003-06-20');
INSERT INTO student (id, name, age, sex, seat, birthday) VALUES (2, '小红', 19, 0, 002, '2004-02-20');
INSERT INTO student (id, name, age, sex, seat, birthday) VALUES (3, '小蓝', 20, 1, 003, '2003-08-12');

3.基本查询

SELECT * FROM student;

4.按字段查询

SELECT name,age,sex,seat,birthday FROM student;

5.字段别名操作

当表名称或者字段名称太长,可以给一个简短的名称代替就叫别名.使用到了as关键字,可以省略不写

①.字段别名

SELECT name,age,sex,seat,birthday as birth FROM student;

 

②.表别名

select a.name,a.age,a.sex,seat,a.birthday from student as a

6.数据去重

SELECT  DISTINCT age FROM student;

7.聚合统计

①.统计条数

SELECT count(*) FROM student;

 ②.平均

SELECT avg(age) from student

 ③.求和

SELECT sum(age) from student

④.最大值

SELECT max(age) from student

⑤.最小值 

SELECT min(age) from student

8.条件查询

①.等于

SELECT name,age,sex,seat,birthday as birth FROM student where name = '小明';

 ②.不等于

SELECT name,age,sex,seat,birthday as birth FROM student where name != '小明';

③.大于

SELECT name,age,sex,seat,birthday as birth FROM student where age >19;

 ④.和

SELECT name,age,sex,seat,birthday as birth FROM student where age >=19 and sex=0;

 ⑤.或

SELECT name,age,sex,seat,birthday as birth FROM student where age >19 or sex=0;

 ⑥.在范围内

SELECT name,age,sex,seat,birthday as birth FROM student where birthday between '2003-06-20' and '2003-07-20';

 ⑦.在集合内

SELECT name,age,sex,seat,birthday as birth FROM student where seat in (001,002)

 ⑧.不为空

SELECT name,age,sex,seat,birthday as birth FROM student where sex is not null

 ⑨.模糊匹配

SELECT name,age,sex,seat,birthday as birth FROM student where name like '%明%'

9.分组查询

①.分组

SELECT sex,count(*) FROM student group by sex

 ②.分组过滤

SELECT sex,count(*) FROM student GROUP BY sex having count(*)>1

10.排序查询

①.从小到大(order by 字段 asc)

SELECT name,age,sex,seat,birthday as birth FROM student order by age asc

SELECT name,age,sex,seat,birthday as birth FROM student order by birthday asc

②.从大到小(order by 字段 desc)

SELECT name,age,sex,seat,birthday as birth FROM student order by age desc

SELECT name,age,sex,seat,birthday as birth FROM student order by birthday desc

11.限制查询

①.直接限制(limit 条数)

SELECT name,age,sex,seat,birthday as birth FROM student limit 2

 ②.跳步限制(limit 开始索引,条数)

SELECT name,age,sex,seat,birthday as birth FROM student limit 0,2

12.关联查询

①.表与表之间关系说明

表与表之间一般存在三种关系，即一对一，一对多，多对多关系

一对一:多用于表的拆分,将一些经常使用的字段放在同一张表中,不经常使用的字段放在另一张表中,有利于提升查询性能。比如学生表和毕业证书档案表的关系，在任意一边加入外键，关联另外一方的主键，并且设置外键为唯一

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(60) NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `sex` int(1) DEFAULT '1' COMMENT '性别(1是男 0是女 )',
  `seat` int(3) unsigned zerofill NOT NULL COMMENT '座位',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  PRIMARY KEY (`id`),
  UNIQUE KEY `seat` (`seat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

CREATE TABLE `diploma` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `diploma_img` varchar(200) NOT NULL COMMENT '毕业证书图片地址',
  `student_id` int(11) unsigned NOT NULL COMMENT '学生id' unique,
  PRIMARY KEY (`id`,`student_id`) USING BTREE,
  KEY `fk_studentid` (`student_id`),
  CONSTRAINT `fk_studentid` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='毕业证书表';

一对多:一个学生对应一个班级,一个班级对应多个学生,在多的一方建立外键，指向另外一方的主键

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(60) NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `sex` int(1) DEFAULT '1' COMMENT '性别(1是男 0是女 )',
  `seat` int(3) unsigned zerofill NOT NULL COMMENT '座位',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `class_id` int(11) unsigned NOT NULL COMMENT '所属班级id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `seat` (`seat`),
  KEY `fk_classid` (`class_id`),
  CONSTRAINT `fk_classid` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

CREATE TABLE `class` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `class_name` varchar(60) NOT NULL COMMENT '班级名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='班级表';

多对多：一个学生可以有多个老师,一个老师对应多个学生,建立第三张表作为中间表，中间表至少包括两个外键，分别关联两张主表的主键

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(60) NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `sex` int(1) DEFAULT '1' COMMENT '性别(1是男 0是女 )',
  `seat` int(3) unsigned zerofill NOT NULL COMMENT '座位',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  PRIMARY KEY (`id`),
  UNIQUE KEY `seat` (`seat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

CREATE TABLE `teacher` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(60) CHARACTER SET utf8mb4 NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `sex` int(1) DEFAULT '1' COMMENT '性别(1是男 0是女 )',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='老师表';

CREATE TABLE `student_teacher_relation` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `teacher_id` int(11) unsigned NOT NULL COMMENT '老师id',
  `student_id` int(11) unsigned NOT NULL COMMENT '学生id',
  PRIMARY KEY (`id`),
	UNIQUE KEY `teacher_student_id` (`teacher_id`,`student_id`),
  KEY `fk_relation_teacherid` (`teacher_id`),
  KEY `fk_relation_studentid` (`student_id`),
  CONSTRAINT `fk_relation_studentid` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`),
  CONSTRAINT `fk_relation_teacherid` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生和老师关系中间表';

②.准备数据

#插入学生数据
INSERT INTO student (id, name, age, sex, seat, birthday, class_id) VALUES (1, '小明', 20, 1, 001, '2003-06-20', 1);
INSERT INTO student (id, name, age, sex, seat, birthday, class_id) VALUES (2, '小红', 19, 0, 002, '2004-02-20', 1);
INSERT INTO student (id, name, age, sex, seat, birthday, class_id) VALUES (3, '小蓝', 20, 1, 003, '2003-08-12', 2);
#插入毕业证书数据
INSERT INTO diploma (id, diploma_img, student_id) VALUES (1, 'aaa', 1);
INSERT INTO diploma (id, diploma_img, student_id) VALUES (2, 'bbb', 2);
INSERT INTO diploma (id, diploma_img, student_id) VALUES (3, 'ccc', 3);
#插入班级数据
INSERT INTO class (id, class_name) VALUES (1, '七年级');
INSERT INTO class (id, class_name) VALUES (2, '八年级');
INSERT INTO class (id, class_name) VALUES (3, '九年级');
#插入老师数据
INSERT INTO teacher (id, name, age, sex) VALUES (1, '黄老师', 28, 0);
INSERT INTO teacher (id, name, age, sex) VALUES (2, '张老师', 30, 1);
#插入老师和学生关系数据
INSERT INTO student_teacher_relation (id, teacher_id, student_id) VALUES (1, 1, 1);
INSERT INTO student_teacher_relation (id, teacher_id, student_id) VALUES (2, 1, 2);
INSERT INTO student_teacher_relation (id, teacher_id, student_id) VALUES (3, 2, 1);
INSERT INTO student_teacher_relation (id, teacher_id, student_id) VALUES (4, 2, 2);
INSERT INTO student_teacher_relation (id, teacher_id, student_id) VALUES (5, 2, 3);

③.内连接(查找两张表共有的数据)

select a.name,a.age,a.sex,seat,a.birthday,b.class_name from student a inner join class b on a.class_id = b.id

 ④.左外连接(左边的表无论是否能够匹配都要完整显示，右边的仅展示匹配上的数据)

SELECT a.class_name,b.name FROM class a  left join student b on a.id = b.class_id

 ⑤.右外连接查询(右边的表无论是否能够匹配都要完整显示，左边的仅展示匹配上的数据)

SELECT a.name,b.class_name FROM student a  right join class b on a.class_id = b.id

⑥.联合查询union(有多个联合查询结果,结果是去重的)

select name from student where id in (1,2) union select name from student where age >19

⑦.联合查询union all(有多个联合查询结果,结果是不去重的)

select name from student where id in (1,2) union all select name from student where age >19

⑧. 子查询(一种嵌套查询)

SELECT NAME from student where class_id  in (select id from class where class_name='七年级')

七、数据库的事务

事务要保证一组数据库的操作，要么全部成功，要么全部失败

1.事务的四大特性

原子性: 事务是一个整体，要么全部完成，或者全部不完成，不会有中间状态。事务在执行过程中发生错误，会被回滚到事务开始前的状态
一致性:事务前后数据的完整性必须保持一致。
隔离性: 事务之间应该是隔离的，并发执行的各个事务之间不能互相干扰
持久性:事务一旦被提交则不可逆，被持久化到数据库中，接下来即使数据库发生故障也不应该对其有任何的影响

2.事务并发的问题

脏读: 一个事务读取了另外一个事务未提交的数据

不可重复读:在一个事务内读取表中的某一行数据，多次读取的结果不同

幻读:在一个事务内读取到了别的事务插入的数据，导致前后读取的结果不一致

3.事务隔离级别

①.查看当前隔离级别

select @@tx_isolation

②.隔离级别说明

读未提交(read-uncommitted)：一个事务读取到其他事务未提交的数据

读已提交(read-committed)：一个事务只能读取到其他事务已经提交的数据

可重复读(repeatable-read)：事务在执行过程中可以读取到其他事务已提交的新插入的数据，但是不能读取其他事务对数据的修改，也就是说多次读取同一记录的结果相同

串行化(serializable)：强制每个事务排序执行，使之不可能相互冲突，性能差

③.隔离级别会产生的问题

隔离级别	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交		√	√
可重复读			√
串行化

八、数据库的索引

建立有效的索引可以提高查询效率,同时可以限制数据的唯一性

1.索引的分类

①.普通索引(在任何一列上都能进行创建,没有什么特殊性)

 create index name_index on student(name);

②.唯一索引(字段列必须唯一,允许空值)

 create unique index name_unique_index on student(name);

③.主键索引(唯一索引中的一种,字段列必须唯一,不允许空值)

alter table student add primary key(id);

④.复合索引(建立索引的时候一起使用多个字段)

create index name_age_index ON student(name,age);

⑤.全文索引

⑥.空间索引

2.索引的删除

①.普通删除

DROP INDEX name_age_index  ON student;

 ②.删除主键索引

ALTER TABLE student DROP PRIMARY KEY

③.查询表索引

show index from student;

3.索引分析 

不是建立索引就可以提高查询效率,由于一些不按照查询规则,导致索引失效,所以需要分析索引使用情况

explain select name,age from student where name = '小明'

①.id列值越大优先级越高，id相同则是按照执行计划列从上往下执行，id为空则是最后执行

②.select_type列显示了对应行是简单还是复杂select

③.table列表示用到的表

④.partitions列指的是查询将匹配记录的分区

⑤.type列表示关联类型或访问类型,从优到差:system > const > eq_ref > ref > range > index > all

⑥.possible_keys列显示在查询中可能用到的索引

⑦.key列显示MySQL在查询时实际用到的索引

⑧.key_len列显示MySQL在索引里使用的字节数

⑨.ref列显示key列记录的索引中，表查找值时使用到的列或常量

⑩.rows列是MySQL在查询中估计要读取的行数

⑪.filtered列是存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例

⑫.Extra列是一些额外信息

九、数据库的权限管理

数据库的权限管理包括了数据库、表、字段、用户的访问权限和安全级别的设置,信息主要存在mysql数据库中

1.查看用户

select * from user;

 2.创建用户

create user yy identified by '123456'

 3.赋予权限

grant all privileges on *.* to yy

 4.更改密码

set password for yy = password("654321")

 5.删除用户

drop user yy