MySQL_基础

一、 数据库概述

1.1 数据库的好处

1.持久化数据到本地
2.可以实现结构化查询，方便管理

1.2 数据库相关概念

1、DB：数据库（DataBase），保存一组有组织的数据的容器
2、DBMS：数据库管理系统(DataBase Management System)，又称为数据库软件（产品），用于管理DB中的数据，像MySql、Orcal
3、SQL:结构化查询语言(Structure Query Language)，用于和DBMS通信的语言

1.3 数据库存储数据的特点

1、将数据放到表中，表再放到库中
2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
5、表中的数据是按行存储的，每一行类似于java中的“对象”。

1.4 MySQL的常见命令

1.查看当前所有的数据库 	show databases;
2.打开指定的库  			use 库名;
3.查看当前库的所有表		show tables;
4.查看其它库的所有表		show tables from 库名;  
5.查看当前所在库			select database();
6.创建表
create table 表名(
	列名 列类型,
	列名 列类型，
	。。。
);
6.查看表结构·				desc 表名;
7.查看服务器的版本		方式一：登录到mysql服务端		select version();
					方式二：没有登录到mysql服务端	mysql --version	或	mysql --V

---

【注解】 一个数据库 里面可以用很多的库（A,B,C,D,…）,然后呢，每一个库里面又可以有很多张表

1.5 MySQL的语法规范

1.不区分大小写,但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进 或换行
4.注释
	单行注释：#注释文字
	单行注释：-- 注释文字
	多行注释：/* 注释文字  */

二、SQL学习

2.1 SQL的语言分类

DQL（Data Query Language）：数据查询语言			select 
DML(Data Manipulate Language):数据操作语言		insert 、update、delete
DDL（Data Define Languge）：数据定义语言			create、drop、alter
TCL（Transaction Control Language）：事务控制语言	commit、rollback

2.2 DQL语言的学习

进阶1：基础查询

语法：
SELECT 要查询的东西	【FROM 表名】;

类似于Java中 :System.out.println(要打印的东西);
特点：
	①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
	② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

#1查询表中单个字段

SELECT last_name;	//last_name 字段

这样回报错

SELECT last_name FROM employees;

#2查询表中多个字段

SELECT last_name,first_name,email FROM employees;

#3查询表中所有字段

SELECT * FROM employees;

我们在做查询的时候，需要打开指定的库名，use 库名；

#4 查询常量值

select 100;

#5 查询表达式

select 100%98;

#6查询函数

SELECT  VERSION();

#7为字段起别名
方式一：使用as

SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;

方式二：使用空格

SELECT 100%98  结果;
SELECT last_name 姓,first_name  名 FROM employees;

#8 去重
#案例:查询员工表中涉及到的所有的部门编号

#案例:查询员工表中涉及到的所有的部门编号
SELECT department_id FROM departments;

但是这样查出来会有很多重复值，只需要再字段名前加上distinct

SELECT distinct department_id FROM departments;

#9 +号的作用

/*

java中的+号：
①运算符，两个操作数都为数值型
②连接符，只要有一个操作数为字符串

mysql中的+号：
仅仅只有一个功能：运算符

select 100+90; 两个操作数都为数值型，则做加法运算
select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型
			如果转换成功，则继续做加法运算
select 'john'+90;	如果转换失败，则将字符型数值转换成0

select null+10; 只要其中一方为null，则结果肯定为null

*/

SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;

【习题】

#1.	下面的语句是否可以执行成功  
SELECT last_name , job_id , salary AS sal FROM employees; 

#2.下面的语句是否可以执行成功  
SELECT  *  FROM employees; 


#3.找出下面语句中的错误 (已改正确)
SELECT employee_id , last_name,salary * 12 AS "ANNUAL  SALARY" FROM employees;



#4.显示表departments的结构，并查询其中的全部数据

DESC departments;

SELECT * FROM `departments`;

#5.显示出表employees中的全部job_id（不能重复）
SELECT DISTINCT job_id FROM employees;

#6.显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT

SELECT 
	IFNULL(commission_pct,0) AS 奖金率,
	commission_pct
FROM 
	employees;
	
#---------------------------------------------------------------------------------

SELECT
	CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct,0)) AS out_put
FROM
	employees;
	

IFNULL(expr1,expr2)

---

进阶2：条件查询

语法：
	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>12000;

#案例2：查询部门编号不等于90号的员工名和部门编号

SELECT 
		department_id,first_name 
FROM 
		employees 
WHERE 
		department_id <> 90;

#二、按逻辑表达式筛选

#案例1：查询工资z在10000到20000之间的员工名、工资以及奖金

SELECT
	last_name,
	salary,
	commission_pct
FROM
	employees;
WHERE
	salary > 10000 AND 	salary < 20000;

#案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息

SELECT
	*
FROM
	employees
WHERE
	NOT(department_id>=90 AND  department_id<=110) OR salary>15000;

#三、模糊查询

like

	
	
between and
in
is null|is not null

特点：
①一般和通配符搭配使用
	通配符：
	% 任意多个字符,包含0个字符
	_ 任意单个字符
	*、

#案例1：查询员工名中包含字符a的员工信息

SELECT
	*
FROM
	employees
WHERE
	last_name LIKE '%a%';

#案例2：查询员工名中第三个字符为n，第五个字符为l的员工名和工资

SELECT
	*
FROM
	employees
WHERE
	last_name LIKE '__n_l%';

#案例3：查询员工名中第二个字符为_的员工名

SELECT
	last_name
FROM
	employees
WHERE
	last_name LIKE '_\_%';

\ 这个是反斜杠，/ 这个是斜杠，而我们转义是用反斜杠\，不要搞错了

SELECT
	last_name
FROM
	employees
WHERE
	last_name LIKE '_$_%' ESCAPE '$';

#2.between and

①使用between and 可以提高语句的简洁度
②包含临界值
③两个临界值不要调换顺序

#案例1：查询员工编号在100到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 in ('IT_PROG','AD_VP','AD_PRES');

#4、is null

=或<>不能用于判断null值
is null或is not null 可以判断null值

#案例1：查询没有奖金的员工名和奖金率

SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NULL;

#安全等于 <=>

#案例1：查询没有奖金的员工名和奖金率

SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct <=>NULL;

#案例2：查询工资为12000的员工信息

SELECT
	last_name,
	salary
FROM
	employees

WHERE 
	salary <=> 12000;

#is null pk <=>

IS NULL:	仅仅可以判断NULL值，可读性较高，建议使用
<=>    :	既可以判断NULL值，又可以判断普通的数值，可读性较低

## 【测试】

1.查询工资大于12000的员工姓名和工资。

SELECT
		CONCAT(last_name,first_name) AS 姓名,
		salary
FROM
		employees
WHERE
		salary > 12000;

2.查询员工号为176的员工的姓名和部门号和年薪

SELECT
	job_id,
	last_name,
	department_id,
	salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
	employees;

3.选择工资不在5000到12000的员工的姓名和工资。

SELECT
		CONCAT(last_name,first_name) AS 姓名,
		salary
FROM
		employees
WHERE
		salary <5000 OR salary>12000;

SELECT
		CONCAT(last_name,first_name) AS 姓名,
		salary
FROM
		employees
WHERE
		NOT salary BETWEEN 5000 and 12000;

4.选择在20或50号部门工作的员工姓名和部门号。

SELECT
		CONCAT(last_name,first_name) AS 姓名,
		department_id
FROM
		employees
WHERE
		department_id in(20,50);

5.选择公司中没有管理者的员工姓名及job id.

SELECT
		CONCAT(last_name,first_name) AS 姓名,
		department_id
FROM
		employees
WHERE
		manager_id IS NULL;

6.选择公司中有奖金的员工姓名，工资和奖金级别。

SELECT
		CONCAT(last_name,first_name) AS 姓名,
		salary,
	commission_pct		
FROM
		employees
WHERE
		commission_pct IS NULL;

7.选择员工姓名的第三个字母是a的员工姓名。

SELECT
		CONCAT(last_name,first_name) AS 姓名
FROM
		employees
WHERE
		last_name LIKE "__a%";

8.选择姓名中有字母a和e的员工姓名。

SELECT `last_name`
FROM `employees`
WHERE `last_name` LIKE "%a%" OR `last_name` LIKE "%e%";

9.显示出表employees表中FIRST NAME以el结尾的员工信息

SELECT *
FROM `employees`
WHERE `first_name` LIKE "%el";

10.显示出表employees部门编号在80-100之间的姓名

SELECT `last_name`
FROM `employees`
WHERE `department_id` BETWEEN 80 AND 100;

11.显示出表employees的manager_id是100，101，110的员工姓名、职位

SELECT `last_name`,`job_id`
FROM `employees`
WHERE `manager_id` IN(100,101,110);

12,查询没有奖金，且工资小于18000的salary，last_name

SELECT `salary`,`last_name`
FROM `employees`
WHERE `commission_pct` IS NULL AND `salary`<18000;

13,查询employees表中，job_id不为‘IT’或者工资为12000的员工信息

SELECT *
FROM `employees`
WHERE `job_id`<>"IT" OR `salary`=12000;

14,查看部门departments表的结构

DESC departments;

15,查询部门departments表中涉及到了哪些位置编号

SELECT DISTINCT location_id
FROM departments;

16、经典面试题：
试问：

SELECT *
FROM employees;

和

SELECT *
FROM employees
WHERE commission_pct LIKE “%%” AND last_name LIKE “%%”;

结果是否一样？并说明原因
#不一样 ，如果没有null，则是一样的,and改为or则一样，取得并集

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

SELECT *
FROM employees
WHERE commission_pct LIKE '%%’ or last_name like “%%”;

---

一、 查询没有奖金，且工资小于18000的salary, last_ _name

SELECT
	salary,
	last_name
FROM
	employees
WHERE
	commission_pct IS NULL  AND salary<18000

二、查询employees表中，job_ id不为’IT’ 或者工资为12000的员工信息

SELECT
	*
FROM
	employees
WHERE
	job_id != 'IT' OR salary = 12000;

三、查看部]depar tment s表的结构，查询效果如下

DESC departments;

四、、查询部门depar tments表中涉及到了哪些位置编号

SELECT DISTINCT location_id FROM departments

P38

---

进阶3：排序查询

语法：
select 查询列表
from 表名
【where  筛选条件】
order by 排序的字段或表达式;


特点：
1、	asc代表的是升序，可以省略
	desc代表的是降序

2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段

3、order by子句在查询语句的最后面，除了limit子句

#1、按单个字段排序

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))
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

#4、按别名排序
#案例：查询员工信息 按年薪升序

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 ASC;

#5、按函数排序
#案例：查询员工名，并且按名字的长度降序

SELECT LENGTH(last_name),last_name 
FROM employees
ORDER BY LENGTH(last_name) DESC;

#6、按多个字段排序

#案例：查询员工信息，要求先按工资降序，再按employee_id升序

SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;

**
**

#1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序

SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC,last_name 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;

进阶4：常见函数

概念：类似于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
	其他函数：
	version
	database
	user
	控制函数
	if
	case

#一、字符函数

#1.length 获取参数值的字节个数

SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');

SHOW VARIABLES LIKE '%char%'

#2.concat 拼接字符串

SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

#3.upper、lower

SELECT UPPER('john');
SELECT LOWER('joHn');
#示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name))  姓名 FROM employees;

#4.substr、substring
注意：索引从1开始

#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7)  out_put;

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;


#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))  out_put
FROM employees;

#5.instr 返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;

#6.trim

SELECT LENGTH(TRIM('    张翠山    ')) AS out_put;

SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')  AS out_put;

#7.lpad 用指定的字符实现左填充指定长度

SELECT LPAD('殷素素',2,'*') AS out_put;

#8.rpad 用指定的字符实现右填充指定长度

SELECT RPAD('殷素素',12,'ab') AS out_put;

#9.replace 替换

SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

#二、数学函数

#round 四舍五入

SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);

#ceil 向上取整,返回>=该参数的最小整数

SELECT CEIL(-1.02);

#floor 向下取整，返回<=该参数的最大整数

SELECT FLOOR(-9.99);

#truncate 截断

SELECT TRUNCATE(1.69999,1);

#mod取余

/*
mod(a,b) ：  a-a/b*b

mod(-10,-3):-10- (-10)/(-3)*（-3）=-1
*/
SELECT MOD(10,-3);
SELECT 10%3;

#三、日期函数

#now 返回当前系统日期+时间

SELECT NOW();

#curdate 返回当前系统日期，不包含时间

SELECT CURDATE();

#curtime 返回当前时间，不包含日期

SELECT CURTIME();

#可以获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;

SELECT  YEAR(hiredate) 年 FROM employees;

SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;

#str_to_date 将字符通过指定的格式转换成日期

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;

#查询入职日期为1992–4-3的员工信息

SELECT * FROM employees WHERE hiredate = '1992-4-3';

SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

#date_format 将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;

#查询有奖金的员工名和入职日期(xx月/xx日 xx年)

SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

#四、其他函数

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

#五、流程控制函数

#1.if函数： if else 的效果

SELECT IF(10<5,'大','小');

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
FROM employees;

#2.case函数的使用一： switch case 的效果

/*
java中
switch(变量或表达式){
	case 常量1：语句1;break;
	...
	default:语句n;break;


}

mysql中

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 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 AS 新工资
FROM employees;

#3.case 函数的使用二：类似于 多重if

/*
java中：
if(条件1){
	语句1；
}else if(条件2){
	语句2；
}
...
else{
	语句n;
}

mysql中：

case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end
*/

#案例：查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示C级别
否则，显示D级别

SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;