|
本文談?wù)凪ySQL的開發(fā)必會(huì)的sql語句 創(chuàng)建數(shù)據(jù)庫create database db1;
刪除數(shù)據(jù)庫drop database db1;
創(chuàng)建數(shù)據(jù)表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）一個(gè)表只能有一個(gè)主鍵，主鍵可以由一列或者多列組成
外鍵的創(chuàng)建CREATE TABLE t5 (
nid int(11) NOT NULL AUTO_INCREMENT,
pid int(11) not NULL,
num int(11),
primary key(nid,pid) --這里就是把兩列設(shè)置成了主鍵
) 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) --這里是設(shè)置外鍵
)engine=innodb default charset=utf8;
數(shù)據(jù)行的操作數(shù)據(jù)的插入insert into tb1(name,age) values('ax',8);
insert into tb12(name,age) select name,age from tb11;
表中的數(shù)據(jù)的刪除delete from t1;
truncate table t1;
drop table t1
delete from tb1 where id > 10
delete from tb12 where id >=2 or name='alex'
數(shù)據(jù)的更新update tb1 set name='root' where id > 10
數(shù)據(jù)的查詢select * from tb;
select id,name from tb;
表結(jié)構(gòu)的查看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('請(qǐng)輸入要查看的頁碼')
# 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條數(shù)據(jù)
select * from tb12 order by id desc limit 10;
分組select count(id),max(id),part_id from userinfo5 group by part_id;
聚合函數(shù)有下面幾個(gè)：
count
max
min
sum
avg
**** 如果對(duì)于聚合函數(shù)結(jié)果進(jìn)行二次篩選時(shí)？必須使用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;
自增值設(shè)置表自增值的設(shè)置alter table t1 auto_increment=20;
-- 這個(gè)就表示從開始20開始算，用上面的show create table t1\G 就可以看到當(dāng)前的值是多少。
基于會(huì)話級(jí)別-- 查看當(dāng)前的會(huì)話值
show session variables like 'auto_incre%'
-- 設(shè)置會(huì)話步長
set session auto_increment_increment=2;
-- 設(shè)置起始值
set session auto_increment_offset=10;
基于全局設(shè)置-- 查看全局的設(shè)置值
show global variables like 'auto_inc%';
-- 設(shè)置全局步長值
set global auto_increment_increment=3;
-- 設(shè)置起始值
set global auto_increment_offset=11;
sql server 是在創(chuàng)建表的時(shí)候就可以自己設(shè)置，靈活度很高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) -- 意思就是這兩列在一行上面數(shù)據(jù)不能相同，例如都是1,1,就不行
);
唯一索引：約束不能重復(fù)（可以為空）主鍵索引：約束不能重復(fù)（不可以為空）他們的特點(diǎn)都是加速查詢
外鍵一對(duì)一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;
外鍵多對(duì)多示例1：
用戶表
相親表
示例2：
用戶表
主機(jī)表
用戶主機(jī)關(guān)系表
===》多對(duì)多
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
將出現(xiàn)null時(shí)一行隱藏
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;
|
