本文談談MySQL的開發(fā)必會的sql語句

[[322273]]

 

創(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)一個表只能有一個主鍵，主鍵可以由一列或者多列組成

外鍵的創(chuàng)建

 

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; 

數(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; 

表結構的查看

 

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條數(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ù)有下面幾個：                   
                    count 
                    max 
                    min 
                    sum 
                    avg 
 
**** 如果對于聚合函數(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; 
-- 這個就表示從開始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 是在創(chuàng)建表的時候就可以自己設置，靈活度很高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,就不行 
); 

唯一索引：約束不能重復(可以為空)主鍵索引：約束不能重復(不可以為空)他們的特點都是加速查詢

外鍵一對一

 

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 
                    將出現(xiàn)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;