MySQL數據庫學習筆記

1、MySQL 數據庫 學習筆記關系數據庫的查詢語言SQL (Structured QueryLanguage) ，是一種非?？谡Z化、 既易學又易懂的語法。此一語言幾乎是每個資料庫系統(tǒng)都必須提供的，用以表示關連式的操作，包含了資料的定義(DDL) 以及資料的處理 (DML) 。SQL 原來拼成 SEQUEL ，這語言的原型以“系統(tǒng)R“的名字在IBM圣荷西實驗室完成，經過IBM 內部及其他的許多使用性及效率測試，其結果相當令人滿意， 并決定在系統(tǒng)R 的技術基礎發(fā)展出來IBM的產品。而且美國國家標準學會(ANSI) 及國際標準化組織(ISO) 在 1987 遵循一個幾乎是以IBM SQL為基礎的標準關

2、連式資料語言定義。第一章、了解概念，并理解它一、資料定義DDL(Data Definition Language)資料定語言是指對資料的格式和形態(tài)下定義的語言，他是每個資料庫要建立時候時首先要面對的，舉凡資料分哪些表格關系、 表格內的有什麼欄位主鍵、表格和表格之間互相參考的關系等等，都是在開始的時候所必須規(guī)劃好的。數據庫第二章MySQL 數據庫數據類型第一節(jié)、數據類型MySQL 數據庫目前支持以下幾種下列代碼字母用于描述中：M 指出最大的顯示尺寸。最大的顯示尺寸長度為255 。D 適用于浮點類型。 指出跟隨在十進制小數點后的數字數量。最大可能值為 30 ，但不應大于 M-2 。方括號 ( an

3、d ) 指定可選的類型修飾部份。1、數值型 ( 包括整數與浮點數， 浮點型支持科學表示法)浮點類型 :numeric nju:merik decimal(5,2),數字位 :5, 小數位 :2numeric等價于decimal并且是作為字符串存儲的.而不是作為二進制浮點數.說明 :salary DECIMAL(3,2)在這個例子中，3 ( 精度 (precision)代表重要的十進制數字的數目， 2 (數據范圍 (scale)代表在小數點后的數字位數。在這種情況下，因此，salary列可以存儲的值范圍是從-99.99到 99.99 。(實際上MySQL在這個列中可以存儲的數值可以一直到999.

4、99 ，因為它沒有存儲正數的符號) 。整型 :integer類型占用字節(jié)從到TINYINT 1-128127SMALLINT 2-3276832767MEDIUMINT 3-83886088388607INT4-21474836482147483647BIGINT 8-9223372036854775808 92233720368547758072 、字符串 (字符 ) 類型3 、日期和時間類型4 、Null 類型不同于例如數字類型的0 或字符串的5 、布爾類型TURE FALSE標識符約定標識符最大長度 允許的字符數據庫64 一個目錄名中允許的任何字符 ,除了和.表64一個目錄名中允許的任何

5、字符,除了 /和 .列64所有的字符索引 64所有的字符加名 255所有的字符定界符 , 反引號 ANSI SQL 中雙引號為定界符1 開始編注意 :在 MYSQL 中,數據庫和表對應操作系統(tǒng)下的目錄與文件 ,因而 ,操作系統(tǒng)的敏感性決定數據庫和表命名的大小敏感 ,所以在 LINUX 下數據庫與表名的大小寫敏感.提示 :USE ,HELP是 Mysql 中為少數幾個不需要終結符的語句之一 .用戶變量表示為: var_name;set x=99;等同于 x := 99;系統(tǒng)變量用兩個 表示 ,它分為 2 種,全局系統(tǒng)變量和會話系統(tǒng)變量 .下面詳細說明各種類型MySQL 支持所有的ANSI/ISO

6、 SQL92數字類型 .1、數值型一個整數可以在浮點語境中使用;它被解釋為等值的浮點數 ;同樣 ,一個浮點數可以被轉化為整數,但損失精度 ,如 100.1-100,100.5-101;2.Mysql 支持 16 進制數值 ,在數字的語境中,它們表現類似于一個整數 (64 位精度 );如 select 0xa+0;在字符串的語境中,它們表現類似于一個二進制的字符串 ,每一對 16 進制 ASCII 碼被轉為一個字符:select 0x787868 ; =select x787868;select hex(xxh);select 0xa+5;select true,true,flase,FALSE

7、;其中浮點型用于域，表示方法解釋一下，如：FLOAT(M,D) UNSIGNED ZEROFILL如果 UNSIGNED被指定，負值是不允許的。 M 是顯示寬度， D 是小數位數。 并且如果負號 “”也占一個寬度。2、字符串類型字符串是多個字符組成的一個字符序列,由單引號 ,或雙引號字符包圍,但在 ANSI 模式中運行時只能用單引號 ,也就是說在SQL SERVER數據庫 ,使用應該使用單引號 , 保證兼容性應該使用單引號.字符串類型有CHAR 、VARCHAR 、BLOB 、TEXT 、ENUM和 SET；ENUM 是一個字符串 對象，其值通常選自一個允許值列表中， 該列表在表創(chuàng)建時的列規(guī)格

8、說明中被明確地列舉。每個枚舉值均有一個索引值：在列說明中列表值所允許的成員值被從號?？兆址e誤值的索引值為 0。這就意味著， 你可以使用下面所示的 SELECT 語句找出被賦于無效ENUM值的記錄行。mysql SELECT * FROM tbl_name WHEREenum_col=0;NULL值的索引值為NULL 。總結： ENUM 用于互相排斥的值，而 SET 列可以從一個值的列表中選擇多個值。常用技巧 :1. 在命令提示符下 :mysql -h -u root db_name x.sql在串的環(huán)境中， 可用十六進制常數來指定串值。 其語法與前面描述的數值值相同， 但是每對十六進制的數

9、字都被看作 ASCII 代碼并轉換為字符， 其結果用于串。例如， 0x616263 作為串時為 “a b c。”在一個字符串中，如果某個序列具有特殊的含義，每個序列以反斜線符號 ( “”)開頭，稱為轉義字符。MySQL識別下列轉義字符：0一個 ASCII 0 (NUL) 字符。一個 ASCII 39單引號 (“ ”字符)。一個 ASCII 34雙引號 (“”字符)。b一個 ASCII 8退格符。n一個 ASCII 10換行符。r一個 ASCII 13回車符。t 一個ASCII 9制表符 (TAB) 。z ASCII(26) (Control-Z)。這個字符可以處理在Windows系統(tǒng)中ASCI

10、I(26)代表一個文件的結束的問題。 (當使用mysql database filename時ASCII(26)可能會引起問題產生。) 一個ASCII 92反斜線( “” )字符。% 一個ASCII 37“ %”字符。它用于在正文中搜索“ %”的文字實例，否則這里“%”將解釋為一個通配符。查看章節(jié) 6.3.2.1字符串比較函數。_ 一個 ASCII 95 “ _字”符。它用于在正文中搜索 “ _” 的文字實例，否則這里 “_將”解釋為一個通配符。查看章節(jié) 6.3.2.1字符串比較函數.3、日期和時間類型4、 Null 類型Null 是 ANSI 中值的空。查找 NUll 類型用 IS Null

11、 和 IS Not Null 測試；并且不能對 NULL 類型進行算術運算。其結果為 NULL 。5.mysql 的注釋為兩種 :/*/ 和#( 單行注釋 , 類似 UNIX 的腳本注釋 ;)第 07 章 MySQL 函數mysql運算符A.比較運算符 =,!=(),=,=Null 安全等于比較 is null ,is not null isnull(express)如果 expr 大于或等于min, 并且 expr 小于或等于max,between返回 1,否則返回 0. 它等價于表達式(min=expr and expr=max)expr in (v1,v2,.);如果 expr 是 in

12、 列表中的一值 ,它將返回 1,否則返回0.B.邏輯運算符在 mysql 中, 所有的邏輯運算符返回的值均為true,false, 或 null( 未知 ), 在 mysql 中 ,它們由1(true),0(false)和 null 實現 .select !1等同于select not 1;C. 流程控制函數ifnull(expr1,value) 當 expr1 的值是 null 時,返回 value, 不是 null 時 ,返回 expr1case value when compare-value then result .set a=2;select case awhen 1 then o

13、newhen 2 then twoelse more end;if (expr1,expre2,expr3)如果 expr1 為真 ,(expr10以及 expr1null), 那么 if()返回 expr2, 否則返回expr3nullif(expr1,expr2)如果 expr1=expr2為真 ,返回 null,否則返回expr1;mysql select nullif(1,1);- nullmysql select nullif(1,2)- 1D.mysql字符串函數返回字符串str 最左邊的那個字符的ASCII 碼值 .mysql select ascii(2);- 50返回 N 的

14、字符串表示的2 進制值形式 .mysql select bin(12);- 1100返回 N 的字符串表示的八進制值形式mysql select oct(12);- 14如果 x 是一個數字 , 返回 n 的字符串表示的16 進制值形式 ,這里 n 是一個長長的 (bigint) 數字 .如果 x 是一個字符串,x 中的每個字符均被轉換為2 位16 進制數字 , 并以 16進制的字符串形式返回 .這是 0xff 形式的字符串反轉操作.mysql select hex(255);- ffmysql select hex(abc); /select ascii(a); - 97select hex

15、(97); - 61- 616263mysql 0x616263;- abcchar() 以整數類型解釋參數, 返回這個整數所代表的Ascii 碼值給出的字符組成的字符串,null 值將被忽略mysql select char(120,120,104);- xxhconcat(str1,str2,.)將參數連接成字符串返回,如果有任何一個參數為null,返回值也為null.locate( 子串 substr, 母串 str) 返回子串 substr 在字符呂中第一次出現的位置 .如果子串 substr 在 str 中不存在 , 返回值為 0:ltrim(str)返回移除了領頭的空格字符后的st

16、r;第 08 章 MySQL 數據操縱語言 DMLselect語法select列 1,列 2,列 3,from 表 1, 表 2, 表 3where條件 group by .having .第二節(jié)域 / 列類型屬性域 /列的類型屬性，有以下特性： 其中可以存放什么類型的值。 值要占據多少空間，以及該值是否是定長的(所有值占相同數量的空間) 或(所占空間量依賴于所存儲的值)。 該類型的值怎樣比較和存儲。 此類型是否允許NULL值。 此類型是否可以索引。數字日期和時間字符串 (字符 )列類型的屬性有以下四種：DefaultNot Null或 NullPrimary Key即主鍵Auto_incre

17、ment (只有 1 個只能被索引 )一、數據庫的操作操作系統(tǒng)下的操作mysql -h -u -pmysql -h localhost -uroot創(chuàng)建數據庫刪除數據庫顯示數據庫使用數據庫數據庫其他相關操作1 、創(chuàng)建數據庫CREATE DATABASE db_Name;說明：2 、刪除數據庫DROP DATABASE db_Name;說明：可以簡寫為:drop db_name;3、顯示數據庫SHOW databases;說明：顯示當前有哪些數據庫。4、使用數據庫USE database;說明：5、顯示數據庫中有哪些表首先 USE database;然后 SHOW tables;參見： show

18、 的用法二、表格、索引的操作創(chuàng)建表格更改表格建立索引刪除表格、索引表格其他相關操作以下操作都是以表格table_name或索引為操作單位。DESCRIBE或 DESC 命令除外。文件用途tbl_name.frm表定義(form)文件tbl_name.MYD數據文件tbl_name.MYI索引文件1、創(chuàng)建表格：CREATE TABLE table_name(column1 DATATYPE NOT NULL NOT NULLPRIMARY KEY,column2 DATATYPE NOT NULL,.)說明：DATATYPE - 是資料的格式，也就是字段的數據類型Not Null - 不允許資料

19、有空的PRIMARY KEY -是本表的主鍵?？梢酝ㄟ^一個表的內容來拷貝一個表，如CREATETEMPORARY TABLEnew_tbl_nameSELECT * FROM old_tbl_name;CREATETEMPORARY TABLEnew_tbl_nameSELECT * FROM old_tbl_name where 1=0;(只是拷貝空表 )2 、更改表格ALTER TABLE tbl_name ADD COLUMN col_nameDATATYPE說明：增加一個欄位即( 字段 )(沒有刪除某個欄位的語法 )。ALTER TABLE tbl_name RENAME AS new

20、_name;說明 : 對表重命名。ALTER TABLE table_name drop column_name說明：刪除指定的列名。ALTER TABLE tbl_name CHANGEold_column_name new_column_name DATATYPE說明：更改列名及列屬性。例： ALTER TABLE tbl_name CHANGEold_column_name new_column_name int(2) not nullprimary key ;ALTER TABLE table_name ADD PRIMARY KEY(column_name)說明：更改表得的定義把某個

21、欄位設為主鍵。ALTER TABLE table_name DROP PRIMARY KEY(column_name)說明：把主鍵的定義刪除。ALTER TABLE my_tbl TYPE=ISAM或MYISAM,HELP,InnoDB等等 說明：更改表的類型3、建立索引CREATE INDEX index_name ON table_name(column_name) ;CREATE UNIQUE index_name ON table_name(column_name) ;ALTER TABLE tbl_name ADD INDEXindex_name(column_list);ALTER

22、 TABLE tbl_name ADD UNIQUEindex_name(column_list);ALTER TABLE tbl_name ADD PRIMARY KEY(column_list);說明：對某個表格的欄位建立索引以增加查詢時的速度。4、刪除表格、索引DROP tbl_name1,tbl_name2,tbl_name3.;DROP INDEX index_name ON tbl_name;ALTER TABLE tbl_nameDROP INDEXindex_name;ALTER TABLE tbl_nameDROP Primary Key;5、顯示表格中列的信息語法：DESC

23、RIBE | DESC tbl_name col_name | wildDESCRIBE tbl_name;說明： DESCRIBE是 SHOW COLUMNS FROM的縮寫。用于顯示表格中列的信息。DESCRIBE tbl_name;等于 SHOW COLUMNSFROM tbl_name;也可以簡寫為 :desc tal_name;describe vt.描寫 , 記述 , 形容 , 形容三、記錄的操作記錄的添加記錄的更改記錄的刪除首先來大概了解一下INSERT的語法，有以下幾種方式：INSERTINTO tbl_name(col_name,.)VALUES (expression |D

24、EFAULT),.),(.),. ON DUPLICATE KEY UPDATEcol_name=expression, . 或者INSERT INTO tbl_name(col_name,.)SELECT .或者INSERT INTO tbl_nameSETcol_name=(expression | DEFAULT), . ON DUPLICATE KEY UPDATEcol_name=expression, . 說明：我們只關心記錄添加的方式啦。注意啦， 現在我們開始啦。1 、記錄的添加最簡單的一種：直接添加一行記錄就是：INSERTINTO tbl_name(col_name,.)VA

25、LUES (expression |DEFAULT),.),(.),.);例 :INSERT INTO tbl_name (col1,col2)VALUES(15,col1*2); insert tbl_name (col1,col2)values (007,xxh5fd)第二種：2 、記錄的更改還是先來看一看UPDATE的語法UPDATE tbl_name , tbl_name .SETcol_name1=expr1 , col_name2=expr2 .WHERE where_definition說明： UPDATE以新的值更新現存表中行的列。SET子句指出要修改哪個列和他們應該給定的值。

26、WHERE子句如果被給出，指定哪個記錄行應該被更新。否則，所有的記錄行被更新。如果ORDER BY子句被指定，記錄行將被以指定的次序更新。例子： UPDATE ybc set id=8 where id=0;3.記錄的刪除簡單的語法 :DELETE FROM table_name WHERE conditions說明：刪除符合條件的資料。數據語法 :SHOW語句可用來獲取數據庫和表的幾個方面的信息，它有如下用法：SHOW DATABASES列出服務器上的數據庫SHOW TABLES列出當前數據庫中的表SHOW TABLESFROM db_name列出指定數據庫中的表SHOW FULL COLU

27、MNS FROM tbl_name顯示指定表中列的信息,等同于 desc tbl_name;SHOW INDEX FROM tbl_name顯示指定表中索引的信息SHOW TABLE STATUS顯示缺省數據庫中表的說明信息SHOW TABLE STATUS FROM db_name顯示指定數據庫中表的說明信息DESCRIBE tbl_name和 EXPLAIN tbl_name語句與SHOW COLUMNS FROM tbl_name功能相同。SELECT的使用及語法詳解作用說明 :SELECT用于檢索從一個或多個表中選取出的行。 select_expression表示你希望檢索的列。SEL

28、ECT也可以被用于檢索沒有引用任何表的計算列。要講的內容是：第一個查詢選擇特定的列，對列進行排序查找不重復的數據創(chuàng)建虛擬列，及列的別名的使用按條件檢索好，現在開始我們的第一個查詢啦！SELECT * From tbl_name;組合查詢組合查詢是指所查詢得資料來源并不只有單一的表格，而是聯(lián)合一個以上的表格才能夠得到結果的。SELECT *FROM table1,table2WHERE table1.colum1=table2.column1說明：1. 查詢兩個表格中其中 column1 值相同的資料。2. 當然兩個表格相互比較的欄位，其資料形態(tài)必須相同。3. 一個復雜的查詢其動用到的表格可能會

29、很多個。整合性的查詢：SELECT COUNT (*)FROM table_nameWHERE column_name = xxx說明：查詢符合條件的資料共有幾筆。SELECT SUM(column1)FROM table_name說明：1.計算出總和，所選的欄位必須是可數的數字形態(tài)。2.除此以外還有AVG()是計算平均、 MAX() 、 MIN()計算最大最小值的整合性查詢。SELECT column1,AVG(column2)FROM table_nameGROUP BY column1HAVING AVG(column2) xxx說明：1.GROUP BY:以 column1為一組計算c

30、olumn2的平均值必須和AVG 、SUM 等整合性查詢的關鍵字一起使用。2.HAVING :必須和GROUP BY一起使用作為整合性的限制。復合性的查詢SELECT *FROM table_name1WHERE EXISTS (SELECT *FROM table_name2WHERE conditions )說明：1.WHERE的 conditions可以是另外一個的query 。2.EXISTS在此是指存在與否。SELECT *FROM table_name1WHERE column1 IN (SELECT column1FROM table_name2WHERE conditions

31、)說明：1. IN 後面接的是一個集合，表示 column1 存在集合里面。2. SELECT 出來的資料形態(tài)必須符合 column1 。其他查詢SELECT *FROM table_name1WHERE column1 LIKE x% ;說明： LIKE必須和後面的 x%相呼應表示以x 為開頭的字串。SELECT *FROM table_name1WHERE column1 IN (xxx,yyy,.)說明： IN後面接的是一個集合，表示 column1存在集合里面。SELECT *FROM table_name1WHERE column1 BETWEEN xx AND yy說明： BETW

32、EEN表示column1的值介於xx 和yy之間。其它重要概念：SQL允許你創(chuàng)建一個虛擬列或對已有的列進組合和修改后產生的派生列。SQL允許可以創(chuàng)建一個新的虛擬列，而且還可以對它安自己的需要進行重命名，你可以按語法列名別名來對任何一個列進行重命名(注意在列名與別名之間有空格 )或用 ()括起來。詞語解釋：numericnju:merikadj.(=mumerical)數字的 , 數值的decimaldesimEladj.以十作基礎的 , 十進位的SQL 查詢語句精華使用簡要一、 簡單查詢簡單的 Transact-SQL查詢只包括選擇列表、FROM 子句和 WHERE 子句。它們分別說明所查詢列

33、、查詢的表或視圖、以及搜索條件等。例如，下面的語句查詢testtable 表中姓名為 張三的 nickname字段和 email 字段。SELECT nickname,emailFROM testtableWHERE name=張三 (一 ) 選擇列表選擇列表 (select_list) 指出所查詢列，它可以是一組列名列表、星號、表達式、變量 (包括局部變量和全局變量 )等構成。1、選擇所有列例如，下面語句顯示testtable 表中所有列的數據：SELECT *FROM testtable2、選擇部分列并指定它們的顯示次序查詢結果集合中數據的排列順序與選擇列表中所指定的列名排列順序相同。例如

34、：SELECT nickname,emailFROM testtable3、更改列標題在選擇列表中，可重新指定列標題。定義格式為：列標題 =列名列名 列標題如果指定的列標題不是標準的標識符格式時，應使用引號定界符，例如，下列語句使用漢字顯示列標題：SELECT昵稱 =nickname, 電子郵件 =emailFROM testtable4 、刪除重復行SELECT語句中使用ALL 或 DISTINCT選項來顯示表中符合條件的所有行或刪除其中重復的數據行，默認為 ALL 。使用 DISTINCT 選項時，對于所有重復的數據行在SELECT 返回的結果集合中只保留一行。5 、限制返回的行數使用 T

35、OP n PERCENT選項限制返回的數據行數， TOP n 說明返回 n 行，而 TOP n PERCENT時，說明 n 是表示一百分數， 指定返回的行數等于總行數的百分之幾。例如：SELECT TOP 2 *FROM testtableSELECT TOP 20 PERCENT *FROM testtable( 二)FROM 子句FROM 子句指定SELECT 語句查詢及與查詢相關的表或視圖。在FROM 子句中最多可指定256 個表或視圖，它們之間用逗號分隔。在 FROM 子句同時指定多個表或視圖時，如果選擇列表中存在同名列， 這時應使用對象名限定這些列所屬的表或視圖。 例如在 usert

36、able 和 citytable 表中同時存在 cityid 列，在查詢兩個表中的cityid 時應使用下面語句格式加以限定：SELECT username,citytable.cityidFROM usertable,citytableWHERE usertable.cityid=citytable.cityid在 FROM 子句中可用以下兩種格式為表或視圖指定別名：表名 as 別名表名 別名(二 ) FROM 子句FROM 子句指定 SELECT 語句查詢及與查詢相關的表或視圖。在FROM 子句中最多可指定256 個表或視圖，它們之間用逗號分隔。在 FROM 子句同時指定多個表或視圖時，如

37、果選擇列表中存在同名列， 這時應使用對象名限定這些列所屬的表或視圖。 例如在 usertable 和 citytable 表中同時存在 cityid 列，在查詢兩個表中的cityid 時應使用下面語句格式加以限定：SELECT username,citytable.cityidFROM usertable,citytableWHERE usertable.cityid=citytable.cityid在 FROM 子句中可用以下兩種格式為表或視圖指定別名：表名 as 別名表名 別名例如上面語句可用表的別名格式表示為：SELECT username,b.cityidFROM usertable a,citytable bWHERE a.cityid=b.cityidSELECT 不僅能從表或視圖中檢索數據，它還能夠從其它查詢語句所返回的結果集合中查詢數據。例如：SELECT a.au_fname+a.au_lnameFROM authors a,titleauthor ta