【MySQL】表的约束、基本查询、内置函数

1. 表的约束

1.1 空属性

• 两个值：null（默认的）和not null(不为空)
• 建表时，若未指明是否可以为空，则默认可以为空

1.2 默认值

• 默认值的设置

mysql> create table tt10 (
-> name varchar(20) not null,
-> age tinyint unsigned default 0,
-> sex char(2) default '男'
-> );
Query OK, 0 rows affected (0.00 sec)

• 如果设置了default，用户将来插入，有具体的数据，就用用户的，没有就用默认的

• 关于default和not null：

• 设置了not null，有一种情况下，不插入具体数据，也符合语法：那就是设置了default默认值
• 可看出：default 和 not null不冲突，而是互相补充的

• 当用户没有设置default值且没有设置了not null时，MySQL会自动优化，添加 default null

1.3 列描述

列描述只是注释而已。
实例：

mysql> create table tt12 (
-> name varchar(20) not null comment '姓名',
-> age tinyint unsigned default 0 comment '年龄',
-> sex char(2) default '男' comment '性别'
-> );

1.4 zerofill

zerofill不会改变数据大小，只会使数据格式化显示。

• 变量只有添加了zerofill，才能格式化显示（若数据位数不够，在前面补0；位数够了，则不用管）
• int(n)：指若格式化显示，则显示n位数
• int 默认为int(11)
• int unsigned默认为int(10) unsigned

mysql> show create table tt3\G
***************** 1. row *****************
Table: tt3
Create Table: CREATE TABLE `tt3` (
`a` int(11) DEFAULT NULL,
`b` int(10) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

1.5 主键

主键：primary key是用来约束该字段里面的数据，使其不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型

• 添加完primary key后，其字段会自动设置为not null
• 创建表的时候直接在字段上指定主键

mysql> create table tt13 (
-> id int unsigned primary key comment '学号不能为空',
-> name varchar(20) not null);
Query OK, 0 rows affected (0.00 sec)

• 删除主键

alter table 表名 drop primary key;

• 当表创建好以后但是没有主键的时候，可以再次追加主键
  注意：若要添加主键的那一列的数据重复或空，则添加主键会失败；要修改数据后，再添加主键

alter table 表名 add primary key(字段列表); 字段列表:例如id,name

• 只有一个主键，当一个主键想约束多个字段时，则使用复合主键

mysql> create table tt14(
-> id int unsigned,
-> course char(10) comment '课程代码',
-> score tinyint unsigned default 60 comment '成绩',
-> primary key(id, course) -- id和course为复合主键
-> );

实例理解：若插入[ 1223(id)，数学(course) ]，则可以插入[ 1224，数学 ]、[ 1223，语文 ]，但是不可以插入[ 1223，数学 ]，若id, course都对应相同，则不可以插入。

1.6 自增长

auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。(如果没有插入，则第一个插入的主键字段是系统默认的auto_increment值，为1）

• 自增长的特点:
• 任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
• 自增长字段必须是整数
• 一张表最多只能有一个自增长

mysql> create table tt21(
-> id int unsigned primary key auto_increment,
-> name varchar(10) not null default ''
-> );
mysql> insert into tt21(name) values('a');
mysql> insert into tt21(name) values('b');
mysql> select * from tt21;
+----+------+
| id | name |
+----+------+
|  1 |   a  |
|  2 |   b  |
+----+------+

在插入后获取上次插入的 AUTO_INCREMENT 的值：

mysql > select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|        2         |
+------------------+

1.7 唯一键

• 唯一键：数据可以为空，也可多个为空，但是数据不可以重复。
• 唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。
• 唯一键和主键是互相补充的关系。
• unique也可以和not null 结合一起用，相当于主键作用

mysql> create table student (
-> id char(10) unique comment '学号，不能重复，但可以为空',    //unique
-> name varchar(10)
-> );

1.8 外键

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。

语法：

foreign key (字段名) references 主表(列)

案例：

对上面的示意图进行设计：

• 先创建主键表（班级表）

create table myclass (
   id int primary key,
   name varchar(30) not null comment'班级名'
);

• 再创建从表（学生表）

create table stu (
   id int primary key,
   name varchar(30) not null comment '学生名',
   class_id int,
   foreign key (class_id) references myclass(id)
);

2. 基本查询

2.1 表的增删改查

2.1.1 插入数据

单行插入：
insert into students values (101, 10001, '孙悟空', '11111');
多行插入：用逗号
insert into students values (101, 10001, '孙悟空', '11111'), (102, 20001, '曹孟德','22222');

2.1.2 插入否则更新

插入否则更新：插入如果不成功，则更改，如果成功，则只插入
详细是指由于 主键 或者 唯一键 对应的值已经存在而导致插入失败，则将使其失败的那一行进行更改。整改的结果on duplicate key update在后面。

insert into students (sn, name) values (20001, '曹阿瞒') on duplicate key update  sn = 10010, name = '唐大师';

可能会出现的结果：

-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，并且数据已经被更新

2.1.3 替换插入

主键 或者 唯一键 没有冲突，则直接插入；
主键 或者 唯一键 如果冲突，则删除使其插入失败的那一行后再插入。
指由于 主键 或者 唯一键 对应的值已经存在而导致插入失败。

replace into students (sn, name) VALUES (20001, '曹阿瞒');

结果：

-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入

2.2 Retrieve

2.2.1 select ----- 查询

• 通常情况下不建议使用 * 进行全列查询
• 1. 查询的列越多，意味着需要传输的数据量越大；
• 2. 可能会影响到索引的使用。

全列查询:
select * from exam_result;

指定列查询:
select id, name, english from exam_result;

查询字段为表达式:
select id, name, 10 from exam_result;
SELECT id, name, chinese + math + english  from exam_result; 

为查询结果指定别名:
select id 编号, name 名字, chinese + math + english 总分 from exam_result;  //把chinese + math + english取为别名总分，把id取别名为编号，把name取别名为名字

查询结果去重:
select  distinct math from  exam_result;   //把math相同的数据出掉

2.2.2 where ----- 筛选

比较运算符：

逻辑运算符：

实例：

筛选数学成绩为58或59或98或99的学生（这只是其中一种方法）:
select name, math from exam_result where math in (58, 59, 98, 99); 

筛选姓孙的人:
select name from exam_result where name like '孙%';
select name from exam_result where name like '孙_';

筛选不姓孙的人:
select name from exam_result where name not like '孙%';

添加知识点：如果NULL通过>, >=,<,<=和别的数，进行比较时，结果永远是：NULL；只有通过<=>，<>才能得到正确结果，正确为1，错为0。

2.2.3 order by ----- 结果排序

asc 为升序（从小到大）
decs 为降序（从大到小）
默认为 asc

同学及数学成绩，按数学成绩升序显示:
select name, math from exam_result order by math;
select name, math from exam_result order by math decs;  //降序

多字段排序，排序优先级随书写顺序:
select name, math, english, chinese from exam_result order by math decs, english, chinese;   //数学降序，英语升序，语文升序

order by 子句中可以使用列别名:
select name, chinese + english + math 总分 from exam_result order by 总分 decs;

关于别名：
mysql按下面的顺序进行识别：

所以，order by 和 limit 子句中可以使用列别名，而只有where后是不可以使用别名的，因为where识别不了

2.2.4 limit ----- 筛选分页结果

起始下标为 0
 
从 0 开始，筛选 n 条结果:
select ... from table_name [where ...] [order by ...] limit n;
 
从 s 开始，筛选 n 条结果:
select ... from table_name [where ...] [order by ...] limit s, n
select ... from table_name [where ...] [order by ...] limit n offset s;

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死

2.3 Update

实例：

把张三语文改成60分，数学80分:
update exam_result set math = 80 chinese = 60 where name = '张三';  
 
把全表同学语文成绩改为2倍:
update exam_result set chinese = chinese * 2;  

2.4 Delete

delete from table_name [where ...] [order by ...] [limit ...]、

删除张三的成绩:
delete * from exam_result where name = '张三';

删除所有人的成绩:
delete from exam_result;

截断表:
truncate exam_result;

注意：
用delete删除所有人的成绩 和 用truncate截断表 的异同：
相同点：进行操作后整个表一行数据都没有
不同点：truncate是对表进行处理，而delete是对进行删除数据操作
      truncate后会重置 AUTO_INCREMENT 项，而delete不会

2.5 插入查询结果

指：将查询的结果插入表中

insert into table_name [(column [, column ...])] select ...

将table1查询的全部结果都插入table2中:
insert into table2 select distinct* from table1;

知识点：

创建一个和table结构一样的表table2（只是结构一样，数据不同）:
creat table table2 like table1;

2.6 聚合函数

实例：

统计班级共有多少同学:
select count(*) from students;
select count(1) from students;

统计本次考试的数学成绩分数个数:
select count(math) from students;
select count(distinct math) from students;   统计的是去重数学成绩数量

统计数学成绩总分，不及格 < 60 的总分，没有结果，返回 NULL:
select sum(math) from exam_result where math < 60;

小知识点：

将在/home/xl/中的scott data.sql文件拷贝到存放mysql文件的目录下-----导入：
mysql > source /home/xl/scott data.sql;

2.7 group by子句的使用

在select中使用group by 子句可以对指定列进行分组查询：

select column1, column2, .. from table group by column;

实例：

显示每个部门的平均工资和最高工资: 
select deptno,avg(sal),max(sal) from EMP group by deptno;

显示每个部门的每种岗位的平均工资和最低工资:
select avg(sal),min(sal),job, deptno from EMP group by deptno, job;

显示平均工资低于2000的部门和它的平均工资:
select avg(sal) 平均 from EMP group by deptno having myavg<2000;
having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where。

where 和 having 的区别：条件筛选的阶段是不同的。
where：对具体的任意列进行条件筛选
having：对分组聚合之后的结果进行条件筛选

显示平均工资低于2000的部门和它的平均工资（SMITH员工不参与统计）:
select avg(sal) 平均 from EMP where ename != 'SMITH' group by deptno having myavg<2000;

3. 内置函数

3.1 日期函数

3.2 字符串函数

3.3 数学函数