MySQL

最新推荐文章于 2024-10-18 10:41:41 发布

一介书生.

最新推荐文章于 2024-10-18 10:41:41 发布

阅读量1.1k

点赞数

文章标签： mysql

本文链接：https://blog.youkuaiyun.com/weixin_51617439/article/details/124430281

版权

第一章、数据库操作

1、系统数库

information:虚拟库，存放数据库启动后的一些参数
performance_schema:存放数据库服务性能参数
mysql：授权库，存放系统用户的权限信息
test：MySQL数据库系统自动创建的测试数据库

2、MySQL的存储引擎

innodb： menmory： blackhole： myisam：

查看MySQL的存储引擎

show engine;

1、创建数据库，并且指定字符集，并指定校队规则

CREATE  DATABASE db02  CHARACTER SET  utf8  COLLATE  utf8_general_ci;

--参数：CREATE 创建数据库，CHARACTER SET 设置字符编码，COLLATE 校验规则。

校对规则：
默认是utf8_general_ci：不区分大小写
utf8_bin：区分大小写

2、删除数据库

DELETE DATABASE db01;

DROP  DATABASE  DB02;

3、查看数据库

SHOW  DATABASES; --查看全部的数据库

SELECT DATABASES(); --查看当前所在数据库

查看数据库的定义信息

SHOW  CREATE DATABASE 'db02';

4、进入数据库（使用数据库）

USE  DATABASE  db01;

5、修改数据库信息：使用 ALTER DATABASE 来修改已经被创建或者存在的数据库的相关参数。

ALTER  DATABASE [数据库名] {
    DEFAULT  CHARACTER  SET <字符集名>
    DEFAULT  COLLATE <校对规则名>
}

mysql> ALTER  DATABASE  db01
    -> DEFAULT  CHARACTER SET gb2312
    -> DEFAULT  COLLATE gb2312_chinese_ci;

6、备份和恢复数据库

备份

mysqldump -u root -p123456 -B db02  > d:\\db02.sql

--参数：mysqldump 备份 -u 用户名 -p 密码 -B 数据库名称 > 文件存储路径

恢复

source  d:\\bak.sql

--参数：source 恢复数据库 文件存储路径

第二章、字段类型

整数类型：

关键字	类型	长度
BIT	位型	1
BOOL	布尔型	1
TINY INT		1
SMALL INT		2
MEDIUM INT		3
INT	整型	4
BIG INT	长整型	8

浮点数类型：

关键字	类型	长度
FLOAT	浮点型	4
DOUBLE	浮点型	8
DECIMAL		M+1

字符串类型：

关键字	类型	长度
CHAR	字符型	255(固定长度)
VARCHAR	长字符串	255(固定长度)
TINY TEXT	小文本串	255(可变长度)
TEXT	文本型	(可变长度)
MEDIUM TEXT		(可变长度)
LONGTEXT		(可变长度)
TINY BLOB	小二进制型
BLOB	二进制型
MEDIUM BLOB
LONG BLOB

字符类型使用细节：

char按照字符存放，varchar按照表的编码存放
char(4)类型是固定长度，即使插入一个字符，也是分配4个字符空间，可变长度是使用多少空间分配多少空间。
数据定长使用char，不确定长度使用varchar。查询速度:char > varchar
TEXT文本类型没有默认值。

日期类型：

关键字	格式
Date	YYYY-MM-DD	2022-02-18
DateTime	YYY-MM-DD HH:MM:SS	2022-02-18 12:30:20
TimeStamp	YYYY-MM-DD	2022-02-18
Time	HH:MM:SS	12:30:20
Year	YYYY	2022

使用细节：TimeStamp在Insert和update时，自动更新。

其他数据类型：

BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

关键字

MySQL关键字	含义
NULL	可以为null
NOT NULL	不能为null
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自增长
CHARACTER SET	字符集
UNSIGNED	无符号

数据类型的使用

create  table  tb01(id int(11)); //默认是有符号的

create  table  tb01(id int(11) unsigned); //无符号的

第三章、表操作

1、创建表

创建一张表，设置id字段（INT类型，长度11，不能为空，自动增长）、name字段（VARCHAR类型，长度255，不能为空）、age字段（INT类型，长度3，不能为空），设置id为主键，采用InnoDB引擎，并且指定字符集utf8。

CREATE  TABLE  tb02 (
        id INT(11)  NOT  NULL  AUTO_INCREMENT=1,
        name  VARCHAR(255)  NOT NULL,
        age  INT(3)  NOT  NULL,
        PRIMARY  KEY  (id))  
        ENGINE=InnoDB  DEFAULT CHARSET=utf8;

#参数：CREATE TABLE 创建表，AUTO_INCREMENT=1,从1开始自增长，NOT NULL不能为空，PRIMARY KEY 设置主键，ENGINE=InnoDB引擎，DEFAULT CHARSET=utf8设置字符集。

2、修改表

添加表中的字段

ALTER  TABLE  tb02  ADD ( sex  CHAR(1)  NOT NULL);

修改列

ALTER  TABLE  表名  CHANGE  列名  新列名  新列名类型 NOT NULL DEFAULT '';

ALTER  TABLE  表名  MODIFY  列名  列的新类型  NOT  NULL DEFAULT 默认值;

删除列

ALTER  TABLE  tb02  DROP  age;

修改表名

ALTER TABLE 表名 RENAME 新表名;

修改表名：

RENAME  TABLE 表名  TO  新表名;

修改列名：

ALTER  TABLE  表名  CHANGE  列名  新列名  列类型;

修改表字符集：

alter  表名  character  set  字符集;

3、查看数据库中的表

show  tables;

查询列

SELECT * FROM tb02;

SHOW CREATE TABLE t1;

SHOW TABLES;

DESC user; //查询全部字段

4、删除表:

DROP  TABLE  表名;

清空表

truncate  表名;

练习：

创建表，创建一个员工表emp(课堂练习),

CREATE TABLE tb03 (
	id INT(11) NOT NULL AUTO_INCREMENT=1,
    name VARCHAR(255) NOT NULL,
    sex CHAR(1) NOT NULL,
    brithday Date NOT NULL,
    entry_date Date NOT NULL,
    job VARCHAR(255) NOT NULL,
    Salary DOUBLE NOT NULL,
    resume TEXT NOT NULL,
	PRIMARY KEY (id)) 
	ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


员工表emp的上增加一个image列，varchar类型(要求在resume后面)。

ALTER TABLE tb02 ADD COLUMN image varchar(255) NOT NULL;
修改job列，使其长度为60。

ALTER TABLE tb02 MODIFY job VARCHAR(60) NOT NULL;
删除sex列。

ALTER TABLE tb02 DROP sex;
表名改为employee

Rename tb02 to employee;
修改表的字符集为utf8

ALTER employee character set utf8;
列名name修改为user_name

ALTER TABLE employee change name user_name VARCHAR(255);

第四章、字段操作

运算符

名称	符号-关键字	说明
算术运算符	+、-、*、/、%	加、减、乘、除、取余
比较运算符	>、<、<=、>=、=、<>、!=	大于、小于、小于等于、大于等于、等于、不等于
	BETWEEN AND	显示在某一区间的值
	not between	不在区间内
	IN(set)	显示在in列表中的值，例如（100，200）
	LINK '张%'	模糊查询 % 表示0到多个字符，_ 表示单个字符
	NOT LINK '张%'	模糊查询
	IS NULL	判断是否为空
	is not null	不空字段
逻辑运算符	and	多个条件同时成立
	or	多个条件任一成立
	not	条件不成立

添加数据 -Insert语句

格式：

INSERT INTO 表名 (字段1, 字段2, 字段3) VALUES (值1, 值2, 值3);

实例

INSERT INTO tb01 VALUES (1, '张三', 20);

INSERT INTO tb01 (id, name, age, sex) VALUES (1, '张三', 19),(2, '李四', 21),(3, '王五', 18),(1, '马六', 19);

使用细节：

插入数据的类型要和字段类型相同。
插入数据的长度要在规定范围内。
字符和日期类型的数据要使用''包含。
可以一次添加多条数据。
如果是给表中的所有字段添加数据，可以不写前面的字段名称

修改数据 -Update语句

格式：

UPDATE 表名 SET 字段=新值 WHERE id=1;

实例

UPDATE tb01 SET name='吴用' WHERE id=1; //修改单行

UPDATE tb01 SET age = 0; //修改该列所有数据

使用细节：

where 是指定修改的行，不使用where，会将name字段全部改成'吴用'。
set 是指定修改的列。
使用逗号分隔可以修改多行。

删除数据-Delete语句

格式：

DELETE FROM 表名 WHERE id=1;

实例

DELETE FROM ta01 WHERE id=1; //删除单个字段

DELETE FROM age; //删除该列所有数据

使用细节：

where 指定删除的上，不使用where，会将删除全部的行。
delete 不能删除某一列的值。
delete不能删除表，drop 可以删除表。

查询数据-Select语句

格式

SELECT DISTINCT *|[column1, column2...] FROM tb01;

-- Select指定查询哪些列的数据。DISTINCT可选，指显示结果时，是否去掉重复数据。*号代表查询所有列。column指定列名。From指定查询哪张表。

1、在select语句中使用as语句

SELECT COLUMN name AS 别名 FROM 表名。

查询—基础

1、单表查询

格式:
SELECT distinct 字段1 FROM 表名 WHERE 条件;

#select 查询列，distinct 去重，column 指定列名，from 指定查询的表，where 设置查询的条件

查询全部

select * from tb01;

查询指定字段

select id, name, age from tb01;


避免重复 distinct

select distinct name from tb01;


四则运算查询

select age*2 from tb01;


给查询出来的字段取别名 as

select age*2 as age1 from tb01;


显示格式 

// concat()函数 用于连接字符串

select concat("姓名：",name, "年龄：",age) as info from tb01;

// concat_ws() 第一个参数为分隔符

select concat_ws("：",name,age) as info from tb01;

2、使用where 子句，条件过滤查询。

格式:

select 字段1 from 表名 where 条件;


1）单条件查询

select * from tb01 where id<18;


2）多条件查询

select name,age from tb01 where id>5 and age >18;
// 指定年龄大于等于18小于等于30的字段，between 区间
select name,age from tb01 where age between 18 and 30;


3）判断字段的空与不空

select * from tb01 where age is null;
select * from tb01 where age is not null;


4）模糊匹配

select * from tb01 where name like "张%";
select * from tb01 where name like "张__";

3、使用 group by子句，分组查询。

分组是按照相同字段进行分类，不分组默认整体是一个大组，也能使用聚合函数。

格式:
SELECT name, age FROM tb01 group by age;

按照相同字段进行分类

select * from tb01 group by age;


只能取分组的字段

// set global sql_mode="ONLY_FULL_GROUP_BY";
# 取出t1表中年龄小于18的年龄个数，按年龄进行分组
select age,count(age) from tb01 where age <18 group by age;

# group_concat() 每个组字段的全部，取出各个年龄的人的名字
select age,group_concat(name) from tb01 group by age;

4、使用 having 子句，对分组后的结果进行过滤。

格式:
SELECT name, age FROM tb01 group by age having age<18;

对group by分组的进一步过滤
# 取出年龄小于18的各个年龄阶层的人的名字、个数

select age,group_concat(name),count(id) from tb01 group by age having age <18;

5、使用 order by子句，排序查询。

格式:
SELECT 字段1,字段2 FROM 表名 ORDER BY 排序字段 DESC;
# ORDER BY 指定排序的字段和模式，ASC(默认)升序，DESC降序。

# 按年龄降序排序

select * from t1 order by age desc;

# 先按年龄降序排序，年龄相同按id升序排序

select * from t1 order by age desc, id asc;

6、使用 limit 子句，限制条数查询。

格式：
SELECT 字段1 FROM 表名 limit 1,5;

# 取出表中前五的字段

select * from t1 limit 5;

# 取出表中年龄最大的前五个的字段

select * from t1 order by age desc limit 5;

7、正则查询 regexp

# 匹配全部姓张的人

select * from t1 where name regexp "^张"

8、数据分组的总结

如果select语句同时包含有group by ,having , limit, order by那么他们的顺序是group by, having, order by, limit

语法顺序：
    select distinct 字段1,字段2 from 库.表
    表1 联表类型 join 表2 on 联表条件 
    where 条件
    group by 分组条件
    having 过滤条件
    order by 排序条件
    limit n;

第五章、mysql约束

1、基本介绍

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

primary key (主键)：用于唯一的标识，当定义主键约束后，该列不能重复和为空。

id INT, primary key(id)（单表主键）
ip INT, port INT, PRIMARY KEY(ip,port)（联合主键）

primary key (主键) 细节说明：

primary key不能重复而且不能为null。
一张表最多只能有一个主键,但可以是复合主键
主键的指定方式有两种：直接在字段名后指定:字段名primakry key，在表定义最后写primary key(列名)
4.使用desc表名，可以看到primary key的情况.

not null (非空)：当前列的数据不能为空

id INT not null

unique (唯一)：当定义了唯一约束后，该列值是不能重复的

id INT unique （单列唯一）
ip INT, port INT, UNIQUE(ip,port) （联合唯一）

unique细节(注意):

1.如果没有指定not null，则unique字段可以有多个null

2.一张表可以有多个unique字段。

foreign key (外键)

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。

foreign key：外键约束，用于限制两个表的关系，保证从表该字段的值来自于主表相关联的字段的值

FOREIGN KEY(本表字段名) REFERENCES 生表名(主键名或unique字段名)
FOREIGN KEY(id) REFERENCES teacher(id)   
# 插入数据：先往被关联表插入记录，在往关联表插入记录

auto_increment (自增长)

id int auto_increment,

自增长使用细节：

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

2.自增长也可以单独使用[但是需要配合一个unique]

3.自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)

4.自增长默认从1开始，你也可以通过如下命令修改altertable表名auto_increment=新的开始值;

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

default (默认值)

name VARCHAR(10) default '';

如果我们不设置默认值，系统默认为NULL。

check

用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000 ~2000之间如果不再1000 ~2000之间就会提示出错。

提示:oracle和sql server均支持check ,但是mysql5.7目前还不支持check ,只做语法校验，但不会生效。

第六章、mysql函数

统计函数：

函数
count()	返回行的总数，但是会排除字段为 null 的情况。
group_concat()	每个组字段的全部

实例

SELECT count(*) FROM tb01;

SELECT count(age) FROM tb01 WHERE age<18;

数学函数

函数名	说明
sum()	求和函数：返回相加的和
avg()	返回平均数：求平均数函数
max()	最大值函数：返回一列的最大值
min()	最小值函数：返回一列的最小值

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

常用函数实例

求和函数：返回相加的和
SELECT sum(math) FROM student;
返回平均数：求平均数函数
SELECT avg(math) FROM student;
最大值函数：返回一列的最大值
SELECT max(age) FROM tb01;
最小值函数：返回一列的最小值
SELECT min(age) FROM tb01;

字符串函数

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

练习：

-- CHARSET(str) 返回字串字符集 
SELECT CHARSET(ename) FROM emp;

 -- CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列 
SELECT CONCAT(ename, ' 工作是 ', job) FROM emp;


-- INSTR (string ,substring ) 返回 substring 在 string 中出现的位置,没有返回 0 
-- dual 亚元表, 系统表 可以作为测试表使用
 SELECT INSTR('hanshunping', 'ping') FROM DUAL;
 

-- UCASE (string2 ) 转换成大写 
SELECT UCASE(ename) FROM emp;

-- LCASE (string2 ) 转换成小写
SELECT LCASE(ename) FROM emp;


-- LEFT (string2, length ) 从 string2 中的左边起取 length 个字符 
-- RIGHT (string2, length ) 从 string2 中的右边起取 length 个字符 
SELECT LEFT(ename, 2) FROM emp;

 

-- LENGTH (string ) string 长度[按照字节] 
SELECT LENGTH(ename) FROM emp;


-- REPLACE (str ,search_str ,replace_str ) 替换字符
-- 在 str 中用 replace_str 替换 search_str
-- 如果是manager 就替换成 经理
SELECT ename, REPLACE(job,'MANAGER', '经理') FROM emp;


-- STRCMP (string1 ,string2 ) 逐字符比较两字串大小 
SELECT STRCMP('hsp', 'hsp') FROM DUAL; 


-- SUBSTRING (str , position [,length ]) 
-- 从 str 的 position 开始【从 1 开始计算】,取 length 个字符
-- 从 ename 列的第一个位置开始取出 2 个字符 
SELECT SUBSTRING(ename, 1, 2) FROM emp;


-- LTRIM (string2 ) RTRIM (string2 ) TRIM(string) 
-- 去除 前端空格或后端空格 
SELECT LTRIM('  HELLO') FROM DUAL;  //前端空格
SELECT RTRIM('HELLO  ') FROM DUAL; //后端空格
SELECT TRIM('  HELLO  ') FROM DUAL; //首尾空格

时间日期

CURRENT_DATE( )	当前日期
CURRENT_TIME( )	当前时间
CURRENT_TIMESTAMP( )	当前时间截
DATE (datetime )	返回datetime的日期部分
DATE_ADD (date2,INTERVAL d_valued_type )	在date2中加上日期或时间
DATE_SUB (date2,INTERVAL d_valued_type )	在date2上减去一个时间
DATEDIFF (date1 ,date2 )	两个日期差(结果是天)
TIMEDIFF(date1,date2)	两个时间差(多少小时多少分钟多少秒)
now( )	当前时间
YEAR\|Month\|DATE (datetime ) FROM_UNIXTIME()	年月日

流程控制函数

IF(expr1, expr2,expr3) 如果expr1为True ,则返回expr2，否则返回expr3

实例：

SELECT IF(true, '北京', '上海') FROM tb01;

IFNULL(expr1, expr2) 如果expr1不为空NULL,则返回expr1,否则返回expr2

实例：

SELECT IFNULL(NULL, '南昌') FROM tb01;

如果expr1为true,则返回expr2,如果expr2为t,返回expr4，否则返回expr5

实例：

SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支-]

加密和系统函数

USER() 查询用户

DATABASE() 数据库名称

MD5(str) 为字符串算出一个MD5 32的字符串，(用户密码)加密

PASSWORD(str) 从原文密码str计算并返回密码字符串,通常用于对mysql数据库的用户密码加密

第七章、查询—进阶

1、分页查询

SELECT * FROM emp ORDER BY empno LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数;

2、多表查询

显示雇员名,雇员工资及所在部门的名字【员工表emp】【部门表dept】

雇员名,雇员工资来自 emp 表

部门的名字来自 dept 表

需求对 emp 和 dept 查询 ename, sal, dname, deptno

当我们需要指定显示某个表的列是，需要表.列表

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

3、合并查询

有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union , union all。

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

select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job='manager';

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

select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='MANAGER";

4、表复制

自我复制数据(蠕虫复制)

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

DESC tab01;
SELECT * FROM tab01;

-- 1. 先把 emp 表的记录复制到

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

-- 2. 自我复制

INSERT INTO tab01 SELECT * FROM tab01;

SELECT COUNT(*) FROM my_tab01;

5、自连接

自连接是指在同一张表的连接查询[将同一张表看做两张表]。

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

--自连接的特点

1．把同一张表当做两张表使用

2．需要给表取别名表名表别名

3 . 列名不明确，可以指定列的别名列名as列的别名

6、联表查询（外连接）

多表查询通过联接的方式将表关联在一起，联表的方法：inner、left、right

查询两张表，返回笛卡尔集

select * from class,student;

查询两张表，并关联起来（在笛卡尔集的基础上筛选出有关系的记录）

seslct * from class,seudent where student.cla_id = class.id;

内联接：只取两张表的共同部分

select * from student inner join class on student.cla_id = class.id;

左联接：在内联接的基础上保留左表的记录

select * from student left join class on student.cla_id = class.id;

右联接：在内联接的基础上保留右表的记录

select * from student right join class on student.cla_id = class.id;

全联接：在内联接的基础上保留左右两表没有对应关系的记录

select * from student left join class on student.cla_id = class.id
union
select * from student right join class on student.cla_id = class.id;

实例：查询student和class表，查询每个班级学生成绩大于60的人数

select class.name,count(student.id) from student left join class on student.cla_id = class.id group by class.name having (score > 60);

8、嵌套查询（子查询）

子查询是将一个查询语句嵌套在另一个查询语句中。

内层查询语句的查询结果，可以为外层查询语句提供查询条件。

子查询中可以包含 in、not in、any、all、exists、not exists等关键字和 =、!=、>、<等运算符。

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

多行子查询：多行子查询指返回多行数据的子查询使用关键字 in

1）带in关键字的子查询查询班级平均分在80分以上的班级名

select name from class where id in (select id from class group by name having avg(score) >80);

2）在比较运算符的子查询查询班级大于所有人的平均年龄的学生名和年龄。

select name,age from student where age > (select avg(age) from student);

3）带exists关键字的子查询

select * from student where exists (select id from class where name="计应二班");

子查询实例：

如何显示与 SMITH同一部门的所有员工?

先查询到 SMITH的部门号得到，把上面的 select 语句当做一个子查询来使用。

SELECT deptno FROM emp WHERE ename = 'SMITH';

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

4）子查询当做临时表使用

5）在多行子查询中使用 all 操作符

实例：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

select ename,sal,deptno from emp where sal>all(selectsal from emp where deptno=30)

6）在多行子查询中使用any 操作符

实例：如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号

select ename,sal,deptno from emp where sal> any(select sal fromemp where deptno=30)

6）多列子查询

-- 实例：查询与 allen 的部门和岗位完全相同的所有雇员(并且不含 allen 本人)

-- 分析: 1. 得到 smith 的部门和岗位

SELECT deptno , job FROM emp WHERE ename = 'ALLEN'

-- 分析: 2 把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配

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

8）在 from 子句中使用子查询

查找每个部门工资最高的人的详细资料

SELECT ename, sal, temp.max_sal, emp.deptno FROM emp, (SELECT deptno, MAX(sal) AS max_sal FROM emp GROUP BY dept) temp WHERE emp.deptno = temp.deptno AND emp.sal = temp.max_sal;

第八章、mysql 索引

1、索引的原理

没有索引，进行查询是全表扫描。建立索引后，表会形成一个索引的数据结构，可以快速定位。

代价：磁盘占用。对dml(update delete insert)语句的效率影响。

2、索引快速入门:

CREATE  INDEX  empno_index  ON  emp (empno)
 
-- empno_index：索引名称
-- emp (empno) : 表示在 emp 表的 empno 列创建索引

3、索引的类型

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

2.唯一索引(UNIQUE)

3.普通索引(INDEX)

4.全文索引(FULLTEXT)[适用于MyISAM]，一般开发，不使用mysql自带的全文索引,而是使用:全文搜索Solr和 ElasticSearch (ES)。

-- 主键，同时也是索引，称为主键索引.name varchar(32));

create table t1(id INT primary key(id))

-- id是唯一的，同时也是索引，称为unique索引.

create table t2(id INT unique)

4、索引使用

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

CREATE [UNIQUE] index index_name ON tbl_name (col_name[(length)1[ASC | DESC], ...);

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

实例：

-- 添加唯一索引

CREATE UNIQUE INDEX id_index ON t25 (id);

-- 添加普通索引方式 1

CREATE INDEX id_index ON t25 (id);

--添加主键(索引)

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

3.删除索引

DROP INDEX index name ON tbl_ name;

alter table table_name drop index index_name;

实例：

-- 删除索引

DROP INDEX id_index ON tb25

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

alter table t_b drop primary key;

实例：

-- 删除主键索引

ALTER TABLE t26 DROP PRIMARY KEY

5.查询索引(三种方式)

show index(es) from table_name;

show keys from table_name;

desc table_Name;

实例：

-- 1. 方式

SHOW INDEX FROM t25

-- 2. 方式

SHOW INDEXES FROM t25

-- 3. 方式

SHOW KEYS FROM t25

-- 4. 方式

DESC t25

索引小结

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数据库控制台事务的几个重要操作(基本操作 transaction.sql)

1.start transaction --开始一个事务

2.savepoint --设置保存点

3.rollback to -回退事务

4.rollback --回退全部事务

5.commit --提交事务,所有的操作生效,不能回退

细节:

1.没有设置保存点

2.多个保存点

3.存储引擎

4.开始事务方式

24.38.3回退事务

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

24.38.4提交事务

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

24.38.5事务细节讨论

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

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

3.你也可以在这个事务中(还没有提交时)，创建多个保存点.比如: savepoint aaa; 执行dml , savepoint bbb;

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

5.mysql的事务机制需要innodb的存储引擎才可以使用,myisam不好使.开始一个事务start transaction,set autocommit=off;

二、mysql事务隔离级别

事务隔离级别介绍

1.多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。(通俗解释)

2.如果不考虑隔离性，可能会引发如下问题：脏读、不可重复读、幻读

查看事务隔离级别

脏读(dirty read): 当一个事务读取另一个事务尚未提交的改变(update,insert, delete)时，产生脏读

不可重复读(nonrepeatable read): 同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除,每次返回不同的结果集，此时发生不可重复读。

幻读(phantom read): 同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

事务隔离级别

概念:Mysql隔离级别定义了事务与事务之间的隔离程度。

Mysql隔离级别(4种)	脏读	不可重复	幻读	加锁读
读未提交(Read uncommitted)	V	V	V	不加锁
读已提交(Read committed)	X	V	V	不加锁
可重复读(Repeatable read)	X	X	X	不加锁
可串行化 (Serializable )[演示重开客户端]	X	X	X	加锁

说明:V可能出现×不会出现

24.39.5设置事务隔离级别

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，一般情况下，没有特殊要求，没有必要修改。

三、mysql事务ACID

事务的acid特性

1.原子性(Atomicity)

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

2.一致性(Consistency)

事务必须使数据库从一个二致性状态变换到另外一个一致性状态

3.隔离性(lsolation)

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

4.持久性(Durability)

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

第十章、mysql视图

1、基本概念

视图：将sql语句查询的虚拟表保存下来

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

2、视图和基表关系的示意图

select * from myview update myview set job='worker' where empno=7369

对视图的总结

1.视图是根据基表(可以是多个基表)来创建的视图是虚拟的表

2.视图也有列，数据来自基表

3.通过视图可以修改基表的数据

4.基表的改变，也会影响到视图的数据

视图的基本使用

\1. create view 视图名 as select语句

\2. alter view 视图名 as select语句 --更新成新的视图．

\3. SHOW CREATE VIEW 视图名

\4. drop view 视图名1,视图名2

实例：

-- 创建视图

CREATE VIEW emp_view01 AS SELECT id, name, job FROM emp;

-- 查看视图

DESC emp_view01

SELECT * FROM emp_view01;

SELECT id, job FROM emp_view01;

-- 查看创建视图的指令

SHOW CREATE VIEW emp_view01

-- 删除视图

SHOW CREATE VIEW emp_view01

-- 视图的细节

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

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

-- 修改视图会影响到基表

UPDATE emp_view01 SET job = 'MANAGER' WHERE id = 7369;

ALTER view teacher_view as select * from course where cid>3;

SELECT * FROM emp;

-- 查询基表

SELECT * FROM emp_view01

视图最佳实践

1.安全：一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。

2.性能：关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时,数据库查询通常会用到连接(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。

3.灵活：如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

第十一章、mysql表类型和存储引擎

基本介绍

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

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

3．这六种又分为两类，一类是”事务安全型”(transaction-safe)，比如:InnoDB;

其余都属于第二类，称为”非事务安全型”(non-transaction-safe)[mysiam和memory].

24.41.2主要的存储引擎/表类型特点

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

细节说明: MyISAM、InnoDB、MEMORY

\1. MylSAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求

\2. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MylSAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

\3. MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应上个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦MySQL服务关闭，表中的数据就会丢失掉,表的结构还在。

引擎使用案例：

-- 查看所有的存储引擎

SHOW ENGINES

-- innodb 存储引擎

-- 1. 支持事务 2. 支持外键 3. 支持行级锁

-- myisam 存储引擎

-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁

 CREATE  TABLE  t28 ( id INT, `name` VARCHAR(32))  ENGINE  MYISAM

-- memory 存储引擎

-- 1. 数据存储在内存中[关闭了Mysql 服务，数据丢失, 但是表结构还在]

-- 2. 执行速度很快(没有 IO 读写)

-- 3. 默认支持索引(hash 表)

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

如何选择表的存储引擎

1.如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MylSAM是不二选择,速度快

2.如果需要支持事务，选择InnoDB。

3.Memory存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将、消失。(经典用法用户的在线状态().)

修改存储引擎

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

-- 指令修改存储引擎

ALTER TABLE `t29` ENGINE = INNODB

第十二章、MySQL管理

1、MySQL用户

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

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

user: 用户名;

authentication string: 密码，是通过mysql的password()函数加密之后的密码。

创建用户

--说明:创建用户，同时指定密码
create user '用户名' @ '允许登录位置' identified by '密码'

删除用户

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

用户修改密码

修改自己的密码:

set password = password(密码");

修改他人的密码(需要有修改用户密码权限):

set password for'用户名'@"登录位置"= password('密码');

2、mysql中的权限

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

3、给用户授权

基本语法:

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.在创建用户的时候，如果不指定Host,则为%，%表示表示所有IP都有连接权限 create user xXX;

2.你也可以这样指定 create user 'xxx'@'192.168.1.%’ 表示xxx用户在192.168.1.*的ip可以登录mysql

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

MySQL

目录

第一章、数据库操作

第二章、字段类型

第三章、表操作

第四章、字段操作

查询—基础

第五章、mysql约束

第六章、mysql函数

第七章、查询—进阶

第八章、mysql 索引

第九章、mysql事务

第十章、mysql视图

第十一章、mysql表类型和存储引擎

第十二章、MySQL管理