本篇是对MySql系列-环境搭建、常用命令、数据类型的常用命令部分的补充总结

基础知识-常用命令

命令不区分大小写

1、mysql连接

mysql -u username -p

实例:

mysql -u root -p

2、元数据查询

//服务器版本信息
SELECT VERSION( ) 
//当前数据库名 (或者返回空)
SELECT DATABASE( ) 
//当前用户名
SELECT USER( ) 
//服务器状态
SHOW STATUS 
//服务器配置变量
SHOW VARIABLES

3、数据库操作

1）创建数据库

语法

CREATE DATABASE 数据库名;

2）查询数据库

语法

SHOW DATABASES;

实例：

3）删除数据库

drop database <数据库名>;

实例：

4）使用数据库

use DATABASE;

实例：

4、创建数据表

语法

CREATE TABLE table_name (column_name column_type);

或者顺便指定索引

CREATE TABLE table_name (
      column1_name data_type,
      column2_name data_type,
      ...,
      INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);

实例：

CREATE TABLE IF NOT EXISTS `User`(
      `user_id` INT UNSIGNED AUTO_INCREMENT,
      `user_name` VARCHAR(100) NOT NULL,
      `user_sex` VARCHAR(10) NOT NULL,
      `insert_date` DATE,
      PRIMARY KEY ( `user_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

5、删除数据表

语法

DROP TABLE table_name ;

实例：

DROP TABLE User;

6、修改数据表结构

在进行重要的结构修改时，建议先备份数据，并在生产环境中谨慎操作,因为修改时会影响到数据库的性能和运行时间。

1）添加新字段

ALTER TABLE table_name
ADD column_name data_type;

2）修改字段类型

ALTER TABLE table_name
MODIFY column_name new_data_type;

3）修改字段名称

ALTER TABLE table_name
CHANGE old_column_name new_column_name data_type;

4）删除字段

ALTER TABLE table_name
DROP column_name;

5）添加主键约束

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

6）添加外键约束

ALTER TABLE table_name
ADD FOREIGN KEY (column_name) REFERENCES referenced_table(ref_column_name);

7）添加普通索引

ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

或者

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

8）添加唯一索引

ALTER TABLE table_name
ADD UNIQUE INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

或者

CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

9）删除索引

ALTER TABLE table_name
DROP INDEX index_name;

或者

DROP INDEX index_name ON table_name;

10）重命名表

ALTER TABLE old_table_name
RENAME TO new_table_name;

11）修改表的存储引擎

ALTER TABLE table_name ENGINE = new_storage_engine;

7、复制数据表

如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用CREATE TABLE ... SELECT 命令，是无法实现的。下面介绍如何完全复制一张表

原始表是User，复制一个新的表叫User_2

1）获取数据表User的建表语句。
命令：SHOW CREATE TABLE User;

2）修改SQL语句的数据表名为User_2，并执行SQL语句。

CREATE TABLE `user_2` (
      `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `user_name` varchar(100) NOT NULL,
      `user_sex` varchar(10) NOT NULL,
      `insert_date` date DEFAULT NULL,
      PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

此时表user_2的结构和原始表user完全一致，但是没有数据。

3）使用INSERT INTO... SELECT 语句拷贝表中的数据

命令：

INSERT INTO User_2(user_id,user_name,user_sex,insert_date) select user_id,user_name,user_sex,insert_date from user;

通过上面三个步骤，就会完整的复制表的内容，包括表结构及表数据。

8、插入数据行

语法

INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );

实例：

INSERT INTO User(user_name,user_sex,insert_date) VALUES('admin','男','2023-08-29');

9、查询数据行

语法

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

实例：

SELECT user_name,user_sex FROM User WHERE user_name='admin' LIMIT 1;

10、更新数据行

语法

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

实例：

UPDATE User SET user_sex='女' where user_name='admin';

11、删除数据行

语法

DELETE FROM table_name [WHERE Clause]

实例：

DELETE FROM User WHERE user_name='admin';

12、模糊查询LIKE

语法

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

LIKE 通常与 % 一同使用，类似于一个元字符的搜索

实例：

SELECT *FROM User WHERE user_name LIKE '%ad%';

13、联合查询UNION

语法

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

UNION ALL: 返回所有结果集，包含重复数据。
UNION: 返回所有结果集，不包含重复数据。

14、排序

语法

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

实例：

//按插入时间升序
SELECT * from User ORDER BY insert_date ASC;

15、分组

语法

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

实例：

//按用户名分组
SELECT user_name,count(1) from User GROUP BY user_name

16、多表连接查询

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

我们可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

语法：

SELECT a.x,b.y from a inner join b on a.id = b.a_id

17、正则表达式

MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

实例：

//查找user_name字段中以'st'为开头的所有数据：
SELECT user_name FROM User WHERE user_name REGEXP '^ad';

18、事务控制

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

语法：

//MYSQL 事务处理主要有两种方法：
//1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
//2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

实例：

19、数据导出

可以使用 SELECT ... INTO OUTFILE 语句导出数据

实例：

select *from user into outfile 'D:\\mysql-5.7.43-winx64\\outfile';

直接执行上述导出会报错：

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

看字面意思是和--secure-file-pri这个变量有关系，我们看下这个变量的设置是什么：

show variables like '%secure%';

可以看到这个值是NULL, 查阅资料得知

secure-file-pri这个变量被用于限制数据导入的导出操作，诸如执行LOAD DATA以及SELECT ... INTO OUTFILE操作以及LOAD_FILE()函数。 
这个secure_file_priv可以设置的值有三个可选项：
- If empty, the variable has no effect. This is not a secure setting. 【如果此配置项值为空，则表示没有安全设置】
- 如果设置为目录名，则服务器将限制导入和导出操作，使其仅适用于该目录中的文件。该目录必须存在；服务器将不会创建它。
- 如果设置为NULL，则服务器将禁用导入和导出操作。从MySQL 5.7.6开始允许使用此值

根据上述的NULL值，可以看到是不允许导出（入）到文件。

解决办法就是在配置文件中，设置secure-file-priv为某个路径即可，如下：

注意配置前一定要加一行[mysqld]

最后要想配置生效，需要重启mysql服务，注意是重启服务！不是退出重新登录！

配置生效后，重新执行前面的导出命令即可导出数据

20、数据导入

1）使用mysql命令导入
语法：

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

以上命令将将备份的整个数据库 data.sql 导入。

2）使用source命令导入

source 命令导入数据库需要先登录到数库终端

导入步骤：

mysql> create database gyd; # 创建数据库
mysql> use gyd; # 使用已创建的数据库 
mysql> set names utf8; # 设置编码
mysql> source /home/gyd/gyd.sql # 导入备份数据库

3）使用 LOAD DATA 导入数据

以下实例中将从当前目录中读取文件 outfile.txt ，将该文件中的数据插入到当前数据库的 user 表中。

mysql> LOAD DATA LOCAL INFILE 'outfile.txt' INTO TABLE user;

LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。

如，在数据文件中的列顺序是 user_sex,user_name, insert_date，但在插入表的列顺序为user_name, user_sex, insert_date，则数据导入语法如下：

mysql> LOAD DATA LOCAL INFILE 'outfile.txt' 
-> INTO TABLE user (user_name, user_sex, insert_date);

4）使用 mysqlimport 导入数据

$ mysqlimport -u root -p --local user outfile.txt
password *****

mysqlimport有很多可选项，有兴趣的可以自行查资料啦！

mysql还有很多命令，小郭会慢慢的补充进来。