MYSQL笔记
MySQL:
mysql是一种开源代码的关系型数据库管理系统。
数据库(database):即存数据的“仓库”,它保存了一系列有组织的数据
DBMS:数据库管理系统(Database Management System):是一种操纵和管理数据库的大型软件,列如刚刚建立、使用和位数数据库。
删除数据库:drop database 数据库名;
选择使用数据库:use 数据库名;
查看当前使用的数据库:select database();
数据库表的操作:
查看当前的数据库的所有表格:show tables; (注意:前面必须要有use数据库名 语句,否则报错)
创建表结构:
基础版:
CREATE TABLE 表名称(字段名1 数据类型1, 字段名2 数据类型2, 字段名3 数据类型3);
CREATE TABLE t_stu(sid INT, sname VARCHAR(100),gender CHAR)
详细版:
CREATE TABLE 表名称(字段名1 数据类型1 主键 自增长,字段名2 数据类型2 非空 默认值,字段名3 数据类型3)ENGINE=当前表格的引擎 AUTO_INCREMENT=自增长的起始值 DEFAULT CHARSET=表数据的默认字符集;
CREATE TABLE t_stu(sid INT PRIMARY KEY AUTO_INCREMENT,sname ARCHAR(100) NOT NULL,gender CHAR NOT NULL DEFAULT '男')ENGINE=INNODB INCREMENT=1 DEFAULT CHARSET=utf8;
查看表结构:desc 表名称;
删除表结构:drop table 表名称;(注意:数据和结构都将被删除)
修改表结构:
- alter table 表名 rename 新表名;
- rename table 表名 to 新表名;
增加一列:
- alter table 表名 add 【column】列名 数据类型【default默认值】 【not null】;(默认在最后面)
- alter table 表名 add 【column】 列名 数据类型【default默认值】【not null】after 某一列;
- alter table 表名 add 【column】列名 数据类型【default默认值】【not null】first;
- 删除列:
- alter table 表名 drop 【column】列名 新列名 数据类型【default默认值】【not null】;
查看某个表的约束和索引:
- SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
- SHOW INDEX FROM 表名;
- SHOW CREATE FROM 表名;
自增列(AUTO_INCREMENT):
一、DML操作
数据操作语言(DML)DML用于插入、修改、删除数据记录,包括如下SQL语句:
- INSERT(insert):添加数据到数据库中
- UPDATA(updata):修改数据库中的数据
- DELETE(delete):删除数据库中的数据
1.1插入数据
1.1.1语法
- INSERT INTO 表名称 VALUES(值1,值2...);=
{ insert into 表名称 values(值1,值2.....);}
- INSERT INTO 表名称 VALUES(值1,值2,......),(值1,值2......);
{insert into 表名称 values(值1,值2,......),(值1,值2,......);}
- INSERT INTO 表名称 (字段1,字段2,......) VALUES(值1,值2,......);
{insert into 表名称(字段1,字段2,....) values(值1,值2.....);}
- INSERT INTO 表名称 (字段1,字段2,......) VALUES(值1,值2,......),(值1,值2,......),.....;
{insert into 表名称(字段1.字段2,....)values(值1,值2,......),(值1,值2,......),.....; }
1.1.3示例
CRETATE TABLE s_stu(
sid int primary key auto_incrment,
sname varchar(100) not null,
gender char not null default '男',
card_id dhar(18)not null unique,
birthdat date,
address varchar(200)
);
INSERT INTO t_stu VALUES(1,'张三',DEFAULT,'123456789012345678','1989-09-09',NULL); INSERT INTO t_stu VALUES(2,'李四','女','123456789012345677','1988-09-09','硅谷'); INSERT INTO t_stu VALUES(0,'王五','男','123456789012345676','1987-09-09','硅谷'); INSERT INTO t_stu VALUES(NULL,'赵六','男','123456789012345675','1987-09-09','硅谷');
INSERT INTO t_stu VALUES (NULL,'冰冰','女','123456789012345674','1988-09-09','硅谷'), (NULL,'小丽','女','123456789012345673','1988-09-09','硅谷');
INSERT INTO t_stu (sname,card_id,birthday)
VALUES('小薇','123456199012045672',STR_TO_DATE(SUBSTRING(card_id,7,8),'%Y%m%d'));
INSERT INTO t_stu (sname,card_id,birthday)VALUES ('小红','123456789012345671','1990-09-09'), ('小紫','123456789012345670','1990-09-09');
练习
CREATE TABLE t_department( did INT PRIMARY KEY AUTO_INCREMENT, dname VARCHAR(100) NOT NULL, description VARCHAR(200), manager_id INT ); INSERT INTO t_department(dname,description) VALUES('教学部','技术培训'), ('咨询部','课程咨询服务');
CREATE TABLE `t_job` ( `job_id` INT(11) PRIMARY KEY AUTO_INCREMENT, `job_name` VARCHAR(100) DEFAULT NULL, `description` VARCHAR(200) DEFAULT NULL ); INSERT INTO t_job VALUES (NULL,'JavaSE讲师','Java基础'), (NULL,'Web讲师','Web基础'), (NULL,'JavaEE框架','框架讲解'), (NULL,'课程顾问','课程咨询');
CREATE TABLE t_employee( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(100) NOT NULL, gender CHAR NOT NULL DEFAULT '男', card_id CHAR(18) UNIQUE, tel CHAR(11), job_id INT, `mid` INT, birthday DATE, hiredate DATE, address VARCHAR(100), dept_id INT, FOREIGN KEY (dept_id) REFERENCES t_department(did), FOREIGN KEY (job_id) REFERENCES t_job(job_id) ); INSERT INTO `t_employee`(`eid`,`ename`,`gender`,`card_id`,`tel`,`job_id`,`mid`,`birthday`,`hiredate`,`address`,`dept_id`) VALUES (1,'孙红雷','男','123456789012345678','12345678901',1,NULL,'1990-01-01','2015-01-01','白庙',1), (2,'张亮','男','123456789012345677','12345678902',2,NULL,'1990-01-02','2015-01-02','天通苑北',1), (3,'鹿晗','男','123456789012345676','12345678903',3,NULL,'1990-01-03','2015-01-03','北苑',1), (4,'邓超','男','123456789012345675','12345678904',2,NULL,'1990-01-04','2015-01-04','和谐家园',1), (5,'孙俪','女','123456789012345674','12345678905',3,NULL,'1990-01-05','2015-01-05','霍营',1), (6,'Angelababy','女','123456789012345673','12345678906',4,NULL,'1990-01-06','2015-01-06','回龙观',2);
CREATE TABLE t_salary( eid INT PRIMARY KEY, basic_salary DECIMAL(10,2), performance_salary DECIMAL(10,2), commission_pct DECIMAL(10,2), deduct_wages DECIMAL(10,2), FOREIGN KEY (eid) REFERENCES t_employee(eid) ); INSERT INTO `t_salary`(`eid`,`basic_salary`,`performance_salary`,`commission_pct`,`deduct_wages`) VALUES (1,'12000.00','6000.00','0.40','0.00'), (2,'9000.00','5000.00','0.20',NULL), (3,'11000.00','8000.00',NULL,NULL), (4,'13000.00','5000.00',NULL,NULL), (5,'8000.00','8000.00','0.30',NULL), (6,'15000.00','6000.00',NULL,NULL);
修改数据
- update 表名称 set 字段名=值1,字段名2=值2,.......(where 条件);
- UPDATE 表1,表2,...... SET 表1.字段名1 = 值1, 表1.字段名2=值2,表2.字段1 = 值1, 表2.字段2=值2...... 【WHERE 条件】;
说明:
2、值可以是常量值、表达式、函数
3、可以同时更新多张表
#修改所有人的基本工资,涨薪5%
UPDATE t_salary
SET basic_salary = basic_salary * 1.05 where 1=1;
#修改"孙俪"的手机号码为"13709098765",生日为"1982-09-26" UPDATE t_employee SET tel = '13709098765',birthday = '1982-09-26' WHERE ename = '孙俪';
#修改"邓超"的入职日期为今天 UPDATE t_employee SET hiredate = CURDATE() WHERE ename ='邓超'; #修改"咨询部"的主管id为6 UPDATE t_department SET manager_id =6 WHERE did = 2; #修改"教学部"的主管id为1 UPDATE t_department SET manager_id =1 WHERE did = 1; #修改"教学部"的主管id为"孙红雷"的编号 UPDATE t_department,t_employee SET t_department.manager_id =t_employee.eid WHERE t_department.`dname` = '教学部' AND t_department.`did` = t_employee.`dept_id` AND t_employee.ename = '孙红雷'; #修改所有员工的领导编号为该员工所在部门的主管编号 UPDATE t_employee,t_department SET t_employee.mid = t_department.manager_id WHERE t_employee.dept_id = t_department.did; #修改教学部的主管编号,以及该部门所有员工的领导编号为"邓超"的编号 UPDATE t_department,t_employee SET t_department.manager_id =t_employee.eid WHERE t_department.`dname` = '教学部' AND t_department.`did` = t_employee.`dept_id` AND t_employee.ename = '邓超'; UPDATE t_employee,t_department SET t_employee.mid = t_department.manager_id WHERE t_employee.dept_id = t_department.did AND t_department.`dname` = '教学部';
删除数据
- delete from 表名称 (where 条件);
- delete 表1,表2,....... from 表1,表2,...... 【where 条件】;
delete from 表名;删除整张表的数据还可以使用truncate 表名;
#删除学号为9的学生信息 DELETE FROM t_stu WHERE sid = 9; #注意:前提是没有外键或外键是on delete cascade #删除所有“教学部”的员工信息和薪资信息和“教学部”部门信息 DELETE t_employee,t_department,t_salary FROM t_employee,t_department,t_salary WHERE t_department.`dname` ='教学部' AND t_employee.`dept_id`=t_department.`did` AND t_employee.`eid` = t_salary.eid;
查询数据
select 查询列表 from 表名或视图列表
【where 条件表达式】
【group by 字段名 【having条件表达式】】
【order by 字段 【asc|decs】】
【limt m,n】;
说明:
(2)SELECT后面的查询列表,可以是表中的字段,常量值,表达式,函数
(3)查询的结果是一个虚拟的表
select语句,可以包含5种子句:依次是where、 group by、having、 order by、limit必须照这个顺序。
示例:
#查询表中的所有行所有列 #使用*表示,查询所有字段,即查询所有行 select * from t_stu; #查询部分字段 select sname,major from t_stu; #查询所有列,部分行 select * from t_stu where major = 'JavaEE'; #查询部分行,部分列 select sname,major from t_stu where major = 'JavaEE';
别名AS
AS 别名
示例:
UPDATE t_department AS d,t_employee AS e SET d.manager_id =e.eid WHERE d.dname = '教学部' AND d.did = e.`dept_id` AND e.ename = '孙红雷'; #查询员工姓名以及手机号码 SELECT ename AS '员工姓名',tel AS '手机号码' FROM t_employee;
去重DISTINCT
示例:
#查询员工表的部门编号
SELECT DISTINCT dept_id FROM t_employee;
#统计员工表中员工有几个部门
SELECT COUNT(DISTINCT dept_id) FROM t_employee;
去重号:
例如:select name
from t_stu;
如果字段名或表名与关键字一样更要加着重号了
MYSQL运算符
- 算术运算符:+ - * /(除也可以写成div,div取整) %(取模可以写成mod)
- 比较运算符:= > >= < <= !=(不等于还可以写成<>) <=>(安全等于)
- 逻辑运算符:&&(逻辑与也可以写成and) ||(逻辑或也可以写成or) not(逻辑非) xor(逻辑异或)
- 范围:表达式 between ... and ... (也可以写成 表达式>=... and 表达式 <=...)
- 表达式 not between ... and ...(也可以写成 表达式<... || 表达式 >...)
- 集合:in (值,值,值...) not in(值,值,值...)
- 模糊查询:LIKE NOT LIKE,通配符:%表示0-n个字符,_下划线代表一个字符
- 位运算符:&(按位与) |(按位或)^(按位异或)~(按位取反)>>(右移)<<(左移)
- NULL值判断,is null 或 is not null,如果使用null=null,null<>null,null=0,null<>0,null=false等都不对
- 不过xxx is null 可以使用xxx <=> null ,xxx is not null 可以写成 not xxx <=> null
- 结论:所有的运算符遇到NULL结果都是NULL,除了<=>
算术运算符:
示例:
#+,-,*,/(div),%(mod) #筛选出eid是偶数的员工 SELECT * FROM t_employee WHERE eid % 2 = 0; SELECT * FROM t_employee WHERE eid MOD 2 = 0; #查看每天的基本工资值,每个月按22天算 SELECT eid,basic_salary/22 AS "日薪" FROM t_salary; #div也表示除,但是只保留整数部分 SELECT eid,basic_salary DIV 12 AS "日薪" FROM t_salary; #关于+,在Java中,+的左右两边如果有字符串,那么表示字符串的拼接,但是在MySQL中+只表示数值相加, #如果遇到非数值类型,先尝试转成数值,如果转失败,就按0计算 SELECT eid+ename FROM t_employee; SELECT eid+birthday FROM t_employee; #MySQL中字符串拼接要使用字符串函数实现 SELECT CONCAT(eid,":",ename) AS result FROM t_employee;
比较运算符
示例:
#=,>, <,>=, <=, !=(不等于<>),<=>(安全等于) #查询basic_salary!=10000 SELECT eid,basic_salary FROM t_salary WHERE basic_salary != 10000; SELECT eid,basic_salary FROM t_salary WHERE basic_salary <> 10000; #查询basic_salary=10000,注意在Java中比较是== SELECT eid,basic_salary FROM t_salary WHERE basic_salary = 10000; #查询commission_pct等于0.40 SELECT eid,commission_pct FROM t_salary WHERE commission_pct = 0.40; SELECT eid,commission_pct FROM t_salary WHERE commission_pct <=> 0.40; #查询commission_pct等于NULL SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NULL; SELECT eid,commission_pct FROM t_salary WHERE commission_pct <=> NULL; #查询commission_pct不等于NULL SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NOT NULL; SELECT eid,commission_pct FROM t_salary WHERE NOT commission_pct <=> NULL;
逻辑运算符
示例:
#与&&,或||,非! #与 AND,或 OR ,非 NOT,异或 XOR #查询性别男,并且在90以前出生的员工 SELECT * FROM t_employee WHERE gender='男' AND birthday<'1990-01-01'; #查询职位编号job_id是1或2的员工 SELECT * FROM t_employee WHERE job_id =1 OR job_id = 2; #查询基本薪资是在9000-12000之间的员工编号和基本薪资 SELECT eid,basic_salary FROM t_salary WHERE basic_salary >=9000 AND basic_salary<=12000; #查询基本薪资不在9000-12000之间的员工编号和基本薪资 SELECT eid,basic_salary FROM t_salary WHERE NOT (basic_salary >=9000 AND basic_salary<=12000); SELECT eid,basic_salary FROM t_salary WHERE basic_salary <9000 OR basic_salary>12000
范围和集合
示例
#between ... and ... 和 not between ... and ... #in(集合) 和 not in(...) #查询基本薪资是在9000-12000之间的员工编号和基本薪资 SELECT eid,basic_salary FROM t_salary WHERE basic_salary BETWEEN 9000 AND 12000; #查询eid是1,3,5的基本工资 SELECT eid,basic_salary FROM t_salary WHERE eid IN (1,3,5);
模糊查询
示例:
#like 和 通配符 一起使用 #like _ 匹配单个字符 #like % 匹配任意个字符 #查询名字中有'冰'字的员工信息 SELECT * FROM t_employee WHERE ename LIKE '%冰%'; #查询姓李的员工信息 SELECT * FROM t_employee WHERE ename LIKE '李%'; #查询姓李,名字就一个字的员工信息 SELECT * FROM t_employee WHERE ename LIKE '李_'; #查询李冰冰的信息 SELECT * FROM t_employee WHERE ename LIKE '李冰冰';
NULL值判断与计算处理
示例
#NULL值判断与处理 #查询奖金百分比不为空的员工编号 SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NOT NULL; #查询奖金百分比为空的员工编号 SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NULL; #关于null值计算 #所有运算符遇到null都是null #计算实际的薪资: basic_salary + salary * 奖金百分比 #函数:IFNULL(表达式,用什么值代替) SELECT eid,basic_salary + performance_salary *(1+ commission_pct) FROM t_salary;#错误的 SELECT eid,basic_salary + performance_salary *(1+ IFNULL(commission_pct,0)) FROM t_salary; #<=>安全等于 #查询奖金百分比为空的员工编号 SELECT eid,commission_pct FROM t_salary WHERE commission_pct <=> NULL;
连接分为:
一对多、多对一、多对多。
关联查询、联合查询
-
内连接:INNER JOIN 、CROSS JOIN
-
外连接:左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)、全外连接(FULL OUTER JOIN)
-
自连接:当table1和table2本质上是同一张表,只是用取别名的方式虚拟成两张表以代表不同的意义
笛卡尔积
示例
#笛卡尔积
#查询员工姓名和所在部门名称
SELECT ename,dname FROM t_employee,t_department;
SELECT ename,dname FROM t_employee INNER JOIN t_department;
SELECT ename,dname FROM t_employee CROSS JOIN t_department;
SELECT ename,dname FROM t_employee JOIN t_department;
关联条件
示例:
#关联条件 #把关联条件写在where后面 SELECT ename,dname FROM t_employee,t_department WHERE t_employee.dept_id=t_department.did; #把关联条件写在on后面,只能和JOIN一起使用 SELECT ename,dname FROM t_employee INNER JOIN t_department ON t_employee.dept_id=t_department.did; SELECT ename,dname FROM t_employee CROSS JOIN t_department ON t_employee.dept_id=t_department.did; SELECT ename,dname FROM t_employee JOIN t_department ON t_employee.dept_id=t_department.did; #把关联字段写在using()中,只能和JOIN一起使用 #而且两个表中的关联字段必须名称相同,而且只能表示= #查询员工姓名与基本工资 SELECT ename,basic_salary FROM t_employee INNER JOIN t_salary USING(eid); #n张表关联,需要n-1个关联条件 #查询员工姓名,基本工资,部门名称 SELECT ename,basic_salary,dname FROM t_employee,t_department,t_salary WHERE t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid; SELECT ename,basic_salary,dname FROM t_employee INNER JOIN t_department INNER JOIN t_salary ON t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid;
内连接
隐式:SELECT [cols_list] from 表1,表2 where [condition]
显式:
- SELECT [cols_list] from 表1 INNER JOIN 表2 ON [关联条件] where [其他筛选条件]
- SELECT [cols_list] from 表1 CROSS JOIN 表2 ON [关联条件] where [其他筛选条件]
- SELECT [cols_list] from 表1 JOIN 表2 ON [关联条件] where [其他筛选条件]
#内连接 #查询员工姓名和所在部门名称 SELECT ename,dname FROM t_employee,t_department WHERE t_employee.dept_id=t_department.did; SELECT ename,dname FROM t_employee INNER JOIN t_department ON t_employee.dept_id=t_department.did; SELECT ename,dname FROM t_employee CROSS JOIN t_department ON t_employee.dept_id=t_department.did; SELECT ename,dname FROM t_employee JOIN t_department ON t_employee.dept_id=t_department.did; #查询员工姓名,基本工资,部门名称 SELECT ename,basic_salary,dname FROM t_employee,t_department,t_salary WHERE t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid; SELECT ename,basic_salary,dname FROM t_employee INNER JOIN t_department INNER JOIN t_salary ON t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid;
外连接
外连接分为:
- 左外连接(LEFT OUTER JOIN),简称左连接(LEFT JOIN)
- 右外连接(RIGHT OUTER JOIN),简称右连接(RIGHT JOIN)
- 全外连接(FULL OUTER JOIN),简称全连接(FULL JOIN)。
自连接:
自连接 #查询员工姓名以及领导姓名,仅显示有领导的员工 SELECT emp.ename,mgr.ename FROM t_employee AS emp, t_employee AS mgr WHERE emp.mid = mgr.eid; #查询员工姓名以及领导姓名,仅显示有领导的员工 SELECT emp.ename,mgr.ename FROM t_employee AS emp INNER JOIN t_employee AS mgr ON emp.mid = mgr.eid; #查询所有员工姓名及其领导姓名 SELECT emp.ename,mgr.ename FROM t_employee AS emp LEFT JOIN t_employee AS mgr ON emp.mid = mgr.eid;
聚合函数
-
-
COUNT(【DISTINCT】 expr)返回expr的非NULL值的数目
-
MIN(【DISTINCT】 expr)返回expr的最小值
-
MAX(【DISTINCT】 expr)返回expr的最大值
-
SUM(【DISTINCT】 expr)返回expr的总和
代码:
#聚合函数 #AVG(【DISTINCT】 expr) 返回expr的平均值 SELECT AVG(basic_salary) FROM t_salary; #COUNT(【DISTINCT】 expr)返回expr的非NULL值的数目 #统计员工总人数 SELECT COUNT(*) FROM t_employee;#count(*)统计的是记录数 #统计员工表的员工所在部门数 SELECT COUNT(dept_id) FROM t_employee;#统计的是非NULL值 SELECT COUNT(DISTINCT dept_id) FROM t_employee;#统计的是非NULL值,并且去重 #MIN(【DISTINCT】 expr)返回expr的最小值 #查询最低基本工资值 SELECT MIN(basic_salary) FROM t_salary; #MAX(【DISTINCT】 expr)返回expr的最大值 #查询最高基本工资值 SELECT MAX(basic_salary) FROM t_salary; #查询最高基本工资与最低基本工资的差值 SELECT MAX(basic_salary)-MIN(basic_salary) FROM t_salary; #SUM(【DISTINCT】 expr)返回expr的总和 #查询基本工资总和 SELECT SUM(basic_salary) FROM t_salary;
代码:
#group by + 聚合函数 #统计每个部门的人数 SELECT dept_id,COUNT(*) FROM t_employee GROUP BY dept_id; #统计每个部门的平均基本工资 SELECT emp.dept_id,AVG(s.basic_salary ) FROM t_employee AS emp,t_salary AS s WHERE emp.eid = s.eid GROUP BY emp.dept_id; #统计每个部门的年龄最大者 SELECT dept_id,MIN(birthday) FROM t_employee GROUP BY dept_id; #统计每个部门基本工资最高者 SELECT emp.dept_id,MAX(s.basic_salary ) FROM t_employee AS emp,t_salary AS s WHERE emp.eid = s.eid GROUP BY emp.dept_id; #统计每个部门基本工资之和 SELECT emp.dept_id,SUM(s.basic_salary ) FROM t_employee AS emp,t_salary AS s WHERE emp.eid = s.eid GROUP BY emp.dept_id;
#按照部门统计员工人数,仅显示部门人数少于3人的 SELECT dept_id,COUNT(*) AS c FROM t_employee WHERE dept_id IS NOT NULL GROUP BY dept_id HAVING c <3; #查询每个部门的平均工资,并且仅显示平均工资高于10000 SELECT emp.dept_id,AVG(s.basic_salary ) AS avg_salary FROM t_employee AS emp,t_salary AS s WHERE emp.eid = s.eid AND dept_id IS NOT NULL GROUP BY emp.dept_id HAVING avg_salary >10000;
#排序 #查询员工基本工资,按照基本工资升序排列,如果工资相同,按照eid升序排列 SELECT t_employee.eid,basic_salary FROM t_employee INNER JOIN t_salary ON t_employee.eid = t_salary.eid ORDER BY basic_salary,eid; #查询员工基本工资,按照基本工资降序排列,如果工资相同,按照eid排列 SELECT t_employee.eid,basic_salary FROM t_employee INNER JOIN t_salary ON t_employee.eid = t_salary.eid ORDER BY basic_salary DESC,eid; #统计每个部门的平均基本工资,并按照平均工资降序排列 SELECT emp.dept_id,AVG(s.basic_salary) FROM t_employee AS emp,t_salary AS s WHERE emp.eid = s.eid GROUP BY emp.dept_id ORDER BY AVG(s.basic_salary) DESC;
#分页 #查询员工信息,每页显示5条,第二页 SELECT * FROM t_employee LIMIT 5,5; #统计每个部门的平均基本工资,并显示前三名 SELECT emp.dept_id,AVG(s.basic_salary) FROM t_employee AS emp,t_salary AS s WHERE emp.eid = s.eid GROUP BY emp.dept_id ORDER BY AVG(s.basic_salary) DESC LIMIT 0,3;
子查询
IN:等于任何一个,sal in (1,2,3)等价于sal>1 or sal>2 or sal>3
ALL:和子查询返回的所有值比较。例如:sal>ALL(1,2,3)等价于sal>1 && sal>2 && sal>3,即大于所有。
ANY:和子查询返回的任意一个值比较。例如:sal>ANY(1,2,3)等价于sal>1 or sal>2 or sal>3,即大于任意一个就可以。
EXISTS:判断子查询是否有返回结果(不关心具体行数和内容),如果返回则为TRUE,否则为FALSE。
#子查询 #where型子查询 #查询比“孙红雷”的工资高的员工编号 SELECT * FROM t_salary WHERE basic_salary > (SELECT basic_salary FROM t_employee INNER JOIN t_salary ON t_employee.eid=t_salary.eid WHERE t_employee.ename='孙红雷'); #查询和孙红雷,李晨在同一个部门的员工 SELECT * FROM t_employee WHERE dept_id IN(SELECT dept_id FROM t_employee WHERE ename='孙红雷' OR ename = '李晨'); SELECT * FROM t_employee WHERE dept_id = ANY(SELECT dept_id FROM t_employee WHERE ename='孙红雷' OR ename = '李晨'); #查询全公司工资最高的员工编号,基本工资 SELECT eid,basic_salary FROM t_salary WHERE basic_salary = (SELECT MAX(basic_salary) FROM t_salary); SELECT eid,basic_salary FROM t_salary WHERE basic_salary >= ALL(SELECT basic_salary FROM t_salary);
from查询
#from型 #找出比部门平均工资高的员工编号,基本工资 SELECT t_employee.eid,basic_salary FROM t_salary INNER JOIN t_employee INNER JOIN ( SELECT emp.dept_id AS did,AVG(s.basic_salary) AS avg_salary FROM t_employee AS emp,t_salary AS s WHERE emp.eid = s.eid GROUP BY emp.dept_id) AS temp ON t_salary.eid = t_employee.eid AND t_employee.dept_id = temp.did WHERE t_salary.basic_salary > temp.avg_salary;
exists型子查询
#exists型 #查询部门信息,该部门必须有员工 SELECT * FROM t_department WHERE EXISTS(SELECT * FROM t_employee WHERE t_employee.dept_id = t_department.did);
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步