Mysql重新整理筆記--woods備忘，mysql--woods

Mysql重新整理筆記--woods備忘，mysql--woods
==============================SQL備忘 CRUD 查詢 多表 事件等===============================
--------------------------------------------------------------------------------------------------一.資料庫 1.建立資料庫  create database [if not exists] db_name [character set xxx] [collate xxx]  *建立一個名稱為mydb1的資料庫。   create database mydb1;  *建立一個使用utf8字元集的mydb2資料庫。   create database mydb2 character set utf8;  *建立一個使用utf8字元集，並帶校對規則的mydb3資料庫。   create database mydb3 character set utf8 collate utf8_bin ; 2.查看資料庫  show databases;查看所有資料庫  show create database db_name; 查看資料庫的建立方式 3.修改資料庫  alter database db_name [character set xxx] [collate xxxx] 4.刪除資料庫  drop database [if exists] db_name; 5.使用資料庫  切換資料庫 use db_name;  查看當前使用的資料庫 select database();--------------------------------------------------------------------------------------------------二、表 1.建立表   create table tab_name(    field1 type,    field2 type,    ...    fieldn type   )[character set xxx][collate xxx];      ****java和mysql的資料類型比較    String  ----------------------  char(n) varchar(n) 255  65535    byte short int long float double -------------- tinyint  smallint int bigint float double    boolean ------------------ bit 0/1    Date ------------------ Date、Time、DateTime、timestamp    FileInputStream FileReader  ------------------------------ Blob Text       *建立一個員工表employee    create table employee(     id int primary key auto_increment ,     name varchar(20),     gender bit default 1,     birthday date,     entry_date date,     job varchar(20),     salary double,     resume text    );       約束:     primary key     unique     not null     auto_increment 主鍵欄位必須是數字類型。     外鍵約束  2.查看錶資訊   desc tab_name 查看錶結構   show tables 查看當前資料庫中的所有的表   show create table tab_name 查看當前資料庫表建表語句  3.修改表結構    (1)增加一列     alter table tab_name add [column] 列名 類型;    (2)修改一列類型     alter table tab_name modify 列名 類型;    (3)修改列名     alter table tab_name change [column] 列名 新列名 類型;    (4)刪除一列     alter table tab_name drop [column] 列名;    (5)修改表名     rename table 表名 to 新表名;    (6)修該表所用的字元集     alter table student character set utf8;        4.刪除表    drop table tab_name;      --------------------------------------------------------------------------------------------------三、表記錄 1.增加一條記錄insert  insert into tab_name (field1,filed2,.......) values (value1,value2,.......);  *插入的資料應與欄位的資料類型相同。  *資料的大小應在列的規定範圍內，例如：不能將一個長度為80的字串加入到長度為40的列中。  *在values中列出的資料位元置必須與被加入的列的排列位置相對應。  *字元和日期型資料應包含在單引號中'zhang' '2013-04-20'  *插入空值：不指定某列的值或insert into table value(null)，則該列取空值。  *如果要插入所有欄位可以省寫列列表，直接按表中欄位順序寫值列表insert into tab_name values(value1,value2,......);   *練習：使用insert語句向表中插入三個員工的資訊。  insert into emp (name,birthday,entry_date,job,salary) values ('張飛','1990-09-09','2000-01-01','打手',999);  insert into emp (name,birthday,entry_date,job,salary) values ('關羽','1989-08-08','2000-01-01','財神',9999);  insert into emp (name,birthday,entry_date,job,salary) values ('趙雲','1991-07-07','2000-01-02','保安',888);  insert into emp values (7,'黃忠',1,'1970-05-05','2001-01-01','戰神',1000,null);
 2.修改表記錄   update tab_name set field1=value1,field2=value2,......[where 語句]   *UPDATE文法可以用新值更新原有表行中的各列。   *SET子句指示要修改哪些列和要給予哪些值。   *WHERE子句指定應更新哪些行。如沒有WHERE子句，則更新所有的行。   *實驗：    將所有員工薪水修改為5000元。    update emp set salary=5000;    將姓名為’zs’的員工薪水修改為3000元。    update emp set salary=3000 where name='zs';    將姓名為’ls’的員工薪水修改為4000元,job改為ccc。    update emp set salary=4000,job='ccc' where name='zs';    將wu的薪水在原有基礎上增加1000元。    update emp set salar=salary+4000 where name='wu';    3.刪除表操作   delete from tab_name [where ....]   *如果不跟where語句則刪除整張表中的資料   *delete只能用來刪除一行記錄，不能值刪除一行記錄中的某一列值（這是update操作）。   *delete語句只能刪除表中的內容，不能刪除表本身，想要刪除表，用drop   *同insert和update一樣，從一個表中刪除記錄將引起其它表的參照完整性問題，在修改資料庫資料時，頭腦中應該始終不要忘記這個潛在的問題。   *TRUNCATE TABLE也可以刪除表中的所有資料，詞語句首先摧毀表，再建立表。此種方式刪除的資料不能在事務中恢複。   *實驗：    刪除表中名稱為’zs’的記錄。    delete from emp where name='黃忠';    刪除表中所有記錄。    delete from emp;    使用truncate刪除表中記錄。    truncate table emp;     
  4.select操作  （1）select [distinct] *|field1，field2，......   from tab_name 其中from指定從哪張表篩選，*表示尋找所有列，也可以指定一個列列表明確指定要尋找的列，distinct用來剔除重複行。    *實驗：     查詢表中所有學生的資訊。     select * from exam;     查詢表中所有學生的姓名和對應的英語成績。     select name,english from exam;     過濾表中重複資料。     select distinct english from exam;  （2）select 也可以使用運算式，並且可以使用 as 別名     在所有學生分數上加10分特長分顯示。     select name,english+10,chinese+10,math+10 from exam;     統計每個學生的總分。     select name,english+chinese+math from exam;     使用別名表示學生總分。     select name,english+chinese+math as 總成績 from exam;     select name,english+chinese+math 總成績 from exam;     select name english from exam;  （3）使用where子句，進行過濾查詢。      *練習：     查詢姓名為XXX的學產生績     select * from exam where name='張飛';     查詢英語成績大於90分的同學     select name,english from exam where english>90;     查詢總分大於200分的所有同學     select name,math+english+chinese as 總成績 from exam where math+english+chinese>200 ;     *where字句中可以使用：       *比較子：          > < >= <= <>            between 10 and 20 值在10到20之間            in(10,20,3)值是10或20或30          like '張pattern' pattern可以是%或者_，如果是%則表示任意多字元，此例中張三丰 張飛 張abcd ，如果是_則表示一個字元張_，張飛符合。張          Is null                *邏輯運算子        在多個條件直接可以使用邏輯運算子 and or not     *實驗      查詢英語分數在 80－100之間的同學。       select name ,english from exam where english between 80 and 100;      查詢數學分數為75,76,77的同學。      select name ,math from exam where math in (75,76,77);      查詢所有姓張的學產生績。      select * from exam where name like '張%';      查詢數學分>70，語文分>80的同學。      select name from exam where math>70 and chinese >80;      尋找缺考數學的學生的姓名      select name from exam where math is null;  （4）Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 語句後指定的別名。     Asc 升序、Desc 降序，其中asc為預設值     ORDER BY 子句應位於SELECT語句的結尾。    *練習：     對數學成績排序後輸出。     select * from exam order by math;     對總分排序按從高到低的順序輸出     select name ,(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) 總成績 from exam order by 總成績 desc;     對姓張的學產生績排序輸出     select name ,(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) 總成績 from exam where name like '張%' order by 總成績 desc;  （5）彙總函式：技巧,先不要管彙總函式要幹嘛，先把要求的內容查出來再包上彙總函式即可。    count(列名)     統計一個班級共有多少學生？先查出所有的學生，再用count包上      select count(*) from exam;     統計數學成績大於70的學生有多少個？      select count(math) from exam where math>70;     統計總分大於250的人數有多少？      select count(name) from exam where (ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))>250;    sum(列名)     統計一個班級數學總成績？先查出所有的數學成績，再用sum包上      select sum(math) from exam;     統計一個班級語文、英語、數學各科的總成績      select sum(math),sum(english),sum(chinese) from exam;     統計一個班級語文、英語、數學的成績總和      select sum(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) as 總成績 from exam;     統計一個班級語文成績平均分       select sum(chinese)/count(*) from exam ;     注意：sum僅對數值起作用，否則會報錯。   AVG(列名)：      求一個班級數學平均分？先查出所有的數學分，然後用avg包上。       select avg(ifnull(math,0)) from exam;      求一個班級總分平均分       select avg((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam ;   Max、Min      求班級最高分和最低分（數值範圍在統計中特別有用）      select Max((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam;      select Min((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam;   （6）group by字句，其後可以接多個列名，也可以跟having子句對group by 的結果進行篩選。      練習：對訂單表中商品歸類後，顯示每一類商品的總價       select product，sum(price) from orders group by product;      練習：查詢購買了幾類商品，並且每類總價大於100的商品        select product，sum(price) from orders group by product having sum(price)>100;    !~having 和 where 的差別： where語句用在分組之前的篩選，having用在分組之後的篩選，having中可以用合計函數，where中就不行。使用where的地方可以用having替代。       練習:查詢商品列表中除了橘子以外的商品，每一類商品的總價格大於500元的商品的名字        select product,sum(price) from orders where product<>'桔子'group by product having sum(price)>500;

  （7）重點：Select from where group by having order by      Mysql在執行sql語句時的執行順序：from where select group by having order by    *分析：     select math+english+chinese as 總成績 from exam where 總成績 >250; ---- 不成功     select math+english+chinese as 總成績 from exam having 總成績 >250; --- 成功     select math+english+chinese as 總成績 from exam group by 總成績 having 總成績 >250; ----成功     select  math+english+chinese as 總成績 from exam order by 總成績;----成功     select * from exam as 成績 where 成績.math>85; ---- 成功      --------------------------------------------------------------------------------------------------四、約束  1.建立表時指定約束：   create table tb(    id int primary key auto_increment,    name varchar(20) unique not null,    ref_id int,     foreign key(ref_id) references tb2(id)   );      create table tb2(    id int primary key auto_increment   );   
 2.外鍵約束：   （1）增加外鍵：    可以明確指定外鍵的名稱，如果不指定外鍵的名稱,mysql會自動為你建立一個外鍵名稱。    RESTRICT : 只要本表格裡面有指向主表的資料， 在主表裡面就無法刪除相關記錄。    CASCADE : 如果在foreign key 所指向的那個表裡面刪除一條記錄，那麼在此表裡面的跟那個key一樣的所有記錄都會一同刪掉。    alter table book add [constraint FK_BOOK] foreign key(pubid) references pub_com(id) [on delete restrict] [on update restrict];      （2）刪除外鍵    alter table 表名 drop foreign key 外鍵（區分大小寫，外鍵名可以desc 表名查看）;       3.主鍵約束：   （1）增加主鍵（自動成長，只有主鍵可以自動成長）    Alter table tb add primary key(id) [auto_increment];   （2）刪除主鍵    alter table 表名 drop primary key   （3）增加自動成長    Alter table employee modify id int auto_increment;   （4）刪除自動成長    Alter table tb modify id int;   --------------------------------------------------------------------------------------------------五、多表設計 一對一（311教室和20130405班級，兩方都是一）:在任意一方儲存另一方的主鍵 一對多、多對一（班級和學生，其中班級為1，學生為多）：在多的一方儲存一的一方的主鍵 多對多（教師和學生，兩方都是多）：使用中間表，儲存對應關係

--------------------------------------------------------------------------------------------------六、多表查詢  create table tb (id int primary key,name varchar(20) );  create table ta (    id int primary key,    name varchar(20),    tb_id int          );  insert into tb values(1,'財務部');  insert into tb values(2,'人事部');  insert into tb values(3,'科技部');   insert into ta values (1,'劉備',1);  insert into ta values (2,'關羽',2);  insert into ta values (3,'張飛',3);    mysql> select * from ta;   +----+------+-------+   | id | name | tb_id |   +----+------+-------+   |  1 | aaa  |     1 |   |  2 | bbb  |     2 |   |  3 | bbb  |     4 |   +----+------+-------+  mysql> select * from tb;   +----+------+   | id | name |   +----+------+   |  1 | xxx  |   |  2 | yyy  |   |  3 | yyy  |   +----+------+   1.笛卡爾積查詢：兩張表中一條一條對應的記錄，m條記錄和n條記錄查詢，最後得到m*n條記錄，其中很多錯誤資料   select * from ta ,tb;    mysql> select * from ta ,tb;    +----+------+-------+----+------+    | id | name | tb_id | id | name |    +----+------+-------+----+------+    |  1 | aaa  |     1 |  1 | xxx  |    |  2 | bbb  |     2 |  1 | xxx  |    |  3 | bbb  |     4 |  1 | xxx  |    |  1 | aaa  |     1 |  2 | yyy  |    |  2 | bbb  |     2 |  2 | yyy  |    |  3 | bbb  |     4 |  2 | yyy  |    |  1 | aaa  |     1 |  3 | yyy  |    |  2 | bbb  |     2 |  3 | yyy  |    |  3 | bbb  |     4 |  3 | yyy  |    +----+------+-------+----+------+  2.內串連：查詢兩張表中都有的關聯資料,相當於利用條件從笛卡爾積結果中篩選出了正確的結果。   select * from ta ,tb where ta.tb_id = tb.id;   select * from ta inner join tb on ta.tb_id = tb.id;    mysql> select * from ta inner join tb on ta.tb_id = tb.id;    +----+------+-------+----+------+    | id | name | tb_id | id | name |    +----+------+-------+----+------+    |  1 | aaa  |     1 |  1 | xxx  |    |  2 | bbb  |     2 |  2 | yyy  |    +----+------+-------+----+------+  3.外串連   （1）左外串連：在內串連的基礎上增加左邊有右邊沒有的結果   select * from ta left join tb on ta.tb_id = tb.id;      mysql> select * from ta left join tb on ta.tb_id = tb.id;    +----+------+-------+------+------+    | id | name | tb_id | id   | name |    +----+------+-------+------+------+    |  1 | aaa  |     1 |    1 | xxx  |    |  2 | bbb  |     2 |    2 | yyy  |    |  3 | bbb  |     4 | NULL | NULL |    +----+------+-------+------+------+   （2）右外串連：在內串連的基礎上增加右邊有左邊沒有的結果    select * from ta right join tb on ta.tb_id = tb.id;       mysql> select * from ta right join tb on ta.tb_id = tb.id;    +------+------+-------+----+------+    | id   | name | tb_id | id | name |    +------+------+-------+----+------+    |    1 | aaa  |     1 |  1 | xxx  |    |    2 | bbb  |     2 |  2 | yyy  |    | NULL | NULL |  NULL |  3 | yyy  |    +------+------+-------+----+------+      （3）全外串連：在內串連的基礎上增加左邊有右邊沒有的和右邊有左邊沒有的結果    select * from ta full join tb on ta.tb_id = tb.id; --mysql不支援全外串連    select * from ta left join tb on ta.tb_id = tb.id    union    select * from ta right join tb on ta.tb_id = tb.id;       mysql> select * from ta left join tb on ta.tb_id = tb.id        -> union        -> select * from ta right join tb on ta.tb_id = tb.id; --mysql可以使用此種方式間接實現全外串連    +------+------+-------+------+------+    | id   | name | tb_id | id   | name |    +------+------+-------+------+------+    |    1 | aaa  |     1 |    1 | xxx  |    |    2 | bbb  |     2 |    2 | yyy  |    |    3 | bbb  |     4 | NULL | NULL |    | NULL | NULL |  NULL |    3 | yyy  |    +------+------+-------+------+------+