数据库的开发必会的SQL语句

创建数据库

1. create database db1; 

删除数据库

1. drop database db1; 

创建数据表

1. create table tb1用户表( 
2.                     id int not null auto_increment primary key, 
3.                     name char(10), 
4.                     department_id int, 
5.                     p_id int, 
6.                 )engine=innodb default charset=utf8; 

主键(primary key)一个表只能有一个主键，主键可以由一列或者多列组成

外键的创建

1. CREATE TABLE t5 ( 
2.                       nid int(11) NOT NULL AUTO_INCREMENT, 
3.                       pid int(11) not NULL, 
4.                       num int(11), 
5.                       primary key(nid,pid) --这里就是把两列设置成了主键 
6.                     ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
7.  
8.                     create table t6( 
9.                         id int auto_increment primary key, 
10.                         name char(10), 
11.                         id1 int, 
12.                         id2 int, 
13.                         CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t1(nid,pid) --这里是设置外键 
14.                     )engine=innodb default charset=utf8; 

数据行的操作

数据的插入

1. insert into tb1(name,age) values('ax',8); 
2. insert into tb12(name,age) select name,age from tb11; 

表中的数据的删除

1. delete from t1; 
2. truncate table t1; 
3. drop table t1 
4. delete from tb1 where id > 10 
5. delete from tb12 where id >=2 or name='alex' 

数据的更新

1. update tb1 set name='root' where id > 10 

数据的查询

1. select * from tb; 
2. select id,name from tb; 

表结构的查看

1. show create table t1; 
2. desc t1; 

其他

1. select * from tb12 where id != 1 
2.   select * from tb12 where id in (1,5,12); 
3.   select * from tb12 where id not in (1,5,12); 
4.   select * from tb12 where id in (select id from tb11) 
5.   select * from tb12 where id between 5 and 12; 

通配符

1. select * from tb12 where name like "a%" 
2. select * from tb12 where name like "a_" 

分页

1. select * from tb12 limit 10;                     
2. select * from tb12 limit 0,10; 
3. select * from tb12 limit 10,10; 
4. select * from tb12 limit 20,10;                  
5. select * from tb12 limit 10 offset 20; 
6.  
7. # page = input('请输入要查看的页码') 
8. # page = int(page) 
9. # (page-1) * 10 
10. # select * from tb12 limit 0,10; 1  
11. # select * from tb12 limit 10,10;2 

排序

1. select * from tb12 order by id desc; 大到小 
2. select * from tb12 order by id asc;  小到大 
3. select * from tb12 order by age desc,id desc; 
4.  
5. 取后10条数据 
6. select * from tb12 order by id desc limit 10; 

分组

1. select count(id),max(id),part_id from userinfo5 group by part_id; 
2. 聚合函数有下面几个：                   
3.                     count 
4.                     max 
5.                     min 
6.                     sum 
7.                     avg 
8.  
9. **** 如果对于聚合函数结果进行二次筛选时？必须使用having **** 
10. select count(id),part_id from userinfo5 group by part_id having count(id) > 1; 
11.  
12. select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1; 

自增值设置

表自增值的设置

1. alter table t1 auto_increment=20; 
2. -- 这个就表示从开始20开始算，用上面的show create table t1\\G 就可以看到当前的值是多少。 

基于会话级别

1. -- 查看当前的会话值 
2. show session variables like 'auto_incre%' 
3. -- 设置会话步长 
4. set session auto_increment_increment=2; 
5. -- 设置起始值 
6. set session auto_increment_offset=10; 

基于全局设置

1. -- 查看全局的设置值 
2. show global variables like 'auto_inc%'; 
3. -- 设置全局步长值 
4. set global auto_increment_increment=3; 
5. -- 设置起始值 
6. set global auto_increment_offset=11; 

SQL Server 是在创建表的时候就可以自己设置，灵活度很高REATE TABLE t5 (nid int(11) NOT NULL AUTO_INCREMENT,pid int(11) NOT NULL,num int(11) DEFAULT NULL,PRIMARY KEY (nid,pid)) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8

CREATE TABLE `t6` (

nid int(11) NOT NULL AUTO_INCREMENT,pid int(11) NOT NULL,num int(11) DEFAULT NULL,PRIMARY KEY (nid,pid)) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=20 DEFAULT CHARSET=utf8

唯一索引

1. create table t1( 
2.     id int, 
3.     num int, 
4.     xx int, 
5.     unique qu1 (num ,xx) -- 意思就是这两列在一行上面数据不能相同，例如都是1,1,就不行 
6. ); 

唯一索引：约束不能重复(可以为空)主键索引：约束不能重复(不可以为空)他们的特点都是加速查询

外键一对一

1. create table userinfo1( 
2.                     id int auto_increment primary key, 
3.                     name char(10), 
4.                     gender char(10), 
5.                     email varchar(64) 
6.                 )engine=innodb default charset=utf8; 
7.  
8.                 create table admin( 
9.                     id int not null auto_increment primary key, 
10.                     username varchar(64) not null, 
11.                     password VARCHAR(64) not null, 
12.                     user_id int not null, 
13.                     unique uq_u1 (user_id), 
14.                     CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id) 
15.                 )engine=innodb default charset=utf8; 

外键多对多

1. 示例1： 
2.                 用户表 
3.                 相亲表 
4.  
5.             示例2： 
6.                 用户表 
7.                 主机表 
8.                 用户主机关系表 
9.             ===》多对多 
10.  
11.                 create table userinfo2( 
12.                     id int auto_increment primary key, 
13.                     name char(10), 
14.                     gender char(10), 
15.                     email varchar(64) 
16.                 )engine=innodb default charset=utf8; 
17.  
18.                 create table host( 
19.                     id int auto_increment primary key, 
20.                     hostname char(64) 
21.                 )engine=innodb default charset=utf8; 
22.  
23.                 create table user2host( 
24.                     id int auto_increment primary key, 
25.                     userid int not null, 
26.                     hostid int not null, 
27.                     unique uq_user_host (userid,hostid), 
28.                     CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id), 
29.                     CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id) 
30.                 )engine=innodb default charset=utf8; 

连表操作

1. select * from userinfo5,department5 
2.  
3.                     select * from userinfo5,department5 where userinfo5.part_id = department5.id 
4.  
5.                     select * from userinfo5 left join department5 on userinfo5.part_id = department5.id 
6.                     select * from department5 left join userinfo5 on userinfo5.part_id = department5.id 
7.                     # userinfo5左边全部显示 
8.  
9.                     # select * from userinfo5 right join department5 on userinfo5.part_id = department5.id 
10.                     # department5右边全部显示 
11.  
12.                     select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id 
13.                     将出现null时一行隐藏 
14.  
15. select * from  
16.                         department5  
17. left join userinfo5 on userinfo5.part_id = department5.id 
18. left join userinfo6 on userinfo5.part_id = department5.id 
19.  
20. select  
21.                         score.sid, 
22.                         student.sid  
23. from  
24.                     score 
25.  
26. left join student on score.student_id = student.sid 
27.  
28. left join course on score.course_id = course.cid 
29.  
30. left join class on student.class_id = class.cid 
31.  
32. left join teacher on course.teacher_id=teacher.ti            
33.  
34. select count(id) from userinfo5; 
    数据库（Database），简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据运行新增、截取、更新、删除等操作。 所谓“数据库”系以一定方式储存在一起、能予多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。一个数据库由多个表空间（Tablespace）构成。