数据库增删改查II
目录
1.约束
1.1null约束
1.2唯一约束
1.3默认值约束
1.4主键约束
1.5外键约束
2.新增
3.查询
3.1聚合查询
3.1.1.聚合函数
3.1.2group by子句
3.1.3 having
3.2联合查询
3.2.1内连接
3.2.2外连接
3.2.3 自连接
3.2.4子查询
3.2.5合并查询
1.约束
1.1null约束
语法
create table 表名( 列名 类型 not null);
指定某列不能存储null值—必填项
create table student(id int not null,sn int,name varchar(20));
" class="reference-link">
1.2唯一约束
语法
create table 表名( 列名 类型 unique);
先查询,看数据是否存在,如果存在,则插入/删除失败,如果不存在,则能插入/删除
create table student(id int not null,sn int unique,name varchar(20));
sn用unique约束,sn的值则唯一,已经插入sn=1时,再次插入sn=1会插入失败
mysql> insert into student values(1,1,'wang');Query OK, 1 row affected (0.00 sec)mysql> insert into student values(2,1,'wan');ERROR 1062 (23000): Duplicate entry '1' for key 'sn'
1.3默认值约束
语法
create table 表名( 列名 类型 default ‘默认值’)
insert指定列插入,其他未被指定到的列就按默认值填充
create table student(id int not null,sn int unique,name varchar(20) default'unkrown');
name用默认值约束,当name列为空时,用默认值unkrown填充
insert into student(id,sn) values(2,2);
+——+———+————-+
| id | sn | name |
+——+———+————-+
| 1 | 1 | wang |
| 2 | 2 | unkrown |
+——+———+————-+
1.4主键约束
语法
create table 表名( 列名 类型 primary key);
特点:
1.唯一,不为空, 一个表一个主键
2.可以是一个列,可以是多个列
3.主键是not null和unique的结合,可以不用not null
create table student(id int not null primary key,sn int unique,name varchar(20));
+———-+——————-+———+——-+————-+———-+
| Field | Type | Null | Key | Default | Extra |
+———-+——————-+———+——-+————-+———-+
| id | int(11) | NO | PRI | NULL | |
| sn | int(11) | YES | UNI | NULL | |
| name | varchar(20) | YES | | NULL | |
+———-+——————-+———+——-+————-+———-+
自增主键:整数类型的主键,可以搭配auto_increment使用.当插入的数据对应字段没有值时,为最大值+1
列名 类型 primary key auto_increment;mysql> insert into student values(1,1,'wang');mysql> insert into student(sn,name) values(2,'li');
+——+———+———+
| id | sn | name |
+——+———+———+
| 1 | 1 | wang |
| 2 | 2 | li |
+——+———+———+
1.5外键约束
外键用于关联其他表的主键或者唯一键
语法
foreign key(字段名) references 主表(列);
创建班级表
CREATE TABLE classes (id INT PRIMARY KEY auto_increment,name VARCHAR(20),`desc` VARCHAR(100));
学生表,学生班级是一对多的关系,id为主键,classes_id为外键,关联班级表id
create table student(id int primary key auto_increment,sn int unique,name varchar(20) default'unkrown',classes_id int,foreign key(classes_id) references classes(id));
student表中每一个记录的classid在classes表的id都存在
student收到classes的约束,把classes叫做student的父表,student是class的子表
2.新增
插入查询结果:查询结果,得到的列数,类型需要和插入表的列数,类型相同.(列名可以不同)
语法
insert into 表名A(...) select...from 表名B;
把表名b中的信息复制到表名a中
3.查询
3.1聚合查询
3.1.1.聚合函数
1.count:计数
字段名中为null的数据不会计入结果
select count(字段名) from 表名;
2.sum:求和
只对数字列有效,自动跳过结果为null的行
select sum(字段名) from 表名 (where ...);
3.avg:平均值
select avg(字段名+..) (as) 别名 from 表名;
4.max:最大值
select max(字段名) from 表名;
5.min:最小值
select min(字段名) from 表名;
3.1.2group by子句
对指定列先分组在查询
注:指定字段必须是分组依据字段,如果要有其他字段,必须要有聚合函数
select 字段名1, 聚合函数(字段名2),... from 表名 group by 字段名1,...;
3.1.3 having
group by 分组后,不能用where ,要用having代替where
select 字段名1, 聚合函数(字段名2),... from 表名 group by 字段名1,...having ...;
3.2联合查询
多表查询的步骤
1.分析清除需求中,涉及的信息在哪些表中
2.针对多个表进行笛卡尔积
3.筛选出其中有效数据(此处用学生id做关联条件)
4.结合需求中的条件,进行加强条件
5.针对列进行
3.2.1内连接
语法
1.join对两张表
select 字段 from 表1 (别名1) join 表2 (别名2) on 连接条件1;
select * from student join score on stsudent.id=score.student_id ;
2.join对多张表
select 字段 from 表1 (别名1) join 表2 (别名2) on 连接条件1 join 表3 (别名3)
on 连接条件2)…;
select * from student join score on stsudent.id=score.student_id join course on course.id=score.course_id;
3.2.2外连接
外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。
语法
1.join对两张表
左外连接:select 字段 from 表1 (别名1) left join 表2 (别名2) on 连接条件1;
右外连接:select 字段 from 表1 (别名1) right join 表2 (别名2) on 连接条件1;
左连接:select * from student left join score on stsudent.id=score.student_id ;右连接:select * from student right join score on stsudent.id=score.student_id ;
2.join对多张表
左外连接:select 字段 from 表1 (别名1) left join 表2 (别名2) on 连接条件1 join 表3 (别名3) on 连接条件2)…;
右外连接:select 字段 from 表1 (别名1) right join 表2 (别名2) on 连接条件1 join 表3 (别名3) on 连接条件2)…;
左外连接:select * from student left join score on stsudent.id=score.student_id left join course on course.id=score.course_id;右外连接:select * from student right join score on stsudent.id=score.student_id right join course on course.id=score.course_id;
注:内外连接的区别
1.内连接—>两个表中都体现的数据
select name,score from student join score on student.id=score.student_id;+------+-------+| name | score |+------+-------+| 张三 | 90 || 李四 | 80 |+------+-------+
2.左外连接,以左侧表为准,左侧表中的所有数据都能体现出来
select name,score from student left join score on student.id=score.student_id;+------+-------+| name | score |+------+-------+| 张三 | 90 || 李四 | 80 || 王五 | NULL |+------+-------+
3.右外连接,以右侧表为准,右侧表中的所有数据都能体现出来
select name,score from student right join score on student.id=score.student_id;+------+-------+| name | score |+------+-------+| 张三 | 90 || 李四 | 80 || NULL | 70 |+------+-------+
3.2.3 自连接
自连接是指在同一张表连接自身进行查询。(本质把行转换成列)—>实现行中的比较
自连接要指定表的别名
select * from 表名 as 别名1,表名 as 别名2(where…);
3.2.4子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
- 单行子查询：返回一行记录的子查询
select 字段名1 from 表名1 where 字段名2 =(select 字段名3 from 表名2 where… )
字段名2和字段名3对应
字段名1,字段名2属于表名1
字段名3属于表名2
1.(not)in关键字
select 字段名1 from 表名1 where 字段名2 (not)in(select 字段名3 from 表名2 where… )
字段名2和字段名3对应
字段名1,字段名2属于表名1
字段名3属于表名2
2.(not) exists关键字
select 字段名1 from 表名1 where (not) exists(select 字段名3 from 表名2 where… )
字段名2和字段名3对应
字段名1属于表名1
字段名3属于表名2
- 在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。
3.2.5合并查询
为了合并多个select的执行结果，可以使用集合操作符 union，union all.
使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。
1.union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
select * from 表名1 where… union select * from 表名2 where…;
可以用or实现
select * from 表名1 where… or select * from 表名2 where…;
2.union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
select * from 表名1 where… union all select * from 表名2 where…;
小咪咪
太过爱你忘了你带给我的痛
还没有评论，来说两句吧...