學者齋2016年資料庫設計技巧大全
- ACCP
- 關注：1.68W次
下文所講解的資料庫技巧是許多人在大量的資料庫分析與設計實踐中，逐步總結出來的。對於這些經驗的運用，讀者不能生幫硬套，死記硬背，而要消化理解，實事求是，靈活掌握。並逐步做到：在應用中發展，在發展中應用。和yjbys一起來學習吧!
常用資料操作語言DML筆記(select insert update delete)
select 語句
高階的查詢功能,見下面的詳細內容
| 12345 | select列名1,列名2 ...from表名1,表名2...[where條件]like[groupby...][having...][orderby...] 以特定的順序顯示 例:orderbynameasc;以名字顯示,為降序排列 |
insert 語句
MySQL 當記錄不存在時插入 insert if not exists.在 MySQL 中,插入(insert)一條記錄很簡單,但是一些特殊應用,在插入記錄前,需要檢查這條記錄是否已經存在,只有當記錄不存在時才執行插入操作,本文介紹的就是這個問題的解決方案.
| 1 | insertinto表名(列名1,列名2,... )values(值1,值2,...) |
update 語句
| 1 | update表名set列名1=值1,列名2=值2 ... [where條件] |
delete 語句
| 1 | deletefrom表名 [where條件] 注:條件時會刪除整個表名 |
truncate 語句
| 12 | truncatetables 表名# 注:相當刪除整個表名,在重建.非常快 |
從一個檔案載入資料到 MYSQL
| 1 | loaddata infile'檔案'intotables 表名 [fields terminatedby'字元'] |
匯出 MYSQL 資料成一個檔案
| 1 | select*from表名intooutfile'檔案' |
DDL資料定義語言(vreate tabe,drop table,altre table)
MYSQL 的字元型別要注意 CHAR VARCHAR TEXT 不分大小寫,只能存字元 ,BLOB可以存二進位制的內容,如果聲音,檔案.
CHAR主要是定長,VARCHAR是變長,所以VARCHAR更加節約空間.
查看錶結構欄位
| 1 | desctablesname; |
新增新的表字段
| 1234 | altertabletestaddnamevarchar(20); #新增一個name的欄位altertabletestaddf_namevarchar(20)afterid; #在id之後新增一個name的'欄位altertabletestaddsidintfirst; #在最前面加入一個欄位altertabletestaddprimarykey(id); #新增一個primary主鍵 |
刪除表中的欄位
| 12 | altertabletestdropname; #刪除altertabletestaddprimarykey; #刪除主鍵 |
設定表中欄位的預設值
| 1 | altertabletestalternamesetdefault'不知道'; |
修改欄位名
| 1 | altertabletest changenamel_anmevarchar(10); #修改欄位名,資料型別要寫上 |
資料庫插入
| 123 | insert#表名valuesinsertintotmpvalues(default,'kate',0,20);#可以使用default.insertintotmp(name,sex,age)values('katess',0,20); |
表的匯入匯出
| 1 | inserttmp2selectname,sexfromtmpwhereid<4; #可以給select中得到的內容放到tmp2的新表中 |
更新 MYSQL 表中的欄位
| 12 | UPDATE<表名>SET<列名 = 更新值> [WHERE<更新條件>]updatetmpsetaddress='北京'whereidbetween4and5 |
刪除表中特定的內容
| 12 | DELETEFROMtmpWHEREid >5 #刪除id大於5的內容DELETEFROMss1,ss2 using ss1,ss2where=andss1.name='alex'#二張表ss1和ss2相同的id中內容有alex的內容刪除 |
高階查詢
常用查詢函式
concat(字元連線)
distinct(去掉重複資料)
as(別名)
limit 3,2 (控制顯示數,如前現示從第三個起,拿二個數據)
集函式
count(統計)
sun
age
查詢(條件內容查詢)
| 12 | select'abc'like'abc%';# %任意字元 _單個字元最少要出現一次 |
MYSQL 中使用正則來查詢資料
| 1 | select'abc'regexp'^a.*c$'; |
排序查詢結果
使用排序功能,和使用別名功能
| 123 | SELECT*FROMstudentORDERBYsageDESC; #降排序orderby要放在最後<SELECT*FROMstudentORDERBYsdeptin('資訊系','美術系') #同時二個條件SELECTsnoAS'學號', snameAS'姓名'FROMstudent; |
分組
| 12 | selectcid,avg(grad)fromscgroupbycid;# 在分組中groupby要放在最後,要是要加條件的話,集函式要使用having來換where.放在groupby後 |
多表查詢
| 1234 | selecte,e form scasg, studentasswheree='扶%'and=;selecte,efromscasginnerjoinstudentasson=;# 內連線,同連線顯示相同的內容,要加inner給whereis 改成on# 如果有的資料沒有,可以換left,和right以第一個表為基礎來排資料,innerjoin是二個表都有的內容. |
子查詢
| 12 | selectsname,sagefromstudentwheresnonotin(selectsidfromsc) ;selectsnamefromstudentwheresnoin(select) |
在 MYSQL 中查詢,要先轉義
#name=mysql_escape_string($name); mysql_escape_string
FAQ:
1. 檢視和修改設定 MYSQL 預設編碼
| 123 | SHOW CHARACTER SET;檢視支援的所有字show variables like'character_set_%';SHOW VARIABLES LIKE'collation_%'; |
讓 MYSQL 重起也可以使用utf8
| 1234 | [mysqld]default-character-set=utf[mysql]default-character-set=utf8 |
2. 怎麼樣進行 MYSQL 備份
MYSQL 的備份可用命令mysqldump ,使用方法很簡單,
| 1 | $ mysqldump -u 使用者名稱 -p (密碼) -h 主機名 資料庫名 >路徑/備份名 |
同時也可以是用mysqldump備份資料結構()和資料()
| 1 | $ mysqldump -u 使用者名稱 -p (密碼) -h 主機名 資料庫名 tablename1 tablename2 > |
mysqldump -u 使用者名稱 -p (密碼) -h 主機名 資料庫名 –tab 路徑 –opt 資料庫名.
EXA:
| 1 | $ mysqldump -u xxxx -p xxxxt databases > ./ |
3. 改變mysql管理員的密碼
方法1: 在/usr/local/mysql/bin/下：
| 1 | $ mysqladmin -u root password'new_password' |
一般安裝時用此方法設定.
方法2:
在mysql狀態下：
| 12 | 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. 什麼情況下會導致 MYSQL 資料庫損壞和怎麼修復 MYSQL
mysql 正在執行的時候,伺服器突然斷電或者直接按reset鍵重啟,硬碟空間不夠,導致資料寫不進去,也很有可能導致資料表損壞,物理硬碟有損壞.主要是這幾個原因,mysql修復方法如下
可以把mysql停掉,用mysql的命令myisamchk來修復,這種修復方法是最好的.
具體命令如下：
| 123 | $ myisamchk -r database/*$ myisamchk -o database/*$ myisamchk -f database/* |
引數 -r, -o , -f 是遞進關係,一般首先用-r修復,然後-o , -f
5. mysql的匯出匯入
| 12 | $ mysqldump -uroot -p mysql >$ mysql -uroot -p pcti > |
使用source命令,後面引數為指令碼檔案(如這裡用到的)
| 1 | mysql>source d:backup_ |
6. 設定進入時的預設編碼
| 1 | mysql -uroot -p --default-character-set=utf8 |
問題：我建立了一個表來存放客戶資訊,我知道可以用 insert 語句插入資訊到表中,但是怎麼樣才能保證不會插入重複的記錄呢?
答案：可以通過使用 EXISTS 條件句防止插入重複記錄.
示例一：插入多條記錄
假設有一個主鍵為 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 nt_id = lier_id);
示例一：插入單條記錄
Code:
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, ‘IBM’, ‘advertising’
FROM dual
WHERE not exists (select * from clients
where nt_id = 10345);
使用 dual 做表名可以讓你在 select 語句後面直接跟上要插入欄位的值,即使這些值還不存在當前表中.
MySQL 連線的狀態資訊
我們常常需要看一些連線的資訊,如下可以顯示相關的資訊:
| 010203040506070809101112 | mysql> show statuslike'%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 嘗試已經失敗的MySQL伺服器的連線的次數。
Connections 試圖連線MySQL伺服器的次數。
Threads_connected 當前開啟的連線的數量.
設定的連線數可以通過下面這個檢視
| 1 | show variableslike'max_connections'; |
如果想修改的話,修改 /etc/ 找到max_connections一行，修改為(如果沒有，則自己新增)
| 1 | max_connections = 1000 |
臨時修改此引數的值, 注意大小寫
| 1 | set GLOBAL max_connections=1000; |
查詢表的格式
| 1 | SHOWTABLESTATUSWHEREROW_FORMATLIKE'Compact' |
查詢 binlog 轉換成可讀
mysqlbinlog mysql-bin.000002 -vvvv –base64-output=DECODE-ROWS
- 文章版權屬於文章作者所有，轉載請註明 https://xuezhezhai.com/zh-tw/itrz/accp/rvodo3.html
