-- 1.查询表中的单个字段
SELECT name FROM beauty;
-- 这里的name是关键字，但是也被我们用来做字段，虽然可以执行，但是我们可以通过加这个符号，避免歧义。
-- 2.查询表中的多个字段
SELECT name , sex FROM beauty;
-- 3.查询表中的所有字段
SELECT * FROM beauty;
-- 查询工资>12000的员工名和工资
SELECT last_name 员工名, salary 工资 FROM employees
WHERE salary > 12000;
-- 查询部门编号不等于90号的员工名和部门编号
SELECT last_name 员工名, department_id 部门编号 FROM employees
WHERE department_id <> 90;
SELECT last_name 员工名, department_id 部门编号 FROM employees
WHERE department_id != 90;
-- 查询员工编号<=110 的员工名和员工编号
SELECT last_name 员工名, employee_id 员工编号 FROM employees
WHERE employee_id <= 110;
2.3 逻辑表达式筛选
&& and ：多个条件同时满足。
|| or：多个条件其中一个满足。
! not：不满足。
一般我们都是用and or not比较多。
演示一下：
-- 查询工资z在10000到20000之间的员工名、工资
SELECT last_name 员工名, salary 工资 FROM employees
WHERE salary >= 10000 AND salary <= 20000;
SELECT last_name 员工名, salary 工资 FROM employees
WHERE salary >= 10000 && salary <= 20000;
-- 查询工资低于10000，或者工资高于20000的员工名、工资
SELECT last_name 员工名, salary 工资 FROM employees
WHERE salary <= 10000 OR salary >= 20000;
SELECT last_name 员工名, salary 工资 FROM employees
WHERE salary <= 10000 || salary >= 20000;
-- 查询员工编号不是在100和150之间的员工名、员工编号
SELECT last_name 员工名, employee_id 员工编号 FROM employees
WHERE NOT(employee_id >= 100 AND employee_id <= 150);
SELECT last_name 员工名, employee_id 员工编号 FROM employees
WHERE !(employee_id >= 100 && employee_id <= 150);
2.4 模糊查询 2.4.1 like关键字
LIKE：通常与两个通配符一起使用：
% ：任意多个字符,包含0 个字符
_ ：任意单个字符
语法：
SELECT 查询列表 FROM 表名 WHERE 字段名 LIKE '通配符字符串';
具体演示下：
-- 查询员工名中以字符a开头的员工名SELECT last_name 员工名 FROM employeesWHERE last_name LIKE 'a%';-- 查询员工名中以字符a结尾的员工名SELECT last_name 员工名 FROM employeesWHERE last_name LIKE '%a';-- 查询员工名中包含字符a的员工名SELECT last_name 员工名 FROM employeesWHERE last_name LIKE '%a%';-- 查询员工名中第三个字符为s，第五个字符为i的员工名和工资SELECT last_name 员工名, salary 工资 FROM employeesWHERE last_name LIKE '__s_i%';-- 查询员工名中第二个字符为_的员工名/这里有个难点就是_这个不是通配符吗，那可以直接使用吗，当然是不行的所以我们也就需要到转义符，如Java一样可以使用\来转义。MySQL 有个特有的方式 即 定义一个字符 后面加上关键字ESCAPE 然后定义 '字符'。/SELECT last_name 员工名, salary 工资 FROM employeesWHERE last_name LIKE '_$_%' ESCAPE '注意：如果我们需要查找的字符包含了我们的通配符类似等，那我们可以使用转义字符：<li>如Java一样可以使用\来转义。
<ul><li>MySQL有个特有的方式 即 定义一个字符 后面加上关键字ESCAPE 然后定义字符。如上面所示。
</li></ul><div align="center"><img src="https://www.codeae.com/forum.php?mod=image&aid=36801&size=300x300&key=224acc25ae240525&nocache=yes&type=fixnone" border="0" aid="attachimg_36801" width="300" alt=""></div>
</li>
2.4.2 between and 关键词
between and：包含临界值并且，两个临界值不要调换顺序。
直接演示下：
-- 查询员工编号在100到120之间的员工信息SELECT * FROM employeesWHERE employee_id BETWEEN 100 AND 120;-- 错误，不会报错，但是没有结果。SELECT FROM employeesWHERE employee_id BETWEEN 120 AND 100;
is nul：
因为=或<>不能用于判断NULL值，而我们可以使用is nul或is not nul来判断NULL值。
直接演示下：
-- 查询没有奖金的员工名和奖金率SELECT last_name 员工名, commission_pct 奖金率 FROM employeesWHERE commission_pct IS NULL;-- 查询有奖金的员工名和奖金率SELECT last_name 员工名, commission_pct 奖金率 FROM employeesWHERE commission_pct IS NOT NULL;
进阶3：排序查询
我们发现我们查询出来的可能是无序，那我们可以把查出来的表按一定规则排序。排序关键词： ORDER BY
语法：
SELECT 查询列表 FROM 表名【where 筛选条件】ORDER BY 字段名 ASC|DESC;
特点：
ASC代表的是升序，可以省略，而DESC代表的是降序。
ORDER BY子句可以支持 单个字段（单列）、别名、表达式、函数、多个字段（多列）。
ORDER BY子句在查询语句的最后面，除了limit子句。这个后面再讲。
实战演示下：
单个字段中的排序即单列排序：
-- 按单个字段排序(单列排序)-- 查询员工名和工资，并按工资降序SELECT last_name, salary FROM employeesORDER BY salary DESC;-- 添加筛选条件再排序-- 查询部门编号>=90的员工名、员工编号，并按员工编号降序SELECT last_name, employee_idFROM employeesWHERE employee_id >= 90ORDER BY employee_id;-- 按表达式排序-- 查询员工名、工资、年薪 按年薪降序-- 年薪= salary12*(1+IFNULL(commission_pct,0))SELECT last_name, salary, salary12(1+IFNULL(commission_pct,0)) 年薪FROM employeesORDER BY salary*12(1+IFNULL(commission_pct,0)) DESC;-- 按别名排序-- 查询员工名、工资、年薪 按年薪升序 SELECT last_name, salary, salary12*(1+IFNULL(commission_pct,0)) 年薪FROM employeesORDER BY 年薪 ASC;-- 按函数排序-- 查询员工名、名字的长度，并且按名字的长度降序-- 长度的函数是length() 函数后面会详细介绍SELECT last_name, LENGTH(last_name) 长度FROM employeesORDER BY LENGTH(last_name) ASC;
-- 查询员工的工资和工资级别SELECT e.salary, g.grade_levelFROM employees e,job_grades gWHERE e.salary BETWEEN g.lowest_sal AND g.highest_salORDER BY g.grade_level DESC;
SELECT salary FROM employees WHERE last_name = 'Abel';
一般搭配着单行操作符使用有：< > = >= <= <> !=
列子查询(一列多行)
SELECT DISTINCT department_id FROM departmentsWHERE location_id IN(1400,1700);
一般搭配着多行操作符使用：in any all。
行子查询(一行多列)
SELECT MIN(employee_id),MAX(salary) FROM employees;
表子查询(多行多列)
SELECT * FROM employees;
7.4 按位置分 7.4.1 在where或having后面
在where或having后面，它支持标量子查询、列子查询、行子查询。
标量子查询
-- 标量子查询-- 可以实现多个子查询 -- 返回job_id与141号员工相同，salary比143号员工工资多的员工姓名，job_id和工资-- 第一次就比较详细的分析，写出来，后面就直接写出来-- 1.查询141号员工的job_idSELECT job_id FROM employeesWHERE employee_id = 141;-- 2.查询143号员工的salarySELECT salary FROM employeesWHERE employee_id = 143;-- 3.查询员工的姓名，job_id 和工资，要求job_id=1并且salary>2SELECT last_name, job_id, salaryFROM employeesWHERE job_id = ( SELECT job_id FROM employees WHERE employee_id = 141)AND salary > ( SELECT salary FROM employees WHERE employee_id = 143);-- 返回公司工资最少的员工的last_name,job_id和salary-- 1.查询公司的最低工资 2. 查询last_name,job_id和salary，要求salary=1的结果SELECT last_name, job_id, salaryFROM employeesWHERE salary = ( SELECT MIN(salary) FROM employees);-- 在having后-- 查询最低工资大于50号部门最低工资的部门id和其最低工资-- 1.查询50号部门的最低工资 2.查询每个部门的最低工资 3. 在2的基础上筛选，满足min(salary)>1的结果SELECT MIN(salary), department_idFROM employeesGROUP BY department_idHAVING MIN(salary) > ( SELECT MIN(salary) FROM employees WHERE department_id = 50);
列子查询
-- 列子查询
-- 返回location_id是1400或1700的部门中的所有员工姓名 (in)
-- 1.查询location_id是1400或1700的部门编号 2. 查询员工姓名，要求部门号是1列表中的某一个
SELECT last_name FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400, 1700)
);
-- 返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary (any)
-- 1.查询job_id为‘IT_PROG’部门任一工资 2. 查询员工号、姓名、job_id 以及salary，salary<(1)的任意一个
SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary < ANY(
SELECT DISTINCT salary FROM employees
WHERE job_id = 'IT_PROG'
);
-- 返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary (all)
-- 思路与上面类似
SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary < ALL(
SELECT DISTINCT salary FROM employees
WHERE job_id = 'IT_PROG'
);
行子查询
用处相对来说不是特别的多。
-- 查询员工编号最小并且工资最高的员工信息-- 1. 最小的员工编号 2. 工资最高 3. 在2,1 的基础上筛选-- 以前的做法SELECT FROM employeesWHERE employee_id = (SELECT MIN(employee_id) FROM employees)AND salary = (SELECT MAX(salary) FROM employees);-- 行子查询的做法SELECT FROM employeesWHERE (employee_id, salary)=( SELECT MIN(employee_id), MAX(salary) FROM employees);
7.4.2 在select后面
仅仅支持标量子查询
-- select 后面-- 查询每个部门的员工个数SELECT d.*,( SELECT COUNT() FROM employees e WHERE e.department_id = d.department_id ) 个数 FROM departments d;-- 查询员工号=102的部门名SELECT ( SELECT department_name,e.department_id FROM departments d INNER JOIN employees e ON d.department_id=e.department_id WHERE e.employee_id=102 ) 部门名;
7.4.3 在from后面
相当于把子查询充当一张表，但是这个必须得起别名。
-- from 后面-- 查询每个部门的平均工资的工资等级-- 1.查询每个部门的平均工资SELECT AVG(salary), department_idFROM employeesGROUP BY department_id;-- 2.连接1的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_salSELECT avg_sa. , g.grade_levelFROM ( SELECT AVG(salary) sa, department_id FROM employees GROUP BY department_id) avg_saINNER JOIN job_grades gON avg_sa.sa BETWEEN g.lowest_sal AND g.highest_sal;
7.4.4 exists后面（相关子查询）
语法：
exists(完整的查询语句)。
返回的结果一般是1或者0。
-- 查询有员工的部门名SELECT department_idFROM departments dWHERE EXISTS( SELECT * FROM employees e WHERE e.department_id = d.department_id);-- 查询没有男神的女神信息SELECT b. FROM beauty bWHERE EXISTS( SELECT bo.id FROM boys bo WHERE bo.id = b.boyfriend_id);
-- 查询前五条员工信息SELECT FROM employees LIMIT 0,5;-- 如果offset是0的话，可以省略SELECT * FROM employees LIMIT 5;-- 查询第11条——第15条员工信息SELECT FROM employees LIMIT 10, 5;-- 有奖金的员工信息，并且工资较高的前10名显示出来SELECT FROM employeesWHERE commission_pct IS NOT NULLORDER BY salary DESCLIMIT 10;
-- 查询部门编号>90或邮箱包含a的员工信息-- 以前用法SELECT FROM employeesWHERE employee_id > 90 OR email LIKE '%a%';-- 使用联合查询SELECT FROM employees WHERE employee_id > 90UNIONSELECT * FROM employees WHERE email LIKE '%a%';
9.3 特点
多条查询语句的查询的列数必须是一致的。
多条查询语句的查询的列的类型几乎相同。
union代表去重，union all代表不去重。
总结
学到这里的查询，可以看下完整的语法：
SELECT 查询列表 FROM 表名【连接类型】 join 表2on 连接条件where 筛选条件group by 分组字段having 分组后的筛选order by 排序的字段limit 【offset,】size;
-- 先创建一个major 表CREATE TABLE major( id INT PRIMARY KEY, majorName VARCHAR(20));
2.3.1 列级约束
语法：直接在字段名和类型后面追加 约束类型即可。
CREATE TABLE 表名( 字段名 字段类型 列级约束, 字段名 字段类型 列级约束,);
实例：
-- 列级约束CREATE TABLE stuinfo( id INT PRIMARY KEY, -- 主键 stuName VARCHAR(20) NOT NULL, -- 非空 gender CHAR(1) CHECK(gender='男' OR gender ='女'), -- 检查 seat INT UNIQUE, -- 唯一 age INT DEFAULT 18, -- 默认约束 majorId INT REFERENCES major(id) -- 外键);-- 可以通过以下命令查看表结构和索引信息DESC stuinfo;SHOW INDEX FROM stuinfo;
-- 表级约束DROP TABLE IF EXISTS stuinfo;CREATE TABLE stuinfo( id INT, stuname VARCHAR(20), gender CHAR(1), seat INT, age INT, majorid INT, CONSTRAINT pk PRIMARY KEY(id),-- 主键 CONSTRAINT uq UNIQUE(seat),-- 唯一键 CONSTRAINT ck CHECK(gender ='男' OR gender = '女'), -- 检查 CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) -- 外键);DESC stuinfo;SHOW INDEX FROM stuinfo;-- 【CONSTRAINT 名称】 这个可以省略DROP TABLE IF EXISTS stuinfo;CREATE TABLE stuinfo( id INT, stuname VARCHAR(20), gender CHAR(1), seat INT, age INT, majorid INT, PRIMARY KEY(id),-- 主键 UNIQUE(seat),-- 唯一键 CHECK(gender ='男' OR gender = '女'), -- 检查 FOREIGN KEY(majorid) REFERENCES major(id) -- 外键);SHOW INDEX FROM stuinfo;
2.3.3 小结
我们通用的写法可以这么写：
-- 类似这样既可CREATE TABLE IF NOT EXISTS stuinfo( id INT PRIMARY KEY, stuname VARCHAR(20), sex CHAR(1), age INT DEFAULT 18, seat INT UNIQUE, majorid INT, CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id));
ON UPDATE CASCADE ：级联更新 主表主键修改后，从表的数据也跟着修改。ON DELETE CASCADE ：级联删除 主表主键删除后，从表数据也跟着删除。
具体用法
CREATE TABLE IF NOT EXISTS stuinfo( id INT PRIMARY KEY, stuname VARCHAR(20), sex CHAR(1), age INT DEFAULT 18, seat INT UNIQUE, majorid INT, CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) ON UPDATE CASCADE ON DELETE CASCADE);
3. 标识列
又称为自增长列，可以不用手动的插入值，系统提供默认的序列值
3.1 语法
字段名 字段类型 PRIMARY KEY AUTO_INCREMENT
关键字：AUTO_INCREMENT 表示自动增长(字段类型必须是数值类型) 3.2 用法
-- 创建表时设置标识列CREATE TABLE tab_identity( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20));-- 插入5条数据INSERT INTO tab_identity (id,NAME) VALUES(NULL,'hello');SELECT * FROM tab_identity;-- 修改表时添加标识列DROP TABLE IF EXISTS tab_identity;CREATE TABLE tab_identity( id INT, NAME VARCHAR(20));ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;-- 删除标识列ALTER TABLE tab_identity MODIFY COLUMN id INT;
3.3 特点
标识列必须和一个key搭配，不一定是主键。
CREATE TABLE tab_identity( id INT UNIQUE AUTO_INCREMENT, NAME VARCHAR(20));
一个表可以最多一个的标识列。
CREATE TABLE tab_identity( id INT UNIQUE AUTO_INCREMENT, NAME INT UNIQUE AUTO_INCREMENT -- 就会报错);
标识列的类型只能是数值型。
CREATE TABLE tab_identity( id INT, NAME VARCHAR(20) UNIQUE AUTO_INCREMENT -- 类型不对也会报错);
标识列可以通过 SET auto_increment_increment=数字;设置步长，也可以通过手动插入值，设置起始值。
-- 设置步长5DROP TABLE IF EXISTS tab_identity;CREATE TABLE tab_identity( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20));-- 查看步长SHOW VARIABLES LIKE '%auto_increment%';SET auto_increment_increment=5;-- 设置起始索引即开始位置。INSERT INTO tab_identity (id,NAME) VALUES(100,'hello');
数据库
发布于：2021-08-03 15:07
|
阅读数：502
|
评论：0
QQ好友和群
QQ空间
