Mysql使用注意問題
duplicate entry for primary key:因為重複向資料庫導入已經存在的資料,删除重複的值即可;
mysql 5.5版本中文不能導入的問題:将my.ini中client和server中的字元集改成GBK,如果使用可視化工具(比如navicate)時也要注意選擇GBK字元集;
auto_increment:自動插值的列不必在寫插入語句的時候出現;
eg:
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5yNxQDZkRDOyMzM0MTYwcjMzE2YwkTNihTYjljY4EDOw8CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
在MySQL中有兩種方法可以删除資料,一種是DELETE語句,另一種是TRUNCATE TABLE語句。DELETE語句可以通過WHERE對要删除的記錄進行選擇。而使用TRUNCATE TABLE将删除表中的所有記錄。是以,DELETE語句更靈活。
如果要清空表中的所有記錄,可以使用下面的兩種方法:
(1)DELETE FROM table1
(2)TRUNCATE TABLE table1
其中第二條記錄中的TABLE是可選的。
如果要删除表中的部分記錄,隻能使用DELETE語句。DELETE FROM table1 WHERE …;
如果DELETE不加WHERE子句,那麼它和TRUNCATE TABLE是一樣的,但它們有一點不同,那就是DELETE可以傳回被删除的記錄數,而TRUNCATE TABLE傳回的是0。
如果一個表中有自增字段,使用TRUNCATE TABLE和沒有WHERE子句的DELETE删除所有記錄後,這個自增字段将起始值恢複成1.如果你不想這樣做的話,可以在DELETE語句中加上永真的WHERE,如WHERE 1或WHERE true。
DELETE FROM table1 WHERE 1;
上面的語句在執行時将掃描每一條記錄。但它并不比較,因為這個WHERE條件永遠為true。這樣做雖然可以保持自增的最大值,但由于它是掃描了所有的記錄,是以,它的執行成本要比沒有WHERE子句的DELETE大得多。
DELETE和TRUNCATE TABLE的最大差別是DELETE可以通過WHERE語句選擇要删除的記錄。但執行得速度不快。而且還可以傳回被删除的記錄數。而TRUNCATE TABLE無法删除指定的記錄,而且不能傳回被删除的記錄。但它執行得非常快。
和标準的SQL語句不同,DELETE支援ORDER BY和LIMIT子句,通過這兩個子句,我們可以更好地控制要删除的記錄。如當我們隻想删除WHERE子句過濾出來的記錄的一部分,可以使用LIMIB,如果要删除後幾條記錄,可以通過ORDER BY和LIMIT配合使用。假設我們要删除users表中name等于”Mike”的前6條記錄。可以使用如下的DELETE語句:
DELETE FROM users WHERE name = ‘Mike’ LIMIT 6;
一般MySQL并不确定删除的這6條記錄是哪6條,為了更保險,我們可以使用ORDER BY對記錄進行排序。
DELETE FROM users WHERE name = ‘Mike’ ORDER BY id DESC LIMIT 6;
可在建表時可用“AUTO_INCREMENT=n”選項來指定一個自增的初始值。
可用alter table table_name AUTO_INCREMENT=n指令來重設自增的起始值;
删除某列:alter table test.consumption drop columnname;
ALTER TABLE tablename auto_increment=’1’ ;執行成功,繼續插入就從1開始了。
mysql根據配置檔案會限制server接受的資料包大小。
有時候大的插入和更新會被max_allowed_packet 參數限制掉,導緻失敗。
檢視目前配置:
show VARIABLES like ‘%max_allowed_packet%’;
顯示的結果為:
+——————–+———+
| Variable_name | Value |
+——————–+———+
| max_allowed_packet | 1048576 |
+——————–+———+
以上說明目前的配置是:1M
修改方法
1) 方法1
可以編輯my.cnf來修改(windows下my.ini),在[mysqld]段或者mysql的server配置段進行修改。
max_allowed_packet = 20M
如果找不到my.cnf可以通過
mysql –help | grep my.cnf
去尋找my.cnf檔案。
2) 方法2
進入mysql server
在mysql 指令行中運作
set global max_allowed_packet = 2*1024*1024*10
然後關閉掉這此mysql server連結,再進入。
show VARIABLES like ‘%max_allowed_packet%’;
檢視下max_allowed_packet是否編輯成功。
11.update set指令用來修改表中的資料:
update set指令格式:update 表名 set 字段=新值,… where 條件;
12.mysql中不能寫類似于 ’某個值‘
13.mysql自定義函數舉例(2016年Alibaba資料研發實習筆試附加題第1題答案):
```
CREATE FUNCTION score
(
age INT,
degree CHAR(1),
balance BIGINT(20),
reg_time DATETIME
)
RETURNS INT
BEGIN
DECLARE score INT;
DECLARE diff_year INT;
SET score = 300;
SET diff_year = TIMESTAMPDIFF(YEAR, reg_time, now());
-- 計算年齡
IF age BETWEEN 18 AND 30 THEN
SET score = score + 10;
ELSEIF age BETWEEN 31 AND 59 THEN
SET score = score + 20;
END IF;
-- 計算學曆
IF degree = 'A' THEN
SET score = score + 20;
ELSEIF degree = 'B' THEN
SET score = score + 10;
END IF;
-- 計算餘額
IF balance >= 100000 THEN
SET score = score + 20;
ELSEIF balance BETWEEN 1000 AND 99999 THEN
SET score = score + 10;
END IF;
-- 計算年限
IF diff_year BETWEEN 1 AND 5 THEN
SET score = score + 10;
ELSEIF diff_year > 5 THEN
SET score = score + 20;
END IF;
RETURN (score);
END;
SELECT t1.uid,t1.acid,t1.regtime,max(t1.score) as uscore
FROM
(
SELECT
u.user_id uid,
a.account_id acid,
a.reg_time regtime,
score(u.age, u.degree, a.balance, a.reg_time) score
FROM
accounts a
JOIN users u ON u.user_id = a.user_id
WHERE a.balance = (
SELECT
MAX(balance)
FROM
accounts
WHERE
user_id = u.user_id
)
HAVING a.reg_time >= (
SELECT
MIN(reg_time)
FROM
accounts
WHERE
user_id = u.user_id
)
)t1
GROUP BY t1.uid
ORDER BY t1.uid ASC;
```
14 . mysql中的DECLARE語句是在複合語句中聲明變量的指令。
在過程中定義的變量并不是真正的定義,隻是在BEGIN/END塊内定義了而已(譯注:也就是形參)。注意這些變量和會話變量不一樣,不能使用修飾符@,必須清楚的在BEGIN/END塊中聲明變量和它們的類型。變量一旦聲明,你就能在任何能使用會話變量、文字、列名的地方使用。
有很多初始化變量的方法。如果沒有預設的子句,那麼變量的初始值為NULL。可以在任何時候使用SET語句給變量指派。
DECLARE a, b INT DEFAULT 5; 在這裡使用了DEFAULT子句來設定初始值,這就不需要把DECLARE和SET語句的實作分開了。
内部的變量在其作用域内享有更高的優先權。當執行到END語句時,内部變量消失,此時已經在其作用域外,變量不再可見了,是以在存儲過程外再也不能找到這個聲明了的變量,但是你可以通過OUT參數或者将其值指派 給會話變量來儲存其值。
15.case when的使用舉例:
下表有user,score,rating三列,求每個user中score值等于rating值的數目(若兩列沒有相等的則記為0)
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5yNxQDZkRDOyMzM0MTYwcjMzE2YwkTNihTYjljY4EDOw8CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
代碼:
select user,sum( case when score=rating then 1 when score!=rating then 0 end) as sum from tablename group by user;
查詢結果為:
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5yNxQDZkRDOyMzM0MTYwcjMzE2YwkTNihTYjljY4EDOw8CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)