Mysql

MySQL的数据类型

主要包括以下五大类：
整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
浮点数类型：FLOAT、DOUBLE、DECIMAL
字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM，TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

日期类型：Date、DateTime、TimeStamp、Time、Year
其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

1、整型

MySQL数据类型 含义（有符号）
tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(±9.22*10的18次方)

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。
int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度，不知道这个m有什么用。

2、浮点型(float和double)

MySQL数据类型 含义
float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d) 双精度浮点型 16位精度(8字节) m总个数，d小数位

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。整数部分最大是3位，如果插入数12.123456，存储的是12.1234，如果插入12.12，存储的是12.1200.

3、定点数
浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。
decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。

4、字符串(char,varchar,_text)

MySQL数据类型 含义
char(n) 固定长度，最多255个字符 varchar(n) 固定长度，最多65535个字符
tinytext 可变长度，最多255个字符 text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符

char和varchar：
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，
所以varchar(4),存入3个字符将占用4个字节。

3.char类型的字符串检索速度要比varchar类型的快。
varchar和text：
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字
节。
2.text类型不能有默认值。
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。

5.二进制数据(_Blob)

1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。
2._BLOB存储的数据只能整体读出。
3._TEXT可以指定字符集，_BLO不用指定字符集。

6.日期时间类型

MySQL数据类型 含义
date 日期 ‘2008-12-2’
time 时间 ‘12:25:36’
datetime 日期时间 2008-12-2 22:06:44’
timestamp 自动存储记录修改时间
若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

数据类型的属性

MySQL关键字 含义
NULL 数据列可包含NULL值
NOT NULL 数据列不允许包含NULL值
DEFAULT 默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增，适用于整数类型
UNSIGNED 无符号
CHARACTER SET name 指定一个字符集

子句

WHERE 子句

WHERE 子句用于过滤记录。 WHERE 子句用于提取满足指定标准的记录。

WHERE 子句中的运算符
WHERE子句中可以使用以下运算符：
运算符 描述

= 等于
<> 不等于。 注意：在某些版本的SQL中，这个操作符可能写成！=
BETWEEN
BETWEEN运算符用于拾取两个值之间的数据范围内的值。
BETWEEN运算符选择给定范围内的值。值可以是数字，文本或日期。
BETWEEN运算符是包含性的：包括开始和结束值，并且开始值需小于结束值。
LIKE ％ - 百分号表示零个，一个或多个字符 _ - 下划线表示单个字符
IN IN运算符允许您在WHERE子句中指定多个值。IN运算符是多个OR条件的简写。

order by 子句

排序
order by a
按a字段升序排列
order by a,b
按a字段升序排列，a相同，再按b字段升序排列
asc 升序（默认）
desc 降序

-- 查询50部门员工，按工资降序
select id,fname,sal,dept_id
from emps
where dept_id=50
order by sal desc;
 
-- 所有员工，按部门升序，相同部门按工资降序
select id,fname,sal,dept_id
from emps
order by dept_id, sal desc;

group by 子句，分组求多行函数
按指定字段中相同的值进行分组
分组后分别求多行函数
分组字段，可以查询
group by a
按a字段相同值分组
group by a,b
按a,b组合的相同值分组

-- 每个部门的平均工资
select dept_id, avg(sal)
from emps
where dept_id is not null
group by dept_id;

-- 每个工作岗位job_id的人数
select job_id,count(*)
from emps
group by job_id;

having子句
用来对多行函数结果进行过滤
having 和 where 作用相同，都是条件过滤
where 过滤普通条件，最早执行
having 过滤多行函数结果，分组，求完多行函数后，才执行
having 跟在 group by 后面

-- 只有一个手下的主管id
select mgr_id, count(*) c
from emps
where mgr_id is not null
group by mgr_id
having c=1;
 
-- 平均工资小于等于5000的岗位代码
select job_id, avg(sal) a
from emps
group by job_id
having a<=5000;

函数

字符串函数
char_length(字符串) 字符数
length(字符串) 字节数
left(字符串, length) 获得左侧字符
substring(字符串, start, length) 截取字符串
instr(字符串, 子串) 查找子串位置
concat(s1,s2,s3…) 字符串连接
lpad(字符串,8,’*’) 左侧填充
数字函数
ceil(数字) 向上取整到个位
floor(数字) 向下取整到个位
round(数字, 2) 四舍五入到小数点2位
truncate(数字, 2) 舍弃到小数点2位
rand() 随机数[0, 1)

-- 工资上涨 11.31%，向上取整到10位
select id,fname,sal, ceil(sal*1.1131/10)*10
from emps;
-- 所有员工随机排序
select id,fname,sal,dept_id
from emps
order by rand();

日期函数
now() 当前日期时间
curdate() 当前日期
curtime() 当前时间
extract(字段 from 日期) 抽取指定字段的值
date_add(日期, interval 字段 值) 在指定字段上加一个值
datediff(日期1， 日期2) 两个日期之间相差的天数

 -- 查询系统当前时间
select now();
 
-- 1997年入职的所有员工
select id,fname,hdate
from emps
-- where hdate between '1997-1-1'
-- and '1997-12-31';
where extract(year from hdate)=1997;
 
-- 员工已入职多少年
select id,fname,hdate,
datediff(now(), hdate)/365 y
from emps
order by y;

null值函数
ifnull(a, b)
a不是null返回a
a是null返回b

-- 年薪*提成
select id,fname,sal,
sal*12*(1+ifnull(com_pct, 0)) t
from emps
order by t desc;

多行函数、聚合函数
sum() 和
avg() 平均
max() 最大
min() 最小
count() 行数
多行函数不能和其他普通字段一起查询
多个多行函数可以一起查询
多行函数会忽略null值
count(*) 记行数

-- 最低工资值
select min(sal)
from emps;
 
-- 最低工资值
select id,fname,min(sal)
from emps;

约束类型

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。
MYSQL中，常用的几种约束：

约束类型： 主键 外键 唯一 非空 自增 默认值
关键字： primary key foreign key unique not null auto_increment default

1、主键约束 primary key
主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。
每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。
当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

-- 基本模式
create table temp( 
id int primary key,
name varchar(20)
);

-- 组合模式
create table temp(
id int ,
name varchar(20),
pwd varchar(20),
primary key(id, name)
);

-- 添加主键约束
alter table temp add primary key(id,name);

-- 修改主键约束
alter  table temp modify id int primary key；

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

-- 基本模式
-- 主表
create table temp(
id int primary key,
name varchar(20)
);

-- 副表
create table temp2(
id int,
name varchar(20),
classes_id int,
foreign key(id) references temp(id)
);


-- 多列外键组合，必须用表级别约束语法
-- 主表
create table classes(
id int,
name varchar(20),
number int,
primary key(name,number)
);

-- 副表
create table student(
id int auto_increment primary key,
name varchar(20),
classes_name varchar(20),
classes_number int,
/*表级别联合外键*/
foreign key(classes_name, classes_number) references classes(name, number) 
);


-- 删除外键约束
alter table student drop foreign key student_id;


-- 增加外键约束
alter table student add foreign key(classes_name, classes_number) references classes(name, number);

3、 唯一约束unique
唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。
唯一约束不允许出现重复的值，但是可以为多个null。
同一个表可以有多个唯一约束，多个列组合的约束。
在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。
唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。

-- 创建表时设置，表示用户名、密码不能重复
    create table temp(
    id int not null ,
    name varchar(20),
    password varchar(10),
    unique(name,password)
);


-- 添加唯一约束
alter table temp add unique (name, password);


-- 修改唯一约束
alter table temp modify name varchar(25) unique;

-- 删除约束
alter table temp drop index name;

4、非空约束 not null 与 默认值 default
非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。
Null类型特征：
　　所有的类型的值都可以是null，包括int、float 等数据类型

-- 创建table表，ID 为非空约束，name 为非空约束 且默认值为abc
create table temp(
           id int not null,
           name varchar(255) not null default  'abc',
           sex char null
)；


-- 增加非空约束
alter table temp
modify sex varchar(2) not null;

-- 取消非空约束
alter table temp modify sex varchar(2) null;

-- 取消非空约束，增加默认值
alter table temp modify sex varchar(2) default 'abc' null;

MySQL多表连接查询

连接(join)：将一张表中的行按照某个条件（连接条件）和另一张表中的行连接起来形成一个新行的过程。

根据连接查询返回的结果，分3类：
内连接（inner join）
外连接（outer join）
交叉连接（cross join） 　　
根据连接条件所使用的操作符，分2类：
相等连接（使用等号操作符）
不等连接（不使用等号操作符）

注意：
　　在连接查询中，一个列可能出现在多张表中，为了避免引起歧义，通常在列名前面加上表名或表别名作为前缀(例：s.sid、x.sid)—使用表别名作为前缀，可以使得SQL代码较短，使用的内存更少(例：stu s,xuanke as x)。

搭建环境：模拟选课
mysql> select * from stu;
+------+--------+---------+
| sid  | sname  | sphonum |
+------+--------+---------+
|    1 | 张三   |     110 |
|    2 | 李四   |     120 |
|    3 | 王五   |     130 |
+------+--------+---------+
3 rows in set (0.00 sec)

mysql> select * from tea;
+------+-----------+---------+
| tid  | tname     | tphonum |
+------+-----------+---------+
| 1113 | 相老师    |    1111 |
| 1114 | 冯老师    |    1112 |
+------+-----------+---------+
2 rows in set (0.00 sec)

mysql> select * from course;
+------+--------+
| cid  | cname  |
+------+--------+
|    1 | linux  |
|    2 | mysql  |
|    3 | hadoop |
+------+--------+
3 rows in set (0.00 sec)

mysql> select * from xuanke;
+------+------+------+--------+
| sid  | tid  | cid  | xuefen |
+------+------+------+--------+
|    1 | 1113 |    2 |      2 |
|    1 | 1114 |    1 |      4 |
|    1 | 1113 |    3 |      6 |
|    2 | 1113 |    2 |      2 |
|    2 | 1114 |    1 |      2 |
|    2 | 1113 |    3 |      2 |
+------+------+------+--------+
6 rows in set (0.00 sec)

1、内连接inner join
　　只返回两张表中所有满足连接条件的行，即使用比较运算符根据每个表中共有的列的值匹配两个表中的行。(inner关键字是可省略的)
①传统的连接写法：
　　在FROM子句中列出所有要连接的表的名字（进行表别名），以逗号分隔；
　　连接条件写在WHERE子句中；
注意：一旦给表定义了别名，那么原始的表名就不能在出现在该语句的其它子句中

mysql> select s.sname,c.cname,t.tname,x.xuefen
    -> from stu s,tea t,course c,xuanke x
    -> where s.sid=x.sid and t.tid=x.tid and c.cid=x.cid;
+--------+--------+-----------+--------+
| sname  | cname  | tname     | xuefen |
+--------+--------+-----------+--------+
| 张三    | linux  | 冯老师     |      4 |
| 李四    | linux  | 冯老师     |      2 |
| 张三    | mysql  | 相老师     |      2 |
| 李四    | mysql  | 相老师     |      2 |
| 张三    | hadoop | 相老师     |      6 |
| 李四    | hadoop | 相老师     |      2 |
+--------+--------+-----------+--------+
6 rows in set (0.08 sec)

②使用on子句(常用)：笔者比较喜欢的方法，因为觉得结构清晰明了。

mysql> select s.sname,t.tname,c.cname,x.xuefen
    -> from stu s
    -> join xuanke x
    -> 　　on s.sid=x.sid
    -> join tea t
    -> 　　on x.tid=t.tid
    -> join course c
    -> 　　on c.cid=x.cid;

结果如上……
表之间的关系以JOIN指定，ON的条件与WHERE条件相同。
③使用using子句

mysql> select s.sname,t.tname,c.cname,x.xuefen
    -> from stu s
    -> join xuanke x
    -> 　　using(sid)
    -> join tea t
    -> 　　using(tid)
    -> join course c
　　 -> 　　using(cid);

结果如上……
表之间的关系以join指定，using(连接列)进行连接匹配，类似于on。（相对用的会比较少）

2、外连接outer join
　　使用外连接不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。
在MySQL数据库中外连接分两类(不支持全外连接)：
　　左外连接、右外连接。(outer关键字可省略)。
共同点：都返回符合连接条件和查询条件（即：内连接）的数据行
不同点：
　　①左外连接还返回左表中不符合连接条件，但符合查询条件的数据行。(所谓左表，就是写在left join关键字左边的表)
　　②右外连接还返回右表中不符合连接条件，但符合查询条件的数据行。(所谓右表，就是写在right join关键字右边的表)

mysql> select s.sname,x.xuefen
    -> from stu s
    -> left join xuanke x
    -> on s.sid=x.sid;
+--------+--------+
| sname  | xuefen |
+--------+--------+
| 张三   |      2  |
| 张三   |      4  |
| 张三   |      6  |
| 李四   |      2  |
| 李四   |      2  |
| 李四   |      2  |
| 王五   |   NULL  |
+--------+--------+
7 rows in set (0.00 sec)

解析：stu表是左表，xuanke表是右表：left join是左连接，stu表中”王五”没有选课，在xueke表中没有数据行，不符合连接条件，返回符合查询条件的数据行，所以xuefen为null。

mysql> select s.sname,x.xuefen
    -> from xuanke x
    -> right join stu s
    -> on x.sid=s.sid;

结果如上(用的是右连接的方式)
给连接查询附加条件：
　　1、写在WHERE子句中
　　2、使用AND和连接条件写在一起
但是：
　　对于内连接，两种写法结果相同；
　　对于外连接，两种写法结果不同。

mysql> select s.sname,x.xuefen
    -> from stu s
    -> left join xuanke x
    -> on x.sid=s.sid
    -> 　　where sname='张三';
+--------+--------+
| sname  | xuefen |
+--------+--------+
| 张三    |      2 |
| 张三    |      4 |
| 张三    |      6 |
+--------+--------+
3 rows in set (0.01 sec)

mysql> select s.sname,x.xuefen                                                                 
    -> from (select * from stu where sname='张三') s
    -> left join xuanke x
    -> on x.sid=s.sid;
+--------+--------+
| sname  | xuefen |
+--------+--------+
| 张三    |      2 |
| 张三    |      4 |
| 张三    |      6 |
+--------+--------+
3 rows in set (0.00 sec)

①先连接后过滤
　　select ……from ……
　　left join ……
　　on 连接条件
　　　　where 过滤条件;
②先过滤后连接
　　select ……from (select ……from ……where 过滤条件)
　　left join ……
　　on 连接条件;