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)