数据库+MYSQL

数据库

数据库概述

组成

数据库系统DBS：由数据库DB，数据库管理系统DBMS，数据库管理员DBA，和应用程序组成

数据库是长期存储在计算机中、有组织的、可共享的数据集合。

DBMS 表示（DataBase Management System），它是位于（用户）和（操作系统）之间的一层数据管理软件。

数据库系统三级模式结构是指模式、内模式和外模式。

功能

数据库管理系统的主要功能包括：

1. 数据定义功能，

2. 数据的组织、存储和管理，

3. 数据操纵功能，

4. 数据库的事务管理与运行管理，

5. 数据库的建立与维护，

6. 其它功能。

数据模型

数据模型的三要素包括（数据结构）、（数据操作）和（数据完整性约束条件）三部分。

实体之间的联系可抽象为三类，它们是（一对一）、（一对多）和（多对多）。

根据数据模型的应用目的不同，数据模型分为（概念模型）、（逻辑模型）和（物理模型）等。

按照数据结构的类型命名，逻辑模型分为（关系模型）、（层次模型）和（网状模型），面向对象模型等。

E-R 图中，（矩形）表示实体，（椭圆）表示属性，（菱形）表示实体之间的联系。

数据操作

数据库中数据操作包括插入、修改、删除和查询。

安全完整

1. 数据库的安全性是指保护数据库，防止因用户非法使用数据库造成数据泄露、更改或破坏。

2. 数据库系统中常用的安全控制方法有技术包括用户标识和鉴别、存取控制、定义视图、审计、数据加密等。

3. 数据完整性是指数据库中的数据在逻辑上的一致性和正确性。

4. 关系数据库的完整性包括实体完整性、参照完整性和用户定义完整性三类。

5. 触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊存储过程。触发器由对表的插入、修改和删除事件激活。

关系模型

关系模型由关系数据结构、关系操作和关系的完整性约束条件三部分组成

关系的完整性约束包括主键约束、外键约束和用户定义的完整性约束三种

关系代数

关系代数中的查询包括关系的并、交、差、笛卡儿积、选择、投影、连接和除法等运算

SQL

SQL 是什么？

• SQL 指结构化查询语言，全称是 Structured Query Language。

• SQL 让用户可以访问和处理数据库，包括数据插入、查询、更新和删除。

• SQL 在1986年成为 ANSI（American National Standards Institute 美国国家标准化组织）的一项标准，在 1987 年成为国际标准化组织（ISO）标准。

SQL 能做什么？

• SQL 面向数据库执行查询

• SQL 可从数据库取回数据

• SQL 可在数据库中插入新的记录

• SQL 可更新数据库中的数据

• SQL 可从数据库删除记录

• SQL 可创建新数据库

• SQL 可在数据库中创建新表

• SQL 可在数据库中创建存储过程

• SQL 可在数据库中创建视图

• SQL 可以设置表、存储过程和视图的权限

在网站中使用 SQL

要创建一个显示数据库中数据的网站，需要：

• RDBMS 数据库程序（比如 MS Access、SQL Server、MySQL）

• 使用服务器端脚本语言，比如 PHP 或 ASP

• 使用 SQL 来获取想要的数据

• 使用 HTML / CSS

RDBMS

RDBMS 指关系型数据库管理系统，全称 Relational Database Management System。

RDBMS 是 SQL 的基础，同样也是所有现代数据库系统的基础，比如 MS SQL Server、IBM DB2、Oracle、MySQL 以及 Microsoft Access。

RDBMS 中的数据存储在被称为表的数据库对象中。

表是相关的数据项的集合，它由列和行组成。

SQL 语法

1. SQL对大小写不敏感。

2. 在 SQL 的查询语句中，使用（FROM）子句数据源，使用（WHERE）子句选择参与运算的数据行，使用（SELECT）子句指定目标列。可使用（GROUP BY）子句结合聚集函数进行分组统计。若对查询结果排序可使用（ORDER BY）子句。

3. 在 SQL 中，（CREATE TBLE）语句创建表和声明完整性约束条件。如果要为一个基本表增加列和完整性约束条件，应该使用（ALTER TABLE）语句。删除表的定义及表中的数据和索引，应该使用的语句是（DROP TABLE）

4. 视图是定义在（基本表）之上的虚表，对视图也可以进行插入、修改、删除和查询操作。但视图不存储数据，对视图的一切操作最终要转换为对（基本表）的操作。

5. SQL 语言集数据查询、数据操纵、数据定义和数据控功能于一体，其主要的操作语句包括 CREATE、ALTER、DROP、INSERT、PDATE、DELETE、SELECT、GRANT、REVOKE 等。

6. SQL 中提供了一些聚集函数，用于统计数据库数据，包括 COUNT、AVG、SUM、MAX、MIN 等，除 COUNT（*）用法外，统计时均忽略空值，使用 DISTINCT 选项可忽略对重复数据的统计。聚集行数只可用于 SELECT 子句和 HAVING 子句。

7. 若一个查询同时涉及到两个表，则称之为连接查询。连接查询的连接条件可在WHERE 子句中通过一个比较运算指定，或在 FROM 子句中在指定连接方式的同时指定连接条件。

8. 子查询又称嵌套查询，是嵌套在 SELECT、INSERT、UPDATE、DELETE 等语句中的 SELECT 查询语句。多用在 WHERE 子句中，使用子查询的结果作为搜索条件。在 Where子句中使用子查询有以下几种方式： 由 IN 引出子查询、由比较运算符引出子查询、由EXISTS 引出子查询。

SQL语句分类

• DDL：数据定义语句 create 数据库、表

• DML：数据操作语言 增加insert 修改update 删除delete

• DQL：数据查询语言 select

• DCL：数据控制语言 管理数据库，例如 授权grant 收回权限revoke

一些最重要的 SQL 命令

• SELECT - 从数据库中提取数据

• UPDATE - 更新数据库中的数据

• DELETE - 从数据库中删除数据

• INSERT INTO - 向数据库中插入新数据

• CREATE DATABASE - 创建新数据库

• ALTER DATABASE - 修改数据库

• CREATE TABLE - 创建新表

• ALTER TABLE - 变更（改变）数据库表

• DROP TABLE - 删除表

• CREATE INDEX - 创建索引（搜索键）

• DROP INDEX - 删除索引

SQL中的注释语句

一、单行注释

在SQL中，单行注释使用双短横线（--）来表示。在双短横线后面的文本将被视为注释，直到该行结束。例如：

SELECT column1, column2 -- 这是一条注释

FROM table1;

二、多行注释

在SQL中，多行注释使用“/”和“/”来包裹注释内容。所有在这对符号之间的文本都将被视为注释，可以跨越多行。例如：

/*

这是一条多行注释。

它可以跨越多行。

*/

SELECT column1, column2

FROM table1;

三、文档注释

需要注意的是，注释语句不会被SQL服务器执行，也不会对查询结果产生任何影响。注释语句只是用于方便人类读取和理解代码。

除了单行注释和多行注释，还有一种特殊的注释语法是文档注释，它用于生成文档或帮助文档。

文档注释通常用于描述函数、存储过程和触发器等SQL对象。在SQL Server和Oracle等一些数据库管理系统中，可以使用特定的标记语法来编写文档注释。例如，SQL Server支持以下标记：

• -- 或 /* */：用于单行或多行注释

• --< 和 -->：用于描述参数或返回值

• --@ 或 --:：用于描述变量或参数

• --<summary>：用于描述函数或存储过程的概要信息

• --<remarks>：用于描述函数或存储过程的详细信息

———————————————————————————————————————————

这样的文档注释可以帮助开发人员更快速地了解该存储过程的作用和参数说明，并且还可以用于生成文档或帮助文档。

除了上述常见的注释语句外，还有一些其他类型的注释语法，具体取决于所使用的数据库管理系统和开发工具。

例如，在MySQL中，可以使用“#”符号表示单行注释。在PostgreSQL中，可以使用“--”表示单行注释，使用“/* /”表示多行注释。在SQL Server中，可以使用“--”或“/ */”表示单行或多行注释。

在一些开发工具中，也可以使用特定的快捷键或命令来添加注释。例如，在SQL Server Management Studio中，可以使用“Ctrl + K，Ctrl + C”来添加单行注释，“Ctrl + K，Ctrl + U”来取消单行注释，使用“Ctrl + Shift + /”来添加或删除多行注释。

总之，无论使用哪种注释语法，注释都是一项非常重要的开发技巧，可以提高代码的可读性和可维护性，让开发人员更好地理解和修改代码。

使用实例

SELECT name,country FROM Websites;--从 "Websites" 表中选取 "name" 和 "country" 列
​
SELECT * FROM Websites;--从 "Websites" 表中选取所有列
​
/*
SQL SELECT DISTINCT 语句
在表中，一个列可能会包含多个重复值，有时希望仅仅列出不同（distinct）的值。
​
DISTINCT 关键词用于返回唯一不同的值,也就是用于删除重复列
*/
​
SELECT DISTINCT country FROM Websites;
--仅从 "Websites" 表的 "country" 列中选取唯一不同的值，也就是去掉 "country" 列重复值
​
/*
WHERE 子句用于提取那些满足指定条件的记录。
​
相当于关系代数中的投影π。
​
where子句中还可以包含比较运算符，like，in，between
​
where字句中不能包含聚集函数。
​
注意：区分where子句和group by中的having子句
*/
​
SELECT * FROM Websites WHERE country='CN';--从 "Websites" 表中选取国家为 "CN" 的所有网站
​
/*
SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。
​
在上个实例中 'CN' 文本字段使用了单引号。
​
如果是数值字段，请不要使用引号。
*/
​
SELECT * FROM Websites WHERE id=1;
​
/*
AND & OR 运算符用于基于一个以上的条件对记录进行过滤。
且    或
*/
​
SELECT * FROM Websites
WHERE country='CN'
AND alexa > 50;
​
SELECT * FROM Websites
WHERE country='USA'
OR country='CN';
​
/*
也可以把 AND 和 OR 结合起来（使用圆括号来组成复杂的表达式）。
*/
​
SELECT * FROM Websites
WHERE alexa > 15
AND (country='CN' OR country='USA');
​
/*
ORDER BY 关键字用于对结果集进行排序。
​
ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，使用 DESC 关键字。
*/
​
SELECT * FROM Websites
ORDER BY alexa;   --从 "Websites" 表中选取所有网站，并按照 "alexa" 列排序
​
SELECT * FROM Websites
ORDER BY alexa DESC;  --从 "Websites" 表中选取所有网站，并按照 "alexa" 列降序排序
​
/*
ORDER BY 多列的时候，先按照第一个column name排序，在按照第二个column name排序
 1）、先将country值这一列排序，同为CN的排前面，同属USA的排后面；
 2）、然后在同属CN的这些多行数据中，再根据alexa值的大小排列。
 3）、ORDER BY 排列时，不写明ASC DESC的时候，默认是ASC。
​
desc 或者 asc 只对它紧跟着的第一个列名有效，其他不受影响:
​
order by A,B        这个时候都是默认按升序排列
order by A desc,B   这个时候 A 降序，B 升序排列
order by A ,B desc  这个时候 A 升序，B 降序排列
*/
​
SELECT * FROM Websites
ORDER BY country,alexa;
​
/*
INSERT INTO 语句用于向表中插入新记录。
*/
​
INSERT INTO Websites (name, url, alexa, country)
VALUES ('百度','https://www.baidu.com/','4','CN');
​
----也可以在指定的列插入数据-----
​
INSERT INTO Websites (name, url, country)
VALUES ('stackoverflow', 'http://stackoverflow.com/', 'IND');
​
/*
UPDATE 语句用于更新表中已存在的记录
​
请注意 SQL UPDATE 语句中的 WHERE 子句!!!!!!!!!!!!!!!!!!!
WHERE 子句规定哪条记录或者哪些记录需要更新。如果省略了 WHERE 子句，所有的记录都将被更新！
执行没有 WHERE 子句的 UPDATE 要慎重，再慎重。
*/
​
UPDATE Websites 
SET alexa='5000', country='USA' 
WHERE name='菜鸟教程';
​
/*
DELETE 语句用于删除表中的行。一行就是一个记录。
*/
​
DELETE FROM Websites
WHERE name='Facebook' AND country='USA';  --从 "Websites" 表中删除网站名为 "Facebook" 且国家为 USA 的网站
​
/*
可以在不删除表的情况下，删除表中所有的行。这意味着表结构、属性、索引将保持不变
​
在删除记录时要格外小心！因为不能重来！
*/
​
DELETE FROM table_name;
​
/*
limit 子句用于规定要返回的记录的数目,mysql使用limit来选取指定的条数数据
*/
​
--语法
SELECT column_name(s)
FROM table_name
LIMIT number;
​
--实例
SELECT * FROM Websites LIMIT 2;--从 "Websites" 表中选取头两条记录
​
/*
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
​
通配符：% 代表任意多个字符（可以是 0 个），_ 代表一个字符，__ 代表两个字符
转义字符：\
*/
​
SELECT * FROM Websites
WHERE name LIKE '%k';--选取 name 以字母 "k" 结尾的所有客户
​
SELECT * FROM Websites
WHERE name LIKE '_oogle';--选取 name 以一个任意字符开始，然后是 "oogle" 的所有客户
​
/*
MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。
*/
​
SELECT * FROM Websites
WHERE name REGEXP '^[GFs]';--选取 name 以 "G"、"F" 或 "s" 开始的所有网站
​
SELECT * FROM Websites
WHERE name REGEXP '^[A-H]';--选取 name 以 A 到 H 字母开头的网站
​
SELECT * FROM Websites
WHERE name REGEXP '^[^A-H]';--选取 name 不以 A 到 H 字母开头的网站
​
/*
IN 操作符允许在 WHERE 子句中规定多个值。
*/
​
SELECT * FROM Websites
WHERE name IN ('Google','菜鸟教程');--选取 name 为 "Google" 或 "菜鸟教程" 的所有网站
​
/*
BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。
*/
​
SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20;--选取 alexa 介于 1 和 20 之间的所有网站
​
​
SELECT * FROM Websites
WHERE alexa NOT BETWEEN 1 AND 20;--如需显示不在上面实例范围内的网站，请使用 NOT BETWEEN
​
SELECT * FROM Websites
WHERE (alexa BETWEEN 1 AND 20)
AND country NOT IN ('USA', 'IND');--选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站
​
SELECT * FROM Websites
WHERE name BETWEEN 'A' AND 'H';--选取 name 以介于 'A' 和 'H' 之间字母开始的所有网站
​
SELECT * FROM access_log
WHERE date BETWEEN '2016-05-10' AND '2016-05-14';
--选取 date 介于 '2016-05-10' 和 '2016-05-14' 之间的所有访问记录
​
/*
as 可以为表名称或列名称指定别名
​
提示：如果列名称包含空格，要求使用双引号或方括号
*/
​
SELECT name AS n, country AS c
FROM Websites;--指定了两个别名，一个是 name 列的别名，一个是 country 列的别名
​
/*
JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段
​
相当于关系代数中的连接，此处注意外连接（左外连接，右外连接，全连接），内连接和悬浮元组的关系
​
*/
​
SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;
​
/*
left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL
​
right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL
​
inner join: 内连接，又叫等值连接，只返回两个表中连接字段相等的行。在表中存在至少一个匹配时返回行
​
full join:外连接，返回两个表中的行：left join + right join。只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。类似于全集。MySQL中不支持 FULL OUTER JOIN
​
cross join:结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。
*/
​
/*
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表(悬浮元组)，然后再将这张临时表返回给用户。
​
在使用left jion时，on和where条件的区别如下：
​
① on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
​
② where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
*/
​
/*
UNION 操作符合并两个或多个 SELECT 语句的结果
​
注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同
*/
​
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;       --从 "Websites" 和 "apps" 表中选取所有不同的country（只有不同的值）
​
/*
UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。
​
UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！
*/
​
SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;    --从 "Websites" 和 "apps" 表中选取所有的country（也有重复的值）
​
​
/*
带有 WHERE 的 SQL UNION ALL
*/
​
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;        --从 "Websites" 和 "apps" 表中选取所有的中国(CN)的数据（也有重复的值）
​
/*
INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。
*/
​
INSERT INTO table2
SELECT * FROM table1;--可以从一个表中复制所有的列插入到另一个已存在的表中
​
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;       --或者可以只复制指定的列插入到另一个已存在的表中
​
/*
CREATE DATABASE 语句用于创建数据库。
*/
​
CREATE DATABASE my_db;--创建一个名为 "my_db" 的数据库
​
/*
CREATE TABLE 语句用于创建数据库中的表
*/
​
-------语法
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

SQL 约束（Constraints）

• NOT NULL - 指示某列不能存储 NULL 值。

• UNIQUE - 保证某列的每行必须有唯一的值。

• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

• CHECK - 保证列中的值符合指定的条件。

• DEFAULT - 规定没有给列赋值时的默认值。

SQL中的约束和数据库的完整性有关，数据库的完整性有四类：

• 实体完整性（主键完整性）

• 参照完整性（外键完整性）

• 用户定义完整性

• 域完整性

要求：

1. 主键非空且唯一

2. 外键可以为空，但是必须以参照的主键为域

3. 主键在定义表时必须出现，如果只有一个主键就跟在属性名之后，用空格分开

   如果是联合主键则写在表的最后，用小括号括住联合的属性名

4. 外键格式：

   FOREIGN KEY (Cno) REFERENCES C(Cno)

5. 注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

示例：

------------前面几个都是 SQL Server 的语法--------------------谁让学校教的是介个捏------------
CREATE TABLE S     
​
 (  Sno  CHAR(8) PRIMARY KEY, /*主码约束*/         
​
   Sname CHAR(20) UNIQUE,   /*唯一约束*/
​
   Ssex  CHAR(2) NOT NULL,  /*非空约束*/
​
   Sage  SMALLINT,
​
   Sdept CHAR(20)   
​
);  
​
CREATE TABLE C
​
(  Cno   CHAR(4) PRIMARY KEY, /*主码约束*/ 
​
   Cname  CHAR(40) NOT NULL,      
​
   Teacher CHAR(8) ,                   
​
); 
​
CREATE TABLE SC
​
(  Sno  CHAR(8) REFERENCES S(Sno),  /*外码约束*/
​
   Cno  CHAR(4), 
​
   Score SMALLINT CHECK(Score<=100),  /*检查约束*/
​
   PRIMARY KEY (Sno,Cno),       /*主码约束*/ 
​
   FOREIGN KEY (Cno) REFERENCES C(Cno) /*外码约束*/
​
  ); 
  
----------MySQL的主键约束语法不一样------------------------------------------------------
​
CREATE TABLE Persons
​
(P_Id int NOT NULL,
    
LastName varchar(255) NOT NULL,
    
FirstName varchar(255),
    
Address varchar(255),
    
City varchar(255),
    
PRIMARY KEY (P_Id)           /*主码约束*/
)
​
--------- check也不一样-----------------啊啊啊啊啊--------------------------------------
​
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)      /*检查约束*/
)
​
-------------- default是一样滴，其他约束也都是一样滴--------------------------------------
CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) DEFAULT 'Sandnes'     /*默认值*/
)
​
-----------------------MySQL喜欢把约束都写到数据的后面-----------------------------------

添加约束

1. 在一个已创建的表的 "Age" 字段中添加 NOT NULL 约束如下所示：

   ALTER TABLE Persons MODIFY Age int NOT NULL;

   在一个已创建的表的 "Age" 字段中删除 NOT NULL 约束如下所示：

   ALTER TABLE Persons MODIFY Age int NULL;

2. 当表已被创建时，如需在 "P_Id" 列创建 UNIQUE 约束，请使用下面的 SQL：

   MySQL / SQL Server / Oracle / MS Access：

   ALTER TABLE Persons ADD UNIQUE (P_Id)

   如需撤销 UNIQUE 约束，请使用下面的 SQL：

   MySQL：

   ALTER TABLE Persons DROP INDEX uc_PersonID

3. 当表已被创建时，如需在 "P_Id" 列创建 PRIMARY KEY 约束，请使用下面的 SQL：

   MySQL / SQL Server / Oracle / MS Access：

   ALTER TABLE Persons ADD PRIMARY KEY (P_Id)

   如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：

   MySQL / SQL Server / Oracle / MS Access：

   ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

   如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：

   MySQL：

   ALTER TABLE Persons DROP PRIMARY KEY

4. 当 "Orders" 表已被创建时，如需在 "P_Id" 列创建 FOREIGN KEY 约束，请使用下面的 SQL：

   MySQL / SQL Server / Oracle / MS Access：

   ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

   如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的 SQL 语法：

   MySQL / SQL Server / Oracle / MS Access：

   ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)*

   如需撤销 FOREIGN KEY 约束，请使用下面的 SQL：

   MySQL：

   ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders

5. 当表已被创建时，如需在 "P_Id" 列创建 CHECK 约束，请使用下面的 SQL：

   MySQL / SQL Server / Oracle / MS Access:

   ALTER TABLE Persons ADD CHECK (P_Id>0)

   如需命名 CHECK 约束，并定义多个列的 CHECK 约束，请使用下面的 SQL 语法：

   MySQL / SQL Server / Oracle / MS Access：

   ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

   如需撤销 CHECK 约束，请使用下面的 SQL：

   MySQL：

   ALTER TABLE Persons DROP CHECK chk_Person

6. 通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：

   CREATE TABLE Orders
   (
       O_Id int NOT NULL,
       OrderNo int NOT NULL,
       P_Id int,
       OrderDate date DEFAULT GETDATE()
   )

当表已被创建时，如需在 "City" 列创建 DEFAULT 约束，请使用下面的 SQL：

MySQL：

ALTER TABLE Persons ​ ALTER City SET DEFAULT 'SANDNES'

如需撤销 DEFAULT 约束，请使用下面的 SQL：

MySQL：

ALTER TABLE Persons ​ ALTER City DROP DEFAULT

索引

CREATE INDEX 语句用于在表中创建索引。

在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。

用户无法看到索引，它们只能被用来加速搜索/查询。

注意：更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

• SQL CREATE INDEX 语法

在表上创建一个简单的索引。允许使用重复的值：

CREATE INDEX index_name
ON table_name (column_name)

• SQL CREATE UNIQUE INDEX 语法

在表上创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

SQL 撤销索引、撤销表以及撤销数据库

• 用于 MS SQL Server 的 DROP INDEX 语法：

DROP INDEX table_name.index_name

• 用于 MySQL 的 DROP INDEX 语法：

ALTER TABLE table_name DROP INDEX index_name

• DROP TABLE 语句用于删除表。

DROP TABLE table_name

• DROP DATABASE 语句用于删除数据库。

  DROP DATABASE database_name

• TRUNCATE TABLE 语句仅仅需要删除表内的数据，但并不删除表本身。

  TRUNCATE TABLE table_name

ALTER TABLE 语句

ALTER TABLE 语句用于在已有的表中添加、删除或修改列。

• 如需在表中添加列，请使用下面的语法:

ALTER TABLE table_name ADD column_name datatype

• 如需删除表中的列，请使用下面的语法（请注意，某些数据库系统不允许这种在数据库表中删除列的方式）：

ALTER TABLE table_name DROP COLUMN column_name

• 要改变表中列的数据类型，请使用下面的语法：

My SQL / Oracle：

ALTER TABLE table_name MODIFY COLUMN column_name datatype

SQL AUTO INCREMENT 字段

Auto-increment 会在新记录插入表中时生成一个唯一的数字。

我们通常希望在每次插入新记录时，自动地创建主键字段的值。

我们可以在表中创建一个 auto-increment 字段。

• 用于 MySQL 的语法

下面的 SQL 语句把 "Persons" 表中的 "ID" 列定义为 auto-increment 主键字段：

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。

默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。

要让 AUTO_INCREMENT 序列以其他的值起始，请使用下面的 SQL 语法：

ALTER TABLE Persons AUTO_INCREMENT=100

要在 "Persons" 表中插入新记录，我们不必为 "ID" 列规定值（会自动添加一个唯一的值）：

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

上面的 SQL 语句会在 "Persons" 表中插入一条新记录。"ID" 列会被赋予一个唯一的值。"FirstName" 列会被设置为 "Lars"，"LastName" 列会被设置为 "Monsen"。

SQL 视图（Views）

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。

可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。

注意：视图中只有基本表的定义而不存放真正的数据。

• SQL CREATE VIEW 语法

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

注意：视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。

• 可以通过 DROP VIEW 命令来删除视图。

• 更新视图

SQL 日期（Dates）

当我们处理日期时，最难的任务恐怕是确保所插入的日期的格式，与数据库中日期列的格式相匹配。

只要数据包含的只是日期部分，运行查询就不会出问题。但是，如果涉及时间部分，情况就有点复杂了。

在讨论日期查询的复杂性之前，先来看看最重要的内建日期处理函数。

MySQL Date 函数

SQL Server Date 函数

下面的表格列出了 SQL Server 中最重要的内建日期函数：

SQL Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式：YYYY-MM-DD

• DATETIME - 格式：YYYY-MM-DD HH:MM:SS

• TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS

• YEAR - 格式：YYYY 或 YY

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式：YYYY-MM-DD

• DATETIME - 格式：YYYY-MM-DD HH:MM:SS

• SMALLDATETIME - 格式：YYYY-MM-DD HH:MM:SS

• TIMESTAMP - 格式：唯一的数字

注意：当在数据库中创建一个新表时，需要为列选择数据类型！

SQL NULL 值

如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。

NULL 值的处理方式与其他值不同。

NULL 用作未知的或不适用的值的占位符。

！注意：无法比较 NULL 和 0；它们是不等价的。

SQL 的 NULL 值处理

无法使用比较运算符来测试 NULL 值，比如 =、< 或 <>。

我们必须使用 IS NULL 和 IS NOT NULL 操作符。

数据库建表的时候默认是 NULL，但在工作中一般建表的时候都会禁止使用 NULL 的！

NULL 表示的是什么都没有，它与空字符串、0 这些是不等价的，是不能用于比较的！ 如： <expr> = NULL 、 NULL = '' 得到的结果为 false，判断 NULL 必须使用 IS NULL 或 IS NOT NULL 进行判断。

• 为什么工作中不使用 NULL？

1. 不利于代码的可读性和可维护性，特别是强类型语言，查询 INT 值，结果得到一个 NULL，程序可能会奔溃...如果要兼容这些情况程序往往需要多做很多操作来兜底

2. 若所在列存在 NULL 值，会影响 count()、 <col> != <value>、 NULL + 1 等查询、统计、运算情景的结果

SQL NULL 函数

SQL Server / MS Access

SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products

Oracle

Oracle 没有 ISNULL() 函数。不过，我们可以使用 NVL() 函数达到相同的结果：

SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))
FROM Products

MySQL

MySQL 也拥有类似 ISNULL() 的函数。不过它的工作方式与微软的 ISNULL() 函数有点不同。

在 MySQL 中，我们可以使用 IFNULL() 函数，如下所示：

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products

或者我们可以使用 COALESCE() 函数，如下所示：

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products

MySQL

MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

• 1.数据以表格的形式出现

• 2.每行为各种记录名称

• 3.每列为记录名称所对应的数据域

• 4.许多的行和列组成一张表单

• 5.若干的表单组成database

RDBMS 术语

• 数据库: 数据库是一些关联表的集合。

• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。

• 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。

• 行：一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。在java程序中，一条记录往往是一个对象。

• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。

• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。

• 外键：外键用于关联两个表。

• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。

• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

• 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySQL数据库

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

• MySQL 是开源的，目前隶属于 Oracle 旗下产品。

• MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。

• MySQL 使用标准的 SQL 数据语言形式。

• MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。

• MySQL 对 PHP 有很好的支持，PHP 是很适合用于 Web 程序开发。

• MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。

• MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

MySQL PHP 语法

MySQL 可应用于多种语言，包括 PERL, C, C++, JAVA 和 PHP，在这些语言中，MySQL 在 PHP 的 web 开发中是应用最广泛。

PHP 提供了多种方式来访问和操作 MySQL 数据库记录。：

PHP MySQL 函数格式如下：

mysqli_function(value,value,...);

以上格式中 function部分描述了mysql函数的功能，如

mysqli_connect($connect);
mysqli_query($connect,"SQL 语句");
mysqli_fetch_array()
mysqli_close()

以下实例展示了PHP调用mysql函数的语法：

<?php
$retval = mysqli_function(value, [value,...]);
if( !$retval )
{
   die ( "相关错误信息" );
}
// 其他 MySQL 或 PHP 语句
?>

安装MySQL5.7.9 创建my.ini文件，先跳过安全检测

使用管理员身份打开DOS窗口

安装SQLyog，

MySQL三层结构

安装数据库实际上是安装DBMS，一个管理系统可以管理多个数据库，一个数据库中有多张表、视图、触发器，用来存放数据。

结构：

DBMS------------->数据库1、数据库2...........------------->表1、表2........

客户端连接时连接的是DBMS的端口，在3306端口监听端口完成服务

数据库在data目录下面

数据库的本质就是一个文件，数据要持久化，所以要放到磁盘里

使用 PHP 脚本连接 MySQL

mysqli_connect()

PHP 提供了 mysqli_connect() 函数来连接数据库。

该函数有 6 个参数，在成功链接到 MySQL 后返回连接标识，失败返回 FALSE 。

语法

mysqli_connect(host, username, password, dbname,port, socket);

参数说明：

mysqli_close()

可以使用 PHP 的 mysqli_close() 函数来断开与 MySQL 数据库的链接。

该函数只有一个参数为 mysqli_connect() 函数创建连接成功后返回的 MySQL 连接标识符。

语法

bool mysqli_close ( mysqli $link )

本函数关闭指定的连接标识所关联的到 MySQL 服务器的非持久连接。如果没有指定 link_identifier，则关闭上一个打开的连接。

提示：通常不需要使用 mysqli_close()，因为已打开的非持久连接会在脚本执行完毕后自动关闭。

JAVA操作MySQL

创建一个表，选用适当的数据类型，添加数据，删除表

数据库操作

创建数据库

• 创建一个数据库

  create database 库名称

• 指定utf-8字符集的数据库

  create database character set utf8

• 指定utf-8字符集的数据库并且带有校对规则的数据库（区分大小写）

  create database character set utf8 collate utf8_bin

校对规则： utf8_bin区分大小写，默认 utf8_general_ci 不区分大小写

在创建（删除）数据库、表的时候，为了规避关键字，可以使用反引号，带上反引号更安全

例如：create database ``

反引号在esc和tab键之间

删除数据库

drop database 库名称

一定要慎用，没有备份就只能跑路了

查询数据库

• 显示数据库语句

  show databases

• 显示数据库创建语句

  show create database db_name

备份和恢复数据库

• 备份数据库（在DOS命令行里）mysqldump指令在MySQL安装目录\bin下

  mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql

  如果要管理员身份用户名就是root

  导出的备份实际上是对应的SQL语句

• 恢复数据库（进入MySQL命令行再执行）

  • 先 mysql -u root -p 回车，输入密码 回车后执行

  source 文件名.sql

  • 直接把之前的备份复制到查询界面后执行

• 备份库的表

  mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > d:\\文件名.sql

创建表

#id 整形 [图形化，指令]
#name 字符串
#password 字符串
#birthday 日期
CREATE TABLE `user` (
id INT, 
`name` VARCHAR(255),
`password` VARCHAR(255), 
`birthday` DATE)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;#数据引擎

MySQL常用数据类型

列类型

日期类型

增强查询

查询加强

日期类型是可以参与比较的

-- 查询加强
-- ■ 使用 where 子句
-- ?如何查找 1992.1.1 后入职的员工
-- 说明： 在 mysql 中,日期类型可以直接比较, 需要注意格式
SELECT * FROM emp
WHERE hiredate > '1992-01-01' 
​
-- ■ 如何使用 like 操作符(模糊)
-- %: 表示 0 到多个任意字符 _: 表示单个任意字符
-- ?如何显示首字符为 S 的员工姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE 'S%' 
​
-- ?如何显示第三个字符为大写 O 的所有员工的姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE '__O%'   #两个下划线
​
-- ■ 如何显示没有上级的雇员的情况
SELECT * FROM emp
WHERE mgr IS NULL; #上级为空就是没有上级，某一列为空只能用is不能用=
​
-- ■ 查询表结构
DESC emp
​
-- 使用 order by 子句
-- ?如何按照工资的从低到高的顺序[升序]，默认就是升序，显示雇员的信息
SELECT * FROM emp
ORDER BY sal [asc] #中括号是可选
​
-- ?按照部门号升序而雇员的工资降序排列 , 显示雇员信息
SELECT * FROM emp
ORDER BY deptno ASC , sal DESC;

分页查询

• 基本语法

  select ... limit start,rows

  表示从start+1取出，取出rows行，start从0开始计算

• 练习

  按雇员的 id 号升序取出， 每页显示 3 条记录，请分别显示 第 1 页，第 2 页，第 3 页

• 推导一个公式 SELECT * FROM emp ORDER BY empno LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数

-- 分页查询
-- 按雇员的 id 号升序取出， 每页显示 3 条记录，请分别显示 第 1 页，第 2 页，第 3 页
​
-- 第 1 页
SELECT * FROM emp
ORDER BY empno
LIMIT 0, 3; 
​
-- 第 2 页
SELECT * FROM emp
ORDER BY empno
LIMIT 3, 3; 
​
-- 第 3 页
SELECT * FROM emp
ORDER BY empno
LIMIT 6, 3;
​
-- 测试
SELECT job, COUNT(*) FROM emp GROUP BY job; 
-- 显示雇员总数，以及获得补助的雇员数
SELECT COUNT(*) FROM emp WHERE mgr IS NOT NULL;
SELECT MAX(sal) - MIN(sal) FROM emp;

分组查询

如果select语句同时包含有多个分组子句和分组函数，那么它们的顺序如下图，不能写错

合并查询

使用集合操作符号union ，union all,

• union all

  用于取得两个结果集的并集，不会去掉重复行

• union

  自动去掉重复行，其余作用一样

表的连接

在前面的使用实例里面有

索引

事务

什么是事务?

事务用于保证数据的一致性，由一组相关的DML语句组成，该组的DML语句要么全部成功要么全部失败（原子性 ）。

例如：银行转账是需要用户A余额的减少和用户B余额的增加同时发生，对于更新的两条语句来说，就要保障同时发生。

这里就引出一个需求，将多个DML语句当做一个整体。

事务和锁

当执行事务操作时，MySQL会在表上加锁，防止其他用户更改表的数据，这对用户来说是非常重要的。

MySQL数据库控制台事务的几个重要操作

可以给事务设置几个保存点，当想回退到先前的版本时，可以进行回滚回到之前。

前提：设置相关的保存点。

-- 事务操作的演示
​
-- 1.创建一张测试表
CREATE TABLE t27
           ( id INT,
          ` name` VARCHAR(32));
​
-- 2. 开始
START TRANSACTION            
​
-- 3. 设置保存点
SAVEPOINT a
-- 执行 DML操作
INSERT INTO t27 VALUES (100,'tom');
SELECT * FROM t27
​
SAVEPOINT b
INSERT INTO t27 VALUES (200,'jack');
SELECT * FROM t27
​
-- 回退
ROLLBACK TO b
ROLLBACK TO a
​
-- 如果直接写ROLLBACK,表示直接回退到事务开始的状态
ROLLBACK
​
-- 一旦执行了commit语句，则所有的回退都是无效的,且同时删除之前设置的所有保存点
COMMIT

回退事务

保存点是事务中的点，用于取消部分事务，当结束事务时，会自动删除该事务所定义的所有保存点。

在执行回退事务时，通过指定保存点来回退到指定的点。

一旦回退以后，被回退的事务以及保存点就会被自动删除，例如a---> b ---> c ,如果从c回退到b,则bc之间的语句和c保存点全部被删除。

提交事务

使用commit语句可以提交事务，当执行了commit语句只够，会确认事务的变化，结束事务，删除保存点释放锁，数据生效。

当使用commit语句结束事务之后，其他会话（其他连接）将可以看到事务变化后的新数据（所有数据就正式生效）。

事务注意事项

1. 如果不开始事务，在默认情况下，DML操作是自动提交的，不能回滚。

2. 如果开始一个事务，但没有创建保存点，那么执行了rollback之后默认回到事务开始时的状态。

3. 可以在事务还没有提交之前创建多个保存点，并且可以选择回退到多个保存点。

4. MySQL事务机制需要 innodb 的存储引擎才能使用，MyISAM不支持

5. 开始一个事务 start transaction 或者 set autocommit = off;

事务隔离级别

1. 多个连接开启各自事务操作数据库中数据时，数据库要负责隔离操作，以保证各个连接在获取数据时的准确性。

2. 如果不考虑隔离性，则可能发生以下问题：

   • 脏读：当一个事务读取另一个事务尚未提交的修改时，产生脏读

   • 不可重复读：同一查询在同一个事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集

   • 幻读：同一查询在同一个事务中多次进行，由于其他提交事务所做的插入，每次返回不同的结果集

3. 事务隔离级别：MySQL隔离级别定义了事务和事务之间的隔离程度。

   

设置事务的隔离级别

ACID特性

1. 原子性：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2. 一致性：事务必须使数据库从一个一致性状态转换成另一个一致性状态。

3. 隔离性：多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

4. 持久性：一个事务一旦被提交，它对数据库中的数据的改变就是永久的，接下来即使数据库发生鼓掌也不应该对其有任何影响。