MySQL初级

MYSQL初级

1 mysql初识

mysql官网

MySQL是一个轻量级关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。

目前MySQL被广泛地应用在Internet上的中小型网站中，由于体积小、速度快、总体拥有成本低，开放源码、免费，一般中小型网站的开发都选择Linux + MySQL作为网站数据库。

MySQL是一个关系型数据库管理系统，MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，就增加了速度并提高了灵活性。

由于分布式和集群的出现，mysql也用于大型的网站上。

**数据库的好处：**可以持久化数据到本地、结构化查询

数据库的常见概念：

• DB：数据库，存储数据的容器
• DBMS：数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB
• SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言

公司名称	数据库名称
IBM公司	DB2数据库
微软公司	SQLServer数据库 Accesss数据库(Office办公软件)
Oralce公司	Oracle数据库 MySQL数据库(最早是一家瑞典的公司MySQL) SUN并购了(Java语言 收购MySQL)

数据库存储数据的特点：

• 数据存放到表中，然后表再放到库中
• 一个库中可以有多张表，每张表具有唯一的表名用来标识自己
• 表中有一个或多个列，列又称为“字段”，相当于java中“属性”
• 表中的每一行数据，相当于java中“对象”

**常见的数据库管理系统：**mysql、oracle、db2、sqlserver

MySQL的优点：

• 开源、免费、成本低
• 性能高、移植性也好
• 体积小，便于安装

MySQL服务的启停、登出

# 启动
net start 服务名

# 停止
net stop 服务名

# 登录
mysql 【-h 主机名 -P 端口号】 -u 用户名 -p密码

-- 退出
exit、quit、\q （三者都可以）

2 sql语言分类及常用命令

SQL分类：

名称	解释	命令
DDL（data definition language）	数据库定义语言， 数据库、表、视图、索引、存储过程	create,drop,alter
DML（data manipulation language）	数据库操纵语言：插入数据INSERT、删除数据DELETE、更新数据UPDATE	insert,delete,update
DQL（Data Query Language ）	用于查询数据库对象所包含的数据	select
DCL（Data Control Language）	数据库控制语言	grant,commit,rollback

show databases -- 显示所有数据库
use dbname     -- 打开某个数据库
describe user  -- 显示表mysql数据库中user表的列信息
create database  [if not exists] student -- 创建数据库
use databasename  -- 选择数据库
drop database [if exists] student -- 删除数据库
SET NAMES GBK     -- 字符乱码！

注意：

操作sql语句时不区分大小写

但是这个跟数据库排序规则有关

假设SELECT * FROM table WHERE txt = ‘a’

如果使用utf8_bin的排序规则，你就找不到 txt = ‘A’ 的那一行, 而 utf8_general_ci的排序规则就可以。

3 数据值和列类型

3.1 数据类型总览

MySQL支持多种类型的SQL数据类型：数值，日期和时间类型，字符串（字符和字节）类型，空间类型和 JSON数据类型等

数据类型描述使用以下约定：

• M表示整数类型的最大显示宽度。M表示整数类型的最大显示宽度。对于浮点和定点类型， M是可以存储的总位数（精度）。对于字符串类型， M是最大长度。允许的最大值M取决于数据类型。
• D适用于浮点和定点类型，并指示小数点后面的位数。最大可能值为30，但不应大于 M-2。
• **[ ]**表示类型定义的可选部分。

3.2 约束条件

约束条件就是在给字段加一些约束，使该字段存储的值更加符合我们的预期。

常用约束条件：

约束名	解释
UNSIGNED	无符号，值从0开始，无负数
ZEROFILL	零填充，当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED
NOT NULL	非空约束，表示该字段的值不能为空
DEFAULT	表示如果插入数据时没有给该字段赋值，那么就使用默认值
PRIMARY KEY	主键约束，表示唯一标识，不能为空，且一个表只能有一个主键。一般都是用来约束id
AUTO_INCREMENT	自增长，只能用于数值列，而且配合索引使用,默认起始值从1开始，每次增长1
UNIQUE KEY	唯一值，表示该字段下的值不能重复，null除外。比如身份证号是一人一号的，一般都会用这个进行约束
FOREIGN KEY	外键约束，目的是为了保证数据的完成性和唯一性，以及实现一对一或一对多关系

3.3 数值型

数值类型包括整数型、浮点型、定点型

整型数据类型包括：

• tinyint ：微整型
• smallint ：小整型
• mediumint ：中整型
• int ：整型
• bigint ：大整型

这些不同大小范围的整型信息如下：

默认整数类型是带符号的，即可以有正负值，比如：

create table zs(num1 int, num2 tinyint);

此时，num1和num2中都可以存储负数（但都不能超出范围）

不带符号的整数类型设置形式如下：

create table zs(num1 int unsigned, num2 tinyint unsigned);

有符号无符号的简单理解：

• 有符号值可以表示负数，0以及正数
• 无符号值只能为0或正数

如果不手动指定UNSIGNED，那么默认就是有符号的。

UNSIGNED小例子：

• 首先创建一个表

CREATE TABLE int_db(
    a TINYINT,
    b SMALLINT,
    c MIDDLEINT,
    d INT,
    e BIGINT
);

• 查看表结构

分析：

为什么每个字段类型后面的括号都有数值，这个数值实际上就是字段的显示宽度，也就是M的值，M表示整数类

型的最大显示宽度。最大显示宽度为255.显示宽度与类型可包含的值范围无关

我们在创建表的时候并没有指定字段类型的显示宽度，那么，默认的显示宽度则是该字段类型最大的显示宽度

例如字段a的显示宽度为4，是因为TINYINT有符号值的范围是-128到127，

-128的长度为4（负号、1、2、8共四位），所以默认的显示宽度最大为4，其他的以此类推

下面我们再新建一个表，将字段a的修改为无符号类型的。再看看a字段的默认显示宽度

可以看到，默认显宽度就变成3了，因为无符号的TINYINT的值范围为0-255，没有负号，所以最多是3位。

ZEROFILL

下面我们来试试ZEROFILL约束，前面的博客中我们知道。使用该约束后当数据的长度比我们指定的显示宽度小的时候会使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED

下面我们新建个表试一下，这次我们来指定一下显示宽度

CREATE TABLE int_db2(
    a TINYINT(8) ZEROFILL,
    b TINYINT(5) UNSIGNED
);

然后插入一条记录：

INSERT int_db2() VALUES(12,12);

可以看到，12变成了00000012，自动在前面补了0，这是因为指定的显示宽度是8，但是12只有两位，所以在前面补0，使长度为8。这就是ZEROFILL的效果

3.4 浮点型

• FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

一个小的（单精度）浮点数。允许值是-3.402823466E+38 到-1.175494351E-38，0以及1.175494351E-38 到3.402823466E+38。

M是总位数，D是小数点后面的位数。

占用4字节存储空间，可称为“单精度浮点数”，约7位有效数字。

浮点数存在精度丢失的问题，如果涉及到小数运算，尽量不要用浮点型

• DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

正常大小（双精度）浮点数。允许值是 -1.7976931348623157E+308到-2.2250738585072014E-308。

0以及 2.2250738585072014E-308到 1.7976931348623157E+308。

M是总位数，D是小数点后面的位数

占用8字节存储空间，可称为“双精度浮点数”，约17位有效数字。

3.5 定点型

• DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

定点小数是“精确的小数”——它通过内部技巧，突破了“有些小数无法用二进制精确表示”的局限。

常用于存储精确的小数，M是总位数，D是小数点后的位数。

小数点和（负数） -符号不计入 M。如果 D为0，则值没有小数点或小数部分。

最大位数（M）为 65.，最大支持小数（D）为30，如果D省略，则默认值为0。

如果M省略，则默认值为10。M的范围是1到65。D范围为0到30，且不得大于M。

DECIMAL也在存储时存在精度丢失的问题（四合五入）。

演示：

定义三个字段分别为float、double和decimal类型，并都插入数字“123456789.123456789123456789”，显示结果。

超出范围和溢出处理:

当MySQL将值存储在超出列数据类型允许范围的数值列中时，结果取决于当时生效的SQL模式：

如果启用了严格的SQL模式，则MySQL会根据SQL标准拒绝带有错误的超出范围的值，并且插入失败。

如果未启用限制模式，MySQL会将值截断到列数据类型范围的相应端点，并存储结果值，并产生一个警告

3.6 字符串类型

• CHAR[(M)]

一个固定长度的字符串，在存储时始终用空格填充指定长度。 M表示以字符为单位的列长度。M的范围为0到255.如果M省略，则长度为1，存储时占用M个字节

• VARCHAR(M)

可变长度的字符串，M 表示字符的最大列长度，M的范围是0到65,535，存储时占用**L+1（L<=M,L为实际字符的长度）**个字节

• TINYTEXT[(M)]

不能有默认值，占用L+1个字节，L<2^8

• TEXT[(M)]

不能有默认值，占用L+2个字节，L<2^16

• MEDIUMTEXT[(M)]

不能有默认值，占用L+3个字节，L<2^24

• LONGTEXT[(M)]

不能有默认值，占用L+4个字节，L<2^32

• ENUM(‘value1’,‘value2’,…)

ENUM是一个字符串对象，其值从允许值列表中选择，它只能有一个值，从值列表中选择,最多可包含65,535个不同的元素

• SET(‘value1’,‘value2’,…)

字符串对象，该对象可以有零个或多个值，最多可包含64个不同的成员

注意：

CHAR类型不管存储的值的长度是多少，都会占用M个字节，而VARCHAR则占用实际长度+1个字节。

3.7 text长文本类型

适用于存储“较长的文本内容”，比如文章内容。最长可存储65535个字符。

如果还需要存储更长的文本，可以使用mediumtext（1600万左右）或longtext（40亿左右）。

设定形式：

字段名称 text

text类型的字段不能设置默认值。

text类型虽然是字符类型，但不能设置长度！！！

text类型的数据不存在行中。

3.8 enum和set类型

enum类型和set类型都是用于存储“有给定值的可选字符”，比如类似表单中的单选，多选，下拉列表。

enum（单选类型/枚举类型）

enum类型通常用于存储表单中的“单选项”的值。

设定形式：

enum(‘选项值1’, ‘选项值2’, ‘选项值3’, …)

这些选项值都对应了相应的“索引值”，类似索引数组的下标，但是从1开始的。

即这些选项的索引值分别为：1， 2， 3， 4， …

enum类型最多可设定65535个选项。

create table tab1 (id int, edu enum(‘大学’, ‘中学’, ‘小学’, ‘其他’ ) );

# 常规插入数据的方式
insert into tab1 (id, edu) values (1, ‘大学’);

# 使用enum插入数据
insert into tab1 (id, edu) values (1, 2); -- 表示中学

set类型（多选类型）

set类型通常用于存储表单中的“多选项”的值。

设定形式：

set(‘选项值1’, ‘选项值2’, ‘选项值3’, …)

这些选项值都对应了相应的“索引值”，其索引值从1开始，并“依次翻倍”。

即这些选项的索引值分别为：1， 2， 4， 8， 16， … （其实就是2的n次方）

enum类型最多可设定64个选项值。

create table tab2 (aihao set(‘篮球’, ‘排球’, ‘足球’, ‘中国足球’ ) ); # 对应索引值为1,2,4,8
insert into tab2 ( aihao ) values ( 2 );   -- 表示排球
insert into tab2 ( aihao ) values ( 7);    -- 表示篮球,排球,足球

3.9 日期时间类型

• TIME：范围是’-838:59:59.000000’ 到’838:59:59.000000’

TIME类型不仅可以用于表示一天中的时间，还可以用于表示两个事件之间的经过时间或时间间隔。

TIME的完整的显示为 D HH:MM:SS

D：表示天数，当指定该值时，存储时小时会先乘以该值

HH：表示小时

MM：表示分钟

SS:表示秒

INSERT time_db() VALUES('22:14:16');

--   -2表示间隔了2两天
INSERT time_db() VALUES('-2 22:14:16');

-- 有冒号从小时开始
INSERT time_db() VALUES('14:16');

-- 没有冒号且没有天数则数据从秒开始
INSERT time_db() VALUES('30');

-- 有天数也从小时开始
INSERT time_db() VALUES('3 10');

-- 直接使用数字代替也可以
INSERT time_db() VALUES(253621);

• DATE：支持的范围是 ‘1000-01-01’到 ‘9999-12-31’

INSERT date_db() VALUES(20180813);

• DATETIME：日期和时间组合。支持的范围是 ‘1000-01-01 00:00:00.000000’到 ‘9999-12-31 23:59:59.999999’。

INSERT datetime_db() VALUES(20180102235432);
INSERT datetime_db() VALUES("2015-04-21 21:14:32");
INSERT datetime_db() VALUES("2015-04-23");

• TIMESTAMP：时间戳。范围是’19timestamp70-01-01 00:00:01.000000’UTC到’2038-01-19 03:14:07.999999’UTC。

INSERT timestamp_db() VALUES(20020121);
INSERT timestamp_db() VALUES(20020121142554);
INSERT timestamp_db() VALUES("2015-12-16 21:14:15");
INSERT timestamp_db() VALUES("2015-12-17");
INSERT timestamp_db() VALUES(NULL);
INSERT timestamp_db() VALUES(CURRENT_TIMESTAMP);
INSERT timestamp_db() VALUES();

使用TIMESTAMP自动添加创建时间（create_time）和修改时间（update_time）
alter table t_user modify column create_time timestamp not null default current_timestamp;
alter table t_user modify column update_time timestamp not null default current_timestamp on update current_timestamp;

• YEAR：范围是 1901到2155

INSERT year_db() VALUES("1993");
INSERT year_db() VALUES(1993);

4 mysql数据定义语言（DDL）

DDL（Data Definition languages）数据定义语言，这些语句主要定义了不同的数据段，数据表、列、索引等操作，主要关键字有create、drop、alter。

4.1 数据库的操作

#创建数据库+指定字符集
create database [if not exists] 库名 character set "utf8";

#删除数据库
drop database if exists 库名;

#更改数据库字符集
alter database 库名 character set='gbk';

4.2 表的管理

#创建表
create table [if not exists] 表名(字段1 字段类型 约束,字段n 字段类型 约束);

#删除表
drop table if exists 表名;

#复制表结构+数据
create table 表名 select * from 旧表;

#复制表结构
create table 表名 like 旧表;

#添加列
alter table 表名 add column 字段名 字段类型 约束;

#删除列
alter table 表名 drop column 字段名;

#修改列名
alter table 表名 change column 旧列名 新列名 新类型;

#修改列类型、约束
alter table 表名 modify column 字段名 varchar(12) 约束;

#修改表名
alter table 旧表名 rename to 新表名;

4.3 常见约束

约束名	说明
not null	非空，该字段的值必填
unique	唯一，该字段的值不可重复
default	默认，该字段的值不用手动插入有默认值
check	检查，mysql不支持
primary key	主键，该字段的值不可重复并且非空 unique+not null
foreign key	外键，该字段的值引用了另外的表的字段

# 列级约束(六大约束语法上都支持，但外键约束没有效果)
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,# 主键
	stuName VARCHAR(20) NOT NULL UNIQUE,# 非空
	gender CHAR(1) CHECK(gender='男' OR gender ='女'),# 检查
	seat INT UNIQUE,# 唯一
	age INT DEFAULT  18,# 默认约束
	majorId INT REFERENCES major(id)# 外键
);

# 表级约束：除了非空、默认，其他的都支持，但对主键无效
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	CONSTRAINT pk PRIMARY KEY(id),# 主键
	CONSTRAINT uq UNIQUE(seat),# 唯一
	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),# 检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键
);

# 通用写法
create table 表名(
	字段名 字段类型 not null,#非空
	字段名 字段类型 primary key,#主键
	字段名 字段类型 unique,#唯一
	字段名 字段类型 default 值,#默认
	constraint 约束名 foreign key(字段名) references 主表（被引用列）
)

# 修改表时添加或删除约束
alter table 表名 modify column 字段名 字段类型 not null;

# 自增长，不用手动插入值，可以自动提供序列值，默认从1开始，步长（auto_increment_increment）为1
id int auto_increment;

5 mysql数据管理语言（DML）

5.0 字符集问题

1、如果以默认方式创建数据库，create database test，字符集有默认值—>latin1()，但是这种方式不能存储中文

2、创建时也可以设置字符集

create database if not exists (库名) default character set = "utf8";

3、查询数据库字符集

select schema_name , default_character_set_name from information_schema.schemata where schema_name = '库名';

4、排序规则

utf8_general_ci： 默认 性能比较高 可能不太精确 俄罗斯 越南

utf8_unicode_ci： 性能比较低 扩展性好

5.1 insert

• 方式一（可插入多行，支持子查询）

insert into 表名字(列,列,列) values(值,值,值),(值,值,值);

特点：

1. 要求值的类型和字段的类型要一致或兼容
2. 字段的个数和顺序不一定与原始表中的字段个数和顺序一致
3. 假如表中有可以为null的字段，注意可以通过以下两种方式插入null值，字段和值都省略、字段写上，值使用null
4. 字段和值的个数必须一致
5. 字段名可以省略，默认所有列

• 方式二

insert into 表名 set 字段=值,字段=值,...;

5.2 delete

# 单表删除
delete from 表名

-- truncate删表
truncate 表名

delete和truncate的区别：

1. truncate删除后，如果再插入，标识列从1开始，delete删除后，如果再插入，标识列从断点开始
2. delete可以添加筛选条件，truncate不可以添加筛选条件
3. truncate效率较高
4. truncate没有返回值，delete可以返回受影响的行数
5. truncate不可以回滚，delete可以回滚

5.3 update

• 单表修改

语法：update 表名 set 字段=值,字段=值 【where 筛选条件】;

update three set a = 110 where b = 9

• 多表修改

update 表1 别名 

left|right|inner join 表2 别名 

on 连接条件  

set 字段=值,字段=值 

【where 筛选条件】

6 mysql数据查询语言（DQL）

数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。

6.1 基础查询

# 万物皆可查（表中的字段、常量值、表达式、函数）、查询的结果是一个虚拟的表格
select 查询列表 from 表名;

# 起别名
SELECT last_name 姓 FROM employees;（SELECT last_name as 姓 FROM employees;）

# 排除重复数据（distinct，跟在需要去重的字段前）
SELECT DISTINCT department_id FROM employees;

/*
	mysql中的+号只充当运算符的作用
	1、 两个操作数都为数值型，则做加法运算
	2、只要其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算如果转换失败，则将字符型数值转换成0
	3、只要其中一方为null，则结果肯定为null
*/
select 100+90;  190 /  select '10'+90; 100 /  select 'john'+90; 90 /  select null+10; null

# 拼接（concat）需求：查询员工名和姓连接成一个字段，并显示为 姓名
SELECT concat( last_name, first_name ) AS '姓名' FROM myemployees.employees

# 判断字段是否为空（ifnull）如果为null 返回指定的值，否则返回原本的值
select ifnull(commission_pct,0) from employees;

6.2 条件查询

# 按条件表达式筛选（>、<、=、!=、<>、>=、<=、<=>）<=>既可以判断NULL值，又可以判断普通的数值，可读性较低
select * from employees where salary > 1200;

# 按逻辑表达式筛选(&& || ! and or not) 需求：查询部门编号不是在90到110之间，或者工资高于15000的员工信息，如果and和or同时出现   and优先级别更高
select * from myemployees.employees emp where department_id not between 90 and 110 or salary > 15000;

# 模糊查询（like、between and、in、is null）like一般和通配符搭配(%表示0或多个字符、_表示一个字符)
SELECT *  FROM employees  WHERE last_name LIKE '%a%';

6.3 排序查询

# asc代表的是升序(默认)，可以省略
SELECT * FROM employees ORDER BY salary ASC;

# 添加筛选条件再排序（desc降序），order by子句在查询语句的最后面，除了limit子句
SELECT * FROM employees WHERE department_id>=90 ORDER BY employee_id DESC;

# 按多个字段排序
SELECT * FROM employees ORDER BY salary DESC,employee_id ASC;

6.4 常见函数

1、字符函数

函数名称	函数说明
concat	拼接（拼接内容有一个为null，则返回null）
substr	取子串（索引从1开始，前后都包括，一个参数表示从它到字符串末尾）
upper	转换成大写
lower	转换成小写
trim	默认是去除前后指定的空格，也可以指定去除字符 (trim(‘aa’ from ‘aaabcaaa’); #去除前后的aa结果为’abca’)
ltrim	去左边空格
rtrim	去右边空格
replace	替换 (replace(‘张无忌爱上了周芷若’,‘周芷若’,‘赵敏’); 结果：‘张无忌爱上了赵敏’，全部对应字符串都会替换)
lpad	左填充字符 (lpad(‘唐小尊’,2,‘ab’); 结果是’唐小’，lpad(‘唐小尊’,6,‘ab’); 结果是’aba唐小尊’)
rpad	右填充字符
instr	返回子串第一次出现的索引 (没找到返回0)
length	获取字节个数
isnull	是空值返回1 不是空值返回0

2、数字函数

函数名称	函数说明
abs	绝对值
round	四舍五入 (一个参数或两个参数，第二个参数为小数点后保留位数)
rand	随机数
floor	向下取整 (小于等于它的第一个整数)
ceil	向上取整 (大于等于它的第一个整数)
mod	取余 (mod(a,b)=a%b，结果是a-a/b*b，如果a,b有负数，则结果是绝对值的a%b，符号为a的符号)
truncate	截断 (truncate(1.69999,1);结果是1.6)
pow	次方根

3、日期函数

函数名称	函数说明
now	当前系统日期+时间
curdate	当前系统日期
curtime	当前系统时间
str_to_date	将字符转换成日期 (str_to_date(‘9-13-1999’,’%m-%d-%Y’);结果为1999-09-13的日期格式。只有合法的数据才可以转换)
date_format	将日期转换成字符 (date_formate(‘2018/6/6’,’%Y年%m月%d日’);输出2018年06月06日)
detediff	datediff(now(),‘1995-1-1’)：得到前面的时间与后面时间相差的天数
year	year(now())：【输出】今年，year(‘1998-1-1’)年：【输出】1998年
month	month(date)：数字月份
monthname	monthname(date)：英文的月份
day	day(date)：日
yearweek	星期，外国人的周日是第一天，得到中国的周几为yearweek(date,1);
hour	小时
minute	分钟
second	秒

序号	格式符	功能
1	%Y	4位的年份
2	%y	2位的年份
3	%m	月份（01，02，…）补零
4	%c	月份（1，2，…）
5	%d	日（01，02…）
6	%H	小时（24小时制）
7	%h	小时（12小时制）
8	%i	分钟（00，01，…，59）
9	%s	秒（00，01，…，59）

4、流程控制函数

# if
select if(1>2,'正确','错误');# 输出错误

# case（方式1）
select salary 原始工资,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;

# case（方式2）
select salary
case
    when salary>20000 then 'A'
    when salary>15000 then 'B'
    when salary>10000 then 'C'
    else 'D'
end as 工资级别
from employees

5、其他函数

函数名称	函数说明
version	当前数据库服务器的版本
database	当前打开的数据库
user	当前用户
password(‘字符’)	返回该字符的密码形式
md5(‘字符’)	返回该字符的md5加密形式

6、分组函数

/*
	sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
	1、sum、avg一般用于处理数值型， max、min、count可以处理任何类型
	2、以上分组函数都忽略null值
	3、MYISAM存储引擎下  ，COUNT(*)的效率高；INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
*/

6.5 分组查询

• 普通分组

# 案例一：查询每个工种的最高工资（每个xx，其中查询的字段是xx，group by后面的字段也是xx）（按工种分组）
select max(salary),job_id
from employess
group by job_id;

# 案例二：查询每个位置上的部门个数（按位置分组）
select count(*),location_id
from departments
group by location_id;

• 分组前筛选

# 添加筛选条件
# 案例一：查询邮箱中包含a字符的，每个部门的平均工资（按部门分组）
select avg(salary),department_id
from employees
where email like '%a%'
group by  department_id

# 案例二：查询有奖金的每个领导手下员工的最高工资（按领导分组）
select max(salary),manager_id
from employees
where commission_pct is not null
group by manager_id;

• 分组后筛选

# 添加复杂的筛选条件（筛选条件在group的结果集中，先出结果再筛选）
# 案例一：查询哪个部门的员工个数>2
# ①查询每个部门的员工个数
select count(*),department_id
from employees
group by department_id;
# ②根据①的结果进行筛选，查询哪个部门的员工个数>2
select count(*),department_id
from employees
group by department_id
having count(*)>2;

# 案例二：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
# 如果条件太多，分不清楚筛选条件放在前还是后，可以先把select、from、group by这三行写好，再加入前后筛选条件
select max(salary),job_id
from employess
where commission_pct is not null
group by job_id
having max(salary)>12000;

# 案例三：查询领导编号>102的每个领导手下的最低工资>5000的领导是谁，以及其最低工资
# 分组的字段是领导，有关领导的条件放在where后。
# 分组函数求最低工资，有关分组函数结果的条件放在having后

select 领导id，min(工资)
from 员工表
where 领导id>102
group by 领导id
having min(工资)>5000;

• 按表达式或函数分组

# 按表达式或函数分组
# 按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些？
select count(*),length(last_name) len_name
form employees
group by length(last_name)
having count(*)>5

• 按多个字段分组

# 按多个字段分组
# 案例：查询每个部门每个工种的员工的平均工资
select avg(salary),department_id,job_id
from employees
group by department_id,job_id;#这两个字段都一样才会分成一个小组

• 添加排序

# 添加排序
# 案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的从高到低显示
select avg(salary),department_id,job_id
from employees
where department_id is not null
group by department_id,job_id
having avg(salary)>10000 #平均工资高于10000
order by avg(salary) desc;#除了limit，就是它最后，比having靠后

• group by的特点

1. 可以按单个字段分组
2. 和分组函数一同查询的字段最好是分组后的字段
3. 可以按多个字段分组，字段之间用逗号隔开
4. 可以支持排序
5. 分组函数做条件肯定是放在having中，having后可以支持别名
6. 考虑到性能问题，能用分组前筛选，就优先考虑分组前筛选
7. group by、having后面都可以跟别名。Oracle不支持

6.6 连接查询

# 显示所有员工的姓名，部门号和部门名称。

# 方式1（使用sql92）
SELECT
	e.last_name,
	d.department_id,
	d.department_name 
FROM
	employees e,
	departments d 
WHERE
	e.department_id = d.department_id
	
# 方式2（使用sql99）更强大
SELECT
	e.last_name,
	d.department_id,
	d.department_name 
FROM
	employees e
	INNER JOIN departments d ON e.department_id = d.department_id

6.7 分页查询

# 需求：有奖金的员工信息，并且工资较高的前10名显示出来
select * from employees where commission_pct is not null order by salary desc limit 10;

6.8 子查询

# where、having（标量子查询、行子、列子查询）
# 返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
    # 查询141号员工的job_id
    SELECT job_id
    FROM employees
    WHERE employee_id = 141
) AND salary>(
    # 查询143号员工的salary
    SELECT salary
    FROM employees
    WHERE employee_id = 143
);

# select（仅支持标量子查询），一般搭配着单行操作符使用 > < >= <= = <>
# 查询每个部门的员工个数
SELECT d.*,( 
    # 查询员工个数
    SELECT COUNT(*) FROM employees e WHERE e.department_id = d.`department_id` 
) 个数 
FROM
	departments d;

# from（支持表子查询）要求必须起别名
# 查询每个部门的平均工资的工资等级
SELECT
	dep_avg.*,
	jb.grade_level 
FROM
	(
        # 查询每个部门的平均工资
	SELECT
		employees.department_id dept_id,
		AVG( myemployees.employees.salary ) AS ag 
	FROM
		employees 
	WHERE
		employees.department_id IS NOT NULL 
	GROUP BY
		employees.department_id 
	) dep_avg
	INNER JOIN job_grades jb ON dep_avg.ag BETWEEN jb.lowest_sal 
	AND jb.highest_sal

6.9 联合查询

# union关键字默认去重，如果使用union all 可以包含重复项

# 查询部门编号>90或邮箱包含a的员工信息

# 普通查询
select * from employees where email like '%a%' or department_id>90;

# 使用联合查询
SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;

6.10 查询总结

查询语句的执行顺序：

select 查询列表 ⑦

from 表1 别名 ①

连接类型 join 表2 ②

on 连接条件 ③

where 筛选 ④

group by 分组列表 ⑤

having 筛选 ⑥

order by排序列表 ⑧

limit 起始条目索引，条目数; ⑨

6.11 常见通用的Join查询

1、AB两表的共有部分（内连接）

select * from A inner join B on A.id=B.id;

2、A表的全集

select * from A left join B on A.id=B.id;

3、B表的全集

select * from A right join B on A.id=B.id;

4、A的独有

select * from A left join B on A.id=B.id where B.id is null;

5、B的独有

select * from A right join B on A.id=B.id where A.id is null;

6、AB全有（MySQL不支持全外连接FULL OUTER JOIN查询，因此可以拆分成A表的全集+B表的全集）

select * from A left join B on A.id=B.id
union # union可去除重复数据
select * from A right join B on A.id=B.id;

7、A的独有+B的独有

select * from A left join B on A.id=B.id where B.id is null;
union
select * from A right join B on A.id=B.id where A.id is null;

7 mysql 事务控制语言（TCL）

7.1 事务的概念及特点？

一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行。

特点（ACID）：

• 原子性（A）：一个事务是不可再分割的整体，要么都执行要么都不执行
• 一致性（C）：一个事务可以使数据从一个一致状态切换到另外一个一致的状态
• 隔离性（i）：一个事务不受其他事务的干扰，多个事务互相隔离的
• 持久性（D）：一个事务一旦提交了，则永久的持久化到本地

7.2 如何开启事务？

insert、update、delete都属于隐式事务，本身就会自动提交！

显式事务的开启结束：

# 第一步，手动设置自动提交方式
set autocommit=0

# 第二步，显示开启
start transaction;

# 第三步，编写sql片段

# 第四步，设置回滚点（可选操作，是否需要回滚）
savepoint 回滚点名;

# 第五步，结束事务
提交：commit;

回滚：rollback;

回滚到指定的地方：rollback to 回滚点名;

7.3 并发事务存在哪些问题？

问题	含义
丢失更新（Lost Update）	当两个或多个事务选择同一行，最初的事务修改的值，会被后面的事务修改的值覆盖。
脏读（Dirty Reads）	当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
不可重复读（Non-Repeatable Reads）	一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现和以前读出的数据不一致。
幻读（Phantom Reads）	一个事务按照相同的查询条件重新读取以前查询过的数据，却发现其他事务插入了满足其查询条件的新数据。

7.4 事务的隔离级别？

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大

备注 ： √ 代表可能出现 ， × 代表不会出现 。

隔离级别	丢失更新	脏读	不可重复读	幻读
Read uncommitted	×	√	√	√
Read committed	×	×	√	√
Repeatable read（默认）	×	×	×	√
Serializable	×	×	×	×

Mysql 的数据库的默认隔离级别为 Repeatable read ， 查看方式：

show variables like 'tx_isolation';

设置隔离级别的方式：

set session transaction isolation level xxx;

mySQL数据库提供默认隔离级别 Repeatable Read

Oracle数据库提供默认隔离级别 Read Committed

8 mysql数据控制语言（DCL）

8.1 创建一个新用户及用户删除

添加用户：

create user ‘用户名’@‘IP’ identified by ‘密码’;

# 创建一个zs的用户，默认权限只允许登录
create user 'zs' @' localhost' identified by '123456'

删除用户：

drop user ‘用户名’@‘IP’;

# 删除zs用户
drop user 'zs' @' localhost'

8.2 权限

给新用户赋予权限：

grant 权限 on 数据库名.表 to ‘用户名’@‘IP’;

# 给zs所有权限
grant all on *.* to 'zs' @' localhost'

# 刷新
flush privileges

回收用户的权限：

revoke 权限 on 数据库名.表名 from ‘用户名’@‘IP’;

# 回收zs  create的权限
revoke create on *.* from 'zs' @' localhost';

修改用户的密码：

update mysql.user set authentication_string = password(‘123’) where user = ‘用户名’;

update mysql.user set authentication_string = password('666666') where user = 'zs';

8.3 常见的mysql权限

权限名	解释
ALL	全局的所有权限或者表级的所有权限，例如all on *.*就是拥有所有全局权限，all on test.test，就是拥有test表级的所有权限。
ALTER	表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。
SELECT	表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。
INSERT	表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。
DELETE	表示授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限。
UPDATE	表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。
REFERENCES	表示授予用户可以创建指向特定的数据库中的表外键的权限。
CREATE	表示授权用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。
SHOW VIEW	表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
CREATE ROUTINE	表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。
ALTER ROUTINE	表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。
INDEX	表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
DROP	表示授予用户可以删除特定数据库中所有表和视图的权限。
CREATE TEMPORARY TABLES	表示授予用户可以在特定数据库中创建临时表的权限。
CREATE VIEW	表示授予用户可以在特定数据库中创建新的视图的权限。
EXECUTE ROUTINE	表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
LOCK TABLES	表示授予用户可以锁定特定数据库的已有数据表的权限。

9 视图和变量

9.1 视图

视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。——百度百科

关系型数据库中的数据是由一张一张的二维关系表所组成，简单的单表查询只需要遍历一个表，而复杂的多表查询需要将多个表连接起来进行查询任务。

对于复杂的查询事件，每次查询都需要编写MySQL代码效率低下。为了解决这个问题，数据库提供了视图（view）功能。

每次进行查询工作，都需要编写查询代码进行查询，而视图的作用就是不必每次都重新编写查询的SQL代码，而是通过视图直接查询即可。

视图是虚拟表，本身不存储数据，而是按照指定的方式进行查询。

# 查询书的价格≥1000的信息(创建视图)
create view vbook as select * from books where price >= 1000

# 使用，视图的使用就当做表使用（使用视图）
select v.name 书名,v.price 价格 from vbook v

# 将查询书的价格≥1000的信息的视图修改成查询所有书籍（修改试图）
create or replace view vbook as select * from books

# 将查询所有书籍的视图修改成查询书籍价格≤1000（修改视图）
alter view vbook as select * from books where price < 1000

# 删除视图
drop view vbook

# 视图权限控制（with check option）
create view vbook3 as  select * from books where price >= 2000 with check option # （插入价格必须大于2000）

9.2 变量

# 定义用户变量
set @my=12

# 查询用户变量值
select @my

# 查看系统变量，如果没有显式声明global还是session，则默认是session
show 【global|session 】variables like ''; 

# 查看指定的系统变量的值
select @@【global|session】.变量名;

# 为系统变量赋值
set @@变量名=值；

11 其它

都类似于java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名字

• 提高重用性
• sql语句简单
• 减少了和数据库服务器连接的次数，提高了效率

10.1 存储过程

# 插入五条数据到admin中（存储过程相当于java中的函数）

# 创建存储过程
DELIMITER $
CREATE PROCEDURE `my_admin`()
BEGIN
	INSERT INTO girls.admin
	VALUES(1,'zs','11112'),(2,'zs22','11121'),(3,'zs12','11211'),(4,'zs1','11111'),(5,'zs','11121');
END $
DELIMITER  ; 

# 查看存储过程
show create procedure my_admin

# 执行存储过程
call my_admin()

# 删除存储过程
drop procedure my_admin

10.2 函数

# 返回公司的员工个数
# 创建函数
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT COUNT(*) INTO c#赋值
	FROM employees;
	RETURN c;
END $
DELIMITER  ;

# 执行函数
SELECT myf1();

# 删除函数
DROP FUNCTION myf3;

# 查看所有的函数
show function status

10.3 触发器

一张表最多只能有 6 个触发器触发器不需要手动调用，当自定义的事件内容发生时会自动启用

-- 当商品订单数量大于库存数量时

-- 触发器的创建
DELIMITER **
CREATE TRIGGER before_order BEFORE INSERT ON my_order FOR EACH ROW
BEGIN
	-- 先获取商品库存
	SELECT inv FROM my_goods WHERE id=new.goods_id INTO @inv;
	-- 将库存与即将要执行的要插入的订单中的商品数量作比较
	IF @inv<new.`g_number` THEN 
			-- 触发器中没有能够阻止事件发生的语句,只能暴力报错
			INSERT INTO XXX VALUES(XXX);
			END IF;
END
**
DELIMITER  ;

-- 删除触发器，触发器只能删除，不能修改
DROP TRIGGER before_order;

;

查看指定的系统变量的值

select @@【global|session】.变量名;

为系统变量赋值

set @@变量名=值；

## 11 其它

都类似于java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名字

- 提高重用性
- sql语句简单
- 减少了和数据库服务器连接的次数，提高了效率

### 10.1 存储过程

```sql
# 插入五条数据到admin中（存储过程相当于java中的函数）

# 创建存储过程
DELIMITER $
CREATE PROCEDURE `my_admin`()
BEGIN
	INSERT INTO girls.admin
	VALUES(1,'zs','11112'),(2,'zs22','11121'),(3,'zs12','11211'),(4,'zs1','11111'),(5,'zs','11121');
END $
DELIMITER  ; 

# 查看存储过程
show create procedure my_admin

# 执行存储过程
call my_admin()

# 删除存储过程
drop procedure my_admin

10.2 函数

# 返回公司的员工个数
# 创建函数
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT COUNT(*) INTO c#赋值
	FROM employees;
	RETURN c;
END $
DELIMITER  ;

# 执行函数
SELECT myf1();

# 删除函数
DROP FUNCTION myf3;

# 查看所有的函数
show function status

10.3 触发器

一张表最多只能有 6 个触发器触发器不需要手动调用，当自定义的事件内容发生时会自动启用

-- 当商品订单数量大于库存数量时

-- 触发器的创建
DELIMITER **
CREATE TRIGGER before_order BEFORE INSERT ON my_order FOR EACH ROW
BEGIN
	-- 先获取商品库存
	SELECT inv FROM my_goods WHERE id=new.goods_id INTO @inv;
	-- 将库存与即将要执行的要插入的订单中的商品数量作比较
	IF @inv<new.`g_number` THEN 
			-- 触发器中没有能够阻止事件发生的语句,只能暴力报错
			INSERT INTO XXX VALUES(XXX);
			END IF;
END
**
DELIMITER  ;

-- 删除触发器，触发器只能删除，不能修改
DROP TRIGGER before_order;