MySQL命令语法

MySQL命令/语法

MySQL命令/语法

启动 MySQL服务

net start mysql

退出MySQL服务

net stop mysql

查看MySQL中有哪些数据库

show databases;

注意英文的分号

选择需要操作的数据库

use 操作的数据库名;

创建数据库

create database 数据库名

查看当前使用的数据库

select database();

查看数据库中有哪些表

show tables;

导入表

source 资源路径

导入的文件是.sql结尾的mysql脚本文件。

查看表结构

desc 表名;

查询所有字段

方法一：select 字段名a,字段名b,字段名c... from 表名

依次列出表中的所有字段
推荐使用这种方式
优点查询速度快，缺点挨个写字段麻烦。

方法二：select * from 表名

使用通配符代替所有字段
不推荐使用这种方式，因为通配符会自动转换成字段名需要时间，测试的时候可以使用
优点方便快捷，缺点查询时间慢

给列起别名

select 字段名 as 别名 from 表名;

使用as关键字起别名，有多条列需要起别名使用逗号,隔开
别名中有中文或者空格，使用单引号''括起来
as关键字可以省略用空格代替，可读性会差点
这个别名只会在输出显示中生效，实际表中的列并未被修改

列参与数学运算

select 字段名*12 from 表名;

可以直接在字段名后加上数学运算

条件查询

select 字段1,字段2,字段3... from 表名 while 条件;

例：查询A表中字段1大于5且小于10的数。

select 
	字段1 
from 
	A表 
where 
	字段1 > 5 && 字段1 < 10;

需要查询多个条件可以使用逻辑运算符
&&也可以使用and

between and用法

between ... and ...

例：查询A表中字段1大于等于5且小于等于10的数。

select 
	字段1 
from 
	A表 
where 
	字段1 between 5 and 10;

between ... and ...等于>= and <=
使用between and时需要注意数据左小右大

注意：在数据库中查询值为null的字段时不能使用等号=，需要使用is null。因为null指的是什么也没有，它不是一个值，所以不能使用等号匹配。

条件查询中`in`的用法

select 字段名 from 表名 where 字段名 in(条件1,条件2)

例：查询A表中字段1中age为18和25的人。

select
	字段1
from
	A表
where
	age in(18,25);

注意：例子中18和25查找的不是18到25中的所有值

in代表的不是一个区间，代表的是指定的条件或数值
not in则是取反

模糊查询

要点：使用like关键字后接下划线或者百分号来代替任意字母作为模糊对象；

下划线_：用于代替一个字符
百分号%：用于代替任意数量的字符

需要查询字段中包含下划线或百分号，使用反斜杠\进行转义后即可查询

百分号例子：

例1：查询字段中含有A字母的字段。

select
	字段名
from
	表名
where
	查询的字段名 like '%A%';

%A%：第一个百分号代表前面有任意字符；其中包含字母A；第二个字母代表后面也含有任意字符

例2：查询以B字母结尾的字段。

select
	字段名
from
	表名
where
	查询的字段名 like '%B';

%B：百分号代表任意字符最后结尾是B的作为条件
B%：这样就是查找B开头的字段

下划线例子：

例1：查找第二个字母是C的字段。

select
	字段名
from
	表名
where
	查询的字段名 like '_C';

_C：下划线代替前任意一个字符，第二位限定为C

例2：查找第四个字母是D的字段

select
	字段名
from
	表名
where
	查询的字段名 like '___D';

条件D前有几个字符就加几个下划线代替

排序查询

升序(ascend)

select 查哪个字段 from 在哪个表查 order by 需要排序的字段;

关键词order by默认是升序(从低到高)
完整的指定升序可以在结尾加上asc，例如``select 查哪个字段 from 在哪个表查 order by 需要排序的字段 asc;

降序(descend)

select 查哪个字段 from 在哪个表查 order by 需要排序的字段 desc;

降序在需要排序的字段后加上desc，叫做指定降序。

多字段排序

select 字段名A,字段B from 表名 order by 需要排序的字段A asc,需要排序的字段B desc;

多字段排序优先按照第一个条件(字段A)来排序，当第一个条件(字段A)相等的情况下再按照第二个条件(字段B)来排序

根据字段位置排序

select 字段名A,字段B from 表名 order by 2

这里的2表示第二列(字段B)，查询结果按照字段B进行排序
这种写法不健壮，一旦列的顺序发生改变，2就会发生改变
作为了解，不推荐使用

单行数据处理函数

数据处理函数也被称为单行处理函数

特点：一个输入对应一个输出。

常见的单行处理函数：

函数名	作用
lower	转换小写
upper	转换大写
substr	取子串`substr(需要截取的字符串,起始下标,截取长度)`
length	取长度
trim	去除空格
str_to_date	将字符串转换为日期
date_format	格式化日期
format	设置千分位
round	四舍五入
rand()	生成随机数
ifnull	可以将`null`转换成一个具体值

例：大小写转换

select 
	lower(字段名)
from
	表名;

转换大写更换函数名

例：取子串

select
	substr(字段名,起始下标,截取长度)
from
	表名;

下标从1开始

例：取长度

select 
	length(字段名) 
from 
	emp;

例：`ifnull()`函数用法

select
	ifnull(字段名,0)
from
	表名

ifnull()函数可以将字段名中值为null的值转换为0，当作0来处理
在数学运算中一旦有null的值参与运算，结果必定为null，这时候就需要ifnull函数来进行转换

多行处理函数

特点：输入多行，最终输出一行

常见的多行处理函数：

函数名	作用
count	计数
sun	求和
avg	平均值
max	最大值
min	最小值

注意：

分组函数在使用时必须先进行分组，然后才能用
若未对数据进行分组，则默认整张表为一组
分组函数自动忽略NULL
分组函数不能直接使用在where条件判断语句中
分组函数可以进行组合使用

使用方法和单行处理函数一致

分组查询

实际应用中，可能会出现这样的需求，需要先对每一组数据进行分组，然后对每一组的数据进行操作。这时候就需要进行分组查询。

语法：

select
	字段名
from
	表名
group by
	字段名

例：查询A表中每个岗位的工资和

select 
	sum(工资字段)
from 
	A表
group by
	岗位字段;

执行过程：从A表中对岗位字段进行分组，计算工资字段的和查询出来

关键字组合在一起时的执行顺序

select		④
	...
from		①
	...
where		②
	...
group by	③
	...
order by 	⑤
	desc

`having`关键字

使用having可以对分完组之后的数据进一步过滤，但它不能代替where，只能和group by进行联合使用。

例：找出每个部门中最高薪资，只查询最高薪资大于3000的。

select
	部门字段,max(工资字段)
from
	表名
group by
	部门字段
having
	max(工资字段) > 3000;

执行过程：从表中对部门字段进行分组，having条件是最大工资字段是3000以上的进行筛选，最后查询显示出部门字段和筛选后的最大工资字段。

having筛选后小于3000的数据就舍弃了，不效率。

优化：

select 
	部门字段,max(工资字段) 
from 
	表名 
where 
	工资字段 > 3000 
group by 
	部门字段;

直接在分组前就筛选出大于3000的数据，效率高
在where和having中优先使用where进行过滤

查询数据去重

关键字：distinct

对查询的结果去除重复记录
destinct只能出现在去重的字段前

例：

select distinct 去重的字段名 from 表名;

笛卡尔积现象

当两张表进行连接查询，没有任何条件限制的时候，最终查询结果的条数，是两张表条数的乘积，这种现象被称之为笛卡尔积现象。

笛卡尔发现的一种数学现象

通过笛卡尔积现象可以得出，表的连接次数越多，效率越低

如何避免笛卡尔积现象出现

连表查询时附加条件进行限制，将满足这个条件的记录筛选出来

连接查询

对多张表联合起来进行查询数据，称之为连接查询

内连接(inner)

连接的两张表，没有主次关系，关系是平等的

inner关键字加在join关键字前，可省略

等值连接

条件是等量关系，所以称之为等职连接

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

EMP表：

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

DEPT表：

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

SQL92语法：

select 
	d.dname,
	e.ename 
from 
	emp e,
	dept d 
where 
	e.deptno = d.deptno and 后续筛选条件;

SQL92缺点：

表连接的条件和后续的筛选条件都放到了where后面，杂糅在一起结构不清晰

SQL99语法：

select 
	d.dname,
	e.ename 
from 
	emp e
inner join	// inner可以省略
	dept d 
on
	e.deptno = d.deptno
where
	后续筛选条件;

SQL99优点：

表连接的条件是独立的，连接之后如果还需要进一步筛选，可以继续添加where筛选过滤
join关键字前的inner不省略可读性更好，可以一眼看出是内连接

非等值连接

条件不是一个等量关系，称之为非等值连接

示例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级。

EMP表：

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

SALGRADE表：

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

查询过程：

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

自连接

单张表自己连接自己，称之为自连接

示例：查询员工的上级领导，要求显示员工名和对应的领导名。

EMP表：

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

查询过程：

先找出需要的条件

select 
	empno,	// 员工编号
	ename,	// 姓名
	mgr 	// 上级领导编号
from 
	emp;

+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+

把一张表看作是两张表，emp as a看作是员工表，emp as b看作是领导表，连接条件是a表的领导编号等于b表的员工编号

select
	a.ename '员工名',
	b.ename '领导名'
from
	emp a
join
	emp b
on
	a.mgr = b.empno;

+-----------+-----------+
| 员工名    | 领导名    |
+-----------+-----------+
| SMITH     | FORD      |
| ALLEN     | BLAKE     |
| WARD      | BLAKE     |
| JONES     | KING      |
| MARTIN    | BLAKE     |
| BLAKE     | KING      |
| CLARK     | KING      |
| SCOTT     | JONES     |
| TURNER    | BLAKE     |
| ADAMS     | SCOTT     |
| JAMES     | BLAKE     |
| FORD      | JONES     |
| MILLER    | CLARK     |
+-----------+-----------+
13 rows in set (0.00 sec)

KING没有被匹配，需要使用外连接

外连接(outer)

连接的两张表有主次关系，左外连接则join关键字左边的表是主表，右外连接则类似

在join关键字前使用leftORright关键字来区分左右连接，同时定位主表在哪个位置
主表就是那个表的所有匹配的全查出来
任何一个左连接都有右连接的写法
任何一个右连接都有左连接的写法

左外连接(左连接)

示例：查询员工的上级领导，要求显示员工名和对应的领导名。

和自连接的示例一样，这次要把KING的值查询出来

筛选过的EMP表：

 select empno,ename,mgr from emp;

+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+
14 rows in set (0.00 sec)

查询过程：

左外查询语法：

select
	a.ename '员工名',b.ename '领导名'
from
	emp a left join emp b
on
	a.mgr = b.empno;

right：表示左边的a表是主表，把这个表中全部数据查询出来，顺带查询右边的表

右外查询语法：

select
	a.ename '员工名',b.ename '领导名'
from
	emp b right outer join emp a
on
	a.mgr = b.empno;

outer：表示外连接，可省略，左外查询语法中就省略了，写上只是为了增加可读性
示例的左外和右外查询结果一致，left和right关键字只是用于区分主表位置

查询结果：

+-----------+-----------+
| 员工名    | 领导名    |
+-----------+-----------+
| SMITH     | FORD      |
| ALLEN     | BLAKE     |
| WARD      | BLAKE     |
| JONES     | KING      |
| MARTIN    | BLAKE     |
| BLAKE     | KING      |
| CLARK     | KING      |
| SCOTT     | JONES     |
| KING      | NULL      |
| TURNER    | BLAKE     |
| ADAMS     | SCOTT     |
| JAMES     | BLAKE     |
| FORD      | JONES     |
| MILLER    | CLARK     |
+-----------+-----------+
14 rows in set (0.00 sec)

通过外连接KING的值也被查询出来了

右外连接(右连接)

和左外连接类似，详情查看左外连接

全连接(暂时忽略)

多表连接

多张表格连接在一起查询

语法：

select
	...
from
	A表
join
	B表
on
	A表和B表的连接条件
left join
	C表
on
	A表和C表的连接条件
right join
	D表
on
	A表和D表的连接条件
......

一条SQL中内连接和外连接可以混合使用

示例：找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级。

EMP表：

员工数据
e表

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

DEPT表：

工作岗位
d表

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

SALGRADE表：

薪资等级
s表

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

思路：

找出表连接条件
- e表和d表的DEPTNO部门编号相同
- e表的sal工资在s表的lowsal最低工资和hisal最高工资之间

编写SQL

select 
	e.ename '员工名',
	d.dname '部门名称',
	e.sal '薪资',
	s.grade '薪资等级'
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on
	sal between s.losal and s.hisal;

子查询

select语句中嵌套select语句，被嵌套的select语句称之为子查询

子查询可以出现的地方

select
	... select ...
from
	... select ...
where
	... select ...

`where`子句中的子查询

示例：找出比最低工资高的员工姓名和工资。

员工数据表：

+--------+---------+
| 姓名   | 工资    |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
14 rows in set (0.00 sec)

错误示范：

select 
	ename '姓名',
	sal '工资'
from
	emp
where
	sal > min(sal);
	
报错：ERROR 1111 (HY000): Invalid use of group function

在分组查询中介绍的那样，where关键字后不能使用分组函数

正确示范：

找到最低工资是多少

select min(sal) '最低工资' from emp;

+--------------+
|  最低工资     |
+--------------+
|   800.00     |
+--------------+

找出大于800的员工和工资

select ename '姓名',sal '工资' from emp where sal > 800;

+--------+---------+
| 姓名   | 工资    |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.02 sec)

SQL语句合并

select 
	ename '姓名',
	sal '工资' 
from 
	emp 
where 
	sal > (select min(sal) from emp);

别名加不加无所谓，上面我省略了别名

`from`子句中的子查询

技巧：可以将from后面的子查询查询结果当作一张临时的表

示例：找出每个岗位的平均工资的薪资等级

EMP表：

员工信息
e表

+--------+-----------+---------+
| ename  | job       | sal     |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 |
| WARD   | SALESMAN  | 1250.00 |
| JONES  | MANAGER   | 2975.00 |
| MARTIN | SALESMAN  | 1250.00 |
| BLAKE  | MANAGER   | 2850.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| TURNER | SALESMAN  | 1500.00 |
| ADAMS  | CLERK     | 1100.00 |
| JAMES  | CLERK     |  950.00 |
| FORD   | ANALYST   | 3000.00 |
| MILLER | CLERK     | 1300.00 |
+--------+-----------+---------+
14 rows in set (0.00 sec)

SALGRADE表：

薪资等级
s表

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

思路：

找出每个岗位的平均工资

使用分组函数按照job工作岗位进行分组，再求sal工资的平均值

select 
	job '工作岗位',
	avg(sal) '平均工资'
from
	emp
group by
	job;
	
select job '工作岗位',avg(sal) '平均工资' from emp group by job;

+--------------+--------------+
| 工作岗位      | 平均工资      |
+--------------+--------------+
| CLERK        |  1037.500000 |
| SALESMAN     |  1400.000000 |
| MANAGER      |  2758.333333 |
| ANALYST      |  3000.000000 |
| PRESIDENT    |  5000.000000 |
+--------------+--------------+
5 rows in set (0.00 sec)

将查询到的平均工资表当作一张临时表 T，和SALGRADE薪资等级表 s进行表连接，连接条件是平均工资在最低和最高工资之间
- 错误示范：
```
select
	t.job,
	t.avg(sal),
	s.GRADE
from
	(select job,avg(sal) from emp group by job) as T
join
	salgrade as s
on
	t.avg(sal) between s.losal and s.hisal;
```
  错误点：
  - 临时表T.avg(sal)这个字段是多行处理函数，未进行分组无法使用，可以给它起个别名就能正常使用了
- 正确示范：
```
select
	t.job,
	t.avgsal,
	s.GRADE
from
	(select job,avg(sal) as avgsal from emp group by job) as T
join
	salgrade as s
on
	t.avgsal between s.losal and s.hisal;
```

`select`子句中的子查询(只做了解)

示例：找出每个员工的部门名称，要求显示员工名，部门名。

EMP表：

员工名称

+--------+--------+
| ename  | deptno |
+--------+--------+
| SMITH  |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| JONES  |     20 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| SCOTT  |     20 |
| KING   |     10 |
| TURNER |     30 |
| ADAMS  |     20 |
| JAMES  |     30 |
| FORD   |     20 |
| MILLER |     10 |
+--------+--------+
14 rows in set (0.00 sec)

DEPT表：

部门名

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

查询过程：

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

注意：

对于select后面的子查询来说，这个子查询只能一次返回一条结果，超过则字段匹配不上报错

也可以用内连接进行查询：

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

`union`合并查询结果集

优点：查询效率高

示例：查询工作岗位是MANAGER和SALESMAN的员工。

EMP表：

+--------+-----------+
| ename  | job       |
+--------+-----------+
| SMITH  | CLERK     |
| ALLEN  | SALESMAN  |
| WARD   | SALESMAN  |
| JONES  | MANAGER   |
| MARTIN | SALESMAN  |
| BLAKE  | MANAGER   |
| CLARK  | MANAGER   |
| SCOTT  | ANALYST   |
| KING   | PRESIDENT |
| TURNER | SALESMAN  |
| ADAMS  | CLERK     |
| JAMES  | CLERK     |
| FORD   | ANALYST   |
| MILLER | CLERK     |
+--------+-----------+
14 rows in set (0.00 sec)

常规写法：

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

条件查询写法

select 
	ename,
	job 
from 
	emp 
where 
	job in ('MANAGER','SALESMAN');

`union`写法

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

以上查询结果都相同，其中union的效率要高一些。对于表连接来说每一次连接新表，则匹配的次数满足笛卡尔积，次数成倍的翻。使用union的时候可以减少匹配的次数，同时还可以将两个表的结果集进行拼接。

例：

表连接

A表连接 B表连接 C表

A表：10条记录

B表：10条记录

C表：10条记录

匹配次数：10 * 10 * 10 = 1000 次
union

A表连接 B表：10 * 10 = 100 次

A表连接 C表：10 * 10 = 100 次

union是对结果集进行拼接，使得乘法换为加法运算

匹配次数：100 + 100 = 200 次

注意事项

union在使用时要求两个结果集拼接时，两个结果集的列数相同
两个结果集拼接时，列和列的数据类型要一致。(mysql中无所谓，oracle中语法严格会报错)

`limit`

作用：将查询结果的一部分显示出来，通常在分页查询中使用

语法

limit startIndex,length

startIndex：起始下标
length：长度

缺省写法：

limit length

起始下标默认是0开始

示例：按照薪资降序，找出排名在前5名的员工。

EMP表：

+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
14 rows in set (0.00 sec)

查询过程：

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

+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

如此一来就能直接按照降序查询前五条记录

例2：查出工资排名在[3-5]名的员工

select
	ename,
	sal
from 
	emp
order by
	sal desc
limit 2,3;

2表示下标从2开始，因为下标从0开始，下标2就是第三条记录
3表示长度，从下标2开始向后取3条记录

分页查询通用公式

limit (页数 - 1) * 每页显示记录条数,每页显示记录条数

limit (pageNO - 1) * pagesize,pagesize

示例：

每页显示3条记录

第一页：limit 0,3 [0 1 2] limit (1-1)*3,3

第二页：limit 3,3 [3 4 5] limit (2-1)*3,3

第三页：limit 6,3 [6 7 8] limit (3-1)*3,3

注意事项

mysql中limit是在order by之后执行

建表(DDL)

语法

create table 表名(字段名1 数据类型,字段名2 数据类型,字段名3 数据类型);

create table 表名(
    字段名1 数据类型,
    字段名2 数据类型,
    字段名3 数据类型
);

表名：建议以 t_ 或者 tbl_ 开始，可读性强
字段名：见名知意
default：建表时，在字段后加上可以指定默认值

关于mysql中的数据类型

在mysql中有很多的数据类型，只需要掌握一些常见的即可

varchar

最长：255

可变长的字符串，可以根据实际数据长度动态分配空间，不确定数据长度时使用

优点：节省空间

缺点：需要动态分配空间，速度慢

char

最长：255

定长字符串，不管实际数据长度是多少，分配的空间都是固定的，确定数据长度时使用

优点：不需要动态分配空间，速度快

缺点：使用不当会造成空间浪费

int

最长：11

数字中的整数型。等同于java中的int

bigint

数字中的长整型。等同于java中的long

float

单精度浮点型数据

double

双精度浮点型数据

date

短日期类型

datetime

长日期类型

date和datetime的区别

date短日期：只包括年Y、月m、日d信息

datetime长日期：包括年Y、月m、日d、时h、分m、秒s

获取系统当前时间

now()函数

获取到的时间带有时分秒的信息，是datetime类型的

clob

CLOB：Character Large Object

字符大对象，最多可以存储4G的字符串，超过255个字符的都要采用CLOB字符大对象来存储

例如：存储一篇文章，存储一个说明

blob

BLOB：Binary Large Object

二进制大对象，专门用于存储图片、声音、视频等流媒体数据

往BLOB类型的字段上插入数据的时候，需要使用IO流才行。

例如：插入一个图片、视频等

快速复制表

create table 复制的新表名 as select * from 被复制的表名;

原理：以一个查询的结果创建一张表

以此可以实现快速复制表
通过结合select语句，也可以将指定的字段进行复制生成新表
表中的数据也会同时进行复制

删表

语法

常规：

drop table 表名;

当删除的表不存在的时候会报错

推荐使用：

drop table if exists 表名;

这种删法不会报错

插入数据`insert`(DML)

语法

insert into 表名(字段名1,字段名2,字段名3) values(值1,值2,值3);

字段名和值要对应
没有给字段指定值，则默认为NULL，建表时可以指定默认值
insert语句一旦执行成功必定增加一条记录

省略字段名写法

insert into 表名 values(值1，值2，值3)

值、数量、数据类型一定得和字段名一致
字段名省略相当于所有字段都写上，相应的后面的值也得全写上

同时插入多条数据

insert into 表名(字段名1,字段名2) values(),(),();

用小括号把多条数据的值分开

查询结果插入一张表中(了解)

insert into 需要插入的表名 select * from 查询的表名

查询的结果要插入表中需要注意，结果的长度、数据类型要一致

修改数据`update`(DML)

语法

update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3 where 条件;

未设置条件会导致所有数据全部更新

删除数据`delete`(DML)

语法

delete from 表名 where 条件;

未设置条件会导致所有数据全部删除
表中的数据删除了，但硬盘上的真实存储空间不会被释放

优点：支持回滚，后悔了可以恢复数据

缺点：删除效率比较低

`truncate`语句删除数据(DDL)

语法

truncate table 表名;

这种删除效率比较高，表被一次截断，物理删除，删除大量数据时有奇效(删库跑路)，且无法恢复，执行前请再次确认。

优点：删除效率高，快速

缺点：不支持回滚

删除表

语法

drop table 表名

不是删除表中的数据，而是把整个表删除

表结构的增删改

实际开发中，表结构一旦确定就很少进行修改

略，仅了解

约束(重要)

约束/constraint，建表的时候我们可以给表中的字段添加约束，来保证这个表中数据的完整性、有效性。

not null(非空约束)

非空约束not null约束的字段不能为NULL
非空约束只有列级约束，没有表级约束

例：

在需要添加约束的字段后添加not null约束

drop table if exists t_student;
create table t_student(
    id int,
    name varchar(255) not null
);

insert into t_student(id,name) values(1,'zhangsan');
insert into t_student(id,name) values(2,'lisi');

一旦该字段没有数据就会报错

insert into t_student(id) values(3);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

unique(唯一性约束)

唯一性约束unique的字段不能重复，但是可以为NULL

例：

在需要添加约束的字段后添加unique约束

drop table if exists t_student;
create table t_student(
    id int,
    name varchar(255) unique,
    email varchar(255)
);

insert into t_student(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_student(id,name,email) values(2,'lisi','lisi@123.com');
insert into t_student(id,name,email) values(3,'wangwu','wangwu@123.com');

select * from t_student;

一旦该字段数据出现重复就会报错

insert into t_student(id,name,email) values(3,'wangwu','wangwu@163.com');
ERROR 1062 (23000): Duplicate entry 'wangwu' for key 't_student.name'

表示wangwu这个字段重复了
没有not null约束，可以为NULL

表级约束(多字段联合唯一)

需要给多个字段联合添加约束时使用

情况描述：一个人有两个或多个邮箱，以此创建一张表

错误写法：

drop table if exists t_student;
create table t_student(
    id int,
    name varchar(255) unique,
    email varchar(255) unique // 列级约束
);

这样的写法只能约束单个字段不能唯一

正确写法：

drop table if exists t_student;
create table t_student(
    id int,
    name varchar(255),
    email varchar(255),
    unique(name,email)	// 表级约束
);

约束没有添加在列的后面，这种约束称之为表级约束

如此一来就可以使name字段相同，enail字段不同的数值插入数据库

insert into t_student values(1,'zhangsan','zhangsan@123.com');
insert into t_student values(2,'zhangsan','zhangsan@126.com');
insert into t_student values(3,'lisi','zhangsan@126.com');

只有两个字段都相同的数据插入时才会报错

insert into t_student values(2,'zhangsan','zhangsan@126.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@126.com' for key 't_student.name'

primary key(主键约束)

primary key，简称PK

主键

主键值是每一行记录的唯一标识
任何一张表都应该有主键，没有主键，表无效

自然主键：主键值是一个自然数，只作为标识，和业务逻辑无关

业务主键：主键值和业务逻辑紧密关联，例如银行卡号作为主键

通常自然主键使用较多，主键只要做到不重复就行，不需要有意义

主键的特征

not null和unique：不能为空，也不能重复

非空
唯一

主键值使用的一般都是定长的数据类型

推荐使用：int、bigint、char

不推荐使用：varchar

添加主键约束

列级约束方式

drop table if exists t_student;
create table t_student(
    id int primary key,
    name varchar(255)
);

表级约束方式

drop table if exists t_student;
create table t_student(
    id int,
    name varchar(255),
    primary key(id)
);

复合主键(了解)

使用表级约束将多个字段联合起来作为主键，主键还是一个

实际开发中不建议使用复合主键

`auto_increment`(主键自增)

自动维护主键

drop table if exists t_student;
create table t_student(
    id int primary key auto_increment,
    name varchar(255)
);

以1开始自动递增
建表语句结尾可以使用auto_increment=数值这样可以设置从几开始自增

foreign key(外键约束)

外键约束：foreign key、简称FK

示例：设计一个数据库表，描述班级和学生的关系

第一种方案：学生和班级放在同一张表中

学生班级表：

drop table if exists t_class;
create table t_class(
	No int primary key auto_increment,
    name varchar(255),
    classNo char(3),
    grade varchar(255)
);

insert into t_class(name,classNo,grade) values('张三','100','高一');
insert into t_class(name,classNo,grade) values('李四','100','高一');
insert into t_class(name,classNo,grade) values('王五','200','高二');
insert into t_class(name,classNo,grade) values('赵六','200','高二');
insert into t_class(name,classNo,grade) values('孙七','300','高三');
insert into t_class(name,classNo,grade) values('周八','300','高三');
select * from t_class;

+----+--------+---------+--------+
| No | name   | classNo | grade  |
+----+--------+---------+--------+
|  1 | 张三   | 100     | 高一   |
|  2 | 李四   | 100     | 高一   |
|  3 | 王五   | 200     | 高二   |
|  4 | 赵六   | 200     | 高二   |
|  5 | 孙七   | 300     | 高三   |
|  6 | 周八   | 300     | 高三   |
+----+--------+---------+--------+
6 rows in set (0.00 sec)

缺点：数据冗余，空间浪费

第二种方案：年级设置为一张表，学生设置为一张表

年级表：

drop table if exists t_grade;
create table t_grade(
	classNo int primary key,
    grade varchar(255)
);

insert into t_grade(classNo,grade) values(100,'高一');
insert into t_grade(classNo,grade) values(200,'高二');
insert into t_grade(classNo,grade) values(300,'高三');
select * from t_grade;

+---------+--------+
| classNo | grade  |
+---------+--------+
|     100 | 高一   |
|     200 | 高二   |
|     300 | 高三   |
+---------+--------+
3 rows in set (0.00 sec)

学生表：

drop table if exists t_student;
create table t_student(
	no int primary key auto_increment,
    name varchar(255),
    cno int,
    foreign key(cNo) references t_grade(classNo)
);

insert into t_student(name,cno) values('张三','100');
insert into t_student(name,cno) values('李四','100');
insert into t_student(name,cno) values('王五','200');
insert into t_student(name,cno) values('赵六','200');
insert into t_student(name,cno) values('孙七','300');
insert into t_student(name,cno) values('周八','300');
select * from t_student;

+----+--------+------+
| no | name   | cno  |
+----+--------+------+
|  1 | 张三   |  100 |
|  2 | 李四   |  100 |
|  3 | 王五   |  200 |
|  4 | 赵六   |  200 |
|  5 | 孙七   |  300 |
|  6 | 周八   |  300 |
+----+--------+------+
6 rows in set (0.00 sec)

示例中的cno字段没有外键进行约束的时候，可能会出现数据无效，例如出现一个值为400的数据，而年级表中没有400对应的年级。这时候添加外键约束就能保证cno字段中的每一个值都是规定好的外键值。

注意

使用了外键约束就会出先父子表的关系
被引用的表是父表，例如示例中的t_grade表就是父表
引用其他表的表则是子表，例如示例中的t_student表就是子表
创建表的顺序：先建父，再建子
删除表的顺序：先删子，再删父
插入数据的顺序：先插父，再插子
删除数据的顺序：先删子，再删父
子表中的外键引用父表中的某个字段，被引用的字段不一定是主键，但至少具有unique约束，具有唯一性
外键可以为NULL

check(检查约束，mysql不支持，oracle支持)

mysql中不支持略过

存储引擎

存储引擎是MySQL中特有的术语，其它数据库中没有(Oracle中有但不叫这个名字)
存储引擎是一种表的存储/组织数据的方式
存储引擎有很多种，每种存储引擎表的存储方式也不同

查看表的存储引擎

show create table 表名

这个命令能查看建表语句，里面有存储引擎的信息

指定存储引擎

在建表语句结束时添加ENGINE=指定的存储引擎来指定需要使用的存储引擎

mysql默认的存储引擎是InnoDB
mysql默认的字符编码是utf8

示例：

create table temp(
	id int primary key,
    name varchar(255)
)engine=innodb default charset=utf8;

查看mysql支持的存储引擎

show engines \G

MyISAM

使用三个文件表示一个表

格式文件：存储表结构的定义(mytable.frm)

数据文件：存储表行的内容(mytable.MYD)

索引文件：存储表上索引(mytable.MYI)

特征：

灵活的AUTO INCREMENT字段处理
可被转换为压缩、只读表来节约空间

InnoDB

mysql的默认存储引擎

InnoDB支持事务，支持数据库崩溃后自动恢复机制

InnoDB存储引擎最主要的特点是：安全

特征：

每个InnoDB表在数据库目录中以.frm格式文件表示
InnoDB表空间tablespase被用于存储表的内容
提供一组来记录事务性活动的日志文件
用commit(提交)、savepoint及rollback(回滚)支持事务处理
提供全ACID兼容
在mysql服务器崩溃之后提供自动恢复
多版本(MVCC)和行级锁定
支持外键及引用的完整性，包括级联删除和更新

MEMORY

使用memory存储引擎的表，其数据存储在内存中，且行的长度固定，因为这两个特点memory存储引擎效率非常高，memory存储引擎以前被称为HEAP引擎

特征：

在数据库目录内，每个表均以.frm格式的文件表示
表数据以及索引被存储在内存中
表级锁机制
不能包含TEXT或BLOB字段
查询效率高
不安全，数据、索引存在内存中，断电即失

事务(transaction)

一个事务就是一个完整的业务逻辑，是最小的工作单元，不可再分。

只有DML语句(增、删、改)才有事务一说

事务的四个特性：

原子性：说明事务是最小的工作单元，不可再分
一致性：在同一个事务当中，所以操作必须同时成功，或者同时失败，以保证数据的一致性
隔离性：A事务和B事务之间具有一定的隔离
持久性：事务最终结束的一个保障。事务提交，也就相当于将没有保存到硬盘上的数据保存到硬盘上

重点了解隔离性

例如A教室和B教室中间有一堵墙，这道墙可厚可薄。这就代表了事务隔离级别的高低。

事务于事务之间隔离级别有四种

读未提交：read uncommitted（最低隔离级别）（没提交就读到了）

事务A可以读取到事务B未提交的数据

可能出现的问题：脏读现象(Dirty Read)

这种隔离级别一般是理论上的，大多数数据库的隔离级别都是二档以上的
读已提交：read committed（提交后才能读到）

事务A只能读取到事务B已提交的数据

这种隔离级别解决了脏读现象

存在的问题：不可重复读取数据

什么叫不可重复读取数据？
- 在事务开始后，第一次读取的数据是3条，但当前事务还未结束任在提交，第二次读取的时候读到的数据是4条，3不等于4，称之为不可重复读取
所以这种隔离级别能拿到真实的数据

Oracle数据库的默认隔离级别就是这种
可重复读：repeatable read（提交之后也读不到，读取到的是刚开启事务时的数据）

事务A开启后，不管什么时候，每一次在事务A中读取到的数据都是一致的。即使事务B已经将数据进行了修改并且提交了，事务A读取到的数据依旧没有发生改变

这种隔离级别解决了不可重复读取数据的问题

存在的问题：会出现幻读现象

什么叫幻读现象？
- 幻读现象是指在多个事务并发执行的情况下，一个事务读取到另一个事务插入或修改的行，导致数据不一致的情况。在MySQL中，幻读可以通过在事务中使用“悲观锁”来避免。
MySQL数据库的默认隔离级别就是这种
序列化/串行化：serializable（最高隔离级别）

这种隔离级别表示事务排队，不能并发，同时解决了之前所有的问题，也因此效率最低

提交事务(commit)

清空事务性活动的日志文件
将数据全部彻底持久化到数据库表中
提交事务标志着事务的结束，且是一种全部成功的结束

回滚事务(rollback)

清空事务性活动的日志文件
将之前的所有DML操作全部撤销
回滚事务标志着事务的结束，且是一种全部失败的结束

mysql关闭自动提交事务机制

在mysql默认开启自动提交事务机制，可以使用以下命令手动开始事务，这样mysql就不会自动提交事务

start transaction;

示例：

// 开始事务
start transaction;
// 插入数据
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');
// 查询插入的数据
mysql> select * from t_vip;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
+------+----------+
2 rows in set (0.00 sec)
// 回滚事务
rollback;
// 再次查询发现插入的数据消失了
mysql> select * from t_vip;
Empty set (0.00 sec)
// 重复插入操作后提交事务，再回滚事务
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');
commit;
rollback;
// 查询数据，此时的数据已经被提交了，回滚只能h
mysql> select * from t_vip;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
+------+----------+
2 rows in set (0.00 sec)

索引

索引是在数据库表的字段上添加的，是为了提高查询效率而存在的一种机制。一张表的一个字段可以添加一个索引，多个字段联合起来也可以添加索引。索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

MySQL在查询方面主要是两种方式：

全表扫描
根据索引检索

注意：

在任何数据库中主键上都会自动添加索引对象，id字段上自动添加索引，因为id是PK。另外在MySQL当中，一个字段上如果有unique约束的话，也会自动创建索引对象。
在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号(物理地址)。
在MySQL当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。
- 在MyISAM存储引擎中，索引存储在一个.MYI文件中
- 在InnoDB存储引擎中，索引存储在一个逻辑名称叫做tablespace的当中
- 在MEMORY存储引擎中，索引存储在内存当中
不管索引存储在哪里，索引在MySQL当中都是一个树的形式存在(自平衡二叉树)。

什么情况下需要主动添加索引？

情况1：数据量庞大（具体需要根据设备的硬件性能决定）

情况2：该字段经常出现在where条件筛选的后面，以条件的形式存在，也就是说该字段总是被扫描（常用）

情况3：该字段很少出现DML(insert、delete、update)操作（因为DML操作之后，索引需要重新排序）

建议不要随意添加索引，因为索引也是需要维护的，太多的索引反而会降低系统的性能。建议通过主键查询，或通过unique约束的字段进行查询，效率会比较高。

创建索引

语法

create [UNIQUE|FULLTEXT|SPATIAL] index 索引名 on 表名 (字段1,字段2,...);

UNIQUE：创建唯一索引
FULLTEXT：创建全文索引
SPATIAL：创建空间索引

删除索引

语法

drop index 索引名 on 表名;

删除索引前需要确认这个索引不再被使用

查看查询是否使用了索引

可以在语句前面加上 EXPLAIN 关键字，然后执行语句。执行后，MySQL 会输出一个结果集，其中包含关于该查询语句的信息。可以通过查看 key 列来确定该查询语句是否使用了索引。如果使用了索引，则该列的值会显示为索引的名称；否则，该列的值为 NULL。

语法

explain select * from 表名;

视图

MySQL的视图是一种特殊的存储引擎，它允许用户查询一个选定的数据表，并将查询结果作为一个虚拟表（称为视图）进行存储。这种方式可以帮助用户快速检索、筛选和查看特定的数据信息，而不需要对原始数据表进行修改或重新组织。通常，视图是一个虚拟表，其中包含一个查询语句，查询语句会根据用户的需求动态生成视图表中的内容。

个人理解：

视图更像是对表重新封装，创建一个叫视图的对象，只展示想展示的数据，对这个对象操作也能直接影响到原表的数据。

创建视图

语法

create view 视图名 as select查询语句;

注意：只有DQL语句才能以View的形式创建视图

删除视图

语法

drop view 视图名；

数据库设计范式

数据库设计范式是一种组织数据库表的方法，旨在提高数据库的性能和结构化。它通过约束表中的数据，使得表之间的关系更加简单和有序，进而提高查询的效率。

共有三种范式：

第一范式(1NF)
第二范式(2NF)
第三范式(3NF)

第一范式

要求任何一张表都必须有主键，表中的每一个字段都必须包含原子性。这意味着每一个字段都不能再被拆分成更小的部分

特点：

必须有主键
每一个字段都是原子性不可再分
最核心，最重要的范式，所有表的设计都需要满足

第二范式

建立在第一范式基础之上，要求每一个非主键字段都必须完全依赖于主键，不要产生部份依赖。这意味着如果表中有多个字段，则必须保证这些字段完全依赖于主键，而不能仅仅依赖于其中的一个字段。

特点：

建立在第一范式之上，即满足第一范式的条件
非主键字段，完全依赖主键
非主键字段，不能出现冗余

多对多表的设计口诀：多对多，三张表，关系表两个外键。

多对多的关系例如老师和学生的关系

第三范式

要求数据库表中不能存在传递依赖。这意味着如果一个字段依赖于另一个字段，则第三范式要求这两个字段必须直接依赖于主键，而不能通过其他字段来间接依赖。

特点：

建立在第一范式和第二范式之上，即满足第一、第二范式的条件
数据库表中不能存在传递依赖

一对多表的设计口诀：一对多，两张表，多的表加外键。

总结

数据库设计三范式是理论上的，可以根据实际情况修改
在sql中，表与表的连接次数越多，效率就越低（笛卡尔积），有的时候会拿冗余换执行速度
有时候建表可能出现冗余，但是为了减少表的连接次数，这样做也是合理的，这样也可以使得sql语句的编写难度降低

posted @ 2022-11-12 16:17 WNAOII 阅读(423) 评论(0) 编辑收藏举报

刷新页面返回顶部

WNAOII

MySQL命令语法

MySQL命令/语法

启动 MySQL服务

退出MySQL服务

查看MySQL中有哪些数据库

选择需要操作的数据库

创建数据库

查看当前使用的数据库

查看数据库中有哪些表

导入表

查看表结构

查询所有字段

给列起别名

列参与数学运算

条件查询

between and用法

条件查询中in的用法

模糊查询

百分号例子：

下划线例子：

排序查询

升序(ascend)

降序(descend)

多字段排序

根据字段位置排序

单行数据处理函数

例：大小写转换

例：取子串

例：取长度

例：ifnull()函数用法

多行处理函数

分组查询

having关键字

查询数据去重

笛卡尔积现象

如何避免笛卡尔积现象出现

连接查询

内连接(inner)

等值连接

非等值连接

自连接

外连接(outer)

左外连接(左连接)

右外连接(右连接)

全连接(暂时忽略)

多表连接

子查询

where子句中的子查询

from子句中的子查询

select子句中的子查询(只做了解)

union合并查询结果集

union写法

注意事项

limit

语法

分页查询通用公式

注意事项

建表(DDL)

语法

关于mysql中的数据类型

varchar

char

int

bigint

float

double

date

datetime

date和datetime的区别

获取系统当前时间

clob

blob

快速复制表

删表

语法

插入数据insert(DML)

语法

省略字段名写法

同时插入多条数据

条件查询中`in`的用法

例：`ifnull()`函数用法

`having`关键字

`where`子句中的子查询

`from`子句中的子查询

`select`子句中的子查询(只做了解)

`union`合并查询结果集

`union`写法

`limit`

插入数据`insert`(DML)

修改数据`update`(DML)

删除数据`delete`(DML)

`truncate`语句删除数据(DDL)

`auto_increment`(主键自增)