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)
);