高级查询

常用的分组函数

COUNT
MIN
MAX
SUM
AVG

分组函数的语法
SELECT    [column,] group_function(column)
FROM        table
[WHERE    condition]
[GROUP BY    column]
[HAVING       group_function(column)expression 
[ORDER BY    column |  group_function(column)expression];

COUNT

COUNT( * | {[DISTINCT | ALL] column | expression})

COUNT( [DISTINCT | ALL] column | expression)：返回满足条件的非空(NULL)行的数量

SELECT COUNT( COMM) from emp;

结果：4
使用DISTINCT 关键字

SELECT COUNT( DISTINCT DEPTNO) from emp;

结果：3
查询部门30有多少个员工领取奖金

SELECT COUNT(comm) from emp where deptno=30;

MIN和MAX

MIN和MAX函数主要是返回每组的最小值和最大值。
MIN([DISTINCT | ALL] column | expression)
MAX([DISTINCT | ALL] column | expression)
MIN和MAX可以用于任何数据类型

查询入职日期最早和最晚的日期

SELECT
    MIN(hirdate),
    MAX(HIREDATE)
FROM
    emp

SUM函数和AVG函数'

SUM和AVG函数分别返回每组的总和及平均值。
SUM([DISTINCT | ALL] column | expression)
AVG([DISTINCT | ALL] column | expression)
SUM和AVG函数都是只能够对数值类型的列或表达式操作。
查询职位以SALES开头的所有员工平均工资、最低工资、最高工资、工资和。

SELECT
    AVG(SAL) 平均工资,
    MIN(SAL) 最低工资,
    MAX(SAL) 最高工资,
    SUM(SAL) 工资和
FROM
    emp
WHERE
    JOB LIKE 'SALES%'

IFNULL 函数可以使分组函数强制包含含有空值的记录

SELECT
    COUNT(IFNULL(COMM,0))
FROM
    emp;

所有员工的平均奖金

SELECT
    AVG(IFNULL(COMM,0))
FROM
    emp;

有奖金的平均奖金

SELECT
    AVG(COMM)
FROM
    emp;

练习：

查询部门20的员工，每个月的工资总和及平均工资。

SELECT
    AVG(sal) 平均工资,
    SUM(sal) 工资和
FROM
    emp
WHERE
    deptno = 20;

查询工作在CHICAGO的员工人数，最高工资及最低工资。

SELECT
    COUNT(m.empno) 员工人数,
    MAX(sal) 最高工资,
    MIN(sal) 最低工资
FROM
    emp m,
    dept d
WHERE
    m.deptno = d.DEPTNO
AND d.loc = 'CHICAGO';

查询员工表中一共有几种岗位类型

SELECT
    COUNT(DISTINCT JOB)
FROM
    emp;

group by 语句

image

通过 GROUP BY 子句可将表中满足WHERE条件的记录按照指定的列划分成若干个小组，其中GROUP BY子句指定要分组的
查询每个部门的部门编号，平均工资

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

查询每个部门的部门编号，每个部门的最低工资
查询每个部门的部门编号，每个部门的最高工资

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

使用规则:

在SELECT列表中除了分组函数那些项，所有列都必须包含在GROUP BY 子句中。

select ename, deptno, avg(sal) from emp group by deptno, ename;

GROUP BY 所指定的列并不是必须出现在SELECT 列表中

select ename, avg(sal) from emp group by deptno, ename;

按多个列分组

查询每个部门每个岗位的工资总和

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

练习：
查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。

SELECT
    d.DEPTNO 部门编号,
    d.DNAME 部门名称,
    COUNT(*) 部门人数,
    MAX(e.SAL) 最高工资,
    MIN(e.SAL) 最低工资,
    SUM(e.SAL) 工资总和,
    AVG(e.SAL) 平均工资
FROM
    emp e,
    dept d
WHERE
    e.DEPTNO = d.DEPTNO
GROUP BY
    e.DEPTNO, d.DNAME;

查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。

SELECT
    d.DEPTNO 部门编号,
    d.DNAME 部门名称,
    e.JOB 岗位名称,
    COUNT(*) 部门人数,
    MAX(e.SAL) 最高工资,
    MIN(e.SAL) 最低工资,
    SUM(e.SAL) 工资总和,
    AVG(e.SAL) 平均工资
FROM
    emp e,
    dept d
WHERE
    e.DEPTNO = d.DEPTNO
GROUP BY
    d.DEPTNO,d.DNAME, e.JOB;

查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。

SELECT
    COUNT(*) 管理人数,
    m.EMPNO 经理编号,
    m.ENAME 经理姓名
FROM
    emp w
LEFT OUTER JOIN emp m ON w.MGR = m.EMPNO
GROUP BY
    m.EMPNO,
    m.ENAME

分组后的筛选

使用WHERE子句中限制分组后失败，应该使用having
每个组内最高薪水大于3000时候输出

SELECT
    e.DEPTNO,
    MAX(e.sal)
FROM
    emp e
WHERE
    MAX(e.sal) > 3000
GROUP BY
    e.DEPTNO

image

Having

使用步骤：

记录已经分组.
使用过组函数.
与 HAVING 子句匹配的结果才输出

SELECT    column, group_function
FROM        table
[WHERE    condition]
[GROUP BY    group_by_expression]
[HAVING    group_condition]
[ORDER BY    column];

举个栗子
查询每个部门最高薪水大于2900的部门编号，最高工资

SELECT
    e.DEPTNO,
    MAX(e.sal)
FROM
    emp e
GROUP BY
    e.DEPTNO
HAVING
    MAX(e.SAL) > 2900

查询每个工种，工资总和，并且工作不是以 SALE开头的且每个工种的工资和大于5000时输出，并按照结果的工资总数进行升序排序

SELECT
    job 工种, 
    SUM(sal) 工资总和 
FROM
    emp
WHERE
    job NOT LIKE 'SALES%'
GROUP BY
    job
HAVING
    SUM(sal) > 5000
ORDER BY
    SUM(sal);

SELECT语句执行过程

1、通过FROM子句中找到需要查询的表；
2、通过WHERE子句进行非分组函数筛选判断；
3、通过GROUP BY子句完成分组操作；
4、通过HAVING子句完成组函数筛选判断；
5、通过SELECT子句选择显示的列或表达式及组函数；
6、通过ORDER BY子句进行排序操作。
举个栗子

SELECT
    DEPTNO,
    JOB,
    AVG(SAL)
FROM
    emp
WHERE
    JOB IN (
        'CLERK',
        'SALESMAN',
        'MANAGER'
    )
GROUP BY
    DEPTNO,
    JOB
HAVING
    AVG(SAL)> 1200
ORDER BY 3 DESC

查询部门人数大于2的部门编号，部门名称，部门人数。

SELECT
    e.DEPTNO 部门编号,
    d.DNAME 部门名称,
    COUNT(*) 部门人数
FROM
    emp e,
    dept d
WHERE
    e.DEPTNO = d.DEPTNO
GROUP BY
    e.DEPTNO
HAVING
    COUNT(*) > 2

查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。

SELECT
    e.DEPTNO 部门编号,
    d.DNAME 部门名称,
    COUNT(*) 部门人数,
    AVG(e.SAL) 部门平均工资
FROM
    emp e,
    dept d
WHERE
    e.DEPTNO = d.DEPTNO
GROUP BY
    e.DEPTNO
HAVING
    (COUNT(*) > 2)
AND (AVG(e.SAL) > 2000)
ORDER BY
    3

子查询

查询里还有查询

查询工资比Jones工资高的员工信息

SELECT
    *
FROM
    emp e
WHERE
    e.SAL > ( SELECT e.SAL from emp e WHERE e.ENAME = 'JONES');

查询工资最低的员工姓名

SELECT
    e.ENAME
FROM
    emp e
WHERE
    e.SAL = (SELECT MIN(e.sal) from emp e);

语法：

SELECT    select_list
FROM    table
WHERE    expr operator
         (SELECT    select_list
           FROM        table);

括号内的查询叫做子查询，也叫内部查询，先于主查询执行。
子查询的结果被主查询（外部查询）使用
expr operator包括比较运算符

单行运算符：>、=、>=、<、<>、<=

多行运算符： IN、ANY、ALL

子查询可以嵌于以下SQL子句中：

WHERE子句
HAVING子句
FROM子句

子查询类型

image

子查询要用括号括起来
将子查询放在比较运算符的右边
对于单行子查询要使用单行运算符
对于多行子查询要使用多行运算符

单行子查询（>、=、>=、<、<>、<=）

显示和工号7369从事相同工作并且工资大于7876的员工姓名和工作

SELECT e.ENAME, e.JOB
from emp e
WHERE
    e.JOB = (SELECT JOB FROM emp WHERE EMPNO = 7369) and 
    e.sal > (SELECT SAL FROM emp WHERE EMPNO = 7876);
```sql
- 查询部门最低工资比20部门最低工资高的部门编号及最低工资
```sql
SELECT e.DEPTNO 部门编号 ,MIN(e.SAL)
from emp e
GROUP BY e.DEPTNO
HAVING
MIN(e.SAL) > (SELECT MIN(sal) from emp WHERE DEPTNO = 20)

练习：
查询入职日期最早的员工姓名，入职日期

SELECT
  e.ename,
  e.hiredate
FROM
  emp e
WHERE
  HIREDATE = (SELECT min(hiredate) FROM emp);

查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称

SELECT
  e.ename,
  e.sal,
  d.dname
FROM
  emp e,
  dept d
WHERE
  e.deptno = d.deptno
AND e.sal > (
  SELECT
    sal
  FROM
    emp
  WHERE
    ename = 'SMITH'
)
AND d.loc = 'CHICAGO';

查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期

SELECT
  e.ename,
  e.hiredate
FROM
  emp e
WHERE
  e.hiredate < (
    SELECT
      min(hiredate)
    FROM
      emp
    WHERE
      deptno = 20
  )

多行子查询

多行子查询进行比较时，需要使用多行操作符，多行操作符包括:

IN 判断是否与子查询的任意一个返回值相同。

SELECT ENAME, SAL
FROM emp 
WHERE EMPNO in (SELECT m.mgr FROM emp m);

ANY
有一个满足就可以
查询是经理的员工姓名和工资

SELECT ENAME, SAL
FROM emp 
WHERE EMPNO  = ANY(SELECT m.mgr FROM emp m);

查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资

SELECT EMPNO, ENAME,JOB, SAL
FROM emp 
WHERE SAL 
>ANY(SELECT sal from emp WHERE DEPTNO = 10) AND DEPTNO <> 10;

ALL
每一个都要满足
查询部门编号不为20，且工资比20部门所有员工工资高的员工编号，姓名，职位，工资。

SELECT EMPNO, ENAME,JOB, SAL
FROM emp 
WHERE SAL 
>ALL(SELECT sal from emp WHERE DEPTNO = 20) AND DEPTNO <> 20;

查询部门编号不为10，且工资比10部门所有员工工资低的员工编号，姓名，职位，工资。

SELECT EMPNO, ENAME,JOB, SAL
FROM emp 
WHERE SAL 
< ALL(SELECT sal from emp WHERE DEPTNO = 10) AND DEPTNO <> 10;

子查询中的空值

查询不是经理的员工姓名

SELECT ename FROM emp WHERE EMPNO not in (SELECT mgr  from emp);

子查询返回的结果中含有空值
上面的SQL语句试图查找出没有下属的雇员，逻辑上，这个SQL语句应该会返回8条记录，但是却一条也没返回，why?
因为子查询的结果中有一条空值，这条空值导致主查询没有记录返回。这是因为所有的条件和空值比较结果都是空值。因此无论什么时候只要空值有可能成为子查询结果集合中的一部分，就不能使用NOT IN 运算符
正确的查询不是经理的员工姓名

SELECT
    ename
FROM
    emp
WHERE
    empno NOT IN (SELECT mgr FROM emp WHERE MGR is NOT NULL);

FROM 子句中使用子查询

查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资

SELECT e.ename 员工姓名, e.SAL 工资, e.DEPTNO 部门编号, a.avgsal 部门平均工资
FROM emp e, (SELECT DEPTNO, AVG(SAL) avgsal  from emp GROUP BY DEPTNO) a
WHERE
e.DEPTNO = a.DEPTNO AND e.SAL > a.avgsal;

数据库管理

新建数据库

CREATE DATABASE  [IF NOT EXISTS] 数据库名

示例

CREATE DATABASE IF NOT EXISTS MySQLDB;

数据类型

image

数值类型

image

日期类型

image

字符串类型

image

特殊字符序列

image

约束

重要，记下

image

非空约束（NOT NULL）

强制列不能为 NULL 值，约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
唯一约束（UNIQUE）

唯一约束可以保证记录的唯一性，即就是同一个表中，相同字段的值不会出现重复。
唯一约束的字段可以为空值（NULL）。
每一张数据表可以存在多个唯一约束字段。

主键约束（PRIMARY KEY）

数据库表要求表中的每一行记录都必须是唯一的，即在同一张表中不允许出现完全相同的两条记录。在设计数据库时，为了保证记录的“唯一性”，最为普遍、最为推荐的做法是为表定义一个主键（primary key）。数据库表中主键有以下两个特征：
表的主键可以由一个字段构成，也可以由多个字段构成（这种情况称为复合主键）。
数据库表中主键的值具有唯一性且不能取空值（NULL），当数据库表中的主键由多个字段构成时，每个字段的值不能取NULL值

CREATE TABLE classes (
    classes_id INT PRIMARY KEY,
    classes_name VARCHAR (10),
    classes_dept VARCHAR (10)
);

外键约束（FOREIGN KEY）

班级实体和班主任实体之间为一对一关系，班级实体和学生实体之间为一对多关系，学生实体和课程实体之间为多对多关系。
实体间的关系可以通过外键来表示。如果表A中的一个字段a对应于表B的主键b，则字段a称为表A的外键。此时存储在表A中字段a的值，同时这个字段值也是表B主键b的值。

CREATE TABLE teacher (
    teacher_id INT PRIMARY KEY,
    teacher_name VARCHAR (10),
    cid INT,
    FOREIGN KEY (cid) REFERENCES classes (classes_id)
);

练习：

学校想做一个选课系统，其中涉及到课程表，学生表，请分别创建这两个表，自己思考表中应有的列及数据类型。
学校有一个选课系统，其中包括如下关系模式：
系(系编号：主键，系名称: 唯一键，系主任：非空约束，系所在校去：默认为浑南区)
班级(班级编号：主键，班级名称：唯一键，所属系：外键)
创建学生表，包含如下属性:
学号定长字符型 10位主键
姓名变长字符型 20位非空
性别定长字符型 2位
出生日期日期型
所在班级

-- 学校想做一个选课系统，其中涉及到课程表，学生表，
-- 请分别创建这两个表，自己思考表中应有的列及数据类型。
-- 学校有一个选课系统，其中包括如下关系模式：
-- 系(系编号： 主键，系名称: 唯一键，系主任： 非空约束，系所在校去：默认为浑南区)
-- 班级(班级编号： 主键，班级名称： 唯一键，所属系： 外键)
-- 创建学生表，包含如下属性:
-- 学号 定长字符型 10位 主键
-- 姓名 变长字符型 20位 非空
-- 性别 定长字符型 2位
-- 出生日期 日期型
-- 所在班级
CREATE TABLE
IF NOT EXISTS xi (
    XINumber VARCHAR (20) NOT NULL PRIMARY KEY,
    XIName VARCHAR (20) NOT NULL UNIQUE,
    XIZhuren VARCHAR (20) NOT NULL,
    Xiaoqu enum ('浑南区')
);

CREATE TABLE
IF NOT EXISTS class (
    classid VARCHAR (20) NOT NULL PRIMARY KEY,
    cname VARCHAR (20) NOT NULL UNIQUE,
    xiname VARCHAR (10) NOT NULL,
    FOREIGN KEY (xiname) REFERENCES xi (xiname)
);

CREATE TABLE
IF NOT EXISTS student (
    xh VARCHAR (20) NOT NULL PRIMARY KEY,
    xm VARCHAR (20) NOT NULL,
    gender CHAR (2),
    birthday DATE,
    cname VARCHAR (20) NOT NULL UNIQUE,
    FOREIGN KEY (cname) REFERENCES class (cname)
);

删除数据库

DROP DATABASE 数据库名

复制表

CREATE TABLE 新表名  LIKE 源表

方法二

CREATE TABLE 新表名 SELECT - FROM 源表
CREATE TABLE emphaha SELECT  *   FROM emp

删除表

DROP TABLE 表名

表的重命名

RENAME TABLE 旧表名 TO 新表名

等效

ALTER TABLE person RENAME human;

创建与dept表相同表结构的表dtest，将dept表中部门编号在40之前的信息插入该表。

CREATE TABLE dtest SELECT * from dept WHERE DEPTNO < 40;

创建与emp表结构相同的表empl，并将其部门编号为前30号的员工信息复制到empl表。

CREATE TABLE empl SELECT * from emp WHERE DEPTNO < 30;

数据操作与事务控制

Data Manipulation Language ,简称DML，主要用来实现对数据库表中的数据进行操作。
数据操作语言主要包括如下几种：
增加行数据：使用INSERT语句实现
修改行数据：使用UPDATE语句实现
删除行数据：使用DELETE语句实现

插入数据

INSERT INTO    table [(column [, column...])]
VALUES        (value [, value...]);

采用这种语法一次只能追加一条记录；
column部分叫做列名列表，value部分叫做值列表，列名列表和值列表必须在个数及数据类型上保持一致；
列名列表部分可以省略，如果省略，默认包括该表的所有列，列的顺序为使用 desc 表名命令所查看的顺序；
列名列表部分也可以指定部分非空的列，注意值列表必须和列名列表对应；
字符和日期型数据必须要用单引号括起来

INSERT into dept VALUES(50, 'IT', '北京')

插入空值NULL

INSERT into dept VALUES(6
0, 'Test', null);

向部门表新增一个部门，部门编号为50，部门名称为HR，工作地点为SY。

 insert into dept values(50,"HR","SY");

向部门表新增一个部门，部门编号为60，部门名称为MARKET。

 insert into dept values(60,"MARKET","");

插入日期值

SYSDATE()

insert into emp values(8888,'haha','该溜子',null,SYSDATE(),4000,12,50  );

插入日期值

insert into emp values(8889,'李会长','小青年',null,'1888-01-01
',4000,12,50  );

向员工表中新增一个员工，员工编号为8888，姓名为BOB，岗位为CLERK，经理为号7788，入职日期为1985-03-03，薪资3000，奖金和部门为空。

insert into emp values(8899, 'BOB', 'clerk', 7788, '1985-03-03', 3000, null, null);

批量插入数据

INSERT INTO EMP(EMPNO, ENAME, JOB, SAL) 
VALUES 
('8881', '张三', '部门经理', 6000),
('8882', '李四', '职员', 3000),
('8883', '王五', '职员', 3500),
('8884' ,'赵六', '部门经理', 6500),
('8885', '高七', '职员', 2500),
('8886', '马八', '职员', 3100),
('8887', '钱九', '部门经理', 5000),
('8888', '孙十',  '职员', 2800);

通过子查询插入多行数据

INSERT子句和数据类型必须和子查询中列的数量和类型相匹配中列的数量
创建一个manager表

create table manager as select * from emp where 1=0;

插入数据，全部工作为salesman的数据

insert into manager select * from emp where job='SALESMAN';

使用CREATE TABLE emp_back as SELECT - FROM EMP WHERE 1=0，创建emp_back表,拷贝下来即可。

create table emp_back as select * from emp where 1=0;

把emp表1982年1月1日之前的员工信息复制到emp_back表中

insert into manager select * from emp where hiredate <'1982-01-01';

修改数据

UPDATE        table
SET        column = value [, column = value]
[WHERE         condition];

Mysql日期计算

DATE_ADD(date,INTERVAL expr unit) 返回一个日期加上指定的时间单元
DATE_SUB(date,INTERVAL expr unit) 返回一个日期减去指定的时间单元

SELECT DATEDIFF('2020-08-05','2020-08-02') AS 相差时间;  -- 3天

unit 可选字段

image

修改部门20的员工信息，把82年之后入职的员工入职日期向后调整10天

UPDATE emp
SET HIREDATE = DATE_ADD(HIREDATE,INTERVAL 10 DAY)
WHERE
    DEPTNO = 20
AND HIREDATE > '1982-01-01';

修改奖金为null的员工，奖金设置为0

update emp set comm=0 where comm is null;

修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500

UPDATE emp e,
 dept d
SET e.sal = e.sal + 500
WHERE
    e.deptno = d.deptno
AND d.loc IN ('NEW YORK', 'CHICAGO');

删除数据语法

DELETE FROM      table
[WHERE      condition];

DELETE FROM      empl
WHERE empl.EMPNO = 7902;

截断表语法

TRUNCATE TABLE table;

TRUNCATE和DELETE区别

TRUNCATE是DDL，只能删除表中所有记录，释放存储空间，使用ROLLBACK不可以回滚。
DELETE是DML，可以删除指定记录，不释放存储空间，使用ROLLBACK可以回滚。
删除经理编号为7566的员工记录

delete from emp_copy where mgr=7566;

删除工作在NEW YORK的员工记录

DELETE
FROM
    emp_copy
WHERE
    deptno IN (
        SELECT
            DEPTNO
        FROM
            dept
        WHERE
            loc = 'NEW YORK'
    )

删除工资大于所在部门平均工资的员工记录

DELETE FROM emp_copy WHERE empno in (select * from (SELECT
    a.empno
FROM
    emp_copy AS a,
    (
        SELECT
            deptno,
            AVG(sal) AS avgsal
        FROM
            emp_copy
        GROUP BY
            deptno
    ) as b
WHERE 
a.deptno = b.deptno
AND a.sal > b.avgsal) as query)

delete from emp22 where sal>(select avg(sal) from emp e where emp22.deptno=e.deptno);

Mysql 事物

在数据库中，事务由一组相关的DML或SELECT语句，加上一个TPL语句（COMMIT、ROLLBACK）或一个DDL语句(CREATE、ALTER、DROP、TRUNCATE等)或一个DCL（GRANT、REVOKE）语句。

事务特征可用四个字母的缩写表示：即ACID
原子性（Atomicity）
事务就像“原子”一样，不可被分割，组成事务的DML操作语句要么全成功，要么全失败，不可能出现部分成功部分失败的情况。
一致性（Consistency）
一旦事务完成，不管是成功的，还是失败的，整个系统处于数据一致的状态。
隔离性（Isolation）
一个事务的执行不会被另一个事务所干扰。比如两个人同时从一个账户从取钱，通过事务的隔离性确保账户余额的正确性。
持久性（Durability）
也称为永久性，指事务一旦提交，对数据的改变就是永久的，不可以再被回滚。

存储引擎

InnoDB存储引擎的特点:
支持外键（Foreign Key）
支持事务（Transaction）：如果某张表主要提供OLTP支持，需要执行大量的增、删、改操作（insert、delete、update语句），出于事务安全方面的考虑，InnoDB存储引擎是更好的选择。
最新版本的MySQL已经开始支持全文检索。
MyISAM存储引擎的特点：
MyISAM具有检查和修复表的大多数工具。
MyISAM表可以被压缩
MyISAM表最早支持全文索引
但MyISAM表不支持事务
但MyISAM表不支持外键（Foreign Key）。
如果需要执行大量的select语句，出于性能方面的考虑，MyISAM存储引擎是更好的选择。

事务演示

建立一个account表

image

CREATE TABLE `account` (
  `id` int(12) NOT NULL,
  `name` varchar(12) NOT NULL,
  `balance` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1、手动开启事务

start transaction;

2、执行一些操作
给张三账户转 50块钱

image

</article>

1人点赞

Fy20-Java9

作者：method
链接：https://www.jianshu.com/p/f536231b831c
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。


### 数据类型

![image](https://upload-images.jianshu.io/upload_images/13248401-87778ccdf8448365.png?imageMogr2/auto-orient/strip|imageView2/2/w/767/format/webp)

数值类型

![image](https://upload-images.jianshu.io/upload_images/13248401-b369a0e08e812f54.png?imageMogr2/auto-orient/strip|imageView2/2/w/796/format/webp)

日期类型

![image](https://upload-images.jianshu.io/upload_images/13248401-15c69f97309f7247.png?imageMogr2/auto-orient/strip|imageView2/2/w/796/format/webp)

字符串类型

![image](https://upload-images.jianshu.io/upload_images/13248401-e160bb27bfc064c1.png?imageMogr2/auto-orient/strip|imageView2/2/w/806/format/webp)

特殊字符序列

![image](https://upload-images.jianshu.io/upload_images/13248401-9e67b8a2504b2e55.png?imageMogr2/auto-orient/strip|imageView2/2/w/796/format/webp)

### 约束

重要，记下

![image](https://upload-images.jianshu.io/upload_images/13248401-714ab1ed1623714d.png?imageMogr2/auto-orient/strip|imageView2/2/w/808/format/webp)

#### 非空约束（NOT NULL）

强制列不能为 NULL 值，约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
唯一约束（UNIQUE）

*   唯一约束可以保证记录的唯一性，即就是同一个表中，相同字段的值不会出现重复。
*   唯一约束的字段可以为空值（NULL）。
*   每一张数据表可以存在多个唯一约束字段。

#### 主键约束（PRIMARY KEY）

数据库表要求表中的每一行记录都必须是唯一的，即在同一张表中不允许出现完全相同的两条记录。在设计数据库时，为了保证记录的“唯一性”，最为普遍、最为推荐的做法是为表定义一个主键（primary key）。数据库表中主键有以下两个特征：
表的主键可以由一个字段构成，也可以由多个字段构成（这种情况称为复合主键）。
数据库表中主键的值具有唯一性且不能取空值（NULL），当数据库表中的主键由多个字段构成时，每个字段的值不能取NULL值

CREATE TABLE classes (
classes_id INT PRIMARY KEY,
classes_name VARCHAR (10),
classes_dept VARCHAR (10)
);


#### 外键约束（FOREIGN KEY）

班级实体和班主任实体之间为一对一关系，班级实体和学生实体之间为一对多关系，学生实体和课程实体之间为多对多关系。
实体间的关系可以通过外键来表示。如果表A中的一个字段a对应于表B的主键b，则字段a称为表A的外键。此时存储在表A中字段a的值，同时这个字段值也是表B主键b的值。

CREATE TABLE teacher (
teacher_id INT PRIMARY KEY,
teacher_name VARCHAR (10),
cid INT,
FOREIGN KEY (cid) REFERENCES classes (classes_id)
);


练习：

> 学校想做一个选课系统，其中涉及到课程表，学生表，请分别创建这两个表，自己思考表中应有的列及数据类型。
> 学校有一个选课系统，其中包括如下关系模式：
> 系(系编号： 主键，系名称: 唯一键，系主任： 非空约束，系所在校去：默认为浑南区)
> 班级(班级编号： 主键，班级名称： 唯一键，所属系： 外键)
> 创建学生表，包含如下属性:
> 学号 定长字符型 10位 主键
> 姓名 变长字符型 20位 非空
> 性别 定长字符型 2位
> 出生日期 日期型
> 所在班级

-- 学校想做一个选课系统，其中涉及到课程表，学生表，
-- 请分别创建这两个表，自己思考表中应有的列及数据类型。
-- 学校有一个选课系统，其中包括如下关系模式：
-- 系(系编号：主键，系名称: 唯一键，系主任：非空约束，系所在校去：默认为浑南区)
-- 班级(班级编号：主键，班级名称：唯一键，所属系：外键)
-- 创建学生表，包含如下属性:
-- 学号定长字符型 10位主键
-- 姓名变长字符型 20位非空
-- 性别定长字符型 2位
-- 出生日期日期型
-- 所在班级
CREATE TABLE
IF NOT EXISTS xi (
XINumber VARCHAR (20) NOT NULL PRIMARY KEY,
XIName VARCHAR (20) NOT NULL UNIQUE,
XIZhuren VARCHAR (20) NOT NULL,
Xiaoqu enum ('浑南区')
);

CREATE TABLE
IF NOT EXISTS class (
classid VARCHAR (20) NOT NULL PRIMARY KEY,
cname VARCHAR (20) NOT NULL UNIQUE,
xiname VARCHAR (10) NOT NULL,
FOREIGN KEY (xiname) REFERENCES xi (xiname)
);

CREATE TABLE
IF NOT EXISTS student (
xh VARCHAR (20) NOT NULL PRIMARY KEY,
xm VARCHAR (20) NOT NULL,
gender CHAR (2),
birthday DATE,
cname VARCHAR (20) NOT NULL UNIQUE,
FOREIGN KEY (cname) REFERENCES class (cname)
);


### 删除数据库

DROP DATABASE 数据库名


### 复制表

CREATE TABLE 新表名 LIKE 源表


方法二

CREATE TABLE 新表名 SELECT - FROM 源表
CREATE TABLE emphaha SELECT * FROM emp


### 删除表

DROP TABLE 表名


#### 表的重命名

RENAME TABLE 旧表名 TO 新表名


等效

ALTER TABLE person RENAME human;


创建与dept表相同表结构的表dtest，将dept表中部门编号在40之前的信息插入该表。

CREATE TABLE dtest SELECT * from dept WHERE DEPTNO < 40;


创建与emp表结构相同的表empl，并将其部门编号为前30号的员工信息复制到empl表。

CREATE TABLE empl SELECT * from emp WHERE DEPTNO < 30;


### 数据操作与事务控制

Data Manipulation Language ,简称DML，主要用来实现对数据库表中的数据进行操作。
数据操作语言主要包括如下几种：
增加行数据：使用INSERT语句实现
修改行数据：使用UPDATE语句实现
删除行数据：使用DELETE语句实现

### 插入数据

INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);


*   采用这种语法一次只能追加一条记录；
*   column部分叫做列名列表，value部分叫做值列表，列名列表和值列表必须在个数及数据类型上保持一致；
*   列名列表部分可以省略，如果省略，默认包括该表的所有列，列的顺序为使用 desc 表名 命令所查看的顺序；
*   列名列表部分也可以指定部分非空的列，注意值列表必须和列名列表对应；
    字符和日期型数据必须要用单引号括起来

INSERT into dept VALUES(50, 'IT', '北京')


#### 插入空值NULL

INSERT into dept VALUES(6
0, 'Test', null);


向部门表新增一个部门，部门编号为50，部门名称为HR，工作地点为SY。

insert into dept values(50,"HR","SY");


向部门表新增一个部门，部门编号为60，部门名称为MARKET。

insert into dept values(60,"MARKET","");


#### 插入日期值

SYSDATE()

insert into emp values(8888,'haha','该溜子',null,SYSDATE(),4000,12,50 );


插入日期值

insert into emp values(8889,'李会长','小青年',null,'1888-01-01
',4000,12,50 );


向员工表中新增一个员工，员工编号为8888，姓名为BOB，岗位为CLERK，经理为号7788，入职日期为1985-03-03，薪资3000，奖金和部门为空。

insert into emp values(8899, 'BOB', 'clerk', 7788, '1985-03-03', 3000, null, null);


#### 批量插入数据

INSERT INTO EMP(EMPNO, ENAME, JOB, SAL)
VALUES
('8881', '张三', '部门经理', 6000),
('8882', '李四', '职员', 3000),
('8883', '王五', '职员', 3500),
('8884' ,'赵六', '部门经理', 6500),
('8885', '高七', '职员', 2500),
('8886', '马八', '职员', 3100),
('8887', '钱九', '部门经理', 5000),
('8888', '孙十', '职员', 2800);


通过子查询插入多行数据

*   INSERT子句和数据类型必须和子查询中列的数量和类型相匹配中列的数量
    创建一个manager表

create table manager as select * from emp where 1=0;


插入数据， 全部工作为salesman的数据

insert into manager select * from emp where job='SALESMAN';


使用CREATE TABLE emp_back as SELECT - FROM EMP WHERE 1=0，创建emp_back表,拷贝下来即可。

create table emp_back as select * from emp where 1=0;


把emp表1982年1月1日之前的员工信息复制到emp_back表中

insert into manager select * from emp where hiredate <'1982-01-01';


# 修改数据

UPDATE table
SET column = value [, column = value]
[WHERE condition];


#### Mysql日期计算

> DATE_ADD(date,INTERVAL expr unit) 返回一个日期加上指定的时间单元
> DATE_SUB(date,INTERVAL expr unit) 返回一个日期减去指定的时间单元

SELECT DATEDIFF('2020-08-05','2020-08-02') AS 相差时间; -- 3天


unit 可选字段

![image](https://upload-images.jianshu.io/upload_images/13248401-ba241cd3f8c5336c.png?imageMogr2/auto-orient/strip|imageView2/2/w/492/format/webp)

修改部门20的员工信息，把82年之后入职的员工入职日期向后调整10天

UPDATE emp
SET HIREDATE = DATE_ADD(HIREDATE,INTERVAL 10 DAY)
WHERE
DEPTNO = 20
AND HIREDATE > '1982-01-01';


修改奖金为null的员工，奖金设置为0

update emp set comm=0 where comm is null;


修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500

UPDATE emp e,
dept d
SET e.sal = e.sal + 500
WHERE
e.deptno = d.deptno
AND d.loc IN ('NEW YORK', 'CHICAGO');


### 删除数据语法

DELETE FROM table
[WHERE condition];

DELETE FROM empl
WHERE empl.EMPNO = 7902;


#### 截断表语法

TRUNCATE TABLE table;


#### TRUNCATE和DELETE区别

*   TRUNCATE是DDL，只能删除表中所有记录，释放存储空间，使用ROLLBACK不可以回滚。
*   DELETE是DML，可以删除指定记录，不释放存储空间，使用ROLLBACK可以回滚。
    删除经理编号为7566的员工记录

delete from emp_copy where mgr=7566;


删除工作在NEW YORK的员工记录

DELETE
FROM
emp_copy
WHERE
deptno IN (
SELECT
DEPTNO
FROM
dept
WHERE
loc = 'NEW YORK'
)


删除工资大于所在部门平均工资的员工记录

DELETE FROM emp_copy WHERE empno in (select * from (SELECT
a.empno
FROM
emp_copy AS a,
(
SELECT
deptno,
AVG(sal) AS avgsal
FROM
emp_copy
GROUP BY
deptno
) as b
WHERE
a.deptno = b.deptno
AND a.sal > b.avgsal) as query)

delete from emp22 where sal>(select avg(sal) from emp e where emp22.deptno=e.deptno);


# Mysql 事物

在数据库中，事务由一组相关的DML或SELECT语句，加上一个TPL语句（COMMIT、ROLLBACK）或一个DDL语句(CREATE、ALTER、DROP、TRUNCATE等)或一个DCL（GRANT、REVOKE）语句。

> 事务特征可用四个字母的缩写表示：即ACID
> 原子性（Atomicity）
> 事务就像“原子”一样，不可被分割，组成事务的DML操作语句要么全成功，要么全失败，不可能出现部分成功部分失败的情况。
> 一致性（Consistency）
> 一旦事务完成，不管是成功的，还是失败的，整个系统处于数据一致的状态。
> 隔离性（Isolation）
> 一个事务的执行不会被另一个事务所干扰。比如两个人同时从一个账户从取钱，通过事务的隔离性确保账户余额的正确性。
> 持久性（Durability）
> 也称为永久性，指事务一旦提交，对数据的改变就是永久的，不可以再被回滚。

#### 存储引擎

*   InnoDB存储引擎的特点:
    支持外键（Foreign Key）
    支持事务（Transaction）：如果某张表主要提供OLTP支持，需要执行大量的增、删、改操作（insert、delete、update语句），出于事务安全方面的考虑，InnoDB存储引擎是更好的选择。
    最新版本的MySQL已经开始支持全文检索。
*   MyISAM存储引擎的特点：
    MyISAM具有检查和修复表的大多数工具。
    MyISAM表可以被压缩
    MyISAM表最早支持全文索引
    但MyISAM表不支持事务
    但MyISAM表不支持外键（Foreign Key）。
    如果需要执行大量的select语句，出于性能方面的考虑，MyISAM存储引擎是更好的选择。

### 事务演示

建立一个account表

![image](https://upload-images.jianshu.io/upload_images/13248401-cb2a6e5a638cccbc.png?imageMogr2/auto-orient/strip|imageView2/2/w/198/format/webp)

CREATE TABLE account (
id int(12) NOT NULL,
name varchar(12) NOT NULL,
balance int(10) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


*   1、手动开启事务

start transaction;


*   2、执行一些操作
    给张三账户转 50块钱

![image](https://upload-images.jianshu.io/upload_images/13248401-b86ba9d1ed1df48e.png?imageMogr2/auto-orient/strip|imageView2/2/w/933/format/webp)

![image](https://upload-images.jianshu.io/upload_images/13248401-1e5590ad5bb89808.png?imageMogr2/auto-orient/strip|imageView2/2/w/881/format/webp)

Mysql（2）

高级查询

常用的分组函数

COUNT

MIN和MAX

SUM函数和AVG函数'

group by 语句

按多个列分组

分组后的筛选

Having

SELECT语句执行过程

子查询

单行运算符：>、=、>=、<、<>、<=

多行运算符： IN、ANY、ALL

子查询类型

单行子查询（>、=、>=、<、<>、<=）

多行子查询

子查询中的空值

FROM 子句中使用子查询

数据库管理

新建数据库

数据类型

约束

非空约束（NOT NULL）

主键约束（PRIMARY KEY）

外键约束（FOREIGN KEY）

删除数据库

复制表

删除表

表的重命名

数据操作与事务控制

插入数据

插入空值NULL

插入日期值

批量插入数据

修改数据

Mysql日期计算

删除数据语法

截断表语法

TRUNCATE和DELETE区别

Mysql 事物

存储引擎

事务演示