MySQL数据库知识点概述
一、为什么要学习数据库
1、为了方便查询数据
2、为了持久化保存数据
二、数据库的相关概念
DBMS、DB、SQL
DB:数据库,保存数据的容器；
DBMS:数据库管理系统或者数据库管理产品；常见的数据库管理系统：MySQL 、ORACAL 、DB2、sql server
SQL:结构化查询语句
三、数据库存储数据的特点
1、表是放在库里面的；
2、数据是存放在表里面的；
3、一个数据库里面表名是唯一的
4、数据库里面列名称之为字段
5、表中数据是以行的形式存放
四、初始化MySQL
MySQL产品介绍
MySQL数据库是由瑞典的MySQL AB研发的，2008年被sun公司收购，紧接着2009sun又被甲骨文收购了
MySQL产品安装
参考视频：如果win7 安装MySQL5.6 需要 .net 4.5.2组件
MySQL服务的启动和停止
计算机 右键 管理 服务 找到 MySQL56 可以启动和停止MySQL服务
MySQL服务登录和退出
登录：
MySQL -u root -p
退出：
quit;或者 exit;
MySQL的常见命令和语法规范
语法规范：
1、关键字大写，表名、字段名小写；
2、MySQL支持关键字小写；
常见命令
查询有哪些数据库：
show databases;
指定操作数据库：
use 数据库名字;
查看数据库中有哪些表:
show tables;
查看表结构
desc表名;
查看表数据库：
select * from 表名
五、DDL语言(数据定义语言)
库和表的管理
常见数据库：
create database 库名;
删除数据库：
drop database 库名;
创建表：
注意：要创建表，需要先要use 数据库
创建表：
create table 表名(
字段名 数据库类型,
字段名 数据库类型,
字段名 数据库类型
);
删除表：
drop table 表名；
MySQL修改表
1、修改表名 rename
alter table 旧表名 rename 新表名；
2、 修改字段的数据类型 MODIFY
alter table 表名 MODIFY 字段名 字段类型;
3、修改字段名 change
alter table 表名 change 旧字段名 新字段名 新字段类型；
4、添加字段 add first after
将字段firstname 添加到age字段之后
alter table student add firstname varchar(5) after age；
将firstname添加到第一列
alter table student add firstname varchar(5) first
5、删除字段 drop
alter table 表名 drop 字段名;
三大范式
设计表的时候的规范
常见数据类型的介绍
常见约束
1、主键PRIMARY KEY
作用：DBMS为了方查找数据库，设计的一个规则
特点：
一张表里面主键是唯一的；
主键可以用来唯一的标识一行数据，主键不能为空，
联合主键：表中字段不能作为唯一标识每一行数据时，选取表中两个或者多个字段作为联合主键
联合主键的特点：如果去掉联合主键某个字段，剩下的字段任然可以构成联合主键，那么说明之前的联合主键是错误的
在修改表时添加主键
alter table user add primary key(id);
删除主键：
alter table user drop primary key;
创建表的时候设置主键：
方式一：
create table class(
-> id int(11) primary key,
-> name varchar(12)
-> );
方式二：
create table class1(
-> id int(11),
-> name varchar(12),
-> primary key(id)
-> );
创建联合主键：
create table user1(
-> name char(16),
-> gender char(10),
-> birthday date,
-> city varchar(16),
-> primary key(name,city)
-> );
2、主键自增 AUTO_INCREMENT
通过auto_increment设置主键自增
特点：
和主键结合使用，
自增字段的数据类型是整数类型
自增的数据开始值是1，没增加一行数据，自增1
添加自增
create table tb_dept10(
id INT(11) PRIMARY key AUTO_INCREMENT,
name VARCHAR(25),
location VARCHAR(50)
);
设置自增默认值
CREATE TABLE tb_dept11(
id INT(11) PRIMARY key auto_increment,
name VARCHAR(25),
location VARCHAR(50)
)auto_increment=100;
删除自增
ALTER TABLE tb_dept10 MODIFY id int(11);
在修改表时设置自增
ALTER TABLE tb_dept12 CHANGE COLUMN id id INT(11) auto_increment;
修改表时指定初始值
ALTER TABLE tb_dept12 auto_increment=100;
3、外键CONSTRAINT fk_rl FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
表间关系：
一对一
一对多
多对多
MySQL5.6中文支持问题
DEFAULT CHARSET=GB2312;
查看创建表是sql
show create table 表名;
外键特点：
外键关联时主表要先去从表创建
外键字段的值必然在主表中是可以一一对应的
外键可以为空，但是不为空的外键必然可以在主表中跟它对于
外键关联的必然是主表的主键
创建外键约束
CREATE TABLE score (
id int(10) NOT NULL PRIMARY KEY auto_increment,
sid INT(10),
name varchar(50) DEFAULT NULL,
subject varchar(50) DEFAULT NULL,
score varchar(50) DEFAULT NULL,
CONSTRAINT fk_sid
FOREIGN KEY(sid) REFERENCES studentinfo(id)
)ENGINE=InnoDB DEFAULT CHARSET=GB2312;
修改变时添加外键约束
alter table 表名 add CONSTRAINT fk_sid FOREIGN KEY(sid) REFERENCES studentinfo(id)
删除外键约束
ALTER TABLE score drop FOREIGN KEY 外键名
4、唯一约束UNIQUE
特点：
主键在一个表里面只能有一个
但是唯一性约束可以有多个
设置了唯一性约束的字段表中有且只能有一个空值
创建表的时候设置唯一性约束
CREATE TABLE tb_dept1(
id INT(11) PRIMARY KEY,
name VARCHAR(25) UNIQUE,
location VARCHAR(50)
);
修改表的时候添加唯一性约束
ALTER TABLE tb_dept2 ADD UNIQUE(name);
起别名：
ALTER TABLE tb_dept2 ADD CONSTRAINT 别名 UNIQUE(字段);
删除唯一性约束
ALTER TABLE tb_dept2 DROP INDEX name;
5、默认值DEFAULT 'Beijing'
特点：
在插入数据时，如果不写入就使用默认值，如果写入就使用新值
新建表时创建
CREATE TABLE tb_dept3(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
location VARCHAR(50) DEFAULT 'shanghai'
);
修改表时创建
ALTER TABLE tb_dept1 CHANGE location location VARCHAR(25) DEFAULT 'beijing';
6、非空约束（NOT NULL）
特点：
一张表中可以设置多个字段非空，主键默认非空
新建表时设置
CREATE TABLE tb_dept4(
id INT(11) NOT NULL,
name VARCHAR(35),
location VARCHAR(50)
);
修改表时设置
ALTER TABLE tb_dept3 CHANGE name name VARCHAR(34) not NULL;
六、DML语言(数据操纵语言)
插入语句
不指定字段插入：
insert into 表名 values (1,'lsii',56,'13512345678');
指定字段名插入：
insert into student (name,age,stuphone) values ('lsii',56,'13512345678');
同时插入多行数据：
insert into student values
(2,'wangwu',46,'13612345678'),
(3,'sadfsa',34,'13412345678');
修改语句
修改其中某个字段的值
update 表名 set id = 4 where age=56;
多个字段同时修改值的时候用逗号隔开
update student set id = 4,name='augus' where age=56;
删除语句
删除其中某一行数据
delete from 表名 where name='fengliu';
同时删除多行数据：
delete from 表名 where name in ('lisi','wangwu');
七、DQL数据查询语言
/*
查询会产生一张虚拟表
语法结构：
注意：*号代表表中所有字段，这个不建议使用，会影响查询效率；
SELECT 字段名,字段名 FROM 表名 WHERE 筛选过滤条件
*/
-- 查询表中所有数据库
-- SELECT * FROM student;
-- 指定字段查询
-- SELECT 'name',address FROM student;
-- AS 起别名
-- SELECT name AS 姓名,address AS 地址 FROM student;
-- 不用as 起别名
-- SELECT name 姓名,address 地址 FROM student;
-- LIMIT使用
-- 注意：limit 0,2 逗号前面表示从哪一行开始取值，第一行是0，逗号右边值取多少行
-- SELECT * FROM student WHERE id=901 LIMIT 0,2
-- DISTINCT排除重复
-- SELECT DISTINCT department AS 院系,address FROM student;
/*
单条件查询：
= != < > >= <=
SELECT * FROM student WHERE id>=905
*/
/*
多条件查询：
AND OR NOT
SELECT * FROM student WHERE id>=905 AND sex='女';
SELECT * FROM student WHERE id>=905 OR sex='男'
*/
/*
范围选择：
方式一：
SELECT * FROM student WHERE (2010-birth)>20 AND (2010-birth)<28;
方式二：BETWEEN AND
SELECT * FROM student WHERE 2010-birth BETWEEN 20 AND 28;
方式三：NOT BETWEEN AND
SELECT * FROM student WHERE 2010-birth NOT BETWEEN 20 AND 28;
*/
/*
集合操作：
1、or
SELECT * FROM student WHERE department='计算机系' OR department='英语系' OR sex='女'
2、in
SELECT * FROM student WHERE department IN ('计算机系','中文系')
3、not in
SELECT * FROM student WHERE department NOT IN ('计算机系','中文系')
*/
/*模糊匹配
LIKE
_ 一个字符
SELECT * FROM student WHERE name LIKE '_三'
%任意字符 可以是0个
SELECT * FROM student WHERE address LIKE '辽宁%'
*/
/*
查询空号 is NULL
SELECT * FROM student WHERE name is NULL
*/
/*
常量列：
SELECT name as 姓名,address AS 地址,'希望小学' AS 学校 FROM student
*/
-- SELECT * FROM score
/*
排序
升序 ASC
SELECT * FROM score WHERE c_name='计算机' ORDER BY grade ASC
降序 DESC
SELECT * FROM score WHERE c_name='计算机' ORDER BY grade DESC
*/
聚合函数：
count 计数：
SELECT COUNT(grade) FROM score
max
求计算机系学生的最高分
SELECT stu_id AS 学号, MAX(grade) AS 成绩 FROM score WHERE c_name='计算机'
min 求计算机系学生的最低分
SELECT stu_id AS 学号, min(grade) AS 成绩 FROM score WHERE c_name='计算机'
sum 求和
SELECT stu_id AS 学号, SUM(grade) AS 成绩 FROM score WHERE c_name='计算机'
avg 平均值
SELECT stu_id AS 学号, AVG(grade) AS 成绩 FROM score WHERE c_name='计算机'
*/
/*
分组
WHERE子句：
从数据源中去掉不符合其搜索条件的数据
GROUP BY子句：
搜集数据行到各个组中，统计函数为各个组计算统计值，每个组输出一行结果
HAVING子句：
从分组计算结果中进行过滤，去掉不符合其组搜索条件的各组数据行
#求每个系的最高分
SELECT c_name,AVG(grade) FROM score GROUP BY c_name
多字段分组
SELECT stu_id,c_name,AVG(grade) FROM score GROUP BY c_name,stu_id
HAVING 对之前分组的结构进行过滤
SELECT c_name,AVG(grade) FROM score GROUP BY c_name HAVING AVG(grade)<=90;
*/
子查询：sql里面嵌套sql
SELECT id,name FROM student
WHERE id=(
SELECT stu_id FROM score WHERE c_name='计算机' AND grade=70);
IN 后面可以赋多个值，用括号
中文系的学生信息
SELECT * FROM student WHERE id IN (SELECT stu_id FROM score WHERE c_name='中文')
NOT IN
SELECT * FROM student WHERE id NOT IN (SELECT stu_id FROM score WHERE c_name='中文')
*/
/*
EXISTS 如果子查询返回结果为真 则执行前面sql ，如果为假 则前半句sql执行结果为空
SELECT * FROM score WHERE EXISTS (SELECT stu_id FROM score WHERE c_name='中文111')
*/
/*
ALL 本质用and 取最大值
ANY OR 取最小值
1.ANY子句表示父查询只需要任意匹配一条子查询结果就可执行
2.ANY子句中 = ANY(子查询) 与IN同意
3.ANY子句中 > ANY代表大于最小值
4.ANY子句中 < ANY代表小于最大值
1.ALL子句中 > ALL代表大于最大值
2.ALL子句中 < ALL代表小于最小值
*/
/*
UNION 合并表
all 数据会重复
SELECT * FROM student_copy UNION all SELECT * FROM student;
不加all会去重
SELECT * FROM student_copy UNION SELECT * FROM student;
*/
内连接：求两张表交集的部分
SELECT * FROM a_table INNER JOIN b_table ON a_table.a_id = b_table.b_id
左连接： 以左表为基础，展示出左表全部数据和右表交集部分数据
SELECT * FROM a_table LEFT JOIN b_table ON a_table.a_id = b_table.b_id
右连接：以右表为基础，展示出右全部数据和左表交集部分数据
SELECT * FROM a_table RIGHT JOIN b_table ON a_table.a_id = b_table.b_id
八、事务
事务具有 4 个特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这 4 个特性通常简称为 ACID。
1. 原子性
事务是一个完整的操作。事务的各元素是不可分的（原子的）。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。
以银行转账事务为例，如果该事务提交了，则这两个账户的数据将会更新。如果由于某种原因，事务在成功更新这两个账户之前终止了，则不会更新这两个账户的余额，并且会撤销对任何账户余额的修改，事务不能部分提交。
2. 一致性
当事务完成时，数据必须处于一致状态。也就是说，在事务开始之前，数据库中存储的数据处于一致状态。在正在进行的事务中. 数据可能处于不一致的状态，如数据可能有部分被修改。然而，当事务成功完成时，数据必须再次回到已知的一致状态。通过事务对数据所做的修改不能损坏数据，或者说事务不能使数据存储处于不稳定的状态。
以银行转账事务事务为例。在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少了，而另一个账户余额尚未修改。因此，所有账户余额的总额处于不一致状态。事务完成以后，账户余额的总额再次恢复到一致状态。
3. 隔离性
对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。
另外，当事务修改数据时，如果任何其他进程正在同时使用相同的数据，则直到该事务成功提交之后，对数据的修改才能生效。张三和李四之间的转账与王五和赵二之间的转账，永远是相互独立的。
4. 持久性
事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。
一个事务成功完成之后，它对数据库所作的改变是永久性的，即使系统出现故障也是如此。也就是说，一旦事务被提交，事务对数据所做的任何变动都会被永久地保留在数据库中。
九、视图
#视图
#什么是视图?
#视图是保存查询的结果，把查询的结果当做一张虚拟的表,视图依赖于
#原始的表数据和结构，如果原来的表数据或者结构发生了改变，视图的
#结构和数据也会发生相应改变，
#视图的特点：
-- 1、效率高
-- 2、安全性，可以指定展示其中某几列数据，隐藏敏感信息
#语法结构
#CREATE VIEW 视图名 AS 查询SQL
#创建视图
#CREATE VIEW ShowStu AS SELECT * FROM student;
#创建视图时指定字段名
#CREATE VIEW view_show_stu (sid,sname,sex,birth,classid) AS SELECT * FROM student;
#创建多表视图 不同使用*
-- CREATE VIEW Get_student_score AS
-- SELECT student.sno,student.sname,student.ssex,student.sbirthday,
-- student.class,score.cno,score.degree FROM student,score WHERE student.sno = score.sno
--
#查看视图结构
#DESC Get_student_score
#查看创建视图的预计
#SHOW CREATE VIEW Get_student_score
#修改视图
-- 语法结构：
-- ALTER VIEW 视图名 AS 新的查询sql
#修改视图
-- ALTER VIEW showstu AS SELECT student.sno,student.sname,student.ssex,student.sbirthday,
-- student.class,score.cno,score.degree FROM student,score WHERE student.sno = score.sno
--
#删除视图
#DROP VIEW showstu;
十、存储过程和函数
#存储过程
#创建存储过程
#定义结束结束
-- delimiter //
--
#创建存储过程
-- CREATE PROCEDURE ShowStuScore1()
-- BEGIN
-- SELECT * FROM tb_students_score;
-- END //
#创建带参数的存储过程
-- delimiter //
-- create PROCEDURE GetStu(in name VARCHAR(30))
-- BEGIN
-- SELECT * FROM student WHERE sname=name;
-- END //
#通过call语句调用存储过程
-- delimiter ;
-- CALL GetStu('李军')
#查看存储过程的状态
#SHOW PROCEDURE STATUS LIKE 'GetStu';
#查看存储过程的定义
#show CREATE PROCEDURE GetStu;
#修改存储过程 只能修改存储过程的状态，修改内容只能先删除在修改
#例子： ALTER PROCEDURE showstuscore MODIFIES SQL DATA SQL SECURITY INVOKER;
#删除存储过程
#drop PROCEDURE ShowStuScore1
十一、数据库备份
#备份数据库中某个表
mysqldump -uroot -p test student>C:\student.sql
备份单个数据库
mysqldump -u root -p 库名>C:\student.sql
同时备份多个库
mysqldump -u root -p --databases 库名 库名>C:\student.sql
#导出数据库信息
查看secure-file-priv当前的值是
show variables like '%secure%';
如果导出失败：
则需要添加secure_file_priv
SELECT * FROM student INTO OUTFILE 'C://Pcode/person.txt';
回复数据库
mysql -u root -p < C:\all.sql
备份所有数据库
mysqldump -u root -p --all-databases > C:\all.sql
