本文談?wù)凪ySQL的開(kāi)發(fā)必會(huì )的sql語(yǔ)句
create database db1;drop database db1;create table tb1用戶(hù)表( 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è)主鍵，主鍵可以由一列或者多列組成
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 t1delete from tb1 where id > 10delete from tb12 where id >=2 or name='alex'update tb1 set name='root' where id > 10select * 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('請輸入要查看的頁(yè)碼')# page = int(page)# (page-1) * 10# select * from tb12 limit 0,10; 1 # select * from tb12 limit 10,10;2select * 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;聚合函數有下面幾個(gè)： count max min sum avg**** 如果對于聚合函數結果進(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;alter table t1 auto_increment=20;-- 這個(gè)就表示從開(kāi)始20開(kāi)始算，用上面的show create table t1\G 就可以看到當前的值是多少。-- 查看當前的會(huì )話(huà)值show session variables like 'auto_incre%'-- 設置會(huì )話(huà)步長(cháng)set session auto_increment_increment=2;-- 設置起始值set session auto_increment_offset=10;-- 查看全局的設置值show global variables like 'auto_inc%';-- 設置全局步長(cháng)值set global auto_increment_increment=3;-- 設置起始值set global auto_increment_offset=11;sql server 是在創(chuàng )建表的時(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, 步長(cháng)=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, 步長(cháng)=20 DEFAULT CHARSET=utf8
create table t1( id int, num int, xx int, unique qu1 (num ,xx) -- 意思就是這兩列在一行上面數據不能相同，例如都是1,1,就不行);唯一索引：約束不能重復（可以為空）主鍵索引：約束不能重復（不可以為空）他們的特點(diǎn)都是加速查詢(xún)
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： 用戶(hù)表 相親表 示例2： 用戶(hù)表 主機表 用戶(hù)主機關(guān)系表 ===》多對多 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時(shí)一行隱藏select * from department5 left join userinfo5 on userinfo5.part_id = department5.idleft join userinfo6 on userinfo5.part_id = department5.idselect 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;聯(lián)系客服
