2016年數據庫設計技巧大全

　　下文所講解的數據庫技巧是許多人在大量的數據庫分析與設計實(shí)踐中，逐步總結出來(lái)的。對于這些經(jīng)驗的運用，讀者不能生幫硬套，死記硬背，而要消化理解，實(shí)事求是，靈活掌握。并逐步做到：在應用中發(fā)展，在發(fā)展中應用。和yjbys一起來(lái)學(xué)習吧!

　　常用數據操作語(yǔ)言DML筆記(select insert update delete)

　　select 語(yǔ)句

　　高級的查詢(xún)功能,見(jiàn)下面的詳細內容

1 2 3 4 5

select 列名1,列名2 ... from 表名1,表名2... [where 條件] like [group by...] [having ...] [order by...] 以特定的順序顯示 例:order by name asc;以名字顯示,為降序排列

　　insert 語(yǔ)句

　　MySQL 當記錄不存在時(shí)插入 insert if not exists.在 MySQL 中,插入(insert)一條記錄很簡(jiǎn)單,但是一些特殊應用,在插入記錄前,需要檢查這條記錄是否已經(jīng)存在,只有當記錄不存在時(shí)才執行插入操作,本文介紹的就是這個(gè)問(wèn)題的解決方案.

1	insert into 表名(列名1,列名2,... )values(值1,值2,...)

　　update 語(yǔ)句

1	update 表名 set 列名1=值1,列名2=值2 ... [where 條件]

　　delete 語(yǔ)句

1	delete from 表名 [where 條件] 注:條件時(shí)會(huì )刪除整個(gè)表名

　　truncate 語(yǔ)句

1 2	truncate tables 表名 # 注:相當刪除整個(gè)表名,在重建.非�？�

　　從一個(gè)文件加載數據到 MYSQL

1	load data infile '文件' into tables 表名 [fields terminated by '字符']

　　導出 MYSQL 數據成一個(gè)文件

1	select * from 表名 into outfile '文件'

　　DDL數據定義語(yǔ)言(vreate tabe,drop table,altre table)

　　MYSQL 的字符類(lèi)型要注意 CHAR VARCHAR TEXT 不分大小寫(xiě),只能存字符 ,BLOB可以存二進(jìn)制的內容,如果聲音,文件.

　　CHAR主要是定長(cháng),VARCHAR是變長(cháng),所以VARCHAR更加節約空間.

　　查看表結構字段

1	desc tablesname;

　　添加新的表字段

1 2 3 4

alter table test add name varchar(20); #添加一個(gè)name的字段 alter table test add f_name varchar(20) after id; #在id之后添加一個(gè)name的字段 alter table test add sid int first; #在最前面加入一個(gè)字段 alter table test add primary key(id); #添加一個(gè)primary主鍵

　　刪除表中的字段

1 2	alter table test drop name; #刪除 alter table test add primary key; #刪除主鍵

　　設置表中字段的默認值

1	alter table test alter name set default '不知道';

　　修改字段名

1	alter table test change name l_anme varchar(10); #修改字段名,數據類(lèi)型要寫(xiě)上

　　數據庫插入

1 2 3

insert #表名 values insert into tmp values(default,'kate',0,20);#可以使用default. insert into tmp(name,sex,age) values('katess',0,20);

　　表的導入導出

1	insert tmp2 select name,sex from tmp where id<4; #可以給select 中得到的內容放到tmp2的新表中

　　更新 MYSQL 表中的字段

1 2	UPDATE <表名> SET <列名 = 更新值> [WHERE <更新條件>] update tmp set address='北京' where id between 4 and 5

　　刪除表中特定的內容

1 2	DELETE FROM tmp WHERE id >5 #刪除id大于5的內容 DELETE FROM ss1,ss2 using ss1,ss2 where ss1.id=ss2.id and ss1.name='alex' #二張表ss1和ss2相同的id中內容有alex的內容刪除

　　高級查詢(xún)

　　常用查詢(xún)函數

　　concat(字符連接)

　　distinct(去掉重復數據)

　　as(別名)

　　limit 3,2 (控制顯示數,如前現示從第三個(gè)起,拿二個(gè)數據)

　　集函數

　　count(統計)

　　sun

　　age

　　查找(條件內容查找)

1 2	select 'abc' like 'abc%'; # %任意字符 _單個(gè)字符最少要出現一次

　　MYSQL 中使用正則來(lái)查找數據

1	select 'abc' regexp '^a.*c$';

　　排序查詢(xún)結果

　　使用排序功能,和使用別名功能

1 2 3

SELECT * FROM student ORDER BY sage DESC ; #降排序order by要放在最后< SELECT * FROM student ORDER BY sdept in('信息系','美術(shù)系') #同時(shí)二個(gè)條件 SELECT sno AS '學(xué)號', sname AS '姓名' FROM student;

　　分組

1 2	select cid,avg(grad) from sc group by cid; # 在分組中 group by要放在最后,要是要加條件的話(huà),集函數要使用having來(lái)?yè)Qwhere.放在group by后

　　多表查詢(xún)

1 2 3 4

select s.sname,g.grade form sc as g, student as s where s.sname='扶%' and s.sno=g.sid; select s.sname,g.grade from sc as g inner join student as s on s.sno=g.sid; # 內連接,同連接顯示相同的內容,要加inner給whereis 改成on # 如果有的數據沒(méi)有,可以換left,和right以第一個(gè)表為基礎來(lái)排數據,inner join是二個(gè)表都有的內容.

　　子查詢(xún)

1 2	select sname,sage from student where sno not in (select sid from sc) ; select sname from student where sno in (select)

　　在 MYSQL 中查詢(xún),要先轉義

　　#name=mysql_escape_string($name); mysql_escape_string

　　FAQ:

　　1. 查看和修改設置 MYSQL 默認編碼

1 2 3

SHOW CHARACTER SET;查看支持的所有字 show variables like 'character_set_%'; SHOW VARIABLES LIKE 'collation_%';

　　讓 MYSQL 重起也可以使用utf8

1 2 3 4

[mysqld] default-character-set=utf [mysql] default-character-set=utf8

　　2. 怎么樣進(jìn)行 MYSQL 備份

　　MYSQL 的備份可用命令mysqldump ,使用方法很簡(jiǎn)單,

1	$ mysqldump -u 用戶(hù)名 -p (密碼) -h 主機名 數據庫名 >路徑/備份名.bak

　　同時(shí)也可以是用mysqldump備份數據結構(tablename.sql)和數據(tablename.txt)

1	$ mysqldump -u 用戶(hù)名 -p (密碼) -h 主機名 數據庫名 tablename1 tablename2 > back.sql

　　mysqldump -u 用戶(hù)名 -p (密碼) -h 主機名 數據庫名 –tab 路徑 –opt 數據庫名.

　　EXA:

1	$ mysqldump -u xxxx -p xxxxt databases > ./news.sql

　　3. 改變mysql管理員的密碼

　　方法1: 在/usr/local/mysql/bin/下：

1	$ mysqladmin -u root password 'new_password'

　　一般安裝時(shí)用此方法設置.

　　方法2:

　　在mysql狀態(tài)下：

1 2	mysql>UPDATE user SET password=PASSWORD('new_password') WHERE user='root'; mysql>FLUSH PRIVILEGES;

　　Method 3:

　　mysql>SET PASSWORD FOR root=PASSWORD('new_password');

　　4. 什么情況下會(huì )導致 MYSQL 數據庫損壞和怎么修復 MYSQL

　　mysql 正在運行的時(shí)候,服務(wù)器突然斷電或者直接按reset鍵重啟,硬盤(pán)空間不夠,導致數據寫(xiě)不進(jìn)去,也很有可能導致數據表?yè)p壞,物理硬盤(pán)有損壞.主要是這幾個(gè)原因,mysql修復方法如下

　　可以把mysql停掉,用mysql的命令myisamchk來(lái)修復,這種修復方法是最好的.

　　具體命令如下：

1 2 3

$ myisamchk -r database/*.MYI $ myisamchk -o database/*.MYI $ myisamchk -f database/*.MYI

　　參數 -r, -o , -f 是遞進(jìn)關(guān)系,一般首先用-r修復,然后-o , -f

　　5. mysql的導出導入

1 2	$ mysqldump -uroot -p mysql >pcti15.sql $ mysql -uroot -p pcti >pcti15.sql

　　使用source命令,后面參數為腳本文件(如這里用到的.sql)

1	mysql>source d:\backup_db.sql

　　6. 設置進(jìn)入時(shí)的默認編碼

1	mysql -uroot -p --default-character-set=utf8

　　問(wèn)題：我創(chuàng )建了一個(gè)表來(lái)存放客戶(hù)信息,我知道可以用 insert 語(yǔ)句插入信息到表中,但是怎么樣才能保證不會(huì )插入重復的記錄呢?

　　答案：可以通過(guò)使用 EXISTS 條件句防止插入重復記錄.

　　示例一：插入多條記錄

　　假設有一個(gè)主鍵為 client_id 的 clients 表,可以使用下面的語(yǔ)句：

　　Code:

　　INSERT INTO clients

　　(client_id, client_name, client_type)

　　SELECT supplier_id, supplier_name, ‘advertising’

　　FROM suppliers

　　WHERE not exists (select * from clients

　　where clients.client_id = suppliers.supplier_id);

　　示例一：插入單條記錄

　　Code:

　　INSERT INTO clients

　　(client_id, client_name, client_type)

　　SELECT 10345, ‘IBM’, ‘advertising’

　　FROM dual

　　WHERE not exists (select * from clients

　　where clients.client_id = 10345);

　　使用 dual 做表名可以讓你在 select 語(yǔ)句后面直接跟上要插入字段的值,即使這些值還不存在當前表中.

　　MySQL 連接的狀態(tài)信息

　　我們常常需要看一些連接的信息,如下可以顯示相關(guān)的信息:

01 02 03 04 05 06 07 08 09 10 11 12

mysql> show status like '%onnect%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Aborted_connects | 8960 | | Connections | 31530 | | Max_used_connections | 111 | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_finished_connects | 0 | | Threads_connected | 73 | +--------------------------+-------+

　　Aborted_connects 嘗試已經(jīng)失敗的MySQL服務(wù)器的連接的次數。

　　Connections 試圖連接MySQL服務(wù)器的次數。

　　Threads_connected 當前打開(kāi)的連接的數量.

　　設置的連接數可以通過(guò)下面這個(gè)查看

1	show variables like 'max_connections';

　　如果想修改的話(huà),修改 /etc/my.cnf 找到max_connections一行，修改為(如果沒(méi)有，則自己添加)

1	max_connections = 1000

　　臨時(shí)修改此參數的值, 注意大小寫(xiě)

1	set GLOBAL max_connections=1000;

　　查詢(xún)表的格式

1	SHOW TABLE STATUS WHERE ROW_FORMAT LIKE 'Compact'

　　查詢(xún) binlog 轉換成可讀

　　mysqlbinlog mysql-bin.000002 -vvvv –base64-output=DECODE-ROWS

【數據庫設計技巧】相關(guān)文章：

2016最數據庫設計技巧03-29

微軟認證考試技巧：數據庫03-21

Oracle 數據庫查詢(xún)小技巧03-21

Oracle數據庫臨時(shí)表管理技巧03-23

陽(yáng)臺設計的技巧03-06

EDA設計技巧03-19

廣告設計的設計技巧03-13

海報設計文字設計技巧03-12

如何設計窗簾-窗簾設計技巧03-06