MySQL入门

CREATE DATABASE [IF NOT EXISTS] db_name
        [create_specification[,create_specification]...]
create_specification:
 [DEFAULT]CHARACTER SET charset_name
 [DEFAULT]COLLATE collation_name

1. CHARATER SET：指定数据库采用的字符集，如果不指定字符集，默认utf-8

2. COLLATE：指定数据库字符集的校对规则(常用的utf8_bin [区分大小写] ,utf8_general_ci [不区分大小写] 注意：默认是utf8_general_ci)

语法一：CREATE DATABASE

语法二：DROP DATABASE

查询语法（select）

#写法一：
SELECT * 
    FROM 数据库 
    WHERE 列名
#写法二：
SELECT * FROM 数据库 WHERE 列名

校对规则 utf8_bin [区分大小写] ,utf8_general_ci [不区分大小写]

utf8_bin [区分大小写]

当前是在cc03数据库创建的表‘校对规则 utf8_bin’

说明：

在创建表时，若没有指定字符集和校对规则，表的字符集和校对规则会默认为当前数据库的字符集和校对规则

如图所示为utf8_bin的数据类型和内容

#查询
#select 查询 * 表示所有字段 FROM 表示从哪个表
#WHERE 表示从哪个字段

例：查询utf8_bin中name为tom的数据

SELECT *
	FROM utf8_bin
	WHERE NAME = 'tom'

结果：

utf8_general_ci[不区分大小写]

在数据库cc02中创建字符集为utf8，校对规则为utf8_general_ci的表格

并输入相同数据

进行相同的数据查询

由此可观察出校对规则utf8_bin 与utf8_general_ci 的区别

查看、删除数据库

显示数据库语句

SHOW DATABASES

显示数据库创建语句

SHOW CREATE DATABASE db_name

数据库删除语句【慎用】

创建数据库补充：创建数据库时使用反引号'`'可以规避关键字

DROP DATABASE [IF EXISTS] db_name

备份恢复数据库

备份数据库（运用DOS命令执行）

语法： mysqldump - u 用户名 -p - B 数据库1 数据库2 数据库n > 文件名.sql

此时打开3.21.sql文件就可以看到CC数据库了

恢复数据库

此时先把数据库CC删除

运用DOS命令进入数据库系统

使用sourse D:\mysql-5.7.19-winx64\1\3.21.sql 恢复文件

由此可知CC数据库被恢复

注意

还有一种方式就是将sql文件进行复制粘贴再运行,对我来说其实sql文件中的内容本质上就是一串命令行相当于重新创建了一次

最简单的备份恢复方式(使用备份/导入)

备份数据库的表

mysqldump -u 用户名 -p密码数据库表1 表2 表n > 文件路径

首先在CC数据库下创建名为1的表并输入如图所示数据

先使用DOS命令对相应的表进行备份

之后的操作如前两个小节一般,对数据库进行恢复就可以对相应的表进行恢复操作

创建表

语法

CREATE TABLE table_name
{
    field1 datatype,
    field2 datatype,
    field3 datatype
}character set 字符集 collate 校对规则 engine 引擎
#field: 指定列名 datatype:指定列类型(字段类型)
#character set:如不指定则为所在数据库校对规则
#engine：引擎

实践：创建一个表cc02，列名id，name，password，birthday类型INT，VARCHAR（255），VARCHAR（255），DATE，字符集utf8，校对规则utf8_bin,引擎 engine

MySql常用数据类型（列类型）

数值型（整数）的基本使用

说明：在满足需求的情况下，尽量选择占用空间小的类型

类型	字节	最小值	最大值
		(带符号的/无符号的)	（带符号的/无符号的）
TINGINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-2^63	2^63-1
		0	2^64-1

实例：测试数据类型TINGINT的数据范围

可以观察到-129，128超出TINYINT数据类型所包含的数据范围

在实际应用中，为了节省内存，并且所输入的数据是正数，我们可以使用unsigned关键字使数据类型变成无符号数据类型，这样就可以输入更大的数据

此时可以发现数据库编辑系统并没有报错

使用select语句查询t4表的数据发现可以录入（超出数据范围录入不了）

此时就不可以输入负数（不在无符号数据类型范围内）

数值型（bit）的使用

1. 基本使用

mysql>create table t02 (num bit(8));

mysql>insert into t02(1,3);

mysql>intsert into t02 values(2,65);

2.细节说明

bit字段显示时，按照位的方式显示

查询的时候仍然可以使用添加的数据

如果一个值只有0，1可以考虑bit（1），可以节约空间

位类型，M指定位数，默认值1，范围1-64

数值型（小数）的基本使用

1. FLOAT/DOUBLE [UNSIGNED]

Float 单精度，Double 双精度

2. DECIMAL（M,D） [UNSIGNED]

- 可以支持更加精确的小数位。M是小数位数（精度）的总数，D是小数点（标度）后面的位数

- 如果D是0，则值没有小数点或分数部分。M最大65。D最大是30。如果D被省略，默认是0。如果M被省略，默认是10

实例：

由图可知，向CC04中三个不同数据类型输入11.123456789123456789，观察三个不同数据类型对数据的保存情况，可知float，double数据类型进位制采用四舍五入进位制，并且decimal数据类型对多出来的位数进行补零填充

字符串的基本使用

CHAR(size)
固定长度字符串最大255字符

VARCHAR(size)

可变长度字符串最大65532字节【utf8编码最大21844字符 1-3个字节用于记录大小】

注：

根据不同的字符编码方式，相同字节所能输入的字符数不同

例：

在utf8编码方式下，varchar 字符串长度最大字节为 65535 但其中有三个字节用于记录数据大小，所以能输入的字符串字节数为65535 - 3 = 65532，在utf8字符编码方式下一个字符站3个字节，所以最终可输入字节为 65532 / 3 = 21844个

例：

由图可知，当设置varchar数值长度为21845时，系统进行报错，原因是65535中三个字符用来记录数值大小，所以字符长度最大为21844

字符串使用细节

1. char（4）

// 这个4表示字符数量（最大255），不是字节数，不管是中文还是字母都是放四个，按字符计算

varchar（4）

// 这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据

注：根据如上所述，根据不同的字符编码（如utf8 一个字符占三个字节，gbk 一个字符占用2个字节）所占用的空间不同

2. char（4）是定长，即即使插入‘aa’，也会占用4个字符

varchar（4）是变长，即插入‘aa’，实际占用空间大小并不是4个字符而是按照实际占用空间来分配

// varchar本身还要占用1-3个字节来记录存放内容长度

3. char 和 varchar 的使用情况

// 如果数据是定长，推荐使用char,比如md5的密码，邮编，手机号，身份证号等

// 如果一个字段的长度是不确定的，使用varchar，比如留言、文章

查询速度：char > varchar

4. 在存放文本时，也可以使用Text数据类型，可以将Text列视为varchar列，注意Text不能有默认值，大小0-2^16字节

// mediumtext 2^24 longtext 2^36

日期类型的基本使用

如下图所示：对创建的表cc06的birthday，job_time 列插入相关数据，login_time 列不插入数据

#演示时间相关的数据类型
CREATE TABLE cc06(
	birthday DATE,
	job_time DATETIME,
	-- 若希望 login_time 自动更新可进行相关设置
	login_time TIMESTAMP 
		NOT NULL -- 不可为空
		DEFAULT CURRENT_TIMESTAMP -- 默认当前时间戳
		ON UPDATE CURRENT_TIMESTAMP -- 修改时自动以当前时间戳为准
);
INSERT INTO cc06 (birthday , job_time) 
	VALUES('2022-11-11','2022-11-11 10:10:10');
SELECT * FROM cc06

根据所得接结果可知login_time列，自动插入当前时间戳

此时我们再进行对birthday，job_time 列插入新的数据

INSERT INTO cc06 (birthday , job_time) 
	VALUES('2022-11-12','2022-11-12 11:10:10');
SELECT * FROM cc06

列login_time 会根据当前的时间进行更新

修改表

实例：

创建如图所示的表：

alter 表上增加一个image列，varchar类型(要求在resume后面)

修改job列使其长度变为60

删除sex列

修改表名

修改表的字符集为utf8

ALTER TABLE alter01 CHARACTER SET utf8

修改列名name为user_name

数据库CURD语句

Insert语句

语法

INSERT INTO table_name[(colum[,colum])]
VALUES (value[,value]);

实例：

Insert语句注意事项

1.插入的数据应与字段的数据类型相同。比如把'abc' 添加到 int 类型会错误

2.数据的长度应在列的规定范围内，例如:不能将一个长度为80的字符串加入到长度为40的列中。
3.在values中列出的数据位置必须与被加入的列的排列位置相对应

4.字符和日期型数据应包含在单引号中。
5.列可以插入空值[前提是该字段允许为空]，insert into table value(null)

6.insert into tab name(列名..)values (),(),()；形式添加多条记录

7.如果是给表中的所有字段添加数据，可以不写前面的字段名称

8.默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错

Update语句

语法

UPDATE table_name
    set colum_name = expr[colum_name2 = expr2...]
    (WHERE where_defintion)

实例：

创建text表并插入数据

使用update语句更新所有行的salary为5000

只把小王的salary修改为4000

Update语句使用细节

1.UPDATE语法可以用新值更新原有表行中的各列。

2.SET子句指示要修改哪些列和要给予哪些值。

3.WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

4.如果需要修改多个字段，可以通过 set 字段1=值1,字段2=值2..

Delete语句

语法

delete from table_name
    [WHERE where_definition]

实例：

删除图中name为小王的记录

Delete语句使用细节

1. 如果不适用where语句，将删除表中所有数据

2. Delete语句不能删除某一列的值（可以使用update设为null或者‘ ’）

3. 使用delete语句仅删除记录不删除表本身（drop table 表名；）

Select语句

为熟练操作语法，接下来会创建一张表，在之后的语法都会对该表进行相关操作

## student
CREATE TABLE student(
	id INT NOT NULL DEFAULT 1,
	`name` VARCHAR(20) NOT NULL DEFAULT ' ',
	chinese FLOAT NOT NULL DEFAULT 0.0,
	english FLOAT NOT NULL DEFAULT 0.0,
	math FLOAT NOT NULL DEFAULT 0.0
);
INSERT INTO student VALUES
(1,'韩',89,78,90);
INSERT INTO student VALUES
(2,'张飞',67,98,56);
INSERT INTO student VALUES
(3,'宋江',87,78,77);
INSERT INTO student VALUES
(4,'关羽',88,98,90);
INSERT INTO student VALUES
(5,'赵云',82,84,67);
INSERT INTO student VALUES
(6,'欧阳锋',55,85,45);
INSERT INTO student VALUES
(7,'黄蓉',75,65,30);

- 基本语法1 [DISTINCT]

SELECT [DISTINCT] *[COLUMN1,COLUMN2,..] FROM table_name

- 注意事项

1. Select 指定查询哪些列的数据

2. column 指定列名

3. * 代表查询所有列

4. FROM 指定查询哪张表

5. DISTINCT 可选，指显示结果时，是否去掉重复数据

例子：

SELECT DISTINCT english FROM student

- 基础语法2

SELECT * |[column1|expression,column2|expression,..] FROM table_name;

- 基础语法3 [as]

SELECT column_name as 别名 FROM 表名

将各个学生的总分用 total_score表示

Where语句中经常使用的运算符

比较运算符	> < >= <= = <> !=	大于、小于、大于（小于）等于、不等于
	BETWEEN...AND...	显示在某一区间的值
	IN(set)	显示在in列表中的值，例：in(100,200)
	LIKE `张pattern` NOT LIKE	模糊查询
	IS NULL	判断是否为空
逻辑运算符	and	多条件同时成立
	or	多条件任一成立
	not	不成立，例：where not(salary>100)

注：

between...and... 所在某区间的范围为闭区间

用于字符匹配的LIKE谓词：谓词LIKE可用来表示字符串的匹配条件，* 表示任意多个字符，？表示任意一个字符（有些系统分别用 % 和 - 表示）

实例：

查找赵姓的学生的成绩

查找第二个字为云的同学姓名和成绩

- 基础语法3 [Order by]

SELECT column1,column2,... 
    FROM table_name 
    order by column asc|desc

1. Order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名

2. Asc 升序[默认]、Desc 降序

3. ORDER BY 子句应位于SELECT语句的结尾
实例：

对数学成绩按升序排序输出

SELECT * FROM student 
	ORDER BY math ASC;

将总分按降序排序

select `name`,(math+chinese+english) as total_score from student
	order by total_score desc;

函数

合计/统计函数

统计函数

SELECT count(*)|count(列名) from table_name
    WHERE where_definition

注：

count（*）返回满足条件的记录的行数

count（列）统计满足条件的某列有多少个，但会排除内容为NULL的记录

实例：

创建tt表并向其中插入相关数据

CREATE TABLE tt(
	`name` VARCHAR(20)
);
INSERT INTO tt VALUES('jack');
INSERT INTO tt VALUES('mike');
INSERT INTO tt VALUES('lili');
INSERT INTO tt VALUES('lucy');
INSERT INTO tt VALUES(NULL);
SELECT * FROM tt;

统计表中有多少人

SELECT COUNT(*) FROM tt

SELECT COUNT(`name`) FROM tt

合计函数

sum（求数值）

语法：

SELECT sum（列名）(,sum(列名),..) FROM table_name
    [WHERE where_definition]

注：

sum函数只对数值起作用

实例：

统计班级的数学总成绩

SELECT SUM(math) FROM student;

avg函数（求平均数）

求班级学生的数学平均成绩

SELECT AVG(math) FROM student;

Max/Min函数

Max/Min函数返回满足where条件的一列的最大/最小值

语法：

SELECT MAX(列名) FROM table_name
 [WHERE where_definition]

实例：

求班级成绩最高分和最低分的同学的名字和总分

select max(math+english+chinese),Min(math+english+chinese) from student;

分组统计

group by

语法：

SELECT column1,column2,column3,.. FROM table_name 
    group by column

group by ... having..

语法：

SELECT column1,column2,column3,.. FROM table_name 
    group by column having ...

注：group by 用于对查询的结果分组统计

having 子句用于限制分组显示结果

创教新表dept

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');
SELECT * FROM dept;

创建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),
    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),
(7400,'ALLEN','SALESMAN',7698,'1991-2-20',1600,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,30),
(7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1991-12-3',1500.00,NULL,30),
(7900,'JAMES','CLERK',7698,'1991-12-3',950.00,NULL,20),
(7902,'FORD','ANALYST',7566,'1991-12-3',3000.00,NULL,20),
(7934,'MILLER','CLERK',7782,'1992-1-23',1300.00,NULL,10);

创建salgrade表

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),
			(2,1201,1400),
			(3,1401,2000),
			(4,2001,3000),
			(5,3001,9999);
SELECT * FROM salgrade;

实例：

查找每个部门的最高工资和平均工资

SELECT AVG(sal),MAX(sal),deptno FROM emp GROUP BY deptno;

以上一实例为基础，找出平均工资低于两千的部门

SELECT AVG(sal),MAX(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)<2000;

字符串函数

CHARSET	返回字串字符集
CONCAT(string2 [,....])	连接字串
INSTR(string,substring)	返回substring在string中出现的位置，没有返回0
UCASE(string2)	转换成大写
LCASE(string2)	转换成小写
LEFT(string2,length)	从string2中的左边起取length个字符
RIGHT(string2，length)	从string2中的右边起取length个字符
LENGTH(string)	string长度[按照字节]
REPLACE(str,search_str,replace_str)	在str中用replace_str替换search_str
STRCMP(string1，string2)	逐字符比较字串大小
SUBSTRING(str,position[length])	从str的position开始[从1开始计算]，取length个字符
LTRIM(string2)RIRIM(string2)trim	去除前端空格或后端空格

实例：

查看emp中ename列的字符集

SELECT CHARSET(enmae) FROM emp;

CONCAT函数使用

SELECT CONCAT(enmae,'job is ',job) FROM emp;

INSTR函数使用

SELECT INSTR('chengcheng','cheng')

SELECT INSTR('chengcheng','cheng') FROM DUAL;

注：

dual为亚元表（系统表），可以作为测试表使用

LENGTH函数用法

SELECT LENGTH('cheng'),LENGTH('程');

REPLACE函数使用

SELECT enmae,REPLACE(job,'MANAGER','经理') FROM emp;

STRCMP函数使用

SELECT STRCMP('cheng','aheng'),STRCMP('cheng','cheng'),
	STRCMP('cheng','abcng'),STRCMP('cheng','zheng'),
	STRCMP('cheng','ahfng'),STRCMP('cheng','chen');

注：

此函数逐字符比，若有在相同位置上的字符不同，则比较，若前面大于后面，返回1，反之返回-1，不进行后续字符的比较，都相同返回0

数学函数

ABS(num)	绝对值
BIN(decimal_number)	十进制转二进制
CEILING(number2)	向上取整，得到比num2大的最小整数
CONV(number2,from_base,to_base)	进制转换
FLOOR(number2)	向下取整，得到比number2更小的最大整数
FORMAT(number,decimal_places)	保留小数位数
HEX(DecimalNumber)	转十六进制数
LEAST(number，number2[，...])	求最小值
MOD(numerator，denominator)	求余
RAND([seed])	RAND([seed])范围为【0-1.0】

注：

若使用RAND()返回随机数,其范围为【0，1.0】

若使用RAND(seed)返回随机数，范围【0，1.0】，若seed不变，该随机数不变（seed为常数）

日期函数

CURRENT_DATE	当前日期（年月日）
CURRENT_TIME	当前时间（时分秒）
CURRENT_TIMESTAMP	当前时间戳（年月日时分秒）
DATE(datetime)	返回datetime的日期部分
DATE_ADD(date2,INTERVAL d_value d_type)	在date2上加上日期或时间
DATE_SUB(date2,INERVAL d_value d_type)	在date2上减去一个时间
DATEDIFF(date1,date2)	两个日期差（结果是天）
TIMEDIFF(date1,date2)	两个时间差（时分秒）
NOW()	当前时间
YEAR\|MONTH\|DATE(datetime)	年月日
UNIX_TIMESTMP()	返回1970-1-1 00：00：00到现在的秒数
FROM_UNIXTIME()	将整数转换成时间戳

实例：

创建mes表并插入相关数据

CREATE TABLE mes(
	id INT,
	content VARCHAR(10),
	send_time DATETIME
);
INSERT INTO mes
	VALUES(1,'北京新闻',CURRENT_TIMESTAMP),
		(2,'上海新闻',CURRENT_TIMESTAMP),
		(3,'天津新闻',CURRENT_TIMESTAMP)
SELECT * FROM mes

查找各新闻的日期

SELECT id,content,DATE(send_time)
	FROM mes

查找插入记录时间在十分钟以内的记录

SELECT * FROM mes
	WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE) > NOW()

DATE(datetime)	返回datetime的日期部分
DATE_ADD(date2,INTERVAL d_value d_type)	在date2上加上日期或时间
DATE_SUB(date2,INERVAL d_value d_type)	在date2上减去一个时间
DATEDIFF(date1,date2)	两个日期差（结果是天）

为每条记录的时间戳多加十分钟

SELECT id,content,DATE(send_time),DATE_ADD(send_time,INTERVAL 10 MINUTE) FROM mes

注：

1. DATE_ADD()中的interval后面可以是YEAR,MONTH,DAY,MINUTE,SECOND

2. DATE_SUB()中的interval后面可以是YEAR,MONTH,DAY,MINUTE,SECOND

3. DATEDIFF(date1，date2)得到的结果可以是负数

4. 这四个函数的日期类型可以是date（年月日），datetime（日期），timestamp（年月日时分秒）

FROM_UNIXTIME();函数使用

SELECT 
    UNIX_TIMESTAMP(),
    FROM_UNIXTIME(1712820180,'%Y-%m-%d  %H:%i:%s')

注：FROM_TIMESTAMP表示当前时间到1970-1-1 00：00：00的秒数

加密和系统函数

USER()	查询用户
DATABASE()	数据库名称
MD5(str)	为字符串算出一个MD5 32的字符串，(常用来进行用户密码加密)
PASSWORD(str) *select from mysql_user**	从原文密码str计算机返回密码字符串。通常用于对mysql数据库的用户密码加密

实例：

查询当前登录mysql的用户及其IP

SELECT USER() FROM DUAL

查询当前使用的数据库

SELECT DATABASE()

加密函数

SELECT MD5('cc'),LENGTH(MD5('cc')),PASSWORD(MD5('cc'))

流程控制函数

IF(expr1,expr2,expr3)	如果expr1为True，则返回expr2，否则返回expr3
IFNULL（expr1,expr2）	如果expr1不为空NULL，则返回expr1，否则返回expr2
SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5；【类似多重分支】	如果expr1为TRUE，则返回expr2，如果expr3为TRUE，返回expr4，否则返回expr5

实例：

IF函数使用

select if(true,'北京','上海')

查询emp表如果comm为null，则显示0.0

SELECT enmae,comm
	FROM emp

SELECT enmae,IF(comm IS NULL,0.0,comm)
	FROM emp

select enmae,ifnull(comm,0.0)
	from emp

如果emp表的job是CLERK，则显示职员，如果是MANAGER则显示经理，如果是SALESMAN则显示销售人员

SELECT enmae,(SELECT CASE WHEN 
		job = 'CLERK' THEN '职员'
		WHEN job = 'MANAGER' THEN '经理'
		WHEN job = 'SALESMAN' THEN '销售人员' 
		ELSE job END)
	FROM emp

注：上面语句中末尾ELSE job END 一定要加，不然其他记录的显现会默认为NULL

SELECT enmae,(SELECT CASE WHEN 
		job = 'CLERK' THEN '职员'
		WHEN job = 'MANAGER' THEN '经理'
		WHEN job = 'SALESMAN' THEN '销售人员' 
		END)
	FROM emp

查询增强联系

下列内容对emp,salary表进行操作

日期可以进行比较

查找在1992.1.1以后入职的员工

select * from emp
	where hiredate > '1992-01-01'

LIKE模糊查询

%：表示0到多个字符，_：表示单个任意字符

显示以S开头的的员工的信息

SELECT * FROM emp WHERE enmae LIKE 'S%'

查询第三个字符为O的员工的信息

SELECT * FROM emp WHERE ename LIKE'__O%'

查询没有上级的员工的信息（mgr为NULL）

SELECT * FROM emp WHERE mgr IS NULL

注：

判断字段值是否为NULL用IS NULL 或 IS NOT NULL

查询表的结构（DESC）

DESC emp

请将员工记录以deptno，sal进行升序，降序排列

SELECT * FROM emp 
	ORDER BY deptno ASC,sal DESC

分页查询

语法

SELECT ... limit start,rows

表示从start+1行开始取，取出rows行，start从0开始计算【类似于Java数组的下标】

实例：

将empno升序取出，每页显示3条记录

-- 第一页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 0,3
-- 第二页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 3,3
-- 第三页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 6,3

注：

其实本质上就是一个公式：

SELECT ... limit (每页显示的记录数)*(第几页-1),每页显示记录数

多表查询

实例：

查询各个员工的姓名/薪水/部门

分析：

姓名/薪水 enmp表

部门 dept表

在默认情况下，当查询两个表时，规则

1. 从第一张表中，取出一行和第二张的每一行进行组合，返回结果[含有两张表的所有列]

2. 一共返回的记录数第一张表的行数*第二张表的行数

这样多表查询默认处理返回的结果称为笛卡尔集

注：

多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集

实操如下：

SELECT ename,sal,dname FROM emp,dept WHERE emp.deptno = dept.deptno

自连接

如图所示可知在emp表中，emp表所显示的信息不仅仅是员工而且还有员工的上司（mgr），若想在同一个表中显示职员名（ename）及其上司（mgr）就需要用到别名，实操如下

SELECT worker.ename AS '职员名' , boss.ename AS '上级名'
	FROM emp worker ,emp boss
	WHERE worker.mgr = boss.empno

子查询

介绍

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

- 单行子查询

单行子查询是指只返回一行数据的子查询语句

- 多行子查询语句

多行子查询语句指3返回多行的子查询使用关键字in

实例：

单行子查询

查询跟SMITH在同一个部门的员工

SELECT * FROM emp
	WHERE deptno = (
	SELECT deptno
	FROM emp
	WHERE ename = 'SMITH')

多行子查询（返回多行结果使用 in）

查询和部门10的工作相同的雇员的名字/岗位/工资/部门号/但是不含部门自己的雇员

SELECT ename,job,sal,deptno 
	FROM emp
	WHERE job IN (
		SELECT DISTINCT job
		FROM emp
		WHERE deptno = 10) AND deptno != 10

子查询临时表

实例：

查询每个部门的最高薪资的员工的姓名和工作

-- 临时表:查找每个部门的最高薪资
SELECT MAX(sal),deptno FROM emp GROUP BY deptno
SELECT emp.deptno,ename,job,sal 
	FROM (SELECT MAX(sal) AS msal,deptno FROM emp GROUP BY deptno)temp,emp
	WHERE emp.deptno = temp.deptno AND emp.sal = temp.msal

ALL和ANY

实例：

查询出工资比部门30的所有员工的工资高的员工的姓名/工资/部门

SELECT ename,sal,deptno
	FROM emp
	WHERE sal>ALL(
		SELECT sal
			FROM emp
			WHERE deptno = 30)

相同代码：

SELECT ename,sal,deptno
	FROM emp
	WHERE sal>(SELECT MAX(sal)
			FROM emp
			WHERE deptno = 30)

查询比30号部门其中一个员工工资高的员工姓名/工资/部门

SELECT ename,sal,deptno
	FROM emp
	WHERE sal>ALL(
		SELECT sal
			FROM emp
			WHERE deptno = 30)

相同代码：

SELECT ename,sal,deptno
	FROM emp
	WHERE sal>(SELECT MIN(sal)
			FROM emp
			WHERE deptno = 30)

多列子查询

介绍

查询返回多个列数据的子查询语句

实例：

查询与ALLEN在相同部门并且有相同工作的员工的记录，但不含ALLEN

分析1：查询ALLEN的工作和部门

select deptno,job
	from emp
	where ename = 'ALLEN'

分析二：查询与ALLEN在相同部门并且有相同工作的员工，但不含ALLEN

SELECT * FROM emp 
	WHERE (deptno,job) = (
		SELECT deptno,job
		FROM emp
		WHERE ename = 'ALLEN') AND ename !='ALLEN'

表复制和去重

复制

创建t01表

CREATE TABLE t01(
	id INT,
	`name` VARCHAR(32),
	sal DOUBLE,
	job VARCHAR(32),
	deptno INT
);
DESC t01

将emp表的记录复制到t01

INSERT INTO t01
	(id,`name`,sal,job,deptno)
	SELECT empno,ename,sal,job,deptno FROM emp
SELECT * FROM t01

自我复制emp

INSERT INTO emp
	SELECT * FROM emp

去重

将emp表中重复的记录删除

思路：

1. 先创建一个临时表temp，使其表的结构与emp一样

CREATE TABLE temp LIKE emp
DESC temp

2. 先把temp的记录通过distinct关键字处理后，把记录复制到temp

INSERT INTO temp
	SELECT DISTINCT * FROM emp
SELECT * FROM temp

3. 清除掉emp的记录

DELETE FROM emp
SELECT * FROM emp

3.把temp表的记录复制到 emp

INSERT INTO emp 
	SELECT * FROM temp
SELECT * FROM emp

4. 删除temp

DROP TABLE temp

合并查询

介绍

在实际应用中，有时为了合并多个select语句的结果，可以使用集合才做符号

union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行

SELECT ename,sal,job FROM emp WHERE sal > 2500 -- 5
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER' -- 3

SELECT ename,sal,job FROM emp WHERE sal > 2500 -- 5
UNION ALL
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER' -- 3

union

介绍

该操作符与union all 相似，但是会自动去掉结果之中重复行

SELECT ename,sal,job FROM emp WHERE sal > 2500 -- 5
UNION
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER' -- 3

Mysql外连接

emp表

dept表

显示部门名称和这些部门的员工的名称和工作，同时要求显示出那些没有员工的部门

SELECT dname,ename,job FROM emp,dept WHERE emp.deptno = dept.deptno ORDER BY dname

可以发现同时要求显示出那些没有员工的部门这个需求做不到，OPERATIONS部门就显示不出来，这时候就需要用到下面内容的知识

左/右连接

在这里将emp表当作左表，dept表当右表

语法

SELECT .. FROM 表1 LEFT JOIN 表2 ON 条件

现在继续完成上列需求：显示部门名称和这些部门的员工的名称和工作，同时要求显示出那些没有员工的部门

SELECT dname,ename,job 
	FROM dept LEFT JOIN emp
	ON emp.deptno = dept.deptno ORDER BY dname

LEFT JOIN 表示左边的表全显示，即dept中没有与emp表中对上的记录也要显示

SELECT dname,ename,job 
	FROM emp RIGHT JOIN dept 
	ON emp.deptno = dept.deptno ORDER BY dname

如图可知，没有员工的OPERATIONS部门也显示了出来，字段值为空

Mysql约束

基本介绍

约束用于确保数据库的数据满足特定的商业规则，在mysql中，约束包括: not null、unique,primary key,foreign key,和check 五种.

主键（primary key）

语法

字段名 字段类型 primary key

细节说明：

1. primary key不能重复而且不能为nuIl

2. 一张表最多只能有一个主键，但可以是复合主键

CREATE TABLE ttt(
	id INT,
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY(id,`name`)
);

注：

此时只有当id，name同时相同才报错

3. 主键的指定方式有两种：

- 直接在字段名后指定:字段名 primakry key

- 在表定义最后写 primary key(列名);

4. 使用desc 表名，可以看到primary key的情况

not bull（非空）

当列定义为not null时，插入数据时必须为列提供数据

语法：

字段名字段类型 not null

unique（唯一）

当定义了唯一约束后，该列是不能重复的

语法

字段名字段类型 unique

1. 如果没有指定 notnu11，则unigue 字段可以有多个nu11

2. 一张表可以有多个umique学段

foreign（外键）

介绍

用于定义主表和从表之间的关系:

外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.,当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

语法

FOREIGN(本表字段名)REFERENCES
主表名(主键名或unique字段名)

实例：

创建学生表和班级表

学生表（从表）	班级表（主表）
id name class_id	id class_name

CREATE TABLE class(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT ''
);
CREATE TABLE stu(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	class_id INT, -- 外键
	-- 确定外键关系
	FOREIGN KEY (class_id) REFERENCES class(id)
);

INSERT INTO class
	VALUES(100,'JAVA'),(200,'WEB')
INSERT INTO stu
	VALUES(1,'tom',100),(2,'jack',200)
INSERT INTO stu
	VALUES(3,'hsp',300)

如图可知，当class表中主键id没有300这条记录时，在从表外键class_id输入300会进行报错，原因是class表中主键id没有300这条记录与其及进行链接

使用细节

1. 外键指向的表的字段，要求是primary key 或者是unique
2. 表的类型是innodb,这样的表才支持外键

3. 外键字段的类型要和主键字段的类型一致(长度可以不同)

4. 外键字段的值，必须在主键字段中出现过，或者为nuIl [前提是外键字段允许为nuI]

5. 一旦建立主外键的关系，数据不能随意删除了

check

介绍

用于强制行数据必须满足的条件

实例：

创建t33表：

假定在sal列上定义了check约束,并要求sal列值在1000~2000之间，如果不再1000~2000之间就会提示出错。

create table t23(
	id int primary key,
	`name` varchar(32),
	sex varchar(6) check (sex in('man','woman')),
	sal double check (sal > 1000 and sal <2000)
);

由于这里使用的是mysql5.7版本，只对check进行语法校验，但并不生效，所以不做结果展示

Mysql约束实践

商店售货系统表设计案例

现有一个商店的数据库shop_db，记录客户及其购物情况，由下面三个表组成:

商品goods(商品号goods_id，商品名goods_name，单价unitprice，商品类别category，供应商provider);

客户customer(客户号customer id,姓名name,住址address,电邮email性别sex,身份证card ld);购买purchase(购买订单号order_id，客户号customer _id,商品号goods id,购买数：nums);
1建表，在定义中要求声明[进行合理设计]:

(1)每个表的主外键:
(2)客户的姓名不能为空值;
(3)电邮不能够重复;
(4)客户的性别[男|女]check 枚举.

(5)单价unitprice在1.0-9999.99 之间 check

create table goods(
	goods_id int primary key,
	goods_name varchar(10) not null default ' ',
	unitprice decimal(10,2) not null default 0
		check (unitprice >=1.0 and unitprice <= 9999.99),
	category varchar(10),
	provider varcahr(10)
);
create table customer(
	customer_id int primary key,
	`name` varchar(10),
	address varchar(20),
	email varchar(20),
	sex enum('男','女') not null,
	card_id varchar(20)
);
create table purchase(
	order_id varchar(20),
	customer_id int,
	goods_id int,
	nums int,
	foreign key (customer_id) references customer(customer_id),
	foreign key (goods_id) references goods(goods_id)
);

Mysql自增长

语法

字段名 整形 PRIMARY KEY AUTO_INCREMENT

实例

CREATE TABLE t24(
	id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT ' ',
	`name` VARCHAR(32) NOT NULL DEFAULT ' '
);
INSERT INTO t24
	VALUES(NULL,'mike@qq.com','mike')
SELECT * FROM t24

INSERT INTO t24
	VALUES(NULL,'mike@qq.com','mike')
SELECT * FROM t24
INSERT INTO t24
	VALUES(NULL,'jack@qq.com','jack')
INSERT INTO t24
	VALUES(4,'lili@qq.com','lili')
INSERT INTO t24
	VALUES(NULL,'mon@qq.com','mon')
INSERT INTO t24(email,`name`)
	VALUES('jack@qq.com','jack')

如上面所示代码可知

1. 当AUTO_INCREMENT字段插入值为NULL时，系统会自行录入

2. 若输入的值不是NULL也是可以的，但在下次输入值为NULL时，系统会根据上条记录的值进行自增长

3. 输入时可以指输入其他字段的值，系统不会报错，不再id字段进行自增长

自增长使用细节

1. 一般来说自增长是和primary key 配合使用的

2. 自增长也可以单独使用[但是需要配合一个unique]（id INT UNIQUE AUTO_INSERMENT）

3. 自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)
4. 自增长默认从 1开始，你也可以通过如下命令修改 alter table表名auto increment = xxx;

5. 如果你添加数据时，给自增长字段(列)指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来

Mysql索引

索引原理

索引其实就是对表中所有的记录进行相应的编号，并用二叉树算法进行排序，一边更加快速地查询数据，在没有创建索引之前，对数据的查询是对表的每个记录进行逐条对比来找到符合的记录，即使查询的数据比较靠前（哪怕是第一条），也会向后进行查询，直到整张表遍历完为止

实例：

如图所示创建一条具有八百万条记录的表emp

在没有创建索引前emp这张表总共有524mb

现在进行查询操作

SELECT * 
	FROM emp
	WHERE ename = 'axJxCTs'

如图可知，查询时长为3.7s

现在在emp的empno字段上创建索引

create index empno_index on emp(empno)

如图可知，现在的文件为655mb，索引也占用了一定的空间

现在进行查询操作

SELECT * 
	FROM emp
	WHERE ename = 'axJxCTs'

如图可知通过索引使查询速度提升（0.001s）

索引的类型

1. 主键索引，主键自动的为主索引(类型Primary key)

2. 唯一索引(UNIQUE)

3. 普通索引(INDEX)

4. 全文索引(FULLTEXT)[适用于MyISAM]

索引使用

1. 添加索引(建小表测试 id，name)

create [UNIQUE] index index_name on tbl_name (col_ name [(length)][ASC | DESC],......);


alter table table_name ADD INDEX [index name] (index_col_name,....)

2. 添加主键(索引)

ALTER TABLE 表名 ADD PRIMARY KEY(列名...);

3. 删除索引

DROP INDEX index_name ON tbl_name

alter table table_name drop index index_name;

4. 删除主键索引比较特别

 alter table tbl_name drop primary key;

实例：

创建表

create table t(
	id int,
	`name` varchar(32)
);

添加唯一索引

CREATE UNIQUE INDEX id_index ON t(id)

查询表是否有索引

show indexes from t

如图所示：表中Non_unique字段值若为0，则代表被添加的字段索引为唯一索引，若为1，则为普通索引

创建表

CREATE TABLE tt(
	id INT,
	`name` VARCHAR(32)
);

添加主键索引

alter table tt add primary key(id)
SHOW INDEXES FROM tt

删除索引

drop index id_index on t
show indexes from t

删除主键索引

ALTER TABLE tt DROP PRIMARY KEY

查询索引

方式一：

show indexes from table_name

方式二：

show index from table_name

方式三：

show keys from table_name

SHOW KEYS FROM t
SHOW KEYS FROM tt

方式四：

desc table_name

DESC t
DESC tt

小结: 哪些列上适合使用索引

1. 较频繁的作为查询条件字段应该创建索引
select *from emp where empno =1

2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
select * from emp where sex='男

3. 更新非常频繁的字段不适合创建索引

select *from emp where logincount = 1

4. 不会出现在WHERE子句中字段不该创建索引

Mysql事务

介绍

事务用于保证数据的一致性,它由一组相关的dml语句组成,该组的dml语句要么全部成功，要么全部失败。如:转账就要用事务来处理，用以保证数据的一致性。

事务和锁

当执行事务操作时(dml语句),mysql会在表上加锁,防止其它用户改表的数据这对用户来讲是非常重要的
mysql 数据库控制台事务的几个重要操作
1. start transaction-开始一个事务
2. savepoint 保存点名-设置保存点
3. rollback to 保存点名--回退事务
4. rollback-回退全部事务
5. commit-提交事务,所有的操作生效,不能回退

细节：

1. 没有设置保存点

2. 多个保存点

3. 存储引擎

4. 开始事务方式

实例：

创建表

CREATE TABLE t27(
	id INT,
	`name` VARCHAR(32)
);

节点1：

-- 开始事务
START TRANSACTION
-- 设置保存点
SAVEPOINT a
-- 执行dml操作
INSERT INTO t27 VALUES(100,'tom');

查询插入数据

节点2：

-- 设置保存点b
savepoint b
select * from t27
-- 执行dml操作
insert into t27 values(200,'jack');

查询插入数据

回退节点a

ROLLBACK TO b
SELECT * FROM t27

注：

若直接写rollback，则直接回退到事务开始的状态（start transaction）

回退事务
在介绍回退事务前，先介绍一下保存点(savepoint).保存点是事务中的点.用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点.当执行回退事务时，通过指定保存点可以回退到指定的点

提交事务

使用commit语句可以提交事务.当执行了commit语句子后,会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话将可以查看到事务变化后的新数据【所有数据正式生效】

作图说明

注：

如同上面时间轴所示，若直接回滚到保存点a，保存点b以后的相关操作也会被撤销

事务细节讨论

1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚

2. 如果开始一个事务，你没有创建保存点.你可以执行rollback，默认就是回退到你事务开始的状态

3. 你也可以在这个事务中(还没有提交时)，创建多个保存点

4. 你可以在事务没有提交前，选择回退到哪个保存点

5. mysql的事务机制需要innodb的存储引擎,myisam不支持.

6. 开始一个事务start transaction,set autocommit=off;

Mysql事务隔离级别

事务隔离级别介绍
1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。

2. 如果不考虑隔离性，可能会引发如下问题:

- 脏读：当一个事务读取另一个事务尚未提交的修改时，产生脏读
- 不可重复读：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
- 幻读：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

Mysql隔离级别	脏读	不可重复读	幻读	加锁读
读未提交(Read uncommitted)	✓	✓	✓	不加锁
读已提交(Read committed)	✕	✓	✓	不加锁
可重复读(Repeatable read)	✕	✕	✕	不加锁
可串行化(Serializable)	✕	✕	✕	加锁

设置隔离相关操作

1. 查看当前会话隔离级别

select @@tx_isolation;

2. 查看系统当前隔离级别

select @@global.tx_isolation;

3. 设置当前会话隔离级别

set session transaction isolation level repeatable read;

4. 设置系统当前隔离级别

set global transaction isolation level repeatable read;

5. mysql默认的事务隔离级别是 repeatable read,一般情况下，没有特殊要求，没有必要修改(因为该级别可以满足绝大部分项目需求)

全局修改，修改my.ini配置文件，在最后加上
#可选参数有:READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ, SERIALIZABLE.
transaction-isolation =REPEATABLE-READ

事务的acid特性

1. 原子性(Atomicity)

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2. 一致性(Consistency)

事务必须使数据库从一个二致性状态变换到另外一个一致性状态
3. 隔离性(lsolation)

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
4. 持久性(Durability

)持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的接下来即使数据库发生故障也不应该对具有任何影响

实例1：

现在我们运用dos命令模拟两个控制台对数据库进行插入操作

查询当前的隔离级别

SELECT @@tx_isolation;

把控制台二的隔离级别设置为Read uncommitted

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

将两个表都启动事务

START TRANSACTION

控制台一创建表

CREATE TABLE `account`(
	id INT,
	`name` VARCHAR(32),
	money INT
);

控制台一插入数据

现在在控制台二查询该表的数据

此时可发现，控制台一并没有进行提交（commit），在控制台二确能读到数据，这就是脏读

在控制台一修改刚刚插入的记录

现在在控制台二查询该表的数据

由此可知，控制台一进行修改或删除操作，控制台二每次返回不同的结果集，此时发生不可重复读。

在控制台一再次插入一条数据

现在在控制台二查询该表的数据

由此可知，控制台一在同一事务中多次进行插入操作，控制台二每次返回不同的结果集，此时发生幻读。

实例2：

现在对两个控制台都进行提交并再次开始事务

将控制台二的隔离级别改为Read committed

向表一插入数据

现在对两个控制台进行进行查询操作

控制台一：

控制台二：

向控制台一设置保存点a并插入数据

此时对控制台二进行查询

由图可知，并没有发生幻读，由此就可以发现，幻读与脏读的区别就是读的数据是否提交（commit）上面一个之所以说出现幻读是因为原先控制台二的隔离级别为（read uncommitted），由于控制台一进行的多次插入操作，使控制台二出现了多次脏读，从而出现了幻读

实例3：

现在将控制台二的隔离级别改为repeatable read

在控制台一进行插入和修改操作

查询控制台二的数据

可发现控制台二并没有发生脏读和不可重复读现象

现在在控制台一上再次插入数据并进行提交

现在再对控制台二进行查询

由此可知，并没有发生幻读现象

实例4：

现在将控制台二设置为serializable（可串行化）

现在对控制台一插入数据

在控制台二上进行查询

此时可以看见并没有跳出account表的数据，原因就在隔离级别serializable给自己的控制台上了锁，意思就是其他控制台还在进行表的修改并且还没有提交，因此不跳出数据

Mysql表类型和存储引擎

基本介绍

1. MySQL的表类型由存储引擎(Storage Engines)决定，主要包括MyISAM、innoDB、Memory等。

2. MySQL数据表主要支持六种类型，分别是:CSV、Memory、ARCHIVE、MRG MYISAM、MYISAM、InnoBDB.

3. 这六种又分为两类，一类是”事务安全型”(transaction-safe)，比如:InnoDB:其余都属于第二类，称为”非事务安全型”(non-transaction-safe)[mysiam和memory].

查看存储引擎

主要的存储引擎/表特点

特点	Myisam	InnoDB	Memory	Archive
批量插入的难度	高	低	高	非常高
事务安全		支持
全文索引	支持
锁机制	表锁	行锁	表锁	行锁
储存限制	没有	64TB	有	没有
树索引	支持	支持	支持	没有
哈希案引		支持	支持
集群索引		支持
数据缓存		支持	支持
索引缓存	支持	支持	支持
数据可压缩	支持			支持
空间使用	低	高	N/A	非常低
内存使用	低	高	中等	低
支持外键		支持

细节说明

1.MyISAM不支持事务、也不支持外键，但其访问速度快，对事务整性没有要求
2InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
3.MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉，表的结构还在。

实例1：

创建储存引擎为Myisam的表

create table t28(
	id int,
	`name` varchar(32)
)engine myisam

开启事务并插入数据

start transaction
insert into t28 
	values(1,'jack')

查询表

SELECT * FROM t28

现在进行回滚

此时可以看见，系统报错，原因使用myisqm储存引擎创建的表不支持事务安全

实例2：

创建储存引擎为memory的表

CREATE TABLE t29(
	id INT,
	`name` VARCHAR(32)
)ENGINE Memory

插入数据

INSERT INTO t29
	VALUES(1,'jack')

此时关闭mysql服务并再次开启

再次查询数据和表结构

此时可以发现插入的数据没了，但创建的表及其表结构都还在

如何选择表的存储引擎

1. 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择,速度快
2. 如果需要支持事务，选择InnoDB.
3. Memory 存储引擎就是将数据存储在内存中，由于没有磁盘I/O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法用户的在线状态().)

修改存储引擎

语法

ALTER TABLE '表名' ENGINE = 储存引擎;

Mysql视图管理

- 基本概念

1.视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列,其数据来自对应的真实表(基表)
2.视图和基表关系的示意图

视图基本使用

create view 视图名 as select语句
alter view 视图名 as select语句
SHOW CREATE VIEW 视图名
drop view 视图名1,视图名2

实例：

CREATE VIEW empview AS SELECT empno,ename,job,comm FROM emp
SELECT * FROM empview

修改视图,对基表都有变化

update empview set comm = 200 where empno = 7369
select * from emp

修改基表，对视图也有变化

UPDATE emp SET comm = 100 WHERE empno = 7369

视图细节讨论

1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式:视图名.frm)

2. 视图的数据变化会影响到基表，甚表的数据变化也会影响到视图[insert update delete]

----针对前面的雇员管理系统-----

CREATE VIEW empview AS SELECT empno,ename,job,comm FROM emp
SELECT * FROM empview
UPDATE empview SET comm = 200 WHERE empno = 7369
SELECT * FROM emp
UPDATE emp SET comm = 100 WHERE empno = 7369

视图最佳实践

1. 安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
2. 性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
3. 灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

Mysql用户管理

mysql中的用户，都存储在系统数据库mysql中user表中

其中user表的重要字段说明:

1. host:允许登录的“位置”，localhost表示该用户只允许本机登录，也1.host:可以指定ip地址，比如:192.168.1.100

2. user:用户名;

3. authentication_string:密码，是通过mysql的password0函数加密之后的密码。

查看用户

select user from mysql.user

创建用户

create user '用户名' @' 允许登录位置’ identified by '密码'

说明:创建用户，同时指定略马

删除用户

drop user '用户名' @' 允许登录位置'

用户修改密码

修改自己的密码:
set password = password('密码');

修改他人的密码(需要有修改用户路码权限):
set password for '用户名'@"登录位置’ = password('密码');

实例：

创建新用户

create user 'cc'@'localhost' identified by '123456'

查看用户权限

show grants for '用户'@'localhost'

登陆新用户

此时可发现用户cc所能看到的表与拥有最高权限（root）的xc的表不同

不同的数据库用户，登录到DBMS后，根据相应的权限，可以操作的数据库和数据对象(表,视图，触发器)都不一样

MySQL权限管理

权限	意义
ALL (PRIVILEGESI	设置除GRANT OPTION之外的所有简单权限
ALTER	允许使用ALTER TABLE
ALTER ROUTINE	更改或取消已存储的子程序
CREATE	允许使用CREATE TABLE
CREATE ROUTINE	创建已存储的子程序
CREATE TEMPORARYTABLES	允许使用CREATE TEMPORARY TABLE
CREATE USER	允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW	允许使用CREATE VIEW
DELETE	允许使用DELETE
DROP	允许使用DROP TABLE
EXECUTE	允许用户运行已存储的子程序
FILE	允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX	允许使用CREATE INDEX和DROP INDEX
INSERT	允许使用INSERT
LOCK TABLES	允许对您拥有SELECI权限的表使 LOCKTABLES
PROCESS	允许使用SHOW FULL PROCESSLIST
REFERENCES	未被实施
RELOAD	允许使用FLUSH
REPLICATIONCLIENT	允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE	用于复制型从属服务器(从主服务器中读取二进制日志事件)
SELECT	允许使用SELECT
SHOW DATABASES	SHOW DATABASES显示所有数据库
SHOW VIEW	允许使用SHOW CREATE VIEW
SHUTDOWN	允许使用mysqladmin shutdown
SUPER	KILL,PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令;允许您连接(一次)，即使已达到max_connections
UPDATE	允许使用UPDATE
USAGE	“无权限”的同义词
GRANT OPTION	允许授予权限

给用户授权

基本语法:
grant 权限列表 on 库.对象名 to '用户名’@’登录位置‘【identified by'密码’】

说明:
1. 权限列表，多个权限用运号分开
grant select on.......

grant select, delete, create on ....

grant all 【privileges】on ...//表示赋予该用户在该对象上的所有权限

2. 特别说明
* . *:代表本系统中的所有数据库的所有对象(表，视图，存储过程)

库.*:表示某个数据库中的所有数据对象(表，视图，存储过程等)

3. identified by可以省略，也可以写出.
(1)如果用户存在，就是修改该用户的密码

(2)如果该用户不存在，就是创建该用户!

回收用户授权

基本语法:
revoke 权限列表 on库对象名 from '用户名'@'登陆位置'

权限生效指令

如果权限没有生效，可以执行下面命令基本语法:

FLUSH PRIVILEGES;

实例1：

创建数据库test1，并创建表news

create table news(
	id int,
	`name` varchar(32)
);
insert into news
	VALUES(1,'JACK')
SELECT * FROM news

给用户cc参考test1表和select/insert权限

grant select,insert
	ON test1.news
	to 'cc'@'localhost'

select * from news
insert into news 
	VALUES(2,'MIKE')

如图所示，用户cc可以看到test1数据库和news表，并且可以进行select/insert相关操作

实例2：

现在进行数据更新操作

update news set `name` = 'lili'
	where id = 200

如图所示可以看出，用户cc只能进行被授权的操作（select/insert）

此时在xc用户的视角下观察news表

由图可知，news的数据也进行了改动

修改用户cc的密码为abc

SET PASSWORD FOR 'cc'@'localhost' = PASSWORD('abc')

回收cc用户在test1.news表的全部权限

revoke all on test1.news from 'cc'@'localhost'

或

revoke select,insert,update on test1.news from 'cc'@'localhost'

此时再次刷新cc用户的视角

可以发现，cc用户已经没有对test1.cc的相关权限

将授予权限给用户

grant [privilege] on database_name.table to 'user_name'@'登陆地址'

细节说明

1. 如果不指定Host,则为%，%表示表示所有IP都有连接权限在创建用户的时候，create user xxX

creat user '用户'

2. 你也可以这样指定

create user 'xxx'@'192.168.1.%'表示 xxx用户在 192.168.1.*的ip可以登录mysql

3. 在删除用户的时候，如果 host 不是 %,需要明确指定用户'@'host值'

思考的橙子

关注

25
点赞
踩
15

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录