目录

环境及软件

安装目录

教程

启动Mysql服务

图形化

命令行

连接和断开服务器

图形化

命令行

SQL语句

介绍

分类

自带表介绍

注释

数据库的添加与删除

表的添加与删除

查询语法

基础查询

条件查询

排序查询

分组查询

去重

连接查询

内连接

外连接

多表连接

子查询

where子句中

from子句中

Select子句中

Unoin查询

Limit（分页查询）

DDL

Create

Drop

ALTER

DML

INSERT

UPDATE

DELETE

约束

非空约束

唯一约束

主键约束

外键约束

常见函数

mysql相关

单行处理函数

字符函数

数学函数

日期函数

流程控制函数

多行处理函数

存储引擎

MyISAM

InnoDB

Memory

事务

四大特性

隔离性

资源

参考

注：本文是实践性文章，请导入文中提到的数据到数据库，跟着文章做，只是看没有太大意义。先根据需求，自己写SQL语句，再与我给出的进行对比。

任务管理器

打开任务管理器->服务->找到MySQL57，右键启动即可

 计算机服务

我的电脑/此电脑/计算机，右键，管理->服务和应用程序->服务，找到MySQL57

同样是右键启动，双击进入属性（任务管理器方式也可右键，打开服务跳转到上图），选择启动类型（如果你最近一直使用Mysql，可以开机自启，即选择自动）。

使用管理员身份打开CMD窗口->输入命令

注意服务名字和你的一致

由于博主的已经启动了，所以显示上图，记下来，关一下再打开，使用命令

打开Navicat->连接 MySql->输入端口，用户名，密码等->连接测试，弹出成功窗口->点击确定即可

双击左侧的连接即可进入，右键 关闭连接即可关闭。

打开CMD，使用命令

进行连接和断开，-u之后加的是用户名，-p之后加的是密码，也可在-p后什么也不写，之后再写密码，会用"*"代替，防止别人看见。

输入命令

可查看更多命令选项

在使用图形化的时候有个 主机名 端口 需要填，命令行是省略了，可使用-h、-P选项，如果你想要连接远程的Mysql的话。

注：由于-u -h选项可以加空格后再加内容，但-p后不能加空格，所以博主干脆都不加了。

SQL语言不是Mysql独有的，sqlserver、Oracle也是使用SQL语言，但有一些区别，Mysql中特有的有LIMIT、show databases等。以分号结尾，不区分大小写！！！

在Mysql注入点知识讲了表Information_schema，有一些用户、表、列的数据，在注入后可以获得。

注释分为行注释、行中间注释和多行注释

# 这个注释直到该行结束

-- 这个注释直到该行结束

/* 这是一个在行中间的注释 */

/* 这是一个 多行注释的形式 */

命令行

 show create database lady_killer9;

可以看到使用字符集utf8

lady_killer9.sql脚本可从本文后面的资源一节下载。

缺点：sql脚本路径不能含中文

删除数据库

mysql> drop database lady_killer9; Query OK, 3 rows affected (0.11 sec)

图形化

新建查询->输入sql语句->运行即可

重连一下即可看见创建的数据库

也可这样创建数据库

选择数据库->运行SQL文件

 路径可以含中文

接下来就看看表的创建，Navicat 中运行SQL语句不再赘述，之后的图形化只会展示非SQL语句运行方式。

以EMP表为例，sql语句如下：

命令行：

 mysql> use lady_killer9; Database changed mysql> show tables; +------------------------+ | Tables_in_lady_killer9 | +------------------------+ | dept                   | | emp                    | | salgrade               | +------------------------+ 3 rows in set (0.00 sec)

mysql> drop table emp;

mysql> CREATE TABLE EMP(     -> EMPNO int(4) not null,     -> ENAME VARCHAR(10),     -> JOB VARCHAR(9),     -> MGR INT(4),     -> HIREDATE DATE DEFAULT NULL,     -> SAL DOUBLE(7,2),     -> COMM DOUBLE(7,2),     -> primary key (EMPNO),     -> DEPTNO INT(2)     -> ); Query OK, 0 rows affected (0.04 sec)

 图形化

删除表左侧按钮，新建表

点击添加字段来新建一行，

输入名，选择类型，输入长度，为浮点数的话输入小数点位数，点击不是null选框插入该字段时不能为空，键代表主键，注释可不写也可写名的中文含义等

下方为默认值，可不填，也可根据需要，对应DEFAULT

表的字段含义

emp表

dept表

salgrade表

SELECT     [ALL | DISTINCT | DISTINCTROW ]       [HIGH_PRIORITY]       [STRAIGHT_JOIN]       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]     select_expr [, select_expr ...]     [FROM table_references       [PARTITION partition_list]     [WHERE where_condition]     [GROUP BY {col_name | expr | position}       [ASC | DESC], ... [WITH ROLLUP]]     [HAVING where_condition]     [ORDER BY {col_name | expr | position}       [ASC | DESC], ...]     [LIMIT {[offset,] row_count | row_count OFFSET offset}]     [PROCEDURE procedure_name(argument_list)]     [INTO OUTFILE 'file_name'         [CHARACTER SET charset_name]         export_options       | INTO DUMPFILE 'file_name'       | INTO var_name [, var_name]]     [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT〈目标列组〉      FROM〈数据源〉

简单来说，就是从哪些表查什么条件的数据，查多少条，查到后怎么分组，怎么排序，是否将数据保存到文件。

最简单的，从某表查询所有数据

添加目标列，例如，查员工名，及年薪。

select ENAME,SAL * 12 from EMP;

由于一般情况下，项目的瓶颈是数据库，所以会查出数据后使用项目的编程语言来进行处理，不会直接乘12这种。

重命名，使用关键字AS，也可省略。

select ENAME as 员工名,SAL * 12 as 年薪 from EMP;

建议： 出现'员工名'、‘年薪’这种，可使用单引号，虽然也可以使用双引号，但是sqlserver、Oracle都是使用的单引号，所以统一一下，学习其他数据库的时候就简单点。

读者自行尝试：

select ENAME '员工名',SAL * 12 '年薪' from EMP;

根据条件进行筛选。

SELECT〈目标列组〉      FROM〈数据源〉      [WHERE 条件1 [AND [OR]] 条件2 ...]

设A为10、B为20。

读者自行尝试，不再截图。

查询薪资大于1000并且部门编号为20或30的员工名，薪资，部门编号

select ename,sal,deptno from emp where sal>1000 and deptno=20 or deptno=30;

看似没有问题，但是实际查询的是薪资大于1000并且部门编号为20的员工，以及部门编号为30的所有员工。原因是and的优先级高于or， sal>1000和deptno=20两个条件合并了。

可以通过加小括号的方式来进行限制，括号的优先级最高。

select ename,sal,deptno from emp where sal>1000 and (deptno=20 or deptno=30);

in 等同于 or，in 后面加的是等于多少的值所构成的数值元组，也可解决上面的问题

select ename,sal,deptno from emp where sal>1000 and deptno in (20,30);

select ename,sal,deptno from emp where sal>1000 and deptno in (20,30); 

not in 就是不在元组中

模糊查询like

查询名字中包含O的员工名

select ename from emp where ename like '%O%';

 查询名字第二个字母是'A'的员工名

select ename from emp where ename like '_A%';

 如果查找名字中含下划线或百分号的，可以使用转义字符 '\'

select ename from emp where ename like '%\_%';

当然，本数据表里面没有，以后碰到了记得使用转义字符即可。

SELECT〈目标列组〉      FROM〈数据源〉      [WHERE 条件1 [AND [OR]] 条件2 ...]      [ORDER BY {列名 | 表达式 | 位置}

查询工作岗位是 'MANAGER'的员工，并按工资升序排列

select * from emp where job = 'MANAGER' order by sal;

默认升序，需要降序添加关键字 desc 即可。 使用asc是升序，你可以像我一样省略。

多条件，逗号隔开，先按前，再按后。

查询员工姓名和工资，并按工资升序排列，如果工资相同，按名字降序排列。

select ename,sal from emp order by sal,ename desc;

位置，按结果的第几列排序，健壮性太差，位置一换就不行了，尽量不用。

select ename,sal from emp order by 2;

SELECT〈目标列组〉      FROM〈数据源〉      [WHERE 条件1 [AND [OR]] 条件2 ...]      [ORDER BY {列名 | 表达式 | 位置}      [GROUP BY {列名 | 表达式 | 位置}      [HAVING  条件子句]

分组函数

分组函数自动忽略null

查询津贴有几种情况

select count(comm) from emp;

从前面可以看到，comm除了null还有四个。

当出现group by时，select 之后只能加分组字段以及分组函数

按多个字段分组

查询每个部门不同工作岗位的最高薪资

select deptno,job,max(sal) from emp group by deptno,job;

分组函数不可直接使用在where子句中

查询工资高于平均工资的员工的名字和工资。

select ename,sal from emp where sal > avg(sal);

解决办法见子查询一节

查询每个工作岗位的最高薪资

select max(sal) from emp group by job;

分组函数在分组之后运行，先按job分组，之后再算每一组的最大值。

having 分组后的数据再次过滤

查询每个部门最高薪资，仅显示大于2900的部门编号及最大薪资。

select deptno,max(sal) from emp group by deptno having max(sal) > 2900;

运行顺序：

from 子句 -> where 子句 -> group by 子句 ->having子句 -> select列 子句 -> order by 子句

where 和having的选择

having的效率是比较低的

 select deptno,max(sal) from emp where sal > 2900 group by deptno;

上面也可以完成“查询每个部门最高薪资，仅显示大于2900的部门编号及最大薪资”， 而且where子句在 group by 子句前执行，过滤掉后再进行分组时会少写数据，但是having是在分组之后再运行。

所以，能用where优先使用where，不行再使用having

查询每个部门平均薪资，仅显示大于2900的部门编号及平均薪资

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2900;

 where子句是无法办到的，原因还是执行顺序。

除了分组，还可以使用关键字 distinct 来进行去重。

select distinct job from emp;

注：distinct只能出现在所有字段的最前方，多个字段则表示后面所有字段看做合并字段进行去重。

为防止数据冗余，会将有关系的数据放在不同的表中，表之间有关系，这也是关系型数据库的由来。

等值连接

连接条件是等量关系

查询每个员工的员工名和所在部门名

select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

 以上为92年的SQL语法，现在多使用92年的SQL语法，使用join on进行表连接

select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

请读者自行尝试。

非等值连接

连接条件是非等值关系

查找每个员工的工资等级，显示员工名，薪资，薪资等级

select e.ename,e.sal,s.grade from emp e join salgrade s on  e.sal between s.losal and hisal;

自连接

自己连自己

查询员工的上级领导，显示员工名、领导名（不必查出所有员工）

select e.ename,m.ename from emp e join emp m on e.mgr = m.empno;

注：join前省略了inner，读者可自行尝试

分为主表和副表，主要主表中的数据全部查询出来，当副表中的数据和主表的数据匹配不上，副表自动模拟出NULL与之匹配。

join前加left或right

左（外）连接

左表为主表

查询员工的上级领导，显示员工名、领导名（查出所有员工，没有领导时显示NULL）

select e.ename,m.ename from emp e left join emp m on e.mgr = m.empno;

注意，KING是大BOSS，没有领导，所以是NULL，另外，left和join之间省略了outer，读者可自行尝试。

右（外）连接

右表为主表

找出哪个部门没有员工

select d.* from emp e right join dept d on e.deptno = d.deptno where empno is null;

使用右连接，部门必全部查出来，如果没有员工，则员工号为NULL

当然，条条大路通罗马，也可以使用子查询。

select * from dept where deptno not in (select deptno from emp group by deptno);

看成多次两表连接即可。

查询每个员工的员工名，部门名，工资等级

select e.ename,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and hisal;

查询高于平均薪资的员工信息

select * from emp where sal > (select avg(sal) from emp);

可以看到，结果都是大于平均薪资的员工

查询每个部门平均薪资所属的薪资等级

select t.*,s.grade from (select deptno,avg(sal) avgsal from emp group by deptno) t join salgrade s where t.avgsal between s.losal and s.hisal;

将上面的查询结果，当做临时表t,临时表t与salgrade表进行连接，连接条件是平均工资在最低和最高工资之间。

查询每个员工所在部门的名称，显示员工名和部门名

使用表连接的话可以这样写：

select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

也可不进行表连接，写到select子句中

select e.ename,(select d.dname from dept d where e.deptno = d.deptno) from emp e; 

这个在SQL注入中是一种注入技巧

两个查询结果必须列数相同

查询工作岗位是SALESMAN和MANAGER的员工

select ename,job from emp where job = 'MANAGER' union select ename,job from emp where job = 'SALESMAN';

Mysql独有的，用于取结果的部分数据，sqlserver、Oracle中不能使用，Oracle中有rownum差不多。

limit [startIndex] length

查询工资前5名的员工名和工资

select ename,sal from emp order by sal desc limit 0,5;

select ename,sal from emp order by sal desc limit 5; 

每页显示pageSize条记录，pageNo从1开始，则

第pageNo页：?,pageSize  ==> ? 为 (PageNo - 1)*pageSize,pageSize

例如，pageSize = 3，pageNo = 1, limit 0,3

创建表

语法

常见字段类型

建立学生表t_student，学号 no，bigint类型，姓名 name，varchar类型，性别 sex char类型，班号 classno，varchar类型，生日 birth char类型

create table t_student(     no bigint,     name varchar(255),     sex char(1),     classno varchar(255),     birth char(10) );

从其他表获取

 create table table_name as select 语句;

创建emp1表， 仅包含emp表的ename和sal字段。

 create table emp1 as select ename,sal from emp;

语法

drop table [if exists] table_name;

使用图形化界面即可，例如Navicat 表右键->设计表，一般创建时很谨慎，设计好再创建，很少修改表结构。

语法

INSERT INTO table_name VALUES (值1, 值2,....),(值1, 值2,....),

我们也可以指定所要插入数据的列，这样，其他列就是默认值：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....),(值1, 值2,....)

insert into t_student values(1,'zhangsan','1','gaosan1ban','1996-11-20');

一次多行插入

insert into t_student values(2,'lisi','0','gaosan1ban','1996-10-20'),(3,'wangwu','1','gaosan1ban','1994-01-12'); 

从其他表插入

列，类型之类的要一样，很少用，提一下，不截图了。

insert into table_name  select 语句;

 创建dept1表与dept表数据一致，在dept1表后再插入一次dept表的所有内容

create table dept1 as select * from dept;

insert into dept1 select * from dept;

语法

update table_name set 字段名1=值1,字段名2=值2,... [where 语句];

 注意，没有条件，整张表全部更新。

将dept1表中部门编号为10的LOC字段改为BEIJING

update dept1 set loc = 'BEIJING ' where deptno = 10;

语法

delete from table_name [where 子句];

没有条件全部删除

删除部门20的所有数据

delete from dept1 where deptno = 20;

大表删除数据

对于亿级及以上的大表，删除快，但无法回滚

语法

truncate table table_name;

删除表dept1

truncate table dept1;

创建表时，添加相应约束

不能为null

create table t_user( id int, username varchar(255) not null, password varchar(255));

关键字：unique

单字段，列级约束

不能重复，但可以为NULL。可以认NULL和NULL不一样。

drop table if exists t_user; create table t_user( id int, username varchar(255) unique);

多字段唯一，表级约束

drop table if exists t_user; create table t_user( id int, username varchar(255), phonenum int,unique(username,phonenum));

可以看成将两个字段合并为一个组，组不能重复。

关键字：primary key

既不能为null，也不能重复

单一主键

drop table if exists t_user; create table t_user( id int primary key, username varchar(255),email varchar(255));

第二行由于主键约束，运行失败。

insert into t_user values(1,'zs','[email protected]'),(2,'ls','ls126.com');

insert into t_user values(2,'ww','[email protected]');

你可以尝试一下，id 为 null 是不是不能插入。

复合主键、业务主键

和unique表级约束语法差不多，primary key(字段1，字段 2)

不推荐，可能产生依赖或业务改变造成影响

自然主键

推荐，使用自然数1、2、3。。。，使用关键字auto_increment

id int primary key auto_increment

为解决数据冗余问题，将一张表的主键作为另一张表的外键，用于select时的表连接。

建一个学生表和班级表，学生表有一个外键班号，来自班级表

drop table if exists t_student; drop table if exists t_class; create table t_class( cno int, cname varchar(255), primary key(cno) ); create table t_student( sno int, sname varchar(255), classno int, foreign key(classno) references t_class(cno));

insert into t_class values(101,'xxxxxxxxxxxxxxx'); insert into t_class values(102,'yyyyyyyyyyyyyyy'); insert into t_student values(1,'zs1',101); insert into t_student values(2,'zs21',101); insert into t_student values(3,'zs4',102); insert into t_student(sno,sname) values(4,'zs5'); insert into t_student values(5,'zs6',103);

最后一句无法运行，因为103不在表t_class中。

 外键可以为NULL，可以理解为，学校来了个转学的学生，先添加进来，再分配班级。

下一节使用Navicat，有一些提示，一些函数名字过长，总是手打，浪费时间。

语法

select 函数名() [from 表名]

database()

返回当前使用数据库

version()

返回mysql版本

 user()、system_user()、session_user()

返回当前用户

长度相关

返回字符串字节长度

length(s) s:字符串

一个汉字是算三个字节，一个数字或字母算一个字节。这是在utf-8字符集下。

char_length(s)

s:字符串

不管汉字还是数字或者是字母都算是一个字节。

字符串拼接

CONCAT(s1,s2...sn)  字符串 s1,s2 等多个字符串合并为一个字符串

CONCAT_WS(x, s1,s2...sn)  同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符

大小写

UPPER(s)     将字符串转换为大写

LOWER(s)     将字符串 s 的所有字母变成小写字母

查询员工表员工名，小写展示

SELECT LOWER(ename) 员工名 from emp;

子串

SUBSTR(s, start [,length])     从字符串 s 的 start 位置截取长度为 length 的子字符串，位置从1开始。

SUBSTRING、MID 一样

SUBSTRING_INDEX(s, delimiter, number)     返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。 如果 number 是正数，返回第 number 个字符左边的字符串。 如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。

例如，查询'lady_killer9'中'_'左面的子串，'lady_killer9'中第二个'l'右边的子串

SELECT SUBSTRING_INDEX('lady_killer9','_',1),SUBSTRING_INDEX('lady_killer9','l',-2);

去空格

LTRIM(s)     去掉字符串 s 开始处的空格

RTRIM(s)     去掉字符串 s 结尾处的空格

TRIM(s)     去掉字符串 s 开始和结尾处的空格

填充

LPAD(s1,len,s2)     在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len

RPAD(s1,len,s2)     在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len

注：最终长度必为len，不够填充，多了截断

替换

REPLACE(s,s1,s2)     将字符串 s2 替代字符串 s 中的字符串 s1

前面去空格的时候，“Hello World”中间的空格没有去掉，可以使用replace进行替换

索引/位置

LOCATE(s1,s)     从字符串 s 中获取 s1 的开始位置 POSITION(s1 IN s)     从字符串 s 中获取 s1 的开始位置

比较字符串

STRCMP(s1,s2)     比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1执行记录到操作历史->提交或回滚（事务结束）

TCL语句，

第一级别：读未提交（read uncommitted)

对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。 读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。

查看全局事务级别

select @@global.tx_isolation;

设置全局事务级别为 读未提交

set global transaction isolation level read uncommitted;

开启事务

start transaction;

插入id为3的记录的事务还未提交，但是却可以读到数据。第二级别：读已提交（read committed)

对方事务提交之后的数据我方可以读取到。oracle数据库默认。 这种隔离级别解决了：脏读现象没有了。 读已提交存在的问题是：不可重复读。

设置全局事务级别为 读已提交

set global transaction isolation level read committed;

注意，要开两个控制台，不然未提交的还会显示。

id为3的数据提交之后才可以读。第三级别：可重复读（repeatable read)

这种隔离级别解决了：不可重复读问题。mysql数据库默认。

这种隔离级别存在的问题是：读取到的数据是幻象。

设置全局事务级别为 可重复读

set global transaction isolation level repeatable read;

id为3的数据删除，且已提交。 但是仍可读取已删除了的id为3的数据。

第四级别：序列化读/串行化读(serializable)

解决了所有问题，但效率低。因为需要事务排队，必须一个事务commit之后，其他事务才能继续。

lady_killer.sql以及参考手册等资源

百度网盘

提取码: xbkm

mysql 5.7 官方手册