mysql——入门4

子查询

语法：

​ select 查询语句中，嵌套select查询语句。 外层查询，成为 父查询。内层叫做子查询。

​ 子查询的作用，给父查询传参。

# 查找笔试最高分的学生
-- 方法1
select *
from stuinfo
where stuno = 
(
	select stuno
	from stumarks
	order by writtenExam desc
	limit 1
)

-- 方法2：
select *
from stuinfo
where stuno = 
(
	select stuNo
	from stumarks
	where writtenExam = 
	(
		select max(writtenExam)
		from stumarks
	)
)

多行子查询

• 单行操作符： > >= < <= = !=
• 多行操作符：in、not in

# 查询笔试成绩 及格的学员信息
select *
from stuinfo
where stuno in
(
	SELECT stuno
	from stumarks
	where writtenExam >= 60
)

# 查询笔试成绩 不及格（包含缺考）的学员信息
select *
from stuinfo
where stuno not in
(
	SELECT stuno
	from stumarks
	where writtenExam >= 60
)

exists 和 not exists

• exists：判断子查询是否有结果集。 有，为真

select stuno, stusex 
from stuinfo
where exists
(
	SELECT *
	from stumarks
	where writtenExam > 70
)

• not exists：判断子查询是否没有结果集。没有，为真。

select stuno, stusex 
from stuinfo
where not exists
(
	SELECT *
	from stumarks
	where writtenExam > 90
)

子查询分类

1. 标量子查询：子查询返回一个结果。
2. 列子查询：子查询返回一列上的多个结果。
3. 行子查询：子查询返回一行或多行。
4. 表子查询：子查询返回一个二维数据表。

• 行子查询：

# 查询语文成绩最高的男生和女生 的学生信息
select *
from stu
where (stusex, ch) in
(
	select stuSex, max(ch)
	FROM stu
	group by stuSex
)

• 表子查询

# 完成填空，查询学生的 姓名、学号、年龄、地址信息：
# select * from _________

select * from 
(
	select stuno, stuname, stuage, stuaddress 
	from stu
) t1;		-- t1 作为表名，不能省略。 并且不能使用 “”  '' 

练习：更新stu表，将总成绩前3名的学生的语文成绩各+1分

UPDATE stu set ch = ch+1
where stuno in 
(
	select stuno 
	from 
	(
		select stuno, ch+math 总成绩
		from stu
		order by 总成绩 DESC
		limit 3
	) as t1
) 

---

事务

• 事务作用：保护书写 SQL 在一个事务中，要么都成功，要么都失败。—— 保证数据的完整性。
• 事务特性：要么都成功、要么都失败。
• 事务使用步骤：
  1. 开启事务 start transaction、 begin。 手动开启
  2. 执行 DML（insert、update、delete）修改数据
  3. 。。。。
  4. commit 提交修改。关闭事务。
  5. rollback 回退修改。关闭事务。

关闭事务

• 显示关闭：
  • 显示提交；commit
  • 显示回滚；rollback
• 隐式关闭：
  • 隐式提交：DDL语句（create、drop、truncate、alter）
  • 隐式回滚：掉电、宕机、退出（exit、quit、\q）

【注意】：只有 innodb 引擎，支持事务。myisam 引擎不支持。

保存点、回滚点

• 创建保存点：Savepoint 保存点名。
• 使用保存点：rollback to 保存点名。 退回至，保存点位置。该保存点后的 SQL被回退掉。
• 特性：rollback to 不能关闭事务。使用 commit、rollback 关闭事务。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update bank set money='1026' where cardid='1001';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from bank where cardid='1002';
Query OK, 1 row affected (0.00 sec)

mysql> savepoint AAA;

mysql> rollback to AAA;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;

事务的4大特性

​ ACID： 原子性、一致性、【隔离性】、持久性。

​ 隔离级别。—— 课后扩展。

---

视图

• 视图的特性：

  1. 借助表创建视图，数据保存在 表中。视图是虚拟表，不能保存数据。
  2. 视图只包含表结构。

• 视图作用：

  1. 隐藏表中的敏感字段。
  2. 简化sql语句。

• 视图的创建：

  • 语法： create [replace] view 视图名 as select语句（单表查询、多表查询、子查询）

  create view vw_stu as 
  (
      select stuno, stuname,stuage, writtenexam, labexam 
   	from stuinfo natural join stumarks
  );
  

• 查看视图：

  1. desc 视图名；
  2. show tables;
  3. show create view 视图名\G
  4. select table_name from information_schema.views;
  5. show table status where comment = ‘view’\G

• 使用视图：

  • 视图的使用方法，与表的使用方法一致（curd）。（数据是保存在表中。）
  • 视图大多数使用场景为 select 操作。

• 删除视图：

  • drop view [if exists] 视图名；

---

索引

• 优点：查询速度快

• 缺点：

  • 增、删、改效率低。
  • 占用额外空间。

  

• 索引类型：

  1. 普通索引
  2. 唯一键索引：
  3. 主键索引。
  4. 全文索引。

• 索引的创建：

  1. 在现有表上，添加索引：

     1. create index 索引名 on 表名（字段名）

     create index idx_name on stu(stuname);
     

     2. alter table 表名 add index 索引名（字段名）；

     alter table stu add index idx_sex (stusex);
     
     

  2. 创建表时指定索引

     create table t2( 
         id int auto_increment primary key, 
         name varchar(20), 
         index `idx_name`(name));
     

• 索引表由MySQL系统自动维护。不需要用户管理。

• 删除索引：

  drop index idx_name on stu;
  

• 创建索引的指导原则：

  1. 用于频繁搜索 —— 建议创建
  2. 用于排序 —— 建议创建
  3. 公共字段 —— 建议创建
  4. 数据量较小 —— 不建议创建
  5. 集合、枚举类型字段 —— 不建议创建。

  综合上述 5 条，选择性创建索引。

预处理SQL

• 使用的场景：一条（一类）SQL反复执行。

• 使用方法：
  1. 准备预处理语句 ： prepare 预处理名 from ‘SQL语句（可以带有？占位符）’
  2. 创建变量，初始化。 set @变量名 = 变量值, @变量名 = 变量值, @变量名 = 变量值, …
  3. 执行预处理语句： execute 预处理名 using @变量名,@变量名,@变量名,…
  4. ?不能标识占位符与的顺序。execute 的时候，按顺序传参。

prepare stmt from 'select * from stu where stuaddress=? && stusex=?';

set @addr='河北', @sex='女';

execute stmt using @addr, @sex;

set @addr='上海', @sex='男';

execute stmt using @addr, @sex;

Go语言使用 MySQL数据

安装MySQL驱动

1. 检测当前系统中是否包含：
   • find ~/ -name “go-sql-driver” —— 如果没有，安装
2. 安装：
   • go get github.com/go-sql-driver/mysql
3. 查看：
   • find ~/ -name “go-sql-driver”
   • cd ~/workspace/go/src/github.com/go-sql-driver/mysql
   • ls -l | wc -l —> 31 条记录 。—— 驱动完整。

测试连接MySQL

1. 指定MySQL

   // 导入MySQL的驱动包
   import (
   	"database/sql"
   	_ "github.com/go-sql-driver/mysql"
   	"fmt"
   )
   

2. 创建数据库连接句柄 DB

   func Open(driverName, dataSourceName string) (*DB, error)
   参：driverName: "mysql"
   参：dataSourceName: 用户名:密码@[tcp](IP地址:port)/数据库名
   返回值：指向数据库的连接DB （open返回时，该连接无效）
   DB的本质：
   type DB struct {
       。。。。
   }
   

3. 连接数据库

   func (db *DB) Ping() error
   如果，ping 没有返回，说明 连接数据库 DB 指向数据库。
   

单行多行插入

• 单、多行：
  1. 指定MySQL驱动
  2. 创建数据连接 DB = sql.Open() — DB不能操作数据库
  3. 连接数据库 DB.Ping() — DB 指向数据库
  4. 组织 sql （insert、update、delete）
  5. DB.Exec(sql) —>reslut —> interface { LastInsertId(), RowsAffected()}
  6. n = reslut.RowsAffected() 得到 影响的 行数

预处理插入：

1. 指定MySQL驱动
2. 创建数据连接 DB = sql.Open() — DB不能操作数据库
3. 连接数据库 DB.Ping() — DB 指向数据库
4. 准备预处理语句（带有 ？占位符）
5. 编译带有 ？的预处理语句。 DB.Prepare(sql) —> stmt --> type Stmt struct { … }
6. 对应 ？准备预处理参数。str := [][]string{{“6”,“李清照”}, {“7”,“辛弃疾”}, {“8”,“陆游”}}
7. 根据 参数的个数，循环执行 stmt.Exec()

单行查询

1. 组织 select 语句，返回一条表中的记录。
2. 执行 SQL 语句：DB.Queryrow(sql) —> row — struct {}
3. 按表字段，定义变量。接收字段值
4. 从row对象中，提取 字段值。row.Scan(&变量， &变量，…)

多行查询

1. 组织 多行 select 语句，返回表中的多条记录。
2. 执行 SQL 语句：DB.Query(sql) —> rows — struct {}
3. 按表字段，定义变量。接收字段值
4. for 循环判断游标，是否有下一行记录可以提取 rows.Next()
5. 从rows 对象中，提取 字段值。rows.Scan(&变量， &变量，…)

预处理查询

1. 组织 带有占位符的 select 语句。
2. 编译带有 ？的预处理语句。 DB.Prepare(sql) —> stmt --> type Stmt struct { … }
3. 执行预处理语句, 同时按占位符顺序传递参数, stmt.Query(占位符值1， 占位符值2， 。。。) —> rows — struct {}
4. 按表字段，定义变量。接收字段值
5. for 循环判断游标，是否有下一行记录可以提取 rows.Next()
6. 从rows 对象中，提取 字段值。rows.Scan(&变量， &变量，…)