MySQL

1. 数据库概述

  • 数据库：英文单词DataBase，简称DB。按照一定格式存储数据的一些文件的组合。顾名思义是存储数据的仓库，实际上就是一堆文件，这些文件中存储了具有特定格式的数据

  • 数据库管理系统：Database Management System，简称DBMS。是用来管理数据库中数据的，数据库管理系统可以对数据库中数据进行增删改查

    常见的数据库：MySQL、SqlSever、Oracle等

  • SQL：结构化查询语言

    ​ 通过编写SQL语句，然后通过DBMS执行SQL语句，最终实现对数据库中的数据进行增删改查

    ​ SQL是一套标准，SQL在MySQL中可以使用，在SqlSever和Oracle中也可以使用

  • 三者关系：DBMS--执行-->SQL语句--操作-->DB

2. 安装MySQL

3. 卸载MySQL

4. 使用数据库

MySQL常用命令：

net start mysql #启动数据库
net start mysql #关闭数据库
mysql -uroot -p #登陆数据库
show databases #查看数据库列表
use databaseName #使用数据库

数据库当中最基本的单元是：表(table)

姓名 性别 年龄 ———————— 张三 男 21 李四 女 22 王五 男 52

数据库中是以表格的形式表示数据的，因为表比较直观

任何一张表都有行和列：
​ 行(row)：被称为数据/记录

​ 列(column)：被称为字段

每一个字段都有：字段名、数据类型、约束等属性

5. SQL语句的分类

  • DQL：数据查询语言（凡是带有select关键字的都是查询语句）
  • DML：数据操作语言（凡是对表中的数据进行增(insert)删(delete)改(update)的语句都是DML，操作的是表的数据）
  • DDL：数据定义语言（凡是带有create、drop、alter的都是DDL，操作的是表的结构，而不是数据）
  • TCL：事务控制语言（包括事务提交:commit、事务回滚:rollback）
  • DCL：数据控制语言（授权qrant、撤销权限revoke）

6. DQL

6.1 DQL前的准备

导入提前准备好的数据：(要先use database)

mysql> source D:\DownLoad\document\bjpowernode.sql

+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept                  |
| emp                   |
| salgrade              |
+-----------------------+
#dept：部门表
#emp：员工表
#salgrade：工资等级表
  • 查看表中的数据：

select * from dept;

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

select * from emp;

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

select * from salgrade;

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
  • 不看表的数据，只看表的结构

desc dept;

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int         | NO   | PRI | NULL    |       | #部门编号
| DNAME  | varchar(14) | YES  |     | NULL    |       | #部门名字 varchar就是Java中的String
| LOC    | varchar(13) | YES  |     | NULL    |       | #地理位置
+--------+-------------+------+-----+---------+-------+

desc emp

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int         | NO   | PRI | NULL    |       | #员工编号
| ENAME    | varchar(10) | YES  |     | NULL    |       | #员工姓名
| JOB      | varchar(9)  | YES  |     | NULL    |       | #工作岗位
| MGR      | int         | YES  |     | NULL    |       | #上级编号
| HIREDATE | date        | YES  |     | NULL    |       | #入职日期
| SAL      | double(7,2) | YES  |     | NULL    |       | #工资
| COMM     | double(7,2) | YES  |     | NULL    |       | #补助
| DEPTNO   | int         | YES  |     | NULL    |       | #部门编号
+----------+-------------+------+-----+---------+-------+

desc salgrade

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| GRADE | int  | YES  |     | NULL    |       | #工资等级
| LOSAL | int  | YES  |     | NULL    |       | #最低工资
| HISAL | int  | YES  |     | NULL    |       | #最高工资
+-------+------+------+-----+---------+-------+
  • 常用查询：
mysql> select version(); #查询当前数据库版本号
mysql> select database(); #查看当前使用的数据库
#注意：MySQL不见";"不执行 ，"\c"用来终止一条SQL语句的输入 SQL语句不区分大小写

6.2 简单查询

  • 查询一个字段？

    select 字段名 from 表名;

    （select和from是关键字，字段名和表名是标识符）

    eg：查询部门名字

    mysql> select dname from dept;
+------------+
| dname      |
+------------+
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+

  • 查询两个或者多个字段

    使用逗号隔开

    eg：查询部门编号和部门名

    mysql> select deptno,dname from dept;
+--------+------------+
| deptno | dname      |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+

  • 查询所有字段

    • 把每个字段都写上，用逗号隔开

    • 用 * 代替所有字段（*会先转换为字段，所以效率较低，且可读性差，在Java中不要使用）

      mysql> select deptno,dname,loc from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

  • 给查询的列起别名

    用 "as"关键字（as关键字可省略）

    mysql> select deptno,dname as deptname from dept; #select deptno,dname deptname from dept;
+--------+------------+
| deptno | deptname   |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+

    注意：只是将显示的查询结果列名显示为deptname，原来列名还是叫dname

    如果所起的别名有空格或者是中文，需要用单引号或双引号将其括起来，否则会报错（在所有的数据库中，字符串统一用单引号括起来，单引号是标准，双引号在Oracle数据库中用不了，但是在MySQL中可以使用）

    mysql> select deptno,dname 'dept name' from dept;
+--------+------------+
| deptno | dept name  |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+

  • 列参与数学运算

    mysql> select ename,sal*12 from emp;
+--------+----------+
| ename  | sal*12   |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

    字段可以使用数学表达式

    mysql> select ename,sal*12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.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 |
+--------+----------+

6.3 条件查询

不是将表中所有数据全部查询，是查询出来符合的条件

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

  • 都有哪些条件呢？

    • "=" 等于

      查询薪资等于800的员工姓名和编号

      select empno,ename from emp where sal = 800;

    • "<>"或"!=" 不等于

      查询薪资不等于800的员工姓名和编号

      select empno,ename from emp where sal != 800

      select empno,ename from emp where sal <> 800

    • "<" 小于

    • "<=" 小于等于

    • ">" 大于

    • ">=" 大于等于

    • "between ... and ..." 两个值之间，等同于">= and <=" 必须遵循左小右大

      查询薪资处于1000-3000之间的员工姓名和编号

      mysql> select empno,ename from emp where sal between 1000 and 3000;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7566 | JONES  |
|  7654 | MARTIN |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7844 | TURNER |
|  7876 | ADAMS  |
|  7902 | FORD   |
|  7934 | MILLER |
+-------+--------+

    • "is null" / "is not null"

      查询哪些员工的津贴、补助为null

      在数据库中null不能用"="进行衡量，它不是一个值，代表什么也没有，必须使用is/is not

      mysql> select empno,ename from emp where comm is null;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7369 | SMITH  |
|  7566 | JONES  |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7839 | KING   |
|  7876 | ADAMS  |
|  7900 | JAMES  |
|  7902 | FORD   |
|  7934 | MILLER |
+-------+--------+

    • "and" 并且 连接两个条件

    • "or" 或者

      查询工资岗位是MANAGER和SALESMAN的员工

      mysql> select empno,ename,job from emp where job='MANAGER' or job= 'SALESMAN';
+-------+--------+----------+
| empno | ename  | job      |
+-------+--------+----------+
|  7499 | ALLEN  | SALESMAN |
|  7521 | WARD   | SALESMAN |
|  7566 | JONES  | MANAGER  |
|  7654 | MARTIN | SALESMAN |
|  7698 | BLAKE  | MANAGER  |
|  7782 | CLARK  | MANAGER  |
|  7844 | TURNER | SALESMAN |
+-------+--------+----------+

      "and"和"or"同时出现的优先级问题：

      查询工资大于2500，并且部门编号为10或20的员工

      mysql> select empno,ename from emp where sal >2500 and deptno = 10 or deptno=20;
+-------+-------+
| empno | ename |
+-------+-------+
|  7369 | SMITH |
|  7566 | JONES |
|  7788 | SCOTT |
|  7839 | KING  |
|  7876 | ADAMS |
|  7902 | FORD  |
+-------+-------+

      and的优先级比or高，以上语句就会先执行and，再执行or

      则此时表示的是：查询工资大于2500并且部门编号为10 或者编号部门为20的员工

      mysql> select empno,ename from emp where sal >2500 and (deptno = 10 or deptno=20);
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7788 | SCOTT |
|  7839 | KING  |
|  7902 | FORD  |
+-------+-------+

    • "in" 包含，相当于多个or("not in" 不包含)

      mysql> select empno,ename,job from emp where job in ('MANAGER','SALESMAN');
+-------+--------+----------+
| empno | ename  | job      |
+-------+--------+----------+
|  7499 | ALLEN  | SALESMAN |
|  7521 | WARD   | SALESMAN |
|  7566 | JONES  | MANAGER  |
|  7654 | MARTIN | SALESMAN |
|  7698 | BLAKE  | MANAGER  |
|  7782 | CLARK  | MANAGER  |
|  7844 | TURNER | SALESMAN |
+-------+--------+----------+

      in不是一个区间，需要具体的值

    • "not" 取非，用在 is 和 in 中

6.4 模糊查询

  • "like"： 模糊查询 支持%或下划线匹配

    • "%"：匹配任意多个字符

      找出名字中含有"o"的

      mysql> select ename from emp where ename like '%o%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD  |
+-------+

      找出名字以"t"结尾的

      mysql> select ename from emp where ename like '%t';
+-------+
| ename |
+-------+
| SCOTT |
+-------+

      找出名字以"k"开始的

      mysql> select ename from emp where ename like 'k%';
+-------+
| ename |
+-------+
| KING  |
+-------+

    • 下划线：一个下划线只匹配一个字符（任意一个）

      找出名字第二个字母是"a"的

      mysql> select ename from emp where ename like '_a%';
+--------+
| ename  |
+--------+
| WARD   |
| MARTIN |
| JAMES  |
+--------+

      找出名字第三个字母是"r"的

      mysql> select ename from emp where ename like '__r%';
+--------+
| ename  |
+--------+
| WARD   |
| MARTIN |
| TURNER |
| FORD   |
+--------+

      找出名字中有下划线的

      mysql> select ename from emp where ename like '%\_%';

      需要用到 ‘\’ 转义

6.5 排序

  • 查询所有员工的薪资，排序

    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 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| KING   | 5000.00 |
+--------+---------+

  • 降序查询所有员工的薪资，排序

    mysql> select ename,sal from emp order by sal desc;#desc 降序 asc 升序
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+

  • 两个\多个字段排序？
    查询所有员工的薪资，要求按照薪资升序，如果薪资一样再按照名字升序排列

    mysql> select ename,sal from emp order by sal asc,ename asc;
#sal起主导，只有sal相等才会启用ename排序
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| MARTIN | 1250.00 |
| WARD   | 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.00 sec)

  • 了解：根据字段的位置排序

    mysql> select ename,sal from emp order by 2;#按照查询结果的第二列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 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| KING   | 5000.00 |
+--------+---------+

6.6 综合查询案例

  • 找出工资在1250到3000之间的员工信息，要求按照薪资降序排列

    mysql> select ename,sal from emp where sal between 1250 and 3000 order by sal desc;
#排序总是在最后执行
+--------+---------+
| ename  | sal     |
+--------+---------+
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
+--------+---------+

6.7 数据处理函数(单行处理函数)

数据处理函数又称为单行处理函数

单行处理函数特点：一个输入对应一个输出

函数名 功能
lower 转换大写为小写
upper 转换小写为大写
substr 取子串（substr(被裁取的字符串,起始下标,截取的长度)）
length 取长度
trim 去空格
str_to_date 将字符串转换为日期
date_format 格式化日期
format 设置千分位
round 四舍五入
rand() 生成随机数
ifnull 可以将null转换成一个具体值
  • lower
mysql> select lower(ename) as ename from emp;
+--------+
| ename  |
+--------+
| smith  |
| allen  |
| ward   |
| jones  |
| martin |
| blake  |
| clark  |
| scott  |
| king   |
| turner |
| adams  |
| james  |
| ford   |
| miller |
+--------+
  • upper
mysql> select upper(name) as name from student;
+----------+
| name     |
+----------+
| MARK     |
| OQPEJD   |
| ODSDSSSS |
+----------+

  • substr

    mysql> select substr(ename,1,1)as ename from emp;
+-------+
| ename |
+-------+
| S     |
| A     |
| W     |
| J     |
| M     |
| B     |
| C     |
| S     |
| K     |
| T     |
| A     |
| J     |
| F     |
| M     |
+-------+

    找出员工名字首字母为A的员工信息？

    ​ 方式一：模糊查询

    ​ select ename from emp where ename like 'A%';

    ​ 方式二：使用截取字符串函数

    mysql> select ename from emp where substr(ename,1,1)='A';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+

    员工名字首字母大写

    mysql> select concat(upper(substr(name,1,1)),substr(name,2,length(name)-1)) as name from student;
+----------+
| name     |
+----------+
| Mark     |
| Oqpejd   |
| Odsdssss |
+----------+

  • length 字符串长度

  • concat 字符串拼接

    mysql> select concat(ename,' ',sal) as 'name sal' from emp;
+----------------+
| name 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 |
+----------------+

  • trim 去空格

    mysql> select * from emp where ename=' KING';
Empty set (0.00 sec)

mysql> select * from emp where ename=trim(' KING');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.01 sec)

  • str_to_date

  • date_format

  • format

  • round

    mysql> select 'abc' from emp;
+-----+
| abc |
+-----+
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
+-----+

    mysql> select 1000 as num from emp;
    +------+
    | num |
    +------+
    | 1000 |
    | 1000 |
    | 1000 |
    | 1000 |
    | 1000 |
    | 1000 |
    | 1000 |
    | 1000 |
    | 1000 |
    | 1000 |
    | 1000 |
    | 1000 |
    | 1000 |
    | 1000 |
    +------+

    
结论：slecet后面可以跟某个表的字段名（可以等同看作变量名），也可以跟字面量/字面值（数据）

```mysql
mysql> select round(1234.567,0) as num from emp;#四舍五入保留到整数位
+------+
| num  |
+------+
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
| 1235 |
+------+

mysql> select round(1234.567,1) as num from emp;#四舍五入保留以为小数
+--------+
| num    |
+--------+
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
+--------+

mysql> select round(1234.567,-1) as num from emp;#保留到十位
+------+
| num  |
+------+
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
| 1230 |
+------+

  • rand()

    mysql> select rand() as rand from emp;
+---------------------+
| rand                |
+---------------------+
|  0.9571965625110982 |
| 0.12555108044813487 |
|  0.7561657454410249 |
|  0.4041755165943648 |
|  0.7523803773823943 |
|  0.5493753678625221 |
|  0.4897357378990722 |
|  0.8005526166414401 |
|  0.5335559002436286 |
| 0.26612168202746816 |
|  0.7299407401400998 |
|  0.8513386853517394 |
| 0.06687123707204241 |
|   0.780340160038639 |
+---------------------+

mysql> select round(rand()*100,0) as rand from emp;#100以内随机数
+------+
| rand |
+------+
|   70 |
|   16 |
|   72 |
|   10 |
|   35 |
|   44 |
|   14 |
|   38 |
|   47 |
|   22 |
|   69 |
|   80 |
|   93 |
|   26 |
+------+

  • ifnull 空处理函数

    在所有数据库中，只有有null参与的数学运算结果都是null

    mysql> select ename,sal + comm as salcomm from emp;
+--------+---------+
| ename  | salcomm |
+--------+---------+
| SMITH  |    NULL |
| ALLEN  | 1900.00 |
| WARD   | 1750.00 |
| JONES  |    NULL |
| MARTIN | 2650.00 |
| BLAKE  |    NULL |
| CLARK  |    NULL |
| SCOTT  |    NULL |
| KING   |    NULL |
| TURNER | 1500.00 |
| ADAMS  |    NULL |
| JAMES  |    NULL |
| FORD   |    NULL |
| MILLER |    NULL |
+--------+---------+

    计算员工的年薪：

    ​ 年薪=（月薪+月补助）* 12

    mysql> select ename,(sal + ifnull(comm,0))*12 as salcomm from emp;
+--------+----------+
| ename  | salcomm  |
+--------+----------+
| 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 |
+--------+----------+

  • case..when..then..when..then..else..end

    当员工的工作岗位是MANGER时，工资上调10%，当工作岗位是SALESMAN时，工资上调50%（不修改数据库，只显示查询结果为题目要求）

    mysql> select ename,job,sal from emp;
+--------+-----------+---------+
| ename  | job       | sal     |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 |
| WARD   | SALESMAN  | 1250.00 |
| JONES  | MANAGER   | 2975.00 |
| MARTIN | SALESMAN  | 1250.00 |
| BLAKE  | MANAGER   | 2850.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| TURNER | SALESMAN  | 1500.00 |
| ADAMS  | CLERK     | 1100.00 |
| JAMES  | CLERK     |  950.00 |
| FORD   | ANALYST   | 3000.00 |
| MILLER | CLERK     | 1300.00 |
+--------+-----------+---------+

mysql> select ename,job,(case job when 'MANAGER' then sal*1 when 'SALEMAN' then sal*5 else sal end) as newsal from emp;
+--------+-----------+---------+
| ename  | job       | newsal  |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 |
| WARD   | SALESMAN  | 1250.00 |
| JONES  | MANAGER   | 2975.00 |
| MARTIN | SALESMAN  | 1250.00 |
| BLAKE  | MANAGER   | 2850.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| TURNER | SALESMAN  | 1500.00 |
| ADAMS  | CLERK     | 1100.00 |
| JAMES  | CLERK     |  950.00 |
| FORD   | ANALYST   | 3000.00 |
| MILLER | CLERK     | 1300.00 |
+--------+-----------+---------+

6.8 聚合函数、分组查询、分页查询

  • 聚合函数：将一列数据作为一个整体，进行纵向的计算

    • count：计算个数

      mysql> select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
|           14 |
+--------------+

mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+

    • max：计算最大值

      mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+

    • min：计算最小值

      mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+

    • sum：计算和

      mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+

    • avg：计算平均值

      mysql> select round(avg(sal),0) as avg  from emp;
+------+
| avg  |
+------+
| 2073 |
+------+

    注意：聚合函数的计算会排除null值

    ​ 解决方案：

    ​ 选择不包含空的列进行计算

    ​ ifnull函数

  • 分组查询

    • 语法：group by 分组字段
    • 注意：
      • 分组之后查询的字段：分组字段或者聚合函数
    mysql> select job from emp group by job;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

    mysql> select job,round(avg(sal),1) as avg from emp group by job;
+-----------+--------+
| job       | avg    |
+-----------+--------+
| CLERK     | 1037.5 |
| SALESMAN  |   1400 |
| MANAGER   | 2758.3 |
| ANALYST   |   3000 |
| PRESIDENT |   5000 |
+-----------+--------+

    mysql> select job,round(avg(sal),1) as avg,count(ename) as counts from emp group by job;
+-----------+--------+--------+
| job       | avg    | counts |
+-----------+--------+--------+
| CLERK     | 1037.5 |      4 |
| SALESMAN  |   1400 |      4 |
| MANAGER   | 2758.3 |      3 |
| ANALYST   |   3000 |      2 |
| PRESIDENT |   5000 |      1 |
+-----------+--------+--------+

    mysql> select job,round(avg(sal),1) as avg,count(ename) as counts from emp where sal > 800 group by job having counts>2;
+----------+--------+--------+
| job      | avg    | counts |
+----------+--------+--------+
| SALESMAN |   1400 |      4 |
| MANAGER  | 2758.3 |      3 |
| CLERK    | 1116.7 |      3 |
+----------+--------+--------+

    where和having的区别：

    • where在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
    • where后不可以是聚合函数，而having可以进行聚合函数的判断

  • 分页查询

    • 语法：limit开始的索引，每页查询的条数

      #每页显示四条记录 
mysql> select * from emp limit 0,4; #开始的索引为 4*(n-1)
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB      | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7369 | SMITH | CLERK    | 7902 | 1980-12-17 |  800.00 |   NULL |     20 |
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
|  7521 | WARD  | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 |     30 |
|  7566 | JONES | MANAGER  | 7839 | 1981-04-02 | 2975.00 |   NULL |     20 |
+-------+-------+----------+------+------------+---------+--------+--------+
#第一页

mysql> select * from emp limit 4,4;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST  | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
+-------+--------+----------+------+------------+---------+---------+--------+
#第二页

mysql> select * from emp limit 8,4;
+-------+--------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 | 0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
+-------+--------+-----------+------+------------+---------+------+--------+
#第三页

mysql> select * from emp limit 12,4;
+-------+--------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+---------+------+------------+---------+------+--------+
|  7902 | FORD   | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7934 | MILLER | CLERK   | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
+-------+--------+---------+------+------------+---------+------+--------+
#第四页

      开始的索引=（当前的页码 - 1） * 每页显示的条数

      limit分页操作是一个“方言”，只能在MySQL中使用

6.9 多表查询

select * from emp,dept;

查询结果称为笛卡尔积：有两个集合A、B，取两个集合的所有组成情况

A表和B表的所有组合情况结果数据很多，需要消除无用的数据

多表查询的分类：

  • 内连接查询

    • 隐式内连接：使用where条件消除无用的数据

      mysql> select ename,job,sal,dname from emp,dept where emp.`deptno`=dept.`deptno`;
+--------+-----------+---------+------------+
| ename  | job       | sal     | dname      |
+--------+-----------+---------+------------+
| CLARK  | MANAGER   | 2450.00 | ACCOUNTING |
| KING   | PRESIDENT | 5000.00 | ACCOUNTING |
| MILLER | CLERK     | 1300.00 | ACCOUNTING |
| SMITH  | CLERK     |  800.00 | RESEARCH   |
| JONES  | MANAGER   | 2975.00 | RESEARCH   |
| SCOTT  | ANALYST   | 3000.00 | RESEARCH   |
| ADAMS  | CLERK     | 1100.00 | RESEARCH   |
| FORD   | ANALYST   | 3000.00 | RESEARCH   |
| ALLEN  | SALESMAN  | 1600.00 | SALES      |
| WARD   | SALESMAN  | 1250.00 | SALES      |
| MARTIN | SALESMAN  | 1250.00 | SALES      |
| BLAKE  | MANAGER   | 2850.00 | SALES      |
| TURNER | SALESMAN  | 1500.00 | SALES      |
| JAMES  | CLERK     |  950.00 | SALES      |
+--------+-----------+---------+------------+

    • 显示内连接

      语法：

      select 字段列表 from 表名1 inner join 表名2 on emp.`deptno`=dept.`deptno`;

      mysql> select ename,job,sal,dname from emp inner join dept on emp.`deptno`=dept.`deptno`;
+--------+-----------+---------+------------+
| ename  | job       | sal     | dname      |
+--------+-----------+---------+------------+
| CLARK  | MANAGER   | 2450.00 | ACCOUNTING |
| KING   | PRESIDENT | 5000.00 | ACCOUNTING |
| MILLER | CLERK     | 1300.00 | ACCOUNTING |
| SMITH  | CLERK     |  800.00 | RESEARCH   |
| JONES  | MANAGER   | 2975.00 | RESEARCH   |
| SCOTT  | ANALYST   | 3000.00 | RESEARCH   |
| ADAMS  | CLERK     | 1100.00 | RESEARCH   |
| FORD   | ANALYST   | 3000.00 | RESEARCH   |
| ALLEN  | SALESMAN  | 1600.00 | SALES      |
| WARD   | SALESMAN  | 1250.00 | SALES      |
| MARTIN | SALESMAN  | 1250.00 | SALES      |
| BLAKE  | MANAGER   | 2850.00 | SALES      |
| TURNER | SALESMAN  | 1500.00 | SALES      |
| JAMES  | CLERK     |  950.00 | SALES      |
+--------+-----------+---------+------------+

    • 内连接注意事项：

      • 从哪些表中查询数据
      • 查询条件是什么（判断有效数据）
      • 查询哪些字段

  • 外连接查询

    • 左外连接：查询的是左表所有数据以及其交集部分(使用左外居多)

    • 语法：select 字段列表 from 左表 left [outer] join 右表 on 条件

      #查询所有员工信息，如果有部门，则查询部门名称，若没有部门，则不显示部门名称

select t1.*,t2.`dname` from emp t1,dept t2 where t1.`deptno`=t2.`deptno`;
#没有部门的行就会被排除掉

select t1.*,t2.`dname` from emp t1 left join dept t2 on t1.`deptno`=t2.`deptno`;
#所有数据都显示

    • 右外连接:查询的是右表所有数据以及其交集部分

    • 语法：select 字段列表 from 左表 right [outer] join 右表 on 条件

      select t1.*,t2.`dname` from emp t1 right join dept t2 on t1.`deptno`=t2.`deptno`;
#没有部门的就会被排除

select * from dept t2 right join emp t1 on t1.`deptno`=t2.`deptno`;
#所有数据都显示

  • 子查询

    • 概念：查询中嵌套查询，称嵌套查询为子查询

      #查询工资最高的工资
#1..查询最高的工资是多少
#2..查询员工信息且工资等于最高工资的

mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+

mysql> select ename,job,sal from emp where sal=5000;
+-------+-----------+---------+
| ename | job       | sal     |
+-------+-----------+---------+
| KING  | PRESIDENT | 5000.00 |
+-------+-----------+---------+

mysql> select ename,job,sal from emp where sal=(select max(sal) from emp);
+-------+-----------+---------+
| ename | job       | sal     |
+-------+-----------+---------+
| KING  | PRESIDENT | 5000.00 |
+-------+-----------+---------+

    • 子查询的不同情况

      • 子查询的结果是单行单列的

        • 子查询可以作为条件，使用运算符去判断。运算符：< > = <= >=

          #查询员工工资小于平均工资的人
mysql> select ename, job,sal from emp where sal<(select avg(sal) from emp);
+--------+----------+---------+
| ename  | job      | sal     |
+--------+----------+---------+
| SMITH  | CLERK    |  800.00 |
| ALLEN  | SALESMAN | 1600.00 |
| WARD   | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
| TURNER | SALESMAN | 1500.00 |
| ADAMS  | CLERK    | 1100.00 |
| JAMES  | CLERK    |  950.00 |
| MILLER | CLERK    | 1300.00 |
+--------+----------+---------+

      • 子查询的结果是多行单列的

        • 子查询可以作为集合去判断
        #查询部门为SALES和ACCOUNTING的员工信息
mysql> select deptno from dept where dname='sales' or dname='accounting';
+--------+
| deptno |
+--------+
|     10 |
|     30 |
+--------+

mysql> select ename,job,sal from emp where deptno=30 or deptno=10;
+--------+-----------+---------+
| ename  | job       | sal     |
+--------+-----------+---------+
| CLARK  | MANAGER   | 2450.00 |
| KING   | PRESIDENT | 5000.00 |
| MILLER | CLERK     | 1300.00 |
| ALLEN  | SALESMAN  | 1600.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| BLAKE  | MANAGER   | 2850.00 |
| TURNER | SALESMAN  | 1500.00 |
| JAMES  | CLERK     |  950.00 |
+--------+-----------+---------+

#多表查询
mysql> select ename,job,sal from emp where deptno in(select deptno from dept where dname='sales' or dname='accounting');
+--------+-----------+---------+
| ename  | job       | sal     |
+--------+-----------+---------+
| CLARK  | MANAGER   | 2450.00 |
| KING   | PRESIDENT | 5000.00 |
| MILLER | CLERK     | 1300.00 |
| ALLEN  | SALESMAN  | 1600.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| BLAKE  | MANAGER   | 2850.00 |
| TURNER | SALESMAN  | 1500.00 |
| JAMES  | CLERK     |  950.00 |
+--------+-----------+---------+

      • 子查询的结果是多行多列的

      • 子查询可以作为一个虚拟表进行表的查询

        #查询员工入职日期是1981-05-01之前的员工信息和部门信息
mysql> select ename,job,sal,deptno from emp where hiredate < '1981-05-01';
+-------+----------+---------+--------+
| ename | job      | sal     | deptno |
+-------+----------+---------+--------+
| SMITH | CLERK    |  800.00 |     20 |
| ALLEN | SALESMAN | 1600.00 |     30 |
| WARD  | SALESMAN | 1250.00 |     30 |
| JONES | MANAGER  | 2975.00 |     20 |
+-------+----------+---------+--------+

mysql> select *from dept t1,(select ename,job,sal,deptno from emp where hiredate < '1981-05-01') t2 where t1.deptno=t2.deptno;
+--------+----------+---------+-------+----------+---------+--------+
| DEPTNO | DNAME    | LOC     | ename | job      | sal     | deptno |
+--------+----------+---------+-------+----------+---------+--------+
|     20 | RESEARCH | DALLAS  | SMITH | CLERK    |  800.00 |     20 |
|     30 | SALES    | CHICAGO | ALLEN | SALESMAN | 1600.00 |     30 |
|     30 | SALES    | CHICAGO | WARD  | SALESMAN | 1250.00 |     30 |
|     20 | RESEARCH | DALLAS  | JONES | MANAGER  | 2975.00 |     20 |
+--------+----------+---------+-------+----------+---------+--------+

#也可以用普通内连接做，更好理解
select * from emp t1,dept,t2 where t1.`deptno`=t2.`deptno` and t1.`hiredate`<'1981-05-01';

7. DDL

7.1 约束

  • 概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性

  • 分类：

    • 主键约束：primary key

      • 含义：非空且唯一

      • 一张表只能有一个字符按为主键

      • 主键就是表中记录的唯一标识

      • 在创建表时添加主键约束

        mysql> create table stu(
    ->  id int primary key,
    ->  name varchar(20)
    -> );
    
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

      • 创建表完成后添加主键

        mysql> alter table stu drop primary key;#删除主键需要drop
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> alter table stu modify id int primary key;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

      • 自增

        mysql> alter table stu modify id int primary key auto_increment;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

    • 非空约束：not null 某一列的值不能为NULL

      • 在创建表时添加约束

        mysql> create table stu(
    ->  id int,
    ->  name varchar(20) not null
    -> );
    
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

#删除约束
mysql> alter table stu modify name varchar(20);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

      • 创建表完成后添加非空约束

        mysql> ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| NAME  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

    • 唯一约束：unique 某一列的值不能重复

      • 创建表时，添加唯一约束

        mysql> drop table stu;

mysql> create table stu(
    -> id int,
    -> phone_number varchar(11) unique
    -> );

mysql> insert into stu values(1,13912345678);
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu values(2,13912345678);
ERROR 1062 (23000): Duplicate entry '13912345678' for key 'stu.phone_number'

mysql> insert into stu values(2,13112345678),(3,13212345678);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from stu;
+------+--------------+
| id   | phone_number |
+------+--------------+
|    1 | 13912345678  |
|    2 | 13112345678  |
|    3 | 13212345678  |
+------+--------------+

mysql> desc stu;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int         | YES  |     | NULL    |       |
| phone_number | varchar(11) | YES  | UNI | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

      • 创建表完成后添加唯一约束

        mysql> alter table stu modify phone_number varchar(11);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int         | YES  |     | NULL    |       |
| phone_number | varchar(11) | YES  | UNI | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

    • 外键约束：foregin key 让表与表产生关系，从而保证数据的正确性

      外键的值可以为null，但不可以是关联外键中不存在的值

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

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
#将员工的部门用DEMTNO表示，部门信息放在dept表中与emp表中的DEMTNO关联，减少数据冗余

      • 语法：

        create table 表名(

        ​ ...

        ​ 外键列

        ​ constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)

        );

        mysql> create table department(
    ->  id int primary key auto_increment,
    ->  dep_name varchar(20),
    ->  dep_locationion varchar(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc department;
+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| id              | int         | NO   | PRI | NULL    | auto_increment |
| dep_name        | varchar(20) | YES  |     | NULL    |                |
| dep_locationion | varchar(20) | YES  |     | NULL    |                |
+-----------------+-------------+------+-----+---------+----------------+

mysql> create table emp(
    ->  id int primary key auto_increment,
    ->  age int,
    ->  dep_id int,
    ->  constraint emp_dept_fk foreign key(dep_id) references department(id)
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> desc emp;
+--------+------+------+-----+---------+----------------+
| Field  | Type | Null | Key | Default | Extra          |
+--------+------+------+-----+---------+----------------+
| id     | int  | NO   | PRI | NULL    | auto_increment |
| age    | int  | YES  |     | NULL    |                |
| dep_id | int  | YES  | MUL | NULL    |                |
+--------+------+------+-----+---------+----------------+

      • 删除外键

        mysql> alter table emp drop foreign key emp_dept_fk;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

      • 创建表之后添加外键

        mysql> alter table emp add constraint emp_dept_fk foreign key(dep_id) references department(id);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

        mysql> use bjpowernode;
Database changed

mysql> alter table emp add constraint emp_dept_fk foreign key(deptno) references dept(deptno);
Query OK, 14 rows affected (0.16 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int         | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int         | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| SAL      | double(7,2) | YES  |     | NULL    |       |
| COMM     | double(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int         | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+

mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int         | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+*

      • 级联操作：

        • 更改外键时同时更改关联外键

          • 添加外键时，设置级联更新 on update cascade
          • alter table emp add constraint emp_dept_fk foreign key(dep_id) references department(id) on update cascade;

          此时就可以修改外键值，两边会同时修改

        • 级联删除：删除所有级联值相同的行,关联外键的行也会被删除 on delete cascade

          alter table emp add constraint emp_dept_fk foreign key(dep_id) references department(id) on update cascade on delete cascade;

8. 数据库设计

8.1 多表关系

  • ※一对多（多对一）

    • 如部门和员工：一个部门有多个员工，一个员工只能对应一个部门
    • 实现方式：在多的一方建立外键，指向一的一方主键

  • 多对多

    • 如学生和课程：一个学生有多个课程，一个课程也可以被很多学生选择
    • 实现方式：需要借助第三张中间表：中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键

  • 一对一

    • 如人和身份证：一个人只能有，一个身份证只能对应一个人
    • 实现方式：可以在任意乙方外键指向另一方的主键

  • 案例：

    #旅游线路分类表 tab_category
#cid：旅游线路分类主键，自动增长
#cname：旅游线路分类名称，非空，唯一，字符串长100
CREATE TABLE tab_category(
	cid INT PRIMARY KEY	AUTO_INCREMENT,
	cname VARCHAR(100) NOT NULL UNIQUE
);

#旅游线路表tab_route
#rid：旅游线路主键，自动增长
#rname：旅游线路名称，非空，唯一，字符串长100
#price：价格
#rdate：商家日期，日期类型
#cid：外键，所属分类
CREATE TABLE tab_route(
	rid INT PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(100) NOT NULL UNIQUE,
	price DOUBLE,
	rdate date,
	cid int,
	FOREIGN KEY(cid) REFERENCES tab_category(cid)
);

  #用户表：tab_user
  #uid：用户主键
  #username：用户名，长度100，唯一，非空
  #password：密码，长度30，非空
  #name：真实姓名，长度100
  #birthday：生日，日期类型
  #sex：性别，定义字符串长度1
  #telephone：手机号，字符串11
  #email：邮箱，字符串长度100
  CREATE TABLE tab_user(
  	uid INT PRIMARY KEY AUTO_INCREMENT,
  	username VARCHAR(100) NOT NULL UNIQUE,
  	password VARCHAR(30) NOT NULL,
  	name VARCHAR(100),
  	birthday date,
  	sex CHAR(1) DEFAULT '男',
  	telephone VARCHAR(11),
  	email VARCHAR(100)
  );

  #用户表和路线表多对多
  #三方表：收藏表：tab_favorite
  #rid：旅游路线id，外键
  #date：收藏时间
  #uid：用户id，外键
  #rid和uid不能重复，设置符合主键，同一个用户不能收藏同一个线路两次
  CREATE TABLE tab_favorite(
  	rid int,
  	datetime date,
  	uid int,
  	PRIMARY KEY(rid,uid),
  	FOREIGN KEY(rid) REFERENCES tab_route(rid),
  	FOREIGN KEY(uid) REFERENCES tab_user(uid)
  );

8.2 范式

概念：设计数据库时，需要遵循的一些规范

三大范式简单理解：

第一范式：表中的每一列字段实现不能再分割的原则，这就是原子性

第二范式：一张表只能表示一个内容，不能同时存储两部或者以上的内容

第三范式：在第二范式的基础上每一列都直接与主键建立关系，通过主键就可以查询到相应的值

9. 事务

  • 事务的基本特征介绍
    • 概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败
    • 操作：
      • 开启事务：start transaction;
      • 回滚：rollback;
      • 提交：commit;
    • MySQL中事务默认自动提交
      • 事务提交的两种方式：
        • 自动提交：MySQL就是自动提交的(Oracle默认是手动提交的)
        • 手动提交：需要先开启事务，再提交
      • 修改事务的默认提交方式：
        • 查看事务的默认提交方式：select @@autocommit; 1代表自动提交，0代表手动提交
        • 修改方式：set @@autocommit = 0;
  • 事务的四大特征
    • 原子性：事务是原子性的，原子是不可再分割的最小操作单位。要么同时成功，要么同时失败。
    • 持久性：如果事务一旦提交或者回滚，将会持久地更新数据库表。
    • 隔离性：多个事务之间相互独立。（实际上会有相互影响）
    • 一致性：事务操作前后，数据总量不变
  • 事务的隔离级别（了解）
    • 概念：多个事务之间是隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
    • 存在问题：
      • 脏读：一个事务，读取到另一个事务中没有提交的数据
      • 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样
      • 幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
    • 隔离级别：
      • read uncommitted：读未提交
        • 产生的问题：脏读、不可重复读（虚读）、幻读
      • read committed：读已提交（Oracle）
        • 产生的问题：不可重复读（虚读）、幻读
      • repeatable read：可重复读（MySQL默认）
        • 产生的问题：幻读
      • serializable：串行化
        • 可以解决所有的问题
    • 注意：隔离级别从小到大安全性越来越高，但是效率越来越低
    • 数据库查询隔离级别：select @@tx_isolation;
    • 数据库设置隔离级别：set global transaction isolation level 级别字符串;

10. DCL

  • 管理用户

    • 添加用户

      CREATE USER '用户名' @ '主机名' IDENTIFIED BY '密码'

    • 删除用户

      DROP USER '用户名' @ '主机名';

    • 修改用户密码

      UPDATE USER SET PASSWORD =PASSWORD('新密码') WHERE USER = '用户名';

      如果在mysql中忘记了ROOT用户的密码：

      • 管理员cmd---> net stop mysql 停止mysql服务

      • 使用无验证方式启动MySQL服务：mysql --skip-grant-tables

      • 开启新的cmd窗口--->mysql 回车即可登陆成功

      • USE mysql;

UPDATE USER SET PASSWORD =PASSWORD('123456') WHERE USER = 'root';

      • 打开任务管理器，手动结束MySQL进程

      • 管理员cmd--->net start mysql 打开MySQL服务

      • 接下来就可以使用新密码登陆啦！

    • 查询用户

      USE mysql;

SELECT * FROM USER;
      • %：通配符，表示可以在任意主机使用用户登陆数据库

  • 权限管理

    • 查询权限：

      SHOW GRANTS FOR '用户名' @ '主机名'

    • 授予权限：

      GRANT 权限列表 ON 数据库名.表名 TO '用户名' @ '主机名'

    • 撤销权限：

      REVOKE 权限列表 ON 数据库名.表名 TO '用户名' @ '主机名'
posted @   风吹头蛋凉OvO  阅读(16)  评论(0)  编辑  收藏  举报
相关博文：
· SpringBoot&MyBatisPlus
· 鱼皮用户中心开发笔记
· MySQL基础知识点串讲PART01
· MySQL学习笔记-day01
· MySQL 基础
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
点击右上角即可分享
微信分享提示