MySQL常用语法

MySQL常用语法

创建数据表

-- 直接新增
CREATE TABLE table_name (column_name column_type);
-- 判断是否存在，不存在则新增
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` )

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

删除数据表

-- 直接删除
DROP TABLE table_name ;
-- 判断是否存在，存在则删除
DROP TABLE IF EXISTS table_name ;

ALTER命令

用于修改数据表名或者修改数据表字段

-- 删除表字段,表中只剩一个字段，无法删除字段
ALTER TABLE testalter_tbl  DROP columnname;

-- 使用 ADD 子句来向数据表中添加列，并定义数据类型
-- FIRST 和 AFTER 关键字用于指定新增字段的位置,只占用于 ADD 子句,所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。
ALTER TABLE testalter_tbl ADD columnname INT[FIRST|AFTER columnnameA];

-- 修改字段类型
ALTER TABLE testalter_tbl MODIFY columnname CHAR(10) [NOT NULL DEFAULT 100];

-- 修改字段名及字段类型
ALTER TABLE testalter_tbl CHANGE columnname columnname1  BIGINT [NOT NULL DEFAULT 100];

-- 修改字段默认值
ALTER TABLE testalter_tbl ALTER columnname SET DEFAULT 1000;

-- 删除字段默认值
ALTER TABLE testalter_tbl ALTER columnname DROP DEFAULT;

-- 修改数据表类型，可以使用 ALTER 命令及 TYPE 子句来完成
ALTER TABLE testalter_tbl ENGINE = MYISAM;

-- 修改表名
ALTER TABLE testalter_tbl RENAME TO alter_tbl;

-- 删除外键约束：keyName是外键别名
alter table tableName drop foreign key keyName;

插入数据

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

查询数据

Select

SELECT column_name,column_name
FROM table_name 
WHERE A=B
[LIMIT N] ;

• 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。SELECT 命令可以读取一条或者多条记录。
• 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
• 你可以使用 WHERE 语句来包含任何条件。
• 你可以使用 LIMIT 属性来设定返回的记录数，。

Union

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

SELECT expression_1, expression_2, expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression_1, expression_2,  expression_n
FROM tables
[WHERE conditions];

• expression1, expression2, … expression_n: 要检索的列。
• tables: 要检索的数据表。
• WHERE conditions: 可选， 检索条件。
• DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
• ALL: 可选，返回所有结果集，包含重复数据。

Order By

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

• 你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
• 你可以设定多个字段来排序。
• 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认ASC情况下，升序排列。
• 你可以添加 WHERE…LIKE 子句来设置条件。

Group By

SELECT column_name, function(column_name)
FROM table_name
[WHERE condition1 [AND [OR]] condition2]
GROUP BY column_name;

• GROUP BY 语句根据一个或多个列对结果集进行分组。
• 在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
  -使用 WITH ROLLUP
• WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
• 使用有 WITH ROLLUP 子句的 GROUP BY 语句时，不能再使用 ORDER BY 语句对结果集进行排序，如果对返回的结果顺序不满意，需要应用程序获得结果后在程序中进行排序。
• Null对应所有数据的统计结果

COALESCE

select coalesce(a,b,c);

如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

条件语句Where

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 表中的字段值来读取指定的数据。

操作符

• 以下表中实例假定 A 为 10, B 为 20
• 如果我们想再 MySQL 数据表中读取指定的数据，WHERE 子句是非常有用的。
• 使用主键来作为 WHERE 子句的条件查询是非常快速的。
• 如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。
• MySQL 的 WHERE 子句的字符串比较是不区分大小写的。你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

操作符	描述	实例
=	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
<>, !=	不等于，检测两个值是否相等，如果不相等返回true (A != B)	返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B)返回false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
<=	小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回

Like

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

• LIKE 通常与 % 一同使用，类似于一个元字符的搜索
• LIKE 子句中使用百分号 %字符来表示任意字符，如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的
• 你可以使用LIKE子句代替等号
• 在字符串前面加%，会降低查询效率

连接语句JOIN

SELECT  a.A, a.A1, b.B2
FROM table1 a
[INNER|LEFT|RIGHT] JOIN table12 b
ON a.A = b.B ;

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

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录，INNER 可以缺省，默认是INNER JOIN。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
• ON a.A = b.B 表示连接条件

INNER JOIN	LEFT JOIN	RIGHT JOIN

NULL值处理

不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。
NULL 值与任何其它值的比较（即使是 NULL）永远返回 false，即 NULL = NULL 返回false 。
NULL 使用 IS NULL 和 IS NOT NULL 运算符。

IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。

更新数据

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

• 你可以同时更新一个或多个字段。
• 你可以在 WHERE 子句中指定任何条件。
• 你可以在一个单独表中同时更新数据。
• 当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

删除数据表中的记录

DELETE FROM table_name [WHERE Clause]

• 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
• 你可以在 WHERE 子句中指定任何条件
• 您可以在单个表中一次性删除记录。

重复数据处理

防止重复数据插入

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

-- 设置主键,表中字段first_name，last_name数据不能重复，你可以设置双主键模式来设置数据的唯一性， 如果你设置了双主键，那么那个键的默认值不能为NULL，可设置为NOT NULL
CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
    PRIMARY KEY (last_name, first_name)
);

-- 添加一个UNIQUE索引
CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

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

INSERT IGNORE INTO与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的，如果插入重复数据，将不返回错误，只以警告形式返回

-- INSERT IGNORE INTO
INSERT IGNORE INTO person_tbl (last_name, first_name)
VALUES( 'Jay', 'Thomas');

REPLACE INTO如果存在primary 或 unique相同的记录，则先删除掉。再插入新记录。

-- REPLACE INTO
REPLACE INTO person_tbl (last_name, first_name)
VALUES( 'Jay', 'Thomas');

统计重复数据

SELECT COUNT(*) as repetitions, last_name, first_name
FROM person_tbl
GROUP BY last_name, first_name
HAVING repetitions > 1;

• 确定哪一列包含的值可能会重复。
• 在列选择列表使用COUNT(*)列出的那些列。
• 在GROUP BY子句中列出的列。
• HAVING子句设置重复数大于1。

过滤重复数据

SELECT 语句中使用 DISTINCT关键字来过滤重复数据。

 SELECT DISTINCT last_name, first_name
 FROM person_tbl;

使用 GROUP BY 来读取数据表中不重复的数据

SELECT last_name, first_name
FROM person_tbl
GROUP BY (last_name, first_name);

删除重复数据

用临时表删除重复数据

CREATE TABLE tmp SELECT last_name, first_name, sex
FROM person_tbl
GROUP BY (last_name, first_name, sex);
DROP TABLE person_tbl;
ALTER TABLE tmp RENAME TO person_tbl;

在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录

ALTER IGNORE TABLE person_tbl
ADD PRIMARY KEY (last_name, first_name);