MySQL數(shù)據(jù)庫學習筆記

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

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

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

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

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

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

7、;其中浮點型用于域，表示方法解釋一下，如：FLOAT(M,D) UNSIGNED ZEROFILL如果 UNSIGNED被指定，負值是不允許的。 M 是顯示寬度， D 是小數(shù)位數(shù)。 并且如果負號 “”也占一個寬度。2、字符串類型字符串是多個字符組成的一個字符序列,由單引號 ,或雙引號字符包圍,但在 ANSI 模式中運行時只能用單引號 ,也就是說在SQL SERVER數(shù)據(jù)庫 ,使用應(yīng)該使用單引號 , 保證兼容性應(yīng)該使用單引號.字符串類型有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 。總結(jié)： ENUM 用于互相排斥的值，而 SET 列可以從一個值的列表中選擇多個值。常用技巧 :1. 在命令提示符下 :mysql -h -u root db_name x.sql在串的環(huán)境中， 可用十六進制常數(shù)來指定串值。 其語法與前面描述的數(shù)值值相同， 但是每對十六進制的數(shù)

9、字都被看作 ASCII 代碼并轉(zhuǎn)換為字符， 其結(jié)果用于串。例如， 0x616263 作為串時為 “a b c。”在一個字符串中，如果某個序列具有特殊的含義，每個序列以反斜線符號 ( “”)開頭，稱為轉(zhuǎn)義字符。MySQL識別下列轉(zhuǎn)義字符：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)代表一個文件的結(jié)束的問題。 (當使用mysql database filename時ASCII(26)可能會引起問題產(chǎn)生。) 一個ASCII 92反斜線( “” )字符。% 一個ASCII 37“ %”字符。它用于在正文中搜索“ %”的文字實例，否則這里“%”將解釋為一個通配符。查看章節(jié) 6.3.2.1字符串比較函數(shù)。_ 一個 ASCII 95 “ _字”符。它用于在正文中搜索 “ _” 的文字實例，否則這里 “_將”解釋為一個通配符。查看章節(jié) 6.3.2.1字符串比較函數(shù).3、日期和時間類型4、 Null 類型Null 是 ANSI 中值的空。查找 NUll 類型用 IS Null

11、 和 IS Not Null 測試；并且不能對 NULL 類型進行算術(shù)運算。其結(jié)果為 NULL 。5.mysql 的注釋為兩種 :/*/ 和#( 單行注釋 , 類似 UNIX 的腳本注釋 ;)第 07 章 MySQL 函數(shù)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 實現(xiàn) .select !1等同于select not 1;C. 流程控制函數(shù)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字符串函數(shù)返回字符串str 最左邊的那個字符的ASCII 碼值 .mysql select ascii(2);- 50返回 N 的

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

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

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

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

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

19、有空的PRIMARY KEY -是本表的主鍵?？梢酝ㄟ^一個表的內(nèi)容來拷貝一個表，如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、欄位設(shè)為主鍵。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, . 說明：我們只關(guān)心記錄添加的方式啦。注意啦， 現(xiàn)在我們開始啦。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以新的值更新現(xiàn)存表中行的列。SET子句指出要修改哪個列和他們應(yīng)該給定的值。

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

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

28、ECT也可以被用于檢索沒有引用任何表的計算列。要講的內(nèi)容是：第一個查詢選擇特定的列，對列進行排序查找不重復的數(shù)據(jù)創(chuàng)建虛擬列，及列的別名的使用按條件檢索好，現(xiàn)在開始我們的第一個查詢啦！SELECT * From tbl_name;組合查詢組合查詢是指所查詢得資料來源并不只有單一的表格，而是聯(lián)合一個以上的表格才能夠得到結(jié)果的。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.計算出總和，所選的欄位必須是可數(shù)的數(shù)字形態(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 等整合性查詢的關(guān)鍵字一起使用。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%相呼應(yīng)表示以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)建一個虛擬列或?qū)σ延械牧羞M組合和修改后產(chǎn)生的派生列。SQL允許可以創(chuàng)建一個新的虛擬列，而且還可以對它安自己的需要進行重命名，你可以按語法列名別名來對任何一個列進行重命名(注意在列名與別名之間有空格 )或用 ()括起來。詞語解釋：numericnju:merikadj.(=mumerical)數(shù)字的 , 數(shù)值的decimaldesimEladj.以十作基礎(chǔ)的 , 十進位的SQL 查詢語句精華使用簡要一、 簡單查詢簡單的 Transact-SQL查詢只包括選擇列表、FROM 子句和 WHERE 子句。它們分別說明所查詢列

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

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

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

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

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