一、标准SQL语句分类
|
DDL: Data Define Language 定义数据结构 CREATE/DROP/ALTER DML: Data Munipulate Language 操作数据 INSERT/DELETE/UPDATE DQL:Data Query Language 查询数据 SELECT DCL:Data Control Language 控制用户权限 GRANT(授权)/REVOKE(收权) |
二、计算机存储字符
(1)如何存储英文字符
ASCII: 总共有128个，对所有的英文字符及标点符号进行了编码。 Hello -> 72102108108111
Latin-1: 总共有256个，兼容ASCII码，同时对欧洲符号进行了编码。MySQL默认就使用这种编码。
(2)如何存储中文字符
GB2312: 对常用的6千多汉字进行了编码，兼容ASCII编码
GBK: 对2万多个汉字进行了编码，兼容GB2312
BIG5: 台湾繁体字编码，兼容ASCII
Unicode: 对世界上主流的语言常用的字符进行了编码，兼容ASCII，不兼容GBK,GB2312,BIG5等系列。具体分为UTF-8、UTF-16、UTF-32等存储方案。
(3)解决MySQL存储中文乱码
sql脚本文件存储的编码utf8
客户端连接服务器端使用的编码为utf8
服务器端创建数据库存储使用的编码为utf8
三、MySQL中的列类型
创建数据表的时候，指定的列可以存储的数据类型。
CREATE TABLE book( bid 列类型);
(1)数值类型——引号可加可不加
TINYINT 微整型，占1个字节 范围-128~127
SMALLINT 小整型，占2个字节 范围-32768~32767
INT 整型，占4个字节
范围 -2147483648~2147483647
BIGINT 大整型，占8个字节
FLOAT 单精度浮点型，占4个字节，范围3.4e38，范围比INT大的多，可能产生计算误差。
DOUBLE 双精度浮点型，占8个字节，范围比BIGINT大的多
DECIMAL(M,D) 定点小数，不会产生计算误差，M代表总的有效位数，D代表小数点后的有效位数
BOOL 布尔型，只有两个结果TRUE/1、FALSE/0，TRUE和FALSE不能添加引号；真正存储数据的时候，会使用TINYINT。
(2)日期时间类型——必须添加引号
DATE 日期型 '2018-12-31'
TIME 时间型 '14:22:30'
DATETIME 日期时间型 '2018-12-31 14:22:30'
(3)字符串类型——必须添加引号
VARCHAR(M) 变长字符串，不会产生空间浪费，操作速度相对较慢，M最大值是65535
CHAR(M) 定长字符串，可能产生空间浪费，操作速度较快，M最大值是255；用于存储手机号码，身份证号等固定长度的字符。
TEXT(M) 大型变长字符串，最多存2G
|
|
CHAR(5) |
VARCHAR(5) |
|
a |
a\0\0\0\0 |
a\0 |
|
ab |
ab\0\0\0 |
ab\0 |
|
abc |
abc\0\0 |
abc\0 |
例：CREATE TABLE t1(
id SMALLINT,
age TINYINT,
commentCount INT,
price DECIMAL(6,2),
phone CHAR(11),
article VARCHAR(8000),
pubTime DATE
);
四、列约束
MySQL可以对插入的数据进行特定的验证，只有满足条件才允许插入到数据表中，否则被认为是非法的插入。
例如：一个人的性别只能是男或者女，一个人的年龄0~100之间。
(1)主键约束——PRIMARY KEY
声明了主键列上的值不能出现重复，表中查询的记录会按照主键由小到大排序——加快查找的速度；通常主键添加到编号列中。
注意：一个表中只能有一个主键
(2)唯一约束——UNIQUE
声明了唯一约束的列上不能插入重复的值，允许插入NULL，而且允许插入多个NULL
|
NULL表示空，在插入数据时，无法确定要保存的数据。例如：无法确定员工的姓名、工资。 |
(3)非空约束——NOT NULL
声明了非空约束的列上不能插入NULL
(4)默认值约束——DEFAULT
可以使用DEFAULT关键字声明默认值，有两种方式可以应用默认值
INSERT INTO xz_laptop_family VALUES(40,'苹果',DEFAULT);
INSERT INTO xz_laptop_family(fid,fname) VALUES(50,'华硕');
(5)检查约束——CHECK
检查约束可以对插入的数据进行检验
CREATE TABLE student(
age TINYINT CHECK(age>=18 AND age<=60 )
);
MySQL不支持检查约束，会降低数据的插入速度。
(6)外键约束——FOREIGN KEY
声明了外键约束的列，取值必须在另一个表的主键列上出现过，列类型要保持一致，取值可以是NULL
FOREIGN KEY(familyId)
REFERENCES xz_laptop_family(fid)
四、MySQL中的自增列
AUTO_INCREMENT: 自动增长，假入一个列声明了自增列，无需手动赋值，直接设置为NULL，会获取当前的最大值，然后加1插入。
注意：
自增列允许手动赋值
只适用于整数型的主键列上
#设置客户端连接服务器编码为UTF8 SET NAMES UTF8; #丢弃数据库tedu_04如果存在的话 DROP DATABASE IF EXISTS tedu_04; #创建数据库tedu_04存储设置为UTF8 CREATE DATABASE tedu_04 CHARSET=UTF8; #进入该数据库 USE tedu_04; #创建数据表dept CREATE TABLE dept( did SMALLINT PRIMARY KEY AUTO_INCREMENT, dname VARCHAR(3) UNIQUE ); #插入数据 INSERT INTO dept VALUES (10,'研发部'), (20,'市场部'), (30,'运营部'), (40,'测试部'); #创建员工表emp CREATE TABLE emp( eid SMALLINT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(10) , sex BOOL, brithday DATE, salary DECIMAL(8,2), #999999.99 deptld SMALLINT, FOREIGN KEY(deptld) REFERENCES dept(did) ); #插入数据 INSERT INTO emp VALUES(NULL,'Tom',1,'1990-5-5',6000,20); INSERT INTO emp VALUES(NULL,'Jerry',0,'1991-8-20',7000,10); INSERT INTO emp VALUES(NULL,'David',1,'1995-10-20',3000,30); INSERT INTO emp VALUES(NULL,'Maria',0,'1992-3-20',5000,10); INSERT INTO emp VALUES(NULL,'Leo',1,'1993-12-3',8000,20); INSERT INTO emp VALUES(NULL,'Black',1,'1991-1-3',4000,10); INSERT INTO emp VALUES(NULL,'Peter',1,'1990-12-3',10000,10); INSERT INTO emp VALUES(NULL,'Franc',1,'1994-12-3',6000,30); INSERT INTO emp VALUES(NULL,'Tacy',1,'1991-12-3',9000,10); INSERT INTO emp VALUES(NULL,'Lucy',0,'1995-12-3',10000,20); INSERT INTO emp VALUES(NULL,'Jone',1,'1993-12-3',8000,30); INSERT INTO emp VALUES(NULL,'Lily',0,'1992-12-3',12000,10); INSERT INTO emp VALUES(NULL,'Lisa',0,'1989-12-3',8000,10); INSERT INTO emp VALUES(NULL,'King',1,'1988-12-3',10000,10); INSERT INTO emp VALUES(NULL,'Brown',1,'1993-12-3',22000,NULL);
五、简单查询
(1)查询特定的列
示例：查询所有员工的姓名、工资、生日
MariaDB [tedu_04]> SELECT ename,salary,birthday FROM emp; +-------+----------+------------+ | ename | salary | birthday | +-------+----------+------------+ | Tom | 6000.00 | 0000-00-00 | | Jerry | 7000.00 | 0000-00-00 | | David | 3000.00 | 0000-00-00 | | Maria | 5000.00 | 0000-00-00 | | Leo | 8000.00 | 0000-00-00 | | Black | 4000.00 | 0000-00-00 | | Peter | 10000.00 | 0000-00-00 | | Franc | 6000.00 | 0000-00-00 | | Tacy | 9000.00 | 0000-00-00 | | Lucy | 10000.00 | 0000-00-00 | | Jone | 8000.00 | 0000-00-00 | | Lily | 12000.00 | 0000-00-00 | | Lisa | 8000.00 | 0000-00-00 | | King | 10000.00 | 0000-00-00 | | Brown | 22000.00 | 0000-00-00 | +-------+----------+------------+ 15 rows in set (0.00 sec)
示例：查询所有员工的编号，姓名，性别，所属部门编号
MariaDB [tedu_04]> SELECT eid,ename,sex,deptld FROM emp; +-----+-------+------+--------+ | eid | ename | sex | deptld | +-----+-------+------+--------+ | 1 | Tom | 1 | 20 | | 2 | Jerry | 0 | 10 | | 3 | David | 1 | 30 | | 4 | Maria | 0 | 10 | | 5 | Leo | 1 | 20 | | 6 | Black | 1 | 10 | | 7 | Peter | 1 | 10 | | 8 | Franc | 1 | 30 | | 9 | Tacy | 1 | 10 | | 10 | Lucy | 0 | 20 | | 11 | Jone | 1 | 30 | | 12 | Lily | 0 | 10 | | 13 | Lisa | 0 | 10 | | 14 | King | 1 | 10 | | 15 | Brown | 1 | NULL | +-----+-------+------+--------+ 15 rows in set (0.00 sec)
(2)查询所有的列
SELECT * FROM emp;
SELECT eid,ename,sex,birthday,salary,deptId FROM emp;
MariaDB [tedu_04]> SELECT * FROM emp; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptld | +-----+-------+------+------------+----------+--------+ | 1 | Tom | 1 | 0000-00-00 | 6000.00 | 20 | | 2 | Jerry | 0 | 0000-00-00 | 7000.00 | 10 | | 3 | David | 1 | 0000-00-00 | 3000.00 | 30 | | 4 | Maria | 0 | 0000-00-00 | 5000.00 | 10 | | 5 | Leo | 1 | 0000-00-00 | 8000.00 | 20 | | 6 | Black | 1 | 0000-00-00 | 4000.00 | 10 | | 7 | Peter | 1 | 0000-00-00 | 10000.00 | 10 | | 8 | Franc | 1 | 0000-00-00 | 6000.00 | 30 | | 9 | Tacy | 1 | 0000-00-00 | 9000.00 | 10 | | 10 | Lucy | 0 | 0000-00-00 | 10000.00 | 20 | | 11 | Jone | 1 | 0000-00-00 | 8000.00 | 30 | | 12 | Lily | 0 | 0000-00-00 | 12000.00 | 10 | | 13 | Lisa | 0 | 0000-00-00 | 8000.00 | 10 | | 14 | King | 1 | 0000-00-00 | 10000.00 | 10 | | 15 | Brown | 1 | 0000-00-00 | 22000.00 | NULL | +-----+-------+------+------------+----------+--------+ 15 rows in set (0.00 sec)
(3)给列起别名
示例：查询所有员工的姓名和工资，使用汉字的别名显示
MariaDB [tedu_04]> SELECT ename AS 姓名,salary AS 工资 FROM emp; +-------+----------+ | 姓名 | 工资 | +-------+----------+ | Tom | 6000.00 | | Jerry | 7000.00 | | David | 3000.00 | | Maria | 5000.00 | | Leo | 8000.00 | | Black | 4000.00 | | Peter | 10000.00 | | Franc | 6000.00 | | Tacy | 9000.00 | | Lucy | 10000.00 | | Jone | 8000.00 | | Lily | 12000.00 | | Lisa | 8000.00 | | King | 10000.00 | | Brown | 22000.00 | +-------+----------+ 15 rows in set (0.00 sec)
示例：查询所有员工的编号，姓名，性别，生日，用中文别名
MariaDB [tedu_04]> SELECT eid AS 编号,ename AS 姓名,sex AS 性别,birthday AS 生日 FROM emp; +------+-------+------+------------+ | 编号 | 姓名 | 性别 | 生日 | +------+-------+------+------------+ | 1 | Tom | 1 | 0000-00-00 | | 2 | Jerry | 0 | 0000-00-00 | | 3 | David | 1 | 0000-00-00 | | 4 | Maria | 0 | 0000-00-00 | | 5 | Leo | 1 | 0000-00-00 | | 6 | Black | 1 | 0000-00-00 | | 7 | Peter | 1 | 0000-00-00 | | 8 | Franc | 1 | 0000-00-00 | | 9 | Tacy | 1 | 0000-00-00 | | 10 | Lucy | 0 | 0000-00-00 | | 11 | Jone | 1 | 0000-00-00 | | 12 | Lily | 0 | 0000-00-00 | | 13 | Lisa | 0 | 0000-00-00 | | 14 | King | 1 | 0000-00-00 | | 15 | Brown | 1 | 0000-00-00 | +------+-------+------+------------+ 15 rows in set (0.00 sec)
示例：查询所有员工的编号，姓名，使用1个英文字母别名 （注意：AS关键字是可以省略的，但要保留空格。）
MariaDB [tedu_04]> SELECT eid a,ename b FROM emp; +----+-------+ | a | b | +----+-------+ | 1 | Tom | | 2 | Jerry | | 3 | David | | 4 | Maria | | 5 | Leo | | 6 | Black | | 7 | Peter | | 8 | Franc | | 9 | Tacy | | 10 | Lucy | | 11 | Jone | | 12 | Lily | | 13 | Lisa | | 14 | King | | 15 | Brown | +----+-------+ 15 rows in set (0.00 sec)
(4)只显示不同的记录/合并相同的记录（distinct）
示例：查询出员工都在哪些部门（显示不同的记录）
MariaDB [tedu_04]> SELECT DISTINCT deptid FROM emp; +--------+ | deptid | +--------+ | NULL | | 10 | | 20 | | 30 | +--------+ 4 rows in set (0.00 sec)
*示例：修改列名
MariaDB [tedu_04]> alter table emp change deptld deptid smallint; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
示例：查询出都有哪些性别的员工
MariaDB [tedu_04]> SELECT DISTINCT sex FROM emp; +------+ | sex | +------+ | 1 | | 0 | +------+ 2 rows in set (0.00 sec)
(5)在查询时执行计算
示例：计算2*33/47-8+10
MariaDB [tedu_04]> SELECT 2*33/47-8+10; +--------------+ | 2*33/47-8+10 | +--------------+ | 3.4043 | +--------------+ 1 row in set (0.00 sec)
示例：查询出所有员工的姓名及其年薪
MariaDB [tedu_04]> SELECT ename,salary*12 FROM emp; +-------+-----------+ | ename | salary*12 | +-------+-----------+ | Tom | 72000.00 | | Jerry | 84000.00 | | David | 36000.00 | | Maria | 60000.00 | | Leo | 96000.00 | | Black | 48000.00 | | Peter | 120000.00 | | Franc | 72000.00 | | Tacy | 108000.00 | | Lucy | 120000.00 | | Jone | 96000.00 | | Lily | 144000.00 | | Lisa | 96000.00 | | King | 120000.00 | | Brown | 264000.00 | +-------+-----------+ 15 rows in set (0.00 sec)
示例：假设每个员工工资增加500元，年终奖5000元，查询所有员工的姓名及其年薪，要给列起别名
MariaDB [tedu_04]> SELECT ename AS 姓名,(salary+500)*12+5000 AS 年薪 FROM emp; +-------+-----------+ | 姓名 | 年薪 | +-------+-----------+ | Tom | 83000.00 | | Jerry | 95000.00 | | David | 47000.00 | | Maria | 71000.00 | | Leo | 107000.00 | | Black | 59000.00 | | Peter | 131000.00 | | Franc | 83000.00 | | Tacy | 119000.00 | | Lucy | 131000.00 | | Jone | 107000.00 | | Lily | 155000.00 | | Lisa | 107000.00 | | King | 131000.00 | | Brown | 275000.00 | +-------+-----------+ 15 rows in set (0.00 sec
(6)查询结果集的排序（ORDER BY 可以按照数值、日期/时间、字符串来排序 默认按照ASC升序排序）
示例：查询所有的部门，结果按照部门编号升序排序
MariaDB [tedu_04]> SELECT * FROM dept ORDER BY did ASC; #ascendant +-----+--------+ | did | dname | +-----+--------+ | 10 | 研发部 | | 20 | 市场部 | | 30 | 运营部 | | 40 | 测试部 | +-----+--------+ 4 rows in set (0.00 sec)
示例：查询所有的部门，结果按照部门编号降序排序
MariaDB [tedu_04]> SELECT * FROM dept ORDER BY did DESC; #descendant +-----+--------+ | did | dname | +-----+--------+ | 40 | 测试部 | | 30 | 运营部 | | 20 | 市场部 | | 10 | 研发部 | +-----+--------+ 4 rows in set (0.00 sec)
示例：查询所有员工信息，结果按照工资由大到小排序
MariaDB [tedu_04]> SELECT * FROM emp ORDER BY salary DESC; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptid | +-----+-------+------+------------+----------+--------+ | 15 | Brown | 1 | 0000-00-00 | 22000.00 | NULL | | 12 | Lily | 0 | 0000-00-00 | 12000.00 | 10 | | 14 | King | 1 | 0000-00-00 | 10000.00 | 10 | | 7 | Peter | 1 | 0000-00-00 | 10000.00 | 10 | | 10 | Lucy | 0 | 0000-00-00 | 10000.00 | 20 | | 9 | Tacy | 1 | 0000-00-00 | 9000.00 | 10 | | 13 | Lisa | 0 | 0000-00-00 | 8000.00 | 10 | | 5 | Leo | 1 | 0000-00-00 | 8000.00 | 20 | | 11 | Jone | 1 | 0000-00-00 | 8000.00 | 30 | | 2 | Jerry | 0 | 0000-00-00 | 7000.00 | 10 | | 8 | Franc | 1 | 0000-00-00 | 6000.00 | 30 | | 1 | Tom | 1 | 0000-00-00 | 6000.00 | 20 | | 4 | Maria | 0 | 0000-00-00 | 5000.00 | 10 | | 6 | Black | 1 | 0000-00-00 | 4000.00 | 10 | | 3 | David | 1 | 0000-00-00 | 3000.00 | 30 | +-----+-------+------+------------+----------+--------+ 15 rows in set (0.00 sec)
示例：查询所有员工，结果按照年龄由大到小排序
MariaDB [tedu_04]> SELECT * FROM emp ORDER BY birthday ASC; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptid | +-----+-------+------+------------+----------+--------+ | 1 | Tom | 1 | 0000-00-00 | 6000.00 | 20 | | 14 | King | 1 | 0000-00-00 | 10000.00 | 10 | | 13 | Lisa | 0 | 0000-00-00 | 8000.00 | 10 | | 12 | Lily | 0 | 0000-00-00 | 12000.00 | 10 | | 11 | Jone | 1 | 0000-00-00 | 8000.00 | 30 | | 10 | Lucy | 0 | 0000-00-00 | 10000.00 | 20 | | 9 | Tacy | 1 | 0000-00-00 | 9000.00 | 10 | | 8 | Franc | 1 | 0000-00-00 | 6000.00 | 30 | | 7 | Peter | 1 | 0000-00-00 | 10000.00 | 10 | | 6 | Black | 1 | 0000-00-00 | 4000.00 | 10 | | 5 | Leo | 1 | 0000-00-00 | 8000.00 | 20 | | 4 | Maria | 0 | 0000-00-00 | 5000.00 | 10 | | 3 | David | 1 | 0000-00-00 | 3000.00 | 30 | | 2 | Jerry | 0 | 0000-00-00 | 7000.00 | 10 | | 15 | Brown | 1 | 0000-00-00 | 22000.00 | NULL | +-----+-------+------+------------+----------+--------+ 15 rows in set (0.00 sec)
示例：查询所有员工，结果按照年龄由小到大排序
MariaDB [tedu_04]> SELECT * FROM emp ORDER BY birthday DESC; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptid | +-----+-------+------+------------+----------+--------+ | 1 | Tom | 1 | 0000-00-00 | 6000.00 | 20 | | 14 | King | 1 | 0000-00-00 | 10000.00 | 10 | | 13 | Lisa | 0 | 0000-00-00 | 8000.00 | 10 | | 12 | Lily | 0 | 0000-00-00 | 12000.00 | 10 | | 11 | Jone | 1 | 0000-00-00 | 8000.00 | 30 | | 10 | Lucy | 0 | 0000-00-00 | 10000.00 | 20 | | 9 | Tacy | 1 | 0000-00-00 | 9000.00 | 10 | | 8 | Franc | 1 | 0000-00-00 | 6000.00 | 30 | | 7 | Peter | 1 | 0000-00-00 | 10000.00 | 10 | | 6 | Black | 1 | 0000-00-00 | 4000.00 | 10 | | 5 | Leo | 1 | 0000-00-00 | 8000.00 | 20 | | 4 | Maria | 0 | 0000-00-00 | 5000.00 | 10 | | 3 | David | 1 | 0000-00-00 | 3000.00 | 30 | | 2 | Jerry | 0 | 0000-00-00 | 7000.00 | 10 | | 15 | Brown | 1 | 0000-00-00 | 22000.00 | NULL | +-----+-------+------+------------+----------+--------+ 15 rows in set (0.00 sec)
示例：查询所有员工，结果按照姓名的升序排序
MariaDB [tedu_04]> SELECT * FROM emp ORDER BY ename; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptid | +-----+-------+------+------------+----------+--------+ | 6 | Black | 1 | 0000-00-00 | 4000.00 | 10 | | 15 | Brown | 1 | 0000-00-00 | 22000.00 | NULL | | 3 | David | 1 | 0000-00-00 | 3000.00 | 30 | | 8 | Franc | 1 | 0000-00-00 | 6000.00 | 30 | | 2 | Jerry | 0 | 0000-00-00 | 7000.00 | 10 | | 11 | Jone | 1 | 0000-00-00 | 8000.00 | 30 | | 14 | King | 1 | 0000-00-00 | 10000.00 | 10 | | 5 | Leo | 1 | 0000-00-00 | 8000.00 | 20 | | 12 | Lily | 0 | 0000-00-00 | 12000.00 | 10 | | 13 | Lisa | 0 | 0000-00-00 | 8000.00 | 10 | | 10 | Lucy | 0 | 0000-00-00 | 10000.00 | 20 | | 4 | Maria | 0 | 0000-00-00 | 5000.00 | 10 | | 7 | Peter | 1 | 0000-00-00 | 10000.00 | 10 | | 9 | Tacy | 1 | 0000-00-00 | 9000.00 | 10 | | 1 | Tom | 1 | 0000-00-00 | 6000.00 | 20 | +-----+-------+------+------------+----------+--------+ 15 rows in set (0.00 sec)
示例：查询所有员工，结果按照工资降序排序，如果工资相同，按照姓名排序
MariaDB [tedu_04]> SELECT * FROM emp ORDER BY salary DESC,ename; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptid | +-----+-------+------+------------+----------+--------+ | 15 | Brown | 1 | 0000-00-00 | 22000.00 | NULL | | 12 | Lily | 0 | 0000-00-00 | 12000.00 | 10 | | 14 | King | 1 | 0000-00-00 | 10000.00 | 10 | | 10 | Lucy | 0 | 0000-00-00 | 10000.00 | 20 | | 7 | Peter | 1 | 0000-00-00 | 10000.00 | 10 | | 9 | Tacy | 1 | 0000-00-00 | 9000.00 | 10 | | 11 | Jone | 1 | 0000-00-00 | 8000.00 | 30 | | 5 | Leo | 1 | 0000-00-00 | 8000.00 | 20 | | 13 | Lisa | 0 | 0000-00-00 | 8000.00 | 10 | | 2 | Jerry | 0 | 0000-00-00 | 7000.00 | 10 | | 8 | Franc | 1 | 0000-00-00 | 6000.00 | 30 | | 1 | Tom | 1 | 0000-00-00 | 6000.00 | 20 | | 4 | Maria | 0 | 0000-00-00 | 5000.00 | 10 | | 6 | Black | 1 | 0000-00-00 | 4000.00 | 10 | | 3 | David | 1 | 0000-00-00 | 3000.00 | 30 | +-----+-------+------+------------+----------+--------+ 15 rows in set (0.00 sec)
示例：查询所有员工，结果按照性别排序，如果性别相同，按照工资的升序排序
MariaDB [tedu_04]> SELECT * FROM emp ORDER BY sex,salary; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptid | +-----+-------+------+------------+----------+--------+ | 4 | Maria | 0 | 0000-00-00 | 5000.00 | 10 | | 2 | Jerry | 0 | 0000-00-00 | 7000.00 | 10 | | 13 | Lisa | 0 | 0000-00-00 | 8000.00 | 10 | | 10 | Lucy | 0 | 0000-00-00 | 10000.00 | 20 | | 12 | Lily | 0 | 0000-00-00 | 12000.00 | 10 | | 3 | David | 1 | 0000-00-00 | 3000.00 | 30 | | 6 | Black | 1 | 0000-00-00 | 4000.00 | 10 | | 1 | Tom | 1 | 0000-00-00 | 6000.00 | 20 | | 8 | Franc | 1 | 0000-00-00 | 6000.00 | 30 | | 11 | Jone | 1 | 0000-00-00 | 8000.00 | 30 | | 5 | Leo | 1 | 0000-00-00 | 8000.00 | 20 | | 9 | Tacy | 1 | 0000-00-00 | 9000.00 | 10 | | 7 | Peter | 1 | 0000-00-00 | 10000.00 | 10 | | 14 | King | 1 | 0000-00-00 | 10000.00 | 10 | | 15 | Brown | 1 | 0000-00-00 | 22000.00 | NULL | +-----+-------+------+------------+----------+--------+ 15 rows in set (0.00 sec)
(7)条件查询
示例：查询出编号为5的员工所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE eid=5; +-----+-------+------+------------+---------+--------+ | eid | ename | sex | birthday | salary | deptid | +-----+-------+------+------------+---------+--------+ | 5 | Leo | 1 | 0000-00-00 | 8000.00 | 20 | +-----+-------+------+------------+---------+--------+ 1 row in set (0.00 sec)
示例：查询出姓名为king的员工的编号，工资，生日。
MariaDB [tedu_04]> SELECT eid,salary,birthday FROM emp WHERE ename='king'; +-----+----------+------------+ | eid | salary | birthday | +-----+----------+------------+ | 14 | 10000.00 | 0000-00-00 | +-----+----------+------------+ 1 row in set (0.00 sec)
示例：查询出20号部门下所有员工信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE deptId=20; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptid | +-----+-------+------+------------+----------+--------+ | 1 | Tom | 1 | 0000-00-00 | 6000.00 | 20 | | 5 | Leo | 1 | 0000-00-00 | 8000.00 | 20 | | 10 | Lucy | 0 | 0000-00-00 | 10000.00 | 20 | +-----+-------+------+------------+----------+--------+ 3 rows in set (0.00 sec)
示例：查询出男员工的所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE sex=1; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptid | +-----+-------+------+------------+----------+--------+ | 1 | Tom | 1 | 0000-00-00 | 6000.00 | 20 | | 3 | David | 1 | 0000-00-00 | 3000.00 | 30 | | 5 | Leo | 1 | 0000-00-00 | 8000.00 | 20 | | 6 | Black | 1 | 0000-00-00 | 4000.00 | 10 | | 7 | Peter | 1 | 0000-00-00 | 10000.00 | 10 | | 8 | Franc | 1 | 0000-00-00 | 6000.00 | 30 | | 9 | Tacy | 1 | 0000-00-00 | 9000.00 | 10 | | 11 | Jone | 1 | 0000-00-00 | 8000.00 | 30 | | 14 | King | 1 | 0000-00-00 | 10000.00 | 10 | | 15 | Brown | 1 | 0000-00-00 | 22000.00 | NULL | +-----+-------+------+------------+----------+--------+ 10 rows in set (0.00 sec)
示例：查询出工资大于等于5000的员工所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE salary>=5000; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptid | +-----+-------+------+------------+----------+--------+ | 1 | Tom | 1 | 0000-00-00 | 6000.00 | 20 | | 2 | Jerry | 0 | 0000-00-00 | 7000.00 | 10 | | 4 | Maria | 0 | 0000-00-00 | 5000.00 | 10 | | 5 | Leo | 1 | 0000-00-00 | 8000.00 | 20 | | 7 | Peter | 1 | 0000-00-00 | 10000.00 | 10 | | 8 | Franc | 1 | 0000-00-00 | 6000.00 | 30 | | 9 | Tacy | 1 | 0000-00-00 | 9000.00 | 10 | | 10 | Lucy | 0 | 0000-00-00 | 10000.00 | 20 | | 11 | Jone | 1 | 0000-00-00 | 8000.00 | 30 | | 12 | Lily | 0 | 0000-00-00 | 12000.00 | 10 | | 13 | Lisa | 0 | 0000-00-00 | 8000.00 | 10 | | 14 | King | 1 | 0000-00-00 | 10000.00 | 10 | | 15 | Brown | 1 | 0000-00-00 | 22000.00 | NULL | +-----+-------+------+------------+----------+--------+ 13 rows in set (0.00 sec)
|
* >= <= > < = !=(不等于) |
示例：查询出1991-1-1后出生的员工所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE birthday>'1991-1-1'; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptld | +-----+-------+------+------------+----------+--------+ | 2 | Jerry | 0 | 1991-08-20 | 7000.00 | 10 | | 3 | David | 1 | 1995-10-20 | 3000.00 | 30 | | 4 | Maria | 0 | 1992-03-20 | 5000.00 | 10 | | 5 | Leo | 1 | 1993-12-03 | 8000.00 | 20 | | 6 | Black | 1 | 1991-01-03 | 4000.00 | 10 | | 8 | Franc | 1 | 1994-12-03 | 6000.00 | 30 | | 9 | Tacy | 1 | 1991-12-03 | 9000.00 | 10 | | 10 | Lucy | 0 | 1995-12-03 | 10000.00 | 20 | | 11 | Jone | 1 | 1993-12-03 | 8000.00 | 30 | | 12 | Lily | 0 | 1992-12-03 | 12000.00 | 10 | | 15 | Brown | 1 | 1993-12-03 | 22000.00 | NULL | +-----+-------+------+------------+----------+--------+ 11 rows in set (0.00 sec)
示例：查询出不在10号部门的员工所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE deptld!=10; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptld | +-----+-------+------+------------+----------+--------+ | 1 | Tom | 1 | 1990-05-05 | 6000.00 | 20 | | 3 | David | 1 | 1995-10-20 | 3000.00 | 30 | | 5 | Leo | 1 | 1993-12-03 | 8000.00 | 20 | | 8 | Franc | 1 | 1994-12-03 | 6000.00 | 30 | | 10 | Lucy | 0 | 1995-12-03 | 10000.00 | 20 | | 11 | Jone | 1 | 1993-12-03 | 8000.00 | 30 | +-----+-------+------+------------+----------+--------+ 6 rows in set (0.00 sec)
示例：查询出没有明确部门的员工所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE deptld IS NULL; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptld | +-----+-------+------+------------+----------+--------+ | 15 | Brown | 1 | 1993-12-03 | 22000.00 | NULL | +-----+-------+------+------------+----------+--------+ 1 row in set (0.00 sec)
示例：查询出有明确部门的员工所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE deptld IS NOT NULL; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptld | +-----+-------+------+------------+----------+--------+ | 1 | Tom | 1 | 1990-05-05 | 6000.00 | 20 | | 2 | Jerry | 0 | 1991-08-20 | 7000.00 | 10 | | 3 | David | 1 | 1995-10-20 | 3000.00 | 30 | | 4 | Maria | 0 | 1992-03-20 | 5000.00 | 10 | | 5 | Leo | 1 | 1993-12-03 | 8000.00 | 20 | | 6 | Black | 1 | 1991-01-03 | 4000.00 | 10 | | 7 | Peter | 1 | 1990-12-03 | 10000.00 | 10 | | 8 | Franc | 1 | 1994-12-03 | 6000.00 | 30 | | 9 | Tacy | 1 | 1991-12-03 | 9000.00 | 10 | | 10 | Lucy | 0 | 1995-12-03 | 10000.00 | 20 | | 11 | Jone | 1 | 1993-12-03 | 8000.00 | 30 | | 12 | Lily | 0 | 1992-12-03 | 12000.00 | 10 | | 13 | Lisa | 0 | 1989-12-03 | 8000.00 | 10 | | 14 | King | 1 | 1988-12-03 | 10000.00 | 10 | +-----+-------+------+------------+----------+--------+ 14 rows in set (0.00 sec)
示例：查询出工资大于6000的女员工所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE salary>6000 AND sex=0; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptld | +-----+-------+------+------------+----------+--------+ | 2 | Jerry | 0 | 1991-08-20 | 7000.00 | 10 | | 10 | Lucy | 0 | 1995-12-03 | 10000.00 | 20 | | 12 | Lily | 0 | 1992-12-03 | 12000.00 | 10 | | 13 | Lisa | 0 | 1989-12-03 | 8000.00 | 10 | +-----+-------+------+------------+----------+--------+ 4 rows in set (0.00 sec)
示例：查询出工资在5000~7000之间的员工所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE salary>=5000 AND salary<=7000; +-----+-------+------+------------+---------+--------+ | eid | ename | sex | birthday | salary | deptld | +-----+-------+------+------------+---------+--------+ | 1 | Tom | 1 | 1990-05-05 | 6000.00 | 20 | | 2 | Jerry | 0 | 1991-08-20 | 7000.00 | 10 | | 4 | Maria | 0 | 1992-03-20 | 5000.00 | 10 | | 8 | Franc | 1 | 1994-12-03 | 6000.00 | 30 | +-----+-------+------+------------+---------+--------+ 4 rows in set (0.00 sec)
或者： SELECT * FROM emp WHERE salary BETWEEN 5000 AND 700
示例：查询出工资为5000以下，7000以上的员工所有信息。
SELECT * FROM emp WHERE salary<5000 OR salary>7000;
SELECT * FROM emp WHERE salary NOT BETWEEN 5000 AND 7000;
MariaDB [tedu_04]> SELECT * FROM emp WHERE salary<5000 OR salary>7000; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptld | +-----+-------+------+------------+----------+--------+ | 3 | David | 1 | 1995-10-20 | 3000.00 | 30 | | 5 | Leo | 1 | 1993-12-03 | 8000.00 | 20 | | 6 | Black | 1 | 1991-01-03 | 4000.00 | 10 | | 7 | Peter | 1 | 1990-12-03 | 10000.00 | 10 | | 9 | Tacy | 1 | 1991-12-03 | 9000.00 | 10 | | 10 | Lucy | 0 | 1995-12-03 | 10000.00 | 20 | | 11 | Jone | 1 | 1993-12-03 | 8000.00 | 30 | | 12 | Lily | 0 | 1992-12-03 | 12000.00 | 10 | | 13 | Lisa | 0 | 1989-12-03 | 8000.00 | 10 | | 14 | King | 1 | 1988-12-03 | 10000.00 | 10 | | 15 | Brown | 1 | 1993-12-03 | 22000.00 | NULL | +-----+-------+------+------------+----------+--------+ 11 rows in set (0.00 sec)
示例：查询出1990之前和1993以后出生的员工所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE birthday<'1990-1-1' OR birthday>'1993-12-31'; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptld | +-----+-------+------+------------+----------+--------+ | 3 | David | 1 | 1995-10-20 | 3000.00 | 30 | | 8 | Franc | 1 | 1994-12-03 | 6000.00 | 30 | | 10 | Lucy | 0 | 1995-12-03 | 10000.00 | 20 | | 13 | Lisa | 0 | 1989-12-03 | 8000.00 | 10 | | 14 | King | 1 | 1988-12-03 | 10000.00 | 10 | +-----+-------+------+------------+----------+--------+ 5 rows in set (0.00 sec)
示例：查询出1993年出生的员工所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE birthday>='1993-1-1' AND birthday<='1993-12-31'; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptld | +-----+-------+------+------------+----------+--------+ | 5 | Leo | 1 | 1993-12-03 | 8000.00 | 20 | | 11 | Jone | 1 | 1993-12-03 | 8000.00 | 30 | | 15 | Brown | 1 | 1993-12-03 | 22000.00 | NULL | +-----+-------+------+------------+----------+--------+ 3 rows in set (0.00 sec)
示例：查询出10号部门和30号部门员工所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE deptld=10 OR deptld=30; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptld | +-----+-------+------+------------+----------+--------+ | 2 | Jerry | 0 | 1991-08-20 | 7000.00 | 10 | | 3 | David | 1 | 1995-10-20 | 3000.00 | 30 | | 4 | Maria | 0 | 1992-03-20 | 5000.00 | 10 | | 6 | Black | 1 | 1991-01-03 | 4000.00 | 10 | | 7 | Peter | 1 | 1990-12-03 | 10000.00 | 10 | | 8 | Franc | 1 | 1994-12-03 | 6000.00 | 30 | | 9 | Tacy | 1 | 1991-12-03 | 9000.00 | 10 | | 11 | Jone | 1 | 1993-12-03 | 8000.00 | 30 | | 12 | Lily | 0 | 1992-12-03 | 12000.00 | 10 | | 13 | Lisa | 0 | 1989-12-03 | 8000.00 | 10 | | 14 | King | 1 | 1988-12-03 | 10000.00 | 10 | +-----+-------+------+------------+----------+--------+ 11 rows in set (0.00 sec)
或者：SELECT * FROM emp WHERE deptId IN(10,30);
示例：查询出不在10号和30号部门员工所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE deptld NOT IN(10,30); +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptld | +-----+-------+------+------------+----------+--------+ | 1 | Tom | 1 | 1990-05-05 | 6000.00 | 20 | | 5 | Leo | 1 | 1993-12-03 | 8000.00 | 20 | | 10 | Lucy | 0 | 1995-12-03 | 10000.00 | 20 | +-----+-------+------+------------+----------+--------+ 3 rows in set (0.00 sec)
|
IS NULL / IS NOT NULL AND / OR BETWEEN...AND.../NOT BETWEEN...AND... IN() / NOT IN() |
注意：删除、更改、查询都可以结合条件查询。
示例：查询出工资为8000以上的女员工的姓名、性别、生日，结果集按照工资降序排序，取前3个人。
MariaDB [tedu_04]> SELECT ename,sex,birthday,salary FROM emp -> WHERE salary>=8000 AND sex=0 -> ORDER BY salary DESC -> LIMIT 0,3; +-------+------+------------+----------+ | ename | sex | birthday | salary | +-------+------+------------+----------+ | Lily | 0 | 1992-12-03 | 12000.00 | | Lucy | 0 | 1995-12-03 | 10000.00 | | Lisa | 0 | 1989-12-03 | 8000.00 | +-------+------+------------+----------+ 3 rows in set (0.01 sec)
(8)模糊条件条件
示例：查询出姓名含有字母e的员工所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE ename LIKE '%e%'; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptld | +-----+-------+------+------------+----------+--------+ | 2 | Jerry | 0 | 1991-08-20 | 7000.00 | 10 | | 5 | Leo | 1 | 1993-12-03 | 8000.00 | 20 | | 7 | Peter | 1 | 1990-12-03 | 10000.00 | 10 | | 11 | Jone | 1 | 1993-12-03 | 8000.00 | 30 | +-----+-------+------+------------+----------+--------+ 4 rows in set (0.00 sec)
示例：查询出姓名中以e结尾的员工所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE ename LIKE '%e'; +-----+-------+------+------------+---------+--------+ | eid | ename | sex | birthday | salary | deptld | +-----+-------+------+------------+---------+--------+ | 11 | Jone | 1 | 1993-12-03 | 8000.00 | 30 | +-----+-------+------+------------+---------+--------+ 1 row in set (0.00 sec)
示例：查询出姓名中倒数第2个字符为e的员工所有信息
MariaDB [tedu_04]> SELECT * FROM emp WHERE ename LIKE '%e_'; +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptld | +-----+-------+------+------------+----------+--------+ | 5 | Leo | 1 | 1993-12-03 | 8000.00 | 20 | | 7 | Peter | 1 | 1990-12-03 | 10000.00 | 10 | +-----+-------+------+------------+----------+--------+ 2 rows in set (0.00 sec)
|
SQL中提供了两个模糊查询的字符 % 可以匹配任意多个字符 >=0 _ 可以匹配任意一个字符 =1 注意：以上两个匹配不能和=使用，必须使用LIKE关键字 |
（9)分页查询
假如查询的结果集中有太多的数据，一次显示不完，可以分页显示。
需要有两个条件：当前的页码、每页的数据量
SELECT * FROM emp LIMIT start,count;
start: 是一个数字，从结果集中的哪1条开始读取，第1条称为0.
count: 是一个数字，最多读取的行数
|
每页开始的算法 start=(页码-1)*count |
假设每一页显示5条记录
第1页：SELECT * FROM emp LIMIT 0,5;
第2页：SELECT * FROM emp LIMIT 5,5;
第3页：SELECT * FROM emp LIMIT 10,5;
第4页：SELECT * FROM emp LIMIT 15,5;
假设每一页显示6条记录
第1页：SELECT * FROM emp LIMIT 0,6;
第2页：SELECT * FROM emp LIMIT 6,6;
（10）复杂查询——聚合查询/分组查询
示例：查询出所有员工的数量
MariaDB [tedu_04]> SELECT COUNT(eid) FROM emp; #15 +------------+ | COUNT(eid) | +------------+ | 15 | +------------+ 1 row in set (0.01 sec)
示例：使用员工的姓名获取员工数量
MariaDB [tedu_04]> SELECT COUNT(ename) FROM emp;#14 +--------------+ | COUNT(ename) | +--------------+ | 15 | +--------------+ 1 row in set (0.00 sec)
示例：使用员工部门编号获取员工数量
MariaDB [tedu_04]> SELECT COUNT(*) FROM emp;#15 +----------+ | COUNT(*) | +----------+ | 15 | +----------+ 1 row in set (0.00 sec)
SELECT COUNT(deptId) FROM emp;#14
SELECT COUNT(*) FROM emp;#15 推荐写法
|
函数：功能体，接收若干个数据，返回特定的结果 —饺子机 |
|
聚合函数 COUNT()/SUM()/AVG()/MAX()/MIN() |
查询出所有员工工资的总和
MariaDB [tedu_04]> SELECT SUM(salary) FROM emp; +-------------+ | SUM(salary) | +-------------+ | 128000.00 | +-------------+ 1 row in set (0.00 sec)
查询出所有男员工的平均工资
MariaDB [tedu_04]> SELECT AVG(salary) FROM emp WHERE sex=1; +-------------+ | AVG(salary) | +-------------+ | 8600.000000 | +-------------+ 1 row in set (0.00 sec)
SELECT SUM(salary)/COUNT(*) FROM emp WHERE sex=1;
SELECT AVG(salary) FROM emp WHERE sex=1;
查询出工资最高的员工的工资
MariaDB [tedu_04]> SELECT MAX(salary) FROM emp; +-------------+ | MAX(salary) | +-------------+ | 22000.00 | +-------------+
查询出工资最低的员工的工资
MariaDB [tedu_04]> SELECT MIN(salary) FROM emp; +-------------+ | MIN(salary) | +-------------+ | 3000.00 | +-------------+ 1 row in set (0.00 sec)
查询年龄最大的员工的生日
MariaDB [tedu_04]> SELECT MIN(birthday) FROM emp; +---------------+ | MIN(birthday) | +---------------+ | 1988-12-03 | +---------------+ 1 row in set (0.00 sec)
*分组查询：只能查询分组条件和聚合函数
查询出每个部门员工的最高工资
MariaDB [tedu_04]> SELECT deptId,MAX(salary) FROM emp GROUP BY deptId; +--------+-------------+ | deptId | MAX(salary) | +--------+-------------+ | NULL | 22000.00 | | 10 | 12000.00 | | 20 | 10000.00 | | 30 | 8000.00 | +--------+-------------+ 4 rows in set (0.00 sec)
查询出男女员工的平均工资，最高工资，最低工资
MariaDB [tedu_04]> SELECT sex,AVG(salary),MAX(salary),MIN(salary) FROM emp GROUP BY sex; +------+-------------+-------------+-------------+ | sex | AVG(salary) | MAX(salary) | MIN(salary) | +------+-------------+-------------+-------------+ | 0 | 8400.000000 | 12000.00 | 5000.00 | | 1 | 8600.000000 | 22000.00 | 3000.00 | +------+-------------+-------------+-------------+ 2 rows in set (0.00 sec)
|
YEAR() 获取日期中的年份 MONTH() 获取日期中的月份 |
查询1991年出生的员工
MariaDB [tedu_04]> SELECT * FROM emp WHERE YEAR(birthday)='1991'; +-----+-------+------+------------+---------+--------+ | eid | ename | sex | birthday | salary | deptid | +-----+-------+------+------------+---------+--------+ | 2 | Jerry | 0 | 1991-08-20 | 7000.00 | 10 | | 6 | Black | 1 | 1991-01-03 | 4000.00 | 10 | | 9 | Tacy | 1 | 1991-12-03 | 9000.00 | 10 | +-----+-------+------+------------+---------+--------+ 3 rows in set (0.01 sec)
查询3月份出生的员工
MariaDB [tedu_04]> SELECT * FROM emp WHERE MONTH(birthday)='3'; +-----+-------+------+------------+---------+--------+ | eid | ename | sex | birthday | salary | deptid | +-----+-------+------+------------+---------+--------+ | 4 | Maria | 0 | 1992-03-20 | 5000.00 | 10 | +-----+-------+------+------------+---------+--------+ 1 row in set (0.00 sec)
复杂查询——子查询
本质上就是一个SQL语句的查询结果作为另一个SQL语句的查询条件
示例：查询出研发部员工的所有信息
步骤1：查询研发部的部门编号
SELECT did FROM dept WHERE dname='研发部';#10
步骤2：使用部门编号查询员工——10
SELECT * FROM emp WHERE deptId=10;
综合：
SELECT * FROM emp WHERE deptId=(
SELECT did FROM dept WHERE dname='研发部'
);
MariaDB [tedu_04]> SELECT * FROM emp WHERE deptId=( -> SELECT did FROM dept WHERE dname='研发部' -> ); +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptid | +-----+-------+------+------------+----------+--------+ | 2 | Jerry | 0 | 1991-08-20 | 7000.00 | 10 | | 4 | Maria | 0 | 1992-03-20 | 5000.00 | 10 | | 6 | Black | 1 | 1991-01-03 | 4000.00 | 10 | | 7 | Peter | 1 | 1990-12-03 | 10000.00 | 10 | | 9 | Tacy | 1 | 1991-12-03 | 9000.00 | 10 | | 12 | Lily | 0 | 1992-12-03 | 12000.00 | 10 | | 13 | Lisa | 0 | 1989-12-03 | 8000.00 | 10 | | 14 | King | 1 | 1988-12-03 | 10000.00 | 10 | +-----+-------+------+------------+----------+--------+ 8 rows in set (0.01 sec)
查询比tom工资高的员工所有信息
步骤1：查询tom的工资
SELECT salary FROM emp WHERE ename='tom'; #6000
步骤2：查询工资比6000高的员工
SELECT * FROM emp WHERE salary>6000;
综合：
SELECT * FROM emp WHERE salary>(
SELECT salary FROM emp WHERE ename='tom'
);
MariaDB [tedu_04]> SELECT * FROM emp WHERE salary>( -> SELECT salary FROM emp WHERE ename='tom' -> ); +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptid | +-----+-------+------+------------+----------+--------+ | 2 | Jerry | 0 | 1991-08-20 | 7000.00 | 10 | | 5 | Leo | 1 | 1993-12-03 | 8000.00 | 20 | | 7 | Peter | 1 | 1990-12-03 | 10000.00 | 10 | | 9 | Tacy | 1 | 1991-12-03 | 9000.00 | 10 | | 10 | Lucy | 0 | 1995-12-03 | 10000.00 | 20 | | 11 | Jone | 1 | 1993-12-03 | 8000.00 | 30 | | 12 | Lily | 0 | 1992-12-03 | 12000.00 | 10 | | 13 | Lisa | 0 | 1989-12-03 | 8000.00 | 10 | | 14 | King | 1 | 1988-12-03 | 10000.00 | 10 | | 15 | Brown | 1 | 1993-12-03 | 22000.00 | NULL | +-----+-------+------+------------+----------+--------+ 10 rows in set (0.00 sec)
查询出和tom同一年出生的员工所有信息
步骤1：查询tom的生日的年份 ——1990
SELECT YEAR(birthday) FROM emp WHERE ename='tom';
步骤2：查询出1990年出生的员工
SELECT * FROM emp WHERE YEAR(birthday)=1990;
综合：
SELECT * FROM emp WHERE YEAR(birthday)=(
SELECT YEAR(birthday) FROM emp WHERE ename='tom'
);
MariaDB [tedu_04]> SELECT * FROM emp WHERE YEAR(birthday)=( -> SELECT YEAR(birthday) FROM emp WHERE ename='tom' -> ); +-----+-------+------+------------+----------+--------+ | eid | ename | sex | birthday | salary | deptid | +-----+-------+------+------------+----------+--------+ | 1 | Tom | 1 | 1990-05-05 | 6000.00 | 20 | | 7 | Peter | 1 | 1990-12-03 | 10000.00 | 10 | +-----+-------+------+------------+----------+--------+ 2 rows in set (0.00 sec)
复杂查询——多表查询
示例：查询出所有员工的姓名及其所在部门名称
SELECT ename,dname FROM emp,dept;
MariaDB [tedu_04]> SELECT ename,dname FROM emp,dept; +-------+--------+ | ename | dname | +-------+--------+ | Tom | 市场部 | | Tom | 测试部 | | Tom | 研发部 | | Tom | 运营部 | | Jerry | 市场部 | | Jerry | 测试部 | | Jerry | 研发部 | | Jerry | 运营部 | | David | 市场部 | | David | 测试部 | | David | 研发部 | | David | 运营部 | | Maria | 市场部 | | Maria | 测试部 | | Maria | 研发部 | | Maria | 运营部 | | Leo | 市场部 | | Leo | 测试部 | | Leo | 研发部 | | Leo | 运营部 | | Black | 市场部 | | Black | 测试部 | | Black | 研发部 | | Black | 运营部 | | Peter | 市场部 | | Peter | 测试部 | | Peter | 研发部 | | Peter | 运营部 | | Franc | 市场部 | | Franc | 测试部 | | Franc | 研发部 | | Franc | 运营部 | | Tacy | 市场部 | | Tacy | 测试部 | | Tacy | 研发部 | | Tacy | 运营部 | | Lucy | 市场部 | | Lucy | 测试部 | | Lucy | 研发部 | | Lucy | 运营部 | | Jone | 市场部 | | Jone | 测试部 | | Jone | 研发部 | | Jone | 运营部 | | Lily | 市场部 | | Lily | 测试部 | | Lily | 研发部 | | Lily | 运营部 | | Lisa | 市场部 | | Lisa | 测试部 | | Lisa | 研发部 | | Lisa | 运营部 | | King | 市场部 | | King | 测试部 | | King | 研发部 | | King | 运营部 | | Brown | 市场部 | | Brown | 测试部 | | Brown | 研发部 | | Brown | 运营部 | +-------+--------+ 60 rows in set (0.00 sec)
会出现笛卡尔积！（左边的一个数据会和右边的每一个数据都复合）
如何避免笛卡尔积，为多表查询添加条件。
SELECT ename,dname FROM emp,dept WHERE deptId=did;
MariaDB [tedu_04]> SELECT ename,dname FROM emp,dept WHERE deptId=did; +-------+--------+ | ename | dname | +-------+--------+ | Tom | 市场部 | | Leo | 市场部 | | Lucy | 市场部 | | Jerry | 研发部 | | Maria | 研发部 | | Black | 研发部 | | Peter | 研发部 | | Tacy | 研发部 | | Lily | 研发部 | | Lisa | 研发部 | | King | 研发部 | | David | 运营部 | | Franc | 运营部 | | Jone | 运营部 | +-------+--------+ 14 rows in set (0.01 sec)
*会出现的bug上述语法无法查询出没有部门的员工，也不能查询没有员工的部门
(1)内连接 INNER JOIN ... ON ——和之前的结果一样
MariaDB [tedu_04]> SELECT ename,dname FROM emp INNER JOIN dept ON deptId=did; +-------+--------+ | ename | dname | +-------+--------+ | Tom | 市场部 | | Leo | 市场部 | | Lucy | 市场部 | | Jerry | 研发部 | | Maria | 研发部 | | Black | 研发部 | | Peter | 研发部 | | Tacy | 研发部 | | Lily | 研发部 | | Lisa | 研发部 | | King | 研发部 | | David | 运营部 | | Franc | 运营部 | | Jone | 运营部 | +-------+--------+ 14 rows in set (0.00 sec)
(2)左外连接 LEFT OUTER JOIN...ON （查询结果是左侧表中所有的记录，即使右侧没有对应的记录。）
OUTER可以省略
MariaDB [tedu_04]> SELECT ename,dname FROM emp LEFT OUTER JOIN dept ON deptId=did; +-------+--------+ | ename | dname | +-------+--------+ | Tom | 市场部 | | Jerry | 研发部 | | David | 运营部 | | Maria | 研发部 | | Leo | 市场部 | | Black | 研发部 | | Peter | 研发部 | | Franc | 运营部 | | Tacy | 研发部 | | Lucy | 市场部 | | Jone | 运营部 | | Lily | 研发部 | | Lisa | 研发部 | | King | 研发部 | | Brown | NULL | +-------+--------+ 15 rows in set (0.00 sec)
(3)右外连接 RIGHT OUTER JOIN...ON
显示右侧表中所有的记录，OUTER可以省略
MariaDB [tedu_04]> SELECT ename,dname FROM emp RIGHT OUTER JOIN dept ON deptId=did; +-------+--------+ | ename | dname | +-------+--------+ | Tom | 市场部 | | Leo | 市场部 | | Lucy | 市场部 | | NULL | 测试部 | | Jerry | 研发部 | | Maria | 研发部 | | Black | 研发部 | | Peter | 研发部 | | Tacy | 研发部 | | Lily | 研发部 | | Lisa | 研发部 | | King | 研发部 | | David | 运营部 | | Franc | 运营部 | | Jone | 运营部 | +-------+--------+ 15 rows in set (0.00 sec)
(4)全连接 FULL JOIN
显示左侧和右侧表中所有的记录——MySQL不支持
(SELECT ename FROM emp_bj)
UNION
(SELECT ename FROM emp_sh);
MySQL中的全连接
(SELECT ename,dname FROM emp LEFT JOIN dept ON deptId=did)
UNION
(SELECT ename,dname FROM emp RIGHT JOIN dept ON deptId=did);
UNION合并相同的记录
UNION ALL 不合并相同的记录
MariaDB [tedu_04]> (SELECT ename,dname FROM emp LEFT JOIN dept ON deptId=did) -> UNION -> (SELECT ename,dname FROM emp RIGHT JOIN dept ON deptId=did); +-------+--------+ | ename | dname | +-------+--------+ | Tom | 市场部 | | Jerry | 研发部 | | David | 运营部 | | Maria | 研发部 | | Leo | 市场部 | | Black | 研发部 | | Peter | 研发部 | | Franc | 运营部 | | Tacy | 研发部 | | Lucy | 市场部 | | Jone | 运营部 | | Lily | 研发部 | | Lisa | 研发部 | | King | 研发部 | | Brown | NULL | | NULL | 测试部 | +-------+--------+ 16 rows in set (0.00 sec)
4.项目中如何保存日期、时间
2018-12-6 14:40:30
2018年12月6月 2018/12/6 12/6/2018
存储的是距离计算机元年(1970-1-1)的毫秒数
48*365*24*60*60*1000
1513728000000 使用BIGINT的列类型
