MySQL学习日记3

MySQL的查询-2

多表连接查询

多表连接由笛卡尔积实现

1.连接查询

内连接（由INNER JOIN … ON 关键字实现）

具体语法如下：
SELECT field1,field2,…fieldn from table_name INNER JOIN join_table ON join_condition;

on后面的跟的是连接条件

select * from commodity inner join commoditytype;

外连接

• 左连接
• 右连接
  具体语法如下：
  SELECT field1,field2,…fieldn
  from table_name LEFT|RIGHT JOIN join_table ON join_condition;

select * from commodity left join commoditytype on c_type=ct_id;

左连查询和右连查询的区别是，以执行语句中的哪个表为主表，输出的结果以主表为准，主表中有的数据才显示，主表中没有的数据即使附表中有也不显示在结果中！

子查询

使用条件:用一张表的数据作为条件去查另一张表

• 返回值是单行单例的值的子查询

select * from table_a where sal > (
	select sal from table_b where name = 'TomCat'
);

• 查询出的是单列多行的子查询

select * from table_a where (sal,name) = (
	select sal,name from table_b where name = 'TomCat'
);

注意：这里的返回值和查询的列名顺便必须一致！

• 查询出的是单列多行的子查询

select * from table_a where sal IN (select sal from table_b;);
#子查询
#单列多行：
select * from table_a where sal IN (select sal from table_b;); 
#查询结果在返回字段结果内的
select * from table_a where sal NOT IN (select sal from 
table_b;); #查询结果不在返回字段结果内的
select * from table_a where sal >=ANY (select sal from 
table_b;); #查询结果在返回字段结果内任意满足
select * from table_a where sal >=ALL (select sal from 
table_b;); #查询结果在返回字段结果内全部满足

子查询的效率比连接查询高
子查询和表连接都可以完成需求的时候 优先使用子查询

例题：
使用连接查询和子查询完成 刘德华买了什么 (c_name)

使用子查询分两步：
先将order和customer连在一起 拼成一张表之后 再和商品表拼接完成即可
先拼里面2张表

select * from `order` inner join customer where cu_name='刘德华';
+------+--------+-------+-------+-------+---------+----------+-----------+------------+
| o_id | o_cuid | o_cid | o_num | cu_id | cu_name | cu_phone | cu_gender | cu_address |
+------+--------+-------+-------+-------+---------+----------+-----------+------------+
|    1 |      1 |    23 |     3 |     1 | 刘德华  | 8888     |         1 | 北京       |
|    2 |      1 |    32 |     2 |     1 | 刘德华  | 8888     |         1 | 北京       |
+------+--------+-------+-------+-------+---------+----------+-----------+------------+
2 rows in set (0.00 sec)

select c_name from commodity where c_id in (select o_cid from `order` where o_cuid=(select cu_id from customer where cu_name='刘德华'));
+-------------+
| c_name      |
+-------------+
| 中华铅笔HB  |
| 礼盒丝带-红 |
+-------------+
2 rows in set (0.00 sec)

使用sql语句一步完成：

select c.c_name 
from commodity as c inner join ( select * from `order` inner join customer 
where cu_name='刘德华') as t on c.c_id=t.o_cid;

mysql提供了内连接另外一种语法（只适用于内连接）

 select c_name
 from commodity as c,`order` as o ,customer as cu
 where c.c_id=o.o_cid and o.o_cuid=cu.cu_id and cu.cu_name='刘德华';

查询出买过刘德华买过任一商品的客户

客户id in 买过刘德华in(或)任一的商品的人

select * from customer where cu_id in(
	select o_cuid from `order` where o_cid in(
		select o_cid from `order` where o_cuid=(
			select cu_id from customer where cu_name='刘德华'
		 )
	 )
 );

查询出买过刘德华买过所有同样商品的客户

select o_cuid from `order` where o_cuid not in(
#买过和刘德华不一样东西的人
	select o_cuid from `order` where o_cid in(
	#刘德华没买过的商品
		select c_id from commodity where c_id not in(
			select o_cid from `order` where o_cuid=(
				select cu_id from customer where cu_name='刘德 华'
			)
		)
	 )
)group by o_cuid
having count(o_cid)=(
	select count(o_cid) from `order`  where o_cuid=(
		select cu_id from customer where cu_name='刘德华'
		)group by o_cuid
	);

事务

1.事务的概念

事务是用户定义的一个数据库操作序列，这些操作要么全部执行，要么全部不执行。因此事务是一个不可分割的工作逻辑单元。在数据库系统上执行并发操作时事务是作为最小的控制单元来使用的。

2.事务的特点

• 原子性（Atomicity）
  事务是数据库的逻辑工作单位，它的操作要么就全做，要么都不做。
• 一致性（Consistency）
  事务执行的结果必须是从数据库从一个一致性状态转换到另一个一致性状态。
• 隔离性（Isolation）
  一个事务的执行不能被其他事务干扰。
• 持久性（Durability）
  一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

MySQL的事务实现方法

◆SET AUTOCOMMIT
使用SET语句来改变自动提交模式
SET AUTOCOMMIT = 0; # 关闭自动提交模式
SET AUTOCOMMIT = 1; # 开启自动提交模式

注意:
MySQL中默认是自动提交
使用事务时应先关闭自动提交

◆START TRANSACTION
开始一个事务,标记事务的起始点
◆COMMIT
提交一个事务给数据库
◆ROLLBACK
将事务回滚，数据回到本次事务的初始状态
◆SET AUTOCOMMIT = 1;
还原MySQL数据库的自动提交

最后要记得还原MySQL数据库的自动提交

事务仅对表级别的修改有效 （drop 无效）（仅对delete，insert，update 有效）

set autocommit=0;# 关闭自动提交模式
start transaction;#开始一个事务
delete from `order`;delete from customer;
select * from customer;
rollback;#在COMMIT（提交）一个事务给数据库之前，都可以将事务回滚
select * from customer;
+-------+---------+----------+-----------+------------+
| cu_id | cu_name | cu_phone | cu_gender | cu_address |
+-------+---------+----------+-----------+------------+
|     1 | 刘德华  | 8888     |         1 | 北京       |
|     2 | 张学友  | 3333     |         1 | 北京       |
|     3 | 林志玲  | 9999     |         0 | 台湾       |
+-------+---------+----------+-----------+------------+
3 rows in set (0.00 sec)
#开启自动提交模式
set autocommit=1;

索引

作用：

• 提高查询速度
• 确保数据的唯一性
• 可以加速表和表之间的连接，实现表与表之间的参照完整性
• 使用分组和排序子句进行数据检索时，可以显著减少组和排序的时间
• 全文检索字段进行搜索优化

分类

主键索引（PRIMARY KEY）

确定特定数据记录在数据库中的位置

• 主键：某一个属性组能唯一标识一条记录
  如：学生表（学号，姓名，班级，性别等） ，学号就是唯一标识的，可作为主键
• 特点：
  1.最常见的索引类型
  2.确保数据记录的唯一性
  示例

CREATE TABLE `表名` (
`GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY,
#或 PRIMARY KEY(`GradeID`)
)

唯一索引（UNIQUE）

作用：避免同一个表中某数据列中的值重复

• 与主键索引的区别
  1.主键索引只能有一个
  2.唯一索引可有多个唯一索引
  示例

CREATE TABLE `Grade` (
`GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
#或 UNIQUE KEY `GradeID` (`GradeID`)
)

常规索引（INDEX）

• 作用：快速定位特定数据
  注意：
  1.index和key关键字都可设置常规索引
  2.应加在查找条件的字段
  示例

CREATE TABLE `result` (				#创建表时添加
//省略一些代码
INDEX/KEY `ind` (`studentNo`,`subjectNo`)
)
#创建后追加
ALTER TABLE `result` ADD INDEX `ind` (`studentNo`, `subjectNo`);

全文索引（FULLTEXT）

• 作用：快速定位特定数据
  注意：
  1.只能用于MyISAM类型的数据表
  2.只能用于 CHAR 、 VARCHAR、TEXT数据列类型
  3.适合大型数据集
  示例：

CREATE TABLE `student` (
#省略一些SQL语句
FULLTEXT (`StudentName`)
)ENGINE=MYISAM;
ALTER TABLE employeeADD FULLTEXT (`first_name`);

创建索引

• 创建表时添加
• 建表后追加：
  语法: ALERT TABLE 表名 ADD 索引类型（数据列名）

删除索引

DROP INDEX 索引名 ON 表名
ALTER TABLE 表名 DROP INDEX 索引名
ALTER TABLE 表名 DROP PRIMARY KEY

查看索引

SHOW INDEX(或KEYS) FROM 表名

注意：
◆索引不是越多越好
◆不要对经常变动的数据加索引
◆小数据量的表建议不要加索引
◆索引一般应加在查找条件的字段