数据库技术部分课程作业

目录

InnoDB锁的一个实例

第15周数据库上机任务 

 range分区基本操作

 mysql各种连接

---

InnoDB锁的一个实例

1，通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

 

2，禁止自动提交

 

3，从commodity_tb表中查询Cid为00786的记录信息

查询成功

4，对Cid为00786的记录信息加共享锁

4，开启第二个会话窗口，并禁止自动提交

 

5，在第二个会话窗口中从commodity_tb表中查询Cid为00786的记录信息

 

 依旧查询成功，甚至还能继续加共享锁

 

6，在第一个会话中对锁定的记录进行更新操作，等待锁

7，在第二个会话中进行同样的更新操作，出现了死锁

 8，获得锁后，成功更新

 

第15周数据库上机任务 

1,创建新用户，设置新密码，并使改动生效

2，将对 dbkeshe.commodity_tb 的 select 权限赋予给新用户testadmin

3，使用新用户登录数据库

4，新用户对 dbkeshe.commodity_tb 进行 select 操作

4，新用户对 dbkeshe.commodity_tb 进行 update 操作

 操作失败，显示该用户无权进行该操作。

5，查看用户权限

 

 6，登录root账户，撤销新用户权限

 

 7，查看新用户权限

 发现权限变更

8，再使用新用户身份登录，发现已无权进行相关操作

 

 range分区基本操作

 

1,建立分区表

CREATE TABLE `user_tb` (
  `Uid` int(20) NOT NULL AUTO_INCREMENT,
  `head` text,
  `wxAcount` varchar(20) DEFAULT NULL,
  `wxName` varchar(16) DEFAULT NULL,
  `zfbAcount` varchar(30) DEFAULT NULL,
  `zfbName` varchar(30) DEFAULT NULL,
  `tel` varchar(20) DEFAULT NULL,
  `orderNum` int(11) DEFAULT NULL,
  `note` mediumtext,
  PRIMARY KEY (`Uid`),
  KEY `orderNum` (`orderNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


PARTITION by RANGE(Uid)(
	PARTITION p1 VALUES less than(1000999),
  PARTITION p2 VALUES less than(1001999),
  PARTITION p3 VALUES less than(1002999)
);

注意：create语句后没有分号，直到分区语句结束之后才有分号

 

2，查看分区信息

select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='user_tb';

 

3， 为每个分区添加数据（调用之前的inster_111()，存储过程：call insert_llll(1000000,2998);）

注意：当插入的数据不在分区内时，插入语句将被无效

 

4，增加一个分区p4(ALTER TABLE user_tb add partition(partition p4 values less than (1003999));)

 

5，删除一个分区（alter table user_tb drop partition p2;）

 

6,查询p3中数据

 

 

7，explain语句

由于是估算所以显示的分区并不正确，但可以看到在执行语句时使用了的type为range

 

8，向新建的p4插入一条数据

insert into user_tb (head,wxAcount,wxName,zfbAcount,zfbName,tel,note) values (
	substring(MD5(RAND()),1, RAND()*10), 1, 1, 1, 1, 1,
	00000);

 

 

 

9，合并p3，p4分区

ALTER TABLE user_tb REORGANIZE PARTITION p3,p4 INTO(
PARTITION p3 VALUES LESS THAN (1003999)
);

 

备注：

在本次实验中了解了分区的基本概念与相关操作。当数据量（如10T）时，索引便面临着失效的情况，分区便是一种很好的替代方法。分区的实质，是物理文件与逻辑文件一对多的情况，因此在分区情况下所使用的查询语句与未分区时完全相同，而所操作的数据又仅是相应分区的数据，而不必全表扫描，因此大大提高了效率。

但分区也有需要注意的地方。如：当插入不属于任何分区的数据时，插入操作将出错；想要在分区中建立索引的话，建立索引的字段必须处于用于分区的字段中。

 

 

 mysql各种连接

1，临时表基础实例

/*将select到的结果插入临时表 基本结构*/
CREATE TEMPORARY TABLE tmp_table(
    SELECT
        Uid
    FROM
        user_tb
);

SELECT * from tmp_table;

2，右外连接并将其插入临时表

/*右外连接*/
CREATE TEMPORARY TABLE tmp_table_right_join(
    SELECT
        user_tb.Uid, user_tb.orderNum, user_tb.wxAcount, 
	ordersum.no_ordersum, ordersum.Uid2, ordersum.orderNum01
    FROM
        user_tb RIGHT join ordersum ON user_tb.Uid = ordersum.Uid2
);

SELECT* FROM tmp_table_right_join;

执行了36s

 

3，左外连接并将其插入临时表

/*左外连接*/
CREATE TEMPORARY TABLE tmp_table_left_join(
    SELECT
        user_tb.Uid, user_tb.orderNum, user_tb.wxAcount, 
	ordersum.no_ordersum, ordersum.Uid2, ordersum.orderNum01
    FROM
        user_tb LEFT join ordersum ON user_tb.Uid = ordersum.Uid2
);

SELECT* FROM tmp_table_left_join;

 4，全外连接

/*全外连接 mysql暂不支持，采用并集方式达成*/
   SELECT *
    FROM
        user_tb LEFT join ordersum ON user_tb.Uid = ordersum.Uid2
	UNION
	SELECT *
    FROM
        user_tb RIGHT join ordersum ON user_tb.Uid = ordersum.Uid2
	
  	
SELECT COUNT(*) FROM tmp_table_full_outer_join;

 132s

 5，内连接并将其插入临时表

/*内连接*/
CREATE TEMPORARY TABLE tmp_table_inner_join(
    SELECT
        user_tb.Uid, user_tb.orderNum, user_tb.wxAcount, 
	ordersum.no_ordersum, ordersum.Uid2, ordersum.orderNum01
    FROM
        user_tb INNER JOIN ordersum ON user_tb.Uid = ordersum.Uid2
);

SELECT* FROM tmp_table_inner_join;

38s

6，交叉连接

/*交叉连接*/
CREATE TEMPORARY TABLE tmp_table_cross_join(
    SELECT
        user_tb.Uid, user_tb.orderNum, user_tb.wxAcount, 
	ordersum.no_ordersum, ordersum.Uid2, ordersum.orderNum01
    FROM
        user_tb CROSS JOIN ordersum ON user_tb.Uid = ordersum.Uid2
);

SELECT* FROM tmp_table_cross_join;