mysql创建数据库

1、mysql 8.0 创建数据库

• create database db_dev;

• Query OK, 1 row affected (0.00 sec)

2、创建用户

• create user 'test' identified by 'test';

• Query OK, 0 rows affected (0.02 sec)

• grant all on db_dev.* to test@'%' ;

• Query OK, 0 rows affected (0.00 sec)

3、设置默认字符集，默认排序顺序

• alter database db_dev DEFAULT CHARACTER SET utf8mb4;

• alter database db_dev DEFAULT COLLATE utf8mb4_bin;

4、查看本地安装的数据库

• show databases;

5、数据库脚本导入

/home/mysql/backupsql/mysql8.0/dev_sql.sql

• cd /home/mysql/backupsql/mysql8.0/dev_sql.sql

• mysql -utest -ptest db_dev <dev_sql.sql;

6、常用SQL脚本

查询定义者

  > select definer from information_schema.VIEWS v;

修改视图定义者

导入视图报错，需修改视图中的用户名，重新执行。

  > select concat("alter DEFINER=test@'%' SQL_SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION ,";") from information_schema.VIEWS where definer<>'test';
  > 复制视图重新执行

7、mysql 生效命令

• cd /home/mysql

• source .bash_file

8、连接mysql命令窗口

• mysql -utest -ptest