數(shù)據(jù)庫設(shè)計技巧

時間：2024-04-20 08:05:07 ACCP培訓(xùn)

2016年數(shù)據(jù)庫設(shè)計技巧大全

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

　　常用數(shù)據(jù)操作語言DML筆記(select insert update delete)

　　select 語句

　　高級的查詢功能,見下面的詳細內(nèi)容

select 列名1,列名2 ... from 表名1,表名2...

[where 條件] like

[group by...]

[having ...]

[order by...] 以特定的順序顯示例:order by name asc;以名字顯示,為降序排列

　　insert 語句

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

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

　　update 語句

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

　　delete 語句

1	`delete` `from` `表名 [where` `條件] 注:條件時會刪除整個表名`

　　truncate 語句

1 2	`truncate` `tables 表名` `# 注:相當(dāng)刪除整個表名,在重建.非常快`

　　從一個文件加載數(shù)據(jù)到 MYSQL

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

　　導(dǎo)出 MYSQL 數(shù)據(jù)成一個文件

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

　　DDL數(shù)據(jù)定義語言(vreate tabe,drop table,altre table)

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

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

　　查看表結(jié)構(gòu)字段

1	`desc` `tablesname;`

　　添加新的表字段

alter table test add name varchar(20); #添加一個name的字段

alter table test add f_name varchar(20) after id; #在id之后添加一個name的字段

alter table test add sid int first; #在最前面加入一個字段

alter table test add primary key(id); #添加一個primary主鍵

　　刪除表中的字段

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

　　設(shè)置表中字段的默認值

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

　　修改字段名

1	`alter` `table` `test change` `name` `l_anme` `varchar(10); #修改字段名,數(shù)據(jù)類型要寫上`

　　數(shù)據(jù)庫插入

insert #表名 values

insert into tmp values(default,'kate',0,20);#可以使用default.

insert into tmp(name,sex,age) values('katess',0,20);

　　表的導(dǎo)入導(dǎo)出

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

　　更新 MYSQL 表中的字段

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

　　刪除表中特定的內(nèi)容

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

　　高級查詢

　　常用查詢函數(shù)

　　concat(字符連接)

　　distinct(去掉重復(fù)數(shù)據(jù))

　　as(別名)

　　limit 3,2 (控制顯示數(shù),如前現(xiàn)示從第三個起,拿二個數(shù)據(jù))

　　集函數(shù)

　　count(統(tǒng)計)

　　sun

　　age

　　查找(條件內(nèi)容查找)

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

　　MYSQL 中使用正則來查找數(shù)據(jù)

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

　　排序查詢結(jié)果

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

SELECT * FROM student ORDER BY sage DESC ; #降排序order by要放在最后<

SELECT * FROM student ORDER BY sdept in('信息系','美術(shù)系') #同時二個條件

SELECT sno AS '學(xué)號', sname AS '姓名' FROM student;

　　分組

1 2	`select` `cid,avg(grad)` `from` `sc` `group` `by` `cid;` `# 在分組中` `group` `by要放在最后,要是要加條件的話,集函數(shù)要使用having來換where.放在group` `by后`

　　多表查詢

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;

# 內(nèi)連接,同連接顯示相同的內(nèi)容,要加inner給whereis 改成on

# 如果有的數(shù)據(jù)沒有,可以換left,和right以第一個表為基礎(chǔ)來排數(shù)據(jù),inner join是二個表都有的內(nèi)容.

　　子查詢

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

　　在 MYSQL 中查詢,要先轉(zhuǎn)義

　　#name=mysql_escape_string($name); mysql_escape_string

　　FAQ:

　　1. 查看和修改設(shè)置 MYSQL 默認編碼

SHOW CHARACTER SET;查看支持的所有字

show variables like 'character_set_%';

SHOW VARIABLES LIKE 'collation_%';

　　讓 MYSQL 重起也可以使用utf8

[mysqld]

default-character-set=utf

[mysql]

default-character-set=utf8

　　2. 怎么樣進行 MYSQL 備份

　　MYSQL 的備份可用命令mysqldump ,使用方法很簡單,

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

　　同時也可以是用mysqldump備份數(shù)據(jù)結(jié)構(gòu)(tablename.sql)和數(shù)據(jù)(tablename.txt)

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

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

　　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. 什么情況下會導(dǎo)致 MYSQL 數(shù)據(jù)庫損壞和怎么修復(fù) MYSQL

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

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

　　具體命令如下：

$ myisamchk -r database/*.MYI

$ myisamchk -o database/*.MYI

$ myisamchk -f database/*.MYI

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

　　5. mysql的導(dǎo)出導(dǎo)入

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

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

1	`mysql>source d:\backup_db.sql`

　　6. 設(shè)置進入時的默認編碼

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

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

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

　　示例一：插入多條記錄

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

　　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 語句后面直接跟上要插入字段的值,即使這些值還不存在當(dāng)前表中.

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

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

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ù)器的連接的次數(shù)。

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

　　Threads_connected 當(dāng)前打開的連接的數(shù)量.

　　設(shè)置的連接數(shù)可以通過下面這個查看

1	`show variables` `like` `'max_connections';`

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

1	`max_connections = 1000`

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

1	`set GLOBAL max_connections=1000;`

　　查詢表的格式

1	`SHOW` `TABLE` `STATUS` `WHERE` `ROW_FORMAT` `LIKE` `'Compact'`

　　查詢 binlog 轉(zhuǎn)換成可讀

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

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

2016最數(shù)據(jù)庫設(shè)計技巧03-29

微軟認證考試技巧：數(shù)據(jù)庫03-21

Oracle 數(shù)據(jù)庫查詢小技巧03-21

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

陽臺設(shè)計的技巧03-06

EDA設(shè)計技巧03-19

廣告設(shè)計的設(shè)計技巧03-13

海報設(shè)計文字設(shè)計技巧03-12

如何設(shè)計窗簾-窗簾設(shè)計技巧03-06