目录
- 1. 表的约束
- 1.1 空属性
- 1.2 默认值
- 1.3 列描述
- 1.4 zerofill
- 1.5 主键
- 1.6 自增长
- 1.7 唯一键
- 1.8 外键
- 2. 基本查询
- 2.1 表的增删改查
- 2.1.1 插入数据
- 2.1.2 插入否则更新
- 2.1.3 替换插入
- 2.2 Retrieve
- 2.2.1 select ----- 查询
- 2.2.2 where ----- 筛选
- 2.2.3 order by ----- 结果排序
- 2.2.4 limit ----- 筛选分页结果
- 2.3 Update
- 2.4 Delete
- 2.5 插入查询结果
- 2.6 聚合函数
- 2.7 group by子句的使用
- 3. 内置函数
- 3.1 日期函数
- 3.2 字符串函数
- 3.3 数学函数
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 ----- 查询
- 通常情况下不建议使用 * 进行全列查询
-
- 查询的列越多，意味着需要传输的数据量越大；
-
- 可能会影响到索引的使用。
全列查询:
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 数学函数
