# MySQL **Repository Path**: tu-zhenjin/mysql ## Basic Information - **Project Name**: MySQL - **Description**: No description available - **Primary Language**: Unknown - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 1 - **Created**: 2022-05-06 - **Last Updated**: 2023-09-04 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # 数据库的相关概念 - **数据库**(Database)简称`DB`：能高效保存数据的仓库。 - **数据库管理系统**(database management system)简称`DBMS`：是数据库系统的核心，是管理数据库的软件。DBMS拥有一个数据字典(系统表)，用来存储它所管理的所有对象的信息，关于数据的数据称为元数据（metadata）。 - **数据库管理员**(Database Administrator)简称`DBA`：是从事管理和维护(DBMS)的相关工作人员的统称。 - **数据库系统**（Database Management System）：简称`DBAS` 是由数据库、数据库管理系统、数据库管理员、计算机的物理设备等组成的数据库系统就可以正常运转。 - **数据**（`Data`）：描述事物的符号，多种表现形式：文本，图形，音频，视频. - `关系型数据库`和`非关系型数据库`的区别: ![img](MySQL.assets/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0dKSF9uZXc=,size_16,color_FFFFFF,t_70-16482965184472.png) 数据库按照数据结构来组织、存储和管理数据，实际上，数据库一共有三种模型： - 层次模型 - 网状模型 - 关系模型 **层次模型**就是以“上下级”的层次关系来组织数据的一种方式，层次模型的数据结构看起来就像一颗树： ![img](MySQL.assets/1424359-20200722162936657-193826499.png) **网状模型**把每个数据节点和其他很多节点都连接起来，它的数据结构看起来就像很多城市之间的路网： ![img](MySQL.assets/1424359-20200722162957878-1963096331.png) **关系模型**把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表： ![img](MySQL.assets/1424359-20200722163017152-489928701.png) 相比层次模型和网状模型，关系模型理解和使用起来最简单并且也最流行。 `RDBMS` 即`关系型数据库管理系统`(Relational Database Management System)的特点： - 1.数据以表格的形式出现 - 2.每行为各种记录名称 - 3.每列为记录名称所对应的数据域 - 4.许多的行和列组成一张表单 - 5.若干的表单组成database | 名称 | 类型 | 说明 | | ------------ | -------------- | ------------------------------------------------------------ | | INT | 整型 | 4字节整数类型，范围约+/-21亿 | | BIGINT | 长整型 | 8字节整数类型，范围约+/-922亿亿 | | REAL | 浮点型 | 4字节浮点数，范围约+/-1038 | | DOUBLE | 浮点型 | 8字节浮点数，范围约+/-10308 | | DECIMAL(M,N) | 高精度小数 | 由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算 | | CHAR(N) | 定长字符串 | 存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串 | | VARCHAR(N) | 变长字符串 | 存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串 | | BOOLEAN | 布尔类型 | 存储True或者False | | DATE | 日期类型 | 存储日期，例如，2018-06-22 | | TIME | 时间类型 | 存储时间，例如，12:20:59 | | DATETIME | 日期和时间类型 | 存储日期+时间，例如，2018-06-22 12:20:59 | # 重启数据库服务 ```txt 打开cmd管理员命令行输入 net stop mysql net start mysql ``` # MySQL5.7的安装 ## 图形化管理器的安装 （NAvicat安装 + SQLyog 安装） ![image-20220327093141360](MySQL.assets/image-20220327093141360.png) # MySQL命令行连接到MySQL ![image-20220326200536393](MySQL.assets/image-20220326200536393.png) # MySQL三层架构 ![image-20220327093040000](MySQL.assets/image-20220327093040000.png) ![image-20220327093008860](MySQL.assets/image-20220327093008860.png) SQL语言大致可以分为以下几类类： - `数据操作语言`（`DML`，Data Manipulation Language ）（DQL+DML）：由select、insert、update和delete关键字完成，用来对数据库表内容的增删改查操作； - `结构操作语言`（数据定义语言，`DDL`，Data Definition Language ）：create、drop、truncate和alter完成，用来对数据库和数据库对象的增删改操作（drop会同时删除表结构和表数据，truncate会只是删除数据不删除表结构，其原理是先drop然后再创建表结构）； - `数据库管理语言`（数据库控制语言，`DCL`，DataBase Control Language）：grant、revoke完成； - `事物控制语言`：主要有commit、rollback、savepoint完成。 在MySQL中主要的数据库对象有： ![img](MySQL.assets/1775037-20201107155646127-172291988.png) # MySQL基础知识 在讲 MySQL 中数据表的增删改查之前，我们先需要创建表。创建表的过程中需要制定表字段的类型，字段的约束以及表的存储引擎等。所以非常有必要先介绍些MySQL的基础语法知识。 ## 字符集和校验规则 **字符集**：字符的集合，常见的字符集有ASCLL、GBK和Unicode等； **校验规则**：当前字符集内，字符之间大小的比较规则。 ## 数据类型 MySQL中三大数据类型是：数值、日期和字符串。 ![img](MySQL.assets/1775037-20201107160408528-1555181622.png) #### 数值型 **1. 数值类型** | 类型 | 字节 | 最小值(有无符号) | 最大值 | | --------- | ---- | ---------------- | --------------------- | | TYNYINT | 1 | -128/0 | 127/255 | | SMALLINT | 2 | -32768/0 | 32767/65535 | | MEDIUMINT | 3 | -8388608/0 | 8388607/16777215 | | INT | 4 | -2147483648/0 | 2147483647/4294967295 | | BIGINT | 8 | -2^63/0 | 2^63^-1 / 2^64^-1 | ```sql INT(M) UNSIGNED ZEROFILL ``` > 可以这样定义数值类型。其中M，表示显示宽度，显示宽度不限制数值的范围。配合zerofill来使用，可以在小于显示宽度的位数前增加0。ZEROFILL 自动为unsigned。`Unsigned 表示无符号，只表示正数`。 **2. 小数类型** 单精度(不推荐使用) ```sql FLOAT(M,D) UNSIGNED ZEROFILL ``` 双精度 ```sql DOUBLE(M,D) UNSIGNED ZEROFILL ``` > 其中M表示总的位数（包括小数位数），D表示小数位数。M,D可以控制保存的范围。比如DOUBLE(10,2)可以表示 -99999999.99 到 99999999.99。 定点数（金额，科学数据推荐使用这个类型） ```sql DECIMAL(M,D) UNSIGNED ZEROFILL ``` > 其中M表示总的位数，D表示小数位数。此M,D可以控制保存的范围。M，D省略，默认为10,0； #### 日期型 可以考虑使用long类型来存储，也就是时间戳。 或者使用datetime（或timestamp）类型来存储。但是这两个类型有个问题：datetime不包含时区的概念，也就是你传'2019-09-08 12:56:34'，它存储的就是这个值。timestamp虽然包含时区的概念，但是它的取值范围比较小（1970-01-01 00:00:01 -- 2038-01-19 03:14:07），有些情况不够用。 所以会有上面的建议，使用long类型存时间戳。 ```MySQL #创建一张表，演示date, datetime, timestamp CREATE TABLE t02 ( birthday DATE , -- 生日 job_time DATETIME , -- 记录年月日 时分秒 login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);-- 自动更新时间 SELECT * FROM t02; INSERT INTO t02(birthday , job_time) VALUES('2022-11-11','2022-11-11 10:10:10'); -- 如果我们更新t02表的某条记录，login_time列会自动的以当前时间更新 ``` #### 字符串型和二进制 [![img](MySQL.assets/1775037-20201107161651505-599264458.png)](https://img2020.cnblogs.com/blog/1775037/202011/1775037-20201107161651505-599264458.png) 老韩之`字符串`细节： 1. ![image-20220327171045365](MySQL.assets/image-20220327171045365.png) 2. char 是定长的（固定大小的） varchar 是变长（变化的大小） > 如：char(4) 写入'aa',也会占用分配4个字符的空间 > > 如：varchar(4)写入'aa',实际占用空间并不是4个字符，而是按照实际空间来分配（`老韩解析：`varchar本身还需要占用1-3个`字节`来记录存放内容长度。 3. `数据定长`，推荐char 如：md5的密码，邮编，身份证号，手机号等 一个字段`不确定长度`，使用varchar 查询速度：char > varchar 4. 在存放文本时，也可以使用`text`(0-2^16^)数据类型，注意：text不能有默认值，大小0-2^16^字节如果更多选`mediumtext`（0-2^24^）或者 `longtext`(0-2^32^) #### 选择数据类型的建议 当选择字段类型时，应该遵循下面几个原则： - 应该使用最精确的类型，占用的空间少，已经事先估计字段可能的长度； - 还应该考虑到相关应用语言的处理，例如常常将时间日期保存成一个整型，便于计算； - 考虑移植的兼容性。 ## MySQL数据库增删改查 数据库是所有其他数据库对象的载体，在创建数据库表之前必有先创建一个数据库。 ### 数据库的创建 ```mysql -- 建数据库语句模板 Create database [if not exists] 数据库名 [数据库选项] -- 建数据库例子,在建数据库时一般只要指定charset就可以了（有时你还可以设置校验规则 ） CREATE DATABASE if not exists my_database charset utf8; -- 选择数据库 use my_database; ``` ### 数据库的删除 ```sql -- 删除数据库语句模板 drop database [if exists] 数据库名 [数据库选项] -- 删除数据库例子,在建数据库时一般只要指定charset就可以了 drop DATABASE if exists my_database; ``` ### 数据库的修改 ```mysql -- 一般只要修改数据库的字符集就可以了，校验规则会默认选和字符集匹配的 ALTER DATABASE my_database CHARACTER SET gbk; ``` MySQL中一般不能直接对数据库重命名，如果想重命名数据库一般的做法有： 1. 创建新数据库，将原数据库内的表重命名到新数据库内，删除原数据库。 2. 将原数据库的数据和结构导出，然后在导入到新数据库内，删除原数据库。 ### 数据库的查询 ```mysql -- 查询数据库名字以my打头的数据库 SHOW DATABASES LIKE 'my%'; -- 显示所有的数据库 SHOW DATABASES; -- 显示数据库的创建结构 SHOW CREATE DATABASE my_database; ``` 老韩细节： 1. 在创建数据库，表的时候，为了`规避关键字`，可以使用`反引号`解决 2. `charset`是`character set`的简写，即字符集 3. 校队规则`utf8_bin`区分大小写,`utf8_general_ci`不区分大小写 4. 删库一定要慎重！！！(删完一定要跑路！！！) ```mysql #创建数据库名字叫 tu_mysql CREATE DATABASE tu_mysql; #删除数据库 DROP DATABASE tu_mysql; #创建一个utf8字符集的tu_mysql2数据库（charset是character set的简写，即字符集） CREATE DATABASE tu_mysql2 CHARSET utf8; #创建一个utf8字符集的tu_mysql3数据库 CREATE DATABASE tu_mysql3 CHARACTER SET utf8; #创建一个utf8字符集且有校队规则的tu_mysql4数据库 CREATE DATABASE tu_mysql4 CHARACTER SET utf8 COLLATE utf8_bin; #utf8_bin区分大小写,utf8_general_ci不区分大小写 #查看当前数据库服务器中的所有数据库 SHOW DATABASES #查看指定数据库 如tu_mysql2 SHOW CREATE DATABASE `tu_mysql2` #在创建数据库，表的时候，为了规避关键字，可以使用反引号解决 ``` ## MySQL数据库的备份 ### 备份恢复数据库 方法一： ![image-20220327151553073](MySQL.assets/image-20220327151553073.png) ```mysql #在DOS命令行 #先备份tu_mysql tu_mysql2 tu_mysql3 mysqldump -u root -p -B tu_mysql2 tu_mysql3 > d:\\bak.sql #后删除数据库 DROP DATABASE tu_mysql3 #再恢复数据库（进入MySQL命令行再执行） #cmd -> mysql -u root -p source d:\\bak.sql ``` 方法二： 找到这个文件 ` bak.sql `直接 `Ctrl+A ` `Ctrl + C ` `Ctrl + V `到MySQL执行(原始方法) ### 备份恢复数据库的表 ![image-20220327153222001](MySQL.assets/image-20220327153222001.png) ## MySQL数据库的表 ### 创建表 ![image-20220327162233377](MySQL.assets/image-20220327162233377.png) ```MySQL #创建表user CREATE TABLE `user`( id INT, `name` VARCHAR(255), `password` VARCHAR(255), `birthday` DATE) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB; ``` ### 修改表 ```MySQL CREATE TABLE emp( -- 创建一个表，下面有操作 id INT , `name` VARCHAR(32), sex VARCHAR(2), brithday DATE, entry_time DATETIME, job VARCHAR(10), salary DOUBLE, `resume` TEXT) CHARSET utf8 COLLATE utf8_bin; INSERT INTO emp VALUES(1,'老刘','男','2001-1-1','2020-9-17 10:10:10','南理',3000,'xiao'); SELECT * FROM emp; ``` ```MySQL -- 在表emp的上增加一个image列，varchar类型（要求在resume后面） ALTER TABLE emp ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER resume; DESC emp -- 显示表的结构 -- 修改job列，长度为60 ALTER TABLE emp MODIFY job VARCHAR(60) NOT NULL DEFAULT '' -- 删除sex列 ALTER TABLE emp DROP sex -- 修改表名employee RENAME TABLE emp TO employee -- 修改字符集为gbk ALTER TABLE employee CHARSET gbk; -- 修改列名name 为 uers_name ALTER TABLE employee CHANGE `name` `uers_name` VARCHAR(32) NOT NULL DEFAULT ''; ``` ### 查询表 ```MySQL -- 查看某个数据库下面的所有表 show tables from database; -- 查看表结构 show create tbl_name; desc tbl_name; ``` ### 删除表 ```MySQL Drop table [if exists] tbl_name1，tbl_name2; -- 删除内容，但是保留表结构 TRUNCATE tbl_name1; ``` ## MySQL表数据的增删改查 ### INSERT ```MySQL -- MySQL特殊语法，可以一次插入多列，Oracle不支持 -- 先创一个表 CREATE TABLE goods( id INT, goods_name VARCHAR(10), price DOUBLE) CHARSET utf8 COLLATE utf8_bin; -- 查表 SELECT * FROM goods; -- 删表 DROP TABLE goods; -- 插入数据 INSERT INTO goods (id, goods_name, price) VALUES(1, '华为手机' , 2000); -- 细节： -- 1.插入数据对应字段的数据类型相同，（形参对应相同） -- 2.数据的长度在规定范围内 -- 3.字符和日期型数据应包含在单引号中 -- 4.插入空值（前提允许为null） -- 5.可以添加多条记录 -- 6.添加表中所有字段，可以不写 ：如(id, goods_name, price) -- 7.字段设有默认值，不插入值时，会自动添加默认 -- 8.字段没有设not null，也没有设默认值，不插入值，默认为null。 ``` ![image-20220328163855161](MySQL.assets/image-20220328163855161.png) ### UPDATE ```MYSQL -- 查表 SELECT * FROM goods; -- 数据更新 -- 没有加where price全设3000 UPDATE goods SET price = 3000; -- 将goods_name 为华为手机 设为 oppo (id = 1) UPDATE goods SET goods_name = 'oppo' WHERE id = 1; -- 在 id = 2 的price上增上1000 并改为荣耀手机 UPDATE goods SET price = price + 1000 , goods_name = '荣耀手机' WHERE id = 2; ``` ### DELETE ```MySQL -- 数据删除 -- 删除id = 1的记录 DELETE FROM goods WHERE id = 1; -- 删除表中所有记录 DELETE FROM goods; ``` 老韩细节： 1. 不使用where语句，将删除表中所有数据 2. delete语句不能删除某一列的数据（可以使用update置为null） 3. 使用delete语句仅删除记录，不能除表本身。如果要删除表，使用drop table语句； ### SELECT #### Where子句 在select查询语句中where条件运算用于初步删选条件，满足这个条件的记录将被进一步删选。常用的运算符有下面这些。除了这些，正则运算符 也是常用的。Mysql本身也提供了很多内置函数帮我们解决问题，我们要善于利用这些函数解决问题。 [![where子句](MySQL.assets/1775037-20201107164415411-1575090299.png)](https://img2020.cnblogs.com/blog/1775037/202011/1775037-20201107164415411-1575090299.png) ![image-20220414184306740](MySQL.assets/image-20220414184306740.png) ```MySQL -- 查表 SELECT * FROM goods; -- 查goods表的列goods_name ，price SELECT goods_name , price FROM goods; -- 查重复的数据 SELECT DISTINCT price FROM goods; ``` #### 模糊查询 在`like`子句中使用通配符 - %：匹配：0个或多个字符 - _:匹配1个字符 - []:匹配一个字符集 ```MySQL -- 创建一张表 CREATE TABLE t03 ( id INT, `name` VARCHAR(20), math INT, english INT); -- 插入数据 INSERT INTO t03 VALUES(1, '涂' , 100 , 80), (2, '刘' , 19 , 8), (3, '徐' , 75, 95); -- 查表 SELECT * FROM t03; -- 统计每个学生的数学和英语成绩总和 SELECT `name` , (math + english) FROM t03; -- as 改名称 SELECT `name` AS '名字' , (math + english)AS '总' FROM t03; -- and or like(模糊查询) where(过滤查询) (between and 闭区间) -- 查询数学75到100闭区间的的记录 SELECT * FROM t03 WHERE math BETWEEN 75 AND 100 ; SELECT * FROM t03 WHERE math >= 75 AND math <= 100; -- 查询名字以涂开头的 SELECT * FROM t03 WHERE `name` LIKE '涂%'; -- 查询 in 涂刘徐 SELECT * FROM t03 WHERE `name` IN ('涂' , '刘' , '徐' ); -- 下带两张截图来说明as ``` ![image-20220328193432575](MySQL.assets/image-20220328193432575.png) ![image-20220328193456758](MySQL.assets/image-20220328193456758.png) #### 升序和降序查询 `ORDER BY` 和 `DESC` ```MySQL -- 创一张表 CREATE TABLE t04 ( id INT, `name` VARCHAR(20), math INT, english INT); -- 插入数据 INSERT INTO t04 VALUES(1, '涂' , 100 , 80), (2, '刘' , 19 , 8), (3, '徐' , 75, 95), (4,'涂在' , 20,30); -- 查表 SELECT * FROM t04; -- 查数学成绩 安 【升序】 看 (第一行是最低的） SELECT * FROM t04 ORDER BY math; -- 查数学成绩 安 【降序】 看 (第一行是最高的） SELECT * FROM t04 ORDER BY math DESC; ``` #### 统计函数 `COUNT` ```MySQL -- 统计函数 SELECT COUNT(*) FROM t04; SELECT COUNT(id) FROM t04; -- count(*) 和 count(id) 的区别 -- count(*)统计所有的行数（包含null） -- count(id)统计列id的非null行数 ``` #### 合计函数 `SUM` ```MySQL -- 合计函数 -- 合计数学总成绩 SELECT SUM(math) FROM t04; -- 合计 数学和英语的 总成绩 SELECT SUM(math + english) FROM t04; -- 合计一个班的数学平均成绩 SELECT SUM(math) / COUNT(*) FROM t04 ; -- 合计一个班的数学平均成绩 (AVG) SELECT AVG(math) FROM t04; -- 班级数学最高分 SELECT MAX(math) FROM t04; -- 班级数学最低分 SELECT MIN(math) FROM t04; ``` #### 分组函数 ##### `Group By` > 注意：group by 后面可以跟多列，此时会根据多列的组合来分组。比如说几个列的组合不同，mysql就认为这是一个不同的分组，会将它显示出来。null会被单独作为一个分组，若该列中存在多个null值，他们会被分为一组。 ![img](MySQL.assets/1775037-20201107164958602-1024091384.png) ##### `HAVING` > having子句用来对group by之后的结果进行进一步删选。 ##### `HAVING`和`WHERE`的比较 `HAVING`子句和`WHERE`子句非常相似，HAVING子句支持WHERE子句中所有的操作符和语法，但是两者存在几点`差异`： - WHERE子句主要用于过滤数据行，而HAVING子句主要用于过滤分组，即HAVING子句基于分组的聚合值而不是特定行的值来过滤数据，主要用来过滤分组。 - WHERE子句不可以包含聚合函数，HAVING子句中的条件可以包含聚合函数。 - HAVING子句是在`Group By`后进行过滤，WHERE子句会在`Group By`前进行过滤。WHERE子句排除的行不包含在分组中，可能会影响HAVING子句基于这些值过滤掉的分组。 # （创建表EMP雇员表） > 后面代码结合这个表来使用 ```mysql CREATE TABLE dept( /*部门表*/ deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, dname VARCHAR(20) NOT NULL DEFAULT "", loc VARCHAR(13) NOT NULL DEFAULT "" ); INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON'); #创建表EMP雇员 CREATE TABLE emp (empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/ ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/ job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/ mgr MEDIUMINT UNSIGNED ,/*上级编号*/ hiredate DATE NOT NULL,/*入职时间*/ sal DECIMAL(7,2) NOT NULL,/*薪水*/ comm DECIMAL(7,2) ,/*红利*/ deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/ ); INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), (7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30), (7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30), (7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20), (7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30), (7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30), (7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10), (7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20), (7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10), (7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30), (7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30), (7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20), (7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10); #工资级别表 CREATE TABLE salgrade ( grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, losal DECIMAL(17,2) NOT NULL, hisal DECIMAL(17,2) NOT NULL ); INSERT INTO salgrade VALUES (1,700,1200); INSERT INTO salgrade VALUES (2,1201,1400); INSERT INTO salgrade VALUES (3,1401,2000); INSERT INTO salgrade VALUES (4,2001,3000); INSERT INTO salgrade VALUES (5,3001,9999); -- 分组代码 -- 显示每个部门的每种岗位的平均工资和最低工资 SELECT AVG(sal) , MIN(sal) deptno, job FROM emp GROUP BY deptno ,job; -- 显示平均工资低于2000的部门号和它的平均工资 SELECT AVG(sal) , deptno FROM emp GROUP BY deptno HAVING AVG(sal) < 2000; ``` #### 字符串相关函数 ![image-20220414190928973](MySQL.assets/image-20220414190928973.png) ```MySQL -- 返回字符集 SELECT CHARSET(ename) FROM emp; -- 连接字符串 SELECT CONCAT(ename , ' 工作是 ', job) FROM emp; -- 返回substring,在string中出现的位置,没有返回0 -- dual 亚元表,系统表做测试表用的 SELECT INSTR('hanshunping' , 'ping') FROM DUAL; -- 将ename表中转成大写 SELECT UCASE(ename) FROM emp; -- 将ename表中转成小写 SELECT LCASE(ename) FROM emp; -- 从ename中左边起取出length个字符 SELECT LEFT(ename , 2) FROM emp; -- 从ename中右边起取出length个字符 SELECT RIGHT(ename , 2) FROM emp; -- 统计ename中的长度[按照字节] SELECT LENGTH(ename) FROM emp; -- 在job中将MANAGER替换经理 SELECT ename , REPLACE(job , 'MANAGER' , '经理') FROM emp; -- 逐个字符比较字符串大小 SELECT STRCMP('hsp' , 'jsp') FROM DUAL;/*-1*/ -- 截取字符串[得从1开始]取length个字符 SELECT SUBSTRING(ename , 1 , 2) FROM emp; -- 去除前空格 SELECT LTRIM(' 韩顺平教育') FROM DUAL; -- 去除后空格 SELECT RTRIM('韩顺平教育 ') FROM DUAL; -- 去除前后空格 SELECT TRIM(' 韩顺平教育 涂 ') FROM DUAL;/*韩顺平教育 涂 */ ``` #### 数学相关函数 ![image-20220414204323019](MySQL.assets/image-20220414204323019.png) ```MySQL -- 绝对值 SELECT ABS(-10) FROM DUAL; -- 十进制转二进制 SELECT BIN(10) FROM DUAL; -- 向上取整 SELECT CEILING(-1.1) FROM DUAL; -- 向下取整 SELECT FLOOR(1.1) FROM DUAL; -- 进制转换 SELECT CONV(8 , 10 , 2) FROM DUAL;/*8为十进制转二进制*/ -- 保留小数位数 SELECT FORMAT(78.123456 , 2) FROM DUAL;/*四舍五入*/ -- 转十六进制 SELECT HEX(10) FROM DUAL; -- 求最小值 SELECT LEAST(0 , 1 , -10 , 4 , 1000) FROM DUAL; -- 求余 SELECT MOD(10 , 3) FROM DUAL;/* 10%3 */ -- 返回随机数 [范围[0,1.0]] SELECT RAND() FROM DUAL; -- SELECT RAND(2) FROM DUAL;在RAND()里面加个数seed固定 ``` #### 时间日期相关函数 ![image-20220414204502890](MySQL.assets/image-20220414204502890.png) ```mysql -- 日期时间相关函数 -- 当前日期 SELECT CURRENT_DATE() FROM DUAL; -- 当前时间 SELECT CURRENT_TIME() FROM DUAL; -- 当前时间戳 -- 1. SELECT CURRENT_TIMESTAMP() FROM DUAL; -- 2. SELECT NOW() FROM DUAL; -- 创建一个表演示后面函数的使用 CREATE TABLE mes( id INT , content VARCHAR(30), send_time DATETIME); -- 添加记录 INSERT INTO mes VALUES(1, '北京新闻', CURRENT_TIMESTAMP()); INSERT INTO mes VALUES(2, '上海新闻', CURRENT_TIMESTAMP()); INSERT INTO mes VALUES(3, '广州新闻', CURRENT_TIMESTAMP()); -- 显示所有新闻消息，发布日期只显示日期，不显示时间 SELECT id, content, DATE(send_time) FROM mes; -- 请查询在10分钟内发布的新闻 -- DATE_ADD在日期上加上时间 SELECT * FROM mes WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW(); -- DATE_SUB在日期上减去时间 SELECT * FROM mes WHERE DATE_SUB(NOW(), INTERVAL 10 MINUTE) <= send_time; -- 求出2011-11-11和 1990-1-1相差多少天 SELECT DATEDIFF('2011-11-11' , '1990-1-1') FROM DUAL; -- 求出你活了多少天 SELECT DATEDIFF(DATE(NOW()), '2001-11-22') FROM DUAL; -- 细节DATEDIFF,TIMEDIFF里面参数可以是日期，时间，时间戳 -- 年月日 SELECT YEAR(NOW()) FROM DUAL; SELECT MONTH(NOW()) FROM DUAL; SELECT DAY(NOW()) FROM DUAL; -- UNIX_TIMESTAMP()返回的是1970-1-1到现在的秒数 SELECT UNIX_TIMESTAMP() FROM DUAL; -- FROM_UNIXTIME()可以把一个UNIX_TIMESTAMP()秒数2，转成指定格式日期 -- %Y-%m-%d固定格式 SELECT FROM_UNIXTIME(1650030963,'%Y-%m-%d') FROM DUAL;/* 2022-04-15 */ SELECT FROM_UNIXTIME(1650030963,'%Y-%m-%d %H:%i:%s') FROM DUAL;/* 2022-04-15 21:56:03 */ ``` #### 加密函数系统函数 ![image-20220415223221098](MySQL.assets/image-20220415223221098.png) ```mysql -- 查询用户 -- 可以查看登录到mysql的有哪些用户，以及登录的IP SELECT USER() FROM DUAL; -- 用户@IP地址 -- 查看当前使用数据库的名称 SELECT DATABASE(); -- 为字符串算出一个MD5 32的字符串，常用用户密码加密 -- root 密码是hsp -> 加密MD5 -> 在数据库中窜访的是加密后的密码 SELECT MD5('hsp') FROM DUAL; -- mysql数据库的加密函数 SELECT PASSWORD('hsp') FROM DUAL; -- 数据库的表 SELECT * FROM mysql.user; ``` #### 流程控制函数 ![image-20220416174542082](MySQL.assets/image-20220416174542082.png) ```mysql -- 案例来演示流程控制函数 -- 1.查询emp表，如果comm是null，则显示0.0 -- 判断为NULL前面一定用is SELECT IF(comm IS NULL , 0.0 , comm) FROM emp; SELECT IFNULL(comm , 0.0) FROM emp; -- 2.如果emp表的job是CLERK则显示职员，如果是MANAGER则显示经理 -- 如果是SALESMAN则显示销售人员，其它正常显示 SELECT (SELECT CASE WHEN job = 'CLERK' THEN '职员' WHEN job = 'MANAGER' THEN '经理' WHEN job = 'SALESMAN' THEN '销售人员' ELSE job END) AS 'job' FROM emp; ``` #### 分页查询 ![image-20220416190307408](MySQL.assets/image-20220416190307408.png) #### 顺序 ![image-20220416193050322](MySQL.assets/image-20220416193050322.png) ```mysql -- 案例：请统计各个部门ground by 的平均工资avg -- 并且是大于1000的having，并且按照平均工资从高到低排序，order by -- 取出前两行记录 limit SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 1000 ORDER BY AVG(sal) DESC LIMIT 0 , 2; ``` ### 多表查询 笛卡尔积 > 不加过滤条件，表相乘 ![image-20220416195200853](MySQL.assets/image-20220416195200853.png) #### 自连接 ```mysql -- 多表查询的自连接 -- 思考题：显示公司员工名字和他的上级的名字 -- 自连接的特点： 1.把同一张表当做两张表使用 -- 2.需要给表取别名，表名，表别名 -- 3.列名不明确，可以指定列的别名，列名 as 列的别名 SELECT worker.ename AS '职员名' , boss.ename AS '上级名' FROM emp worker , emp boss WHERE worker.mgr = boss.empno; ``` #### 多行子查询 > 分析：子查询相当于嵌套在小括号里的 > > 单行子查询就是小括号里的只有一行记录 > > 多行子查询就是小括号里的有多行记录 > > `都是单列` ```mysql -- 单行子查询 -- 显示和SMITH同一个部门的员工 SELECT * FROM emp WHERE deptno = ( SELECT deptno FROM emp WHERE ename = 'SMITH'); -- 多行子查询（配合in来） -- 查询和部门10的工作相同的雇员的 -- 名字、岗位、工资、部门号，但是不含10号部门的 SELECT DISTINCT ename , job , sal , deptno FROM emp WHERE job IN( SELECT DISTINCT job FROM emp WHERE deptno = 10 ) AND deptno != 10;-- 不等 != 或<> ``` #### 子查询之临时表 > 将子查询当做一张临时表 题目：查询ecshop中各个类别中，价格最高的商品 ![image-20220418191452911](MySQL.assets/image-20220418191452911.png) > 将小括号里的子查询当做临时表 #### 子查询之all和any操作符 ```mysql -- 显示工资比部门30的所有员工的工资高的员工姓名、工和部门号 SELECT ename , sal , deptno FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30) -- 显示工资比部门30的其中一个员工的工资高的员工姓名、工和部门号 SELECT ename , sal , deptno FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 30) ``` #### 多列子查询 ```mysql -- 查询与allen的部门和岗位完全相同的所有雇员（不含allen本人） SELECT * FROM emp WHERE (deptno , job) = ( SELECT deptno , job FROM emp WHERE ename = 'ALLEN') AND ename != 'ALLEN'; ``` > 写法 表.*表示将该表所有的列都显示出来 ### 表复制和去重 ```mysql SELECT COUNT(*) FROM my_tab01; DESC my_tab01; -- 创建一个表来演示自我复制 CREATE TABLE my_tab01 LIKE emp; -- 上面这个语句相当于复制emp表的结构 -- 自我复制 INSERT INTO my_tab01 SELECT * FROM my_tab01; -- 去重 -- 思路： -- （1）先创一个临时表my_tmp 和my_tab01表的结构一样 CREATE TABLE my_tmp LIKE my_tab01; -- （2）把my_tmp的记录通过DISTINCT关键字处理后把记录复制到my_tmp INSERT INTO my_tmp SELECT DISTINCT * FROM my_tab01; -- （3）清除my_tab01记录 DELETE FROM my_tab01; -- （4）把my_tmp表的记录复制到my_tab01 INSERT INTO my_tab01 SELECT * FROM my_tmp; -- （5）drop掉临时表my_tmp DROP TABLE my_tmp; ``` ### 合并查询 ```mysql -- 下列俩条语句 SELECT ename , sal , job FROM emp WHERE sal > 2500 ; SELECT ename , sal , job FROM emp WHERE job = 'MANAGER' ; -- union all 就是将两个查询结果合并，不会去重 SELECT ename , sal , job FROM emp WHERE sal > 2500 UNION ALL SELECT ename , sal , job FROM emp WHERE job = 'MANAGER' ; -- union 就是将两个查询结果合并，会去重 SELECT ename , sal , job FROM emp WHERE sal > 2500 UNION SELECT ename , sal , job FROM emp WHERE job = 'MANAGER' ; ``` ### 外连接 `左外连接:`如果左侧的表完全显示我们就说是左外连接 `右外连接:`如果右侧的表完全显示我们就说是右外连接 ```mysql 左外连接语法如下： select ... from 表1 left join 表2 on 条件[表1：就是左表，表2：就是右表] ``` ```mysql 右外连接语法如下： select ... from 表1 RIGHT join 表2 on 条件[表1：就是左表，表2：就是右表] ``` 案例： ```mysql -- 先创表演示 -- stu表 CREATE TABLE stu( id INT, `name` VARCHAR(32)); INSERT INTO stu VALUES(1, 'jack') , (2, 'tom') , (3 , 'kity'), (4,'nono'); SELECT * FROM stu; -- exam表 CREATE TABLE exam( id INT , grade INT ); INSERT INTO exam VALUES(1, 56) , (2, 76) , (11 , 8); SELECT * FROM exam; -- 左连接 -- 显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号，成绩显示为空 SELECT `name` , stu.id , grade FROM stu LEFT JOIN exam ON stu.id = exam.id; -- 右连接 -- 显示所有成绩，如果没有名字匹配，显示空 SELECT `name` , stu.id , grade FROM stu RIGHT JOIN exam ON stu.id = exam.id; ``` ## MySQL约束 `约束：`用于确保数据库的数据满足谈定的商业规则 ### 约束的分类： - `NOT NULL` 非空约束，规定某个字段不能为空 - `UNIQUE` 唯一约束，规定某个字段在整个表中是唯一的 - `PRIMARY KEY` 主键(非空且唯一)约束 - `FOREIGN KEY` 外键约束 - `CHECK` 检查约束 - `DEFAULT` 默认值约束 ### PRIMARY KEY 主键 > primary key > > 用于唯一标示表行的数据，当定义主键约束后，该列不能重复 ```mysql -- 主键细节 CREATE TABLE t05( id INT PRIMARY KEY , -- 表示id列是主键 `name` VARCHAR(32), email VARCHAR(32)); INSERT INTO t05 VALUES(1, 'jack' , 'jack@sohu.com'); INSERT INTO t05 VALUES(2, 'tom' , 'tom@sohu.com'); -- 不能重复 INSERT INTO t05 VALUES(1, 'hsp' , 'hsp@sohu.com'); -- 不能为null INSERT INTO t05 VALUES(NULL, 'hsp' , 'hsp@sohu.com'); -- 主键唯一 -- 但可以成为复合主键 CREATE TABLE t06( id INT , `name` VARCHAR(32), email VARCHAR(32), PRIMARY KEY (id , `name`) -- 这就是id+name的复合主键 ); INSERT INTO t06 VALUES(1, 'jack' , 'jack@sohu.com'); INSERT INTO t06 VALUES(1, 'tom' , 'tom@sohu.com'); INSERT INTO t06 VALUES(1, 'tom' , 'tom@sohu.com');-- 这违反复合主键 -- 主键的指定方式有两种 -- 1.直接在字段名后指定：字段名 PRIMARY KEY -- 2.在表定义最后写 PRIMARY KEY(列名); -- 使用desc表名 ,可以看到primary key ``` ### NOT NULL 非空约束 > 规定某个字段不能为空 ### UNIQUE 唯一约束 > 规定某个字段在整个表中是唯一的 ```mysql -- unique的使用 CREATE TABLE t07( id INT UNIQUE,-- 表示id列唯一，不可重复 `name` VARCHAR(32), email VARCHAR(32)); INSERT INTO t07 VALUES(1, 'jack' , 'jack@sohu.com'); INSERT INTO t07 VALUES(1, 'tom' , 'tom@sohu.com');-- 添加不进去 -- unique使用细节 -- 如果没有指定not null ，则unique字段可以有多个null INSERT INTO t07 VALUES(NULL, 'tom' , 'tom@sohu.com'); -- 一张表可以有多个unique字段 ``` ### FOREIGN KEY 外键约束 > 用于定义主表和从表之间的关系： > > 外键约束要定义在从表上，主表则必须具有`主键约束`或是`unique约束`， > > 当定义外键约束后，要求外键列数据必须在主表的主键列存在或者是为null ```mysql -- 外键 -- 创建主表 my_class CREATE TABLE my_class( id INT PRIMARY KEY , -- 班级编号 `name` VARCHAR(32) NOT NULL DEFAULT ''); -- 创建从表 my_stu CREATE TABLE my_stu( id INT PRIMARY KEY , -- 学生编号 `name` VARCHAR(32) NOT NULL DEFAULT '' , class_id INT , -- 指定外键关系 FOREIGN KEY (class_id) REFERENCES my_class(id)); -- 测试数据 INSERT INTO my_class VALUES(100 , 'java') , (200 , 'web'); SELECT * FROM my_class; INSERT INTO my_stu VALUES(1 , 'tom' , 100) , ( 2, 'jack' , 200); INSERT INTO my_stu VALUES(3 , 'hsp' , 300); -- 加不进去，300班级不存在 -- 加入null可以,前提允许为null INSERT INTO my_stu VALUES(4 , 'tu' , NULL); SELECT * FROM my_stu; ``` 老韩细节说明： 1. 外键指向的表的字段，要求是primary key 或unique 2. 表的类型是innodb（这种表才支持外键） 3. 外键字段的类型要和主键字段的类型一致（长度可以不同） 4. 外键字段的值，必须在主键字段中存在或者为null【前提允许为null】 5. 一旦建立外键关系，数据不能随意删除 ### CHECK 检查约束 > mysql 5.7 目前还不支持check，只做语法校验，但不会生效() > > oracle SQL sever 这两数据库可以生效 ```mysql -- 演示check的使用 CREATE TABLE t08( id INT PRIMARY KEY , `name` VARCHAR(32), sex VARCHAR(6) CHECK (sex IN('man' , 'woman')), sal DOUBLE CHECK ( sal > 1000 AND sal < 200)); INSERT INTO t08 VALUES(1 , 'jack' , 'mid' , 1); SELECT * FROM t08; ``` ### AUTO_INCREMENT 自增长 细节： 1. 一个表最多只能有一个自增长列 2. **搭配主键约束或unique**使用 3. 当需要产生唯一标识符或顺序值时，可设置自增长 4. 自增约束的列的数据类型必须是整数类型 5. 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。 ```txt 设置初始值： alter table 表名 auto_increment = 初始值; ``` ## MySQL索引 ### 为什么要建索引 为了提高查询在海量数据表中的速度，建索引可以大大提高查询速度。 #### 索引怎么提高速度的 普通查询是全盘扫描的方式而索引通过优化了，比如二叉树等方式，从而提高速度 ```mysql 创建索引： create index 索引名 on 表名（字段）; ``` ### 创建索引优缺点： 1. 创建索引要占磁盘空间 2. 对（update、delete、insert）有影响 3. 查找速度变快 ### 索引类型： 1. 主键索引（主键自动的为主索引） 2. 唯一索引（UNIQUE） 3. 普通索引（INDEX） 4. 全文索引（FULLTEXT）【适合于myISAM】一般开发，不使用mysql自带的全文索引，而是使用Solr和ES ```mysql -- 演示索引的使用 CREATE TABLE t09( id INT , `name` VARCHAR(32)); -- 查询表是否有索引 -- 方式1 SHOW INDEXES FROM t09; -- 方式2 SHOW INDEX FROM t09; -- 方式3 SHOW KEYS FROM t09; -- 方式4 DESC t09; -- 添加唯一索引 CREATE UNIQUE INDEX id_index ON t09(id); -- 添加普通索引 CREATE INDEX id_index ON t09(id); -- 添加普通索引方式2 ALTER TABLE t09 ADD INDEX id_index(id); -- 添加主键索引 -- 建表直接添加为主键 ALTER TABLE t09 ADD PRIMARY KEY (id); -- 删除索引 DROP INDEX id_index ON t09; -- 删除主键索引 ALTER TABLE t09 DROP PRIMARY KEY; ``` ### 哪适合建索引 1. 较频繁的查询适合建索引 2. 唯一性太差的字段不适合建索引，比如sex男 3. 更新频繁字段不适合建索引 ## MySQL事务 > MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！ - 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。 - 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。 - 事务用来管理 insert,update,delete 语句 一般来说，事务是必须满足4个条件（ACID）：：原子性（**A**tomicity，或称不可分割性）、一致性（**C**onsistency）、隔离性（**I**solation，又称独立性）、持久性（**D**urability）。 - **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。 - **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。 - **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。 - **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。 ### 什么是事务 事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。 >如转账就要用事务来处理，用于保证数据的一致性 ![image-20220420221255722](MySQL.assets/image-20220420221255722.png) ### 事务的操作 ![image-20220420221354257](MySQL.assets/image-20220420221354257.png) ```mysql -- 开始一个事务另一种方式 SET autocommit = off; ``` 详说下面代码： > 回退可以回到b，也可以回退到a，但不能从a到b。 ```mysql -- 事务细节 CREATE TABLE t10 ( id INT, `name` VARCHAR(32)); -- 开始一个事务 START TRANSACTION; -- 设置保存点 SAVEPOINT a; INSERT INTO t10 VALUES(100, 'smith'); SAVEPOINT b; INSERT INTO t10 VALUES(200, 'jack'); ROLLBACK TO b; ROLLBACK TO a; COMMIT; -- 如果不开始事务，默认情况下，dml操作是自动提交的，不能回退 INSERT INTO t10 VALUES(300 , 'milan');-- 自动提交 commit SELECT * FROM t10; -- 如果只有一个开始事务，没有保存点，可以执行rollback START TRANSACTION; INSERT INTO t10 VALUES(400 , 'liu'); INSERT INTO t10 VALUES(500 , 'tu'); ROLLBACK-- 表示直接回退到事务开始的状态 COMMIT; -- 开始一个事务另一种方式 SET autocommit = off; ``` ### 事务的隔离级别 #### dirty read 脏读 当一个事务读取另一个事务`尚未提交的`dml语句时，产生脏读 #### nonrepeatable read 不可重复读 同一查询在同一事务中多次进行，由于其他提交事务所做的`修改或删除`，每次返回不同的结果集，此时发生不可重复读。 #### phantom read 幻读 同一查询在同一事务中多次进行，由于其他提交事务所做的`插入操作`，每次返回不同的结果集，此时发生幻读。 | MySQL隔离级别 | 脏读 | 不可重复读 | 幻读 | 加锁读 | | :------------------------- | ---- | ---------- | ---- | ------ | | 读未提交(Read uncommitted) | √ | √ | √ | 不加锁 | | 读已提交(Read committed) | × | √ | √ | 不加锁 | | 可重复读(Repeatable read) | × | × | × | 不加锁 | | 可串行化(Serializable) | × | × | × | 加锁 | ### 修改隔离的操作 ![image-20220421200535127](MySQL.assets/image-20220421200535127.png) ![image-20220421201107896](MySQL.assets/image-20220421201107896.png) ## MySQL存储引擎 ### 基本介绍 和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。 存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为`表类型`。 Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。 MySQL5.0支持的存储引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中**InnoDB和BDB提供`事务安全表`**，其他存储引擎是非事务安全表。 可以通过指定 `show engines` ， 来查询当前数据库支持的存储引擎 ： ### 存储引擎大全介绍 | 名称 | 介绍 | | :-------- | :----------------------------------------------------------- | | MyISAM | MyISAM存储引擎提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL版本里被支持；不支持事务处理；它是MySQL的默认的存储引擎； | | MEMORY | MEMORY存储引擎，别称HEAP存储引擎；提供“内存中”表，将数据存储在内存中。MEMORY存储引擎不支持事务处理；MySQL的所有版本都支持InnoDB存储引擎；注释：MEMORY存储引擎正式地被确定为HEAP引擎。 | | MERGE | MRG_MYISAM存储引擎，别名MERGE；MRG_MYISAM存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。MRG_MYISAM存储引擎不支持事务处理；MySQL的所有版本都支持MRG_MYISAM存储引擎； | | ISAM | Obsolete storage engine, now replaced by MyISAM | | MRG_ISAM | Obsolete storage engine, now replaced by MERGE | | InnoDB | InnoDB存储引擎，别名INNOBASE；提供事务安全表；MySQL的所有版本都支持InnoDB存储引擎；它支持事务处理； | | BDB | BDB存储引擎，别名BERKELEYDB；BDB存储引擎提供事务安全表；mysql 5.1以下版本才支持此存储引擎； | | EXAMPLE | EXAMPLE存储引擎是一个“存根”引擎，它不做什么。你可以用这个引擎创建表，但没有数据被存储于其中或从其中检索。这个引擎的目的是服务，在MySQL源代码中的一个例子，它演示说明如何开始编写新存储引擎。同样，它的主要兴趣是对开发者。 | | NDB | NDB存储引擎，别名NDBCLUSTER；NDB Cluster是被MySQL Cluster用来实现分割到多台计算机上的表的存储引擎。它在MySQL-Max 5.1二进制分发版里提供。这个存储引擎当前只被Linux, Solaris, 和Mac OS X 支持。在未来的MySQL分发版中，我们想要添加其它平台对这个引擎的支持，包括Windows。 | | ARCHIVE | ARCHIVE存储引擎被用来无索引地，非常小地覆盖存储的大量数据。 | | CSV | CSV存储引擎把数据以逗号分隔的格式存储在文本文件中。CSV存储引擎不支持事物处理； | | BLACKHOLE | BLACKHOLE存储引擎接受但不存储数据，并且查询也总是返回一个空集；/dev/null storage engine (anything you write to it disappears) | | FEDERATED | FEDERATED存储引擎把数据存在远程数据库中。在MySQL 5.1中，它只和MySQL一起工作，使用MySQL C Client API。在未来的分发版中，我们想要让它使用其它驱动器或客户端连接方法连接到另外的数据源。FEDERATED存储引擎支持事务处理； | ### 常用的存储引擎 下面重点介绍几种常用的存储引擎， 并对比各个存储引擎之间的区别， 如下表所示 ： | 特点 | InnoDB | MyISAM | MEMORY | MERGE | NDB | | :----------- | :---------------- | :----- | :----- | :---- | :--- | | 存储限制 | 64TB | 有 | 有 | 没有 | 有 | | 事务安全 | 支持 | | | | | | 锁机制 | 行锁(适合高并发) | 表锁 | 表锁 | 表锁 | 行锁 | | B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 | | 哈希索引 | | | 支持 | | | | 全文索引 | 支持(5.6版本之后) | 支持 | | | | | 集群索引 | 支持 | | | | | | 数据索引 | 支持 | | 支持 | | 支持 | | 索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 | | 数据可压缩 | | 支持 | | | | | 空间使用 | 高 | 低 | N/A | 低 | 低 | | 内存使用 | 高 | 低 | 中等 | 低 | 高 | | 批量插入速度 | 低 | 高 | 高 | 高 | 高 | | 支持外键 | 支持 | | | | | ### 老韩细节说明 重点给大家介绍三种: MyISAM、InnoDB、 MEMORY 1. MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求 2. InnoDB存储引擎提供 了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MylSAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。 3. MEMORY存储引擎使用存在内存中的内容来创建表。 每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦MySQL服务关闭，表中的数据就会丢失掉,表的结构还在。 代码说明： ```mysql -- 查看所有的存储引擎 SHOW ENGINES; -- INNODB存储引擎，前面使用过 -- 1.支持事务 2.支持外键 3.支持行级锁 -- MYISAM 存储引擎 CREATE TABLE t11( id INT , `name` VARCHAR(32)) ENGINE MYISAM ; -- 1.添加速度快 2.不支持外键和事务 3.支持表级锁 -- 演示MYISAM存储引擎不支持事务 -- 开启事务 START TRANSACTION; -- 设置保存点 SAVEPOINT t1; -- 插入记录 INSERT INTO t11 VALUES(1 , 'jack'); -- 查表 SELECT * FROM t11; -- 回退到t1 ROLLBACK TO t1; -- 结果表记录还存在 -- MEMORY存储引擎 -- 执行速度很快（没有IO读写） 默认支持索引（hash表） CREATE TABLE t12( id INT , `name` VARCHAR(32)) ENGINE MEMORY -- 添加记录 INSERT INTO t12 VALUES(1, 'tom') , (2 , 'jack') , (3 , 'smith'); -- 查表t12 SELECT * FROM t12; -- 重启mysql服务后，记录将不存在，表结构还在 ``` ### 如何选择表的存储引擎 1. 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么选MYISAM，速度快 2. 如果需要支持事务，选INNODB 3. MEMORY适合所做的任何修改在服务器重启后都将消失（比如用户的在线离线） ```mysql -- 指令修改存储引擎 ALTER TABLE t12 ENGINE = INNODB; ``` ## MySQL视图 ### 基本介绍： 1. MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成。 2. 视图的行和列的数据来自于基表，并且还是在使用视图时动态生成的。 3. 基表：用来创建视图的表叫做基表base table 4. 视图的改变会影响基表，基表的改变也会影响视图 5. 创建视图只有一个视图结构文件（形式：视图名.frm） 6. 可以在视图上在创一个视图（套娃） ### 视图的基本使用 ```MySQL -- 创建视图 create view 视图名 as select 语句 ``` ```MySQL -- 更新视图 alter view 视图名 as select 语句 ``` ```MySQL -- 查看视图信息 show create view 视图名 ``` ```MySQL -- 删除视图 drop view 视图名 ``` ```MySQL -- 将emp表中empno , ename , job , deptno 作为视图 -- 视图操作演示 CREATE VIEW emp_view01 AS SELECT empno , ename , job , deptno FROM emp; -- 查看视图结构 DESC emp_view01; -- 查看记录 SELECT * FROM emp_view01; -- 查看视图创建信息 SHOW CREATE VIEW emp_view01; -- 更新视图 ALTER VIEW emp_view01 AS SELECT empno FROM emp; -- 删除视图 DROP VIEW emp_view01; ``` ### 视图优点 1. `安全`。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。 2. `性能`。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。 3. `灵活`。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。 ## MySQL用户管理 ### 基本介绍 ![image-20220422201226622](MySQL.assets/image-20220422201226622.png) ```MySQL -- 查看user SELECT `Host` , `User` , authentication_string FROM mysql.user; -- Host表示允许登录的位置 -- localhost 表示该用户只允许本机登录，也可以指定ip地址 -- User用户名 -- authentication_string 通过mysql的password加密的密码 ``` ### 创建用户 老韩细节： 1. **如果不指定Host，则为%，表示任何ip都可以连接** 2. **Host也可以为一个ip地址,如下** ```mysql create user 'xxx'@'192.168.1.%' -- 表示ip为192.168.1前缀的都可以登录 ``` 3. 删除用户时，如果是%，可以直接写 ```mysql drop user 用户名 ``` ![image-20220422194334798](MySQL.assets/image-20220422194334798.png) ### 删除用户 ```MySQL CREATE USER 'tu'@'localhost' IDENTIFIED BY '123456'; DROP USER 'tu'@'localhost'; ``` ### 修改用户密码 ![image-20220422202058435](MySQL.assets/image-20220422202058435.png) ```MySQL -- 修改本用户密码 SET PASSWORD = PASSWORD('hsp'); -- 我的原密码就是hsp -- 修改其他用户密码（需要权限） SET PASSWORD FOR 'tu'@'localhost' = PASSWORD('abcdef'); ``` ### 用户权限 权限列表： ![image-20220422213134975](MySQL.assets/image-20220422213134975.png) #### 给与用户权限 ```mysql 格式： grant 权限列表 on 库.表 to 用户名@登录位置 【identified by '密码'】 ``` 1. ***.\*代表所有数据库、库.\*代表一个数据库** 2. identified by 可以省略，加上去后，如果用户存在就修改该用户的密码，用户不存在就是创建该用户。 3. ```mysql grant all [privileges] on ... //表示赋予该用户在该对象上的所有权限 ``` #### 回收用户权限 ```mysql 格式： revoke 权限列表 on 库.表 from 用户名@登录位置 ``` ### 用户管理练习题 ```mysql -- 1.创建一个用户(你的名字， 拼音)，密码123, -- 并且只可以从本地登录，不让远程登录mysq| CREATE USER 'zhenjin'@'localhost' IDENTIFIED BY '123'; -- 2.创建库和表testdb 下的news表, 要求:使用root用户创建 CREATE DATABASE testdb ; CREATE TABLE news ( id INT , `name` VARCHAR(32)); INSERT INTO news VALUES(1 , 'tu') , (2 ,'wen') ,(3 , 'liu'); -- 查表记录 SELECT * FROM news; -- 3.给用户分配查看news表积添加数据的权限 GRANT SELECT ,INSERT , UPDATE ON testdb.news TO 'zhenjin'@'localhost'; -- 4.测试看看用户是否只有这几个权限 -- 5.修改密码为abc , 要求:使用root用户完成 SET PASSWORD FOR 'zhenjin'@'localhost' = PASSWORD('abc'); -- 6.重新登录 -- 7.回收权限 REVOKE SELECT ON testdb.news FROM 'zhenjin'@'localhost'; -- 8.使用root用户删除你的用户 DROP USER 'zhenjin'@'localhost'; ``` 练习题 ```mysql -- homework02 SELECT * FROM dept; SELECT * FROM emp; -- 查dept表和emp表结构 DESC dept; DESC emp; -- 显示所有部门得到名称 SELECT dname FROM dept; -- 显示所有雇员名及其全年收入 13月（工资+补助），并指定列名"年收入" SELECT ename , (sal + IFNULL(comm, 0.0))*13 AS "年收入" FROM emp -- 显示工资超过2850的员工的姓名和工资 SELECT ename , sal FROM emp WHERE sal > 2850; -- 显示工资不在1500到2850之间的所有雇员名及工资 SELECT ename , sal FROM emp WHERE sal > 2850 OR sal < 1500; -- 显示编号为7566的雇员姓名及所在部门编号 SELECT ename , deptno FROM emp WHERE empno = 7566; -- 显示部门10和30中工资超过1500的员工姓名及工资 SELECT ename , sal FROM emp WHERE (deptno = 10 OR deptno = 30) AND sal > 1500; -- 显示无管理这的雇员名及岗位 SELECT ename , job FROM emp WHERE mgr IS NULL; -- 显示在1991年2月1号到1991年5月1日之间雇用的雇员名， -- 岗位及雇用日期，并以雇用日期进行排序 SELECT ename , job , hiredate FROM emp WHERE hiredate >= '1991-2-1' AND hiredate <= '1991-5-1' ORDER BY hiredate; -- 显示获得补助的所有雇员名，工资及补助，并以工资降序排序 SELECT ename , sal , comm FROM emp ORDER BY sal DESC; ``` ```mysql -- homework03 SELECT * FROM emp; -- 1.选择部门30中的所有员工. SELECT * FROM emp WHERE deptno = 30; -- 2.列出所有办事员(CLERK)的姓名，编号和部门编号. SELECT ename , empno , deptno FROM emp WHERE job = 'CLERK'; -- 3.找出佣金高于薪金的员工. SELECT * FROM emp WHERE IFNULL(comm , 0) > sal; -- 4.找出佣金高于薪金60%的员工， SELECT * FROM emp WHERE IFNULL(comm , 0) > sal * 0.6; -- 5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料. SELECT * FROM emp WHERE (deptno = 10 AND job = 'MANAGER') OR (deptno = 20 AND job = 'CLERK'); -- 6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK), -- 还有既不是经理又不是办事员但其薪金大于或等于2000的所有员I的详细资料. SELECT * FROM emp WHERE deptno = 10 AND job = 'MANAGER' UNION SELECT * FROM emp WHERE deptno = 20 AND job = 'CLERK' UNION SELECT * FROM emp WHERE NOT (job = 'CLERK' OR job = 'MANAGER') AND sal >= 2000; -- 7.找出收取佣金的员工的不同工作. SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL ; -- 8.找出不收取佣金或收取的佣金低于100的员工. SELECT * FROM emp WHERE comm IS NULL OR IFNULL(comm , 0) < 100; -- 9.找出各月倒数第3天受雇的所有员工. SELECT * FROM emp WHERE (DAY(DATE_ADD(hiredate,INTERVAL 3 DAY)) = 30) OR (MONTH(DATE_ADD(hiredate,INTERVAL 3 DAY)) > MONTH(hiredate)); SELECT LAST_DAY(NOW());//得到这个月最后一天 -- 10.找出早于12年前受雇的员工. SELECT * FROM emp WHERE DATEDIFF(NOW() , hiredate) > 12*365; -- 11.以首字母小写的方式显示所有员工的姓名. SELECT CONCAT(LCASE(LEFT(ename,1)) , SUBSTRING(ename , 2)) FROM emp ; -- 12.显示正好为5个字符的员工的姓名. SELECT ename FROM emp WHERE LENGTH(ename) = 5; -- 13.显示不带有" R"的员工的姓名. SELECT * FROM emp WHERE ename NOT LIKE'%R%'; -- 14. 显示所有员工姓名的前三个字符. SELECT SUBSTRING(ename , 1 , 3) FROM emp; -- 15.显示所有员工的姓名,用a替换所有"A" SELECT REPLACE(ename , 'A' ,'a') FROM emp; -- 16.显示满10年服务年限的员I的姓名和受雇日期. SELECT ename , hiredate FROM emp WHERE DATE_ADD(hiredate , INTERVAL 10 YEAR) <= NOW(); -- 17.显示员工的详细资料按姓名排序. SELECT * FROM emp ORDER BY ename; -- 18.显示员工的姓名和受雇日期,根据其服务年限将最老的员工排在最前面. SELECT ename , hiredate FROM emp ORDER BY hiredate; -- 19.显示所有员工的姓名、 工作和薪金按工作降序排序，若工作相同则按薪金排序. SELECT ename ,job , sal FROM emp ORDER BY job DESC ,sal; -- 20.显示所有员工的姓名、加入公司的年份和月份按受雇日期所在月排序，若月份相同则将最早年份 -- 的员工排在最前面. SELECT ename , CONCAT(YEAR(hiredate),'-', MONTH(hiredate)) FROM emp ORDER BY MONTH(hiredate),YEAR(hiredate); -- 21.显示在一个月为30天的情况所有 员工的日薪金,忽略余数. SELECT ename , FLOOR(sal / 30) , sal / 30 FROM emp; -- 22.找出在(任何年份的)2月受聘的所有员工。 SELECT * FROM emp WHERE MONTH(hiredate) = 2; -- 23.对于每个员工，显示其加入公司的天数. SELECT ename , DATEDIFF(NOW(), hiredate) FROM emp; -- 24.显示姓名字段的任何位置包含”A"的所有员工的姓名. SELECT * FROM emp WHERE ename LIKE '%A%'; -- 25.以年月日的方式显示所有员工的服务年限(大概) SELECT ename , FROM_DAYS(DATEDIFF(NOW(), hiredate)) FROM emp; ```