数据库—MySQL基础_xustudyxu's blog

类型

全称

数据结构/主流产品

层次式数据库

HDBMS(Hierarchical Database Management System)

层次式数据库管理系统

有根结点的定向有序树

网络式数据库

NDBMS(Network Database Management System)

网络式数据库管理系统

网状数据结构

关系型数据库

RDBMS(Relational Database Management System)

关系型数据库管理系统

二元关系（即二维表格形式）。

主要有：Oracle（Oracle）、MySQL（Oracle）、SQL Server（MS）、DB2（IBM）、Sybase（SAP）、PostgreSQL、INFORMIX

非关系型数据库

NoSQL

主要分类：

键值存储：Redis、MemcacheDB

文档存储：MongoDB、Elastic Search

列存储：HBase、Cassandra

图关系存储：Neo4J

新关系型数据库

NewSQL

结合关系型和非关系的优点。

主要有：Google Spanner、OceanBase、TiDB

模式/映像

含义

外模式（用户级）

与某一应用有关的数据逻辑表示

外模式/模式映像

模式（概念级）

对数据库中全部数据的逻辑结构和特征的总体描述（DDL）

模式/内模式映像

内模式（物理级/存储级）

全体数据的内部表示（底层描述）

实体及其属性表示

实体关系表示

分类

用于

包含语句

事务

数据定义语言（DDL）

Data Definition Language

用于操作数据库对象

CREATE、ALTER、DROP、RENAME、SHOW TABLES、DESC等语句

不需要事务的参与，自动提交

数据操纵语言（DML）

Data Manipulation Language

用于操作数据库数据

INSERT、UPDATE、DELETE/truncate等语句

与事务相关，执行完DML操作后必须经过事务控制语句提交后才真正的将改变应用到数据库中

事务控制语言（TCL）

Transaction Control Language

用于维护数据一致性

提交(COMMIT)、回滚(ROLLBACK)、保存点(SAVEPOINT)三条语句

数据查询语言（DQL）

Data Query Language

select语句

数据控制语言（DCL）

Data Control Language

用于创建用户、控制访问权限等

授予(GRANT)语句，收回(REVOKE)语句，CREATE USER语句

不需事务参与，自动提交

CRUD

增 Create

删 Delete

改 Update

查 Read

数据库管理

create

drop

alter

show

表管理

create

drop

alter(add/drop/modify/change/rename)

show/desc

数据管理

insert

delete/truncate

update…set…

select

create,drop,alter,show管理数据库或表，truncate管理数据时后面要接database(s)或table关键字；

insert,delete,update,select管理数据，desc管理表时后面不要接table关键字。

MySQL

Oracle

登陆数据库

本地：mysql -u用户名 -p密码

远程：mysql [-h主机地址] [-P端口号] -u用户名 [-p密码]

注：

1. 使用mysql命令前需将MySQL的bin目录加入到环境变量。
2. -h、-P、-p为可选参数，如果未指定：

-h默认本机地址

-P默认3306，

-p未指定则会提示输入密码

1. -h和-P后面可以有空格，-p后面不能有空格

如遇报错：

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

解决：

管理员身份运行cmd，输入：mysqld -install

然后启动MySQL服务

mysql8登录如遇报2059错误，原因是8.0之后mysql更改了密码的加密规则，只要在命令窗口把加密方法改回去即可：

alter user root@localhost identified by 'root的密码' password expire never;

alter user root@localhost identified with mysql_native_password by 'root的密码';

flush privileges;

本地：sqlplus

远程：sqlplus 用户名/密码@ip:端口/服务名

退出数据库

exit

quit;

exit;

查看软件版本

select version();

查看数据库文件存储路径

select @@datadir;

查询所有数据库

show databases;

MySQL初始4个数据库：

information_schema：mysql元数据数据库。

mysql：mysql配置数据库。其中user表用于管理mysql用户信息。

performance_schema：--mysql性能监控信息数据库。

test：测试数据库。

查询当前数据库

select DATABASE()

选择数据库

use 库名

创建数据库并指定默认字符集

create database [if not exists] 库名 [default character set 字符集] [collate 排序规则];

修改数据库的字符集

alter database 数据库 default character set utf8;

查看数据库字符集

show variables like 'character%';

select userenv('language') from dual;

设置数据库字符集

set names utf8; 等同于下面三句：

设置客户端字符集：set character_set_client=utf8;

设置连接字符集：set character_set_connection=utf8;

设置输出字符集：set character_set_results=utf8;

注：

1）修改cmd当前代码页为utf-8：chcp 65001

2）mysql有六处使用了字符集：

| character_set_client |客户端使用的默认字符集

| character_set_connection |连接数据库的默认字符集

| character_set_database |数据库使用的默认字符集

| character_set_filesystem |

| character_set_results |返回结果集的默认字符集

| character_set_server |服务器安装时指定的默认字符集

| character_set_system |数据库系统使用的字符集

| character_sets_dir |

创建数据库并指定默认校验规则

create database 库名 collate utf8_general_ci;

查看数据库的创建语句和字符集

show create database 库名;

查看常用的字符串集的校验规则

show character set;

校验规则：一个字符集可以同时存在多种校验规则。

使用A校验规则：取字符的ascii码值比较大小: a<b（97<98）

使用B校验规则：取字符的ascii码值的负数比较大小:a>b（-97<-98）

删除数据库

drop database [if exists] 数据库;

【语法】

CREATE TABLESPACE 表空间名

   DATAFILE '数据文件路径' SIZE 大小

   [AUTOEXTEND ON] [NEXT 大小]

   [MAXSIZE 大小];

【说明】

表空间名会自动转为大写

数据文件路径中若包含目录需要先创建

SIZE为初始表空间大小，要带上单位（K或M）

AUTOEXTEND ON 开启自动扩展

NEXT为文件满了后扩展大小

MAXSIZE为文件最大大小，值为数值或UNLIMITED（表示不限大小）

--管理员角色查看表空间

SELECT file_name,tablespace_name,bytes,autoextensible

FROM dba_data_files

WHERE tablespace_name='ITCAST_TS'; --需按大写查询

【注意】表空间名需全部大写

ALTER TABLESPACE 表空间名

   ADD DATAFILE '文件路径' SIZE 大小

   [AUTOEXTEND ON] [NEXT 大小]

   [MAXSIZE 大小];

DROP TABLESPACE 表空间名; -- 只删除表空间

DROP TABLESPACE 表空间名 INCLUDING CONTENTS AND DATAFILES; --删除表空间及数据文件

用户

说明

sys

超级用户，主要用来维护系统信息和管理实例，以SYSDBA或SYSOPER角色登录。密码为在安装时设置的管理口令，如一般设置为：orcl

system

默认的系统管理员，拥有DBA权限，通常用来管理Oracle数据库的用户、权限和存储，以Normal方式登录。密码为在安装时设置的管理口令，如一般设置为：orcl

scott

示范用户，使用users表空间。一般该用户默认密码为tiger

MySQL

Oracle

查询用户

mysql的用户信息保存在mysql.user表中

select * from mysql.user

select * from all_users;

或

select * from dba_users; --更详细的用户信息

查看当前用户

show user

解锁用户

ALTER USER 用户名 ACCOUNT UNLOCK;

锁定用户

ALTER USER 用户名 ACCOUNT LOCK;

创建用户

CREATE USER '用户名'@'主机名'IDENTIFIED BY '密码'

注1：新创建的用户需要赋予权限才能访问表

注2：主机名为%时表示任何主机

CREATE USER 用户名 IDENTIFIED BY 密码

DEFAULT TABLESPACE 表空间名

[TEMPORARY TABLESPACE 临时表空间名];

【注】新创建的用户需要赋予权限才能登录

查看用户密码

select PASSWORD('root');

-- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

删除用户

方法一：drop user 用户名@主机名;

方法二：DELETE FROM mysql.user WHERE User='用户名' and Host='主机名';

DROP USER 用户名 CASCADE;

修改用户密码

5.7之前版本：

①set password for twx@localhost = password('123456');

②update mysql.user set password=password('123456') where user="twx" and host = "localhost";

5.7版本：

①update mysql.user set authentication_string=password('123456') where user="twx" and host="localhost";

8.0以上版本：

①alter user 'twx'@'localhost' identified [with mysql_native_password] by 'root';

②set password for twx@localhost = '123456';

【语法】

ALTER USER 用户名 identified by 密码

修改Oracle的sys或system用户密码

运行cmd：

键入“sqlplus/nolog” 回车

键入“conn/as sysdba” 回车

键入“alter user system identified by 要改成的密码;” 回车

重置密码前要先开启服务，不然执行不了。

切换用户

conn 用户名/密码

以管理员身份登录：conn system as sysdba

MySQL实现远程登录二法：

1、授权法（增加用户）：

use mysql;

grant all privileges on *.* to root@'%' identified by "password";

2、改表法（修改用户）：

use mysql;

update user set host = '%' where user = 'root' and host='localhost';

Oracle密码过期，取消密码180天限制

1、进入sqlplus模式

sqlplus / as sysdba;

2、查看用户密码的有效期设置(一般默认的配置文件是DEFAULT)

SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

3、将密码有效期由默认的180天修改成“无限制”，修改之后不需要重启动数据库，会立即生效

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

4、帐户再改一次密码

alter user 用户名 identified by 原密码;

5、使用修改后的用户登录，如果报“ORA-28000:用户已被锁”，解锁

alter user db_user account unlock;

commit;

MySQL

Oracle

授予

GRANT 权限列表 ON 库名.表名 TO 用户名@主机名 [IDENTIFIED BY '新密码'] [WITH GRANT OPTION];

注一：多个权限用英文逗号分割，常用的权限有：all或all privileges、select、insert、update、delete、alter、drop、create,默认有usage权限。

注二：任意库名、任意表名用*表示，任意主机名用%表示。

注二：with grant option表示允许用户将自己的权限授权给其它用户

注三：授权后若未生效，可刷新权限使生效：flush privileges;

-------------------------------------------------------

【语法1】

GRANT 角色 TO 用户;

【示例1】

--授予CONNECT和RESOURCE两个角色

GRANT connect, resource TO itcast;

【备注1】使用如下语句可以查看resource角色下的权限

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE'

【语法2】

GRANT 操作 ON 模式.对象 TO 用户;

【示例2】

--允许用户查看、更新 EMP 表中的记录

GRANT select,update ON SCOTT.emp TO  itcast;

【查询角色、权限】

--查看当前用户的系统权限

select * from user_sys_privs;

--查看当前用户的对象权限

select * from user_tab_privs;

--查看当前用户的所有角色

select * from user_role_privs;

撤销

REVOKE 权限列表 ON 库名.表名 FROM 用户名@主机名

【语法1】

REVOKE 角色权限（角色）[,角色权限] FROM 用户;

【示例1】

--撤销CONNECT和RESOURCE两个角色

REVOKE connect, resource FROM itcast;

【语法2】

REVOKE 操作 ON 模式.对象  FROM 用户;

【示例2】

--撤销用户查看、更新 EMP 表中的记录的操作

REVOKE select,update ON SCOTT.emp FROM  itcast;

查询

show grants for 用户名@主机名;

分类

MySQL

Oracle

数值类型

bit(位数)：位类型。位数默认值1，范围1-64

tinyint：大小1byte，有符号范围-128~127

bool或boolean：使用0或1表示真或假

smallint：大小2byte，有符号范围正负3万多

mediumint：大小3byte，有符号范围正负8百多万

int(最小显示位数)：大小4byte，有符号范围正负21亿多，最小显示位数用于限定zerofill样式

bigint：大小8byte，有符号范围正负九百多亿亿

float[(精度, 标度)]

double[(精度, 标度)]

decimal[(精度, 标度)]：精度默认10，标度默认0

number

number[(精度[, 刻度])]

精度的范围从1到38，刻度的范围从-84到127

例：

NUMBER 表示使用默认值NUMBER(5)

NUMBER(5,2) 表示整数部分最大3位，小数部分为2位；

NUMBER(5,-2) 表示数的整数部分最大为7其中对整数的倒数2位为0,前面的取整。

float

存储二进制数据，精度为二进制的1-126，转十进制时需乘以0.30103

字符串（定长/变长）

char(字符数)：定长字符串，最大可指定255

varchar(字符数)：变长字符串，最大容量65535B（64KB），utf8mb4编码时最大可指定16383

tinytext或tinyclob：小文本数据，最大255B

text或clob：大文本数据，最大64KB（65535B）

mediumtext或mediumclob：更大文本数据，最大16MB

longtext或longclob：超大文本数据，最大4GB

char[(大小)] 定长字符串，最大2000字节，默认1

NCHAR(大小)  定长字符串，根据Unicode标准定义长度

VARCHAR2(大小)  变长字符串。最大4000字节

NVARCHAR2(大小) 变长字符串，依据所选国家字符集。最大4000字节

long 可变长度的字符数据,最大2G字节（少用）

CLOB 大文本数据，最大4G字节。可容纳单字节字符。不支持不等宽字符集

NCLOB 大文本数据，依据所选国家字符集。最大4G字节。可容纳单字节字符。不支持不等宽字符集

二进制

tinyblob：小的二进制数据，最大255B

blob ：大的二进制数据，最大64KB（65535B）

mediumblob：更大的二进制数据，最大16MB

longblob：超大的二进制数据，最大4GB

RAW(大小) 二进制数据，最大2000字节

LONG RAW 变长二进制数据，最大2G字节

BLOB 大的二进制数据，最大4G字节

BFILE 大型二进制文件定位器，目标文件最大4G字节，通过字符流访问

日期时间

date ：大小3B，格式YYYY-MM-DD

time：大小3B，格式HH:MM:SS

year：大小1B，格式YYYY

datetime：大小8B，格式YYYY-MM-DD HH:MM:SS

timestamp ：大小4B，格式YYYY-MM-DD HH:MM:SS（可自动记录insert、update的时间，有2038年问题）

DATE 有效范围从BC 4712-01-01到 AD 9999-12-31

timestamp

MySQL

Oracle

查询表

查看所有表

show tables

select * from tabs;

select table_name from tabs;

查看表结构

desc 表名;

show columns from 表名;

describe 表名;

desc 表名;（只能写在命令窗口中）

查看建表语句

show create table 表名;

创建表

【员工表（主表/参考表）】：

CREATE TABLE employee(

id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '主键',

name VARCHAR(20) NOT NULL COMMENT '姓名',

id_card CHAR(18) UNIQUE COMMENT '身份证',

age TINYINT CHECK(age > 0 && age <= 120) COMMENT '年龄',

e_id INT(8) ZEROFILL COMMENT '工号',

create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

PRIMARY KEY(id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '员工表';

【员工考勤表（副表/从表）】：

CREATE TABLE employee_sign(

id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '主键',

employee_id INT COMMENT '员工编号',

sign_in TIME DEFAULT NULL COMMENT '签到时间',

sign_out TIME DEFAULT NULL COMMENT '签退时间',

create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

PRIMARY KEY(id),

[CONSTRAINT] sign_emloyee_fk FOREIGN KEY(employee_id) REFERENCES employee(id) [ON update CASCADE] [ON delete CASCADE]

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '员工签到表';

【约束说明】：

UNSIGNED 无符号（指定非负数可增加数据长度）

ZEROFILL 零填充

PRIMARY KEY 主键（非空+唯一）

PRIMARY KEY(字段1, 字段2) 联合主键

AUTO_INCREMENT 自增长（默认从0开始,不受DELETE影响，受TRUNCATE影响）

NOT NULL 非空（不可赋null，可赋''）

UNIQUE 唯一（可重复赋null）

DEFAULT 默认值（可赋null）

CKECK 检查约束（8.0.16版本开始）

DEFAULT CURRENT_TIMESTAMP 创建时间自动填充

CONSTRAINT 声明约束

FOREIGN KEY 外键约束。

外键：主表主键作为从表外键，两表之间产生数据约束关系。

增加数据时，要先主表后从表。

删除数据时，要先从表后主表。（主先副而生，后副而亡）

外键删除/更新行为：

NO ACTION或RESTRICT：主键列和外键列都不允许直接修改更新，除非先删除彼表相关数据（暂存到临时表或临时变量），再更新此表，最后更新彼表。

CASCADE：直接修改或删除主表数据，从而影响副表数据。（级联修改：ON update CASCADE /级联删除：ON delete CASCADE）

SET NULL：删除主表记录时，对应子表外键值设为null

SET DEFAULT：删除主表记录时，对应子表外键值设为默认值

CREATE TABLE 表名(

字段1 类型 [NOT NULL] [PRIMARY KEY],

字段2 类型 [NOT NULL],

...

[, constraint 约束名 约束类型 (要约束的字段),

... ] );

【说明】

constraint约束类型有:

check,unique,primary key,not null,foreign key);

【示例】

create table t_student(

s_id number(8) PRIMARY KEY,

s_name varchar2(20) not null,

s_sex varchar2(8),

clsid number(8),

constraint u_1 unique(s_name),

constraint c_1 check (s_sex in ('MALE','FEMALE'))

);

复制表结构或兼数据

复制表结构：

create table b like a;

灵活定义表结构和数据的复制（会丢失表注释；若不想要数据，添加条件1=2）：

create table c as select ...

--从现有的表创建表及复制其数据

复制表结构：

CREATE TABLE 表名 as <SELECT 语句>

复制表结构和数据：

CREATE TABLE 表名 as <SELECT 语句带where 1=2判断> 

例：

create table emp as select empno,ename from scott.emp;--复制表结构和数据

create table emp as select * from scott.emp where 1=2;--只复制表结构

create table tongwx.dept as select * from scott.dept;--在模式（用户）之间复制表

删除表

drop table [if exists] 表名;

DROP TABLE 表名;--删除到回收站

DROP TABLE 表名 PURGE;--彻底删除

删除并重建表

truncate table 表名;

修改表

添加字段

alter table 表 add [column] 字段名 字段类型 COMMENT '注释'[, add [column] 字段名 字段类型 COMMENT '注释'...];

ALTER TABLE 表名 ADD (字段1 类型 [NOT NULL],

字段2 类型 [NOT NULL] ... );

添加外键约束

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表(主表字段) [ON update 更新方式] [ON delete 删除方式]

删除字段（慎用）

alter table 表 drop [column] 字段名[, drop [column] 字段名…];

ALTER TABLE 表名 DROP(字段1,字段2... );

删除主键约束

ALTER TABLE 表名 DROP PRIMARY KEY;

删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

修改字段类型

alter table 表 modify [column] 字段名 新的字段类型;

ALTER TABLE 表名 MODIFY(字段1 新类型,字段2 新类型 ...);

修改字段名称、字段类型[及其他]

alter table 表 change [column] 旧字段名 新字段名 字段类型 [COMMENT '注释'] [约束];

ALTER TABLE 表名 RENAME COLUMN 原字段名 TO 新字段名;

修改表名称

alter table 表名 rename [to] 新表名;

或：

rename table 表名 to 新表名;

修改自增主键id起始值

alter table user AUTO_INCREMENT=100;

修改表的字符集

alter table student CHARACTER SET utf8mb4;

修改表的排序规则

ALTER TABLE 表名 COLLATE=utf8mb4_bin;

查看回收站：

show recyclebin; 或 

select * from recyclebin;

清空回收站：

purge recyclebin;

MySQL

Oracle

增加数据

插入所有字段：INSERT INTO 表名 VALUES(1,'张三','男',20)[,(2,'李四','女',30)...];

插入部分字段：INSERT INTO 表名(id,name) VALUES(1,'张三')[,(2,'李四')...];

删除数据

带条件的删除：delete from student where id=2;

全部删除（慎用）：去掉where子句

全部删除另一种方式（属于DDL，慎用）：truncate table student;

delete和truncate比较：

delete: 可带条件；不删约束（不重置自增长索引）；可回滚。

truncate:不带条件；删约束（重置自增长索引）；不回滚。（原理：先删除表再创建表）

修改数据

带条件的修改：update student set gender='男',age=30 where id=2;

全部修改（慎用）：去掉where子句。

拷贝数据

create table t1 as select * from emp where 1=2;

insert into t1 select * from emp where sal>2000;

子句顺序

子句内部顺序

优化指南

说明

FROM

从后往前、从右到左

执行顺序为从后往前, 所以数据量较少的表尽量放在后面

多表连接时，使用表的别名并把别名前缀于每个Column上。可以减少解析的时间并减少那些由Column 歧义引起的语法错误。

对FROM子句中前两个表执行笛卡尔积生成虚拟表vt1。

oracle 的解析器按照从右到左的顺序处理，FROM 子句中的表名，FROM 子句中写在最后的表(基础表 driving table)将被最先处理，即最后的表为驱动表，在FROM 子句中包含多个表的情况下,你应当选择记录条数最少的表作为基础表。如果有3 个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指被其他表所引用的表

ON

对vt1表应用ON筛选器生成虚拟表vt2

JOIN

如果是outer join 那么这一步就将添加外部行，left outer jion 就把左表在上一步中过滤掉的行添加进来，如果是right outer join 那么就将右表在上一步中过滤掉的行添加进来，这样生成虚拟表 vt3。

如果 from 子句中的表数目多于两个表，那么就将vt3和第三个表连接从而计算笛卡尔乘积，生成虚拟表，也就是重复1-3步骤，最终得到一个新的虚拟表 vt3。

WHERE

自下而上、从右到左

将能过滤掉最大数量记录的条件写在WHERE子句的最右

对vt3应用 WHERE 筛选器生成虚拟表vt4。

GROUP BY

从左往右分组

开始使用select中的别名，后面的语句中都可以使用

按GROUP BY子句中的列对vt4中的行分组生成vt5。

如果应用了group by，那么后面的所有步骤都只能得到vt5的列或者是聚合函数。

组函数？

WITH {CUBE | ROLLUP}？

把超组(supergroups)插入vt6，生成vt6 

having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器。

使用聚组函数返回的是每组的汇总信息

HAVING

很耗资源，尽量少用，未涉及分组统计的过滤请使用WHERE

对vt6应用HAVING筛选器生成vt7

HAVING 只有在检索出所有记录之后才对结果集进行过滤，需要排序、总计等操作，因此很耗资源。

 (非Oracle中)ON、WHERE、HAVING三者皆用于过滤：

ON最先执行，用于多表联接过滤，速度最快，

WHERE然后执行，用于分组统计前过滤行（不能使用组函数），速度其次。

HAVING 最后执行，用于分组统计后过滤分组（常使用组函数），速度最慢。

on和where、HAVING的最大区别在于，在on执行后在下一步outer join中还可以把移除的行再次添加回来，而where、HAVING的移除是最终的。

SELECT

少用*号，尽量取字段名称。

ORACLE 在解析的过程中, 通过查询数据字典将*号依次转换成所有的列名, 消耗时间。

SQL语句用大写，因为Oracle总要把小写转大写再执行

选择指定的列生成vt8

DISTINCT

将重复的行从vt8中去除产生vt9。

事实上如果应用了group by子句那么distinct是多余的。

UNION

ORDER BY

从左到右排序，消耗资源。

将vt9的行按order by子句中的列列表排序生成一个游标vc10。

此时返回的一个游标，而不是虚拟表。sql是基于集合的理论的，集合不会预先对他的行排序，它只是成员的逻辑集合，成员的顺序是无关紧要的。对表进行排序的查询可以返回一个对象，这个对象包含特定的物理顺序的逻辑组织。这个对象就叫游标。正因为返回值是游标，那么使用order by 子句查询不能应用于表表达式。排序是很需要成本的，除非你必须要排序，否则最好不要指定order by，最后，在这一步中是第一个也是唯一一个可以使用select列表中别名的步骤。

LIMIT

取出指定行的记录，产生虚拟表VT11, 并将结果返回

TOP

从vc10的开始处选择指定数量或比例的行生成vt11 并返回调用者

MySQL

Oracle

基本查询（select）

所有字段

select * from student;

指定字段

select id,name,gender from student;

指定字段别名

select id [as] '编号', name [as] '姓名' from student;

添加常量列

select id, name, '一年级 ' AS '年级' from student;

合并列

+、-、*、/

select id,name,(servlet+jsp) [AS] '总成绩' from student;

注意：

1. 非数值类型列合并，其值会被忽略
2. 合并列有Null值的，结果也为Null。

+、-、*、/、||

select id,name,(servlet+jsp) [AS] '总成绩' from student;

select '工号为：' || empno || ' 的员工的姓名为：'|| ename from emp;

去除重复记录

select DISTINCT address from student;

或者使用函数写法：

select DISTINCT(address) from student;

条件查询(where)

注：MySql数据库字符集为..._general_ci时，查询条件匹配是对大小写不敏感的，如果需要对大小写敏感，可将字符集设为..._bin，或者在查询语句中的条件字段前加入关键字binary如：

SELECT * FROM 表 WHERE binary 字段 = ?;

比较条件

>  <  >=  <=  =  <>或!=  

between...and（闭区间，等价于>=且<=）  in(...)

select * from student where servlet>70;

select * from student where jsp>=75 AND jsp<=90;

select * from student where jsp BETWEEN 75 AND 90;

select * from student where gender<>'男'; （非标准写法：!=）

注意：使用大于、小于、不等于和不在过滤时会把null值也过滤。如不想过滤null值，可以如：

...where a <> 'A' or a is null；

...where ifnull(a,'default') <> 'A'；

...where a <> 'A' or a is null；

...where nvl(a,'default') <> 'A'；

判空条件

is null / is not null / ='' / <>''（MySQL中 ''不等于null）

select * from student where address IS NULL;

select * from student where address='';

--查询无地址的学生（包括null和空字符串）：

select * from student where address IS NULL OR address='';

--查询有地址的学生(不包括null和空字符串)：

select * from student where address IS NOT NULL AND address<>'';

is null / is not null（Oracle中''等于null）

注意：WHERE 1 = NULL、WHERE NOT(1 = NULL)、WHERE 1 NOT IN (1,NULL)、WHERE 1 NOT IN (2,NULL)、WHERE null in (null)、WHERE null = null都不返回任何数据（把null看作未知数来判断，无法判断的条件都为false）

模糊条件

like

-- % : 表示任意个字符

-- _ : 一个表示一个字符

escape：指定转义字符

select * from student where name LIKE '李%';

select * from student where name LIKE '李_';

select * from student where name LIKE '李__';

select * from student where name LIKE '%\_%'; --查找名字含“_”的学生。

select * from student where name LIKE '%/%%' escape '/'; --查找名字含“%”的学生。

注：“\”为默认的转义字符，在MySQL中，默认的“\”不要再重申escape '\'，在Oracle中，默认的“\”可以再重申escape '\'

逻辑条件

and或&&  or或||  not()或! 优先级（执行顺序）：NOT >AND >OR

select * from student where gender='男'AND address='北京';

select * from student where address=北京’ OR address='上海'; 

select * from student where not(age > 18);

分组查询（group by）

分组后统计（先分组(GROUP BY)，后统计(COUNT(*)每组数量)：

select gender,COUNT(*) from student GROUP BY gender;

分组后筛选（先分组后统计再筛选(having)）：

select gender,COUNT(*) from student where grade='一年级' GROUP BY gender HAVING COUNT(*)>2;

注意：分组前条件用where，分组后条件用having。

使用分组查询的规则：出现在select列表中的字段，如果不是组函数，那么就必须出现在group by 子句中，否则就会出现错误：not a GROUP BY expression

在mysql 5.7.5之前的版本，ONLY_FULL_GROUP_BY默认不开启，即mysql对标准SQL的扩展可以生效：

    允许having子句、select子句、order by子句中的非聚合列不出现在group by子句中，此时mysql会对这些列随机选择值。效果等同于使用ANY_VALUE()。

    允许在having子句中使用select子句中的别名

查看是否开启了only_full_group_by规则校验：

全局查看：SELECT @@GLOBAL.sql_mode;

会话查看：SELECT @@SESSION.sql_mode;

如果查询结果中可以看到ONLY_FULL_GROUP_BY这个关键字，说明开启了规则校验，如果要关闭only_full_group_by规则校验，将上面的查询结果中删去ONLY_FULL_GROUP_BY后的值设回参数后重启mysql服务：

全局设置：set @@GLOBAL.sql_mode='<删去ONLY_FULL_GROUP_BY后的值>';

会话设置：set @@SESSION.sql_mode='<删去ONLY_FULL_GROUP_BY后的值>';

查询排序（order by）

asc顺序，desc倒序

select * from student; --无order时默认按插入顺序排序

select * from student ORDER BY id [ASC]; -- 默认正序可不加ASC

select * from student ORDER BY id DESC;-- 反序

select * from student ORDER BY servlet [ASC],jsp DESC; -- 多个排序条件

分页查询

limit 起始行,查询几行-- 行号从0开始

嵌套子查询 --行号从1开始

select * from student LIMIT (当前页-1)*每页条数, 每页条数;

查询第1,2条记录（第1页的数据）：

select * from student LIMIT 0,2;

查询第3,4条记录（第2页的数据）：

select * from student LIMIT 2,2;

查询第5,6条记录（无记录不显示）：

select * from student LIMIT 4,2;

开始索引：(当前页-1)*每页条数+1

结束索引：每页条数*页数

若不要排序，先取1到结束索引的行，再取开始索引到结束索引的行（二层嵌套）

SELECT *

FROM (SELECT ROWNUM R,EMP.*

      FROM EMP

      WHERE ROWNUM < 7)

WHERE R > 3;

若要排序，先排序再加伪列并分页（三层嵌套）

注：Oracle嵌套分页查询为什么要先判断小于结束索引再判断大于开始索引？

例子：select * from (select ta.*,rownum r from (select * from A) ta where rownum < 10) where r > 5

（其实两层就可以，不过，两层嵌套查询不会用到oracle的外层条件内推机制，效率慢了点）

先判断大于开始索引为什么不好使？

因为检索和操作rownum的时候游标的指向必须从1开始，不能跳过；

rownum是查询过后才按顺序排的，假如你的条件是rownum>1;那么返回数据的第一条（rownum是1）就不符合要求了，然后第二条数据变成了现在的第一条，结果这一条rownum又变成1了又不符合要求了，以此类推，就没有返回结果。

查询合并

第一个select语句的列或别名作为结果标题

并集

union all（并集不去重）、union（并集且去重）

select * from emp where deptno=10

union

select * from emp where deptno=20;

交集

intersect：取出结果集中共有的记录

select ename,sal from emp where sal between 1000 and 2000

intersect

select ename,sal from emp where sal between 1500 and 2500;

减集

minus：取出第一个查询中有，第二个查询中没有的记录

select ename,sal from emp where sal between 1000 and 2000

minus

select ename,sal from emp where sal between 1500 and 2500;

运算符类型

运算符

括号

()

一元运算符

+、-、NOT、!、~

二元运算符

*、/、%、DIV、MOD

加减运算符

+、-

比较运算符

IS[NOT]NULL, LIKE, [NOT]IN、=、>、<、>=、<=、<>、!=、<=>、[NOT] BETWEEN

逻辑运算符

AND、OR

--按部门统计员工的人数，要求显示部门号、部门名称、和部门人数，部门下没有人的也将显示

select d.deptno,d.dname,count(e.empno) from dept d,emp e where d.deptno=e.deptno(+) group by d.deptno,d.dname;

select e.empName,d.deptName from employee e RIGHT [OUTER] JOIN dept d ON d.id=e.deptId;

--查询员工及其上司

select e.empName,b.empName from employee e LEFT [OUTER] JOIN employee b ON e.bossId=b.id;

--select后面的子查询

select (select dname from dept where deptno=10),ename from emp where deptno=10;

--from后面的子查询

select * from (select ename,sal from emp);

--where后面的子查询；查询工资比10号部门员工中任意一个员工的工资低的员工信息

select * from emp where sal < (select min(sal) from emp where deptno=10);

函数

返回类型

MySQL函数

Oracle函数

ABS (number2 )

绝对值

BIN (decimal_number )

十进制转二进制

向上取整

CEIL(number)

或

CEILING(number)

向下取整

FLOOR(number)

CONV(number2,from_base,to_base)

进制转换

FORMAT (number,decimal_places )

保留小数位数

HEX (DecimalNumber )

转十六进制

LEAST (number , number2 [,..])

求最小值

MOD(numerator ,denominator)

求余（取模）

RAND([seed])

RAND([seed])

四舍五入

round(x[,y])

返回对x四舍五入保留y位小数（若y大于0）或y位0（若y小于0）的值。

y默认为0。

若y为小数，先对y四舍五入。

round(x[,y])

【功能】返回四舍五入后的值

【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则四舍五入为y位小数，如果y小于0则四舍五入到小数点向左第y位。

【返回】数字

【示例】

select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) from dual;

返回：   5555.67     ,    5600    ,    5556

trunc(x[,y])

数字

【功能】返回x按精度y截取后的值

【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示。

【示例】

select trunc(5555.66666,2.1),

trunc(5555.66666,-2.6),trunc(5555.033333)  from dual;

返回：5555.66                    5500               5555

功能

返回类型

MySQL函数

Oracle函数

CHARSET(str)

返回字串字符集

连接字串

CONCAT(string[,...])

INSTR (string ,substring )

返回substring在string中出现的位置,没有返回0

转换成大写

UPPER(string)或UCASE(string)

转换成小写

LOWER(string)或LCASE(string)

LEFT (string2 ,length )

从string2中的左边起取length个字符

LENGTH (string )

数值型

返回string长度。【说明】多字节符(汉字、全角符等)，按1个字符计算

STRCMP (string1 ,string2 )

逐字符比较两字串大小,

去除空格

LTRIM(str)：去除左端空格

RTRIM(str)：去除右端空格

TRIM(str)：去除两端空格

字符串填充

字符型

左填充：LPAD(str,n,pad)

右填充：RPAD(str,n,pad)

【说明】返回用pad填充str的左（右）边，直到长度为n时为止的结果，如果str长度大于n，则返回str的前n个字符

替换字符串

字符型

REPLACE(str ,search_str ,replace_str )

在str中用replace_str替换search_str

REPLACE(c1,c2[,c3])

将c1中的c2替换成c3，若不指定c3，则删除c1中的c2

截取字符串

字符型

SUBSTR(str,start[,length])

或

SUBSTRING(str,start[,length])

SUBSTR(str,start[,length])

【说明】下标从1开始。若不指定length，则从start截至末尾

返回类型

MySQL函数

Oracle函数

返回追加后的日期（日期时间）。

ADDTIME(原date ,要追加的time_interval)

【示例】select addtime('16:58:30','01:01:01');

select addtime(time,'1 1-1 10:09:09') from student; --时间戳上增加，注意年后没有

DATE_ADD (date2 , INTERVAL d_value d_type)

在date2中加上日期或时间

【示例】select date_add(entry_date,INTERVAL 2 year) from student; --增加两年

DATE_SUB (date2 , INTERVAL d_value d_type)

在date2上减去一个时间

add_months(原日期,要追加的月数)

【示例】select sysdate,add_months(sysdate,3) from dual;

返回当前日期时间（时间戳）。

日期时间

CURRENT_TIMESTAMP ()：返回当前时间戳

now()：返回当前日期时间（now和括号之间不能有空格）

sysdate：返回当前日期时间

【说明】：没有参数，没有括号

【示例】select sysdate from dual;

当前日期和时间

NOW()

当前日期

CURDATE()或CURRENT_DATE()

当前时间

CURTIME()或CURRENT_TIME()

DATE(时间戳)

返回时间戳的日期部分

计算日期差（月份差）

DATEDIFF (date1 ,date2)

两个日期差

months_between(d1,d2)

【功能】：返回月数差（实数）。

【参数】：d1，d2 日期型

【返回】：实数。如果d1>d2，则返回正实数；如果d1<d2，则返回负实数

【示例】

select sysdate,

months_between(sysdate,to_date('2015-01-01','YYYY-MM-DD')) 距2015元旦,

months_between(sysdate,to_date('2016-01-01','YYYY-MM-DD')) 距2016元旦 from dual;

当前时间

YEAR(日期时间)

MONTH(日期时间)

DATE(日期时间)

返回年

返回月

返回日期

提取数字

extract(c1 from d1)

【功能】：日期/时间d1中，参数(c1)的值

【参数】：d1日期型(date)/日期时间型(timestamp),c1为字符型(参数)

【参数表】：c1对应的参数表详见示例

【返回】：字符

【示例】

select 

extract(YEAR from timestamp '2015-5-1 12:26:18 ' ) 年,

extract(MONTH from timestamp '2015-5-1 12:26:18 ' ) 月,

extract(DAY from timestamp '2015-1-5 12:26:18 ' ) 日,

extract(hour from timestamp '2015-5-1 12:26:18 ' ) 时,

extract(minute from timestamp '2015-5-1 12:26:18' ) 分,

extract(second from timestamp '2015-5-1 12:26:18 ' ) 秒 

from dual;

返回：

年 月 日 时 分 秒

2015 5 5 12 26 18

【示例】

select extract (YEAR from date '2015-5-1' ) from dual; 

返回：2015

【示例】

select sysdate 当前日期,

extract(YEAR from sysdate ) 年,

extract(MONTH from sysdate ) 月,

extract(DAY from sysdate ) 日

from dual;

--如下语句也可获取年份、月份等

select to_number(to_char(sysdate,'yyyy')) from dual;

Oracle

TO_CHAR(x[[,c2],C3])

【功能】将日期或数据转换为char数据类型

【参数】

       x是一个date或number数据类型。

       c2为格式参数

       c3为NLS设置参数

【返回】varchar2字符型

【示例】

select to_char（sysdate,'YYYY_MM_DD HH24:MI:SS'） FROM dual;

--返回：2017_11_19 13:20:20

select to_char（1210.7, '$9,999.00'） FROM dual;

--返回：$1,210.70

TO_DATE(X[,c2[,c3]])

【功能】将字符串X转化为日期型

【参数】c2,c3,字符型，参照to_char()

       x是字符串。

       c2为格式参数

       c3为NLS设置参数

【返回】字符串

如果x格式为日期型(date)格式时，则相同表达：date x

如果x格式为日期时间型(timestamp)格式时，则相同表达：timestamp x

【示例】

select to_date('201212','yyyymm'),

to_date('2012.12.20','yyyy.mm.dd'),

(date '2012-12-20') XXdate, 

to_date('2012-12-20 12:31:30','yyyy-mm-dd hh24:mi:ss'),

to_timestamp('2012-12-20 12:31:30','yyyy-mm-dd hh24:mi:ss'),

(timestamp '2012-12-20 12:31:30') XXtimestamp

from dual;

TO_NUMBER(X[[,c2],c3])

【功能】将字符串X转化为数字型

【参数】c2,c3,字符型

【返回】数字串

【示例】

select TO_NUMBER('201212') + 3,TO_NUMBER('450.05') + 1 from dual;

select '201212' + 3 from dual;

功能

MySQL函数

Oracle函数

判真

IF(exp, value1, value2)

如果exp为true，返回value1，否则返回value2

判空

IFNULL(value1, value2)

NVL(value1, value2)

NVL2(exp, exp非空时返回值, exp为空时返回值)。

如果value1非NULL，返回此value1，否则返回value2

流程判断

CASE WHEN exp1 THEN res1 ... [ELSE default] END

若exp为真，返回对应res，否则返回default（有else default时）或null

CASE exp WHEN test1 THEN res1 ... [ELSE default] END

若exp等于test，返回对应res，否则返回default（有else default时）或null

decode(exp, test1, res1,...,...,default)

--查询部门的员工工种情况，并在部门内重新进行排序；PARTITION BY类似group by,根据ORDER BY排序字段的值重新由1开始排序。

--RANK 使用相同排序排名一样，后继数据空出排名；即有2个排序为1的，那么接下来的排序号则为3

select deptno,ename,job,rank() over(partition by deptno order by job) as myRank from emp e;

--DENSE_RANK使用，使用相同排序排名一样，后继数据不空出排名；即有2个排序为1的，那么接下来的排序号则为2

select deptno,ename,job,dense_rank() over(partition by deptno order by job) as myDenseRank from emp e;

--ROW_NUMBER使用，不管排名是否一样，都按顺序排名；即有2个排序为1的，那么排序号不会重现重复

select deptno,ename,job,row_number() over(partition by deptno order by job) as myRowNumber from emp e;--如果只想查看某部门的排序，后面加上where deptno=部门号

行（长表）：

列（宽表）：

行转列：

SELECT userid,

SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文',

SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学',

SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语',

SUM(CASE `subject` WHEN '体育' THEN score ELSE 0 END) as '体育'

FROM tb_score

GROUP BY userid

列转行：

SELECT userid, '语文' as subject, `语文` as score  

FROM tb_score  

WHERE `语文` IS NOT NULL  

UNION ALL

SELECT userid, '数学' as subject, `数学` as score  

FROM scoreWide  

WHERE `数学` IS NOT NULL  

UNION ALL

SELECT userid, '英语' as subject, `英语` as score  

FROM scoreWide  

WHERE `英语` IS NOT NULL   

UNION ALL

SELECT userid, '体育' as subject, `体育` as score  

FROM scoreWide  

WHERE `体育` IS NOT NULL   

方式一

方式二

-- 查看事务是否自动提交

select @@autocommit;

-- 修改事务为手动提交

set @@autocommit = 0;

-- 修改数据，修改暂存在当前会话缓存中

update user set age = 30 where name = '李四';

-- 查询当前会话缓存中数据（已修改未入库）

select * from user;

-- 手动回滚事务，撤回当前会话缓存中的修改

rollback;

-- 手动提交事务，将当前会话缓存中的修改提交到数据库

commit;

-- 修改事务为自动提交

set @@autocommit = 1;

-- 手动开启事务

begin; -- 或者start transaction;

-- 修改数据，修改暂存在当前会话缓存中

update user set age = 30 where name = '李四';

-- 设置一个保存点a

savepoint a;

-- 再次修改数据，修改暂存在当前会话缓存中

update user set age = 20 where name = '李四';

-- 查询当前会话缓存中数据（已修改未入库）

select * from user;

-- 手动回滚事务到保存点a，撤回当前会话缓存中保存点a后的修改

rollback to a;

-- 手动回滚事务，撤回当前会话缓存中的所有修改

rollback;

-- 手动提交事务，将当前会话缓存中的修改提交到数据库，所有保存点将丢弃

commit;

备份（终端下）：mysqldump --default-character-set=编码 -u 用户名 -p 数据库名 > sql文件完整路径

default-character-set=charset 指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。

模板：mysqldump --default-character-set=utf8 -uroot -p bankingsystem > E:\bankingsystem20180605.sql

恢复（终端下）：mysql -u 用户名 -p密码 -- default-character-set=编码如utf8 数据库名 < sql文件完整路径

导入（mysql下）：source sql文件完整路径 （要先登录）

--全表备份

exp itcast/itcast@orcl file=d:\database\oracle_data\itcast.dmp full=y;

--指定表备份

exp itcast/itcast@orcl file=d:\database\oracle_data\itcast_emp_dept.dmp tables=(emp,dept);

【说明】full：完整导出数据库，一般使用system具有管理员权限的用户在命令行下进行操作。

--全表恢复

imp itcast/itcast@orcl ignore=y file=d:\database\oracle_data\itcast.dmp  full=y;

--指定表恢复

imp itcast/itcast@orcl ignore=y file=d:\database\oracle_data\itcast_emp_dept.dmp tables=(emp,dept);

【说明】ignore：忽略创建错误

存储引擎

InnoDB

MyISAM

Memory

存储限制

64TB

有

有

事务

支持

-

-

锁机制

行锁

表锁

表锁

索引

支持B+tree索引，5.6版本以后支持全文索引

支持B+tree索引和全文索引

支持B+tree索引和Hash索引

空间使用

高

低

-

内存使用

高

低

中等

批量插入速度

低

高

高

外键

支持

-

-

表空间文件

文件配置参数：innodb_file_per_table

在MySQL8中innodb_file_per_table默认为ON，在该配置下：

文件名：表名.idb

可在cmd中用ibd2sdi命令查看二进制文件内容（JSON格式）：ibd2sdi 表名.idb

存储内容：表结构（frm、sdi）、数据、索引

在MySQL早期版本中innodb_file_per_table默认为OFF，在该配置下：

文件名：表名.frm

存储内容：

一个表对应三个文件：

表名.MYD：存储数据

表名.MYI：存储索引

表名_序号.sdi：存储表结构信息（JSON格式文本文件）

表名.sdi：存储表结构信息

（数据存放在内存中）

逻辑存储结构

TableSpace：表空间

Segment：段

Extent：区（1M，包含64个Page）

Page：页（16K）

Row：行

在Row中：

Trx id：最后一次操作事务的id

Roll Pointer

col1、col2、...

索引结构

特点

支持的引擎

B+Tree索引

最常见的索引

大部分引擎都支持

Hash索引

支持精确查找（=、in）

不支持范围查找（between、>、<...）和排序

Memory引擎支持

InnoDB引擎具有自适应Hash功能

R-Tree索引

空间索引主要用于地理空间数据类型。

MyISAM引擎支持

Full-Text索引

全文索引是一种倒排索引，类似于Lucene、Solr、ES

InnoDB（5.6版本起）和MyISAM引擎支持

分类

含义

特点

关键字

主键索引

针对表中主键创建的索引

默认自动创建，只能有一个

PRIMARY

唯一索引

避免列值重复（可为NULL）

可以多个

UNIQUE

常规索引

快速定位数据

可以多个

全文索引

查找文本中的关键词

可以多个

FULLTEXT

分类

含义

特点

聚集索引/聚簇索引/主键索引

Clustered Index

将数据与索引一起存储，叶子节点存储行数据。

有且只有一个。如果没有主键，就默认在第一个非空唯一键上建立聚集索引；如果又没有，就会创建一个隐藏的row-id作为聚集索引。

二级索引/非聚集索引/非聚簇索引/普通索引/辅助索引

Secondary Index

将数据与索引分开存储，叶子节点存储主键值。

可以多个

EXPLAIN|DESC SELECT ……

EXPLAIN EXTENDED SELECT ……;

SHOW WARNINGS

（mysql8不再支持）

先运行EXPLAIN EXTENDED将执行计划“反编译”

再运行SHOW WARNINGS可得到被MySQL优化器优化后的查询语句

EXPLAIN PARTITIONS SELECT ……

用于分区表的EXPLAIN

EXPLAIN列

含义

说明

id

子句操作顺序

id不同，执行顺序从大到小

id相同，执行顺序从上到下

select_type

select的类型

SIMPLE：简单查询，不含关联查询、子查询或UNION

PRIMARY：主查询，即最外层的查询

SUBQUERY：子查询

DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）

UNION：UNION中的第二个及以后的查询；若UNION包含在 FROM子句的子查询中，外层SELECT将被标记为：DERIVED

UNION RESULT：从UNION表获取结果的SELECT

table

子句查询的表

partitions

type

连接类型

效率从高到低：

null：查询时不用访问表或索引，如：select 1;

system：查询的表只有一行且命中，这是const类型的特殊情况

const：命中主键索引或唯一索引，比较值是一个常量。

eq_ref：关联查询命中主键索引或非空唯一索引，比较值是关联表对应列。

ref：非唯一索引扫描（包括唯一联合索引的非唯一前缀），返回匹配某个单独值的所有行。

range：索引范围查找（between、>、<、>=、<=、like前缀匹配、in？）

index：遍历索引树

index_merge：AND或OR连接的条件，对多个索引分别进行条件扫描，然后合并结果(intersects/unions/unions-of-intersections)。其中index intersect merge可以优化为复合索引

all：全表扫描

possible_keys

可能用到的索引

注：查询中若使用了覆盖索引，则该索引仅出现在key列表中

key

实际使用的索引

可用USE|FORCE INDEX(indexname)来强制使用某个索引

可用IGNORE INDEX(indexname)来强制忽略某个索引

key_len

使用的索引字节长度

索引字段的最大可能字节长度，并非实际使用字节长度，即key_len是根据表定义计算而得，key_len主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列。

计算方式：

1）数字类型：tinyint=1，int=4，bigint=8。

2）字符串类型：

字符数 * 字符集单位字节长度（latin1=1,gbk=2,utf8=3,utf8mb4=4）

若为变长类型VARCHAR，还需要再加2字节来存储该变长列的实际长度

3）日期时间类型：

若不使用小数秒，YEAR=1，DATE=3，TIME=3，DATETIME=5，TIMESTAMP=4；

若使用小数秒，需再加上小数秒字节长度（1~2位=1，3~4位=2，5~6位=3）

1. 若索引列允许NULL，还需要再加1字节

ref

匹配条件

显示索引的哪一列被使用了，即哪些列或常量被用于查找索引列上的值

如果可能的话，是一个常数

rows

预估扫描行数

预估找到所需的记录所需要读取的行数

filtered

返回行数占扫描行数的百分比

越大越好

Extra

额外信息

Distinct：一旦mysql找到了与行相联合匹配的行，就不再搜索了。

Not exists：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。

Range checked for each Record（index map:#） ：没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。

Using index：使用了覆盖索引（Covering Index，包含所有满足查询需要的数据的索引）。

Using where：表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Post-filter）,如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集

Using temporary：（需要优化）表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。

Using filesort：（需要优化）MySQL中无法利用索引完成的排序操作称为“文件排序”。

Where used ：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。

要义

不推荐

推荐

首先应考虑在 where 及 order by 涉及的列上建立索引。

尽量避免where子句中对字段进行 null 值判断

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

把可以过滤掉最大数量记录的条件应写在WHERE子句最后。

WHERE子句内部的执行顺序是自下而上、从右到左。

尽量避免在 where子句中使用!=或<>操作符

or、in筛选值若连续，改用between and，若不连续，改用union all

不连续：

select id from t where num = 10 or num = 20 or num = 30;

select id from t where num in (10,20,30);

连续

select id from t where num in (1,2,3)

不连续：

select id from t where num = 10

union all

select id from t where num = 20

union all

select id from t where num = 30:

连续：

select id from t where num between 1 and 3

用exists代替in；not exists代替 not in

not in 字句将执行一个内部的排序和合并，任何情况下，not in是最低效的，子查询中全表扫描

select num from a where num in(select num from b)

select num from a where exists(select 1 from b where num=a.num)

避免在索引列上使用NOT

因为Oracle服务器遇到NOT后，他就会停止目前的工作，转而执行全表扫描

用 >= 替代 >
 

低效：where num > 3

 --首先定位到num = 3的记录并且扫描到第一个DEPT大于3的记录

高效：WHERE DEPTNO >= 4

--直接跳到第一个num等于4的记录

索引列不要加函数或运算

... where num/2=100

... where substring(name,1,3)='abc'

... where num=100*2

... where name like 'abc%'

索引列不要发生类型转换。例如字符串条件值忘加单引号导致条件两边类型不等从而索引列发生隐式类型转换

or分割的条件只要有一个不走索引，那么整体不走索引。解决方法：补全索引或改用union查询

有多条件查询时，尽量建立联合索引而非多个单列索引（MySQL只会选择使用一个单列索引）

联合索引要遵循最左匹配原则

模糊查询尽量遵循最左匹配原则

select id from t where name like '%abc%'

select id from t where name like 'abc%'

尽量使用覆盖索引避免回表查询（不要select *）

覆盖索引（using index）：如果要返回的数据都可在使用的索引中取得（执行计划Extra中为 Using index），则称为覆盖索引，这时不用回表查询，直接返回结果，减少IO。

回表查询：通过普通索引查询主键，再通过聚集索引查询记录（执行计划Extra中为Using index condition或null）。

当varchar类型长度很大时，可使用前缀索引。前缀长度（sub_part）可根据索引的选择性决定，索引的选择性=select count(distinct substr(索引列, 1, 前缀长度)/count(0) from 表。索引选择性越高则查询效率越高。唯一索引的选择性是1，是最高的选择性。

注意数据分布影响：

1）如果MySQL评估使用索引更慢，就不使用索引。例如：列值多null时is null判断走全表扫描，列值少null时is null判断走索引

当同一列有多个索引时，可使用SQL提示MySQL走或不走哪个索引：

use index(索引名)

ignore index(索引名)

force index(索引名)

不要写一些没有意义的查询

如需要生成一个空表结构

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的

应改成这样：

create table #t(...)

变量类型

说明

语法

系统变量

系统变量由MySQL服务器提供。

系统变量分为全局变量（GLOBAL）、会话变量（SESSION）。

查看

查看所有：SHOW [ [SESSION] | GLOBAL ] VARIABLES;

模糊查找：SHOW [ [SESSION] | GLOBAL ] VARIABLES LIKE '...';

精确查找：SELECT @@[[SESSION.]|GLOBAL.]系统变量名;

设置

方式一：SET [ [SESSION] | GLOBAL ] 系统变量名 = 新值;

方式二：SET @@[[SESSION.]|GLOBAL.]系统变量名 = 新值;

注意

1. 如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
2. mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf中配置。

用户变量

用户变量不用提前声明，作用域为当前连接。

赋值

方式一：SET @var_name := expr [, @var_name := expr] ... ;

方式二：SELECT @var_name := expr [, @var_name := expr] ... ;

方式三：SELECT 字段名 INTO @var_name FROM 表名;

使用

SELECT @var_name [, @var_name,...];

注意

1. 赋值时，可以使用= ，也可以使用:= ,推荐使用:=。
2. 用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

局部变量

局部变量访问之前需要声明，可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块。

声明

DECLARE 变量名 变量类型 [DEFAULT ... ] ;

赋值

方式一：SET 变量名 := 值; # 也可使用=，推荐使用:=

方式二：SELECT 字段名 INTO 变量名 FROM 表名 ... ;

使用

SELECT 变量名

在Linux中，数据库库名表名对大小写敏感，与文件系统保持一致：

在Windows中，数据库库名表名对大小写不敏感，与文件系统保持一致：

mysql> show variables like 'lower%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| lower_case_file_system | OFF   |

| lower_case_table_names | 0     |

+------------------------+-------+

2 rows in set (0.01 sec)

mysql>

mysql> show variables like 'lower%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| lower_case_file_system | ON    |

| lower_case_table_names | 1     |

+------------------------+-------+

2 rows in set (0.07 sec)

mysql>

参数值

系统默认

解释

0

Unix默认

使用CREATE TABLE或CREATE DATABASE语句指定的大小写字母在硬盘上保存表名和数据库名。名称比较对大小写敏感。在大小写不敏感的操作系统如windows或Mac OS x上我们不能将该参数设为0，如果在大小写不敏感的文件系统上将--lowercase-table-names强制设为0，并且使用不同的大小写访问MyISAM表名，可能会导致索引破坏。

1

Windows默认

表名在硬盘上以小写保存，名称比较对大小写不敏感。MySQL将所有表名转换为小写在存储和查找表上。该行为也适合数据库名和表的别名。该值为Windows的默认值。

2

Mac OS X默认

表名和数据库名在硬盘上使用CREATE TABLE或CREATE DATABASE语句指定的大小写字母进行保存，但MySQL将它们转换为小写在查找表上。名称比较对大小写不敏感，即按照大小写来保存，按照小写来比较。注释：只在对大小写不敏感的文件系统上适用! innodb表名用小写保存。

创建

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])

BEGIN

  SQL语句;

END ;

调用

CALL 存储过程名称 ([ 参数列表 ]);

查看

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询指定数据库的存储过程及状态信息

SHOW CREATE PROCEDURE 存储过程名称; -- 查询某个存储过程的定义

删除

DROP PROCEDURE [ IF EXISTS ] 存储过程名称；

注意:

在命令行中，执行创建存储过程的SQL时，需要先通过关键字 delimiter 修改SQL语句的结束符（默认的分号与存储过程中的分号冲突），创建完再改回来，例如：

delimiter $$

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])

BEGIN

-- SQL语句

END$$

delimiter ;

CALL 存储过程名称 ([ 参数列表 ]);

带输入参数的存储过程：

例：传入一个员工的id，查询员工信息

DELIMITER $

CREATE PROCEDURE pro_findById(IN eid INT)

BEGIN

select * from employee where id=eid;

END $

--调用

CALL pro_findById(4);

带输出参数的存储过程：

DELIMITER $

CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))

BEGIN

set str='helljava';

END $

--调用

CALL pro_testOut(@name);-- 定义一个会话变量name以接收存储过程的返回值

select @name;-- 查看变量值（返回值）

带输入输出参数的存储过程

DELIMITER $

CREATE PROCEDURE pro_testInOut(INOUT n INT)

BEGIN

   select n;

   set n =500;

END $

set @n=10;

--调用

CALL pro_testInOut(@n); --执行此步输出10

select @n; --执行此步输出500

带输入输出参数的存储过程（使用查询的结果赋值（INTO）给变量）

DELIMITER $

CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )

BEGIN

select empName INTO vname from employee where id=eid;

END $

--调用

CALL pro_findById2(1,@name);

select @name;

带有判断条件的存储过程

输入一整数，若1，返回“星期一”,若2，返回“星期二”,余者返回“错误输入”：

DELIMITER $

CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))

BEGIN

IF num=1 THEN

set str='星期一';

ELSEIF num=2 THEN

set str='星期二';

ELSE

set str='输入错误';

END IF;

END $

--调用

CALL pro_testIf(4,@str);

select @str;

带有循环条件的存储过程

案例一：输入一整数求包括1到此整数之间所有整数的和：

DELIMITER $

CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)

BEGIN

-- 定义一个局部变量

DECLARE i INT DEFAULT 1;

DECLARE vsum INT DEFAULT 0;

WHILE i<=num DO

      set vsum = vsum+i;

      set i=i+1;

END WHILE;

set result=vsum;

END $

--调用

CALL pro_testWhile(100,@result);

select @result;

案例二：批量插入测试数据：

DELIMITER $

CREATE PROCEDURE insert100 ()

BEGIN

DECLARE i INT DEFAULT 1;

WHILE i <= 100 DO

INSERT INTO student(student_number, NAME) VALUES (i, concat('twx', i));

SET i = i + 1;

END WHILE;

END $

锁粒度

锁类型

具体锁

说明

触发SQL

解除SQL

全局锁

全局锁是对整个数据库实例加锁，加锁后整个实例处于只读状态，当前客户端的DML、DDL、已经更新操作的事务提交语句将失败，其他客户端的写将被阻塞。

其典型使用场景是做全库的逻辑备份，锁定所有表，保证数据一致性。

flush tables with read lock;

unlock tables;

表级锁

表锁

表共享读锁

所有客户端可读，当前事务不可写，其他事务写阻塞

lock tables 表名... read

unlock tables;

或客户端断开

表独占写锁

当前事务可读可写，其他事务读写阻塞

lock tables 表名... write

元数据锁

元数据共享锁

在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

DML

事务提交

元数据排他锁

DDL

意向锁

意向共享锁(IS)

与表共享锁(read)兼容

与表排他锁(write)互斥

任意意向锁之间兼容（不是真正的锁,只是表达了加锁的意向）

select ... lock in share mode

意向排他锁(IX)

与表共享锁(read)互斥

与表排他锁(write)互斥

任意意向锁之间兼容（不是真正的锁,只是表达了加锁的意向）

DML、

select...for update

行级锁

行锁

行共享锁(S)

共享锁之间兼容，排它锁之间互斥，共享锁与排它锁之间互斥。

默认隔离级别RR下，InnoDB使用临键锁进行索引扫描，以防止幻读。

1）不在索引上的查询，升级为表锁。

2）在唯一索引上的等值查询，记录存在时，优化为行锁。

3）在唯一索引上的等值查询，记录不存在时，退化为间隙锁。

4）在普通索引上的等值查询，向右遍历时最后一个值不满足查询需求时，退化为间隙锁。

select ... lock in share mode

行排他锁(X)

DML、

select...for update

间隙锁

临键锁