MySQL零基础到熟练这一篇就够了_mysql,sqlserver,excel工具熟练运用

持续更新中。。。。。

MySQL背景

前身属于瑞典一家公司， MySQL AB
08年被sun公司收购
09年sun公司被oracle公司收购

一、使用数据库好处

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

二、数据库相关概念

1、DB：数据库，保存一组有组织的数据的容器
2、DBMS：数据库管理系统，又称数据库软件（产品），用于管理DB中的数据
3、SQL：结构化查询语言，专门用来和数据库通信的语言
SQL优点：不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL，且简单易学

三、数据库存储数据的特点

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

四、MySQL产品的介绍和安装

1、MySQL服务的启动和停止

方式一：计算机——右键管理——服务
方式二：通过管理员身份运行（推荐）
net start 服务器名（即MySQL，启动服务）
net stop 服务器名（停止服务）

2、MySQL服务的登录和退出

方式一：通过MySQL自带的客户端
只限于root用户
方式二：通过windows自带的客户端（推荐）
登录：
mysql -h 主机名 -P 端口号 -u 用户名 -p 密码
退出：
exit or Ctrl + C

3、MySQL常见命令

1、查看当前所有数据库

show databases;
1

2、打开指定的库

use 库名;
1

3、查看当前数据库所有表

show tables;
1

4、查看其它库的所有表

show tables from 库名;
1

5、创建表

create table 表名(
	列名	列类型,
	列名 列类型,
	....
);
1
2
3
4
5

6、查看表结构

desc 表名;
1

7、查看服务器版本
方式一：登录到MySQL服务器端

MySQL version();
1

方式二：没有登录到MySQL服务端

mysql --version;
或
MySQL -V;
1
2
3

4、MySQL语法规范

1、字母不区分大小写，但建议关键字大写，表名列名小写；
2、每条命令最好用分号结尾(\g亦可)
3、每条命令根据需要，可以进行缩进或者换行
4、注释
单行注释： # 注释文字
单行注释：-- 注释文字（注：-- 后必须空一格）
多行注释：/* 注释文字 */

5、SQL的语言分类

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

6、SQL的常见命令

查看所有数据库
show databases;
打开指定的库
use 库名; 
显示库中所有列表
show tables;
显示指定库中所有列表
show tables from 库名;
创建表
create table 表名(
字段名		字段类型,
字段名		字段类型,
......
);
查看指定表结构
desc 表名;
显示表中所有数据
select * from 表名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

五、DQL语言的学习

进阶一：基础查询

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

select 字段名 from 表名;
1

2、查询表中多个字段

select 字段1, 字段2, ... from 表名;
1

3、查询表中所有字段

方式一：select * from tableName;
方式二：select fieldName1, fieldName2, ... from tableName;
1
2

4、查询常量值

select 'constant';
1

5、查询表达式

select 100%98;
1

6、查询函数

select version();
1

7、起别名
作用1：便于理解；
作用2：要查询字段有重名情况，可以使用别名区分开来；

#方式一：使用as
select 100%98 as delivery;
#方式二：使用空格
select last_name 姓, first_name 名 from tableName;
1
2
3
4

8：去重（查询用工表中所有部门编号）

select distinct department_id from tableName;
1

9：+号 的作用
仅有一个功能：运算符

#情形1：两操作数为数值型，做加法运算
select 100+90;
#情形2：只要有一方为字符型，则试图将字符型转换为数值型，
#成功：做加法运算
select '123' + 90;
#失败：字符型数值转换为0
select 'jhon' + 10;
#情形3：若一方为null，则结果为null
select null + 100;
1
2
3
4
5
6
7
8
9

进阶二：条件查询

语法：

select	查询列表	#step 3
from	表名	#step 1
where	筛选条件	#step 2
1
2
3

分类：
一：按条件表达式筛选
简单条件运算符：> < = != >= <= <>
二：按逻辑表达式筛选
逻辑运算符：用于连接条件表达式
&& || ！
and or not
三：模糊查询
like：一般搭配通配符使用，可以判断字符型数值或数值型
between… and…
in
is null
一、按条件表达式筛选

#eg:查询员工工资大于12000的员工信息
select * from tableName where salary>12000;
1
2

二、按逻辑表达式筛选

#eg:查询工资z在10000到20000之间的员工名、工资以及奖金
select last_name, salary, commission 
from tableName 
where salary>=10000 and salary<=20000;
1
2
3
4

三、模糊查询
1:like
特点：一般和通配符搭配使用
通配符:
%：任意多个字符，包含0个字符
_：任意单个字符

#案例1：查询员工名中包含字符a的员工信息
select * from tableName where last_name like '%a%';
#案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
select * from tableName where last_name like '__e_a';
#案例3：查询员工名中第二个字符为_的员工名
select * from tableNmme where last_name like '_$_%' escape $;
1
2
3
4
5
6

2.between…and…
特性：
1、可提高语句简洁度
2、包含临界值
3、两个临界值不要调换顺序

#案例1：查询员工编号在100到120之间的员工信息
select * from tableName where employee_id between 100 and 120;
1
2

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

#查询员工的工种编号是 IT_PROG、IT_PROG、AD_PRES中的一个员工名和工种编号
SELECT
	last_name, job_id
FROM
	employees
WHERE
	job_id in('IT_PROG', 'IT_PROG', 'AD_PRES');
1
2
3
4
5
6
7

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

#案例1：查询没有奖金的员工名和奖金率
select last_name, commission
from tableName
where commission is null;
#案例1：查询有奖金的员工名和奖金率
select last_name, commission
from tableName
where commission is not null;
#补充：安全等于
#<=> 即可判断数值，又可判断null
select last_name, commission
from tableName
where commission <=> null;
1
2
3
4
5
6
7
8
9
10
11
12
13

进阶三：排序查询

语法：
select 查询列表
from 表名
【where 筛选条件】
order by 排序的字段或表达式；
特点：
1、asc代表的是升序，可省略
desc代表降序
2、order by子句支持 单个字段、别名、表达式、函数、多个字段
3、order by子句在查询结果的最后面，除了limit子句

#1、按单个字段降序排序
select * from tableName where salary order by desc;
#2、添加筛选条件再排序
#案例：查询部门编号>=90的员工信息，并按员工编号降序
select * 
from tableName 
where department_id >= 90
order by employee_id desc;
#3、按别名排序
#案例：查询员工信息 按年薪升序
select *, salary*12(1+ifnull(commission, 0)) as 年薪
from tableName
order by 年薪 asc;
#4、按函数排序
#案例：查询员工名，并且按名字的长度降序
select last_name, length(last_name)
from tableName
order by length(last_name) desc;
#6、按多个字段排序
#案例：查询员工信息，要求先按工资降序，再按employee_id升序
select *
from tabelName
order by salary desc, employee_id asc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

进阶四：常见函数

概念：类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏实现细节 2、提高代码重用性
调用：select 函数名（实参列表）【from 表】;
特点：
1、叫什么（函数名）
2、干什么（函数功能）
分类：
1、单行函数
如：concat、ifnull、length
2、分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数
常见函数：

一、单行函数

1、字符函数：
length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
concat substr	instr	trim	upper	lower	lpad	rpad	replace
2、数学函数：
round	ceil	floor	truncate	mod
3、日期函数：
now	curdate	curtime	year	month	monthname	day	
hour	minute	second
str_to_date	date_format
4、其他函数：
version	database	user
5、控制函数
if	case
1
2
3
4
5
6
7
8
9
10
11
12
13

#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
select 
	concat(upper(substr(last_name,1)),'_',lower(substr(last_name),2)) as out_put
from tableName;

#查询入职日期为1992--4-3的员工信息
select * 
from tableName
where hiredate = str_to_date('4-3 1995', '%c-%d %Y');

#date_format 将日期转换成字符
select date_format(now(),'%y年%m月%d日') as out_put;
1
2
3
4
5
6
7
8
9
10
11
12

流程控制函数

#1、if函数 if else 的效果
select if(10<5, '大', '小');
1
2

#2、case函数的使用
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
else 要显示的值n或语句n;
end

/*案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
其他部门，显示的工资为原工资
*/
select salary 原始工资, department_id,
case department
when 30 then salary*1.1
when 40 then salary*1.2
else salary
end as 新工资
from tableName;
1
2
3
4
5
6
7
8
9
10
11
12

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

#案例：查询员工的工资的情况
/*如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示C级别
否则，显示D级别
*/
select last_name, salary,
case
when salary>18000 then 'A'
when salary>12000 then 'B'
when salary>10000 then 'C'
else 'D'
end as 工资级别
from tableName
order by salary desc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

二、分组函数

功能：用作统计使用，又称聚合函数或统计函数或组函数
分类：
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
特点：
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算
4、count函数的单独介绍
一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段

#简单 的使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

#和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
1
2
3
4
5
6
7
8
9
10

注： 和分组函数一同查询的字段有限制

进阶五：分组查询

语法：
select 查询列表
from tableName
[where 筛选条件]
group by 分组的字段
[order by 排序的字段]
注：
查询列表必须特殊，要求是分组函数和group by后出现的字段
特点：
1、分组查询中的筛选条件分两类
数据源 位置 关键字
分组前筛选 原始表 group by 子句前面 where
分组后筛选 分组后的结果集 group by 子句后面 having
(1).分组函数做条件肯定是放在having子句中
(2).能用分组前筛选的就尽量使用分组前筛选
2、group by子句支持单个字段分组，多个字段分组(多个字段之间用逗号隔开没有顺序要求)，表达式或函数（用的较少）
3、也可以添加排序，排序放在整个的分组查询之后的

简单查询

#查询每个部门的员工个数
select count(1),department_id
from tableName
group by department_id;
1
2
3
4

实现分组前的筛选

#案例1：查询邮箱中包含a字符的 每个部门的最高工资
select email, max(salary), department_id
from tableName
where email like '%a%'
group by department_id;
1
2
3
4
5

分组后筛选

#案例：查询哪个部门的员工个数>5
select count(*), department_id
from tableName
group by department_id
having count(*)>5;
1
2
3
4
5

添加排序

#案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
select job_id, max(salary)
from tableName
where commission_pct is not null
group by job_id
desc max(salary);
1
2
3
4
5
6

按多个字段分组

#案例：查询每个工种每个部门的最低工资,并按最低工资降序
SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;
1
2
3
4
5

进阶六：连接查询

含义：又称多表查询
笛卡尔乘积现象：表1有m行，表2有n 行， 结果=m*n行
原因：没有连接条件
避免：添加有效的连接条件
分类：
按年代分类：
sql92标准：仅支持内连接
sql99标准：支持所有内联+外联（左外+右外）+交叉连接
按功能分类：
1、等值连接
2、非等值连接
3、自连接查询

一：sql92标准

1、等值连接
① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

二：SQL99标准

语法：

/*
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名 on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

分类：
内连接（★）：inner
外连接
	左外(★):left 【outer】
	右外(★)：right 【outer】
	全外：full【outer】
交叉连接：cross 
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

内连接：等值连接，非等值连接，自连接

USE girls;
SELECT * FROM beauty;
SELECT * FROM boys;
SELECT NAME, boyname FROM beauty, boys
WHERE beauty.boyfriend_id=boys.id;
#案例二：查询员工名和对应的部门名
SELECT last_name, department_name
FROM departments,employees
WHERE employees.department_id=departments.department_id;
#2 为表起别名
#查询员工名、工种号、工种名
SELECT last_name, e.job_id, job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id;
#3、两个表的顺序是否可以调换

#4、加筛选
SELECT last_name, department_name, commission_pct
FROM employees e, departments d
WHERE e.department_id=d.department_id
AND e.commission_pct IS NOT NULL;
#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

#7、可以实现三表连接
#查询员工名、部门名和所在城市
SELECT last_name, department_name, city
FROM locations l, employees e, departments d
WHERE d.department_id=e.department_id
AND l.location_id=d.location_id
AND city LIKE 's%'
ORDER BY department_name DESC;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  INT,
 highest_sal INT);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);
SELECT * FROM job_grades;
#2、非等值连接
#案例1：查询出员工的工资和工资级别
SELECT salary s, grade_level
FROM job_grades j, employees
WHERE  salary BETWEEN j.lowest_sal 
AND j.highest_sal;
#3、自连接
#案例：查询员工名称和上级领导名称
SELECT e.employee_id, e.last_name, m.employee_id, m.last_name
FROM employees e, employees m
WHERE e.employee_id = m.manager_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

进阶七：子查询

/*
含义：
出现在其它语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询

分类：
按子查询出现的位置分类：
	select 后面
		仅支持标量子查询
	from 后面
		支持表子查询
	where 或 having 后面 ☆
		标量子查询 （单行） √
		列子查询   （多行） √
		行子查询（用的较少）
	exists后面（相关子查询）
		表子查询
按结果集的行列数不同：
	标量子查询（结果集自由一行一列）
	列子查询（结果只有一列多行）
	行子查询（结果集可以有'一/多'行多列）
	表子查询（结果集一般为多行多列）
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

#一、where 或 having 后面

#1、标量子查询（单行子查询）

#案例1：谁的工资比 Abel 高?
#1.查询Abel 的工资

SELECT *
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name='abel'
);

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资
#1.查询job_id
SELECT job_id
FROM employees
WHERE employee_id='141';
#2.查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id='143';

SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id='141'
) AND salary > (
	SELECT salary
	FROM employees
	WHERE employee_id='143'
);

#案例3：返回公司工资最少的员工的last_name,job_id和salary
;

SELECT last_name, job_id, salary
FROM employees
HAVING salary = (
	SELECT MIN(salary)
	FROM employees
);

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#查询50号部门最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id='50';

SELECT MIN(salary), department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id='50'
);


#列子查询（多行子查询）
#案例1：返回location_id是1400或1700的部门中的所有员工姓名

SELECT DISTINCT d.department_id
FROM departments d
WHERE d.location_id IN(1400, 1700);

SELECT e.last_name
FROM employees e
WHERE e.department_id IN(
	SELECT d.department_id
	FROM departments d
	WHERE d.location_id IN(1400, 1700)
);

#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的
#	员工的员工号、姓名、job_id 以及salary
SELECT DISTINCT salary
FROM employees e
WHERE e.job_id='IT_PROG';

SELECT e.employee_id, e.last_name, e.job_id, e.salary
FROM employees e
WHERE e.salary < ANY(
	SELECT DISTINCT salary
	FROM employees e
	WHERE e.job_id='IT_PROG'
) AND e.job_id <> 'IT_PROG';

#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的
#	员工   的员工号、姓名、job_id 以及salary
SELECT	e.salary
FROM employees e
WHERE e.job_id='IT_PROG';

SELECT e.employee_id, e.last_name, e.job_id, e.salary
FROM employees e
WHERE e.salary < ALL(
	SELECT e.salary
	FROM employees e
	WHERE e.job_id='IT_PROG'
);

#案例：查询员工编号最小并且工资最高的员工信息
SELECT MAX(salary)
FROM employees;

SELECT MIN(employee_id)
FROM employees;

SELECT e.*
FROM employees e
WHERE e.employee_id = (
	SELECT MIN(employee_id)
	FROM employees
)AND e.salary = (
	SELECT MAX(salary)
	FROM employees
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120

#二、select后面

#案例：查询每个部门的员工个数
SELECT d.*, (
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.department_id
) 个数
FROM departments d;

#案例2：查询员工号=102的部门名

SELECT d.department_name
FROM departments d
LEFT OUTER JOIN employees e
ON e.department_id = d.department_id
WHERE e.employee_id = '104';

SELECT *
FROM  boys bo
WHERE bo.id NOT IN(
	SELECT boyfriend_id
	FROM beauty
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

进阶八：分页查询

应用场景：要显示的数据一页显示不全，需要分页提交SQL请求
/*
select 查询列表
from tableName
limit offset, size;
offset:要显示条目的起始索引（起始索引从0开始）
size：要显示的条目数个数
*/

进阶九：联合查询

六、DML语言的学习

/*
数据操作语言：
插入：insert
修改：update
删除：delete
*/

#一、插入语句
#方式一：经典的插入
/*
语法：
insert into 表名(列名,…) values(值1,…);
*/