常用MySQL语句

目录

创建可以无主机限制的账号

1create USER 'clibing'@'%' IDENTIFIED BY 'ty88yYMQH0udg1pC';

创建数据库

1CREATE SCHEMA IF NOT EXISTS `clibing` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

授权所有数据库权限

1grant all on *.* to 'clibing'@'%';

撤销授权所有数据库权限

1revoke all on *.* from 'clibing'@'%';

只授权对clibing数据库的所有权限

1grant all on clibing.* to 'clibing'@'%';

只授权对clibing数据库的select权限

1grant select on clibing.* to 'clibing'@'%'

修改密码

1update user set password=password('123456') where user='root' and host='%';  
1flush privileges;

SET PASSWORD FOR ‘root’@'%' = PASSWORD(‘111111’);

fulltext

1CREATE TABLE articles (
2    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
3    title VARCHAR (200),
4    body TEXT,
5    FULLTEXT (title, body) WITH PARSER ngram
6) ENGINE = INNODB;
7
8select * from articles where match(title, body) against('key1 key2' in natural language mode)
9

新库新账号

  • 创建数据库
1CREATE SCHEMA `TestDB` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
  • 创建用户
1create USER 'test-db'@'%' IDENTIFIED BY '123456';

如果出现一下提示,说明已经存在,use mysql; select user, host from user;进行查看确认,

删除用户 drop user 'test-db'@'%', 删除后一定要执行 flush privileges;

1ERROR 1396 (HY000): Operation CREATE USER failed for 'test-db'@'%'
  • 授权
1 grant all on TestDB.* to 'test-db'@'%';
  • 验证
1mysql -u 'test-db' -p
2
3密码上面设置的 123456