MySQL

01 概述

1.数据库基本概念

【1】数据库（database 简称：DB） 【2】定义：用于存储和管理数据的仓库 【3】特点： （1）持久化存储数据，其实数据库就是一个文件系统 （2）方便存储和管理数据 （3）使用了同一的方式来操作数据 – SQL

2.常见数据库软件

Oracle:收费的大型数据库，Oracle公司的产品 MySQL:开源免费的数据库，小型的数据库，已经被Oracle收购了。MySQL6.x版本也开始收费。后来Sun 公司收购了 MySQL，而 Sun 公司又被 Oracle 收购 SQL Server:MicroSoft 公司收费的中型的数据库。C#、.net 等语言常使用 DB2 :IBM 公司的数据库产品,收费的。常应用在银行系统中 SQLite: 嵌入式的小型数据库，应用在手机端，如:Android

02 MySQL概述

1.MySQL环境配置

【1】安装 【2】卸载 【3】配置 【4】本地MySQL服务器登陆和退出

mysql -u root -p # 输入密码 #退出 exit quit

【5】非本地MySQL服务器登陆

#IP地址 mysql -h 127.0.0.1 -uroot -p # 输入连接目标密码

2.MySQL目录结构

【1】安装目录 （1）配置文件：my.ini 【2】数据目录

数据库：文件夹表：文件数据

03 SQL

1.基本概念

【1】定义：Structured Query Language：结构化查询语言 【2】其实就是定义了操作所有关系型数据库的规则。【3】每一种数据库操作的方式存在不一样的地方，称为“方言”

2.通用语句

【1】SQL 语句可以单行或多行书写，以分号结尾 【2】可使用空格和缩进来增强语句的可读性 【3】MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写 【4】 3 种注释 （1）单行注释1：-- 注释内容（有空格！） （2）单行注释2：# 注释内容（mysql 特有） （3）多行注释: /* 注释 */

3.分类

【1】DDL(Data Definition Language)数据定义语言

用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等

【2】DML(Data Manipulation Language)数据操作语言

用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等

【3】 DQL(Data Query Language)数据查询语言

用来查询数据库中表的记录(数据)。关键字：select, where 等

【4】DCL(Data Control Language)数据控制语言(了解)

用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

04 DDL：操作数据库&表

1.操作数据库：CRUD

【1】C：creat创建

-- 创建数据库： create database 数据库名称; -- 创建数据库，判断不存在，再创建： create database if not exists 数据库名称; -- 创建数据库，并指定字符集 create database 数据库名称 character set 字符集名;

【2】R：retrieve查询

-- 查询所有数据库名称 show DATABASES; -- 查询某个数据库的字符集:查询某个数据库的创建语句 show create database 数据库名称;

【3】U：update修改

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

【4】D：delete删除

-- 删除数据库 drop database 数据库名称; --判断数据库存在，存在再删除 drop database if exists 数据库名称;

【5】使用数据

-- 查询当前正在使用的数据库名称 select database(); -- 使用数据库 use 数据库名称;

2.操作数据表：CRUD

【1】C：creat创建 （1）语法

create table 表名( 列名1 数据类型1, 列名2 数据类型2, .... 列名n 数据类型n );

Tips： 最后一列，不需要加逗号（,） （2）数据库数据类型

int：整数类型 age int,double:小数类型，第一个参数一共几位，第二个参数小数点后保留几位 score double(5,2)date:日期，只包含年月日 yyyy-MM-dddatetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:sstimestamp:时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值varchar：字符串 name varchar(20):姓名最大20个字符 zhangsan：8个字符 张三：2个字符 （3）复制表 create table 表名 like 被复制的表名;

【2】R：retrieve查询

-- 查询某个数据库中所有的表名称 show tables; -- 查询表结构 desc 表名;

【3】U：update修改

-- 1. 修改表名 alter table 表名 rename to 新的表名; -- 2. 修改表的字符集 alter table 表名 character set 字符集名称; -- 3. 添加一列 alter table 表名 add 列名 数据类型; -- 4. 修改列名称 类型 alter table 表名 change 列名 新列名 新数据类型; alter table 表名 modify 列名 新数据类型; -- 5. 删除列 alter table 表名 drop 列名;

【4】D：delete删除

drop table 表名; drop table if exists 表名 ;

【5】使用数据

-- 查询当前正在使用的数据库名称 select database(); -- 使用数据库 use 数据库名称;

图形化界面工具：SQL Workbench

05 DML：增删改表中数据

1.添加数据

语法：

insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

Tips： 【1】 列名和值要一一对应 【2】 如果表名后，不定义列名，则默认给所有列添加值

insert into 表名 values(值1,值2,...值n);

【3】除了数字类型，其他类型需要使用引号(单双都可以)引起来

2.删除数据

语法

delete from 表名 [where 条件];

Tips： 【1】如果不加条件，则删除表中所有记录。 【2】如果要删除所有记录 （1）delete from 表名; – 不推荐使用。有多少条记录就会执行多少次删除操作 （2）TRUNCATE TABLE 表名; – 推荐使用，效率更高 先删除表，然后再创建一张一样的表

3.修改数据

语法

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

Tips： 如果不加任何条件，则会将表中所有记录全部修改

06 DQL：查询表中的记录

select * from 表名;

1.语法

select 字段列表 --聚合函数 from 表名列表 --基础查询 where 条件列表 --条件查询 group by 分组字段 --分组查询 having 分组之后的条件 --分组查询 order by 排序 --排序查询 limit 分页限定 --分页查询

2.基础查询

【1】多个字段的查询

select 字段名1，字段名2... from 表名；

Tips： 如果查询所有字段，则可以使用*来替代字段列表

【2】去除重复的结果集：

select distinct 字段名1，字段名2... from 表名；

【3】计算列 （1）一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算） （2）null参与的运算，计算结果都为null

ifnull(english,0) SELECT name,math,english,math+ifnull(english,0) FROM student; 表达式1：哪个字段需要判断是否为null表达式2：如果该字段为null后的替换值。

【4】起别名 AS：as也可以省略

SELECT name,math AS 数学,english AS 英语,math+ifnull(english,0) AS 总分 FROM student;

3.条件查询

【1】where子句后跟条件 【2】运算符 （1）> 、< 、<= 、>= 、= 、<>、!=

!=，<>不等于

（2）BETWEEN…AND （3）IN( 集合) （4）LIKE：模糊查询

占位符：_：单个任意字符%：多个任意字符

（5）IS NULL / IS NOT NULL

NULL 值不能使用等号不等号判断

（6）and 或 && （7）or 或 || （8）not 或 !

4.排序查询

【1】语法： order by 子句

order by 排序字段1 排序方式1 ，排序字段2 排序方式2...

【2】排序方式： （1）ASC：升序，默认的。 （2）DESC：降序。

Tips： 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件

5.聚合函数

【1】概念：将一列数据作为一个整体，进行纵向的计算 【2】count：计算个数 1. 一般选择非空的列：主键 2. count(*) 【3】max：计算最大值 【4】min：计算最小值 【5】sum：计算和 【6】avg：计算平均值

Tips： 【1】聚合函数的计算，排除null值。 【2】解决方案： （1）选择不包含非空的列进行计算 （2）IFNULL函数

6.分组查询

语法

group by 分组字段;

Tips： 【1】分组之后查询的字段： （1）分组字段 （2）聚合函数 【2】where 和 having 的区别？ （1）where 在分组之前进行限定，如果不满足条件，则不参与分组，having在分组之后进行限定，如果不满足结果，则不会被查询出来 （2） where 后不可以跟聚合函数，having可以进行聚合函数的判断

7.分页查询

【1】语法：

limit 开始的索引,每页查询的条数;

【2】公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数

SELECT * FROM student LIMIT 0,3; -- 第1页 SELECT * FROM student LIMIT 3,3; -- 第2页 SELECT * FROM student LIMIT 6,3; -- 第3页

【3】 limit 是一个MySQL"方言"

07 约束

1.概念&分类

【1】概念对表中的数据进行限定，保证数据的正确性、有效性和完整性 【2】分类 （1）主键约束：primary key （2）非空约束：not null （3）唯一约束：unique （4）外键约束：foreign key

2.非空约束：not null

【1】not null：值不能为null 【2】创建表时添加约束

CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL -- name为非空 );

【3】创建表完后，添加非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

【4】删除name的非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20);

3.唯一约束：unique

【1】unique，值不能重复 【2】创建表时，添加唯一约束

CREATE TABLE stu( id INT, phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束 );

Tips： mysql中，唯一约束限定的列的值可以有多个null

【3】删除唯一约束：drop index

ALTER TABLE stu DROP INDEX phone_number;

【4】在创建表后，添加唯一约束

ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

4.主键约束：primary key

Tips： 【1】 含义：非空且唯一 【2】一张表只能有一个字段为主键 【3】 主键就是表中记录的唯一标识

【1】在创建表时，添加主键约束

create table stu( id int primary key,-- 给id添加主键约束 name varchar(20) );

【2】删除主键：drop primary

-- 错误 alter table stu modify id int ; ALTER TABLE stu DROP PRIMARY KEY;

【3】创建完表后，添加主键

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

【4】自动增长 （1）概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长 （2）在创建表时，添加主键约束，并且完成主键自增长

create table stu( id int primary key auto_increment,-- 给id添加主键约束 name varchar(20) );

Tips： 数据自动增长，只和上一条数据有关系

（3） 删除自动增长

ALTER TABLE stu MODIFY id INT;

（4）添加自动增长

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

5.外键约束：foreign key

【1】foreign key：让表于表产生关系，从而保证数据的正确性（一张表的列关联其他的表的主键列） 【2】在创建表时，可以添加外键：语法：

create table 表名( .... 外键列, --外键名称：自己取 constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) );

Tips：

不能删除外键列在的表添加不能在主键列中添加不存在的外键列类型

【3】删除外键：drop foreign key

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

【4】创建表之后，添加外键：add

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

【5】级联操作 （1）添加级联操作：语法：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;

（2）分类： A. 级联更新：ON UPDATE CASCADE B. 级联删除：ON DELETE CASCADE （3）弊端： 级联删除：关联表的记录都会被删除掉

08 数据库设计

1.多表关系介绍

【1】分类 （1）一对一(了解)：

如：人和身份证分析：一个人只有一个身份证，一个身份证只能对应一个人

（2）一对多(多对一)：

如：部门和员工分析：一个部门有多个员工，一个员工只能对应一个部门

（3）多对多：

如：学生和课程分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选

2.一对多关系实现

实现方式：在多的一方建立外键，指向一的一方的主键

3.多对多关系实现

实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键

4.一对一关系实现

实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键，让外键唯一 unique

5.代码实现

【1】外键的省略写法

-- 省略写法 FOREIGN KEY (cid) REFERENCES tab_category(cid)

【2】联合主键

rid INT, -- 线路id DATE DATETIME, uid INT, -- 用户id PRIMARY KEY(rid,uid), -- 联合主键 FOREIGN KEY (rid) REFERENCES tab_route(rid), FOREIGN KEY(uid) REFERENCES tab_user(uid)

6.范式

【1】概念：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）基本来说，掌握前三类即可

【2】分类 （1）第一范式：每一列都是不可分割的原子数据项 存在的问题：

存在非常严重的数据冗余（重复）：姓名、系名、系主任数据添加存在问题：例如添加新开设的系和系主任，数据不合法数据删除存在问题：删掉张无忌，会将系数据一起删除

（2）第二范式：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

概念：

函数依赖：A–>B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A 例如：学号–>姓名。 （学号，课程名称） --> 分数完全函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值 例如：（学号，课程名称） --> 分数部分函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可 例如：（学号，课程名称） – > 姓名传递函数依赖：A–>B, B -->C . 如果通过A属性属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A 例如：学号–>系名，系名–>系主任码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码 例如：该表中码为：（学号，课程名称） 主属性：码属性组中的所有属性非主属性：除过码属性组的属性

解决了数据冗余的问题

（3）第三范式：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

番外01 数据库备份和还原

【1】目的：防止数据丢失 【2】方式1：命令行 语法： （1）备份

mysqldump -u用户名 -p密码 数据库名称 > 保存的路径

出现Warning:Using a password on the command line interface can be insecure问题 解决：在my.cnf的[mysqldump]下添加：

user=your_backup_user_name password=your_backup_password

直接执行：

mysqldump 数据库名称 > 保存的路径

（2）还原

登录数据库创建数据库使用数据库执行文件 source 文件路径

【3】方式2：图形化工具

09 多表查询

1.概述

【1】语法

select 列名列表 --可以写多个表 from 表名列表 where....

【2】笛卡尔积： 有两个集合A,B .取这两个集合的所有组成情况。 要完成多表查询，需要消除无用的数据 【3】多表查询的分类： （1）内连接查询 （2）外链接查询 （3）子查询

2.内连接查询

【1】隐式内连接：使用where条件消除无用数据

-- 例子 SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`; SELECT t1.name, -- 员工表的姓名 t1.gender,-- 员工表的性别 t2.name -- 部门表的名称 FROM emp t1, dept t2 WHERE t1.`dept_id` = t2.`id`;

【2】显式内连接： （1）语法：

select 字段列表 from 表名1 [inner] join 表名2 on 条件

（2）例子：

-- 例子 SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;

Tips： 内连接查询： 【1】从哪些表中查询数据 【2】条件是什么 【3】查询哪些字段

3.外连接查询

【1】左外连接： （1）语法

select 字段列表 from 表1 left [outer] join 表2 on 条件;

（2）查询的是左表所有数据以及其交集部分

【2】右外连接： （1）语法：

select 字段列表 from 表1 right [outer] join 表2 on 条件;

（2）查询的是右表所有数据以及其交集部分

4.子查询

【1】概念：查询中嵌套查询，称嵌套查询为子查询 【2】基本例子：

-- 查询工资最高的员工信息 SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

【3】子查询情况1：子查询的结果是单行单列的

子查询可以作为条件，使用运算符去判断 运算符： > >= < <= =

【4】子查询情况2：子查询的结果是多行单列的

子查询可以作为条件，使用运算符in来判断 -- 查询'财务部'和'市场部'所有的员工信息 SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

【5】子查询情况3：子查询的结果是多行单列的

子查询可以作为一张虚拟表参与查询 -- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息 -- 子查询 SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id; -- 普通内连接 SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > '2011-11-11'

10 事物 Transaction

1.基本介绍

【1】概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败 【2】操作： （1）开启事务： start transaction; （2）回滚：rollback; （3）提交：commit; 【3】例子：

-- 0. 开启事务 START TRANSACTION; -- 1. 张三账户 -500 UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan'; -- 2. 李四账户 +500 -- 出错了... UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi'; -- 发现执行没有问题，提交事务 COMMIT; -- 发现出问题了，回滚事务 ROLLBACK;

【4】MySQL数据库中事务默认自动提交 【5】事务提交的两种方式： （1）自动提交

mysql就是自动提交的一条DML(增删改)语句会自动提交一次事务 （2）手动提交Oracle 数据库默认是手动提交事务需要先开启事务，再提交

【6】修改事务的默认提交方式：

查看事务的默认提交方式： SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交 修改默认提交方式: set @@autocommit = 0;

2.事务的四大特征

【1】原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败 【2】持久性：当事务提交或回滚后，数据库会持久化的保存数据 【3】隔离性：多个事务之间。相互独立 【4】一致性：事务操作前后，数据总量不变（例如：银行卡转账，金额的总量）

3.事务的隔离级别（了解）

【1】概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题 【2】存在的问题 （1）脏读：一个事务，读取到另一个事务中没有提交的数据 （2）不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样 （3）幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改 【3】隔离级别： （1）read uncommitted：读未提交 产生的问题：脏读、不可重复读、幻读 （2）read committed：读已提交 （Oracle） 产生的问题：不可重复读、幻读 （3）repeatable read：可重复读 （MySQL默认） 产生的问题：幻读 （4）serializable：串行化（锁） 可以解决所有的问题

Tips： 【1】隔离级别从小到大安全性越来越高，但是效率越来越低 【2】数据库查询隔离级别：

select @@tx_isolation;

【3】数据库设置隔离级别：

set global transaction isolation level 级别字符串;

11 DCL：管理用户，授权

1.概述

DBA：数据库管理员

2.增删查&修改密码

【1】管理用户1：添加用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

【2】管理用户2：删除用户

DROP USER '用户名'@'主机名';

【3】修改用户密码

UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名'; SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

【4】管理用户3：查询用户 （1） 切换到mysql数据库

USE myql;

（2）查询user表

SELECT * FROM USER;

Tips： 通配符： % 表示可以在任意主机使用用户登录数据库

3.忘记密码

【1】cmd – > net stop mysql 停止mysql服务（设置中可以停止） 需要管理员运行该cmd

sudo -s

【2】使用无验证方式启动mysql服务： mysqld --skip-grant-tables 【3】打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功 【4】use mysql; 【5】update user set password = password(‘你的新密码’) where user = ‘root’; 【6】关闭两个窗口 【7】 打开任务管理器/活动监视器，手动结束mysqld.exe 的进程 【8】 启动mysql服务 【9】 使用新密码登录

4.管理权限

【1】查询权限：

-- 查询权限 SHOW GRANTS FOR '用户名'@'主机名'; SHOW GRANTS FOR 'lisi'@'%';

【2】授予权限：

-- 授予权限 grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

【3】撤销权限：

-- 撤销权限： revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

Tips： 通配符：*所有

【4】权限列表： CREATE、ALTER、SELECT、INSERT、UPDATE等