创建数据库
create database db1;删除数据库
drop database db1;创建数据表
create table tb1用户表( id int not null auto_increment primary key, name char(10), department_id int, p_id int)engine=innodb default charset=utf8;主键（primary key）一个表只能有一个主键，主键可以由一列或者多列组成
外键的创建
CREATE TABLE t5 ( nid int(11) NOT NULL AUTO_INCREMENT, pid int(11) not NULL, num int(11), primary key(nid,pid) 数据行的操作
数据的插入
insert into tb1(name,age) values('ax',8);insert into tb12(name,age) select name,age from tb11;表中数据的删除
delete from t1;truncate table t1;drop table t1;delete from tb1 where id > 10;delete from tb12 where id >=2 or name='alex';数据的更新
update tb1 set name='root' where id > 10数据的查询
select * from tb;select id,name from tb;`表结构的查看
show create table t1;desc t1;其他
select * from tb12 where id != 1;select * from tb12 where id in (1,5,12);select * from tb12 where id not in (1,5,12);select * from tb12 where id in (select id from tb11);select * from tb12 where id between 5 and 12;通配符
select * from tb12 where name like "a%";select * from tb12 where name like "a_";分页
select * from tb12 limit 10;select * from tb12 limit 0,10;select * from tb12 limit 10,10;select * from tb12 limit 20,10;select * from tb12 limit 10 offset 20; # page = input('请输入要查看的页码') # page = int(page) # (page-1) * 10 # select * from tb12 limit 0,10; 1 # select * from tb12 limit 10,10;2排序
select * from tb12 order by id desc; 大到小select * from tb12 order by id asc; 小到大select * from tb12 order by age desc,id desc; 取后10条数据 select * from tb12 order by id desc limit 10;分组
select count(id),max(id),part_id from userinfo5 group by part_id;聚合函数有下面几个： count max min sum avg**** 如果对于聚合函数结果进行二次筛选时？必须使用having ****select count(id),part_id from userinfo5 group by part_id having count(id) > 1;select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;自增值设置
表自增值的设置
alter table t1 auto_increment=20;-- 这个就表示从开始20开始算，用上面的show create table t1\G 就可以看到当前的值是多少。基于会话级别
-- 查看当前的会话值show session variables like 'auto_incre%'-- 设置会话步长set session auto_increment_increment=2;-- 设置起始值set session auto_increment_offset=10;基于全局设置
-- 查看全局的设置值show global variables like 'auto_inc%';-- 设置全局步长值set global auto_increment_increment=3;-- 设置起始值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=utf8CREATE 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
唯一索引
create table t1( id int, num int, xx int, unique qu1 (num ,xx) -- 意思就是这两列在一行上面数据不能相同，例如都是1,1,就不行);唯一索引：约束不能重复（可以为空）主键索引：约束不能重复（不可以为空）他们的特点都是加速查询
外键一对一
create table userinfo1( id int auto_increment primary key, name char(10), gender char(10), email varchar(64))engine=innodb default charset=utf8;create table admin( id int not null auto_increment primary key, username varchar(64) not null, password VARCHAR(64) not null, user_id int not null, unique uq_u1 (user_id), CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id))engine=innodb default charset=utf8;外键多对多
示例1： 用户表 相亲表示例2： 用户表 主机表 用户主机关系表===》多对多create table userinfo2( id int auto_increment primary key, name char(10), gender char(10), email varchar(64))engine=innodb default charset=utf8;create table host( id int auto_increment primary key, hostname char(64))engine=innodb default charset=utf8;create table user2host( id int auto_increment primary key, userid int not null, hostid int not null, unique uq_user_host (userid,hostid), CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id), CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id))engine=innodb default charset=utf8;连表操作
select * from userinfo5,department5;select * from userinfo5,department5 where userinfo5.part_id = department5.id;select * from userinfo5 left join department5 on userinfo5.part_id = department5.id;select * from department5 left join userinfo5 on userinfo5.part_id = department5.id;# userinfo5左边全部显示# select * from userinfo5 right join department5 on userinfo5.part_id = department5.id# department5右边全部显示select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id将出现null时一行隐藏select * from department5left join userinfo5 on userinfo5.part_id = department5.idleft join userinfo6 on userinfo5.part_id = department5.id;select score.sid,student.sid from scoreleft join student on score.student_id = student.sidleft join course on score.course_id = course.cidleft join class on student.class_id = class.cidleft join teacher on course.teacher_id=teacher.ti;select count(id) from userinfo5;