您好,登錄后才能下訂單哦!
本篇內(nèi)容主要講解“MySQL的開(kāi)發(fā)必須會(huì)的SQL語(yǔ)句”,感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷,實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“MySQL的開(kāi)發(fā)必須會(huì)的SQL語(yǔ)句”吧!
創(chuàng)建數(shù)據(jù)庫(kù)
create database db1;
刪除數(shù)據(jù)庫(kù)
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_"
分頁(yè)
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)輸入要查看的頁(yè)碼') # 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è)就表示從開(kāi)始20開(kāi)始算,用上面的show create table t1\G 就可以看到當(dāng)前的值是多少。
基于會(huì)話級(jí)別
-- 查看當(dāng)前的會(huì)話值 show session variables like 'auto_incre%' -- 設(shè)置會(huì)話步長(zhǎng) set session auto_increment_increment=2; -- 設(shè)置起始值 set session auto_increment_offset=10;
基于全局設(shè)置
-- 查看全局的設(shè)置值 show global variables like 'auto_inc%'; -- 設(shè)置全局步長(zhǎng)值 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, 步長(zhǎ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, 步長(zhǎng)=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;
到此,相信大家對(duì)“MySQL的開(kāi)發(fā)必須會(huì)的SQL語(yǔ)句”有了更深的了解,不妨來(lái)實(shí)際操作一番吧!這里是億速云網(wǎng)站,更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢,關(guān)注我們,繼續(xù)學(xué)習(xí)!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。