软件开发项目实践（一）

1.自己设计至少一张表，字段不少于10个，包含主键自增、自动生成时间戳等。业务根据自己的兴趣爱好，例如电影、美食、图书、旅游均可以。

mysql> desc user;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field       | Type        | Null | Key | Default           | Extra                       |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id          | int(11)     | NO   | PRI | NULL              | auto_increment              |
| phone       | varchar(11) | NO   | PRI | NULL              |                             |
| passwd      | varchar(16) | NO   |     | NULL              |                             |
| nickname    | varchar(20) | NO   |     | NULL              |                             |
| gender      | varchar(5)  | NO   |     | NULL              |                             |
| home        | varchar(50) | NO   |     | NULL              |                             |
| status      | varchar(30) | NO   |     | NULL              |                             |
| birth       | varchar(50) | NO   |     | NULL              |                             |
| body_height | int(11)     | NO   |     | NULL              |                             |
| body_weight | int(11)     | NO   |     | NULL              |                             |
| createTime  | timestamp   | NO   |     | CURRENT_TIMESTAMP |                             |
| updateTime  | datetime    | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+-------------------+-----------------------------+
12 rows in set (0.00 sec)

2. 验证事务的四个特性以及四个隔离级别。

实践所采取的数据库为MySQL，MySQL中的事物主要用于处理操作量大，复杂度高的数据。用我自己的话来说，我们每一个人都有属于自己的一个校园邮箱，毕业后这个邮箱会被收回并且注销，说白了，实际上就是当我们毕业后，教务会把我们的信息给删除了，并且还会告知网管中心把这个学生的邮箱账号也给删除，甚至还会告知其它部门同步的把这个学生对应的信息统统删除。现在都讲信息化，我们需要一套流程去规范它，这套流程就可以理解为事物，说得再实际一些，就是一坨SQL语句的集合。

事物有四大特性和四大隔离级别。四大特性分别为：

原子性： 事物中的所有操作，要么全部执行完，要么全部不执行。就好比，有可能这个学生留级了要回来重新读书，所以教务不能够因为毕业时间到就把所有学生信息都删除，而是要等待一个迂回时间进行统一。
一致性： 在事物执行的整个生命周期中，数据库的完整性没有被破坏，包括实体完整性、参照完整性、用户自定义完整性等。
隔离性： 包括事物的四个隔离级别。这个性质比较重要，后边细说。
持久性： 事物执行完毕后，对数据的修改就是永久的，宕机也是OJBK的。🙂

四大隔离级别为：

未提交读： 该隔离级别下避免数据丢失，只要你更新了数据就一定能够保证更新成功，但却有可能（基本上100%）出现脏读，也就是可能读取到其他会话中未提交事务修改的数据。说的再透彻些，就是指当一个事务正在执行访问数据的SQL，并且对数据进行了修改，而这种修改还没有commit到数据库中，此时另外一个事务也执行了相同的访问数据SQL，那么就会读取到前面事物中修改的数据。
提交读： 在该隔离级别下，避免了脏读，只能读取到已经提交的数据。当事务还没有结束时，另外一个事务也访问该同一table下的数据，此时并不能读到第一个事物修改的数据，但正因为如此，又有可能出现了不可重复读问题。
可重复读： 在同一个事务内的查询都是事务开始时刻一致的。若在A时刻创建了事物一，随后又在B时刻创建了事物二，那么位于AB两个时刻下创建的事物只在之前时刻的数据是一致的，此后位于时刻A创建的事物一与位于时刻B创建的事物二再无任何关系（因为会被锁住该事物下的所有行），但是却会出现幻读。
串行读： 该隔离级别下会发现比如完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞，有事物没有被提交，其它事物只能等着。

说的可能有些些的绕口，用我自己的话来说，就好比要去抄别人的作业。未提交读 的意思是：我随时可以看同桌试卷上的答案，不管他此时此刻到底写没写题，读没读题，我想看的时候，就去看好了。提交读 的意思是：我只能看到同桌写完并且停笔的题。没写过的题或者写了没停笔的题，我是看不了的。可重复读 的意思：我开始写题之前，必须要小心，因为一旦选定某个时机开始写题后，就再也不能看到同桌写的题目了，假设我8点半开始写题，那我就只能看到8点半此时同桌完成的题目，随后的任何时间里我都看不到了，除非等待下一场考试。串行读 的意思：这个隔离级别下我和同桌简直就是难兄难弟，他写完一道题后，我抄一道题，我抄完一道题后，他开始写一道题（如果你的同桌愿意这么干的话），也可以认为你的同桌对你不好，你要抄他的题之前，必须要得到他的允许。（用了自己的大白话说了一遍，比较尴尬。肯定有错误的地方，不过也可以先这么构建起来。

接下来先看看如何实现为提交读，也就是如果不管session1和session2有何关系，只要session1中进行了修改，那么session2中就可以读到。

不过在进行实验之前，我们首先得先确定自己所使用的数据库是否支持事物操作（不过如果你是mysql就算了，那是一定支持的的）

1
2
3

show ENGINES

-- Engine字段下为InnoDB为支持事物操作的存储引擎

创建一个database，创建一张table，（此处全略过），接下来，使用该sql语句查看当前session的事物隔离级别：

select @@tx_isolation;

-- 我的session1和session2执行结果为：
-- REPEATABLE-READ

我们可以先来验证一波关于可重复读，

-- session1开启事物
begin

select * from user;
-- select结果为空数据

-- session2 开启事物
begin

insert into user(phone, passwd, nickname, gender, home, status, birth, body_height, body_weight) value('18811758987', 'qweqwe123qweqwe', 'kkl', 'man', 'china', 'travel', '1997/12/05', '165', '67');

commit
-- session2 commit

-- session1
select * from user;
-- select结果为空数据，此时session1并未查到session2提交数据，解决了脏读
commit
-- session1 commit

继续测试可重复读，

-- 清空user表
truncate table user;

--session1 开启事物
begin

select * from user;
-- select结果为空数据

-- session2 开启事物
begin

insert into user(phone, passwd, nickname, gender, home, status, birth, body_height, body_weight) value('18811758987', 'qweqwe123qweqwe', 'kkl', 'man', 'china', 'travel', '1997/12/05', '165', '67');
-- session2 commit
commit

-- session1
select * from user;
-- session1 select返回空数据

-- 此时执行update passwd
update user set passwd='woaiwoziji321'

-- 😱恐怖的事情发生了！明明上一次select出来的空集合，但是却update成功了！！！

-- 此时再select一次
select * from user;
-- 返回了session2中insert的数据

对可重读读的第二次测试出现了幻读，接下来验证read uncommit，

1 2	-- 设置隔离级别 set session transaction isolation level read uncommitted;

-- session1 开启事物
begin

-- session2 开启事物
begin

select * from user;
-- 返回空集合

-- session1 insert
insert into user(phone, passwd, nickname, gender, home, status, birth, body_height, body_weight) value('18811758987', 'qweqwe123qweqwe', 'kkl', 'man', 'china', 'travel', '1997/12/05', '165', '67');

-- session2 select
select * from user;
-- 😱返回了session1中insert的数据

-- session1 commit
commit

-- session2 commit
commit

未提交读避免了更新丢失，但是却导致了脏读，验证read committed，

1 2	-- 设置隔离级别 set session transaction isolation level read committed;

-- session1 开启事物
begin

-- session1 select
select * from user;
-- 返回空集合

-- session2 开启事物
begin
-- session2 select
select * from user;
-- 返回空集合

-- session2 insert
insert into user(phone, passwd, nickname, gender, home, status, birth, body_height, body_weight) value('18811758987', 'qweqwe123qweqwe', 'kkl', 'man', 'china', 'travel', '1997/12/05', '165', '67');

-- session1 select
select * from user;
-- 返回了空集合

-- session2 commit
commit

-- session1 select
select * from user;
-- 😱返回了session2中insert的数据

-- session1 commit
commit

提交读避免了脏读，却又出现了不可重复读，最后来验证Serializable,

-- 改变session1的隔离级别为 serializable
set session transaction isolation level serializable;
-- session1 开启事物
begin

-- session2 开启事物
begin

-- session1 select
select * from user;

-- session2 insert
insert into user(phone, passwd, nickname, gender, home, status, birth, body_height, body_weight) value('18811758987', 'qweqwe123qweqwe', 'kkl', 'man', 'china', 'travel', '1997/12/05', '165', '67');

-- 此时会进行等待，如果你用terminal，那就是啥都不出现，如果你用DB manager会出现loading

-- session1 select
select * from user;
-- 返回空集合

-- session commit
commit
-- commit结束后，session2的loading结束，插入数据完成

-- session1 select
select * from user;
-- 返回空集合

-- session2 commit
commit

3. 利用insert into使该表的数据量达到百万级别。
实验文档给了个提示，使用insert into来进行，那么我的猜想，老师应该不会让我们按20次键盘来达到所谓的百万级别又或者其它数据，因此我继续猜想，老师应该是想让我们用mysql的自定义函数来做，因此，写了一个addRowToMillion的函数，

delimiter $$
create function addRowToMillion(x int) returns int
begin
    declare i int default 1;
    declare sum int default 0;
    pjwhile:while i < x do
        insert into user(
	phone, passwd, nickname, gender, home, status, birth, body_height, body_weight
	) select
	phone, passwd, nickname, gender, home, status, birth, body_height, body_weight
	from user;
        set i = i + 1;
        set sum = i;
    end while;
    return sum;
end
$$
delimiter ;

然后直接使用select addRowToMillion(times)，即可达到把table中数据拉到自己想要的级别，当然前提得是你的table中务必保证有至少一条数据。

4. 自己定义一个业务实现，业务不做硬性规定，但是技术点要包括：分组，排序，avg、sum、max、min中至少两个,索引，触发器，存储过程，事件。
一个用户可以发布多篇文章和多个评论，一篇文章下可以有多个用户的评论。

新增两张表，分别为m_artcle文章表，如下所示：

+-------------+-----------+------+-----+-------------------+-----------------------------+
| Field       | Type      | Null | Key | Default           | Extra                       |
+-------------+-----------+------+-----+-------------------+-----------------------------+
| id          | int(11)   | NO   | PRI | NULL              | auto_increment              |
| owner_id    | int(11)   | NO   | PRI | NULL              |                             |
| content     | text      | NO   |     | NULL              |                             |
| create_time | timestamp | NO   |     | CURRENT_TIMESTAMP |                             |
| update_time | datetime  | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-----------+------+-----+-------------------+-----------------------------+

m_comment表，如下所示：

+-------------+-----------+------+-----+-------------------+-----------------------------+
| Field       | Type      | Null | Key | Default           | Extra                       |
+-------------+-----------+------+-----+-------------------+-----------------------------+
| id          | int(11)   | NO   | PRI | NULL              | auto_increment              |
| owner_id    | int(11)   | NO   | PRI | NULL              |                             |
| artcle_id   | int(11)   | NO   | PRI | NULL              |                             |
| content     | text      | NO   |     | NULL              |                             |
| create_time | timestamp | NO   |     | CURRENT_TIMESTAMP |                             |
| update_time | datetime  | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-----------+------+-----+-------------------+-----------------------------+

建立普通索引，使得加快查询某一用户所发表的所有文章数据速度：

1	alter table m_artcle add index(owner_id)

获取某一用户发布的所有文章，按时间降序排序

1
2
3

select * from m_artcle
where owner_id = 4
order by create_time desc

获取某一用户被发表评论数最多的文章：

select id, content from m_artcle
  where id = (select artcle_id from m_comment
	group by artcle_id
	order by count(artcle_id) desc limit 1
	)

当注销一个用户后，也要把其发布的文章和评论都一一删除，编写一个触发器：

delimiter ;;
create trigger `tri_delete_user` after delete on `user` for each row
begin
    delete from m_artcle where owner_id = old.id;
    delete from m_comment where owner_id = old.id;
end
;;
DELIMITER ;

查看一个用户发布文章数，和一个文章下的评论数，使用存储过程进行：
PS : 存储过程是存储在数据库服务器中的一组sql语句，通过在查询中调用一个指定的名称来执行这些sql语句命令.(来自网络)

-- 查看某个用户发布的文章数
delimiter ;;
create procedure `select_artcle_count`(ownerID int)
begin
    select count(id) from m_artcle where owner_id = ownerID;
end;;
delimiter ;

-- 执行
call select_artcle_count(3)

-- 查看某个文章下的评论数
delimiter ;;
create procedure `select_comment_count`(artcleID int)
begin
    select count(id) from m_comment where artcle_id = artcleID;
end;;
delimiter ;

-- 执行
call select_comment_count(3)

每一小时执行统计一遍当前发布的文章数（测试，10秒即可），在正式运行之前得先判断当前数据库是否开启事件功能，当然如果你是mysql 5.1之前的版本就别看了，不支持的，不过我猜现在应该也没人会去装5.1之前的版本吧。

需要新增一张用户计数的c_artcle表，

+--------------+-----------+------+-----+-------------------+-----------------------------+
| Field        | Type      | Null | Key | Default           | Extra                       |
+--------------+-----------+------+-----+-------------------+-----------------------------+
| id           | int(11)   | NO   | PRI | NULL              | auto_increment              |
| artcle_count | int(11)   | NO   |     | NULL              |                             |
| create_time  | timestamp | NO   |     | CURRENT_TIMESTAMP |                             |
| update_time  | datetime  | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+-----------+------+-----+-------------------+-----------------------------+

-- 查看当前是否开启
show variables like 'event_scheduler';
-- 若为off，啧需要打开
set global event_scheduler = on;

如果想要每次重启数据库或者服务器（或者你自己的电脑）时都要保证这个事件是启动的，那么就需要去修改mysql的配置文件，Mac下的mysql配置文件路径为

1	$ /usr/local/mysql/support-files/my-default.cnf

使用文本编辑器打开后，在该文件末尾加上如下一行即可（我没试过🙂

1	event_scheduler=ON

创建一个每隔十秒中就自动统计m_artcle表中发布文章数的事件，如下所示，

DELIMITER ;;
create event `event_artcleCount_second` on schedule every 10 second starts now() on completion preserve enable do
BEGIN
  insert into c_artcle(artcle_count)
  select count(*) from _artcle where unix_timestamp(now()) - unix_timestamp(create_time) < 10;
END
;;
DELIMITER ;

5. 自己查询资料，验证在何种情况下无法走到索引，例如like，找到至少3种情况。
以下内容均来自网络。🙂

条件字段选择性弱，查出的结果集较大，不走索引；
where条件等号两边字段类型不同，不走索引；
优化器分析的统计信息陈旧也可能导致不走索引；
索引字段 is null 不走索引；
对于count(*) 当索引字段有not null约束时走索引，否则不走索引；
like 后面的字符当首位为通配符时不走索引；
使用不等于操作符如：<>、!= 等不走索引；
索引字段前加了函数或参加了运算不走索引；