MYSQL笔记

MySQL：

mysql是一种开源代码的关系型数据库管理系统。

数据库（database）：即存数据的“仓库”，它保存了一系列有组织的数据

DBMS：数据库管理系统（Database Management System）：是一种操纵和管理数据库的大型软件，列如刚刚建立、使用和位数数据库。

关系型数据库：关系数据库的表采用二维表格来存储数据，是一种按行与列排列的具有相关信息的逻辑组，它类似于Excle工作表。

数据库的基本操作：

创建数据库：create datebase 数据库名；

查看数据库：show databases;

删除数据库：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 表名称；（注意：数据和结构都将被删除）
修改表结构：

1. 　　alter table 表名 rename 新表名；
2. 　　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 条件】;

说明：

1、如果不写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 条件】;

说明

1、如果不加where条件，表示删除整张表的数据，表结构保留。

delete from 表名;删除整张表的数据还可以使用truncate 表名;

truncate相当于删除表再重建一张同名结构的表，操作后得到一张全新表，而delete是在原有表中删除数据。如果决定清空一张表的数据，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】;

说明：

如果SELECT后面是*，那么表示查询所有字段

（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;

聚合函数

AVG(【DISTINCT】 expr) 返回expr的平均值
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 + 聚合函数

代码：

#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;

having 筛选

代码：

#按照部门统计员工人数，仅显示部门人数少于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;

order by 排序

order by col1,col2,col3...

代码：

#排序
#查询员工基本工资，按照基本工资升序排列，如果工资相同，按照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;

limit分页

limit n,m

#分页
#查询员工信息，每页显示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);

posted @ 2020-10-20 20:50 卑微小梁阅读(86) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

梁向月