第一篇：mysql 數(shù)據(jù)庫自增id 的總結(jié)

mysql 數(shù)據(jù)庫自增id 的總結(jié)

有一個(gè)表StuInfo，里面只有兩列 StuID，StuName其中StuID是int型，主鍵，自增列。現(xiàn)在我要插入數(shù)據(jù)，讓他自動(dòng)的向上增長，insert into StuInfo(StuID,StuName)values()如何寫？

INSERT INTO StuInfo(StuID,StuName)VALUES(NULL, `字符`)或者 INSERT INTO StuInfo(StuName)VALUES(`字符`)

INSERT和REPLACE語句的功能都是向表中插入新的數(shù)據(jù)。這兩條語句的語法類似。它們的主要區(qū)別是如何處理重復(fù)的數(shù)據(jù)。INSERT的一般用法

MySQL中的INSERT語句和標(biāo)準(zhǔn)的INSERT不太一樣，在標(biāo)準(zhǔn)的SQL語句中，一次插入一條記錄的INSERT語句只有一種形式。

INSERT INTO tablename(列名…)VALUES(列值);而在MySQL中還有另外一種形式。

INSERT INTO tablename SET column_name1 = value1, column_name2 = value2，…;第一種方法將列名和列值分開了，在使用時(shí)，列名必須和列值的數(shù)一致。如下面的語句向users表中插入了一條記錄：

INSERT INTO users(id, name, age)VALUES(123, '姚明', 25);第二種方法允許列名和列值成對(duì)出現(xiàn)和使用，如下面的語句將產(chǎn)生中樣的效果。INSERT INTO users SET id = 123, name = '姚明', age = 25;如果使用了SET方式，必須至少為一列賦值。如果某一個(gè)字段使用了省缺值（如默認(rèn)或自增值），這兩種方法都可以省略這些字段。如id字段上使用了自增值，上面兩條語句可以寫成如下形式：

INSERT INTO users(name, age)VALUES('姚明',25);INSERT INTO uses SET name = '姚明', age = 25;MySQL在VALUES上也做了些變化。如果VALUES中什么都不寫，那MySQL將使用表中每一列的默認(rèn)值來插入新記錄。

INSERT INTO users()VALUES();如果表名后什么都不寫，就表示向表中所有的字段賦值。使用這種方式，不僅在VALUES中的值要和列數(shù)一致，而且順序不能顛倒。INSERT INTO users VALUES(123, '姚明', 25);如果將INSERT語句寫成如下形式MySQL將會(huì)報(bào)錯(cuò)。

INSERT INTO users VALUES('姚明',25);2 使用INSERT插入多條記錄

看到這個(gè)標(biāo)題也許大家會(huì)問，這有什么好說的，調(diào)用多次INSERT語句不就可以插入多條記錄了嗎！但使用這種方法要增加服務(wù)器的負(fù)荷，因?yàn)椋瑘?zhí)行每一次SQL服務(wù)器都要同樣對(duì)SQL進(jìn)行分析、優(yōu)化等操作。幸好MySQL提供了另一種解決方案，就是使用一條INSERT語句來插入多條記錄。這并不是標(biāo)準(zhǔn)的SQL語法，因此只能在MySQL中使用。

INSERT INTO users(name, age)VALUES('姚明', 25),('比爾.蓋茨', 50),('火星人', 600);上面的INSERT 語句向users表中連續(xù)插入了3條記錄。值得注意的是，上面的INSERT語句中的VALUES后必須每一條記錄的值放到一對(duì)(…)中，中間使用“,”分割。假設(shè)有一個(gè)表table1 CREATE TABLE table1(n INT)；

如果要向table1中插入5條記錄，下面寫法是錯(cuò)誤的： INSERT INTO table1(i)VALUES(1,2,3,4,5);MySQL將會(huì)拋出下面的錯(cuò)誤

ERROR 1136: Column count doesn't match value count at row 1 而正確的寫法應(yīng)該是這樣：

INSERT INTO t able1(i)VALUES(1),(2),(3),(4),(5);當(dāng)然，這種寫法也可以省略列名，這樣每一對(duì)括號(hào)里的值的數(shù)目必須一致，而且這個(gè)數(shù)目必須和列數(shù)一致。如：

INSERT INTO t able1 VALUES(1),(2),(3),(4),(5);3 REPLACE語句

我們?cè)谑褂脭?shù)據(jù)庫時(shí) 可能會(huì)經(jīng)常遇到這種情況。如果一個(gè)表在一個(gè)字段上建立了唯一索引，當(dāng)我們?cè)傧蜻@個(gè)表中使用已經(jīng)存在的鍵值插入一條記錄，那將會(huì)拋出一個(gè)主鍵沖突的錯(cuò)誤。當(dāng)然，我們可能想用新記錄的值來覆蓋原來的記錄值。如果使用傳統(tǒng)的做法，必須先使用DELETE語句刪除原先的記錄，然后再使用INSERT插入新的記錄。而在MySQL中為我們提供了一種新的解決方案，這就是REPLACE語句。使用REPLACE插入一條記錄時(shí)，如果不重復(fù)，REPLACE就和 INSERT的功能一樣，如果有重復(fù)記錄，REPLACE就使用新記錄的值來替換原來的記錄值。

使用REPLACE的最大好處就是可以將DELETE和INSERT合二為一，形成一個(gè)原子操作。這樣就可以不必考慮在同時(shí)使用DELETE和INSERT時(shí)添加事務(wù)等復(fù)雜操作了。

在使用REPLACE時(shí)，表中必須有唯一索引，而且這個(gè)索引所在的字段不能允許空值，否則REPLACE就和INSERT完全一樣的。

在執(zhí)行REPLACE后，系統(tǒng)返回了所影響的行數(shù)，如果返回1，說明在表中并沒有重復(fù)的記錄，如果返回2，說明有一條重復(fù)記錄，系統(tǒng)自動(dòng)先調(diào)用了DELETE刪除這條記錄，然后再記錄用INSERT來插入這條記錄。如果返回的值大于2，那說明 有多個(gè)唯一索引，有多條記錄被刪除和插入。

REPLACE的語法和INSERT非常的相似，如下面的REPLACE語句是插入或更新一條記錄。

REPLACE INTO users(id,name,age)VALUES(123, '趙本山', 50);插入多條記錄：

REPLACE INTO users(id, name, age)VALUES(123, '趙本山', 50),(134,'Mary',15);REPLACE也可以使用SET語句

REPLACE INTO users SET id = 123, name = '趙本山', age = 50;

第1頁 上面曾提到REPLACE可能影響3條以上的記錄，這是因?yàn)樵诒碇杏谐^一個(gè)的唯一索引。在這種情況下，REPLACE將考慮每一個(gè)唯一索引，并對(duì)每一個(gè)索引對(duì)應(yīng)的重復(fù)記錄都刪除，然后插入這條新記錄。假設(shè)有一個(gè)table1表，有3個(gè)字段a, b, c。它們都有一個(gè)唯一索引。

CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);假設(shè)table1中已經(jīng)有了3條記錄 a b c 1 1 1 2 2 2 3 3 3 下面我們使用REPLACE語句向table1中插入一條記錄。REPLACE INTO table1(a, b, c)VALUES(1,2,3);返回的結(jié)果如下

Query OK, 4 rows affected(0.00 sec)在table1中的記錄如下 a b c 1 2 3 我們可以看到，REPLACE將原先的3條記錄都刪除了，然后將（1, 2, 3）插入。UPDATE UPDATE的功能是更新表中的數(shù)據(jù)。這的語法和INSERT的第二種用法相似。必須提供表名以及SET表達(dá)式，在后面可以加WHERE以限制更新的記錄范圍。

UPDATE table_anem SET column_name1 = value1, column_name2 = value2,...WHERE...;如下面的語句將users表中id等于123的記錄的age改為24 UPDATE users SET age = 24 WHERE id = 123;同樣，可以使用UPDATE更新多個(gè)字段的值 UPDATE users SET age = 24, name = 'Mike' WHERE id = 123;上面的UPDATE語句通過WHERE指定一個(gè)條件，否則，UPDATE將更新表中的所有記錄的值。

在使用UPDATE更新記錄時(shí)，如果被更新的字段的類型和所賦的值不匹配時(shí)，MySQL將這 個(gè)值轉(zhuǎn)換為相應(yīng)類型的值。如果這個(gè)字段是數(shù)值類型，而且所賦值超過了這個(gè)數(shù)據(jù)類型的最大范圍，那么MySQL就將這個(gè)值轉(zhuǎn)換為這個(gè)范圍最大或最小值。如果 字符串太長，MySQL就將多余的字符串截去。如果設(shè)置非空字段為空，那么將這個(gè)字段設(shè)置為它們的默認(rèn)值，數(shù)字的默認(rèn)值是0，字符串的默認(rèn)值是空串（不是 null，是“"）。

有兩種情況UPDATE不會(huì)對(duì)影響表中的數(shù)據(jù)。

1.當(dāng)WHERE中的條件在表中沒有記錄和它匹配時(shí)。2.當(dāng)我們將同樣的值賦給某個(gè)字段時(shí)，如將字段abc賦為'123'，而abc的原值就是'123'。和INSERT、REPLACE一樣，UPDATE也返回所更新的記錄數(shù)。但這些記錄數(shù)并不包括滿足WHERE條件的，但卻未被更新的記錄。如下同的UPDATE語句就未更新任何記錄。

UPDATE users SET age = 30 WHERE id = 12;Query OK, 0 rows affected(0.00 sec)

第2頁 需要注意的時(shí)，如果一個(gè)字段的類型是TIMESTAMP，那么這個(gè)字段在其它字段更新時(shí)自動(dòng)更新。

在有些時(shí)候我們需要得到UPDATE所選擇的行數(shù)，而不是被更新的行數(shù)。我們可以通過一些API來達(dá)到這個(gè)目的。如MySQL提供的C API提供了一個(gè)選項(xiàng)可以得到你想要的記錄數(shù)。而MySQL的JDBC驅(qū)動(dòng)得到的默認(rèn)記錄數(shù)也是匹配的記錄數(shù)。

UPDATE和REPLACE基本類似，但是它們之間有兩點(diǎn)不同。1.UPDATE在沒有匹配記錄時(shí)什么都不做，而REPLACE在有重復(fù)記錄時(shí)更新，在沒有重復(fù)記錄時(shí)插入。

2.UPDATE可以選擇性地更新記錄的一部分字段。而REPLACE在發(fā)現(xiàn)有重復(fù)記錄時(shí)就將這條記錄徹底刪除，再插入新的記錄。也就是說，將所有的字段都更新了。DELETE和TRUNCATE TABLE 在MySQL中有兩種方法可以刪除數(shù)據(jù)，一種是DELETE語句，另一種是TRUNCATE TABLE語句。DELETE語句可以通過WHERE對(duì)要?jiǎng)h除的記錄進(jìn)行選擇。而使用TRUNCATE TABLE將刪除表中的所有記錄。因此，DELETE語句更靈活。

如果要清空表中的所有記錄，可以使用下面的兩種方法： DELETE FROM table1 TRUNCATE TABLE table1 其中第二條記錄中的TABLE是可選的。

如果要?jiǎng)h除表中的部分記錄，只能使用DELETE語句。DELETE FROM table1 WHERE...;如果DELETE不加WHERE子句，那么它和TRUNCATE TABLE是一樣的，但它們有一點(diǎn)不同，那就是DELETE可以返回被刪除的記錄數(shù)，而TRUNCATE TABLE返回的是0。

如果一個(gè)表中有自增字段，使用TRUNCATE TABLE和沒有WHERE子句的DELETE刪除所有記錄后，這個(gè)自增字段將起始值恢復(fù)成1.如果你不想這樣做的話，可以在DELETE語句中加上永真的WHERE，如WHERE 1或WHERE true。

DELETE FROM table1 WHERE 1;上面的語句在執(zhí)行時(shí)將掃描每一條記錄。但它并不比較，因?yàn)檫@個(gè)WHERE條件永遠(yuǎn)為true。這樣做雖然可以保持自增的最大值，但由于它是掃描了所有的記錄，因此，它的執(zhí)行成本要比沒有WHERE子句的DELETE大得多。

DELETE和TRUNCATE TABLE的最大區(qū)別是DELETE可以通過WHERE語句選擇要?jiǎng)h除的記錄。但執(zhí)行得速度不快。而且還可以返回被刪除的記錄數(shù)。而TRUNCATE TABLE無法刪除指定的記錄，而且不能返回被刪除的記錄。但它執(zhí)行得非常快。

和標(biāo)準(zhǔn)的SQL語句不同，DELETE支持ORDER BY和LIMIT子句，通過這兩個(gè)子句，我們可以更好地控制要?jiǎng)h除的記錄。如當(dāng)我們只想刪除WHERE子句過濾出來的記錄的一部分，可以使用LIMIB，如果要?jiǎng)h除后幾條記錄，可以通過ORDER BY和LIMIT配合使用。假設(shè)我們要?jiǎng)h除users表中name等于”Mike“的前6條記錄。可以使用如下的DELETE語句：

DELETE FROM users WHERE name = 'Mike' LIMIT 6;一般MySQL并不確定刪除的這6條記錄是哪6條，為了更保險(xiǎn)，我們可以使用ORDER BY對(duì)記錄進(jìn)行排序。

DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6;將B表查詢的數(shù)據(jù)插入A表： insert into A select。。from B where。。

第3頁 or insert into A(。。）select。。from B where。。將B表的值更新到A表：

update A set a =(select b from B where B.id = A.id)where exists(select 1 from B where id = A.id)and。。

同時(shí)刪除多表數(shù)據(jù)：

delete A,B from A,B where A.id = B.id and。。or delete from A,B using A,B where A.id = B.id and。。

1.建表時(shí)就創(chuàng)建自增列： create table test(id int auto_increment primary key, name varchar(20)not null, password varchar(20)not null);

insert into test values(null,'aa','aa');insert into test values(null,'bb','bb');

注意：

插入語句時(shí)，自增列的值為NULL。

2、創(chuàng)建表格后添加： alter table table1 add id int auto_increment primary key自增字段，一定要設(shè)置為primary key.注:只有int類型且為primary key 才可以使用auto_increment.如果用hibernate作為持久層,那么主鍵ID處用identity類型.對(duì)于一個(gè)表主鍵的選擇，從架構(gòu)設(shè)計(jì)角度來說，盡量設(shè)計(jì)一個(gè)與業(yè)務(wù)無關(guān)的字段來作為主鍵會(huì)對(duì)系統(tǒng)更好 當(dāng)然，這勢(shì)必會(huì)增加表數(shù)據(jù)量的大小，可能有些人會(huì)認(rèn)為這個(gè)副作用不值得而選擇一個(gè)有業(yè)務(wù)意義的字段（或者多個(gè)字段）來作為主鍵

但是對(duì)于大多數(shù)時(shí)候，這個(gè)字段所增加的物理存儲(chǔ)空間實(shí)際上是很小的，并沒有很多人想象的那么可怕。而帶來邏輯上的簡單是很可觀的。

另外，是否選擇自增類型作為主鍵，這個(gè)可以說是各有好處，如果用自增，應(yīng)用可以不care這個(gè)主鍵的值得設(shè)定，但是有在高并發(fā)下成為瓶頸的風(fēng)險(xiǎn)，當(dāng)然如果并發(fā)并不是非常非常高的話，一般不會(huì)成為瓶頸。如果自己控制這個(gè)主鍵值的話，更需要付出一點(diǎn)點(diǎn)代價(jià)來生成這

第4頁 個(gè)值，并發(fā)問題可以通過擴(kuò)展應(yīng)用集群來解決。

2種解決辦法

1.修改你的數(shù)據(jù)庫表，將ID設(shè)為自增長，注：只有主鍵才可以設(shè)置為自增長

例：CREATE TABLE `message `(`id` INT(8)NOT NULL AUTO_INCREMENT, PRIMARY KEY(`id`));

2.代碼中進(jìn)行處理

Statement ps;ResultSet rs;int id = 0;..................String sql=”select max(id)as id from message“;rs = ps.executeQuery(sql);//查詢出最大ID if(rs.next){//如果有ID，原有ID+1 id = rs.getInt(”id“)+1;

}else{//否則 ID 初始為0 id = 0;}

//下面接你寫的代碼就OK了

PreparedStatement ps;

String sql=”insert message(id,name,msg)value(?,?,?)"

ps.setString(1,id);ps.setString(2,name);ps.setString(3,msg);

第5頁

第二篇：mysql數(shù)據(jù)庫要點(diǎn)總結(jié)

查詢(R)Selec子句

書寫順序

Select distinct：要返回的列或表達(dá)式 From：從中檢索數(shù)據(jù)的表 Where：行級(jí)過濾/分組前過濾 group by：分組說明

having：組級(jí)過濾/分組后過濾 order by：輸出排序順序desc/asc limit start, count：要檢索的行數(shù) limit 3,4 從第3行開始的連續(xù)4行

SELECT order_num,sum(quantity*item_price)ordertotal FROM orderitems WHEREorder_numBETWEEN20005AND 20009GROUP BY order_num HAVINGsum(quantity*item_price)>=50ORDER BYordertotalDESCLIMIT4;執(zhí)行順序

From表名->inner/left/right/ join on->where->group by->select distinct *->having->order by->limit start, count 插入Insert into(C)INSERT INTO students(name)values(‘楊過’),(‘小龍女’);更新(U)Update UPDATE customers SETcust_email = ‘elmer@fudd.com’WHEREcust_id = 10005;刪除(D)Delete DELETE FROM customers WHEREcust_id = 10006;Truncate刪除原來的表并重新創(chuàng)建一個(gè)表，刪除標(biāo)的全部內(nèi)容時(shí)效率高。

操作表

創(chuàng)建CREATE TABLE customers(Cust_idint not null auto_increment primary key,Cust_name char(50)not null，Cust_addresschar(50)null)engine = innoDB;更新ALTER TABLE刪除DROP TABLEcustomers2;重命名RENAME TABLE customers2 TO customers;操作數(shù)據(jù)庫

創(chuàng)建CREATE DATABASE xxx charset = utf8;刪除DROP DATABASEXXX;切換USE XXX;查看SHOW DATABASES;關(guān)聯(lián)查詢

INNER/LEFT/RIGHTJOIN ON SELECT students.name,subjects.title,scores.scoreFROM scores INNER JOIN students ONscores.stuid = students.id INNER JOIN subjects ONscores.subid = subjects.id;

第三篇：Mysql數(shù)據(jù)庫學(xué)習(xí)心得

Mysql數(shù)據(jù)庫學(xué)習(xí)心得(1)

由于工作中需要使用mysql，筆者通過網(wǎng)上學(xué)習(xí)，動(dòng)手實(shí)踐，終于從一個(gè)“數(shù)據(jù)庫菜鳥”變成了能熟練操作mysql的“準(zhǔn)大蝦”了，：)。現(xiàn)將學(xué)習(xí)心得整理如下。

MySQL是完全網(wǎng)絡(luò)化的跨平臺(tái)關(guān)系型數(shù)據(jù)庫系統(tǒng)，一個(gè)真正的多用戶、多線程SQL數(shù)據(jù)庫服務(wù)器，同時(shí)是具有客戶機(jī)/服務(wù)器體系結(jié)構(gòu)的分布式數(shù)據(jù)庫管理系統(tǒng)。它具有功能強(qiáng)、使

用簡便、管理方便、運(yùn)行速度快、安全可靠性強(qiáng)等優(yōu)點(diǎn)，用戶可利用許多語言編寫訪問MySQL數(shù)據(jù)庫的程序，對(duì)于中、小型應(yīng)用系統(tǒng)是非常理想的。除了支持標(biāo)準(zhǔn)的ANSI SQL語句，更重要的是，它還支持多種平臺(tái)，而在Unix系統(tǒng)上該軟件支持多線程運(yùn)行方式，從而能獲得相當(dāng)好的性能。對(duì)于不使用Unix的用戶，它可以在Windows NT系統(tǒng)上以系統(tǒng)服務(wù)方式運(yùn)行，或者在Windows 95/98系統(tǒng)上以普通進(jìn)程方式運(yùn)行。而在Unix/Linux系統(tǒng)上，MySQL支持多線程運(yùn)行方式，從而能獲得相當(dāng)好的性能，而且它是屬于開放源代碼軟。

MySQL是以一個(gè)客戶機(jī)/服務(wù)器結(jié)構(gòu)的實(shí)現(xiàn)，它由一個(gè)服務(wù)器守護(hù)程序mysqld和很多不同的客戶程序和庫組成，MySQL的執(zhí)行性能非常高，運(yùn)行速度非常快，并非常容易使用，是一個(gè)非常棒的數(shù)據(jù)庫。MySQL的官方發(fā)音是“My Ess Que Ell”(不是MY-SEQUEL)。

一.獲得MySQL

首先必須下載MySQL。Mysql的官方網(wǎng)站是：www.tmdps.cnf copy為c:my.cnf，并把c:mysqlibgwinb19.dll copy到winntsystem32。

3。啟動(dòng)mysql的方法是：

c:mysqlinmysqld-shareware--install

net start mysql

這么簡單，就可以啟動(dòng)了。

4。更改超級(jí)用戶(root)的密碼：

C:mysqlinmysql mysql

mysql> UPDATE user SET password=PASSWORD('your password')swheresuser='root';

mysql> QUIT

C:mysqlinmysqladmin reload

使用命令C:mysqlinmysqlshow去看看檢測(cè)一下。在這里應(yīng)該顯示：

+-----------+

| Databases |

+-----------+

| mysql |

| test |

+-----------+

看到這些信息這一步就證明沒問題了

再來:

C:mysqlinmysqlshow--user=root--password=your password mysql

在這里應(yīng)該顯示：

Database: mysql

+--------------+

| Tables |

+--------------+

| columns_priv |

| db |

| host |

| tables_priv |

| user |

+--------------+

一切搞定！

5。C:mysqlinmysqladmin version status proc

應(yīng)該可以看到這些版本信息的：

mysqladmin Ver 8.0 Distrib 3.22.32, for Win95/Win98 on i586

TCX Datakonsult AB, by Monty

Server version 3.22.32-shareware-debug

Protocol version 10

Connection localhost via TCP/IP

TCP port 3306

Uptime: 1 hour 29 min 30 sec

Threads: 1 Questions: 72 Slow queries: 0 Opens: 16 Flush tables: 1 Open tables: 0 Memory in use: 16423K Max memory

used: 16490K

Uptime: 5370 Threads: 1 Questions: 72 Slow queries: 0 Opens: 16 Flush tables: 1 Open tables: 0 Memory in use: 16423K

Max memory used: 16490K

+----+------+-----------+----+---------+------+-------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+------+-----------+----+---------+------+-------+------------------+

| 35 | ODBC | localhost | | Query | 0 | | show processlist |

+----+------+-----------+----+---------+------+-------+------------------+

接著，用show databases命令可以將安裝的數(shù)據(jù)庫列出來：

mysql> show databases;

你就可以看到：

+----------+

| Database |

+----------+

| mysql |

| test |

+----------+ rows in set(0.00 sec)

如果一切正常的話，那說明MySQL可以完全工作了!如果要退出程序，輸入：exit

mysql> exit;

Bye

Mysql數(shù)據(jù)庫學(xué)習(xí)心得(3)------------------

三.Mysql常識(shí)

(一)字段類型

1.INT[(M)]

正常大小整數(shù)類型

2.DOUBLE[(M,D)] [ZEROFILL]

正常大小(雙精密)浮點(diǎn)數(shù)字類型

3.DATE

日期類型。支持的范圍是'1000-01-01'到'9999-12-31'。MySQL以'YYYY-MM-DD'格式來顯示DATE值，但是允許你使用字符串或數(shù)字把值賦給

DATE列

4.CHAR(M)

定長字符串類型，當(dāng)存儲(chǔ)時(shí)，總是是用空格填滿右邊到指定的長度

5.BLOB TEXT

BLOB或TEXT類型，最大長度為65535(2^16-1)個(gè)字符。

6.VARCHAR

變長字符串類型，最常用的類型。

(二)基本操作

1:顯示數(shù)據(jù)庫

mysql>SHOW DATABASES;

2：當(dāng)前選擇的數(shù)據(jù)庫，mysql> SELECT DATABASE();

+------------+

| DATABASE()|

+------------+

| test |

+------------+

3.當(dāng)前數(shù)據(jù)庫包含的表信息：

mysql> SHOW TABLES;

+---------------------+

| Tables in test |

+---------------------+

| mytable1 |

| mytable2 |

+---------------------+

4.獲取表結(jié)構(gòu)

mysql> desc mytable1;

+---------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| s1 | varchar(20)| YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

5.創(chuàng)建表

表是數(shù)據(jù)庫的最基本元素之一，表與表之間可以相互獨(dú)立，也可以相互關(guān)聯(lián)。創(chuàng)建表的基本語法如下：

create table table_name

(column_name datatype {identity |null|not null}，…)

其中參數(shù)table_name和column_name必須滿足用戶數(shù)據(jù)庫中的識(shí)別器(identifier)的要求，參數(shù)datatype是一個(gè)標(biāo)準(zhǔn)的SQL類型或由用戶數(shù)

據(jù)庫提供的類型。用戶要使用non-null從句為各字段輸入數(shù)據(jù)。

create table還有一些其他選項(xiàng)，如創(chuàng)建臨時(shí)表和使用select子句從其他的表中讀取某些字段組成新表等。還有，在創(chuàng)建表是可用PRIMARY

KEY、KEY、INDEX等標(biāo)識(shí)符設(shè)定某些字段為主鍵或索引等。書寫上要注意：在一對(duì)圓括號(hào)里的列出完整的字段清單。字段名間用逗號(hào)隔開

。字段名間的逗號(hào)后要加一個(gè)空格。最后一個(gè)字段名后不用逗號(hào)。所有的SQL陳述都以分號(hào)“;”結(jié)束。

例：

mysql>CREATE TABLE guest(name varchar(10),sex varchar(2),age int(3),career varchar(10));

6.創(chuàng)建索引

索引用于對(duì)數(shù)據(jù)庫的查詢。一般數(shù)據(jù)庫建有多種索引方案，每種方案都精于某一特定的查詢類。索引可以加速對(duì)數(shù)據(jù)庫的查詢過程。創(chuàng)建

索引的基本語法如下：

create index index_name

on table_name(col_name[(length)],...)

例：

mysql> CREATE INDEX number ON guest(number(10));

7.執(zhí)行查詢

查詢是使用最多的SQL命令。查詢數(shù)據(jù)庫需要憑借結(jié)構(gòu)、索引和字段類型等因素。大多數(shù)數(shù)據(jù)庫含有一個(gè)優(yōu)化器(optimizer)，把用戶的查

詢語句轉(zhuǎn)換成可選的形式，以提高查詢效率。

值得注意的是MySQL不支持SQL92標(biāo)準(zhǔn)的嵌套的where子句，即它只支持一個(gè)where子句。其基本語法如下：

SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY]

[DISTINCT | DISTINCTROW | ALL]

select_expression,...[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]

[FROM table_references

[WHERE where_definition]

[GROUP BY col_name,...]

[HAVING where_definition]

[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]

[LIMIT [offset,] rows]

[PROCEDURE procedure_name] ]

其中where從句是定義選擇標(biāo)準(zhǔn)的地方，where_definition可以有不同的格式，但都遵循下面的形式：

字段名操作表達(dá)式

字段名操作字段名

在第一種形式下，標(biāo)準(zhǔn)把字段的值與表達(dá)式進(jìn)行比較；在第二種形式下，把兩個(gè)字段的值進(jìn)行比較。根據(jù)所比較的數(shù)據(jù)類型，search_condition中的操作可能選以下幾種：

=檢查是否相等

！=檢查是否不等

>(或>=)檢查左邊值是否大于(或大于等于)右邊值

<(或<=)檢查左邊值是否小于(或小于等于)右邊值

[not] between檢查左邊值是否在某個(gè)范圍內(nèi)

[not] in檢查左邊是否某個(gè)特定集的成員

[not] like檢查左邊是否為右邊的子串

is [not] null檢查左邊是否為空值

在這里，可以用通配符_代表任何一個(gè)字符，%代表任何字符串。使用關(guān)鍵字、和可以生成復(fù)雜的詞，它們運(yùn)行檢查時(shí)使用

布爾表達(dá)式的多重標(biāo)準(zhǔn)集。

例：

mysql> select t1.name, t2.salary from employee AS t1, info AS t2swherest1.name = t2.name;

mysql> select college, region, seed from tournament

ORDER BY region, seed;

mysql> select col_name from tbl_nameswherescol_name > 0;

8.改變表結(jié)構(gòu)

在數(shù)據(jù)庫的使用過程中，有時(shí)需要改變它的表結(jié)構(gòu)，包括改變字段名，甚至改變不同數(shù)據(jù)庫字段間的關(guān)系。可以實(shí)現(xiàn)上述改變的命令是

alter，其基本語法如下：

alter table table_name alter_spec [, alter_spec...]

例：

mysql> alter table dbname add column userid int(11)not null primary key auto_increment;

這樣，就在表dbname中添加了一個(gè)字段userid，類型為int(11)。

9.修改表中數(shù)據(jù)

在使用數(shù)據(jù)庫過程中，往往要修改其表中的數(shù)據(jù)，比如往表中添加新數(shù)據(jù)，刪除表中原有數(shù)據(jù)，或?qū)Ρ碇性袛?shù)據(jù)進(jìn)行更改。它們的基本

語法如下：

數(shù)據(jù)添加：

insert [into] table_name [(column(s))]

values(expression(s))

例：

mysql>insertsintosmydatabase values('php','mysql','asp','sqlserver','jsp','oracle');Mysql數(shù)據(jù)庫學(xué)習(xí)心得(4)------------------

10.表的數(shù)據(jù)更新

(1)一次修改一個(gè)字段，再次注意語法。文本需要加引號(hào)但數(shù)字不要。

mysql>update table01 set field03='new info'swheresfield01=1;

Query OK, 1 row affected(0.00 sec)

(2)一次改變多個(gè)字段，記住在每一個(gè)更新的字段間用逗號(hào)隔開。

mysql>update table01 set field04=19991022, field05=062218swheresfield01=1;

Query OK, 1 row affected(0.00 sec)

(3)一次更新多個(gè)數(shù)據(jù)

mysql>update table01 set field05=152901swheresfield04>19990101;

Query OK, 3 rows affected(0.00 sec)

11.刪除數(shù)據(jù)

mysql>delete from table01swheresfield01=3;

Query OK, 1 row affected(0.00 sec)

12.導(dǎo)入數(shù)據(jù)庫表

(1)創(chuàng)建.sql文件

(2)先產(chǎn)生一個(gè)庫如auction.c:mysqlin>mysqladmin-u root-p creat auction，會(huì)提示輸入密碼，然后成功創(chuàng)建。

(3)導(dǎo)入auction.sql文件

c:mysqlin>mysql-u root-p auction < auction.sql。

通過以上操作，就可以創(chuàng)建了一個(gè)數(shù)據(jù)庫auction以及其中的一個(gè)表auction。

13.mysql數(shù)據(jù)庫的授權(quán)

mysql>grant select,insert,delete,create,drop

on *.*(或test.*/user.*/..)

to用戶名@localhost

identified by '密碼'；

如：新建一個(gè)用戶帳號(hào)以便可以訪問數(shù)據(jù)庫，需要進(jìn)行如下操作：

mysql> grant usage

-> ON test.*

-> TO testuser@localhost;

Query OK, 0 rows affected(0.15 sec)

此后就創(chuàng)建了一個(gè)新用戶叫：testuser，這個(gè)用戶只能從localhost連接到數(shù)據(jù)庫并可以連接到test數(shù)據(jù)庫。下一步，我們必須指定

testuser這個(gè)用戶可以執(zhí)行哪些操作：

mysql> GRANT select, insert, delete,update

-> ON test.*

-> TO testuser@localhost;

Query OK, 0 rows affected(0.00 sec)

此操作使testuser能夠在每一個(gè)test數(shù)據(jù)庫中的表執(zhí)行SELECT，INSERT和DELETE以及UPDATE查詢操作。現(xiàn)在我們結(jié)束操作并退出MySQL客戶

程序：

mysql> exit

Bye

14.授權(quán)MySQL用戶密碼

MySQL數(shù)據(jù)庫的默認(rèn)用戶名為“root”(MS SQL Server的sa相似)，密碼默認(rèn)為空。在DOS提示符(注 意，不是mysql提示符)下輸入

c:mysqlin>“mysqladmin-u root-p password newpassword

回車后會(huì)提示你輸入原來的密碼，由于原來密碼為空，直接回車，root用戶的密碼便改為”newpassword“了。

Mysql數(shù)據(jù)庫學(xué)習(xí)心得(5)------------------

四.安裝phpMyAdmin

MySQL圖形界面管理器phpMyAdmin是一套以php3寫成，針對(duì)MySQL數(shù)據(jù)庫系統(tǒng)的Web管理界面。它可以很方便地以圖形化界面，來對(duì)MySQL數(shù)據(jù)庫里的字段、數(shù)據(jù)進(jìn)行增加、刪除等的動(dòng)作，更可以做數(shù)據(jù)庫本身的增刪管理；phpMyAdmin可以管理整個(gè)MySQL服務(wù)器(需要超級(jí)用戶)，也可以管理單個(gè)數(shù)據(jù)庫。另外，您也可以通過使用這個(gè)圖形化界面來學(xué)習(xí)SQL正確的語法，直至熟練掌握。那要等到你看過MySQL手冊(cè)中相關(guān)的部分。你可以到http://www.phpwizard.net/phpMyAdmin/去下載最新的版本。

首先，將phpMyAdmin軟件包解到一個(gè)目錄下。

1.修改文檔config.inc.php3。

將原來的設(shè)定：

§cfgServers[1]['host'] = '';// MySQL hostname

§cfgServers[1]['port'] = '';// MySQL port-leave blank fordefault port

§cfgServers[1]['adv_auth'] = false;// Use advanced authentication?

§cfgServers[1]['stduser'] = '';// MySQL standard user(only needed with advanced auth)

§cfgServers[1]['stdpass'] = '';// MySQL standard password(only needed with advanced auth)

§cfgServers[1]['user'] = '';// MySQL user(only needed withbasic auth)

§cfgServers[1]['password'] = '';// MySQL password(only needed with basic auth)

§cfgServers[1]['only_db'] = '';// If set to a db-name, only this db is accessible

§cfgServers[1]['verbose'] = '';// Verbose name for this host-leave blank to show the hostname

:

:

require(”english.inc.php3“);

修改成：

§cfgServers[1]['host'] = 'MySQL Server的hostname';//填入您的MySQL Server的主機(jī)名稱

§cfgServers[1]['port'] = '';//填入連結(jié)MySQL的port，不填則以預(yù)設(shè)的port進(jìn)行連結(jié)

§cfgServers[1]['adv_auth'] = true;//改成true則進(jìn)入phpMyAdmin必須先身份認(rèn)證

§cfgServers[1]['stduser'] = 'root';// MySQL使用者的帳號(hào)

§cfgServers[1]['stdpass'] = '密碼';// MySQL使用者的密碼

§cfgServers[1]['user'] = 'root';// MySQL管理帳號(hào)

§cfgServers[1]['password'] = '密碼';// MySQL管理密碼

§cfgServers[1]['only_db'] = '';//指定管理的資庫名稱，不填則可以管理整個(gè)Server

§cfgServers[1]['verbose'] = '';//指定MySQL的名稱，不填則使用系統(tǒng)本身的hostname

:

:

require(”chinese_gb.inc.php3");//將語言改成中文

說明：

(1)因本管理接口，可以以一個(gè)接口，管理多個(gè)MySQL Server，所以可以在config.inc.php3中找到

§cfgServers[1]...§cfgServers[1]...§cfgServers[1]...：

§cfgServers[2]...§cfgServers[2]...§cfgServers[2]...其中[1]代表第一個(gè)MySQL Server，[2]代表第二個(gè)MySQL Server，您要管理的MySQL Server超過三臺(tái)以上，您可以依照同樣的?述，增加[4].....下去！

(2)若您的MySQL Server與http Server是同一臺(tái)，則§cfgServers[1]['host'] =可直接填入localhost。

最后，打開流覽器，輸入你的網(wǎng)址/phpMyAdmin之后您會(huì)看到一個(gè)密碼驗(yàn)證的小窗口，輸入您的MySQL管理帳號(hào)及密碼，即可成功地看到phpMyAdmin的管理畫面。通過phpmyadmin，你就可以圖形化的、方便的管理你的所有數(shù)據(jù)庫了。

五.小結(jié)

通過以上的學(xué)習(xí)和操作，終于可以自由操作mysql數(shù)據(jù)庫了，也可以輕車熟路的使用php或者asp調(diào)用mysql了。最后，筆者要特別感謝開發(fā)了mysql并且免費(fèi)貢獻(xiàn)的天才以及在網(wǎng)上提供mysql使用說明的大蝦們。

第四篇：計(jì)算機(jī)二級(jí)《MySQL數(shù)據(jù)庫程序設(shè)計(jì)》知識(shí)點(diǎn)總結(jié)

MySQL知識(shí)點(diǎn)總結(jié)

.數(shù)據(jù)操作：檢索、排序、過濾、分組、匯總、計(jì)算、聯(lián)接、子查詢與組合查詢.表操作：表的創(chuàng)建、修改、刪除和重命名、表數(shù)據(jù)的插入、更新和刪除.索引（含主、外鍵）、視圖

.難度編程：存儲(chǔ)過程與存儲(chǔ)函數(shù)、觸發(fā)器與事件、PHP.數(shù)據(jù)庫管理：事務(wù)處理、用戶與權(quán)限、備份與還原、數(shù)據(jù)庫維護(hù)

1.檢索數(shù)據(jù)：select?from?

Select [distinct] prod_id,prod_name from products [limit 4,5];2.檢索排序：order by?

Select * from products order by prod_id [asc|desc],prod_name [asc|desc];3.過濾數(shù)據(jù)：where 字句

= <>!= >>= <<= between（1）普通where 字句

Select prod_id,prod_name from products where prod_name=’liyang’;Select prod_id,prod_name from products where prod_id between 10 and 50;Select prod_id,prod_name from products where prod_name is [not] null;（2）組合where字句：使用AND和OR連接多個(gè)條件表達(dá)式，且AND次序優(yōu)于OR;（3）IN 與NOT操作符

Select prod_id,prod_name from products where prod_id [not] in(1,2,3)|prod_name in(’zhangsan’,’lisi’,’wangwu’);（4）LIKE操作符與通配符：“%”與“_”

Select prod_id,prod_name from products where prod_name like ’%liu%’;Select prod_id,prod_name from products where prod_name like ’_u%’;找出u位于名字的第二個(gè)位置的prod_id和prod_name。（5）正則表達(dá)式 4.計(jì)算字段

（1）拼接字段：concat(?,?)Select concat(姓氏,名字)as 姓名 from orders;Select concat(vend_name,’(’,vend_country,’)’)from vendors;（2）算術(shù)運(yùn)算

Select prod_name,prod_price,prod_num,prod_price*prod_num as prod_money from products;4.使用數(shù)據(jù)處理函數(shù)：文本處理函數(shù)、日期和時(shí)間處理函數(shù)、數(shù)值處理函數(shù)。5.匯總數(shù)據(jù)：聚集函數(shù) SUM()AVG()COUNT()MAX()MIN()Select avg(distinct prod_price)from products;Select avg(prod_price)均價(jià),max(prod_price)最高價(jià) from products;6.分組數(shù)據(jù)：group by? 創(chuàng)建分組、過濾分組、分組排序

Select count(prod_id),prod_id from products where prod_id>1000 group by prod_id having count(prod_id)>2 order by prod_id;求出prod_id大于1000且產(chǎn)品數(shù)量大于2的產(chǎn)品數(shù)量，并按prod_id排序，注意分組語句中對(duì)象要前后一致，如下劃線部分。7.使用子查詢：進(jìn)行過濾select?where?in(select?where?in(select?))、作為計(jì)算字段使用子查詢。8.聯(lián)接：join?on?（1）普通聯(lián)接

Select oi.order_num,oi.prod_id,p.prod_name,p.vend_id,v.vend_name from orderitems oi join products p on oi.prod_id=p.prod_id join vendors v on p.vend_id=v.vend_id where vend_name=’liyang’;可同時(shí)聯(lián)接多個(gè)表且可同時(shí)用于數(shù)據(jù)過濾，這種類型的聯(lián)接一般為內(nèi)部聯(lián)接。

（2）自聯(lián)接：一個(gè)表自己與自己聯(lián)接，注意判斷好各字段與前后兩個(gè)表的關(guān)系。（3）自然聯(lián)接：基本上簡歷的內(nèi)部聯(lián)接都是自然聯(lián)接。

（4）外部聯(lián)接：在關(guān)系表中沒有關(guān)聯(lián)的信息的行也能顯示出來的聯(lián)接，根據(jù)表在join字句的左邊還是右邊可分為左聯(lián)接與右聯(lián)接。（5）帶聚集函數(shù)的聯(lián)接

Select c.cust_id,count(o.order_num)num_ord from customers c join orders o on c.cust_id=o.cust_id order by c.cust_id;找出客戶對(duì)應(yīng)的訂單數(shù)。

9.組合查詢：連接多個(gè)（至少兩個(gè)）查詢語句，滿足其中一個(gè)查詢語句條件的結(jié)果都會(huì)顯示出來 union（不重復(fù)顯示）/union all（可重復(fù)顯示即全部顯示）Select vend_id,prod_id,prod_price from products where prod_price<=5 Union [all] Select vend_id,prod_id,prod_price from products where vend_id in(1001,1002)order by prod_id;注意每個(gè)查詢必須包含相同的列、表達(dá)式或者聚集函數(shù)，列的數(shù)據(jù)類型必須兼容，排序語句只能放在最后面，該排序語句對(duì)組合查詢語句中的所有select語句都適用。10.全文本搜索：只支持引擎為MyISAM的表，不支持引擎為InnoDB的表，可對(duì)搜索結(jié)果進(jìn)行智能排序后輸出，具有較高等級(jí)的行先返回。

Match(全文本搜索字段)against（’全文本搜索內(nèi)容’[with query expansion]）其中下劃線部分為拓展語句，使用該語句，除了可以返回符合所設(shè)置的“全文本搜索內(nèi)容”的數(shù)據(jù)結(jié)果，還可返回與“全文本搜索內(nèi)容”有較高相似度的數(shù)據(jù)結(jié)果。（1）啟用全文本搜索支持

Create table fs(id int not null primary key,c text,c1 text,fulltext(c,c1))engine=MyISAM;（2）進(jìn)行全文本搜索

Select note_text from productnotes where match(note_text)against(’liyang’ with query expansion);11.插入數(shù)據(jù)：insert into?{values|select}?

Insert into products(prod_id,prod_name,prod_price)values(1,’豆?jié){’,2),(3,’雞蛋’,1);可同時(shí)插入多行數(shù)據(jù)。

Insert into products(prod_id,prod_name,prod_price)select vend_id,vend_name, vend_price from vendors where vend_id<=10;12.更新數(shù)據(jù)：update [ignore]?set?,一般情況下，若更新的數(shù)據(jù)中有部分?jǐn)?shù)據(jù)出錯(cuò)，則全部數(shù)據(jù)返回到原來的數(shù)據(jù)，而ignore的作用在于即使更新的數(shù)據(jù)中出現(xiàn)錯(cuò)誤，只對(duì)出現(xiàn)錯(cuò)誤的數(shù)據(jù)返回到原來數(shù)據(jù)，而未出現(xiàn)錯(cuò)誤的數(shù)據(jù)返回更新后的結(jié)果實(shí)現(xiàn)更新。update products set prod_name='饅頭',prod_price=1 where prod_id=1;update customers set cust_city=concat(cust_city,’市’)| cust_city =replace(cust_city,’市’,’city’)where cust_id>1000;13.刪除數(shù)據(jù)：delete from? Delete from products where prod_id between 10 an 50;14.表的相關(guān)操作

（1）創(chuàng)建表：對(duì)表結(jié)構(gòu)進(jìn)行設(shè)置create table?

Create table products(prod_id int null auto_increment primary key,prod_name varchar(50),prod_price int,prod_city varchar(50)default ’廣州’)engine= InnoDB;每個(gè)字段名后需要設(shè)置數(shù)據(jù)類型，default為指定默認(rèn)值，只支持常量不支持函數(shù)，且只在插入數(shù)據(jù)時(shí)起作用而在更新數(shù)據(jù)時(shí)不起作用，InnoDB是一個(gè)可靠的事務(wù)處理引擎，但不支持全文本搜索。

（2）更新表：對(duì)表結(jié)構(gòu)進(jìn)行修改 alter table {add|drop}?

Alter table products add prod_city varchar(50)； Alter table products drop prod_price;（3）刪除表：一旦刪除，無法撤銷 drop table?

Drop table products;（4）重命名表：rename table?to?

Rename table products to new_products;15.索引的相關(guān)操作

（1）創(chuàng)建索引：常用于數(shù)據(jù)的快速檢索，MySQL中，常用索引在物理可分為：BTREE、HASH索引兩類；在具體用途上可分為：INDEX、UNIQUE、PRIMARY KEY、FOREIGN KEY、FULL TEXT、SPATIAL等。

1使用create index 語句創(chuàng)建索引，對(duì)已存在的表創(chuàng)建索引 ○Create [unique|fulltext|spatial] index index_name [using BTREE|HASH] on tbl_name(index_col_name[,index_col_name?]);Create unique index index_products on products(prod_name(2)desc,prod_price);2使用create table 語句創(chuàng)建索引，創(chuàng)建表的同時(shí)創(chuàng)建索引 ○Create table seller(seller_id int not null auto_increment,seller_name char(50),seller_adress char(50),seller_contact char(50),product_type int,sales int,primary key(seller_id,product_type),[unique|fulltext|spatial] index index_seller(sales));3使用alter table語句創(chuàng)建索引，修改表的同時(shí)添加索引 ○Alter table tbl_name add {[unique|fulltext|spatial] index index_tbl_name(字段名)|primary key（字段名）|foreign key（字段名）references elsetbl_name（相同字段名）};（2）查看索引:Show index from tbl_name [where expr];（3）刪除索引：drop index index_nameon tbl_name語句或alter table語句

Drop index index_name on tbl_name;Alter table tbl_name drop {[unique|fulltext|spatial] index index_tbl_name(字段名)|primary key（字段名）|foreign key（字段名）references elsetbl_name（相同字段名};（下劃線部分不確定）16.視圖的相關(guān)操作

視圖：虛擬的表，視圖本身不包含表中的列和數(shù)據(jù)，它包含只是一個(gè)SQL查詢,常用于 檢索數(shù)據(jù)。*視圖的作用與規(guī)則。（1）創(chuàng)建視圖：Create view view_name as select?[where?];Create view view_products as select prod_id,prod_name,prod_price,prod_num, prod_price*prod_num as prod_money from products where prod_id<=10 [with check option];--下劃線部分表示今后對(duì)該視圖數(shù)據(jù)的修改都必須符合prod_id<=10（2）查看視圖（用法同表）: select * from view_name;（3）刪除視圖：drop view view_name;17.完整性：實(shí)體完整性（主鍵與候選鍵）、參照完整性（主鍵與外鍵）、用戶定義的完整性（非空約束與check約束）。

18.創(chuàng)建主鍵約束：create table語句或alter table語句

Create table products(prod_id int not null auto_increment primary key,c int);作為列的主鍵約束；

Create table products(prod_id int not null auto_increment,c int,c1 int,primary key(prod_id));作為表的主鍵約束，且復(fù)合主鍵職能用這種形式創(chuàng)建 Alter table products add primary key(prod_id);備注：實(shí)體完整性通過主鍵約束與候選鍵約束來實(shí)現(xiàn)，候選鍵約束的創(chuàng)建類似主鍵約束的創(chuàng)建，實(shí)質(zhì)上同索引。

19.設(shè)置表外鍵：create table語句或alter table語句,外鍵中列的數(shù)目和數(shù)據(jù)類型必須與被參照表的主鍵中列的數(shù)目和對(duì)應(yīng)數(shù)據(jù)類型一致。

alter table tbl_name add [constraint fk_name] foreign key(?)references? Create table products(prod_id int not null auto_increment,c int,c1 int,foreign key(prod_id)references customers(prod_id));alter table products add constraint fk_products_cust foreign key(cust_id)references cust(cust_id);20.存儲(chǔ)過程：為了以后的使用而保存的一條或多條SQL語句的集合

--建立存儲(chǔ)過程：建立一個(gè)可通過輸入item_id，輸出對(duì)應(yīng)訂單總金額的存儲(chǔ)過程->Delimiter //--改變分割符為//->create procedure ordertotal(in o_id int,out o_total decimal(10,2))過程名字輸入?yún)?shù)及類型輸出參數(shù)及類型->begin->select sum(item_price*item_num)from orderitems where item_id=o_id into o_total;->if o_total is null then->select ’不存在該訂單號(hào)’;->end if;->end;->//--執(zhí)行存儲(chǔ)過程：當(dāng)item_id=200005時(shí)，得出對(duì)應(yīng)訂單總金額->delimiter;--將分割符改回分號(hào)->call ordertotal(200005,@total);--由于不存在輸出參數(shù)，故定義一個(gè)輸出變量，變量必須用@開頭->select @total;返回結(jié)果為149.87 備注：書本第十一章后的編程題，使用update語句，兩個(gè)參數(shù)類型都需要為in。--顯示存儲(chǔ)過程->Show create procedure ordertotal;--刪除存儲(chǔ)過程

->Drop procedure ordertotal;21.存儲(chǔ)函數(shù)

存儲(chǔ)函數(shù)與存儲(chǔ)過程的區(qū)別：.存儲(chǔ)函數(shù)不能擁有輸出參數(shù)；.存儲(chǔ)函數(shù)可直接調(diào)用，且不需使用call語句，而存儲(chǔ)過程的調(diào)用必須使用call語句；.存儲(chǔ)函數(shù)中必須包含一條return語句，而這條特殊的SQL語句不允許包含于存儲(chǔ)過程。--建立存儲(chǔ)函數(shù)：根據(jù)給定的cust_id返回客戶所在的州名（縮寫），若庫中無給定的cust_id，則返回“不存在該客戶”。->delimiter //->create function fn_search(c_id int)->returns varchar(50)--定義返回的數(shù)據(jù)類型，與函數(shù)部分中的數(shù)據(jù)類型需統(tǒng)一，如函數(shù)中的“不存在該客戶”為6個(gè)字符，如果這里設(shè)置為char(5)，則無法輸出該結(jié)果->deterministic– 表示對(duì)于相同的輸入值，返回值也相同->begin->declare state char(2);--聲明一個(gè)變量state，作為輸出的州變量->select cust_state from customers where cust_id=c_id into state;->if state is null then->return(select ’不存在該客戶’);--注意這里return不用加s->else->return(select state);->end if;->end;->//--執(zhí)行存儲(chǔ)函數(shù)

->select fn_search(10001);--刪除存儲(chǔ)函數(shù)

->drop function fn_search;--刪除前要確定該函數(shù)無依賴關(guān)系，即不存在其他存儲(chǔ)過程或存儲(chǔ)函數(shù)調(diào)用過該存儲(chǔ)函數(shù)。

22.觸發(fā)器：MySQL響應(yīng)insert、delete、update語句時(shí)自動(dòng)執(zhí)行的一條MySQL語句，創(chuàng)建觸發(fā)器時(shí)需要給出的4條信息：唯一的觸發(fā)器名、觸發(fā)器相關(guān)的表、觸發(fā)器應(yīng)該響應(yīng)的活動(dòng)（insert delete、update）、觸發(fā)器何時(shí)執(zhí)行（處理前或處理后）。

（1）insert觸發(fā)器：當(dāng)對(duì)表插入數(shù)據(jù)時(shí)起作用，含有一個(gè)虛擬表New，可訪問增加的行，只能用after--建立一個(gè)insert觸發(fā)器，用于記錄insert語句操作時(shí)的系統(tǒng)時(shí)間和插入的order_num->delimiter //->create trigger trg_order_insert after insert on orders for each row 觸發(fā)器 觸發(fā)器名 執(zhí)行時(shí)間 相關(guān)表->begin->insert into order_log(o_date,order_num)values(now(),new.order_num);--order_log是事先建立好的表，用于記錄insert語句操作時(shí)的系統(tǒng)時(shí)間和插入的order_num->end;->//--執(zhí)行insert觸發(fā)器->delimiter;->insert into orders(order_date,cust_id)values(’2010-9-15’,10001);--由于order_num是自動(dòng)遞增的，故在這里不作為插入對(duì)象（2）delete觸發(fā)器：當(dāng)對(duì)表刪除數(shù)據(jù)時(shí)起作用，含有一個(gè)虛擬表Old，可訪問被刪除的行，只能用after，創(chuàng)建方法與insert類似，區(qū)別在于delete和old--建立一個(gè)delete觸發(fā)器，用于記錄delete語句操作時(shí)的系統(tǒng)時(shí)間和刪除的order_num->delimiter //->create trigger trg_order_delete after delete on orders for each row 觸發(fā)器 觸發(fā)器名 執(zhí)行時(shí)間 相關(guān)表->begin->insert into order_log(o_date,order_num)values(now(),old.order_num);--order_log是事先建立好的表，用于記錄delete語句操作時(shí)的系統(tǒng)時(shí)間和刪除的order_num->end;->//--執(zhí)行delete觸發(fā)器->delimiter;->delete from orders where order_num=20010;（3）update觸發(fā)器：當(dāng)對(duì)表修改數(shù)據(jù)時(shí)起作用，同時(shí)含有new和old兩個(gè)虛擬表。結(jié)合New可訪問更新行的記錄；結(jié)合old可訪問更新前行的記錄，可用after，也可用before。1用after ○--建立一個(gè)update觸發(fā)器，用于記錄update語句操作時(shí)的系統(tǒng)時(shí)間和更新數(shù)據(jù)的order_num->delimiter //->create trigger trg_order_update after update on orders for each row 觸發(fā)器 觸發(fā)器名 執(zhí)行時(shí)間 相關(guān)表->begin->insert into order_log(o_date,order_num)values(now(),old.order_num);->end;->//--執(zhí)行update觸發(fā)器->delimiter;->update orders set order_date=’2015-9-18’ where cust_id=10001;2用before ○--建立一個(gè)update觸發(fā)器，如果更新后的prod_price大于原來的1.2倍，則用原來的1.2倍作為當(dāng)前價(jià)格->delimiter //->create trigger trg_order_update before update on orders for each row 觸發(fā)器 觸發(fā)器名 執(zhí)行時(shí)間 相關(guān)表->begin->if new.prod_price>old.prod_price*1.2 then->set new.prod_price=old.prod_price*1.2;->end if;->end;->//（4）刪除觸發(fā)器：drop trigger trg_name;23.事件：臨時(shí)觸發(fā)器，要使用事件調(diào)度器，必須開啟“event_scheduler”.查看：show variables like ’event_scheduler’;.開啟：set global event_scheduler=1;（1）創(chuàng)建事件

CREATE EVENT EVENT_NAME ON SCHEDULE schedule DO event_body;其中schedule的語法格式為

AT timestamp [+INTERVAL interval]?|every interval--指定事件執(zhí)行的時(shí)間，可以為某時(shí)刻點(diǎn)即timestamp，或某時(shí)刻點(diǎn)開始的interval時(shí)間后，或者為每隔interval時(shí)間執(zhí)行一次

[starts timestamp [+INTERVAL interval]]--設(shè)置事件開始執(zhí)行的時(shí)間 [ends timestamp [+INTERVAL interval]]--設(shè)置事件終止執(zhí)行的時(shí)間

--建立一個(gè)事件，用于每個(gè)月向customers表中插入一條數(shù)據(jù)“l(fā)iyang、廣州”，該事件從下個(gè)月開始并于2015-12-31結(jié)束->delimiter //->create event event_insert on schedule every 1 month->starts curdate()+interval 1 month->ends ’2015-12-31’->do->begin->if year(curdate())<2015 then->insert into customers(cust_name,cust_adress)values(’liyang’,’廣州’);->end if;->end;->//（2）修改事件，用于修改時(shí)間的狀態(tài):alter event event_name{enable|disable};（3）刪除事件:drop event event_name;24.管理實(shí)務(wù)處理：start transaction?

實(shí)務(wù)處理的術(shù)語：

（1）實(shí)務(wù)（transaction）：一組SQL語句;（2）回退(rollback):撤銷指定SQL語句的過程;（3）提交(commit)：指定未存儲(chǔ)的SQL語句結(jié)果寫入到數(shù)據(jù)庫表里，提交后無法回退;（4）保留點(diǎn)(savepoint)：實(shí)務(wù)處理中設(shè)置的臨時(shí)占位符。

25.安全管理（用戶創(chuàng)建修改與刪除以及用戶權(quán)限的查看設(shè)置與撤銷）（1）創(chuàng)建用戶賬號(hào)：create user ben identified by ’ben’;（2）修改用戶賬號(hào)：update mysql.user set user=’new_ben’ where user=’ben’;--從mysql數(shù)據(jù)庫中的用戶表user進(jìn)行修改（3）查看訪問權(quán)限：show grants for new_ben;（4）設(shè)置訪問權(quán)限:grant?to?.grant {all|select,update,delete,insert}on {*.*|crashcourse.*|crashcourse.cus tomers} to new_ben;.grant select(cust_id,cust_name)on crashcourse.customers to new_ben;--可針對(duì){整個(gè)服務(wù)器|整個(gè)數(shù)據(jù)庫|數(shù)據(jù)庫中某個(gè)表|數(shù)據(jù)庫中某個(gè)表的某些字段}，對(duì)用戶同時(shí)設(shè)置全部或一種或多種權(quán)限

（5）撤銷訪問權(quán)限：revoke?from?,用法與grant?to?類似（6）更改口令（密碼）

Set password for new_ben=password(’new_ben’);（7）刪除用戶：drop user new_ben;26.數(shù)據(jù)庫備份與還原.使用SQL語句

backup table tbl_name to?/restore table tbl_name from?（只用于MyISAM表）select?intooutfile?/load data?infile?into table tlb_name.使用命令行實(shí)用程序:mysqlhotcopy（只用于MyISAM表）或mysqldump/mysql（1）使用select?intooutfile?/load data?infile?into table tlb_name.備份數(shù)據(jù)：

Select * from mysql.products into outfile ’d:products.txt’ [Fields terminated by ’,’ optionally enclosed by ’”’

lines terminated by ’nr’;--定義字段間的分割符、字符型數(shù)據(jù)的存放形式、行與行之間的分割符.恢復(fù)數(shù)據(jù)

Load data infile ’d:products.txt’into table customers.copy [Fields terminated by ’,’ optionally enclosed by ’”’

lines terminated by ’nr’;--必須與備份時(shí)一致（2）使用命令行實(shí)用程序mysqldump/mysql（文本形式）

進(jìn)入cmd運(yùn)行界面(mysqldump—help 可用于獲取mysqldump的選項(xiàng)表及更多幫助信息).備份整個(gè)數(shù)據(jù)庫服務(wù)器、或整個(gè)數(shù)據(jù)庫或數(shù)據(jù)庫中某個(gè)表

Mysqldump –u root –proot –P 3306 –h localhost {all-databases|mysql_test [products]}>d:data.sql.恢復(fù)數(shù)據(jù)

Mysql –u root –proot –P 3306 –h localhost {all-databases|mysql_test [products]}

（1）analyze table tbl_name;更新表的索引散列程度，檢查表鍵是否正確（2）check table tbl_name;檢查一個(gè)或多個(gè)表是否有錯(cuò)誤

（3）checksum table tbl_name;對(duì)數(shù)據(jù)庫中的表進(jìn)行校驗(yàn)，保證數(shù)據(jù)的一致性

（4）optimize table tbl_name;利用表中未使用的空間并整理數(shù)據(jù)文件碎片，保證數(shù)據(jù)讀取效率

（5）repair table tbl_name;修復(fù)一個(gè)或多個(gè)可能被損害的MyISAM表 28.二進(jìn)制日志文件的使用：mysqlbinlog

29.使用PHP進(jìn)行MySQL數(shù)據(jù)庫編程 編程步驟：

.首先建立與MySQL數(shù)據(jù)庫服務(wù)器的連接；.然后選擇要對(duì)其進(jìn)行操作的數(shù)據(jù)庫；

.再執(zhí)行相應(yīng)的數(shù)據(jù)庫操作，包括對(duì)數(shù)據(jù)的添加、刪除、修改和查詢等；.最后關(guān)閉與MySQL數(shù)據(jù)庫服務(wù)器的連接。（1）數(shù)據(jù)庫服務(wù)器連接、選擇數(shù)據(jù)庫

.使用mysql_connect（）建立非持久連接 ”;die();} echo “數(shù)據(jù)庫服務(wù)器連接成功！
”;?> //將connect.php部署在已開啟的WAMP平臺(tái)環(huán)境中，并在瀏覽器地址中輸入“http://localhost/connect.php”

.使用mysql_pconnect（）建立持久連接 ”;echo “用戶名：root
”;echo “使用函數(shù)mysql_pconnect（）永久連接數(shù)據(jù)庫。
”;?>

.使用mysql_select_db(databases[,connection])選擇數(shù)據(jù)庫 ”;die();} mysql_select_db(“mysql_test”,$con);if(mysql_errno()){ echo “數(shù)據(jù)庫選擇失敗！
”;die();} echo “數(shù)據(jù)庫選擇成功！
” ?>（2）數(shù)據(jù)的添加、更新和刪除操作,mysql_query(SQL語句[,connection])，insert、update、delete語句可置于函數(shù)mysql_query（）中從而實(shí)現(xiàn)數(shù)據(jù)的添加、更新和刪除操作.數(shù)據(jù)的添加

/*向數(shù)據(jù)庫mysql_test中的表customers添加一個(gè)名為“李中華”的客戶的全部信息*/ ”);Mysql_select_db(“mysql_test”,$con)or die(“數(shù)據(jù)庫選擇失敗！
”);Mysql_query(“set names ’gbk’”);//設(shè)置中文字符集

$sql=“insert into customers(’cust_id’,’cust_name’,’cust_sex’)”;$sql=$sql.“values(null,’李中華’,’M’)”;if(mysql_query($sql,$con))echo “客戶信息添加成功！
”;else echo “客戶信息添加失敗！
”;?>

.數(shù)據(jù)的更新

/*將數(shù)據(jù)庫mysql_test的表customers中的一個(gè)名為“李中華”的客戶的地址修改為“廣州市”*/ ”);Mysql_select_db(“mysql_test”,$con)or die(“數(shù)據(jù)庫選擇失敗！
”);Mysql_query(“set names ’gbk’”);$sql=“update customers set cust_address=’廣州市’”;$sql=$sql.“where cust_name=’李中華’”;if(mysql_query($sql,$con))echo “客戶地址修改成功！
”;else echo “客戶地址修改失敗!
”;?>

.數(shù)據(jù)的刪除

/*將數(shù)據(jù)庫mysql_test的表customers中一個(gè)名為“李中華”的客戶信息刪除*/ ”);Mysql_select_db(“mysql_test”,$con)or die(“數(shù)據(jù)庫選擇失敗！
”);Mysql_query(“set names ’gbk’”);$sql=“delete from customers”;$sql=$sql.“where cust_name=’李中華’”;if(mysql_query($sql,$con))echo(“客戶信息刪除成功！
”);else echo(“客戶信息刪除失敗！
”);?>

（3）數(shù)據(jù)庫的查詢

.使用mysql_fetch_array(data[,array_type])讀取結(jié)果集中的記錄

/*在數(shù)據(jù)庫mysql_test的表customers中查詢cust_id為916的客戶的姓名*/ ”);Mysql_select_db(“mysql_test”,$con)or die(“數(shù)據(jù)庫選擇失敗！
”);Mysql_query(“set names ’gbk’”);$sql=“select cust_name from customers”;$sql=$sql.“where cust_id=916”;$result=mysql_query($sql,$con);if($result){ echo “客戶查詢成功！
”;$array=mysql_fetch_array($result,MYSQL_NUM);if($array){ echo “讀取到客戶信息！
”;echo “所要查詢客戶的姓名為：”.$array[0].“
”;} else echo “未讀取到客戶信息！
”;} else echo “客戶查詢失敗！
”;?>

.使用mysql_num_rows(data)讀取結(jié)果集中的記錄數(shù)

/*在數(shù)據(jù)庫mysql_test的表customers中查詢女性客戶的人數(shù)*/ ”);Mysql_select_db(“mysql_test”,$con)or die(“數(shù)據(jù)庫選擇失敗！
”);Mysql_query(“set names ’gbk’”);$sql=“select * from customers”;$sql=$sql.“where cust_sex=’F’”;$result=mysql_query($sql,$con);if($result){ echo “查詢成功！
”;$num=mysql_num_rows($result);//如果結(jié)果為空，則為0行 echo “所要查詢的女性客戶人數(shù)為：”.$num.“位
”;} else echo “查詢失敗！
”;?>

第五篇：MySQL數(shù)據(jù)庫連接超時(shí)(Wait_timeout)問題總結(jié)

當(dāng)應(yīng)用程序和數(shù)據(jù)庫建立連接時(shí)，如果超過了8個(gè)小時(shí)，應(yīng)用程序不去訪問數(shù)據(jù)庫，數(shù)據(jù)庫就會(huì)出現(xiàn)斷掉連接的現(xiàn)象。這時(shí)再次訪問就會(huì)拋出異常.一般的解決方法大多是在數(shù)據(jù)庫連接字符串中增加“autoReconnect=true ”選項(xiàng)。但是這只對(duì)mysql4以前的版本有效。在最新的mysql中是無效的。其實(shí)要解決這個(gè)問題也有一個(gè)簡單的方法，就是修改mysql的啟動(dòng)參數(shù)。缺省情況下mysql的timeout時(shí)間是28800秒，正好是8小時(shí)，增加一個(gè)0就可以了。決定從根源入手，設(shè)置mysql的wait_timeout為31536000（一年），再來試試。set-variable=wait_timeout=31536000 set-variable=interactive_timeout=31536000 問題得到了解決 想了深入解一下mysql的工作原理

百度了一下Google發(fā)現(xiàn)很多人都出現(xiàn)過這種問題，大多是配置hibernate時(shí)候出的問題，可惜我的項(xiàng)目中沒有使用到hibernate只是簡單的自己配了一個(gè)連接池，所以綜合了問題的關(guān)鍵所在改了一下數(shù)據(jù)庫配置，在這里暫且記錄一下，以后備用。

Mysql服務(wù)器默認(rèn)的“wait_timeout”是8小時(shí)，也就是說一個(gè)connection空閑超過8個(gè)小時(shí)，Mysql將自動(dòng)斷開該 connection。這就是問題的所在。

最近碰到了這個(gè)問題，檢查后發(fā)現(xiàn)數(shù)據(jù)庫連接池中保存的連接超時(shí)后失效了，下面是官方的解釋 mysql gone-away

從Mysql 5.x的某個(gè)版本之后，MySQL的自動(dòng)關(guān)閉空閑連接的特性被修改了，假如一個(gè)連接空閑到超時(shí)時(shí)間(默認(rèn)28000秒8小時(shí))，再次發(fā)起的Reconnect重新連接請(qǐng)求不會(huì)被接受，需要重新建立新連接，這就導(dǎo)致了SER的重連機(jī)制不能正常工作：SER只會(huì)在需要操作數(shù)據(jù)庫時(shí)去使用同一個(gè)連接接口，斷開了則發(fā)起重新連接請(qǐng)求，而且這個(gè)問題短期內(nèi)SER也不能夠解決。

下文中將具體講述處理的方法：

1.使用Mysql 4.0或4.1版本，如果沒有用到Mysql 5的一些新特性比如存儲(chǔ)過程觸發(fā)器之類。

2.定時(shí)重啟Mysql服務(wù)器或Ser(由于本問題可能同樣會(huì)影響到其它一些需要Mysql支持的服務(wù)器程序，所以重啟Mysql服務(wù)器為好，但需要檢測(cè)Mysql服務(wù)器不被使用的一個(gè)時(shí)間重啟比較難確定)

3.設(shè)置my.cnf，有mysqld字段內(nèi)增加參數(shù)：

[mysqld]port = 3306socket = /tmp/mysql.sockwait_timeout= 500000interactive_timeout = 500000(500000秒約五六天的超時(shí)時(shí)間，可根據(jù)實(shí)際需要選擇一個(gè)數(shù)據(jù)庫可能空閑的最長時(shí)間稍大的時(shí)間值。)重啟Mysqld應(yīng)用即可，也可以在執(zhí)行mysqld時(shí)加-o wait_timeout=500000參數(shù)同樣效果。

在MySQL客戶端show variable時(shí)應(yīng)該可以看到最后一條從默認(rèn)的wait_time=28000變成500000了。

(假如重啟Mysqld不生效，可以重啟機(jī)子)