软件测试Day7|数据库Mysql

内外连接

• join (联合连接) 和 union(组合查询)
• • join 是两张表做交连后里面条件相同的部分记录产生一个记录集，
    union是产生的两个记录集(字段要一样的)并在一起，成为一个新的记录集 。
  • union的两张表需要列数和字段完全匹配 与 Intersect 相同
  • union 自动去重 union all 不去重
• 内连接和外连接
• • 内连接 [inner] join inner 可忽略
• • 外连接 = 左右连接+全连接 left/right [outer] join outer可忽略 +left join... union right join... 全连接

in VS exists

• exits的使用：exits 只关心 是否返回行，不关心具体的返回数据，返回有数据即为True
  例子：没有下过订单的客户 ( where 条件后为True则被筛选出来）

  SELECT * FROM customer c
        WHERE not exists (SELECT customer_id FROM order o WHERE c.customer_id=o.customer_id);
  1
  2

• in 和 exits
• • in后面放数据量小的表，exits后面放数据量大的表
  • 使用 in ，两表执行顺序是先查 B 表，再查 A 表

    select * from A where id in (select id from B)
    1

  • 使用exits，使用 exists，两表执行顺序是先查 A 表，再查 B 表

    select * from A where exists (select 1 from B where B.id = A.id)
    1

窗口函数

窗口函数是一种分析型的 OLAP（Online Anallytical Processing，联机分析处理）函数，意思是对数据库数据进行实时分析处理。

• 执行顺序：在执行完select之后，在所得结果集之上进行partition

• <函数名称> OVER ([PARTITION BY <列名1>, <列名2>, ...] ORDER BY <排序列> [ASC|DESC] ROWS <行范围指定>)

• • 函数
    • 聚合函数
    • row_num()、rank()、dense_rank() 1234\1223\1223
    • first_value、last_value 取分组内，排序后，截止到当前行第一个值\最后一个值
  • PARTITION BY colx (分组语句，类似group by)
  • ORDER BY coly
  • ROWS 行选择语句
    • rows unbounded preceding 当前行至第一行
    • rows x following/preceding 当前行到当前行往后/前x行
    • rows between x preceding and y following 从前x到后y行
    • rows between unbounded preceding and unbounded following 从第一行到最后一行

• 链接1

INSERT

• 插入完整数据，忽略列名

  INSERT INTO table1
  VALUES(NULL,'Pop E.','90046','USA',NULL,NULL);
  1
  2

• 插入部分列数据，写上对应列名

  INSERT INTO table1(name,addr,zip,country)
  VALUES('Pop E.','90046','USA');
  1
  2

• 插入多个行

  INSERT INTO table1(name,addr,zip,country)
  VALUES('Pop E.','90046','USA'),
  	  ('Martian M.','11213','USA');
  1
  2
  3

UPDATE、DELETE

• 更新

  UPDATE table1
  SET name = 'new name',
  	zip = 'new zip'
  WHERE id =10005;
  1
  2
  3
  4

• 删除

  DELETE FROM table1
  WHERE id =10005;
  1
  2

表操作

• 创建表
• • NOT NULL,AUTO_INCREMENT:只允许一个自增列；DEFUALT,PRIMARY KEY(),ENGINE =

  --注释
  /*
  多行注释
  */
  CREATE TABLE table1
  (
  	id int NOT NULL AUTO_INCREMENT,
  	name char(50) NOT NULL DEFAULT 'JACK',
  	countrt char(50) NULL,
  	PRIMARY KEY(id)
  )ENGINE = InnoDB;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

• 更新表

  ALTER TABLE table1
  ADD  c2 int, --新增
  ADD  CONSTRAINT xxx,
  Drop c3, -- 删除列
  CHANGE c_old c_new int.  -- 修改列名
  MODIFY c4， int -- 修改数据类型
  FOREIGN KEY (id) REFERENCES table2 (id)
  1
  2
  3
  4
  5
  6
  7

• 重命名表

  RENAME TABLE new_table1 TO table1,
  			 new_table2 TO table2,
  			 new_table3 TO table3;
  1
  2
  3

• 删除表

  DROP TABLE tables
  1

视图

为什么使用视图

• 虚拟的表，包含的不是数据，而是根据需要检索数据的查询
• 重用SQL语句
• 简化复杂的SQL操作
• 使用表的组成部分而不是整个表
• 保护数据，给予用户表的特定访问权限而不是整个表的访问权限
• 更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据

视图的规则和限制

• 唯一命名
• 可创建的视图数目没有限制
• 创建视图需要有相应的访问权限
• 视图可以嵌套（可以从其他视图查询数据得到视图）和联结（可以编写联结视图与表的SELECT语句）
• ORDER BY可以用在视图中，但如果从该视图中查询的SELECT语句含有ORDER BY，则会覆盖视图的ORDER BY 语句？
• 视图不能索引，也不能有关联的触发器和默认值

视图的使用

• 创建视图

  CREATE VIEW view_name
  SELECT id
  FROM table1
  1
  2
  3

• 删除视图

  DROP VIEW view_name
  1

• 更新视图 可以先drop再创建，也可使用下列语句

  CREATE OR REPLACE VIEW view
  1

• 查看创建视图的语句

  SHOW CREATE VIEW view_name
  1

存储过程

• 创建存储过程

  CREATE PROCEDURE procedure_name(
      In id  INT,                      //输入参数  
  	OUT p1 DECIMAL(8,2),             //输出变量，也可不输出
  	OUT ph DECIMAL(8,2),
  	OUT pa DECIMAL(8,2),
  )
  BEGIN
  	SELECT MIN(price)
  	INTO p1
  	FROM table1
  	SELECT MAX(price)
  	INTO ph
  	FROM table1
  	SELECT AVG(price)
  	INTO pa
  	FROM table1
  END
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17

• 调用：执行存储过程

  CALL PROCEDURE procedure_name(1000,    //传入输入参数
                                @price1,  //传入参数接受结果
                                @price2, 
                                @price3); 
  SELECT @price1;
  1
  2
  3
  4
  5

• 删除存储过程

  DROP PROCEDURE procedure_name
  1

• 更新存储过程

  CREATE OR REPLACE VIEW view
  1

• 查看创建存储过程的语句

  SHOW CREATE PROCEDURE procedure_name
  1

游标

• 游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而实被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
• 使用游标
• • MySQL5中只能用于存储过程中（或函数）中，其他的DBMS不一定；
  • 声明游标 DECLARE
  • 声明后，必须打开游标使用
  • 对于含有数据的游标，根据需要取出（检索）游标
  • 结束游标使用后，必须关闭游标

CREATE PROCEDURE procedurename()
BEGIN
	DECLARE o INT;     // 声明局部变量

	DECLARE cursorname CURSOR   // 声明游标
	FOR 
	SELECT num FROM orders;

	OPEN cursorname;

	REPEAT   // 循环将查询到的num赋值给局部变量o
		FETCH num TO o;
	UNTIL done END REPEAT;
	
	CLOSE cursorname;
END
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

触发器

触发器使用注意

• 唯一的触发器名
• 触发器关联表
• 触发器响应的活动（DELETE、UPDATE、INSERT）
• 触发器何时执行(AFTER、BEFORE)

INSERT 触发器

• NEW 虚拟表，访问插入的行
• BEFORRE 触发器中，NEW的值可进行更改
• 对于AUTO_INCREMENT列，插入前包含0，插入后自动包含自动生成的值

CREATE TRIGGER trigger_name AFTER INSERT ON table1
FOR EACH ROW SELECT NEW.ordernum;
1
2

DELETE 触发器

• OLD 虚拟表，访问被删除的行
• OLD表中的值只读，不可更新

CREATE TRIGGER trigger_name BEFORE DELETE ON table1
FOR EACH ROW 
BEGIN
	INSERT INTO tabel2(name,id)
	VALUES (OLD.name,OLD.id);
END;
1
2
3
4
5
6

UPDATE触发器

• NEW 虚拟表，访问更新后的行，BEFORE触发器中可更改需要UPDATE的行的数据
• OLD 虚拟表，访问被更新的行，只读

CREATE TRIGGER trigger_name BEFORE UPDATE ON table1
FOR EACH ROW SET NEW.country = upper(NEW.country);
1
2