MySQL

MySQL

查询系统时间

SELECT SYSDATE();

 

R: 2018-01-26 14:05:41

注释 -- xxxxxxxxxxxxxx

SQL分类
数据定义语言DDL
数据操作语言DML
事物控制语言TCL
数据查询语言DQL
数据控制语言DCL

事务控制语言
用来维护数据一致性的语句

SQL 语句中关键字大写 非关键字小写

MySQL 数据类型
数值类型：
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 字节 (-128，127) (0，255) 小整数值
SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度浮点数值
DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D， 为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值
a指定指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38。
b指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0。

float:浮点型，含字节数为4，32bit，数值范围为-3.4E38~3.4E38（7个有效位）

double:双精度实型，含字节数为8，64bit数值范围-1.7E308~1.7E308（15个有效位）

decimal:数字型，128bit，不存在精度损失，常用于银行帐目计算。（28个有效位）

日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型 大小(字节) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS 混合日期和时间值，时间戳




字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。
类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据

 

CREATE DATABASE emp;
--创建emp库 数据定义语言DDL
-- DDL 是对数据库对象进行操作的语言 数据库对象包括：表、视图、索引、序列

CREATE TABLE employee(
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
gender CHAR(1)DEFAULT 'M',
birth DATE,
salary DECIMAL(6,2),
job VARCHAR(30),
deptno INT UNSIGNED,
PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

-- 创建表 employee id 为无符号int 类型，自增 主键 ，name varchar(20) 可变长度20 不可为空 ，gender char（1）定长 1 默认值为 'M' ，birth date类型 日期 ，salary DECIMAL类型 6位小数两位，job varchar 30 deptno int无符号 表类型Inn'o'DB 字符类型utf8

DESC employee

-- 查看表结构

DROP TABLE employee

--删除表

RENAME TABLE employee TO myemp

--表重命名

 

ALTER TABLE myemp ADD sex CHAR(1) NOT NULL;

-- 向表中添加一个字段 添加到最后

 

ALTER TABLE myemp ADD sex2 CHAR(1) FIRST;

--FIRST 添加到最前面

 

ALTER TABLE myemp ADD sex3 CHAR(1) AFTER name;

-- AFTER 添加到某个字段后面

 

ALTER TABLE myemp DROP sex;    

-- 删除一个字段

MySQL目前不支持列的Default 为函数的形式,
如达到你某列的默认值为当前更新日期与时间的功能,
你可以使用TIMESTAMP列类型
下面就详细说明TIMESTAMP列类型

ALTER TABLE myemp ADD hiredate2 TIMESTAMP DEFAULT NOW();

 

修改字段
可以修改字段的类型，长度，默认值是否非空，修改表结构都应当避免在表中有时间以后进行，
若表中有数据 修改表中字段时尽量不要修改类型修改长度尽量增大

ALTER TABLE myemp MODIFY name VARCHAR(100);

 

-- 修改字段长度

ALTER TABLE myemp CHANGE gender sex CHAR(1)

 

-- CHANGE old new

DML 语句
是对表中的数据进行的操作
伴随事务控制
增、删、改

INSERT INTO myemp (id,name,salary,deptno) VALUES (1,'jack',5000,10)

 

-- 插入一条数据

INSERT INTO myemp (name,salary,deptno,birth) VALUES ('jack',5000,10,now())

 

-- 插入一条数据

INSERT INTO myemp (name,salary,deptno,birth) VALUES ('jack',5000,10,'2018-01-02')

 

-- 插入一条数据

　　mysql日期和字符相互转换方法
　　date_format(date,'%Y-%m-%d') -------------->oracle中的to_char();
　　str_to_date(date,'%Y-%m-%d') -------------->oracle中的to_date();
　　%Y：代表4位的年份
　　%y：代表2为的年份
　　%m：代表月, 格式为(01……12)
　　%c：代表月, 格式为(1……12)
　　%d：代表月份中的天数,格式为(00……31)
　　%e：代表月份中的天数, 格式为(0……31)
　　%H：代表小时,格式为(00……23)
　　%k：代表 小时,格式为(0……23)
　　%h： 代表小时,格式为(01……12)
　　%I： 代表小时,格式为(01……12)
　　%l ：代表小时,格式为(1……12)
　　%i： 代表分钟, 格式为(00……59)
　　%r：代表 时间,格式为12 小时(hh:mm:ss [AP]M)
　　%T：代表 时间,格式为24 小时(hh:mm:ss)
　　%S：代表 秒,格式为(00……59)
　　%s：代表 秒,格式为(00……59)
　　SELECT DATE_FORMAT(20130111191640,'%Y-%m-%d %H:%i:%s')
　　DATE_FORMAT(20130111191640,'%Y-%m-%d %H:%i:%s')

UPDATE myemp SET salary=6000;

 

--修改数据

UPDATE myemp SET salary=6000 WHERE id=1;

 

--修改数据 使用条件

DELETE FROM myemp;

 

--删除数据

DELETE FROM myemp WHERE id =2;

 

--删除数据 使用条件


程度从强到弱
1、drop table tb
drop将表格直接删除，没有办法找回
2、truncate (table) tb
删除表中的所有数据，不能与where一起使用
3、delete from tb (where)
删除表中的数据(可制定某一行)
区别：truncate和delete的区别
1、事务：truncate是不可以rollback的，但是delete是可以rollback的；
原因：truncate删除整表数据(ddl语句,隐式提交)，delete是一行一行的删除，可以rollback
2、效果：truncate删除后将重新水平线和索引(id从零开始) ,delete不会删除索引
3、 truncate 不能触发任何Delete触发器。
4、delete 删除可以返回行数

各种函数和用法

SELECT name,salary*12 FROM myemp;

 

-- 年薪

SELECT CONCAT('name:',name) FROM myemp;

 

-- CONCAT 字符串连接函数

SELECT CONCAT(name,salary) FROM myemp

 

mysql CONCAT(str1,str2,…)
返回结果为连接参数产生的字符串。
如有任何一个参数为NULL ，则返回值为 NULL。
或许有一个或多个参数。 如果所有参数均为非二进制字符串，则结果为非二进制字符串。
如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。
一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast,
例如： SELECT CONCAT(CAST(int_col AS CHAR), char_col)

CONCAT_WS(separator,str1,str2,…)
CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。 第一个参数是其它参数的分隔符。
分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。
如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

SELECT CONCAT(name,',',salary) FROM myemp

 

--两个相等

SELECT CONCAT_WS(',',name,salary) FROM myemp

SELECT 'zong' || 'xuan' FROM DUAL;

 

-> 0
--MySQL 没有 || 连接字符串

 

SELECT LENGTH(salary) FROM myemp
SELECT LENGTH(name) FROM myemp

 

-- LENGTH()字符串的长度

 

SELECT UPPER('LiSan') ,LOWER('LiSan')

 

-- UPPER 转大写 LOWER 转小写
--MySQL 没有首字母大写函数

SELECT 1 + 1 FROM DUAL;
--DUAL 伪表 5.1以后

SELECT TRIM('zong' from 'zongxuan') FROM DUAL;

 

-> xuan
-- TRIM 把前面的从全部后面拿掉 不管有几次

SELECT TRIM(' zongxuan ') FROm DUAL;

 

->zongxuan
--TRIM 去空格

SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); 

 

-> 'barxxx'
--删除指定的首字符 x

SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); 

 

-> 'bar'
--删除指定的首尾字符 x

SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); 

 

-> 'barx'
--删除指定的尾字符 x

SELECT LTRIM(' barbar'); 

 

-- 左空格

SELECT RTRIM('barbar '); 

 

-- 右空格

 

SELECT name,LPAD(salary,7,'$') FROM myemp

 

-- 补位函数

SELECT name,LPAD(salary,7,' ') FROM myemp

 

--右对齐

SELECT name,RPAD(salary,10,' ') FROM myemp

 

--左对齐
LPAD(str,len,padstr)
用字符串 padstr对 str进行左边填补直至它的长度达到 len个字符长度，然后返回 str。如果 str的长度长于 len'，那么它将被截除到 len个字符。

mysql> SELECT LPAD('hi',4,'??'); -> '??hi'
RPAD(str,len,padstr)
用字符串 padstr对 str进行右边填补直至它的长度达到 len个字符长度，然后返回 str。如果 str的长度长于 len'，那么它将被截除到 len个字符。
mysql> SELECT RPAD('hi',5,'?'); -> 'hi???'

SELECT SUBSTR('zongxuan',5,4) FROM DUAL;
-> xuan
--SUBSTR 字符串截取 从第5个开始截取4个字符
SELECT SUBSTR('zongxuan',-4,4)
-- 倒数第4个开始截取4个字符

SELECT INSTR('zongxuan','n')
-- 查找 n 在zongxuan 中的位置 没有 更高级的用法


SELECT ROUND('123.123456',0) FROM DUAL;
SELECT ROUND('123.123456',-1) FROM DUAL;
SELECT ROUND('123.123456',5) FROM DUAL;
--正数 四舍五入到小数点后第m位
--0 四舍五入到整数位
--负数 四舍五入到小数点前m位


--MySQL 没有 TRUNC 函数 Oracle有

 

SELECT MOD(salary,1000) FROM myemp
-- 求余数


SELECT CEIL(45.678) FROM DUAL
--向上取整

SELECT FLOOR(45.678)
--向下取整

 


SELECT SYSDATE() FROM DUAL;
--系统时间

 

now()函数的同义词有：CURRENT_TIMESTAMP 、 CURRENT_TIMESTAMP()、LOCALTIMESTAMP 、 LOCALTIMESTAMP()、LOCALTIME 、 LOCALTIME()
SYSDATE( )：返回服务器的当前日期和时间
与now的不同点：(一般使用NOW而不用SYSDATE)
　　SYSDATE()返回的是函数执行时的时间
　　now()返回的是语句执行时的时间

 

SELECT CURTIME() FROM DUAL;
--CURTIME() 返回当前时间 只包含时分秒

SELECT CURDATE() FROM DUAL;
--CURDATE()：返回当前日期，只包含年月日

SELECT TIMEDIFF(NOW(),'2018-01-31 10:59:00') FROM myemp;
--TIMEDIFF(expr1, expr2)：返回两个日期相减（expr1 − expr2 ）相差的时间数（两个参数类型必须相同）
--是时间 不是日期
SELECT DATEDIFF(NOW(),'2018-02-05 10:59:00') FROM myemp;
--DATEDIFF(expr1, expr2)：返回两个日期相减（expr1 − expr2 ）相差的天数
-- 是天数

SELECT DATE_ADD(NOW(),interval 1 HOUR)
-- 加一小时
SELECT DATE_ADD(NOW(),interval 1 MINUTE)
-- 加一分钟
SELECT DATE_ADD(NOW(),interval 1 SECOND)
-- 加一秒钟
SELECT DATE_ADD(NOW(),interval 1 MICROSECOND)
-- 加一毫秒

 

 

HOUR 小时| MINUTE 分 | SECOND 秒 | MICROSECOND 毫秒

YEAR 年 | MONTH 月 | DAY 日 | WEEK 周 | QUARTER 季

YEAR_MONTH 年和月 | DAY_HOUR 日和小时 | DAY_MINUTE 日和分钟 | DAY_SECOND 日和秒

HOUR_MINUTE 小时和分 | HOUR_SECOND 小时和秒 | MINUTE_SECOND分钟和秒

 

SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
-- 不使用函数，也可以写表达式进行日期的加减

 

6、选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒（常用）

SELECT now(),date(now()); -- 日期

SELECT now(),time(now()); -- 时间

SELECT now(),year(now()); -- 年

SELECT now(),quarter(now()); -- 季度

SELECT now(),month(now()); -- 月

SELECT now(),week(now()); -- 周

SELECT now(),day(now()); -- 日

SELECT now(),hour(now()); -- 小时

SELECT now(),minute(now()); -- 分钟

SELECT now(),second(now()); -- 秒

SELECT now(),microsecond(now()); -- 微秒

 

EXTRACT(unit FROM date)：从日期中抽取出某个单独的部分或组合

SELECT now(),extract(YEAR FROM now()); -- 年

SELECT now(),extract(QUARTER FROM now()); -- 季度

SELECT now(),extract(MONTH FROM now()); -- 月

SELECT now(),extract(WEEK FROM now()); -- 周

SELECT now(),extract(DAY FROM now()); -- 日

SELECT now(),extract(HOUR FROM now()); -- 小时

SELECT now(),extract(MINUTE FROM now()); -- 分钟

SELECT now(),extract(SECOND FROM now()); -- 秒

SELECT now(),extract(YEAR_MONTH FROM now()); -- 年月

SELECT now(),extract(HOUR_MINUTE FROM now()); -- 时分

 

UNIX_TIMESTAMP() 当前系统时间戳

个性化显示时间日期

SELECT now(),dayofweek(now());
--　　dayofweek(date)
--一周的第几天
SELECT now(),dayofmonth(now());
--　　dayofmonth(date)
--一月的第几天
select now(),dayofyear(now());
--　　dayofyear(date)
--一年的第几天
set lc_time_names='zh_CN';
-- 设定 名称是中文or英文的由系统变量lc_time_names控制(默认值是'en_US')
select dayname(now())
--    dayname() 返回日期的星期
select dayname(now()),monthname(now());
--　　monthname()月份名称

show variables like 'lc_time_names';
--名称是中文or英文的由系统变量lc_time_names控制(默认值是'en_US')


-- 0时区 -> 东八区 
SELECT CONVERT_TZ('2014-01-01 01:00:00','+00:00','+8:00') aa; 
-- 西八区 -> 东八区 
SELECT CONVERT_TZ('2014-01-01 01:00:00','-8:00','+8:00') aa;

 

- 增加时间 
select ADDTIME(NOW(), '30:0:0') aa,ADDTIME('10:10:10', '30:0:0') bb; 
select ADDTIME(NOW(), '1 1:1:1.000002') aa,ADDTIME('10:10:10', '1 1:1:1.000002') bb; 
select ADDTIME(NOW(), '100 01:01:01.000002') aa,ADDTIME('10:10:10', '100 01:01:01.000002') bb;


-- 当前时间戳 
select CURRENT_TIMESTAMP() a1,CURRENT_TIMESTAMP a2,NOW() a3; 
select NOW() a1,LOCALTIME a2,LOCALTIME() a3,LOCALTIMESTAMP a4,LOCALTIMESTAMP () a5;


-- 两个时间相减(日期部分相减,返回天数) 
select DATEDIFF('2014-1-1 10:00:00','2014-2-2 01:00:00') aa; 
select TIMEDIFF('2014-1-1 10:10:10','2014-2-2 01:20:30') aa; 
SELECT TIMESTAMPDIFF(YEAR,'2003-02-01','2003-05-01') aa; 
SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') aa; 
SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01'); 
SELECT TIMESTAMPDIFF(HOUR,'2003-02-01','2003-05-01');


-- 从数字数据获取时间 
SELECT FROM_DAYS(730669.89) aa; 
SELECT FROM_UNIXTIME(1196440219.12) aa; 
SELECT FROM_UNIXTIME(1196440219) + 0 aa; 
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x');


--LAST_DAY获取当前月份的最后一天
SELECT LAST_DAY('2014-02-05') aa; 
SELECT LAST_DAY('2014-02-05 11:11:11') aa; 
SELECT LAST_DAY(NOW()) aa;

--UTC时间获取
select UTC_DATE() a1,UTC_TIME() a2,UTC_TIMESTAMP() a3;

--时间戳加减
SELECT TIMESTAMPADD(YEAR,1,'2014-01-02') aa; 
SELECT TIMESTAMPADD(MONTH,1,'2014-01-02') aa; 
SELECT TIMESTAMPADD(DAY,1,'2014-01-02') aa; 
SELECT TIMESTAMPDIFF(YEAR,NOW(),'2014-01-02') aa; 
SELECT TIMESTAMPDIFF(MONTH,NOW(),'2014-01-02') aa; 
SELECT TIMESTAMPDIFF(DAY,NOW(),'2014-01-02') aa;

--时间格式化
select TIME_FORMAT('08:08:01','%h:%i:%s') aa; 
select TIME_FORMAT(current_time,'%H:%m:%s') aa;


--秒换算时间
select SEC_TO_TIME(60) aa; 
select SEC_TO_TIME(3600) aa; 
select TIME_TO_SEC(current_time) aa; 
select TIME_TO_SEC('00:10:10') aa;

--计算月份差
SELECT PERIOD_DIFF(201402,201403) aa; 
SELECT PERIOD_DIFF('201402','201403') aa;

--增加月份
select PERIOD_ADD('201401',3) aa; 
select PERIOD_ADD('1401',3) aa;


--创建日期、时间
select MAKEDATE(2014,10) aa; 
select MAKETIME(13,1,6) aa;

 

 

 

 

NULL
SELECT * FROM myemp WHERE birth IS NULL
SELECT * FROM myemp WHERE birth IS NOT NULL
--空值判断
NULL 与任何数字运算还是NULL
NULL 与字符串拼接等于什么都没干


SELECT IFNULL(null,'123')
--IFNULL(expr1,expr2) 
--如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。 
--MySQL 没有NVL()
SELECT name,salary+IFNULL(comm,0) FROM myemp


SELECT name,salary,IF(comm IS NULL,'N','Y') FROM myemp
--MySQL 没有NUVL2() 用这个替换

 


SELECT name AS 姓名 FROM myemp
-- AS 别名


SELECT name AS 姓名 FROM myemp WHERE id=1;
--WHERE 查询条件
-- > 大于 < 小于 >= 大于等于 <= 小于等于 != 不等于 <> 不等于 = 等于 
SELECT name FROM myemp WHERE id=1 OR id=2;
-- AND 并 OR 或 AND的优先级高于OR 使用() 来提高OR的优先级

SELECT * FROM myemp WHERE name LIKE '_zong%';
--LIKE 模糊查询 两个通配符 % 和_ % 0到任意个 _任意一个


SELECT * FROM myemp WHERE jobid IN (1,2,3,4,5);
SELECT * FROM myemp WHERE jobid NOT IN (1,2,3,4,5);
-- IN 和NOT IN 判断是否在列表中或不在列表中

SELECT * FROM myemp WHERE salary BETWEEN 1500 AND 3000;
-- BETWEEN AND 在一个区间


SELECT * FROM myemp WHERE deptno < ALL(SELECT comm FROM myemp)

SELECT * FROM myemp WHERE deptno > ANY(SELECT comm FROM myemp)
-- ANY 和ALL 不能单独使用，需要配合比较操作符 > >= < <= 一起使用 
-- >ANY 大于最小 <ANY 小于最大 >ALL 大于最大 <ALL 小于最小
-- 用于子查询

SELECT DISTINCT job FROM myemp;
--DISTINCT 去重
SELECT DISTINCT job,deptno FROM myemp;
--多字段去重 是指的job 和deptno 的组合没有重复的
SELECT DISTINCT count_time ,id c FROM es_gw
SELECT COUNT(DISTINCT count_time ,id) c FROM es_gw


SELECT * FROM myemp ORDER BY id 
--ORDER BY ASC 排序 升序 默认的
-- ORDER BY DESC 排序 降序
-- ORDER BY 写在最后
--NULL 被认作 最大值
SELECT * FROM myemp ORDER BY deptno,salary
--多字段排序 先排第一个字段，在拍第二个字段
SELECT * FROM myemp ORDER BY deptno DESC ,salary
SELECT * FROM myemp ORDER BY deptno DESC ,salary DESC

 

聚合函数

MAX MIN COUNT AVG SUM
--聚合函数 对于NULL值都是忽略的

SELECT AVG(IFNULL(comm,0)) FROM myemp
--对于 NULL都是忽略 所以有部分的需求就是要 将NULL 转为 0

 

 

分组
配合聚合函数进行更细分的统计工作
GROUP BY

 

SELECT AVG(salary),deptno FROM myemp GROUP BY deptno
-- 按照 deptno 分组 统计 每个部门的评价工资

SLELCT deptno,job,AVG(salary) FROM myemp GROUP BY deptno,job
--按照多字段分组 就是 多个字段的组合为一组

 

当SELECT 子句中含有聚合函数时 那么凡看不在聚合函数中的其他单独字段都必须出现在GROUP BY 自居中， 反过来则不是必须的
-----------------------------------------------------------------------

SLELCT deptno,job,AVG(salary) FROM myemp GROUP BY deptno,job

SELECTR 中有deptno 和job 那么 GROUP BY 必须有 deptno 和job

SLELCT job,AVG(salary) FROM myemp GROUP BY deptno,job

GROUP BY 有 deptno 和job 那么 SELECTR 可以没有deptno

-----------------------------------------------------------------------

 

SELECT m_ip,COUNT(*) AS count_4ip FROM tou_connection2 GROUP BY m_ip
--ip 登陆数统计
SELECT MAX(count_4ip) FROM (SELECT m_ip,COUNT(*) AS count_4ip FROM tou_connection2 GROUP BY m_ip) AS TEMP
--ip登陆数最大的
SELECT m_ip,COUNT(*) FROM tou_connection2 GROUP BY m_ip HAVING COUNT(*)= (SELECT MAX(count_4ip) FROM (SELECT m_ip,COUNT(*) AS count_4ip FROM tou_connection2 GROUP BY m_ip) AS TEMP)
--ip 和 登陆数 HAVING 作为分组的条件查询

 

WHERE 中不能使用聚合函数作为过滤条件，过滤的时间不对 WHERE 是在数据库检索表中的数据时，对数据逐条过滤以决定是否查询出来该数据 所以 WHERE 时用来确认结果集的数据

使用聚合函数的结果作为过滤条件， 那么一定时数据从表中查询完毕（WHERE是查询过程中有用）得到结果及，并且分组完毕才进行聚合函数统计结果得到后才可以对分组进行过滤 这个过滤的时机是在where之后的

查询语句执行的顺序
SQL 语句的优化：
FROM 子句 从后往前 从右到左 数据量较少的表放后面
WHERE 子句 想下而上 从右到左 将能过滤最大数量记录的条件写在 WHERE 的最右
GROUP BY 子句 从左到右分组 最好在GROUP BY 前使用WHERE 将不需要的记录在GROUP BY之前过滤掉
HAVING 子句 消耗资源 尽量避免使用 HAVING 会在检索出所有记录后才 对结果集进行过滤 需要排序等 操作
SELECT 子句 少使用*
ORDER BY子句 执行顺序从左到右 排序 消耗资源

关联查询
从多张表中查询对应记录的信息 关联查询的重点在于这些表中的记录的对应的关系，这个关系也称为连接条件

SELECT ename,dname 
FROM emp,dept
WHERE emp.deptno=deptno.deptno

 

关联查询要添加连接条件，否则会产生笛卡儿积 通常是一个 无意义的结果集，他的记录是所有参与查询的表的记录数乘积的结果 要避免 数据量大的时候极易出现内存溢出等
N张表关联查询要右至少N-1个连接条件

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。 (所有满足条件的)
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。()
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
MySQL 不支持 全连接 ：但是可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join.

select * from A left join B on B.name = A.name union select * from A right join B on B.name = A.name;
--全连接
SELECT e.name,d.dname 
FROM emp e JOIN dept d 
ON e.deptno=d.deptno 
WHERE e.job='asd'

select * from A right join B on A.name = B.name;
--右外连接
select * from A left join B on A.name=B.name where A.id is not null and B.id is not null;
--左外连接

 

MySQL 不支持 (+) 代替 左右连接

自连接
当前表的的一条记录可以对应当前表自己的多条记录
自连接是为了解决同类型数据但是又存在上下级关系的树状结构数据使用
同类型数据有上下级

SELECT e.ename,m.ename,d.loc FROM emp e , emp m , dept d WHERE e.mgr=m.empno AND m.deptno=d.deptno AND e.ename='SMITH'
-- JOIN ON 更好 更明了 
SELECT e.ename,m.ename,d.loc FROM emp e JOIN emp m ON e.mgr=m.empno JOIN dept d ON m.deptno=d.deptno WHERE e.ename='SMITH'

 

同一张表可以看作员工表 有可以看作 领导表
表中有员工编号 又有 领导编号

子查询
子查询是一条SELECT 语句 但它是嵌套在其他SQL 语句中为的是给该SLQL 提供数据以支持其执行操作

SELECT ename,sal
FROM emp 
WHERE sal>(SELECT sal FROM emp WHERE ename='CLARK')

 

CREATE TABLE test
AS
SELECT bts.id,b.name,bts.trs_name
FROM brokers AS b INNER JOIN broker_trade_services AS bts ON b.id=bts.broker_id
--查询结果创建表
--使用 子查询 创建表的时候 字段有使用运算或者函数 必须使用别名


DELETE FROM emp 
WHERE deptno=(SELECT deptno 
FROM emp    
WHERE name='CLARK')


SELECT ename,job
FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp)

 

子查询根据查询结果集的不同分为
单行单列子查询 常用月过滤条件 可以配合 = > >= < <=使用
多行单列子查询 常用于过滤条件 由于查询多多个值 在判断=时要用IN 判断 > >= < <= 要配合 ANY ALL
多行多列子查询 那就是一张表

SELECT ename
FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE job IN('CLERK','SALESMAN'))

 

EXISTS 后面跟一个子查询 当该子查询可以查询出来至少一条记录时，则 EXISTS 表达式成立并返回true

 

SELECT MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal) > (SELECT MIN(sal) 
FROM emp 
WHERE deptno=30)

 

 

SELECT e.ename,e.sal,e.deptno,temp.avg_sal
FROM emp AS e LEFT JOIN (SELECT AVG(sal) AS avg_sal,deptno 
FROM emp 
GROUP BY deptno) AS temp ON e.deptno=temp.deptno
HAVING e.sal>temp.avg_sal

 

在SELECT 子句中使用子查询 可以将查询结果当作外层查询记录中的一个字段值显示， 这样就实现了外连接

 

分页查询
分页查看是将查询表中数据时分段查询，而不是一次性将所有数据查询出来 优势查询的数据两非常大 这回导致系统资源消耗大 响应时间长，数据冗余严重
所以我们要使用分页查询 数据库都支持分页 但是没有标准 所以不同的数据库有不同的方法

MySQL 使用limit 函数分页

SELECT * FROM emp LIMIT 0,3
--前三个

SELECT * FROM emp ORDER BY sal DESC LIMIT 6,5
--第6到第10 从第几个开始取，取多少个

 

DECODE 函数
MySQL 没有 可以用IF 来代替

row_number()
MySQL 没有

rank()
MySQL 没有

dense_rank
MySQL 没有

集合操作符

并 交 差

UNION 并
A表中有 B表中没有 出现一次
A表中没有 B表中有 出现一次
A表中有 B表中有 出现一次
A表中没有 B表中没有 不出现

UNION ALL 全并
A表中有 B表中没有 出现一次
A表中没有 B表中有 出现一次
A表中有 B表中有 出现两次
A表中没有 B表中没有 不出现

INTERSECT 交
A表中有 B表中没有 不出现
A表中没有 B表中有 不出现
A表中有 B表中有 出现一次
A表中没有 B表中没有 不出现

MINUS 差
A表中有 B表中没有 出现一次
A表中没有 B表中有 不出现
A表中有 B表中有 不出现
A表中没有 B表中没有 不出现

多条左集合操作的SELECT语句的列的个数和数据类型必须匹配
ORDER BY 只能放在最后一个查询语句中

 

高级分组函数
高级分组函数用在GROUP BY 子句中每个高级分组函数都有一套分组策略

MySQL 生成随机数
RAND()

SELECT CEIL(RAND()*2+2010)

生成2011-2012的随机数

ROLLUP 分组原则参数主次递减 一直到所有参数都不要，每一种分组都统计一次结果并且并在一个结果集显示.、
2、rollup注意：

1）ORDER BY不能在rollup中使用，两者为互斥关键字；

2）如果分组的列包含NULL值，那么rollup的结果可能不正确，因为在rollup中进行的分组统计时，null具有特殊意义。因此在进行rollup时可以先将

null转换成一个不可能存在的值，或者没有特别含义的值，比如：IFNULL(xxx,0)

3）mysql中没有像oracle那样的grouping()函数；

CREATE TABLE `sales_tab` ( 
`year_id` int(11) NOT NULL, 
`month_id` int(11) NOT NULL, 
`day_id` int(11) NOT NULL, 
`sales_value` int(11) NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

INSERT INTO sales_tab
SELECT CEIL(RAND()*2+2010) AS year_id,
CEIL(RAND()*12) AS month_id,
CEIL(RAND()*31) AS day_id,
RAND()*100+210 AS sales_value
FROM dual

 

delimiter $$
create procedure insert_info()
begin 
DECLARE done INT DEFAULT 0; 
while(done<=23) do 
INSERT INTO sales_tab SELECT CEIL(RAND()*2+2010) AS year_id,CEIL(RAND()*12) AS month_id,CEIL(RAND()*31) AS day_id,RAND()*100+210 AS sales_value FROM dual;
set done=done+1; 
end while; 
end $$

call insert_info();

 

 

SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab GROUP BY year_id,month_id,day_id WITH ROLLUP
--和ORACLE 的用法不一样 效果一样

 

CUBE() 每种组合分一次组
分组次数是2的参数个数次方
但是MySQL 只定义了 没有实现

视图
视图 VIEW 也被称作虚表 是一个数据的逻辑表示
视图对应于一条SELECT 语句 结果集被赋予一个名字 就是视图名字
视图本身不包含任何数据 它只包含映射到基表的一个查询语句 当基表的数据发生变化 视图数据也随之变化
视图在SQL语句中体现的角色与表相同但是视图并不是一张真实存在的表而只是对应一个SELECT 语句的查询结果集
并将它当作表来看待
使用视图的目的是简化SQL语句的复杂度重用了查询，限制数据访问 视图本质就是一条SELECT 语句 所以当访问视图的时候只能
访问到对应的SELECT语句中涉及的列，对基表中其他列起到安全和保密的作用

CREATE VIEW
--创建视图
CREATE VIEW v_emp_10 
AS
SELECT empno,ename,sal,deptno FROM emp WHERE deptno=10;
--创建一个视图 视图的名字一般用v_ 开始
SELECT * FROM v_emp_10
-- 在视图中查询

 

修改视图
OR REPLACE 短语修改视图对应的SLQL 查询语句

CREATE OR REPLACE VIEW v_emp_10 
AS
SELECT empno id,ename name,sal salary,deptno FROM emp 
WHERE deptno=10;

 

视图分类
简单视图
--最简单的没有函数没有处理
复杂视图
--有函数之类的
连接视图
--多个表联查

对视图进行DML操作
仅能对简单视图进行DML操作

对视图进行操作可能对基表进行数据污染

 

WITH CHECK OPTION

--通过视图所做的就该必须在视图的可见范围内 假设INSERT 新增的记录在视图仍可查看
假设UPDATE 修改后的结果必须能通过视图查看到

 

为视图添加只读选项

WITH READ ONLY

 

--只能看不能改

DROP VIEW v_emp_10

--删除视图 不会影响基表数据

 

数据字典
--MySQL 数据库中的一些信息 比如 有多少表，表的名字

information_schema库中

SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。

TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。

COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。

STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。

USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。

SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。

TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。

COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。

CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。

COLLATIONS表：提供了关于各字符集的对照信息。

COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。

TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。

KEY_COLUMN_USAGE表：描述了具有约束的键列。

ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。

TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表

 

 

 

 

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

MySQL中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义列。

CREATE TABLE insect
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL, 
date DATE NOT NULL,
origin VARCHAR(30) NOT NULL 
);

 

在MySQL的客户端中你可以使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。

如果你删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。 不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。操作如下所示：

ALTER TABLE insect DROP id;

ALTER TABLE insect
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);

 

一般情况下序列的开始值为1，但如果你需要指定一个开始值100，那我们可以通过以下语句来实现：

CREATE TABLE insect
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL, 
date DATE NOT NULL,
origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

 

或者你也可以在表创建成功后，通过以下语句来实现：

ALTER TABLE t AUTO_INCREMENT = 100;

SET auto_increment_increment=10; #自增量每次增加的值改为10，
SET auto_increment_offset=2; #第一次加载数值时的偏移值的个位值
SHOW VARIABLES LIKE 'AUTO_INC%'; #查看修改后变量的值
SHOW TABLE STATUS FROM NAME_DB; -->显示数据库NAME_DB中所有表的信息
SHOW CREATE TABLE NAME_TBL; -->显示表NAME_TBL创建时的信息
SHOW VARIABLES LIKE 'AUTO_INC%' -->显示MySQL的AUTO_INC开头的用户会话变量(SHOW GLOBAL VARIABLES)

 

 

索引

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

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

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

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

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

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

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

索引 一般是以 idx 开头

索引的统计

CREATE INDEX idx_name ON emp(ename);
-- 给emp 表的ename 字段创建一个索引名字是idx_name

ALTER TABLE emp ADD INDEX idx_id(id);
--修改表结构(添加索引)

复合索引 多个字段 
CREATE INDEX idx_name2 ON emp(ename,job);
--不一样的
CREATE INDEX idx_name1 ON emp(job,ename);

SELECT ename,sal,job FROM emp ORDER BY job,ename
--用到的是idx_name1这个索引 顺序有要求

 

基于函数的索引
......
如果经常在索引列上 执行DML操作 需要定期重建索引
提高索引的空间利用率

alter index idx_a1 rebuild online;

 

DROP INDEX idx_name2;

删除索引

添加索引的原则：
不要在小表上 添加
为经常出现在WHERE 子句中的列创建索引
为经常出现在ORDER BY DISTINCT 后面的字段建立索引
如果要建立复合索引 索引的字段顺序要和这些关键字后面的字段一致
为经常作为表的连接条件的列上创建索引
限制表上的索引数目索引不是越多越好
删除很少被使用的不合理的索引

约束的作用
非空约束 =》NOT NULL NN
唯一约束 =》Unique NK
主键约束 =》Primary Key PK
外键约束 =》Foreign Key FK
检查约束 =》Check CK

列级别：

CREATE TABLE table_name(column_name data_type
　　[ [NOT NULL] | [UNIQUE [KEY] | PRIMARY KEY]
　　|CHECK(expr)],…)

 

表级别：

CREATE TABLE table_name(
　　column_name data_type [NOT NULL],
　　column_name data_type [not null],…,
　　[CONSTRAINT constraint_name] PRIMARY KEY (col_name,...)
　　|[CONSTRAINT constraint_name] unique (col_name,...)
　　|[CONSTRAINT constraint_name] foreign KEY (col_name) REFERENCES tbl_name (index_col_name)
　　|check(expr)
);

 

　1）NOT NULL约束只能在列级别定义，作用在多个列上的约束只能定义在表级别，例如复合主键约束；

　　2）列级别上不能定义外键约束，并且不能给约束起名字，由MySQL自动命名（NOT NULL除外）；

　　3）表级别上定义的约束可以给约束起名字（CHECK约束除外）

mysql> create table temp_uk(
-> id int not null unique,
-> name varchar(20) unique);

mysql> insert into temp_uk values(1,'a');
mysql> insert into temp_uk values(2,'a');
ERROR 1062 (23000): Duplicate entry 'a' for key 'name'
mysql> insert into temp_uk values(2,null);
mysql> insert into temp_uk values(3,null);

mysql> select * from temp_uk;
+----+------+
| id | name |
+----+------+
| 2 | NULL |
| 3 | NULL |
| 1 | a |
+----+------+

 

2、unique唯一约束

　　1.唯一约束是指定table的列或列组合不能重复，保证数据的唯一性，约束的列不允许有重复值；

　　2.唯一约束不允许出现重复的值，但是可以为多个null；

　　3.同一个表可以有多个唯一约束，多个列组合的约束
可见，唯一性约束的列可以有多个null值，因为null <> null

　　4.在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同；

　　5.唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。

mysql> create table test(
-> id int not null,
-> name varchar(20),
-> password varchar(16),
---使用表级约束语法 
-> constraint uk_name_pwd unique(name,password)
-> ); #表示用户名和密码组合不能重复
Query OK, 0 rows affected (0.08 sec)

查询数据字典，查看唯一键约束的信息
mysql> select * from information_schema.table_constraints
-> where table_name='test';


mysql> create table temp_pk(
-> id int primary key);

mysql> insert into temp_pk values(1),(2);

mysql> insert into temp_pk values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> update temp_pk set id=1 where id=2;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> insert into temp_pk values(null);
ERROR 1048 (23000): Column 'id' cannot be null

 

3、primary key主键约束

　　primary key = not null + unique

主键：用来唯一的标示表中的每一行(类型一般为整型或者字符串)

　　具有主键约束的列不允许有null值，并且不允许有重复值;

　　每个表最多只允许一个主键(可定义联合主键)，主键名总是PRIMARY。
!!给主键一个新的名字，但在数据字典中，主键名还是显示primary

 

mysql> create table temp_pk(
-> id int,
-> name varchar(20),
-> constraint pk_id_name primary key(id,name)
-> );
Query OK, 0 rows affected (0.06 sec)

mysql> desc temp_pk;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)

mysql> insert into temp_pk values(1,'张三');
Query OK, 1 row affected (0.00 sec)
mysql> insert into temp_pk values(2,'李四');
Query OK, 1 row affected (0.01 sec)

mysql> insert into temp_pk values(1,'王五');
Query OK, 1 row affected (0.00 sec)

mysql> insert into temp_pk values(1,'张三');
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'PRIMARY'

mysql> select * from temp_pk;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 1 | 王五 |
| 2 | 李四 |
+----+--------+
rows in set (0.00 sec)

 

联合主键（用几个列进行唯一标识一行）

 

//创建父表

mysql> create table dept(
-> deptid int,
-> dname varchar(20),
-> constraint dept_deptid_pk primary key(deptid)
-> );

mysql> insert into dept(deptid,dname) values(10,'市场部');
mysql> insert into dept(deptid,dname) values(20,'销售部');

 

//创建子表（表级别创建外键约束）

mysql> create table emp(
-> id int,
-> name varchar(20),
-> deptid int,
-> constraint emp_id_pk primary key(id),
-> constraint emp_deptid_fk foreign key(deptid)
-> references dept(deptid)
-> );

 

4、foreign key外键约束

外键约束：

　　参照完整性约束，保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

注意：

　　1）具有外键约束的列的值不能随便给，必须满足外键所引用的主键的取值；

　　2）一张表中可以定义多个外键；

　　3）外键列默认可以给null值。

按照定义，外键必须引用一个主键或者唯一键，引用的主键一般在另外一张表中，也可以是本表的主键(后者称为“自引用”)。

父子表：

　　外键所在的表叫做子表、从表

　　外键所引用的主键所在的表叫做父表、主表

注意：父子表是相对而言的，表a可以是表b的子表，但同时也可以是表c的父表

查询数据字典，查看外键约束的信息：

　　上面创建子表的时候给外键约束命名emp_deptid_fk；

　　如果不给外键约束命名，那么默认的名字是表名_ibfk_n, n是整数,从1开始；

 

mysql> insert into emp(id,name,deptid) values(1,'张三',10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into emp(id,name,deptid) values(2,'李四',10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into emp(id,name,deptid) values(3,'王五',50);　　#insert主表deptid列没有的数据
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_deptid_fk` FOREIGN KEY (`deptid`) REFERENCES `dept` (`deptid`))

mysql> update emp set deptid=30 where id=1;　　#update主表deptid列没有的数据
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_deptid_fk` FOREIGN KEY (`deptid`) REFERENCES `dept` (`deptid`))
外键的update更新操作规则如下删除规则……

mysql> delete from dept where deptid=10;　　#delete父表中的行(子表中有引用的数据行)
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_deptid_fk` FOREIGN KEY (`deptid`) REFERENCES `dept` (`deptid`))

 

外键的默认删除规则：
　　当删除父表中的行时，如果子表中有依赖于被删除父行的子行存在，那么就不允许删除，并抛出异常（默认对外键使用on delete restrict 或on delete no action选项）

 

reference_definition:
REFERENCES tbl_name (index_col_name,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION

在定义外键约束时，通过使用on delete cascade或者on delete set null选项，可以改变外键的默认删除规则：

　　①ON DELETE CASCADE：级联删除。当删除父表中的行时，如果子表中有依赖于被删除父行的子行存在，那么连同子行一起删除（很危险！！！）

　　②ON DELETE SET NULL：当删除父表中的行时，如果子表中有依赖于被删除父行的子行存在，那么不删除，而是将子行的外键列设置为null

 

5、check约束

　　MySQL可以使用check约束，但check约束对数据验证没有任何作用。

　　Oracle中可以使用check约束，是有相应的作用的。

mysql> create table test_ck(
-> id int check(id>0)
-> );

mysql> insert into test_ck values(-100);

mysql> select * from test_ck;
+------+
| id |
+------+
| -100 |
+------+

 

The CHECK clause is parsed but ignored by all storage engines。

定义数据库列时，可以使用ENUM（enumeration，枚举）和SET（集合）类型：变通的实现CHECK约束

两者的区别是：

　　使用ENUM，只能选一个值；

　　使用SET，可以选多个值；

ENUM和SET中的值都必须是字符串类型。

1、enum枚举类型
注意：

　　在内部存储ENUM值时，MYSQL给ENUM中的每个值一个顺序号码：第一个值的顺序号码是1，第二个值的顺序号码是2，以此类推。当排序或比较ENUM的时候，使用这些顺序号码进行。

mysql> select * from student order by sex;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 5 | 王五 | NULL |
| 1 | 张三 | M |
| 2 | 李四 | F |
+----+--------+------+

 

2、set类型：因为可以列举多个值，所以在建表列约束中常被使用到。

mysql> create table team(
-> teamno int not null,
-> division set('north','south','east','west')
-> );

mysql> insert into team values(1,'west');
mysql> insert into team values(2,'west,south');
mysql> insert into team values(4,null);
mysql> insert into team values(3,'east,asia');
ERROR 1265 (01000): Data truncated for column 'division' at row 1

mysql> select * from team;
+--------+------------+
| teamno | division |
+--------+------------+
| 1 | west |
| 2 | south,west |
| 4 | NULL |
+--------+------------+

 

转载于:https://www.cnblogs.com/lmdtx/p/9765758.html