第十三天3月7日之MySQL基礎

 　　一、SQL

　　SQL：Structured Query Language的縮寫

　　結構化查詢語言

　　SQL工業標准：由ANSI(ISO(國際標准委員會international standard orgnation)核心成員)

　　按照工業標准編寫的SQL能運行在任何數據庫上。

　　方言：只能運行在特定數據庫上的SQL語句叫做方言。

　　工業標准和方言：普通話和浙江話。

　　二、SQL語言的組成：

　　DDL：數據定義語言 Data Definition Language

　　DML：數據操作語言 Data Manipulation Language

　　DQL：數據查詢語言 Data Query Language

　　TPL：事務處理語言 Transaction Process Language

　　DCL：數據控制語言

　　CCL：指針控制語言

　　三、基本知識

　　Java定義的類---------------表的結構

　　Java對象-------------------表中的記錄

　　MySQL:非windows環境下是嚴格區分大小寫的。

　　約定：關鍵字都采用小寫。多個單詞組成用_分割。

　　四、DDL

　　作用：定義數據庫和表結構等的語句。

　　關鍵字：create alter drop truncate(摧毀)

　　練習：

　　1、DDL之數據庫操作

　　創建一個名稱為mydb1的數據庫。

　　mysql>create database MYDB1;

　　查看有哪些數據庫

　　mysql>show databases;

　　查看創建數據庫的具體細節

　　mysql>show create database MYDB1;

　　創建一個使用gbk字符集的mydb2數據庫。

　　mysql>create database MYDB2 characterset gbk;

　　創建一個使用gbk字符集，並帶校對規則的mydb3數據庫。

　　mysql>create database MYDB3 characterset gbk collate gbk_chinese_ci;

　　刪除前面創建的mydb3數據庫

　　mysql>drop database MYDB3;

　　查看服務器中的數據庫，並把mydb2的字符集修改為utf8;

　　mysql>alter database MYDB2 character setutf8;

　　2、DDL之表結構

　　選擇一個庫

　　mysql>use MYDB1;

　　創建一個員工表

　　mysql>create table EMPLOYEE(

　　IDint,

　　NAMEvarchar(200),

　　GENDERvarchar(200),

　　BIRTHDAYdate,

　　ENTRY_DATEdate,

　　JOBvarchar(200),

　　SALARYfloat(8,2),

　　RESUMEtext

　　);

　　顯示當前庫中的所有表

　　mysql>show tables;

　　查看表的結構

　　mysql>desc EMPLOYEE;

　　查看表的創建細節

　　mysql>show create table EMPLOYEE;

　　在上面員工表的基本上增加一個image列。

　　mysql>alter table EMPLOYEE add IMAGEblob;

　　修改job列，使其長度為60。

　　mysql>alter table EMPLOYEE modify JOBvarchar(60);

　　刪除image列。

　　mysql>alter table EMPLOYEE drop IMAGE;

　　表名改為user。

　　mysql>rename table EMPLOYEE to USER;

　　修改表的字符集為utf8

　　mysql>alter table USER character setutf8;

　　列名name修改為username

　　mysql>alter table USER change NAMEUSERNAME varchar(100);

　　五、DML:Data ManipulationLanguage

　　用於向數據庫表中插入、刪除、修改數據

　　常用關鍵字：INSERT UPDATEDELETE

　　在MySQL中，字符串和日期時間類型，要使用單引號引起來。

　　特殊值：null

　　練習：

　　使用insert語句向USER表中插入三個員工的信息。

　　mysql>insert into USER(ID,USERNAME,GENDER,BIRTHDAY,ENTRY_DATE,JOB,SALARY,RESUME) values(1,'wangdongxue','female','1991-09-08','2014-02-17','CEO',100000,'beautygirl');

　　mysql>insert into USER values(2,'wangdong','male','1990-09-08','2014-02-17','CTO',100000,'strong man');

　　mysql>insert into USER values (3,'查顯成','男性','1988-09-08','2014-02-17','UFO',100000,'帥鍋');

　　查看表中的所有記錄

　　mysql>select * from USER;

　　查看庫的所有的編碼：

　　mysql>show variables like'character_set%';

　　character_set_client:指示客戶端使用的字符集

　　mysql>set character_set_client=gbk; 通知服務器，客戶端使用的是GBK字符集

　　character_set_results：指示顯示的結果集使用的字符集

　　mysql>setcharacter_set_results=gbk; 通知服務器，客戶端接受的結果使用的字符集為gbk

　　將所有員工薪水修改為5000元。

　　mysql>update USER set SALARY=5000;

　　將姓名為’查顯成’的員工薪水修改為3000元。

　　mysql>update USER set SALARY=3000 whereUSERNAME='查顯成';

　　將姓名為’wangdong’的員工薪水修改為4000元,job改為CEO。

　　mysql>update USER setSALARY=4000,JOB='CEO' where USERNAME='wangdong';

　　將王冬雪的薪水在原有基礎上增加1000元。

　　mysql>update USER set SALARY=SALARY+1000where USERNAME='wangdongxue';

　　刪除表中名稱為’查顯成’的記錄。

　　mysql>delete from USER where USERNAME='查顯成';

　　刪除表中所有記錄。(DML語句)

　　mysql>delete from USER; #一條一條的刪除

　　使用truncate刪除表中記錄。 (DDL語句)

　　mysql>truncate USER; #刪除整張表，重建的表結構

　　六、DQL數據查詢語言

　　查詢

　　關鍵字：select

　　查詢表中所有學生的姓名和對應的英語成績。(投影查詢)

　　mysql>select NAME,ENGLISH from STUDENT;

　　過濾表中重復數據。

　　mysql>select distinct ENGLISH fromSTUDENT;

　　在所有學生數學分數上加10分特長分。

　　mysql>select NAME,MATH+10 from STUDENT;

　　統計每個學生的總分。

　　mysql>select NAME,CHINESE+ENGLISH+MATHfrom STUDENT;

　　使用別名表示學生分數。

　　mysql>select NAME as姓名,CHINESE+ENGLISH+MATH總分 from STUDENT;

　　查詢姓名為王五的學生成績

　　mysql>select * from STUDENT where NAME='王五';

　　查詢英語成績大於90分的同學

　　mysql>select * from STUDENT whereENGLISH>90;

　　查詢總分大於200分的所有同學

　　mysql>select * from STUDENT where(CHINESE+ENGLISH+MATH)>200;

　　模糊查詢的pattern： _ 表示匹配一個字符 % 匹配任意字符

　　查詢英語分數在 80-90之間的同學。

　　mysql>select NAME,ENGLISH from STUDENTwhere ENGLISH between 80 and 90;

　　查詢數學分數為89,90,91的同學。

　　mysql>select NAME,MATH from STUDENTwhere MATH in(89,90,91);

　　查詢所有姓李的學生成績。

　　mysql>select * from STUDENT where NAMElike '李%';

　　查詢數學分>80，語文分>80的同學。

　　mysql>select * from STUDENT whereMATH>80 and CHINESE>80;

　　滿足where條件的記錄才會顯示，邏輯運算的結果true。

　　對數學成績排序後輸出。

　　mysql>select NAME,MATH from STUDENTorder by MATH;

　　對總分排序後輸出，然後再按從高到低的順序輸出

　　mysql>select NAME,CHINESE+MATH+ENGLISH總分 from STUDENT order by總分 desc;

　　注意：order是關鍵字。把關鍵字當做普通標示符對待，使用``(不是單引號，反引號)引起來

　　對姓李的數學學生成績排序輸出

　　mysql>select NAME,MATH from STUDENTwhere NAME like '李%' order by MATH desc;

　　分頁查詢

　　查詢前3條記錄

　　limit M,N; M開始記錄的索引(從0開始)，N一次要查詢的條數

　　mysql>select * from STUDENT limit 0,3;

　　七、數據的完整性

　　數據完整性是為了保證插入到數據中的數據是正確的，它防止了用戶可能的輸入錯誤。

　　1、域完整性(列完整性)

　　指數據庫表的列(即字段)必須符合某種特定的數據類型或約束

　　ID int(11):該字段必須是整數，長度不能超過11

　　NAME varchar(100) not null:不能為null

　　USERNAME varchar(100) unique:有的話必須唯一

　　2、實體完整性(記錄完整性)

　　規定表的一行(即每一條記錄)在表中是唯一的實體

　　主鍵約束：primary key

　　ID int primary key; ID就是主鍵(不能為null，而且是唯一的)

　　或者

　　ID int,

　　primary key(ID)

　　MySQL：主鍵可以自動增長。auto_increment(主鍵的值由MySQL數據來管理和維護)。oracle中沒有自動增長。

　　create table T2(

　　ID int primary keyauto_increment,//auto_increment是方言，不是標准，只有MySQL中有。

　　NAME varchar(100)

　　);

　　3、參照完整性(引用完整性)：外鍵 foreign key (多表情況)

　　一對多、多對多、一對一

　　remote:遠程意思

　　collation校對意思

　　charset字符集意思

　　manipulation操作

　　MySQL：

　　時間戳：賦值了沒用，不賦值就是把當前值給字段(數據庫中)

　　clob(存放文本數據)能存的，bolb(存放二進制文件)就能存。

　　engine引擎：InnoDB:表示支持事務，安裝時自己選擇的

　　1.域完整性

　　not null 可以是空字符串，但不沒能沒有

　　unique可以為空，有的話必須唯一

　　2.order by後面的字段必須出現在select後面即投影字段的後面嗎?不是必須的

　　+------------+--------------+------+-----+---------+-------+

　　| ID | int(11) | YES | |NULL | |

　　| NAME | varchar(200) | YES | | NULL | |

　　| GENDER | varchar(200) | YES | | NULL | |

　　| BIRTHDAY | date | YES | |NULL | |

　　| entry_date | date | YES | | NULL | |

　　| job | varchar(200) | YES | | NULL | |

　　| salary | float(8,2) | YES | |NULL | |

　　| resume | text | YES | | NULL | |

　　| IMAGE | blob | YES | |NULL | |

　　+------------+--------------+------+-----+---------+-------+

　　9 rows in set (0.01 sec)

　　mysql> alter table employee modify JOBvarchar(60);

　　Query OK, 0 rows affected (0.08 sec)

　　Records: 0 Duplicates: 0 Warnings: 0

　　mysql> desc employee;

　　+------------+--------------+------+-----+---------+-------+

　　| Field | Type | Null | Key |Default | Extra |

　　+------------+--------------+------+-----+---------+-------+

　　| ID | int(11) | YES | |NULL | |

　　| NAME | varchar(200) | YES | | NULL | |

　　| GENDER | varchar(200) | YES | | NULL | |

　　| BIRTHDAY | date | YES | |NULL | |

　　| entry_date | date | YES | | NULL | |

　　| JOB | varchar(60) | YES | |NULL | |

　　| salary | float(8,2) | YES | |NULL | |

　　| resume | text | YES | |NULL | |

　　| IMAGE | blob | YES | |NULL | |

　　+------------+--------------+------+-----+---------+-------+

　　9 rows in set (0.01 sec)

　　mysql> alter table employee

　　-> drop image;

　　Query OK, 0 rows affected (0.18 sec)

　　Records: 0 Duplicates: 0 Warnings: 0

　　mysql> desc employee;

　　+------------+--------------+------+-----+---------+-------+

　　| Field | Type | Null | Key |Default | Extra |

　　+------------+--------------+------+-----+---------+-------+

　　| ID | int(11) | YES | | NULL | |

　　| NAME | varchar(200) | YES | | NULL | |

　　| GENDER | varchar(200) | YES | | NULL | |

　　| BIRTHDAY | date | YES | |NULL | |

　　| entry_date | date | YES | | NULL | |

　　| JOB | varchar(60) | YES | |NULL | |

　　| salary | float(8,2) | YES | |NULL | |

　　| resume | text | YES | |NULL | |

　　+------------+--------------+------+-----+---------+-------+

　　8 rows in set (0.01 sec)

　　mysql> alter table employee

　　-> to USER;

　　ERROR 1064 (42000): You have an error inyour SQL syntax; check the manual that

　　corresponds to your MySQL server versionfor the right syntax to use near 'to US

　　ER' at line 2

　　mysql> rename table employee

　　-> to USER;

　　Query OK, 0 rows affected (0.15 sec)

　　mysql> show tables;

　　+-----------------+

　　| Tables_in_mydb1 |

　　+-----------------+

　　| user |

　　+-----------------+

　　1 row in set (0.00 sec)

　　mysql> show create table user;

　　+-------+-----------------------------------------------------------------------

　　--------------------------------------------------------------------------------

　　--------------------------------------------------------------------------------

　　--------------------------------------------------------------------------------

　　----+

　　| Table | Create Table

　　|

　　+-------+-----------------------------------------------------------------------

　　--------------------------------------------------------------------------------

　　--------------------------------------------------------------------------------

　　--------------------------------------------------------------------------------

　　----+

　　| user | CREATE TABLE `user` (

　　`ID` int(11) DEFAULT NULL,

　　`NAME` varchar(200) DEFAULT NULL,

　　`GENDER` varchar(200) DEFAULT NULL,

　　`BIRTHDAY` date DEFAULT NULL,

　　`entry_date` date DEFAULT NULL,

　　`JOB` varchar(60) DEFAULT NULL,

　　`salary` float(8,2) DEFAULT NULL,

　　`resume` text

　　) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

　　+-------+-----------------------------------------------------------------------

　　--------------------------------------------------------------------------------

　　--------------------------------------------------------------------------------

　　--------------------------------------------------------------------------------

　　----+

　　1 row in set (0.00 sec)

　　mysql> alter table user character setutf8;

　　Query OK, 0 rows affected (0.03 sec)

　　Records: 0 Duplicates: 0 Warnings: 0

　　mysql> alter table user change nameusername varchar(100);

　　Query OK, 0 rows affected (0.07 sec)

　　Records: 0 Duplicates: 0 Warnings: 0

　　mysql> insert into USER values(2,'wangdong','male','1990-09-08','2014-02-17','C

　　TO',100000,'strong man');

　　Query OK, 1 row affected (0.07 sec)

　　mysql> insert into USER values(1,'wangdongxue','male','1990-09-08','2014-02-17'

　　,'CTO',100000,'strong man');

　　Query OK, 1 row affected (0.05 sec)

　　mysql> insert into USER values(1,'wangdongxue','male','1990-09-08','2014-02-17'

　　,'CTO',100000,'strong man');

　　Query OK, 1 row affected (0.00 sec)

　　mysql> insert into USER values(1,'wangdongxue','male','1990-09-08','2014-02-17'

　　,'CTO',100000,'strong man');

　　Query OK, 1 row affected (0.07 sec)

　　mysql> select * from user;

　　+------+-------------+--------+------------+------------+------+-----------+----

　　--------+

　　| ID | username | GENDER |BIRTHDAY | entry_date | JOB | salary | res

　　ume |

　　+------+-------------+--------+------------+------------+------+-----------+----

　　--------+

　　| 2 | wangdong | male | 1990-09-08 | 2014-02-17 | CTO | 100000.00 | str

　　ong man |

　　| 1 | wangdongxue | male |1990-09-08 | 2014-02-17 | CTO |100000.00 | str

　　ong man |

　　| 1 | wangdongxue | male |1990-09-08 | 2014-02-17 | CTO |100000.00 | str

　　ong man |

　　| 1 | wangdongxue | male | 1990-09-08| 2014-02-17 | CTO | 100000.00 | str

　　ong man |

　　+------+-------------+--------+------------+------------+------+-----------+----

　　--------+

　　4 rows in set (0.06 sec)

　　mysql> insert into USER values (3,'查顯成','男性','1988-09-08','2014-02-17','UFO

　　',100000,'帥鍋');

　　ERROR 1366 (HY000): Incorrect string value:'xB2xE9xCFxD4xB3xC9' for colum

　　n 'username' at row 1

　　mysql> show variables like'character_set%';

　　+--------------------------+----------------------------------------------------

　　-----+

　　| Variable_name | Value

　　|

　　+--------------------------+----------------------------------------------------

　　-----+

　　| character_set_client | utf8

　　|

　　| character_set_connection | utf8

　　|

　　| character_set_database | utf8

　　|

　　| character_set_filesystem | binary

　　|

　　| character_set_results | utf8

　　|

　　| character_set_server | utf8

　　|

　　| character_set_system | utf8

　　|

　　| character_sets_dir | D:Program FilesMySQLMySQL Server5.1sharechars

　　ets |

　　+--------------------------+----------------------------------------------------

　　-----+

　　8 rows in set (0.07 sec)

　　mysql> set character_set_client=gbk;

　　Query OK, 0 rows affected (0.07 sec)

　　mysql> select * from user;

　　+------+-------------+--------+------------+------------+------+-----------+----

　　--------+

　　| ID | username | GENDER |BIRTHDAY | entry_date | JOB | salary | res

　　ume |

　　+------+-------------+--------+------------+------------+------+-----------+----

　　--------+

　　| 2 | wangdong | male | 1990-09-08 | 2014-02-17 | CTO | 100000.00 | str

　　ong man |

　　| 1 | wangdongxue | male |1990-09-08 | 2014-02-17 | CTO |100000.00 | str

　　ong man |

　　| 1 | wangdongxue | male |1990-09-08 | 2014-02-17 | CTO |100000.00 | str

　　ong man |

　　| 1 | wangdongxue | male |1990-09-08 | 2014-02-17 | CTO |100000.00 | str

　　ong man |

　　+------+-------------+--------+------------+------------+------+-----------+----

　　--------+

　　4 rows in set (0.00 sec)

　　mysql> insert into USER values (3,'湯柳清','男性','1988-09-08','2014-02-17','UFO

　　',100000,'帥鍋');

　　Query OK, 1 row affected (0.02 sec)

　　mysql> select * from user;

　　+------+-------------+--------+------------+------------+------+-----------+----

　　--------+

　　| ID | username | GENDER |BIRTHDAY | entry_date | JOB | salary | res

　　ume |

　　+------+-------------+--------+------------+------------+------+-----------+----

　　--------+

　　| 2 | wangdong | male | 1990-09-08 | 2014-02-17 | CTO | 100000.00 | str

　　ong man |

　　| 1 | wangdongxue | male |1990-09-08 | 2014-02-17 | CTO | 100000.00| str

　　ong man |

　　| 1 | wangdongxue | male |1990-09-08 | 2014-02-17 | CTO |100000.00 | str

　　ong man |

　　| 1 | wangdongxue | male |1990-09-08 | 2014-02-17 | CTO |100000.00 | str

　　ong man |

　　| 3 |鏌ユ樉鎴? | 鐢鋒