mysql琐碎

---

SELECT * from employees limit 2,1

select * from employees limit 2 OFFSET 3;

SELECT RAND() from DUAL;

SELECT (TRUNCATE(RAND(),2))*100 FROM DUAL;

REPLACE(str,from_str,to_str)

CONCAT(str1,str2,...)
SELECT CONCAT_WS(",",last_name,salary) s FROM employees;



NULLIF(expr1,expr2)


SELECT CURRENT_DATE() from DUAL;
SELECT CURRENT_TIME() from DUAL;

SELECT CURRENT_TIMESTAMP() from DUAL;
SELECT NOW() FROM DUAL;
SELECT LOCALTIME() from DUAL;

SELECT UNIX_TIMESTAMP(NOW()) FROM dual;

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())) from DUAL;

-- sql语法
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

-- sql内部
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT



SELECT department_id,AVG(salary) from employees GROUP BY department_id with ROLLUP;

#创建管理表

create database test1;


create database test2 set character set utf8mb4;

create database if not exists test3;


show databases;

SELECT database();

show tables from tes1;

show create database test3;

use test2;

alter database test character set utf8mb3;

drop database test3;

drop databse if exists test3;

CREATE TABLE emp1 AS SELECT * FROM employees;

alter table test3 ADD lname VARCHAR(20);

alter table test3 modify lname varchar(30);

alter table test2 CHANGE lname newname VARCHAR(45);

alter table test2 drop column newname ;

rename table test2 to newtest2;
alter table test2 rename to newtest2;

drop table if exists test2;

drop table test2;

truncate table test2;

delete from test2;




#数据处理之增删改

insert into test2 values (DEFAULT,'wqddwq',242,NULL);


insert into test(lname,age,other) values('cdfdcew',234,'fewcf');


insert into test
values
(DEFAULT,'wqddwq',242,NULL),
(DEFAULT,'dsggvg',6567,NULL),
(DEFAULT,'kjyutkj',766,NULL);

insert into test(id,lname,other)
VALUES
(DEFAULT,'wqddwq',242,NULL),
(DEFAULT,'dsggvg',6567,NULL),
(DEFAULT,'kjyutkj',766,NULL);


insert into test SELECT * from test2;

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

update employee set department_ids = 30 
WHERE employees_id = 2;

update employee set departments_id = 11;


delete from test1 WHERE lname = 'xxx';
delete from test1;

drop table books;
CREATE TABLE books(
id INT AUTO_INCREMENT,
bname VARCHAR(50) DEFAULT '',
`authors` VARCHAR(100) DEFAULT'',
price FLOAT DEFAULT NULL,
pubdate YEAR DEFAULT NULL,
note VARCHAR(100) DEFAULT '',
num INT DEFAULT NULL,
PRIMARY key (id)
);




#约束

NOT NULL 非空约束，规定某个字段不能为空
UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
PRIMARY KEY 主键(非空且唯一)约束
FOREIGN KEY 外键约束
DEFAULT 默认值约束

ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);
alter table 表名称 drop primary key;

alter table 表名称 modify 字段名 数据类型 auto_increment;

alter table books modify num int auto_increment;



#视图

-- 视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和
-- 修改操作时，数据表中的数据会相应地发生变化，反之亦然。
create view testViwe
as 
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;


-- 利用视图对数据进行格式化

CREATE VIEW emp_depart
AS
SELECT CONCAT(last_name,'(',department_name,')') AS emp_dept
FROM employees e JOIN departments d
WHERE e.department_id = d.department_id


-- 查看视图
show tables;

drop view testViwe;

SHOW CREATE VIEW 视图名称;

DROP VIEW IF EXISTS 视图名称;

ALTER VIEW 视图名称
AS
查询语句






#事务
1. 脏写（ Dirty Write ）
对于两个事务 Session A、Session B，如果事务Session A 修改了 另一个 未提交 事务Session B 修改过 的数据，那就意味着发生了 脏写
2. 脏读（ Dirty Read ）
对于两个事务 Session A、Session B，Session A 读取 了已经被 Session B 更新 但还 没有被提交 的字段。之后若 Session B 回滚 ，Session A 读取 的内容就是 临时且无效 的。
Session A和Session B各开启了一个事务，Session B中的事务先将studentno列为1的记录的name列更新
为'张三'，然后Session A中的事务再去查询这条studentno为1的记录，如果读到列name的值为'张三'，而Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据，这种现象
就称之为 脏读
3. 不可重复读（ Non-Repeatable Read ）
对于两个事务Session A、Session B，Session A 读取 了一个字段，然后 Session B 更新 了该字段。 之后Session A 再次读取 同一个字段， 值就不同 了。那就意味着发生了不可重复读。
我们在Session B中提交了几个 隐式事务 （注意隐式事务，意味着语句结束事务就提交了），这些事务
都修改了studentno列为1的记录的列name的值，每次事务提交之后，如果Session A中的事务都可以查看
到最新的值，这种现象也被称之为 不可重复读 。
4. 幻读（ Phantom ）
对于两个事务Session A、Session B, Session A 从一个表中 读取 了一个字段, 然后 Session B 该表中 插入 了一些新的行。 之后, 如果 Session A 再次读取同一表就会多出几行。那就意味着发生了幻读。Session A中的事务先根据条件 studentno > 0这个条件查询表student，得到了name列值为'张三'的记录；之后Session B中提交了一个 隐式事务 ，该事务向表student中插入了一条新记录；之后Session A中的事务再根据相同的条件 studentno > 0查询表student，得到的结果集中包含Session B中的事务新插入的那条记
录，这种现象也被称之为 幻读 。我们把新插入的那些记录称之为 幻影记录 。

-- 问题：
脏写 > 脏读 > 不可重复读 > 幻读

-- 隔离级别

READ UNCOMMITTED ：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结
果。不能避免脏读、不可重复读、幻读。
READ COMMITTED ：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做
的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可
重复读、幻读问题仍然存在。
REPEATABLE READ ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提
交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍
然存在。这是MySQL的默认隔离级别。
SERIALIZABLE ：可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止
其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避
免脏读、不可重复读和幻读。


-- 用户权限管理

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句" -S "socket路径"

mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"

#创建用户

CREATE USER zhang3 IDENTIFIED BY '123123';

CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';

# 修改用户名

UPDATE mysql.user SET USER='li4' WHERE USER='zhang3';

FLUSH PRIVILEGES;

select * from mysql.user ;

# 删除用户
drop user li4;  #默认删除host为%的用户
drop user 'kangshifu'@'localhost';
DELETE FROM mysql.user WHERE Host='localhost' AND User='li4'; #不推荐


-- 修改当前用户密码
ALTER USER USER() IDENTIFIED BY 'new_password';
SET PASSWORD='new_password';
-- 修改其他用户密码
ALTER USER 'kangshifu'@'localhost' IDENTIFIED BY 'new_password';
SET PASSWORD FOR 'username'@'hostname'='new_password';

-- 密码过期策略
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE;
-- 使用SQL语句更改该变量的值并持久化
SET PERSIST default_password_lifetime = 180; # 建立全局策略，设置密码每隔180天过期
-- 配置文件my.cnf中进行维护
[mysqld]
default_password_lifetime=180 #建立全局策略，设置密码每隔180天过期

-- 手动设置指定时间过期方式2：单独设置
#设置kangshifu账号密码每90天过期：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
#设置密码永不过期：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;
#延用全局密码过期策略：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;

#密码重用策略
SET PERSIST password_history = 6; #设置不能选择最近使用过的6个密码
SET PERSIST password_reuse_interval = 365; #设置不能选择最近一年内的密码
-- my.cnf配置文件
[mysqld]
password_history=6
password_reuse_interval=365

-- 手动设置密码重用方式2：单独设置
#不能使用最近5个密码：
CREATE USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;
ALTER USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;
#不能使用最近365天内的密码：
CREATE USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
#既不能使用最近5个密码，也不能使用365天内的密码
CREATE USER 'kangshifu'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 365 DAY;


#权限管理
show privileges;

GRANT SELECT,INSERT,DELETE,UPDATE ON atguigudb.* TO li4@localhost ;
GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';


-- 查看当前用户权限
SHOW GRANTS;
# 或
SHOW GRANTS FOR CURRENT_USER;
# 或
SHOW GRANTS FOR CURRENT_USER();

SHOW GRANTS FOR 'user'@'主机地址' ;


-- 收回权限命令  #须用户重新登录后才能生效
-- REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
#收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';
#收回mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;


-- user表
-- 记录用户账号和权限信息

DESC mysql.user;
-- 查看用户, 以列的方式显示数据：
SELECT * FROM mysql.user \G;

SELECT * FROM mysql.user;
-- 查询特定字段：
SELECT host,user,authentication_string,select_priv,insert_priv,drop_priv
FROM mysql.user;


#角色
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

CREATE ROLE 'manager'@'localhost';
GRANT privileges ON table_name TO 'role_name'[@'host_name'];

SHOW PRIVILEGES;
SHOW PRIVILEGES\G;
GRANT SELECT ON demo.settlement TO 'manager';

SHOW GRANTS FOR 'manager';
REVOKE privileges ON tablename FROM 'rolename';
REVOKE INSERT, UPDATE, DELETE ON school.* FROM 'school_write';
SHOW GRANTS FOR 'school_write';

# 如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限 。
DROP ROLE 'school_read';

# 角色创建并授权后，要赋给用户并处于 激活状态 才能发挥作用
# 给用户赋予角色
GRANT 'school_read' TO 'kangshifu'@'localhost';

SHOW GRANTS FOR 'kangshifu'@'localhost';

#查询当前角色是否激活
SELECT CURRENT_ROLE();

#激活角色
#方式1：使用set default role 命令激活角色
SET DEFAULT ROLE ALL TO 'kangshifu'@'localhost';

# 使用 SET DEFAULT ROLE 为下面4个用户默认激活所有已拥有的角色如下
SET DEFAULT ROLE ALL TO
'dev1'@'localhost',
'read_user1'@'localhost',
'read_user2'@'localhost',
'rw_user1'@'localhost';

#方式2：将activate_all_roles_on_login设置为ON

show variables like 'activate_all_roles_on_login';

SET GLOBAL activate_all_roles_on_login=ON;   -- 这条 SQL 语句的意思是，对 所有角色永久激活 。
 
# 撤销用户的角色

REVOKE role FROM user;

REVOKE 'school_read' FROM 'kangshifu'@'localhost';



#mysqldump


#备份全部数据库
mysqldump -uroot -p123456 --all-databases > backup_all_databases.sql

#备份一个数据库
mysqldump -uroot -p123456 --databases database_test1 > backup_database_test1.sql
 
#备份多个数据库
mysqldump -uroot -p123456 --databases database_test1 database_test2 > backup_database_test1_test2.sql

#还原数据库
mysql -uroot -p123456 < backup_database_test1_test2.sql

#恢复数据表
#恢复表的前提是表所在的库必须存在，且可任意指定库进行恢复操作
mysql -u root -p123456 database_test1 < backup_tables.sql

#不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名
mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user

#使用的连接协议。
mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp


#1. db级
#导出所有db结构和数据
mysqldump -uroot -p -A > /data/bak/all.sql

#仅导出所有db结构
mysqldump -uroot -p -A -d > /data/bak/all_struct.sql

#仅导出所有db数据不导表结构
mysqldump -uroot -p -A -t > /data/bak/all_data.sql

#导出单个db结构和数据
mysqldump -uroot -p mydb > /data/bak/mydb.sql

#仅导出单个db结构
mysqldump -uroot -p mydb -d > /data/bak/mydb.sql

#仅导出单个db数据
mysqldump -uroot -p mydb -t > /data/bak/mydb.sql

#导出多个db结构和数据（--databases参数，数据，结构单独导出方法同上）
mysqldump -uroot -p --databases db1 db2 > /data/bak/muldbs.sql

#2. 表级
#导出指定db某张表结构及数据（数据，结构单独导出方法同上）
mysqldump -uroot -p dbname test > db.sql
#导出指定db中多张表（test1,test2,test3）结构及数据
mysqldump -uroot -p dbname test1 test2 test3 > db.sql
#排除部分表，导出DB中其余表结构和数据
mysqldump -uroot -p mydb --ignore-table=mydb.test1 --ignore-table=mydb.test2 > /data/bak/mydb.sql
#指定where条件导出表的部分数据
mysqldump -uroot -w "id=6032" -p db_name  tbl_name > /tmp/where.sql

--where="user='jimf'"
-w"userid>1"
-w"userid<1"

docker exec mysql83 mysqldump -uroot -pzmsqlmaster atguigudb employees --where="salary>'10000'" -w "employee_id<150" > /data/w3.sql

docker exec mysql83 mysqldump -uroot -pzmsqlmaster atguigudb employees --where="salary>10000 and employee_id<110" > /data/w3.sql

desc employees;


grant all PRIVILEGES on *.* to root@'localhost';

flush PRIVILEGES;


ALTER USER 'root'@'localhost' IDENTIFIED BY 'zmsqlmaster';


select count(*) from employees WHERE employee_id < 150 AND salary > 10000;





查看当前连接数：

若要查看当前的MySQL连接数，您可以使用以下命令：

SHOW STATUS LIKE 'Threads_connected';

这将返回一个包含当前连接数的结果集。

查看并发查询数：

若要查看当前的并发查询数，您可以使用以下命令：

SHOW STATUS LIKE 'Threads_running'