MYSQL数据库超全知识总结#yyds干货盘点# _Mysql数据库

曾无好事来相访，赖尔高文一起予。这篇文章主要讲述MYSQL数据库超全知识总结#yyds干货盘点#相关的知识，希望能为你提供帮助。
@TOC
1 数据库概述 1.1 什么是数据库

数据库就是一个文件系统，只不过我们需要通过命令(SQL)来操作这个文件系统。
数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。
数据库是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合，可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、查询、更新、删除等操作。 1.2 数据库的作用
存储数据，数据的仓库，带有访问权限，限制不同的人有不同的操作。 1.3 为什么要学数据库
操作的都是后台数据，取到后台数据进行封装，然后交给前台去展现。
1.4 常见的数据库
1. mysql：开源免费的适用于中小型企业的免费数据库，sun 公司收购了 MySQL，sun 公司被 Oracle 收购了，Oracle 收购之后就开始收费了。
2. Mariadb：由 MySQL 创始人搞出来的，直接是 MySQL 开源版本的一个分支，基本上所有的命令都是一样的。
3. Oracle：甲骨文公司，商业收费，适用于大型电商网站，收购 sun 公司和java。
4. db2：IBM 公司，主要提供解决方案，软件和硬件，服务器架构。银行系统大多数采用的是 db2。
5. SQLserver：Windows 里面，政府网站 asp.net，大多数大学教学通常都是采用 SQLserver，图形化工具不错。
6. Sybase：已经被淘汰了。
NoSQL 非关系型数据库：key:value 的形式，常见的有 Mongodb、Redis 等。

1.5 关系型数据库

主要是用来描述实体与实体之间的关系。
E-R 图表示：实体-方框，属性-椭圆，关系-菱形。

1.6 MySQL数据库服务器

MySQL 数据库：数据库管理软件。
服务器：类似一台电脑，这台电脑安装相关的服务器软件，这些软件会监听不同的端口号，根据用户访问的端口号，提供不同的服务。

2 MySQL的SQL语句

SQL：Structure Query Language 结构化查询语言。
DDL：数据定义语言。定义数据库，数据库表及结构。create(创建)，drop(删除)，alter(修改)。
DML：数据操纵语言：主要是用来操作数据。insert(插入)，update(修改)，delete(删除)。
DCL：数据控制语言：定义访问权限，取消访问权限，安全设置 grant。
DQL：数据查询语言：select(查询)，from子句，where子句。

2.1 数据库的CRUD的操作

首先要登录数据库服务器：mysql -u [用户名] -p [密码] 2.1.1 创建数据库
1. create database 数据库名字; create database sql_1;
2. create database 数据库名字 character set 字符集; create database sql_2 character set utf8;
3. create database 数据库名字 character set 字符集 collate 校对规则; create database sql_3 character set utf8 collate utf8_bin;

2.1.2 查看数据库

-- 查看所有的数据库 show databases;
-- 查看数据库定义的语句 show create database 数据库的名字 show create database sql_1; show create database sql_2;

2.1.3 修改数据库
-- 修改数据的字符集 alter database 数据库的名字 character set 字符集; alter database sql_1 character set gbk;

2.1.4 删除数据库
drop database 数据库名字; drop database sql_2; drop database sql_3;

2.1.5 其它数据库操作命令

-- 切换数据库(选中数据库) use 数据库的名字; use sql_1;
-- 查看当前正在使用的数据库 select database();

2.2 表的CRUD操作 2.2.1 创建表

create table 表名( 列名1 列的类型(长度) 约束, 列名2 列的类型(长度) 约束, . . . . . . ); create table student( sid int primary key, sname varchar(15), sex int, age int );
列的类型
-- int -- char/varchar[char：固定长度, varchar：可变类型] -- double, float, boolean -- date[YYYY-MM-DD] -- time[hh:mm:ss] -- datatime[YYYY-MM-DD hh:mm:ss] 默认值是null -- timestamp[YYYY-MM-DD hh:mm:ss] 默认使用当前时间 -- text[主要是用来存放文本] -- blob[存放的是二进制]
列的约束
-- 主键约束: primary key -- 唯一约束: unique -- 非空约束: not null

2.2.2 查看表
-- 查看所有表 show tables;
-- 查看表的定义 show create table 表的名字; show create table student;
-- 查看表的结构 desc 表的名字; desc student;

2.2.3 修改表

添加列(add)，修改列类型(modify)，修改列名(change)，删除列(drop)，修改表名(rename)。
1. -- 添加列 alter table 表名 add 列名列的类型列的约束 alter table student add grade int not null;
2. -- 修改列类型 alter table 表名 modify 列名列的类型; alter table student modify sex varchar(2);
3. -- 修改列名 alter table 表名 change 原列名新列名新列的类型; alter table student change sex tel varchar(20);
4. -- 删除列 alter table 表名 drop 列名; alter table student drop tel;
5. -- 修改表名 rename table 原表名 to 新表名; rename table student to stus;
6. -- 修改表的字符集 alter table 表名 character set 字符集; alter table stus character set gbk;
  
  2.2.4 删除表
7. drop table 表名; drop table stus;

2.3 表中数据的CRUD操作 2.3.1 插入数据

insert into 表名(列名1, 列名2, 列名3) values(值1, 值2, 值3); insert into student(sid, sname, sex, age) values (1, liuyi, 1, 23);
-- 如果插入的是全列名的数据，表名后面的列名可以省略 insert into 表名 values(值1, 值2, 值3); insert into student values(2, chener, 1, 23);
-- 注意，如果插入的是部分列，列名不可省略 insert into student(sid, sname) values(3, zhangsan); insert into student values(3, zhangsan); --错误
-- 批量插入 insert into student values (4, lisi, 1, 23), (5, wangwu, 1, 23), (6, zhaoliu, 1, 23), (7, sunqi, 1, 23), (8, zhouba, 1, 23);

2.3.2 删除记录

delete from 表名 [where 条件]; delete from student where sid=10;
-- 如果没有指定条件，会将表中的数据一条一条全部删除掉 delete from student;
delete 删除数据和 truncate 删除数据的区别
-- delete: DML 一条一条删除表中的数据 -- truncate: DDL 先删除表再重建表 -- 关于哪条执行效率高，具体要看表中的数据 -- 如果数据比较少，delete 比较高效 -- 如果数据比较多，truncate 比较高效

2.3.3 更新表记录

update 表名 set 列名1=列1的值, 列名2=列2的值 [where 条件];

-- 如果参数是字符串或日期，要加上单引号 update student set sname=李四 where sid=4;

-- 没有条件，那么每一条记录都会执行 update student set sname=李四, sex=20;

2.3.4 查询记录

select [distinct] [*] [列名1, 列名2] from 表名 [where 条件]; -- distinct 去除重复数据
先创建两个表，用于操作:

-- 商品分类 -- 1. 分类ID -- 2. 分类名称 -- 3. 分类描述 create table category( cid int primary key auto_increment, cname varchar(10), cdesc varchar(31) ); -- 所有商品 -- 1. 商品ID -- 2. 商品名称 -- 3. 商品价格 -- 4. 生产日期 -- 5. 商品分类ID -- 商品和商品分类：所属关系 create table product( pid int primary key auto_increment, pname varchar(10), price double, pdate timestamp, cno int ); -- 关于数据这里就不再操作了，随便一点数据就行
简单查询

-- 查询所有商品 select * from product;

-- 查询商品名称和商品价格 select pname, price from product;
别名查询，as 关键字，as 可以省略
-- 表别名(主要用于多表查询) select p.pname, p.price from product as p;

-- 列别名 select pname as 商品名称, price as 商品价格 from product;

-- 省略 as 关键字 select pname 商品名称, price 商品价格 from product;
去掉重复的值

-- 查询商品所有的价格 select price from product; select distinct price from product;

-- select 运算查询：仅仅在查询结果上做了运算，不改变表中的数据 select *, price*1.5 from product; select *, price*0.9 from product; select *, price*0.5 as 折后价 from product;
条件查询 [where关键字], 指定条件，确定要操作的记录

-- 查询商品价格> 60的所有商品信息 select * from product where price > 60;

-- where 后的条件写法 -- 关系运算符：> > = < < = = != < > -- < > 不等于，标准 SQL 语法 -- != 不等于，非标准 SQL 语法

-- 查询商品价格不等于 88 的所有商品 select * from product where price < > 88;

-- 查询商品价格在 10 到 100 之间 select * from product where price > 10 and price < 100;

-- between...and... select * from product where price between 10 and 100;
逻辑运算：and, or, not
-- 查询出商品价格小于 100 或者商品价格大于 900 的商品 select * from product where price < 100 or price > 900;
like 模糊查询

-- _：代表一个字符 -- %：代表多个字符

-- 查询出名字中带有饼的所有商品 %饼% select * from product where pname like %饼%;

-- 查询第二名是烟的所有商品 _烟% select * from product where pname like _烟%;
in 在某个范围中获取值

-- 查询出商品分类ID在1，4，5里面的所有商品 select * from product where cno in (1, 4, 5);
排序查询：order by 关键字
-- asc: ascend 升序，默认的排序方式 -- desc: descend 降序

-- 查询所有商品，按照价格进行排序 select * from product order by price;

-- 查询所有商品，按价格进行降序排序 select * from product order by price desc;

-- 查询名称有小的商品，按价格升序 select * from product where pname like %小% order by price asc;
聚合函数

sum():求和 avg():求平均值 count():统计数量 max():最大值 min():最小值

-- 获取所有商品价格的总和 select sum(price) from product;

-- 获取所有商品的平均价格 select avg(price) from product;

-- 获取所有商品的个数 select count(*) from product;
- 注意，where 条件后面不能接聚合函数
子查询

-- 查询商品价格大于平均价格的商品 select * from product where price > (select avg(price) from product);
分组：group by
-- 根据 cno 字段分组，分组后统计商品个数 select cno, count(*) from product group by cno;

-- 根据 cno 分组，分组统计每组商品的平均价格并且商品平均价格> 60 select cno, avg(price) from product group by cno having avg(price) > 60;

-- having 关键字，可以接聚合函数，出现在分组之后 -- where 关键字，不可以接聚合函数，出现在分组之前
编写顺序
-- S..F..W..G..H..O -- select .. from .. where .. group by .. having .. order by
执行顺序
-- F..W..G..H..S..O -- from .. where .. group by .. having .. select .. order by

2.3 分页查询
limit [索引], [显示的个数]

select * from product limit 0, 3; -- 出来的是第1~3条的3条数据 select * from product limit 3, 3; -- 出来的是第4~6条的3条数据

2.4 多表之间的关系维护
外键约束: foreign key

-- 给 product 中的 cno 添加一个外键约束(两张表:product,category) alter table product add foreign key(cno) references category(cid);
- 删除的时候, 先删除外键关联的所有数据,再才能删除分类的数据。
主键约束：默认就是不能为空，唯一。
- 外键都是指向另外一张表的主键
- 一张表只能有一个主键
唯一约束：列里面的内容必须是唯一，不能出现重复情况，可为空。
- 唯一约束不可以作为其它表的外键
- 可以有多个唯一约束

2.4.1 建数据库原则

通常情况下，一个项目/应用建一个数据库。 2.4.2 多表之间的建表原则
1. 一对多：商品和分类
  - 建表原则：在多的一方添加一个外键，指向一的一方的主键。
2. 多对多：老师和学生，学生和课程
  - 建表原则：建立一张中间表，将多对多的关系，拆分成一对多的关系，中间表至少要有两个外键，分别指向原来的那两张表。

一对一：班级和班长, 公民和身份证, 国家和国旗
- 建表原则：
  - 将一对一的情况，当作是一对多情况处理，在任意一张表添加一个外键，并且这个外键要唯一，指向另外一张表
  - 直接将两张表合并成一张表
  - 将两张表的主键建立起连接，让两张表里面主键相等
- 实际用途: 用的不是很多(拆表操作).
  - 拆表操作：将个人的常用信息和不常用信息拆分出来，减少表的臃肿。

2.5 多表查询 2.5.1 笛卡尔积(交叉连接查询)

-- 查出来的就是两张表的乘积，查出的结果没有意义。 select * from product, category;
-- 过滤出有意义的数据 select * from product, category where cno = cid; select * from product as p, category as c where p.cno = c.cid; select * from product p, category c where p.cno = c.cid;

2.5.2 内连接查询