数据库的基础方法

首先建好3个表 , 分别为stu , emp , dept , 方便使用 , 效果直观.

stu内容

emp内容

dept内容

下面是数据库的一些基本使用

• 去除重复数据 , 查询emp表中所有薪金(注意 : 查询的所有数据完全相同才能去除重复的)

select distinct sal from emp;

• 查看雇员的月薪与佣金之和

select ename,sal+comm from emp;

注意 : sal+comm 两列值相加并且组成一个新的字段值 , 和null相加只能返回null

• 如果comm是null , 就用参数2来代替

select ename,sal+ifnull(comm,0) from emp;

• 给查询的字段起别名 , 关键词 AS (可以省略)

select ename,sal+ifnull(comm,0) as total from emp;

• 排序 , 关键词 order by , 默认是升序asc(可以省略) , 降序desc

查询所有学生记录，按年龄升序排序

select * from stu order by age;

• 查询所有学生记录，按年龄降序排序

select * from stu order by age desc;

• 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

select * from emp order by sal desc,empno asc;

• 聚合函数 : sum(总和) , avg(平均数) , max(最大值) , min(最小值) , count(总记录数)
• 查询emp表中记录数

select count(*) from emp;

• 查询emp表中月薪大于2500的人数

select count(*) from emp where sal>2500;

• 统计月薪与佣金之和大于2500元的人数

select count(*) from emp where sal+ifnull(comm,0)>2500;

• 查询有佣金的人数，有领导的人数(注意:查询时要保证查询的字段记录数对等)

select count(comm),count(mgr) from emp ;

• 查询所有雇员月薪和

select sum(sal) from emp;

• 查询所有雇员月薪和，以及所有雇员佣金和

select sum(sal),sum(comm) from emp;

• 查询所有雇员月薪+佣金和 , 改名total

select sum(sal+ifnull(comm,0)) total from emp;

• 统计所有员工平均工资

select avg(sal) from emp;

• 查询最高工资和最低工资：

select max(sal),min(sal) from emp;

• 分组查询 , 关键词 group by
• 查询每个部门的部门编号和每个部门的工资和 , (注意 : 按什么分组 , 前面查询的字段就是什么 , 保证查询结果数量对等)

select deptno,sum(sal) from emp group by deptno;

• 查询每个部门的部门编号以及每个部门的人数：

select deptno,count(*) from emp group by deptno;

• 查询每个部门的部门编号以及每个部门工资大于1500的人数(注意 : where条件要加在Group By的前面)

select deptno,count(*) from emp where sal>1500 group by deptno;

• 查询工资总和大于9000的部门编号以及工资和
  注意:
  1.where 后面不能使用聚合函数
  2.having 后面可以使用聚合函数
  3.where 条件是在分组之前进行筛选
  4.having 条件是在分组之后进行筛选

select deptno,sum(sal) from emp group by deptno having sum(sal)>9000;

• 分页查询 , 关键词 limit (mysql的方言 , limit关键词要在所有的条件之后写)
• 从第3条开始显示数据,一共显示3条数据

select * from emp limit 2,3;

• 模糊查询 , 关键词 like , 使用占位符 , “%” 百分号表示多个字符 , “_” 一个下划线表示一个字符

数据库外键

1.数据完整性(保证用户插入的数据有效的)
2.引用完整性(多张表依赖的关系)
3.域完整性(一个单元格的完整性)
4.实体完整性(一条数据的完整性)
5.主键(唯一且不能为空) 精准删除
6.唯一键 (唯一的 , 但是可以是空)
7.主键自增

• 设置主键关键词 primary key

设置主键方式1

create table stu1(
   sid int primary key,
   sname varchar(50)	
);

设置主键方式2

create table stu2(
   sid int,
   sname varchar(50),
   primary key (sid)	
);

创建联合主键(用两个字段同时当主键)

create table stu3(
   classesid int,
   sid int,
   sname varchar(50),
   primary key (classesid,sid)
);

注意:联合主键都相同才算重复

• 先创建出来表, 再添加主键约束

create table stu4(
   sid int,
   sname varchar(50)
);

添加主键约束

alter table stu4 add constraint primary key(sid);

• 删除主键约束(注意:删除主键,不用constraint)

alter table stu4 drop primary key;

• 唯一键约束

create table stu5(
   sid int primary key,
   sname varchar(50) unique
);

• 插入两条记录(可以插空,不可以重复)

insert into stu5 (sid,sname) values (1,'星');
insert into stu5 (sid,sname) values (2,null);
insert into stu5 (sid,sname) values (3,'星');

• 主键自增

create table stu6(
   sid int primary key auto_increment,
   sname varchar(50) unique
);
insert into stu6 (sname) values ('星1');
insert into stu6 (sid,sname) values (null,'星2');

注意:主键只能自增 , 删除了也不会倒退 , 插入时可以给null , 让系统自增

• 域完整性:

1. not null(非空)
2. default(默认值)

• 字段类型

create table stu7(
   sid int primary key auto_increment,
   sname varchar(50) not null,
   sgender varchar(50) default '男'
);
insert into stu7 (sname,sgender) values ('星3',null);

注意:有默认值约束时 , 可以直接插入null , 要使用默认值 , 可以不插入该字段(跟主键不同)

• 引用完整性(参照物约束)
  表和表之间的关系 : 一对一(罕见) , 一对多 , 多对多(常用)
• 学生表(主) 和 分数表(从) (可以通过外键来建立两张表之间的关系)
  建立外键的方式1(明确外键是在从表中)

create table student(
	sid int primary key,
	sname varchar(50)
);

注意 : 外键别名规范 fk_主表名_从表名_字段名
写法 : constraint 外键别名 foreign key (从表中的字段) reference 主表名 (主表中关联的字段)

create table score(
	sid int,
	score int,
	constraint fk_student_score_sid
	foreign key (sid) references student (sid)
);

• 建立外键方式2

create table student1(
	sid int primary key,
	sname varchar(50)
);

create table score1(
	sid int,
	score int
);

添加外键约束

alter table score1 add constraint fk_student1_score1_sid foreign key (sid) references student1 (sid);

• 删除外键(一个表中可能有多个外键)

alter table score1 drop foreign key fk_student1_score1_sid;

• 多对多关系 , 老师表和学生表

create table teacher(
	tid int primary key,
	tname varchar(50)
);
create table studentx(
	sid int primary key,
	sname varchar(50)
);

中间表

create table teacher_studentx(
	tid int,
	sid int
);

添加外键(多对多关系 , 外键在中间表中)

alter table teacher_studentx 
add constraint fk_teacher_studentx_tid
foreign key (tid) references teacher (tid);

alter table teacher_studentx 
add constraint fk_teacher_studentx_sid
foreign key (sid) references studentx (sid);