mysql學習筆記（三）--- 基本的SQL語句

【正文】

主要內容：

• 一、cmd命令行的常用命令

• 二、數據定義語言（DDL）

• 三、數據操縱語言（DML）

• 四、數據查詢語言（DRL）

• 五、事務控制語言（TCL）

一、cmd命令行的常用命令：

當我們使用MySQL 5.5 Command Line Client這個客戶端登陸時，只能登陸root用戶。如果今后創建了別的用戶，就很麻煩了，所以我們不用MySQL 5.5 Command Line Client這個客戶端，而是直接使用cmd。

登錄MySQL數據庫：

因為在這之前安裝MySQL的時候，已經將環境變量配置好了，所以可以直接在cmd中輸入如下命令進行登陸：

mysql -u root -p

緊接著輸入密碼就行了。

上方代碼中，-u表示username，即用戶名。-p表示password，即密碼。

查看數據庫： 

show databases;

使用數據庫：（此命令后面可以不加分號）

use 數據庫名;

查看當前數據庫表：

show tables;

注：命令后面的分號表示結束。

注：上圖中，先輸入show databases;顯示出了四個庫，前三個表示系統的庫，第四個test是系統自帶的測試庫。然后我們使用test這個數據庫，顯示：Database changed,表示當前的數據庫處于激活狀態。緊接著可以對其進行其他相關的命令了。這里我們輸入show tables，可以看到test這個數據庫的表里面是空的。

所以要開始建表了，于是就涉及到了SQL語句。

SQL全稱是：結構化查詢語言(Structured Query Language)。既然是語言，就涉及到了語法。下面來看一下常見的語法。

二、數據定義語言（DDL）

數據定義語言：Data Definition Language。如CREATE, DROP,ALTER等語句。需要注意的是，數據庫中的命令不區分大小寫。

創建數據庫（CREATE DATABASE語句）：

【舉例】新建數據庫，命名為mydb：

CREATE DATABASE mydb;

注：如若要刪除數據庫，將“create”改為“drop”即可。

創建表（CREATE TABLE語句）：

【舉例】創建一個名為teacher的表：

   (((

上面所有的代碼其實是同一行。

定義字段的格式：字段的名字+字段的類型+屬性

注：每個字段定義完后，要用逗號隔開，最后一個字段沒有逗號。

一般情況下，每個表都要有一個主鍵。

命令運行的效果如下：

查看表結構：

desc teacher;

效果如下：

需要注意的是：主鍵是用來唯一代表一條記錄的字段（主鍵值必須是唯一）

刪除表（DROP TABLE語句）：

DROP TABLE teacher;

注：drop table 語句會刪除該的所有記錄及表結構

修改表結構（ALTER TABLE語句）：（假設表名為test）

• alter table test add column job varchar(10); --添加表列

• alter table test rename test1; --修改表名

• alter table test drop column name; --刪除表列

• alter table test modify address char(10) --修改表列類型（改類型）

• alter table test change address address1  char(40) --修改表列類型（改名字和類型，和下面的一行效果一樣）

• alter table test change column address address1 varchar(30)--修改表列名（改名字和類型）

舉例：將主鍵修改為自動增長：alter table tab_teacher change id id int auto_increment;

三、數據操縱語言（DML）：

數據操縱語言：Data Manipulation Language。如：INSERT（增）, UPDATE（改）, DELETE（刪）語句

添加數據（INSERT INTO…語句）：（即添加表的記錄）

INSERT INTO 表名(字段1,字段2,字段3) values(值，值，值);

舉例：

insert into tab_teacher(name,gender,age,createDate) values('smyh','b',22,now());

注：上方的now()函數可以調出當前系統的時間。

然后通過如下命令進行查詢表的所有記錄：

select * from tab_teacher;

其中，通配符“*”表示所有字段，即從tab_teacher這個表中查所有字段的記錄。

顯示效果如下：

修改數據（UPDATE … SET語句）：

UPDATE 表名 SET 字段1名=值,字段2名=值,字段3名=值 where 字段名=值;

注：where后面的部分表示修改的條件。修改時，按照字段1、字段2、字段3的先后順序修改。

刪除數據：（DELETE FROM…語句）

刪除所有記錄：

DELETE FROM 表名;

刪除ID為1的記錄：

DELETE FROM 表名 where id=1;

四、數據查詢語言（DRL）：

數據查詢語言（Data Retrieval Language --DRL）：SELECT語句。

在實際開發中，數據查詢語言用的是最多的。我們現在以下面的這張表格為例：

注：實際開發中，要避免使用通配符"*"（通配符代表所有字段），因為系統要先解析出所有的字段名，將其還原為真實的名字，然后再進行操作。如果記錄數據庫內容特別多，會影響效率。

查詢所有老師的信息：

select * from tab_teacher;

或者：(推薦)

select id,name,gender,age,job,createDate from tab_teacher;

在沒有表被引用的情況下，允許指定DUAL作為一個假的表名：

DUAL是虛擬表。也就是說，表中沒有這個數據，但是要執意使用sql語句，系統就會用這個虛擬表來滿足你。舉例效果如下：

查詢ID為2的老師信息：

select * from tab_teacher where id=2;

查詢職業為空的老師信息:

select * from tab_teacher where job is null;

注意，上方代碼中，字段為空用“job is null”來表示，而不是“job=null”。

查詢ID為2的老師的姓名和性別：

select name,gender from tab_teacher where id=2;

查詢性別為女和指定日期的老師信息:

select $ from tab_teacher where gender='w' and ceateDate='2014-10-14';

注：并且用“and”，或用“or”。

查詢性別為男或者ID小于2的老師:

select * from tab_teacher where gender='m' or id<2;

查詢姓名的最后一個字符為“e”的老師：（關鍵字：like '%+指定字符'）

select * from tab_teacher where name like '%e';

查詢姓名以指定字符開頭的老師：

select * from tab_teacher where name like 's%';

查詢姓名中包含“m”的老師:

select * from tab_teacher where name like '%m%';

查詢所有老師信息，并按日期降序或者升序排列:（ORDER BY 字段，+默認為升序：ASC/降序：DESC）

• 升序：

select * from tab_teacher order by createDate;

注：最后一個單詞ASC可寫可不寫，因為默認為升序。

• 降序：

select * from tab_teacher order by createDate desc;

多個排序條件：（當第一個條件相同時，按照第二個條件排序）

例如：上面圖片的排序中，是按照日期降序排序的，但是id為2和id為3的日期相同，這兩條記錄按照默認順序排序。

舉例：現在排序的第一條件為：日期降序，第二條件為：年齡降序。多個排序條件用逗號隔開。命令為：

   tab_teacher   createDate ,age ;

按性別分組查詢男女老師的人數（GROUP BY ）:

 gender,(gender)  tab_teacher   gender;

上面的命令表示，查找gender這個字段；并計算gender有多少個（count(gender)）；然后按照gender里的內容將個數進行分組（group by gender）。

問題：我覺得應該是先分組，再查詢性別，再數性別的個數。

注：這里面用到了count()這個內置的函數。關于count()這個聚合函數，將在下一篇文章中講到。

正確的效果和錯誤的效果如下：

按性別分組，查詢出女老師人數的總數:

 gender,(gender)  tab_teacher   gender  gender;

這里在上一個命令的基礎上，增加了一個關鍵字：having。having關鍵字專門用來在分組之后添加的條件。意思是：先求總數，然后進行分組，然后再從分組里找到字段等于“m”的個數。注：不能用where作為關鍵字，因為where是加在分組之前的條件。

顯示效果如下：

注：這種寫法操作繁瑣，效率較低，having語句盡量少用，可以用其他語句代替，例如：

 gender,(gender)  tab_teacher  gender;

效果是一樣的。

查詢表的總記錄數：

• 方式一：（不推薦）

 ()  tab_teacher;

• 方式二：（使用主鍵的方式查詢）

 (id)  tab_teacher;

方式一將空記錄也包含進來了。所以采用方式二，因為主鍵唯一，且不能為空,此時id總數則代表總記錄數。

別名的用法：

• 使用別名查詢表的總記錄數：

select count(id) as counts from tab_teacher;

不用別名和用別名的效果對比如下：（命令中的"as"可以省略）

• 使用別名來代替表的名字：

舉例：將表的名字起為t。

 t.name,t.age  tab_teacher t;

當表名比較長，或者同時有多個表存在時，用別名就顯得非常方便了。

查詢老師記錄的前三條（從0位置開始找出3條）：（非常有用）

   tab_teacher limit ,;

命令解釋：limit后面有兩個參數：第一個參數是起始位置，第二個參數是偏移量。聲明：第一條記錄的id不管為多少，索引位置都是0。

注：這種分頁查詢方式非常有用，例如使用百度搜索時，告訴你總記錄數，但是會分頁顯示。

五、事務控制語言（TCL）：

事務控制語言：Transaction Control Language--TCL。如COMMIT,ROLLBACK語句。

1、事務：

事務(Transaction)的概念：事務(Transaction)是訪問并可能更新數據庫中各種數據項的一個程序執行單元(unit)。在關系數據庫中，一個事務可以是一條SQL語句，一組SQL語句或整個程序。

事務的屬性：原子性（atomicity）、一致性（consistency）、隔離性（isolation）、持續性（durability）。這四個屬性通常稱為ACID特性。

事務的特點：事務就是對數據庫的多步操作，要么一起成功，要么一起失敗。

總結：事務就是可以把多步操作看成一個整體，這個整體要么一起成功，要么一起失敗。

2、事務的提交和回滾命令：

設置默認事務提交方式：（默認為true）

• 設置事務提交方式為“手動提交”：

 autocommit  false;

• 設置事務提交方式為“自動提交”：

 autocommit  true;

手動提交事務：

;

回滾事務：

rollback;

注：只要還沒手動commit提交，一旦回滾，之前的修改操作都將清零。

注：commit命令和rollback命令只有在提交方式為“手動提交”時，才可以用。

3、保存還原點的命令：

當數據未commit之前，增刪改查的操作都是暫時保存在內存之中的，當我們修改操作進行到某一步時，可以給這一步設置一個還原點，方便以后回滾到此還原點（類似于PS當中的快照）。

保存還原點：

savepoint name_point;

回滾到指定還原點：

rollback to name_point；