MySQL常用SQL语句小结

一、SQL基础操作

1、创建数据库

#创建数据库
CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8;

#或
CREATE SCHEMA 数据库名 DEFAULT CHARACTER SET utf8;

2、创建表

DROP TABLE IF EXISTS `learn`;
CREATE TABLE `learn` (
	id INT,
	course VARCHAR(32),
	chapters TINYINT,
	activity VARCHAR(32),
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、常用语句

#修改显示宽度
ALTER TABLE `learn` CHANGE COLUMN `id` `id` INT(3) UNSIGNED ZEROFILL NOT NULL ;

#显示宽度
ALTER TABLE `learn` CHANGE COLUMN `id` `id` INT(11) ZEROFILL NOT NULL ;

#添加数据(中文乱码)
INSERT INTO learn(id, course, chapters)VALUES(6, '\xF0\x9F\x92\x94', 3);

#修改字符编码
ALTER TABLE learn CHANGE COLUMN `course` `course` VARCHAR(32) CHARACTER SET 'utf8mb4' NULL DEFAULT NUL
L;

#添加数据
INSERT INTO learn(id, course, chapters, activity)VALUES(1, '5G 时代-音视频高级开发', 3,'');
INSERT INTO learn(id, course, chapters, activity)VALUES(2, 'C/C++Linux 服务器开发/后台架构师', 5, '');
INSERT INTO learn(id, course, chapters, activity)VALUES(3, '大学生春招秋招 Linux C/C++开发就业课程', 3, '');

#修改数据
UPDATE learn SET chapters = 5 WHERE id = 1;

#删除数据
DELETE FROM learn WHERE id = 3;

#查询数据
SELECT * FROM learn;

#修改字段名
ALTER TABLE `learn` CHANGE COLUMN `chapters` `chapter_count` TINYINT NULL DEFAULT 0;

#增加字段
ALTER TABLE `learn` ADD COLUMN `price` DECIMAL(8,2) NOT NULL DEFAULT 0 AFTER `chapter_count`;

#删除字段
ALTER TABLE `learn` DROP COLUMN `price`

二、MySQL连接信息

1、查看当前连接

show status like 'Threads%';

Threads_connected 跟 show processlist 结 果 相 同 ， 表 示 当 前 连 接 数 。 准 确 的 来 说 ，Threads_running 是代表当前并发数。

2、查询数据库当前设置的最大连接数

show variables like '%max_connections%';

3、显示连接列表

 show processlist;

4、显示连接状态

 SHOW STATUS LIKE '%connect%';

三、MySQL定时任务

自 MySQL5.1.6 起， 增加了一个非常有特色的功能-事件调度器（Event Scheduler）， 可以用做定时执行某些特定任务（例如： 删除记录、 对数据进行汇总、 数据备份等等），来取代原先只能由操作系统的计划任务来执行的工作。

事件调度器可以精确到每秒钟执行一个任务， 而操作系统的计划任务（ 如： Linux 的 cron 或 Windows 下的任务计划） 只能精确到每分钟执行一次。 对于一些对数据实时性要求比较高的应用（ 例如： 股票、赔率、 比分等） 就非常适合。

事件调度器有时也可以称为临时触发器（ temporal triggers） ， 因为事件调度器是基于特定时间周期触发来执行某些任务， 而触发器（ Triggers） 是基于某个表所产生的事件触发的， 区别也就在这里。

1） 关闭事件任务： ALTER EVENT eventName ON COMPLETION PRESERVE DISABLE;
2） 开启事件任务： ALTER EVENT eventName ON COMPLETION PRESERVE ENABLE;
3） 查看事件任务： SHOW EVENTS ;

1、开启 event_scheduler

SET GLOBAL event_scheduler = ON;

或者可以在配置 my.cnf 文件 中加上 event_scheduler = 1。

2、关闭 event_scheduler

SET GLOBAL event_scheduler = OFF;

或者可以在配置 my.cnf 文件 中加上 event_scheduler = 0。

3、查看当前是否已开启事件调度器

SHOW VARIABLES LIKE 'event%';

或者

SELECT @@event_scheduler;

或者

mysql> SHOW PROCESSLIST;

4、定时任务例子

mysql> CREATE TABLE t2(timeline TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE EVENT event_test_insert
-> ON SCHEDULE EVERY 1 SECOND
-> DO INSERT t2 VALUE(CURRENT_TIMESTAMP);

Query OK, 0 rows affected (0.00 sec)

等3秒…

四、MySQL备份

1、mysqldump -h 主机名 -P 端口 -u 用户名 -p 密码 数据库名 > 文件名.sql

mysql>mysqldump -h 主机名 -P 端口 -u 用户名 -p 密码 --all-databases --lock-all-tables --routines --triggers --flush-logs|gzip > `date +%Y%m%d%H%M%S`all.sql.gz

2、直接将 MySQL 数据库压缩备份

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

3、备份 MySQL 数据库某个(些)表

mysql>mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

4、同时备份多个 MySQL 数据库

mysql>mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

5、仅仅备份数据库结构

mysql>mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql

6、备份服务器上所有数据库

mysql>mysqldump -hhostname -uusername -ppassword –-all-databases > allbackupfile.sql

7、还原 MySQL 数据库的命令

mysql>mysql -hhostname -uusername -ppassword databasename < backupfile.sql

8、还原压缩的 MySQL 数据库

mysql>gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

案例

#备份
mysqldump -uroot -pls!@#456 -R lingsheng-mall>/home/milo/mysql_backup/backup.dump

#创建数据库
CREATE SCHEMA `lingsheng-mall_new` DEFAULT CHARACTER SET utf8 ;

#恢复
mysql -uroot -pls!@#456 lingsheng-mall_new< /home/milo/mysql_backup/backup.dump

五、MySQL表复制

1、创建表 t1

mysql>create table t1 (int id, name varchar(30));

2、创建表 t2,(学习 t1 表的结构)

mysql>create table t2 like t1;

3、复制表(两个表的结构完全一致)

mysql>insert into t2 select * from t1;

4、复制表(两个表的结构可能不一致)

mysql>insert into t2 select name from t1

六、MySQL导出数据

1、使用 SELECT … INTO OUTFILE 语句导出

以下实例中我们将数据表 user 数据导出到 /tmp/testdata.txt 文件中:

mysql> select * from `user` INTO OUTFILE '/tmp/testdata.txt';

你可以通过命令选项来设置数据输出的指定格式， 以下实例为导出 CSV 格式：

mysql> SELECT * FROM `user` INTO OUTFILE '/tmp/testdata.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';

在下面的例子中， 生成一个文件， 各值用逗号隔开。 这种格式可以被许多程序使用。

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;

SELECT … INTO OUTFILE 语句有以下属性:

• 1）LOAD DATA INFILE 是 SELECT … INTO OUTFILE 的逆操作， SELECT 句法。 为了将一个数据库的数据写入一个文件， 使用 SELECT … INTO OUTFILE， 为了将文件读回数据库， 使用 LOAD DATA INFILE。
• 2）SELECT…INTO OUTFILE 'file_name’形式的 SELECT 可以把被选择的行写入一个文件中。该文件被创建到服务器主机上， 因此您必须拥有 FILE 权限， 才能使用此语法。
• 3）输出不能是一个已存在的文件。 防止文件数据被篡改。
• 4）你需要有一个登陆服务器的账号来检索文件。 否则 SELECT … INTO OUTFILE 不会起任何作用。
• 5）在 UNIX 中， 该文件被创建后是可读的， 权限由 MySQL 服务器所拥有。 这意味着， 虽然你就可以读取该文件， 但可能无法将其删除。

2、导出表作为原始数据
mysqldump 是 mysql 用于转存储数据库的实用程序。 它主要产生一个 SQL 脚本， 其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录， 该目标必须是可写的。

以下实例将数据表 student_tbl 导出到 /tmp 目录中：

mysqldump -u root -p --no-create-info --tab=/tmp STUDENT student_tbl

3、导出 SQL 格式的数据

导出 SQL 格式的数据到指定文件， 如下所示：

mysqldump -u root -p STUDENT student_tbl > dump.txt

以上命令创建的文件内容如下：

CREATE TABLE student_tbl (
	id int(11) NOT NULL auto_increment,
	title varchar(100) NOT NULL default '',
	teacher varchar(40) NOT NULL default '',
	`date` datetime default NULL,
	PRIMARY KEY (id),
	UNIQUE KEY AUTHOR_INDEX (teacher)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO student_tbl VALUES (1,'class1','xiaoming', now());
INSERT INTO student_tbl VALUES (2,'class2','lilei', now());
INSERT INTO student_tbl VALUES (3,'class3','hanmei', now());

如果你需要导出整个数据库的数据， 可以使用以下命令：

mysqldump -u root -p STUDENT > database_dump.txt

如果需要备份所有数据库， 可以使用以下命令：

mysqldump -u root -p --all-databases > database_dump.txt

–all-databases 选项在 MySQL 3.23.12 及以后版本加入。该方法可用于实现数据库的备份策略。

4、将数据表及数据库拷贝至其他主机

如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。在源主机上执行以下命令， 将数据备份至 dump.txt 文件中:

mysqldump -u root -p database_name table_name > dump.txt

如果完整备份数据库， 则无需使用特定的表名称。
如果你需要将备份的数据库导入到 MySQL 服务器中， 可以使用以下命令， 使用以下命令你需要确认数据库已经创建：

mysql -u root -p database_name < dump.txt

你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的， 是可以相互访问的：

mysqldump -u root -p database_name | mysql -h other-host.com database_name

七、MySQL导入数据

1、mysql 命令导入

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

实例：

mysql -uroot -p123456 < student.sql

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

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

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

3、使用 LOAD DATA 导入数据

MySQL 中提供了 LOAD DATA INFILE 语句来插入数据。 以下实例中将从当前目录中读取文件dump.txt ， 将该文件中的数据插入到当前数据库的 mytbl 表中。

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

如果指定 LOCAL 关键词， 则表明从客户主机上按路径读取文件。 如果没有指定， 则文件在服务器上按路径读取文件。

你能明确地在 LOAD DATA 语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。

两个命令的 FIELDS 和 LINES 子句的语法是一样的。 两个子句都是可选的， 但是如果两个同时被指定， FIELDS 子句必须出现在 LINES 子句之前。

如果用户指定一个 FIELDS 子句， 它的子句 （ TERMINATED BY、 [OPTIONALLY] ENCLOSED BY 和 ESCAPED BY) 也是可选的， 不过， 用户必须至少指定它们中的一个。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
-> FIELDS TERMINATED BY ':'
-> LINES TERMINATED BY '\r\n';

LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致， 则需要指定列的顺序。
如， 在数据文件中的列顺序是 a,b,c， 但在插入表的列顺序为 b,c,a， 则数据导入语法如下：

mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
-> INTO TABLE mytbl (b, c, a);

4、使用 mysqlimport 导入数据

mysqlimport 客户端提供了 LOAD DATA INFILEQL 语句的一个命令行接口。 mysqlimport的大多数选项直接对应 LOAD DATA INFILE 子句。

从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令：

mysqlimport -u root -p --local mytbl dump.txt

mysqlimport 命令可以指定选项来设置指定格式,命令语句格式如下：

mysqlimport -u root -p --local --fields-terminated-by=":" \
--lines-terminated-by="\r\n" mytbl dump.txt

mysqlimport 语句中使用 --columns 选项来设置列的顺序：

 mysqlimport -u root -p --local --columns=b,c,a mytbl dump.txt

mysqlimport 的常用选项介绍

mysqlimport 命令常用的选项还有：

-v 显示版本（ version）
-p 提示输入密码（ password）