方老師今日講解數(shù)據(jù)庫入門，主要內(nèi)容是對(duì)數(shù)據(jù)庫操作的SQL語句，僅涉及單表操作。內(nèi)容雖然枯燥，但也學(xué)習(xí)了很多知識(shí)。
什么是SQL？structred query language（結(jié)構(gòu)化查詢語言），在數(shù)據(jù)庫中它是通用的。我之前并未深入使用數(shù)據(jù)庫。對(duì)此還有些模糊，但今日的學(xué)習(xí)讓我有了深入了解。其實(shí)我也應(yīng)該早就想到，對(duì)數(shù)據(jù)的一些操作通過SQL語句就可以完成，程序直接拿結(jié)果就可以了。
方老師以MySQL5.0數(shù)據(jù)庫，給我們做了講解。是為了下一課學(xué)習(xí)JDBC做準(zhǔn)備。前面的對(duì)數(shù)據(jù)庫的簡(jiǎn)介和MySQL5.0的安裝及配置，我就不寫出來了。下面是用戶、服務(wù)器、數(shù)據(jù)庫和表的關(guān)系圖：
在配置數(shù)據(jù)庫時(shí)選中：
，或手動(dòng)將MySQL安裝目錄添加到Windows環(huán)境變量Path中。使用控制臺(tái)連接MySQL數(shù)據(jù)庫：mysql –u 用戶名 –p 密碼。
下面是常用的SQL語句，只針對(duì)單表使用，有關(guān)語句的具體信息請(qǐng)查詢MySQL的手冊(cè)：
一、數(shù)據(jù)庫相關(guān)SQL語句：
1. 創(chuàng)建數(shù)據(jù)庫：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]
其中create_specification是：
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
例，創(chuàng)建一個(gè)名為“ccdb”，字符集為UTF-8的數(shù)據(jù)庫：
create database ccdb character set utf8;
|
2. 查看、刪除數(shù)據(jù)庫：
顯示數(shù)據(jù)庫：
SHOW CREATE {DATABASE | SCHEMA} db_name
例，查看服務(wù)器中所有的數(shù)據(jù)庫：
顯示數(shù)據(jù)庫創(chuàng)建語句：
SHOW {DATABASES | SCHEMAS} [LIKE 'pattern']
例，查看創(chuàng)建的“ccdb”數(shù)據(jù)庫的創(chuàng)建語句：
刪除數(shù)據(jù)庫：
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
例，刪除創(chuàng)建的數(shù)據(jù)庫“ccdb”：
|
3. 修改數(shù)據(jù)庫：
ALTER {DATABASE | SCHEMA} [db_name] alter_specification [, alter_specification] ...
其中alter_specification是：
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
例，更改數(shù)據(jù)庫“ccdb”的字符編碼為“gbk”：
|
二、數(shù)據(jù)表相關(guān)SQL語句：
1. 創(chuàng)建表：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement]
或
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(] LIKE old_tbl_name [)];
其中create_definition、table_options等，請(qǐng)查看手冊(cè)。
例，在數(shù)據(jù)庫“ccdb”中創(chuàng)建一個(gè)“user”表：
在創(chuàng)建表之前，必須選擇當(dāng)前庫。否則服務(wù)器不知道在哪個(gè)數(shù)據(jù)庫中創(chuàng)建表，或者當(dāng)前已經(jīng)有被選擇的其他數(shù)據(jù)庫，就會(huì)創(chuàng)建到別的庫。
|
MySQL常用數(shù)據(jù)類型（拿方老師的，嘿嘿）：
分類
|
數(shù)據(jù)類型
|
說明
|
數(shù)值類型
|
BIT
TINYINT [UNSIGNED] [ZEROFILL]
BOOL，BOOLEAN
SMALLINT [UNSIGNED] [ZEROFILL]
INT [UNSIGNED] [ZEROFILL]
BIGINT [UNSIGNED] [ZEROFILL]
FLOAT[(M,D)][UNSIGNED] [ZEROFILL]
DOUBLE[(M,D)][UNSIGNED] [ZEROFILL]
|
范圍為從1到64。
帶符號(hào)的范圍是-128到127。無符號(hào)0到255。
使用0或1表示真或假
2的16次方
2的32次方
2的64次方
M指定長(zhǎng)度，d指定小數(shù)位數(shù)
表示比float精度更大的小數(shù)
|
文本類型
|
CHAR(size)
VARCHAR(size)
BLOB LONGBLOB
TEXT LONGTEXT
|
固定長(zhǎng)度字符串
可變長(zhǎng)度字符串
二進(jìn)制數(shù)據(jù)
大文本
|
時(shí)間日期
|
DATE/DATETIME/TimeStamp
|
日期類型(YYYY-MM-DD) (YYYY-MM-DD HH:MM:SS)，TimeStamp表示時(shí)間戳，它可用于自動(dòng)記錄insert、update操作的時(shí)間
|
2. 修改表：
ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ...
其中alter_specification，請(qǐng)查看手冊(cè)。
修改表“user”名為“userinfo”：
修改表“userinfo”的字符集為“gb2313”，并將字符集校對(duì)設(shè)置為與“gb2313”相應(yīng)的校對(duì)：
可以在控制臺(tái)輸入：SHOW CHARACTER SET;，列出可用的字符集。也可以在手冊(cè)中查找“Character sets”，查找手冊(cè)中的字符集列表。
可以在控制臺(tái)輸入：SHOW COLLATION LIKE 'gb%';，查看以gb開頭的所有字符集。
先將表“userinfo”恢復(fù)名稱為“user”，查看表“user”：
查看表“user”的結(jié)構(gòu)：
添加列“photo”：
修改列“sex”屬性-類型為“bit”：
修改列“email”名稱為“address”：
刪除列“address”：
|
3. 數(shù)據(jù)庫CURD語句：
|
Insert語句：
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
例，向“user”表中插入一條新數(shù)據(jù)：
Update語句：
UPDATE tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]
例，將“sex”為1的人的“name”修改為“changcheng”：
Delete語句：
delete from tbl_name [WHERE where_definition]
例，刪除剛才添加的新記錄：
如果沒有后邊的where條件，則刪除表中所有的數(shù)據(jù)。
truncate table tbl_name;是刪除表，然后立即創(chuàng)建一個(gè)新表。
|
4. Select查詢語句（因?yàn)樗侵攸c(diǎn)，所以單獨(dú)拿出來）：
|
1. 基本select語句：
SELECT [DISTINCT] *|{column1, column2. column3..} FROM table;
例，我們新創(chuàng)建了一個(gè)sutdent表，查看表中的所有記錄：
例，查詢表中所有學(xué)生姓名和他的數(shù)學(xué)成績(jī)：
例，過濾表中的重復(fù)數(shù)據(jù)（distinct）：
2. 在select語句中可使用表達(dá)式對(duì)查詢的列進(jìn)行運(yùn)算：
SELECT *|{column1｜expression, column2｜expression，..} FROM table;
例，將所有學(xué)習(xí)的數(shù)學(xué)加上10分：
例，統(tǒng)計(jì)學(xué)生的總分：
3. 在select語句中可使用as語句：
SELECT column as 別名 from 表名;
例，修改統(tǒng)計(jì)學(xué)生總分的列名：
4. 在select語句中加上where條件，進(jìn)行過濾查詢：
例，查詢總分大于260分的學(xué)生：
5. where經(jīng)常使用的運(yùn)算符：
|
比較運(yùn)算符
|
> < <= >= = <>
|
大于、小于、大于(小于)等于、不等于
|
|
BETWEEN ...AND...
|
顯示在某一區(qū)間的值
|
|
IN(set)
|
顯示在in列表中的值，例：in(100,200)
|
|
LIKE ‘pattern’
|
模糊查詢，Like語句中，% 代表零個(gè)或多個(gè)字符，_ 代表一個(gè)字符，例first_name like ‘_a%’;
|
|
IS NULL
|
判斷是否為空
|
|
邏輯運(yùn)算符
|
and
|
多個(gè)條件同時(shí)成立
|
|
or
|
多個(gè)條件任一成立
|
|
not
|
不成立，例：where not(salary>100);
|
例，查詢數(shù)學(xué)成績(jī)?cè)?0-90之間的學(xué)生：
例，查詢數(shù)據(jù)成績(jī)?yōu)?0、80、78的學(xué)生：
例，查詢所有名字以“a”開頭的學(xué)生的數(shù)學(xué)成績(jī)：
6. 使用order by 子句排序查詢結(jié)果：
SELECT column1, column2. column3.. FROM table order by column asc|desc
|
5. 合計(jì)函數(shù)：
|
1. Count返回滿足where子句條件的行的總數(shù)：
Select count(*)|count(列名) from tablename [WHERE where_definition]
例，查詢共有多少學(xué)生：
例，查詢總分大于250分的人數(shù)：
2. Sum函數(shù)返回滿足where條件的行的和：
Select sum(列名)｛,sum(列名)…｝ from tablename [WHERE where_definition]
注意：sum僅對(duì)數(shù)值起作用，否則會(huì)報(bào)錯(cuò)。
例，統(tǒng)計(jì)本班數(shù)學(xué)成績(jī)的平均分：
例，統(tǒng)計(jì)各科總成績(jī)：
3. AVG函數(shù)返回滿足where條件的一列的平均值：
Select avg(列名)｛,avg(列名)…｝ from tablename [WHERE where_definition]
例，求數(shù)學(xué)的平均分：
例，求總平均分：
4. Max/min函數(shù)返回滿足where條件的一列的最大/最小值：
Select max(列名) from tablename [WHERE where_definition]
例，查詢數(shù)學(xué)成線最高分和最低分：
|
6. 分組與過濾：
|
1. 使用group by 子句對(duì)列進(jìn)行分組：
SELECT column1, column2. column3.. FROM table group by column
例，按照ID分組并對(duì)數(shù)據(jù)成績(jī)求和：
2. 使用having 子句過濾：
SELECT column1, column2,column3 ... FROM table group by column having ...
Having和where均可實(shí)現(xiàn)過濾，但在having可以使用合計(jì)函數(shù),having通常跟在group by后，它作用于組。
例，按id分組，查詢數(shù)學(xué)成績(jī)組總和大于100的組：
例，查詢數(shù)據(jù)成績(jī)>=90的學(xué)生：
|
7. 時(shí)間日期相關(guān)函數(shù)：
|
ADDTIME (date2 ,time_interval )
|
將time_interval加到date2
|
|
CURRENT_DATE ( )
|
當(dāng)前日期
|
|
CURRENT_TIME ( )
|
當(dāng)前時(shí)間
|
|
CURRENT_TIMESTAMP ( )
|
當(dāng)前時(shí)間戳
|
|
DATE (datetime )
|
返回datetime的日期部分
|
|
DATE_ADD (date2 , INTERVAL d_value d_type )
|
在date2中加上日期或時(shí)間
|
|
DATE_SUB (date2 , INTERVAL d_value d_type )
|
在date2上減去一個(gè)時(shí)間
|
|
DATEDIFF (date1 ,date2 )
|
兩個(gè)日期差
|
|
NOW ( )
|
當(dāng)前時(shí)間
|
|
YEAR|Month|DATE (datetime )
|
年月日
|
8. 字符串相關(guān)函數(shù)：
|
CHARSET(str)
|
返回字串字符集
|
|
CONCAT (string2 [,... ])
|
連接字串
|
|
INSTR (string ,substring )
|
返回substring在string中出現(xiàn)的位置,沒有返回0
|
|
UCASE (string2 )
|
轉(zhuǎn)換成大寫
|
|
LCASE (string2 )
|
轉(zhuǎn)換成小寫
|
|
LEFT (string2 ,length )
|
從string2中的左邊起取length個(gè)字符
|
|
LENGTH (string )
|
string長(zhǎng)度
|
|
REPLACE (str ,search_str ,replace_str )
|
在str中用replace_str替換search_str
|
|
STRCMP (string1 ,string2 )
|
逐字符比較兩字串大小,
|
|
SUBSTRING (str , position [,length ])
|
從str的position開始,取length個(gè)字符
|
|
LTRIM (string2 ) RTRIM (string2 )
|
去除前端空格或后端空格
|
9. 數(shù)學(xué)相關(guān)函數(shù)：
|
ABS (number2 )
|
絕對(duì)值
|
|
BIN (decimal_number )
|
十進(jìn)制轉(zhuǎn)二進(jìn)制
|
|
CEILING (number2 )
|
向上取整
|
|
CONV(number2,from_base,to_base)
|
進(jìn)制轉(zhuǎn)換
|
|
FLOOR (number2 )
|
向下取整
|
|
FORMAT (number,decimal_places )
|
保留小數(shù)位數(shù)
|
|
HEX (DecimalNumber )
|
轉(zhuǎn)十六進(jìn)制
|
|
LEAST (number , number2 [,..])
|
求最小值
|
|
MOD (numerator ,denominator )
|
求余
|
|
RAND([seed])
|
RAND([seed])
|
10. 定義表的約束：
|
1. 定義主鍵：
Primary key，在預(yù)定義主鍵的列后邊加上此屬性。
例，創(chuàng)建student表時(shí)，將id設(shè)為主鍵：
刪除、添加主鍵屬性：
2. 定義非空約束：
not null，在預(yù)定義主鍵的列后邊加上此屬性：
例，將name列設(shè)置為非空：
此時(shí)，當(dāng)向表中添加新數(shù)據(jù)時(shí)，name必須指定值。可以使用“is null”判斷某一記錄的值是否為空。
3. 定義列值自動(dòng)增長(zhǎng)：
Auto_increment，在預(yù)定義主鍵的列后邊加上此屬性：
例，定義id為主鍵且自動(dòng)增長(zhǎng)：
4. 定義外鍵約束：
這個(gè)是今天課程中，唯一涉及到兩個(gè)表的SQL語句操作。
constraint ordersid_FK foreign key(ordersid) references orders(id)，在預(yù)定義外鍵的列后邊加上此屬性：
例，定義一個(gè)classes表，然后再定義一個(gè)students表：
創(chuàng)建classes表：
創(chuàng)建students表：
當(dāng)向students表中添加一條記錄時(shí)，如果指定的class_id值在classes表中不存在，添加就會(huì)失敗！如果對(duì)應(yīng)的classes表中的id值被修改了，那么也需要手動(dòng)修改students表中的class_id值。
|
如果創(chuàng)建庫和表，以及對(duì)它們的操作過多的話，逐條手動(dòng)鍵入SQL語句是一件痛苦的事。SQL的腳本文件(*..sql)可以為我們解決這一難題。Sql文本文件中保存的是我們要手動(dòng)鍵入所有的SQL語句，使用source filename語句，導(dǎo)入sql腳本文件。比如：“source C:\test.sql”。
OK，終于搞完了！也當(dāng)做練習(xí)了，不過是按照老方的PPT一步步來的，有點(diǎn)侵權(quán)的感覺。不過我想沒問題的，老方對(duì)此會(huì)很高興的。
SQL語句本身并不復(fù)雜，做為程序員并不需要完全掌握數(shù)據(jù)庫。數(shù)據(jù)庫的優(yōu)化上全由DBA來做。我們需要掌握對(duì)數(shù)據(jù)庫的增、刪、改、查這些SQL語句即可！
今日的課程沒有多深的理論與實(shí)踐，主要就是講解SQL語句。同學(xué)們學(xué)習(xí)效果很好！