PHP全站开发工程师-第2章 SQL进阶语法

PHP全站开发工程师-第1章 SQL基础语法

 

第二阶段（后端） 25天 144学时

第2章 SQL进阶语法（12）

[学习课时] 本章共需要学习  12  课时

[目的要求] 

1. 掌握数据库概述和原理
2. 掌握SQL数据完整性
3. 掌握SQL三大范式规则
4. 掌握SQL别名的使用
5. 掌握SQL排序的方法
6. 掌握SQL分页查询的方法
7. 掌握SQL外键约束
8. 掌握SQL索引的创建和维护
9. 掌握SQL视图的使用
10. 掌握SQL事物的规则和使用方法
11. 了解SQL的内置函数

[教学内容]

数据库设计

1. RDBMS概念

RDBMS代表关系数据库管理系统。 RDBMS是SQL的基础.

 

一个关系数据库管理系统(RDBMS)是一个数据库管理系统(DBMS)

 

1. 数据完整性

数据的完整性有以下几类存在于每个RDBMS：

 

• 实体完整性: 指表中行必须有唯一的标识，没有重复行
• 域完整性: 指列的值域的完整性。如数据类型、格式、值域范围、是否允许空值等。
• 参考完整性: 用于约束外关键字和主关键字的关系，被其他记录使用的行不能被删除，
• 用户定义的完整性: 强制执行不属于实体，域和参照完整性一些具体的业务规则

 

1. 数据库规范化

 

数据库正常化是有效地组织数据库中的数据的过程。归一化处理的两个原因：

 

第一范式（1NF）

第一范式(1NF)设置了一个有组织的数据库非常基本的规则：

 

• 表结构的每个属性都是不可分的基本数据项。
  1. 1. 如：记录销售员销售的信息：销售 => 单价 + 数量

 

第二范式（2NF）

• 表结构中每一行属性只包含与一个对象【函数相关】的属性值，多个应该拆分为新的表。
  1. 1. 如：学生（学号、年龄、性别、班级编号、班级名称、班主任）

 

第二范式(Second Normal Form,2nd NF)是指每个表必须有一个(而且仅有一个)数据元素为主关键字(Primary key),其他数据元素与主关键字一一对应。通常称这种关系为函数依赖(Functional dependence)关系，即表中其他数据元素都依赖于主关键字,或称该数据元素惟一地被主关键字所标识。第二范式是数据库规范化中所使用的一种正规形式。它的规则是要求数据表里的所有非主属性都要和该数据表的主键有完全依赖关系；如果有哪些非主属性只和主键的一部份有关的话，它就不符合第二范式。

 

函数相关：如，每个学生都有自己的名字

非函数相关：如，很多个学生都是软件2班的

 

考虑客户订单的关系，你想存储客户ID，客户名称，订单ID和订单的细节，以及购买日期：

CREATE TABLE CUSTOMERS(

       CUST_ID    INT              NOT NULL,

       CUST_NAME VARCHAR (20)      NOT NULL,

       ORDER_ID   INT              NOT NULL,

       ORDER_DETAIL VARCHAR (20)  NOT NULL,

       SALE_DATE  DATETIME,

       PRIMARY KEY (CUST_ID, ORDER_ID)

);

此表是第一范式，因为它遵循了第一范式的所有规则。在该表中，主键由CUST_ID和ORDERID组成。 它们是唯一的，假设同一客户将很难订购同样的东西。

 

为了使这个表符合第二范式，需要的列分在三个表。

 

首先，创建一个表来存储客户的详细信息如下：

CREATE TABLE CUSTOMERS(

       CUST_ID    INT              NOT NULL,

       CUST_NAME VARCHAR (20)      NOT NULL,

       PRIMARY KEY (CUST_ID)

);

 

接下来，创建一个表来存储每个订单的详细信息：

CREATE TABLE ORDERS(

       ORDER_ID   INT              NOT NULL,

       ORDER_DETAIL VARCHAR (20)  NOT NULL,

       PRIMARY KEY (ORDER_ID)

);

 

最后，创建第三个表存储只是CUST_ID和ORDER_ID让所有的客户订单可以跟踪：

CREATE TABLE CUSTMERORDERS(

       CUST_ID    INT              NOT NULL,

       ORDER_ID   INT              NOT NULL,

       SALE_DATE  DATETIME,

       PRIMARY KEY (CUST_ID, ORDER_ID)

);

 

第三范式（3NF）

第三范式的表满足以下条件：

 

• 满足第二范式
• 表结构中每一行属性只都跟主键有直接关系，而非间接关系。

 

第三范式(Third Normal Form,3rd NF)就是指表中的所有数据元素不但要能惟一地被主关键字所标识,而且它们之间还必须相互独立,不存在其他的函数关系。也就是说，对于一个满足2nd NF 的数据结构来说，表中有可能存在某些数据元素依赖于其他非关键字数据元素的现象,必须消除。

 

如：

Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）

存在上述关系。 学号--> 所在院校 --> (院校地址，院校电话)

应该拆开如下：                                                                                      

（学号，姓名，年龄，性别，所在院校）--（所在院校，院校地址，院校电话）

 

 

例如，在下面的表中，街道名称，城市，国家是可绑定到邮政编码的。

CREATE TABLE CUSTOMERS(

       CUST_ID       INT              NOT NULL,

       CUST_NAME     VARCHAR (20)      NOT NULL,

       DOB           DATE,

       STREET        VARCHAR(200),

       CITY          VARCHAR(100),

       STATE         VARCHAR(100),

       ZIP           VARCHAR(12),

       EMAIL_ID      VARCHAR(256),

       PRIMARY KEY (CUST_ID)

);

邮政编码和地址之间的依赖关系称为传递依赖。为了符合第三范式，所有你需要做的是移动的街道，城市和国家领域纳入自己表中，您可以调用邮编表：

CREATE TABLE ADDRESS(

       ZIP           VARCHAR(12),

       STREET        VARCHAR(200),

       CITY          VARCHAR(100),

       STATE         VARCHAR(100),

       PRIMARY KEY (ZIP)

);

 

接下来，改变CUSTOMERS表如下：

CREATE TABLE CUSTOMERS(

       CUST_ID       INT              NOT NULL,

       CUST_NAME     VARCHAR (20)      NOT NULL,

       DOB           DATE,

       ZIP           VARCHAR(12),

       EMAIL_ID      VARCHAR(256),

       PRIMARY KEY (CUST_ID)

);

 

 

SQL别名

有如下表：

CREATE TABLE `users` (

  `id` BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',

  `nickname` VARCHAR (255) NOT NULL DEFAULT '' COMMENT '昵称',

  `name` VARCHAR (60) NOT NULL DEFAULT '' COMMENT '用户名',

  `pswd` VARCHAR (255) NOT NULL DEFAULT '' COMMENT '密码',

  `sex` BIT (1) NOT NULL DEFAULT b'1' COMMENT '1:男；0:女',

  `birthday` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '生日',

  `register` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '注册时间',

  `status` INT (10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '状态',

  PRIMARY KEY (`id`)

) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

 

查询语句：

 

SELECT nickname AS nn

FROM users AS u;

WHERE u.id=’2’;

 

 

AS可以省略不写。

排序

SELECT column_name,column_name

FROM table_name

WHERE condition

ORDER BY column_name,column_name ASC|DESC;

 

分页查询

所有的数据库都可以通过between and 语句实现分页查询

 

 

SELECT * FROM  users WHERE id BETWEEN 1 AND 2;

 

而对于MySQL数据库，还可以使用limit 来实现这个功能

 

SELECT * FROM  users LIMIT n,m;

SELECT * FROM  users LIMIT 1,2;

 

查询的结果一样：

外键

外键是用于两个表链接在一起的键。这有时被称为一个参考项。

 

外键创建条件：

 

• 使用外键约束的表必须是InnoDB引擎
• 两个字段的类型或者大小必须严格匹配，如，INT(10), INT(11),就不匹配。
• 被引用的外键必须是主键或者建立起索引。
• 外键的名字不能和其他字段名重复

 

外键的特点：

 

• 插入数据时，外键引用的列的值必须是在被引用表中已经存在的值
• 删除数据时，被外键引用的表不能被删除，除非先删除引用的表数据。

 

外键的作用：

 

• 预防破坏表之间连接的动作。
• 防止非法数据插入外键列

 

1. 语法

创建语法1：

 

CREATE TABLE table_name(

id int NOT NULL PRIMARY KEY,

f_id int FOREIGN KEY REFERENCES table_name 2(id)

);

 

创建语法2：

 

CREATE TABLE table_name (

id int NOT NULL PRIMARY KEY,

f_id int,

PRIMARY KEY (id),

FOREIGN KEY (f_id) REFERENCES table_name 2(id)

);

 

创建语法3：

ALTER TABLE table_name ADD FOREIGN KEY (f_id) REFERENCES table_name 2(id);

 

 

删除外键：

ALTER TABLE table_name DROP FOREIGN KEY fk_name;

 

 

1. 案例

创建数据库

CREATE DATABASE `testdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

 

创建班级表

CREATE TABLE class(

id INT UNSIGNED NOT NULL,

name VARCHAR(255) NOT NULL,

teacher_id INT UNSIGNED NOT NULL,

PRIMARY KEY (id)

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 

创建学生表

CREATE TABLE student (

id INT UNSIGNED NOT NULL,

name VARCHAR(60) NOT NULL,

pswd VARCHAR(255) NOT NULL,

class_id INT UNSIGNED,

PRIMARY KEY (id),

FOREIGN KEY (class_id) REFERENCES class(id)

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 

插入班级数据：

insert into class values

('1','ruanjian1','1'),

('2','ruanjian2','1'),

('3','hanyu','2'),

('4','jianzhu','3');

 

插入学生数据：

insert into student values

('1','zhangsan','123' ,'1'),

('2','lisi','123','1'),

('3','wangwu','123','2'),

('4','feifei','123','3'),

('5','haha','123','4');

 

1. 级联删除、更新

在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作。

 

包括RESTRICT、NO ACTION、SET NULL和CASCADE。

• RESTRICT -指在子表有关联记录的情况下父表不能更新；
• CASCADE - 表示父表在更新或者删除时，更新或者删除子表对应记录；
• SET NULL - 则是表示父表在更新或者删除的时候，子表的对应字段被SET NULL。
• NO ACTION – 不做任何操作，和NO ACTION相同。

 

修改学生表如下：其他表不变，插入数据不变

 

CREATE TABLE student (

id INT UNSIGNED NOT NULL,

name VARCHAR(60) NOT NULL,

pswd VARCHAR(255) NOT NULL,

class_id INT UNSIGNED,

PRIMARY KEY (id),

FOREIGN KEY (class_id) REFERENCES class(id) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 

执行删除操作：

 

 

删除后查询：

 

索引

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

 

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

 

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

 

1. 查看索引

mysql> SHOW INDEX FROM tblname;

OR

mysql> SHOW INDEX FROM tblname;

 

• Table：表的名称。
• Non_unique：如果索引不能包括重复词，则为0。如果可以，则为1。
• Key_name：索引的名称。
• Seq_in_index：索引中的列序列号，从1开始。
• Column_name：列名称。
• Collation：列以什么方式存储在索引中。在MySQL中：‘A’（升序）或NULL（无分类）。
• Cardinality：索引中唯一值的数目的估计值。
• Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
• Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。
• Null：如果列含有NULL，则含有YES。如果没有，则该列含有NO。
• Index_type：用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

 

1. 简单的索引

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

 

CREATE INDEX index_name ON tablename (columnname)

 

创建表的时候同时创建索引

 

CREATE TABLE `tablename` ( 

`id` int(11) NOT NULL AUTO_INCREMENT , 

`title` char(255) NOT NULL , 

INDEX indexname (title(20)) 

) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

 

 

注：Key和index的区别

 

• Key：包含两层意义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等
• Index：是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；
    因此，索引只是索引，它不会去约束索引的字段的行为（那是key要做的事情）。

 

1. 短索引

CREATE INDEX index_name ON tablename (title(20))

 

INDEX indexname (title(20))：将title前面20个字符作为索引

 

1. 唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

 

好处：

• 一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；
• 二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。

 

创建唯一索引

 

CREATE UNIQUE INDEX index_name ON table_name(column_name) 

 

创建表的时候直接指定 

 

CREATE TABLE `table_name` ( 

`id` int(11) NOT NULL AUTO_INCREMENT , 

`title` char(255) NOT NULL , 

UNIQUE index_name (title) 

); 

 

1. 主索引（唯一）

必须为主键字段创建一个索引，这个索引就是所谓的"主索引"。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。但是包含UNIQUE的特征

 

创建主索引

 

CREATE PRIMARY INDEX index_name ON table_name(column_name) 

 

创建表的时候直接指定 

 

CREATE TABLE `table_name` ( 

`id` int(11) NOT NULL AUTO_INCREMENT , 

`title` char(255) NOT NULL , 

PRIMARY KEY (`id`), 

); 

 

1. 复合索引

复合索引就是使用多个列作为索引，当某一个列出现相同值的时候，使用另一个列来检索，因此，作为复合索引的多个列的组合一定是不能重复的，如班级和姓名一起作为复合主键。

 

KEY (`role_id`,`permission_id`)

 

UNIQUE KEY (`role_id`,`permission_id`)

 

INDEX (`role_id`,`permission_id`)

 

UNIQUE INDEX (`role_id`,`permission_id`)

 

1. 全文索引

文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。

 

这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。

 

CREATE FULLTEXT INDEX index_name ON table_name (column_name)

 

也可以在创建索引的时候指定索引的长度：

 

CREATE FULLTEXT INDEX index_name ON table_name (column_name)

 

CREATE TABLE article (

id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

body TEXT,

FULLTEXT(title, body)

) ENGINE =MYISAM;

 

视图

视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义的查询select语句组成。视图是基于 SQL 语句的结果集的可视化的表

 

• 视图中的数据并不属于视图本身，而是属于基本的表，对视图可以像表一样进行insert,update,delete操作。
• 视图不能被修改，表修改或者删除后应该删除视图再重建。
• 视图的数量没有限制，但是命名不能和视图以及表重复，具有唯一性。
• 视图可以被嵌套，一个视图中可以嵌套另一个视图。
• 视图不能索引，不能有相关联的触发器和默认值

 

1. 语法

CREATE VIEW view_name AS

SELECT column_name1, column_name2

FROM table_name

WHERE condition

 

1. 案例

创建用户表：

 

create table user(

id int(10) unsigned primary key auto_increment,

name varchar(60) unique not null,

pswd varchar(255)not null,

class_id int(10)

);

 

插入用户数据：

 

insert into user (name,pswd class_id) values

 ('zhangsan','123456','1'),

 ('lishi','123456','1'),

 ('wangwu','123456','2');

 

创建班级表：

 

create table class (

id int(10) unsigned primary key auto_increment,

name varchar(255) not null unique

);

 

插入班级数据：

 

insert into class (name) values

('class1'),('class2'),('class3');

 

 

创建视图：

 

create view user_class as

select u.*,c.id c_id,c.name c_name

from user u,class c

where u.class_id=c.id;

 

查询视图：

 

select * from user_class;

 

 

更新视图：

 

create or replace view user_class as

select u.*,c.id c_id,c.name c_name

from user u,class c;

 

事务

事务就是对数据库执行的工作单元(执行的多个操作)。事务是完成逻辑顺序的工作，无论是在手动方式由用户或者自动地通过某种数据库程序的序列的单元。

 

1. 事务特性：

 

事务具有以下四个标准属性，通常由首字母缩写ACID简称：

 

事务需要使用INNODB数据引擎

 

• 原子性: 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性: 确保数据库正确后成功提交事务更改状态。
• 隔离性: 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。。
• 持久性: 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

 

1. 事务控制：

 

用来控制事务有如下命令：

 

• BEGIN | START transaction : 开启事务
• COMMIT : 提交事务。
• ROLLBACK : 事物回滚。
• SAVEPOINT : 创建回滚事务点
• SET TRANSACTION : 放置事务的名称。

 

 

SAVEPOINT命令:

 

保存点SAVEPOINT是，可以回滚事务到某一事务节点，而不回滚整个事务。

 

SAVEPOINT命令的语法如下：

 

SAVEPOINT SAVEPOINT_NAME;

 

ROLLBACK命令:

 

ROLLBACK命令是用来撤消尚未被保存到数据库事务的事务命令。

 

ROLLBACK;

 

SQL> DELETE FROM CUSTOMERS

     WHERE AGE = 25;

SQL> ROLLBACK;

 

或者

 

SQL> SAVEPOINT SAVEPOINT_NAME;

SQL> DELETE FROM CUSTOMERS

     WHERE AGE = 25;

SQL> ROLLBACK TO SAVEPOINT_NAME;

 

函数

SQL 拥有很多可用于计数和计算的内建函数。

 

基于返回值的函数：

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

 

基于输入值的函数

 

• UCASE() - 将某个字段转换为大写
• LCASE() - 将某个字段转换为小写
• MID() - 从某个文本字段提取字符，MySql 中使用
• SubString(字段，1，end) - 从某个文本字段提取字符
• LEN() - 返回某个文本字段的长度
• ROUND() - 对某个数值字段进行指定小数位数的四舍五入
• NOW() - 返回当前的系统日期和时间
• FORMAT(Y_M_D h:m:s) - 格式化某个字段的显示方式

 

连接(JOIN)

SQL join 用于把来自两个或多个表的行结合起来。

 

SQL JOIN 类型：

 

• INNER JOIN - 如果表中有至少一个匹配，则返回行（默认）
• LEFT JOIN - 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN - 即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN - 只要其中一个表中存在匹配，则返回行left和right的组合

 

语法：

 

SELECT table_name1.colunm1,table_name2.colunm2

FROM table_name1

[INER| LEFT|RIGHT|FULL] JOIN table_name2

ON condition;

 

学生表：

班级表：

 

级联：inner

select *

from user

inner join class

on user.class_id=class.id;

 

 

级联：left

select *

from user

left join class

on user.class_id=class.id;

 

 

 

级联：right

select *

from user

right join class

on user.class_id=class.id;

 

[作业实验]

1. 建立图书馆借书数据模型

分析：

已知对象：

       学生（姓名，学号，已借书数量，账户余额）

       图书表（书号，书名，作者，总数量，已借出数量）

图书管理员（编号，姓名）

       借书单（学号，书号，借出时间，归还到期时间，实际归还时间，处理的管理员）

根据以上提供的信息创建数据库，和必要的表。并且插入数据成功借一本书

 

1. 购物车系统数据模型设计

 

PHP全站开发工程师-第3章 SQL高级语法