SHOW DATABASES;
SELECT VERSION();
SHOW TABLES;
USE mysql;
SHOW TABLES;
STATUS;
SELECT * FROM db;
USE myemployees;
查看有多少个表
SHOW TABLES;
查看表的结构
SELECT * FROM departments;
SELECT * FROM jobs;
SELECT * FROM employees;
查询表中的单个字段
SELECT last_name FROM employees;
查询表中的多个字段
SELECT last_name,first_name, FROM employees;
SELECT * FROM employees;
SELECT last_name,phone_number FROM employees;
查询表中所有字段
SELECT
employee_id,
`first_name`,
`last_name`,
`phone_number`,
`last_name`,
`job_id`,
`salary`,
`commission_pct`,
`manager_id`,
`department_id`,
`hiredate`
FROM
employees ;
SELECT * FROM employees;
常量查询
SELECT 100;
SELECT "john";
查询表达式
SELECT 100*32.46545;
查询系统函数
查看MySQL系统版本
SELECT VERSION();
起别名
#1)使用as
SELECT 100.154*34.14 AS 结果;
SELECT first_name AS 姓,last_name AS 名 FROM employees;
#2)使用空格
SELECT email 邮箱 FROM employees;
#案例查询:salary,显示结果为out put
USE employees;
SELECT salary AS "output" FROM employees;
去重
#查询所有员工表中所有涉及到的部门编号
SELECT department_id FROM employees;
SELECT DISTINCT department_id FROM employees;
/*
语法:
select
查询列表
from
表名
where
筛选条件;
分类:
一、按条件表达式筛选
简单条件运算符:> < = != <> >= <=
二、按逻辑表达式筛选
逻辑运算符:
作用:用于连接条件表达式
&& || !
and or not
&&和and:两个条件都为true,结果为true,反之为false
||或or: 只要有一个条件为true,结果为true,反之为false
!或not: 如果连接的条件本身为false,结果为true,反之为false
三、模糊查询
like
between and
in
is null
*/
按条件查询
#案例1:查询工资>12000的员工信息 大于>
SELECT * FROM employees WHERE salary>10000;
#案例2:查询部门编号不等于90号的员工名和部门名 不等于 <> !=
SELECT last_name,department_id FROM employees WHERE department_id<>90;
SELECT last_name,department_id FROM employees WHERE department_id!=90;
按逻辑表达式查询
#查询工资在10000到20000之间的员工名、工资以及奖金 && and 条件都为真时才执行
SELECT last_name,salary,commission_pct FROM employees WHERE salary >=10000 AND salary <= 20000;
SELECT last_name,salary,commission_pct FROM employees WHERE salary >=10000 && salary <= 20000;
#案例3:查询部门编号不是在90到110之间,或者工资高于15000的员工信息
# 不在什么区间可以用 not( * and * ) 或 !( * and * )
SELECT
*
FROM
employees
WHERE
NOT(department_id>=90 AND department_id<=110) OR salary>15000;
SELECT
*
FROM
employees
WHERE
!(department_id>=90 AND department_id<=110) OR salary>15000;
模糊查询
/*
like
between and
in
is null|is not null
*/
like模糊查询
/*
特点:
一般和通配符使用:
% 任意多个字符,包含0个
_ 任意单个字符
*/
#案例1:查询员工名包含字符a的员工信息
SELECT last_name FROM employees WHERE last_name LIKE "%a%";
#案例2:查询员工名中包含第二个字符为u,第五个字符为o的员工名和工资
SELECT last_name,salary FROM employees WHERE last_name LIKE '_u_o%';
#案例3:查询员工名中第二个字符为_的员工名 可以用转义 \ ,也可以用ESCAPE $把 $当成转义符号.
SELECT last_name FROM employees WHERE last_name LIKE "_$_%" ESCAPE "$";
SELECT last_name FROM employees WHERE last_name LIKE "_\_%";
between and可以提高语句的简洁度
#案例1:查询员工编号在100到120之间的员工信息
#第一种方法
SELECT
*
FROM
employees
WHERE
employee_id>=100 AND employee_id<=120;
#第二种方法
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 100 AND 120;
#案例:查询员工的工种编号是IT——PROG、AD_VP、AD_PRES中的一个员工和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id = "IT_PROG" OR job_id="AD_VP" OR job_id="AD_PRES";
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN("IT_PROG","AD_VP","AD_PRES");
null
/*
=或<>不能用于判断null值
is null或is not null 可以判断null值
*/
#案例1:查询没有奖金的员工名和奖金率
SELECT
last_name,salary,commission_pct
FROM
employees
WHERE
commission_pct IS NULL;
#案例2:查询有奖金的员工名和奖金率,奖金
SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
#安全等于 <=>可以结合null使用
# 可以结合<=>使用,还可以结合变量使用
#案例3:查询没有奖金的员工名和奖金率
SELECT
last_name,salary,commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
#案例4:查询工资为12000的员工信息
SELECT * FROM employees WHERE salary <=>12000;
#IS NULL:仅仅可以判断NULL值,可读性较高,建议使用
#<=> :既可以判断NULL值,又可以判断普通的数值,可读性较低
#案例5:查询员工为176的员工的姓名和部门名和年薪
SELECT
last_name,employee_id,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
employees
WHERE
employee_id = 176;
#employee_id <=>176
SELECT job_id FROM employees;
SELECT DISTINCT job_id FROM employees;
SELECT * FROM employees;
SELECT * FROM employees WHERE commission_pct LIKE "%%" AND last_name LIKE "%%";
SELECT * FROM employees WHERE commission_pct LIKE "%%" OR last_name LIKE "%%";
#获取表的结构
DESC employees;
#查看详细表的结构
show create table employees;
/*
语法:
select 查询列表
from 表名
【where 筛选条件】
order by 排序的字段或表达式;
特点:
1、asc代表的是升序,可以省略
desc代表的是降序
2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段
3、order by子句在查询语句的最后面,除了limit子句
*/
按单个字段排序
#这里不写默认是asc代表升序,后面添加desc代表降序
SELECT * FROM employees ORDER BY salary;
SELECT * FROM employees ORDER BY salary DESC;
添加筛选条件再排序
#案例:查询部门编号>=90的员工信息,并按员工编号降序
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employee_id DESC;
按表达式排序
#案例:查询员工信息 按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
按别名排序
#案例:查询员工信息 按年薪排序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC;
按函数排序
#案例:查询员工名,并且按名字的长度排序
SELECT LENGTH(last_name) AS 名字长度,last_name
FROM employees
ORDER BY 名字长度 DESC;
SELECT LENGTH(last_name) AS 名字长度,last_name
FROM employees
ORDER BY LENGTH(last_name) ASC;
按多个字段排序
#案例: 查询员工信息,要求先按工资降序,再按employee_id升序
SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;
练习案例:
#案例1:查询员工的姓名和部门号和年薪,按年薪降序 按姓名升序
USE myemployees;
SHOW TABLES;
SELECT last_name AS 姓名,department_id AS 部门号,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC,姓名 ASC;
#案例2:选择工资不在8000到17000的员工的姓名和工资,按工资降序
SELECT last_name 姓名,salary 工资
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
#案例3:查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
SELECT *,LENGTH(email)
FROM employees
WHERE email LIKE "%e%"
ORDER BY LENGTH(email) DESC,department_id ASC;
#1.if函数:if-else的效果
SELECT IF(10<3,'小','大');
#当没有奖金的用户表现出(没奖金呵呵),有奖金时变现为(有奖金呵呵)
SELECT last_name,commission_pct 奖金,IF(commission_pct IS NULL,"没奖金 呵呵","有奖金,嘻嘻") 备注
FROM employees
ORDER BY commission_pct DESC;
#2.case函数的使用一:switch case的效果
/*
mysql中
#case第一种写法
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
*/
/*案例:查询员工的工资,要求
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门,显示的工资为原工资
*/
SELECT last_name,salary 原工资,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 新工资
FROM employees;
#case:第二种写法
/*
mysql中:
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end
*/
SELECT last_name 姓名,salary 工资,
CASE
WHEN salary>20000 THEN "A"
WHEN salary>15000 THEN "B"
WHEN salary>10000 THEN "C"
ELSE "D"
END 工资登记
FROM employees
ORDER BY salary DESC;
练习题
#案例1:显示系统时间(注:日期+时间)
SELECT NOW();
#案例2:查询员工号,姓名,工资,以及工资提高百分之20后的结果(new salary)
SELECT last_name 姓名,employee_id 员工号,salary 原工资,salary*1.2 AS 新工资
FROM employees;
#案例3:将员工的姓名按首字母排序,并写出姓名的长度(length)
SELECT last_name 姓名,LOWER(SUBSTR(last_name,1,1)) 姓名首字母,LENGTH(last_name) 姓名长度
FROM employees
ORDER BY 姓名首字母 DESC;
SELECT last_name FROM employees;
#案例4: 做一个查询,产生下面的结果
/*
Dream Salary
K_ing earns 24000.00 monthly but wants 72000.00
*/
SELECT CONCAT(last_name," earns ",salary," monthly but wants ",salary*3) AS "Dream Salary"
FROM employees
WHERE salary=24000;
#案例5:使用case-when,按照下面的条件
/*
job grade
AD_PRES A
ST_MAN B
IT_PROG C
other D
*/
SELECT job_id job,
CASE job_id
WHEN "AD_PRES" THEN "A"
WHEN "ST_MAN" THEN "B"
WHEN "IT_PROG" THEN "C"
ELSE "D"
END grade
FROM employees
ORDER BY grade ASC;
SELECT job_id FROM employees;
#1.简单使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) 总和,TRUNCATE(AVG(salary),2) 平均值取两位,MAX(salary) 最大值,MIN(salary) 最小值,COUNT(salary) 个数
FROM employees;
#2.参数支持哪些类型
SELECT SUM(last_name) FROM employees;
SELECT AVG(last_name) FROM employees;
SELECT MIN(last_name) FROM employees;
SELECT COUNT(last_name) FROM employees;
#3. 是否会忽略null不计
SELECT MAX(last_name) FROM employees; #null会忽略不计
SELECT MIN(commission_pct) FROM employees; #null会忽略不计
SELECT COUNT(commission_pct) FROM employees; #null会忽略不计
#4.和distinct去重搭配
SELECT SUM(DISTINCT(salary)),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT(salary)),COUNT(salary) FROM employees;
#5. count合并函数的详细介绍
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees; #所需时间慢些在inndob引擎慢,在myisam引擎下快
SELECT COUNT(1) FROM employees; #所需时间快些在inndob引擎快,在myisam引擎下慢
#扩展:查看当前什么存储引擎(利用系统变量和函数进行查询或模糊匹配)
SHOW TABLE STATUS FROM myemployees WHERE NAME="employees";
SHOW TABLE STATUS FROM mysql WHERE NAME="db";
SHOW VARIABLES LIKE "%storage_engine%";
#查看创建的表示什么引擎,
USE myemployees;
SHOW CREATE TABLE jobs; #这条命令不仅可以查看表示用什么引擎,还可以看到怎么创建表的
USE mysql;
SHOW CREATE TABLE db;
#desc table只能查看表的一部分结构
DESC jobs;
SELECT VERSION(); #查看版本号
SHOW ENGINES; #查看系统默认支持的存储引擎
USE mysql;
SHOW TABLES;
#6.和分组函数一同查询的字段有限制
USE myemployees;
SELECT AVG(salary),employee_id FROM employees; #执行不了
练习题
#案例1:查询员工表中的最大入职时间和最小入职时间的相差天数(datediff)
#用到datediff函数来计算两个日期相差的天数
#MAX(hiredate),MIN(hiredate)
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate))FROM employees;
SELECT DATE_FORMAT(NOW(),"%Y-%c-%d");
SELECT DATEDIFF(DATE_FORMAT(NOW(),"%Y-%c-%d"),"2019-5-26");
SELECT DATEDIFF(NOW(),"2019-5-26");
#案例2:查询部门编号为90的员工个数
SELECT COUNT(*) FROM employees WHERE department_id=90;