mysql常用SQL语句

1.新建数据库

CREATE DATABASE dbName;

2.修改数据库名称

a.RENAME DATABASE dbName TO newDbName
这个语法在mysql 5.1.7中被添加进来，到了5.1.23又去掉了。
据说有可能丢失数据。还是不要用的好。

b.如果所有表都是MyISAM类型的话，可以改文件夹的名字
关闭mysqld
把data目录中的dbName目录重命名为newDbName
开启mysqld

c.重命名所有的表
CREATE DATABASE newDbName;
RENAME TABLE dbName.table1 TO newDbName.table1,dbName.table2 TO newDbName.table2;
DROP DATABASE dbName;

3.建表

CREATE TABLE tableName (
  uid BIGINT UNSIGNED NOT NULL DEFAULT 0,
  name VARCHAR(32) NOT NULL DEFAULT '',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (uid),
  KEY (name)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

4.修改字段类型

 ALTER TABLE tableName MODIFY COLUMN columnName TINYINT NOT NULL;

5.修改字段默认值

ALTER TABLE tableName ALTER COLUMN columnName SET DEFAULT '';

6.修改字段名

ALTER TABLE tableName CHANGE columnName newColumnName INT UNSIGNED NOT NULL DEFAULT 0;

7.新增字段

ALTER TABLE tableName ADD columnName INT UNSIGNED NOT NULL DEFAULT 0;

8.删除字段

ALTER TABLE tableName DROP COLUMN columnName;

9.修改表名

ALTER TABLE tableName RENAME newTableName;

查看表索引

SHOW INDEX FROM tableName;
SHOW KEYS FROM tableName;

创建索引

CREATE INDEX indexName ON tableName(columnName)；——创建普通索引
CREATE UNIQUE INDEX indexName ON tableName(columnName)；——创建唯一索引
ALTER TABLE tableName ADD PRIMARY KEY (columnName);——添加主键索引

删除索引

DROP INDEX indexName ON talbeName;
ALTER TABLE tableName DROP INDEX indexName;
ALTER TABLE tableName DROP PRIMARY KEY;

13.修改root用户密码

set password for 'root'@'localhost'=password('newpassword');
如果是当前登陆用户用SET PASSWORD = PASSWORD("newpassword"); 

丢失root密码的时候
mysqld_safe --skip-grant-tables&
mysql -u root mysql
mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='root';
mysql> FLUSH PRIVILEGES;

14.查看mysql中主机、用户、密码

select host,user,password from mysql.user;

15.新建mysql用户

create user 'username'@'host' identified by 'password';

username - 你将创建的用户名
host - 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%.
password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.

CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456'; 
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456'; 
CREATE USER 'pig'@'%' IDENTIFIED BY '123456'; 
CREATE USER 'pig'@'%' IDENTIFIED BY ''; 
CREATE USER 'pig'@'%';

16.删除用户

DROP USER 'username'@'host';

17.授权

GRANT privileges ON databasename.tablename TO 'username'@'host'

privileges - 用户的操作权限,如SELECT/INSERT/UPDATE/DELETE/ALL等
databasename - 数据库名
tablename-表名
如果要授予该用户对所有数据库和表的相应操作权限则可用表示, 如.*

GRANT SELECT, INSERT ON test.user TO 'pig'@'%'; 
GRANT ALL ON *.* TO 'pig'@'%'; 

注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令: 
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

18.撤销用户权限

REVOKE privilege ON databasename.tablename FROM 'username'@'host'; 
REVOKE SELECT ON *.* FROM 'pig'@'%';

假如你在给用户'pig'@'%'授权的时候是这样的(或类似的):
GRANT SELECT ON test.user TO 'pig'@'%'
 则在使用REVOKE SELECT ON *.* FROM 'pig'@'%'命令时，并不能撤销该用户对test数据库中user表的SELECT 操作；
相反,如果授权使用的是
GRANT SELECT ON *.* TO 'pig'@'%';
则REVOKE SELECT ON test.user FROM 'pig'@'%'命令也不能撤销该用户对test数据库中user表的Select 权限.

19.查看用户权限信息

SHOW GRANTS FOR 'pig'@'%'

20.修改密码时提示错误

image.png

发生这一错误的原因是在变更了mysql.user表之后，没有使用FLUSH PRIVILEGES命令来更新权限表
FLUSH PRIVILEGES;

21.mysql查询时中文显示乱码问题

set names 'utf8';

22.mysql刷新

flush privileges

23.查看正在执行的SQL，终止进程

show processlist;

image.png

终止进程
kill 62;

24.临时修改接受数据包大小上限

set global max_allowed_packet=2*1024*1024*10;
show VARIABLES like '%max_allowed_packet%';