数据库相关知识点总结

MySQL

1.概念
(1)数据库

• 数据库 可以比喻为一个大型打超
• 表 超市货架
• 数据 超市货物

(2)数据库管理软件

• ①MySQL：开源，免费，3306，属于Oracle
• ②Oracle：收费 贵 ，1521
• ③SQL Server：微软 ，1433

(3)数据库管理语言 SQL：结构化查询语言
2.安装、配置与卸载（安装这里就不多解释了）
3.基础使用
(1)启动服务、登录、退出

• ①配置环境变量
• ②登陆
  1)Cmd> Mysql -h 主机 -u 用户名 -p 密码
  2)Cmd> mysql -h127.0.0.1 -uroot -proot
  3)Cmd> mysql -uroot -p
• ③Commands end with; or\g.每句话以；或\g结尾
• ④Type ‘help;’or’\h’or’?’for help. 用help;或\h寻求帮助
• ⑤Type ‘\c’ to clear the current input statement. 用\c 来清除已输入的语句
• ⑥\T 开始记录sql语句及结果 到文件中， \t结束记录
• ⑦\W 显示最近的警告信息，\w不显示警告信息
• ⑧Quit exit \q 退出mysql
• ⑨\d 修改分隔符
• ⑩\G 按每行去输出结果
• ⑪\r 数据库 主机：奇幻数据库
• ⑫use 数据库名 或者 \u 数据库名 ：切换数据库
• ⑬\s 查看当前服务器信息（字符集，端口号，所在数据库等）
• ⑭source 或 . 导入恢复数据库
• ⑮\C 字符集 切换客户端字符集
  (2)对数据库的CRUD
• ①新建数据库 create database 数据库名 character set 字符集；
• ②查看已有的数据库 show databases；
• ③查看建库语句 show create database 库名；
• ④？修改数据库 alter database 原数据库名 rename 新数据库
• ⑤删除数据库 drop database 数据库名；
• ⑥使用数据库 use 数据库名；

(3)对表的CRUD
①数据库的数据类型
1)int
2)double
3)date
4)datetime
5)Timestamp
6)char（固定长度）
7)Varchar（可变长度）
②创建表

1)create table 表名（
列明1 类型(长度)，
列明1 类型(长度)，
......
）engine=存储引擎 default charset 字符集；

create table food(
id int,
Name char(20),
Price double
) engine=MyISAM default charset =utf8;

③修改表
1)修改表名

• a.alter table 原表名 rename to 新表名；
• b.alter table food rename to foods；

2)修改字段

 - a.alter table 表名  change  列名 新列名 类型（长度）；
 - b.alter table foods change id num int（8）；
 - c.alter table 表名 modify 列名 新类型（新长度）；
 - d.alter table foods modify num int（10）；

3)修改字符集

 a.alter table 表名 charset=gbk；

4)修改存储引擎

 a.alter table 表名 engine=innodb；

④查看表结构 desc 表名；
⑤查看已有的表 show tablse；
⑥查看建表语句 show create table 表名；
⑦删除表

1)drop table 表名；

(4)对数据的CRUD
①插入数据

1)insert into 表名(列名1，列名2) value()
2)insert into foods(name,price) value(‘卫龙’,2.5);
3)insert into food(name,price) values(‘卫龙’,2.5),(‘薯条’,1.5),(‘雪饼’,0.5);
4)insert into food value(10001,‘卫龙’,2.5);
5)insert into food values(10001,‘卫龙’,2.5);

②设置字符集

• 1)\C gbk
• 2)Set names utf8;

③修改数据

• 1)update 表名 set 列名 1=值 1，列名2 = 值2 条件；
• 2)update food set id 10003 where price =2.5；

④删除数据

• 1)delete from 表名条件；
• 2)截断表 truncate table 表名;

⑤查询数据

• 1)Select *from 表名：

4.单表查询
(1)基础查询

• ①语法

select字段列表from表名列表where条件列表group by分组字段having分组之后的条件order by排序limit分页限定;

• ②查询多有字段

  Select *from 表名；

• ③查询指定字段

  Select name，price from 表名；

• ④去除重复数据

  Select distinct id，name，price，from food；

• ⑤计算列

1)ifnull(字段名，为null后替换的值 )
2)Select ifnull（id，10000），name，from food；

• ⑥起别名

  Select ifnull（id，10000）as idd，name，from food；

(2)条件查询

• ①where子句
• ②运算符

1)比较查询

• a.> 、< 、<= 、>= 、= 、<>
• b.IS NULL

2)范围查询

a.BETWEEN...AND
	Select *from food where id between 10001 and 10003;

b.IN(...)
Select *from food where id in(10001,10005);

3)模糊查询
a.LIKE：模糊查询
占位符：

• _ : 单个任意字符

• % : 多个任意字符

  Select *from food where name like ‘%牛%’;
  Select *from food where name like’__牛%’;

4)逻辑条件

• a.AND 或 &&
• b.OR 或 ||
• c.NOT 或 !

(3)排序查询
①单列排序

1) select * from food order by price;
2) select * from food order by price asc;
3) select * from food order by price desc;

②组合排序

1)select * from food order by price desc,id asc;
2)select * from food order by price desc,id desc;

(4)分组查询
①聚合函数
1)max()
2)min()
3)avg()
4)count()
5)sum()
②having子句
1)与where子句的区别

• a.Where 是分组前的筛选
• b.Having是分组后的筛选

2)select price,count(id),max(id),min(id),avg(price),sum(price) from food group by price having count(id)>2;
(5)限制查询
①limit n

1)select * from food order by price desc limit 1;

②limit n,m

1)n偏移量 offset,m分页量

2)select * from food order by id asc limit 5,5;

5.多表查询
(1)内连接
①隐式内连接

1)笛卡尔积：select * from students,classes；
2)select * from students,classes where students.class_id = classes.class_id;
3)select * from students as s,classes as c where s.class_id = c.class_id;

②显式内连接

1)select * from students inner join classes;
2)select * from students s inner join classes c on s.class_id=c.class_id;

(2)外连接
①左外链接

1) select * from students s left outer join classes c on s.class_id=c.class_id;
2) select * from students s left join classes c on s.class_id=c.class_id;

②右外链接

1)select * from classes c right outer join students s on s.class_id=c.class_id;
2)select * from classes c right join students s on s.class_id=c.class_id;

(3)子查询
①子查询的结果是：多行单列，作为条件范围
1)select stu_id,stu_name from students where stu_id in (select stu_id from students_courses where course_id=3)
②子查询的结果是：单行单列，作为条件

1) select * from classes where id>(select count(id) from students where class_id=22);

③子查询的结果是：多行多列，作为(虚拟)表

1)select count(id) from (select * from students_courses where course_id=3) cc;

6.数据约束
(1)主键

• ①Primary key: 唯一、不能为null
• ②Auto_increment:默认是从1开始。

(2)默认

• ①Default: 当你没有指定值的时候，给默认值

(3)非空

• ①Not null:不填，或 填 null 都不行。
• ②默认 允许为 null

(4)唯一

• ①Unique:唯一索引，索引 理解为 目录。可以为null

(5)外键
①外键条件

• 1)存储引擎支持，Innodb
• 2)俩个表的字段 类型 长度 要一致。
• 3)清理掉，不符合外键约束的数据。

②添加外键
1)创建表时添加

a.create table 表名(
列名1 类型(长度) 【约束】,
列名2 类型(长度) 【约束】【外键】,
...

【外键】

)engine=存储引擎 default charset=字符集;
Create Table: CREATE TABLE `students` (
  `stu_id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(20) NOT NULL,
  `stu_gender` char(3) NOT NULL DEFAULT '男',
  `stu_age` tinyint(4) DEFAULT '1',
  `stu_idcard` char(18) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`stu_id`),
  UNIQUE KEY `stu_idcard_key` (`stu_idcard`),
  KEY `stu_classes_fk_1` (`class_id`),
  CONSTRAINT `stu_classes_fk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`
class_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

2)建完表后再添加外键

a.alter table students add foreign key (class_id) references classes (class_id);
b.alter table 表名 add constraint 外键名foreign key (本表的某个字段) references 关联表名(外键字段);

3)修改外键
a.删除外键

ALTER TABLE students DROP FOREIGN KEY `外键名`;

b.再添加
③级联操作

1)on update cascade
2)on delete	cascade

7.多表之间关系
(1)一对多：主外键
(2)多对多：中间表，俩个一对多
(3)一对一:
①处理为一对多，多方加唯一约束
②从表的主键同时又是外键
8.三大范式
(1)第一范式1NF：
原子性：表中每列不可再拆分。
(2)第二范式2NF：
不产生局部依赖，一张表只描述一件事情。
(3)第三范式3NF:
不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其他列间接依赖于主键。
(4)BC范式
(5)第四范式4NF
(6)第五范式5NF
9.事务
(1)概念
①事务的特性ACID

• 1)原子性
• 2)一致性
• 3)隔离性
• 4)持久性

(2)手动开启事务

• ①start transaction;开启事务
• ②commit;提交事务
• ③rollback;回滚事务

(3)设置自动提交事务

• ①select @@autocommit;
• ②set @@autocommit = 0;
• ③commit;

(4)回滚点

• ①savepoint 名字
• ②rollback to 名字

(5)事务的隔离级别

• ①脏读: 一个事务读取到了另一个事务中尚未提交的数据
• ②不可重复读：一个事务中俩次读取的数据内容不一致。update引发的问题。
• ③幻读：一个事务中俩次读取的数据的数量不一样。insert和delete引发的问题。

(6)MySQL的隔离级别
级别 名字 隔离级别 脏读 不可重复读 幻读 默认级别

• 1 读未提交 read uncommitted 是 是 是
• 2 读已提交 read committed 否 是 是 Oracel和SQL Server
• 3 可重复读 repeatable read 否 否 是 MySQL
• 4 串行化 serializable 否 否 否

(7)设置事务隔离级别

①select @@tx_isolation;
②set global transaction isolation level 级别;

10.用户及授权管理
创建用户：create user wangrui idenfified by '123123';

授予权限：

grant all privileges on jiaowu.* to wangrui;
  grant insert,delete on jiaowu.* to wangrui;

撤销权限：revoke all privileges on jiaowu.* from wangrui;

查看权限：show grants;

删除用户：
修改用户密码：

rename user wangrui to ruirui;
    set password=password('321321');

set password for ruirui = password('123456');

刷新权限：flush privileges; （可以去修改 mysql数据库的user表的对应信息，改完，记得刷新权限）
当前登录用户：select user();
查看当前在线用户：show processlist;
开启服务：net start mysql服务名
停止服务：net stop mysql服务名
windows 杀死进程taskkill /PID 5540 /F5540 /F
数据的备份与恢复：
11.数据的备份与恢复
(1)备份
①cmd> mysqldump -u用户 -p密码 数据库 > 文件路径
(2)恢复
①mysql> use 数据库;
②mysql> source 文件路径;