MySQL操作 · Dayup

一、连接与基本使用

连接数据库

mysql -h host.com -P 3306 -uroot -p

基本查询

SELECT VERSION();                                    //查询版本

SHOW DATABASES;                                 //查询数据库

USE mysql;                                      //使用数据库

SHOW TABLES;                                    //查询数据表 

SHOW TABLE STATUS FROM mysql WHERE name = 'user';   //查询数据表状态
show table status like 'fileCompare';                //查询数据表状态
SELECT TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'dscg' AND TABLE_NAME = 'fileCompare';

DESC user;
SHOW FULL COLUMNS FROM user;                    //查看数据表字段

SHOW CREATE TABLE user;                         //查看数据表建表语句DDL

SHOW INDEX FROM user;                           //查看数据表索引

SHOW STATUS;                                    //查询Server的状态

SHOW VARIABLES;                                 //MySQL的变量

SHOW PROCESSLIST;                               //已登录用户信息、连接信息

SHOW GRANTS FOR user@'localhost';               //列出用戶已有的授权

show variables like '%connections%';            //查看最大连接数(set global max_connections=1000;)

show status like  'Threads%';                   //查看连接线程状态

#其他常用SQL
SHOW ENGINE innodb status;                     #展示查询InnoDB状态，可以排查死锁
SHOW PROCESSLIST;                               #显示用户正在运行的线程
SET GLOBAL slow_query_log = 0;                 #关闭慢查询
SHOW VARIABLES LIKE 'slow_query_log';          #查询是否开启慢查询日志
SHOW variables LIKE 'log_bin';                 #查询是否开启binlog
SHOW VARIABLES LIKE "%innodb_lock_wait_timeout%";          #查询锁超时时间
SHOW GLOBAL VARIABLES LIKE "%innodb_lock_wait_timeout%";   #查询全局锁超时时间
SELECT * FROM information_schema.INNODB_LOCKS;              #查看正在锁的事务
SELECT * FROM information_schema.INNODB_LOCK_WAITS;       #查看等待锁的事务
SELECT trx_mysql_thread_id,trx_query from information_schema.innodb_trx where trx_state='LOCK WAIT';   #查询锁表

开启慢查询

slow_query_log = 1                              #开启慢查询日志
slow_query_log_file = /path/to/slow-query.log   #指定慢查询日志文件的路径和文件名。
log_slow_rate_limit = 10                        #相同的慢查询在每秒内最多记录10次
log_slow_verbosity = query_plan,innodb          #慢查询日志的详细程度
long_query_time = 1                             #慢查询的时长阈值 秒

备份

/usr/bin/mysqldump -h${host} -u${user} -p${password} ${database} --single-transaction --skip-add-locks --quick --opt --routines --triggers -c > ${database}.sql
mysql -h${host} -u${user} -p${password} ${database} --init-command="SET autocommit=0; SET foreign_key_checks=0; SET unique_checks=0;" < ${database}.sql

二、用户与权限

--创建用户组
GROUPADD group1

--创建用户
CREATE USER 'username'@'host' IDENTIFIED BY '123456';
ALTER USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;
--删除用户
DROP USER 'username'@'%';

--修改用户密码
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
ALTER USER 'root'@'%' identified BY '<新密码>';        -- 在MySQL 8.0及以上版本中，可以使用ALTER USER语句来修改用户密码。


--用户加入用户组
useradd -g group1 user
useradd user1 -g group1 -G group2                                   -- －g:指定用户所属的起始群组  －G:指定用户所属的附加群组

--授予用户权限
GRANT privilege ON db.table TO 'user'@'host';                       -- privilege：SELECT,INSERT,UPDATE ... ALL
GRANT privilege ON db.table TO 'user'@'host' WITH GRANT OPTION;     -- 可再授权权限
--删除用户权限
REVOKE privilege ON db.table FROM 'user'@'host';


-- 例子：创建用户、创建数据库、授予数据库权限、删除用户
CREATE USER IF NOT EXISTS '${username}'@'%' IDENTIFIED WITH mysql_native_password BY '${123456}';
CREATE DATABASE IF NOT EXISTS ${dbname} DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP ON ${dbname}.* TO '${username}'@'%';
REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON ${dbname}.* TO '${username}'@'%';  --撤销授权
SELECT * FROM mysql.user WHERE User='xxxx' AND Host='%'\G --查看所有权限
SELECT * FROM mysql.db WHERE User='xxxx' AND Host='%'\G
FLUSH PRIVILEGES;   --MySQL 8.0+ 通常无需手动刷新
mysql -hlocalhost -uroot -p -D ${dbname} --init-command="SET autocommit=0; SET foreign_key_checks=0; SET unique_checks=0;" < ${DB}.sql        --导入数据
DROP USER '${username}'@'%';

三、常用SQL

--导出数据（确保导出和导入环境的MySQL版本兼容，导出后验证SQL文件完整性和数据行数。 对大表可使用mydumper/myloader工具提高速度）
/usr/bin/mysqldump -h${local_host} -u${db_user} -p ${database} ${tablename} --single-transaction --skip-add-locks --quick --opt --routines --triggers -t -c | gzip > ${tablename}.sql
-- -t 无建表语句 -c 完整插入语句含字段映射  -R,--routines: Dump stored routines (functions and procedures).  --triggers: Dump triggers for each dumped table.(Defaults to on; use --skip-triggers to disable.)

--导入数据
mysql -h${db_host} -u${db_user} -p${db_password} ${database} --init-command="SET autocommit=0; SET foreign_key_checks=0; SET unique_checks=0;" < ${large_file}.sql


--插入、查询、更新、删除
INSERT INTO db.table(`id`, `f1_name`, `create_time`) VALUES (1,'name1', '2021-01-01 12:00:00');

SELECT * FROM tabel WHERE f1_name like 'name%' AND create_time >= "2021-01-01 12:00:00" ORDER BY id;

UPDATE db.table SET f1_name = 'name2', create_time = '2021-01-01 12:00:00' WHERE f1_name = 'name1';

DELETE FROM db.table where f1_name='name2';


--批量插入(插入前先删除)
REPLACE INTO db.table(`id`, `f1_name`, `create_time`) VALUES (1,'name1', '2021-01-01 12:00:00'),(2,'name2', '2021-01-01 12:00:00');

--批量插入(存在则更新)
INSERT INTO db.table(`id`, `f1_name`, `create_time`) VALUES (1,'name1', '2021-01-01 12:00:00'),(2,'name2', '2021-01-01 12:00:00') 
ON duplicate key UPDATE f1_name= VALUES(f1_name),create_time= VALUES(create_time);


--批量更新
UPDATE db.table 
    SET t1 = CASE id 
        WHEN 1 THEN 123 
        WHEN 2 THEN 456
        WHEN 3 THEN 789
    END, 
    t2 = CASE id 
        WHEN 1 THEN 't1_value'
        WHEN 2 THEN 't2_value'
        WHEN 3 THEN 't3_value'
    END
WHERE id IN (1,2,3)

--批量删除
DELETE FROM 
    db.table
WHERE 
    id IN (1,2,3)

四、其他SQL

获取当前时间判断，为上午12:00前则展示到昨天数据，下午则展示到当天数据

SELECT *
FROM test.xxxdaily_stats AS a 
WHERE a.DAY_ID <= cast(
        IF(
            CURRENT_TIMESTAMP() < DATE_FORMAT(CURDATE(), '%Y-%m-%d 12:00:00'),
            DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y%m%d'),
            DATE_FORMAT(CURDATE(), '%Y%m%d')
        ) as SIGNED INTEGER
    );

行转列

-- 表字段： day_id | level | count_num
SELECT
    "a".day_id,
    sum(case "level" when 1 then count_num end) as level1,
    sum(case "level" when 2 then count_num end) as level2,
    sum(case "level" when 3 then count_num end) as level3,
    sum(case "level" when 4 then count_num end) as level4,
    sum(case "level" when 5 then count_num end) as level5,
    sum(case "level" when 6 then count_num end) as level6,
    sum(case "level" when 7 then count_num end) as level7,
    sum(case "level" when 8 then count_num end) as level8,
    sum(case "level" when 9 then count_num end) as level9,
    sum(case "level" when 10 then count_num end) as level10
FROM
    level_count AS "a"

五、字符集及排序规则

字段类型	推荐字符集	推荐排序规则	理由
商品标题/描述	utf8mb4	utf8mb4_unicode_ci	支持多语言和特殊字符
用户信息(姓名/地址)	utf8mb4	utf8mb4_unicode_ci	国际化支持
用户名/登录账号	utf8mb4	utf8mb4_bin	精确匹配，区分大小写
SKU编码/订单号	ascii	ascii_bin	通常只需ASCII字符，二进制精确匹配
商品分类/标签	utf8mb4	utf8mb4_unicode_ci	支持多语言分类
搜索关键词	utf8mb4	utf8mb4_unicode_ci	支持多语言搜索
评论/评价内容	utf8mb4	utf8mb4_unicode_ci	支持emoji和特殊字符

六、其他数据库PostgreSQL

psql postgres://username:password@host:port/dbname  
psql -U username -h hostname -p port -d dbname 
\l           --查看数据库
\c dbname    --切换数据库
\dt          --查看表
--查看最大连接数
show max_connections;

--管理员保留连接数
show superuser_reserved_connections;

--当前连接数
select count(1) from pg_stat_activity;

--查询数据库连接
SELECT datname,pid,usename,application_name,client_addr,query_start,state_change,"state",query from pg_stat_activity  ORDER BY query;

--关闭连接
select pg_terminate_backend(pid) from pg_stat_activity where client_addr ='';