软件安装
免安装版本
解压文件
在Path变量值中添加变量值：%MYSQL_HOME%\bin，
创建data文件夹
创建my.ini文件
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\Program Files\mysql-8.0.12-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\Program Files\mysql-8.0.12-winx64\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
cmd管理员身份运行
进入bin目录
mysql
mysqld install MySQL
mysqld --initialize
net start mysql
alter user user() idenfitied by '123456';
### sqlyog
ALTER USER 'root'@'localhost' identified with mysql_native_password by '123456';
字符集：
调整提示符：
prompt \u@\h \d>
alter database t1 default character set = utf8;
基本操作
登录
show语句
SOHW databases：列出所有数据库
SHOW TABLES：列出默认数据库中的表
SHOW TABLES FROM <database_name>：列出指定数据库中的表
SHOW COLUMNS FROM <table_name>：显示表的列结构
SHOW INDEX FROM <table_name>：显示表中有关索引和索引列的信息
SHOW CHARACTER SET：显示可用的字符集及其默认整理
SHOW COLLATION：显示每个字符集的整理
SHOW STATUS：列出当前数据库状态
SHOW VARIABLES：列出数据库中的参数定义值
SHOW PROCESSLIST 查看当前的连接数量
use Database打开数据库
scott用户
数据库操作
show databases;
create database + 数据库名称 + [库选项];
create database TBL_ERROR_CODE charset utf8;
数据库创建和删除
create databases t1 ;
drop database t1;
show databases t1
/*
功能：创建 scott 数据库中的 dept 表
*/
create database scott;
use scott;
create table dept(
deptno int unsigned auto_increment primary key COMMENT '部门编号',
dname varchar(15) COMMENT '部门名称',
loc varchar(50) COMMENT '部门所在位置'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表';
/*
功能：创建 scott 数据库中的 emp 表
*/
create table emp(
empno int unsigned auto_increment primary key COMMENT '雇员编号',
ename varchar(15) COMMENT '雇员姓名',
job varchar(10) COMMENT '雇员职位',
mgr int unsigned COMMENT '雇员对应的领导的编号',
hiredate date COMMENT '雇员的雇佣日期',
sal decimal(7,2) COMMENT '雇员的基本工资',
comm decimal(7,2) COMMENT '奖金',
deptno int unsigned COMMENT '所在部门',
foreign key(deptno) references dept(deptno)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='雇员表';
*/
功能：创建数据库 scott 中的 salgrade 表，工资等级表
*/
create table salgrade(
grade int unsigned COMMENT '工资等级',
losal int unsigned COMMENT '此等级的最低工资',
hisal int unsigned COMMENT '此等级的最高工资'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工资等级表';
/*
功能：创建数据库 scott 的 bonus 表，工资表
*/
create table bonus(
ename varchar(10) COMMENT '雇员姓名',
job varchar(9) COMMENT '雇员职位',
sal decimal(7,2) COMMENT '雇员工资',
comm decimal(7,2) COMMENT '雇员资金'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工资表';
/*
功能：插入数据库 scott 中表 dept 的初始化数据
*/
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
/*
功能：插入数据库 scott 中表 emp 的初始数据
*/
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'87-7-13',3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'87-7-13',1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);
/*
功能：插入数据库 scott 中表 salgrade 的初始数据
*/
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);
基本概念
数据
数据是指对客观事件进行记录并可以鉴别的符号，是对客观 事物的性质、状态以及相互关系等进行记载的物理符号或这些物 理符号的组合。它是可识别的、抽象的符号。
数据库简介
数据库，简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据运行新增、截取、更新、删除等操作。
所谓“数据库”系以一定方式储存在一起、能予多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。
DBMS：数据库管理系统
创建，管理，删除，运行数据库的软件
数据模型：
- 概念模型
- 逻辑模型
- 物理模型
概念模型:解决存什么问题
逻辑模型:解决怎么存问题逻辑模型
- 层次模型
- 网状模型
- 关系模型（主流的模型）
物理模型:解决存在哪的问题
Nosql：
键值（KV）存储：Memcached、Redis
列存储（column-oriented）：HBASE(新浪，360)、Cassandra（200台服务器集群 ）
文档数据库（document-oriented）：MongoDB（最接近关系型数据库的NoSQL）
图形存储（Graph）：Neo4j
MySQL基本体系结构
（1） Connectors指的是不同语言中与SQL的交互
（2）Management Serveices & Utilities： 系统管理和控制工具，例如备份恢复、Mysql复制、集群等
（3）Connection Pool: 连接池：管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求
（4）SQL Interface: SQL接口：接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
（5）Parser: 解析器，SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚 本， 主要功能：
a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的
b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的
（6）Optimizer: 查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行 查询。
用一个例子就可以理解： select uid,name from user where gender = 1;
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
将这两个查询条件联接起来生成最终查询结果
（7） Cache和Buffer（高速缓存区）： 查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
通过LRU算法将数据的冷端溢出，未来得及时刷新到磁盘的数据页，叫脏页。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
（8）Engine ：存储引擎。存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。
Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）
现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的MyISAM,InnoDB,BDB
MyISAM引擎，它查询速度快，有较好的索引优化和数据压缩技术。但是它不支持事务。
InnoDB支持事务，并且提供行级的锁定，应用也相当广泛。
Mysql也支持自己定制存储引擎，甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。
连接层
通讯协议为：tcp/ip 或 socket
连接线程 为连接的数量
用户验证 为通过用户名 密码验证进行通讯协议
SQL层
sql即结构化的查询语句（数据库内部逻辑语言）sql92 sql99
1、判断语法、语句、语义
判断语句类型
2、数据库不能直接响应sql语句
必须明确的知道数据在哪个磁盘
3、数据库对象授权情况判断
授权失败不继续
4、解析(解析器)
将sql语句解析成执行计划，运行执行计划，生成找数据的方式
5、优化 (优化器)
运行执行计划
5.6之后 基于代价的算法，从执行计划中选择代价最小的交给"执行器"
6、"执行器"
运行执行计划
最终生产如何去磁盘找数据方式
7、将取数据的方式，交由下层(存储引擎层)进行处理
8、最终将取出的数据抽象成管理员或用户能看懂的方式(表)，展现在用户面前
9、查询缓存： 缓存之前查询的数据。
假如我们查询的表是一个经常有变动的表，查询缓存不要设置太大
存储引擎层
由上层决定存储方式
存储引擎是充当不同表类型的处理程序的服务器组件。
文件系统存储
文件系统：操作系统组织和存取数据的一种机制。文件系统是一种软件。
类型：ext2 3 4 ，xfs 数据。 不管使用什么文件系统，数据内容不会变化，不同的是，存储空间、大小、速度。
mysql数据库存储
MySQL引擎： 可以理解为，MySQL的“文件系统”，只不过功能更加强大。
MySQL引擎功能： 除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能。
存储引擎层功能：
存数据、取数据
数据的不同存储方
不同的管理方式：
事务（增、删、改）
备份恢复
高级功能（高可用的架构、读写分离架构）
InnoDb引擎
支持ACID的事务，支持事务的四种隔离级别；
支持行级锁及外键约束：因此可以支持写并发；
不存储总行数；
一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制；
主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。
Innodb的主索引结构如下：
MyISAM引擎
不支持事务，但是每次查询都是原子的；
支持表级锁，即每次操作是对整个表加锁；
存储表的总行数；
一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；
采用菲聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。
SQL: 结构化查询语言
dql:数据查询语言
dml:数据操作语言（第11章）
ddl:数据定义语言
dcl:数据控制语言
tpl/tcl:事物处理语言或者事物控制语言
dql:
语法：语言本身不区分大小写
SELECT [distinct] | * |{列名 | 表达式 [列别名][,... ]}
[FROM 表名 ];
SELECT ：寻找哪些数据
FROM: 从哪里寻找数据
[]:被中括号括起来的内容是可有可无的
| ：或者
关键字
' *' ：所有
列名： 表中某一列的名字
表达式：表达式的内容可以和表无关，但是受表行数的影响
字符串表达式 ：
算数表达式：算数运算
列别名：给列起外号，如果碰到格式中不符合可以使用双引号括起来
distinct：去掉重复数据
,...：
空值/null:任何数和空值做任何运算的结果都为null
选择查询：
SELECT [distinct] | * |{列名 | 表达式 [列别名][,... ]}
FROM 表名
[where 逻辑表达式 ];
逻辑表达式 = 关系表达式 [逻辑运算符 ...]
关系表达式 = 列名|表达式 比较运算符 列名|表达式
SELECT [distinct] | * |{列名 | 表达式 [列别名][,... ]}
FROM 表名
[where {列名|表达式 比较运算符 列名|表达式} [逻辑运算符 ...] ]
[limit] [start] [ length] ; //start 从0开始
比较运算符：
常用比较运算符：
>,< , = , >= , <= , != , <>,<=>
对日期的比较需要按照日期的格式
特殊比较运算符：
between and:
in:
is null:
like:模糊查询
通配符：%: 任意长度的任意字符
_: 一个长度的任意字符
escape: 注册转译字符
逻辑运算符：
and :
or :
not :
xor:
逻辑运算符的规则：
| and | true | false | null |
|---|---|---|---|
| true | true | false | null |
| false | false | false | false |
| null | false | false | null |
| or | true | false | null |
|---|---|---|---|
| true | true | true | true |
| false | true | false | null |
| null | false | false | null |
| xor | true | false | null |
|---|---|---|---|
| true | false | true | null |
| false | true | false | null |
| null | null | null | null |
排序：
SELECT [distinct] | * |{列名 | 表达式 [列别名][,... ]}
FROM 表名
[where {列名|表达式 比较运算符 列名|表达式} [逻辑运算符 ...] ]
[order by 列名|表达式|列别名 [asc] [desc] [,...] ]
[limit] [start] [ length] ;
filed
SELECT [distinct] | * |{列名 | 表达式 [列别名][,... ]}
FROM 表名
[where {列名|表达式 比较运算符 列名|表达式} [逻辑运算符 ...] ]
[order by 列名|表达式|列别名 [asc] [desc] [,...] ] [field (列名 ,str1,str2,str3...)]
[limit] [start] [ length] ;
多表连接
--查询smith的员工姓名，部门名称，部门所在的地址
select empno ,ename ,deptno from emp where ename = 'SMITH';
select dname ,loc from dept where deptno = 20;
--多表连接
select * from emp , dept
WHERE DEPT.DEPTNO = EMP.DEPTNO
--sql 99
--sql99
--cross join (交叉连接)
select * from emp cross join dept;
--oracle 笛卡尔积
select * from emp , dept;
--natural joib (自然连接)
select * from emp natural join dept;
emp salgrade
--using 连接（natural 二代）
select * from emp join dept using(deptno);
select * from emp a join emp b using(empno)
--oracle 等值连接
select * from emp ,dept where emp.deptno = dept.deptno
--on 连接
select *
from emp
join salgrade
on (emp.sal between salgrade.losal and salgrade.hisal and ename = 'SMITH');
select * from emp join dept
on(emp.deptno = dept.deptno) ;
--不等值连接
select *
from emp
,salgrade
where emp.sal between salgrade.losal and salgrade.hisal;
--left outer join
--查询领导的姓名和员工的姓名
select a.ename ,b.ename
from emp b ,emp a
where b.mgr =a.empno (+)
select a.ename ,b.ename
from emp b left outer join emp a on(b.mgr =a.empno);
--right outer join
--查询部门的名称和员工的名称 （所有）
select dname ,ename
from emp,dept
where emp.deptno (+)= dept.deptno;
select dname ,ename
from emp right outer join dept on( emp.deptno = dept.deptno)
函数
函数：编程中的一个工具，关心函数功能，名字，参数类型，结果。
函数分类：
字符函数
数字函数
日期函数
转换函数
通用函数
单行函数：一行数据得到一个结果，多行数据得到多个结果
多行函数：多行数据得到一个结果。
语法：
函数的调用：
函数名（列名|表达式 [,...]）
（）：参数列表
举例：
电饭锅（电，水，米） = 饭
作用场景：
除了from都能用
例子：
--函数
--字符函数：
--concat(str[,...])
select concat('a','b','c') ;
concat_ws('@','abc','123.com');--指定分隔符号进行拼接
--Lower
select lower(ename) from emp;
--匹配名字是smith的人员忽略大小写
select ename from emp where lower(ename) = 'smith';
select * from emp where lower(ename) = lower('smith')
--upper
select upper('xixi') from emp;
--format()--数字格式化
SELECT FORMAT(123324.123,4);
--left(str,len), right
select left('aasd',2);
--length 字节长度
SELECT LENGTH('asd啊'); --中文utf8 是3个字节
--char_length --字符长度
SELECT CHAR_LENGTH('asd啊');
--trim() --去空格
select trim(' as d ');
--substring();
SELECT SUBSTRING('asd',1,2) ; -- 位置从1开始，第三个参数是长度
--repeat(str,n) 重复
SELECT REPEAT('asd',3) ;
--SPACE(n);返回多个空格
select replace('haha','a','c') from emp;
--查询出名字中包含S的人员信息，要求不允许使用like ,instr,substr
select * from emp where replace(ename,'S','s') <>ename
--instr(str,str1);
SELECT INSTR('adasd','as'); -- 从1 开始
--reverse 反转字符串
SELECT REVERSE('asd') ;
--lpad
select lpad('abc',1,'$') from dual;
select rpad('abc',10,'$') from dual;
--length 字符长度
SELECT LENGTH('你我他')FROM DUAL;
--trim
select trim( ' hah ah ') from dual;
select trim(leading 'h' from 'haha') from dual;
select trim(trailing 'a' from 'haha') from dual;
select trim(both 'a' from 'haha') from dual;
--like
SELECT 'asd' LIKE '%a%'; --符合返回1
--数字函数：
--div:整数除法
SELECT 1 DIV 3
--mod ：取余
select mod(5,3) from dual;
--round:四舍五入
select round(4.5) from dual;
--truncate:截断
select truncate(4.1) from dual;
--floor：向下取整
select floor(4.9) from dual;
--ceil：向上取整
select ceil(4.98) from dual;
select floor (-4.1) from dual;
--sign :取表达式或者数的结果为正负
select sign(-100) from dual;
--rand(种子):
select rand();
--abs：绝对值
select abs(-100) from dual;
--power：次幂
select power(3,3);
--日期函数
--当前时间
SELECT NOW(),CURDATE()+1,CURTIME();
--时间计算
SELECT DATE_ADD('2014-13-13',INTERVAL -365 DAY);
--两个日期之间差额（只计算天）
SELECT DATEDIFF('2013-1-8 ','2013-1-2');
--两个日期之间差额（随意计算）
select timstampdiff(hour,'2012-1-1 11:23','2013-1-1 11"24');
--指定格式的日期
SELECT DATE_FORMAT(NOW(),'%Y/%m/%d %h-%i-%s');
--字符串转时间
select str_to_date('2016-01-02', '%Y-%m-%d %H');
SELECT UNIX_TIMESTAMP('2014-06-01')
-- 1401552000
SELECT DATE_ADD(FROM_UNIXTIME(1401552000),INTERVAL 10 DAY);
[图片上传失败...(image-d93c73-1550702731218)]
其他函数：
inet_aton();
inet_nota();
SELECT INET_ATON('192.168.0.1');
SELECT INET_NTOA(3232235521)
--通用函数：
LAST_INSERT_ID() --返回最近生成的AUTO_INCREMENT值
SELECT IFNULL(null,'Hello Word')
->Hello Word
--nullif
select nullif(3,3) from dual;
select 1/nullif(0,0) from dual;
--coalesce
select coalesce(null,null,null,4,null,6) from dual;
--case ,decode
--查询各个部门的人员姓名，部门编号和部门的名称
select ename,
deptno,
(case deptno
when 10 then
'10部门'
when 20 then
'20部门'
else
'30部门'
end)
from emp;
select ename ,deptno ,decode(deptno ,10,'10部门',20,'20部门','30部门')
from emp ;
select decode(haha,1,11,2,12,3,13,4,14,5,15,6,16,7,17,8,19) from dual;
select * from dept;
/*
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
*/
--要求查询出 所有人员的姓名，工资，（如果工资低于1000，屌丝；如果1000到2000之间 ，
--能活；如果2001到3000之间，哎呦不错哦；如果超过高富帅）
--case 的简单写法
select ename,
sal,
case
when sal < 1000 then
'X丝'
when sal between 1000 and 2000 then
'能活'
when sal between 2001 and 3000 then
'哎呦不错哦'
else
'高富帅'
end
from emp;
create table haha(name varchar2(10),class varchar2(10) ,cj number(10));
--select * from haha for update
select name,
sum(case class
when 'oracle' then
cj
end) oracle,
sum(case class
when 'java' then
cj
end) java,
sum(case class
when 'HTML' then
cj
end) HTML
from haha
group by name;
select * from haha
生成唯一序列好：uuid（）：不固定
多行函数
sum：和
count:计数
max:最大
min:最小
avg:平均
多行函数不处理null值
计数：count(1) ;count(*)
多行函数的作用是用来统计分析，每次统计相当于对原始数据进行压缩提炼，会改变原表的结构。
count被limit影响：
SELECT COUNT(1) FROM emp LIMIT 3;
SELECT FOUND_ROWS();
分组语句：分组的作用是细化统计的项目，让统计数据更精准。
mysql中规定：select中的字段，可以不一定 出现在group by中，如果这种情况，随机取个值
但是5.7之后的版本中多个show variables like '%sql_mode%'命令，不能够影响分组，规定应该跟其他数据库一样同步
https://blog.csdn.net/li1325169021/article/details/78344083
group by：分组条件
having: 能够处理分组函数条件的条件子句
having写条件的前提是：使用的条件，必须再select中有的字段才可以再having中使用或者必须使用了多行函数的表达式作为条件
SELECT [distinct] | * |{列名 | 表达式 [列别名][,... ]}
FROM 表名
[where {列名|表达式 比较运算符 列名|表达式} [逻辑运算符 ...] ]
[group by 列名|表达式 [,...][asc][desc]]
[having （包含分组函数的）条件表达式]
[order by 列名|表达式|列别名 [asc] [desc] [,...] ];
语句执行顺序：
from
where
group by
having
select
order by
--函数嵌套
-- 单行嵌单行
select concat(concat('a','b'),'c') from dual;
-- 单行嵌多行
select round(avg(sal)) from emp;
-- 多行嵌单行
select avg(round(sal)) from emp;
-- 多行嵌多行
select avg(count(1)) from emp group by deptno
group_concat
select sex,age,count(*),group_concat(name) from student group by sex;
[图片上传失败...(image-54b9f7-1550702731218)]
mysql> select count(),sage from age group by sage with rollup;
+----------+------+
| count() | sage |
+----------+------+
| 2 | 20 |
| 3 | 21 |
| 3 | 22 |
| 8 | NULL |
+----------+------+
group by with rollup
如果想在下面这个表下面添加一行 总计 数据行SQL代码怎么实现 并且根据9月金额进行城市降序 总计置于底部呢
MySQL提供了 group by with rollup 函数进行group by 字段的汇总
但是order by 互斥的不能同时用
第一步还是是先计算各城市每个月的金额
SELECT b.城市,SUM(IF(b.年月=201607,b.金额,NULL)) AS 7月金额,SUM(IF(b.年月=201608,b.金额,NULL)) AS 8月金额,SUM(IF(b.年月=201609,b.金额,NULL)) AS 9月金额
FROM (
SELECT city AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金额
FROM test_a03order AS a
GROUP BY city,DATE_FORMAT(order_time,"%Y%m")
) AS b
GROUP BY b.城市
第二步我们先用group by with rollup 函数添加针对字段的汇总
SELECT b.城市,SUM(IF(b.年月=201607,b.金额,NULL)) AS 7月金额,SUM(IF(b.年月=201608,b.金额,NULL)) AS 8月金额,SUM(IF(b.年月=201609,b.金额,NULL)) AS 9月金额
FROM (
SELECT city AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金额
FROM test_a03order AS a
GROUP BY city,DATE_FORMAT(order_time,"%Y%m")
) AS b
GROUP BY b.城市 WITH ROLLUP
第三 添加总计字样(有坑) ifnull()函数是将空字段另外命名
SELECT IFNULL(b.城市,"总计") AS 城市,SUM(IF(b.年月=201607,b.金额,NULL)) AS 7月金额,SUM(IF(b.年月=201608,b.金额,NULL)) AS 8月金额,SUM(IF(b.年月=201609,b.金额,NULL)) AS 9月金额
FROM (
SELECT city AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金额
FROM test_a03order AS a
GROUP BY city,DATE_FORMAT(order_time,"%Y%m")
) AS b
GROUP BY b.城市 WITH ROLLUP
第四 摆脱掉坑
为什么说有坑呢 如果ifnull()函数放在上面代码位置 看似实现了总计的字样 ifnull()是针对用了with rollup 函数总计这个位置出现空字段时候修改它为总计字样的,
如果城市这一列里本身就含有没有命名的城市呢 这一列就会出现多个总计字样 因此我们需要将ifnull()函数放到嵌套的子表里 将空字段在子表里面就预先改成别名（不能是总计字样）
因此 使用2个ifnull()函数就不会有这样的问题 结果是一样的
SELECT IFNULL(b.城市,"总计") AS 城市,SUM(IF(b.年月=201607,b.金额,NULL)) AS 7月金额,SUM(IF(b.年月=201608,b.金额,NULL)) AS 8月金额,SUM(IF(b.年月=201609,b.金额,NULL)) AS 9月金额
FROM (
SELECT IFNULL(city,'空城市') AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金额
FROM test_a03order AS a
GROUP BY city,DATE_FORMAT(order_time,"%Y%m")
) AS b
GROUP BY b.城市 WITH ROLLUP
第五 排序
因此 如果想实现 根据9月金额进行城市降序 总计置于底部 效果 可以把上面代码当成一个子表嵌套 结合 order by field()自定义函数实现
SELECT c.*
FROM (
SELECT IFNULL(b.城市,"总计") AS 城市,SUM(IF(b.年月=201607,b.金额,NULL)) AS 7月金额,SUM(IF(b.年月=201608,b.金额,NULL)) AS 8月金额,SUM(IF(b.年月=201609,b.金额,NULL)) AS 9月金额
FROM (
SELECT IFNULL(city,'空城市') AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金额
FROM test_a03order AS a
GROUP BY city,DATE_FORMAT(order_time,"%Y%m")
) AS b
GROUP BY b.城市 WITH ROLLUP
) AS c
ORDER BY FIELD(城市,'总计'),c.9月金额 DESC
子查询
--分步查询
select sal from emp where ename ='JONES';
SELECT ENAME FROM EMP WHERE SAL>2975.00;
--多表连接
SELECT B.ENAME
FROM EMP A, EMP B
WHERE A.ENAME ='JONES' AND a.sal <b.sal;
--子查询
SELECT ENAME
FROM EMP
WHERE SAL > (select sal from emp where ename = 'JONES');
--多行子查询
in， any ，all ，some (等于any)
any：翻译是一些的意思
--多列子查询
where中多个条件绑定判断需要通过多列子查询处理。
--NOT IN 空值问题
如果子查询的返回值中有null值，需要通过is not null处理空值。
FROM子查询：通过from子查询能够把一个子查询作为临时表供给主查询当数据源使用，在使用from子查询的时候一般需要给子查询起表别名，其中的分组函数起列别名。
from子查询的作用一般处理分组函数和非分组条件同时显示的问题。
rownum问题：
mysql 没有rownum所以
SELECT @rownum:=@rownum+1 AS rownum ,emp.*
FROM (SELECT @rownum:=0)a ,emp;
DML
DELETE
删除数据
delete 代替select * ,其他和查询一直
多表删除
DELETE empas,deptas FROM empas JOIN deptas
ON empas.deptno = deptas .deptno;
指定删除哪张表的数据：
DELETE empas FROM empas JOIN deptas
ON (empas.deptno = deptas .deptno)
INSERT
添加数据有两种：
添加主数据：
insert into 表 （列名[,...]） values{(值[,...])[ , ...]} ；
insert into 表 set {(列名 = 值 [ , ...]} ；
添加业务数据：
insert into 表 （列名[,...]） select语句（ 查询值 ）;
UPDATE
update语句分成两部分： 分为修改哪些数据？修改的数据内容为什么？
思路：根据“值等于”和“为什么”断句，分别书写两个查询语句再合成一个语句
insert语句一般和多表配合，update一般和子查询配合使用
注意
mysql的子查询跟其他数据库相比有一些小问题
1.不能修改通过表自己作为条件的的操作 ，（exists 或者join 或者from子查询解决）
2.from子查询的表需要给别名
--修改平均工资最低的部门的所有的做领导的人员 的工资为10部门平均工资
update emp a
set sal =
(select avg(sal) from emp where deptno = 10)
where empno in
(select empno
from emp b,
(select avg(sal) avgsal, deptno from emp group by deptno) d,
(select min(avg(sal)) minavgsal from emp group by deptno) c
where empno in (select distinct mgr from emp)
and b.deptno = d.deptno
and d.avgsal = c.minavgsal);
update emp a
set sal =
(select avg(sal) from emp where deptno = 10)
where exists (select 1
from emp b,
(select deptno
from emp
group by deptno
having avg(sal) = (select min(avg(sal))
from emp
group by deptno)) c
where a.empno = b.mgr
and a.deptno = c.deptno
and a.deptno = b.deptno)
--修改工资等级在3级的员工的领导的 工资为20部门的最低工资
update emp a
set sal =
(select min(sal) from emp where deptno = 20)
where empno in (select mgr
from emp, salgrade
where emp.sal between losal and hisal
and grade = 3)
--修改工作地点在芝加哥的部门中工资最低的人员的 岗位为他们所在部门工资最高的人员的岗位
update emp a
set job =
(select job
from emp b
where a.deptno = b.deptno
and b.sal = (select max(sal) from emp where deptno = b.deptno))
where EXISTS (SELECT 1
FROM DEPT B
WHERE A.DEPTNO = B.DEPTNO
AND LOC = 'CHICAGO')
AND EXISTS (SELECT 1
FROM EMP B
WHERE A.DEPTNO = B.DEPTNO
AND A.SAL < B.SAL HAVING count(1) <= 0)
update emp a
set job =
(select job
from emp,
dept,
(select min(sal) maxsal, deptno from emp group by deptno) c
where emp.deptno = dept.deptno
and loc = 'CHICAGO'
and c.deptno = dept.deptno
and c.maxsal = emp.sal)
where empno in
(select empno
from emp,
dept,
(select min(sal) maxsal, deptno from emp group by deptno) c
where emp.deptno = dept.deptno
and loc = 'CHICAGO'
and c.deptno = dept.deptno
and c.maxsal = emp.sal);
多表更新
Mysql独有，但是Oracle不支持
跟多表连接类似
UPDATE emp,emptest SET emp.sal =100 ,emptest.sal = 1000 WHERE emp.ename = emptest.ename;
事务：一组sql语句（一般对应前台的一个业务操作），从第一个当前对话或者连接中运行的dml语句开始到第一个tpl语句结束。
作用：保证数据的一致性。
特性：原子性，一致性，持久性，隔离性。
隔离性是通过锁来实现的。
mysql 默认自动提交
autocommit ;
一系列将要发生或正在发生的连续操作。
而事务安全，是一种保护连续操作同时实现（完成）的机制。事务安全的意义就是，保证数据操作的完整性。
-- 开启事务
start transaction;
-- 查询自动事务
show variables like 'autocommit';
set autocommit = off / 0;
-- 设置全局级别变量
set global autocommit = 0;
实际上，MySQL 使用的默认存储引擎是 InnoDB，而 InnoDB 默认使用的锁机制是 行锁（锁住操作的当前行），但是如果在事务操作的过程中，我们没有使用索引字段，那么系统就会自动进行全表检索，也就是其自动将行锁升级为 表锁（锁住操作的当前表）。
DDL
数据库对象分为：表，约束，索引，视图，序列，同义词，（函数，过程，触发器）等。
定义和操作数据库对象的语句为ddl语句：
create drop truncate alter
数据库对象命名：字母开头，（数字，字母，下划线，$,#）组成。
数据类型
任何一条记录最长不超过 65535 个字节，这意味着varchar永远达不到理论最大值。
int(N)我们只需要记住两点：
无论N等于多少，int永远占4个字节
N表示的是显示宽度，不足的用0补足，超过的无视长度而直接显示整个数字，但这要整型设置了unsigned zerofill才有效
create table test_int_width (
a int(5),
b int(5) unsigned,
c int(5) unsigned zerofill,
d int(8) unsigned zerofill
) engine=innodb charset=utf8;
D表示浮点型数据小数点之后的精度，假如超过D位则四舍五入，即1.233四舍五入为1.23，1.237四舍五入为1.24
M表示浮点型数据总共的位数，D=2则表示总共支持五位，即小数点前只支持三位数，所以我们并没有看到1000.23、10000.233、100000.233这三条数据的插入，因为插入都报错了
定点型
float/double在db中存储的是近似值，而decimal则是以字符串形式进行保存的
decimal(M,D)的规则和float/double相同，但区别在float/double在不指定M、D时默认按照实际精度来处理而decimal在不指定M、D时默认为decimal(10, 0)
datetime与timestamp两种类型的区别：
上面列了，datetime占8个字节，timestamp占4个字节
由于大小的区别，datetime与timestamp能存储的时间范围也不同，datetime的存储范围为1000-01-01 00:00:00——9999-12-31 23:59:59，timestamp存储的时间范围为19700101080001——20380119111407
datetime默认值为空，当插入的值为null时，该列的值就是null；timestamp默认值不为空，当插入的值为null的时候，mysql会取当前时间
datetime存储的时间与时区无关，timestamp存储的时间及显示的时间都依赖于当前时区
考虑将行数据的创建时间和最后更新时间记录下来是很好的实践。尤其是可能需要做数据同步或者对数据新鲜度有要求的表。举些应用场景，更新距上次更新超过2小时的行数据，或者是将一个月前的订单数据归档等等。我们想把这个的需求丢给数据库服务器管理，而不是在应用程序中对每一条语句设置创建时间和最后更新时间字段。在mysql中，这实现起来很容易。我们需要借助于DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP
create_time timestamp default current_timestamp comment "创建时间",
update_time timestamp default current_timestamp on update current_timestamp comment "修改时间",
char和varchar类型
关于char和varchar的对比，我总结一下：
char是固定长度字符串，其长度范围为0255且与编码方式无关，无论字符实际长度是多少，都会按照指定长度存储，不够的用空格补足；varchar为可变长度字符串，在utf8编码的数据库中其长度范围为021844
char实际占用的字节数即存储的字符所占用的字节数，varchar实际占用的字节数为存储的字符+1或+2或+3
MySQL处理char类型数据时会将结尾的所有空格处理掉而varchar类型数据则不会
MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。
MEMORY存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理的。
InnoDB 存储引擎和数据列：建议使用 VARCHAR类型。
text和blob两种数据类型，它们的设计初衷是为了存储大数据使用的，因为之前说了，MySql单行最大数据量为64K。
先说一下text，text和varchar是一组既有区别又有联系的数据类型，其联系在于当varchar(M)的M大于某些数值时，varchar会自动转为text：
M>255时转为tinytext
M>500时转为text
M>20000时转为mediumtext
所以过大的内容varchar和text没有区别，同事varchar(M)和text的区别在于：
单行64K即65535字节的空间，varchar只能用63352/65533个字节，但是text可以65535个字节全部用起来
text可以指定text(M)，但是M无论等于多少都没有影响
text不允许有默认值，varchar允许有默认值
varchar和text两种数据类型，使用建议是能用varchar就用varchar而不用text（存储效率高），varchar(M)的M有长度限制，之前说过，如果大于限制，可以使用mediumtext（16M）或者longtext（4G）。
至于text和blob，简单过一下就是text存储的是字符串而blob存储的是二进制字符串，简单说blob是用于存储例如图片、音视频这种文件的二进制数据的。
binary 与 varbinary 类型和char与varchar类型是相似的，只是他们存储的是二进制数据，也就是说他们是包含字节流而不是字符流，他们有二进制字符的集合和顺序，他们的对比，排序是基于字节的数值进行的
binary与varbinary的最大长度和char与varchar是一样的，只不过他们是定义字节长度，而char和varchar对应的是字符长度
ENUM只能取单值，它的数据列表是一个枚举集合。它的合法取值列表最多允许有65535个成员。例如：ENUM("N","Y")表示，该数据列的取值要么是"Y"，要么就是"N"。
SET可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。
数据类型：
varchar2对比char ，能够节省空间，可变长度；缺点是速度比char慢。如果是性别类型的字段最好用char，varchar2适合如名字这样的字段。
number：可以不跟长度，但是最好是有长度的。（如果是两个参数，第一个数总长度，第二个是小数长度）
clob和blob：4g大小的字段，一个是字符，一个是图片
drop table：删除表的结构和数据，可以还原
delete ：删除某些数据，可以还原
truncate table：删除全部数据不能还原
alter table： 修改表的结构。
约束
--定义约束
--列级约束
create table Person(
name varchar2(50) not null,
sex char(1) constraint sex_person_ck check ( sex in ('1','2','0')),
id char(18) constraint id_person_uk unique,
dna char(50) constraint dna_person_pk primary key,
classid varchar2(10) constraint classid_Person_class_fk references class(classid)
)
--追加约束
--表级约束
create table class(
classid varchar2(10) ,
classcount number ,
classname varchar2(10) not null,
constraint classid_class_pk primary key (classid),
constraint classcount_class_ck check (classcount>0)
)
alter table Person add constraint classid_Person_class_fk foreign key (classid)
references class(classid)
视图
create or replace view haha
as
select * from emp
where deptno = 10
with check option constraint hahacon
;
or replace 是替换的意思
with check option 根据视图的查询条件做约束
视图分为：简单和复杂
复杂视图一般包含多个表，分组，函数等。
复杂视图根据实际情况看是否能进行dml操作，简单视图可以做dml操作。
with read only:只读视图
索引
索引分类
1.普通索引index :加速查找
2.唯一索引
主键索引：primary key ：加速查找+约束（不为空且唯一）
唯一索引：unique：加速查找+约束 （唯一）
3.联合索引
-primary key(id,name):联合主键索引
-unique(id,name):联合唯一索引
-index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用
1 举个例子来说，比如你在为某商场做一个会员卡的系统。
2
3 这个系统有一个会员表
4 有下列字段：
5 会员编号 INT
6 会员姓名 VARCHAR(10)
7 会员身份证号码 VARCHAR(18)
8 会员电话 VARCHAR(10)
9 会员住址 VARCHAR(50)
10 会员备注信息 TEXT
11
12 那么这个 会员编号，作为主键，使用 PRIMARY
13 会员姓名 如果要建索引的话，那么就是普通的 INDEX
14 会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）
15
16 #除此之外还有全文索引，即FULLTEXT
17 会员备注信息 ， 如果需要建索引的话，可以选择全文搜索。
18 用于搜索很长一篇文章的时候，效果最好。
19 用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
20 但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。
21
22 #其他的如空间索引SPATIAL，了解即可，几乎不用
条件
比较运算符：>、<、>=、<=、<>、=、like、between and、in和not in；
逻辑运算符：&&、||、和!.
第一种情况：主键冲突，进行更新操作。
基本语法：insert into + 表名 + [(字段列表：包含主键)] + values (值列表) on duplicate key update 字段 = 新值;
-- 当主键冲突的时候，进行更新操作
insert into my_class values ('PM3527','B315')
-- 冲突处理
on duplicate key update
-- 更新主键值
room = 'B315';
第二种情况：主键冲突，选择替换操作。
基本语法：replace insert into + 表名 + [(字段列表：包含主键)] + values (值列表);
关键字: on
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在使用left jion时，on和where条件的区别如下：
1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
在使用INNER JOIN时会产生一个结果集，WHERE条件在这个结果集中再根据条件进行过滤，如果把条件都放在ON中，在INNER JOIN的时候就进行过滤了，比如
SELECT *
FROM A
INNER JOIN B
ON B.ID = A.ID
AND B.State = 1
INNER JOIN C
ON B.ID = C.ID
在联查B表时，就把状态不等于1的忽略掉了，这样对于状态不等于1的就不需要去联查C表了
而
SELECT *
FROM A
INNER JOIN B
ON B.ID = A.ID
INNER JOIN C
ON B.ID = C.ID
WHERE B.State = 1
则不管B的状态是否满足，都去联查C，最后再将B状态满足的查出来。
这样一分析，得出的结论就是inner join on 比直接where的查询效率要高。
inner join on 后面的条件已经把结果过滤了一遍，而where 则是把限制条件放到最后，执行最后一次查询前结果里值变多了，查询起来变慢了，效率自然变低了。
使用on
Select * from a left join b on b.col = a.col and b.col2 = ‘aa’
使用 where
Select * from a left join b on b.col = a.col where b.col2 = ‘aa’ and b.col2 is null
分析
1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
用户
1.权限表
MYSQL是一个多用户的数据库，MYSQL的用户可以分为两大类：
（1） 超级管理员用户（root），拥有全部权限
（2） 普通用户，由root创建，普通用户只拥有root所分配的权限
1.1 权限表的位置
数据库：mysql
与权限相关的数据表：user,db,host,tables_priv,columns_priv，procs_priv等
1.2 user表
User表存储了：
（1）用户的信息：hots（用户所在的主机）,user（用户名）,password（密码）
（2）用户的权限信息：_priv
（3）安全性相关的信息：ssl_,x509,记录用户登录的验证操作等
（4）与资源分配相关的信息：max_，
max_questions表示用户在一个小时内最多可以进行的查询次数。
max_updates表示用户在一个小时内最多可以进行的更新次数，也包括增加数据、删除数据。
Max_connections：表示用户最多可以建立的连接数
1.2.1 user表的内容
（1）host列
Localhost表示本机的用户或者超级管理员
%表示任何主机上的root用户
说明：_priv权限是全局权限，不受数据库的范围限制
1.3 DB表
（1）与用户相关的字段：hots（用户所在的主机）,user（用户名）,
（2）与权限相关的字段：_priv，DB字段规定了_priv权限的有效范围。
1.4 host表
（1）与用户相关的字段：hots（用户所在的主机）
（2）与权限相关的字段：_priv，DB字段规定了_priv权限的有效范围。
说明：
（1） 记录主机上的用户对数据库拥有的权限，侧重点在主机，而不在用户，例如假设select_priv=Y,那个这个主机上的所有数据库用户都拥有select权限。
（2） Host表的优先级大于db表，如果db表规定这个用户没有权限，但是host表规定了这台主机的用户有权限，那么db的这个用户也是拥有权限的。
1.5 tables_priv表
设定了用户对某个表拥有的权限，该表记录了用户的信息，以及某个表的权限信息table_priv(select ,lnsert,alter等等)，以及表上的某个列的权限信息column_priv。
1.6 column_priv表
记录某用户对某表的某个列所拥有的权限。
1.7procs_priv 表
规定了用户关于存储过程及存储函数的操作权限，主要字段：proc_priv
- 创建数据库用户
2.1 创建普通用户
2.1.1 CREATE USER
CREATE USER ‘用户名称’ [@’主机名称’]
例：CREATE USER 'user1';
验证是否创建成功：
mysql> SELECT user FROM mysql.user;
+-------+
| user |
+-------+
| user1 |
| root |
| root |
| |
| pma |
| root |
+-------+
6 rows in set (0.00 sec) 说明新创建的用户已经进入user表内
说明：使用CREATE USER来创建的用户，均无任何权限，user表的权限字段的值均为N
（1）创建带有主机名的用户
CREATE USER ‘用户名称’ [@’主机名称’] [INDENTIFIED BY ‘用户密码’]
CREATE USER 'user2' @'localhost';
mysql> select user,host from mysql.user;
+-------+---------------------+
| user | host |
+-------+---------------------+
| user1 | % |
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| pma | localhost |
| root | localhost |
| user2 | localhost |
+-------+---------------------+
7 rows in set (0.00 sec)
说明：host字段的%表示不受任何主机的限制
（2）创建带密码的用户
CREATE USER 'user3' @'localhost' [INDENTIFIED BY ‘用户密码’];
例子：
CREATE USER 'user3' @'localhost' IDENTIFIED BY '123333';
验证：
mysql> SELECT user,password,host FROM mysql.user;
+-------+-------------------------------------------------------------------------+-----------+
| user | password | host |
+-------+-------------------------------------------------------------------------+-----------+
| root | | localhost |
| root | | 127.0.0.1 |
| root | | ::1 |
| | | localhost |
| pma | | localhost |
| user1 | | % |
| user2 | | localhost |
| user3 | *0166E21E66009700F528CA21179AF9AD81120DA2 | localhost |
+-------+-------------------------------------------------------------------------+-----------+
8 rows in set (0.00 sec)
说明：密码是哈希码的形式显示的
2.1.2 使用GRANT来创建用户，以及授予权限
GRANT是用来给用户授权的，但是也可以用来创建用户，GRANT在给用户进行授权的时候，如果用户是不存在的，那么GRANT会自动创建这个用户，然后再给这个用户进行授权。
(1)添加权限
grant 权限 on 数据库.表 to '用户名'@'登录主机' [INDENTIFIED BY ‘用户密码’];
权限： select ,update,delete,insert(表数据)、create,alert,drop(表结构)、references(外键)、create temporary tables(创建临时表)、index(操作索引)、create view,show view(视图)、create routine,alert routine,execute(存储过程)、all,all privileges(所有权限)
数据库：数据库名或者*(所有数据库)
表：表名或者(某数据库下所有表)，.*表示所有数据库的所有表
主机:主机名或者%(任何其他主机)
例：grant selec,insert,update,delete on . to 'jifei'@'%';
GRANT SELECT ON . TO 'user4' @'localhost' IDENTIFIED BY '123333';
mysql> SELECT user,password,host FROM mysql.user;
+-------+-------------------------------------------------------------------------------+-----------+
| user | password | host |
+-------+-------------------------------------------------------------------------------+-----------+
| root | | localhost |
| root | | 127.0.0.1 |
| root | | ::1 |
| | | localhost |
| pma | | localhost |
| user1 | | % |
| user2 | | localhost |
| user3 | *0166E21E66009700F528CA21179AF9AD81120DA2 | localhost |
| user4 | *0166E21E66009700F528CA21179AF9AD81120DA2 | localhost |
+-------+-------------------------------------------------------------------------------+-----------+
9 rows in set (0.00 sec)
(2) 为用户授予指定数据库、指定表、指定列的权限：
GRANT UPDATE(cid,cname) ON mysqlpart2.custom TO 'user3'@'localhost';
授权成功后，可以在以下表中查看到授权信息：
数据库: mysql » 表: tables_priv "Table privileges"
数据库: mysql » 表: columns_priv "Column privileges"
(3)用户权限表
位置：数据库: information_schema »表: USER_PRIVILEGES
表的说明：
GRANTEE:授权者
PRIVILEGE_TYPE:权限名称
用户表：数据库: mysql »表: user "Users and global privileges"
说明：user表中，”_priv”的值域USER_PRIVILEGES表的PRIVILEGE_TYPE的值是一一对应的。
（4）权限的层级关系
①权限的层级关系，就是指权限的适用范围。
②权限的最高层级是全局级，所谓全局级就是可以在任何数据库的任何数据表上进行操作。
③数据库级：只能在某个数据库上进行操作。
④表级：权限信息所在位置：数据库: mysql »表: tables_priv "Table privileges"
⑤列级：权限信息所在位置：数据库: mysql »表: columns_priv "Column privileges"
⑥子程序级：权限信息所在位置：数据库: mysql »表: procs_priv "Procedure privileges"
（5）撤销权限
REVOKE 权限 ON 数据库.表 FROM '用户名'@'登录主机;
说明：赋权与撤销权限的区别，就是REVOKE是将to改为from
例：revoke all on . from ‘jifei’ @’%’;
REVOKE UPDATE(cid,cname) ON mysqlpart2.custom FROM 'user3'@'localhost';
(6) 查看权限
SHOW GRANTS;//自己
SHOW GRANTS FOR 用户名称@主机名称；
例：
SHOW GRANTS FOR dba@localhost;//指定用户指定host
mysql> SHOW GRANTS FOR user3@localhost;
+---------------------------------------------------------------------------------------------------------------+
| Grants for user3@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON . TO 'user3'@'localhost' IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD' |
| GRANT UPDATE (cname, cid) ON mysqlpart2.custom TO 'user3'@'localhost' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
说明：所有SHOW关键字后面的词都是复数，所有CREATE关键字后面的词都是单数
通过mysql.columns_priv表来查看权限：
SELECT * FROM mysql.columns_priv WHERE user='user3' AND host='localhost';
mysql> SELECT * FROM mysql.columns_priv WHERE user='user3' AND host='localhost'\G
*************************** 1. row ***************************
Host: localhost
Db: mysqlpart2
User: user3
Table_name: custom
Column_name: cid
Timestamp: 0000-00-00 00:00:00
Column_priv: Update
*************************** 2. row ***************************
Host: localhost
Db: mysqlpart2
User: user3
Table_name: custom
Column_name: cname
Timestamp: 0000-00-00 00:00:00
Column_priv: Update
2 rows in set (0.00 sec)
1.1.1 用户的定义
用户名+主机域
mysql> select user,host,password from mysql.user;
+--------+------------+-------------------------------------------+
| user | host | password |
+--------+------------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 127.0.0.1 | |
| znix | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| clsn | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 10.0.0.1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+--------+------------+-------------------------------------------+
6 rows in set
1.1.2 用户的作用
1、用户登录
2、用于管理数据库及数据
1.1.3 连接数据库
定义用户：用户名+主机域，密码
定义权限：对不同的对象进行权限(角色)定义
命令:
grant 权限 on 权限范围 to 用户 identified by '密码'
权限
对数据库的读、写等操作
（insert update、select、delete、drop、create等）
角色
数据库定义好的一组权限的定义
（all privileges、replication slave等）
权限范围
全库级别： *.*
单库级别：clsn.*
单表级别：clsn.t1
用户
'clsn'@'localhost' 本地
'clsn'@'192.168.66.149'
'clsn'@'192.168.66.%'
'clsn'@'192.168.66.14%'
1.1.4 【练习题】按照要求创建用户
用户只能通过10.0.0.0/24网段访问，用户名为clsn 密码为123
这个用户只能对clsn数据库下的对象进行增insert create、改update 、查select；
创建命令：
grant select,create,insert,update on clsn.* to 'clsn'@'10.0.0.%' identified by '123';
查看用户权限
mysql> show grants for clsn@'172.16.1.%'\G
*************************** 1. row ***************************
Grants for clsn@172.16.1.%: GRANT USAGE ON . TO 'clsn'@'172.16.1.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
1 row in set (0.00 sec)
查看当前存在的用户:
select user,host from mysql.user;
创建用户语法
CREATE USER '用户'@'主机' IDENTIFIED BY '密码';
示例：
create user 'clsn'@'localhost' identified by 'clsn123';
注意这个样创建的用户只有连接权限
企业里创建用户一般是授权一个内网网段登录，最常见的网段写法有两种。
方法1：172.16.1.%（%为通配符，匹配所有内容）。
方法2：172.16.1.0/255.255.255.0，但是不能使用172.16.1.0/24，是个小遗憾。
标准的建用户方法：
create user 'web'@'172.16.1.%' identified by 'web123';
查看用户对应的权限
show grants for oldboy@localhost\G
1.1.5 用户删除
删除用户语法：
drop user 'user'@'主机域'
【练习】用户优化：只保留
| root | 127.0.0.1 |
| root | localhost |
特殊的删除方法:(慎用，尽量不要直接去修改表)
mysql> delete from mysql.user where user='clsn' and host='localhost';
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
1.1.6 用户授权
给用户授权
创建用户
create user 'clsn'@'localhost' identified by 'clsn123';
查看用户
select user,host from mysql.user;
授权所有权限给clsn用户
GRANT ALL ON . TO 'clsn'@'localhost';
查看clsn用户的权限
SHOW GRANTS FOR 'clsn'@'localhost'\G
创建用户的同时授权
grant all on . to clsn@'172.16.1.%' identified by 'clsn123';
刷新权限
flush privileges; #<==可以不用。
创建用户然后授权
create user 'clsn'@'localhost' identified by 'clsn123';
GRANT ALL ON . TO 'clsn'@'localhost';
授权和root一样的权限
grant all on . to system@'localhost' identified by 'clsn123' with grant option;
授权给用户select,create,insert,update 权限
grant select,create,insert,update on clsn.* to 'clsn'@'10.0.0.%' identified by '123';
回收权限
REVOKE INSERT ON . FROM clsn@localhost;
可以授权的用户权限
INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,
PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER,
CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE,
REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER
ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
【示例】博客授权收回示例
grant select,insert,update,delete,create,drop on blog.* to 'blog'@'172.16.1.%' identified by 'blog123';
revoke create,drop on blog.* from 'blog'@'172.16.1.%';
授权博客类的最多权限：select,insert,update,delete
jdbc配置字符串
https://dev.mysql.com/downloads/connector/j/
8.0
import com.mysql.cj.jdbc.Driver;
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/user?useSSL=false&serverTimezone=UTC", "root", "123456");
PreparedStatement ps = conn.prepareStatement("Select ename ,empno from emp where empno=?");
