常用MySQL语句

2020-04-16 1 分钟阅读 #MySQL#SQL#SQL#DBA
目录

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

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)

新库新账号

  • 创建数据库
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

常用错误

  • 解决Cannot delete or update a parent row: a foreign key constraint fails报错,即删除带有外键关联的数据
1SET foreign_key_checks = 0;  // 先设置外键约束检查关闭
2
3drop table table1;
4detele from table where ;
5
6// 删除表或数据
7SET foreign_key_checks = 1; // 开启外键约束检查，以保持表结构完整性

相关文章