mySql笔记之基础篇（参看尚硅谷视频）

一、cmd操作mysql

1、使用命令进入MySQL

//cmd中输入：
mysql -u(用户名) -P(端口号)  -h(ip地址) -p(密码)
eg：mysql -uroot -P3306 -hlocalhost -p
#若在本机使用默认的，可省略 端口号、IP地址

2、使用命令启动关闭服务

#启动 mysql 服务命令：
net start mysql服务名
eg：net start mysql80

#停止 mysql 服务命令
net stop mysql服务名
eg：net stop mysql80

#注意：若显示“拒绝访问”，请使用“管理员window终端” 或 以 管理员身份 运行 cmd 。

3、使用命令退出

#方式一：
exit
#方式二：
quit
#方式三：
按下：ctrl + z

4、使用命令查看数据库版本

#方式一（在cmd未登进MySQL时）：
mysql -V(注意是大写的)

#方式二（在cmd未登进mysql时）：
mysql --version(注意有两个连接符)

#方式三（登进MySQL后）：
select version();（注意带上 分号）。

5、使用命令显示MySQL中的数据库

show databases; (注意带上 分号)

6、查看MySQL使用的编码格式

注意：5.x版本的mysql数据库需要手动配置编码格式！！

show variables like 'character_%';
show variables like 'collation_%';

7、使用命令操作mysql

• 创建数据库：

create database liu;

• 查看数据库的创建信息

show create database liu;

• 创建表：

#先确定在哪个数据库中创建表
use liu;
create table student(id int,name varchar(10));

• 查看表的创建信息

show create table student;

8、导入现有的数据表、表的数据。

• 方式一：在cmd命令行中执行

source 文件的全路径名
例子：source d:\liu.sql;

• 方式二：在图形工具中导入数据

比如：SQLyog中 选择 “工具”---“执行sql脚本”--选中xxx.sql即可。

二、开启mysql之路

1、最基本的select语句

-- ①
select 1+1 , 3*2 ;
相当于：
select 1+1 , 3*2 from dual; #dual是“伪表”。

-- ②
select *from employees;
#“*”表示 表中的所有的字段（或列）

2、列的别名（as）

#as的全称：alias（别名），可省略。
select userName name from student；

#不省略的情况
select userName as name from student；

#列的别名可以使用一对“”双引号引起来，不要使用‘’单引号 （遵守约定）
select userName as “name” from student；

3、去除重复行（distinct）

#例如查询信息部有哪些班级
#①错误的写法：没有去重的情况
select classId from emp;

#②正确的写法：去重的情况
select distinct classId from emp；

#③错误情况二：
select stuName , distinct classId from emp;

#④错误情况三：
#仅仅是没有报错，但没有实际意义，没能去掉重复的
select distinct classId,stuName;

#注意：distinct 关键字只能放在前面，若放后面则报错。

4、空值参与运算

• 空值：null
• null 不等同于 0、’ '，‘null’
• 空值参与运算：结果一定也为空。

select employee_id,salary as "月工资" , salary * (1+commission_pct) * 12 as "年工资" ,commission_pct from employees;
#查询后可看到 与 空值 运算的 结果也为空。

• 实际问题的解决方案(针对上面的情况)：引入ifnull

select employee_id,salary as "月工资" , salary * (1+ ifnull(commission_pct,0)) * 12 as "年工资" ,commission_pct from employees;

5、着重号 ``

#使用场景：当字段、表名 和 mysql中 的保留字一样时，使用着重号标记就可以正常使用了。
select * from  `order`;

6、查询常数

select '刘',123,stuName,stuSex from stu;
#注意：“刘”，123都不是 stu表中的，这两个就是为常数了，
#此用法可显示一个学生对应的所有成绩（应该是这样）。

7、显示表结构（describe）

#显示表中字段的详细信息
#方式一：
describe employees;

#方式二：
desc employees；

8、过滤数据（where关键字）

#练习：查询90号部门的员工信息
select * from employees  where department_id=90;

#练习：查询last_name为’King‘的员工信息
select * from employees where last_name='King';

9、练习题：

# 1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY
SELECT employee_id , last_name,salary * 12 "ANNUAL SALARY"
FROM employees;

SELECT employee_id,last_name,salary * 12 * (1 + IFNULL(commission_pct,0)) "ANNUAL
SALARY"
FROM employees;

# 2.查询employees表中去除重复的job_id以后的数据
SELECT DISTINCT job_id
FROM employees;

# 3.查询工资大于12000的员工姓名和工资
SELECT last_name, salary
FROM employees
WHERE salary > 12000;

# 4.查询员工号为176的员工的姓名和部门号
SELECT last_name, department_id
FROM employees
WHERE employee_id = 176;

# 5.显示表 departments 的结构，并查询其中的全部数据
show create table departments;

三、运算符

A：算术运算符

• 算术运算符有：

+（加法）、-（减法）、*（乘法）、/（div）除法、%（mod）求余。

1、加法：“+”

select 10+10,10+'a','a'+'b' ,10+null from dual;

#结果为：20、10、0，null。
#在mysql中，“+”只有运算作用，没有 连接作用。
#在mysql中，任何字符与 null 参与运算 ，结果都为 null

2、减法：“-”

select 10-5,10-'a','a'-10 ,10-null from dual;
#结果为：5、10、-10、null。
#在mysql中 不是数字的字符默认为0.
#在mysql中，任何字符与 null 参与运算 ，结果都为 null

3、乘法：“*”

select 10*2,10*'a' ,10*null from dual;
#结果为：20、0、null。

4、除法：“/ 或 div”

select 10/2,6 div 3,9 div -3,2/0 , 0/2,'a'/2 from dual;
结果为：5、2、-3、null、0、0。
#注意：当分母为0时，结果为 null，当分子为0时，结果为0。

5、取模运算：“% 或 mod”

SELECT 10 % 2,5 % 3,6 % -5,-6 % 5 ,-6 mod -5 FROM DUAL;
#结果为：0、2、1、-1、-1。
/*
注意：
当分子为正时，结果为正（分母为负也是），
当分子为负时，结果为负（分母为负也是）。
*/

B：比较运算符

• 比较运算符有：=、<=>、<>、!=、<、<=、>、>=

1、“=”：等于 的使用

SELECT 1=2, 1 != 2,1=1,1='1',1='a','a'='b','a'='a' FROM DUAL;
#结果为：0、1、1、1、0、0、1。
#0为false，1为true。
#字符串存在隐式转换，若转换不了则默认为0
select 1=null，null=null，null='null' from dual；
#结果为：null、null、null

2、“<=>”：安全等于 的使用

• ’<=>‘和’=‘的唯一区别：’<=>‘可以判断null。

SELECT 1<=>1,1<=>5,null<=>null,
null<=>'null',1<=>null,'a'<=>'a',
'a'<=>'null','a'<=>null 
FROM DUAL;
#结果：1、0、1、0、0、1、0、0

#练习：获取employees 表中commission_pct 为null的信息

#方式一：使用’<=>‘
SELECT last_name,salary,commission_pct 
FROM employees
WHERE commission_pct <=>NULL;

#方式二：使用 IS NULL
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;

#方式三：使用函数 ISNULL()
SELECT last_name,salary,commission_pct
FROM employees
WHERE ISNULL(commission_pct);

3、” != “或“<>”：不等于 的使用

SELECT 3<>2,2<>'2',NULL<>NULL,NULL<>'NULL','a'<>'b' 
FROM DUAL;
#结果如下：1、0、null、null、1

4、“> ,>=,<,<=”的使用

select 2>1,2>=2,2<1,2<=2 from dual;
#结果：1，1，0，1

C、关键字的使用：

1、IS NULL、IS NOT NULL、ISNULL的使用

#练习一：获取employees 表中commission_pct 为null的信息

#方式一：使用关键字 IS NULL
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;

#方式二：使用函数 ISNULL()
SELECT last_name,salary,commission_pct
FROM employees
WHERE ISNULL(commission_pct);

#方式三：使用’<=>‘
SELECT last_name,salary,commission_pct 
FROM employees
WHERE commission_pct <=>NULL;

#练习二：查询表中commission_pct不为null的数据有哪些

方式一：使用关键字：IS NOT NULL
SELECT last_name,salary,commission_pct 
FROM employees
WHERE commission_pct IS NOT NULL;

方式二：对函数ISNULL()取反
SELECT last_name,salary,commission_pct 
FROM employees
WHERE NOT ISNULL(commission_pct);

方式三：对运算符 <=> 取反
SELECT last_name,salary,commission_pct 
FROM employees
WHERE NOT commission_pct <=>NULL;

2、LEAST()最小值、GREATEST()最大值

#练习一：使用函数 获取最小值、最大值
SELECT LEAST(5,9,1,'a'),GREATEST(5,9,1,'a') FROM DUAL;
结果：1、a

3、BETWEEN 条件1(下界) AND 条件2(上界)

• 查询条件1和条件2范围内的数据，包含边界

#练习一：查询工资在6000 到 8000的员工信息

SELECT last_name,salary,commission_pct
FROM employees
WHERE salary BETWEEN 6000 AND 8000;

#注意：交换6000 和 8000之后，查询不到数据
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 8000 AND 6000;

#练习二：查询工资不在6000 到 8000的员工信息

方式一：使用 between...and
SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;

方式二：使用运算符
SELECT employee_id,last_name,salary
FROM employees
WHERE salary <6000 OR salary>8000;

4、IN()，NOT IN()的使用

#练习一：查询部门为10,20,30部门的员工信息

SELECT last_name,salary, department_id
FROM  employees
WHERE department_id IN(10,20,30);

#练习二：查询工资不是6000,7000,8000的员工信息

SELECT last_name,salary,commission_pct
FROM employees
WHERE salary NOT IN(6000,7000,8000);

5、like 模糊查询

# % : 代表不确定个数的字符 （0个，1个，或多个）

#练习：查询last_name中包含字符'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';

#练习：查询last_name中以字符'a'开头的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE 'a%';

#练习：查询last_name中包含字符'a'且包含字符'e'的员工信息
#写法1：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
#写法2：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

# _ ：代表一个不确定的字符

#练习：查询第3个字符是'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

#练习：查询第2个字符是_且第3个字符是'a'的员工信息
#需要使用转义字符: \ 
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%';

#或者  (了解)
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';

6、REGEXP \ RLIKE :正则表达式

SELECT 'shkstart' REGEXP '^shk',
 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk'
FROM DUAL;

SELECT 'atguigu' REGEXP 'gu.gu','atguigu' REGEXP '[ab]'
FROM DUAL;

D、逻辑运算符：

• OR(或 ||)、AND(或&&)、NOT(或 ！)、XOR

# or  and 
SELECT last_name,salary,department_id
FROM employees
#where department_id = 10 or department_id = 20;
#where department_id = 10 and department_id = 20;
WHERE department_id = 50 AND salary > 6000;

# not 
SELECT last_name,salary,department_id
FROM employees
#where salary not between 6000 and 8000;
#where commission_pct is not null;
WHERE NOT commission_pct <=> NULL;

# XOR :追求的"异"
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 50 XOR salary > 6000;
#当满足=50时不满足>6000的，相反满足>6000不满足=50的。
#注意：AND的优先级高于OR

练习题：

# 1.选择工资不在5000到12000的员工的姓名和工资

SELECT last_name, salary
FROM employees
WHERE salary < 5000 OR salary > 12000;

SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;

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

SELECT last_name, department_id
FROM employees
WHERE department_id = 20 OR department_id = 50;

SELECT last_name, department_id
FROM employees
WHERE department_id IN(20, 50);
# 3.选择公司中没有管理者的员工姓名及job_id

SELECT last_name, job_id
FROM employees
WHERE manager_id IS NULL;

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

SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

# 5.选择员工姓名的第三个字母是a的员工姓名

SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

# 6.选择姓名中有字母a和k的员工姓名

SELECT last_name
FROM employees
WHERE last_name LIKE '%a%k%' OR last_name LIKE '%k%a%';

# 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息

SELECT employee_id,first_name,last_name
FROM employees
WHERE first_name LIKE '%e';

SELECT employee_id,first_name,last_name
FROM employees
WHERE first_name REGEXP 'e$';

# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种

SELECT last_name,job_id
FROM employees
#where department_id in (80,90,100);
WHERE department_id BETWEEN 80 AND 100;

# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id

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

四、排序与分页

1、排序规则

• 使用order by 子句排序

ASC（ascend）：升序
DESC（descend）：降序

ORDER BY 子句在select语句的结尾。

-- 注意：
-- 如果没有使用排序操作，
-- 默认情况下查询返回的数据是按照添加数据的顺序显示的。

• 练习

# 练习一：按照salary从高到低的顺序显示员工信息

SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;

# 练习二：按照salary从低到高的顺序显示员工信息
方式一：
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary ASC;

方式二：
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary;
-- 如果在ORDER BY 后没有显式指名排序的方式的话，
-- 则默认按照升序排列。

#练习三：我们可以使用列的别名，进行排序
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal;
/*
注意：
#列的别名只能在 ORDER BY 中使用，不能在WHERE中使用。
#如下操作报错！
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
WHERE annual_sal > 81600;
*/

• 强调格式：WHERE 需要声明在FROM后，ORDER BY之前。

SELECT employee_id,salary
FROM employees
WHERE department_id IN (50,60,70)
ORDER BY department_id DESC;

• 二级排序

#练习：显示员工信息，按照department_id的降序排列，
-- salary的升序排列
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;

2、分页操作（limit）

• mysql使用limit实现数据的分页显示

# 需求1：每页显示20条记录，此时显示第1页

SELECT employee_id,last_name
FROM employees
LIMIT 0,20;
#注：limit a，b；a表示开始位置，b表示一页要显示的条数。

# 需求2：每页显示20条记录，此时显示第2页

SELECT employee_id,last_name
FROM employees
LIMIT 20,20;

# 需求3：每页显示20条记录，此时显示第3页

SELECT employee_id,last_name
FROM employees
LIMIT 40,20;

• 分页公式

#需求：每页显示pageSize条记录，此时显示第pageNo页：
#公式：LIMIT (pageNo-1) * pageSize,pageSize;

• WHERE … ORDER BY …LIMIT 声明顺序如下：

# LIMIT的格式： 严格来说：LIMIT 位置偏移量,条目数
# 结构"LIMIT 0,条目数" 等价于 "LIMIT 条目数"

SELECT employee_id,last_name,salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
#limit 0,10;
LIMIT 10;


#练习：表里有107条数据，我们只想要显示第 32、33 条数据怎么办呢？

SELECT employee_id,last_name
FROM employees
LIMIT 31,2;

• MySQL8.0新特性：LIMIT … OFFSET …

#练习：表里有107条数据，我们只想要显示第 32、33 条数据怎么办呢？

SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;

#练习：查询员工表中工资最高的员工信息
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC
#limit 0,1
LIMIT 1;

#2.4 LIMIT 可以使用在MySQL、PGSQL、MariaDB、SQLite 等-
#数据库中使用，表示分页。
# 不能使用在SQL Server、DB2、Oracle！

3、练习题

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

SELECT last_name,department_id,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal DESC,last_name ASC;

#2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，
#显示第21到40位置的数据

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20;

#3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，
#再按部门号升序

SELECT last_name,email,department_id
FROM employees
#where email like '%e%'
WHERE email REGEXP '[e]'
ORDER BY LENGTH(email) DESC,department_id ASC;

五、多表查询

• 笛卡尔积的错误：缺少了多表的连接条件

#错误的实现方式：每个员工都与每个部门匹配了一遍。
SELECT employee_id,department_name
FROM employees,departments;  #查询出2889条记录

#错误的方式
SELECT employee_id,department_name
FROM employees CROSS JOIN departments;#查询出2889条记录

• . 多表查询的正确方式：需要有连接条件

SELECT employee_id,department_name
FROM employees,departments
#两个表的连接条件
WHERE employees.`department_id` = departments.department_id;

#如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。
SELECT employees.employee_id,departments.department_name,
employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;

• 从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表。

• 可以给表起别名，在SELECT和WHERE中使用表的别名。

SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.department_id;

#如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
#如下的操作是错误的：
SELECT emp.employee_id,departments.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = departments.department_id;

• 结论：如果有n个表实现多表的查询，则需要至少n-1个连接条件

#练习：查询员工的employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,
l.city,e.department_id,l.location_id
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;

1、多表查询的分类

• 可分为：

1、角度一： 等值连接 vs 非等值连接
2、角度二： 自连接 vs 非自连接
3、角度三： 内连接 vs 外连接

• A、等值连接 vs 非等值连接

1、等值连接
等值连接就是 表与表之间有相同的部分，
例如：department_id

2、非等值连接
例子：
SELECT *
FROM job_grades;

SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
#where e.`salary` between j.`lowest_sal` and j.`highest_sal`;
WHERE e.`salary` >= j.`lowest_sal` AND e.`salary` <= j.`highest_sal`;

• B、自连接 vs 非自连接

1、自连接：自己连接自己
#练习：查询员工id,员工姓名及其管理者的id和姓名
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp ,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;

2、非自连接：不同表之间的连接。
例子：省略

• C、内连接 vs 外连接

1、内连接：
合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行。

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id;  #只有106条记录

2、外连接：合并具有同一列的两个以上的表的行, 
结果集中除了包含一个表与另一个表匹配的行之外，
还查询到了左表 或 右表中不匹配的行。
2.1、 外连接的分类：左外连接、右外连接、满外连接
2.2、 左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。
2.3、右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。

#练习：查询所有的员工的last_name,department_name信息 
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id; # 需要使用左外连接

2.4、注意：
外连接：MYSQL不支持SQL92的 外连接语法（+），支持使用SQL99语法
SQL99语法中使用 JOIN ...ON 的方式实现多表的查询。
这种方式也能解决外连接的问题。MySQL是支持此种方式的。

2.5、SQL99语法实现内连接：
SELECT last_name,department_name
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`;

SELECT last_name,department_name,city
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`;

2.6、SQL99语法实现外连接：

#练习：查询所有的员工的last_name,department_name信息 
# 左外连接：
SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右外连接：
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;


#满外连接：mysql不支持FULL OUTER JOIN
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

2.7、 UNION  和 UNION ALL的使用
# UNION：会执行去重操作
# UNION ALL:不会执行去重操作
#结论：如果明确知道合并数据后的结果数据不存在重复数据，
#或者不需要去除重复的数据，
#则尽量使用UNION ALL语句，以提高数据查询的效率。



2.8、 SQL99语法的新特性1:自然连接

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

# NATURAL JOIN : 它会帮你自动查询两张连接表中`所有相同的字段`，然后进行`等值连接`。
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;


2.9、 SQL99语法的新特性2:USING
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);


3.0、拓展：
SELECT last_name,job_title,department_name 
FROM employees INNER JOIN departments INNER JOIN jobs 
ON employees.department_id = departments.department_id 
AND employees.job_id = jobs.job_id;

2、练习题：

# 第06章_多表查询的课后练习

# 1.显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name,e.department_id,d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

# 2.查询90号部门员工的job_id和90号部门的location_id

SELECT e.job_id,d.location_id
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` =