程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> Mysql重新整理筆記--woods備忘

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

編輯:MySQL綜合教程

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


一.數據庫    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  |
 +------+------+-------+------+------+

 


  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved