MySql数据库基础知识

MySql数据库基础知识

目标：

• 理解关系型数据库的基本概念
• 掌握关系模型
• 掌握MySql安装及连接
• 熟悉结构化查询语言定义及其分类
• 掌握数据库管理操作
• 掌握创建数据表，掌握MySql的数据类型
• 掌握数据库表操作

为何需要数据库：

• 存储数据的方法

• 第一种方法：用大脑来记住数据

• 第二种方法：写在纸上

• 第三种方法：写在计算机的内存中

• 第四种方法：写成磁盘文件

• 数据库能干什么

• 存储大量数据，方便检索和访问

• 保持数据信息的一致、完整

• 共享和安全

• 通过组合分析，产生新的有用信息

数据库发展历史：

• 萌芽阶段——文件系统

• 使用磁盘文件来存储数据

• 初级阶段——第一代数据库

• 出现了网状模型、层次模型的数据库

• 中级阶段——第二代数据库

• 关系型数据库和结构化查询语言

• 高级阶段——新一代数据库

• “关系-对象”型数据库

关系型数据库的基本概念：

• 一对一关系

• 当两个表中第一个表中的一行只与第二个表中的一行相关，同时第二个表中的一行也只与第一个表中的一行相关时，我们称两个表之间是一对一关系

• 我们通常把一对多关系中，“多边”的表称为从表，把“一边”的表称为主。一对多关系是我们在数据库中最常看到的一种关系类型

• 多对多关系

• 多对多关系是指两个表中，第一个表的一行可以与第二个表中的一到多个行相关联，同时，第二个表中的一行可以与第一个表中的一到多个行相关联。

• 在大多数关系型数据库管理系统中，多对多关系通过在关系的两个表间增加第三个表来实现。我们把第三个表称为连接表（Junction Table），连接表充当支持多对多关系的两个表之间的桥梁，多对多关系是逻辑而不是物理实现的。

关系型数据库管理系统：

• 什么是数据库管理系统

• 关系型数据库只是一个保存数据的容器，大多数数据库依靠一个称为数据库管理系统（Database Management System，简称DBMS）的软件来管理数据库中数据。管理关系型数据库的软件称为关系型数据库管理系统（Relational Database Management System，简称RDBMS）。数据库应用程序通过RDBMS与关系型数据库进行交互。

• 本地数据库管理系统

• 本地数据库管理系统又称桌面型数据库管理系统。在这种系统模式下，RDBMS与数据库应用程序运行在同一客户端的进程中。

常见数据库：

• Oracle

• Oracle公司的产品；产品免费、服务收费；金融、通信、生产制造、快速消费品零售、物流运输、医疗、互联网、政府；

• SQL Server

• 针对不同用户群体的多个版本；易用性好；政府、生产制造、快速消费品零售、医疗、互联网为主；

• DB2

• IBM公司的产品；支持多操作系统、多种类型的硬件和设备；金融为主；

• MySQL

• 开放源代码；
  互联网为主，近2年开始扩展到金融、通信、生产制造、快速消费品零售、物流运输、医疗、政府等行业。

• MongoDB

• 非关系型数据库，{编号:1,姓名:张三,年龄:20,性别:男}

• Redis

• 内存数据库，非关系型数据库，可进行持久化到磁盘，缓存数据库

• Solr

• 帮助进行大数据搜索，集群化

• Hbase、Hive

• 大数据系统常使用的数据库

MySql数据库：

• MySQL 是最流行的关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。在WEB应用方面 MySQL 是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

• MySQL 是开源的，不需要支付额外的费用。

• MySQL 支持大型的数据库。支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。

• MySQL 使用标准的SQL数据语言形式。

• MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。

• MySQL 对PHP有很好的支持，PHP是目前最流行的Web开发语言。

MySQL数据库登录方式：

• 命令行方式：打开cmd，输入以下命令：mysql –u root –p,之后输入密码；

• MySQL方式：打开MySQL自带连接工具，输入root密码即可登录；

• 第三方工具：Navicat、SQLYOG

名词解释：

• DB DataBase 数据库
• DBA DataBase Administrator 数据库管理员
• DBMS DataBase Management System 数据库管理系统
• RDBMS Relational DataBase Management System 关系型数据库管理系统
• SQL Structured Query Language结构化查询语言
• DDL Data Definition language数据定义语言
• DML Data manipulation language数据操纵语言
• DQL Data query language数据查询语言
• DCL Data Control Language数据控制语言

结构化查询语言SQL：

• | 语言分类 | 名称 | 详细描述 |
  | -------------------- | -------------------------------------- | ------------------------------------ |
  | 1、数据查询语句(DQL) | Select | 数据选择查询 |
  | 2、数据操纵语句(DML) | Insert; Update; Delete; | 插入；更新；删除 |
  | 3、数据定义语句(DDL) | Create; Alter; Drop; Rename; Truncate; | 新建；修改；结构；删除；重命名；删除 |
  | 4、数据控制语言(DCL) | Grant; Revoke; | 授权；解除授权 |

为什么要学习数据库：

1. 为了方便查询数据
2. 为了持久化保存数据

DBA、RDBMS、SQL是什么意思？

DB:数据库,保存数据的容器；
DBMS:数据库管理系统或者数据库管理产品；常见的数据库管理系统：MySQL 、ORACAL 、DB2、sql server
SQL:结构化查询语句

数据库存储数据的特点：

1、表是放在库里面的；
2、数据是存放在表里面的；
3、一个数据库里面表名是唯一的
4、数据库里面列名称之为字段
5、表中数据是以行的形式存放

数据库操作：

• 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、外键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’);

数据类型：

• 整数数据类型

类型	大小	范围(有符号)	范围(无符号*)	用途
TINYINT	1B	(-128，127)	(0，255)	极小整数值
SMALLINT	2B	(-32 768，32 767)	0，65 535)	小整数值
MEDIUMINT	3B	(-8 388 608， 8 388 607)	(0，16 777 215)	小整数值
INT	4B	(-2 147 483 648， 2 147 483 647)	(0，4 294 967 295)	整数值
BIGINT	8B	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	大整数值

• 浮点数据类型

类型	大小	范围	精度	用途
FLOAT	4B	(-3.40E-38，3.40E+38)	7位小数	单精度浮点数
DOUBLE	8B	(-8 388 608， 8 388 607)	15位小数	双精度浮点数

• 定点数据类型

类型	大小	范围	精度	用途
DECIMAL(M,D)	17B	(-10的38次方-1 , 10的38次方-1)	30位小数	大浮点数

• 字符串类型

类型	大小	范围	用途
CHAR(M)	M	0-255	字符型
VARCHAR(M)	M+1B	0-65535	字符型
TINYTEXT	LENGTH+2B	0-255	文本型
TEXT	LENGTH+2B	0-65535	文本型（无默认值）
BINARY(M)	M	0-M	0-M变长字符串
VARBINARY(M)	M+1B	0-M	0-M变长字符串

• 二进制数据类型

类型	大小	范围	用途
TINYBLOB	255	0-255	二进制大对象
BLOB	65K	0-65KB	二进制大对象
MEDIUMBLOB	16M	0-16M	二进制大对象
LONGBLOB	4G	0-4G	二进制大对象

• 日期和时间数据类型

类型	大小	格式	范围
YEAR	1B	YYYY	1901 - 2155
DATE	3B	YYYY-MM-DD	1000-01-01 - 9999-12-31
TIME	3B	HH:MM:SS	-838：59：59 - 838:59：59
TIMESTAMP	4B	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 - 2038
DATETIME	8B	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 - 9999-12-31 23:59:59

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

• 排序
  升序 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 取最小值

• 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