MySQL基础入门篇 - 核心概念及SQL命令

最新推荐文章于 2024-05-28 12:32:57 发布

郭二～

最新推荐文章于 2024-05-28 12:32:57 发布

阅读量255

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/G2theCY/article/details/119302985

版权

一、MySQL简介

MySQL是数据库管理系统(DBMS, DataBase Management System)的一种，与此并列的DBMS还有SqlServer、Oracle、DB2等，MySQL代码开源。C-S架构。现在MySQL是Oracle公司的产品。

database(数据库)，是文件系统，包含了表。
table(表)，数据库中的基本单元，是一种结构化的文件，可以存储数据，一个database中的table名唯一。
column(列) & row(行)，一个table中每一列(字段)都有相应要存储的字段名、数据类型、约束等属性，如唯一性约束，每一行(记录)表示一条完整数据。
primary key(主键)，是用来区分表中每一行的唯一标识。

二、Linux-MySQL相关命令

本地登录命令

port：3306 
$ mysql -u 用户名 -p
$ 输入密码：...
(多以超管用户登录)

root用户 - username ： root ； password ： root

三、DQL语句

数据查询语言-DQL SELECT(查询语句)，最常用。

查询语句思路：

从某张表中查询数据，先经过where条件筛选出有价值的数据；

对这些有价值的数据进行分组；

分组之后可以用having继续筛选；

再用select查询出来；

最后排序输出！

以下3张表为后面案例的应用对象：

-- Table1 Name ： EMP
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
-- Table2 Name ： DEPT
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
-- Table3 Name ： SALGRADE
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

3.1DQL - 单表的查询语句

-- 1. 简单查询
	-- 查询一个字段？-- 查询多个字段用逗号隔开; -- 查询所有字段用*
	select 字段名 from 表名;
	select ENAME, JOB,  from EMP;
	select * from EMP; -- 在实际开发中不建议用*，建议写上所有字段名
	-- 使用as关键字或空格来将列名起别名显示(字符串使用单引号括起来，'str')
	select ENAME, JOB as abc from EMP;


-- 2.条件查询(=, >, <, !=(<>), is null, is not null, between...and..., and, or)
	select 字段名 from 表名 where 条件;
	select empno,ename,job,sal from EMP where job = 'MANAGER' and sal > 2500;
	select empno,ename,job,sal from EMP where job = 'manager' or job = 'salesman';
	
	-- 优先级and高于or，如要查询工资大于2500，并且 部门编号为10或20部门的员工，加括号可解决。
	select * from EMP where sal > 2500 and (deptno = 10 or deptno = 20);
	
	-- in后面跟具体的值而非区间 相当于多个 or， not in 表示不在这个范围中
	select * from EMP where job in('MANAGER', 'SALESMAN');
	select * from EMP where job not in('MANAGER', 'SALESMAN');
	
	-- 区间范围用between...and
	select * from EMP where job between'MANAGER' and 'SALESMAN';
	
	-- 模糊查询用like，'%'代表任意多个字符，'_'代表一个字符。如查询员工姓名中包含T的员工列
	select * from EMP where ename like '%T%'; -- 以T结尾:'%T',以T开头:'T%',第三个字母是	A:'__A%',查找带有'_'的字符串：'%\_%'
	
	-- 查找并排序，order by。如查找所有员工薪资，并按薪资排序
	select ename,sal from EMP order by sal; -- 默认升序
	select ename,sal from EMP order by sal desc; -- 后面加个desc为降序
	select ename,sal from EMP order by sal asc; -- 后面加个asc为指定升序(也可以不写)
	select ename,sal from EMP order by sal asc, ename asc; -- 按照薪资升序，如果薪资一样的话在按照名字升序排列(多个字段)。
	
	/*
	 *综合案例：找出工资在1250到3000之间的员工信息，要求按照薪资降序排列
	 */
	select ename,sal from EMP where sal between 1250 and 3000 order by sal desc; -- 关键字顺序就不能变：select from where order by

数据处理函数/单行处理函数

特点：一个输入对应一个输出

1.	lower() -- 转换小写
	upper() -- 转换大写
mysql> select lower(ENAME) as ename from EMP;
2.	substr() -- 参数(被截取的字符串，起始下标，截取的长度)，下标从1开始算起
mysql> select substr(ename, 1, 1) as ename from EMP;
	找出员工名字第一个字母是A的员工信息？
	1 - select * from EMP where ename like 'A%';
	2 - select * from EMP where substr(ename, 1, 1) = 'A';
3.	length() -- 取一个串的长度
mysql> select length(ENAME) as enamelength from EMP;
4.	round() -- 四舍五入，参数(目标值, 保留的小数位)
mysql> select round(1236.567, 2) as result from EMP;
5.	ifnull() -- 参数(数据，被当做哪个值),如果“数据”为NULL时，把这个数据当做哪个值
mysql> select ENAME, (sal + ifnull(COMM, 0)) * 12 as yearsalary from EMP;
6.	case..when..then..when..then..else..end -- 类似if..else语句
select
	ename, 
	job, 
	sal as oldsal, 
	(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal 
from 
	EMP;

分组函数/多行处理函数

特点：多行的输入对应1行输出

注意：1.分组函数在使用时必须先进行分组，然后才能使用，若未对数据进行分组，整张表默认为1组

2.分组函数自动忽略NULL，无需对NULL提前进行处理。

3.分组函数不能直接使用在where子句中，

如select ename, sal from EMP where sal > min(sal);不可以，原因是select执行前，from、where、group by执行过了，放在select后可以，where执行前，group by未执行，放在where后不可以。

4.所有分组函数可以组合在一起来用。

1.	count() -- 计数
mysql> select count(*) from EMP; -- 统计表中的总行数(表中只要有1条记录，则这条记录不全为NULL)
+----------+
| count(*) |
+----------+
|       14 |
+----------+

mysql> select count(comm) from EMP; -- 统计该字段中非NULL的记录总数
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+

-- 语法顺序：select .. from .. where .. group by .. order by .. 
-- 执行顺序：(from .. where .. group by .. having .. order by ..)先执行，select再执行，即先分组后执行
-- group by是用来分组的，因此分组函数不可以放在where后，但可以放在select后，因为执行顺序。
2.	sum() -- 求和
3.	avg() -- 求均值
4.	max() -- 找最大值
5.	min() -- 找最小值

分组查询 group by语句

先分组，再对每一组的数据进行查询。

– group by后面可以跟很多字段，联合起来分组，此时select后只能跟参加分组的字段。

例子1：找出每个岗位的工资和？
实现思路：按照岗位分组，对工资求和
select 
	job, sum(sal)
from 
	EMP 
group by 
	job;
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| ANALYST   |  6000.00 |     
| CLERK     |  4150.00 |
| MANAGER   |  8275.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  5600.00 |
+-----------+----------+
-- select语句中如果有group by语句的话，select后只可跟参加分组的字段及分组函数，不能跟别的字段，因为就不会对齐，所以无意义

例子2：找出每个部门的最高薪资？
mysql> select DEPTNO, max(sal) from EMP group by DEPTNO;
+--------+----------+
| DEPTNO | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+

例子3：找出每个部门(DEPTNO)中不同工作岗位(JOB)的最高薪资(SAL)？
技巧：需要将两个字段联合成一个字段看，即联合分组
+--------+-----------+---------+--------+
| ename  | job       | sal     | deptno |
+--------+-----------+---------+--------+
| CLARK  | MANAGER   | 2450.00 |     10 |
| KING   | PRESIDENT | 5000.00 |     10 |
| MILLER | CLERK     | 1300.00 |     10 |

| SMITH  | CLERK     |  800.00 |     20 |
| JONES  | MANAGER   | 2975.00 |     20 |
| SCOTT  | ANALYST   | 3000.00 |     20 |
| ADAMS  | CLERK     | 1100.00 |     20 |
| FORD   | ANALYST   | 3000.00 |     20 |

| ALLEN  | SALESMAN  | 1600.00 |     30 |
| WARD   | SALESMAN  | 1250.00 |     30 |
| MARTIN | SALESMAN  | 1250.00 |     30 |
| BLAKE  | MANAGER   | 2850.00 |     30 |
| TURNER | SALESMAN  | 1500.00 |     30 |
| JAMES  | CLERK     |  950.00 |     30 |
+--------+-----------+---------+--------+
mysql> select deptno, job, max(sal) from EMP group by deptno, job;
+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+

例子4：找出每个部门的最高薪资，显示大于3000的条目？
mysql> select deptno, max(sal) from EMP where sal > 3000 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)

例子5：找出每个部门的平均薪资，要求显示平均薪资高于2500的？
mysql> select deptno, avg(sal) from EMP group by deptno having avg(sal) > 2500;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)

综合案例：

找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位之外，要求按照平均薪资降序排序。

mysql> select job, avg(sal) from EMP where job not in('MANAGER') group by job having avg(sal) > 1500 order by avg(sal) desc;
-- not in('MANAGER'), <> 'MANAGER', 都可以
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST   | 3000.000000 |
+-----------+-------------+

3.2 DQL - 连接查询语句

预热：distinct关键字

将查询结果显示去除重复记录，出现在所有字段的最前方

mysql> select deptno, job from EMP;
+--------+-----------+
| deptno | job       |
+--------+-----------+
|     20 | CLERK     |
|     30 | SALESMAN  |
|     30 | SALESMAN  |
|     20 | MANAGER   |
|     30 | SALESMAN  |
|     30 | MANAGER   |
|     10 | MANAGER   |
|     20 | ANALYST   |
|     10 | PRESIDENT |
|     30 | SALESMAN  |
|     20 | CLERK     |
|     30 | CLERK     |
|     20 | ANALYST   |
|     10 | CLERK     |
+--------+-----------+
mysql> select distinct deptno, job from EMP; <<<------两个字段联合起来，去重
+--------+-----------+
| deptno | job       |
+--------+-----------+
|     20 | CLERK     |
|     30 | SALESMAN  |
|     20 | MANAGER   |
|     30 | MANAGER   |
|     10 | MANAGER   |
|     20 | ANALYST   |
|     10 | PRESIDENT |
|     30 | CLERK     |
|     10 | CLERK     |
+--------+-----------+

eg01：统计工作岗位的数量？
mysql> select count(distinct job) from EMP; <<<------可以用分组函数
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+

3.2.1 连接查询 - 概念

跨表查询，多张表联合起来查询数据，为连接查询。如：从EMP表取员工名字，从DEPT表中取部门名字

减少表连接次数，可提高效率哦！

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

外连接：左连接、右连接

eg02：查询员工所在部门名称？
-- 注意：当两张表连接查询，无任何条件限制时，最终查询结果条数是两张表的乘积，即-笛卡尔积现象。
mysql> select * from DEPT;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

mysql> select ename, dname from EMP, DEPT;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | ACCOUNTING |
| SMITH  | RESEARCH   |
| SMITH  | SALES      |
| SMITH  | OPERATIONS |
| ALLEN  | ACCOUNTING |
| ALLEN  | RESEARCH   |
| ALLEN  | SALES      |
| ALLEN  | OPERATIONS |
| WARD   | ACCOUNTING |
| WARD   | RESEARCH   |
| WARD   | SALES      |
...
56 rows in set (0.00 sec)       <<<<--------14 * 4 = 56 条记录
-- 如何避免 笛卡尔积现象？
mysql> select e.ename, d.dname from EMP e, DEPT d where e.deptno = d.deptno; -- 表起了别名
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

3.2.2 连接查询 - 内连接

完全能匹配上条件的数据，查询出来。AB两表无主次关系。

内连接之 - 等值连接

例子1：找到每个员工的部门名称？

SQL92语法：
select 
	e.ename, d.dname 
from 
	EMP e, DEPT d 
where 
	e.deptno = d.deptno;
SQL92语法的缺点：结构不清楚，表的连接条件和后期进一步筛选的条件，都放到了where后面。

SQL99语法：
select 
	e.ename, d.dname 
from 
	EMP e
join	-- 	连接的表
	DEPT d 
on		--  连接条件
	e.deptno = d.deptno;
where	-- 	筛选条件
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+

SQL99语法的优点：表连接的条件是独立的，与where不重叠。

内连接之 - 非等值连接

案例2：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

mysql> select * from SALGRADE;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 | -- 薪资在700-1200之间的都是1级
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
mysql> select e.ename, e.sal, s.grade from EMP e join SALGRADE s on e.sal between s.losal and s.hisal; -- 条件不是一个等量关系，非等值连接
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.00 sec)

内连接之 - 自连接

案例3：查询员工的上级领导，要求显示员工名和对应的领导名？

mysql> select ea.ename, eb.ename from EMP ea join EMP eb on ea.mgr = eb.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
13 rows in set (0.00 sec) -- 因为KING的mgr是NULL，因此少一条记录！

3.2.3 连接查询 - 外连接

外连接有主次关系。右连接主表是右表，左连接主表是左表。左右连接可以通用互换。

右外连接(右连接)

将join关键字右边的表看成主表，主要是为了将主表数据全部查询出来，捎带关联查询左边的表。

案例1：查询每个员工的部门名称，要求显示员工姓名和其所在部门名称？

mysql> select e.ename, d.dname from EMP e right join DEPT d on e.deptno = d.deptno;
+--------+------------+                    ↑↑↑
| ename  | dname      |			   -- 多了right关键字
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
| NULL   | OPERATIONS |   -- 右边的DEPT表的全部部门都显示出来了
+--------+------------+

左外连接(左连接)

-- 同样想查询结果与右连接一样的，只是主表换了
mysql> select e.ename, d.dname from DEPT d left join EMP e on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
| NULL   | OPERATIONS |
+--------+------------+

3.2.4 连接查询之 - 多表查

~~~~~~用法~~~~~~
select
	...
from
	a
join
	b
on
	a和b的连接条件
right join          -- 内外连接混用
	c
on
	a和c的连接条件
...
~~~~~~案例~~~~~~
-- 1.查询出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级？
mysql> select e.ename, d.dname, e.sal, s.grade from EMP e join DEPT d on e.deptno = d.deptno join SALGRADE s on e.sal between s.losal and s.hisal;
+--------+------------+---------+-------+
| ename  | dname      | sal     | grade |
+--------+------------+---------+-------+
| SMITH  | RESEARCH   |  800.00 |     1 |
| ALLEN  | SALES      | 1600.00 |     3 |
| WARD   | SALES      | 1250.00 |     2 |
| JONES  | RESEARCH   | 2975.00 |     4 |
| MARTIN | SALES      | 1250.00 |     2 |
| BLAKE  | SALES      | 2850.00 |     4 |
| CLARK  | ACCOUNTING | 2450.00 |     4 |
| SCOTT  | RESEARCH   | 3000.00 |     4 |
| KING   | ACCOUNTING | 5000.00 |     5 |
| TURNER | SALES      | 1500.00 |     3 |
| ADAMS  | RESEARCH   | 1100.00 |     1 |
| JAMES  | SALES      |  950.00 |     1 |
| FORD   | RESEARCH   | 3000.00 |     4 |
| MILLER | ACCOUNTING | 1300.00 |     2 |
+--------+------------+---------+-------+
14 rows in set (0.00 sec)
-- 2.查询出每个员工的部门名称、工资等级及上级领导，要求显示员工名、部门名、领导名、薪资及薪资等级？
mysql> select e.ename, d.dname, eb.ename, e.sal, s.grade from EMP e join DEPT d on e.deptno = d.deptno left join EMP eb on e.mgr = eb.empno join SALGRADE s on e.sal between s.losal and s.hisal;
+--------+------------+-------+---------+-------+
| ename  | dname      | ename | sal     | grade |
+--------+------------+-------+---------+-------+
| SMITH  | RESEARCH   | FORD  |  800.00 |     1 |
| ADAMS  | RESEARCH   | SCOTT | 1100.00 |     1 |
| JAMES  | SALES      | BLAKE |  950.00 |     1 |
| WARD   | SALES      | BLAKE | 1250.00 |     2 |
| MARTIN | SALES      | BLAKE | 1250.00 |     2 |
| MILLER | ACCOUNTING | CLARK | 1300.00 |     2 |
| ALLEN  | SALES      | BLAKE | 1600.00 |     3 |
| TURNER | SALES      | BLAKE | 1500.00 |     3 |
| JONES  | RESEARCH   | KING  | 2975.00 |     4 |
| BLAKE  | SALES      | KING  | 2850.00 |     4 |
| CLARK  | ACCOUNTING | KING  | 2450.00 |     4 |
| SCOTT  | RESEARCH   | JONES | 3000.00 |     4 |
| FORD   | RESEARCH   | JONES | 3000.00 |     4 |
| KING   | ACCOUNTING | NULL  | 5000.00 |     5 |
+--------+------------+-------+---------+-------+
14 rows in set (0.01 sec)

3.3 DQL - 子查询

嵌套select查询语句

select、from、where后面均可嵌套select子查询

1.select (select)...
(了解即可)
2.from (select)...
案例1：找出每个岗位的平均工资的薪资等级？
-- from后嵌套的select子查询相当于一张临时表了。
3mysql> select tmp.job, tmp.avgsal, sg.grade from (select job, avg(sal) avgsal from EMP group by job) tmp join SALGRADE sg on tmp.avgsal between sg.losal and sg.hisal order by sg.grade;
+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| CLERK     | 1037.500000 |     1 |
| SALESMAN  | 1400.000000 |     2 |
| ANALYST   | 3000.000000 |     4 |
| MANAGER   | 2758.333333 |     4 |
| PRESIDENT | 5000.000000 |     5 |
+-----------+-------------+-------+
.where (select)...
案例2：找出比最低工资高的员工姓名和薪资？
-- 错误实现：
mysql> select ename, sal from EMP where sal > min(sal);
ERROR 1111 (HY000): Invalid use of group function
-- 正确实现：
mysql> select ename, sal from EMP where sal > (select min(sal) from EMP);
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.00 sec)

3.4 union关键字

结果集合并：要求两个结果集的列数和列的数据类型相同。

优点：效率高于连接查询。

案例：查询工作岗位为MANAGER和SALESMAN的员工姓名？

-- 法1：
select ename, job from EMP where job in('MANAGER', 'SALESMAN'); 
-- 法2：
select ename, job from EMP where job = 'MANAGER'
	union
select ename, job from EMP where job = 'SALESMAN';

3.5 limit关键字(重要)

limit：分页查询中，是将查询出来的结果集的一部分取出来，即限制结果条数。

limit在order by之后执行！！！！

案例1：按照薪资降序，取出排名在第3-5名的员工？
-- limit 用法1：limit 起始下标startIndex，长度length；缺省用法：limit 长度。
 select ename, sal from EMP order by sal desc limit 2, 3; -- 下标从0开始算的。
案例2：取出工资排名在[5-9]名的员工？
select ename, sal from EMP order by sal desc limit 4, 5;

分页

每页显示3条记录  每页显示pageSize条记录
第1页：limit 0, 3   [0 1 2]  第pageNo页 
第2页：limit 3, 3   [3 4 5]
第3页：limit 6, 3   [6 7 8]
-- 用法：
limit (pageNo - 1) * pageSize, pageSize;

public static void main(String [] args) {
    //用户提交过来一个页码，和每页显示的记录条数
    int pageNo = 5, pageSize = 10;
    int startIndex = (pageNo - 1) * pageSize;
    String sql = "select ... limit" + startIndex + ", " + pageSize;
}

select语句总结：select...from...where...group by... having...order by...limit....

四、DDL语句和DML语句

数据定义语言-DDL CREATE(新建)、DROP(删除)、ALTER(修改)，针对表结构进行操作

4.1 DDL语句 - 表的创建(建表)

语法格式：

CREATE TABLE 表名(
	字段名1 数据类型[(长度限制)] [default 默认值],
    字段名2 数据类型[(长度限制)] [default 默认值],
    字段名3 数据类型[(长度限制)] [default 默认值]
); -- 最后一个没逗号
表名：建议以t_或tbl_开始，可读性强。

表的复制：将一个表的查询结果复制出一张新表
CREATE TABLE t_student2 as SELECT * FROM t_student;

4.1.1 MySQL中的常用数据类型

1. varchar    -- (最长255位)可变size的字符串，节省空间，动态分配内存，速度慢
2. char       -- (最长255位)固定size的字符串，分配固定内存，书读快，可能浪费空间

3. int        -- (最长11位)整型
4. bigint     -- (最长255)long

5. float
6. double

7. date
8. datetime

9.  clob      -- 字符大对象Character Large Object，高于255位的字符串可以用，如一篇文章。
10. blob     -- 二进制大对象，Binary Large Object，专门存图片、音频、视频等流媒体数据。须使用IO流。

用上面的数据类型建表：t_student 学生信息表

-建立学生信息表，字段包括：学号、姓名、性别、出生日期、 email、班级标识
 CREATE TABLE t_student( 
     id int, 
     name varchar(32), 
     sex char(1) default 'm', 
     birthday(10),
     age int(3), 
     email varchar(255) 
 );
DROP TABLE IF EXISTS t_student; -- 删除该表

4.1.2 约束(非常重要！)

字段加上一些约束，来保证表中数据的完整性，有效性！

常见约束：

非空约束 - not null，约束了not null的字段不能为NULL。not null只有列级约束，无表级约束

唯一性约束 - unique，约束了unique的字段不能重复(可以为NULL，NULL不算重复)，有表级约束

unique和not null可以一起使用的，当一个字段同时被not null和unique约束的话，该字段自动变成主键字段。

CREATE TABLE t_student(
    id int primary key,   -- 主键
    name varchar(32) not null,
    first_name varchar(32),
    unique(name, first_name),        -- 表级约束，用于多字段联合约束
    email varchar(20) unique         -- 列级约束
);

(AAAAA)主键约束 - primary key，每行记录的唯一标识哦，一张表必须有且仅能有主键字段~

主键约束 primary key = not null + unique，不能为空，也不能重复。

主键字段

主键值：主键字段中的每一个值，建议使用int，bigint，char等类型。

自然主键：主键值和业务无关，开发中建议使用自然主键。

业务主键：主键值就是业务中的字段值，主键一旦和业务挂钩，当业务发生变动时，可能会影响到主键值。

drop table if exists t_vip;
create table t_vip(
    id int primary key auto_increment,   -- 主键值可以使用auto_increment自增生成
    name varchar(255)
);

(AAAAA)外键约束 - foreign key

外键外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值须具有唯一性，可以不是主键，如： emp 中的 deptno 值必须来源于 dept 表中的 deptno 字段值。

建立学生表t_student和班级表t_class之间的连接

-- 先建父表
drop table if exists t_classes;
create table t_classes(
classes_id int(3),
classes_name varchar(40),
constraint pk_classes_id primary key(classes_id)
);
-- 再建子表
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3),
primary key(student_id),
foreign key(classes_id) references t_classes(classes_id)   -- 将父表的某字段设为外键
);

检查约束(MySQL不支持)

tips：*.sql文件是SQL脚本文件，全是SQL语句，可以利用 source 该脚本的绝对路径/*.sql来执行该脚本，一般是初始化

！！！注意：表结构的增删改，如删除一个字段，修改一个字段，几乎很少这么做，开发过程中修改表结构成本太高，因此不是重点。

4.2 DML语句 - 表中数据的增删改

数据操作语言-DML INSERT(增), DELETE(删), UPDATE(改)，针对数据改动

插入数据 - insert

语法格式：
insert into 表名(字段名1, 字段名2, 字段名3...) values
(value1, value2, value3),
(value1, value2, value3),
(value1, value2, value3); -- 一次可插入多条记录
字段名和value要一一对应。只要insert，一定会多一条记录。

insert插入日期
MySQL中的日期格式：
date：%Y-%m-%d 年-月-日
datetime：%Y-%m-%d %h:%i:%s 年-月-日 时:分:秒

日期相关的单组函数：
1. str_to_date()函数可以把字符串 varchar 转换为 date 类型数据  -- 
insert into t_student(id, name, birthday) values(3, 'lisi', str_to_date('01-10-1990', '%d-%m-%Y'));
= insert into t_student(id, name, birthday) values(3, 'lisi', '1990-10-01');
2. date_format()函数可以将 日期 类型 转换为 特定格式的字符串    -- 格式化成非默认格式，在查询日期时使用
select id, name, date_format(birthday, '%m/%d/%Y') birth from t_student;
3. now()函数：获取系统当前的datetime格式时间
insert into t_users(id, name, birth, create_time) values(1, 'zhangsan', '1990-10-01', now());

可以将表的查询结果插入到某表中。仅作了解。

修改数据 - update

语法格式：
update 表名 set 字段名1=值1，字段名2=值2，字段名3=值3 where 条件;
-- 注意：没有条件限制会导致所有数据全部更新。
update t_users set name = 'jack', birth = '2000-10-11' where id = 2;

删除表中的数据(表还在) - delete

语法格式？
delete from 表名 where 条件;
-- 注意：没有条件将删除整张表的数据！
delete from t_users where id = 1;

delete from t_users; -- 可删除表中所有数据，但比较慢。

delete删除原理？
不是删除，可以将数据回滚恢复，效率较低，delete可以删除单条记录。
truncate删除原理？
与 delete相反，效率较高，不支持回滚恢复，只能删除整个数据。
truncate table t_users;

4.事务控制语言-TCL

5.数据控制语言-DCL

SHOW DATABASES; -- 查看有哪些数据库
USE 库名; -- 进入到某库下
CREATE DATABASE mydb CHARACTER SET utf8mb4; -- 以utf-8编码形式创建一个库，永远记住utf8mb4！
SHOW CREATE DATABASE mydb;
SHOW CREATE DATABASE mydb2; -- 查看某库的字符编码格式
CREATE DATABASE IF NOT EXISTS mydb2 CHARACTER SET utf8mb4; -- 避免报错，如果不存在该库名即创建一个库
DROP DATABASE IF EXISTS mydb; -- 同理为防止报错终止执行语句，如果存在即删除该库
ALTER DATABASE mydb CHARACTER SET gbk; -- 修改数据库的编码格式
-- mysql无法直接重命名数据库哦！
-- 进入库中，进行表操作
SHOW TABLES; -- 查看某库下有哪些表
source ~/kkb_cpp/MySQL/bjpowernode.sql -- 导入
-- 查看表的数据
SELECT * FROM 表名;
-- 只查看表的结构不看数据 describe
DESC 表名;

五、存储引擎

show engines \G`命令查看支持的存储引擎

5.1 常用的存储引擎

MyISAM

它管理的表
InnoDB

支持事务，所以安全级别高，效率不是最高，
MEMORY

是啥

六、事务(AAAAA)

6.1 事务(transaction) - 概述

一个完整的业务逻辑，最小的工作单元，不可再分。

DML语句，数据操作语言，insert，delete，update语句与事务有关，其它都没关系。对数据增删改的操作，一定要考虑安全问题。

正是因为做某个逻辑时，需要多条DML语句联合完成，所以需要事务的存在。

事务的本质 - 多条DML语句同时成功或同时失败！

InnoDB存储引擎会提供一组用来记录事务性活动的日志文件。

举例：

事务开启了：

insert insert insert delete update update update…

事务结束了！

在事务的执行过程中，每一条DML操作都会记录到“事务性活动的日志文件”中，在事务的执行过程中，我们可以提交事务，可以回滚事务。

事务的4个特性：

A:原子性

事务是最小的工作单元，不可再分。

C:一致性

在同一事务中，所有操作必须同时成功，或同时失败，以保证数据的一致性。

I:隔离性

A事务与B事务之间有一定的隔离级别。

隔离级别(4个)：

1 - 读未提交(read uncommitted)，脏读现象(Dirty Read)。大多数隔离级别都是2挡及以上。

2 - 读已提交(read committed)，解决了脏读现象。

3 - 可重复读(repeatable read)

4 - 序列化/串行化(serializable)

D:持久性

事务最终结束的一个保障，提交事务，相当于将数据保存在硬盘上。

6.2 提交事务(commit)与回滚事务(rollback)

提交事务？ - commit

1.清空事务性活动的日志文件，2.将数据全部彻底持久化到数据库表中。

提交事务标志着，事务全部成功的结束。

回滚事务？ - rollback

1.将之前的DML操作全部撤销，2.清空事务性活动的日志文件

回滚事务标志着，事务全部失败的结束。

MySQL默认的事务行为是事务自动提交，每执行一次DML语句，就提交一次！

若要关闭自动提交机制，开启手动提交机制：

	先执行`start transaction;`

	进行DML语句的操作

	需要提交事务的时候再执行`commit;`

	需要回滚事务的时候再执行`rollback;`  -- 回滚至上一次提交后的点

七、索引

7.1 索引 - 概述

索引是提升查询效率的方式。相当于字典中的目录。

MySQL中的查询数据的扫描方式有2种：全扫描，索引扫描

主键(PK) 与 unique约束字段会自动添加索引对象，且索引在MySQL中是平衡二叉树B-Tree的数据结构。

如果1.数据量庞大；2.该字段经常出现在where的后面，以查询条件的形式存在；3.该字段很少会有DML(增删改)操作：就会考虑给字段添加索引。

建议不要随意添加索引，因为索引也是需要维护的，索引太多反而会降低系统的性能。建议通过主键和unique字段查询。

7.2 索引 - 实现原理

7.3 索引 - 创建和删除索引

创建索引：
mysql> create index ename_index on EMP(ename);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除索引：
mysql> drop index ename_index on EMP;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看一个SQL语句是否使用了索引进行检索？：
mysql> explain select * from EMP where ename = 'KING';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | EMP   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
type = ALL，rows = 14，全扫描。

mysql> create index ename_index on EMP(ename);
Query OK, 0 rows affected (0.01 sec)

mysql> explain select * from EMP where ename = 'KING';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | EMP   | NULL       | ref  | ename_index   | ename_index | 13      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
type = ref，rows = 1，索引方式扫描。

7.4 索引 - 索引的失效

索引什么时候会失效？

1.如select \* from EMP where ename like '%T'; ，ename上添加索引也不会走索引，为什么？

因为模糊匹配当中以%开头了。

尽量避免以%开始。这是一种优化的手段。

2.如select * from EMP where ename = 'KING' or job = 'MANAGER';，使用or进行查询时，如果两边的条件字段都有索引，才会走索引，如果其中一边有个字段没有索引，呢么另一个字段上的索引也会失效。所以一般别用or这个关键字来查询。可以使用union，这样索引不会失效，从而代替or。

3.使用符合索引的时候，没用左侧的列查找，索引会失效。(两个字段联合起来添加一个索引，叫复合索引)

mysql> create index emp_ename_job_index on EMP(ename, job);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 以左边列为条件查询时，按照了索引查找
mysql> explain select * from EMP where ename = 'KING';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | EMP   | NULL       | ref  | emp_ename_job_index | emp_ename_job_index | 13      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

-- 以右边列为条件查询时，没用索引查找
mysql> explain select * from EMP where job = 'SALESMAN';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | EMP   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

4.在where中索引列参加了运算，索引失效。

等等…

7.5 索引的分类

单一索引：单字段

复合索引：多字段联合

主键索引：pk字段

唯一性索引：unique字段

八、视图

8.1 视图 - 概述

可以面向视图对象进行增删改查，对视图对象的操作会导致原表被操作。

视图对象在实际开发中的作用？

简化SQL语句，利于维护(在DQL语句很复杂的情况下)

-- 视图的创建
mysql> create view dept2_view as select * from dept2;
Query OK, 0 rows affected (0.00 sec)

-- 视图的删除
mysql> drop view dept2_view;
Query OK, 0 rows affected (0.00 sec)

create view
	emp_dept_view
as
	select
		e.ename, e.sal, e.dname
	from
		emp e                        -- 后面这个DQL查询语句如果很长，可以引用为一个视图，简化查询语句的使用
	join
		dept d
	on
		e.deptno = d.deptno;

郭二～

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础入门篇 - 核心概念及SQL命令

MySQL基础入门篇一、MySQL简介二、Linux-MySQL相关命令三、DQL语句3.1DQL - 单表的查询语句3.2 DQL - 连接查询语句3.2.1 连接查询 - 概念3.2.2 连接查询 - 内连接3.2.3 连接查询 - 外连接3.2.4 连接查询之 - 多表查3.3 DQL - 子查询3.4 union关键字3.5 limit关键字(重要)四、DDL语句和DML语句4.1 DDL语句 - 表的创建(建表)4.1.1 MySQL中的常用数据类型4.1.2 约束(非常重要！)4.2 DML语句
复制链接

扫一扫