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) --这里就是把两列设置成了主键
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table t6(
id int auto_increment primary key,
name char(10),
id1 int,
id2 int,
CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t1(nid,pid) --这里是设置外键
)engine=innodb default charset=utf8;
数据行的操作
数据的插入
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;
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
department5
left join userinfo5 on userinfo5.part_id = department5.id
left join userinfo6 on userinfo5.part_id = department5.id
select
score.sid,
student.sid
from
score
left join student on score.student_id = student.sid
Java技术网站 → 『 Java开发技术 』 → 『 操作系统、网络架构和数据库专题 』 → MySQL的开发必会的SQL语句
消息
查看
搜索
好友
邮件
复制
引用