一、数据库操作

1、连接数据库

mysql -u -p -h -P

-u：指定用户名

-p：指定密码

-h：主机

-P：端口

2、修改远程访问权限

//修改my.ini内mysql运行端口配置为非3306

port=3306

//连接数据库

mysql -u root -p

//选择数据表

use mysql;

//修改远程访问权限，"%"指的是所有地址

update user set Host="%" where User="root";

//刷新配置

flush privileges

//查看配置结果

select host, user from user where user="root";

//成功状态

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

| host      | user          |

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

| %         | root          |

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

3、创建数据库

//创建数据库db_test

create database db_test;

//如果数据库不存在则创建，存在则不创建。

//创建db_test数据库，并设定编码集为utf8 (这个很重要，如果不设置utf8的话，中文支持可能会有点问题。)

create database if not exists db_test default charset utf8 collate utf8_general_ci;

4、删除数据库

drop命令删除数据库

drop database db_test;

命令作用：

1. 删除名为db_test的数据库

5、选择数据库

use db_test;

命令作用：

1. 选择名为db_test的数据库。

6、备份数据库

mysqldump -uroot -p db_test > d:\db_test.sql

命令作用：

1. 对db_test的数据库进行备份至d:\db_test.sql文件。

二、数据表操作

1、创建数据表

命令格式：

create table tb_name(column_name column_type);

建表例句：

create table if not exists tb_name1(

    id int not null auto_increment,

    title varchar(300) null,

    content longtext,

    hits int default 0,

    dt datetime default now(),

    primary key(id)

)engine = innoDB default charset=utf8;

命令作用：

1. 创建一个名称为tb_name1的数据表
2. auto_increment定义id为自增属性
3. primary key 关键字是用于定义id为主键字段。如果多个的话，可以用逗号分隔开。
4. 设置dt为日期时间字段，默认值为当前日期时间(now())
5. default 设置hits为int类型字段，默认值为0。
6. ENGINE 设置存储引擎，CHARSET 设置编码。

2、删除数据表

命令格式：

drop table table_name;

3、修改数据表

//修改表名称

alter table oldtable rename newtable;

4、查看表结构

show columns from tb_info;

或是

describe tb_info;

三、其他

//查看mysql版本

select version();

四、数据导入导出

1、导出数据

修改my.ini文件，增加以下行保存，

secure_file_priv=

重启数据库

//可以使用以下语句查看参数

show variables like "%secure%";

//导出语句

select * from tb_name into outfile "d:\\exportFile.txt"

或是

select * from tb_info limit 10 into outfile "d:\\exportfile.csv"

fields terminated by ‘,’ enclosed by ‘"’

lines terminated by ‘\r\n’;

备份数据表表结构及数据

mysqldump -u root -p 数据库名 数据表名 > d:\\table.txt

备份数据库所有表结构及数据

mysqldump -u root -p 数据库名 > d:\\database.txt

备份所有数据库

mysqldump -u root -p --all-databases > d:\\database_dump.txt

将远程服务器数据备份到本地

mysqldump -h other-host.com -P port -u root -p database_name > d:\\dump.txt

2、导入数据

mysql命令导入数据

mysql -u用户名 -p密码 databasename < 要导入的数据库数据(dump.txt)

source导入数据

mysql> create database abc;      # 创建数据库

mysql> use abc;                  # 使用已创建的数据库 

mysql> set names utf8;           # 设置编码

mysql> source d:\\dump.txt  # 导入备份数据库

3、MySQL 导出数据为csv格式文件

SELECT * FROM mytable 

INTO OUTFILE 'd:\\mytable.csv' 

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n';

如果遇到类似于身份证之类的较长的数字导出来的结果会转换成科学记数法的，可以使用CONCAT(p_code,"\t")这样的方式添加制表符后就可以解决。

select id,concat(p_code,"\t"),p_name,p_type,dz,dt,url from tb_tender_info where content is not null

五、mysql查看表结构的三种方法总结

mysql查看表结构

1. show create table

show create table 表名称

2. desc

desc 表名称

3. information_schema.COLUMNS

select * from information_schema.COLUMNS where TABLE_SCHEMA = '库名称' and TABLE_NAME = '表名称'

附：information_schema.COLUMNS字段和含义

字段名称及含义

TABLE_CATALOG表类型（没搞懂干啥用？）

TABLE_SCHEMA所属库名称

TABLE_NAME表名称

COLUMN_NAME字段名称

ORDINAL_POSITION位置序号

COLUMN_DEFAULT默认值

IS_NULLABLE是否可为空

DATA_TYPE数据类型

CHARACTER_MAXIMUM_LENGTH字符串最大长度（数值类型为空）

CHARACTER_OCTET_LENGTH字符串最大存储长度（一般与上一字段相同）

NUMERIC_PRECISION数值精度（非数值类型为空）

NUMERIC_SCALE数值小数位数（非数值类型为空）

DATETIME_PRECISION日期精度

CHARACTER_SET_NAME编码方式

COLLATION_NAME排序方式

COLUMN_TYPE字段类型

COLUMN_KEY字段涉及的key（主键、唯一键等）

EXTRA其他（如 auto_increment）

PRIVILEGES权限

COLUMN_COMMENT字段注释

GENERATION_EXPRESSION代表达式

获取所有的表结构及备注

1、根据库名导出所有表信息

SELECT

    *

FROM

    information_schema.`TABLES`

WHERE

    TABLE_SCHEMA = 'db_name'

2、根据库名导出所有表名及表备注

SELECT

    TABLE_NAME,

    TABLE_COMMENT

FROM

    information_schema.`TABLES`

WHERE

    TABLE_SCHEMA = 'db_name';

3、mysql获取整个库的所有表，及表结构

SELECT

    TABLE_SCHEMA AS '库名',

    TABLE_NAME AS '表名',

    COLUMN_NAME AS '列名',

    ORDINAL_POSITION AS '列的排列顺序',

    COLUMN_DEFAULT AS '默认值',

    IS_NULLABLE AS '是否为空',

    DATA_TYPE AS '数据类型',

    CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',

    NUMERIC_PRECISION AS '数值精度(最大位数)',

    NUMERIC_SCALE AS '小数精度',

    COLUMN_TYPE AS '列类型',

    COLUMN_KEY 'KEY',

    EXTRA AS '额外说明',

    COLUMN_COMMENT AS '注释'

FROM

    information_schema.`COLUMNS`

WHERE

    TABLE_SCHEMA = 'db_name'

ORDER BY

    TABLE_NAME,

    ORDINAL_POSITION;