SQL必知必会总结4-第18到22章

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'

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

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

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

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

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

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

-- 检索数据
SELECT *
FROM CustomerEmailList;

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;

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
  | [NOT] DETERMINISTIC
  | { 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]

DELIMITER $$
或
DELIMITER //

CREATE PROCEDURE demo_in_parameter(IN p_in int)

BEGIN .... END

SET @p_in=1

DECLARE l_int int unsigned default 40000;

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'
                     )

update user set fee=200 where id=1;     # 语句1
-- 由于断网、安全限制、超出磁盘空间等不可控制原因，下面的语句可能无法成功执行
update user set fee=400 where id=2;     # 语句2

CREATE DATABASE IF NOT EXISTS employees;    -- 创建数据库
USE employees;

CREATE TABLE `employees`.`account` (   -- 数据库中创建表account
  `id` BIGINT (11) NOT NULL AUTO_INCREMENT,
  `p_name` VARCHAR (4),
  `p_money` DECIMAL (10, 2) NOT 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;   -- 显式提交

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

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

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

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

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

OPEN CURSOR CustCursor   -- 打开游标

CLOSE Custcursor

import pymysql

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

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()

>>cur.scroll(2)  # 相对于当前位置移动2个单位
>>cur.fetchone()  # 显示数据
>>cur.scroll(2, "absolute")   # 加上参数，实现“绝对移动”，到第三条

>>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()

-- 方式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);

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

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
)

CREATE INDEX prod_name_ind
ON Products (prod_name);

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