创建索引：
mysql> create index stu_stuid on stu (stuid);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table dept add index dept_id (deptId);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除索引：
mysql> drop index stu_stuid on stu;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index dept_id on dept;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
临时表：创建 插入数据 删除临时表
mysql> create temporary table test(
-> testid int primary key,
-> testname varchar(20) not null);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test (testid,testname) values (1,'test');
Query OK, 1 row affected (0.00 sec)
mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)
创建复制表：
mysql> create table stu1(
-> stuid int primary key,
-> stuname varchar(50) not null,
-> studept varchar(20)) engine=InnoDb;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into stu1 (stuid,stuname,studept)
-> select stuid,stuname,studept from stu;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select *from stu1;
+-------+---------+---------+
| stuid | stuname | studept |
+-------+---------+---------+
| 20211 | 小魏 | cs |
| 20212 | 小王 | kj |
| 20214 | 小刘 | gc |
+-------+---------+---------+
3 rows in set (0.00 sec)
导出数据：
mysql> show variables like "secure_file_priv";
+------------------+------------------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------------------+
| secure_file_priv | C:ProgramDataMySQLMySQL Server 8.0Uploads |
+------------------+------------------------------------------------+
1 row in set, 1 warning (0.01 sec)
如果value值为null，则为禁止，如果有文件夹目录，则只允许改目录下文件（测试子目录也不行），如果为空，则不限制目录；
更改my.ini
secure-file-priv=""
mysql> select * from stu into outfile 'D:/cate.txt'
-> fields terminated by ','
-> lines terminated by 'rn';
Query OK, 3 rows affected (0.02 sec)
导出数据库：
C:Windowssystem32>mysqldump -u root -p tmysql >C:UsersMotusDesktoptmysql.sql
Enter password: ******
只导出数据库表结构（此时要加-d）：
C:Windowssystem32>mysqldump -u root -p -d tmysql >C:UsersMotusDesktoptmysql_table.sql
Enter password: ******
导出某张表结构和数据
C:Windowssystem32>mysqldump -u root -p tmysql stu>C:UsersMotusDesktoptmysql_table_stu.sql
Enter password: ******
导出某张表结构(此时要加-d)
C:Windowssystem32>mysqldump -u root -p -d tmysql stu>C:UsersMotusDesktoptmysql_table_stu_table.sql
Enter password: ******
数据库的导入：
创建数据库：
create database tmysql;
使用数据库：
use tmysql;
导入数据表：
mysql> source C:UsersMotusDesktoptmysql_table_stu_table.sql
Query OK, 0 rows affected (0.00 sec)
导入数据表和数据
mysql> source C:UsersMotusDesktoptmysql_table_stu.sql
导入所有数据表但不带数据
mysql> source C:UsersMotusDesktoptmysql_table.sql
Query OK, 0 rows affected (0.00 sec)
导入所有数据表和数据：
mysql> source C:UsersMotusDesktoptmysql.sql
Query OK, 0 rows affected (0.00 sec)
使用使用 LOAD DATA 导入数据：
mysql> SET GLOBAL local_infile=1; Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'local_infile';
mysql --local-infile=1 -u root -p
Enter password: ******
mysql> use tmysql;
Database changed
mysql> load data local infile 'D:\tstu.txt' into table stu
-> fields terminated by ';'
-> lines terminated by 'n';
Query OK, 3 rows affected (0.00 sec)
