MySQL常用操作命令
1、2、MySQL的安装与基本操作
概念
数据库：数据的仓库
Oracle、SQL server、MySQL(小、免费、开源)
表格
行
列
学生信息
| 学号 | 姓名 | 性别 |
|---|---|---|
字段：一列的含义，比如“学号”字段
记录：一行的完整歇息，称为一个记录
安装
安装mariadb
yum -y install mariadb mariadb-server
systemctl start mariadb
systemctl enable mariadb
mysql_secure_installation #初始化数据库，设置数据库密码
#这里推荐安装一个mariadb好用的客户端mycli，而不是它自带的mysql
yum -y install python3
pip3 install mycli -i https://mirrors.aliyun.com/pypi/simple/
yum -y install mariadb-server mariadb #安装数据库
systemctl start mariadb #启动数据库服务
systemctl enable mariadb #设置数据库开机启动
mysql_secure_installation #初始化数据库，设置数据库密码，其他的选项暂时默认（直接敲Enter键）
grant all privileges on *.* to root@'%' identified by 'password';
grant all privileges on *.* to root@'%' identified by‘password‘;
#外网访问数据库授权
select User,Host,Password from mysql.user; #查看用户权限
systemctl restart mariadb.service #重启数据库服务移除MySQL
删除mariadb
yum remove mariadb*
rm /var/lib/mysql -rf登录mariadb，使用客户端工具mysql连接
mysql -u用户 -p密码
mysql -uroot -p123456
常用选项：
-u：user 指定登录用户
-p：password 指定登录用户密码
-h：host 指定登录主机，默认127.0.0.1
-p：port 指定数据库连接端口3306
-D：Database 指定数据库查看都有哪些数据库
show databases;
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [lxw]> show database;
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version
for the right syntax to use near 'database' at line 1
# 当不够清晰的时候，可以百度一下错误码ERROR 1064自己创建一个数据库
create database 数据库的名称; 名称由自己起名。
MariaDB [(none)]> create database lxw;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lxw |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)删除一个数据库
drop database 数据库名称;
MariaDB [(none)]> drop database mysql;
Query OK, 24 rows affected, 2 warnings (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lxw |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> drop dateabase 1;
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version
for the right syntax to use near 'dateabase 1' at line 1
#删除的数据库不存在选择要操作的数据库
use 数据库名称 只有use命令不是SQL命令，是MySQL自己的命令
MariaDB [(none)]> use lxw
Database changed
MariaDB [lxw]>
MariaDB [lxw]> create database lxw;
ERROR 1007 (HY000): Can't create database 'lxw'; database exists
#当前名称的数据库已经存在，创建数据库失败。
'
MariaDB [(none)]> use 1
ERROR 1049 (42000): Unknown database '1'
#没有数据库13、 MySQL表的操作
sql 语句以;结尾 汉 译 英
查看表
查看当前数据库都有哪些表
show tables;创建表
如何创建一张数据库的表
create table 表名(字段名称 字段的类型,字段名称 字段类型...) 创建一张学生表
| 学号 | 姓名 | 性别 | 年级 | 年龄 | 入学日期 |
|---|---|---|---|---|---|
create table student(num int(6),
-> name varchar(10),
-> sex varchar(2),
-> age int,schooldate date); int类型长度可以不规定，默认长度为11，varchar长度必须规定，如果不规定会报错。
创建表成功
查看下表中的数据呢
selcet * from 表名
mariadb root@localhost:test> select * from student;
+-----+------+-----+-----+------------+
| num | name | sex | age | schooldate |
+-----+------+-----+-----+------------+查看表的结构
如何查看一个表的结构
desc 表名;
mariadb root@localhost:test> desc student;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| num | int(6) | YES | | <null> | |
| name | varchar(10) | YES | | <null> | |
| sex | varchar(2) | YES | | <null> | |
| age | int(11) | YES | | <null> | |
| schooldate | date | YES | | <null> | |
+------------+-------------+------+-----+---------+-------+
#age没设置长度，默认为11删除表
表创建错了，想要删除这张表
drop table 表名;
mariadb root@localhost:test> drop table student;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 0 rows affected
Time: 0.002s创建一张表
Book
书号：
书名：
发行日期：
价格：数据类型中的double长度控制可以是两个长度 一个是总长度，一个是小数点后的长度
create table book(num int,name varchar(10),date1 date,price double(5,2))| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1字节 | (-128,127) | (0,255) | 小整数值 |
| SMALLINT | 2字节 | (-32 768,32 767) | (0,65 535) | 大整数值 |
| MEDIUMINT | 3字节 | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
| INT或INTEGER | 4字节 | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
| BIGINT | 8字节 | 极大整数值 | ||
| FLOAT | 4字节 | 单精度浮点数 | ||
| DOUBLE | 8字节 | 双精度浮点数 |
INT在日常中能满足90%上的需求
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-255字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过255个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
CHAR：插入数据的时候，不到255个字节，内存当中占用255字节
VARCHAR：插入数据的时候，不到255个字节，内存占用以实际插入数据的长度为准
TEXT：
LONGTEXT：
| 类型 | 大小 | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3字节 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3字节 | "-838:59:59"/"838:59:59" | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1字节 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8字节 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 8字节 | 1970-01-01 00:00:00/2037年某时 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
4、 MySQL表结构操作
book表中还有一个库存字段
能不能对表的结构进行修改
再原表的基础上
增加字段
修改字段
删除字段
增加字段
alter table 表名 add 字段名 字段类型
mariadb root@localhost:test> alter table book add count int
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 0 rows affected
Time: 0.006s
'
mariadb root@localhost:test> desc book;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num | int(11) | YES | | <null> | |
| name | varchar(10) | YES | | <null> | |
| date1 | date | YES | | <null> | |
| price | double(5,2) | YES | | <null> | |
| count | int(11) | YES | | <null> | |
+-------+-------------+------+-----+---------+-------+修改字段
alter table 表名 modify 字段名称 字段类型;
mariadb root@localhost:test> alter table book modify price in
-> t;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 0 rows affected
Time: 0.009s
mariadb root@localhost:test> desc bookl
(1146, "Table 'test.bookl' doesn't exist")
"
mariadb root@localhost:test> desc book;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num | int(11) | YES | | <null> | |
| name | varchar(10) | YES | | <null> | |
| date1 | date | YES | | <null> | |
| price | int(11) | YES | | <null> | |
| count | int(11) | YES | | <null> | |
+-------+-------------+------+-----+---------+-------+删除字段
alter table 表名 drop 字段名称;
mariadb root@localhost:test> alter table book drop count;
You''re about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 0 rows affected
Time: 0.007s
mariadb root@localhost:test> desc book;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num | int(11) | YES | | <null> | |
| name | varchar(10) | YES | | <null> | |
| date1 | date | YES | | <null> | |
| price | int(11) | YES | | <null> | |
+-------+-------------+------+-----+---------+-------+5、 MySQL数据库表的插入
向表中插入数据
insert into 表名(想插入的字段名称,...) values(想插入的字段的值)
insert into 表名 values(表中所有字段的值)
mariadb root@localhost:test> insert into student(num) vlaues(
-> 1);
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'vlaues(1)' at line 1")
mariadb root@localhost:test> insert into student(num) values(
-> 1);
Query OK, 1 row affected
Time: 0.002s
mariadb root@localhost:test> select * from student;
+-----+--------+--------+--------+------------+
| num | name | sex | age | schooldate |
+-----+--------+--------+--------+------------+
| 1 | <null> | <null> | <null> | <null> |
+-----+--------+--------+--------+------------+
mariadb root@localhost:test> insert into student(num,name) va
-> lues(2,tom);
(1054, "Unknown column 'tom' in 'field list'")
mariadb root@localhost:test> insert into student(num,name) va
-> lues(2,'tom');
Query OK, 1 row affected
Time: 0.002s
#name是字符串类型的值，需要加上''
mariadb root@localhost:test> select * from student;
+-----+--------+--------+--------+------------+
| num | name | sex | age | schooldate |
+-----+--------+--------+--------+------------+
| 1 | <null> | <null> | <null> | <null> |
| 2 | tom | <null> | <null> | <null> |
+-----+--------+--------+--------+------------+
mariadb root@localhost:test> insert into student(num,name,sch
-> ooldate) values(3,'jerry','1998-
-> 01-28');
Query OK, 1 row affected
Time: 0.002s
mariadb root@localhost:test> select * from student;
+-----+--------+--------+--------+------------+
| num | name | sex | age | schooldate |
+-----+--------+--------+--------+------------+
| 1 | <null> | <null> | <null> | <null> |
| 2 | tom | <null> | <null> | <null> |
| 3 | jerry | <null> | <null> | 1998-01-28 |
+-----+--------+--------+--------+------------+
mariadb root@localhost:test> insert into student(num,name,sex
-> ,schooldate) values(4,'jerry','m
-> ','1998-01-28');
Query OK, 1 row affected
Time: 0.002s
mariadb root@localhost:test> select * from student;
+-----+--------+--------+--------+------------+
| num | name | sex | age | schooldate |
+-----+--------+--------+--------+------------+
| 1 | <null> | <null> | <null> | <null> |
| 2 | tom | <null> | <null> | <null> |
| 3 | jerry | <null> | <null> | 1998-01-28 |
| 4 | jerry | m | <null> | 1998-01-28 |
+-----+--------+--------+--------+------------+省略字段名称的时候，表示我要再这张表的所有字段都进行插入，values必须都要对应上所有的字段。
mariadb root@localhost:test> insert into student values('jerr
-> y','m','1998-01-28');
(1136, "Column count doesn't match value count at row 1") #列的数目和值的数目不对等
mariadb root@localhost:test> insert into student(num,name,sex
-> ,schooldate) values(4,'jerry','m
-> ','1998-01-28');
Query OK, 1 row affected
Time: 0.002s
mariadb root@localhost:test> select * from student;
+-----+--------+--------+--------+------------+
| num | name | sex | age | schooldate |
+-----+--------+--------+--------+------------+
| 1 | <null> | <null> | <null> | <null> |
| 2 | tom | <null> | <null> | <null> |
| 3 | jerry | <null> | <null> | 1998-01-28 |
| 4 | jerry | m | <null> | 1998-01-28 |
| 4 | jerry | m | <null> | 1998-01-28 |
+-----+--------+--------+--------+------------+插入的信息超过限制
mariadb root@localhost:test> insert into student(sex) values(
-> 'aaa');
Query OK, 1 row affected
Time: 0.002s
mariadb root@localhost:test> insert into student(sex) values(
-> 'aaaaaa');
Query OK, 1 row affected
Time: 0.002s
mariadb root@localhost:test> select * from student;
+--------+--------+--------+--------+------------+
| num | name | sex | age | schooldate |
+--------+--------+--------+--------+------------+
| 1 | <null> | <null> | <null> | <null> |
| 2 | tom | <null> | <null> | <null> |
| 3 | jerry | <null> | <null> | 1998-01-28 |
| 4 | jerry | m | <null> | 1998-01-28 |
| 4 | jerry | m | <null> | 1998-01-28 |
| <null> | <null> | aa | <null> | <null> |
| <null> | <null> | aa | <null> | <null> |
+--------+--------+--------+--------+------------+创建三张表为后续学习做准备
第一张表 EMP表
#创建表并插入数据的命令
create table EMP(EMPNO int(4),ENAME varchar(10),JOB varchar(9),MGR int(4),HIREDATE date,SAL double(7,2),COMM double(7,2),DEPTNO int(2));
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7369,'SMITH','CLERK',7902,'1980-12-17',800.00,null,20);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600.00,300.00,30);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7521,'WARD','SALESMAN',7698,'1981-2-22',1250.00,500.00,30);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7566,'JONES','MANAGER',7839,'1981-4-2',2975.00,null,20);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250.00,1400.00,30);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850.00,null,30);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7782,'CLARK','MANAGER',7839,'1981-6-9',2450.00,null,10);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7788,'SCOTT','ANALYST',7566,'1987-4-19',3000.00,null,20);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7839,'KING','PRESIDENT',null,'1981-11-17',5000.00,null,10);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500.00,0.00,30);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7876,'ADAMS','CLERK',7788,'1987-5-23',1100.00,null,20);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7900,'JAMES','CLERK',7698,'1981-12-3',950.00,null,30);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7902,'FORD','ANALYST',7566,'1981-12-3',3000.00,null,20);
insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7934,'MILLER','CLERK',7782,'1982-1-23',1300.00,null,10);
mariadb root@localhost:test> create table EMP(EMPNO int(4),EN
-> AME varchar(10),JOB varchar(9),M
-> GR int(4),HIREDATE date,SAL doub
-> le(8,2),COMM double(8,2),DEPTON
-> int(2));
Query OK, 0 rows affected
Time: 0.005s
mariadb root@localhost:test> desc EMP;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int(4) | YES | | <null> | |
| ENAME | varchar(10) | YES | | <null> | |
| JOB | varchar(9) | YES | | <null> | |
| MGR | int(4) | YES | | <null> | |
| HIREDATE | date | YES | | <null> | |
| SAL | double(7,2) | YES | | <null> | |
| COMM | double(7,2) | YES | | <null> | |
| DEPTNO | int(2) | YES | | <null> | |
+----------+-------------+------+-----+---------+-------+
......
mariadb root@localhost:test> select * from EMP;
+-------+--------+-----------+--------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+--------+------------+--------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.0 | <null> | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.0 | 300.0 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.0 | 500.0 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.0 | <null> | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.0 | 1400.0 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.0 | <null> | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.0 | <null> | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.0 | <null> | 20 |
| 7839 | KING | PRESIDENT | <null> | 1981-11-17 | 5000.0 | <null> | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.0 | 0.0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.0 | <null> | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.0 | <null> | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.0 | <null> | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.0 | <null> | 10 |
+-------+--------+-----------+--------+------------+--------+--------+--------+第二张表 dept表结构
dept表结构
DEPTNO int(2)
DNAME VARCHAR(14)
LOC VARCHAR(13)
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
#创建并插入数据的命令
create table dept(DEPTNO int(2),DNAME VARCHAR(14),LOC VARCHAR(13));
insert into dept(DEPTNO,DNAME,LOC) values(10,'ACCOUNTING','NEW YORk');
insert into dept(DEPTNO,DNAME,LOC) values(20,'RESEARCH','DALLAS');
insert into dept(DEPTNO,DNAME,LOC) values(40,'OPERATIONS','BOSTON');
mariadb root@localhost:test> create table dept(DEPTNO int(2),DNAME VARCHAR(14),L
-> OC VARCHAR(13));
Query OK, 0 rows affected
Time: 0.004s
mariadb root@localhost:test> show tables;
+----------------+
| Tables_in_test |
+----------------+
| EMP |
| book |
| dept |
| student |
+----------------+
......
mariadb root@localhost:test> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORk |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+第三张表 SALGRADE表结构
GRADE int
LOSAL int
HISAL int
1, 700,1200
2, 1201,1400
3, 1401,2000
4, 2001,3000
5, 3001,9999
#创建并插入数据的命令
create table SALGRADE(GRADE int,LOSAL int,HISAL int);
insert into SALGRADE(GRADE,LOSAL,HISAL) values(1,700,1200);
insert into SALGRADE(GRADE,LOSAL,HISAL) values(2,1201,1400);
insert into SALGRADE(GRADE,LOSAL,HISAL) values(3,1401,2000);
insert into SALGRADE(GRADE,LOSAL,HISAL) values(4,2001,3000);
insert into SALGRADE(GRADE,LOSAL,HISAL) values(4,3001,9999);
mariadb root@localhost:test> create table SALGRADE(GRADE int,LOSAL int,HISAL int
-> );
Query OK, 0 rows affected
Time: 0.004s
mariadb root@localhost:test> desc SALGRADE;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| GRADE | int(11) | YES | | <null> | |
| LOSAL | int(11) | YES | | <null> | |
| HISAL | int(11) | YES | | <null> | |
+-------+---------+------+-----+---------+-------+
......
mariadb root@localhost:test> select * from SALGRADE;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 4 | 3001 | 9999 |
+-------+-------+-------+6、 MySQL删除插入操作
删除插入
删除的sql语句 筛选行的删除
#清空表
delete from 表名;
#删除哪几条记录
delete from 表名 where 条件表达式;修改表中的记录
update 表名 set 字段名=新的字段值,......where 条件表达式
mariadb root@localhost:test> update student set sex = 'm'
-> ;
You''re about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 4 rows affected
Time: 0.002s
mariadb root@localhost:test> update student set sex = 'f'
-> where num = 3;
Query OK, 1 row affected
Time: 0.002s
mariadb root@localhost:test> select * from student;
+--------+--------+-----+--------+------------+
| num | name | sex | age | schooldate |
+--------+--------+-----+--------+------------+
| 2 | tom | m | <null> | <null> |
| 3 | jerry | f | <null> | 1998-01-28 |
| 4 | jerry | m | <null> | 1998-01-28 |
| 4 | jerry | m | <null> | 1998-01-28 |
| <null> | <null> | m | <null> | <null> |
| <null> | <null> | m | <null> | <null> |
+--------+--------+-----+--------+------------+
mariadb root@localhost:test> update student set sex = 'm'
-> ,name = 'cat' where num = 3;
->
Query OK, 1 row affected
Time: 0.002s
mariadb root@localhost:test> select * from student;
+--------+--------+-----+--------+------------+
| num | name | sex | age | schooldate |
+--------+--------+-----+--------+------------+
| 2 | tom | m | <null> | <null> |
| 3 | cat | m | <null> | 1998-01-28 |
| 4 | jerry | m | <null> | 1998-01-28 |
| 4 | jerry | m | <null> | 1998-01-28 |
| <null> | <null> | m | <null> | <null> |
| <null> | <null> | m | <null> | <null> |
+--------+--------+-----+--------+------------+7、 MySQL约束
MySQL约束
不允许去做某些事情
唯一约束：创建表的时候就加进去unique
mariadb root@localhost:test> create table stu(num int,nam
-> e varchar(11));
Query OK, 0 rows affected
Time: 0.006s
mariadb root@localhost:test> insert into stu values(1,'to
-> m');
Query OK, 1 row affected
Time: 0.001s
mariadb root@localhost:test> select * from stu;
+-----+------+
| num | name |
+-----+------+
| 1 | tom |
+-----+------+
mariadb root@localhost:test> insert into stu values(1,'to
-> m');
Query OK, 1 row affected
Time: 0.001s
mariadb root@localhost:test> select * from stu;
+-----+------+
| num | name |
+-----+------+
| 1 | tom |
| 1 | tom |
+-----+------+
#当有两个相同数据时如何删除一个唯一约束
unique
mariadb root@localhost:test> create table stu(num int unique,name varchar(11));
mariadb root@localhost:test> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num | int(11) | YES | UNI | <null> | |#Key变为UNI，num字段唯一不可重复
| name | varchar(11) | YES | | <null> | |
+-------+-------------+------+-----+---------+-------+
mariadb root@localhost:test> insert stu values(1,'tom');
Query OK, 1 row affected
Time: 0.001s
mariadb root@localhost:test> insert stu values(1,'tom');
(1062, "Duplicate entry '1' for key 'num'") #1已经存在，不可重复
mariadb root@localhost:test> insert stu values(2,'tom');
Query OK, 1 row affected
Time: 0.001s
mariadb root@localhost:test> select * from stu;
+-----+------+
| num | name |
+-----+------+
| 1 | tom |
| 2 | tom |
+-----+------+
mariadb root@localhost:test> select * from stu;
+--------+------+
| num | name |
+--------+------+
| 1 | tom |
| 2 | tom |
| <null> | tom |
+--------+------+null 任何的null都不等于另一个null
mariadb root@localhost:test> insert into stu values(null,'tom');
Query OK, 1 row affected
Time: 0.001s
mariadb root@localhost:test> select * from stu;
+--------+------+
| num | name |
+--------+------+
| 1 | tom |
| 2 | tom |
| <null> | tom |
| <null> | tom |
+--------+------+非空约束
not null
mariadb root@localhost:test> create table stu(num int notnull,name varchar(11));
mariadb root@localhost:test> insert into stu values(null,'tom');
(1048, "Column 'num' cannot be null") #num列不允许为空同一个字段加多个约束
不需要,
mariadb root@localhost:test> create table stu(num int unique not null,name varchar(11));
mariadb root@localhost:test> insert into stu values(1,'tom');
Query OK, 1 row affected
Time: 0.001s
mariadb root@localhost:test> insert into stu values(1,'to m');
(1062, "Duplicate entry '1' for key 'num'")
mariadb root@localhost:test> insert into stu values(null,'tom');
(1048, "Column 'num' cannot be null")主键约束
非空约束和唯一约束的组合，我们称之为主键约束
主键约束
primary key
mariadb root@localhost:test> create table stu(num int primary key,name varchar(11));
mariadb root@localhost:test> insert into stu values(1,'tom');
Query OK, 1 row affected
Time: 0.002s
mariadb root@localhost:test> insert into stu values(1,'tom');
(1062, "Duplicate entry '1' for key 'PRIMARY'")
mariadb root@localhost:test> insert into stu values(null,'tom');
(1048, "Column 'num' cannot be null")MySQL的自动增长策略
一般数字类型主键配合MySQL的自动增长策略
当我们使用了主键约束的时候，如何避免开我表中已经存在的主键
主键由mysql帮我们生成
MySQL的自动增长策略
primary key auto_increment
mariadb root@localhost:test> create table stu(num int primary key auto_increment,namevarchar(11));
mariadb root@localhost:test> insert into stu(name) values('tom');
Query OK, 1 row affected
Time: 0.002s
mariadb root@localhost:test> insert into stu(name) values('tom');
Query OK, 1 row affected
Time: 0.002s
mariadb root@localhost:test> select * from stu;
+-----+------+
| num | name |
+-----+------+
| 1 | tom |
| 2 | tom |
+-----+------+
mariadb root@localhost:test> delete from stu where num = 2;
You''re about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 1 row affected
Time: 0.002s
mariadb root@localhost:test> insert into stu(name) values('tom');
Query OK, 1 row affected
Time: 0.001s
mariadb root@localhost:test> select * from stu;
+-----+------+
| num | name |
+-----+------+
| 1 | tom |
| 3 | tom |
+-----+------+外键约束
学生表 班级号
班级表
mariadb root@localhost:test> create table clazz(num int,n
-> ame varchar(11));
Query OK, 0 rows affected
Time: 0.004s
mariadb root@localhost:test> create table stu(num int,nam
-> e varchar(11),clazznum int);
->
Query OK, 0 rows affected
Time: 0.004s
mariadb root@localhost:test> desc clazz;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num | int(11) | YES | | <null> | |
| name | varchar(11) | YES | | <null> | |
+-------+-------------+------+-----+---------+-------+
mariadb root@localhost:test> desc stu;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| num | int(11) | YES | | <null> | |
| name | varchar(11) | YES | | <null> | |
| clazznum | int(11) | YES | | <null> | |
+----------+-------------+------+-----+---------+-------+
mariadb root@localhost:test> insert into clazz values(1,'
-> one');
Query OK, 1 row affected
Time: 0.002s
mariadb root@localhost:test> insert into stu values(1,'to
-> m',1);
Query OK, 1 row affected
Time: 0.001s
mariadb root@localhost:test> insert into stu values(2,'ca
-> t',1);
Query OK, 1 row affected
Time: 0.001s
mariadb root@localhost:test> insert into stu values(2,'je
-> rry',1);
Query OK, 1 row affected
Time: 0.001s
mariadb root@localhost:test> select * from stu;
+-----+-------+----------+
| num | name | clazznum |
+-----+-------+----------+
| 1 | tom | 1 |
| 2 | cat | 1 |
| 2 | jerry | 2 | #2班是一个错误数据，这个班级号必须在clazznum中存在
+-----+-------+----------+ #如何保证数据的正确性如何保证数据的这种正确性
外键约束，约束的是这个字段里的值必须是另一张表中存在的值。
因为要用到另一张表，所以不能直接加
单独加
MySQL中外键必须是另一张表的主键
mariadb root@localhost:test> select * from clazz;
+-----+------+
| num | name |
+-----+------+
| 1 | one |
+-----+------+
mariadb root@localhost:test> drop table stu;
You''re about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 0 rows affected
Time: 0.002s
mariadb root@localhost:test> create table stu(num int pri
-> mary key auto_increment,name
-> varchar(11),clazznum int foreign key);
#给哪个字段加，参考哪一张表的哪一个字段
mariadb root@localhost:test> create table stu(num int pri
-> mary key auto_increment,name
-> varchar(11),clazznum int,fo
-> reign key(clazznum) referenc
-> es clazz(num));
(1005, "Can't create table 'test.stu' (errno: 150)") #MySQL中外键必须是另一张表的主键
mariadb root@localhost:test> drop table clazz; #删除clazz表重新创建
You''re about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 0 rows affected
Time: 0.002s
mariadb root@localhost:test> create table clazz(num int p
-> rimary key auto_increment,na
-> me varchar(11));
Query OK, 0 rows affected
Time: 0.004s
mariadb root@localhost:test> create table stu(num int pri
-> mary key auto_increment,name
-> varchar(11),clazznum int,fo
-> reign key(clazznum) referenc
-> es clazz(num));
Query OK, 0 rows affected
Time: 0.004s
mariadb root@localhost:test> insert into clazz values(null,'tom');
Query OK, 1 row affected
Time: 0.001s
mariadb root@localhost:test> select * from clazz;
+-----+------+
| num | name |
+-----+------+
| 1 | tom |
+-----+------+
mariadb root@localhost:test> insert into stu values(null,'tom',1);
Query OK, 1 row affected
Time: 0.002s
mariadb root@localhost:test> select * from stu;
+-----+------+----------+
| num | name | clazznum |
+-----+------+----------+
| 1 | tom | 1 |
+-----+------+----------+
mariadb root@localhost:test> select * from stu;
+-----+------+----------+
| num | name | clazznum |
+-----+------+----------+
| 1 | tom | 1 |
+-----+------+----------+
mariadb root@localhost:test> insert into stu values(null,'tom',2);
(1452, 'Cannot add or update a child row: a foreign key constraint fails (`test`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`clazznum`) REFERENCES `clazz` (`num`))')
###报错因为有了外键约束，num的值只有1，没有2
mariadb root@localhost:test> insert into clazz values(null,'two');
Query OK, 1 row affected
Time: 0.002s
mariadb root@localhost:test> select * from stu;
+-----+------+----------+
| num | name | clazznum |
+-----+------+----------+
| 1 | tom | 1 |
+-----+------+----------+
mariadb root@localhost:test> select * from num;
(1146, "Table 'test.num' doesn't exist")
mariadb root@localhost:test> select * from clazz;
+-----+------+
| num | name |
+-----+------+
| 1 | tom |
| 2 | two |
+-----+------+
mariadb root@localhost:test> insert into stu values(null,'tom',2);
Query OK, 1 row affected
Time: 0.002sOracle数据库中check约束，在mysql中不起任何作用
8、 MySQL基本查询操作
查询语句
select * from 表名
#查询出该表名下的所有数据
*代表了所有字段了解查询语句的格式
EMP
dept
salgrade
select [字段列表,表达式,函数] from 表名字段列表
select 字段列表[字段1,字段2，...] from 表名
#当我们需要将表中的所有数据都查询出来的时候，那么这个时候我们只需要将字段列表替换成*即可
mariadb root@localhost:test> select * from EMP;
+-------+--------+-----------+--------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+--------+------------+--------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.0 | <null> | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.0 | 300.0 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.0 | 500.0 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.0 | <null> | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.0 | 1400.0 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.0 | <null> | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.0 | <null> | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.0 | <null> | 20 |
| 7839 | KING | PRESIDENT | <null> | 1981-11-17 | 5000.0 | 10.0 | <null> |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.0 | 0.0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.0 | <null> | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.0 | <null> | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.0 | <null> | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.0 | <null> | 10 |
+-------+--------+-----------+--------+------------+--------+--------+--------+
mariadb root@localhost:test> select SAL from EMP;
+--------+
| SAL |
+--------+
| 800.0 |
| 1600.0 |
| 1250.0 |
| 2975.0 |
| 1250.0 |
| 2850.0 |
| 2450.0 |
| 3000.0 |
| 5000.0 |
| 1500.0 |
| 1100.0 |
| 950.0 |
| 3000.0 |
| 1300.0 |
+--------+
mariadb root@localhost:test> select ENAME,SAL from EMP;
+--------+--------+
| ENAME | SAL |
+--------+--------+
| SMITH | 800.0 |
| ALLEN | 1600.0 |
| WARD | 1250.0 |
| JONES | 2975.0 |
| MARTIN | 1250.0 |
| BLAKE | 2850.0 |
| CLARK | 2450.0 |
| SCOTT | 3000.0 |
| KING | 5000.0 |
| TURNER | 1500.0 |
| ADAMS | 1100.0 |
| JAMES | 950.0 |
| FORD | 3000.0 |
| MILLER | 1300.0 |
+--------+--------+
mariadb root@localhost:test> select ENAME,HIREDATE from EMP;
+--------+------------+
| ENAME | HIREDATE |
+--------+------------+
| SMITH | 1980-12-17 |
| ALLEN | 1981-02-20 |
| WARD | 1981-02-22 |
| JONES | 1981-04-02 |
| MARTIN | 1981-09-28 |
| BLAKE | 1981-05-01 |
| CLARK | 1981-06-09 |
| SCOTT | 1987-04-19 |
| KING | 1981-11-17 |
| TURNER | 1981-09-08 |
| ADAMS | 1987-05-23 |
| JAMES | 1981-12-03 |
| FORD | 1981-12-03 |
| MILLER | 1982-01-23 |
+--------+------------+表达式
算术表达式
+
-
*
/
%
select 表达式[算数表达式] from 表名
#查询年薪+
mariadb root@localhost:test> select ENAME,SAL*12 from EMP;
+--------+---------+
| ENAME | SAL*12 |
+--------+---------+
| SMITH | 9600.0 |
| ALLEN | 19200.0 |
| WARD | 15000.0 |
| JONES | 35700.0 |
| MARTIN | 15000.0 |
| BLAKE | 34200.0 |
| CLARK | 29400.0 |
| SCOTT | 36000.0 |
| KING | 60000.0 |
| TURNER | 18000.0 |
| ADAMS | 13200.0 |
| JAMES | 11400.0 |
| FORD | 36000.0 |
| MILLER | 15600.0 |
+--------+---------+
#减100元工资
mariadb root@localhost:test> select ENAME,SAL-100 from EMP;
+--------+---------+
| ENAME | SAL-100 |
+--------+---------+
| SMITH | 700.0 |
| ALLEN | 1500.0 |
| WARD | 1150.0 |
| JONES | 2875.0 |
| MARTIN | 1150.0 |
| BLAKE | 2750.0 |
| CLARK | 2350.0 |
| SCOTT | 2900.0 |
| KING | 4900.0 |
| TURNER | 1400.0 |
| ADAMS | 1000.0 |
| JAMES | 850.0 |
| FORD | 2900.0 |
| MILLER | 1200.0 |
+--------+---------+
#除法
mariadb root@localhost:test> select ENAME,SAL/100 from EMP;
+--------+---------+
| ENAME | SAL/100 |
+--------+---------+
| SMITH | 8.0 |
| ALLEN | 16.0 |
| WARD | 12.5 |
| JONES | 29.75 |
| MARTIN | 12.5 |
| BLAKE | 28.5 |
| CLARK | 24.5 |
| SCOTT | 30.0 |
| KING | 50.0 |
| TURNER | 15.0 |
| ADAMS | 11.0 |
| JAMES | 9.5 |
| FORD | 30.0 |
| MILLER | 13.0 |
+--------+---------+
#取余
mariadb root@localhost:test> select ENAME,SAL%100 from EMP;
+--------+---------+
| ENAME | SAL%100 |
+--------+---------+
| SMITH | 0.0 |
| ALLEN | 0.0 |
| WARD | 50.0 |
| JONES | 75.0 |
| MARTIN | 50.0 |
| BLAKE | 50.0 |
| CLARK | 50.0 |
| SCOTT | 0.0 |
| KING | 0.0 |
| TURNER | 0.0 |
| ADAMS | 0.0 |
| JAMES | 50.0 |
| FORD | 0.0 |
| MILLER | 0.0 |
+--------+---------+去重
select distinct DEPTNO from EMP
mariadb root@localhost:test> select DEPTNO from EMP;
+--------+
| DEPTNO |
+--------+
| 20 |
| 30 |
| 30 |
| 20 |
| 30 |
| 30 |
| 10 |
| 20 |
| <null> |
| 30 |
| 20 |
| 30 |
| 20 |
| 10 |
+--------+
mariadb root@localhost:test> select distinct DEPTNO from EMP;
+--------+
| DEPTNO |
+--------+
| 20 |
| 30 |
| 10 |
| <null> |
+--------+
mariadb root@localhost:test> select DEPTNO from dept
+--------+
| DEPTNO |
+--------+
| 10 |
| 20 |
| 30 |
| 40 |
+--------+函数
先放下函数，后面
9、 条件查询
where关键字
where 条件表达式
where 条件表达式等值比较 大于 小于 大于等于 小于等于 不等于
#数值类型
mariadb root@localhost:test> select * from EMP where DEPTNO =1 0;
+-------+--------+-----------+--------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+--------+------------+--------+--------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.0 | <null> | 10 |
| 7839 | KING | PRESIDENT | <null> | 1981-11-17 | 5000.0 | <null> | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.0 | <null> | 10 |
+-------+--------+-----------+--------+------------+--------+--------+--------+
#字符串类型
mariadb root@localhost:test> select * from EMP where ENAME = 'CLARK';
+-------+-------+---------+------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+--------+--------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.0 | <null> | 10 |
+-------+-------+---------+------+------------+--------+--------+--------+
#日期类型
mariadb root@localhost:test> select * from EMP where HIREDATE = '1983-1-12';
+-------+-------+-----+-----+----------+-----+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----+-----+----------+-----+------+--------+
#大于，小于，大于等于，小于等于
#大于
mariadb root@localhost:test> select * from EMP where SAL > 1600;
+-------+-------+-----------+--------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+--------+------------+--------+--------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.0 | <null> | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.0 | <null> | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.0 | <null> | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.0 | <null> | 20 |
| 7839 | KING | PRESIDENT | <null> | 1981-11-17 | 5000.0 | <null> | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.0 | <null> | 20 |
+-------+-------+-----------+--------+------------+--------+--------+--------+
#小于
mariadb root@localhost:test> select * from EMP where SAL < 1600;
+-------+--------+----------+------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+--------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.0 | <null> | 20 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.0 | 500.0 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.0 | 1400.0 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.0 | 0.0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.0 | <null> | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.0 | <null> | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.0 | <null> | 10 |
+-------+--------+----------+------+------------+--------+--------+--------+
#大于等于
mariadb root@localhost:test> select * from EMP where SAL >= 1600;
+-------+-------+-----------+--------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+--------+------------+--------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.0 | 300.0 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.0 | <null> | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.0 | <null> | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.0 | <null> | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.0 | <null> | 20 |
| 7839 | KING | PRESIDENT | <null> | 1981-11-17 | 5000.0 | <null> | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.0 | <null> | 20 |
+-------+-------+-----------+--------+------------+--------+--------+--------+
#小于等于
mariadb root@localhost:test> select * from EMP where SAL <= 1600;
+-------+--------+----------+------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+--------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.0 | <null> | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.0 | 300.0 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.0 | 500.0 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.0 | 1400.0 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.0 | 0.0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.0 | <null> | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.0 | <null> | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.0 | <null> | 10 |
+-------+--------+----------+------+------------+--------+--------+--------+
#不等于
mariadb root@localhost:test> select * from EMP where SAL <> 1600;
+-------+--------+-----------+--------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+--------+------------+--------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.0 | <null> | 20 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.0 | 500.0 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.0 | <null> | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.0 | 1400.0 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.0 | <null> | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.0 | <null> | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.0 | <null> | 20 |
| 7839 | KING | PRESIDENT | <null> | 1981-11-17 | 5000.0 | <null> | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.0 | 0.0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.0 | <null> | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.0 | <null> | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.0 | <null> | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.0 | <null> | 10 |
+-------+--------+-----------+--------+------------+--------+--------+--------+多条件查询 and、or、in、not in、between...and...、is、is not
与 & and
或 | or
关键字 in：
代表在这个取值中只要有一个匹配就是符合条件
关键字not in：
不在这个范围区间之内的
between and相当于>= <=
空永远不等于空
null 和 null做等值判断时，结果永远是假。
判断一个字段的数值是否是null需要用到关键字 is
判断一个字段的数值不为不为空需要用到关键字 is not
#并且 与 and
mariadb root@localhost:test> select * from EMP where SAL > 1600 and SAL <=3000;
->
+-------+-------+---------+------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+--------+--------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.0 | <null> | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.0 | <null> | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.0 | <null> | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.0 | <null> | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.0 | <null> | 20 |
+-------+-------+---------+------+------------+--------+--------+--------+
mariadb root@localhost:test> select * from EMP where SAL > 1600 and SAL <=3000 and DEPTNO = 10;
+-------+-------+---------+------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+--------+--------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.0 | <null> | 10 |
+-------+-------+---------+------+------------+--------+--------+--------+或 | or
#或者 或 or
mariadb root@localhost:test> select * from EMP where SAL > 3000 or SAL < 1000;
+-------+-------+-----------+--------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+--------+------------+--------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.0 | <null> | 20 |
| 7839 | KING | PRESIDENT | <null> | 1981-11-17 | 5000.0 | <null> | 10 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.0 | <null> | 30 |
+-------+-------+-----------+--------+------------+--------+--------+--------+
#以下两种方式的查询结果相同
mariadb root@localhost:test> select * from EMP where SAL = 800 or SAL = 950 or SAL = 1600;
+-------+-------+----------+------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+--------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.0 | <null> | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.0 | 300.0 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.0 | <null> | 30 |
+-------+-------+----------+------+------------+--------+--------+--------+
mariadb root@localhost:test> select * from EMP where SAL in(800,950,1600);
+-------+-------+----------+------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+--------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.0 | <null> | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.0 | 300.0 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.0 | <null> | 30 |
+-------+-------+----------+------+------------+--------+--------+--------+关键字 in：
代表在这个取值中只要有一个匹配就是符合条件
关键字not in：
不在这个范围区间之内的
mariadb root@localhost:test> select * from EMP where SAL in(800,950,1600);
+-------+-------+----------+------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+--------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.0 | <null> | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.0 | 300.0 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.0 | <null> | 30 |
+-------+-------+----------+------+------------+--------+--------+--------+
mariadb root@localhost:test> select * from EMP where SAL not in(800,950,1600);
+-------+--------+-----------+--------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+--------+------------+--------+--------+--------+
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.0 | 500.0 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.0 | <null> | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.0 | 1400.0 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.0 | <null> | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.0 | <null> | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.0 | <null> | 20 |
| 7839 | KING | PRESIDENT | <null> | 1981-11-17 | 5000.0 | <null> | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.0 | 0.0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.0 | <null> | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.0 | <null> | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.0 | <null> | 10 |
+-------+--------+-----------+--------+------------+--------+--------+--------+以下两行命令相同
between and相当于>= <=
mariadb root@localhost:test> select * from EMP where SAL >= 1600 and SAL <= 3000;
mariadb root@localhost:test> select * from EMP where SAL between 1600 and 3000;
->
+-------+-------+----------+------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+--------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.0 | 300.0 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.0 | <null> | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.0 | <null> | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.0 | <null> | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.0 | <null> | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.0 | <null> | 20 |
+-------+-------+----------+------+------------+--------+--------+--------+空永远不等于空
null 和 null做等值判断时，结果永远是假。
判断一个字段的数值是否是null需要用到关键字 is
判断一个字段的数值不为不为空需要用到关键字 is not
mariadb root@localhost:test> select * from EMP where comm = null;
+-------+-------+-----+-----+----------+-----+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----+-----+----------+-----+------+--------+
mariadb root@localhost:test> select * from EMP where COMM is null;
+-------+--------+-----------+--------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+--------+------------+--------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.0 | <null> | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.0 | <null> | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.0 | <null> | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.0 | <null> | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.0 | <null> | 20 |
| 7839 | KING | PRESIDENT | <null> | 1981-11-17 | 5000.0 | <null> | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.0 | <null> | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.0 | <null> | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.0 | <null> | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.0 | <null> | 10 |
+-------+--------+-----------+--------+------------+--------+--------+--------+
mariadb root@localhost:test> select * from EMP where COMM is not null;
+-------+--------+----------+------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+--------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.0 | 300.0 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.0 | 500.0 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.0 | 1400.0 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.0 | 0.0 | 30 |
+-------+--------+----------+------+------------+--------+--------+--------+10、 MySQL函数
MySQL常用函数
select 字段列表 | 表达式 | 函数函数名(参数)
数学函数
PI() 返回PI的值（圆周率）
mariadb root@localhost:test> select distinct pi() from EMP;
+----------+
| pi() |
+----------+
| 3.141593 |
+----------+FLOOR(x) 返回小于x的最大整数值，（去掉小数取整，直接砍掉小数部分）
mariadb root@localhost:test> select floor(SAL) from EMP where EMPNO = 7777;
+------------+
| floor(SAL) |
+------------+
| 2356.0 |
+------------+
mariadb root@localhost:test> select floor(COMM) from EMP where EMPNO = 7777;
+-------------+
| floor(COMM) |
+-------------+
| 235.0 |
+-------------+CEILING(x) 返回大于x的最小整数值，（进1取整，有小数就+1取整）
mariadb root@localhost:test> select ceiling(SAL) from EMP where EMPNO = 7777;
+--------------+
| ceiling(SAL) |
+--------------+
| 2357.0 |
+--------------+
mariadb root@localhost:test> select ceiling(COMM) from EMP where EMPNO = 7777;
+---------------+
| ceiling(COMM) |
+---------------+
| 236.0 |
+---------------+ROUND(x,y) 返回参数x的四舍五入的有y位小数的值，（四舍五入）
mariadb root@localhost:test> select round(sal,1) from EMP where EMPNO = 7777;
+--------------+
| round(sal,1) | #以小数点后一位进行四舍五入
+--------------+
| 2356.5 |
+--------------+
mariadb root@localhost:test> select SAL from EMP where EMPNO = 7777;
+---------+
| SAL |
+---------+
| 2356.54 |
+---------+
mariadb root@localhost:test> select round(COMM,1) from EMP where EMPNO = 7777;
+---------------+
| round(COMM,1) |
+---------------+
| 235.4 |
+---------------+
mariadb root@localhost:test> select COMM from EMP where EMPNO = 7777;
+--------+
| COMM |
+--------+
| 235.45 |
+--------+TRUNCATE(x,y) 返回数字x截短位y位小数的结果
mariadb root@localhost:test> select truncate(COMM,1) from EMP where EMPNO = 7777;
+------------------+
| truncate(COMM,1) |
+------------------+
| 235.4 |
+------------------+
mariadb root@localhost:test> select truncate(COMM,-1) from EMP where EMPNO = 7777;
+-------------------+
| truncate(COMM,-1) |
+-------------------+
| 230.0 |
+-------------------+
mariadb root@localhost:test> select truncate(COMM,-2) from EMP where EMPNO = 7777;
+-------------------+
| truncate(COMM,-2) |
+-------------------+
| 200.0 |
+-------------------+
mariadb root@localhost:test> select round(COMM,-1) from EMP where EMPNO = 7777;
+----------------+
| round(COMM,-1) |
+----------------+
| 240.0 |
+----------------+聚合函数 也称之为分组函数
AVG(col) 返回指定列的平均值
#单位员工工资平均数
mariadb root@localhost:test> select avg(SAL) from EMP;
+-------------+
| avg(SAL) |
+-------------+
| 2092.102667 |
+-------------+COUNT(col) 返回指定列中非NULL值/行的个数（当函数参数为*时不会忽略）
#查询公司员工中都有谁有奖金
mariadb root@localhost:test> select count(COMM) from EMP;
+-------------+
| count(COMM) |
+-------------+
| 5 |
+-------------+
#有记录的
mariadb root@localhost:test> select count(*) from EMP;
+----------+
| count(*) |
+----------+
| 15 |
+----------+MIN(col) 返回指定列的最小值
mariadb root@localhost:test> select MIN(SAL) from EMP;
+----------+
| MIN(SAL) |
+----------+
| 800.0 |
+----------+MAX(col) 返回指定列的最大值
mariadb root@localhost:test> select MAX(SAL) from EMP;
+----------+
| MAX(SAL) |
+----------+
| 5000.0 |
+----------+SUM(col) 返回指定列的所有值之和
mariadb root@localhost:test> select sum(SAL) from EMP;
+----------+
| sum(SAL) |
+----------+
| 31381.54 |
+----------+字符串函数
CONCAT(str1,str2,...) 将str1,str2,...连接成字符串
mariadb root@localhost:test> select ENAME,SAL from EMP;
+--------+---------+
| ENAME | SAL |
+--------+---------+
| SMITH | 800.0 |
| ALLEN | 1600.0 |
| WARD | 1250.0 |
| JONES | 2975.0 |
| MARTIN | 1250.0 |
| BLAKE | 2850.0 |
| CLARK | 2450.0 |
| SCOTT | 3000.0 |
| KING | 5000.0 |
| TURNER | 1500.0 |
| ADAMS | 1100.0 |
| JAMES | 950.0 |
| FORD | 3000.0 |
| MILLER | 1300.0 |
| TOM | 2356.54 |
+--------+---------+
mariadb root@localhost:test> select concat(ENAME,SAL) from EMP;
+-------------------+
| concat(ENAME,SAL) |
+-------------------+
| SMITH800.00 |
| ALLEN1600.00 |
| WARD1250.00 |
| JONES2975.00 |
| MARTIN1250.00 |
| BLAKE2850.00 |
| CLARK2450.00 |
| SCOTT3000.00 |
| KING5000.00 |
| TURNER1500.00 |
| ADAMS1100.00 |
| JAMES950.00 |
| FORD3000.00 |
| MILLER1300.00 |
| TOM2356.54 |
+-------------------+
mariadb root@localhost:test> select concat(ENAME,' salary is ',SAL) from EMP;
+---------------------------------+
| concat(ENAME,' salary is ',SAL) |
+---------------------------------+
| SMITH salary is 800.00 |
| ALLEN salary is 1600.00 |
| WARD salary is 1250.00 |
| JONES salary is 2975.00 |
| MARTIN salary is 1250.00 |
| BLAKE salary is 2850.00 |
| CLARK salary is 2450.00 |
| SCOTT salary is 3000.00 |
| KING salary is 5000.00 |
| TURNER salary is 1500.00 |
| ADAMS salary is 1100.00 |
| JAMES salary is 950.00 |
| FORD salary is 3000.00 |
| MILLER salary is 1300.00 |
| TOM salary is 2356.54 |
+---------------------------------+别名 直接写 "(中文 中间有空格 有关键字)"
mariadb root@localhost:test> select concat(ENAME,' salary is ',SAL) 'salary table' from EMP;
+--------------------------+
| salary table |
+--------------------------+
| SMITH salary is 800.00 |
| ALLEN salary is 1600.00 |
| WARD salary is 1250.00 |
| JONES salary is 2975.00 |
| MARTIN salary is 1250.00 |
| BLAKE salary is 2850.00 |
| CLARK salary is 2450.00 |
| SCOTT salary is 3000.00 |
| KING salary is 5000.00 |
| TURNER salary is 1500.00 |
| ADAMS salary is 1100.00 |
| JAMES salary is 950.00 |
| FORD salary is 3000.00 |
| MILLER salary is 1300.00 |
| TOM salary is 2356.54 |
+--------------------------+LTRIM(str) 去掉字符串str开头的空格
RTRIM(str) 去掉字符串ftr尾部的空格
TRIM 去掉字符串首部和尾部的所有空格
INSERT(str,x,y,instr) 将字符串str从第x为开始，y个字符长度的字串替换为字符串instr,返回结果
SUBSTRING(str,x,y) 截取字符串x开始y个
日期函数
YEAR(date) 返回日期date的年份(1000~9999)
mariadb root@localhost:test> select year(hiredate) from EMP;
+----------------+
| year(hiredate) |
+----------------+
| 1980 |
| 1981 |
| 1981 |
| 1981 |
| 1981 |
| 1981 |
| 1981 |
| 1987 |
| 1981 |
| 1981 |
| 1987 |
| 1981 |
| 1981 |
| 1982 |
| <null> |
+----------------+MONTH(date) 返回日期date的月份值(1~12)
DAY(date) 返回日期date的天数部分
mariadb root@localhost:test> select day(hiredate) from EMP;
+---------------+
| day(hiredate) |
+---------------+
| 17 |
| 20 |
| 22 |
| 2 |
| 28 |
| 1 |
| 9 |
| 19 |
| 17 |
| 8 |
| 23 |
| 3 |
| 3 |
| 23 |
| <null> |
+---------------+HOUR(time) 返回time的小时值(0~23)
MINUTE(time) 返回time的分钟值(0~59)
SECOND(time) 返回time的秒值(0~59)
DATE(datetime) 返回datetime的日期值
TIME(datetime) 返回datetime的时间值
11、 MySQL分组函数
别名
可以给字段 表达式 函数 表
#给字段起别名
mariadb root@localhost:test> select ENAME from EMP;
+--------+
| ENAME |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
| TOM |
+--------+
mariadb root@localhost:test> select ENAME name from EMP;
+--------+
| name |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
| TOM |
+--------+
mariadb root@localhost:test> select ENAME name,SAL s from EMP;
+--------+---------+
| name | s |
+--------+---------+
| SMITH | 800.0 |
| ALLEN | 1600.0 |
| WARD | 1250.0 |
| JONES | 2975.0 |
| MARTIN | 1250.0 |
| BLAKE | 2850.0 |
| CLARK | 2450.0 |
| SCOTT | 3000.0 |
| KING | 5000.0 |
| TURNER | 1500.0 |
| ADAMS | 1100.0 |
| JAMES | 950.0 |
| FORD | 3000.0 |
| MILLER | 1300.0 |
| TOM | 2356.54 |
+--------+---------+
#给表达式起别名
mariadb root@localhost:test> select ename,sal*12 totalSal from EMP;
+--------+----------+
| ename | totalSal |
+--------+----------+
| SMITH | 9600.0 |
| ALLEN | 19200.0 |
| WARD | 15000.0 |
| JONES | 35700.0 |
| MARTIN | 15000.0 |
| BLAKE | 34200.0 |
| CLARK | 29400.0 |
| SCOTT | 36000.0 |
| KING | 60000.0 |
| TURNER | 18000.0 |
| ADAMS | 13200.0 |
| JAMES | 11400.0 |
| FORD | 36000.0 |
| MILLER | 15600.0 |
| TOM | 28278.48 |
+--------+----------+
#给函数起别名
mariadb root@localhost:test> select avg(sal) avg_sal from EMP;
+-------------+
| avg_sal |
+-------------+
| 2092.102667 |
+-------------+
#起一个中文别名加" "
mariadb root@localhost:test> select avg(sal) "平均工资" from EMP;
+-------------+
| 平均工资 |
+-------------+
| 2092.102667 |
+-------------+
#别名中间有空格加' '
mariadb root@localhost:test> select avg(sal) 'avg sal' from EMP;
+-------------+
| avg sal |
+-------------+
| 2092.102667 |
+-------------+
#给表起别名
mariadb root@localhost:test> select avg(sal) 'avg sal' from EMP emp;
+-------------+
| avg sal |
+-------------+
| 2092.102667 |
+-------------+
#起别名的关键字as，一般省略
mariadb root@localhost:test> select avg(sal) as 'avg sal' from EMP emp;
+-------------+
| avg sal |
+-------------+
| 2092.102667 |
+-------------+分组函数
要查出每个部门的平均工资
第一步：按照部门进行分组
第二部：按照每个组算平均工资
#整个公司的平均工资
mariadb root@localhost:test> select avg(sal) from EMP;
+-------------+
| avg(sal) |
+-------------+
| 2092.102667 |
+-------------+
#各个部门的平均工资
mariadb root@localhost:test> select avg(sal) from EMP group by deptno;
+-------------+
| avg(sal) |
+-------------+
| 2916.666667 |
| 2175.0 |
| 1566.666667 |
+-------------+
#各个部门的平均工资
mariadb root@localhost:test> select avg(sal) avg_sal,deptno from EMP group by deptno;
+-------------+--------+
| avg_sal | deptno |
+-------------+--------+
| 2916.666667 | 10 |
| 2175.0 | 20 |
| 1566.666667 | 30 |
+-------------+--------+
#各个部门的总工资
mariadb root@localhost:test> select sum(sal) total_sal,deptno from EMP group by deptno;
+-----------+--------+
| total_sal | deptno |
+-----------+--------+
| 8750.0 | 10 |
| 10875.0 | 20 |
| 9400.0 | 30 |
+-----------+--------+
#各个部门的最高工资
mariadb root@localhost:test> select max(sal) max_sal,deptno from EMP g
-> roup by deptno;
+---------+--------+
| max_sal | deptno |
+---------+--------+
| 5000.0 | 10 |
| 3000.0 | 20 |
| 2850.0 | 30 |
+---------+--------+
#各个部门的最低工资
mariadb root@localhost:test> select min(sal) min_sal,deptno from EMP g
-> roup by deptno;
+---------+--------+
| min_sal | deptno |
+---------+--------+
| 1300.0 | 10 |
| 800.0 | 20 |
| 950.0 | 30 |
+---------+--------+
#各个部门的人数
mariadb root@localhost:test> select count(*) 'count',deptno from EMP g
-> roup by deptno;
+-------+--------+
| count | deptno |
+-------+--------+
| 3 | 10 |
| 5 | 20 |
| 6 | 30 |
+-------+--------+如何查出工资最大的人的姓名？分组查询不行，需要用到子查询
#语法允许，但是结果会出错
mariadb root@localhost:test> select max(sal),ename from EMP;
+----------+-------+
| max(sal) | ename |
+----------+-------+
| 5000.0 | SMITH |
+----------+-------+
mariadb root@localhost:test> select ename,sal from EMP;
+--------+--------+
| ename | sal |
+--------+--------+
| SMITH | 800.0 |
| ALLEN | 1600.0 |
| WARD | 1250.0 |
| JONES | 2975.0 |
| MARTIN | 1250.0 |
| BLAKE | 2850.0 |
| CLARK | 2450.0 |
| SCOTT | 3000.0 |
| KING | 5000.0 |
| TURNER | 1500.0 |
| ADAMS | 1100.0 |
| JAMES | 950.0 |
| FORD | 3000.0 |
| MILLER | 1300.0 |
+--------+--------+
#结果错误
mariadb root@localhost:test> select max(sal),ename,deptno from EMP group by deptno;
+----------+-------+--------+
| max(sal) | ename | deptno |
+----------+-------+--------+
| 5000.0 | CLARK | 10 |
| 3000.0 | SMITH | 20 |
| 2850.0 | ALLEN | 30 |
+----------+-------+--------+注意一点，如果使用分组函数，那么，不在分组函数中的字段，必须存在于group by 后
比如上面 ename不在group by后面，出现错误。
出错的原因是什么？
在MySQL中会直接将ename中的第一条取出来与max(sal)对应
但在Oracle中会直接报错
mariadb root@localhost:test> select max(sal) from EMP;
+----------+
| max(sal) |
+----------+
| 5000.0 |
+----------+
mariadb root@localhost:test> select ename from EMP;
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+--------+having关键字
mariadb root@localhost:test> select avg(sal),deptno from EMP group by
-> deptno;
+-------------+--------+
| avg(sal) | deptno |
+-------------+--------+
| 2916.666667 | 10 |
| 2175.0 | 20 |
| 1566.666667 | 30 |
+-------------+--------+
mariadb root@localhost:test> select avg(sal) avg_sal,deptno from EMP
-> group by deptno where avg_sal > 2000;
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where avg_sal > 2000' at line 1")
mariadb root@localhost:test> select avg(sal) avg_sal,deptno from EMP
-> group by deptno having avg_sal > 2000;
+-------------+--------+
| avg_sal | deptno |
+-------------+--------+
| 2916.666667 | 10 |
| 2175.0 | 20 |
+-------------+--------+12、 MySQL子查询
子查询
就是一个查询的结果可以作为另一个查询的数据源或者条件
如何查出工资最大的人的姓名
工资 姓名
可以将工资的最大值作为一个筛选条件
1.当成查询条件
子查询情况
将查询的结果作为另一个查询的条件
等值比较
能否给我查出公司中都有哪些人是经理人
看哪些人的empno出现在mgr字段中
in
mariadb root@localhost:test> select max(sal) from EMP;
+----------+
| max(sal) |
+----------+
| 5000.0 |
+----------+
#将select max(sal) from EMP查询结果作为另一个查询的条件
mariadb root@localhost:test> select ename,sal from EMP where sal = (se
-> lect max(sal) from EMP);
+-------+--------+
| ename | sal |
+-------+--------+
| KING | 5000.0 |
+-------+--------+能否给我查出公司中都有哪些人是经理人
看哪些人的empno出现在mgr字段中
in
mariadb root@localhost:test> select distinct mgr from EMP where mgr is not null;
+------+
| mgr |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
#将上面查询的结果作为下面查询的条件
mariadb root@localhost:test> select ename,empno from EMP where empno in(select dist
-> inct mgr from EMP where mgr is not null);
+-------+-------+
| ename | empno |
+-------+-------+
| JONES | 7566 |
| BLAKE | 7698 |
| CLARK | 7782 |
| SCOTT | 7788 |
| KING | 7839 |
| FORD | 7902 |
+-------+-------+2.当成查询数据源
能够给我查出平均工资最大的部门
主函数不能嵌套
我们的查询结果作为另一个查询的数据源 当成一张表
当成表的过程中 必须起别名
#主函数不能嵌套
mariadb root@localhost:test> select max(avg(sal)),deptno from EMP group by deptno;
(1111, 'Invalid use of group function')
#临时表必须要起别名
mariadb root@localhost:test> select max(avg_sal) from (select avg(sal) avg_sal,dept
-> no from EMP group by deptno);
(1248, 'Every derived table must have its own alias')
#使用了主函数，主函数的字段
mariadb root@localhost:test> select max(avg_sal) from (select avg(sal) avg_sal,dept
-> no from EMP group by deptno) avg_table;
+--------------+
| max(avg_sal) |
+--------------+
| 2916.666667 |
+--------------+
#部门号是10，碰巧对了而已
mariadb root@localhost:test> select max(avg_sal),deptno from (select avg(sal) avg_s
-> al,deptno from EMP group by deptno) avg_table;
+--------------+--------+
| max(avg_sal) | deptno |
+--------------+--------+
| 2916.666667 | 10 |
+--------------+--------+
#部门号还是10
mariadb root@localhost:test> select min(avg_sal),deptno from (select avg(sal) avg_s
-> al,deptno from EMP group by deptno) avg_table;
+--------------+--------+
| min(avg_sal) | deptno |
+--------------+--------+
| 1566.666667 | 10 |
+--------------+--------+正确的
mariadb root@localhost:test> select avg_sal,deptno from(select avg(sal) avg_sal,deptno
-> from EMP group by deptno) e where avg_sal = (select max(av
-> g_sal) from (select avg(sal) avg_sal,deptno from EMP group
-> by deptno) avg_table);
+-------------+--------+
| avg_sal | deptno |
+-------------+--------+
| 2916.666667 | 10 |
+-------------+--------+
1 row in set
Time: 0.014s
mariadb root@localhost:test> select avg_sal,deptno from(select avg(sal) avg_sal,deptno
-> from EMP group by deptno) e where avg_sal = (select min(av
-> g_sal) from (select avg(sal) avg_sal,deptno from EMP group
-> by deptno) avg_table);
+-------------+--------+
| avg_sal | deptno |
+-------------+--------+
| 1566.666667 | 30 |
+-------------+--------+13、 MySQL多表联合查询
多表查询
我想查询员工的姓名和员工所在部门的名称
直接查询
笛卡尔积
14*4=56
mariadb root@localhost:test> select * from EMP
+-------+--------+-----------+--------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+--------+------------+--------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.0 | <null> | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.0 | 300.0 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.0 | 500.0 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.0 | <null> | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.0 | 1400.0 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.0 | <null> | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.0 | <null> | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.0 | <null> | 20 |
| 7839 | KING | PRESIDENT | <null> | 1981-11-17 | 5000.0 | <null> | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.0 | 0.0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.0 | <null> | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.0 | <null> | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.0 | <null> | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.0 | <null> | 10 |
+-------+--------+-----------+--------+------------+--------+--------+--------+
14 rows in set
Time: 0.022s
mariadb root@localhost:test> select * from dept
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORk |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
mariadb root@localhost:test> select ename,dname from EMP,dept;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | ACCOUNTING |
| SMITH | RESEARCH |
| SMITH | SALES |
| SMITH | OPERATIONS |
| ALLEN | ACCOUNTING |
| ALLEN | RESEARCH |
| ALLEN | SALES |
| ALLEN | OPERATIONS |
| WARD | ACCOUNTING |
| WARD | RESEARCH |
| WARD | SALES |
| WARD | OPERATIONS |
| JONES | ACCOUNTING |
| JONES | RESEARCH |
| JONES | SALES |
| JONES | OPERATIONS |
| MARTIN | ACCOUNTING |
| MARTIN | RESEARCH |
| MARTIN | SALES |
| MARTIN | OPERATIONS |
| BLAKE | ACCOUNTING |
| BLAKE | RESEARCH |
| BLAKE | SALES |
| BLAKE | OPERATIONS |
| CLARK | ACCOUNTING |
| CLARK | RESEARCH |
| CLARK | SALES |
| CLARK | OPERATIONS |
| SCOTT | ACCOUNTING |
| SCOTT | RESEARCH |
| SCOTT | SALES |
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | ACCOUNTING |
| SMITH | RESEARCH |
| SMITH | SALES |
| SMITH | OPERATIONS |
| ALLEN | ACCOUNTING |
| ALLEN | RESEARCH |
| ALLEN | SALES |
| ALLEN | OPERATIONS |
| WARD | ACCOUNTING |
| WARD | RESEARCH |
| WARD | SALES |
| WARD | OPERATIONS |
| JONES | ACCOUNTING |
| JONES | RESEARCH |
| JONES | SALES |
| JONES | OPERATIONS |
| MARTIN | ACCOUNTING |
| MARTIN | RESEARCH |
| MARTIN | SALES |
| MARTIN | OPERATIONS |
| BLAKE | ACCOUNTING |
| BLAKE | RESEARCH |
| BLAKE | SALES |
| BLAKE | OPERATIONS |
| CLARK | ACCOUNTING |
| CLARK | RESEARCH |
| CLARK | SALES |
| CLARK | OPERATIONS |
| SCOTT | ACCOUNTING |
| SCOTT | RESEARCH |
| SCOTT | SALES |
| SCOTT | OPERATIONS |
| KING | ACCOUNTING |
| KING | RESEARCH |
| KING | SALES |
| KING | OPERATIONS |
| TURNER | ACCOUNTING |
| TURNER | RESEARCH |
| TURNER | SALES |
| TURNER | OPERATIONS |
| ADAMS | ACCOUNTING |
| ADAMS | RESEARCH |
| ADAMS | SALES |
| ADAMS | OPERATIONS |
| JAMES | ACCOUNTING |
| JAMES | RESEARCH |
| JAMES | SALES |
| JAMES | OPERATIONS |
| FORD | ACCOUNTING |
| FORD | RESEARCH |
| FORD | SALES |
| FORD | OPERATIONS |
| MILLER | ACCOUNTING |
| MILLER | RESEARCH |
| MILLER | SALES |
| MILLER | OPERATIONS |
+--------+------------+正确的语法
mariadb root@localhost:test> select ename,dname from EMP,dept where EMP.deptno = dept.d
-> eptno;
+--------+------------+
| 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 |
+--------+------------+
mariadb root@localhost:test> select ename,dname from EMP e,dept d where e.deptno = d.de
-> ptno;
+--------+------------+
| 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 |
+--------+------------+
mariadb root@localhost:test> select ename,dname from EMP join dept on EMP.deptno =dept.
-> 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 |
+--------+------------+mariadb root@localhost:test> select * from EMP;
+-------+--------+-----------+--------+------------+--------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+--------+------------+--------+--------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.0 | <null> | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.0 | 300.0 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.0 | 500.0 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.0 | <null> | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.0 | 1400.0 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.0 | <null> | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.0 | <null> | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.0 | <null> | 20 |
| 7839 | KING | PRESIDENT | <null> | 1981-11-17 | 5000.0 | <null> | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.0 | 0.0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.0 | <null> | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.0 | <null> | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.0 | <null> | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.0 | <null> | 10 |
+-------+--------+-----------+--------+------------+--------+--------+--------+
mariadb root@localhost:test> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORk |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
mariadb root@localhost:test> select * from SALGRADE;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 4 | 3001 | 9999 |
+-------+-------+-------+三张表联合查询
姓名，所在部门，工资等级
mariadb root@localhost:test> select ename,dname,grade from EMP join dept join SALGRADE
-> on EMP.deptno = dept.deptno and EMP.sal between losal and
-> hisal;
+--------+------------+-------+
| ename | dname | grade |
+--------+------------+-------+
| SMITH | RESEARCH | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCH | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | ACCOUNTING | 4 |
| SCOTT | RESEARCH | 4 |
| KING | ACCOUNTING | 4 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| FORD | RESEARCH | 4 |
| MILLER | ACCOUNTING | 2 |
+--------+------------+-------+将EMP表当作两张不同的表
mariadb root@localhost:test> select e.ename,m.ename from EMP e join EMP m on e.empno = m.mgr;
+-------+--------+
| ename | ename |
+-------+--------+
| FORD | SMITH |
| BLAKE | ALLEN |
| BLAKE | WARD |
| KING | JONES |
| BLAKE | MARTIN |
| KING | BLAKE |
| KING | CLARK |
| JONES | SCOTT |
| BLAKE | TURNER |
| SCOTT | ADAMS |
| BLAKE | JAMES |
| JONES | FORD |
| CLARK | MILLER |
+-------+--------+
#只有13条数据，KING没有经理人
mariadb root@localhost:test> select e.ename,m.ename from EMP m join EMP e on e.empno =
-> m.mgr;
+-------+--------+
| ename | ename |
+-------+--------+
| JONES | SCOTT |
| JONES | FORD |
| BLAKE | ALLEN |
| BLAKE | WARD |
| BLAKE | MARTIN |
| BLAKE | TURNER |
| BLAKE | JAMES |
| CLARK | MILLER |
| SCOTT | ADAMS |
| KING | JONES |
| KING | BLAKE |
| KING | CLARK |
| FORD | SMITH |
+-------+--------+正确的
表联合
左外联合
将左边这个标里没有匹配上的数据显示出来
左边表中如果存在记录没有匹配到还想显示
mariadb root@localhost:test> select m.ename,e.ename from EMP m left join EMP e on e.emp
-> no = m.mgr;
+--------+--------+
| ename | ename |
+--------+--------+
| SCOTT | JONES |
| FORD | JONES |
| ALLEN | BLAKE |
| WARD | BLAKE |
| MARTIN | BLAKE |
| TURNER | BLAKE |
| JAMES | BLAKE |
| MILLER | CLARK |
| ADAMS | SCOTT |
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
| SMITH | FORD |
| KING | <null> |
+--------+--------+右边表里有一条记录没有匹配出
右外联合
右边的表中如果存在记录没有匹配到还想显示出来
mariadb root@localhost:test> select ename,dname from EMP join dept on EMP.deptno = dept
-> .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 |
+--------+------------+
mariadb root@localhost:test> select ename,dname from EMP right join dept on EMP.deptno
-> = dept.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 |
| <null> | OPERATIONS |
+--------+------------+练习
部门的平均薪水等级
部门平均的薪水等级
雇员之间哪些人是经理人
不用组函数求薪水最大值
平均薪水最高的部门编号
平均薪水最高的部门名称
平均薪水的等级最低的部门名称
比普通员工的最高薪水还要高的经理人的名称
