mysql 基礎總結

標籤：esc   其他   delete   max   連表   prim   lex   cat   rtm   

一，基於CMD操作

1.資料庫檔案夾：

      create   database  db1;                  #建立資料庫db1

　  drop      database  db1;　　　      #刪除資料庫db1

     show    databases db1;             #查看檔案db1

     use   db1;                                 #切換到t1檔案 

2.資料表:

 建表：
 create table t1使用者表(id int auto_increment primary key,num int)

 engine=innodb default charset=utf8;

 show  tables;                   #查看建的表

 清空表：
        delete from t1;          #清除內容但id從清除的數字開始
        truncate table t1        #清除內容但id從零開始

 select count(1) from t1;        查看錶裡多少資料

 select * from t1;               查看t1表裡的內容

 desc   t1;                      查看t1是否有自增

 show create table t1 \G;        查看t1表如何建立的

 

3. 增 刪 改 查

增

   insert into tb11(name,age) values(‘alex‘,12);
   
   insert into tb11(name,age) values(‘alex‘,12),(‘root‘,18);
   
   insert into tb12(name,age) select name,age from tb11;
刪
   delete from tb12;
   delete from tb12 where id !=2 
   delete from tb12 where id =2 
   delete from tb12 where id > 2 
   delete from tb12 where id >=2 
   delete from tb12 where id >=2 or name=‘alex‘

改
   update tb12 set name=‘alex‘ where id>12 and name=‘xx‘
   update tb12 set name=‘alex‘,age=19 where id>12 and name=‘xx‘
查
   
   select * from tb12;
   
   select id,name from tb12;
   
   select id,name from tb12 where id > 10 or name =‘xxx‘;
   
   select id,name as cname from tb12 where id > 10 or name =‘xxx‘;
   
   select name,age,11 from tb12;
   
   其他：

      alter table t1 auto_increment=2;   從序號二開始插入內容

      select * from tb12 where id != 1
      select * from tb12 where id in (1,5,12);   查看1,5,12
      select * from tb12 where id not in (1,5,12);   查看除1,5,12以外的
      select * from tb12 where id in (select id from tb11)  查看錶裡具體一列，*只能一列
      select * from tb12 where id between 5 and 12; 區（閉）間固定，取5到12之間。

萬用字元：
   
   select * from tb12 where name like "a%"      舉例 assss    afffffffff
   select * from tb12 where name like "a_"       舉例 as     ad     ac


分頁：
   
      select * from tb12 limit 10;    查看前十條
      
      select * from tb12 limit 0,10;    從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;

      
   連表操作：
   
      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.tid
      



select count(id) from userinfo5;
   

 

mysql 基礎總結