mysql基础命令学习(尚硅谷李玉婷老师教学)

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

一、基础查询方法

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;

1. 查询表中的单个字段

SELECT last_name FROM employees;

2. 查询表中的多个字段

SELECT last_name,first_name, FROM employees;
SELECT * FROM employees;
SELECT last_name,phone_number FROM employees;

3. 查询表中所有字段

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;

4. 常量查询

SELECT 100;
SELECT "john";

5. 查询表达式

SELECT 100*32.46545;

6. 查询系统函数

查看MySQL系统版本
SELECT VERSION();

7. 起别名

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

8. 去重

#查询所有员工表中所有涉及到的部门编号
SELECT department_id FROM employees;
SELECT DISTINCT department_id FROM employees;

9. +号的作用

#仅仅是一个运算符
SELECT 100+80;
SELECT '123'+2;
SELECT 'yang'+299;
SELECT NULL+2134;
SELECT TRUE+234;

案例

查询员工和姓名连接成一个字段，并显示为姓名
SELECT CONCAT('a','b','c') AS 结果;

SELECT
	CONCAT(first_name,'-',last_name) AS 姓名
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
*/

1. 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 "_\_%";

2. 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;

3. in

/*
含义：判断某字段的值是否属于in列表中的某一项
特点：
	①使用in提高语句简洁度
	②in列表的值类型必须一致或兼容
	③in列表中不支持通配符
	

*/

#案例：查询员工的工种编号是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");

4. 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子句

*/

1. 按单个字段排序

#这里不写默认是asc代表升序,后面添加desc代表降序
SELECT * FROM employees ORDER BY salary;
SELECT * FROM employees ORDER BY salary DESC;

2. 添加筛选条件再排序

#案例：查询部门编号>=90的员工信息，并按员工编号降序
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employee_id DESC;

3. 按表达式排序

#案例：查询员工信息	按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

4. 按别名排序

#案例：查询员工信息	按年薪排序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC;

5. 按函数排序

#案例：查询员工名，并且按名字的长度排序
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;

6. 按多个字段排序

#案例： 查询员工信息，要求先按工资降序，再按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;

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

四、常见函数

/*
概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节  2、提高代码的重用性
调用：select 函数名(实参列表) 【from 表】;
特点：
	①叫什么（函数名）
	②干什么（函数功能）

分类：
	1、单行函数
	如 concat、length、ifnull等
	2、分组函数
	
	功能：做统计使用，又称为统计函数、聚合函数、组函数

常见函数：
	一、单行函数
	字符函数：
	length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
	concat
	substr
	instr
	trim
	upper
	lower
	lpad
	rpad
	replace
	
	数学函数：
	round
	ceil
	floor
	truncate
	mod
	
	日期函数：
	now
	curdate
	curtime
	year
	month
	monthname
	day
	hour
	minute
	second
	str_to_date
	date_format
	load_file
	其他函数：
	version
	database
	user
	控制函数
	if
	case
*/

单行函数

• 字符函数

#1. length 获取参数值的字节个数
SELECT LENGTH("TOM");
SELECT LENGTH("猪文文");
SHOW VARIABLES LIKE "%char%"; #查看系统的字符集

#2.concat 拼接字符串
SELECT CONCAT(last_name,"_",first_name) 姓名 FROM employees;

#3.upper(将字母都变大)	lower(将字母都变小)
SELECT UPPER("joung");
SELECT LOWER("HOjg");
#示例：将姓变大，名变小，然后拼接
SELECT CONCAT(UPPER(last_name),"-",LOWER(first_name)) FROM employees;

#4. substr	substring
#注意：索引从1开始
#截取从指定索引处后面所有字符 从字符7到结束
SELECT SUBSTR("猪文文爱上一头大蠢猪杨",7) 结果;

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR("猪文文爱上一头大蠢猪杨",1,3) 结果;

#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(last_name,"_",first_name) 结果 FROM employees;
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),"_",LOWER(SUBSTR(last_name,2))) 结果 FROM employees;

#5. instr 返回字符串第一次出现的索引，如果找不到返回0,默认索引是从1开始的
SELECT INSTR("猪文文爱上一头大蠢猪文文","你") 结果;

#6. trim 默认只能去掉空格，不能去掉制表符/t,也可以指定去掉的字符(去掉前后的空格或指定其他的字符)
SELECT LENGTH(TRIM('    张翠   山    ')) 结果;
SELECT LENGTH('    张翠   山    ') 结果;
SELECT TRIM('a' FROM 'aaaaa张aaaaa翠山aaaaaaaaaaaaaaaaaaa') 结果;

#7. lpad用于指定字符串实现左填充指定长度
SELECT 
  LPAD("殿速速", 6, "$") 结果;

#8. rpad用于指定字符实现右填充长度
SELECT RPAD("你是谁",6,"$") jieuog;

#9. replace全部替换
SELECT REPLACE("猪宝宝大猪宝宝文文文文","宝","$") jieguo;

#10读取文件且返回文件内容为字符串
update t set blob_col=load_file('/tmp/picture') where id=1;

• 数学函数

#round 四舍五入
SELECT ROUND(-132.633511) result;
SELECT ROUND(-132.233511) result;
SELECT ROUND(431.5345) result;
SELECT ROUND(431.2345) result;

#ceil 向上取整,返回>=该参数的最大整数(进1法),对正数有用
SELECT CEIL(-12.4564);
SELECT CEIL(12.1545);
SELECT CEIL(12.6654);

#floor 向下取整，返回<=该参数的最大整数(进1法),只能对负数有用,对正数无用
SELECT FLOOR(-9.4564) result;
SELECT FLOOR(-121.1) result;
SELECT FLOOR(16546.5111) result;
SELECT FLOOR(2.4) resultl;

#truncate 截断，小数位截断,不会四舍五入,也不会向下取整
SELECT TRUNCATE(1.45646,1);
SELECT TRUNCATE(-4564.4654,2);
SELECT TRUNCATE(4654,2);

#mod取余
/*
mod(a,b):	等价于a-a/b*b
*/
SELECT MOD(10,-3); #10-(10)/(-3)*(-3) 
SELECT MOD(-10,-3);
SELECT MOD(-10,-3);
SELECT MOD(10,3);

#产生0-1的随机数
SELECT RAND();

#需求产生255-4000的随机数
select ROUND(RAND()*3745+255);

#利用md5产生32位随机数
SELECT MD5(RAND()*1000);
SELECT LENGTH(MD5(RAND()*1000));

#利用rand()和concat()函数生成11位随机数
#生成11位随机手机号函数：CONCAT('1',CEILING(RAND()*9000000000+1000000000));
select concat('1',ceiling(rand()*90000000000+1000000000));

• 日期函数

#now 返回当前系统日期+时间
SELECT NOW();

#curdate 返回当前系统的日期(不包含时间)
SELECT CURDATE();

#也可以获取指定的部分,年,月,日,小时,分钟,秒 (YEAR,MONTH,DAY,minute,second)
SELECT YEAR(NOW());
SELECT YEAR("1893-10-12");
SELECT YEAR(hiredate) FROM employees;

SELECT  MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

#str_to_date 将指定字符通过指定格式转换成日期: "%Y-%c-%d"
SELECT STR_TO_DATE("1952_3_5","%Y_%m_%d"); #按照它指定字符格式去取日期
SELECT STR_TO_DATE("1952_3_5","%Y_%c_%d"); #按照它指定字符格式去取日期

#查询日志日期为1992-4-3的员工信息
SELECT hiredate,last_name FROM employees; #1992-04-03 00:00:00  K_ing 
SELECT hiredate,last_name FROM employees WHERE hiredate="1992-4-3";
SELECT hiredate,last_name FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');

#date_format 将日期转成字符
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日");
SELECT DATE_FORMAT(NOW(),"%y年%m月%d日");
SELECT DATE_FORMAT(NOW(),"%Y年%c月%d日");
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日%W%H时%i分%s秒");
SELECT DATE_FORMAT(NOW(),"%Y年%c月%d日-星期%w-%H时%i分%s秒");

#查询有奖金的员工名和入职日期
SELECT last_name,DATE_FORMAT(hiredate,"%Y年%m月%d日") 日期,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

• 其他系统函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

• 流程控制函数

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

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

五、分组函数

/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数

特点：
1、sum、avg一般用于处理数值型
   max、min、count可以处理任何类型
2、以上分组函数都忽略null值

3、可以和distinct搭配实现去重的运算

4、count函数的单独介绍
一般使用count(*)用作统计行数

5、和分组函数一同查询的字段要求是group by后的字段

*/

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

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

六、分组查询

/*
语法：

select 查询列表
from 表