mysql数据库入门

163 阅读42分钟

mysql概念

sql、DB、DBMS之间关系

  1. sql :结构化查询语言,是一门标准通用的语言。标准的sql适合于所有的数据库产品
  2. DB:(database)数据库
  3. 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

#查询薪水在15005000区间
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)

#andor联合使用,找出薪资大于1500,并且部门编号是2030部门的员工;
#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');

#找出薪资是12003000的
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语句放在whereselect * 
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)
1hello
2sql

创建学生表

学生信息:
学号: 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,因为在创建表时设置的defaultNULL。
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       高中12        zs2       101       高中13        zs3       102       高中24        zs4       102       高中25        zs5       102       高中2

第二种方案:拆分为2张表

t_class 班级表  (父表)
cno(pk)      cname
-----------------------
101          高中1102	         高中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,总共扫描了 2rows数据。

删除索引

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)
  ---------------------------------------------
  1011				1
  1022				1
  1033				2
  1044				2
  1055				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

扫码关注公众号:分享更多开发知识技巧 扫码_白色版.png 火眼金睛识物小程序,扫描识别不认识的动物、植物、汽车等。 火眼金睛小程序.jpeg