数据库实现的留言板

mysql> create database qq
    -> character set gb2312
    -> collate gb2312_chinese_ci;
Query OK, 1 row affected (0.02 sec)

mysql> use qq
Database changed
mysql> create table user
    -> (uid varchar(20) not null,u_password varchar(20) not null,u_birthday date,u_address varchar(200),
telephone varchar(15),e_mail varchar(30) not null,
    -> primary key(uid));
Query OK, 0 rows affected (0.08 sec)

mysql> desc user;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| uid        | varchar(20)  | NO   | PRI |         |       |
| u_password | varchar(20)  | NO   |     |         |       |
| u_birthday | date         | YES  |     | NULL    |       |
| u_address  | varchar(200) | YES  |     | NULL    |       |
| telephone  | varchar(15)  | YES  |     | NULL    |       |
| e_mail     | varchar(30)  | NO   |     |         |       |
+------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> create table com
    -> (id bigint not null auto_increment,title varchar(100) not null,
    -> uid varchar(20) not null,uid2 varchar(20) not null,content varchar(2000),
    -> primary key(id));
Query OK, 0 rows affected (0.05 sec)

mysql> desc com;
+---------+---------------+------+-----+---------+----------------+
| Field   | Type          | Null | Key | Default | Extra          |
+---------+---------------+------+-----+---------+----------------+
| id      | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| title   | varchar(100)  | NO   |     |         |                |
| uid     | varchar(20)   | NO   |     |         |                |
| uid2    | varchar(20)   | NO   |     |         |                |
| content | varchar(2000) | YES  |     | NULL    |                |
+---------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> alter table com
    -> add constraint fk_uid1 foreign key (uid) references user(uid);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table com
    -> add constraint fk_uid2 foreign key (uid2) references user(uid);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show indexes from com;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| com   |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| com   |          1 | fk_uid1  |            1 | uid         | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| com   |          1 | fk_uid2  |            1 | uid2        | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

mysql> create table friend
    -> (uid varchar(20),uid2 varchar(20),primary key (uid,uid2));
Query OK, 0 rows affected (0.05 sec)

mysql> alter table friend
    -> add constraint fk_uf1 foreign key (uid) references user(uid);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table friend\
    -> c
    -> \c
mysql> alter table friend
    -> add constraint fk_uf2 foreign key (uid2) references user(uid);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user
    -> ;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| uid        | varchar(20)  | NO   | PRI |         |       |
| u_password | varchar(20)  | NO   |     |         |       |
| u_birthday | date         | YES  |     | NULL    |       |
| u_address  | varchar(200) | YES  |     | NULL    |       |
| telephone  | varchar(15)  | YES  |     | NULL    |       |
| e_mail     | varchar(30)  | NO   |     |         |       |
+------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> insert into user
    -> values('hello','123456','1999-2-5',null,'12356486541','[e-mail]');
Query OK, 1 row affected (0.03 sec)

mysql> insert into user
    -> values('nihao','123456','1999-2-5',null,'12356486541','[e-mail]');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user
    -> values('july','123456','1999-2-5',null,'12356486541','[e-mail]');
Query OK, 1 row affected (0.02 sec)

mysql> insert into user
    -> values('marry','123456','1999-2-5',null,'12356486541','[e-mail]');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user
    -> values('wangwu','123456','1999-2-5',null,'12356486541','[e-mail]');
Query OK, 1 row affected (0.02 sec)

mysql> insert into friend
    -> values('july','wangwu');
Query OK, 1 row affected (0.01 sec)

mysql> insert into friend
    -> values('july','hello');
Query OK, 1 row affected (0.01 sec)

mysql> insert into friend
    -> values('july','nihao');
Query OK, 1 row affected (0.02 sec)

mysql> insert into friend
    -> values('hello','nihao');
Query OK, 1 row affected (0.03 sec)

mysql> insert into friend
    -> values('marry','nihao');
Query OK, 1 row affected (0.02 sec)

mysql> insert into friend
    -> values('hello','july');
Query OK, 1 row affected (0.03 sec)

mysql> select * from user;
+--------+------------+------------+-----------+-------------+----------------+
| uid    | u_password | u_birthday | u_address | telephone   | e_mail         |
+--------+------------+------------+-----------+-------------+----------------+
| hello  | 123456     | 1999-02-05 | NULL      | 12356486541 | [e-mail]    |
| july   | 123456     | 1999-02-05 | NULL      | 12356486541 | [e-mail] |
| marry  | 123456     | 1999-02-05 | NULL      | 12356486541 | [e-mail] |
| nihao  | 123456     | 1999-02-05 | NULL      | 12356486541 | [e-mail]   |
| wangwu | 123456     | 1999-02-05 | NULL      | 12356486541 | [e-mail] |
+--------+------------+------------+-----------+-------------+----------------+
5 rows in set (0.01 sec)

mysql> select * from friend;
+-------+--------+
| uid   | uid2   |
+-------+--------+
| july  | hello  |
| hello | july   |
| hello | nihao  |
| july  | nihao  |
| marry | nihao  |
| july  | wangwu |
+-------+--------+
6 rows in set (0.00 sec)

mysql> select uid2 from friend
    -> where uid='july';
+--------+
| uid2   |
+--------+
| hello  |
| nihao  |
| wangwu |
+--------+
3 rows in set (0.01 se
mysql> set names gbk;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into com
    -> values(1,'welcome','july','hello','欢迎您！');
Query OK, 1 row affected (0.03 sec)
mysql> insert into com(title,uid,uid2,content)
    -> values('你好','july','hello','你好啊');
Query OK, 1 row affected (0.01 sec)

mysql> select * from com;
+----+---------+------+-------+----------+
| id | title   | uid  | uid2  | content  |
+----+---------+------+-------+----------+
|  1 | welcome | july | hello | 欢迎您！ |
|  2 | 你好    | july | hello | 你好啊   |
+----+---------+------+-------+----------+
2 rows in set (0.00 sec)

mysql> insert into com(title,uid,uid2,content)
    -> values('你好','july','wangwu','你好啊');
Query OK, 1 row affected (0.03 sec)

mysql> select com.uid,uid2,u_address,telephone,u_birthday,content from
    -> com join user on com.uid2=user.uid;
+------+--------+-----------+-------------+------------+----------+
| uid  | uid2   | u_address | telephone   | u_birthday | content  |
+------+--------+-----------+-------------+------------+----------+
| july | hello  | NULL      | 12356486541 | 1999-02-05 | 欢迎您！ |
| july | hello  | NULL      | 12356486541 | 1999-02-05 | 你好啊   |
| july | wangwu | NULL      | 12356486541 | 1999-02-05 | 你好啊   |
+------+--------+-----------+-------------+------------+----------+
3 rows in set (0.00 sec)
mysql> select com.uid,uid2,u_address,telephone,u_birthday,content from
    -> com join user on com.uid2=user.uid
    -> where uid2='hello';
+------+-------+-----------+-------------+------------+----------+
| uid  | uid2  | u_address | telephone   | u_birthday | content  |
+------+-------+-----------+-------------+------------+----------+
| july | hello | NULL      | 12356486541 | 1999-02-05 | 欢迎您！ |
| july | hello | NULL      | 12356486541 | 1999-02-05 | 你好啊   |
+------+-------+-----------+-------------+------------+----------+
2 rows in set (0.00 sec)

mysql> notee