mysql概念
sql、DB、DBMS之间关系
- sql :结构化查询语言,是一门标准通用的语言。标准的sql适合于所有的数据库产品
- DB:(database)数据库
- DBMS:database mangement system(数据库管理系统,常见的mysql、oracle、DB2、Sybase、SqlServer)
DBMS负责执行SQL,使用sql操作DB中的数据
数据库中表
表:table是数据库的基本组成单元,所有数据都已表格的形式组织,目的是可读性强。 表包括行和列:
- 行被称为数据
- 列被称为字段 | 学号(int) | 姓名(varchar) | 年龄(int) | | --- | --- | --- | | 1 | 张三 | 12 | | 2 | 李四 | 13 |
每个字段都包含:字段名、数据类型、相关的约束。
SQL的分类
sql语句包括增删改查
- DQL(数据查询语言):查询语句,所有select都是DQL
- DML(数据修改语言):更新语句,包含insert、update、delete,对数据进行增删改
- DDL(数据定义语言):create、drop、alter,对表结构进行增删改
- TCL(事务控制语言):commit提交事务,rollback撤销事务
- DCL(数据控制语言):grant授权,revoke取消权限
导入和删除数据
第一步:登陆mysql数据库管理系统dos命令窗口:mysql -uroot -p12345678 第二步:查看有哪些数据库show databases;(这个不是sql语句,属于mysql的命令) 第三步:创建属于我们自己的数据库 create database pnode; 数据 第四步:使用pnode数据 use pnode; (这个不是sql语句,属于mysql的命令。)
DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;
CREATE TABLE DEPT
(DEPTNO int(2) not null ,
DNAME VARCHAR(14) ,
LOC VARCHAR(13),
primary key (DEPTNO)
);
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)
)
;
CREATE TABLE SALGRADE
( GRADE INT,
LOSAL INT,
HISAL INT );
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
30, 'SALES', 'CHICAGO');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
40, 'OPERATIONS', 'BOSTON');
commit;
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7369, 'SMITH', 'CLERK', 7902, '1980-12-17'
, 800, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20'
, 1600, 300, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7521, 'WARD', 'SALESMAN', 7698, '1981-02-22'
, 1250, 500, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7566, 'JONES', 'MANAGER', 7839, '1981-04-02'
, 2975, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28'
, 1250, 1400, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01'
, 2850, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7782, 'CLARK', 'MANAGER', 7839, '1981-06-09'
, 2450, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19'
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7839, 'KING', 'PRESIDENT', NULL, '1981-11-17'
, 5000, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08'
, 1500, 0, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7876, 'ADAMS', 'CLERK', 7788, '1987-05-23'
, 1100, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7900, 'JAMES', 'CLERK', 7698, '1981-12-03'
, 950, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7902, 'FORD', 'ANALYST', 7566, '1981-12-03'
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7934, 'MILLER', 'CLERK', 7782, '1982-01-23'
, 1300, NULL, 10);
commit;
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
1, 700, 1200);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
2, 1201, 1400);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
3, 1401, 2000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
4, 2001, 3000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
5, 3001, 9999);
commit;
sql脚本
当一个文件的扩展名是.sql,并且该文件中编写了大量的sql语句,我们称这样的文件为sql脚本。 注意:直接使用source命令可以执行sql脚本。
删除数据库
drop database bjpowernode
查看表结构
先显示所有表:show tables;
使用 desc 命令查看表结构:
mysql> desc EMP;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int(4) | NO | PRI | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int(4) | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int(2) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> desc DEPT;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2) | NO | PRI | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| LOC | varchar(13) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
查看表中所有数据
select * from emp;
select * from dept;
查看当前使用的数据库
查看当前使用的数据select database();
查看使用版本 select version();
终止语句,退出mysql
- \c 结束一条未执行完成的命令
- exit 退出mysql
查看创建表的语句
show create table emp;
mysql> show create table EMP;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EMP | CREATE TABLE `EMP` (
`EMPNO` int(4) NOT NULL,
`ENAME` varchar(10) DEFAULT NULL,
`JOB` varchar(9) DEFAULT NULL,
`MGR` int(4) DEFAULT NULL,
`HIREDATE` date DEFAULT NULL,
`SAL` double(7,2) DEFAULT NULL,
`COMM` double(7,2) DEFAULT NULL,
`DEPTNO` int(2) DEFAULT NULL,
PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查询语句DQL
基础查询
语法格式: select 字段1,字段2,字段3,... from 表名;
- sql语句都必须以";"结尾。
- sql不区分大小写
# 查询员工年薪
select ename, sal *12 as '年薪' from emp;
条件查询
语法格式: **select 字段1,字段2,字段3,... ** **from 表名 ** where 条件 ; 执行顺序:先from,然后where,最后执行select查询
# 查询工资等于5000的员工
select ename,sal from EMP where sal = 5000;
# 查询工资不等于5000的员工
select ename,sal from EMP where sal <> 5000;
select ename,sal from EMP where sal != 5000;
# 查询SMITH的工资
select sal from EMP where ename='SMITH';
条件查询-区间查询between and
#查询薪水在1500到5000区间
mysql> select ename, sal from EMP where sal between 1500 and 5000;
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| FORD | 3000.00 |
+--------+---------+
8 rows in set (0.01 sec)
#查询字符
mysql> select ename from EMP where ename between 'A' and 'C';
+-------+
| ename |
+-------+
| ALLEN |
| BLAKE |
| ADAMS |
+-------+
3 rows in set (0.00 sec)
- between and使用时必须是左小右大,否则查不出数据。
- between and查询数字时,左右都是闭区间
- between and查询字符时,左闭右开区间
条件查询-判断查询is null和is not null
查询字段是null, 数据库中NULL代表什么都没有,为空。不能用=号做判断
mysql> select ename, comm from EMP where comm is null;
+--------+------+
| ename | comm |
+--------+------+
| SMITH | NULL |
| JONES | NULL |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+------+
10 rows in set (0.00 sec)
mysql> select ename, comm from EMP where comm is not null;
+--------+---------+
| ename | comm |
+--------+---------+
| ALLEN | 300.00 |
| WARD | 500.00 |
| MARTIN | 1400.00 |
| TURNER | 0.00 |
+--------+---------+
4 rows in set (0.00 sec)
条件查询-and和or的优先级
#找出工作岗位是SALESMAN和MANAGER
mysql> select ename,job from EMP where job = 'MANAGER' or job= 'SALESMAN';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
#and和or联合使用,找出薪资大于1500,并且部门编号是20或30部门的员工;
#and 优先级大于or,可以使用()改变运算优先级
mysql> select ename,sal,deptno from EMP where sal > 1500 and (deptno = 20 or deptno=30);
+-------+---------+--------+
| ename | sal | deptno |
+-------+---------+--------+
| ALLEN | 1600.00 | 30 |
| JONES | 2975.00 | 20 |
| BLAKE | 2850.00 | 30 |
| SCOTT | 3000.00 | 20 |
| FORD | 3000.00 | 20 |
+-------+---------+--------+
5 rows in set (0.00 sec)
条件查询-in查询
in等同于or, in后边表示具体的值,不是区间。
#找出工作岗位是SALESMAN和MANAGER
mysql> select ename,job from EMP where job = 'MANAGER' or job= 'SALESMAN';
mysql> select ename,job from EMP where job in('MANAGER', 'SALESMAN');
#找出薪资是1200和3000的
mysql> select ename,job,sal from EMP where sal in(1200, 3000);
+-------+---------+---------+
| ename | job | sal |
+-------+---------+---------+
| SCOTT | ANALYST | 3000.00 |
| FORD | ANALYST | 3000.00 |
+-------+---------+---------+
2 rows in set (0.00 sec)
条件查询-模糊查询
找出名字含有 o 的;在模糊查询中,必须掌握2个特殊符号, 一个%:表示任意多个字符; 一个_ : 表示任意1个字符;
#找出名字含有 o 的员工;
mysql> select ename from EMP where ename like '%O%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
3 rows in set (0.00 sec)
#找出名字第二个字符是A的员工
mysql> select ename from EMP where ename like '_A%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+
3 rows in set (0.00 sec)
注意:如果需要查询的是特殊字符,比如_、% , 那么就需要使用 \ 做转义;
数据排序
默认是升序asc,如果降序使用order by sal desc;
#按照工资升序,找出员工名和薪资
mysql> select ename,sal from EMP order by sal;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
+--------+---------+
14 rows in set (0.01 sec)
select ename,sal from EMP order by sal asc;
select ename,sal from EMP order by sal desc;
当一个字段值相同,可以设置多个字段参与排序;排序字段用逗号分开
#按照工资的降序排,如果相同,则按照名称的升序排
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 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
14 rows in set (0.12 sec)
#找出工作岗位是SALESMAN的员工,并且要求按照薪资降序排列
mysql> select ename, sal, job from EMP where job='SALESMAN' order by sal desc;
+--------+---------+----------+
| ename | sal | job |
+--------+---------+----------+
| ALLEN | 1600.00 | SALESMAN |
| TURNER | 1500.00 | SALESMAN |
| WARD | 1250.00 | SALESMAN |
| MARTIN | 1250.00 | SALESMAN |
+--------+---------+----------+
4 rows in set (0.01 sec)
DQL语句执行顺序: select ------ 3 _ field_ from ------ 1 _ tableName_ where ------ 2 condition order by ------ 4 .... order by是最后执行。
分组函数
- count:计数
- sum:求和
- avg:求平均值
- max:最大值
- min:最小值
所有的分组函数都是对“一组”数据进行操作。
#找出工资总和
select sum(sal) from EMP;
#找出最高工资
select max(sal) from EMP;
#找出最低工资
select min(sal) from EMP;
#算出平均工资
select avg(sal) from EMP;
#找出总人数
select count(ename) from EMP;
- 分组函数会自动忽略NULL;
- 分组函数不能直接放在where子句中。【错误:select ename, sal from EMP where sal > avg(sal);】
count(*)和count(comm)的区别?
- count(*)表示所有数据条数据,和字段无关;
- count(具体字段),查询出某个字段中不为NULL的数据个数;
查询工资高于平均工资的员工,需要使用到子查询; 分组函数不能直接使用在where中,因为group by是在where执行之后才执行的。 分2步骤进行查询 第一步:查询平均工资 select avg(sal) from EMP; 第二步:找出大于平均工资的员工 select ename, sal from EMP where sal > (select avg(sal) from EMP);
#找出工资大于平均工资的员工,使用子查询
mysql> select ename, sal from EMP where sal > (select avg(sal) from EMP);
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
6 rows in set (0.03 sec)
单行处理函数
单行处理函数,输入一行就输出一行; 计算每个员工的年薪;
mysql> select ename, (sal+comm)*12 as yearsal from EMP;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | NULL |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | NULL |
| MARTIN | 31800.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 18000.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+----------+
14 rows in set (0.00 sec)
使用ifnull() 空处理函数,进行判断;
#使用ifnull() 空处理函数,先进行NULL判断处理;
mysql> select ename, (sal + ifnull(comm, 0))*12 as yearsal from EMP;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)
分组查询 group by和having
- group by:按照某个字段或者某些字段进行分组
- having: 对分组之后的数据进行再次过滤
#按照工作岗位进行分组,并显示该岗位的最大薪资
mysql> select job,max(sal) from EMP group by job;
+-----------+----------+
| job | max(sal) |
+-----------+----------+
| ANALYST | 3000.00 |
| CLERK | 1300.00 |
| MANAGER | 2975.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 1600.00 |
+-----------+----------+
5 rows in set (0.00 sec)
⚠️:分组函数一般需要和group by 联合使用。任何一个分组函数都是在group by语句执行结束之后才执行的。 当一条sql语句没有group by时,是把整张表的数据组成了一组。 当一条语句中有group by时,select 后面只能跟分组函数和参与分组的字段【非常重要】。
多字段分组查询
多个字段联合起来一起分组
#找出每个部门不同工作岗位的最高薪资
mysql> select deptno,job,max(sal) from EMP group by deptno,job;
+--------+-----------+----------+
| deptno | job | max(sal) |
+--------+-----------+----------+
| 10 | CLERK | 1300.00 |
| 10 | MANAGER | 2450.00 |
| 10 | PRESIDENT | 5000.00 |
| 20 | ANALYST | 3000.00 |
| 20 | CLERK | 1100.00 |
| 20 | MANAGER | 2975.00 |
| 30 | CLERK | 950.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1600.00 |
+--------+-----------+----------+
9 rows in set (0.00 sec)
可以把多个字段联合起来进行分组。
having和where的选择
- where用于过滤行数据
- having用于过滤分组数据,使用了分组函数计算处理的数据
- where在数据分组前过滤,having在数据分组后过滤
- where查询条件后不可使用字段别名,having可以使用字段别名的数据
#找出每个部门最高薪资,要求显示薪资大于2900的数据
mysql> select max(sal),deptno from EMP where sal >2900 group by deptno;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
| 5000.00 | 10 |
| 3000.00 | 20 |
+----------+--------+
2 rows in set (0.00 sec)
#找出每个部门平均薪资,要求显示薪资大于2000的数据
# 第一步先找出每个部门平均薪资
select deptno,avg(sal) from EMP group by deptno;
#第二步 要求显示薪资大于2000的数据
mysql> select deptno,avg(sal) from EMP group by deptno having avg(sal) >2000;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
+--------+-------------+
2 rows in set (0.00 sec)
总结:如果没有使用到分组函数,并且可以先进行数据过滤的,那么就是where,这样会提高效率。当使用的 分组函数时就无法使用where,这时必须使用having。having和group by配合使用,分组函数一般也配合group by使用。
DQL语句执行顺序
select ------ 5 .. from ------ 1 .. where ------ 2 .. group by ------ 3 .. having ------ 4 .. order by ------ 6 ..
关联表查询
字段去重
使用distinct
对结果集去重复。
#查询有多少种工作岗位
select
distinct job
from EMP;
distinct
只能出现在所有字段的最前面;
去重,并不会改变原数据结构。
# distinct会把后面的所有字段联合起来去重
select
distinct deptno, job
from EMP;
+--------+-----------+
| deptno | job |
+--------+-----------+
| 20 | CLERK |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | MANAGER |
| 10 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | CLERK |
| 10 | CLERK |
+--------+-----------+
9 rows in set (0.00 sec)
关联查询
查询员工所在部门名称,并显示员工名称; 由于数据在2张表中,员工名称ename在EMP表,部门信息在DEPT表中。
#首先要给表进行命名,这样避免两个表的字段存在重复。
#添加where查询条件,可以过滤出来需要显示的信息。
mysql> select e.ename, d.dname from EMP e, DEPT d where e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+------------+
#符合笛卡尔积查询,显示56条数据。
select e.ename, d.dname from EMP e, DEPT d;
当添加where过滤条件时,并不会影响笛卡尔积匹配查询的次数。
内连接-等值查询
内连接的等值查询,条件是等量关系
#查询每个员工的部门名称,要求显示员工名和部门名
select
e.ename,d.dname
from
EMP e
join
DEPT d
on
e.deptno = d.deptno;
+-----------+--------------+
| 员工名 | 部门名称 |
+-----------+--------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+-----------+--------------+
14 rows in set (0.01 sec)
内连接-非等值查询
内连接中的非等值连接,连接条件中的关系是非等量关系;比如成绩分数不显示具体分数,而是根据分数显示等级
#查询出每个员工的工资等级,并显示员工的姓名、工资、工资等级
#第一步先查询工资
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 |
| 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.01 sec)
#第二步查询工资等级
mysql> select * from SALGRADE;
+-------+-------+-------+
| 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)
#第三步,将每个员工的工资匹配下工资等级
mysql> select e.ename, e.sal, s.grade
from
EMP e
join
SALGRADE s
on
e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
14 rows in set (0.01 sec)
#还可以按照薪资等级排序
mysql> select e.ename, e.sal, s.grade
from
EMP e
join
SALGRADE s
on
e.sal between s.losal and s.hisal;
order by
grade;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| MARTIN | 1250.00 | 2 |
| MILLER | 1300.00 | 2 |
| WARD | 1250.00 | 2 |
| ALLEN | 1600.00 | 3 |
| TURNER | 1500.00 | 3 |
| FORD | 3000.00 | 4 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| JONES | 2975.00 | 4 |
| KING | 5000.00 | 5 |
+--------+---------+-------+
14 rows in set (0.01 sec)
内连接-自连接查询
在同一个表内数据的查询, 特点是:一张表看成2张表;自己和自己的一些字段相互连接查询。
#查询员工的领导,显示员工名和领导名,[存在的关系:员工的领导编号MGR = 领导的员工编号EMPNO]
#由于领导(MGR)也是数据员工表,所以是EMP表自己连接自己进行查询
mysql> select
e.ename as '员工', s.ename as '领导'
from
EMP e
join
EMP s
on
e.mgr = s.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.01 sec)
注意:此时查询出来的数据为13条,少了 KING
数据的显示,因为 KING 的 MGR 为 NULL 无法进行等值匹配,所以把数据给删除了。为了解决这个问题,必须使用下面的外连接查询。
外连接查询
内连接和外连接的区别:
- 内连接:
a表和b表进行连接,两张表能匹配的数据查询出来【匹配不上会丢失,比如上面的KING数据】。a表和b表没有主副之分。两张表是平等的。
- 外连接
a表和b表进行连接,一张表是主表,一张是副表。主要查询主表数据,如果副表的数据没有和主表相匹配上,副表会自动模拟出 NULL 为之相互匹配。 总结:外连接查询,主表的数据不会丢失。
外连接分类:
- 左外连接,表示左表是主表
- 右外连接,表示右表是主表
#内连接
mysql> select
e.ename as '员工', s.ename as '领导'
from
EMP e
join
EMP s
on
e.mgr = s.empno;
#外连接
mysql> select
e.ename as '员工', s.ename as '上级'
from
EMP e
left join
EMP s
on
e.mgr = s.empno;
+--------+--------+
| 员工 | 上级 |
+--------+--------+
| 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)
使用外连接,可以把主表的所有数据都显示出来,如果没有和副表匹配上的,副表会自动填充NULL数据。
在join前添加 left或者right 就是左外连接或右外连接。
- left 左边的表就是主表。
- right 右边的表是主表
#右外连接,查询员工和领导
mysql> select
e.ename as '111', s.ename as '222'
from
EMP s
right join
EMP e
on
e.mgr = s.empno;
+--------+-------+
| 员工 | 上级 |
+--------+-------+
| 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)
外连接特点,主表数据不会丢失,匹配不出来的会填充NUll
#找出哪个部门没有员工,【首先确定下来主表】
mysql> select
e.*, d.*
from
EMP e
right join
DEPT d
on
e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
15 rows in set (0.00 sec)
mysql> select
e.*, d.*
from
EMP e
right join
DEPT d
on
e.deptno = d.deptno;
where
e.empno is null;
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+
1 row in set (0.00 sec)
3张表连接查询
先处理两张表的查询,然后在查其它表 ... A join B on 条件 join C on ... 表示: A表和B表先进行连接查询,再和C表进行连接查询
#找出每个员工的员工名、部门名称、工资等级;
第一步先把需要的数据查询并显示出来
mysql> select
e.ename, e.sal, e.deptno
from
EMP e;
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| SMITH | 800.00 | 20 |
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| CLARK | 2450.00 | 10 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| JAMES | 950.00 | 30 |
| FORD | 3000.00 | 20 |
| MILLER | 1300.00 | 10 |
+--------+---------+--------+
14 rows in set (0.00 sec)
#显示工资表
mysql> select * from SALGRADE;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.04 sec)
#先使用EMP和DEPT表关联查询,然后再用EMP和SALGRADE关联查询
mysql> 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 s.hisal;
+--------+------------+-------+
| ename | dname | grade |
+--------+------------+-------+
| SMITH | RESEARCH | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCH | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | ACCOUNTING | 4 |
| SCOTT | RESEARCH | 4 |
| KING | ACCOUNTING | 5 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| FORD | RESEARCH | 4 |
| MILLER | ACCOUNTING | 2 |
+--------+------------+-------+
14 rows in set (0.04 sec)
查询员工的部门、薪资等级、上级领导【需要使用到外连接】
mysql> select
e.ename as '员工姓名', d.dname as '部门', s.grade as '薪资等级', e1.ename as '上级领导'
from
EMP e
join
DEPT d
on
e.deptno = d.deptno
join
SALGRADE s
on
e.sal between s.losal and s.hisal
left join
EMP e1
on
e.mgr = e1.empno;
+--------------+------------+--------------+--------------+
| 员工姓名 | 部门 | 薪资等级 | 上级领导 |
+--------------+------------+--------------+--------------+
| SMITH | RESEARCH | 1 | FORD |
| ALLEN | SALES | 3 | BLAKE |
| WARD | SALES | 2 | BLAKE |
| JONES | RESEARCH | 4 | KING |
| MARTIN | SALES | 2 | BLAKE |
| BLAKE | SALES | 4 | KING |
| CLARK | ACCOUNTING | 4 | KING |
| SCOTT | RESEARCH | 4 | JONES |
| KING | ACCOUNTING | 5 | NULL |
| TURNER | SALES | 3 | BLAKE |
| ADAMS | RESEARCH | 1 | SCOTT |
| JAMES | SALES | 1 | BLAKE |
| FORD | RESEARCH | 4 | JONES |
| MILLER | ACCOUNTING | 2 | CLARK |
+--------------+------------+--------------+--------------+
14 rows in set (0.00 sec)
子查询
子查询就是select语句中可以嵌套select语句,被嵌套的语句就是子查询。 子查询可以出现在select、from、where后面
where后面嵌套子查询
找出高于平均薪资的员工
第一步可以分组查询算出平均工资
mysql> select avg(sal) from EMP;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)
第二步使用子查询,把select语句放在where后
select *
from
EMP
where
sal > (select avg(sal) from EMP);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 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 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+------+--------+
6 rows in set (0.00 sec)
from后面嵌套子查询
找出每个部门平均薪水的 薪资等级。【先算平均薪水,再查询等级】
#按照部门分组,算出平均薪资
mysql> select deptno, avg(sal) as avgsal from EMP group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.01 sec)
# 把上面查出来的结果当作一张临时表t, 让t表和SALGRADE s表连接,条件是
# t.avgsal between s.losal and s.hisal
mysql> select
t.deptno,s.grade
from
(select deptno, avg(sal) as avgsal from EMP group by deptno) t
join
SALGRADE s
on
t.avgsal between s.losal and s.hisal;
+--------+-------+
| deptno | grade |
+--------+-------+
| 10 | 4 |
| 20 | 4 |
| 30 | 3 |
+--------+-------+
3 rows in set (0.01 sec)
找出每个部门的薪水等级的平均值。
#先找出每个员工的薪水等级
mysql> select
e.ename,e.deptno,e.sal,s.grade
from
EMP e
join
SALGRADE s
on
e.sal between s.losal and s.hisal;
+--------+--------+---------+-------+
| ename | deptno | sal | grade |
+--------+--------+---------+-------+
| SMITH | 20 | 800.00 | 1 |
| ALLEN | 30 | 1600.00 | 3 |
| WARD | 30 | 1250.00 | 2 |
| JONES | 20 | 2975.00 | 4 |
| MARTIN | 30 | 1250.00 | 2 |
| BLAKE | 30 | 2850.00 | 4 |
| CLARK | 10 | 2450.00 | 4 |
| SCOTT | 20 | 3000.00 | 4 |
| KING | 10 | 5000.00 | 5 |
| TURNER | 30 | 1500.00 | 3 |
| ADAMS | 20 | 1100.00 | 1 |
| JAMES | 30 | 950.00 | 1 |
| FORD | 20 | 3000.00 | 4 |
| MILLER | 10 | 1300.00 | 2 |
+--------+--------+---------+-------+
14 rows in set (0.00 sec)
#第二步,按照 deptno 分组,求grade的平均值
mysql> select
e.ename,e.deptno,avg(s.grade) as '部门薪资等级平均值'
from
EMP e
join
SALGRADE s
on
e.sal between s.losal and s.hisal
group by
e.deptno;
+-------+--------+-----------------------------+
| ename | deptno | 部门薪资等级平均值 |
+-------+--------+-----------------------------+
| CLARK | 10 | 3.6667 |
| SMITH | 20 | 2.8000 |
| ALLEN | 30 | 2.5000 |
+-------+--------+-----------------------------+
3 rows in set (0.00 sec)
select后面嵌套子查询
显示每个员工的名称和所在部门名称
# 第一种方法,使用的关联查询,查询的次数更多
select e.ename, d.dname
from
EMP e
join
DEPT d
on
e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+------------+
14 rows in set (0.03 sec)
# 第二种方法,没有使用关联查询
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 |
+--------+------------+
14 rows in set (0.00 sec)
# 可见,下面这种方法查询的更快
union联合查询
可以将查询结果相加;
#找出工作岗位是 MANAGER 和 SALESMAN 的员工;
#第一种: select ename,job from EMP where job='SALESMAN' or job = 'MANAGER';
#第二种: select ename,job from EMP where job in('SALESMAN', 'MANAGER');
#第三种:
select ename,job from EMP where job='SALESMAN'
union
select ename,job from EMP where job='MANAGER';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
+--------+----------+
7 rows in set (0.01 sec)
limit查询,[处理分页查询]
limit取结果集中的部分数据, 语法:limit startIndex, length startIndex: 表示起始位置 length:取数据的长度 取出工资前5名的员工【将员工工资降序排序,取前面5个】
select
ename,sal
from
EMP
order by
sal
desc
limit 0,5;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)
sql语句执行顺序
【from - where - group by - having - select - order by - limit】 ** 从 哪 分组 过滤 查 排序 分页**
select ------5
...
from ------1
...
where ------2
...
group by ------3
...
having ------4
...
order by ------6
...
limit ------7
... ;
数据表的编辑、事务、索引
创建表
建表语句的语法:
create table 表名称(
字段名 数据类型 约束,
字段名2 数据类型 约束,
...
);
关于MySQL当中数据类型:
int | 整数 | java中的init |
---|---|---|
bigint | 长整数(java中的long) | java中的long |
float | 浮点数 | java中的float double |
char | 定长字符串 | String |
varchar | 可变长字符串(最长存放255个) | StringBuffer/StringBuilder |
date | 日期 | java.sql.Date |
BLOB | 二进制大文件(存储图片或者视频流媒体文件) | Object |
CLOB | 字符大对象(存储大的文本,可以存放4G的字符串文件) | Object |
char和varchar的区别:
- char属于固定长度字符串,性能更高。比如:日期值、性别类型
- varchar是可变换长度的数据字符串,最初为255个。长度会随时变化,节省空间。
BLOB和CLOB类型的使用
主要用来存放大对象,比如存放电影数据表,海报的类型使用BLOB,简介的类型使用CLOB。
id(int) | name(varchar) | images(BLOB) | introduce(CLOB) |
---|---|---|---|
1 | hello | ||
2 | sql |
创建学生表
学生信息:
学号: bigint
姓名: varchar
性别: char
班级编号: int
生日: char
------------------------
create table t_student(
sNo bigint,
name varchar(255),
gender char(1),
classNo varchar(255),
birth char(10)
);
插入数据
insert插入数据语法:
语法格式:insert into 表名(字段1,字段2,字段3,字段4, ...) values(value1, value2, value3, value4, ...) 第一种:insert into 表名(字段1,字段2,字段3,字段4, ...) values(value1, value2, value3, value4, ...); 第二种:insert into 表名(字段1) values(value1); 第三种:insert into 表名 values(value1, value2, value3, value4); 这种情况values必须是全部字段的值。 第四种:insert into 表名(字段1) values(value1), (value2), (value3), (value4); 用逗号隔开,一次插入多条数据
给学生表插入数据:
insert into t_student(sNo, name, gender, classNo, birth) values(1,'zs','f','class1', '2022-10-22');
insert into t_student(sNo, name, gender, classNo, birth) values(2,'ls','m','class2', '2021-10-22');
mysql> select * from t_student;
+------+------+--------+---------+------------+
| sNo | name | gender | classNo | birth |
+------+------+--------+---------+------------+
| 1 | zs | f | class1 | 2022-10-22 |
| 2 | ls | m | class2 | 2021-10-22 |
+------+------+--------+---------+------------+
mysql> insert into t_student(name) values('wangwu');
mysql> select * from t_student;
+------+--------+--------+---------+------------+
| sNo | name | gender | classNo | birth |
+------+--------+--------+---------+------------+
| 1 | zs | f | class1 | 2022-10-22 |
| 2 | ls | m | class2 | 2021-10-22 |
| NULL | wangwu | NULL | NULL | NULL |
+------+--------+--------+---------+------------+
如果字段没有值,则设置为NULL,因为在创建表时设置的default为NULL。
mysql> desc t_student;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| sNo | bigint(20) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| classNo | varchar(255) | YES | | NULL | |
| birth | char(10) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
删除表,之后在插入数据
删除表 drop 需要先进行表是否存在的判断
drop table if exists tableName;
#如果t_student表存在,就删除表
drop table if exists t_student;
#删除表后,重新创建t_student表
create table t_student(
sNo bigint,
name varchar(255),
gender char(1) default 'm',
classNo varchar(255),
bith char(10)
);
mysql> desc t_student;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| sNo | bigint(20) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| gender | char(1) | YES | | m | |
| classNo | varchar(255) | YES | | NULL | |
| birth | char(10) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
insert into t_student(name) values('wang5');
mysql> select * from t_student;
+------+-------+--------+---------+-------+
| sNo | name | gender | classNo | birth |
+------+-------+--------+---------+-------+
| NULL | wang5 | m | NULL | NULL |
+------+-------+--------+---------+-------+
此时gender 默认就是m值。
修改表数据
先复制一张表,对复制出来的表进行数据修改
create table copyTableName as select * from t_student;
#复制一张学生表
create table t_student1 as select * from t_student;
#复制一张员工表,只包括员工编号和薪水
create table EMP1 as select empno,sal from EMP;
mysql> select * from EMP1;
+-------+---------+
| empno | sal |
+-------+---------+
| 7369 | 800.00 |
| 7499 | 1600.00 |
| 7521 | 1250.00 |
| 7566 | 2975.00 |
| 7654 | 1250.00 |
| 7698 | 2850.00 |
| 7782 | 2450.00 |
| 7788 | 3000.00 |
| 7839 | 5000.00 |
| 7844 | 1500.00 |
| 7876 | 1100.00 |
| 7900 | 950.00 |
| 7902 | 3000.00 |
| 7934 | 1300.00 |
+-------+---------+
将查询结果插入到一张表中,前提必须确保字段一致
create table dept1 as select * from DEPT;
insert into dept1 select * from DEPT;
mysql> select * from dept1;
+--------+------------+----------+
| 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 |
+--------+------------+----------+
修改表数据
update更新,语法格式:
update 表名 set 字段名1=值1, 字段名2=值2 ... where 条件;
注意:没有where条件,整张表的数据全部更新。
#将部门deptno为10的LOC修改为上海, 将部门名称修改为HR
mysql> update DEPT set LOC='shanghai',dname='HR' where deptno = 10;
mysql> select * from DEPT;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | HR | shanghai |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
删除表数据
delete语法格式:
delete from 表名 where 条件;
没有where条件,全部删除
mysql> create table dept2 as select * from dept;
mysql> delete from dept2 where deptno = 10;
mysql> select * from dept2;
+--------+------------+---------+
| DEPTNO | DNAME | LOC |
+--------+------------+---------+
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+---------+
删除表所有数据
delete from dept2;
怎么删除大表de数据?
使用 truncate
,风险非常大,数据无法回滚。
mysql> truncate table dept2; //表被截断,数据不可回滚
mysql> select * from dept2;
Empty set (0.01 sec)
修改表结构alter
就是修改DDL(create, drop, alter);类型语句
create table 表名(...)创建表
drop table if exists 表名 删除表
alter table 表名 修改表结构字段类型
ADD 添加字段
# 给表 dept2 添加一个coll字段,类型int,并使用first放置第一个位置
mysql> alter table dept2 add column coll int first;
mysql> desc dept2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| coll | int(11) | YES | | NULL | |
| DEPTNO | int(2) | NO | | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| LOC | varchar(13) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
# 给 dept2 添加col2字段,类型int,并且放置到dname之后
mysql> alter table dept2 add column col2 int after dname;
mysql> desc dept2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| coll | int(11) | YES | | NULL | |
| DEPTNO | int(2) | NO | | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| col2 | int(11) | YES | | NULL | |
| LOC | varchar(13) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
MODIFY修改字段类型
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
# 修改表 dept2 中的 col2 字段的类型为 varchar
mysql> alter table dept2 modify col2 varchar(255);
mysql> desc dept2;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| coll | int(11) | YES | | NULL | |
| DEPTNO | int(2) | NO | | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| col2 | varchar(255) | YES | | NULL | |
| LOC | varchar(13) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
DROP删除字段
ALTER TABLE <表名> DROP <字段名>;
# 删除表 dept2 中的 coll字段
mysql> alter table dept2 drop coll;
mysql> desc dept2;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| DEPTNO | int(2) | NO | | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| col2 | varchar(255) | YES | | NULL | |
| LOC | varchar(13) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
CHANGE修改字段名称
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
#修改dept2 字段 col2为部门人数,类型为int
mysql> alter table dept2 change col2 deptpeoplenumber int;
mysql> desc dept2;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| DEPTNO | int(2) | NO | | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| deptpeoplenumber | int(11) | YES | | NULL | |
| LOC | varchar(13) | YES | | NULL | |
+------------------+-------------+------+-----+---------+-------+
RENAME修改表名
ALTER TABLE <旧表名> RENAME [TO] <新表名>;
#修改表 dept2名称为 deptx
mysql> alter table dept2 rename to deptx;
mysql> show tables;
+-----------------+
| Tables_in_pnode |
+-----------------+
| DEPT |
| dept1 |
| deptx |
+-----------------+
front1234!
约束【Constraint】作用以及常见约束
什么是约束?常见的约束有哪些?
在创建表的时候,可以给表的字段添加相应的约束,目的是为了让表中的数据具有合法性、有效性、完整性。 常见的约束:
- 非空约束(not null):约束的字段不能为 NULL
- 唯一约束(unique):约束的字段不能重复
- 主键约束(primary key):约束的字段即不能为NULL,也不能为重复,简称PK
- 外键约束(foreign key):简称 FK
- 检查约束(check):注意Oracle数据库有check约束,但是mysql不支持;
非空约束 not null
drop table if exists dept1;
create table dept1(
id int,
name varchar(255) not null
);
mysql> insert into dept1(id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
唯一性unique约束
唯一约束的字段具有唯一性,不能重复,但是unqiue可以为NULL,NULL和NULL中间不能=;
mysql> create table t_user(
-> id int,
-> username varchar(255) unique
-> );
mysql> insert into t_user values(1, 'zs');
mysql> insert into t_user values(2, 'zs');
ERROR 1062 (23000): Duplicate entry 'zs' for key 'username'
给2列或者多列添加**unique**
约束, 是多个字段联合起来判断唯一性
mysql> drop table if exists t_user;
mysql> create table t_user(
-> id int,
-> usercode varchar(255),
-> username varchar(255),
-> unique(usercode, username)
-> );
mysql> insert into t_user values(1,'101', 'zs');
Query OK, 1 row affected (0.02 sec)
mysql> insert into t_user values(2,'102', 'zs');
Query OK, 1 row affected (0.08 sec)
mysql> insert into t_user values(2,'102', 'zs');
ERROR 1062 (23000): Duplicate entry '102-zs' for key 'usercode'
主键约束primary key
**使用:字段后添加 ****primary key**
mysql> 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', 'zs@qq.com');
insert into t_user values(2, 'ls', 'ls@qq.com');
insert into t_user values(3, 'ws', 'ss@qq.com');
# 主键不能重复
mysql> insert into t_user value(1, 'jk', 'jk@qq.com');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
# 主键不能为NULL
mysql>insert into t_user(username, email) values('zl', 'zl@qq.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
主键的作用:
- 主键的值,是这一行数据的唯一标识,即使该行其它字段值完全相同,只要主键不同,就是不同的数据。
- 表设计三范式,第一范式要求任何一张表都应该有主健。
主健的分类:
- 根据主健字段数量来划分:单一主键【常用】、复合主键【不推荐使用】
- 根据主健性质划分:自然主键、业务主键【使用业务字段设置主键,不推荐使用】
**一张表的主键约束只能有一个。**
使用表级约束方式定义主键
mysql> drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
primary key(id)
);
insert into t_user(id, username) values(1,'zs');
insert into t_user(id, username) values(2,'ls');
insert into t_user(id, username) values(1,'ws');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
主键值自增 auto_increment
mysql> drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
username varchar(255)
);
insert into t_user(username) values('zs');
insert into t_user(username) values('ls');
insert into t_user(username) values('we');
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
| 2 | ls |
| 3 | we |
+----+----------+
外键约束foreign key
外健主要为了解决数据存在冗余的问题。 业务背景:设置一张学生表,维护学生和班级信息 第一种方案: 一张表存储所有数据,缺陷数据冗余,不推荐使用
no(pk) name classno classname
-----------------------------------------------
1 zs1 101 高中1班
2 zs2 101 高中1班
3 zs3 102 高中2班
4 zs4 102 高中2班
5 zs5 102 高中2班
第二种方案:拆分为2张表
t_class 班级表 (父表)
cno(pk) cname
-----------------------
101 高中1班
102 高中2班
t_student 学生表。(子表)
sno(pk) sname classno(fk)
-------------------------------
1 zs1 101
2 zs2 101
3 zs3 102
4 zs4 102
5 zs5 102
操作顺序要求:
- 删除数据时,先删除子表,再删除父表
- 添加数据时,先添加父表,在添加子表
- 创建表,先创建父表,在创建子表
- 删除表的时候,先删除子表,再删除父表
#删除表
drop table if exists t_student;
drop table if exists t_class;
#创建表
create table t_class(
cno int primary key,
cname varchar(255)
);
create table t_student(
sno int primary key,
sname varchar(255),
classno int,
foreign key(classno) references t_class(cno)
);
**foreign key(classno) references t_class(cno)**
外键关联2张表,
#插入数据
insert into t_class values(101, 'class101');
insert into t_class values(102, 'class102');
insert into t_student values(1,'zs1', 101);
insert into t_student values(2,'zs2', 101);
insert into t_student values(3,'zs3', 102);
insert into t_student values(4,'zs4', 102);
insert into t_student values(5,'zs5', 102);
mysql> select * from t_class;
+-----+----------+
| cno | cname |
+-----+----------+
| 101 | class101 |
| 102 | class102 |
+-----+----------+
mysql> select * from t_student;
+------+-------+---------+
| sno | sname | classno |
+------+-------+---------+
| 1 | zs1 | 101 |
| 2 | zs2 | 101 |
| 3 | zs3 | 102 |
| 4 | zs4 | 102 |
| 5 | zs5 | 102 |
+------+-------+---------+
#外健的值不能随便加,必须是另外一张表中存在的数据
insert into t_student values(6, 'zs6', 103);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`pnode`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
#外健可以为NULL
insert into t_student(sno, sname) values(7, 'zs7');
mysql> select * from t_student;
+------+-------+---------+
| sno | sname | classno |
+------+-------+---------+
| 1 | zs1 | 101 |
| 2 | zs2 | 101 |
| 3 | zs3 | 102 |
| 4 | zs4 | 102 |
| 5 | zs5 | 102 |
| 7 | zs7 | NULL |
+------+-------+---------+
外键字段引用其它表的某个字段,被引用的字段在另外表中不一定是主健,但是必须具有唯一性 unique 约束;
常见的存储引擎
存储引擎是表存储数据的方式,常见的存储引擎,MyISAM、InnoDB、MEMORY三个。
mysql默认存储引擎InnoDB
。
#查看建表语句
show create table EMP;
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EMP | CREATE TABLE `EMP` (
`EMPNO` int(4) NOT NULL,
`ENAME` varchar(10) DEFAULT NULL,
`JOB` varchar(9) DEFAULT NULL,
`MGR` int(4) DEFAULT NULL,
`HIREDATE` date DEFAULT NULL,
`SAL` double(7,2) DEFAULT NULL,
`COMM` double(7,2) DEFAULT NULL,
`DEPTNO` int(2) DEFAULT NULL,
PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
mysql默认为 ENGINE=InnoDB
;默认的字符集采用UTF8
#完整的建表语句
create table t_x(
id int(11) default NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
查看当前mysql支持的存储引擎
show engines \G;
*************************** 1. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 8. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.01 sec)
MyISAM
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
------------------------------
MyISAM这种存储引擎不支持事务。
MyISAM是mysql最常用的存储引擎,但是这种引擎不是默认的。
MyISAM采用三个文件组织一张表:
xxx.frm(存储格式的文件)
xxx.MYD(存储表中数据的文件)
xxx.MYI(存储表中索引的文件)
优点:可被压缩,节省存储空间。并且可以转换为只读表,提高检索效率。 缺点:不支持事务。
InnoDB
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
优点:支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。
表的结构存储在xxx.frm文件中 数据存储在tablespace这样的表空间中(逻辑概念),无法被压缩,无法转换成只读。 这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。 InnoDB支持级联删除和级联更新。
MEMORY
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
缺点:不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。 优点:查询速度最快。以前叫做HEPA引擎。
事务的使用
事务原理
一个事务是一个完整的业务逻辑单元,不可再分。 比如:银行账户转账,从A账户向B账户转账10000.需要执行两条update语句:
update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';
以上两条DML语句必须同时成功,或者同时失败,不允许出现一条成功,一条失败。 要想保证以上的两条DML语句同时成功或者同时失败,那么就需要使用数据库的“事务机制”。
和事务相关的语句只有:DML语句。(insert delete update);
事务的存在是为了保证数据的完整性,安全性。
事务的四大特性
事务包括四大特性:ACID A: 原子性:事务是最小的工作单元,不可再分。 C: 一致性:事务必须保证多条DML语句同时成功或者同时失败。 I:隔离性:事务A与事务B之间具有隔离。 D:持久性:持久性说的是最终数据必须持久化到硬盘文件中,事务才算成功的结束。
事务隔离性的分类
事务隔离性存在隔离级别,理论上隔离级别包括4个:
第一级别:读未提交(read uncommitted)
对方事务还没有提交,我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读(Dirty Read)现象:表示读到了脏的数据。
第二级别:读已提交(read committed)
对方事务提交之后的数据我方可以读取到。
这种隔离级别解决了: 脏读现象没有了。
读已提交存在的问题是:不可重复读。
第三级别:可重复读(repeatable read)
这种隔离级别解决了:不可重复读问题。
这种隔离级别存在的问题是:读取到的数据是幻象。
第四级别:序列化读/串行化读(serializable)
解决了所有问题。
效率低。需要事务排队。
oracle数据库默认的隔离级别是:读已提交。
mysql数据库默认的隔离级别是:可重复读。
演示事务
* mysql事务默认情况下是自动提交的。
(什么是自动提交?只要执行任意一条DML语句则提交一次。)
怎么关闭自动提交?start transaction;
* 准备表:
drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
username varchar(255)
);
* 演示:mysql中的事务是支持自动提交的,只要执行一条DML,则提交一次。
mysql> insert into t_user(username) values('zs');
Query OK, 1 row affected (0.03 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
+----+----------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
+----+----------+
1 row in set (0.00 sec)
* 演示:使用start transaction; 关闭自动提交机制。
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t_user(username) values('lisi');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
| 2 | lisi |
+----+----------+
2 rows in set (0.00 sec)
mysql> insert into t_user(username) values('wangwu');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
| 2 | lisi |
| 3 | wangwu |
+----+----------+
3 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
+----+----------+
1 row in set (0.00 sec)
--------------------------------------------------------------------
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t_user(username) values('wangwu');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_user(username) values('rose');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_user(username) values('jack');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
| 4 | wangwu |
| 5 | rose |
| 6 | jack |
+----+----------+
4 rows in set (0.00 sec)
* 进行提交, rollback也是提交过的数据
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
| 4 | wangwu |
| 5 | rose |
| 6 | jack |
+----+----------+
4 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
| 4 | wangwu |
| 5 | rose |
| 6 | jack |
+----+----------+
4 rows in set (0.00 sec)
* 演示两个事务,假如隔离级别,设置隔离级别
演示第1级别:读未提交
set global transaction isolation level read uncommitted;
演示第2级别:读已提交
set global transaction isolation level read committed;
演示第3级别:可重复读
set global transaction isolation level repeatable read;
索引 Index
索引的定义以及作用
索引相当于给数据添加了一个目录,当查询数据时通过目录可以快速查询出对应数据。添加索引内部使用BTree算法对数据做了分类计算。 索引快速查询的原理,缩小了扫描范围。 添加索引是给某个或某些字段添加。
explain select ename,sal from EMP where sal = 3000;
#当 sal 字段没添加索引,sql语句会全表扫描,查询sal符合条件的数据
#当 sal 字段添加索引,sql语句根据索引查询,快速定位结果。内部采用BTree对数据进行分类
添加索引的前提
- 数据量很大
- 该字段很少有DML操作
- 该字段经常出现在where子句中。
主键或者具有unique约束的字段自动添加索引。
使用 explain
查看sql执行情况
mysql> explain select ename,sal from EMP where sal = 3000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
可以看到 type 为 ALL,表示全表扫描。
给字段添加索引
创建索引 index .. on ..
create index 索引名 on 表名(字段名);
create index emp_sal_index on EMP(sal);
mysql> explain select ename,sal from EMP where sal = 3000;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | EMP | NULL | ref | emp_sal_index | emp_sal_index | 9 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
可以看到 type 为 ref,总共扫描了 2行 rows数据。
删除索引
drop index 索引名 on 表名;
索引的分类
单一索引:单字段索引 复合索引:给多个字段联合起来添加索引 主键索引:主键上自动添加索引 唯一索引:有unique约束的字段会自动添加索引
视图view
视图的目的是保障数据安全, 可以把真实的数据字段给隐藏。操作视图会操作原表的数据。
创建和删除view
create table emp_bak as select * from emp;
create view emp_view as select empno,ename,sal from emp_bak;
update emp_view set ename='sam',sal=9999 where empno = 7369; // 通过视图修改原表数据。
delete from emp_view where empno = 7369; // 通过视图删除原表数据。
视图可以隐藏表的实现细节。保密级别较高的系统,数据库只对外提供相关的视图,程序员只对视图对象进行CRUD。
只有DQL才能创建view
create view myview as select empno,ename from emp;
drop view myview;
注意:只有DQL语句才能以视图对象的方式创建出来。
数据库的导入导出
导出
在系统的终端执行命令:【是在系统终端执行命令,不是在mysql中】
导出指定库 pnode 的指定表 emp;
mysqldump pnode emp > ~/Desktop/pnode.sql -uroot -p
导出库的所有数据
mysqldump pnode > ~/Desktop/pnodeall.sql -uroot -p
导入,进入到数据库中操作
create database pnode;
use pnode;
source ~/Desktop/pnodeall.sql
数据库设计的三范式
设计表的依据:按照三范式设计的表不会出现数据冗余。
三范式:
第一范式:任何一张表都应该有主键,并且每一个字段原子性不可再分。
第二范式:建立在第一范式的基础之上,所有非主键字段完全依赖主键,不能产生部分依赖。
多对多?三张表,关系表两个外键。
t_student学生表
sno(pk) sname
-------------------
1 张三
2 李四
3 王五
t_teacher 讲师表
tno(pk) tname
---------------------
1 王老师
2 张老师
3 李老师
t_student_teacher_relation 学生讲师关系表
id(pk) sno(fk) tno(fk)
----------------------------------
1 1 3
2 1 1
3 2 2
4 2 3
5 3 1
6 3 3
第三范式:建立在第二范式的基础之上,所有非主键字段直接依赖主键,不能产生传递依赖。
一对多?两张表,多的表加外键。
班级t_class
cno(pk) cname
--------------------------
1 班级1
2 班级2
学生t_student
sno(pk) sname classno(fk)
---------------------------------------------
101 张1 1
102 张2 1
103 张3 2
104 张4 2
105 张5 2
提醒:在实际的开发中,以满足客户的需求为主,有的时候会拿冗余换执行速度。
一对一表的设计:
一对一设计有两种方案:主键共享
t_user_login 用户登录表
id(pk) username password
--------------------------------------
1 zs 123
2 ls 456
t_user_detail 用户详细信息表
id(pk+fk) realname tel ....
------------------------------------------------
1 张三 1111111111
2 李四 1111415621
一对一设计有两种方案:外键唯一。
t_user_login 用户登录表
id(pk) username password
--------------------------------------
1 zs 123
2 ls 456
t_user_detail 用户详细信息表
id(pk) realname tel userid(fk+unique)....
-----------------------------------------------------------
1 张三 1111111111 2
2 李四 1111415621 1
扫码关注公众号:分享更多开发知识技巧 火眼金睛识物小程序,扫描识别不认识的动物、植物、汽车等。