提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
提示:以下是本篇文章正文内容,下面案例可供参考
目录
一、安装MySQL
一、环境变量配置
二、启动MySQL
三、登录MySQL
四、MySQL常用命令
注意:不见;不执行,其表示结束
五、SQL语句分类
六、导入数据
七、查看表结构
八、查询语句
1.简单查询(1)查询一个字段
2.条件查询
九、数据处理函数(单行处理函数)
1.lower(转换小写)
2.upper(转换大写)
3.substr(取子串)
4.length(取长度)
5.trim(去空格)
6.string_to_date(将字符串转换成日期)
7..date_format(格式化千分位)
8.format(设置千分位)
9.concat(字符串拼接)
十、分组函数(多行处理函数)
1.max()
2.min()
3.avg()
4.count()
5.sum()
十一、分组查询(*****)
1.group by
2.distinct(去重)
十二、连接查询
案例(SQL92语法)
笛卡尔积现象
1.内连接
2.外连接
两种可以进行相互转换
案例
案例
十三、子查询
1.子查询可以出现在哪里?
2.where字句中的子查询
案例
3.from子句中的子查询
案例
4.select后面出现的子查询(了解)
案例
十四、union合并查询结果集
案例
十五、limit
案例
分页
十六、表的操作
1.创建表
2.删除表及删改数据
3.insert插入
4.string_to_date
5.date_format
格式化千分位,date_format(日期类型数据,'日期格式')
6.date和datetime
7.快速创建表
8.快速删除表中的数据
9.约束(constraint)
1)not null
2)unique
3)primary key(主键约束)一般为数字,定长
4)foreign
9.脚本文件
十七、存储引擎
1.查看MySQL支持哪些引擎:
2.常用存储引擎
(1) MyISAM
(2) InnoDB
(3)MEMORY
十八、事务
1.什么是一个完整的业务逻辑
2.只有DML语句才有事务
3.事务如何实现
(1)提交事务?会自动提交
(2)回滚事务
(3)怎么将MySQL的自动提交机制关闭呢?
4.事务的特性
(1)A:原子性
(2)C:一致性
(3)I:隔离性
(4)D:持久性
十九、索引(index)
1.添加索引的条件
2.创建和删除
3.查看是否使用索引进行检索
4.索引失效
5.索引分类
二十、视图
1.创建及删除视图对象
2.视图做什么
一、安装MySQL
二、启动MySQL
三、登录MySQL
四、MySQL常用命令
注意:不见;不执行,其表示结束
五、SQL语句分类
六、导入数据
七、查看表结构
八、查询语句
1.简单查询(1)查询一个字段
2.条件查询
九、数据处理函数(单行处理函数)
1.lower(转换小写)
2.upper(转换大写)
3.substr(取子串)
4.length(取长度)
5.trim(去空格)
6.string_to_date(将字符串转换成日期)
7..date_format(格式化千分位)
8.format(设置千分位)
9.concat(字符串拼接)
十、分组函数(多行处理函数)
1.max()
2.min()
3.avg()
4.count()
5.sum()
十一、分组查询(*****)
1.group by
2.distinct(去重)
十二、连接查询
案例(SQL92语法)
笛卡尔积现象
1.内连接
2.外连接
两种可以进行相互转换
案例
案例
十三、子查询
1.子查询可以出现在哪里?
2.where字句中的子查询
案例
3.from子句中的子查询
案例
4.select后面出现的子查询(了解)
案例
十四、union合并查询结果集
案例
十五、limit
案例
分页
十六、表的操作
1.创建表
2.删除表及删改数据
3.insert插入
4.string_to_date
5.date_format
格式化千分位,date_format(日期类型数据,'日期格式')
6.date和datetime
7.快速创建表
8.快速删除表中的数据
9.约束(constraint)
1)not null
2)unique
3)primary key(主键约束)一般为数字,定长
4)foreign
9.脚本文件
十七、存储引擎
1.查看MySQL支持哪些引擎:
2.常用存储引擎
(1) MyISAM
(2) InnoDB
(3)MEMORY
十八、事务
1.什么是一个完整的业务逻辑
2.只有DML语句才有事务
3.事务如何实现
(1)提交事务?会自动提交
(2)回滚事务
(3)怎么将MySQL的自动提交机制关闭呢?
4.事务的特性
(1)A:原子性
(2)C:一致性
(3)I:隔离性
(4)D:持久性
十九、索引(index)
1.添加索引的条件
2.创建和删除
3.查看是否使用索引进行检索
4.索引失效
5.索引分类
二十、视图
1.创建及删除视图对象
2.视图做什么
一、环境变量配置
path
二、启动MySQL
net start mysql
net stop mysql
三、登录MySQL
四、MySQL常用命令
查看数据库
show database;
创建数据库
create database student;
选择使用哪个数据库
use mysql;
查看mysql数据库版本号
select version();
查看当前使用的数据库
select database();
注意:不见;不执行,其表示结束
五、SQL语句分类
- DQL:数据库查询语言 select关键字
- DML:数据库操作语言 (对表中的数据进行修改)
- 增:insert
- 删:delete
- 改:update
- DDL:数据库定义语言(主要是对表的结构进行操作)
- 新建:等同于增
- 删除:drop
- 修改:alter
- TCL:事务控制语言
- 事务提交:commit
- 事务回滚:rollback
- DCL:数据控制语言
- 授权:grant
desc emp;
- 撤销权限:revoke
六、导入数据
1.创建数据库
create database 数据库名;
2.导入
source 数据库保存的地址(不能有中文);
3.查看数据表
show tables;
七、查看表结构
desc 表名;
八、查询语句
1.简单查询
(1)查询一个字段
select 字段名 from 表名;
五、SQL语句分类
- DQL:数据库查询语言 select关键字
- DML:数据库操作语言 (对表中的数据进行修改)
- 增:insert
- 删:delete
- 改:update
- DDL:数据库定义语言(主要是对表的结构进行操作)
- 新建:等同于增
- 删除:drop
- 修改:alter
- TCL:事务控制语言
- 事务提交:commit
- 事务回滚:rollback
- DCL:数据控制语言
- 授权:grant
desc emp;
- 撤销权限:revoke
六、导入数据
1.创建数据库
create database 数据库名;
2.导入
source 数据库保存的地址(不能有中文);
3.查看数据表
show tables;
七、查看表结构
desc 表名;
八、查询语句
1.简单查询
(1)查询一个字段
select 字段名 from 表名;
desc emp;
1.创建数据库
create database 数据库名;
2.导入
source 数据库保存的地址(不能有中文);
3.查看数据表
show tables;
七、查看表结构
desc 表名;
八、查询语句
1.简单查询
(1)查询一个字段
select 字段名 from 表名;
1.简单查询
(1)查询一个字段
select 字段名 from 表名;
(2)查询两个/多个字段,用逗号隔开
select deptno,dname from dept; +--------+------------+ | deptno | dname | +--------+------------+ | 10 | ACCOUNTING | | 20 | RESEARCH | | 30 | SALES | | 40 | OPERATIONS | +--------+------------+
(3)查询所有字段
mysql> select * from dept; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 这种方式的缺点: 1.可读性差 2.效率低 在实际开发中不建议使用
(4)给查询列起别名(别名是中文,用单引号括起来)
mysql> select deptno,dname as deptname from dept; +--------+------------+ | deptno | deptname | +--------+------------+ | 10 | ACCOUNTING | | 20 | RESEARCH | | 30 | SALES | | 40 | OPERATIONS | +--------+------------+ 使用as关键字起别名,只改变显示,不改变原表
(5)列参与数学运算
mysql> select ename,sal from emp; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | +--------+---------+ 14 rows in set (0.00 sec) mysql> select ename,sal*12 from emp; //字段可以使用数学表达式! +--------+----------+ | ename | sal*12 | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | +--------+----------+
2.条件查询
select字段.... from 表名 where 条件;
(1)=
mysql> select empno,ename from emp where sal=800; +-------+-------+ | empno | ename | +-------+-------+ | 7369 | SMITH | +-------+-------+
(2)不等于
select empno,ename from emp where sal !=800; select empno,ename from emp where sal <>800; +-------+--------+ | empno | ename | +-------+--------+ | 7499 | ALLEN | | 7521 | WARD | | 7566 | JONES | | 7654 | MARTIN | | 7698 | BLAKE | +-------+--------+
(3)<
(4)<=
(5)>
(6)>=
(7)between...and
mysql> select empno,ename,sal from emp where sal >=2450 and sal <= 3000;
select empno,ename,sal from emp where sal between 2450 and 3000;
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7902 | FORD | 3000.00 |
+-------+-------+---------+
(8)查询是否为空
mysql> select empno,ename,sal,comm from emp where comm is not null; +-------+--------+---------+---------+ | empno | ename | sal | comm | +-------+--------+---------+---------+ | 7499 | ALLEN | 1600.00 | 300.00 | | 7521 | WARD | 1250.00 | 500.00 | | 7654 | MARTIN | 1250.00 | 1400.00 | | 7844 | TURNER | 1500.00 | 0.00 | +-------+--------+---------+---------+ select empno,ename,sal,comm from emp where comm is null;
(9)and并且
查询工作岗位时manager并且工资大于2500的员工信息
mysql> select empno,ename,job,sal from emp where job='manager' and sal > 2500; +-------+-------+---------+---------+ | empno | ename | job | sal | +-------+-------+---------+---------+ | 7566 | JONES | MANAGER | 2975.00 | | 7698 | BLAKE | MANAGER | 2850.00 | +-------+-------+---------+---------+
(10)or或者
查询工作岗位时manager和salesman的员工
mysql> select empno,ename,job from emp where job='manager' or job='salesman'; +-------+--------+----------+ | empno | ename | job | +-------+--------+----------+ | 7499 | ALLEN | SALESMAN | | 7521 | WARD | SALESMAN | | 7566 | JONES | MANAGER | | 7654 | MARTIN | SALESMAN | | 7698 | BLAKE | MANAGER | | 7782 | CLARK | MANAGER | | 7844 | TURNER | SALESMAN | +-------+--------+----------+
(11)and和or同时出现时,有优先级问题吗?
and 优先级比or高,()优先级最高
(12)in包含,相当于多个or(not in不在这个范围中)
mysql> select ename,sal from emp where sal in (800,5000);
+-------+---------+
| ename | sal |
+-------+---------+
| SMITH | 800.00 |
| KING | 5000.00 |
+-------+---------+
注:in后面的括号相当于集合
(13)not表示不在这几个值当中
(14)like
称为模糊查询,支持%或下划线匹配,%:任意多个字符,下划线:任意一个字符
找出名字含有o的 mysql> select ename from emp where ename like '%o%'; +-------+ | ename | +-------+ | JONES | | SCOTT | | FORD | +-------+
(15)排序(order by)
· 默认升序,指定降序desc,指定升序asc
mysql> select ename,sal from emp order by sal desc; +--------+---------+ | ename | sal | +--------+---------+ | KING | 5000.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | +--------+---------+
·多个字段排序
mysql> select ename,sal from emp order by sal desc,ename asc; +--------+---------+ | ename | sal | +--------+---------+ | KING | 5000.00 | | FORD | 3000.00 | | SCOTT | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 || +--------+---------+
·根据字段的位置排序(了解)
按照查询结果第二列进行排序 mysql> select ename,sal from emp order by 2; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | MILLER | 1300.00 || +--------+---------+
(16)综合一点的案例
注:关键字顺序不能变 mysql> select ename,sal from emp where sal between 1250 and 3000 order by sal desc; +--------+---------+ | ename | sal | +--------+---------+ | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | +--------+---------+
九、数据处理函数(单行处理函数)
单行处理函数特点:一个输入对应一个输出
1.lower(转换小写)
mysql> select lower(ename) as ename from emp;
+--------+
| ename |
+--------+
| smith |
| allen |
| ward |
| jones |
| martin |
+--------+
2.upper(转换大写)
3.substr(取子串)
第一种方式:substr函数 emp where substr(ename,1,1)='A';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
第二种方式:模糊查询
mysql> select ename from emp where ename like 'A%';
4.length(取长度)
mysql> select length(ename) enamelength from emp;
+-------------+
| enamelength |
+-------------+
| 5 |
| 5 |
| 4 |
+-------------+
5.trim(去空格)
mysql> select * from emp where ename=' KING';
Empty set (0.00 sec)
mysql> select * from emp where ename=trim(' KING');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
+-------+-------+-----------+------+------------+---------+------+--------+
6.string_to_date(将字符串转换成日期)
7..date_format(格式化千分位)
8.format(设置千分位)
9.concat(字符串拼接)
mysql> select concat(empno,ename) from emp;
+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH |
| 7499ALLEN |
| 7521WARD |
+---------------------+
十、分组函数(多行处理函数)
3.substr(取子串)
第一种方式:substr函数 emp where substr(ename,1,1)='A';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
第二种方式:模糊查询
mysql> select ename from emp where ename like 'A%';
4.length(取长度)
mysql> select length(ename) enamelength from emp;
+-------------+
| enamelength |
+-------------+
| 5 |
| 5 |
| 4 |
+-------------+
5.trim(去空格)
mysql> select * from emp where ename=' KING';
Empty set (0.00 sec)
mysql> select * from emp where ename=trim(' KING');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
+-------+-------+-----------+------+------------+---------+------+--------+
6.string_to_date(将字符串转换成日期)
7..date_format(格式化千分位)
8.format(设置千分位)
9.concat(字符串拼接)
mysql> select concat(empno,ename) from emp;
+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH |
| 7499ALLEN |
| 7521WARD |
+---------------------+
十、分组函数(多行处理函数)
mysql> select length(ename) enamelength from emp; +-------------+ | enamelength | +-------------+ | 5 | | 5 | | 4 | +-------------+
5.trim(去空格)
mysql> select * from emp where ename=' KING';
Empty set (0.00 sec)
mysql> select * from emp where ename=trim(' KING');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
+-------+-------+-----------+------+------------+---------+------+--------+
6.string_to_date(将字符串转换成日期)
7..date_format(格式化千分位)
8.format(设置千分位)
9.concat(字符串拼接)
mysql> select concat(empno,ename) from emp;
+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH |
| 7499ALLEN |
| 7521WARD |
+---------------------+
十、分组函数(多行处理函数)
7..date_format(格式化千分位)
8.format(设置千分位)
9.concat(字符串拼接)
mysql> select concat(empno,ename) from emp;
+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH |
| 7499ALLEN |
| 7521WARD |
+---------------------+
十、分组函数(多行处理函数)
9.concat(字符串拼接)
mysql> select concat(empno,ename) from emp;
+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH |
| 7499ALLEN |
| 7521WARD |
+---------------------+
十、分组函数(多行处理函数)
多行处理函数特点:输入多行,最终输出一行
注意:分组函数在使用时必须先分组,后使用
1.max()
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
| 5000.00 |
+----------+
2.min()
mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
| 800.00 |
+----------+
3.avg()
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
4.count()
mysql> select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
| 14 |
+--------------+
5.sum()
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
mysql> select min(sal) from emp; +----------+ | min(sal) | +----------+ | 800.00 | +----------+
3.avg()
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
4.count()
mysql> select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
| 14 |
+--------------+
5.sum()
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
mysql> select count(ename) from emp; +--------------+ | count(ename) | +--------------+ | 14 | +--------------+
5.sum()
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
6.分组函数的注意点
(1)分组函数在使用的时候需要注意哪些?
分组函数会自动忽略null,不需要提前对null进行处理
(2)分组函数中count(*)和count(具体字段)有什么区别
count(具体字段):表示统计该字段下所有不为NULL的元素的总数
count(*):统计表当中的总行数
(3)分组函数不能够直接使用where子句中
(4)所有分组函数可以组合起来用
mysql> select sum(sal),min(sal),max(sal),count(*),avg(sal) from emp; +----------+----------+----------+----------+-------------+ | sum(sal) | min(sal) | max(sal) | count(*) | avg(sal) | +----------+----------+----------+----------+-------------+ | 29025.00 | 800.00 | 5000.00 | 14 | 2073.214286 | +----------+----------+----------+----------+-------------+
十一、分组查询(*****)
先进行分组,然后进行操作
以下关键字的顺序不能颠倒,需要记忆
from、where、group by、select、order by
select
...
from
...
group by
...
order by
...
1.group by
eg:
(1)按照工作岗位对工资求和
mysql> select job,sum(sal) from emp group by job; +-----------+----------+ | job | sum(sal) | +-----------+----------+ | CLERK | 4150.00 | | SALESMAN | 5600.00 | | MANAGER | 8275.00 | | ANALYST | 6000.00 | | PRESIDENT | 5000.00 | +-----------+----------+
(2)找出每个部门的做高薪资
mysql> select deptno,max(sal) from emp group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 20 | 3000.00 | | 30 | 2850.00 | | 10 | 5000.00 | +--------+----------+
(3)找出每个部门,不同工作岗位的最高薪资
mysql> select deptno,job,max(sal) from emp group by deptno,job; +--------+-----------+----------+ | deptno | job | max(sal) | +--------+-----------+----------+ | 20 | CLERK | 1100.00 | | 30 | SALESMAN | 1600.00 | | 20 | MANAGER | 2975.00 | | 30 | MANAGER | 2850.00 | | 10 | MANAGER | 2450.00 | | 20 | ANALYST | 3000.00 | | 10 | PRESIDENT | 5000.00 | | 30 | CLERK | 950.00 | | 10 | CLERK | 1300.00 | +--------+-----------+----------+
(4)找出每个部门的最高薪资,要求娴熟最高薪资大于3000
where与having优先选择where
1)having字句可以对分组之后 的数据进行过滤
mysql> select
deptno,max(sal)
from
emp
group by
deptno
having
max(sal)>3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
2)可以先找出薪资大于3000的,然后进行分组
mysql> select
deptno,max(sal)
from
emp
where
sal > 3000
group by
deptno ;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
2.distinct(去重)
(1)岗位去重
mysql> select distinct job from emp; +-----------+ | job | +-----------+ | CLERK | | SALESMAN | | MANAGER | | ANALYST | | PRESIDENT | +-----------+
(2)job和deptno两个字段同时去重
mysql> select distinct job,deptno from emp; +-----------+--------+ | job | deptno | +-----------+--------+ | CLERK | 20 | | SALESMAN | 30 | | MANAGER | 20 | | MANAGER | 30 | | MANAGER | 10 | | ANALYST | 20 | | PRESIDENT | 10 | | CLERK | 30 | | CLERK | 10 | +-----------+--------+
十二、连接查询
内连接:等值连接、非等值连接、自连接
外连接:左外连接、右外连接
全连接(不讲)
案例(SQL92语法)
查询每个员工所在的部门名称
mysql> select ename,deptno from 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 | +--------+--------+ mysql> select * from dept; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
两张表没有条件限制:
mysql> select ename,dname from emp,dept; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | OPERATIONS | | SMITH | SALES | | SMITH | RESEARCH | | SMITH | ACCOUNTING | | ALLEN | OPERATIONS | | ALLEN | SALES | | ALLEN | RESEARCH | | ALLEN | ACCOUNTING | |.....................| 56 rows in set (0.37 sec)
笛卡尔积现象
当两张表连接查询,两张表没有条件限制时,最终查询结果条数,是两张表条数的乘积,这种现象被称为笛卡尔积现象。
连接时加条件,满足这个条件的记录被筛选出来!
mysql> select ename,dname from emp,dept where emp.deptno=dept.deptno; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | RESEARCH | | ALLEN | SALES | | WARD | SALES | | JONES | RESEARCH | | MARTIN | SALES | | BLAKE | SALES | | CLARK | ACCOUNTING | | SCOTT | RESEARCH | | KING | ACCOUNTING | | TURNER | SALES | | ADAMS | RESEARCH | | JAMES | SALES | | FORD | RESEARCH | | MILLER | ACCOUNTING | +--------+------------+ 14 rows in set (0.04 sec)
以下为SQL99语法
1.内连接
特点:完全能够匹配上这个条件的数据查询出来,没有主次关系
(1)等值连接
案例:查询每个员工所在部门名称,显示员工名和部门名
mysql> select
e.ename,d.dname
from
emp e
inner join
dept d
on
e.deptno=d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.01 sec)
(2)非等值连接(条件不是一个等值关系)
案例:找出每个员工的薪资等级,要求显示员工名、薪资等级?
mysql> select
e.ename,s.grade
from
emp e
inner join
salgrade s
on
e.sal
between s.losal and s.hisal;
+--------+-------+
| ename | grade |
+--------+-------+
| SMITH | 1 |
| ALLEN | 3 |
| WARD | 2 |
| JONES | 4 |
| MARTIN | 2 |
| BLAKE | 4 |
| CLARK | 4 |
| SCOTT | 4 |
| KING | 5 |
| TURNER | 3 |
| ADAMS | 1 |
| JAMES | 1 |
| FORD | 4 |
| MILLER | 2 |
+--------+-------+
14 rows in set (0.36 sec)
(3)自连接
案例:查询员工的上级领导,要求显示员工名和对应的领导名?
mysql> 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)
技巧:一张表看成两张表
emp a:员工表 emp b:领导表
mysql> select a.ename as 'employee',b.ename as 'leader'
-> from
-> emp a
-> join
-> emp b
-> on
-> a.mgr=b.empno;
+----------+--------+
| employee | leader |
+----------+--------+
| 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)
2.外连接
两种可以进行相互转换
(1)右外连接
right表示将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部显示出来,捎带关联查询right左边这张表。
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.41 sec)
mysql> select * from 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno=d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
内连接只显示了有员工的部门10、20、30,没有员工的部门40没有显示出来,若想要显示,就需要使用外连接。
mysql> select
-> e.ename,d.dname
-> from
-> emp e
-> right join
-> dept d
-> on
-> e.deptno=d.deptno;
mysql> select
-> e.ename,d.dname
-> from
-> dept d
-> left join
-> emp e
-> on
-> e.deptno=d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| MILLER | ACCOUNTING |
| KING | ACCOUNTING |
| CLARK | ACCOUNTING |
| FORD | RESEARCH |
| ADAMS | RESEARCH |
| SCOTT | RESEARCH |
| JONES | RESEARCH |
| SMITH | RESEARCH |
| JAMES | SALES |
| TURNER | SALES |
| BLAKE | SALES |
| MARTIN | SALES |
| WARD | SALES |
| ALLEN | SALES |
| NULL | OPERATIONS |
+--------+------------+
(2)左外连接
案例
查询每个员工的上级领导,要求显示所有的员工名和领导名
mysql> select
a.ename,b.ename
from
emp a
left join
emp b
on
a.mgr=b.empno;
+--------+-------+
| ename | ename |
+--------+-------+
| 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 |
+--------+-------+
(3)三张表连接
mysql> select
...
from
a
join
b
on
a和b连接的条件
join
c
on
a和c连接的条件
right join
d
on
a和d连接的条件
案例
找出每个员工的部门名称以及工资等级,要求显示员工名、部门名、薪资、薪资等级?
mysql> select
-> e.ename,e.sal,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 s.hisal;
+--------+---------+------------+-------+
| ename | sal | dname | grade |
+--------+---------+------------+-------+
| SMITH | 800.00 | RESEARCH | 1 |
| ALLEN | 1600.00 | SALES | 3 |
| WARD | 1250.00 | SALES | 2 |
| JONES | 2975.00 | RESEARCH | 4 |
| MARTIN | 1250.00 | SALES | 2 |
| BLAKE | 2850.00 | SALES | 4 |
| CLARK | 2450.00 | ACCOUNTING | 4 |
| SCOTT | 3000.00 | RESEARCH | 4 |
| KING | 5000.00 | ACCOUNTING | 5 |
| TURNER | 1500.00 | SALES | 3 |
| ADAMS | 1100.00 | RESEARCH | 1 |
| JAMES | 950.00 | SALES | 1 |
| FORD | 3000.00 | RESEARCH | 4 |
| MILLER | 1300.00 | ACCOUNTING | 2 |
+--------+---------+------------+------
十三、子查询
select语句中嵌套select语句,被嵌套的select语句称为子查询
1.子查询可以出现在哪里?
select
...[select]...
from
...[select]...
where
...[select]...
2.where字句中的子查询
where子句中不能使用分组函数
案例找出比最低工资高的员工姓名、薪资
mysql> select
ename,sal
from
emp
where
sal> (select min(sal) from emp);
+--------+---------+
| ename | sal |
+--------+---------+
| 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 |
+--------+---------+
3.from子句中的子查询
from后面的子查询,可以将子查询的查询结果当作一张临时表
案例找出每个岗位的平均工资的薪资等级
第一步:找出每个岗位的平均工资(按照岗位分组求平均值)
mysql> select
job,avg(sal)
from
emp
group by
job;
+-----------+-------------+
| job | avg(sal) |
+-----------+-------------+
| CLERK | 1037.500000 |
| SALESMAN | 1400.000000 |
| MANAGER | 2758.333333 |
| ANALYST | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
第二步:克服心理障碍,把以上的查询结果就当作一张真是存在的表t
mysql> select
job,avg(sal)
as
avgsal
from
emp
group by
job;
+-----------+-------------+
| job | avgsal |
+-----------+-------------+
| CLERK | 1037.500000 |
| SALESMAN | 1400.000000 |
| MANAGER | 2758.333333 |
| ANALYST | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
mysql> select
-> t.*,s.grade
-> from
-> (select job,avg(sal)as avgsal from emp group by job) t
-> join
-> salgrade s
-> on
-> t.avgsal between s.losal and s.hisal;
+-----------+-------------+-------+
| job | avgsal | grade |
+-----------+-------------+-------+
| CLERK | 1037.500000 | 1 |
| SALESMAN | 1400.000000 | 2 |
| MANAGER | 2758.333333 | 4 |
| ANALYST | 3000.000000 | 4 |
| PRESIDENT | 5000.000000 | 5 |
+-----------+-------------+-------+
4.select后面出现的子查询(了解) 案例
找出每个员工的部门名称,要求显示员工名、部门名?
mysql> select
e.ename,(select d.dname from dept d where e.deptno=d.deptno)
as
dname
from
emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
十四、union合并查询结果集 案例
查询工作岗位是manager和salesman的员工
mysql> select ename,job from emp where job='manager' or job='salesman';
select ename,job from emp where job in('manager'.'salesman');
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
mysql> select ename,job from emp where job='manager'
-> union
-> select ename,job from emp where job='salesman';
+--------+----------+
| ename | job |
+--------+----------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
union效率更高一些,匹配次数会减少
十五、limit
limit是将查询结果集的一部分取出来,通常使用在分页查询中
案例按照薪资降序,取出排名在前5名的员工
mysql> select ename,sal from emp order by sal desc limit 5;
select ename,sal from emp order by sal desc limit (0,5);
// 5代表长度,下标从0开始
//取出3-5名
select ename,sal from emp order by sal desc limit (2,3);
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
分页
每页显示三条记录
第一页:limit 0,3 第二页:limit 3,3 第三页:limit 6,3
每页显示pagesize条记录
第pageno页:limit (pageno-1)*pagesize,pagesize
十六、表的操作
1.创建表
create table 表名(
id int unsigned primary key auto_increment,
name varchar(5),
age int unsigned
);
2.删除表及删改数据
create table 表名(
id int unsigned primary key auto_increment,
name varchar(5),
age int unsigned
);
2.删除表及删改数据
表存在删除后创建:
drop table if exists students
查看数据表结构:
desc students
删除数据表:
drop table students //只删除所有数据,不重置主键字段的计数
truncate table students //删除所有数据,并重置主键字段的计数
delete from students //删掉整张表
修改数据:
update students set age 40 where id=2;
删除数据:
delete from students where id=2;
3.insert插入
数字格式化:format(数字,'格式')
mysql> select ename,format(sal,'$999,999') as sal from emp; +--------+-------+ | ename | sal | +--------+-------+ | SMITH | 800 | | ALLEN | 1,600 | | WARD | 1,250 | | JONES | 2,975 | | MARTIN | 1,250 | | BLAKE | 2,850 | | CLARK | 2,450 | | SCOTT | 3,000 | | KING | 5,000 | | TURNER | 1,500 | | ADAMS | 1,100 | | JAMES | 950 | | FORD | 3,000 | | MILLER | 1,300 | +--------+-------+
插入多条记录语法:
添加一行数据:
insert into students (字段1,字段2) value() //数据要与表的字段一一对应
添加多行数据:
insert into students value(...),(...),(...)
4.string_to_date
mysql> create table t_user(
id int,
name varchar(32),
birth date //生日也可以使用date日期类型
);
mysql> desc t_user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| birth | date | YES | | NULL | |
将字符串转换成日期
mysql> insert into t_user(id,name,birth)
values
(1,'zhangsan',str_to_date('01-10-1990','%d-%m-%Y'));
mysql> insert into t_user(id,name,birth)
values
(2,'lisi','1990-10-01'); //如果你提供的字符串是日期格式,会自动转换
Query OK, 1 row affected (0.41 sec)
5.date_format
格式化千分位,date_format(日期类型数据,'日期格式')
这个函数通常使用在查询日期方面,设置展示的日期格式
mysql> select
id,name,date_format(birth,'%m/%d/%Y')
as birth from t_user;
+------+----------+------------+
| id | name | birth |
+------+----------+------------+
| 1 | zhangsan | 10/01/1990 |
| 2 | lisi | 10/01/1990 |
+------+----------+------------+
6.date和datetime
date是短日期,只包含年月日信息,默认格式:%Y-%m-%d
datetime是长日期,包含年月日时分秒,默认格式:%Y-%m-%d %h:%l:%s
mysql> create table t_user(
id int,
name varchar(32),
birth date,
create_time datetime
);
mysql> insert into t_user(id,name,birth,create_time)
values
(1,'zhangsan','1990-10-01','2022-6-5 10:09:40');
mysql> insert into t_user(id,name,birth,create_time)
values (2,'lisi','1991-10-01',now()); //now()获取当前时间
mysql> select * from t_user;
+------+----------+------------+---------------------+
| id | name | birth | create_time |
+------+----------+------------+---------------------+
| 1 | zhangsan | 1990-10-01 | 2022-06-05 10:09:40 |
| 2 | lisi | 1991-10-01 | 2022-06-05 10:14:10 |
+------+----------+------------+---------------------+
7.快速创建表
原理:将一个查询结果当作一张表新建!!!可以完成表的复制
mysql> create table emp2 as select * from emp; Query OK, 14 rows affected, 2 warnings (1.25 sec) Records: 14 Duplicates: 0 Warnings: 0
可以将查询结果插入到一张表中
mysql> create table dept_bak select * from dept; Query OK, 4 rows affected (1.23 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into dept_bak select * from dept; Query OK, 4 rows affected (0.43 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from dept_bak; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
8.快速删除表中的数据
delete语句(DML语句)删除数据的原理: 只删除数据,但数据在硬盘上的真实空间不会被释放
优点:支持回滚,后悔了可以恢数据复
缺点:删除次效率较低
truncate语句(DDL操作)删除的原理:效率比较高,表被一次截断,物理删除
优点:快速
缺点:不支持回滚
mysql> delete from dept_bak; Query OK, 4 rows affected (0.42 sec) mysql> select * from dept_bak; Empty set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.05 sec) mysql> select * from dept_bak; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.00 sec) mysql> truncate table dept_bak; Query OK, 0 rows affected (1.25 sec) mysql> select * from dept_bak; Empty set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec)
9.约束(constraint)
在创建表的时候,我们可以给表中的字段加上一些约束,来保证这个表中数据的完整性、有效性
1)not null
只有列级约束,没有表级约束
mysql> insert into t_vip values(1,'zhangsan');
insert into t_vip values(2,'lisi');
mysql> select * from t_vip;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
+------+----------+
mysql> insert into t_vip values(3);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
2)unique
约束的字段不能重复,但是可以为null
mysql> create table t_vip(
-> id int,
-> name varchar(255) unique,
-> email varchar(255)
-> );
mysql> insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
mysql> insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');
mysql> insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com');
mysql> insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');
ERROR 1062 (23000): Duplicate entry 'wangwu' for key 't_vip.name'
mysql> select * from t_vip;
+------+----------+------------------+
| id | name | email |
+------+----------+------------------+
| 1 | zhangsan | zhangsan@123.com |
| 2 | lisi | lisi@123.com |
| 3 | wangwu | wangwu@123.com |
+------+----------+------------------+
mysql> insert into t_vip(id) values(4);
mysql> insert into t_vip(id) values(5);
mysql> select * from t_vip;
+------+----------+------------------+
| id | name | email |
+------+----------+------------------+
| 1 | zhangsan | zhangsan@123.com |
| 2 | lisi | lisi@123.com |
| 3 | wangwu | wangwu@123.com |
| 4 | NULL | NULL |
| 5 | NULL | NULL |
+------+----------+------------------+
两个字段联合起来唯一
mysql> create table t_vip(
-> id int,
-> name varchar(255) ,
-> email varchar(255),
unique(name,email)
-> );
not null和unique联合
mysql> create table t_vip(
-> id int,
-> name varchar(255) not null unique
-> );
在MySQL中,如果一个字段同时被notbull和unique约束的画,该字段自动变成主键字段
3)primary key(主键约束)一般为数字,定长
在MySQL中,有一种机制可以帮我们自动维护一个主键值
mysql> create table t_vip(
-> id int primary key auto_increment, // auto_increment表示自增,从1开始
-> name varchar(255)
-> );
任何一张表必须有主键,若没有,表无效
作用:每一行记录的唯一标识
特征:not null+unique(主键值不能是null,同时也不能重复)
相关术语:
主键约束:就是一种约束
主键字段:该字段上添加了主键约束
主键值:主键上的每一个值
mysql> create table t_vip(
-> id int,
-> name varchar(255) ,
-> email varchar(255),
primary key(id,name) //复合主键
mysql> create table t_vip(
-> id int primary key, //列级主键
-> name varchar(255) ,
-> email varchar(255)
-> );
4)foreign
mysql> create table class(
-> classno int primary key,
-> classname varchar(255)
-> );
mysql> insert into class(classno,classname) values(101,'北京市大兴区高三一班');
mysql> insert into class(classno,classname) values(102,'北京市大兴区高三二班');
mysql> create table student(
-> no int primary key auto_increment,
-> name varchar(255),
-> cno int,
-> foreign key(cno) references class(classno)
-> );
mysql> insert into student (name,cno) values('lala',101);
mysql> insert into student (name,cno) values('baby',101);
mysql> insert into student (name,cno) values('alice',102);
mysql> select * from class;
+---------+--------------------------------+
| classno | classname |
+---------+--------------------------------+
| 101 | 北京市大兴区高三一班 |
| 102 | 北京市大兴区高三二班 |
+---------+--------------------------------+
mysql> select * from student;
+----+-------+------+
| no | name | cno |
+----+-------+------+
| 1 | lala | 101 |
| 2 | baby | 101 |
| 4 | alice | 102 |
+----+-------+------+
9.脚本文件
xxxx.sql这种文件被sql脚本文件,批量执行sql脚本文件的时候,该文件中所有的sql语句会全部执行!
执行sql脚本文件的语句:source sql脚本的绝对路径
十七、存储引擎
结论:
MySQL默认的存储引擎:InnoDB
MySQL默认的字符编码:utf-8
mysql> create table t_vip(
-> id int primary key,
-> name varchar(255)
-> )ENGINE=InnoDB DEFAULT CHARSET=GBK;
1.查看MySQL支持哪些引擎:
支持九大引擎
mysql> show engines G
2.常用存储引擎
(1) MyISAM
对于一张表来说,只要是主键,或者加有unique字段上会自动创建索引
优势:可被转换为压缩、只读表来节省空间
它管理的表具有以下特征:使用三个文件表示每个表
格式文件:存储表结构的定义(mytable.frm)
数据文件:存储表行的内容(mytable.MYD)
索引文件:存储表的索引(mytable.MYI),索引是一本书的目录,缩小扫描范围,提高查询效率的一种机制
(2) InnoDB
提供一组用来记录事务活动的文件
MySQL数据库默认的存储引擎,同时是一个重量级的存储引擎
特点:非常安全
最大特点:支持事务,以保证数据的安全,并且也不能压缩,不能转换为只读,不能很好的节省存储空间
InnoDB支持事务,支持数据库崩溃后自动恢复机制
(3)MEMORY
优点:查询效率最高
缺点:不安全,关机之后数据消失,因为数据和索引都是在内存当中
十八、事务
一个事务是一个完整的业务逻辑
1.什么是一个完整的业务逻辑
假设转账,从A账户向B账户中转账1000
将A账户的钱减去1000(update语句)
将B账户的钱加上1000(update语句)
这就是一个完整的逻辑
以上的操作是一个最小的工作单元,要么同时成功,要么同时失败,不可再分,这两个update语句要求必须同时成功或者失败,这样才能保证钱是正确的
2.只有DML语句才有事务
增删改三个语句与事务有关(数据安全第一位)
3.事务如何实现
InnoDB存储引擎:提供一组用来记录事务活动的文件
事务开启了:
insert,delete,update
事务结束了!
在事务执行的过程中,每一条DML的操作都会记录到“事务性活动的日志文件”中
在事务执行过程中,我们可以提交事务,也可以回滚事务
(1)提交事务?会自动提交
commit提交事务
清空事务性活动的日志文件,将数据全部彻底持久化到数据库表中
提交事务标志着事务结束
(2)回滚事务
rollback 回滚到上一次提交的地方
将之前所有的DML操作全部撤销, 并清空事 务性活动的日志文件
mysql> use bjpowernode; Database changed mysql> delete from dept_bak; Query OK, 1 row affected (0.44 sec) mysql> select * from dept_bak; Empty set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec mysql> insert into dept_bak values (20,'abc','tj'); Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak values (20,'abc','tj'); Query OK, 1 row affected (0.00 sec) mysql> select * from dept_bak; +--------+-------+------+ | DEPTNO | DNAME | LOC | +--------+-------+------+ | 20 | abc | tj | | 20 | abc | tj | +--------+-------+------+ 2 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.36 sec) mysql> select * from dept_bak; Empty set (0.00 sec)
(3)怎么将MySQL的自动提交机制关闭呢?
先执行start transaction,所有数据插入完成后执行commit
4.事务的特性
(1)A:原子性
最小单元,不可再分
(2)C:一致性
在同一事务中,左右操作必须同时成功或同时失败
(3)I:隔离性
事务之间有一定的隔离,不能相互影响
隔离级别:
读未提交:read uncommitted(最低级别)
读已提交:read committed
可重复读:repeatable read
序列化/串行化:serializable(最高级别)
(4)D:持久性
事务最终结束的一个屏障,事务提交,就相当于将没有保存到硬盘的数据保存到硬盘中
十九、索引(index)
主键,unique自动添加索引对象
索引是在数据库表的字段上添加的,是为了提交查询效率存在的一种机制
索引相当于一本书的目录,是为了缩小扫描范围而存在的机制
1.添加索引的条件
1.数据量大
2.该字段经常出现在where自居后,以条件形式存在
3.该字段很少用DML操作
2.创建和删除
mysql> create index empname_index on emp(ename);
mysql> drop index empname_index on emp;
3.查看是否使用索引进行检索
mysql> explain select * from emp where ename='KING';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.38 sec)
mysql> create index emp_empname_index on emp(ename);
Query OK, 0 rows affected (0.69 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from emp where ename='KING';
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | emp_empname_index | emp_empname_index | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
4.索引失效
mysql> explain select * from emp where ename='KING'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.38 sec) mysql> create index emp_empname_index on emp(ename); Query OK, 0 rows affected (0.69 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from emp where ename='KING'; +----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | emp | NULL | ref | emp_empname_index | emp_empname_index | 43 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
4.索引失效
1)使用模糊查询
mysql> select * from emp where ename like '%T'; +-------+-------+---------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+---------+------+------------+---------+------+--------+ | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | +-------+-------+---------+------+------------+---------+------+--------+ 1 row in set (0.36 sec) mysql> explain select * from emp where ename like '%T'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 11.11 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
2)使用or
mysql> select * from emp where ename='KING' or job='manager'; mysql> explain select * from emp where ename='KING' or job='manager'; +----+-------------+-------+------------+------+-------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | emp_empname_index | NULL | NULL | NULL | 14 | 16.43 | Using where | +----+-------------+-------+------------+------+-------------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
3)使用复合索引,没有使用左侧的列查找
mysql> create index emp_job_sal_index on emp(job,sal); mysql> explain select * from emp where job='manager'; +----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | emp | NULL | ref | emp_job_sal_index | emp_job_sal_index | 39 | const | 3 | 100.00 | NULL | +----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+ mysql> explain select * from emp where sal=800; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
5.索引分类
(1)单一索引:一个字段添加索引
(2)复合索引:两个字段或更多的字段上添加索引
(3)主键索引:具有unique约束的字段上添加索引
注意:唯一性比较弱的字段上添加索引用处不大
二十、视图
站在不同的角度取看待同一份数据
1.创建及删除视图对象
//表复制
mysql> create table dept2 select * from dept;
mysql> select * from dept2;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
//创建视图对象
mysql> create view dept2_view as select * from dept2;
//删除视图对象
mysql> drop view dept2_view;
2.视图做什么
我们可以面向视图对象进行增删改查,对视图对象的增删改查,会导致原表被操作
mysql> create view dept2_view as select * from dept2; //面向视图查询 mysql> select * from dept2_view; //面向视图插入 mysql> insert into dept2_view(deptno,dname,loc) values (60,'sales','beijing'); //查询原表数据 mysql> select * from dept2; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | | 60 | sales | beijing | +--------+------------+----------+ //面向视图删除 mysql> delete from dept2_view; //查询原表数据 mysql> select * from dept2;
增删改查用叫做:CRUD



