mysql md 123456_mysql用户等.md

```sql

#查询

select * from sales for update ;

select * from sales ;

show databases ;

show tables;

#插入

insert into sales (id, year, country, product, profit)

values (20,2020,'china','computer',100);

# distinct ,先distinct 然后在limit

select * from sales order by id limit 1,1;

select distinct year from sales limit 1,1;

select distinct year from sales limit 1 offset 1;

# uuid

select upper(replace(uuid(),'-',''));

select uuid();

# if null

select if(product='Phone','mobile',product) from sales;

#设置id

update sales set ids=uuid();

#dense，窗口函数

select country,profit, dense_rank() over (partition by country order by profit) rk,id

from sales;

#

select country,profit, row_number() over (partition by country order by profit) rk,id

from sales;

#

select country,profit, rank() over (partition by country order by profit) rk,id

from sales;

#新建用户

select * from mysql.user;

select Host,User,authentication_string from mysql.user ;

# use mysql;

# use leetcode;

create user xsj@'localhost' identified by '123456';

create user xsj2@'localhost' identified by '123456';

create user xsj3@'localhost' identified by '123456';

flush privileges ;

# 直接插入数据库的方式新建用户，密码没有加密

insert into mysql.user(Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject)

values ('localhost','shengjie','123456','','','');

flush privileges ;

# SHOW GRANTS FOR 'root'@'localhost';

drop user xsj2@'localhost';

drop user xsj3@'localhost';

drop user shengjie@'localhost';

flush privileges;

#给用户赋予权限

grant insert,update,select ON leetcode.*

To xsj@'localhost' identified by 'shengjie';

show databases ;

grant select, insert, update, delete on *.* to shengjie@'%' identified by '123456';

select host,User,authentication_string

from mysql.user where User like 'x%';

create user a1 identified by '123',a2 identified by '123';

drop user a1,a2;

create user xsj@'localhost' identified by '123456';

flush privileges;

# with grant option 可以将自己的权限赋予别

grant select,update,insert on leetcode.* to xsj@'localhost' with grant option ;

# 这个是只能在本地连接把

create user leet@'localhost' identified by 'leet';

grant select,update,insert on leetcode.* to leet@'localhost' ;

# 修改密码

set password for xsj@'localhost' = 'shengjie';

flush privileges ;

set password for leet@'localhost' ='123456';

select host,User,authentication_string from mysql.user;

# 显示用户权限

show grants for leet@'localhost';

show grants for root@'%';

#

grant update,select,insert,delete on leetcode.* to leet@'localhost' with grant option ;

show grants for leet@'localhost';

# 删除权限

revoke update on leetcode.* from leet@'localhost';

flush privileges;

revoke all privileges on leetcode.* from leet@'localhost';

revoke grant option on leetcode.* from leet@'localhost';

revoke all privileges ,grant option from leet@'localhost';

# 时间日期

select now() +10;

select date_add(now(),interval 7 year );

select date_add(now(),interval 7 hour );

select datediff(date_add(now(),interval 7 day ),now());

select year(now());

select date (now());

select timestamp (now());

select time(now());

select date_format(now(),'%Y-%m-%d %H:%i:%S');

# group，group_concat

select country,group_concat(product,'-',year) from sales group by country with rollup ;

select country,group_concat(product,'-',year) from sales group by country ;

select ifnull(year,1),ifnull(country,1),group_concat(year),group_concat(country) from sales group by year,country with rollup;

# 视图 关键词as

create view sales_group3 as select year,country,group_concat(country) from sales group by year, country;

# select * from sales_group3;

describe sales_group;

show tables ;

#

drop view sales_group3;

select * from sales_group;

# 普通索引

create index sales_id on sales(id);

# 唯一索引

CREATE UNIQUE INDEX index_id ON sales(id);

create index country_4 on sales(country(4));

# 显示索引

show index from sales;

drop index index_id on sales;

EXPLAIN select * from sales where country like 'c%';

EXPLAIN select * from sales where id=2;

EXPLAIN select now();

select now();

SHOW VARIABLES LIKE 'character%';

SHOW VARIABLES LIKE 'collation_server';

select Host,User from mysql.user;

# 触发器

create trigger ids_trigger

before insert on sales

for each ROW

BEGIN

SET new.ids=REPLACE(UUID(),'-','');

END;

#备份

use seasons;

show tables;

use leetcode;

select * from sales;

select * from sales into outfile 'my.txt';

SHOW VARIABLES LIKE 'log_error';

SHOW VARIABLES LIKE 'log_bin';

SHOW binary logs;

SHOW VARIABLES LIKE '%general%';

```

一键复制

编辑

Web IDE

原始数据

按行查看

历史