- 最數(shù)據(jù)庫設(shè)計(jì)技巧 推薦度：
- 相關(guān)推薦
2016年數(shù)據(jù)庫設(shè)計(jì)技巧大全
下文所講解的數(shù)據(jù)庫技巧是許多人在大量的數(shù)據(jù)庫分析與設(shè)計(jì)實(shí)踐中，逐步總結(jié)出來的。對于這些經(jīng)驗(yàn)的運(yùn)用，讀者不能生幫硬套，死記硬背，而要消化理解，實(shí)事求是，靈活掌握。并逐步做到：在應(yīng)用中發(fā)展，在發(fā)展中應(yīng)用。和yjbys一起來學(xué)習(xí)吧!
常用數(shù)據(jù)操作語言DML筆記(select insert update delete)
select 語句
高級的查詢功能,見下面的詳細(xì)內(nèi)容
|
1
2
3
4
5
|
select 列名1,列名2 ... from 表名1,表名2...[where 條件] like[group by...][having ...][order by...] 以特定的順序顯示 例:order by name asc;以名字顯示,為降序排列 |
insert 語句
MySQL 當(dāng)記錄不存在時(shí)插入 insert if not exists.在 MySQL 中,插入(insert)一條記錄很簡單,但是一些特殊應(yīng)用,在插入記錄前,需要檢查這條記錄是否已經(jīng)存在,只有當(dāng)記錄不存在時(shí)才執(zhí)行插入操作,本文介紹的就是這個(gè)問題的解決方案.
|
1
|
insert into 表名(列名1,列名2,... )values(值1,值2,...) |
update 語句
|
1
|
update 表名 set 列名1=值1,列名2=值2 ... [where 條件] |
delete 語句
|
1
|
delete from 表名 [where 條件] 注:條件時(shí)會刪除整個(gè)表名 |
truncate 語句
|
1
2
|
truncate tables 表名# 注:相當(dāng)刪除整個(gè)表名,在重建.非常快 |
從一個(gè)文件加載數(shù)據(jù)到 MYSQL
|
1
|
load data infile '文件' into tables 表名 [fields terminated by '字符'] |
導(dǎo)出 MYSQL 數(shù)據(jù)成一個(gè)文件
|
1
|
select * from 表名 into outfile '文件' |
DDL數(shù)據(jù)定義語言(vreate tabe,drop table,altre table)
MYSQL 的字符類型要注意 CHAR VARCHAR TEXT 不分大小寫,只能存字符 ,BLOB可以存二進(jìn)制的內(nèi)容,如果聲音,文件.
CHAR主要是定長,VARCHAR是變長,所以VARCHAR更加節(jié)約空間.
查看表結(jié)構(gòu)字段
|
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; #刪除主鍵 |
設(shè)置表中字段的默認(rèn)值
|
1
|
alter table test alter name set default '不知道'; |
修改字段名
|
1
|
alter table test change name l_anme varchar(10); #修改字段名,數(shù)據(jù)類型要寫上 |
數(shù)據(jù)庫插入
|
1
2
3
|
insert #表名 valuesinsert 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)示從第三個(gè)起,拿二個(gè)數(shù)據(jù))
集函數(shù)
count(統(tǒng)計(jì))
sun
age
查找(條件內(nèi)容查找)
|
1
2
|
select 'abc' like 'abc%';# %任意字符 _單個(gè)字符最少要出現(xiàn)一次 |
MYSQL 中使用正則來查找數(shù)據(jù)
|
1
|
select 'abc' regexp '^a.*c$'; |
排序查詢結(jié)果
使用排序功能,和使用別名功能
|
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要放在最后,要是要加條件的話,集函數(shù)要使用having來換where.放在group by后 |
多表查詢
|
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;# 內(nèi)連接,同連接顯示相同的內(nèi)容,要加inner給whereis 改成on# 如果有的數(shù)據(jù)沒有,可以換left,和right以第一個(gè)表為基礎(chǔ)來排數(shù)據(jù),inner join是二個(gè)表都有的內(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 默認(rèn)編碼
|
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 ,使用方法很簡單,
|
1
|
$ mysqldump -u 用戶名 -p (密碼) -h 主機(jī)名 數(shù)據(jù)庫名 >路徑/備份名.bak |
同時(shí)也可以是用mysqldump備份數(shù)據(jù)結(jié)構(gòu)(tablename.sql)和數(shù)據(jù)(tablename.txt)
|
1
|
$ mysqldump -u 用戶名 -p (密碼) -h 主機(jī)名 數(shù)據(jù)庫名 tablename1 tablename2 > back.sql |
mysqldump -u 用戶名 -p (密碼) -h 主機(jī)名 數(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í)用此方法設(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 正在運(yùn)行的時(shí)候,服務(wù)器突然斷電或者直接按reset鍵重啟,硬盤空間不夠,導(dǎo)致數(shù)據(jù)寫不進(jìn)去,也很有可能導(dǎo)致數(shù)據(jù)表損壞,物理硬盤有損壞.主要是這幾個(gè)原因,mysql修復(fù)方法如下
可以把mysql停掉,用mysql的命令myisamchk來修復(fù),這種修復(fù)方法是最好的.
具體命令如下：
|
1
2
3
|
$ myisamchk -r database/*.MYI$ myisamchk -o database/*.MYI$ myisamchk -f database/*.MYI |
參數(shù) -r, -o , -f 是遞進(jìn)關(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è)置進(jìn)入時(shí)的默認(rèn)編碼
|
1
|
mysql -uroot -p --default-character-set=utf8 |
問題：我創(chuàng)建了一個(gè)表來存放客戶信息,我知道可以用 insert 語句插入信息到表中,但是怎么樣才能保證不會插入重復(fù)的記錄呢?
答案：可以通過使用 EXISTS 條件句防止插入重復(fù)記錄.
示例一：插入多條記錄
假設(shè)有一個(gè)主鍵為 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)的信息:
|
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ù)器的連接的次數(shù)。
Connections 試圖連接MySQL服務(wù)器的次數(shù)。
Threads_connected 當(dāng)前打開的連接的數(shù)量.
設(shè)置的連接數(shù)可以通過下面這個(gè)查看
|
1
|
show variables like 'max_connections'; |
如果想修改的話,修改 /etc/my.cnf 找到max_connections一行，修改為(如果沒有，則自己添加)
|
1
|
max_connections = 1000 |
臨時(shí)修改此參數(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è)計(jì)技巧】相關(guān)文章：
2016最數(shù)據(jù)庫設(shè)計(jì)技巧03-29
微軟認(rèn)證考試技巧：數(shù)據(jù)庫03-21
Oracle數(shù)據(jù)庫臨時(shí)表管理技巧03-23
陽臺設(shè)計(jì)的技巧03-06
EDA設(shè)計(jì)技巧03-19
廣告設(shè)計(jì)的設(shè)計(jì)技巧03-13
