MySQL基本操作

---

MySQL基本操作

---

基本操作

1.建库/删库

数据库对名称是大小写敏感的。

#创建数据库

CREATE DATABAE 库名;##

#删库

DROP DATABASE 库名;

2. 插入insert

2.1 仅插入一行

INSERT INTO 表名(列名1，列名2，列名3) VALUES(行1值1, 行1值2, 行1值3);

2.2 插入多行，比如三行

INSERT INTO 表名(列名1，列名2，列名3)

VALUES(行1值1,行1值2,行1值3),

(行2值1,行2值2,行2值3),

(行3值1,行3值2,行3值3);

3.更新update和删除delete

更新和删除操作一定要注意添加WHERE限制条件，否则很危险！！！

更新和删除操作只是对表的内容更改，并不会改变表的结构。

更新

UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;

删除（这是删除内容，并不会释放存储空间。）

DELETE FROM 表名字 WHERE 条件;

4.查询select

4.1 简单查询

SELECT 列1,列2,列3 FROM 表名 WHERE 限制条件;

4.2 子查询

最终结果只来自一个表的情况。条件里嵌套有查询，根据需要可多层嵌套。通过比较符连接。

SELECT … FROM 表名 WHERE
department IN (SELECT department FROM …)

4.3 连接查询

最终结果来自多个表的情况，有某些列作为连接字段。

内连接

 得到两个表的公共集合，以下三个 查询语句都等价

方式一：SELECT … FROM 表a,表b WHERE a.某列名 =
b.某列名;

方式二：SELECT … FROM 表a (INNER) JOIN 表b ON a.某列名 = b.某列名;

方式三：SELECT … FROM 表a CROSS JOIN 表b WHERE a.某列名 = b.某列名;

特殊的内连接-自连接，自己和自己作为两个表处理。

SELECT … FROM 表a AS
A,表a AS B WHERE A.某列名 = B.某列名的计算操作;

左连接

 结果集是左表的全部内容，如果右表的某行和左表不匹配，则右表的相关列内容为空值NULL。

SELECT … FROM 表a LEFT
(OUTER) JOIN 表b ON a.某列名 = b.某列名;

右连接

 结果集是右表的全部内容，如果左表的某行和右表不匹配，则左表的相关列内容为空值NULL。

SELECT … FROM 表a RIGHT
(OUTER) JOIN 表b ON
a.某列名 = b.某列名;

完整外部连接/全连接

 返回两个表的所有行，即返回左、右连接的和。若一个表的行在另一个表不匹配时则显示为空值NULL。

SELECT … FROM 表a FULL
(OUTER) JOIN 表b ON
a.某列名 = b.某列名;

交叉连接

 笛卡尔连接，返回左表每一行和右表每一行相互组合的结果，可借助笛卡尔积n*m理解。条件限制只能使用WHERE不能用ON。

SELECT … FROM 表a CROSS
JOIN 表b WHERE a.某列名 = b.某列名;

如果没有WHERE语句，那么交叉连接和没有限制的内连接等价，都是得到两个表的笛卡尔积。

即SELECT … FROM 表a CROSS JOIN 表b;结果集等同于 SELECT … FROM 表a,表b;。

操作表：对表格的操作，可以理解为更改表的属性或者结构。

5.1 建表

表的名字是大小写敏感的。

CREATE TABLE 表名(

列名1 INT(长度) UNSIGNED DEFAULT NOT NULL AUTO_INCREMENT，

列名2 数据类型(长度) DEFAULT 默认值，

列名3 数据类型(长度) DEFAULT NULL,

PRIMARY KEY(列名)

)ENGINE=InnoDB default-charset=utf8;

添加主键也可以在括号里的最后一行加上CONSTRAINT 主键名 PRIMARY KRY(列名)。

主键也可以是多列的复合主键CONSTRAINT 主键名
PRIMARY KEY(列1,列2,列3)。

查看表的结构

#方式一：使用关键字DESCRIBE

DESCRIBE 表名；

#方式二：查看建表语句

SHOW CREATE TABLE 表名;

5.2 重命名表格

对一张已经建立的表重命名

方式一：RENAME TABLE 原名 TO 新名;

方式二：ALTER TABLE 原名 RENAME
新名;

方式三：ALTER TABLE 原名 RENAME
TO 新名;

5.3 删除表

DROP TABLE 表名字;

5.4 表增加一列

以下两种方式是等效的

方式一：ALTER TABLE 表名字 ADD 列名 数据类型 约束;

方式二：ALTER TABLE 表名字 ADD COLUMN 列名 数据类型 约束;

如果没有指定添加的新列的位置，那么就会默认添加在表的最后一列，如果需要指定在第一列则用关键字FIRST，如果需要在某列后则用关键字AFTER 某列名。

新增列放在第一列

ALTER TABLE 表名字 ADD
列名 数据类型 约束 FIRST;

新增列放在列A之后

ALTER TABLE 表名字 ADD
列名 数据类型 约束 AFTER A;

5.5 表删除一列

方式一：ALTER TABLE 表名字 DROP 列名;

方式二：ALTER TABLE 表名字 DROP COLUMN 列名;

5.6 重命名表的一列/修改该列的名字

本质上是修改一个列的属性，用了关键字CHANGE。

ALTER TABLE 表名字 CHANGE
原列名 新列名 新列的数据类型 约束;

注意到如果新列名和原列名相同的话，就可以修改这个列的数据类型和约束了。

但是修改数据类型可能会导致数据丢失！！

5.7 修改表一列的数据类型

方式一：ALTER TABLE 表名字 CHANGE 原列名 原列名 新数据类型 约束;

方式二：ALTER TABLE 表名字 MODIFY 列名字 新数据类型;

注意，修改数据类型可能会导致数据丢失！！

6.索引

数据库默认使用全表扫描，当一张表数据量较少的时候检索速度也会很快，当数据量很大的时候（比如达到百万级）如果使用全表扫描就会很慢。这个时候就需要添加索引来提高查询速度，往往会有很大的改善。添加索引时会在磁盘上创建索引文件，并且写入索引信息，所以创建索引会增加插入速度和消耗一部分存储空间。

执行查询语句时，数据库会自动判断是否有可用的索引，如果没有才会进行全表扫描。

创建单列索引的命令：

方式一：ALTER TABLE 表名字 ADD INDEX 索引名 (列名);

方式二：CREATE INDEX 索引名 ON 表名字 (列名);

创建多列索引的命令：

 多列索引必须符合最左匹配原则。

方式一：ALTER TABLE 表名字 ADD INDEX 索引名 (列名1,列名2,列名3);

方式二：CREATE INDEX 索引名 ON 表名字 (列名1,列名2,列名3);

查询一张表的索引命令:

SHOW INDEX FROM 表名字;

6.视图

视图是一张虚拟表，基于数据库中已有的一张表或者多张表的真实数据组织而成，是一张逻辑表而不是实际存在的物理表,数据库中存放的是视图的定义。查询的时候可以把视图当做一张普通的表操作。

创建视图命令：

CREATE VIEW 视图名(视图列1,视图列2,视图列3) AS SELECT
列1,列2,列3
FROM 表;

可以看到视图中用到关键字AS，而且在AS后面是一个查询语句，所以这个查询语句可以是子查询和连接查询，视图依赖的数据可以是多个表的数据。

查看视图表的数据命令（和查询普通表一样，只不过把表名字换成了视图名字）：

SELECT * FROM 视图名;

删除视图（和删除表操作一样）:

DROP VIEW 视图名;

7.导入和导出

导入

 导入本地文件数据到数据库中的某张表去。需要注意指定文本中行、列数据的分隔符，默认使用制表符Tab作为列分隔符、使用换行符（windows是“\r\n”，OS
 X是“\r”，Linux是“\n”）作为行分隔符。

#使用默认的分隔符

LOAD DATA INFILE ‘文件路径’ INTO TABLE 表名;

注意导入的文件路径需要加上单引号。如果需要指定分隔符，则为：

LOAD DATA INFILE ‘文件路径’

INTO TABLE 表名

FIELDS TERMINATED BY ‘列分隔符’

LINES TERMINATED BY ‘换行符’;

导出

 导出操作和导入相反，是把数据库的数据导出到本地文件中去。需要设置MySQL服务器允许导出权限。

SELECT 列1,列2,列3 INTO OUTFILE ‘文件路径’ FROM 表名;

注意导出文件路径需要加上单引号。同样，上面是使用默认的分隔符导出数据，如果想要设定分隔符，则可以在上面的命令后添加分隔操作。

SELECT 列1,列2,列3

INTO OUTFILE ‘文件路径’

FROM 表名

FIELDS TERMINATED BY ‘列分隔符’

[OPTIONALLY] ENCLOSED BY ‘指定把字段包括起来的字符（若用了OPTIONALLY则只会包括CHAR和VARCHAR类型）’

ESCAPED BY ‘指定当需要转义时用什么作为转义字符（通常为\）’

LINES TERMINATED BY ‘换行符’;

对于用LOAD DATA INFILE或SELECT …INTO OUTFILE执行的文本文件导入或导出操作，NULL用序列\N表示。

8.数据备份和恢复

导出和备份的区别

 导出仅仅是导出需要的数据，而备份则是把数据库的结构，包括表结构、所有数据、索引、视图等全部另存为一个文件。

备份

 数据备份使用关键词mysqldump，mysqldump是一个备份程序，产生一个备份脚本文件*.sql。

备份命令不需要在MySQL服务里面敲命令，需要退出MySQL的命令行操作界面，在系统命令行操作界面进行。

#备份整个数据库

mysqldump -u 用户名 数据库名 > 备份文件路径;

#备份某个数据表

mysqldump -u 用户名 数据库名 表名 > 备份文件路径;

恢复

 方式一是在MySQL命令行操作，使用关键词source。会重新创建需要的库和表。

 source命令直接执行sql脚本文件。

source 备份文件路径;

方式二先在数据库建一个空库，然后在系统命令行操作恢复命令。使用Linux输入符号“<”完成恢复。

mysql -u 用户名 < 备份文件路径;

不交互执行SQL

在shell脚本执行SQL语句，不需要登录MySQL服务执行操作。

mysql -h主机IP地址 -u 登录用户名 -p ‘密码’ -S
mysql.sock的路径 -e “SQL命令;”

保存本地文件路径

如果没有-h参数，则表示是访问本地的MySQL服务。举例：

mysql -uroot -p’fefjay@2017’ -S
/tmp/mysql.sock -e “show databases;” >> /home/a.txt

echo “show databases;” | mysql
-uroot -p’fefjay@2017’ -S /tmp/mysql.sock > /home/a.txt

上述命令都是先要登录MySQL服务，然后执行命令，得到结果，至于把结果是导出到本地的文件a.txt，或者结合管道符|和grep的操作，看具体的业务需要了。但是这里需要写出登录的密码，不安全，需要找到一种更加安全的可以写到脚本中的方法