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','aa@sina.com');
Query OK, 1 row affected (0.03 sec)
mysql> insert into user
-> values('nihao','123456','1999-2-5',null,'12356486541','aaa@sina.com');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user
-> values('july','123456','1999-2-5',null,'12356486541','aaaaa@sina.com');
Query OK, 1 row affected (0.02 sec)
mysql> insert into user
-> values('marry','123456','1999-2-5',null,'12356486541','aaaaa@sina.com');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user
-> values('wangwu','123456','1999-2-5',null,'12356486541','aaaaa@sina.com');
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 | aa@sina.com |
| july | 123456 | 1999-02-05 | NULL | 12356486541 | aaaaa@sina.com |
| marry | 123456 | 1999-02-05 | NULL | 12356486541 | aaaaa@sina.com |
| nihao | 123456 | 1999-02-05 | NULL | 12356486541 | aaa@sina.com |
| wangwu | 123456 | 1999-02-05 | NULL | 12356486541 | aaaaa@sina.com |
+--------+------------+------------+-----------+-------------+----------------+
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