mysql数据库操作二

创建索引：

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:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |

+------------------+------------------------------------------------+

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 '\r\n';

Query OK, 3 rows affected (0.02 sec)

导出数据库：

C:\Windows\system32>mysqldump -u root -p tmysql >C:\Users\Motus\Desktop\tmysql.sql

Enter password: ******

只导出数据库表结构（此时要加-d）：

C:\Windows\system32>mysqldump -u root -p -d tmysql >C:\Users\Motus\Desktop\tmysql_table.sql

Enter password: ******

导出某张表结构和数据

C:\Windows\system32>mysqldump -u root -p tmysql stu>C:\Users\Motus\Desktop\tmysql_table_stu.sql

Enter password: ******

导出某张表结构(此时要加-d)

C:\Windows\system32>mysqldump -u root -p -d tmysql stu>C:\Users\Motus\Desktop\tmysql_table_stu_table.sql

Enter password: ******

数据库的导入：

创建数据库：

create database tmysql;

使用数据库：

use tmysql;

导入数据表：

mysql> source C:\Users\Motus\Desktop\tmysql_table_stu_table.sql

Query OK, 0 rows affected (0.00 sec)

导入数据表和数据

mysql> source C:\Users\Motus\Desktop\tmysql_table_stu.sql

导入所有数据表但不带数据

mysql> source C:\Users\Motus\Desktop\tmysql_table.sql

Query OK, 0 rows affected (0.00 sec)

导入所有数据表和数据：

mysql> source C:\Users\Motus\Desktop\tmysql.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)