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