sql语句的一些小练习

时间：2010-03-28 来源：筱sone

1.删除某些字段的重复记录 CREATE TABLE users_groups (
                id int(10) unsigned NOT NULL AUTO_INCREMENT,
                uid int(11) NOT NULL,
                gid int(11) NOT NULL,
                PRIMARY KEY (id)); load data infile '/tmp/u_g.txt' into table u_g fields terminated by ',' lines terminated by '\n'; users_groups.txt内容：
1,11,502
2,107,502
3,100,503
4,110,501
5,112,501
6,104,502
7,100,502
8,100,501
9,102,501
10,104,502
11,100,502
12,100,501
13,102,501
14,110,501
mysql> select * from u_g group by uid order by id;
create table tmp select * from u_g group by uid order by id;
tun
insert into u_g select * from tmp;
删除重复记录后的结果：
mysql> select * from users_groups;
id uid gid
1 11 502
2 107 502
3 100 503
4 110 501
5 112 501
6 104 502
9 102 501
create table tmp select * from u_g group by uid order by id;
tun
insert into u_g select * from tmp;
2.外键:
create table dpt (id int primary key auto_increment,name char(10))type=innodb;
这里可以在/tmp下创建一个dpt.txt的文本，然后导入内容
load data infile '/tmp/dpt.txt' into table dpt; department表
id    name
101 MIS
102 SAL
103 ENG
create table mer(id int,name char(10),fk_dpt int,index(fk_dpt),foreign key(fk_dpt) references dpt(id) on update cascade on delete cascade)type=innodb;
insert into mer values (10,'mike',101),(11,'john',101),(12,'tom',102),(15,'jack',103); member表
id name fk_department
10 mike 101
11 john 101
12 tom 102
15 jack 103 3.连接：
cata表
cid cname
1 program
2 network
3 application
4 database
5 system
create table cata(cid int,cname char(15));
insert into cata values(1,'program'),(2,'network'),(3,'applocation'),(4,'database'),(5,'system'); bookname表
bid bname  bdate  cid
100 java  2003-03-19 1
102 vbird  2007-01-01 5
105 cisco  2002-10-10 2
106 c langusage 2006-10-19 1
107 office  2007-01-02 3
108 photoshop 2008-12-01 3
110 oracle  2009-12-03 4
create table bookname(bid int primary key,bname char(10),byear datetime,cid int);
load data infile '/tmp/bk.txt' into table bookname; 查询2005年出版的图书
select * from bookname where byear>='2005';
列出网络类图书
select * from cata where cname='network';
列出2005年以后出版的网络类图书
select * from bookname where bname='cisco' and byear>'2005';
select bookname.bname,cata.cname from bookname,cata where bookname.cid=cata.cid and byear<'2005'; 4.把bookname表复制另一份只要bid、bname、bdate字段。
create table bk select bid,bname,byear from bookname;
5.把以上表进行备份，当member表被删除后进行数据恢复。
select * from mer into outfile '/tmp/mer.txt';