Mysql

Mysql复习二

1.1. WHERE

语法

SELECT 列名 FROM 表名 [WHERE 条件语句]

WHERE子句中的运算符

比较运算符	>, <, >=, <=, =, <>	注意不等于和Java中不同, 是<>
	BETWEEN ... AND ...	某一区间内的值, 从 ... 到 ...
	IN(列表)	在列表之中, 例: in(1,2,3) 代表1或2或3
	LIKE(表达式)	模糊查询, %代表多个字符, _代表单个字符
	IS NULL	判断是否为NULL
逻辑运算符	AND &&	与, 两边都为TRUE结果为TRUE
	OR ||	或, 一边为TRUE结果就为TRUE
	NOT !	非, 将表达式结果取反

1.2. ORDER BY

语法

SELECT 列名 FROM 表名 ORDER BY 列名 ASC|DESC;

注意事项

ORDER BY 指定排序的列名可以是表中的列名, 也可以是SELECT语句后面起的别名

ASC为升序, DESC为降序

ORDER BY应在查询语句的结尾

1.3. COUNT函数

语法

SELECT COUNT(*)|COUNT(列名) from 表名 [WHERE 条件语句]

注意事项

COUNT(列名)的方式是统计指定列中有多少条记录, 不包括值为NULL的

COUNT(*)则是统计表中有多少条数据

COUNT(DISTINCT 列名) 统计不重复的记录数

如果加上WHERE子句, 则是统计满足条件的记录

1.4. SUM函数

语法

SELECT SUM(列名) FROM 表名 [WHERE 条件语句];

注意事项

计算指定列中所有记录的和, 如果有WHERE子句则计算满足条件的记录

1.5. AVG函数

语法

SELECT AVG(列名) FROM 表名 [WHERE 条件语句];

注意事项

计算指定列的平均值, 如果有WHERE子句, 则计算满足条件的记录

AVG()统计平均数不包含NULL值

1.6. MAX / MIN函数

语法

SELECT MAX(列名) FROM 表名 [WHERE 条件语句];

SELECT MIN(列名) FROM 表名 [WHERE 条件语句];

注意事项

获取指定列最高/最低值, NULL不参与统计

1.7. GROUP BY(重点)

语法

SELECT 列名 FROM 表名 GROUP BY 列名 [HAVING 条件语句]

注意事项

按照某列归类

HAVING和WHERE类似, 但HAVING是作用于组, 其中可以使用组函数

SELECT列表中未包含在组函数中的列名, 只能是GROUP BY中的列名

HAVING中可以使用组函数, WHERE不能.

2. 函数

2.1. 时间函数

注意date, datetime, timestamp之间的区别

ADDTIME(原时间, 增加值) 在某个时间上增加一段时间

select addtime('18:23:01', '01:01:01');

select addtime(now(),'3:0:0');

CURRENT_DATE() 当前日期

select current_date();

CURRENT_TIME() 当前时间

select current_time();

CURRENT_TIMESTAMP() 当前时间戳

select current_timestamp();

DATE(时间) 返回制定时间的日期部分

select date('2011-02-14 18:00:00');

DATE_ADD(日期,INTERVAL 增加值 类型) 在指定日期上对某个字段增加

select date_add('2011-02-14 23:00:00', interval 10 month);

DATE_SUB(日期,INTERVAL 减少值 类型) 在指定日期上对某个字段减少

select date_sub('2011-02-14 23:00:00', interval 1 year);

DATEDIFF(日期1, 日期2) 计算两个日期之间的差值

select datediff('2000-02-14', '2001-02-14');

NOW() 当前时间

select now();

YEAR|MONTH|DATE|HOUR|MINUTE|SECOND(时间) 获取指定时间的某个字段

select year('2011-02-14 23:00:00');

select hour('2011-02-14 23:00:00');

2.2. 字符串函数

CHARSET(字符串) 返回字符串字符集

select charset(name) from student;

CONCAT(字符串1[, 字符串2]... ) 连接字符串

select concat('aaa', 'bbb', 'ccc');

INSTR(字符串, 子字符串) 查找子字符串出现位置, 注意序号从1开始

select instr('abc', 'a');

UCASE(字符串) 将字符串转为大写

select ucase('aBc');

LCASE(字符串) 将字符串转为小写

select lcase('aBc');

LEFT(字符串, 长度) 从字符串左边取指定长度个字符

select left('aBc',2);

LENGTH(字符串) 计算字符串长度

select length('aBc');

REPLACE(字符串, 搜索字符串, 替换字符串) 将字符串中指定字符串替换为其他字符串

select replace('abbcbbd', 'bb', 'ee');

STRCMP(字符串1, 字符串2) 逐个字符比较两个字符串, 如果是包含关系, 则返回长度差值

select strcmp('abcc', 'abde');

select strcmp('abc', 'ab');

SUBSTRING(字符串, 开始坐标[, 个数]) 从字符串中截取

select substring('abcdef', 3);

select substring('abcdef', 3, 2);

LTRIM(字符串) 去掉左边空白

select ltrim(' abc ');

select concat('--', ltrim(' abc '), '--');

RTRIM(字符串) 去掉右边空白

select concat('--', rtrim(' abc '), '--');

TRIM(字符串) 去掉左右两边空白

select concat('--', trim(' abc '), '--');

3. 表的约束

约束的作用

限定某一列上的数据, 阻止非法数据录入, 提高程序健壮性.

3.1. 唯一约束 unique

unique约束的字段在整张表中唯一, 不可重复, 不包括多个NULL

创建表时设置唯一

create table test (

id int,

name varchar(20) unique

);

删除唯一约束

show create table test; 发现唯一索引名叫name

alter table test drop index name;

添加唯一约束

alter table test change name name varchar(20) unique;

3.2. 非空约束 not null

not null约束的字段不能为空

创建表时设置非空

create table test1 (

id int,

name varchar(20) not null

);

删除非空约束

alter table test1 change name name varchar(20);

添加非空约束

alter table test1 change name name varchar(20) not null;

3.3. 主键约束 primary key

通常我们在设计表的时候需要给每一条记录一个独有的标识, 我们就用主键来约束这个标识.

primary key用来标识一个字段, 这个字段是非空且唯一的.

创建表时设置主键

create table test2(

id int primary key,

name varchar(20)

);

删除主键

alter table test2 drop primary key;

在制定列上添加主键

alter table test2 change id id int primary key;

alter table test2 add primary key(id);

设置主键自动增长

create table test3(

id int primary key auto_increment,

name varchar(20)

);

删除自增长

alter table test3 change id id int;

设置自增长

alter table test3 change id id int auto_increment;

UUID主键

128位的2进制, 32位16进制加上4个-

java.util.UUID.randomUUID().toString()

3c2372a4-da2a-4470-b17a-f2e50ac79636

3.4. 外键约束 foreign key

foreign key约束某一列的值是参照另外一列

创建表时添加外键约束

create table husband(

id int primary key,

name varchar(20) not null

);

create table wife(

id int primary key,

name varchar(20) not null,

husband_id int,

constraint husband_id_fk foreign key(husband_id) references husband(id)

);

wife表的husband_id的值必须是husband表中的id

3.5. 单独的实体

3.6. 一对多、多对一

多的一方设置外键

create table department(

id int primary key auto_increment,

name varchar(20)

);

create table employee(

id int primary key auto_increment,

name varchar(20),

department_id int,

constraint department_id_fk foreign key(department_id) references department(id)

);

insert into department(name) values('开发部');

insert into department(name) values('市场部');

insert into employee(name, department_id) values('张三',1);

insert into employee(name, department_id) values('李四',1);

insert into employee(name, department_id) values('王五',2);

3.7. 一对一

独立外键, 没有依赖关系, 两个表的对象都可以独立存在.

主键即外键, 分为主表和从表, 从表依赖于主表, 从表中的对象不能单独存在.

注意从表的主键, 不能自动增长.

3.8. 多对多

用一张关系表保存多对多的关系, 有两列分别引用两张表的主键, 并且这两列组合起来成为联合主键

4. 多表查询

4.1. 连接查询

当我们要插叙的数据不只是在一张表中, 我们就需要使用多表连接查询.

例如: 查询员工所在的部门名称, 查询部门中员工名称, 都需要查询两张表.

注意:

在多表连接查询的时候, 如果没有有效的连接条件, 所有表中的行会互相连接, 形成笛卡尔集.

为了避免笛卡尔集, 可以再where后加入有效的连接条件

4.2. 多表连接

多张表连接查询, 一张表外键引用另外一张表, 另外一张表再引用其他表.

4.3. 自连接

自己和自己连接, 当前表的外键引用自己的主键.

4.4. 内连接、左外连接、右外连接

1. 内连接

之前我们使用逗号和where子句进行的连接就是内连接. 标准语法应使用 inner join 和 on,

2. 左外连接

使用 left outer join 和 on 关键字进行连接查询, 这时左表中不满足条件的记录也会被查询出来

3右外连接

使用 right outer join 和 on 关键字进行连接查询, 这时右表中不满足条件的记录也会被查询出来

5. 子查询

一条查询语句需要使用另一条查询语句的结果.

多行子查询

ANY 集合中只要有一个元素满足条件, 结果就成立.

ALL 集合中所有元素都满足条件, 结果才成立

IN 相当于 =ANY