MySql学习笔记
\quad
PS:本文整理的笔记来自于B站视频:老杜带你学_mysql入门基础(mysql基础视频+数据库实战)。视频讲的很好,值得大家一看~
\quad
一、MySql安装及概述
1.1 MySQL安装
MySql安装包下载链接:https://dev.mysql.com/downloads/mysql/
下载完后,我们将 zip 包解压到相应的目录,这里我将解压后的文件夹放在 C:\web\mysql-8.0.11 下。
接下来我们需要配置下 MySQL 的配置文件
打开刚刚解压的文件夹 C:\web\mysql-8.0.11 ,在该文件夹下创建 my.ini 配置文件,编辑 my.ini 配置以下基本信息:
[client]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:\\web\\mysql-8.0.11
# 设置 mysql数据库的数据的存放目录,MySQL 8+ 不需要以下配置,系统自己生成即可,否则有可能报错
# datadir=C:\\web\\sqldata
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
- 转到mysql的bin目录下:
cd D:\Software\mysql\bin
- 安装mysql的服务:
mysqld --install
- 初始化mysql,在这里,初始化会产生一个随机密码:
mysqld --initialize --console
- 开启mysql的服务:
net start mysql
- 登录验证,mysql是否安装成功!要注意上面产生的随机密码,不包括前面符号前面的空格,否则会登陆失败
mysql -u root -p
改密码:(这里是改为空密码)
alter user 'root'@'localhost' identified by '';
设置系统的全局变量: 为了方便登录操作mysql,设置一个全局变量。(环境变量)
端口号:3306 服务名字:MySQL
1.2 SQL的分类
- 数据查询语言(DQL:Data Query Language)
select
- 数据操纵语言(DML:Data Manipulation Language)
insert delete update
- 数据定义语言(DDL:Data Definition Language)
create drop alter
- 事务控制语言(TCL:Transactional Control Language)
commit rollback
- 数据控制语言(DCL:Data Control Language)
grant revoke
1.3 导入数据
mysql> create database bjpowernode;
mysql> use bjpowernode
mysql> source D:\bjpowernode.sql
mysql> drop database bjpowernode; # 删除数据库
\quad
二、常用命令
2.1 退出 mysql
可使用\q
、QUIT
或 EXIT
2.2 查看mysql中有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sqltest |
| sys |
+--------------------+
5 rows in set (0.00 sec)
注意:SQL语句以
;
结尾!!
2.3 查看表
show tables;
2.4 选择使用某个数据库
used test
在数据库中建立表,因此创建表的时候必须要先选择数据库。
2.5 创建数据库
create database test;
2.6 查看数据库的版本号
select version();
2.7 查询当前使用的数据库
select database();
2.8 终止一条语句
- 如果想要终止一条正在编写的语句,可键入
\c
。
2.9 导入数据库
source D:\Software\test.sql
注意:路径不要有中文
2.10 查看其他库中的表
mysql> show tables from mysql;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version |
| replication_group_member_actions |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------------------------+
37 rows in set (0.01 sec)
注意:SQL语句不区分大小写!!
MySQL 在 Windows 下都不区分大小写。MySQL 在 Linux 下数据库名、表名、列名、别名大小写的规则:
- 数据库名与表名是严格区分大小写的;
- 表的别名是严格区分大小写的;
- 列名与列的别名在所有的情况下均是忽略大小写的;
- 变量名也是严格区分大小写的。
\quad
三、一般查询
3.1 查询表结构
desc
不看表结构。
desc <table name>;
例如:
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 | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
3.2 查询一个字段
select 字段名 from <table name>;
select
语句后面跟的是字段名称,select
是关键字,select
和字段名称之间采用空格隔开,from
表示将要查询的表,它和字段之间采用空格隔开。
3.3 查询多个字段
select 字段名1,字段名2 from <table name>;
查询多个字段,select
中的字段采用逗号间隔即可。
3.4 查询全部字段
select * from <table name>;
效率低,可读性差,实际开发中不建议。
3.5 给查询的列起别名
as
可以省略。
select 字段名 as 名字 from <table name>
注意:
select
语句永远不会进行修改操作
例如:
mysql> select deptno, dname as "dept name" from dept;
+--------+------------+
| deptno | dept name |
+--------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)
名字有空格的话,需要用引号括起来。
3.6 字段可以使用数学表达式
mysql> SELECT ename, sal * 12 as '年薪' from emp;
+--------+----------+
| ename | 年薪 |
+--------+----------+
| 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 |
+--------+----------+
14 rows in set (0.00 sec)
注意:中文最好也要用引号括起来。
3.7 查看表的创建语句
show create table <table name>;
例如:
mysql> show create table emp;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp | CREATE TABLE `emp` (
`EMPNO` int NOT NULL,
`ENAME` varchar(10) DEFAULT NULL,
`JOB` varchar(9) DEFAULT NULL,
`MGR` int DEFAULT NULL,
`HIREDATE` date DEFAULT NULL,
`SAL` double(7,2) DEFAULT NULL,
`COMM` double(7,2) DEFAULT NULL,
`DEPTNO` int DEFAULT NULL,
PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
四、条件查询
不是讲表中所有数据都查出来。是查询出来符合条件的。
语法格式:
select 字段1,字段2,... from 表名 where 条件;
4.1 =
mysql> select ename, sal from emp where sal=800;
+-------+--------+
| ename | sal |
+-------+--------+
| SMITH | 800.00 |
+-------+--------+
1 row in set (0.00 sec)
mysql> select empno, ename, sal from emp where ename = 'smith';
+-------+-------+--------+
| empno | ename | sal |
+-------+-------+--------+
| 7369 | SMITH | 800.00 |
+-------+-------+--------+
1 row in set (0.00 sec)
4.2 !=
mysql> select ename,sal from emp where sal != 800;
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.00 sec)
<>
也可以表示不等于
4.3 < > >= <=
mysql> select ename,sal from emp where sal < 2000;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| MILLER | 1300.00 |
+--------+---------+
8 rows in set (0.00 sec)
4.4 between … and …
mysql> select empno, ename, sal from emp where sal between 2450 and 3000;
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7902 | FORD | 3000.00 |
+-------+-------+---------+
5 rows in set (0.00 sec)
注意:
between...and...
是包含最大值和最小值的,而且必须左小右大
4.5 is null or is not null
mysql> select empno, ename, comm from emp where comm is null;
+-------+--------+------+
| empno | ename | comm |
+-------+--------+------+
| 7369 | SMITH | NULL |
| 7566 | JONES | NULL |
| 7698 | BLAKE | NULL |
| 7782 | CLARK | NULL |
| 7788 | SCOTT | NULL |
| 7839 | KING | NULL |
| 7876 | ADAMS | NULL |
| 7900 | JAMES | NULL |
| 7902 | FORD | NULL |
| 7934 | MILLER | NULL |
+-------+--------+------+
10 rows in set (0.00 sec)
注意:数据库中的null代表什么也没有,不能用=衡量
4.6 and or
mysql> select empno, ename, job, sal from emp where job = 'manager' and sal > 2500;
+-------+-------+---------+---------+
| empno | ename | job | sal |
+-------+-------+---------+---------+
| 7566 | JONES | MANAGER | 2975.00 |
| 7698 | BLAKE | MANAGER | 2850.00 |
+-------+-------+---------+---------+
2 rows in set (0.00 sec)
mysql> select empno, ename, job, sal from emp where job = 'manager' or job = 'salesman';
+-------+--------+----------+---------+
| empno | ename | job | sal |
+-------+--------+----------+---------+
| 7499 | ALLEN | SALESMAN | 1600.00 |
| 7521 | WARD | SALESMAN | 1250.00 |
| 7566 | JONES | MANAGER | 2975.00 |
| 7654 | MARTIN | SALESMAN | 1250.00 |
| 7698 | BLAKE | MANAGER | 2850.00 |
| 7782 | CLARK | MANAGER | 2450.00 |
| 7844 | TURNER | SALESMAN | 1500.00 |
+-------+--------+----------+---------+
7 rows in set (0.00 sec)
需要注意:and
和or
同时出现的话,有优先级问题,and
优先级更高,会先执行and
。
mysql> select empno, ename, job, sal, deptno from emp where sal > 2500 and (deptno = 10 or deptno = 20);
+-------+-------+-----------+---------+--------+
| empno | ename | job | sal | deptno |
+-------+-------+-----------+---------+--------+
| 7566 | JONES | MANAGER | 2975.00 | 20 |
| 7788 | SCOTT | ANALYST | 3000.00 | 20 |
| 7839 | KING | PRESIDENT | 5000.00 | 10 |
| 7902 | FORD | ANALYST | 3000.00 | 20 |
+-------+-------+-----------+---------+--------+
4 rows in set (0.00 sec)
4.7 in
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 |
+-------+--------+----------+
7 rows in set (0.00 sec)
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 |
+-------+--------+----------+
7 rows in set (0.00 sec)
可以看到如上两个语句是等价的。
mysql> select ename,sal from emp where sal = 800 or sal = 5000;
+-------+---------+
| ename | sal |
+-------+---------+
| SMITH | 800.00 |
| KING | 5000.00 |
+-------+---------+
2 rows in set (0.00 sec)
mysql> select ename,sal from emp where sal in (800, 5000);
+-------+---------+
| ename | sal |
+-------+---------+
| SMITH | 800.00 |
| KING | 5000.00 |
+-------+---------+
2 rows in set (0.00 sec)
上面两句同理。
注意:
in
不是区间。
4.8 模糊查询
like
支持%
或_
匹配:
%
匹配任意多个字符_
匹配任意一个字符
下面列举一些例子:
找出名字中含有O的:
mysql> select ename from emp where ename like '%O%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
3 rows in set (0.00 sec)
找出名字以T结尾的:
mysql> select ename from emp where ename like '%T';
+-------+
| ename |
+-------+
| SCOTT |
+-------+
1 row in set (0.00 sec)
找出名字以K开始的:
mysql> select ename from emp where ename like 'K%';
+-------+
| ename |
+-------+
| KING |
+-------+
1 row 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)
找出第三个字母是R的:
mysql> select ename from emp where ename like '__R%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| TURNER |
| FORD |
+--------+
4 rows in set (0.00 sec)
\quad
五、排序
排序采用 order by
子句,order by
后面跟上排序字段,排序字段可以放多个,多个采用逗号间隔,order by
默认采用升序,如果存在 where
子句那么 order by
必须放到 where
语句的后面。
5.1 单一字段排序
查询所有员工薪资,排序:(默认升序)
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 |
+--------+---------+
14 rows in set (0.00 sec)
5.2 多个字段排序
按照多个字段排序,如:首先按照 sal 排序,再按照 ename 排序
mysql> select ename, sal from emp order by 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)
5.3 手动指定排序顺序
默认是升序,可以指定顺序。
- 降序:
desc
- 升序:
asc
手动指定按照薪水由大到小排序
mysql> select ename, sal from emp order by sal desc;
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
14 rows in set (0.00 sec)
按照 job 和薪水倒序
mysql> select ename, job, sal from emp order by job desc, sal desc;
+--------+-----------+---------+
| ename | job | sal |
+--------+-----------+---------+
| ALLEN | SALESMAN | 1600.00 |
| TURNER | SALESMAN | 1500.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
| KING | PRESIDENT | 5000.00 |
| JONES | MANAGER | 2975.00 |
| BLAKE | MANAGER | 2850.00 |
| CLARK | MANAGER | 2450.00 |
| MILLER | CLERK | 1300.00 |
| ADAMS | CLERK | 1100.00 |
| JAMES | CLERK | 950.00 |
| SMITH | CLERK | 800.00 |
| SCOTT | ANALYST | 3000.00 |
| FORD | ANALYST | 3000.00 |
+--------+-----------+---------+
14 rows in set (0.00 sec)
mysql> select ename, job, sal from emp order by sal desc, job desc;
+--------+-----------+---------+
| ename | job | sal |
+--------+-----------+---------+
| KING | PRESIDENT | 5000.00 |
| SCOTT | ANALYST | 3000.00 |
| FORD | ANALYST | 3000.00 |
| JONES | MANAGER | 2975.00 |
| BLAKE | MANAGER | 2850.00 |
| CLARK | MANAGER | 2450.00 |
| ALLEN | SALESMAN | 1600.00 |
| TURNER | SALESMAN | 1500.00 |
| MILLER | CLERK | 1300.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
| ADAMS | CLERK | 1100.00 |
| JAMES | CLERK | 950.00 |
| SMITH | CLERK | 800.00 |
+--------+-----------+---------+
14 rows in set (0.00 sec)
排序的字段要注意顺序,先排哪个。注意看上面两个的区别。
根据字段的位置也可以排序:按照第二列排序
mysql> select ename,job,sal from emp order by 2;
+--------+-----------+---------+
| ename | job | sal |
+--------+-----------+---------+
| SCOTT | ANALYST | 3000.00 |
| FORD | ANALYST | 3000.00 |
| SMITH | CLERK | 800.00 |
| ADAMS | CLERK | 1100.00 |
| JAMES | CLERK | 950.00 |
| MILLER | CLERK | 1300.00 |
| JONES | MANAGER | 2975.00 |
| BLAKE | MANAGER | 2850.00 |
| CLARK | MANAGER | 2450.00 |
| KING | PRESIDENT | 5000.00 |
| ALLEN | SALESMAN | 1600.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
| TURNER | SALESMAN | 1500.00 |
+--------+-----------+---------+
14 rows in set (0.00 sec)
不建议使用此种方式,采用数字含义不明确,程序不健壮。
工资在1250-300之间的员工信息,要求按照薪资降序排列:
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 |
+--------+---------+
10 rows in set (0.00 sec)
关键字顺序不能变:
select
...
from
...
where
...
order by
...
执行顺序:
- from
- where
- select
- order by
排序总是在最后执行!
\quad
六、数据处理函数
数据处理函数又被称为单行处理函数,其特点是:一个输入对应一个输出。与之对应的是多行处理函数,其特点是:多个输入对应一个输出。
常见的单行处理函数:
6.1 lower upper
lower 转小写: upper 转大写
mysql> select lower(ename) from emp;
+--------------+
| lower(ename) |
+--------------+
| smith |
| allen |
| ward |
| jones |
| martin |
| blake |
| clark |
| scott |
| king |
| turner |
| adams |
| james |
| ford |
| miller |
+--------------+
14 rows in set (0.00 sec)
6.2 substr
substr 取子串,格式:
substr(被截取的字符串,起始下标,截取的长度)
起始下标从1开始
mysql> select substr(ename, 1, 3) as ename from emp;
+-------+
| ename |
+-------+
| SMI |
| ALL |
| WAR |
| JON |
| MAR |
| BLA |
| CLA |
| SCO |
| KIN |
| TUR |
| ADA |
| JAM |
| FOR |
| MIL |
+-------+
14 rows in set (0.00 sec)
首字母大写:
select concat(upper(substr(ename, 1, 1)),lower(substr(ename, 2, length(ename) - 1))) as result from emp;
6.3 concat
concat 字符串拼接
mysql> select concat(empno, '-', ename) from emp;
+---------------------------+
| concat(empno, '-', ename) |
+---------------------------+
| 7369-SMITH |
| 7499-ALLEN |
| 7521-WARD |
| 7566-JONES |
| 7654-MARTIN |
| 7698-BLAKE |
| 7782-CLARK |
| 7788-SCOTT |
| 7839-KING |
| 7844-TURNER |
| 7876-ADAMS |
| 7900-JAMES |
| 7902-FORD |
| 7934-MILLER |
+---------------------------+
14 rows in set (0.00 sec)
进阶:首字母大写。
mysql> select concat(upper(substr(ename, 1, 1)), lower(substr(ename, 2, length(ename) - 1))) as name from emp;
+--------+
| name |
+--------+
| Smith |
| Allen |
| Ward |
| Jones |
| Martin |
| Blake |
| Clark |
| Scott |
| King |
| Turner |
| Adams |
| James |
| Ford |
| Miller |
+--------+
14 rows in set (0.00 sec)
6.4 length
length 取长度
mysql> select length(ename) as enameLength from emp;
+-------------+
| enameLength |
+-------------+
| 5 |
| 5 |
| 4 |
| 5 |
| 6 |
| 5 |
| 5 |
| 5 |
| 4 |
| 6 |
| 5 |
| 5 |
| 4 |
| 6 |
+-------------+
14 rows in set (0.00 sec)
6.5 trim
trim 去除空白
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.00 sec)
trim
只会去首尾空格,不会去除中间的空格。
6.6 round
round 四舍五入
mysql> select round(123.56354363, 2);
+------------------------+
| round(123.56354363, 2) |
+------------------------+
| 123.56 |
+------------------------+
1 row in set (0.00 sec)
mysql> select round(123.56354363, -2);
+-------------------------+
| round(123.56354363, -2) |
+-------------------------+
| 100 |
+-------------------------+
1 row in set (0.00 sec)
注意:-1保留到十位,-2会保留到百位。
6.7 rand
rand 生成随机数
mysql> select rand();
+---------------------+
| rand() |
+---------------------+
| 0.12361311458387703 |
+---------------------+
1 row in set (0.00 sec)
mysql> select round(rand()*100);
+-------------------+
| round(rand()*100) |
+-------------------+
| 93 |
+-------------------+
1 row in set (0.00 sec)
随机抽取记录数
mysql> select ename, sal from emp order by rand() limit 5;
+-------+---------+
| ename | sal |
+-------+---------+
| SMITH | 800.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| ADAMS | 1100.00 |
| CLARK | 2450.00 |
+-------+---------+
5 rows in set (0.00 sec)
order by
不能省。
6.8 ifnull
ifnull
可以将 null
转换成一个具体值。ifnull
是空处理函数,专门处理空的。
在所有数据库当中,只要有 null
参与的数学运算,最终结果就是 null
。比如下面的代码:
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
函数用法:ifnull(data, value)
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)
6.9 case…when…then…when…then…else…end
如果 job 为 MANAGERG 薪水上涨 10%,如果 job 为 SALESMAN 工资上涨 50%,其他的工资不动。
mysql> select ename, job, (case job when 'manager' then 1.1*sal when 'salesman' then sal*1.5 else sal end) as newsal from emp;
+--------+-----------+---------+
| ename | job | newsal |
+--------+-----------+---------+
| SMITH | CLERK | 800.00 |
| ALLEN | SALESMAN | 2400.00 |
| WARD | SALESMAN | 1875.00 |
| JONES | MANAGER | 3272.50 |
| MARTIN | SALESMAN | 1875.00 |
| BLAKE | MANAGER | 3135.00 |
| CLARK | MANAGER | 2695.00 |
| SCOTT | ANALYST | 3000.00 |
| KING | PRESIDENT | 5000.00 |
| TURNER | SALESMAN | 2250.00 |
| ADAMS | CLERK | 1100.00 |
| JAMES | CLERK | 950.00 |
| FORD | ANALYST | 3000.00 |
| MILLER | CLERK | 1300.00 |
+--------+-----------+---------+
14 rows in set (0.00 sec)
不修改数据库,只是将查询结果调整显示。
6.10 str_to_date
查询 1981-02-20 入职的员工
第一种方法,与数据库的格式匹配上:
mysql> select * from emp where hiredate='1981-02-20';
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
+-------+-------+----------+------+------------+---------+--------+--------+
1 row in set (0.00 sec)
第二种方法,将字符串转换成 date
类型:
mysql> select * from emp where hiredate=str_to_date('1981-02-20', '%Y-%m-%d');
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
+-------+-------+----------+------+------------+---------+--------+--------+
1 row in set (0.00 sec)
mysql> select * from emp where hiredate=str_to_date('02-20-1981', '%m-%d-%Y');
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
+-------+-------+----------+------+------------+---------+--------+--------+
1 row in set (0.00 sec)
str_to_date
可以将字符串转换成日期,具体格式为str_to_date (字符串, 匹配格式)
6.11 date_format
查询 1981-02-20 以后入职的员工,将入职日期格式化成 yyyy-mm-dd hh:mm:ss
mysql> select empno, ename, date_format(hiredate, '%Y-%m-%d %H:%i:%s') as hiredate
from emp;
+-------+--------+---------------------+
| empno | ename | hiredate |
+-------+--------+---------------------+
| 7369 | SMITH | 1980-12-17 00:00:00 |
| 7499 | ALLEN | 1981-02-20 00:00:00 |
| 7521 | WARD | 1981-02-22 00:00:00 |
| 7566 | JONES | 1981-04-02 00:00:00 |
| 7654 | MARTIN | 1981-09-28 00:00:00 |
| 7698 | BLAKE | 1981-05-01 00:00:00 |
| 7782 | CLARK | 1981-06-09 00:00:00 |
| 7788 | SCOTT | 1987-04-19 00:00:00 |
| 7839 | KING | 1981-11-17 00:00:00 |
| 7844 | TURNER | 1981-09-08 00:00:00 |
| 7876 | ADAMS | 1987-05-23 00:00:00 |
| 7900 | JAMES | 1981-12-03 00:00:00 |
| 7902 | FORD | 1981-12-03 00:00:00 |
| 7934 | MILLER | 1982-01-23 00:00:00 |
+-------+--------+---------------------+
14 rows in set (0.00 sec)
获取当前时间:
mysql> select date_format(now(),'%Y-%m-%d %H %i %s');
+----------------------------------------+
| date_format(now(),'%Y-%m-%d %H %i %s') |
+----------------------------------------+
| 2022-05-30 15 12 40 |
+----------------------------------------+
1 row in set (0.00 sec)
now()
获得当前时间。
日期格式的说明:
%Y | 代表 4 位的年份 |
---|---|
%y | 代表 2 位的年份 |
%m | 代表月,格式为(01……12) |
%c | 代表月,格式为(1……12) |
%H | 代表小时,格式为(00……23) |
%h | 代表小时,格式为(01……12) |
%i | 代表分钟,格式为(00……59) |
%r | 代表时间,格式为 12 小时(hh:mm:ss [AP]M) |
%T | 代表时间,格式为 24 小时(hh:mm:ss) |
%S | 代表秒,格式为(00……59) |
%s | 代表秒,格式为(00……59) |
\quad
七、分组函数/聚合函数/多行处理函数
多行处理函数的特点:输入多行,最终输出一行。
- count
- sum
- avg
- max
- min
分组函数在使用时必须先进行分组才能使用。如果没有对数据进行分组,整张表默认为一组。
注意事项:
- 分组函数自动忽略 NULL,不需要提前对 NULL 进行处理。
count(*)
与count(comm)
的区别?
- 具体字段:表示统计该字段下所有不为 NULL 的元素的总数;
- *:统计表当中的总行数。
每一行记录不可能都为 NULL,一行数据中有一列不为 NULL,则这行数据就是有效的。
- 分组函数不能够直接使用在where子句中。
mysql> select ename,sal from emp where sal > avg(sal);
ERROR 1111 (HY000): Invalid use of group function
- 所有的分组函数可以组合起来一起用。
7.1 count
取得所有的员工数
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
1 row in set (0.00 sec)
Count(*)
表示取得所有记录,忽略null
,null
的值也会取的。
取得津贴不为 null 员工数
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)
采用
count(字段名称)
,不会取得为null
的记录
取得工作岗位的个数
mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
| 5 |
+---------------------+
1 row in set (0.00 sec)
7.2 sum
sum
可以取得某一个列的和,null
会被忽略。
取得薪水的合计
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)
取得津贴的合计
mysql> select sum(comm) from emp;
+-----------+
| sum(comm) |
+-----------+
| 2200.00 |
+-----------+
1 row in set (0.00 sec)
取得薪水的合计(sal+comm)
mysql> select sum(sal + comm) from emp;
+-----------------+
| sum(sal + comm) |
+-----------------+
| 7800.00 |
+-----------------+
1 row in set (0.00 sec)
上面这个方法是错的,sal+comm
会导致null
的出现,最终sum
不会处理这些null
值,可以用下面的方法处理这个问题:
mysql> select sum(sal + ifnull(comm, 0)) from emp;
+----------------------------+
| sum(sal + ifnull(comm, 0)) |
+----------------------------+
| 31225.00 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select sum(sal) + sum(comm) from emp;
+----------------------+
| sum(sal) + sum(comm) |
+----------------------+
| 31225.00 |
+----------------------+
1 row in set (0.00 sec)
7.3 avg
取得平均薪水
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
7.4 max
取得最高薪水
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
| 5000.00 |
+----------+
1 row in set (0.00 sec)
取得最晚入职的员工:
mysql> select empno, ename, hiredate from emp where hiredate = (select max(hiredate) from emp);
+-------+-------+------------+
| empno | ename | hiredate |
+-------+-------+------------+
| 7876 | ADAMS | 1987-05-23 |
+-------+-------+------------+
1 row in set (0.00 sec)
mysql> select max(str_to_date(hiredate, '%Y-%m-%d')) from emp;
+----------------------------------------+
| max(str_to_date(hiredate, '%Y-%m-%d')) |
+----------------------------------------+
| 1987-05-23 |
+----------------------------------------+
1 row in set (0.00 sec)
7.5 min
取得最低薪水
mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
| 800.00 |
+----------+
1 row in set (0.00 sec)
取得最早入职得员工
mysql> select empno, ename, hiredate from emp where hiredate = (select min(hiredate) from emp);
+-------+-------+------------+
| empno | ename | hiredate |
+-------+-------+------------+
| 7369 | SMITH | 1980-12-17 |
+-------+-------+------------+
1 row in set (0.00 sec)
mysql> select min(str_to_date(hiredate, '%Y-%m-%d')) from emp;
+----------------------------------------+
| min(str_to_date(hiredate, '%Y-%m-%d')) |
+----------------------------------------+
| 1980-12-17 |
+----------------------------------------+
1 row in set (0.00 sec)
7.6 组合聚合函数
可以将这些聚合函数都放到 select 中一起使用
mysql> select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;
+----------+----------+----------+-------------+----------+
| sum(sal) | min(sal) | max(sal) | avg(sal) | count(*) |
+----------+----------+----------+-------------+----------+
| 29025.00 | 800.00 | 5000.00 | 2073.214286 | 14 |
+----------+----------+----------+-------------+----------+
1 row in set (0.00 sec)
\quad
八、分组查询
分组查询主要涉及到两个子句,分别是:group by
和 having
。
8.1 group by
先进行分组,然后对每一组的数据进行操作。
select
...
from
...
group by
...
执行顺序:
select
...
from
...
where
...
group by
...
order by
...
执行顺序不能颠倒!执行顺序不能颠倒!执行顺序不能颠倒!
- from
- where
- group by
- select
- order by
取得每个工作岗位的工资合计,要求显示岗位名称和工资合计
mysql> select job, sum(sal) from emp group by job;
+-----------+----------+
| job | sum(sal) |
+-----------+----------+
| CLERK | 4150.00 |
| SALESMAN | 5600.00 |
| MANAGER | 8275.00 |
| ANALYST | 6000.00 |
| PRESIDENT | 5000.00 |
+-----------+----------+
5 rows in set (0.00 sec)
如果使用了 order by,order by 必须放到 group by 后面
mysql> select job, sum(sal) from emp group by job order by sum(sal);
+-----------+----------+
| job | sum(sal) |
+-----------+----------+
| CLERK | 4150.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 5600.00 |
| ANALYST | 6000.00 |
| MANAGER | 8275.00 |
+-----------+----------+
5 rows in set (0.00 sec)
按照工作岗位和部门编码分组,取得的工资合计
mysql> select job, deptno, sum(sal) from emp group by job, deptno;
+-----------+--------+----------+
| job | deptno | sum(sal) |
+-----------+--------+----------+
| CLERK | 20 | 1900.00 |
| SALESMAN | 30 | 5600.00 |
| MANAGER | 20 | 2975.00 |
| MANAGER | 30 | 2850.00 |
| MANAGER | 10 | 2450.00 |
| ANALYST | 20 | 6000.00 |
| PRESIDENT | 10 | 5000.00 |
| CLERK | 30 | 950.00 |
| CLERK | 10 | 1300.00 |
+-----------+--------+----------+
9 rows in set (0.00 sec)
两个字段联合成一个字段看——两个字段联合分组
注意:在 SQL 语句中若有 group by 语句,那么在 select 语句后面只能跟分组函数+参与分组的字段,其他的一律不能跟。
mysql> select empno, deptno, sum(sal) from emp group by deptno;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sqltest.emp.EMPNO' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
8.2 having
如果想对分组数据再进行过滤需要使用 having 子句。
找出每个部门最高薪资,要求显示最高薪资大于300的?
mysql> select deptno, max(sal) from emp group by deptno having max(sal) > 3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
1 row in set (0.00 sec)
mysql> select deptno, max(sal) from emp where sal > 3000 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
1 row in set (0.00 sec)
上面两句是一样的。
找出每个部门平均薪资,要求显示平均薪资高于2500的
mysql> select deptno, avg(sal) from emp group by deptno having avg(sal) > 2500;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)
使用 having 可以对分完组之后的数据进一步过滤。 having 不能单独使用, having 必须和 group by 联合使用。having 不能代替 where。where 和 having,优先选择 where。
8.3 去重
把查询结果去除重复记录(原表数据不会被修改,只是查询结果去重)
去重需要使用一个关键字:distinct
。要注意的是:distinct
只能出现在所有字段的前方。
mysql> select distinct job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)
distinct
出现在多个字段之前,表示多个字段联合起来去重
mysql> select distinct job,ename from emp;
+-----------+--------+
| job | ename |
+-----------+--------+
| CLERK | SMITH |
| SALESMAN | ALLEN |
| SALESMAN | WARD |
| MANAGER | JONES |
| SALESMAN | MARTIN |
| MANAGER | BLAKE |
| MANAGER | CLARK |
| ANALYST | SCOTT |
| PRESIDENT | KING |
| SALESMAN | TURNER |
| CLERK | ADAMS |
| CLERK | JAMES |
| ANALYST | FORD |
| CLERK | MILLER |
+-----------+--------+
14 rows in set (0.00 sec)
统计工作岗位的数量:
mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
| 5 |
+---------------------+
1 row in set (0.00 sec)
8.4 select 语句总结
一个完整的 select 语句格式如下:
select
...
from
...
where
...
group by
...
having
...
order by
...
以上语句的执行顺序
- 首先执行 where 语句过滤原始数据
- 执行 group by 进行分组
- 执行 having 对分组数据进行操作
- 执行 select 选出数据
- 执行 order by 排序
需要注意:执行顺序不能颠倒!执行顺序不能颠倒!执行顺序不能颠倒!
原则:能在 where 中过滤的数据,尽量在 where 中过滤,效率较高。having 的过滤是专门对分组之后的数据进行过滤的。
看个综合的例子:找出每个岗位的平均薪资,要求显示平均薪资大于1500的,除manager岗位之外,要求按照平均薪资降序排序
mysql> select job, avg(sal) as avgsal from emp where job != 'manager' group by job
having avgsal > 1500 order by avgsal desc;
+-----------+-------------+
| job | avgsal |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST | 3000.000000 |
+-----------+-------------+
2 rows in set (0.00 sec)
\quad
九、连接查询
连接查询:也可以叫跨表查询,需要关联多个表进行查询。
9.1 连接查询的分类
根据语法的年代分类:(SQL92语法,SQL99语法)
根据表连接的方式分类:
- 内连接:等值连接、非等值连接、自连接(
table1 inner join table2 on condition
)- 做连接查询的时候一定要写上关联条件
- inner 可以省略
- 外连接:左外连接,右外连接
- 左外连接:
table1 left outer join table2 on condition
- 做连接查询的时候一定要写上关联条件
- outer 可以省略
- 右外连接:
table1 right outer join table2 on condition
- 做连接查询的时候一定要写上关联条件
- outer 可以省略
- 左外连接:
- 全连接 :笛卡尔积
左外连接(左连接)和右外连接(右连接)的区别:左连接以左面的表为准和右边的表比较,和左表相等的不相等都会显示出来,右表符合条件的显示,不符合条件的不显示。右连接相反。
左连接能完成的功能右连接一定可以完成。
9.2 SQL92 语法
显示每个员工信息,并显示所属的部门名称
mysql> select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
以上查询也称为 “内连接”,只查询相等的数据(连接条件相等的数据)。
取得员工和所属的领导的姓名
mysql> select e.ename, o.ename from emp e, emp o where e.mgr = o.empno;
+--------+-------+
| ename | ename |
+--------+-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------+
13 rows in set (0.00 sec)
以上称为“自连接”,只有一张表连接。具体的查询方法,把一张表看作两张表即可,如以上示例:第一个表 emp e
代表员工表,emp o
代表领导表,相当于员工表和部门表一样。
9.3 SQL99 语法
(内连接)显示薪水大于 2000 的员工信息,并显示所属的部门名称
方法一:SQL92语法
mysql> select e.ename, e.sal, d.dname from emp e, dept d where e.deptno = d.deptno and e.sal > 2000;
+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH |
| BLAKE | 2850.00 | SALES |
| CLARK | 2450.00 | ACCOUNTING |
| SCOTT | 3000.00 | RESEARCH |
| KING | 5000.00 | ACCOUNTING |
| FORD | 3000.00 | RESEARCH |
+-------+---------+------------+
6 rows in set (0.00 sec)
方法二:SQL99语法
mysql> select e.ename, e.sal, d.dname from emp e join dept d on e.deptno = d.deptn
o where sal > 2000;
+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH |
| BLAKE | 2850.00 | SALES |
| CLARK | 2450.00 | ACCOUNTING |
| SCOTT | 3000.00 | RESEARCH |
| KING | 5000.00 | ACCOUNTING |
| FORD | 3000.00 | RESEARCH |
+-------+---------+------------+
6 rows in set (0.00 sec)
注意:内连接在实际中一般不加
inner
关键字。
SQL92 语法和 SQL99 语法的区别:SQL99 语法可以做到表的连接和查询条件分离,特别是多个表进行连接的时候,会比 SQL92更清晰。
(外连接)显示员工信息,并显示所属的部门名称,如果某一个部门没有员工,那么该部门也必须显示出来
mysql> select e.ename, e.sal, d.dname from dept d left join emp e on e.deptno = d.deptno;
+--------+---------+------------+
| ename | sal | dname |
+--------+---------+------------+
| MILLER | 1300.00 | ACCOUNTING |
| KING | 5000.00 | ACCOUNTING |
| CLARK | 2450.00 | ACCOUNTING |
| FORD | 3000.00 | RESEARCH |
| ADAMS | 1100.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| JONES | 2975.00 | RESEARCH |
| SMITH | 800.00 | RESEARCH |
| JAMES | 950.00 | SALES |
| TURNER | 1500.00 | SALES |
| BLAKE | 2850.00 | SALES |
| MARTIN | 1250.00 | SALES |
| WARD | 1250.00 | SALES |
| ALLEN | 1600.00 | SALES |
| NULL | NULL | OPERATIONS |
+--------+---------+------------+
15 rows in set (0.00 sec)
mysql> select e.ename, e.sal, d.dname from emp e right join dept d on e.deptno = d.deptno;
+--------+---------+------------+
| ename | sal | dname |
+--------+---------+------------+
| MILLER | 1300.00 | ACCOUNTING |
| KING | 5000.00 | ACCOUNTING |
| CLARK | 2450.00 | ACCOUNTING |
| FORD | 3000.00 | RESEARCH |
| ADAMS | 1100.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| JONES | 2975.00 | RESEARCH |
| SMITH | 800.00 | RESEARCH |
| JAMES | 950.00 | SALES |
| TURNER | 1500.00 | SALES |
| BLAKE | 2850.00 | SALES |
| MARTIN | 1250.00 | SALES |
| WARD | 1250.00 | SALES |
| ALLEN | 1600.00 | SALES |
| NULL | NULL | OPERATIONS |
+--------+---------+------------+
15 rows in set (0.00 sec)
以上两个查询效果相同。
\quad
十、子查询
子查询就是嵌套的 select 语句,可以理解为子查询是一张表。
在where语句中使用子查询,也就是在where语句中加入select语句。
查询员工信息,查询哪些人是管理者,要求显示出其员工编号和员工姓名。
思路:先取得管理者的编号,再查询员工编号包含管理者编号的
mysql> select empno, ename from emp where empno in (select distinct mgr from emp where mgr is not null);
+-------+-------+
| empno | ename |
+-------+-------+
| 7902 | FORD |
| 7698 | BLAKE |
| 7839 | KING |
| 7566 | JONES |
| 7788 | SCOTT |
| 7782 | CLARK |
+-------+-------+
6 rows in set (0.00 sec)
查询哪些人的薪水高于员工的平均薪水,需要显示员工编号,员工姓名,薪水。
mysql> select empno, ename, sal from emp where sal > (select avg(sal) from emp);
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7902 | FORD | 3000.00 |
+-------+-------+---------+
6 rows in set (0.00 sec)
在 from 语句中使用子查询,可以将该子查询看做一张表
查询员工信息,查询哪些人是管理者,要求显示出其员工编号和员工姓名
mysql> select e.empno, e.ename from emp e join (select distinct mgr from emp where mgr is not null) m on e.empno = m.mgr;
+-------+-------+
| empno | ename |
+-------+-------+
| 7902 | FORD |
| 7698 | BLAKE |
| 7839 | KING |
| 7566 | JONES |
| 7788 | SCOTT |
| 7782 | CLARK |
+-------+-------+
6 rows in set (0.00 sec)
查询各个部门的平均薪水所属等级,需要显示部门编号,平均薪水,等级编号
mysql> select deptno, avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 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.00 sec)
mysql> select e.deptno, e.avgsal, g.grade from (select deptno, avg(sal) as avgsal from emp group by deptno) e join salgrade g on e.avgsal between g.losal and g.hisal;
+--------+-------------+-------+
| deptno | avgsal | grade |
+--------+-------------+-------+
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
+--------+-------------+-------+
3 rows in set (0.01 sec)
在 select 语句中使用子查询
查询员工信息,并显示出员工所属的部门名称
mysql> select e.ename, d.dname from emp e join dept d where e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
mysql> select e.ename, (select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
上述两种方法等效。
\quad
十一、union
11.1 union
union 可以合并集合(相加)
union更有效率
查询 job 包含 MANAGER 和包含 SALESMAN 的员工
mysql> select * from emp where job = 'manager' union select * from emp where job= 'salesman';
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
7 rows in set (0.00 sec)
合并结果集的时候,需要查询字段对应个数相同。
在 Oracle 中更严格,不但要求个数相同,而且还要求类型对应相同。
11.2 union all
结果不去重就用union all
,去重就用union
。
现在要分别查看工作为manager或者工资大于3000的用户的编号、姓名、职业、领导的数据,请取出相应结果,结果不去重。
mysql> select empno, ename, job, mgr, sal from emp where job = 'manager' union all select empno, ename, job, mgr, sal from emp where sal > 2000;
+-------+-------+-----------+------+---------+
| empno | ename | job | mgr | sal |
+-------+-------+-----------+------+---------+
| 7566 | JONES | MANAGER | 7839 | 2975.00 |
| 7698 | BLAKE | MANAGER | 7839 | 2850.00 |
| 7782 | CLARK | MANAGER | 7839 | 2450.00 |
| 7566 | JONES | MANAGER | 7839 | 2975.00 |
| 7698 | BLAKE | MANAGER | 7839 | 2850.00 |
| 7782 | CLARK | MANAGER | 7839 | 2450.00 |
| 7788 | SCOTT | ANALYST | 7566 | 3000.00 |
| 7839 | KING | PRESIDENT | NULL | 5000.00 |
| 7902 | FORD | ANALYST | 7566 | 3000.00 |
+-------+-------+-----------+------+---------+
9 rows in set (0.00 sec)
注意:分别查看&结果不去重:所以直接使用两个条件的or
是不行的,直接用union
也不行,要用union all
,分别去查满足条件1的和满足条件2的,然后合在一起不去重。
\quad
十二、limit
MySql提供了limit,主要用于提取前几条或者中间某几行数据。
select * from table limit m, n
其中,m是指记录开始的index,索引从0开始,表示第一条记录,n是指从第m+1条开始,取n条。
select * from tablename limit 2,4
即取出第 3 条至第 6 条,一共4 条记录。
取得前 5 条数据
mysql> select * from emp limit 5;
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+----------+------+------------+---------+---------+--------+
5 rows in set (0.00 sec)
从第二条开始取两条数据
mysql> select * from emp limit 1, 2;
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+-------+-------+----------+------+------------+---------+--------+--------+
2 rows in set (0.00 sec)
取得薪水最高的前 5 名
mysql> select * from emp order by sal desc limit 5;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
+-------+-------+-----------+------+------------+---------+------+--------+
5 rows in set (0.00 sec)
\quad
十三、表
13.1 创建表
语法格式:
create table tableName(
columnName dataType(length),
.....
columnName dataType(length)
);
set character_set_results='gbk';
show variables like '%char%';
创建表的时候,表中有字段,每一个字段有:
- 字段名
- 字段数据类型
- 字段长度限制
- 字段约束
MySql 常用数据类型
类型 | 描述 |
---|---|
Char(长度) | 定长字符串,存储空间大小固定,适合作为主键或外键 |
Varchar(长度) | 变长字符串,存储空间等于实际数据空间 |
Double(有效数字位数,小数位) | 数值型 |
Float(有效数字位数,小数位) | 数值型 |
Int(长度) | 整型 |
Bigint(长度) | 长整型 |
Date | 日期型 |
BLOB | Binary Large OBject(二进制大对象) |
CLOB | Character Large OBject(字符大对象) |
建立学生信息表,字段包括:学号、姓名、性别、出生日期、email、班级标识
create table t_student(
id int(10),
name varchar(20),
sex char(2),
birthday date,
email varchar(30),
class_id int(3)
);
查看表结构:
mysql> desc t_student;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(2) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| email | varchar(30) | YES | | NULL | |
| class_id | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
向 t_student 表中加入数据:
mysql> insert into t_student(id, name, sex, birthday, email, class_id) values(1001,'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_student;
+------+----------+------+------------+-------------+----------+
| id | name | sex | birthday | email | class_id |
+------+----------+------+------------+-------------+----------+
| 1001 | zhangsan | m | 1988-01-01 | qqq@163.com | 10 |
+------+----------+------+------------+-------------+----------+
1 row in set (0.00 sec)
向 t_student 表中加入数据(使用默认值)
drop table if exists t_student;
create table t_student(
id int(10),
name varchar(20),
sex char(2) default 'm',
birthday date,
email varchar(30),
classes_id int(3)
);
insert into t_student(student_id, student_name, birthday, email, classes_id) values(1002, 'zhangsan', '1988-01-01', 'qqq@163.com', 10)
13.2 增加/删除/修改表结构
采用 alter table
来增加/删除/修改表结构,不影响表中的数据
添加字段
需求发生改变,需要向 t_student 中加入联系电话字段,字段名称为:contatct_tel
类型为varchar(40)
。
mysql> alter table t_student add contact_tel varchar(40);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(2) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| email | varchar(30) | YES | | NULL | |
| class_id | int | YES | | NULL | |
| contact_tel | varchar(40) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
修改字段
name
无法满足需求,长度需要更改为100。
mysql> alter table t_student modify name varchar(40);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| sex | char(2) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| email | varchar(30) | YES | | NULL | |
| class_id | int | YES | | NULL | |
| contact_tel | varchar(40) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
如sex
字段名称感觉不好,想用gender
那么就需要更改列的名称。
mysql> alter table t_student change sex gender char(2) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| gender | char(2) | NO | | NULL | |
| birthday | date | YES | | NULL | |
| email | varchar(30) | YES | | NULL | |
| class_id | int | YES | | NULL | |
| contact_tel | varchar(40) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
删除字段
删除联系电话字段
mysql> alter table t_student drop contact_tel;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| gender | char(2) | NO | | NULL | |
| birthday | date | YES | | NULL | |
| email | varchar(30) | YES | | NULL | |
| class_id | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
13.3 添加、修改和删除
添加、修改和删除都属于DML,主要包含的语句:insert
、update
、delete
。
13.3.1 insert
insert
语法格式
insert into 表名(字段,...) values(值,...)
省略字段的插入
mysql> insert into emp values(9999,'zhangsan','MANAGER', null, null, 3000, 500, 10);
Query OK, 1 row affected (0.00 sec)
指定字段的插入(建议使用此种方式)
mysql> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9999, 'zhangsan', 'MANAGER', null, null, 3000, 500, 10);
ERROR 1062 (23000): Duplicate entry '9999' for key 'emp.PRIMARY'
出现了主键重复的错误,主键表示了记录的唯一性,不能重复。
如何插入日期:
第一种方法,插入的日期格式和显示的日期格式一致。
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9997, 'zhangsan', 'MANAGER', null, '1981-06-12', 3000, 500, 10);
第二种方法,采用str_to_date
。
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9996, 'zhangsan', 'MANAGER', null, str_to_date('1981-06-12','%Y-%m-%d'), 3000, 500, 10);
第三种方法,添加系统日期(now()
)。
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9995, 'zhangsan', 'MANAGER', null, now(), 3000, 500, 10);
表复制
mysql> create table emp_bark as select * from emp where sal = 3000;
Query OK, 3 rows affected, 2 warnings (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 2
mysql> select * from emp_bark;
+-------+----------+---------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+----------+---------+------+------------+---------+--------+--------+
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 9999 | zhangsan | MANAGER | NULL | NULL | 3000.00 | 500.00 | 10 |
+-------+----------+---------+------+------------+---------+--------+--------+
3 rows in set (0.00 sec)
以上方式,会自动创建表,将符合查询条件的数据自动复制到创建的表中。
如何将查询的数据直接放到已经存在的表中,可以使用条件
mysql> insert into emp_bark select * from emp where sal = 1250;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from emp_bark;
+-------+----------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+----------+----------+------+------------+---------+---------+--------+
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 9999 | zhangsan | MANAGER | NULL | NULL | 3000.00 | 500.00 | 10 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+-------+----------+----------+------+------------+---------+---------+--------+
5 rows in set (0.00 sec)
13.3.2 update
update
可以根据条件修改数据。
语法格式:
update 表名 set 字段名称 1=需要修改的值 1, 字段名称 2=需要修改的值 2 where ...
将job
为manager
的员工的工资上涨10%
。
mysql> update emp_bark set sal = 1.1 * sal where job = 'manager';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp_bark;
+-------+----------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+----------+----------+------+------------+---------+---------+--------+
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 9999 | zhangsan | MANAGER | NULL | NULL | 3300.00 | 500.00 | 10 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+-------+----------+----------+------+------------+---------+---------+--------+
5 rows in set (0.00 sec)
13.3.3 delete
delete
可以根据条件删除数据。
语法格式:
delete from 表名 where ...
删除津贴为 500 的员工
mysql> delete from emp_bark where comm = 500;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from emp_bark;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
3 rows in set (0.00 sec)
删除津贴为null
的员工
mysql> delete from emp_bark where comm is null;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from emp_bark;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
1 row in set (0.00 sec)
13.4 创建表加入约束
常见的约束
- 非空约束,not null
- 唯一约束,unique
- 主键约束,primary key
- 外键约束,foreign key
- 自定义检查约束,check(不建议使用)(在 mysql 中现在还不支持)
13.4.1 非空约束,not null
非空约束,针对某个字段设置其值不为空,如:学生的姓名不能为空。
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date,
email varchar(30),
classes_id int(3)
)
insert into t_student(student_id, birthday, email, classes_id) values(1002, '1988-01-01', 'qqq@163.com', 10)
13.4.2 唯一约束,unique
唯一性约束,它可以使某个字段的值不能重复,如:email 不能重复:
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date,
email varchar(30) unique,
classes_id int(3)
);
insert into t_student(student_id, student_name , sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10);
查看约束:
mysql> use information_schema;
Database changed
mysql> select * from table_constraints where table_name = 't_student';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def | test | email | test | t_student | UNIQUE | YES |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
1 row in set (0.00 sec)
关于约束名称可以到table_constraints
中查询
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date,
email varchar(30) ,
classes_id int(3) ,
constraint email_unique unique(email)/*表级约束*/
)
复合主键(两个字段联合起来唯一)
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
email varchar(255),
unique(name,email) # 约束没有添加在列的后面,这种约束被称为表级约束。
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
Tips:
- 约束直接添加到列后面的,叫做列级约束;
- 约束没有添加在列的后面,这种约束被称为表级约束;
- 需要给多个字段联合起来添加某一个约束的时候,需要使用表级约束。
在mysql当中,如果一个字段同时被not null
和unique
约束的话,该字段自动变成主键字段。
13.4.3 主键约束,primary key
每个表应该具有主键,主键可以标识记录的唯一性,主键分为单一主键和复合(联合)主键,单一主键是由一个字段构成的,复合(联合)主键是由多个字段构成的
任何一张表都应该有主键,没有主键,表无效!
主键的特征:not null
+ unique
(主键值不能是NULL
,同时也不能重复!)
drop table if exists t_student;
create table t_student()
student_id int(10) primary key, /*列级约束*/
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date,
email varchar(30) ,
classes_id int(3)
);
insert into t_student(student_id, student_name , sex, birthday, email, classes_id) values(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10);
我们可以通过表级约束为约束起个名称:
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date,
email varchar(30) ,
classes_id int(3),
CONSTRAINT p_id PRIMARY key (student_id)
);
insert into t_student(student_id, student_name , sex, birthday, email, classes_id) values(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10);
注意:一张表,主键约束只能添加一个。
主键除了单一主键和复合主键之外,还可以这样进行分类:
- 自然主键:主键值是一个自然数,和业务没关系;
- 业务主键:主键值和业务紧密关联,例如拿银行卡账号做主键值。
在mysql当中,有一种机制,可以帮助我们自动维护一个主键值?
drop table if exists t_vip;
create table t_vip(
id int primary key auto_increment, /*auto_increment表示自增,从1开始,以1递增!*/
name varchar(255)
);
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
13.4.4 外键约束,foreign key
外键是为了维护表之间的关系,保证参照完整性。如果表中的某个字段为外键字段,那么该字段的值必须来源于参照的表的主键,如:emp
中的deptno
值必须来源于dept
表中的deptno
字段值。
例子:建立学生和班级表之间的连接。
首先建立班级表t_classes
drop table if exists t_classes;
create table t_classes(
classes_id int(3),
classes_name varchar(40),
constraint pk_classes_id primary key(classes_id)
);
在 t_student 中加入外键约束
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3),
constraint student_id_pk primary key(student_id),
constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)
);
向 t_student 中加入数据
insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10)
出现错误,因为在班级表中不存在班级编号为 10 班级,外键约束起到了作用。
注意:外键值可以为NULL。
存在外键的表就是子表,参照的表就是父表,所以存在一个父子关系,也就是主从关系,主表就是班级表,从表就是学生表。
\quad
十四、存储引擎
14.1 存储引擎的使用
存储引擎是MySQL中特有的一个术语,其它数据库中没有。(Oracle中有,但是不叫这个名字)
存储引擎这个名字高端大气上档次。实际上存储引擎是一个表存储/组织数据的方式。
不同的存储引擎,表存储数据的方式不同。
数据库中的各表均被(在创建表时)指定的存储引擎来处理。
服务器可用的引擎依赖于以下因素:
- MySQL 的版本
- 服务器在开发时如何被配置
- 启动选项
为了解当前服务器中有哪些存储引擎可用,可使用语句:
mysql> show engines\G
*************************** 1. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
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: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
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: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.01 sec)
在创建表时,可使用ENGINE
选项为CREATE TABLE
语句显式指定存储引擎。
create table tablename engine=MYISAM;
如果在创建表时没有显式指定存储引擎,则该表使用当前默认的存储引擎。默认的存储引擎可在my.ini
配置文件中使用default-storage-engine
选项指定。
现有表的存储引擎可使用alter table
语句来改变:
alter table tablename engine = INNODB;
14.2 常用的存储引擎
14.2.1 MyISAM 存储引擎
MyISAM 存储引擎是 MySQL 最常用的引擎。 它管理的表具有以下特征:
- 使用三个文件表示每个表:
- 格式文件:存储表结构的定义(mytable.frm)
- 数据文件:存储表行的内容(mytable.MYD)
- 索引文件:存储表上索引(mytable.MYI)
- 灵活的
AUTO_INCREMENT
字段处理 - 可被转换为压缩、只读表来节省空间
14.2.2 InnoDB 存储引擎
InnoDB 存储引擎是 MySQL 的缺省引擎。 它管理的表具有下列主要特征:
- 每个 InnoDB 表在数据库目录中以
.frm
格式文件表示 - InnoDB 表空间
tablespace
被用于存储表的内容 - 提供一组用来记录事务性活动的日志文件
- 用 COMMIT(提交)、SAVEPOINT 及 ROLLBACK(回滚)支持事务处理
- 提供全 ACID 兼容
- 在 MySQL 服务器崩溃后提供自动恢复
- 多版本(MVCC)和行级锁定
- 支持外键及引用的完整性,包括级联删除和更新
14.2.3 MEMORY 存储引擎
使用 MEMORY 存储引擎的表,其数据存储在内存中,且行的长度固定,这两个特点使得 MEMORY 存储引擎非常快。 MEMORY 存储引擎管理的表具有下列特征:
- 在数据库目录内,每个表均以
.frm
格式的文件表示。 - 表数据及索引被存储在内存中。
- 表级锁机制。
- 不能包含
TEXT
或BLOB
字段。
MEMORY 存储引擎以前被称为 HEAP 引擎。
14.3 选择合适的存储引擎
- MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM 表的另一种适用情形是使用压缩的只读表。
- 如果查询中包含较多的数据更新操作,应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
- 可使用MEMORY存储引擎来存储非永久需要的数据,或者是能够从基于磁盘的表中重新生成的数据。
\quad
十五、事务
15.1 概述
事务可以保证多个操作原子性,要么全成功,要么全失败。对于数据库来说事务保证批量的 DML 要么全成功,要么全失败。
事务具有四个特征(ACID):
- 原子性(Atomicity) 整个事务中的所有操作,必须作为一个单元全部完成(或全部取消)。
- 一致性(Consistency) 在事务开始之前与结束之后,数据库都保持一致状态。
- 隔离性(Isolation) 一个事务不会影响其他事务的运行。
- 持久性(Durability) 在事务完成以后,该事务对数据库所作的更改将持久地保存在数据库之中,并不会被回滚。
事务中存在一些概念:
- 事务(Transaction):一批操作(一组 DML)
- 开启事务(Start Transaction)
- 回滚事务(rollback)
- 提交事务(commit)
- SET AUTOCOMMIT:禁用或启用事务的自动提交模式
当执行 DML 语句是其实就是开启一个事务。
注意:只能回滚
insert
、delete
和update
语句,不能回滚select
(回滚 select 没有任何意义),对于create
、drop
、alter
这些无法回滚。
事务只对 DML 有效果。
15.2 事务的隔离级别
事务的隔离级别决定了事务之间可见的级别。 当多个客户端并发地访问同一个表时,可能出现下面的一致性问题:
- 脏读取(Dirty Read) 一个事务开始读取了某行数据,但是另外一个事务已经更新了此数据但没有能够及时提交,这就出现了脏读取。
- 不可重复读(Non-repeatable Read) 在同一个事务中,同一个读操作对同一个数据的前后两次读取产生了不同的结果,这就是不可重复读。
- 幻像读(Phantom Read) 幻像读是指在同一个事务中以前没有的行,由于其他事务的提交而出现的新行。
InnoDB 实现了四个隔离级别,用以控制事务所做的修改,并将修改通告至其它并发的事务:
- 读未提交(READ UMCOMMITTED) 允许一个事务可以看到其他事务未提交的修改。
- 读已提交(READ COMMITTED) 允许一个事务只能看到其他事务已经提交的修改,未提交的修改是不可见的。
- 可重复读(REPEATABLE READ) 确保如果在一个事务中执行两次相同的 SELECT 语句,都能得到相同的结果,不管其他事务是否提交这些修改。(银行总账) 该隔离级别为 InnoDB 的缺省设置。
- 串行化(SERIALIZABLE) 【序列化】 将一个事务与其他事务完全地隔离。
\quad
十六、索引
索引被用来快速找出在一个列上用一特定值的行。没有索引,MySQL就不得不首先以第一条记录开始,然后读完整个表直到它找出相关的行。表越大,花费时间越多。对于一个有序字段,可以运用二分查找,这就是为什么性能能得到本质上的提高。MYISAM 和 INNODB 都是用 B+Tree 作为索引结构。
如果未使用索引,查询就会执行全表扫描。
什么时候需要给字段添加索引:
- 表中该字段中的数据量庞大
- 经常被检索,经常出现在
where
子句中的字段 - 经常被 DML 操作的字段不建议添加索引
索引等同于一本书的目录。主键会自动添加索引,所以尽量根据主键查询效率较高。
建立索引的语法:
create unique index 索引名 on 表名(列名);
查看索引:
show index from 表名
删除索引:
drop index index_name on talbe_name
\quad
十七、视图
视图是一种根据查询(也就是 select
表达式)定义的数据库对象,用于获取想要看到和使用的局部数据。 视图有时也被成为“虚拟表”,其可以被用来从常规表(称为“基表”)或其他视图中查询数据。
相对于从基表中直接获取数据,视图有以下好处:
- 访问数据变得简单
- 可被用来对不同用户显示不同的表的内容
- 可用来协助适配表的结构以适应前端现有的应用程序
视图作用:
- 提高检索效率
- 隐藏表的实现细节【面向视图检索】
为什么使用视图?因为需求决定以上语句需要在多个地方使用,如果频繁的拷贝以上代码,会给维护带来成本,视图可以解决这个问题。
创建视图:
mysql> create view v_emp as select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno = e.deptno and e.deptno = 10;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from v_emp;
+--------+------------+---------+------------+--------+
| ename | dname | sal | hiredate | deptno |
+--------+------------+---------+------------+--------+
| MILLER | ACCOUNTING | 1300.00 | 1982-01-23 | 10 |
| KING | ACCOUNTING | 5000.00 | 1981-11-17 | 10 |
| CLARK | ACCOUNTING | 2450.00 | 1981-06-09 | 10 |
| MILLER | RESEARCH | 1300.00 | 1982-01-23 | 10 |
| KING | RESEARCH | 5000.00 | 1981-11-17 | 10 |
| CLARK | RESEARCH | 2450.00 | 1981-06-09 | 10 |
| MILLER | SALES | 1300.00 | 1982-01-23 | 10 |
| KING | SALES | 5000.00 | 1981-11-17 | 10 |
| CLARK | SALES | 2450.00 | 1981-06-09 | 10 |
| MILLER | OPERATIONS | 1300.00 | 1982-01-23 | 10 |
| KING | OPERATIONS | 5000.00 | 1981-11-17 | 10 |
| CLARK | OPERATIONS | 2450.00 | 1981-06-09 | 10 |
+--------+------------+---------+------------+--------+
12 rows in set (0.00 sec)
mysql> create view v_dept_avg_sal_grade as select a.deptno, a.avg_sal, b.grade from (select deptno, avg(sal) avg_sal from emp group by deptno) a, salgrade b where a.avg_sal between b.losal and b.hisal;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from v_dept_avg_sal_grade;
+--------+-------------+-------+
| deptno | avg_sal | grade |
+--------+-------------+-------+
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)
注意:mysql 不支持子查询创建视图。
修改视图:
mysql> alter view v_emp as select ename,dname,sal,hiredate,e.deptno from emp e, dept d where e.deptno = 20;
Query OK, 0 rows affected (0.01 sec)
删除视图:
mysql> drop view if exists v_emp;
Query OK, 0 rows affected (0.00 sec)
\quad
十八、数据库设计的三范式
18.1 第一范式
数据库表中不能出现重复记录,每个字段是原子性的不能再分。
关于第一范式,每一行必须唯一,也就是每个表必须有主键,这是我们数据库设计的最基本要求,主要通常采用数值型或定长字符串表示,关于列不可再分,应该根据具体的情况来决定。如联系方式,为了开发上的便利行可能就采用一个字段了。
18.2 第二范式
第二范式是建立在第一范式基础上的,另外要求所有非主键字段完全依赖主键,不能产生部分依赖。
18.3 第三范式
建立在第二范式基础上的,非主键字段不能传递依赖于主键字段。(不要产生传递依赖)
如果一个表是单一主键,那么它就符合第二范式,部分依赖和主键有关系。
18.4 总结
- 第一范式:有主键,具有原子性,字段不可分割;
- 第二范式:完全依赖,没有部分依赖;
- 第三范式:没有传递依赖 。
数据库设计尽量遵循三范式,但是还是根据实际情况进行取舍,有时可能会拿冗余换速度,最终用目的要满足客户需求。