mysql runoob_MYSQL 学习

创建数据库

CREATE DATABASE ***

删除数据库

DROP DATABASE ***

MYSQL支持多种数据类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型

创建数据表

CREATE TABLE table_name (columns_name column_type);

eg:

CREATE TABLE IF NOT EXISTS 'runoob_tbl'(

'runoob_id' INT UNSIGNED AUTO_INCREMENT,

'runoob_title' VARCHAR(100) NOT NULL,

'runoob_author' VARCHAR(40) NOT NULL,

'submission_date' DATE,

PRIMARY KEY ('runoob_id')

)ENGINE=InnoDB CHARSET=utf8;

删除数据表

DROP TABLE ***

插入数据

INSERT INTO table_name (field1, field2, ...fieldN)

VALUES

(value1, value2, ...valueN);

eg:

INSERT INTO runoob_tb1

(runoob_title, runoob_author, submission_date)

VALUES

("学习PHP", "菜鸟教程", now());

查询数据

SELECT column_name, column_name

FROM table_name

[WHERE Clause]

[LIMIT N][OFFSET M]

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。

SELECT命令可以读取一条或多条记录。

你可以使用星号(*)来代替其他字段，SELECT语句会返回表的所有字段数据

你可以使用WHERE语句来包含任何条件。

你可以使用LIMIT属性来设定返回的记录数。

你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

eg:

SELECT runoob_id, runoob_title, runoob_author, submission_date FROM runoob_tb1;

MYSQL WHERE子句

我们知道从MYSQL表中使用SQL SELECT语句来读取数据。

如需有条件地从表中选取数据，可将WHERE子句添加到SELECT语句中。

SELECT field1, field2, ...fieldN FROM table_name1, table_name2...

[WHERE condition1 [AND [OR]] condition2.....

查询语句中你可以使用一个或者多个表，表之间使用逗号‘,’分割，并使用WHERE语句来设定查询条件。

你可以在WHERE子句中指定任何条件。

你可以使用AND或者OR指定一个或多个条件。

WHERE子句也可以运用于SQL的DELETE或者UPDATE命令。

WHERE子句类似于程序语言中的if条件，根据MYSQL表中的字段值来读取指定的数据。

使用主键来作为WHERE子句的条件查询是非常快速的。

eg:

SELECT runoob_id, runoob_title, runoob_author, submission_date FROM runoob_tb1

WHERE runoob_author="RUNOOB.COM";

MYSQL UPDATE 更新

UPDATE命令修改MYSQL数据表数据的通用SQL语法：

UPDATE table_name SET field1=new-value1, field2=new-value2

[WHERE Clause]

eg:

UPDATE runoob_tb1 SET runoob_title="学习Python" WHERE runoob_id=3;

MYSQL DELETE 语句

DELETE FROM table_name [WHERE Clause]

如果没有指定WHERE子句，MYSQL表中的所有记录将被删除。

你可以在WHERE子句中指定任何条件。

你可以在单个表中一次性删除记录。

eg:

DELETE FROM runoob_tb1 WHERE runoob_id=3;

MYSQL LIKE子句

我们知道在 MySQL 中使用 SQL SELECT 命令来读取数据， 同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。

WHERE 子句中可以使用等号 = 来设定获取数据的条件，如 "runoob_author = 'RUNOOB.COM'"。

但是有时候我们需要获取 runoob_author 字段含有 "COM" 字符的所有记录，这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。

SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

SELECT field1, field2,...fieldN

FROM table_name

WHERE field1 LIKE condition1 [AND [OR]] field2 = 'somevalue'

eg:

SELECT * from runoob_tb1 WHERE runoob_author LIKE '%COM';

在 where like 的条件查询中，SQL 提供了四种匹配方式。

%：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号(%%)表示。

_：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。

[]：表示括号内所列字符中的一个(类似正则表达式)。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

[^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询。

UNION操作符

MYSQL UNION操作符用于连接两个以上的SELECT语句的结果组合到一个结果集合中。多个SELECT语句会删除重复的数据。

SELECT expression1, expression2,...expression_n

FROM tables

[WHERE conditions]

UNION [ALL | DISTINCT]

SELECT expression1, expression2,...expression_n

FROM tables

[WHERE conditions];

参数：

DISTINCT:可选，删除结果集中重复的数据。默认情况下UNION操作符已经删除了重复数据，所以DISTINCT修饰符对结果没啥影响。

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

eg:

SELECT country FROM Websites

UNION

SELECT country FROM apps

ORDER BY country;

MYSQL排序

如果我们需要对读取的数据进行排序，我们就可以使用MYSQL的ORDER BY子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

SELECT field1, field2,...fieldN table_name1, tablename2...

ORDER BY field1 [ASC [DESC][默认ASC]],[field2...][ASC [DESC][默认ASC]]

你可以设定多个字段来排序

你可以使用ASC或DESC关键字来设置查询结果是按升序或降序排列

你可以添加WHERE...LIKE子句来设置条件

eg:

SELECT * from runoob_tb1 ORDER BY submission_date ASC

MYSQL GROUP BY语句

该语句根据一个或多个列对结果集进行分组

在分组的列上我们可以使用COUNT,SUM,AVG等函数

SELECT column_name, function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name;

eg:

SELECT name, COUNT(*) FROM employee_tb1 BROUP BY name

使用WITH ROLLUP

WITH ROLLUP可以实现现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT...)。

例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数

eg:

SELECT name, SUM(singn) as singn_count FROM employee_tb1 GROUP BY name WITH ROLLUP;

MYSQL连接的使用

在真正的应用经常需要从多个数据表中读取数据

在这里介绍如何使用MYSQL的JOIN在两个或多个表中查询数据

你可以在SELECT，UPDATE和DELETE语句中使用MYSQL的JOIN来联合多表查询

JOIN按照功能大致分为如下三类：

INNER JOIN(内连接或等值连接)：获取两个表中字段匹配关系的记录

LEFT JOIN(左连接)：获取左表所有记录，即使右表没有对应匹配的记录

RIGHT JOIN(右连接)：与LEFT JOIN相反，用于获取右表所有记录，即使左表没有对应匹配的记录

eg:

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tb1 a INNER JOIN tcount_tb1 b ON a.runoob_author = b.runoob_author;

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tb1 a LEFT JOIN tcount_tb1 b ON a.runoob_author = b.runoob_author;

MYSQL NULL值处理

我们已经知道MYSQL使用SQL SELECT命令及WHERE子句来读取数据表中的数据，但是当提供的查询条件字段为NULL时，该命令就可能无法正常工作。

为了处理这种情况，MYSQL提供了三大运算符：

IS NULL:当列的值是NULL，此运算符返回true

IS NOT NULL:当列的值不为NULL，运算符返回true

<=>:比较运算符(不同于=运算符)，当比较的两个值为NULL时返回true

关于NULL的条件比较运算是比较特殊的。你不能使用=NULL或!=NULL在列中查找NULL值

在MYSQL中，NULL值与任何其他值的比较(即使是NULL)返回永远是false

eg:

SELECT * FROM runoob_tb1 WHERE runoob_count IS NULL

MYSQL正则表达式

MYSQL同样也支持正则表达式的匹配，使用REGEXP操作符来进行正则表达式匹配

eg:

SELECT name FROM person_tb1 WHERE name REGEXP '^st'

MYSQL ALTER命令

当我们需要修改数据表名或者修改数据表字段时，就需要使用到MYSQL ALTER命令。

eg:

删除，添加或修改表字段

ALTER TABLE testalter_tbl DROP i;

ALTER TABLE testalter_tbl ADD i INT FIRST;

ALTER TABLE testalter_tbl DROP i;

ALTER TABLE testalter_tbl ADD i INT AFTER c;

修改字段类型及名称

ALTER TABLE testalter_tbl MODIFY c CHAR(10);

ALTER TABLE testalter_tbl CHANGE i j BIGINT;

修改表名

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

MYSQL索引

MYSQL索引的建立对于MYSQL的高效运行是很重要的，索引可以大大提高MYSQL的检索速度。

打个比方，如果合理的设计且使用索引的MYSQL是一辆兰博基尼的话，没有设计和使用索引的MYSQL就是一个人力三轮车

创建索引时，你需要确保该索引是应用在SQL查询语句的条件(一般作为WHERE子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MYSQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

普通索引

创建索引

CREATE INDEX indexName ON mytable(username(length));

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

删除索引

DROP INDEX [indexName] ON mytable;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构

ALTER table mytable ADD UNIQUE [indexName](username(length))

使用ALTER命令添加和删除索引

ALTER TABLE testalter_tb1 ADD INDEX (c);

ALTER TABLE testalter_tb1 DROP INDEX c;

MYSQL临时表

临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，MYSQL会自动删除表并释放所有空间。

CREATE TEMPORARY TABLE***

MYSQL复制表

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

本章节将为大家介绍如何完整的复制MySQL数据表，步骤如下：

使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。

复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。

如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。

MYSQL序列使用

MYSQL序列是一组整数：1,2,3...，由于一张数据表只能有一个字段自增主键，如果你想实现其他字段也自动增加，就可以使用MYSQL序列来实现。

使用AUTO_INCREMENT

MYSQL处理重复数据

如何防止表中出现重复数据

你可以在MYSQL数据表中设置指定的字段为PRIMARY KEY或者UNIQUE索引来保证数据的唯一性。

让我们尝试一个示例：下表中无索引及主键，所以该表允许出现多条重复记录。

CREATE TABLE person_tb1

(

first_name CHAR(20),

last_name CHAR(20),

sex CHAR(10),

);

如果你想设置表中字段first_name, last_name数据不能重复，你可以设置双主键模式来设置数据的唯一性，如果你设置了双主键，那么那个键得默认值不能为NULL，可设置为NOT NULL。如下所示：

CREATE TABLE person_tb1

(

first_name CHAR(20) NOT NULL,

last_name CHAR(20) NOT NULL,

sex CHAR(10),

PRIMARY KEY (last_name, first_name)

);

如果我们设置了唯一索引，那么在插入重复数据时，SQL语句将无法执行成功，并抛出错。

另一种设置数据的唯一性的方法是添加一个UNIQUE索引，如下所示：

CREATE TABLE person_tb1

(

first_name CHAR(20) NOT NULL,

last_name CHAR(20) NOT NULL,

sex CHAR(10),

UNIQUE (last_name, first_name)

);

统计重复数据

SELECT COUNT(*) as repetitions, last name, first name

FROM person_tb1

GROUP BY last_name, first_name

HAVING repetitions > 1;

过滤重复数据

如果你需要读取不重复的数据可以在SELECT语句中使用DISTINCT来过滤重复数据。

SELECT DISTINCT last_name, first_name

FROM person_tb1;

你也可以使用GROUP BY来读取数据表中不重复的数据：

SELECT last_name, first_name

FROM person_tb1

GROUP BY (last_name, first_name);

删除重复数据

如果你想删除数据表中的重复数据，你可以使用以下SQL语句：

CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tb1 GROUP BY (last_name, sex);

DROP TABLE person_tb1;

ALTER TABLE tmp RENAME TO person_tb1;

当然你也可以在数据表中添加INDEX和PRIMARY KEY这种简单的方法来删除表中的重复记录：

ALTER IGNORE TABLE person_tb1

ADD PRIMARY KEY (last_name, first_name);

MYSQL导出数据

使用SELECT ...INTO OUTFILE导出到txt

SELECT * FROM runoob_tb1

INTO OUTFILE '/tmp/runoob.txt';

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

SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'

FIELDS TERMINATED BY ',' ENCLOSED BY '"'

LINES TERMINATED BY '\r\n';

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

LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT ... INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。

SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。

输出不能是一个已存在的文件。防止文件数据被篡改。

你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。

在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。

MYSQL导入数据

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

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

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

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

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

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytb1

FIELDS TERMINATED BY ':'

LINES TERMINATED BY '\r\n';