mysql常用语法

MYSQL 有则修改 无则插入 ON DUPLICATE KEY UPDATE

INSERT INTO user_information_view (
user_id,
information_id,
view_count,
created_at,
modified_at,
modified_on,
STATUS 
)
VALUES
(
	67,
	685100,
	1,
	NOW( ),
	NOW( ),
	CURRENT_DATE,
	0 
) 
ON DUPLICATE KEY UPDATE 
view_count = view_count + 1,
modified_at = NOW( ),
modified_on = CURRENT_DATE,
STATUS = 1 

规则:如果你插入的记录导致一个**UNIQUE索引或者primary key(主键)**出现重复，那么就会认为该条记录存在，则执行update语句而不是insert语句，反之，则执行insert语句而不是更新语句。所以 ON DUPLICATE KEY UPDATE是不能写where条件的

SQL 不等于查询

查找非自己，他人是否使用该名称
DB::table('user')
    ->where('nickname', $nickname)
    ->where('user_id', '<>', $user['user_id'])
    ->first();

SQL 自定义查询字段

SELECT
`last_check_in_on`,
`continuous_days`,
CURRENT_DATE AS `today`//自定义字段
FROM
`user_check_in` 
WHERE
`user_id` = 67 

MYSQL CASE 语句

# 查询输出 sex 1男 0女 其他 未知
SELECT user_id,nickname,(CASE sex WHEN 0 THEN '女' WHEN 1 THEN '男' ELSE '未知' END) AS '性别' FROM USER LIMIT 50  
# 修改 批量条件修改(当user_Id=1,sex修改为3；当user_id=2,sex修改为4)
UPDATE user SET `sex` = CASE WHEN `user_id` = 1 THEN 3 WHEN `user_id` = 2 THEN 4 ELSE `sex` END WHERE `user_id` IN (1,2)  

用户管理

// 创建用户
CREATE USER 'report_select'@'%' IDENTIFIED BY 'Report_123'; 

// 查看所有账户
select user,host from mysql.user;

// 删除账户
drop user 'report_select'@'localhost';

// 查看账号权限
show grants for report_select;

// 授权report_select账户所有库写权限 ，所有ip主机可连
grant select on *.* to report_select@'%';

// 授权dev账户所有库权限，所有ip主机可连
GRANT ALL ON *.* TO 'dev'@'%';

// 刷新账户更改
FLUSH PRIVILEGES;

// 分配权限
语法  grant 权限 on 数据库.数据表 to '用户' @ '主机名';
grant select on *.* to 'select_auth'@'%'; // 只读权限
grant 后面的枚举值说明 https://www.bbsmax.com/A/RnJWDbno5q/

// 重置密码
update mysql.user set authentication_string=password('rootrootpwd') where user='root';
flush privileges;

dump导出

// 查的权限忽略锁表dump导出
mysqldump --skip-opt -h10.0.10.194 -ureport_select -pReport_123 ziwei kwai_campaign_list > /root/ziwei/kwai_campaign_list.sql

添加3306端口外部访问

查看端口是否开放
firewall-cmd --query-port=3306/tcp
开放端口
firewall-cmd --permanent --zone=public --add-port=3306/tcp

Mysql where条件对int字段的处理

如表student，字段Id（int），可以select * from student where id='12abc'。发现可以查到id=12的记录。

原理：Mysql会将传入的string（要赋值给int字段的）从左到右的第一个非数值开始，将后面的字符串转成0，在和数值类型相加，如上述例子就是 12abc=12+0=12，也就是等效于select * from student where id=12。

insert、select、update、delete都可以

mysql 按in条件中的字段排序

mysql 用In 在排序时可以按照所需的样子排序

    SELECT * FROM `subject` WHERE id in(18,16,19) order by field(id,18,16,19) 

SQL查询语句中的 limit 与 offset 的区别
limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据
limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据

第1页： 跳过0条数据，获取20条数据 (即1~20条)

select * from testtable limit 0, 20; 
select * from testtable limit 20 offset 0;  

第2页： 跳过20条数据，获取20条数据 (即21~40条)

select * from testtable limit 20, 20; 
select * from testtable limit 20 offset 20;  

第3页： 跳过40条数据，获取20条数据 (即41~60条)

select * from testtable limit 40, 20;  
select * from testtable limit 20 offset 40;  

复制新建一张表，并复制其中的数据，like复制不包含数据

create table test_data_test as select * from test_data;