mysql对标准sql的扩展_MySQL官方文档阅读与记录

1. MySQL对标准SQL的扩展

/*! MySQL-specific code */

SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...

CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;

-- 上述语句表明，当且仅当MySQL的版本大于等于指定版本 50110 的时候才会执行 KEY_BLOCK_SIZE=1024 语句

数据的组织方式

每个数据库对应数据目录下的一个目录

每个表对应一个文件

数据库表名是否大小写敏感与操作系统对文件名大小写是否敏感有关

一般语法SQL语法

字符串可以用”或者’括起来，当ANSI_QUOTES启用的时候则只能使用单引号

表示转义字符

可以用db_name.tbl_name来访问不同数据库下的表，MySQL不支持表空间

数据类型

MEDIUMINT,SET, ENUM,BLOB,TEXT

AUTO_INCREMENT,BINARY,NULL,UNSIGNED, ZEROFILL

函数与操作符

函数支持别名

MySQL认为||和&&是逻辑OR和逻辑AND，因此不支持标准SQL的||字符串连接，而是使用concat函数

COUNT(DISTINCT value_list)

默认的字符串比较是不区分大小写的，如果需要区分大小写，则在声明某一列的时候需要加上BINARY属性

%取余，N%M等于MOD(N,M)

LAST_INSERT_ID()返回最新的auto_increment的数据

LIKE可以用于数值型的数据

REGEXPNOT REGEXP

CONCAT() CHAR()

BIT_COUNT(),CASE,ELT(),FROM_DAYS(),FORMAT(),IF(),PASSWORD(),MD5(),PERIOD_ADD(),PERIOD_DIFF(),TO_DAYS(),WEEKDAY()

TRIM()

STD(),BIT_OR(),BIT_AND(),BIT_XOR(),GROUP_CONCAT()

2. MySQL与标准SQL的语法差异

-- 不支持SELECT ... INTO TABLE， 可以用INSERT INTO ... SELECT 代替(待验证)，支持 SELECT ... INTO OUTFILE和CREATE TABLE ... SELECT.

INSERT INTO tbl_temp2 (fld_id)

SELECT tbl_temp1.fld_order_id

FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

/* comment */是标准SQL的注释，MySQL支持该语法，用于实现MySQL的特殊语法

-- 注释的时候--和后面的注释内容之间必须有空格

UPDATE account SET credit=credit-payment

-- 当payment等于-1时：

UPDATE account SET credit=credit--1

-- 如果将--后面的内容解析为注释，则上面的语句相当于：

UPDATE account SET credit=credit

3. MySQL对于约束的处理

(1) 主键和唯一索引

(2) 外键

Mysql支持在CREATE TABLE和ALTER TABLE的时候对外键进行UPADTE和DELETE，可选的操作包括：RESTRICT(默认),CASCADE,SET NULL,NO ACTION

MySQL要求外键必须有索引，如果没有索引的话将会创建自动索引

可以通过查询INFORMATION_SCHEMA.KEY_COLUMN_USAGE表来获取外键的信息

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME

> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

> WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;

+--------------+---------------+-------------+-----------------+

| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME |

+--------------+---------------+-------------+-----------------+

| fk1 | myuser | myuser_id | f |

| fk1 | product_order | customer_id | f2 |

| fk1 | product_order | product_id | f1 |

+--------------+---------------+-------------+-----------------+

3 rows in set (0.01 sec)

(3) 无效数据

默认情况下，MySQL将会对非法的输入值进行强制处理使其符合规范，可以启用strict SQL mode

(4) 枚举和集合

ENUM('a','b','c') -- 值''、'd'、'ax'均为无效的错误数据

SET('a','b','c') -- 值'd'、'a,b,c,d'均为无效的错误数

3.Tutorial

提示符

含义

mysql>

可以输入下一条执行语句

>

等待继续输入

‘>

等待以单引号开始的字符串的结尾

“>

等待以双引号开始的字符串的结尾

`>

等待以`开始的字符串的结尾

/*>

-- 查看所有的数据库

show databases；

-- 使用某一个数据库，use和其他的语句不同，不需要在结尾处加分号，但是必须在一行内输完，不能换行

use test

-- 创建数据库

CREATE DATABASE menagerie;

-- 登陆并连接使用数据库

mysql -h host -u user -p menagerie

-- 查看当前使用的数据库

SELECT DATABASE();

-- 查看数据库下有哪些表

SHOW TABLES;

-- 创建表

CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

-- 查看表定义

describe pet；

-- 从文件中导入数据到表中

-- pet.txt内容为：Whistler Gwen bird N 1997-12-09 N

LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

-- 当文件中行结尾的分隔符为nr的时候需要特别的标明

LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet LINES TERMINATED BY 'rn';

-- 当从文件中load数据失败时(ERROR 1148 (42000): The used command is not allowed with this MySQL version)，需要查询是否启用了该功能，默认情况下是关闭的

show variables like '%LOCAL%';

-- 可以看到local_infile变量，默认情况下为OFF，设置该属性为ON，注意：使用这种方式修改后仍无法导入数据

SET global local_infile=1；

-- 需要在与数据库服务器建立连接的时候，设置该连接数据

mysql -h host -u user --local_infile -p menagerie

-- 插入数据

INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

-- 查询数据

SELECT * FROM pet;

-- 删除所有数据

DELETE FROM pet;

-- 更新数据

UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

-- 查询符合条件的数据

SELECT * FROM pet WHERE name = 'Bowser';

SELECT * FROM pet WHERE birth >= '1998-1-1';

SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';

SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';

SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');

-- 排序

SELECT name, birth FROM pet;

SELECT owner FROM pet;

SELECT DISTINCT owner FROM pet;

SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';

MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

-- 时间日期计算

SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;

SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;

SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age;

SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;

SELECT name, birth, MONTH(birth) FROM pet;

SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

SELECT name, birth FROM pet WHERE

— 对NULL值的支持，NULL表示值缺失

— 0和NULL代表false，其他均表示true

SELECT 1 IS NULL, 1 IS NOT NULL;

— 任何数据和NULL进行=、、>、

SELECT 1 = NULL, 1 NULL, 1 NULL;

— 空字符串和NULL是不一致的

SELECT 0 IS NULL, 0 IS NOT NULL, ” IS NULL, ” IS NOT NULL;

-- 模式匹配

SELECT * FROM pet WHERE name LIKE 'b%';

SELECT * FROM pet WHERE name LIKE '%fy';

SELECT * FROM pet WHERE name LIKE '%w%';

-- 查找名称长度为5个字符的数据

SELECT * FROM pet WHERE name LIKE '_____';

-- 正则表达式匹配

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');

SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');

SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');

-- 当需要大小写敏感的时候需要指定大小写敏感的字符集

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs);

SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');

-- 统计记录数目

SELECT COUNT(*) FROM pet;

SELECT owner, COUNT(*) FROM pet GROUP BY owner;

SELECT species, COUNT(*) FROM pet GROUP BY species;

SELECT sex, COUNT(*) FROM pet GROUP BY sex;

SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;

SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;

-- 多表操作

SELECT pet.name,TIMESTAMPDIFF(YEAR,birth,date) AS age,remark FROM pet INNER JOIN event ON pet.name = event.name WHERE event.type = 'litter';

SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1 INNER JOIN pet AS p2 ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

：http://www.linuxidc.com/Linux/2018-01/150560.htm

均为null