数据库查询语句与主键表的设置

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.主键表多对多第三方表实现

-- 建立多对多关系的时候 实际上就是建立外键
-- 1.明确:在那张表建立外键(中间表)
-- 2.哪个字段与哪个字段建立联系,
-- 中间表的tid与老师表的tid 建立外键关系
-- 中间表的tid与学生表的sid 建立外键关系

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