1.特殊查询
1.字段和排空值查询
关键字:ifnull()
语法:SELECT 字段1+IFNULL(字段2,0) FROM 表;
解释:如果字段2为空当做值0来处理
拓展:任何值的字段和字段为空的字段相加结果都为0
如:查询薪金与佣金之和
SELECT sal,comm,sal+IFNULL(comm,0) as total FROM emp;
2.查询升序,降序排列
关键字:order by asc/desc 升序/降序
语法:select * from 表名 order by asc/desc
如:1.按薪金降序排列
SELECT * FROM emp ORDER BY sal DESC;
2.查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序
SELECT * FROM emp ORDER BY sal DESC,empno DESC;
3.查询不为空的总记录条数
关键字count();
语法:select count(字段名) from 表名;
如:
1.查询表的记录数
SELECT COUNT(*) FROM emp;
2.查询佣金的记录数
SELECT COUNT(comm),COUNT(sal) FROM emp;
3.查询emp表中月薪大于2500的人数:
SELECT COUNT(sal) FROM emp WHERE sal >=2500;
4.统计月薪与佣金之和大于2500元的人数:
SELECT COUNT(*) FROM emp WHERE sal+IFNULL(comm,0) > 2500;
5.查询有佣金的人数,有领导的人数
SELECT COUNT(comm),COUNT(mgr) FROM emp;
3.查询一条字段的总和
关键字:sum();
语法:select sum(字段名) from 表名;
如:
1.查询薪金总和
SELECT SUM(sal) FROM emp;
2.查询所有雇员月薪和,以及所有雇员佣金和:
SELECT SUM(sal),SUM(comm) FROM emp;
3.查询所有雇员月薪+佣金和:
SELECT SUM(sal)+SUM(comm) FROM emp;
4.查询一条字段的平均数
关键字avg();
语法:select avg(字段名) from 表名;
如:
统计所有员工平均工资:
SELECT AVG(sal) FROM emp;
5.查询一条字段的最大值和最小值
关键字:max()/min() 最大值/最小值;
语法:select max(字段名)/min(字段名) from 表名
如:
SELECT MAX(sal) FROM emp;
SELECT MIN(sal) FROM emp;
6.分组查询
关键字:group by
语法:select 字段名1 from 表名 group by 字段名1;
注意:分组查询时查询的字段必须是分组的字段(记录数要对应);
如:
1.查询每个部门的编号和每个部门的工资和
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;
2.查询每个部门的编号以及每个部门的人数
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;
3.查询每个部门的部门编号以及每个部门工资大于1500的人数:
SELECT deptno,COUNT(*) FROM emp WHERE sal >1500 GROUP BY deptno;
7.分组后筛选条件的查询
关键字:having
特点:可以使用函数
having关键字是分组之后使用的的
语法:select 字段1 from 表名 group by 字段1 having 筛选条件
如:
查询工资总和大于9000的部门编号以及工资和:
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;
8.分页查询
关键字:limit 起始页 数据条数;
语法:select * from 表名 limit 起始页 数据条数;
1.第一页查询3条数据
SELECT * FROM emp LIMIT 0,3;
2.第二条查询3条数据
SELECT * FROM emp LIMIT 3,3;
3.第x条查询y条数据
SELECT * FROM emp LIMIT (x-1)*y,y;
9.模糊查询
关键字:like %/_;
%代表0~n个字符
_代表一个字符
1.查询名字以w开头的数据
SELECT * FROM emp WHERE ename LIKE 'w%';
2.查询名字以w开头并且全名4个字符的数据
SELECT * FROM emp WHERE ename LIKE 'w___';
2.表约束
1.主键约束:在创建表的时候保持数据的完整性
特点:1.唯一性 2.不能为空
写法1:放在字段后面
如:
创建Student表以id为主键约束
CREATE TABLE student(
id int PRIMARY KEY,
name VARCHAR(50)
);
写法2:另起一行
特点:可以添加联合主键
如:
CREATE TABLE student1(
id int,
name VARCHAR(50),
PRIMARY KEY(id)
);
2.联合主键:主键内容不能完全相同,不完全一样的都可以添加插入到数据库
如:
CREATE TABLE student2(
id int,
stuid int,
name VARCHAR(50),
PRIMARY KEY(id,stuid)
);
insert into student2 values(1,2,'qq'),(1,1,'qq'),(2,1,'qq');
3.主键约束可以通过修改表结构进行添加主键约束
语法:alter TABLE 表名 ADD CONSTRAINT PRIMARY KEY(字段名);
如:
alter TABLE student3 ADD CONSTRAINT PRIMARY KEY(id):
4.唯一约束
特点:不能重复 可以有空值
关键字:unique
写法:放在字段后面
如:
CREATE TABLE student4(
id int PRIMARY KEY,
name VARCHAR(50) UNIQUE
);
5.自增长列(auto_increment)
语法:放在字段\主键后面
如:CREATE TABLE student5(
id int PRIMARY KEY auto_increment,
name VARCHAR(50)
);
6.不为空与默认值设置
关键字:default
如:
CREATE TABLE student6(
id int PRIMARY KEY,
name VARCHAR(50) not null,
sex varchar(20) DEFAULT '男'
);
如果sex不插值则默认是男
7.域
功能:限制单元格数据内容
关系:一对多
语法:constraint 域名 foreign key(外键字段) references 主键表(主键表字段)
如:创建Student主键表和score外键表
1.主键表
create table Student7(
sid int PRIMARY KEY,
name VARCHAR(20)
);
2.外键表
create table score(
sid int,
score int,
<!--键表链接-->
CONSTRAINT fk_score_sid FOREIGN KEY(sid) REFERENCES Student7(sid)
);
1.主键表多对多第三方表实现
create table teacher (
tid int primary key,
name varchar(20)
);
create table Student(
sid int primary key,
sname varchar(10)
);
create table ts(
tid int,
sid int,
think varchar(10),
CONSTRAINT ts_t FOREIGN KEY(sid) REFERENCES Student(sid),
CONSTRAINT ts_s FOREIGN KEY(tid) REFERENCES teacher(tid)
);