Loading...
墨滴

Peter

2021/03/22  阅读:25  主题:萌绿

SQL必知必会总结3-第14到22章

十四、组合查询UNION

什么是组合查询

SQL允许执行多个查询(多条SELECT语句),并将结果作为一个查询结果集返回,这些组合通常称为并union或者复合查询;通常两种情况下需要使用组合查询:

  • 在一个查询中从不同的表返回结构数据
  • 对一个表执行多个不同的查询,按照一个查询返回数据

创建组合查询

可以用UNION操作符来组合数条SQL查询。

-- 语句1
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI');
-- 语句2
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_name = 'Fun4ALL';

通过组合查询将上面两个查询组合在一起:

-- 组合查询
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION    -- 关键字
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_name = 'Fun4ALL';

我们也可以使用多个WHERE条件来实现:

-- 语句1
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
AND cust_name = 'Fun4ALL';

UNION使用规则

总结UNION使用规则:

  1. UNION必须由两条或者两条以上的SELECT语句组成;语句之间通过UNION关键字隔开
  2. UNION中的每个查询必须包含相同的列、表达式或者聚集函数
  3. 列数据类型必须兼容:类型不必完全相同
  4. UNION从查询结果集中会自动消除重复的行;但是如果想保留所有的行,使用UNION ALL 实现

对组合结果进行排序

SELECT语句的输出用ORDER BY子句排序。

-- 组合查询
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION    -- 关键字
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_name = 'Fun4ALL'
ORDER BY cust_name, cust_contact;   -- 组合之后再进行排序

十五、插入数据

插入数据

INSERT用来将行插入(或者添加)到数据库表中,3种插入方式:

  • 插入完整的行
  • 插入行的一部分
  • 插入某些查询的结果

下面通过实际的例子来说明:

1、插入完整的行

INSERT INTO Customers
VALUES('1000000006',
       'Tony',
       '123 Any Street',
       'New York',
       'NY',
       '1111',
       'USA',
       NULL,
       NULL   
)

将上面的数据插入到Customers表中,每列对应一个值。如果值不存在,则用NULL代替。同时插入数据的顺序必须和表中定义的相同。

安全写法:列出每个字段名称

INSERT INTO Customers(cust_id,   -- 明确列出列名
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email               
)
VALUES('1000000006',   -- 和上面的列名一一对应
       'Tony',
       '123 Any Street',
       'New York',
       'NY',
       '1111',
       'USA',
       NULL,
       NULL   
)

上面列名和下面插入的数据必须一一对应,我们改变插入顺序:

INSERT INTO Customers(cust_id,   -- 明确列出列名
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state                                   
)
VALUES('1000000006',   -- 和上面的列名一一对应
       '1111',
       'USA',
        NULL,
        NULL,   
       'Tony',
       '123 Any Street',
       'New York',
       'NY'
)

2、插入部分数据

上面的例子中我们插入的是全部列名的数据,现在指定部分列名进行插入:

INSERT INTO Customers(cust_id,   -- 明确列出列名
                      cust_zip,
                      cust_country,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state                                   
)
VALUES('1000000006',   -- 和上面的列名一一对应
       '1111',
       'USA',
       'Tony',
       '123 Any Street',
       'New York',
       'NY'
)

3、插入检索出来的数据

INSERT的另一种使用是将SELECT检索出来的结果插入到表中,使用INSERT SELECT语句

INSERT INTO Customers(cust_id,   -- 2、将SELECT检索的结果插进来
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state                                   
)
SELECT cust_id,   -- 1、SELECT先检索结果
      cust_zip,
      cust_country,
     cust_contact,
       cust_email,
       cust_name,
       cust_address,
       cust_city,
       cust_state   
FROM CustNew;

INSERT SELECT中SELECT语句也是可以包含WHERE子句,以便过滤插入的数据。

从一个表复制到另一个表

还有一种数据插入不需要使用INSERT语句,要将一个表的内容复制到另一个表,可以使用SELECT INSERT语句

SELECT * 
INTO CustCopy
FROM Customers;

需要注意4点:

  1. 在复制的过程中,任何SELECT选项和子句都可以使用,包含WHERE 和GROUP BY子句
  2. 可以利用联结从多个表插入数据
  3. 不管从多少个表中检索数据,数据最终只能插入到一个表中
  4. INSERT INTO是插入数据;SELECT INSERT是导出数据

十六、更新和删除数据

更新数据

更新(修改)表中的数据,可以使update语句。常见的有两种update方式:

  • 更新表中特定的行
  • 更新表中所有的行

update语句的3个组成部分:

  1. 要更新的表
  2. 列名和它们的新值
  3. 确定要更新哪些行的过滤条件
UPDATE Customers  -- 1、待更新的表
SET cust_email = '28173497@gmail.com'  -- 2、需要更细的列名和它的新值
WHERE cust_id = '10000000005';  -- 3、过滤条件

同时更新多个值:

UPDATE Customers  -- 1、待更新的表
SET   -- 2、同时更新多个值
 cust_email = '28173497@gmail.com',
 cust_contact = 'Sam Roberts'
WHERE cust_id = '10000000005';  -- 3、过滤条件

在更新多个列的值时,只需要使用提交SET命令,每个列=值对之间通过逗号隔开,最后一个列不同。

如果想删除某个列的值,可以将其设置成NULL(假如表定义允许NULL值)。

  • 空字符串用''表示,是一个值
  • NULL是没有值的
UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '100000000005';

删除数据

从表中删除数据使用DELETE语句。有两种删除方式:

  • 从表中删除特定的行
  • 从表中删除所有的行
DELETE FROM Customers
WHERE cust_id = '011111111116';

DELETE是删除整行而不是删除列。要删除列请使用UPDATE语句

更新和删除的指导原则

  • 请一定要带上WHERE子句,否则会修改全部的数据;除非我们的确是需要更新全部记录(少见)
  • 要保证每个表都有主键,可以指定各个主键、多个值或者值的范围
  • 在UPDATE或者DELETE语句使用WHERE语句之前,先用SELECT进行测试,保证它过滤掉的是正确的记录

十七、创建和操作表

创建表

SQL中一般有两种创建表的方法:

  • 多数DBMS都具有交互式创建和管理数据库表的工具
  • 表也可以直接使用SQL语句来操控;通过create table来实现
CREATE TABLE Products   -- 创建表
(
  prod_id     CHAR(10)      NOT NULL,
  vend_id     CHAR(10)      NOT NULL,
  prod_name   CHAR(254)     NOT NULL,
  prod_price  DECIMAL(8,2)  NOT NULL,
  prod_desc   VARCHAR(1000NULL                  
);

上面代码的解释:

  1. 表名紧跟CREATE TABLE 关键字
  2. 列名在圆括号中,各个列之间通过逗号隔开
  3. 每列的定义以列名开始,后紧跟数据类型 ,是否允许控制等
  4. 整条语句是以分号结束

使用NULL值

NULL值就是没有值或者缺失值。每个表中的列要么是NULL列,要么是NOT NULL列。

主键是其值唯一标识表中每一行的列。只有不允许NULL值的列可作为主键,允许NULL值的列不能作为唯一标识。

笔记:NULL是默认设置。如果不指定NOT NULL,则认为指定的就是NULL。

注意NULL和空字符串的区别:

  1. NULL值没有值,不是空字符串
  2. 空字符串值一个有效的值,它不是无值
  3. NULL值使用关键字NULL而不是空字符串指定

指定默认值

SQL中创建表的时候可以指定通过关键字DEFAULT来指定:

CREATE TABLE OrderItems
(
  order_num           INTEGER         NOT NULL,
  order_item          INTEGER         NOT NULL,
  prod_id             CHAR(10)        NOT NULL,
  quantity            INTEGER         NOT NULL   DEFAULT 1,    -- 指定默认值
  item_price          DECIMAL(8,2)    NOT NULL
);

默认值一般是用于日期或者时间戳列。

更新表

更新表中的数据使用关键词ALTER TABLE。

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

删除表

删除整个表而不是其内容,使用DROP TABLE。

DROP TABLE CustCopy;   -- 执行这条语句将会永久删除该表

重命名表

通过关键字RENAME来实现

RENAME TABLE old_name TO new_name;

旧表(old_name)必须存在,新表(new_name)必须不存在。 如果新表new_name存在,则该语句将失败。

十八、视图VIEW

什么是视图

视图是虚拟的表,与包含数据的表不一样,视图只包含使用时动态检索数据的查询。之前的例子:检索订购了某种产品的顾客

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 = 'RGAN01'

现在我们将上面的结果包装成一个名ProductsCustomers的虚拟表,可以得到相同的数据:

SELECT cust_name, cust_contact
FROM ProductsCustomers
WHRE prod_id = 'RGAN01'

ProductsCustomers并不是一个表,只是一个视图,它不包含任何列或者数据,包含的是一个查询。

为什么使用视图

总结以下几点使用视图的原因:

  1. 重用SQL语句
  2. 简化复杂的SQL操作
  3. 使用表的一部分而不是整个表
  4. 保护数据。可以授予用户访问表的特定部分数据,而不是整个表的数据
  5. 更改数据格式和表示、视图可以返回和底层表的表示和格式不同的数据

笔记:视图本身不包含数据,使用的是从别处检索出来的数据。

视图规则和限制

关于视图创建和使用的一些常见规则和限制:

  1. 与表一样,视图命名必须唯一
  2. 创建视图的数目没有限制
  3. 创建视图必须具有足够的访问权限
  4. 视图是可以嵌套的
  5. 视图不能索引,也不能有关联的触发器或者默认值

创建视图

1、利用视图简化复杂的联结

CREATE VIEW ProductsCustomers AS  -- 创建视图
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

上面 代码创建了一个名为ProductsCustomers的视图,我们查询一条信息:

SELECT cust_name, cust_contact
FROM ProductsCustomers
WHRE prod_id = 'RGAN01'

2、利用视图重新格式化检索出的数据

CREATE VIEW VendorLocations AS  -- 创建视图
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'  AS vend_title
FROM Vendors;  

-- 从视图中检索数据
SELECT *
FROM VendorLocations;

3、使用视图过滤数据

CREATE VIEW CustomerEmailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

-- 检索数据
SELECT *
FROM CustomerEmailList;

4、使用视图和计算字段

CREATE VIEW OrderItemExpanded AS   -- 创建视图
SELECT 
 order_num,
 prod_id,
 quantity,
 item_price,
 quantity * item_price AS expanded_price
FROM OrderItems;

-- 使用视图查询数据
SELECT *
FROM OrderItemExpanded
WHERE order_num = 2008;

十九、使用存储过程

什么是存储过程

存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

笔记:简答来说,存储过程就是为以后使用而保存的一条或者多条SQL语句。

为什么要使用存储过程

  1. 通过把处理封装在一个易用的单元中,可以简化复杂的操作
  2. 存储过程保证里数据的一致性,降低出错可能性
  3. 简化对变动的关管理。如果表名、列名或者业务逻辑有变化,那么只需要更改存储过程中的代码,使用它的人员无需知道变化
  4. 存储过程通常是以编译过的形式进行存储,所以DBMS处理命令所需的工作量少,提高了性能

笔记:总结存储过程的3个优点:简单、安全、高性能

创建存储过程

MySQL中创建存储过程:

CREATE
    [DEFINER = { user | CURRENT_USER }]
 PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOTDETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
  Valid SQL routine statement
 
[begin_label:] BEGIN
  [statement_list]
    ……
END [end_label]

MYSQL 存储过程中的关键语法

声明语句结束符,可以自定义:

DELIMITER $$

DELIMITER //

声明存储过程:

CREATE PROCEDURE demo_in_parameter(IN p_in int)       

存储过程开始和结束符号:

BEGIN .... END    

变量赋值:

SET @p_in=1  

变量定义:

DECLARE l_int int unsigned default 40000

创建MySQL存储过程、存储函数:

CREATE procedure 存储过程名(参数)

存储过程体:

CREATE function 存储函数名(参数)

创建一个实际的存储过程:

mysql> delimiter $$  -- 将语句的结束符号从分号;临时改为两个$$(可以是自定义)
mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    -> BEGIN
    ->   DELETE FROM MATCHES
    ->    WHERE playerno = p_playerno;
    -> END$$
Query OK, 0 rows affected (0.01 sec)
 
mysql> delimiter;  -- 将语句的结束符号恢复为分号

执行存储过程

EXECUTE AddNewProduct('JS01',
                      'Stuffed Eiffel Tower',
                      9.83,
                      'Plush stuffed toy with the text La Tour Tower'
                     )
  • 关键词是EXECUTE,后面紧跟的是存储过程的名字
  • 名字后面有4个参数
  • 作用:这个存储过程将行添加到Products表中,并将传入的属性赋给相应的列

二十、管理事务处理

这一章介绍的是MySQL中事务的相关知识点,包含什么是事务处理,怎样利用COMMIT 和 ROLLBACK语句管理事务处理

事务处理

事务Transaction,一个最小的、不可再分的工作单元,通常一个事务对应一个完整的业务。InnoDB引擎是支持事务的,MyISAM不支持事务。事务是针对数据库中DML数据操作语言的。

事务处理(transaction processing)是一种机制,用来管理必须成批执行的SQL操作。利用事务处理,可以保证一组操作不会中途停止,要么完全执行,要么完全不执行,来维护数据库的完整性。

  • 在MySQL中,一条SQL语句就是一个事务。
  • 一个完整的业务需要大量的DML(insert、update、delete)语句来共同完成。只有DML数据操作语句才有事务。
  • 事务保证一组SQL语句要么全部执行成功,要么全部失败,以此来维护数据库的完整性。
  • 如果没有发生错误,整个语句提交到数据库表中;如果发生错误,则进行回退(撤销),将数据库恢复到某个已知且安全的状态

栗子:银行转账业务

比如在两个表中,A(原有400)给B(原有200)转200块钱,包含两个过程:

  • A转出200,B转进200
  • 只有当两个过程全部完成才算真正的执行了一个完整的事务过程。
update user set fee=200 where id=1;     # 语句1
-- 由于断网、安全限制、超出磁盘空间等不可控制原因,下面的语句可能无法成功执行
update user set fee=400 where id=2;     # 语句2
  • 语句1的成功执行,并不能将底层数据库中的第一个账户的数据进行修改,只是单纯地记录操作,记录在内存中完成
  • 第二条语句成功执行之后,和底层数据库文件中的数据完成同步
  • 若第二条数据执行失败,清空所有的历史记录

事务相关术语

事务处理相关的术语:

  1. 事务transaction:一组SQL语句。只能处理DML语句:insert、update、delete语句
  2. 回退rollback:指撤销指定SQL的语句
  3. 提交commit:指将未存储的SQL语句结果写入到数据库表;只有进行了commit操作,数据才会从内存中写入磁盘中
  4. 保留点savepoint:指事务处理过程中设置的临时占位符plaveholder,可以对它发布回退(与整个事务回退处理不同);保留点越多越好:越多的话,能够更加灵活地处理回退问题
CREATE DATABASE IF NOT EXISTS employees;    -- 创建数据库
USE employees;

CREATE TABLE `employees`.`account` (   -- 数据库中创建表account
  `id` BIGINT (11NOT NULL AUTO_INCREMENT,
  `p_name` VARCHAR (4),
  `p_money` DECIMAL (102NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)  -- 设置主键
) ;
INSERT INTO `employees`.`account` (`id``p_name``p_money`VALUES ('1''tim''200');    -- 插入两条记录
INSERT INTO `employees`.`account` (`id``p_name``p_money`VALUES ('2''bill''200'); 

START TRANSACTION;   -- 开启事务
SELECT p_money FROM account WHERE p_name="tim";    -- 三条语句必须完整执行 
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
COMMIT TRANSACTION;   -- 显式提交

事务四大特性

事务具有四大特点,简称为ACID

  • 原子性Atomicity:一个事务中的语句,要么全部成功,要么全部失败。不存在只执行了部分的情况。
  • 一致性Consistency:在事务开始之前或者结束之后,必须保持数据库的一致性。比如上面的栗子中,A减掉200,那么相应的,B一定要加上200。否则数据库中的数据不一致。
  • 隔离性Isolation:当多个用户并发访问数据库,操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。用户的操作之间存在独立性。事务A和B之间具有隔离性。
  • 持久性Durability:事务一旦被提交,对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。事务的成功,是硬盘数据上的更改,不仅是内存上的变化。持久性是事务的保证,是事务结束的标志。

隔离级别

隔离级别 脏读(Dirty Read) 不可重复读(NonRepeatable Read) 幻读(Phantom Read) 加锁读Locked Read
未提交读(Read uncommitted) Y Y Y N
已提交读(Read committed) N Y Y N
可重复读(Repeatable read) N N Y N
可串行化(Serializable ) N N N Y
  • 脏读:未提交读,事务中修改即使没有提交,对其他会话也是可见的,可以读取到未被提交的数据。脏读会导致很多的问题,较少使用
  • 提交读:不可重复读,该级别保证事务如果没有成功执行(commit),事务中的操作不会被其他会话可见。解决了脏读问题,但是会对其他session产生两次不一样的读取结果
  • 幻读:会话T1事务中执行一次查询,然后会话T2新插入一行记录,这行记录恰好可以满足T1所使用的查询的条件。然后T1又使用相同 的查询再次对表进行检索,但却看到了事务T2刚才插入的新行。这个新行就称为“幻像”。

查看隔离级别

-- 查看系统隔离级别:
select @@global.tx_isolation;

-- 查看当前会话隔离级别
select @@tx_isolation;

-- 设置当前会话隔离级别
SET session TRANSACTION ISOLATION LEVEL serializable;   -- serializable 级别

-- 设置全局系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- READ UNCOMMITTED 可读未提交级别

事务开始和结束标志

开始标志:任何一条DML语句的执行,标志事务开始

结束的标志分为两种:成功结束的标识和失败结束的标识

1、成功结束的标志

  • commit:提交
  • 将所有的DML语句的操作历史记录和底层硬盘中的数据进行了同步。
  • 只有事务成功执行,硬盘中的数据才会进行修改更新。

2、 失败结束的标识

  • rollback:回滚
  • 将所有的DML语句的操作记录进行全部清空。

二十一、使用游标

本章节中讲解的是什么是游标,以及如何使用游标。

什么是游标

SQL检索操作返回一组称为结果集的行,这组返回的行都是与SQL语句相匹配的行(零行或者多行)。

简单地使用SELECT语句,没有办法得到第一行、下一行或者前10行的数据。但是实际中,我们需要在检索出来的行中前进或者后退一行甚至多行,这时候便可以使用游标。

游标是一个存储在DBMS服务器上的数据库存查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。

使用游标

使用游标的几个步骤:

  • declare:在使用游标之前,必须先进行声明
  • open:一旦声明了游标,就必须打开游标
  • 对于填有数据的游标,根据需要取出检索的各行
  • close:在结束的时候,必须关闭游标;有的DBMS还需要释放游标

创建游标

DECLARE关键词来定义和命名游标

DECLARE CustCursor CURSORVB   -- 声明游标
FOR 
SELECT * FROM Customers
WHERE cust_email IS NULL;

使用游标

1、OPEN CURSOR语句来打开游标

OPEN CURSOR CustCursor   -- 打开游标

2、打开游标之后我们可以使用FETCH语句来访问游标数据了。FETCH指出要检索哪些行?从什么地方开始检索?将它们放于何处?

关闭游标

关闭游标的关键词是close

游标一旦关闭,如果不再次打开,将无法使用;第二次使用的时候,不需要声明,直接open即可

CLOSE Custcursor

二十二、Python操作游标

下面的内容是个人增加部分,讲解的是如何通过Python的第三方库pymysql操作游标。

连接数据库

使用的是pymysql模块,需要先进行导入

import pymysql
  • host:主机名或者IP地址
  • port:默认是3306
  • user:用户名
  • passwd:user账户登录mysql的密码
  • db:创建的数据库
  • charset:防止中文出错,编码格式设置为charset="utf8"

使用模块的connect()方法进行连接

import pymysql
conn = pymysql.connect(
  host=“localhost”,  # 主机名
  port=3306,  # 端口
  user="root",  # 用户名
  passwd="123456",   # 密码
  db="bookdb",   # 数据库名字
  charset="utf8")  # 指定字符集

操作数据库

Python建立了和数据库的连接,实际上就是建立了一个pymysql.connect()实例对象,或者称之为连接对象

Python就是通过连接对象和数据库进行对话。pymysql.connect()实例对象的方法有:

  • commit:提交数据
  • rollback:如果有权限,取消当前的操作,否则会报错
  • cursor([cursorclass]):返回连接的游标对象,执行SQL语句
  • close():关闭连接

游标对象cur操作方法

名称 描述
close 关闭游标
execute 执行一条SQL语句,可以带上参数; 执行之后需要conn.commit(),数据库中才会真正建立
fetchone 返回一条语句
fetchall 返回所有的语句
fetchmany 返回many条语句
nextset() 移动到下一个结果
  • 插入
import pymysql    # 导入模块
conn = pymysql.connect(host=“localhost”,  # 建立python和数据库的连接
                       port=3306
                       user="root"
                       passwd="123456"
                       db="bookdb"
                       charset="utf8")      
cur = conn.cursor()  # 建立游标对象cur
cur.execute("insert into users (username, passwd, email) values (%s,  %s, %s)", ("python""123456""python@gmail.com"))   
conn.commit()  # 需要再执行conn.commit()

# 同时执行插入多条信息:多条信息用元组的形式表示
cur.executemany("insert into users (username, passwd, email) values (%s,  %s, %s)", (("python""123456""python@gmail.com"), ("java""456789""java@gmail.com"), ("php""123789""php@gmail.com")))
conn.commit()  # 需要再执行conn.commit()
  • 查询
>>cur.execute("select * from users")
>>lines = cur.fetchall()   # 返回所有的查询结果
>>for line in lines:   # 遍历返回所有的结果
    print(line)
    
# 只想返回一条结果
>>cur.execute("select * from users where id=1")  # 查询id=1

>>cur.execute("select * from users")
>>cur.fetchone()   # 返回一条结果;游标会跟着移动,直到将所有的数据取完
>>cur.fetchone() 
>>cur.fetchone()
  • 游标移动scroll到指定位置
  • 操作存储在内存中的对象时候游标会移动,可以指定游标到某个位置:
>>cur.scroll(2)  # 相对于当前位置移动2个单位
>>cur.fetchone()  # 显示数据
>>cur.scroll(2"absolute")   # 加上参数,实现“绝对移动”,到第三条

绝对移动的数字不能是负数,相对移动可以是负数

Python的连接对象的游标方法中提供一个参数,将读取到的数据保存成字典形式:

>>cur = conn.cursor(pymysql.cursors.DictCursor)
>>cur.execute("select * from users")
>>conn.commit()  # 需要再执行conn.commit()
>>cur.fetchall()

更新

>>cur.execute("update users set username=s% where id=2", ("mypython"))   # 更新第二条语句
>>cur.execute("select * from users where id=2")
>>conn.commit()  # 需要再执行conn.commit()
>>cur.fetchone()

二十三、SQL高级特性

在最后的一章中简单的介绍了SQL的3个高级特性:

  • 约束constraint
  • 索引index
  • 触发器trigger

约束

约束是管理如何插入或者处理数据库数据的规则。DBMS通过在数据库表上施加约束来实施引用完整性。

1、主键

主键是一种特殊的约束,用来保证一列(或者一组列)中的值是唯一的。主键必须满足的4个条件:

  • 任意两行的主键值不相同
  • 每行都具有一个主键值,即列中不允许NULL值
  • 包含主键的列从不修改或者更新
  • 主键值不能重复用
-- 方式1
CREATE TABLE Vendors(
  vend_id       char(10)   NOT NULL  primary key,   -- 添加主键
  vend_name     char(50)   NOT NULL,
  vend_address  char(50)   NULL,
  vend_city     char(50)   NULL,
  vend_state    char(5)    NULL,
  vend_zip      char(10)   NULL,
  vend_country  char(50)   NULL
);

-- 方式2
ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

2、外键

外键值表中的一列,其值必须列在另一表的主键中。外键是保证引用完整性的重要部分。

通过订单信息表Orders表中的顾客ID和顾客信息表Customers的顾客ID进行关联。

CREATE TABLE Orders
(
  order_num       INTEGER   NOT NULL PRIMARY KEY,   -- 主键:订单号唯一
  order_date      DATETIME  NOT NULL,
  cust_id         CHAR(10)  NOT REFERENCES    -- 外键:顾客id不唯一,一个顾客可能有多个订单
)

3、唯一约束

唯一约束用来保证一列中的数据是唯一的,可以通过关键词UNIQUE来定义。它和主键的区别在于:

  • 表中可以包含多个唯一约束,但是只能有一个主键
  • 唯一约束列可以包含NULL值
  • 唯一约束列可以修改或者更新
  • 唯一约束列的值可以重复使用
  • 与主键不同,唯一约束不能用来定义外键

4、检查约束

检查约束用来保证一列(或者一组列)中的数据满足一组指定的条件,常见的用途有:

  • 检查最大值或者最小值
  • 指定数据的范围
  • 只允许特定的值,例如性别字段中只允许M或者F
CREATE TABLE OrderItems
(
  order_num      INTEGER    NOT  NULL,
  order_item     INTEGER    NOT  NULL,
  prod_id        CHAR(10)   NOT  NULL,
  quantity       INTEGER    NOT  NULL CHECK (quantity>0),   -- 设置检查约束,保证大于0
  item_price     MONEY      NOT NULL
)

索引index

索引用来排序数据以加快搜索和排序操作的速度。创建索引前记住几点:

  1. 索引改善检索操作的性能,但是降低了数据插入、修改和删除的性能
  2. 索引数据可能要占用大量的存储空间
  3. 索引用于数据过滤和数据排序
  4. 可以在索引中定义多个列
  5. 索引必须唯一命名
CREATE INDEX prod_name_ind
ON Products (prod_name);

触发器trigger

触发器(trigger):监视某种情况,并触发某种操作,它是提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发。

触发器可以与特定表上INSERT、UPDATE、DELETE操作相关联。触发器的常见用途:

  • 保证数据一致
  • 基于某个表的变动在其他表上执行活动
  • 进行额外的验证并根据需要回退数据
  • 计算计算列的值或者更新时间戳

触发器创建语法四要素:

  1. 监视地点(table)
  2. 监视事件(insert/update/delete)
  3. 触发时间(after/before)
  4. 触发事件(insert/update/delete)

笔记:触发器只能创建在永久表上,而不能对临时表创建触发器

触发器创建的基本语法如下

CREATE TRIGGER trigger_name
AFTER/BEFORE INSERT/UPDATE/DELETER ON table_name
FOR each row   -- 固定语句
begin
 sql语句;
end;

二十四、总结

《SQL必知必会》一本非常经典的数据库书籍,也可以说是自己入门数据库的书。算上本次万字精华整理,应该是第三次系统的阅读本书,本书主要有几个特点:

  • 知识全面:书中囊括了很多的数据库知识,从最基础的创建库表到联结查询,再到数据库的视图、触发器等,有基础也有提升
  • 案例丰富:本书的讲解主要是通过5个不同表来完成,结合各种案例来说明SQL的使用细节
  • 对比性强:不同的DBMS系统的语法是有一定出入的,本书在讲解的过程中针对不同DBMS,给出不同的写法,主要还是MySQL

Peter

2021/03/22  阅读:25  主题:萌绿

作者介绍

Peter

厦门大学