一、约束
为表创建约束是为了保证进入数据库的数据都是有效的、可靠的。
在数据库中有五种约束来保证数据的有效性和可行型:
- 主键约束(PK);
- 唯一约束(UQ);
- 默认约束(PQ);
- 外键约束(FK);
- 检查约束(CK);
1、主键约束(PK)
- 表中常有一列或多列的组合,其值能唯一的标识表中的每一行,
- 这样的一列或多列称为主键,也称为主键约束;
- 主键约束最显著性的特征是主键列中的值是不能重复的,通过主键约束可以强制表的实体唯一性;
语法结构:
ALTER TABLE tab_name ADD CONSTRAINT pk_name PRIMARY KEY (col_name);
注意
- 主键约束的列不允许有重复的值;
- 主键约束的列不允许有NULL值;
实例之为dept表和emp表增加主键
ALTER TABLE dept ADD CONSTRAINT pk_dept_deptno PRIMARY KEY (deptno);
ALTER TABLE emp ADD CONSTRAINT pk_emp_empno PRIMARY KEY (emp);
2、自增长列
- 并不是所有的表在设计完成后都能找到适合作为主键的列,为此数据库提供了自增长列;
- 自增长列是int类型的,其值是由数据库自动维护的并且是永远都不会重复的,因此自增长列是最适合作为主键的;
- 在创建表时,通过AUTO_INCREMENT关键字来标识自增长列;
- 在MySQL数据库中自增长列必须是主键。
实例之在创建表时确定主键和自增长列
CREATE TABLE tab_name (
col1 INT AUTO_INCERMENT PRIMARY KEY,
col2 VARCHAR(14) ,
col3 INT
...
);
3、唯一约束(UQ)
- 主键约束实现了主键列中的值具有唯一性,如果对非主键列中的值也要求具有唯一性时,就需要使用唯一约束(UNIQUE)。
语法结构:
ALTER TABLE tab_name ADD CONSTRAINT UNIQUE(col_name);
注意:
- 唯一约束的列不允许有重复的值;
- 唯一约束的值允许有一个NULL值。
创建表时可同时添加主键约束和唯一约束
CREATE TABLE tab_name(
col1 INT AUTO_INCERMENT PRIMARY KEY,
col2 VARCHAR(14) UNIQUE,
col3 VARCHAR(12)
...
);
4、默认约束(DF)
- 默认约束(DEFAULT)是为列中的值设置默认值。当使用INSERT语句添加记录时,如果列没有指定值,那么该列就使用默认值,如果列已经指定了,则默认值就无效了。
语法结构:
ALTER TABLE tab_name CHANGE COLUMN old_column_name new_col_name new_datatype DEFAULT value;
5、外键约束(PK)
在关系型数据库中,表与表之间存在三种关系:
- 一对一(1:1):指一张表中一条记录仅与 另一张表中的一条记录相对应;
- 一对多(1:M):指一张表中的一条记录与另一张表中的多条记录相对应;
- 多对多(N:M):指一张表中的多条记录与另一种张表中的多条记录相对应。
- 如果表中的列的值来自另外一张表的主键或唯一键的列称为外键(FOREIGN KEY 简称FK);
- 将被引用值的表称为主表或父表,将引用值的表成为从表或子表;
语法规则:
ALTER TABLE tab_name ADD CONSTRAINT FOREIGN KEY fk_name (col_name) REFERENCES re_tab_name (re_col_name);
实例之为emp表的deptno列添加外键约束:
ALTER TABLE tab_name ADD CONSTRAINT FOREIGN KEY fk_emp_deptno (deptno) REFERENCES re_dept_deptno (deptno);
注意:
- 添加记录时,先添加主表记录,再添加子表记录;
- 删除记录时,先删除子表记录,再删除主表记录;
- 主表被子表引用的键必须是主键或唯一键;
6、检查约束(CK) 在MySQL中无效
- 检查(CHECK)约束是用于检查列中的值是否符合要求。
语法规则:
ALTER TABLE tab_name ADD CONSTRAINT ck_name CHECK (col_name_condition);
二、高级查询
1、 查询所有列
语法结构:
SELECT
*
FROM
tab_name;
2、查询指定列:
语法结构:
SELECT
col1_name,
col2_name,
col3_name
FROM
tab_name;
3 、去掉重复列
语法结构:
SELECT DISTINCT
col_name
FROM
tab_name;
4、查询语句中使用算数表达式
语法结构:
SELECT
col+num
FROM
tab_name;
5、查询语句中使用列的别名
语法结构:
SELECT
col1 AS new_name1,
col2 AS new_name2,
...
FROM
tab_name;
6、条件查询where子句
语法结构:
where子句中可以使用的比较运算符包括>、 >= 、<= 、<>
SELECT
条件
FROM
tab_name
WHERE
查询条件
7、逻辑运算符查询
逻辑运算符包括and、or、not三种运算
语法结构:
SELECT
条件
FROM
tab_name
WHERE
条件1 AND 条件2;
SELECT
条件
FROM
tab_name
WHERE
条件1 OR 条件2;
SELECT
条件
FROM
tab_name
WHERE
条件1 NOT 条件2;
8、BETWEEN…AND
BETWEEN 60 AND 100 相当于 >=60 AND <= 100
语法结构:
SELECT
条件
FROM
tab_name
WHERE
BETWEEN 条件1 AND 条件2;
9、in查询
in查询没有规律的查询范围。相当于多个or的组合。
实例之: id in(1,4,7)相当于id = 1 OR id = 4 OR id=7;
SELECT
条件
FROM
tab_name
WHERE
col_name in (条件1,条件2,条件3...);
10、模糊查询like
- 模糊查询使用通配符%和_来实现。
- % 表示任意字符,
- _ 表示一个字符;
语法结构:
SELECT
条件
FROM
tab_name
WHERE
col_name LIKE '_虎_';
SELECT
条件
FROM
tab_name
WHERE
col_name LIKE '_虎%';
SELECT
条件
FROM
tab_name
WHERE
col_name LIKE '&虎';
11、NULL值查询
- NULL值在数据库中表示没有数据,应使用is来判断列中是存在NULL值
语法结构:
SELECT
条件
FROM
tab_name
WHERE
col_name IS NULL;
or
SELECT
条件
FROM
tab_name
WHERE
col_name IS NOT NULL;
12、聚合函数查询
- 聚合就是将多个数据聚合成一个数据,聚合是通过聚合函数实现的,聚合函数有MIN()、MIN()、SUM()、AVG()、COUNT()
语法结构
SELECT
MAX(col_name) 最大值,
MIN(col_name) 最小值,
AVG(col_name) 平均值,
SUM(col_name) 总和,
COUNT(*) 总数
FROM
tab_name;
13、分组查询GROUP BY 子句
- 分组是按照指定的列,将列中相同的值分为一组,一组用一条记录来表示。分组后可以每组中的数据进行聚合查询。
实例之
SELECT
deptno 部门编号,
COUNT(*) 人数
FROM
emp
GROUP BY
deptno;
14、分组后筛选having子句
- Having子句是对分组后,每组中的数据进行筛选。相对于where子句是在分组前进行的筛选,having子句是在分组后进行的筛选。
实例之
SELECT
deptno 部门编号,
COUNT(*) 人数
FROM
emp
GROUP BY
deptno
HAVING
人数 > 3;
三、多表查询
- 查询两个或者两个以上的数据表或者视图的查询称之为连接查询,这种连接查询通常建立在存在相互关系的父子表之间。
- 表的连接查询包括等值连接、自链接、内连接和外连接。
1、笛卡尔积现象
- 表查询中的而笛卡尔积现象:多行表在查询时,如果定义了无效连接或者漏写了连接条件,就会产生笛卡尔积现象;
- 笛卡尔积现象就是每个表的每一行都和其他表的每一行组合;假设两张表的总行数分别是X行和Y行,笛卡尔积就会返回X*Y行结果。
2、等值连接查询
- 通常在存在主外键关联关系的表之间进行,并将连接条件设定为有关系的列【主键-外键】;
- 使用等号 “=” 连接相关的表;
- 为避免笛卡尔积现象,n个表进行等值连接查询,最少需要n-1个等值条件来约束;
实例之查询每个部门的所有员工
SELECT
d.dname,
e.ename
FROM
dept d,
emp e
WHERE
d.deptno = e.deptno;
3、自连接查询
- 多表查询不仅仅在多个表之间进行也可以在一个表中进行多表查询,称为自连接查询。
实例之查询当前公司员工和所属上级员工的信息
SELECT
e1.empno 员工编号,
e1.ename 员工姓名,
e2.empno 领导编号,
e2.ename 领导姓名
FROM
emp e1,
emp e2
WHERE
e1.mgr = e2.empno;
4、内连接查询
- 内连接查询时多表连接查询的一种方式,这种方式使用INNER JOIN 关键字来实现,INNER可省略;
注:
- jion子句实现内连接查询;
- on关键字表示连接条件;
- 内连接返回满足on条件的记录。
实例之查询每个部门的所有员工
SELECT
d.dname,
e.ename
FROM
emp e
INNER JOIN
dept d
ON
e.deptno = d.deptno;
相当于
SELECT
d.dname,
e.ename
FROM
dept d,
emp e
WHERE
d.deptno = e.deptno;
5、外连接
- 外连接分为左外连接(left outer join)和右外连接(rigth outer join)其值outer可以省略;
- 在左外连接中join左侧的表称为左表,右侧的表称为右表;
- 左表的每一行数据去匹配右表的每一行数据,如果左表的数据与其右表的数据满足ON条件,则将相关数据返回到查询结果集中;
- 如果左表的数据与右表的数据不满足ON的条件,那么左表的数据也会将返回的查询结果集中,而右表使用NULL值填充结果集;
- 左外连接与右外连接原理相同。
实例之查询每个部门的所有员工
- 向dept表中插入一条新数据
INSERT INTO
dept
VALUES(
40,
'项目部',
'兰州'
);
- 体验右外连接
SELECT
d.dname,
e.ename
FROM
emp e
RIGHT OUTER JOIN
dept d
ON
e.deptno = d.deptno;
- 体验左外连接
SELECT
e.ename,
d.dname
FROM
dept d
LEFT OUTER JOIN emp e
ON
e.deptno = d.deptno;
6、子查询
- 在日常使用的时候,WHERE查询体条件中的限制条件不是一个确定的值,而是来自了另外一个查询结果;
- 在SQL中这种为了给主查询提供数据而首先执行的查询语句称为子查询;
- 根据返回结果的不同,可以分为单行子查询、多行子查询等等。
单行子查询
实例之查询软件部门下的所有员工
SELECT
*
FROM
emp e
WHERE
e.deptno = (
SELECT
d.deptno
FROM
dept d
WHERE
d.dname = '软件部'
);
多行子查询
-
如果子查询返回了多行记录,则称这样的嵌套查询为多行子查询,多行子查询就需要用到多行记录的操作符如:IN、ANY、ALL等等
-
示例一之查询公司中和员工张青相同薪水和奖金的员工
SELECT
*
FROM
emp e1
WHERE
(e1.sal, e1.comm) = (
SELECT
e2.sal,
e2.comm
FROM
emp e2
WHERE
e2.ename = '张青'
);
- 示例二之统计所有员工都分布在那些部门信息:
SELECT
*
FROM
dept d
WHERE
d.deptno IN (SELECT e.deptno FROM emp e);
- 示例三之查询公司中比任意一个员工的工资高的所有员工:
SELECT
*
FROM
emp e1
WHERE
e1.sal > ANY (SELECT e2.sal FROM emp e2);
- 示例四之查询公司中比所有的助理的工资高i但不是助理的员工:
SELECT
*
FROM
emp e1
WHERE
e1.sal > ALL (
SELECT
e2.sal
FROM
emp e2
WHERE
e2.job LIKE '%助理'
);
多列子查询
- 实例之查询公司中和员工张青相同奖金和工资的员工
SELECT
*
FROM
emp e1
WHERE
(e1.sal, e1.comm) = (
SELECT
e2.sal,
e2.comm
FROM
emp e2
WHERE
e2.ename = '张青'
);
7、分页查询limit子句
- 当表中的数据很多时,可以使用分页查询,以降低对数据库服务器的压力。使用limit子句可以以实现分页查询。
实例之查询员工信息
- 语法结构:
SELECT
*
FROM
emp
LIMIT x,y;
代码解析
- 第一个参数x表示从第几条开始查询(排序从0开始);
- 第二个参数y表示输出几条数据。
8、综合查询示例
需求:统计2000年以后入职,部门人数超过2人的部门,按照部门人数从多到少排序输出,分页显示,每页5条
SELECT
d.deptno AS '部门编号',
d.dname AS '部门名称',
COUNT(*) AS '人数'
FROM
emp AS e
INNER JOIN dept AS d ON e.deptno = d.deptno
WHERE
e.hirdate >= '2000-01-01 00:00:00'
GROUP BY
e.deptno
HAVING
人数 >= 2
ORDER BY
人数 DESC
LIMIT 0,
5;
代码解析:
当SQL语句中有多个子句时,子句的书写顺序和执行顺序如下:
子句 | select | from | where | group | having | order | limit |
---|---|---|---|---|---|---|---|
书写顺序 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
执行顺序 | 5 | 1 | 2 | 3 | 4 | 6 | 7 |
执行顺序解释:
- from:获取所有记录;
- where:筛选掉不满足条件的记录,保留满足条件的记录;
- group:对满足条件的记录进行筛选;
- select:选择指定的列;
- order by:对选出的列进行脾排序;
- limit:取出指定行的记录。
四、单行函数
SQL函数即数据库的内置函数,可以应用在SQL语句中实现特定功能。
1、定义
- 函数用来执行数据处理和进行一些复杂运算;
- 函数的输入值叫做参数,在函数的内部对输的参数进行一些运算,最终结算得到一个结果返回给用户;
- MySQL的内置函数用于实现特定的功能,单行函数可以应用于SQL语句中。
2、字符串函数
- 实例之大小写转换函数:UPPER、LOWER
SELECT UPPER('abcde');
SELECT LOWER('ABCDE');
代码解析:
- UPPER(str):此函数将字符串转换成大写;
- LOWER(str):此函数将字符串转换成小写。
- 字符串连接函数:CONCAT
实例之连接字符串‘My’、‘SQL’
SELECT CONCAT('My','SQL');
- 获取子字符串的函数SUBSTR
实例之获取子字符串函数
SELECT SUBSTR('SQL IS GOOD' FROM 2);
OR
SELECT SUBSTR('SQL IS GOOD' , 2);
代码解析:
- 第一个参数是要被街区的字符串;
- 第二个参数是从第几位开始截取(从1开始);
其他字符串函数见下表:
函数 | 示例 | 结果 | 作用 |
---|---|---|---|
LENGTH | SELECT LENGTH('java web sniper') | 15 | 截取字符串长度 |
INSTR | SELECT INSTR('java web sniper','web') | 6 | 截取字符串在父字符串中的索引 |
LTRIM | SELECT LTRIM(' java web sniper') | java web sniper | 去掉左侧空格 |
RTRIM | SELECT RTRIM('java web sniper ') | java web sniper | 去掉右侧的空格 |
REPLACE | SELECT REPLACE('hello web sniper' , 'web sniper', 'MySQL') | hello MySQL | 替换文本 |
3、数学函数
函数 | 示例 | 结果 | 作用 |
---|---|---|---|
ROUND(x, [y]) | SELECT ROUND(5.63); / SELECT ROUND(4.36,1); | 6/4.4 | 对指定的值x进行四舍五入操作,可以保留指定的位数y |
TRUNCATE(x,y) | SELECT TRUNCATE(5.6,0); | 5 | 对指定的值x进行截取的操作,可以指定保留的数值位数y |
CEIL(x) | SELECT CEIL(4.56); | 5 | 返回不小于指定的值x的最小整数 |
FLOOR(x) | SELECT FLOOR(8.5); | 8 | 返回不大于指定的值x的最大整数 |
ABS(x) | SELECT ABS(-12); | 12 | 取绝对值函数 |
MOD(x) | SELECT MOD(5,3); | 3 | 取x除以y的余数函数 |
SIGN(x)) | SELECT SIGN(-128); | -1 | 取x的符号函数(负数、0、正数) |
POWER(x,y) | SELECT POWER(2,4); | 8 | 取x的y次幂函数 |
SQRT(x) | SELECT SQRT(16); | 12 | 取x的二次方根函数 |
4、日期函数
4.1、当前日期函数
函数 | 示例 | 结果 | 作用 |
---|---|---|---|
CURRENT_TIMESTAMP() | SELECT CURRENT_TIMESTAMP(); | 2020-11-23 13:06:39 | 获取当前时间戳函数 |
CURRENT_DATE() | SELECT CURRENT_DATE(); | 2020-11-23 | 获取当前日期函数 |
CURRENT_TIME() | SELECT CURRENT_TIME; | 13:09:19 | 获取当前时间函数 |
NOW() | SELECT NOW(); | 2020-11-23 13:10:25 | 获取当前日期+时间 |
4.2、日期截取函数
函数 | 示例 | 结果 | 作用 |
---|---|---|---|
DAYOFWEEK(date) | SELECT DAYOFWEEK('2020-11-23'); | 2 | 返回日期date是星期几 |
CURRENT_DATE() | SELECT DAYOFMONTH('2020-11-23'); | 23 | 返回date是一月中的第几日 |
DAYOFYEAR(date) | SELECT DAYOFYEAR('2020-11-23') | 328 | 返回date是一年中的第几日 |
YEAR(date) | SELECT YEAR('2020-11-23'); | 2020 | 返回date的年份 |
MONTH(date) | SELECT MONTH('2020-11-23'); | 11 | 返回date中的月份数值 |
DAY(date) | SELECT DAY('2020-11-23'); | 23 | 返回date中的日期数值 |
WEEK(date) | SELECT WEEK('2020-11-23'); | 47 | 返回date是一年的第几周 |
HOUR(time) | SELECT HOUR('10:05:03'); | 10 | 返回time的小时数 |
MINUTE(time) | SELECT MINUTE('10:05:03'); | 5 | 返回time的分钟数 |
SECOND(time) | SELECT SECOND('10:05:03'); | 3 | 返回time的秒数 |
4.3、 日期增加函数 DATE_ADD(date,INTERVAL expr type)
- date是一个DATETIME或DATE值
- INTERVAL表示时间间隔,是固定写法
- expr对date进行加减法的一个表达式
- type是预期的格式,预期格式见下表。
type值 | 预期的expr格式 |
---|---|
MICROSECOND | MICROSECONDS |
SECOND | SECONDS |
MINUTE | MINUTES |
HOUR | HOURS |
DAY | DAYS |
WEEK | WEEKS |
MONTH | MONTHS |
QUARTER | QUARTERS |
YEAR | YEARS |
SECOND_MICROSECOND | ‘SECONDS.MICROSECONDS’ |
MINUTE_MICROSECOND | ‘MINUTES.MICROSECONDS’ |
MINUTE_SECOND | ‘MINUTES:SECONDS’ |
HOUR_MICROSECOND | ‘HOURS.MICROSECONDS’ |
HOUR_SECOND | ‘HOURS:MINUTES:SECONDS’ |
HOUR_MINUTE | ‘HOURS:MINUTES’ |
DAY_MICROSECOND | ‘DAYS.MICROSECONDS’ |
DAY_SECOND | ‘DAYS HOURS:MINUTES:SECONDS’ |
DAY_MINUTE | ‘DAYS HOURS:MINUTES’ |
DAY_HOUR | ‘DAYS HOURS’ |
YEAR_MONTH | ‘YEARS-MONTHS’ |