MySQL数据库知识学习（一）SQL与基本知识_mysql数据模型从抽象层次看是什么-优快云博客

本文链接：https://blog.youkuaiyun.com/cai13070139328/article/details/100023447

前言

MySQL 是一种关系型数据库，因为 MySQL 是开源免费的，并且方便扩展，所以在Java企业级开发中非常常用的。因此决定从头开始系统的学习一次MySQL的知识，具体路线如下：

基本知识

1、数据模型

数据模型是用来抽象、表示和处理现实世界中的数据和信息的工具，是对现实世界的模拟，是数据库系统的核心和基础。

数据抽象共有三个层次：物理层、逻辑层和视图层。

在逻辑层使用的数据模型包括两类：

概念数据模型：主要用于数据库设计，它能被一般的用户理解，与人的思维表达方式比较接近。这样的模型有实体-联系模型（ERM）；
逻辑数据模型：，按计算机系统的观点对数据建模，使得数据更适合用计算机加以表示。这里模型主要用于DBMS的实现，比如关系模型、面向对象模型、层次模型和网状模型。

设计师构建数据库模式的方法通常是首先使用E-R模型在高层对数据建模，然后再将其转变成关系模型。在物理层使用的数据模型称为物理数据模型。

数据模型通常由数据结构、数据操作和完整性约束三部分组成。

2、数据库语言

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

数据查询语言DQL：
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：
SELECT <字段名表> FROM <表或视图名> WHERE <查询条件>
SELECT * FROM lmapp.lm_addr where uid like ‘U1351106%’;

数据操纵语言DML：
在数据库表中更新，增加和删除记录，不包含查询，数据操纵语言DML主要有三种形式：
1)插入：INSERT
insert into tmp (medid,searchid) values (12345,54321);
2)更新：UPDATE
update tmp set searchid=’1’ where medid=’12345’;
3)删除：DELETE
delete from tmp where medid=’12345’;

数据定义语言DDL：
数据定义语言DDL用来创建数据库中的各种对象—–表、视图、索引、同义词、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER(表视图索引同义词簇)
下面是通用的SQL语法用来创建MySQL表：
- CREATE TABLE table_name (column_name column_type);

数据控制语言DCL：
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
1) GRANT：授权。
2) REVOKE：撤销授权。
3) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点，回滚命令使数据库状态回到上次最后提交的状态。其格式为： SQL>ROLLBACK;
4) COMMIT [WORK]：提交。在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。

3、数据库模式

根据数据的不同抽象层次，数据库有三级模式：外模式、概念模式和内模式。

数据库内模式：又称为存储模式，是对数据库物理结构和存储方式的描述，是数据在数据库内部的表示方式。一个数据库只有一个内模式。内模式反映了数据库系统的存储观。

概念模式：又称模式或逻辑模式，对应于概念级。它是由数据库设计者综合所有用户的数据，按照统一的观点构造的全局逻辑结构，是对数据库中全部数据的逻辑结构和特征的总体描述，是所有用户的公共数据视图(全局视图)。它是由数据库管理系统提供的数据模式描述语言(Data Description Language，DDL)来描述、定义的。概念模式反映了数据库系统的整体观。

数据库外模式：又称为子模式或用户模式，它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图。通常是模式的子集。一个数据库可有多个外模式。用户可以通过外模式描述语言来描述、定义对应于用户的数据记录(外模式)，也可以利用数据操作语言(Data Manipulation Language，DML)对这些数据记录进行操作。外模式反映了数据库系统的用户观。

4、E-R图和关系模型

E-R图由以下元素构成：

矩形，代表实体型；
椭圆，代表属性；
菱形，代表联系；
线段，将属性和实体性相连，或将实体型和联系相连。

关系模型由关系数据结构、关系操作集合和关系完整性约束三部分构成。在关系模型中，现实世界实体以及实体间的联系均用关系来表示。

关系模型中常用的关系操作包括：选择、投影、连接、除、并、交、差等查询操作和增加、删除、修改两大部分。关系操作的特点是集合操作方式，即操作的对象和结果都是集合。关系操作可以使用两种方式定义：基于代数的定义称为关系代数；基于逻辑的定义称为关系演算。由于使用变量的不同，关系演算又分为元组关系演算和域关系演算。

关系模型允许定义三类完整性约束：实体完整性、参照完整性和用户定义完整性。其中实体完整性和参照完整性是关系模型必须满足的完整性约束条件。实体完整性规则是：关系的主码不能取空值。参照完整性规则是：外码必须是另一个表中主码的有效值，或者是“空值”。

SQL语言

关系数据库系统支持三级模式结构，其模式（数据库）、外模式和内模式中的基本对象有表、视图和索引。因此SQL的数据定义功能包括模式定义、表定义、视图定义和索引定义。SQL通常不提供修改模式定义、修改视图定义和修改索引定义。用户如果想修改这些对象，只能先将它们删除，然后再重建。

1、表的基本操作

1.创建表

使用 CREATE TABLE 基本表名 语句创建表，如下所示创建学生表：

 CREATE TABLE student (
           sid INT NOT NULL AUTO_INCREMENT,
           sname VARCHAR(100) NOT NULL,
           sage int NOT NULL DEFAULT 18,
           saddress VARCHAR(255),      
           PRIMARY KEY (sid) 
)ENGINE = InnoDB;

其中 student 为表名，表的所有列在括号中，各列之间逗号隔开且每列的名称是唯一的，列名后是数据类型，之后是列完整性约束，PRIMARY KEY关键字指定表的主键为哪一列，这里为sid，AUTO_INCREMENT表示sid是自增的；ENGINE = InnoDB表示数据库的存储引擎。

需要注意的是，创建的表名必须是不存在于数据库的，否则将出错。如果想覆盖某个表，必须先删除，再重建。

NULL值：

NULL值为没有值或缺值。允许NULL值的列也允许插入行时不给出该列的值，不允许NULL值的列，不接受没有值的行。每个表的列只能是NULL或 NOT NULL两种，默认为NULL，以上学生表为例，学号sid、姓名sname和年龄sage不允许为空，但地址saddress是允许为空的。

注意，不要把NULL值和空串搞混了，NULL值是没有值，而 ‘’（两个单引号，中间没有字符）是允许在NOT NULL列的。

主键：

能够唯一地标识一个元组的属性或属性组称为关系的键或候选键。若一个关系有多个候选键则可选其一作为主键(Primary key)。

主键一定不能允许NULL值，且允许多个列共同组成唯一标识主键，这些列都不能为空。

使用默认值：

如果在插入行时没有给出值，MySQL可以使用默认值，默认值由关键字DEFAULT指定，如上表的年龄默认为18。MySQL不允许将函数作为默认值，只能是常量，尽量多用默认值而非null。

存储引擎：

MySQL有多个用来具体管理和处理数据的内部引擎，可用ENGINE = 引擎名来指定，默认为InnoDB引擎，详细请见这篇文章

2.修改表

使用 ALTER TBALE 表名 更改表，更新表的定义而非数据。而且在理想状态下，表一旦存储数据之后就不应该被更改，所以表的设计需要大量的时间，尽可能不在后期更改。

以下语句为表sutdent添加phone列和删除该列，表名必须存在，否则报错。

alter  table  <基本表名>  add  <列名>  <类型>

alter  table  student  add  phone  char(20);

alter  table  <基本表名>  drop  <列名>  <类型>

alter  table  student  drop column phone;

ALTER TBALE的常见用途是定义外键。

3.删除表

使用DROP TABLE 删除表，删除的是整个表而不仅仅是内容。一旦删除，不能撤销。

RENAME TABLE 可以重命名一个表或多个表。语法为：RENAME TABLE student TO stu;

2、数据查询

1.SELECT语句

select语句查询至少给出两条信息：想选择什么，以及从什么地方选择。

SQL查询返回的数据的顺序是没有特殊的，可能是添加的顺序，也可能不是，只要返回相同数目的行就是正确的。

SQL是不区分大小写的，但一般关键字大写，自己定义的列和表名小写，且所有空格被忽略。

//检索单个列，检索stu表的sname列
SELECT  sname  FROM  stu;

//检索多个列
SELECT  sid,sname,sage  FROM  stu;

//检索所有列(表中所有数据)
SELECT  * FROM  stu;

//检索不同的行,DISTINCT用于所有列而非仅前置它的列，所有列的组合相同才算相同
SELECT DISTINCT sname,sid FROM  stu;

//LIMIT限制返回结果，第一个数表示从第几行开始(包括0)，第二个数为返回的行的条数
SELECT sid FROM  stu LIMIT 3,5;//从行3(第4行)开始的5个行

//可以使用列的全限定名查询,stu表位于school数据库中
SELECT  stu.sid FROM  school.stu;

2.排序检索数据

ORDER BY子句可以明确规定SELECT语句查询数据的排列顺序，具体如下：

//检索结果按sid排序，也可按其他列，不一定是检索列
SELECT sid FROM stu ORDER BY sid;

//多列排序，最左原则(只有在sid列相同的时候，才对sname列排序)
SELECT sid,sname FROM stu ORDER BY sid,sname;

//指定排序方向DESC降序，默认为ASC升序
SELECT sid FROM stu ORDER BY sid DESC;

3.过滤数据

WHERE 子句用于提取那些满足指定条件的记录。

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

其中的operator为WHERE子句的操作符

运算符	描述
=	等于
<>	不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式
IN	指定针对某个列的多个可能值

AND & OR

//多个列进行过滤，多个AND
SELECT * FROM Websites WHERE country='CN' AND alexa > 50;

//多个列中任意满足条件的列存在就行
SELECT * FROM Websites WHERE country='USA' OR country='CN';

//还可以把 AND 和 OR 结合起来，使用圆括号来组成复杂的表达式，这样不会有歧义
SELECT * FROM Websites WHERE (country='CN' OR country='USA') AND alexa > 15;

IN & NOT

//IN 操作符允许在WHERE子句中规定多个值。
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

//NOT操作符否认它之后的任何条件，NOT与IN搭配
SELECT column_name(s)
FROM table_name
WHERE column_name NOT IN (value1,value2,...);

LIKE 通配符

在 SQL 中，通配符与 SQL LIKE 操作符一起使用。SQL 通配符用于搜索表中的数据。

通配符	描述
%	替代 0 个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或 [!charlist]	不在字符

//检索Websites 表中 url列 的开头为https的所有行（区分大小写）
SELECT * FROM Websites WHERE url LIKE 'https%';

//_下划线匹配单个字符，name以一个任意字符开始，然后是 "oogle" 的所有客户：
SELECT * FROM Websites WHERE url LIKE '_oogle';

总之，

where子句中可以使用下列运算符：

算术运算符
逻辑运算符
字符串匹配运算符，包括like，not like
集合成员资格运算符，包括in，not in
谓词，包括exists，all，some，unique
聚合函数，包括avg，min，max，sum和count
还可以是另一个select语句

select语句完整语法：

        select 目标表的列名或列表达式序列

        from 基本表名和（或）视图序列

        [where 行条件表达式]

        [group by  列名序列]

               [having  组条件表达式]

        [order by 列名[asc | desc]]

整个语句的执行过程如下：

1)       读取from子句中基本表、视图的数据，执行笛卡尔积操作；

2)       选取满足where子句中给出的条件表达式的元组；

3)       按group子句中指定列的值分组，同时提取满足having子句中组条件表达式的那些组；

4)       按select子句中给出的列名或列表达式求值输出；

5)       order子句对输出的目标表进行排序，按附加说明asc升序排列，或按desc降序排列。

4.数据分组

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

SELECT column_name, aggregate_function(column_name) AS cname（别名）
FROM table_name
WHERE column_name operator value
GROUP BY column_name
ORDER BY column_name;

聚集函数Aggregate ：

AVG() - 返回平均值
COUNT() - 返回行数
FIRST() - 返回第一个记录的值
LAST() - 返回最后一个记录的值
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和

5.子查询

即把一条SELECT语句返回的查询结果用于另一条SELECT语句的WHERE子句。

SELECT sid
FROM stu
WHERE sage > 17;

SELECT sname
FROM stu
WHERE sid IN (SELECT sid
              FROM stu
              WHERE sage > 17);

6.联结表

联结是一种机制，用一条SELECT语句中关联各表，联结并不是物理试实体，他只存在于查询中。

//WHERE来联结的方式，我们称为等值联结或者内部联结
//联结git和coder两表，条件是coder.ID等于git.id
SELECT git.name,coder.ID,git.id FROM coder,git 
WHERE coder.ID = git.id ORDER BY git.name;

//内部联结
SELECT git.name,coder.ID,git.id FROM coder
INNER JOIN git ON coder.ID = git.id ORDER BY git.name;

//多表联结
SELECT prod_name,vend_name,quantity FROM orderitems,products,vendors
WHERE products.ven_id = vendors.vend_id 
AND orderitems.prod_id = products.prod_id 
AND order_num = 20005;

7.高级联结

使用别名：别名除了用于列名和计算字段外，还可以给表起别名。可使用AS为表设置别名，如下的customers别名设置为c，orders别名为o，简化SQL语句

select cust_name,cust_contact 
from customers as c,orders as o,orderitems as oi 
where c.cust_id = o.cust_id 
and oi.order_num = o.order_num 
and prod_id = 'TNT2';

表别名不仅仅可以用于where子句，还可以用于select的列表、order by子句以及语句的其他部分。但，表别名只在查询执行中使用，与列别名不一样，表别名不返回客户机。

使用自联结：自联结（同一个表相联结），如：当我们发现物品（ID为DTNTR）存在问题，因此我们想知道生产该物品的供应商的其他物品是否也存在这些问题。步骤：首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商的其他物品。

//使用子查询
select prod_id,prod_name from products 
where vend_id=(
select vend_id from products where prod_id='DTNTR');

//使用自联结
select p1.prod_id , p1.prod_name from products as p1,  products as p2 
where p1.vend_id=p2.vend_id 
and p2.prod_id='DTNTR';

使用自然联结：它是这样的一种联结，其中你只能选择那些唯一的列，一般通过对表使用通配符(select *),对所有其他表的列明确使用的子集来完成的。通配符只对第一个表使用，所有其他列明确列出，所以没有重复的列被检索出来。

select c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price 
from customers as c,orders as o,orderitems as oi 
where c.cust_id=o.cust_id 
and oi.order_num=o.order_num 
and prod_id='FB';

使用外部联结：联结包含了那些在相关表中没有关联行的行，这种类型的联结称为外部联结。相反，只包含那些有关联行的行的联结称为内部联结。

//内部联结：检索所有客户及其订单
select customers.cust_id,orders.order_num 
from customers inner join orders 
on customers.cust_id = orders.cust_id;

//外部联结：检索所有客户及其订单，包括没有订单的客户
select customers.cust_id,orders.order_num 
from customers left outer join orders
on customers.cust_id = orders.cust_id;

//右外部联结：检索所有客户及其订单，包括没有订单的客户
select customers.cust_id,orders.order_num 
from customers right outer join orders
on orders.cust_id = customers.cust_id;  //转换位置(左边表的所有行)

使用带聚集函数的联结：

//检索所有客户及其每个客户所下的订单数，不包括0
select customers.cust_name,customers.cust_id,count(orders.order_num) as num_ord 
from customers inner join orders 
on customers.cust_id=orders.cust_id 
group by customers.cust_id;

//检索所有客户及其每个客户所下的订单数，包括没有下订单的客户，其订单数为0
select customers.cust_name,customers.cust_id, count(orders.order_num) as num_ord 
from customers left outer join orders 
on customers.cust_id=orders.cust_id 
group by customers.cust_id;

8.组合查询

UNION执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回，取并集。

//查询价格小于10 或供应商名字为a的id号，使用2段语句实现
SELECT order_id, order_price, order_name
FROM order1
WHERE order_price < 10;
和
SELECT order_id, order_price, order_name
FROM order1
WHERE order_name = 'a';

//使用union组合,自动去重(不想去重可使用UNION ALL)
SELECT order_id, order_price, order_name
FROM order1
WHERE order_price < 10
UNION
SELECT order_id, order_price, order_name
FROM order1
WHERE order_name = 'a'
ORDER BY order_id, order_price;//只能有一个ORDER子句

9.插入、更新和删除数据

//插入数据
INSERT INTO customers(cust_name,cust_id,...)
VALUES('wang',null，....);

//更新数据
UPDATE customers
SET cust_name = 'haha',
    cust_address = 'sz',
WHERE cust_id = 12;

//删除数据（删除行）
DELETE FROM customers 
WHERE cust_id = 12;

10.使用视图

视图(view)：从一个或几个基本表中根据用户需要而做成一个虚表。

视图是虚表,它在存储时只存储视图的定义,而没有存储对应的数据
视图只在刚刚打开的一瞬间,通过定义从基表中搜集数据,并展现给用户

--创建视图--
create or replace view v_student as select * from student;
--从视图中检索数据--
select * from v_student;
--删除视图--
drop view v_student;

可根据以下例子来理解视图的使用：

//建立显示年龄大于20岁的学生学号、姓名、性别等信息的视图v_stu

create view v_stu
as  
select sno,sname,sex 
from s 
where age>20  ;


//创建视图v_score，要求基本表来源：S,C,SC；
//选择的字段为：S表中的sno、sname；C表中的cname及SC表中score；
//要求查询的数据为学号为20030001的学生的考试成绩。
 
create view v_score 
As  
Select s.sno,s.sname,c.cname,sc.score  
From s,c,sc  
Where s.sno=sc.sno and c.cno=sc.cno and sno=20030001;

参考文章：

《MySQL必知必会》

https://segmentfault.com/a/1190000019619667#articleHeader0

https://zhuanlan.zhihu.com/p/60031703