mysql增删改查常用命令

-- 创建数据库用户
create user 'test'@'%' identified by '123qqq...A';
-- 授权所有库只读权限
grant select on *.* to 'test'@'%' identified by '123qqq...A';

# 8.0版本需要将创建用户和授权进行分步进行：

create user 'prometheusalert'@'%' identified with mysql_native_password BY 'prometheusalert';
GRANT ALL ON prometheusalert.* TO `prometheusalert`@`%` WITH GRANT OPTION;
 
# 创建账号密码
CREATE USER `nacos`@`127.0.0.1` IDENTIFIED BY 'password';
 
# 授予权限
GRANT ALL ON nacos.* TO `nacos`@`%` WITH GRANT OPTION;
 
# 删除权限
REVOKE all privileges ON databasename.tablename FROM 'username'@'host';
 
# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';
 
# 创建带过期时间的用户：
CREATE USER `wangwei`@`127.0.0.1` IDENTIFIED BY 'wangwei' PASSWORD EXPIRE INTERVAL 90 DAY;
 
GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` WITH GRANT OPTION;

show databases;
use test;
show tables from mysql;
select database();
create table stuinfo(id int,name varchar(20));
insert into stuinfo (id,name) values(1,'john');
desc stuinfo
update stuinfo set name='lilei' where id=1;
delete from stuinfo where id=1;
select * from stuinfo;
select version();

myemployees 员工
create database myemployees
create table employees(employee_id int(6),first_name varchar(20),last_name varchar(25),email varchar(25)
,phone_number varchar(20),job_id varchar(10),salary double(10,2),commission_pct double(4,2),manager_id int(6),department_id int(4)
,hiredate datetime)


create table departments(department_id int(4),department_name varchar(3),manager_id int(6),location_id int(4)); 部门表
create table locations(location_id int(11),street_address varchar(40),postal_code varchar(12),city varchar(30)，
state_province varchar(25),country_id varchar(2));     位置表
create table jobs(job_id varchar(10),job_title varchar(35),min_salary int(6),max_salary int(6));   工种

select last_name from employees;
select last_name,salary,email from employees;
select * from employees;
select last_name AS 姓,first_name AS 名 from employees;  起别名AS 空格
select last_name 姓,first_name 名 from employees;
select distinct department_id from employees;   去除重复编号
select concat(last_name,first_name) AS 姓名 from employees; 字段连接
select last_name , job_id , salary as sal from employees;
select 查询列表 from 表面 where 筛选条件;
select * from employees where salary>12000;
select last_name,department_id from employees where department_id<>90; 不等于90的数值
select last_name,salary,commission_pct from employees where salary>=10000 and salary<=20000;
select * from employees where department_id<90 or department_id>110 or salary>15000;
select * from employees where not(department_id>=90 and department_id<=110) 
select * from employees where last_name like '%a%'
select * from employees where employee_id between 100 and 120;
select last_name,job_id from employees where job_id in('it_prot','ad_vp','ad_pres');
select last_name,commission_pct from employees where commission_pct is null;
select last_name,commission_pct from employees where commission_pct is not null;
select salary,last_name from employees where commission_pct is null and salary<18000;
select * from employees where job_id <>'it' or salary=12000;
select 查询列表 from 表 where 筛选条件 order by 排序列表 asc|desc;  asc代表升序,desc代表降序
select * from employees order by salary desc;
select * from employees order by salary ;
select * from employees where department_id>=90 order by hiredate asc;
select * from employees order by salary asc,employee_id desc;
select last_name,salary from employees wheresalary not between 8000 and 17000 order by salary desc
select 函数名(实参列表) from 表;
select concat(upper(last_name),lower(first_name)) 姓名 from employees  upper(变大写) lower(变小写)
select substr('李莫愁爱上陆展元',7) out_put     截取指定索引处字符:substr
select substr('李莫愁爱上陆展元',1,3) out_put;
select length('张三丰hahaha');
select round(-1.55)
select cell(-1.02)
select truncate(1.69999,1)
select now();  返回当前系统日期+时间
select curdate();  系统日期不包含时间
select curtime(); 当前时间，不包含日期
select year(now())年；
select year('1998-1-1')年;
select year(hiredate) 年 from employees;
select month(now())月;
select str_to_date()
str_to_date 将字符转换成日期

简单的条件运算符:> < = != <>不等于 >= <= 
逻辑运算符:&& || ! and or not &&和and两个条件都为true，
||或or:只要一个条件为true,结果为true,反之为false
！或not:如果连接条件本身为false，结果为true，反之false
模糊查询:like,between,and,in,is null,is not null  
in:判断某字段的值是否属于in列表中的某一项
通配符:%所有任意字符，\转义字符，_代表所在位置任意字符
单行函数:concat(拼接字符串),length(获取参数字节个数),ifnull
字符函数length
数学函数round 四舍五入 
向上取整cell  floor向下取整
truncate 截断
日期函数 now
  

create database mydatabase 
create table if not exists mydatabase.student(name varchar(10),gender varchar(10),number varchar(10),age int)charset utf8;  #如果不存在创建student
CREATE TABLE class(NAME VARCHAR(10),room VARCHAR(10))CHARSET utf8;  创建班级表
SHOW TABLES LIKE '%s'  #查询s结尾的表
SHOW CREATE TABLE student;   #查看表创建语句
DESC student；     查看表结构
rename table student to my_student   #修改表名
ALTER TABLE my_student ADD COLUMN ID INT FIRST;    #给表增加ID放在第一位
alter table my_student modify number char(10) after id; #number字段变成固定长度，凡在id之后
alter table my_student change gender sex varchar(10);  #改字段名
alter table my_student drop age; #删除表中age字段
drop table class;  #删除表
insert into my_student values(1,'itcast0001','jim','male'),(2,'itcast0002','Hanmeimei','female'); #插入两行数据
insert into my_student(number,sex,name,id)values('itcast0003','male','tom',3),('itcast0004','female','lily',4) #插入数据指定字段列表
select id,number,sex,name from my_student where id=1   #查看id为1的字段
update my_student set sex='female' where name='jim'    #更新数据
delete from my_student where sex='male';  #删除性别为male数据
show variables like 'character_set%';     #查看服务器支持那些字符集
set character_set_client=gbk;  #修改客服段字符集为gbk
insert into my_student values(5,'itcast0005','张越','男');
数值型-数值型数据都是数值，系统将数值型分为整数型和小数型。
整数型-存放整型数据，在sql中因为更多考虑如何节省磁盘空间，所以系统将整型分为五类
tinyint 迷你整型，使用一个字节存储，表示的状态最多256种
smallint 小整型，使用2个字节存储，表示的状态最多为65536种
mediumint:中整型，使用3个字节存储
int 标准整型，使用4个字节存储
bigint 大整型，使用8个字节存储
create table my_int(int_1 tinyint, int_2 smallint,init3 int,int_4 bigint)charset utf8;    --创建整型表
insert into my_int values(100,100,100,100);  #插入整型数据

Mysql 快速创建账号并授权只读权限

本地访问：

create user username@localhost identified by 'password';

外网访问：

create user 'username'@'%' identified by 'password';
flush privileges;
账号授权database_name数据库所有权限
grant all privileges on `database_name`.* to 'username'@'%' identified by 'password';
flush privileges;
只读权限
grant select on database1.* to 'username'@'%' identified by 'password';
grant select on database2.* to 'username'@'%' identified by 'password';
flush privileges;
-- select 查询权限，all privileges 所有权限或指定select,delete,update,create,drop权限
-- database1 数据库
-- username 用户账号
-- password 密码
-- % 所有电脑可访问

--查询所有数据的大小--
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;

--查看指定数据库的大小--
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='osale_clue';

--查看实例下所有数据库的空间占用情况
select 
     table_schema
    ,concat(round(sum(data_length/1024/1024),2),'MB') as data 
from information_schema.tables 
where table_schema like '%' 
group by table_schema
;



--查看指定数据库的某个表的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data 
from information_schema.tables 
where table_schema='cloud' and table_name='hnpp_member'
;

 查询数据库表的行数

USE information_schema;

SELECT table_name,table_rows FROM TABLES 
WHERE TABLE_SCHEMA = 'osale_clue' 
ORDER BY table_rows DESC; 

二进制日志的保存时间参数binlog_expire_logs_seconds和expire_logs_days的设置 

2592000是30天。binlog_expire_logs_seconds是8.0里面新增的，优先级比expire_logs_days高

关闭二进制日志

SET SQL_LOG_BIN=0;  #重启后失效

mysql5.7设置二进制日志保存天数

expire_logs_days=5;

mysql8.0设置二进制日志保存天数

binlog_expire_logs_seconds = 432000

mysql8.0以下版本

-- mysql8.0以下版本查看当前数据库日志binlog保存时效 以天为单位，默认0 永不过期
show variables like '%expire_logs_days%';
-- mysql8.0以下版本通过设置全局参数expire_logs_days修改binlog保存时效 以天为单位，默认0 永不过期
set global expire_logs_days=5;

mysql.cnf #配置文件内修改后要从其pod或者服务
expire_logs_days=5;

重启服务
kubectl rollout restart StatefulSet percona57-master -n yxyw

 mysql8.0以上版本

-- mysql8.0以下版本查看当前数据库日志binlog保存时效 以秒为单位
show variables like '%binlog_expire_logs_seconds%';
-- mysql8.0以下版本通过设置全局参数binlog_expire_logs_seconds修改binlog保存时间 以秒为单位；默认2592000 30天
-- 14400   4小时；86400  1天；432000  5天；
 set global binlog_expire_logs_seconds=259200;

mysql.cnf #配置文件内修改后要从其pod或者服务
binlog_expire_logs_seconds = 432000

kubectl rollout restart StatefulSet percona80-master -n yxyw

查询慢语句

SELECT * FROM information_schema.processlist