MySQL创建数据库和表

创建库

常用sql语句

# 创建一个数据库teachers,不指定字符集,默认拉丁
CREATE DATABASE blog;

# 创建数据库,并指定默认字符集为utf8,注意utf8没有'-'
> CREATE DATABASE blog DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

# 创建库时判断是否存在
> CREATE DATABASE IF NOT EXISTS blog;

# 查看全局变量中定义的各个字符集的语言
> SHOW GLOBAL VARIABLES LIKE '%char%';

# 修改表字符集
> ALTER TABLE blog CONVERT TO CHARACTER SET utf8;

# 设置字符集语言为utf8
> CHARACTER SET utf8

# 更改数据库默认字符集
> ALTER DATABASE blog DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

# 更新升级数据字典
update data directory NAME;
# 数据字典：system catalog：保存了数据库服务器上的元数据，通过数据库保存

# vim /etc/my.cnf
"""
[client] # 修改客户端字符集
default-character-set=utf8

[mysqld] # 修改服务端字符集
default-character-set=utf8 # 5.1- 版本
character-set-server=utf8 # 5.5+ 版本
"""

# 查看所有库
mysql> SHOW DATABASES;

# 查看数据库信息
mysql> SHOW CREATE DATABASE teachers;

# 查看字符集
mysql> SHOW VARIABLES LIKE 'character%';

# 修改数据库字符集
mysql> ALTER DATABASE db_name CHARACTER SET utf8;
# service mysqld restart

# 修改表字符集
mysql> ALTER TABLE table_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

# 备份所有库和表-all-databases
mysqldump -uroot -p -all-databases > backupname1.sql

# 备份指定库指定表
mysqldump -uroot -p db_name1 table_name1 table_name2 > backupname2.sql
mysqldump -uroot -p --databases db_name1 db_name2 db_name3 > backupname3.sql

# 删除数据库：
mysql> drop database db_name；
mysql> drop database if exists db_name;

# 还原所有库
# mysql -uroot -p < backupname1.sql

# 还原指定库的备份表
# mysql -uroot -p db_name1 < backupname2.sql

修改已有数据的库的字符集,需要将数据线导出,修改完后,再导入

比如:当前字符集latinl,要修改为utf8

1,导出库及表结构

2,sed批量修改导出文件的字符集为utf8

3,导出所有的数据

4,修改mysql服务端和客户端字符编码为utf8

5,删除库和表

6,导入库及表结构

7,导入数据

创建授权用户

mysql> SELECT user,host,password FROM mysql.user;
mysql> DELETE FROM mysql.user WHERE user='';
mysql> DELETE FROM mysql.user WHERE host='::1';
mysql> USE mysql;
mysql> GRANT ALL PRIVILEGES ON *.* TO root@"localhost" IDENTIFIED BY "123456";
mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "123456";
mysql> GRANT ALL PRIVILEGES ON *.* TO root@"127.0.0.1" IDENTIFIED BY "123456";
mysql> GRANT ALL PRIVILEGES ON blog.* TO 'bloguser'@"%" IDENTIFIED BY "blogpwd";
mysql> FLUSH PRIVILEGES;
> \q