mysql语句engin_MySQL必会的SQL语句

本文谈谈MySQL的开发必会的sql语句

创建数据库

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) --这里就是把两列设置成了主键

) 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;

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=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

唯一索引

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

left join course on score.course_id = course.cid

left join class on student.class_id = class.cid

left join teacher on course.teacher_id=teacher.ti

select count(id) from userinfo5;