MySQL是一个关系型数据库管理系统,由瑞典MySQL AB公司开发,目前属于Oracle公司。
基本概念
(1)DB:数据库;
(2)DBMS:数据库管理系统,用于创建和管理DB;
(3)SQL:结构化查询语句,用于和数据库通信的语言。
数据库存储数据的特点:
(1)数据存到表中,然后表存到库中;
(2)一个库中可以有多张表,每张表具有唯一的表名来标识自己;
(3)表中有一个或多个列,又称为“字段”,相当于java中的“属性”;
(4)表中的每一行数据,相当于java中的“对象”。
数据库的好处:
(1)可以持久化数据到本地;
(2)结构化查询。
【注】以下的数据库练习文件——employees.sql可以在网上很快找到。
一、基础查询:
select
查询列表
from
表名
1.查询单个字段
SELECT last_name FROM employees;
2.查询多个字段
SELECT last_name,salary,email FROM employees;
3.查询表中的所有字段
如果有输出顺序要求的话:
SELECT
`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`,`hiredate`
FROM employees;
没有顺序要求时:
SELECT * FROM employees;
4.查询函数
SELECT VERSION();
5.起别名
SELECT
last_name AS 姓,
first_name AS 名
FROM
employees;
6.去重:查询员工表中涉及的所有部门编号
SELECT DISTINCT department_id FROM employees;
7.拼接字段:
—— +号的作用:查询员工名和姓连成一个字段,并显示为姓名
/*
mysql中,+号只能用于运算:
select 100+90;
select ‘123’+90;其中一方为字符型,试图将字符型数组转换为数值型,如果转换成功,则继续加法运算
select ‘john’+90;如果失败,则将字符型数组转换为0
select null +10;只要其中一方为null,结果为null
这个运行是错误的————SELECT last_name+first_name AS 姓名 FROM employees;
*/
#所以此处当用concat(str1,str2)来拼接(null与任何字符拼接都为null)
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
8.运算后起别名:
SELECT employee_id,last_name,salary*12 AS "ANNUAL SALARY" FROM employees;
9.当有的列含有null时:
将null替换为0,并重命名:
SELECT
IFNULL(commission_pct,0) AS 奖金率,
commission_pct
FROM
employees;
#显示表employees的全部列,各个列用逗号隔开
SELECT
CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct,0)) AS out_put
FROM
employees;
二、条件查询
select
查询列表
from
表名
where
筛选条件;
分类:
一、按条件表达式筛选:
条件运算符:>, <, =, != 或者 <>,<=, >=,<=>
二、按逻辑表达式筛选:
逻辑运算符:支持用 &&,||,! 或者 mysql中推荐用 and or not
三、模糊查询:
like , between and, in, is null,is not null
(1)、按条件表达式筛选:
查询工资大于12000的员工信息:
SELECT * FROM employees WHERE salary>12000;
#查询部门编号 不等于90号 的 员工名 和 部门编号
SELECT
CONCAT(first_name,last_name) AS 姓名,department_id
FROM
employees
WHERE
department_id<>90;
#查询工资在10000到20000之间的员工名、工资和奖金。
SELECT
last_name,salary,commission_pct
FROM
employees
WHERE
salary >=10000 AND salary<=20000;
(2)、按逻辑表达式筛选:
#查询部门编号不是在90~110之间,或者工资高于15000之间的员工信息
SELECT
*
FROM
employees
WHERE
department_id <90 OR department_id>110 OR salary>15000;
(3)、模糊查询: ——like , between and, in, is null
#1、模糊查询:查询员工名中包含字符a的员工信息
like:一般和通配符搭配使用
通配符:1)%:任意多个字段,包含0个字符;2)_ :任意单个字符
SELECT * FROM employees WHERE last_name LIKE '%a%';
如果是以a结尾,则:’%a’;以a开头:'a%'
‘1__’:表示以1开头的三位数
2、查询员工名中第三个字符为e,第五个字符为a的员工名和工资
SELECT
last_name,
salary
FROM
employees
WHERE
last_name LIKE '__e_a%';
3、查询员工名中第二个字符为_的员工名
SELECT last_name FROM employees WHERE last_name LIKE '_\_%';
或者使用转移符:’$’
SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';
4、查询部门编号不是在90~110之间,或者工资高于15000之间的员工信息也可以用between and:
SELECT
last_name,salary,commission_pct
FROM
employees
WHERE
salary BETWEEN 10000 AND 20000;
——包含了临界值!不可颠倒顺序!(BETWEEN 20000 AND 10000是错的)
5、in 关键字:查询员工工种编号是 IT_PROG、AD_VP、AD_PRES 中的一个的员工名和工种编号:
——IN列表类型需一致
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN ('IT_PROG','AD_VP','AD_PRES');
6、is null:查询没有奖金的员工名和奖金率:
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NULL;# —— “=”是不能用来判断NULL的!
——不能用 “=”号来判断NULL值,is null和is not null可以判断null值。
【补充】:安全等于<=>,可以判断NULL值
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
——IS NULL :仅仅可以判断NULL值
——<=>:既可以判断NULL值,也可以判断普通数值
【知识点】
(1)启动和停止MySql服务:
net start 服务名
net stop 服务名
登陆:
mysql 【-h 主机名 -p 端口号】 -u 用户名 -p 密码
三、排序查询
语法:
SELECT 查询列表(字段)
FROM 表
ORDER BY 排序列表 【ASC/DESC】
eg:
1、排序查询:按工资从低到高的顺序排序
SELECT * FROM employees ORDER BY salary ASC;
2、查询部门编号>=90的员工信息,按入职时间先后顺序进行排序
SELECT * FROM
employees
WHERE
department_id >= 90
ORDER BY
hiredate ASC;
3、按年薪从大到小排序:
SELECT
*,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
4、按函数排序
SELECT
LENGTH(CONCAT(last_name,first_name)) AS 字节长度,last_name,first_name,salary
FROM
employees
ORDER BY LENGTH(CONCAT(last_name,first_name)) DESC;
*order by 可以支持:单个字段、多个字段、表达式、函数、别名
四、mysql命令基础
字符函数:length,concat,substr,instr,trim,upper,lower,lpad,rpad
数学函数:round,ceil,floor,truncate,mod
日期函数:now,curdate,curtime,year,month,monthname,day,hour,minute,second,str_to_date,date_format
其它函数:version,database,user
控制函数:if,case
(1)字符函数
1.concat:连接字符串
SELECT CONCAT(UPPER(last_name),'.',LOWER(first_name)) AS 姓名 FROM employees;
2. substring:截取字符串(索引从1开始计算)
SELECT SUBSTRING('大厂offer我来了',3,5) AS out_put;
#参数重载,可以有4种参数形式,第二个数字为字符长度
首字符大写,其它小写:
SELECT
CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2))) AS out_put
FROM
employees;
3. instr:返回子串在字符串中的第一次出现的索引
SELECT INSTR('offer I am coming!','coming') AS out_put;
——输出为12
4、trim:去除前后空格 或者 前后特定的字符
去除前后空格:
SELECT TRIM(' offer ') AS out_put;
去除前后特定字符:
SELECT TRIM('a' FROM 'aaaaaaaMy offer!aaaa') AS out_put; #输出:My offer!
5、lpad:用指定的字符实现左填充指定长度
SELECT LPAD('offer',10,'*') AS out_put;
6、replace:替换
SELECT REPLACE('华为offer','华为','腾讯') AS out_put;
——输出“腾讯offer”
(2)数学函数
1、round:四舍五入
SELECT ROUND(1.567,2);#保留两位小数,输出1.57
2、cell:向上取整
SELECT CEIL(1.02);#输出2
3、floor:向下取整
4、truncate:截断
SELECT TRUNCATE(1.69999,2); #输出:1.69
5、mod:取余/模
SELECT MOD(10,3); #等同于:SELECT 10%3;
6、rand:获取随机数,返回0~1之间的小数
(3)日期函数
1、now:返回当前系统日期+时间
SELECT NOW();
year、month、day:返回相应的年月日
hour、minute、second:返回时分秒
SELECT YEAR(NOW()) AS 年;#输出2020
SELECT MONTHNAME(NOW()) AS 月;#输出:May
2、curtdate:返回当前系统日期,不包含时间
SELECT CURDATE();
3、curtime:返回当前时间,不包含系统日期
SELECT CURTIME();
4、str_to_date:将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1995-07-23','%Y-%c-%d') AS out_put;
【应用场景】:
SELECT * FROM
employees
WHERE
hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');#用于将日期解析为相应格式
5、date_format:将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年-%c月-%d日') AS out_put;
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 入职日期 FROM employees;
6、datediff:返回两个日期相差的天数
SELECT DATEDIFF(NOW(),'1995-07-23');
7、monthname:以英文形式返回月
SELECT MONTHNAME(NOW());
(3)其它函数
SELECT VERSION();——SQL版本:8.0.13
SELECT DATABASE();
SELECT USER();
SELECT MD5(‘鹿晗’):返回字符的密码形式
五、流程控制函数
(1)IF
——if(条件表达式,表达式1,表达式2):条件成立,返回表达式1,否则返回表达式2
SELECT IF(10>5,'大','小');#大
(2)case函数的使用一
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
…
else 要显示的值或语句
end
SELECT salary AS 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
(3)case函数的使用二
:类似于 多重if() … else if()
case
when 条件一 then 要显示的值1或语句1
when 条件一 then 要显示的值2或语句2
…
else 要显示的值2或语句2
end
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
六、分组函数
——功能:用作统计使用,又称为聚合函数或组函数
(1)四种分组函数
- 1、sum,avg——支持数值型,不支持字符型
#sum:工资之和
SELECT SUM(salary) FROM employees;
#avg
SELECT AVG(salary) FROM employees;
- 2、max,min——可以支持字符型、日期等任何类型
SELECT MAX(salary) FROM employees;
日期:
SELECT MAX(hiredate),MIN(hiredate) FROM employees;
- 3、count:非空的值的个数——同样可处理任何类型
count(*):统计结果集的行数
count(字段):统计该字段非空值的个数
SELECT COUNT(salary) FROM employees;#非空值个数
(2)特点总结
- sum,avg——支持数值型,不支持字符型
- max、min、count都可处理任何类型的数
- 可和distinct搭配去重
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
-
——以上分组函数都忽略NULL值。
如:SELECT AVG(commission_pct) FROM employees;
,分母为去除NULL后的数 -
那么,用count(*)可用来统计表的行数(只要同一行有不为NULL的,就会统计上):
SELECT COUNT(*) FROM employees;
——得出107行 -
或者:
SELECT COUNT(1) FROM employees;
——将一个常量字段加入到表中,统计个数
效率:
MYISAM存储引擎下,COUNT(*)的效率高;
INNDB存储引擎下,COUNT(字段)的效率更高一些
#查询部门编号为90的员工个数
SELECT COUNT(*) FROM employees WHERE `department_id`=90;
- datediff:将日期换算为天数
SELECT DATEDIFF('2017-10-1','2017-9-29');#等于2
SELECT DATEDIFF(NOW(),'1995-07-23');#我活了这么久了!
#查询员工入职时间相差的最大天数
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;
七、分组查询
(1)分组:group by
分组查询语法:——注意顺序要求
select 分组函数,列
from 表
【where 筛选条件】
group by 要分组的列表(直接填select中的列)
【order by子句】
#查询每个部门的平均工资
SELECT `department_id`,AVG(salary) FROM employees GROUP BY `department_id`;
【案例一】:查询每个工种的最高工资
SELECT `job_id`,MAX(`salary`)
FROM employees
GROUP BY `job_id`;
【案例二】:查询每个位置上的部门个数
SELECT `location_id`,COUNT(*)
FROM `departments`
GROUP BY `location_id`;
【案例三】查询邮箱中含‘a’字符的每个部门的平均工资
SELECT department_id,AVG(salary)
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
(2)分组后的筛选:having
——分组后:筛选条件需要依据分组查询结果
【案例】查询哪个部门的员工个数>2
(1)查询各个部门员工个数
(2)根据1的结果进行筛选
SELECT `department_id`,COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
(3)分组前筛选+分组后筛选
——分组前:筛选条件不需要分组查询结果
【案例】查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
(4)按多个字段分组
【案例】查询每个部门每个工种的员工的平均工资:两个编号一致的员工合并后求平均工资
SELECT AVG(salary),`department_id`,`job_id`
FROM employees
GROUP BY `department_id`,`job_id`;
八、连接查询
(1)多表查询——等值连接
笛卡尔乘积现象:表1有m行,表2有n行,结果=m*n行——不合理!
【案例一】:beauty表和boys表的配对连接
SELECT * FROM beauty;
SELECT * FROM boys;
——出现m*n行数据
解决方案:添加有效的连接条件
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id;
——结果为男女匹配的表
【案例2】查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE departments.department_id = employees.department_id;
(2)多表的等值连接
1)多表等值连接为:多表的交集部分;
2)n表连接,至少需要n-1个连接条件;
3)多表的顺序没有要求;
4)一般需要为表起别名!
5)可以搭配前面的所有子句使用:排序、分组、筛选等。
【案例1】查询城市名中,第二个字符为o的部门名和城市名
SELECT `department_name`,`city`
FROM `departments` d,`locations` l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';
【案例2】查询有奖金的每个部门的 部门名 和 部门的领导编号 和 该部门的最低工资
SELECT `department_name`,d.`manager_id`,MIN(`salary`)
FROM `departments` d,`employees` e
WHERE d.`department_id` = e.`department_id`
AND `commission_pct` IS NOT NULL
GROUP BY `department_name`,d.`manager_id`;
【案例3】查询有奖金的 每个部门的部门名 和 部门的领导编号 和 该部门的最低工资
SELECT `department_name`,d.`manager_id`,MIN(`salary`)
FROM `departments` d,`employees` e
WHERE d.`department_id` = e.`department_id`
AND `commission_pct` IS NOT NULL
GROUP BY `department_name`,d.`manager_id`;
【案例4】查询每个工种的工种名和员工的个数,并且按员工个数降序
SELECT job_title,COUNT(*)
FROM jobs j,employees e
WHERE e.`job_id` = j.`job_id`
GROUP BY `job_title`
ORDER BY COUNT(*) DESC;
【三表连接】
#查询员工名、部门名和所在的城市
SELECT `last_name`,`department_name`,`city`
FROM `employees` e,`departments` d,`locations` l
WHERE d.`location_id` = l.`location_id`;#部门地点 = 城市地点,一个部门对应相应的城市
(3)非等值连接
#查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;
(4)自连接
【案例】查询员工名和上级名称
#注:员工之间胡成上下级关系,员工的manage_id即为其上级的employee_id
SELECT e.`employee_id`,e.`last_name`,m.`employee_id`,m.`last_name`
FROM `employees` e,`employees` m
WHERE e.`manager_id` = m.`employee_id`;
SQL99:
内连接:inner
外连接:1)左外:left 【outer】;2)右外:right 【outer】;3)全外:full 【outer】
交叉连接:cross
一、内连接(与SQL92等值相似)
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件
分类:等值、非等值、自连接
(1)等值连接
【案例一】查询员工名、部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d #两张表可互换顺序
ON e.`department_id` = d.`department_id`;
等同于SQL92中的:
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;
【案例二】查询名字中包含字符e的员工名和工种名(含筛选)
SELECT `last_name`,`job_title`
FROM `employees` e
INNER JOIN `jobs` j
ON e.`job_id` = j.`job_id`
WHERE e.`last_name` LIKE '%e%';
【案例三】查询部门个数>3的城市名和部门个数(分组后查询+筛选)
SELECT city,COUNT(*) 部门个数
FROM `locations` l
INNER JOIN `departments` d
ON l.`location_id` = d.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
【案例四】查询哪个部门的员工个数>3的部门id和员工个数,并按个数降序
SELECT `department_name`,COUNT(*)
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id` = d.`department_id`
GROUP BY `department_name`
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
【案例五】多表等值连接:查询员工名、部门名、工种名,并按部门名降序(三表连接)
SELECT `last_name`,`department_name`,`job_title`
FROM `employees` e
INNER JOIN `departments` d
ON d.`department_id` = e.`department_id`
INNER JOIN `jobs` j
ON j.`job_id` = e.`job_id`
ORDER BY `department_name` DESC;
(2)非等值连接
#非等值连接:查询工资级别人数>20的工资级别个数,并且按工资级别降序
SELECT COUNT(*),`grade_level`
FROM `employees` e
INNER JOIN `job_grades` g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY `grade_level`
HAVING COUNT(*)>20
ORDER BY `grade_level` DESC;
(3)自连接
#查询姓名中包含字符K的员工名字、上级的名字
SELECT e.`last_name`,m.`last_name`
FROM `employees` e
INNER JOIN `employees` m
ON e.`manager_id` = m.`employee_id`
WHERE e.`last_name` LIKE '%k%';
二、外连接
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的,则显示匹配的值,如果从表中没有和它匹配的,则显示null 。
即:外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录。
2、左外连接,left (outer) join左边的是主表
3、右外连接:right (outer) join右边的是主表
4、左外和右外交换两个表的顺序,可以实现同样地效果
(1)左外连接
SELECT b.name,bo.*
FROM beauty b #主表
LEFT OUTER JOIN boys bo #从表
ON b.`boyfriend_id`=bo.`id`;
(2)右外连接
SELECT b.name,bo.*
FROM beauty b
RIGHT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NOT NULL;
左连接
【案例】组合两个表
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:
FirstName, LastName, City, State
select p.FirstName,p.LastName,a.City,a.State
from Person p left join Address a
on p.PersonId = a.PersonId;