目录
MySQL(数据库):
数据库简介:
学习数据库主要学习的就是如何对数据进行增删改查操作.
增加(插入数据) 删除数据 修改数据 查询数据
特点:
- 采用复杂的结构化的数据模型
- 最低的冗余度、
- 较高的数据独立性
- 数据库系统为用户提供了方便的接口
- 数据库控制功能:
- 并发控制
- 数据库的恢复
- 数据的完整性:数据的一致性、正确
- 安全性
- 系统更加灵活
- 信息处理方式不再以程序为中心,而是以数据为中心
MySQL 基础知识
特点:性能高 跨平台支持 简单易用 开源 支持多用户
- 为什么使用数据库软件?
文件存储方式保存数据的弊端:缺乏对数据的整体管理数据不便修改;不利于数据分析和共享;数据量急剧增长,大量数据不可能长期保存在文件中.之前在讲webserver时通过IO技术已经操作过数据,其实这部分代码相当于自己写了一个数据库软件,只不过功能较少执行效率较低,将来工作中不管开发什么网站都需要对数据进行增删改查操作,这种使用频率较高而且开发过程复杂的内容在互联网行业中肯定会有一个通用的解决方案.
DB和DBMS:
数据库( Database,简称DB )是按照数据结构来组织、存储和管理数据的文件仓库。
数据库管理系统(Database Management System,简称DBMS) :管理数据库的软件。
- DBMS:DataBaseManagementSystem数据库管理系统(数据库软件),包括:MySQL/Oracle/SQLserver/DB2等
- 学习数据库主要学习如何和数据库软件进行交流,通过SQL语言和数据库软件进行交流
- 常见的DBMS介绍:
1. MySQL: Oracle公司产品, MySQL在08年被Sun公司收购,09年Sun公司被Oracle公司收购,开源软件, 被收购后MySQL创始人从Oracle离开创建了MariaDB,MariaDB就是MySQL软件的一个分支. 市占率第一
2. Oracle: Oracle公司产品, 性能最高价格最贵的数据软件. 市占率第二
3. SQLserver: 微软公司产品 .net语言使用此数据库 市占率第三.
4. DB2: IBM公司产品
5. SQLite: 轻量级数据库软件,安装包只有几十k. 主要 应用在移动设备和嵌入式设备中.
开源和闭源:
开源:开发源代码, 盈利方式:靠卖服务, 有技术大拿无偿维护升级.
闭源:不开放源代码, 盈利方式:靠卖产品(软件)和卖服务, 有技术大拿攻击找漏洞,但是产品公司会花钱养一群技术大拿维护和升级.
SQL语言:
概述:
Structured Query Language:结构化查询语言,用户程序员和数据库软件进行交流.SQL是在关系数据库上执行数据操作、检索及维护所使用的标准语言,可以用来查询数据, 操纵数据,定义数据,控制数据所有数据库都使用相同或者相似的语言.
SQL分类
- DDL: 数据定义语言, 负责数据库和表相关的操作 create drop alter
- DML: 数据操作语言, 负责数据增删改 insert update delete
- DQL: 数据查询语言, 负责查询数据 select
- DCL: 数据控制语言, 负责控制用户权限相关 create user grant revoke
- TCL: 事务控制语言, 事务相关 commit rollback savepoint
DDL: 数据定义语言
DDL ( Data Definition Language ),负责数据结构定义与创建数据库对象的语言,常用的有CREATE、ALTER与DROP三个
注意: DDL不支持事务DDL语句操作以后无法回滚( rollback ). 数据库软件中要想保存数据需要先建库再键表.
数据库相关SQL
1. 查询所有数据库
- 格式: show databases;
2. 创建数据库
- 默认字符集格式: create database 数据库名;
create database db1;
- 指定字符集格式: create database 数据库名 character set gbk/utf8;
create database db2 character set gbk;
create database db3 character set utf8;
3. 查询数据库详情
- 格式: show create database 数据库名;
show create database db1;
4. 删除数据库
- 格式: drop database 数据库名;
drop database db1;
5. 使用数据库
- 如果需要进行表操作后者数据操作 必须使用某个数据库之后再进行
- 格式: use 数据库名;
use db2;
MariaDB [newdb3]> drop database db2;
Query OK, 0 rows affected (0.22 sec)
MariaDB [newdb3]> drop database db3;
Query OK, 0 rows affected (0.00 sec)
MariaDB [newdb3]> drop database db4;
Query OK, 0 rows affected (0.00 sec)
MariaDB [newdb3]> drop database db5;
Query OK, 0 rows affected (0.00 sec)
MariaDB [newdb3]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb1 |
| mydb3 |
| mysql |
| newdb3 |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
MariaDB [newdb3]> create database db1;
Query OK, 1 row affected (0.00 sec)
MariaDB [newdb3]> create database db2 character set gbk;
Query OK, 1 row affected (0.00 sec)
MariaDB [newdb3]> create database db3 character set utf8;
Query OK, 1 row affected (0.00 sec)
MariaDB [newdb3]> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [newdb3]> show create database db2;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [newdb3]> show create database db3;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db3 | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [newdb3]> use db2;
Database changed
MariaDB [db2]>
表相关的SQL语句
操作表相关的SQL 必须先使用某个数据库
create database mydb1;
show create database mydb1;
use mydb1;
- 如果默认不是utf8,以后创建数据库需要制定字符集为utf8
create database mydb1 character utf8;
1. 创建表
- 格式: create table 表名(字段1名 类型[ 约束条件],字段2名 类型 [ 约束条件]);
约束类型
1)主键约束 primary key
不允许为空,不允许重复
一个表中只能有一个主键
2)唯一约束 unique
不允许重复 允许为null ,且null值可以有多个
3)非空约束 not null 非空字段的不允许为null 对一个字段可以设置多种约束 ,顺序任意
4)默认约束 default
5)检查约束 check --目前MySQL 暂时不支持检查约束 可以使用触发器完成
age int check(age>=18 and age<=60);
6)外键约束 foreign key (两张表) 外键约束用于定义表与表之间的关系
主表 (父表):被参照的表 (提供数据的表)
从表(字表):外键所在的表
外键字段的值必须来自于主表中的对应字段或者为u'll
语法:
constraint 约束名 foreign key(外键字段) reference 主表(字段)
外键字段 参考的主表中的字段必须是唯一性字段,一般使用主键
create table person(name varchar(5),age int);
- 制定字符集格式:
create table 表名(字段1名 类型,字段2名 类型) charset=utf8/gbk;
create table student(name varchar(10),score int) charset=gbk;
create table hero(name varchar(10),money int) default charset=utf8;
2. 查询所有表
- 格式: show tables;
3. 查询表详情
- 格式: show create table 表名;
show create table person;
show create table hero;
4. 查询表字段
- 格式: desc 表名;
desc person;
5. 删除表
- 格式: drop table 表名;
drop table hero;
6. 修改表名
- 格式: rename table 原名 to 新名;
rename table person to t_person;
7. 添加表字段
- 最后添加格式:alter table 表名 add 字段名 类型;
alter table student add gender varchar(5);
- 最前面添加格式:alter table 表名 add 字段名 类型 first;
alter table student add id int first;
- 在某个字段的后面添加: alter table 表名 add 字段名 类型 after xxx;
alter table student add money int after name;
8. 删除表字段
- 格式: alter table 表名 drop 字段名;
alter table student drop money;
9. 修改表字段
- 格式: alter table 表名 change 原名 新名 新类型;
alter table student change gender money int;
表相关练习
1. 创建mydb2 字符集utf8 并使用该数据库
2. 在mydb2中创建员工表emp 字段有name 字符集utf8
3. 最后面添加age字段
4. 最前面添加id字段
5. name后面添加gender字段
6. 修改gender字段为工资salary
7. 删除age字段
8. 修改表名为
9. 删除表 删除数据库
MariaDB [db2]> create database mydb2 character set utf8;
Query OK, 1 row affected (0.00 sec)
MariaDB [db2]> use mydb2;
Database changed
MariaDB [mydb2]> create table emp(name varchar(10)) charset=utf8;
Query OK, 0 rows affected (0.17 sec)
MariaDB [mydb2]> alter table emp add age int;
Query OK, 0 rows affected (0.55 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [mydb2]> alter table emp add id int first;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [mydb2]> alter table emp add gender varchar(5) after name;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [mydb2]> alter table emp change gender salary int;
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [mydb2]> alter table emp drop age;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [mydb2]> show create table emp;
+-------+--------------------------------------------------------------------------
--------------------------------------------------+
| Table | Create Table
|
+-------+--------------------------------------------------------------------------
--------------------------------------------------+
| emp | CREATE TABLE `emp` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`salary` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------
--------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [mydb2]> desc emp;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| salary | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
MariaDB [mydb2]> rename table emp to t_emp;
Query OK, 0 rows affected (0.12 sec)
MariaDB [mydb2]> drop table t_emp;
Query OK, 0 rows affected (0.10 sec)
MariaDB [mydb2]> drop database mydb2;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| mydb1 |
| mydb3 |
| mysql |
| newdb3 |
| performance_schema |
| test |
+--------------------+
10 rows in set (0.00 sec)
MariaDB [(none)]>
DML:数据操作语言
DML ( Data Manipulation Language ),负责对数据库中更改数据操作的指令,包括SELECT、INSERT、UPDATE、DELETE指令, 简称: "CRUD" 操作(增删改查)经常将DQL(SELECT)作为DML的一部分,注意: DML语包支持事务,在非自动提交模式时候,可以利用
rollback回滚操作.
- 执行操作数据的SQL必须保证已经使用了某个数据库,并且存在数据所对应的表
create database mydb3 character set utf8;
use mydb3;
create table person(name varchar(10),age int)charset=utf8;
1. 插入数据(增)
- 全表插入格式(要求值的数量和顺序和表字段保持一致):
insert into 表名 values(值1,值2,值3);
insert into person values("Tom",18);
插入语句的语法
insert into 表名 [(字段列表)] values(值列表)
insert into student values('2018001','张三');
- 指定字段插入格式(要求值得数量和顺序和指定的一致)
insert into 表名(字段名1,字段名2) values(值1,值2);
insert into person(name) values('Jerry');
- 批量插入:
insert into person values('Lucy',20),('Lily',21);
insert into person(name) values('Lilei'),('Hanmeimei');
- 插入中文
insert into person values('刘德华',50);
如果执行上面SQL语句出现错误提示 提示中包含16进制内容,执行以下SQL
set names gbk;
insert into person values('刘德华',50);
DQL数据查询语言
DML ( Data Query Language )用于查询表格内数据的指令,包括 SELECT命令般作为DML的一部分
2. 查询数据
- 格式: select 字段信息 from 表名 where 条件;
select name from person;
select name,age from person where age>20;
select * from person where age=50;
3. 修改数据
- 格式: update 表名 set 字段名=xxx,字段名=xxx where 条件;
- 举例:
update person set age=8 where name='Tom';
update person set age=10 where age is null;
4. 删除数据
- 格式: delete from 表名 where 条件;
delete from person where age=10;
delete from person where name='刘德华';
delete from person where age<=20;
delete from person;
数据相关练习题
1. 创建hero表 字段: id,name,job(职业),money
2. 插入以下数据:
3. 修改13888为28888
4. 修改所有射手价格为3000
5. 删除价格为18888的英雄
6. 修改孙尚香为猪八戒
7. 删除价格低于5000的英雄
8. 给表添加gender字段在name字段后面
9. 修改所有英雄性别为男
10. 删除表里面所有数据
11. 删除表
MariaDB [(none)]> create database mydb3 character set utf8;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use mydb3;
Database changed
MariaDB [mydb3]> create table hero (id int,name varchar(10),job varchar(5),
Query OK, 0 rows affected (0.16 sec)
MariaDB [mydb3]> insert into hero values(1, '诸葛亮', '法师', 18888),(2, '周
', 8888),(4, '孙尚香', '射手', 6888),(5 ,'黄忠', '射手', 8888);
Query OK, 5 rows affected (0.09 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [mydb3]> update hero set money = 28888 where money=13888;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [mydb3]> update heroset money=3000 where name='射手';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
version for the right syntax to use near '=3000 where name='射手'' at line
MariaDB [mydb3]> update hero set money=3000 where name='射手';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
MariaDB [mydb3]> delete from hero where money=18888;
Query OK, 1 row affected (0.09 sec)
MariaDB [mydb3]> desc hero;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| job | varchar(5) | YES | | NULL | |
| money | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
MariaDB [mydb3]> select*from hero;
+------+--------+------+-------+
| id | name | job | money |
+------+--------+------+-------+
| 2 | 周瑜 | 法师 | 28888 |
| 3 | 刘备 | 战士 | 8888 |
| 4 | 孙尚香 | 射手 | 6888 |
| 5 | 黄忠 | 射手 | 8888 |
+------+--------+------+-------+
4 rows in set (0.00 sec)
MariaDB [mydb3]> update hero name='猪八戒' where name='孙尚香';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
version for the right syntax to use near '='猪八戒' where name='孙尚香'' a
MariaDB [mydb3]> update hero set name='猪八戒' where name='孙尚香';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [mydb3]> delete from hero where money<5000;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mydb3]> alter table hero add gender varchar(4) after name;
Query OK, 4 rows affected (0.28 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [mydb3]> select*from hero;
+------+--------+--------+------+-------+
| id | name | gender | job | money |
+------+--------+--------+------+-------+
| 2 | 周瑜 | NULL | 法师 | 28888 |
| 3 | 刘备 | NULL | 战士 | 8888 |
| 4 | 猪八戒 | NULL | 射手 | 6888 |
| 5 | 黄忠 | NULL | 射手 | 8888 |
+------+--------+--------+------+-------+
4 rows in set (0.00 sec)
MariaDB [mydb3]> update hero set gender='男';
Query OK, 4 rows affected (0.09 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MariaDB [mydb3]> select*from hero;
+------+--------+--------+------+-------+
| id | name | gender | job | money |
+------+--------+--------+------+-------+
| 2 | 周瑜 | 男 | 法师 | 28888 |
| 3 | 刘备 | 男 | 战士 | 8888 |
| 4 | 猪八戒 | 男 | 射手 | 6888 |
| 5 | 黄忠 | 男 | 射手 | 8888 |
+------+--------+--------+------+-------+
4 rows in set (0.00 sec)
MariaDB [mydb3]> delete from hero;
Query OK, 4 rows affected (0.10 sec)
MariaDB [mydb3]> drop table hero;
Query OK, 0 rows affected (0.08 sec)
MariaDB [mydb3]>
事务控制语言TCL
TCL ( Transaction Control Language )
-负责实现数据库中事务支持的语言,包括COMMIT、ROLLBACK、SAVEPOINT 等指令
数据库控制语言DCL
DCL ( Data Control Language )
用于管理数据库授权,角色控制等,包括GRANT(授权) ,REVOKE(取消授权)等
数据库数据类型
主要包括以下五大类:
整数类型: BIT、BOOL、TINYINT、 SMALL INT、MEDIUM INT, INT、BIG INT
浮点数类型: FLOAT、DOUBLE、DECIMAL
字符串类型: CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、 TINY BLOB、BLOB、 MEDIL JM
BLOB、LONG BLOB
日期类型: Date、DateTime、 TimeStamp、 Time、 Year
其他数据类型: BINARY、VARBINARY、 ENUM、 SET、Geometry、Point、 MultiPoint、 LineString、 MultiLineString、
Polygon、GeometryCollection等
1. 整数类型: int(m)和bigint(m) 等效java中的long , m代表显示长度
MariaDB [newdb3]> use mydb3;
Database changed
MariaDB [mydb3]> create table t1(name varchar(10),age int(10) zerofill);
Query OK, 0 rows affected (0.17 sec)
MariaDB [mydb3]> insert into t1 values('aaa',18);
Query OK, 1 row affected (0.04 sec)
MariaDB [mydb3]> select * from t1;
+------+------------+
| name | age |
+------+------------+
| aaa | 0000000018 |
+------+------------+
1 row in set (0.00 sec)
MariaDB [mydb3]>
2. 浮点数:
double(m,d) m代表总长度 d代表小数长度 53.234 double(5,3) ,超高精度浮点数 decimal(m,d)精度远高于double 只有涉及超高精度运算时使用. salary decimal(7,2) -99999.99~99999.99
3. 字符串:
- char(m): 固定长度, m=10 存abc 占10 , 优点:执行效率略高 ,最大长度 m<255
- varchar(m): 可变长度,m=10 存abc 占3 , 优点:更节省空间,最大长度65535,但是建议存255以下
- text(m):可变长度, 最大长度65535, 建议保存长度大于255的
补充:
char(n)若存入字符数小于n ,则以空格补于其后,查询之时再将空格去掉。
char类型存储的字符串末尾有空格时候读取会丢失。
char(n)固定长度,如char(4)不管是存入几个字符,都将占用4个字节。
char类型的字符串检索速度要比varchar类型的快。
text类型不能有默认值。
varchar可直接创建索引,text创建索弓|要指定前多少个字符。
varchar查询速度快于text。
varchar中字符转换为字节数量有数据库的存储引擎决定,请参考相关的手册。
4. 日期
- date: 只能保存年月日 3字节 '1000-01-01'~'9999-12-31' YYY-MM-DD
- time: 只能保存时分秒 3字节 '-838:59:59' ~ '838:59:59' HH:ii:ss
- datetime: 默认值为null ,最大值9999-12-31 8字节 '1000-01-01 00:00:00' ~ '9999-12-31 23:59:59' YYY-MM-DD hh:ii:ss
- timestamp(时间戳:距1970年1月1号的毫秒数): 默认值为当前系统时间,最大值2038-1-19
MariaDB [mydb3]> create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
Query OK, 0 rows affected (0.14 sec)
MariaDB [mydb3]> insert into t_date values(null,null,'2020-6-12 16:19:20',null);
Query OK, 1 row affected (0.06 sec)
MariaDB [mydb3]> insert into t_date values('2018-10-12','15:30:20',null,null);
Query OK, 1 row affected (0.05 sec)
MariaDB [mydb3]> select * from t_date;
+------------+----------+---------------------+---------------------+
| t1 | t2 | t3 | t4 |
+------------+----------+---------------------+---------------------+
| NULL | NULL | 2020-06-12 16:19:20 | 2020-06-13 20:02:47 |
| 2018-10-12 | 15:30:20 | NULL | 2020-06-13 20:02:47 |
+------------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)
MariaDB [mydb3]>
数据类型使用建议
整数:范围合适即可,常用int和bigint
小数:double精度高,如果是钱数可以使用decima更加精确
字符串:常用varchar,长文本用text longtext
日期:使用datetime
where字句: where条件查询
*.sql文件
- 此文件是从数据库中导出的数据,里面有很多条的SQL语句.
- 把emp.sql文件解压出来后 赋值到某个盘的根目录(如果linux系统放在桌面)
- 通过以下SQL语句导入该文件
windows系统: source d:/emp.sql;
linux系统: source /home/soft01/桌面/emp.sql;
- 提示一堆Query OK 说明导入完成
- 测试: show tables;
select * from emp; 如果显示乱码 执行 set names gbk; 再查
MariaDB [mydb3]> use newdb3;
Database changed
MariaDB [newdb3]> show tables;
+------------------+
| Tables_in_newdb3 |
+------------------+
| dept |
| emp |
+------------------+
2 rows in set (0.00 sec)
MariaDB [newdb3]> select*from emp;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 1 | 孙悟空 | 销售 | 4 | 1980-12-17 | 800.00 | NULL | 1 |
| 2 | 猪八戒 | 销售 | 4 | 1981-02-20 | 1600.00 | 300.00 | 1 |
| 3 | 沙僧 | 销售 | 4 | 1981-02-22 | 1250.00 | 500.00 | 1 |
| 4 | 唐僧 | 销售经理 | 8 | 1981-04-02 | 2975.00 | NULL | 1 |
| 5 | 刘备 | 项目经理 | NULL | 1981-09-28 | 1250.00 | 1400.00 | 3 |
| 6 | 关羽 | 程序员 | 5 | 1981-05-01 | 2850.00 | NULL | 3 |
| 7 | 张飞 | 程序员 | 5 | 1981-06-09 | 2450.00 | NULL | 3 |
| 8 | 观音 | CEO | NULL | 1981-11-17 | 5000.00 | NULL | 1 |
| 9 | 白骨精 | 人事 | 8 | 1981-09-08 | 1500.00 | 0.00 | 2 |
| 10 | 蜘蛛精 | 人事 | 8 | 1981-12-03 | 950.00 | NULL | 2 |
| 11 | 黑熊怪 | 市场 | 8 | 1981-12-03 | 3000.00 | NULL | 2 |
+-------+--------+----------+------+------------+---------+---------+--------+
where关键字:
WHERE子句用于规定选择的标准
如需有条件地从表中选取数据,可将WHERE子句添加到SELECT
语句
常用运算符:
- and、or
- like
- between X and x
- is、in、not
去重 distinct
1. 查询员工表中有哪些工作
MariaDB [newdb3]> select distinct job from emp;
+----------+
| job |
+----------+
| 销售 |
| 销售经理 |
| 项目经理 |
| 程序员 |
| CEO |
| 人事 |
| 市场 |
+----------+
7 rows in set (0.00 sec)
2. 查询员工表中的部门编号deptno有哪些
MariaDB [newdb3]> select distinct deptno from emp;
+--------+
| deptno |
+--------+
| 1 |
| 3 |
| 2 |
+--------+
3 rows in set (0.00 sec)
is null和is not null
1. 查询没有上级领导的员工信息
MariaDB [newdb3]> select*from emp where mgr is null;
+-------+-------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+---------+---------+--------+
| 5 | 刘备 | 项目经理 | NULL | 1981-09-28 | 1250.00 | 1400.00 | 3 |
| 8 | 观音 | CEO | NULL | 1981-11-17 | 5000.00 | NULL | 1 |
+-------+-------+----------+------+------------+---------+---------+--------+
2 rows in set (0.00 sec)
2. 查询有上级领导的员工姓名 工资和工作
MariaDB [newdb3]> select ename,sal,job from emp where mgr is not null;
+--------+---------+----------+
| ename | sal | job |
+--------+---------+----------+
| 孙悟空 | 800.00 | 销售 |
| 猪八戒 | 1600.00 | 销售 |
| 沙僧 | 1250.00 | 销售 |
| 唐僧 | 2975.00 | 销售经理 |
| 关羽 | 2850.00 | 程序员 |
| 张飞 | 2450.00 | 程序员 |
| 白骨精 | 1500.00 | 人事 |
| 蜘蛛精 | 950.00 | 人事 |
| 黑熊怪 | 3000.00 | 市场 |
+--------+---------+----------+
9 rows in set (0.00 sec)
比较运算符 > < >= <= = !=和<>
1. 查询工资小于等于3000的员工姓名和工资
MariaDB [newdb3]> select ename,sal from emp where sal<=3000;
+--------+---------+
| ename | sal |
+--------+---------+
| 孙悟空 | 800.00 |
| 猪八戒 | 1600.00 |
| 沙僧 | 1250.00 |
| 唐僧 | 2975.00 |
| 刘备 | 1250.00 |
| 关羽 | 2850.00 |
| 张飞 | 2450.00 |
| 白骨精 | 1500.00 |
| 蜘蛛精 | 950.00 |
| 黑熊怪 | 3000.00 |
+--------+---------+
2. 查询不是程序员的员工姓名和工作(两种写法)
MariaDB [newdb3]> select ename,job from emp where job!='程序员';select ename,job from emp where job<>'程序员';
+--------+----------+
| ename | job |
+--------+----------+
| 孙悟空 | 销售 |
| 猪八戒 | 销售 |
| 沙僧 | 销售 |
| 唐僧 | 销售经理 |
| 刘备 | 项目经理 |
| 观音 | CEO |
| 白骨精 | 人事 |
| 蜘蛛精 | 人事 |
| 黑熊怪 | 市场 |
+--------+----------+
9 rows in set (0.00 sec)
+--------+----------+
| ename | job |
+--------+----------+
| 孙悟空 | 销售 |
| 猪八戒 | 销售 |
| 沙僧 | 销售 |
| 唐僧 | 销售经理 |
| 刘备 | 项目经理 |
| 观音 | CEO |
| 白骨精 | 人事 |
| 蜘蛛精 | 人事 |
| 黑熊怪 | 市场 |
+--------+----------+
and和or
- and类似java中的 && ,当需要同时满足多个条件的时候使用
- or类型java中的||, 当多个条件满足一个就行的时候使用
1. 查询1号部门工资大于2000的员工信息
MariaDB [newdb3]> select * from emp where deptno=1 and sal>2000;
+-------+-------+----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+---------+------+--------+
| 4 | 唐僧 | 销售经理 | 8 | 1981-04-02 | 2975.00 | NULL | 1 |
| 8 | 观音 | CEO | NULL | 1981-11-17 | 5000.00 | NULL | 1 |
+-------+-------+----------+------+------------+---------+------+--------+
2. 查询工作是人事或者工资大于3000的员工姓名 工作和工资
MariaDB [newdb3]> select job,sal,ename from emp where job='人事' or sal>3000;
+------+---------+--------+
| job | sal | ename |
+------+---------+--------+
| CEO | 5000.00 | 观音 |
| 人事 | 1500.00 | 白骨精 |
| 人事 | 950.00 | 蜘蛛精 |
+------+---------+--------+
in
- 当查询某个字段的值为多个值得时候使用
1. 查询工资为,3000/1500/5000的员工信息
MariaDB [newdb3]> select*from emp where sal in(1500,3000,5000);
+-------+--------+------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO |
+-------+--------+------+------+------------+---------+------+--------+
| 8 | 观音 | CEO | NULL | 1981-11-17 | 5000.00 | NULL | 1 |
| 9 | 白骨精 | 人事 | 8 | 1981-09-08 | 1500.00 | 0.00 | 2 |
| 11 | 黑熊怪 | 市场 | 8 | 1981-12-03 | 3000.00 | NULL | 2 |
+-------+--------+------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)
between x and y 包含x y
- 当查询某个字段的值在某两个值之间的时候使用
1. 查询工资在1000-2000之前的员工姓名和工资
MariaDB [newdb3]> select ename,sal from emp where sal between 1000 and 2000;
+--------+---------+
| ename | sal |
+--------+---------+
| 猪八戒 | 1600.00 |
| 沙僧 | 1250.00 |
| 刘备 | 1250.00 |
| 白骨精 | 1500.00 |
+--------+---------+
4 rows in set (0.00 sec)
综合练习
1. 查询有上级领导并且是3号部门的员工信息
2. 查询2号部门工资在1000到2000之间的员工姓名 工资和部门编号
3. 查询1号部门工资为800和1600的员工信息
4. 查询1号和2号部门工资高于2000的员工信息
5. 查询员工表中出现的部门编号有哪几个
MariaDB [newdb3]> select*from emp where mgr is not null and deptno=3;
+-------+-------+--------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO |
+-------+-------+--------+------+------------+---------+------+--------+
| 6 | 关羽 | 程序员 | 5 | 1981-05-01 | 2850.00 | NULL | 3 |
| 7 | 张飞 | 程序员 | 5 | 1981-06-09 | 2450.00 | NULL | 3 |
+-------+-------+--------+------+------------+---------+------+--------+
2 rows in set (0.00 sec)
MariaDB [newdb3]> select ename,sal,deptno from emp where deptno=2 and sal between 1000 and 2000 ;
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| 白骨精 | 1500.00 | 2 |
+--------+---------+--------+
1 row in set (0.00 sec)
MariaDB [newdb3]> select*from emp where sal>2000 and deptno in(1,2);
+-------+--------+----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+------+--------+
| 4 | 唐僧 | 销售经理 | 8 | 1981-04-02 | 2975.00 | NULL | 1 |
| 8 | 观音 | CEO | NULL | 1981-11-17 | 5000.00 | NULL | 1 |
| 11 | 黑熊怪 | 市场 | 8 | 1981-12-03 | 3000.00 | NULL | 2 |
+-------+--------+----------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)
MariaDB [newdb3]> select distinct deptno from emp;
+--------+
| deptno |
+--------+
| 1 |
| 3 |
| 2 |
+--------+
3 rows in set (0.00 sec)
MariaDB [newdb3]>
MySQL约束
约束是一种限制,它通过对表的行或列的数据做出限制,来确保表的数据的完整性、唯一性。
MySQL中,常用的几种约束:
1、主键约束 primary key
- 主键约束相当于 唯一约束 + 非空约束 的组合,主键约束列不允许重复,也不允许出现空值。
- 每个表最多只允许一个主键,建立主键约束可以在列级别创建,也可以在表级别创建。
- 当创建主键的约束时,系统默认会在所在的列和列组合上建立对应的唯一索引。
2、外键约束 foreign key
- 外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系
3、 唯一约束unique
- 唯一约束是指定table的列或列组合不能重复,保证数据的唯一性。
- 唯一约束不允许出现重复的值,但是可以为多个null。
- 同一个表可以有多个唯一约束,多个列组合的约束。
- 在创建唯一约束时,如果不给唯一约束名称,就默认和列名相同。
- 唯一约束不仅可以在一个表内创建,而且可以同时多表创建组合唯一约束。
4、非空约束 not null 与 默认值 default
非空约束用于确保当前列的值不为空值,非空约束只能出现在表对象的列上。
Null类型特征:
所有的类型的值都可以是null,包括int、float 等数据类型
5.自增约束 auto_increment
字段名 数据类型 auto_increment
create table classes(class no primary key auto_increment, class_name cahr(20) not null unique)
自增长字段的数据类型必须是整形,并且必须是设置为主键
该列数据自增长,与约束一起使用(与主键一起使用)。每个表中只能有一个自增长约束
6.检查约束 check
check: 指定一个布尔表达式,用于指定对应的值必须满足该表达式(mysql不支持check约束)
主键约束
- 主键: 表示数据唯一性的字段称为主键
- 约束: 创建表时给表字段添加的限制条件
- 主键约束: 限制主键的值 唯一且非空
格式: primary key
create table t1(id int primary key,name varchar(10)); insert into t1 values(1,'aaa'); insert into t1 values(1,'bbb');报错:Duplicate entry '1' for key 'PRIMARY' 主键值重复 insert into t1 values(null,'ccc'); 报错:Column 'id' cannot be null id值不能为null
MariaDB [mydb3]> show tables;
+-----------------+
| Tables_in_mydb3 |
+-----------------+
| t1 |
| t_date |
+-----------------+
2 rows in set (0.00 sec)
MariaDB [mydb3]> drop table t1;
Query OK, 0 rows affected (0.06 sec)
MariaDB [mydb3]> create table t1(id int primary key,name varchar(10));
Query OK, 0 rows affected (0.14 sec)
MariaDB [mydb3]> insert into t1 values(1,'aaa');
Query OK, 1 row affected (0.09 sec)
MariaDB [mydb3]> insert into t1 values(1,'bbb');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
MariaDB [mydb3]> insert into t1 values(null,'ccc');
ERROR 1048 (23000): Column 'id' cannot be null
MariaDB [mydb3]>
自增 auto_increment
- 自增字段给null值会触发自增
- 从历史最大值+1
- 举例: create table t2(id int primary key auto_increment,name varchar(10)); insert into t2 values(null,'aaa'); 1 insert into t2 values(null,'bbb'); 2 insert into t2 values(10,'ccc'); 10 insert into t2 values(null,'ddd'); 11 delete from t2 where id>=10; insert into t2 values(null,'eee'); 12
-
MariaDB [mydb3]> create table t2(id int primary key auto_increment,name varchar(10)); Query OK, 0 rows affected (0.17 sec) MariaDB [mydb3]> insert into t2 values(null,'aaa'); Query OK, 1 row affected (0.07 sec) MariaDB [mydb3]> insert into t2 values(null,'bbb'); Query OK, 1 row affected (0.28 sec) MariaDB [mydb3]> desc t2; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.17 sec) MariaDB [mydb3]> select*from t2; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | bbb | +----+------+ 2 rows in set (0.00 sec) MariaDB [mydb3]> insert into t2 valuse(10,'ccc'); 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 'valuse(10,'ccc')' at line 1 MariaDB [mydb3]> insert into t2 values(10,'ccc'); Query OK, 1 row affected (0.08 sec) MariaDB [mydb3]> insert into t2 values(null,'ddd'); Query OK, 1 row affected (0.09 sec) MariaDB [mydb3]> select*from t2; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | bbb | | 10 | ccc | | 11 | ddd | +----+------+ 4 rows in set (0.00 sec) MariaDB [mydb3]> delete from t2 where id>=10; Query OK, 2 rows affected (0.37 sec) MariaDB [mydb3]> select*from t2; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | bbb | +----+------+ 2 rows in set (0.00 sec) MariaDB [mydb3]> insert into t2 values(null,'eee'); Query OK, 1 row affected (0.08 sec) MariaDB [mydb3]> select*from t2; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | bbb | | 12 | eee | +----+------+ 3 rows in set (0.00 sec) MariaDB [mydb3]>
模糊查询like
- _: 代表1个未知字符
- %: 代表0或多个未知字符
- 举例:
- 以x开头 x%
- 以x结尾 %x
- 包含x %x%
- 第二个字符x _x%
- 倒数第三个是x %x__
- 以x开头倒数第二个是y x%y_
- 查询员工表中姓孙的员工姓名 select ename from emp where ename like '孙%';
-
MariaDB [mydb3]> use newdb3; Database changed MariaDB [newdb3]> select*from emp where ename like '孙%'; +-------+--------+------+------+------------+--------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+--------+------+------+------------+--------+------+--------+ | 1 | 孙悟空 | 销售 | 4 | 1980-12-17 | 800.00 | NULL | 1 | +-------+--------+------+------+------------+--------+------+--------+ 1 row in set (0.09 sec) MariaDB [newdb3]>
- 查询工作中包含销售的员工姓名和工作 select ename,job from emp where job like '%销售%';
-
MariaDB [newdb3]> select ename,job from emp where job like '%销售%'; +--------+----------+ | ename | job | +--------+----------+ | 孙悟空 | 销售 | | 猪八戒 | 销售 | | 沙僧 | 销售 | | 唐僧 | 销售经理 | +--------+----------+ 4 rows in set (0.00 sec)
- 查询名字以精结尾的员工姓名 select ename from emp where ename like '%精';
-
MariaDB [newdb3]> select ename from emp where ename like '%精'; +--------+ | ename | +--------+ | 白骨精 | | 蜘蛛精 | +--------+ 2 rows in set (0.02 sec)
- 查询工作中第二个字是"序"的员工信息 select * from emp where job like '_序%';
-
MariaDB [newdb3]> select*from emp where job like '_序%'; +-------+-------+--------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+-------+--------+------+------------+---------+------+--------+ | 6 | 关羽 | 程序员 | 5 | 1981-05-01 | 2850.00 | NULL | 3 | | 7 | 张飞 | 程序员 | 5 | 1981-06-09 | 2450.00 | NULL | 3 | +-------+-------+--------+------+------------+---------+------+--------+ 2 rows in set (0.00 sec)
排序:搜索结果排序
order by 子句
ORDER BY语句用于根据指定的列对结果集进行排序。
ORDER BY语句默认按照升序对记录进行排序。
如果您希望按照降序对记录进行排序,可以使用DESC关键字。
ASC为升序,desc为降序
ORDER BY子句需写在where子句之后
- 格式: order by 字段名 asc升序(默认)/desc降序
- 查询2号部门员工姓名和工资按照工资升序排序 select ename,sal from emp where deptno=2 order by sal;
-
MariaDB [newdb3]> select ename,sal from emp where deptno=2 order by sal; +--------+---------+ | ename | sal | +--------+---------+ | 蜘蛛精 | 950.00 | | 白骨精 | 1500.00 | | 黑熊怪 | 3000.00 | +--------+---------+
- 查询所有员工信息 按照工资降序 select * from emp order by sal desc;
-
MariaDB [newdb3]> select*from emp order by sal desc; +-------+--------+----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+--------+----------+------+------------+---------+---------+--------+ | 8 | 观音 | CEO | NULL | 1981-11-17 | 5000.00 | NULL | 1 | | 11 | 黑熊怪 | 市场 | 8 | 1981-12-03 | 3000.00 | NULL | 2 | | 4 | 唐僧 | 销售经理 | 8 | 1981-04-02 | 2975.00 | NULL | 1 | | 6 | 关羽 | 程序员 | 5 | 1981-05-01 | 2850.00 | NULL | 3 | | 7 | 张飞 | 程序员 | 5 | 1981-06-09 | 2450.00 | NULL | 3 | | 2 | 猪八戒 | 销售 | 4 | 1981-02-20 | 1600.00 | 300.00 | 1 | | 9 | 白骨精 | 人事 | 8 | 1981-09-08 | 1500.00 | 0.00 | 2 | | 5 | 刘备 | 项目经理 | NULL | 1981-09-28 | 1250.00 | 1400.00 | 3 | | 3 | 沙僧 | 销售 | 4 | 1981-02-22 | 1250.00 | 500.00 | 1 | | 10 | 蜘蛛精 | 人事 | 8 | 1981-12-03 | 950.00 | NULL | 2 | | 1 | 孙悟空 | 销售 | 4 | 1980-12-17 | 800.00 | NULL | 1 | +-------+--------+----------+------+------------+---------+---------+--------+ 11 rows in set (0.00 sec)
- 查询销售人员信息按照工资降序排序 select * from emp where job like '%销售%' order by sal desc;
-
MariaDB [newdb3]> select*from emp where job like '%销售%' order by sal desc; +-------+--------+----------+------+------------+---------+--------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+--------+----------+------+------------+---------+--------+--------+ | 4 | 唐僧 | 销售经理 | 8 | 1981-04-02 | 2975.00 | NULL | 1 | | 2 | 猪八戒 | 销售 | 4 | 1981-02-20 | 1600.00 | 300.00 | 1 | | 3 | 沙僧 | 销售 | 4 | 1981-02-22 | 1250.00 | 500.00 | 1 | | 1 | 孙悟空 | 销售 | 4 | 1980-12-17 | 800.00 | NULL | 1 | +-------+--------+----------+------+------------+---------+--------+--------+
- 多字段排序格式: order by 字段名,字段名
- 查询员工姓名 工资和部门编号 按照部门编号降序排序 select ename,sal,deptno from emp order by deptno desc,sal;
-
MariaDB [newdb3]> select ename,sal,deptno from emp order by deptno desc ,sal; +--------+---------+--------+ | ename | sal | deptno | +--------+---------+--------+ | 刘备 | 1250.00 | 3 | | 张飞 | 2450.00 | 3 | | 关羽 | 2850.00 | 3 | | 蜘蛛精 | 950.00 | 2 | | 白骨精 | 1500.00 | 2 | | 黑熊怪 | 3000.00 | 2 | | 孙悟空 | 800.00 | 1 | | 沙僧 | 1250.00 | 1 | | 猪八戒 | 1600.00 | 1 | | 唐僧 | 2975.00 | 1 | | 观音 | 5000.00 | 1 | +--------+---------+--------+
搜索结果分页 limit 子句
分页查询 limit
limit begin,size
limit子句写在order by子句之后
begin是本页数据的起始行,从0开始
size是本页数据要显示的总行数
- 格式: limit 跳过条数(跳过页数*每页条数),请求条数(每页条数)
- 查询员工表第一页的3条数据 select * from emp limit 0,3;
-
MariaDB [newdb3]> select*from emp limit 0,3; +-------+--------+------+------+------------+---------+--------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+--------+------+------+------------+---------+--------+--------+ | 1 | 孙悟空 | 销售 | 4 | 1980-12-17 | 800.00 | NULL | 1 | | 2 | 猪八戒 | 销售 | 4 | 1981-02-20 | 1600.00 | 300.00 | 1 | | 3 | 沙僧 | 销售 | 4 | 1981-02-22 | 1250.00 | 500.00 | 1 | +-------+--------+------+------+------------+---------+--------+--------+
- 查询员工表第2页的3条数据 select * from emp limit 3,3;
-
MariaDB [newdb3]> select*from emp limit 3,3; +-------+-------+----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+-------+----------+------+------------+---------+---------+--------+ | 4 | 唐僧 | 销售经理 | 8 | 1981-04-02 | 2975.00 | NULL | 1 | | 5 | 刘备 | 项目经理 | NULL | 1981-09-28 | 1250.00 | 1400.00 | 3 | | 6 | 关羽 | 程序员 | 5 | 1981-05-01 | 2850.00 | NULL | 3 | +-------+-------+----------+------+------------+---------+---------+--------+
- 查询员工表第3页的2条数据(每页2条) select * from emp limit 4,2;
-
MariaDB [newdb3]> select*from emp limit 4,2; +-------+-------+----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+-------+----------+------+------------+---------+---------+--------+ | 5 | 刘备 | 项目经理 | NULL | 1981-09-28 | 1250.00 | 1400.00 | 3 | | 6 | 关羽 | 程序员 | 5 | 1981-05-01 | 2850.00 | NULL | 3 | +-------+-------+----------+------+------------+---------+---------+--------+ 2 rows in set (0.00 sec)
- 查询工资最高的前三名的员工信息 select * from emp order by sal desc limit 0,3;
-
MariaDB [newdb3]> select*from emp order by sal desc limit 0,3; +-------+--------+----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+--------+----------+------+------------+---------+------+--------+ | 8 | 观音 | CEO | NULL | 1981-11-17 | 5000.00 | NULL | 1 | | 11 | 黑熊怪 | 市场 | 8 | 1981-12-03 | 3000.00 | NULL | 2 | | 4 | 唐僧 | 销售经理 | 8 | 1981-04-02 | 2975.00 | NULL | 1 | +-------+--------+----------+------+------------+---------+------+--------+
- 查询1号部门工资最少的员工信息 select * from emp where deptno=1 order by sal limit 0,1;
-
MariaDB [newdb3]> select*from emp where deptno=1 order by sal limit 0,1; +-------+--------+------+------+------------+--------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+--------+------+------+------------+--------+------+--------+ | 1 | 孙悟空 | 销售 | 4 | 1980-12-17 | 800.00 | NULL | 1 | +-------+--------+------+------+------------+--------+------+--------+
数值计算+ - * /
- 查询每个员工的姓名 工资及年终奖(5个月的工资) select ename 名字,sal 工资,sal*5 年终奖 from emp;
MariaDB [newdb3]> select ename 名字,sal 工资,sal*5 年终奖 from emp;
+--------+---------+----------+
| 名字 | 工资 | 年终奖 |
+--------+---------+----------+
| 孙悟空 | 800.00 | 4000.00 |
| 猪八戒 | 1600.00 | 8000.00 |
| 沙僧 | 1250.00 | 6250.00 |
| 唐僧 | 2975.00 | 14875.00 |
| 刘备 | 1250.00 | 6250.00 |
| 关羽 | 2850.00 | 14250.00 |
| 张飞 | 2450.00 | 12250.00 |
| 观音 | 5000.00 | 25000.00 |
| 白骨精 | 1500.00 | 7500.00 |
| 蜘蛛精 | 950.00 | 4750.00 |
| 黑熊怪 | 3000.00 | 15000.00 |
+--------+---------+----------+
- 查询每个员工的姓名 工资和涨薪5块钱之后的工资 select ename,sal,sal+5 from emp;
-
MariaDB [newdb3]> select ename 名字,sal 工资,sal+5 涨薪 from emp; +--------+---------+---------+ | 名字 | 工资 | 涨薪 | +--------+---------+---------+ | 孙悟空 | 800.00 | 805.00 | | 猪八戒 | 1600.00 | 1605.00 | | 沙僧 | 1250.00 | 1255.00 | | 唐僧 | 2975.00 | 2980.00 | | 刘备 | 1250.00 | 1255.00 | | 关羽 | 2850.00 | 2855.00 | | 张飞 | 2450.00 | 2455.00 | | 观音 | 5000.00 | 5005.00 | | 白骨精 | 1500.00 | 1505.00 | | 蜘蛛精 | 950.00 | 955.00 | | 黑熊怪 | 3000.00 | 3005.00 | +--------+---------+---------+
聚合函数 汇总搜索结果
- 对查询的多条数据进行统计查询, 统计方式:平均值,最大值,最小值,求和,计数
- 平均值avg(字段名)
- 查询1号部门的平均工资 select avg(sal) from emp where deptno=1;
-
MariaDB [newdb3]> select avg(sal) from emp where deptno=1; +-------------+ | avg(sal) | +-------------+ | 2325.000000 | +-------------+ 1 row in set (0.07 sec)
- 查询程序员的平均工资 select avg(sal) from emp where job='程序员';
-
MariaDB [newdb3]> select avg(sal) from emp where job='程序员'; +-------------+ | avg(sal) | +-------------+ | 2650.000000 | +-------------+ 1 row in set (0.00 sec)
- 最大值max(字段名)
- 查询2号部门的最高工资 select max(sal) from emp where deptno=2;
-
MariaDB [newdb3]> select max(sal) from emp where deptno=2; +----------+ | max(sal) | +----------+ | 3000.00 | +----------+
- 最小值min(字段名)
- 查询有上级领导的员工中的最低工资 select min(sal) from emp where mgr is not null;
-
MariaDB [newdb3]> select min(sal) from emp where mgr is not null; +----------+ | min(sal) | +----------+ | 800.00 | +----------+
- 求和sum(字段名)
- 查询3号部门的工资总和 select sum(sal) from emp where deptno=3;
-
MariaDB [newdb3]> select sum(sal) from emp where deptno=3; +----------+ | sum(sal) | +----------+ | 6550.00 | +----------+
- 计数count(*)
- 查询工资大于2000的员工人数 select count(*) from emp where sal>2000;
-
MariaDB [newdb3]> select count(*) from emp where sal>2000; +----------+ | count(*) | +----------+ | 5 | +----------+
- 查询1号部门工资大于2000的员工人数 select count(*) from emp where deptno=1 and sal>2000;
-
MariaDB [newdb3]> select count(*) from emp where deptno=1 and sal>2000; +----------+ | count(*) | +----------+ | 2 | +----------+
练习题
- 查询员工表中工资高于2000的员工姓名和工资,按照工资升序排序,查询第二页 的2条数据 select ename,sal from emp where sal>2000 order by sal limit 2,2;
- 查询和销售相关的工作的工资总和 select sum(sal) from emp where job like '%销售%';
- 查询程序员人数 select count(*) from emp where job='程序员';
- 查询1号部门中有领导的员工中的最高工资 select max(sal) from emp where deptno=1 and mgr is not null;
- 查询2号部门的最高工资和最低工资 起别名 select max(sal) 最高工资,min(sal) 最低工资 from emp where deptno=2;
- 查询1号部门里面名字中包含空字的员工姓名 select ename from emp where ename like '%空%' and deptno=1;
-
MariaDB [newdb3]> select ename,sal from emp where sal>2000 order by sal limit 1,2; +-------+---------+ | ename | sal | +-------+---------+ | 关羽 | 2850.00 | | 唐僧 | 2975.00 | +-------+---------+ 2 rows in set (0.00 sec) MariaDB [newdb3]> select sum(sal) from emp where job like '%销售%'; +----------+ | sum(sal) | +----------+ | 6625.00 | +----------+ 1 row in set (0.00 sec) MariaDB [newdb3]> select count(*) from emp where job='程序员'; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) MariaDB [newdb3]> select max(sal) from emp where mgr is not null and deptno=1; +----------+ | max(sal) | +----------+ | 2975.00 | +----------+ 1 row in set (0.00 sec) MariaDB [newdb3]> select max(sal)最高工资,min(sal)最低工资 from emp where deptno=2; +----------+----------+ | 最高工资 | 最低工资 | +----------+----------+ | 3000.00 | 950.00 | +----------+----------+ 1 row in set (0.04 sec) MariaDB [newdb3]> select ename from emp where deptno=1 and ename like '%空%'; +--------+ | ename | +--------+ | 孙悟空 | +--------+
分组查询 group by
- 以某个字段作为分组条件,这个字段相同的值为一组,对该组数据进行统计查询
- 查询每个部门的平均工资 select deptno,avg(sal) from emp group by deptno ;
-
MariaDB [newdb3]> select deptno,avg(sal) from emp group by deptno; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 1 | 2325.000000 | | 2 | 1816.666667 | | 3 | 2183.333333 | +--------+-------------+
- 查询每个部门的最高工资 select deptno,max(sal) from emp group by deptno;
-
MariaDB [newdb3]> select deptno,max(sal) from emp group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 1 | 5000.00 | | 2 | 3000.00 | | 3 | 2850.00 | +--------+----------+
- 查询每种工作的人数 select job,count(*) from emp group by job;
-
MariaDB [newdb3]> select job,count(*) from emp group by job; +----------+----------+ | job | count(*) | +----------+----------+ | CEO | 1 | | 人事 | 2 | | 市场 | 1 | | 程序员 | 2 | | 销售 | 3 | | 销售经理 | 1 | | 项目经理 | 1 | +----------+----------+
- 查询每个部门工资高于1500的人数 select deptno,count(*) from emp where sal>1500 group by deptno;
- 查询1号部门和2号部门的最高工资 select deptno,max(sal) from emp where deptno in(1,2) group by deptno;
- 查询1号部门每种工作的人数 select job,count(*) from emp where deptno=1 group by job;
- 查询1号和2号部门中每种工作的工资总和 select job,sum(sal) from emp where deptno in(1,2) group by job;
- 查询每种工作的最低工资 select job,min(sal) from emp group by job;
-
MariaDB [newdb3]> select deptno,count(*) from emp where sal>1500 group by deptno; +--------+----------+ | deptno | count(*) | +--------+----------+ | 1 | 3 | | 2 | 1 | | 3 | 2 | +--------+----------+ 3 rows in set (0.00 sec) MariaDB [newdb3]> select deptno,max(sal) from emp where deptno in(1,2) group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 1 | 5000.00 | | 2 | 3000.00 | +--------+----------+ 2 rows in set (0.05 sec) MariaDB [newdb3]> select job,count(*) from emp where deptno=1 group by job; +----------+----------+ | job | count(*) | +----------+----------+ | CEO | 1 | | 销售 | 3 | | 销售经理 | 1 | +----------+----------+ 3 rows in set (0.00 sec) MariaDB [newdb3]> select job,sun(sal) from emp where deptno in(1,2) group by job; ERROR 1305 (42000): FUNCTION newdb3.sun does not exist MariaDB [newdb3]> select job,sum(sal) from emp where deptno in(1,2) group by job; +----------+----------+ | job | sum(sal) | +----------+----------+ | CEO | 5000.00 | | 人事 | 2450.00 | | 市场 | 3000.00 | | 销售 | 3650.00 | | 销售经理 | 2975.00 | +----------+----------+ 5 rows in set (0.00 sec) MariaDB [newdb3]> select job,min(sal) from emp group by job; +----------+----------+ | job | min(sal) | +----------+----------+ | CEO | 5000.00 | | 人事 | 950.00 | | 市场 | 3000.00 | | 程序员 | 2450.00 | | 销售 | 800.00 | | 销售经理 | 2975.00 | | 项目经理 | 1250.00 | +----------+----------+ 7 rows in set (0.00 sec)
having
- where后面只能写普通字段的条件,不能写聚合函数条件
-
having后面写聚合函数的条件,需要结合分组查询使用.
-
查询每个部门的平均工资,只查询平均工资大于2000的信息 select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
-
查询每种工作人数只查询人数为1的 select job,count() from emp group by job having count()=1; select job,count(*) c from emp group by job having c=1;
- 查询每个部门的平均工资,只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的部门 select deptno,avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>=2000;
-
MariaDB [newdb3]> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 1 | 2325.000000 | | 3 | 2183.333333 | +--------+-------------+ 2 rows in set (0.00 sec) MariaDB [newdb3]> select job,count(*) from emp group by job having count(*)=1; +----------+----------+ | job | count(*) | +----------+----------+ | CEO | 1 | | 市场 | 1 | | 销售经理 | 1 | | 项目经理 | 1 | +----------+----------+ 4 rows in set (0.00 sec) MariaDB [newdb3]> select job,count(*) c from emp group by job having c=1; +----------+---+ | job | c | +----------+---+ | CEO | 1 | | 市场 | 1 | | 销售经理 | 1 | | 项目经理 | 1 | +----------+---+ 4 rows in set (0.00 sec) MariaDB [newdb3]> select deptno,avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>=2000; +--------+-------------+ | deptno | a | +--------+-------------+ | 2 | 2250.000000 | | 3 | 2183.333333 | +--------+-------------+ 2 rows in set (0.05 sec) MariaDB [newdb3]>
各个关键字的顺序
- select 字段信息 from 表名 where 条件 group by 字段 having 聚合函数条件 order by字段 limit....;
子查询(嵌套查询)
- 查询工资高于1号部门平均工资的员工信息
- 得到1号部门的平均工资 select avg(sal) from emp where deptno=1; 2325
- 高于1号平均工资的员工信息 select * from emp where sal>(select avg(sal) from emp where deptno=1);
-
MariaDB [newdb3]> select*from emp where sal>(select avg(sal) from emp where deptno=1); +-------+--------+----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+--------+----------+------+------------+---------+------+--------+ | 4 | 唐僧 | 销售经理 | 8 | 1981-04-02 | 2975.00 | NULL | 1 | | 6 | 关羽 | 程序员 | 5 | 1981-05-01 | 2850.00 | NULL | 3 | | 7 | 张飞 | 程序员 | 5 | 1981-06-09 | 2450.00 | NULL | 3 | | 8 | 观音 | CEO | NULL | 1981-11-17 | 5000.00 | NULL | 1 | | 11 | 黑熊怪 | 市场 | 8 | 1981-12-03 | 3000.00 | NULL | 2 | +-------+--------+----------+------+------------+---------+------+--------+
- 查询最高工资的员工信息 select * from emp where sal=(select max(sal) from emp);
-
MariaDB [newdb3]> select *from emp where sal=(select max(sal) from emp); +-------+-------+------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+-------+------+------+------------+---------+------+--------+ | 8 | 观音 | CEO | NULL | 1981-11-17 | 5000.00 | NULL | 1 | +-------+-------+------+------+------------+---------+------+--------+
- 查询工资高于2号部门最低工资的员工信息 select * from emp where sal>(select min(sal) from emp where deptno=2);
-
MariaDB [newdb3]> select*from emp where sal>(select min(sal) from emp where deptno= 2); +-------+--------+----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+--------+----------+------+------------+---------+---------+--------+ | 2 | 猪八戒 | 销售 | 4 | 1981-02-20 | 1600.00 | 300.00 | 1 | | 3 | 沙僧 | 销售 | 4 | 1981-02-22 | 1250.00 | 500.00 | 1 | | 4 | 唐僧 | 销售经理 | 8 | 1981-04-02 | 2975.00 | NULL | 1 | | 5 | 刘备 | 项目经理 | NULL | 1981-09-28 | 1250.00 | 1400.00 | 3 | | 6 | 关羽 | 程序员 | 5 | 1981-05-01 | 2850.00 | NULL | 3 | | 7 | 张飞 | 程序员 | 5 | 1981-06-09 | 2450.00 | NULL | 3 | | 8 | 观音 | CEO | NULL | 1981-11-17 | 5000.00 | NULL | 1 | | 9 | 白骨精 | 人事 | 8 | 1981-09-08 | 1500.00 | 0.00 | 2 | | 11 | 黑熊怪 | 市场 | 8 | 1981-12-03 | 3000.00 | NULL | 2 | +-------+--------+----------+------+------------+---------+---------+--------+
-
查询和孙悟空相同工作的其他员工信息
select * from emp where job=(select job from emp where ename='孙悟空') and ename!='孙悟空';
- 得到孙悟空工作 select job from emp where ename='孙悟空';
-
MariaDB [newdb3]> select*from emp where job=(select job from emp where ename='孙悟空') and ename!='孙悟空'; +-------+--------+------+------+------------+---------+--------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+--------+------+------+------------+---------+--------+--------+ | 2 | 猪八戒 | 销售 | 4 | 1981-02-20 | 1600.00 | 300.00 | 1 | | 3 | 沙僧 | 销售 | 4 | 1981-02-22 | 1250.00 | 500.00 | 1 | +-------+--------+------+------+------------+---------+--------+--------+
- 查询拿最低工资员工的同事们的信息(同事指同一部门的员工)
- 得到最低工资 select min(sal) from emp;
- 通过最低工资得到部门编号 select deptno from emp where sal=(select min(sal) from emp);
- 根据部门编号查询员工信息 还要去掉最低工资的 select * from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);
MariaDB [newdb3]> select*from emp where deptno=(select deptno from emp where sal = (select min(sal) from emp))and sal!=(select min(sal) from emp);
+-------+--------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+--------+--------+
| 2 | 猪八戒 | 销售 | 4 | 1981-02-20 | 1600.00 | 300.00 | 1 |
| 3 | 沙僧 | 销售 | 4 | 1981-02-22 | 1250.00 | 500.00 | 1 |
| 4 | 唐僧 | 销售经理 | 8 | 1981-04-02 | 2975.00 | NULL | 1 |
| 8 | 观音 | CEO | NULL | 1981-11-17 | 5000.00 | NULL | 1 |
+-------+--------+----------+------+------------+---------+--------+--------+
-
查询白骨精的部门信息(需要用到dept部门表) -得到白骨精的部门编号 select deptno from emp where ename='白骨精'; select * from dept where deptno=(select deptno from emp where ename='白骨精');
-
MariaDB [newdb3]> select*from dept where deptno =(select deptno from emp where ename='白骨精'); +--------+-------+--------+ | DEPTNO | DNAME | LOC | +--------+-------+--------+ | 2 | 妖怪 | 盘丝洞 | +--------+-------+--------+
-
查询所有员工的部门信息(部门只有1,2,3但是部门表里面有1,2,3,4 只根据员工表中出现的部门编号去查询部门信息)
select * from dept where deptno in(select distinct deptno from emp);
- 查询员工表中出现的部门编号 select distinct deptno from emp;
-
MariaDB [newdb3]> select*from dept where deptno in(select distinct deptno from emp); +--------+--------+--------+ | DEPTNO | DNAME | LOC | +--------+--------+--------+ | 1 | 神仙 | 天庭 | | 2 | 妖怪 | 盘丝洞 | | 3 | 普通人 | 北京 | +--------+--------+--------+
综合练习
- 查询没有上级领导的员工编号empno,姓名,工资
MariaDB [newdb3]> select empno,ename,sal from emp where mgr is not null; +-------+--------+---------+ | empno | ename | sal | +-------+--------+---------+ | 1 | 孙悟空 | 800.00 | | 2 | 猪八戒 | 1600.00 | | 3 | 沙僧 | 1250.00 | | 4 | 唐僧 | 2975.00 | | 6 | 关羽 | 2850.00 | | 7 | 张飞 | 2450.00 | | 9 | 白骨精 | 1500.00 | | 10 | 蜘蛛精 | 950.00 | | 11 | 黑熊怪 | 3000.00 | +-------+--------+---------+
- 查询有奖金的员工姓名和奖金
MariaDB [newdb3]> select ename,comm from emp where comm; +--------+---------+ | ename | comm | +--------+---------+ | 猪八戒 | 300.00 | | 沙僧 | 500.00 | | 刘备 | 1400.00 | +--------+---------+
- 查询名字中包含精的员工姓名和工资
MariaDB [newdb3]> select ename,sal from emp where ename like '%精%'; +--------+---------+ | ename | sal | +--------+---------+ | 白骨精 | 1500.00 | | 蜘蛛精 | 950.00 | +--------+---------+
- 查询名字中第二个字是八的员工信息
MariaDB [newdb3]> select*from emp where ename like '_八%'; +-------+--------+------+------+------------+---------+--------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+--------+------+------+------------+---------+--------+--------+ | 2 | 猪八戒 | 销售 | 4 | 1981-02-20 | 1600.00 | 300.00 | 1 | +-------+--------+------+------+------------+---------+--------+--------+
- 查询1号部门工资大于2000的员工信息
MariaDB [newdb3]> select*from emp where sal>2000 and deptno=1; +-------+-------+----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+-------+----------+------+------------+---------+------+--------+ | 4 | 唐僧 | 销售经理 | 8 | 1981-04-02 | 2975.00 | NULL | 1 | | 8 | 观音 | CEO | NULL | 1981-11-17 | 5000.00 | NULL | 1 | +-------+-------+----------+------+------------+---------+------+--------+
- 查询2号部门或者工资低于1500的员工信息
MariaDB [newdb3]> select *from emp where sal<1500 or deptno=2; +-------+--------+----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+--------+----------+------+------------+---------+---------+--------+ | 1 | 孙悟空 | 销售 | 4 | 1980-12-17 | 800.00 | NULL | 1 | | 3 | 沙僧 | 销售 | 4 | 1981-02-22 | 1250.00 | 500.00 | 1 | | 5 | 刘备 | 项目经理 | NULL | 1981-09-28 | 1250.00 | 1400.00 | 3 | | 9 | 白骨精 | 人事 | 8 | 1981-09-08 | 1500.00 | 0.00 | 2 | | 10 | 蜘蛛精 | 人事 | 8 | 1981-12-03 | 950.00 | NULL | 2 | | 11 | 黑熊怪 | 市场 | 8 | 1981-12-03 | 3000.00 | NULL | 2 | +-------+--------+----------+------+------------+---------+---------+--------+
- 查询工资为3000,1500,5000的员工信息按照工资升序排序
MariaDB [newdb3]> select *from emp where sal in(3000,1500,5000) order by sal; +-------+--------+------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+--------+------+------+------------+---------+------+--------+ | 9 | 白骨精 | 人事 | 8 | 1981-09-08 | 1500.00 | 0.00 | 2 | | 11 | 黑熊怪 | 市场 | 8 | 1981-12-03 | 3000.00 | NULL | 2 | | 8 | 观音 | CEO | NULL | 1981-11-17 | 5000.00 | NULL | 1 | +-------+--------+------+------+------------+---------+------+--------+
- 查询3号部门的工资总和
MariaDB [newdb3]> select sum(sal) from emp where deptno=3; +----------+ | sum(sal) | +----------+ | 6550.00 | +----------+
- 查询每个部门工资大于1000的员工人数,按照人数升序排序
MariaDB [newdb3]> select deptno,count(*) c from emp where sal>1000 group by deptno order by c; +--------+---+ | deptno | c | +--------+---+ | 2 | 2 | | 3 | 3 | | 1 | 4 | +--------+---+
- 查询每种工作中有领导的员工人数按照人数降序排序
MariaDB [newdb3]> select job,count(*) c from emp where mgr is not null group by job order by c desc; +----------+---+ | job | c | +----------+---+ | 销售 | 3 | | 程序员 | 2 | | 人事 | 2 | | 销售经理 | 1 | | 市场 | 1 | +----------+---+
- 查询所有员工信息,按照部门编号升序排序,如果部门编号一致则工资降序
MariaDB [newdb3]> select*from emp order by deptno,sal desc; +-------+--------+----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+--------+----------+------+------------+---------+---------+--------+ | 8 | 观音 | CEO | NULL | 1981-11-17 | 5000.00 | NULL | 1 | | 4 | 唐僧 | 销售经理 | 8 | 1981-04-02 | 2975.00 | NULL | 1 | | 2 | 猪八戒 | 销售 | 4 | 1981-02-20 | 1600.00 | 300.00 | 1 | | 3 | 沙僧 | 销售 | 4 | 1981-02-22 | 1250.00 | 500.00 | 1 | | 1 | 孙悟空 | 销售 | 4 | 1980-12-17 | 800.00 | NULL | 1 | | 11 | 黑熊怪 | 市场 | 8 | 1981-12-03 | 3000.00 | NULL | 2 | | 9 | 白骨精 | 人事 | 8 | 1981-09-08 | 1500.00 | 0.00 | 2 | | 10 | 蜘蛛精 | 人事 | 8 | 1981-12-03 | 950.00 | NULL | 2 | | 6 | 关羽 | 程序员 | 5 | 1981-05-01 | 2850.00 | NULL | 3 | | 7 | 张飞 | 程序员 | 5 | 1981-06-09 | 2450.00 | NULL | 3 | | 5 | 刘备 | 项目经理 | NULL | 1981-09-28 | 1250.00 | 1400.00 | 3 | +-------+--------+----------+------+------------+---------+---------+--------+
- 查询有领导的员工,每个部门的编号和最高工资
MariaDB [newdb3]> select deptno,max(sal) from emp where mgr is not null group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 1 | 2975.00 | | 2 | 3000.00 | | 3 | 2850.00 | +--------+----------+
- 查询有领导的员工,按照工资升序排序,第3页的2条数据
MariaDB [newdb3]> select*from emp where mgr is not null order by sal limit 4,2; +-------+--------+--------+------+------------+---------+--------+--------+ | EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO | +-------+--------+--------+------+------------+---------+--------+--------+ | 2 | 猪八戒 | 销售 | 4 | 1981-02-20 | 1600.00 | 300.00 | 1 | | 7 | 张飞 | 程序员 | 5 | 1981-06-09 | 2450.00 | NULL | 3 | +-------+--------+--------+------+------------+---------+--------+--------+
- 查询每个部门的工资总和,只查询有上级领导的员工并且要求工资总和大于5400,最后按照工资总和降序排序,只查询结果中的第一条数据;
MariaDB [newdb3]> select deptno,sum(sal) from emp where mgr is not null group by deptno having sum(sal)>5400 order by sum(sal) desc limit 0,1;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
| 1 | 6625.00 |
+--------+----------+
1 row in set (0.00 sec)
MariaDB [newdb3]> select deptno,sum(sal) s from emp where mgr is not null group by deptno having s>5400 order by s desc limit 0,1;
+--------+---------+
| deptno | s |
+--------+---------+
| 1 | 6625.00 |
+--------+---------+
1 row in set (0.00 sec)
关联关系
创建表时,表与表之间存在的业务关系
外键: 用来建立关系的字段称为外键
有哪些关系:
1. 一对一:
有AB两张表,A表的一条数据对应B表的一条,同时B表的1条也对应A表的一条,称为一对一关系。
如何创建一对一的两张表:在从表(userinfo)中添加外键指向主表(user)的主键。
MariaDB [newdb3]> create table user(id int primary key auto_increment,username varchar(10),password varchar(10))ch
arset=utf8;
Query OK, 0 rows affected (0.15 sec)
MariaDB [newdb3]> create table userinfo(uid int,nick varchar(10),email varchar(20))charset=utf8;
Query OK, 0 rows affected (0.18 sec)
MariaDB [newdb3]> insert into user values(null,'libai','123456');
Query OK, 1 row affected (0.23 sec)
MariaDB [newdb3]>
MariaDB [newdb3]> insert into userinfo values(1,'刺客','xxx@163.com');
Query OK, 1 row affected (0.13 sec)
MariaDB [newdb3]> select*from user,userinfo;
+----+----------+----------+------+------+-------------+
| id | username | password | uid | nick | email |
+----+----------+----------+------+------+-------------+
| 1 | libai | 123456 | 1 | 刺客 | xxx@163.com |
+----+----------+----------+------+------+-------------+
2. 一对多:
有AB两张表,A表的一条数据对应B表的多条,同时B表的1条对应A表的一条,称为一对多关系。
如何创建一对多的两张表: **外键添加在多的表中**
MariaDB [newdb3]> create table team(id int primary key auto_increment,name varchar(10))charset=utf8;
Query OK, 0 rows affected (0.24 sec)
MariaDB [newdb3]> create table player(id int primary key auto_increment,name varchar(10),tid int)charset=utf8;
Query OK, 0 rows affected (0.18 sec)
MariaDB [newdb3]> insert into team values(null,'北京队');
Query OK, 1 row affected (0.12 sec)
MariaDB [newdb3]>
MariaDB [newdb3]> insert into player values(null,'林书豪',1),(null,'王老五',1);
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [newdb3]> select*from team,player;
+----+--------+----+--------+------+
| id | name | id | name | tid |
+----+--------+----+--------+------+
| 1 | 北京队 | 1 | 林书豪 | 1 |
| 1 | 北京队 | 2 | 王老五 | 1 |
+----+--------+----+--------+------+
3. 多对多:
有AB两张表,A表的一条数据对应B表的多条,同时B表的1条对应A表的多条,称为多对多关系。
如何创建多对多的两张表: **创建单独的关系表**
MariaDB [newdb3]> create table student(id int primary key auto_increment,name varchar(10))charset=utf8;
Query OK, 0 rows affected (0.88 sec)
MariaDB [newdb3]> create table teacher(id int primary key auto_increment,name varchar(10))charset=utf8;
Query OK, 0 rows affected (0.20 sec)
MariaDB [newdb3]> create table t_s(tid int,sid int);
ERROR 1050 (42S01): Table 't_s' already exists
MariaDB [newdb3]> drop table t_s;
Query OK, 0 rows affected (0.13 sec)
MariaDB [newdb3]> create table t_s(tid int,sid int);
Query OK, 0 rows affected (0.12 sec)
MariaDB [newdb3]> insert into student values(1,'小明'),(2,'小红'),(3,'小绿');
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [newdb3]> insert into teacher values(1,'苍老师'),(2,'传奇哥');
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [newdb3]> insert into t_s values(1,1),(1,2),(1,3),(2,1);
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [newdb3]> select*from student;
+----+------+
| id | name |
+----+------+
| 1 | 小明 |
| 2 | 小红 |
| 3 | 小绿 |
+----+------+
3 rows in set (0.00 sec)
MariaDB [newdb3]> select*from teacher;
+----+--------+
| id | name |
+----+--------+
| 1 | 苍老师 |
| 2 | 传奇哥 |
+----+--------+
2 rows in set (0.00 sec)
MariaDB [newdb3]> select*from t_s;
+------+------+
| tid | sid |
+------+------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
+------+------+
4 rows in set (0.00 sec)
MariaDB [newdb3]>
关联查询
关联查询: 查询存在关联关系的多张表的查询方式
三种关联查询的方式:1. 等值连接 2. 内连接 3. 外连接
1. 等值连接
格式: select * from A,B where 关联关系 and 其它条件
查询工资高于2000的员工的姓名和对应的部门
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal>2000;
MariaDB [newdb3]> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal>2000;
+--------+--------+
| ename | dname |
+--------+--------+
| 唐僧 | 神仙 |
| 关羽 | 普通人 |
| 张飞 | 普通人 |
| 观音 | 神仙 |
| 黑熊怪 | 妖怪 |
+--------+--------+
5 rows in set (0.16 sec)
2. 内连接
格式: select * from A join B on 关联关系 where 其它条件
查询工资高于2000的员工的姓名和对应的部门名
select e.ename,d.* from emp e join dept d on e.deptno=d.deptno where e.sal>2000;
MariaDB [newdb3]> select e.ename,d.*from emp e join dept d on e.deptno=d.deptno where e.sal>2000;
+--------+--------+--------+--------+
| ename | DEPTNO | DNAME | LOC |
+--------+--------+--------+--------+
| 唐僧 | 1 | 神仙 | 天庭 |
| 观音 | 1 | 神仙 | 天庭 |
| 黑熊怪 | 2 | 妖怪 | 盘丝洞 |
| 关羽 | 3 | 普通人 | 北京 |
| 张飞 | 3 | 普通人 | 北京 |
+--------+--------+--------+--------+
等值连接和内连接查询到的数据是一样的,都是两张表的交集数据
查询每个球队名和对应的球员名
select t.name,p.name from team t join player p on p.tid=t.id;
MariaDB [newdb3]> select t.name,p.name from team t join player p on p.tid=t.id;
+--------+--------+
| name | name |
+--------+--------+
| 北京队 | 林书豪 |
| 北京队 | 王老五 |
+--------+--------+
查询1号部门工资低于3000的员工姓名,工资,部门地址
select e.ename,e.sal,d.loc from emp e join dept d on e.deptno=d.deptno where e.deptno=1 and e.sal<3000;
MariaDB [newdb3]> select e.ename,e.sal,d.loc from emp e join dept d on e.deptno=d.deptno where e.deptno=1 and e.sal<3000;
+--------+---------+------+
| ename | sal | loc |
+--------+---------+------+
| 孙悟空 | 800.00 | 天庭 |
| 猪八戒 | 1600.00 | 天庭 |
| 沙僧 | 1250.00 | 天庭 |
| 唐僧 | 2975.00 | 天庭 |
+--------+---------+------+
3. 外连接:
格式: select * from A left/right join B on 关联关系 where 条件;
等值连接和内连接查询的是两张表的交集数据,而外连接查询的是一张表的全部数据和另外一张表的交集数据.
查询所有员工姓名和对应的部门名
select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno;
MariaDB [newdb3]> select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno;
+--------+--------+
| ename | dname |
+--------+--------+
| 孙悟空 | 神仙 |
| 猪八戒 | 神仙 |
| 沙僧 | 神仙 |
| 唐僧 | 神仙 |
| 刘备 | 普通人 |
| 关羽 | 普通人 |
| 张飞 | 普通人 |
| 观音 | 神仙 |
| 白骨精 | 妖怪 |
| 蜘蛛精 | 妖怪 |
| 黑熊怪 | 妖怪 |
| 灭霸 | NULL |
+--------+--------+
12 rows in set (0.00 sec)
查询所有部门名称,地址和对应的员工姓名,工资
select d.dname,d.loc,e.ename,e.sal from emp e right join dept d on e.deptno=d.deptno;
MariaDB [newdb3]> select d.dname,d.loc,e.ename,e.sal from emp e right join dept d on e.deptno=d.deptno;
+--------+--------+--------+---------+
| dname | loc | ename | sal |
+--------+--------+--------+---------+
| 神仙 | 天庭 | 孙悟空 | 800.00 |
| 神仙 | 天庭 | 猪八戒 | 1600.00 |
| 神仙 | 天庭 | 沙僧 | 1250.00 |
| 神仙 | 天庭 | 唐僧 | 2975.00 |
| 普通人 | 北京 | 刘备 | 1250.00 |
| 普通人 | 北京 | 关羽 | 2850.00 |
| 普通人 | 北京 | 张飞 | 2450.00 |
| 神仙 | 天庭 | 观音 | 5000.00 |
| 妖怪 | 盘丝洞 | 白骨精 | 1500.00 |
| 妖怪 | 盘丝洞 | 蜘蛛精 | 950.00 |
| 妖怪 | 盘丝洞 | 黑熊怪 | 3000.00 |
| 赛亚人 | 外星球 | NULL | NULL |
+--------+--------+--------+---------+
关联查询总结:
如果查询的是多张表的数据,则使用关联查询, 查询的是两张表的交集数据使用等值连接或内连接(推荐),如果查询的是一张表的全部和另外一张表的交集数据则使用外连接.
各个关键字的顺序
select * from A join B on 关联关系 where普通字段条件 group by 字段名 having聚合函数条件 order by 字段名 limit 跳过条数,请求条数;