mysql常用命令以及查询方法总结

引言

写这篇文章的原因是因为我发现我各种地方装的mysql太多了，几台云服务器里有用docker搭建mysql还有redis等，然后服务器本地跑了另外的东西，最近用自己的笔记本电脑办公，发现宿主机同样还有mysql，感觉可以搭一个集群了。。。中间遇到很多非查询命令有些忘记，所以在这里记录一下。

库操作

首先从进入mysql开始，这一部分有连接，授权还有更改密码：

// 1. 登录
mysql -u root -p
// 远程登录
mysql -p IP -u root -p

// 修改密码
mysqld --shared-memory --skip-grant-tables ;
mysql ;
flush privileges ;
alter user "root"@"localhost" identified by "root";

// 授权
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '密码' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON src.* TO 'src'@'%';
// 删除授权
revoke all privileges on *. from root@”%”; 
delete from user where user=”root” and host=”%”; 
flush privileges;

这一部分的演示可以见docker学习笔记（4）：制作mysql镜像与后续相关维护总结。

创建与更新数据库：

// 创建数据库mydb
CREATE DATABASE "mydb" CHARACTER SET utf8 COLLATE utf8_general_ci;
// 更改数据库默认编码类型
ALTER DATABASE "mydb" DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

表操作

建表通用模板：

CREATE TABLE IF NOT EXISTS `Test2`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `xx` VARCHAR(100) NOT NULL,
   `xxx` VARCHAR(40) NOT NULL,
   `xxxx` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

实例解析：

• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
• AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
• PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
• ENGINE 设置存储引擎，CHARSET 设置编码。

导出表：

mysqldump -u user_name -p123456 database_name > outfile_name.sql  // 导出数据库
mysqldump -u user_name -p database_name table_name > outfile_name.sql	// 导出数据表

导出文件默认是存在mysql\bin目录下，也可以自己指定路径。

source "路径"+/world.sql

导入数据或者从txt文本中加载数据：

load data infile "C:/Users/Desktop/123.txt" into world;		// 按原格式插入

LOAD DATA LOCAL INFILE "C:/Users/Desktop/123.txt" INTO TABLE world fields terminated by ',';	// 以","分隔插入

fields关键字指定了文件记段的分割格式，如果用到这个关键字，MySQL剖析器希望看到至少有下面的一个选项：

• terminated by 以什么字符作为分隔符
• enclosed by 字段闭合标签
• escaped by 转义字符
• terminated by 描述字段的分隔符，默认情况下是tab字符（\t）
• enclosed by 描述的是字段的括起字符。
• escaped by 描述的转义字符。默认的是反斜杠（backslash：\ ）
  

查看表结构与数据

desc world	// 查看表的结构

create table table2 select * from table1 where 1<>1;	// 复制已有表的结构给表2

SHOW CREATE TABLE <表名>\G； // 显示创建表时的SQL语句

使用 DESCRIBE 查看表 world的结构，输入的 SQL 语句和运行结果如下所示。

mysql> desc world;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name       | varchar(50) | YES  |     | NULL    |       |
| continent  | varchar(50) | YES  |     | NULL    |       |
| area       | int(11)     | YES  |     | NULL    |       |
| population | int(11)     | YES  |     | NULL    |       |
| gdp        | int(11)     | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

其中，各个字段的含义如下：

• Null：表示该列是否可以存储 NULL 值。
• Key：表示该列是否已编制索引。PRI 表示该列是表主键的一部分，UNI 表示该列是 UNIQUE 索引的一部分，MUL 表示在列中某个给定值允许出现多次。
• Default：表示该列是否有默认值，如果有，值是多少。
• Extra：表示可以获取的与给定列有关的附加信息，如 AUTO_INCREMENT 等。

insert插入数据三种方式：

// insert插入多条数据
INSERT INTO table_name(field1, field2,...fieldN)  VALUES  (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......;

// 如果所有的列都要添加数据可以不规定列进行添加数据
INSERT INTO "world" VALUES(Andorra, Abania,2381741, 20609294);

// 列名列值独立
INSERT INTO tablename SET column_name1 = value1, column_name2 = value2，…;

关于其它用得多的命令可以查看如下两篇博文：

mysql（3）：查询基础总结

mysql总结(一)：思维导图

补充

未完待续