【非广告】常用SQL语句，看这篇就够了（五）

CREATE TABLE `sequence` (
  `name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '序列的名字',
  `current_value` int(11) NOT NULL COMMENT '序列的当前值',
  `increment` int(11) NOT NULL DEFAULT '1' COMMENT '序列的自增值',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

BEGIN 
     DECLARE value INTEGER; 
     SET value = 0; 
     SELECT current_value INTO value 
          FROM sequence 
          WHERE name = seq_name; 
     RETURN value; 
END

DROP FUNCTION IF EXISTS nextval; 
DELIMITER $ 
CREATE FUNCTION nextval (seq_name VARCHAR(50)) 
     RETURNS INTEGER 
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT '' 
BEGIN 
     UPDATE sequence 
          SET current_value = current_value + increment 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END 
$ 
DELIMITER ;

DROP FUNCTION IF EXISTS setval; 
DELIMITER $ 
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) 
     RETURNS INTEGER 
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT '' 
BEGIN 
     UPDATE sequence 
          SET current_value = value 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END 
$ 
DELIMITER ;

# 添加一个sequence名称和初始值，以及自增幅度
INSERT INTO sequence VALUES ('testSeq', 0, 1);
#设置指定sequence的初始值
SELECT SETVAL('testSeq', 10);
#查询指定sequence的当前值
SELECT CURRVAL('testSeq');
#查询指定sequence的下一个值
SELECT NEXTVAL('testSeq');

aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee

select uuid();

select UUID_SHORT();

select * from mysql.user;

# 格式
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

# 例子，创建一个用户名为admin,密码123456，可以本地访问的用户
CREATE USER 'admin'@'localhost' IDENTIFIED BY '123456';

# 格式
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

# 例子，将用户名admin,密码修改为456789，可以本地访问的用户
SET PASSWORD FOR 'admin'@'localhost' = PASSWORD("456789");

# 格式
DROP USER 'username'@'host';

# 例子，删除用户名为admin的用户
DROP USER 'admin'@'localhost';

#刷新操作使其生效
flush privileges

# 格式
SHOW GRANTS FOR 'username'@'host'

# 查询用户名为 'root'@'%'的权限信息
SHOW GRANTS FOR 'root'@'%'

# 格式
GRANT privileges ON databasename.tablename TO 'username'@'host'

grant all privileges on test_db.* to 'testuser'@'localhost';

grant all privileges on *.* to 'testuser'@'localhost';

grant all privileges on test_db.user_info to 'testuser'@'localhost';

grant all privileges on *.* to 'testuser'@'localhost';

grant select on *.* to 'testuser'@'localhost';

grant select,insert,update,delete on *.* to 'testuser'@'localhost';

grant all privileges on *.* to 'testuser'@'192.168.1.100';

# bind-address = 127.0.0.1

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

grant all privileges on *.* to 'root'@'%'  identified by '123456';

grant all privileges on *.* to 'root'@'localhost'  identified by '123456';

flush privileges;

#例如，创建一个admin用户，密码为admin
grant all privileges on *.* to 'admin'@'%' identified by 'admin';
#刷新MySQL的系统权限相关表方可生效
flush privileges;