SQL 概念： Structured Query Language 结构化查询语言 作用： 1) 是一种所有关系型数据库的查询规范，不同的数据库都支持。 2) 通用的数据库操作语言，可以用在不同的数据库中。 3) 不同的数据库 SQL 语句有一些区别 SQL 语句分类 1) Data Definition Language (DDL 数据定义语言) 建表用来定义数据库对象：数据库，表，列等。 关键字：create, drop,alter 等 2) Data Manipulation Language(DML 数据操纵语言) 用来对数据库中表的数据进行增删改。 关键字：insert, delete, update 等 3) Data Query Language(DQL 数据查询语言) 用来查询数据库中表的记录(数据)。 关键字：select, where 等 4) Data Control Language(DCL 数据控制语言) 用来定义数据库的访问权限和安全级别，及创建用户。 关键字：GRANT， REVOKE 等 SQL通用语法 1) 每条语句可以单行或多行书写，以分号结尾。 可使用空格和缩进来语句的可读性 2)MySQL数据库的SQL语句不区分大小写，关键字中认为大写和小写是一样的 建议使用大写 3) 3 种注释： 单行注释：--空格+注释内容 或 #+注释内容 多行注释： /* 注释内容 */ DDL--操作数据库、表 操作MySQL数据库 创建数据库 创建数据库 CREATE DATABASE 数据库名; 判断数据库是否已经存在，不存在则创建数据库 CREATE DATABASE IF NOT EXISTS 数据库名; 创建数据库并指定字符集 CREATE DATABASE 数据库名 CHARACTER SET 字符集（utf8/gbk/gb2312…）; 具体操作： -- 直接创建数据库db1 create database db1; -- 判断是否存在，如果不存在则创建数据库db2 create database if not exists db2; -- 创建数据库db3并指定字符集为gbk mysql> create database db3 -> default character set gbk; Query OK, 1 row affected (0.00 sec) 查看数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | book | | carsales_info | | company | | create_sql | | db_manage | | db_tmlog | | mysql | | performance_schema | | school | | sys | | teaching | +--------------------+ 查看某个数据库的定义 语法： show create database 数据库名; 操作： mysql> show create database db3; +----------+-------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------+ | db3 | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+-------------------------------------------------------------+ 1 row in set (0.00 sec) 修改数据库 修改数据库的默认字符集 语法： alter database 数据库名 default character set 字符集; 操作： mysql> alter database db3 default character set utf8; Query OK, 1 row affected (0.00 sec)
mysql> show create database db3; +----------+--------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------+ | db3 | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+--------------------------------------------------------------+ 1 row in set (0.00 sec) 删除数据库 语法： drop database 数据库名; 操作： mysql> drop database db3; Query OK, 0 rows affected (0.00 sec) 使用或切换数据库 语法： use 数据库名; 操作： 使用： mysql> use db3; Database changed 查看正在使用的数据库： mysql> select database(); +------------+ | database() | +------------+ | db3 | +------------+ 1 row in set (0.00 sec) 操作MySQL数据表结构 创建表： 语法： create table 表名( 字段1 数据类型 [not null(非空),primary key(主键),unique(唯一),auto_increment(自增，默认从1开始)], 字段2 数据类型 , ... ); 操作： mysql> create table tb01( -> id int, -> name varchar(11) -> ); Query OK, 0 rows affected (0.29 sec) mysql> create table student( -> id int, -- 整数 -> name varchar(20), -- 字符串 -> birthday date -- 生日，最后没有逗号 -> ); Query OK, 0 rows affected (0.27 sec) MySQL数据类型 常用的数据类型 数据库类型： 1. int：整数类型 * age int, 2. double:小数类型 * score double(5,2) 3. date:日期，只包含年月日，yyyy-MM-dd 4. datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss 5. timestamp:时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss * 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
6. varchar：字符串 * name varchar(20):姓名最大20个字符 * zhangsan 8个字符 张三 2个字符 7.text: 大文本类型 允许长度0-65535字节 查看表 查看当前使用数据库的所有表 语法： show tables; 操作： mysql> show tables; +---------------+ | Tables_in_db3 | +---------------+ | student | | tb01 | +---------------+ 2 rows in set (0.00 sec) 查看某个数据表结构 语法： desc 表名; 操作： mysql> desc student; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | birthday | date | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) 查看某个表的建表语句 语法： show create table 表名; 操作： mysql> show create table student; +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | student | CREATE TABLE `student` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `birthday` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gbk | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 复制表结构（快速创建(复制)一个表结构相同的表） 语法： create table 新表 like 旧表; 操作： mysql> create table tb02 like student; Query OK, 0 rows affected (0.62 sec) mysql> desc tb02; -- 查看表结构与student表结构相同 +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | birthday | date | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 修改表结构 增加字段(列) --add 语法： alter table 表名 add 字段(列名) 数据类型 [not null(非空),primary key(主键),unique(唯一),auto_increment(自增，默认从1开始)] 操作： mysql> alter table student add loc varchar(20) primary key; Query OK, 0 rows affected (2.44 sec) Records: 0 Duplicates: 0 Warnings: 0 修改字段(列)类型 --modify 语法： alter table 表名 modify 字段(列名) 新的类型; 操作： mysql> alter table student modify loc int; Query OK, 0 rows affected (0.43 sec) Records: 0 Duplicates: 0 Warnings: 0 修改字段名(列名) --change 语法： alter table 表名 change 原字段名(列名) 新字段名(列名) 数据类型； 操作： mysql> alter table student change id sno int; Query OK, 0 rows affected (1.92 sec) Records: 0 Duplicates: 0 Warnings: 0 删除字段(列) 语法： alter table 表名 drop 字段名(列名); 操作： mysql> alter table student drop loc; Query OK, 0 rows affected (2.26 sec) Records: 0 Duplicates: 0 Warnings: 0 修改表名 语法： rename table 原表名 to 新表名; 或 alter table 原表名 rename to 新表名; 操作： mysql> rename table tb01 to tb001; Query OK, 0 rows affected (0.45 sec) mysql> alter table tb001 rename to tb01; Query OK, 0 rows affected (1.87 sec) 修改表的字符集 语法： alter table 表名 character set 字符集; 操作： mysql> alter table tb001 character set gbk; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 删除表 语法： drop table [if exists] 表名; 操作: mysql> drop table tb02; Query OK, 0 rows affected (0.55 sec)
DML--增删改数据库中的表数据 增加(插入)记录 语法： insert into 表名(字段名1,字段名2,...) values(值1,值2,...); 或 insert into 表名 values(值1,值2,...); 注意： 1. 列名和值要一一对应。 2. 如果表名后，不定义列名，则默认给所有列添加值 insert into 表名 values(值1,值2,...值n); 3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来 4.写法"insert into 表名 values(值1,值2,...);" 默认往所有字段插入值，如果插入部分则报错(具体看操作) 5.写法"insert into 表名(字段名1,字段名2,...) values(值1,值2,...);" 会往表名后面括号内所列举的字段插入数据，没有插入值的字段会赋值null 操作: -- 往student表中插入记录 mysql> insert into student values(1,"Poison"); --报错 ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> insert into student(sno,name) values(1,"Poison"); --插入部分字段 Query OK, 1 row affected (0.10 sec) mysql> select * from student; --查看插入结果 +------+--------+----------+ | sno | name | birthday | +------+--------+----------+ | 1 | Poison | NULL | +------+--------+----------+ 1 row in set (0.00 sec) 更新(修改)记录 语法： update 表名 set 字段名1(列名1)=值[,字段名2(列名2)=值,...] [where 条件表达式] 注意： 如果不加where条件则修改所有记录 操作： mysql> select * from student; +------+--------+------------+ | sno | name | birthday | +------+--------+------------+ | 1 | Poison | NULL | | 2 | Island | 2019-01-01 | +------+--------+------------+ 2 rows in set (0.00 sec)
mysql> update student set birthday="2019-10-29"; -- 修改所有记录 Query OK, 2 rows affected (0.07 sec) Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from student; +------+--------+------------+ | sno | name | birthday | +------+--------+------------+ | 1 | Poison | 2019-10-29 | | 2 | Island | 2019-10-29 | +------+--------+------------+ 2 rows in set (0.00 sec)
mysql> update student set name="二狗" where name="Island"; -- 修改name为Island的记录 Query OK, 1 row affected (0.94 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from student; +------+--------+------------+ | sno | name | birthday | +------+--------+------------+ | 1 | Poison | 2019-10-29 | | 2 | 二狗 | 2019-10-29 | +------+--------+------------+ 2 rows in set (0.00 sec)
mysql> update student set name="Island",birthday="2019-10-30" where name="二狗"; -- 一次修改多个字段(列) Query OK, 1 row affected (0.14 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student; +------+--------+------------+ | sno | name | birthday | +------+--------+------------+ | 1 | Poison | 2019-10-29 | | 2 | Island | 2019-10-30 | +------+--------+------------+ 2 rows in set (0.00 sec) 删除表记录 语法： delete from 表名 [where 条件表达式]; truncate table 表名; -- 删除表中所有记录，推荐使用 注意： 如果不加where条件，则会清空表中所有记录 操作： mysql> delete from student where name="Island"; --删除name为Island的例 Query OK, 1 row affected (0.04 sec)
mysql> select * from student; +------+--------+------------+ | sno | name | birthday | +------+--------+------------+ | 1 | Poison | 2019-10-29 | +------+--------+------------+ 1 row in set (0.00 sec) mysql> delete from student; --清空所有记录，不推荐使用 Query OK, 1 row affected (1.84 sec)
mysql> select * from student; Empty set (0.00 sec) mysql> insert into student(sno,name) values(1,"Poison","2019-10-29"); Query OK, 1 row affected (0.09 sec) mysql> select * from student; +------+--------+------------+ | sno | name | birthday | +------+--------+------------+ | 1 | Poison | 2019-10-29 | +------+--------+------------+ 1 row in set (0.00 sec)
mysql> truncate table student; -- 清空所有记录，推荐使用 Query OK, 0 rows affected (2.08 sec)
mysql> select * from student; Empty set (0.00 sec) truncate与delete的区别 truncate相当于删除表结构，重新创建一张一模一样的新表 而delete是在原表进行删除
DQL查询表中记录(使用自定义数据库) 基本语法： select 查询的字段名(列名) from 表名 [where 条件表达式]; 基础查询： 查询表中所有记录 语法： select * from 表名; 操作： mysql> use company; Database changed mysql> show tables; +-------------------+ | Tables_in_company | +-------------------+ | dept | | emp | | job | | sal | | salarygrade | +-------------------+ 5 rows in set (0.00 sec)
mysql> select * from emp; +------+-----------+--------+------+------------+----------+----------+---------+ | id | ename | job_id | mgr | joindate | salary | bonus | dept_id | +------+-----------+--------+------+------------+----------+----------+---------+ | 1001 | 孙悟空 | 4 | 1004 | 2000-12-17 | 8000.00 | NULL | 20 | | 1002 | 卢俊义 | 3 | 1006 | 2001-02-20 | 16000.00 | 3000.00 | 30 | | 1003 | 林冲 | 3 | 1006 | 2001-02-22 | 12500.00 | 5000.00 | 30 | | 1004 | 唐僧 | 2 | 1009 | 2001-04-02 | 29750.00 | NULL | 20 | | 1005 | 李逵 | 4 | 1006 | 2001-09-28 | 12500.00 | 14000.00 | 30 | | 1006 | 宋江 | 2 | 1009 | 2001-05-01 | 28500.00 | NULL | 30 | | 1007 | 刘备 | 2 | 1009 | 2001-09-01 | 24500.00 | NULL | 10 | | 1008 | 猪八戒 | 4 | 1004 | 2007-04-19 | 30000.00 | NULL | 20 | | 1009 | 罗贯中 | 1 | NULL | 2001-11-17 | 50000.00 | NULL | 10 | | 1010 | 吴用 | 3 | 1006 | 2001-09-08 | 15000.00 | 0.00 | 30 | | 1011 | 沙僧 | 4 | 1004 | 2007-05-23 | 11000.00 | NULL | 20 | | 1012 | 李逵 | 4 | 1006 | 2001-12-03 | 9500.00 | NULL | 30 | | 1013 | 小白龙 | 4 | 1004 | 2001-12-03 | 30000.00 | NULL | 20 | | 1014 | 关羽 | 4 | 1007 | 2002-01-23 | 13000.00 | NULL | 10 | | 1015 | 张无忌 | 4 | 1008 | 2001-03-10 | 30000.00 | NULL | NULL | +------+-----------+--------+------+------------+----------+----------+---------+ 15 rows in set (0.08 sec) 查询指定字段(列) 语法： select 字段列表 from 表名; 操作： mysql> select ename,mgr,salary,dept_id from emp; +-----------+------+----------+---------+ | ename | mgr | salary | dept_id | +-----------+------+----------+---------+ | 孙悟空 | 1004 | 8000.00 | 20 | | 卢俊义 | 1006 | 16000.00 | 30 | | 林冲 | 1006 | 12500.00 | 30 | | 唐僧 | 1009 | 29750.00 | 20 | | 李逵 | 1006 | 12500.00 | 30 | | 宋江 | 1009 | 28500.00 | 30 | | 刘备 | 1009 | 24500.00 | 10 | | 猪八戒 | 1004 | 30000.00 | 20 | | 罗贯中 | NULL | 50000.00 | 10 | | 吴用 | 1006 | 15000.00 | 30 | | 沙僧 | 1004 | 11000.00 | 20 | | 李逵 | 1006 | 9500.00 | 30 | | 小白龙 | 1004 | 30000.00 | 20 | | 关羽 | 1007 | 13000.00 | 10 | | 张无忌 | 1008 | 30000.00 | NULL | +-----------+------+----------+---------+ 15 rows in set (0.00 sec) 指定列的别名进行查询 语法： select 字段名1(列名1) as 别名, 字段名2(列名2) as 别名, ... from 表名; 或（省略as关键字） select 字段名1(列名1) 别名, 字段名2(列名2) 别名, ... from 表名; 使用别名的好处： 显示记录时使用新名字，但不修改表的结构 操作： mysql> select ename as 员工姓名,salary as 工资 from emp; -- 显示使用别名 +--------------+----------+ | 员工姓名 | 工资 | +--------------+----------+ | 孙悟空 | 8000.00 | | 卢俊义 | 16000.00 | | 林冲 | 12500.00 | | 唐僧 | 29750.00 | | 李逵 | 12500.00 | | 宋江 | 28500.00 | | 刘备 | 24500.00 | | 猪八戒 | 30000.00 | | 罗贯中 | 50000.00 | | 吴用 | 15000.00 | | 沙僧 | 11000.00 | | 李逵 | 9500.00 | | 小白龙 | 30000.00 | | 关羽 | 13000.00 | | 张无忌 | 30000.00 | +--------------+----------+ 15 rows in set (0.05 sec) mysql> select ename 员工姓名,salary 工资 from emp; -- 省略as关键字 +--------------+----------+ | 员工姓名 | 工资 | +--------------+----------+ | 孙悟空 | 8000.00 | | 卢俊义 | 16000.00 | | 林冲 | 12500.00 | | 唐僧 | 29750.00 | | 李逵 | 12500.00 | | 宋江 | 28500.00 | | 刘备 | 24500.00 | | 猪八戒 | 30000.00 | | 罗贯中 | 50000.00 | | 吴用 | 15000.00 | | 沙僧 | 11000.00 | | 李逵 | 9500.00 | | 小白龙 | 30000.00 | | 关羽 | 13000.00 | | 张无忌 | 30000.00 | +--------------+----------+ 15 rows in set (0.00 sec) mysql> desc emp; -- 表结构并未发生改变 +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | ename | varchar(50) | YES | | NULL | | | job_id | int(11) | YES | MUL | NULL | | | mgr | int(11) | YES | | NULL | | | joindate | date | YES | | NULL | | | salary | decimal(7,2) | YES | | NULL | | | bonus | decimal(7,2) | YES | | NULL | | | dept_id | int(11) | YES | MUL | NULL | | +----------+--------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) 指定表的别名进行查询(一般用于多表查询) 语法： select [表的别名.]字段名1(列名), [表的别名.]字段名2(列名), from 表名 [as] 表的别名; 操作: mysql> select tb1.ename from emp tb1; +-----------+ | ename | +-----------+ | 孙悟空 | | 卢俊义 | | 林冲 | | 唐僧 | | 李逵 | | 宋江 | | 刘备 | | 猪八戒 | | 罗贯中 | | 吴用 | | 沙僧 | | 李逵 | | 小白龙 | | 关羽 | | 张无忌 | +-----------+ 15 rows in set (0.00 sec) 清除重复值进行查询 语法： select distinct 字段名(列名) from 表名; 操作： mysql> select * from emp; +------+-----------+--------+------+------------+----------+----------+---------+ | id | ename | job_id | mgr | joindate | salary | bonus | dept_id | +------+-----------+--------+------+------------+----------+----------+---------+ | 1001 | 孙悟空 | 4 | 1004 | 2000-12-17 | 8000.00 | NULL | 20 | | 1002 | 卢俊义 | 3 | 1006 | 2001-02-20 | 16000.00 | 3000.00 | 30 | | 1003 | 林冲 | 3 | 1006 | 2001-02-22 | 12500.00 | 5000.00 | 30 | | 1004 | 唐僧 | 2 | 1009 | 2001-04-02 | 29750.00 | NULL | 20 | | 1005 | 李逵 | 4 | 1006 | 2001-09-28 | 12500.00 | 14000.00 | 30 | | 1006 | 宋江 | 2 | 1009 | 2001-05-01 | 28500.00 | NULL | 30 | | 1007 | 刘备 | 2 | 1009 | 2001-09-01 | 24500.00 | NULL | 10 | | 1008 | 猪八戒 | 4 | 1004 | 2007-04-19 | 30000.00 | NULL | 20 | | 1009 | 罗贯中 | 1 | NULL | 2001-11-17 | 50000.00 | NULL | 10 | | 1010 | 吴用 | 3 | 1006 | 2001-09-08 | 15000.00 | 0.00 | 30 | | 1011 | 沙僧 | 4 | 1004 | 2007-05-23 | 11000.00 | NULL | 20 | | 1012 | 李逵 | 4 | 1006 | 2001-12-03 | 9500.00 | NULL | 30 | | 1013 | 小白龙 | 4 | 1004 | 2001-12-03 | 30000.00 | NULL | 20 | | 1014 | 关羽 | 4 | 1007 | 2002-01-23 | 13000.00 | NULL | 10 | | 1015 | 张无忌 | 4 | 1008 | 2001-03-10 | 30000.00 | NULL | NULL | +------+-----------+--------+------+------------+----------+----------+---------+ mysql> select distinct dept_id from emp; +---------+ | dept_id | +---------+ | NULL | | 10 | | 20 | | 30 | +---------+ 4 rows in set (0.06 sec) ifnull()的用法 语法： ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null * 表达式1：哪个字段需要判断是否为null * 如果该字段为null后的替换值。 操作： mysql> select distinct ifnull(dept_id,0) 部门id from emp; -- 如果dept_id为null，则替换成为0，并为字段名取别名 +----------+ | 部门id | +----------+ | 0 | | 10 | | 20 | | 30 | +----------+ 4 rows in set (0.00 sec) 查询结果参与运算 语法： select 字段名(列名1)+固定值 from 表名; 或 select 字段名(列名1)+字段名2(列名2) 操作： mysql> select *,mark+10 加分后 from score; -- 给所有成绩加10分 +-----+-------+------+-----------+ | sno | cno | mark | 加分后 | +-----+-------+------+-----------+ | 103 | 3-105 | 92 | 102 | | 103 | 3-245 | 86 | 96 | | 103 | 6-166 | 85 | 95 | | 105 | 3-105 | 88 | 98 | | 105 | 3-245 | 75 | 85 | | 105 | 6-166 | 79 | 89 | | 109 | 3-105 | 76 | 86 | | 109 | 3-245 | 68 | 78 | | 109 | 6-166 | 81 | 91 | +-----+-------+------+-----------+ 9 rows in set (0.00 sec) mysql> select math+english from student3; -- 下方student3表 +--------------+ | math+english | +--------------+ | 144 | | 185 | | 133 | | 141 | | NULL | | 198 | | 198 | | 121 | +--------------+ 8 rows in set (0.00 sec) 条件查询 语法： select 字段名 from 表名 where 条件; 准备数据: 创建一个学生表，包含如下列： CREATE TABLE student3 ( id int, -- 编号 name varchar(20), -- 姓名 age int, -- 年龄 sex varchar(5), -- 性别 address varchar(100), -- 地址 math int, -- 数学 english int -- 英语 ); INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男',' 杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩 ',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港 ',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65); 条件运算符 语法或类型： 1.>、<、<=、>=、=、<> <>在 SQL 中表示不等于，在 mysql 中也可以使用!=，没有== 2.BETWEEN...AND 在一个范围之内，如：between 100 and 200相当于条件在 100 到 200 之间，包头又包尾 3.IN(集合) 集合表示多个值，使用逗号分隔 4.LIKE '张%' 模糊查询，表示以"张"开头的记录 5.IS [not] NULL 查询某一列为 NULL 的值，注：不能写=NULL 操作： mysql> select * from student3 where english=null; -- 不能用=null Empty set (0.00 sec) mysql> select * from student3 where english is null; -- 查english成绩为null的记录 +------+--------+------+------+---------+------+---------+ | id | name | age | sex | address | math | english | +------+--------+------+------+---------+------+---------+ | 5 | 柳青 | 20 | 男 | 湖南 | 86 | NULL | +------+--------+------+------+---------+------+---------+ 1 row in set (0.00 sec) mysql> select * from student3 where math<66; --查询数学成绩小于66的学生信息 +------+--------------+------+------+---------+------+---------+ | id | name | age | sex | address | math | english | +------+--------------+------+------+---------+------+---------+ | 3 | 马景涛 | 55 | 男 | 香港 | 56 | 77 | | 8 | 德玛西亚 | 18 | 男 | 南京 | 56 | 65 | +------+--------------+------+------+---------+------+---------+ mysql> select * from student3 where math<=60; -- 查询数学成绩小于等于60的学生信息 +------+--------------+------+------+---------+------+---------+ | id | name | age | sex | address | math | english | +------+--------------+------+------+---------+------+---------+ | 3 | 马景涛 | 55 | 男 | 香港 | 56 | 77 | | 8 | 德玛西亚 | 18 | 男 | 南京 | 56 | 65 | +------+--------------+------+------+---------+------+---------+ 2 rows in set (0.00 sec) mysql> select * from student3 where age=18; -- 查询age等于18的学生信息 +------+--------------+------+------+---------+------+---------+ | id | name | age | sex | address | math | english | +------+--------------+------+------+---------+------+---------+ | 8 | 德玛西亚 | 18 | 男 | 南京 | 56 | 65 | +------+--------------+------+------+---------+------+---------+ 1 row in set (0.00 sec) mysql> select * from student where sex!='男'; --写法一：查询sex不为男的学生信息 +---------+-----------+-----+------------+-------+ | sno | sname | sex | birthday | class | +---------+-----------+-----+------------+-------+ | 103 | 王丽 | 女 | 1976-01-23 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 | 95031 | | 2019002 | 何白露 | 女 | 2000-12-04 | 95031 | | 2019004 | 梁一苇 | 女 | 1999-09-03 | 95033 | | 2019005 | 凌浩风 | 女 | 2001-10-06 | 95033 | | 2019008 | 凌莹莹 | 女 | 2001-01-01 | 95033 | | 2019009 | 王全美 | 女 | 2000-02-13 | 95031 | +---------+-----------+-----+------------+-------+ 7 rows in set (0.00 sec) mysql> select * from student where sex<>'男'; -- 写法二： 查询sex不为男的学生信息 +---------+-----------+-----+------------+-------+ | sno | sname | sex | birthday | class | +---------+-----------+-----+------------+-------+ | 103 | 王丽 | 女 | 1976-01-23 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 | 95031 | | 2019002 | 何白露 | 女 | 2000-12-04 | 95031 | | 2019004 | 梁一苇 | 女 | 1999-09-03 | 95033 | | 2019005 | 凌浩风 | 女 | 2001-10-06 | 95033 | | 2019008 | 凌莹莹 | 女 | 2001-01-01 | 95033 | | 2019009 | 王全美 | 女 | 2000-02-13 | 95031 | +---------+-----------+-----+------------+-------+ 7 rows in set (0.00 sec) mysql> select * from student3 where age between 30 and 50; -- 查询年龄在30到50之间的学生信息 +------+-----------+------+------+---------+------+---------+ | id | name | age | sex | address | math | english | +------+-----------+------+------+---------+------+---------+ | 2 | 马化腾 | 45 | 女 | 深圳 | 98 | 87 | +------+-----------+------+------+---------+------+---------+ 1 row in set (0.00 sec) mysql> select * from student3 where age in(22,18,45); -- 查询年龄在in集合内的学生信息 +------+--------------+------+------+---------+------+---------+ | id | name | age | sex | address | math | english | +------+--------------+------+------+---------+------+---------+ | 2 | 马化腾 | 45 | 女 | 深圳 | 98 | 87 | | 7 | 马德 | 22 | 女 | 香港 | 99 | 99 | | 8 | 德玛西亚 | 18 | 男 | 南京 | 56 | 65 | +------+--------------+------+------+---------+------+---------+ 3 rows in set (0.00 sec) mysql> select * from emp where bonus is not null; -- 查询bonus不为null的员工信息 +------+-----------+--------+------+------------+----------+----------+---------+ | id | ename | job_id | mgr | joindate | salary | bonus | dept_id | +------+-----------+--------+------+------------+----------+----------+---------+ | 1002 | 卢俊义 | 3 | 1006 | 2001-02-20 | 16000.00 | 3000.00 | 30 | | 1003 | 林冲 | 3 | 1006 | 2001-02-22 | 12500.00 | 5000.00 | 30 | | 1005 | 李逵 | 4 | 1006 | 2001-09-28 | 12500.00 | 14000.00 | 30 | | 1010 | 吴用 | 3 | 1006 | 2001-09-08 | 15000.00 | 0.00 | 30 | +------+-----------+--------+------+------------+----------+----------+---------+ 4 rows in set (0.00 sec) mysql> select * from student where sname like '王%'; -- 模糊查询，查询名字以"王"口头的或者说姓王的学生信息 +---------+-----------+-----+------------+-------+ | sno | sname | sex | birthday | class | +---------+-----------+-----+------------+-------+ | 103 | 王丽 | 女 | 1976-01-23 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 | 95033 | | 2019009 | 王全美 | 女 | 2000-02-13 | 95031 | +---------+-----------+-----+------------+-------+ 4 rows in set (0.00 sec) mysql> select * from student where sname like '___'; -- 查询名字是三位的学生信息 +---------+-----------+-----+------------+-------+ | sno | sname | sex | birthday | class | +---------+-----------+-----+------------+-------+ | 107 | 王尼玛 | 男 | 1976-02-20 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 | 95031 | | 2019001 | 许东山 | 男 | 1999-11-05 | 95033 | | 2019002 | 何白露 | 女 | 2000-12-04 | 95031 | | 2019003 | 敬横江 | 男 | 2000-03-01 | 95031 | | 2019004 | 梁一苇 | 女 | 1999-09-03 | 95033 | | 2019005 | 凌浩风 | 女 | 2001-10-06 | 95033 | | 2019006 | 赵临江 | 男 | 2000-02-13 | 95031 | | 2019008 | 凌莹莹 | 女 | 2001-01-01 | 95033 | | 2019009 | 王全美 | 女 | 2000-02-13 | 95031 | +---------+-----------+-----+------------+-------+ 11 rows in set (0.00 sec) mysql> select * from student3 where name like '_化%'; -- 查询名字第二位含有“化”的学生信息 +------+-----------+------+------+---------+------+---------+ | id | name | age | sex | address | math | english | +------+-----------+------+------+---------+------+---------+ | 2 | 马化腾 | 45 | 女 | 深圳 | 98 | 87 | +------+-----------+------+------+---------+------+---------+ 1 row in set (0.00 sec) mysql> select * from student3 where name like '%德%'; -- 查询名字含有“德”字的学生信息 +------+--------------+------+------+---------+------+---------+ | id | name | age | sex | address | math | english | +------+--------------+------+------+---------+------+---------+ | 6 | 刘德华 | 57 | 男 | 香港 | 99 | 99 | | 7 | 马德 | 22 | 女 | 香港 | 99 | 99 | | 8 | 德玛西亚 | 18 | 男 | 南京 | 56 | 65 | +------+--------------+------+------+---------+------+---------+ 3 rows in set (0.00 sec) 逻辑运算符 语法或类型： and 或 && 与，SQL 中建议使用前者，后者并不通用。 or 或 || 或 not 或 ! 非 操作： mysql> select * from student3 where age>40 and sex='男'; -- 查询年龄大于40 且性别为男的学生信息 +------+-----------+------+------+---------+------+---------+ | id | name | age | sex | address | math | english | +------+-----------+------+------+---------+------+---------+ | 1 | 马云 | 55 | 男 | 杭州 | 66 | 78 | | 3 | 马景涛 | 55 | 男 | 香港 | 56 | 77 | | 6 | 刘德华 | 57 | 男 | 香港 | 99 | 99 | +------+-----------+------+------+---------+------+---------+ 3 rows in set (0.00 sec) mysql> select * from student3 where age>40 or sex='男'; --查询年龄大于40或性别是男的学生信息 +------+--------------+------+------+---------+------+---------+ | id | name | age | sex | address | math | english | +------+--------------+------+------+---------+------+---------+ | 1 | 马云 | 55 | 男 | 杭州 | 66 | 78 | | 2 | 马化腾 | 45 | 女 | 深圳 | 98 | 87 | | 3 | 马景涛 | 55 | 男 | 香港 | 56 | 77 | | 5 | 柳青 | 20 | 男 | 湖南 | 86 | NULL | | 6 | 刘德华 | 57 | 男 | 香港 | 99 | 99 | | 8 | 德玛西亚 | 18 | 男 | 南京 | 56 | 65 | +------+--------------+------+------+---------+------+---------+ 6 rows in set (0.00 sec) mysql> select * from student3 where id=1 or id=5 or id=6; -- 查询id为1或5或6的学生信息 +------+-----------+------+------+---------+------+---------+ | id | name | age | sex | address | math | english | +------+-----------+------+------+---------+------+---------+ | 1 | 马云 | 55 | 男 | 杭州 | 66 | 78 | | 5 | 柳青 | 20 | 男 | 湖南 | 86 | NULL | | 6 | 刘德华 | 57 | 男 | 香港 | 99 | 99 | +------+-----------+------+------+---------+------+---------+ 3 rows in set (0.00 sec) 排序查询(order by... desc|asc) 语法： select 字段名(列名)列表 from 表名 where 条件表达式 order by 字段名(列名) [desc(降序)|asc(默认升序)]; 注意： 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。 操作： mysql> select * from student where class='95033' order by sno desc; -- 查询班级为95033的学生信息，按学号降序排列 +---------+-----------+-----+------------+-------+ | sno | sname | sex | birthday | class | +---------+-----------+-----+------------+-------+ | 2019008 | 凌莹莹 | 女 | 2001-01-01 | 95033 | | 2019005 | 凌浩风 | 女 | 2001-10-06 | 95033 | | 2019004 | 梁一苇 | 女 | 1999-09-03 | 95033 | | 2019001 | 许东山 | 男 | 1999-11-05 | 95033 | | 107 | 王尼玛 | 男 | 1976-02-20 | 95033 | | 104 | 李军 | 男 | 1976-02-20 | 95033 | | 103 | 王丽 | 女 | 1976-01-23 | 95033 | +---------+-----------+-----+------------+-------+ 7 rows in set (0.00 sec) mysql> select * from student where class='95033' order by sno asc; -- 查询班级为95033的学生信息，按学号升序排列 +---------+-----------+-----+------------+-------+ | sno | sname | sex | birthday | class | +---------+-----------+-----+------------+-------+ | 103 | 王丽 | 女 | 1976-01-23 | 95033 | | 104 | 李军 | 男 | 1976-02-20 | 95033 | | 107 | 王尼玛 | 男 | 1976-02-20 | 95033 | | 2019001 | 许东山 | 男 | 1999-11-05 | 95033 | | 2019004 | 梁一苇 | 女 | 1999-09-03 | 95033 | | 2019005 | 凌浩风 | 女 | 2001-10-06 | 95033 | | 2019008 | 凌莹莹 | 女 | 2001-01-01 | 95033 | +---------+-----------+-----+------------+-------+ 7 rows in set (0.00 sec) mysql> select * from student where class='95033' order by sno; -- 查询班级为95033的学生信息，默认按学号升序排列 +---------+-----------+-----+------------+-------+ | sno | sname | sex | birthday | class | +---------+-----------+-----+------------+-------+ | 103 | 王丽 | 女 | 1976-01-23 | 95033 | | 104 | 李军 | 男 | 1976-02-20 | 95033 | | 107 | 王尼玛 | 男 | 1976-02-20 | 95033 | | 2019001 | 许东山 | 男 | 1999-11-05 | 95033 | | 2019004 | 梁一苇 | 女 | 1999-09-03 | 95033 | | 2019005 | 凌浩风 | 女 | 2001-10-06 | 95033 | | 2019008 | 凌莹莹 | 女 | 2001-01-01 | 95033 | +---------+-----------+-----+------------+-------+ 7 rows in set (0.00 sec) mysql> select * from student where class='95033' order by birthday desc,sno asc; -- 查询班级为95033的学生信息，默认按学号升序排列 +---------+-----------+-----+------------+-------+ | sno | sname | sex | birthday | class | +---------+-----------+-----+------------+-------+ | 2019005 | 凌浩风 | 女 | 2001-10-06 | 95033 | | 2019008 | 凌莹莹 | 女 | 2001-01-01 | 95033 | | 2019001 | 许东山 | 男 | 1999-11-05 | 95033 | | 2019004 | 梁一苇 | 女 | 1999-09-03 | 95033 | | 104 | 李军 | 男 | 1976-02-20 | 95033 | | 107 | 王尼玛 | 男 | 1976-02-20 | 95033 | | 103 | 王丽 | 女 | 1976-01-23 | 95033 | +---------+-----------+-----+------------+-------+ 7 rows in set (0.00 sec) 聚合函数查询 语法： 1. count(列名)：计算这一列的记录个数 1. 一般选择非空的列：主键 2. count(*) 2. max(列名)：计算这一列的最大值 3. min(列名)：计算这一列的最小值 4. sum(列名)：计算这一列的总和 5. avg(列名)：计算这一列的平均值 注意： 聚合函数的计算，排除null值。 解决方案： 1. 选择不包含非空的列进行计算 2. IFNULL函数 用法： IFNULL(列名，默认值) 如果列名不为空，返回这列的值。如果为 NULL，则返回默认值。 操作： mysql> select count(id) 学生人数 from student3; -- 计算学生总人数 +--------------+ | 学生人数 | +--------------+ | 8 | +--------------+ 1 row in set (0.00 sec) mysql> select count(*) 学生人数 from student3; -- 计算学生总人数 +--------------+ | 学生人数 | +--------------+ | 8 | +--------------+ 1 row in set (0.00 sec)
mysql> select avg(math) 数学平均成绩 from student3; -- 计算数学平均成绩 +--------------------+ | 数学平均成绩 | +--------------------+ | 79.5000 | +--------------------+ 1 row in set (0.00 sec) mysql> select max(math) 数学最高分 from student3; --计算数学成绩最高分 +-----------------+ | 数学最高分 | +-----------------+ | 99 | +-----------------+ 1 row in set (0.00 sec) mysql> select min(math) 数学成绩最低分 from student3; --计算数学成绩最低分 +-----------------------+ | 数学成绩最低分 | +-----------------------+ | 56 | +-----------------------+ 1 row in set (0.00 sec) mysql> select * from score; +-----+-------+------+ | sno | cno | mark | +-----+-------+------+ | 103 | 3-105 | 92 | | 103 | 3-245 | 86 | | 103 | 6-166 | 85 | | 105 | 3-105 | 88 | | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | | 109 | 6-166 | 81 | +-----+-------+------+ 9 rows in set (0.00 sec)
mysql> select sum(mark) from score where sno=109; -- 查询成绩表中学号为109的同学的所有课程总分 +-----------+ | sum(mark) | +-----------+ | 225 | +-----------+ 1 row in set (0.00 sec) -- 当需要把含有值为null的列统计个数时，使用ifnull()解决 mysql> select * from emp; +------+-----------+--------+------+------------+----------+----------+---------+ | id | ename | job_id | mgr | joindate | salary | bonus | dept_id | +------+-----------+--------+------+------------+----------+----------+---------+ | 1001 | 孙悟空 | 4 | 1004 | 2000-12-17 | 8000.00 | NULL | 20 | | 1002 | 卢俊义 | 3 | 1006 | 2001-02-20 | 16000.00 | 3000.00 | 30 | | 1003 | 林冲 | 3 | 1006 | 2001-02-22 | 12500.00 | 5000.00 | 30 | | 1004 | 唐僧 | 2 | 1009 | 2001-04-02 | 29750.00 | NULL | 20 | | 1005 | 李逵 | 4 | 1006 | 2001-09-28 | 12500.00 | 14000.00 | 30 | | 1006 | 宋江 | 2 | 1009 | 2001-05-01 | 28500.00 | NULL | 30 | | 1007 | 刘备 | 2 | 1009 | 2001-09-01 | 24500.00 | NULL | 10 | | 1008 | 猪八戒 | 4 | 1004 | 2007-04-19 | 30000.00 | NULL | 20 | | 1009 | 罗贯中 | 1 | NULL | 2001-11-17 | 50000.00 | NULL | 10 | | 1010 | 吴用 | 3 | 1006 | 2001-09-08 | 15000.00 | 0.00 | 30 | | 1011 | 沙僧 | 4 | 1004 | 2007-05-23 | 11000.00 | NULL | NULL | | 1012 | 李逵 | 4 | 1006 | 2001-12-03 | 9500.00 | NULL | 30 | | 1013 | 小白龙 | 4 | 1004 | 2001-12-03 | 30000.00 | NULL | 20 | | 1014 | 关羽 | 4 | 1007 | 2002-01-23 | 13000.00 | NULL | 10 | | 1015 | 张无忌 | 4 | 1008 | 2001-03-10 | 30000.00 | NULL | NULL | +------+-----------+--------+------+------------+----------+----------+---------+ 15 rows in set (0.00 sec)
mysql> select count(ifnull(dept_id,0)) 员工人数 from emp; +--------------+ | 员工人数 | +--------------+ | 15 | +--------------+ 1 row in set (0.00 sec)
mysql> select count(dept_id) 员工人数 from emp; -- 不使用ifnull时，只为null的将不被统计 +--------------+ | 员工人数 | +--------------+ | 13 | +--------------+ 1 row in set (0.00 sec) 分组查询 语法： select 字段1,字段2,... from 表名 group by 分组字段 [having 条件] 注意： 分组之后查询的字段：分组字段、聚合函数 where 和 having 的区别？ 1. where 在分组之前进行限定，如果不满足条件，则不参与分组。 having在分组之后进行限定，如果不满足结果，则不会被查询出来 2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。 操作： mysql> select sex,count(id) 总人数 from student3 group by sex; -- 分组查询男女生人数 +------+-----------+ | sex | 总人数 | +------+-----------+ | 女 | 3 | | 男 | 5 | +------+-----------+ 2 rows in set (0.00 sec) mysql> select sex 性别,avg(math) 平均分,count(id) 人数 from student3 group by sex; -- 按性别分组查询男女生各自数学成绩的平均分，男女人数 +--------+-----------+--------+ | 性别 | 平均分 | 人数 | +--------+-----------+--------+ | 女 | 91.0000 | 3 | | 男 | 72.6000 | 5 | +--------+-----------+--------+ 2 rows in set (0.00 sec) -- 按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组 mysql> select sex 性别,avg(math) 数学平均分,count(id) 人数 from student3 where math>=70 group by sex; +--------+-----------------+--------+ | 性别 | 数学平均分 | 人数 | +--------+-----------------+--------+ | 女 | 91.0000 | 3 | | 男 | 92.5000 | 2 | +--------+-----------------+--------+ 2 rows in set (0.00 sec) -- 按照性别分组。分别查询男、女同学的平均分,人数 -- 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人 mysql> select sex 性别,avg(math) 数学平均分,count(id) 人数 from student3 where math>70 group by sex having count(id)>2; +--------+-----------------+--------+ | 性别 | 数学平均分 | 人数 | +--------+-----------------+--------+ | 女 | 91.0000 | 3 | +--------+-----------------+--------+ 1 row in set (0.00 sec) mysql> select sex 性别,avg(math) 数学平均分,count(id) 人数 from student3 where math>70 group by sex having 人数>2; +--------+-----------------+--------+ | 性别 | 数学平均分 | 人数 | +--------+-----------------+--------+ | 女 | 91.0000 | 3 | +--------+-----------------+--------+ 1 row in set (0.00 sec) 分页查询(limit) 语法： select 字段列表 from 表名 [where条件] [GROUP BY 字段] [HAVING (条件判断)] [ORDER BY desc(降序)|asc(升序)] [LIMIT offset,length]; 关键字： limit offset,length; offset：开始索引或起始行数，从0开始计数，如果省略，默认就是 0 length： 返回的行数或每页查询的条数 公式： 开始的索引(offset) = （当前的页码 - 1） * 每页显示的记录条数(length) 准备数据： INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (9,'唐僧',25,'男','长安',87,78), (10,'孙悟空',18,'男','花果山',100,66), (11,'猪八戒',22,'男','高老庄',58,78), (12,'沙僧',50,'男','流沙河',77,88), (13,'白骨精',22,'女','白虎岭',66,66), (14,'蜘蛛精',23,'女','盘丝洞',88,88); 操作： -- 分页查询，每页显示4条记录 mysql> select * from student3 limit 0,4; +------+-----------+------+------+---------+------+---------+ | id | name | age | sex | address | math | english | +------+-----------+------+------+---------+------+---------+ | 1 | 马云 | 55 | 男 | 杭州 | 66 | 78 | | 2 | 马化腾 | 45 | 女 | 深圳 | 98 | 87 | | 3 | 马景涛 | 55 | 男 | 香港 | 56 | 77 | | 4 | 柳岩 | 20 | 女 | 湖南 | 76 | 65 | +------+-----------+------+------+---------+------+---------+ 4 rows in set (0.00 sec) mysql> select * from student3 limit 4,4; +------+--------------+------+------+---------+------+---------+ | id | name | age | sex | address | math | english | +------+--------------+------+------+---------+------+---------+ | 5 | 柳青 | 20 | 男 | 湖南 | 86 | NULL | | 6 | 刘德华 | 57 | 男 | 香港 | 99 | 99 | | 7 | 马德 | 22 | 女 | 香港 | 99 | 99 | | 8 | 德玛西亚 | 18 | 男 | 南京 | 56 | 65 | +------+--------------+------+------+---------+------+---------+ 4 rows in set (0.00 sec) mysql> select * from student3 limit 8,4; +------+-----------+------+------+-----------+------+---------+ | id | name | age | sex | address | math | english | +------+-----------+------+------+-----------+------+---------+ | 9 | 唐僧 | 25 | 男 | 长安 | 87 | 78 | | 10 | 孙悟空 | 18 | 男 | 花果山 | 100 | 66 | | 11 | 猪八戒 | 22 | 男 | 高老庄 | 58 | 78 | | 12 | 沙僧 | 50 | 男 | 流沙河 | 77 | 88 | +------+-----------+------+------+-----------+------+---------+ 4 rows in set (0.00 sec) 多表查询(重要) * 笛卡尔积： * 有两个集合A,B .取这两个集合的所有组成情况。 * 要完成多表查询，需要消除无用的数据 * 多表查询的分类： 1. 内连接查询： 1. 隐式内连接：使用where条件消除无用数据 * 例子： -- 查询所有员工信息和对应的部门信息
SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`; -- 查询员工表的名称，性别。部门表的名称 SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`; SELECT t1.name, -- 员工表的姓名 t1.gender,-- 员工表的性别 t2.name -- 部门表的名称 FROM emp t1, dept t2 WHERE t1.`dept_id` = t2.`id`;
2. 显式内连接： * 语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件 * 例如： * SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`; * SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;
3. 内连接查询： 1. 从哪些表中查询数据 2. 条件是什么 3. 查询哪些字段 2. 外链接查询： 1. 左外连接： * 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件； * 查询的是左表所有数据以及其交集部分。 * 例子： -- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称 SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`; 2. 右外连接： * 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件； * 查询的是右表所有数据以及其交集部分。 * 例子： SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`; 3. 子查询： * 概念：查询中嵌套查询，称嵌套查询为子查询。 -- 查询工资最高的员工信息 -- 1 查询最高的工资是多少 9000 SELECT MAX(salary) FROM emp; -- 2 查询员工信息，并且工资等于9000的 SELECT * FROM emp WHERE emp.`salary` = 9000; -- 一条sql就完成这个操作。子查询 SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
* 子查询不同情况 1. 子查询的结果是单行单列的： * 子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= = * -- 查询员工工资小于平均工资的人 SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp); 2. 子查询的结果是多行单列的： * 子查询可以作为条件，使用运算符in来判断 -- 查询'财务部'和'市场部'所有的员工信息 SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部'; SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2; -- 子查询 SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
3. 子查询的结果是多行多列的： * 子查询可以作为一张虚拟表参与查询 -- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息 -- 子查询 SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id; -- 普通内连接 SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > '2011-11-11' *子查询小结 1.子查询结果只要是单列，则在 WHERE 后面作为条件 2.子查询结果只要是多列，则在 FROM 后面作为表进行二次查询 3.多表查询练习 数据准备 -- 部门表 CREATE TABLE dept ( id INT PRIMARY KEY PRIMARY KEY, -- 部门id dname VARCHAR(50), -- 部门名称 loc VARCHAR(50) -- 部门所在地 );
-- 添加4个部门 INSERT INTO dept(id,dname,loc) VALUES (10,'教研部','北京'), (20,'学工部','上海'), (30,'销售部','广州'), (40,'财务部','深圳');
-- 职务表，职务名称，职务描述 CREATE TABLE job ( id INT PRIMARY KEY, jname VARCHAR(20), description VARCHAR(50) );
-- 添加4个职务 INSERT INTO job (id, jname, description) VALUES (1, '董事长', '管理整个公司，接单'), (2, '经理', '管理部门员工'), (3, '销售员', '向客人推销产品'), (4, '文员', '使用办公软件');
-- 员工表 CREATE TABLE emp ( id INT PRIMARY KEY, -- 员工id ename VARCHAR(50), -- 员工姓名 job_id INT, -- 职务id mgr INT , -- 上级领导 joindate DATE, -- 入职日期 salary DECIMAL(7,2), -- 工资 bonus DECIMAL(7,2), -- 奖金 dept_id INT, -- 所在部门编号 CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id), CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id) );
-- 添加员工 INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20), (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30), (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30), (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20), (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30), (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30), (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10), (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20), (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10), (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30), (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20), (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30), (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20), (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 工资等级表 CREATE TABLE salarygrade ( grade INT PRIMARY KEY, -- 级别 losalary INT, -- 最低工资 hisalary INT -- 最高工资 );
-- 添加5个工资等级 INSERT INTO salarygrade(grade,losalary,hisalary) VALUES (1,7000,12000), (2,12010,14000), (3,14010,20000), (4,20010,30000), (5,30010,99990);
-- 需求：
-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述 分析： 1.emp-员工编号，员工姓名，工资，job-职务名称，职务描述 答案： 方式一：隐式内连接实现 select t1.id, t1.ename, t1.salary, t2.jname, t2.description from emp t1, job t2 where t1.job_id=t2.id; 方式二：显式内连接实现 select t1.id, t1.ename, t1.salary, t2.jname, t2.description from emp t1 inner join job t2 on t1.job_id=t2.id;
-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置 分析： 1. emp-员工编号，员工姓名，工资 job-职务名称，职务描述 dept-部门名称，部门位置 2. 条件： emp.job_id = job.id and emp.dept_id = dept.id 答案： select t1.id, t1.ename, t1.salary, t2.jname, t2.description, t3.dname, t3.loc from emp t1, job t2, dept t3 where t1.job_id=t2.id and t1.dept_id=t3.id; -- 3.查询员工姓名，工资，工资等级 分析: 1.emp-员工姓名，工资 salarygrade-工资等级 2.条件：emp.salary between salarygrade.losalary and salarygrade.hisalary 答案： select t1.ename, t1.salary, t2.grade from emp t1, salarygrade t2 where t1.salary between t2.losalary and t2.hisalary;
-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级 分析： 1.emp-员工姓名，工资 job-职务名称，职务描述 dept-部门名称，部门位置 salarygrade-工资等级 2.条件：emp.job_id=job.id and emp.dept_id=dept.id and emp.salary between salarygrade.losalary and salarygrade.hisalary; 答案： select t1.ename, t1.salary, t2.jname, t2.description, t3.dname, t3.loc, t4.grade from emp t1, job t2, dept t3, salarygrade t4 where t1.job_id=t2.id and t1.dept_id=t3.id and t1.salary between t4.losalary and t4.hisalary; -- 5.查询出部门编号、部门名称、部门位置、部门人数 分析： 1.分组(group by)查询各部门人数count(),emp-部门人数 dept-部门编号、部门名称、部门位置 2.条件：emp.dept_id=dept.id; 答案： select t2.id, t2.dname, t2.loc, count(t1.dept_id) from emp t1, dept t2 where t1.dept_id=t2.id group by t2.id; -- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询 分析： 1.emp-员工姓名，直接上级，没有领导的员工也需要查询(外连接) 2.条件：t1.mgt=t2.id; 答案：左外连 select t1.id, t1.ename, t2.id, t2.ename from emp t1 left join emp t2 on t1.mgr=t2.id; 4.面试题： 内连接与外连接的区别 内连接：指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。 外连接：连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。 左外连接：左边表数据行全部保留，右边表保留符合连接条件的行。 右外连接：右边表数据行全部保留，左边表保留符合连接条件的行。 全外连接：左外连接 union 右外连接。
