SQL

作者: 青城墨阕 | 来源:发表于2021-07-16 20:09 被阅读0次

SQL语言：通常被称为「结构化查询语言」。
SQL语言特点：
2.1 综合一体化；
2.3 高度非过程化；
2.3 面向集合的操作方式；
2.4 多种使用方法；
2.5. 语言简洁。
SQL语言共分为四大类：
3.1 数据查询语言DQL
3.2 数据操纵语言DML
3.3 数据定义语言DDL
3.4 数据控制语言DCL。

1. 基本命令

// 1. 创建
// 1.1 创建数据库
          create database test;
          show database; // 查询数据库服务器中的数据库
          use test; // 选中某个数据库
// 1.2 创建数据库表
          /* create table 表名(
          * *        列1 key          类型
          * *        列2 key          类型
          * *        ...
          * *        列n key          类型)
          */
          create table pet (
                    name varchat(20),
                    owner varchat(20),
                    age int,
                    sex char(1),
                    birth date);

// 2. 增
          /* insert into 表名 values (
          * *        列1 value
          * *        列2 value
          * *        ...
          * *        列n value)
          */
          insert into pet values ('旺财',  '周星驰', 3, '公', '2019-09-20');

// 3. 删
          // 3.1 删除表中某行信息
          // delete from 表名 where 列n key=列n value; 
          delete from pet where name='旺财';
          // 3.2 删除整张表
          // drop table 表名;
          drop table pet;

// 4. 改
          /*update 表名 set 列m key=列m value 
          * *        where 列n key=列n value; 
          */
          update pet set name='旺旺财' where owner='周星驰';

// 5. 查
          // select *  from 表名; 
          select * from pet;

2. 建表约束

// 1. 主键约束(primary key)
// 1.1 primary key：不重复 & 不能为空
          create table user (
                    id int primary key,
                    name varchat(20));
          insert into user values(1, '张三'); // OK 插入成功
          insert into user values(1, '张三'); // fail 插入失败，主键重复
          insert into user values(NULL, '张三'); // fail 插入失败，主键为空
// 1.2 主键约束的增、删、改？
          create table user1 (
                    id int,
                    name varchat(20));
          // 添加主键
          alter table user1 add primary key(id);
          // 删除主键
          alter table user1 drop primary key;
          // 修改主键
          alter table user1 modify id int primary key;
// 1.3 联合主键
// 只要联合主键值加起来不重复就可以
          create table user2 (
                    id int,
                    name varchat(20),
                    password varchat(20),
                    primary key (id, name));
          insert into user2 values(1, '张三', '123'); // OK 插入成功
          insert into user2 values(1, '张三'); // fail 插入失败，主键重复
          insert into user values(2, '张三'); // OK 插入成功

// 2. 自增约束(auto_increment)
// auto_increment 与主键配合使用，可管控主键，让其自动增长
          create table user3 (
                    id int primary key auto_increment,
                    name varchat(20));
          insert into user3(name) values('张三'); // OK 插入成功
          insert into user3(name) values('张三'); // OK 插入成功
          select * from user3;
          /* 
          **  id          name
          **  1          张三
          **  2          张三
          */ 

// 3. 唯一约束(unique)
// 4. 非空约束(not null)
// 5. 默认约束(default)
// 6. 外键约束(foreign key)

3. 查询练习

建表&插入数据

// 1. 学生表student(学号、姓名、性别、出生年月、班级)
create table student(
          sno varchat(20) primary key,
          sname varchat(20) not null,
          ssex varchat(10) not null,
          sbirthday datetime,
          class varchat920)
);
// 2. 教师表teacher(教师编号、教师姓名、出生年月、职称、所在部门)
create table teacher(
          tno varchat(20) primary key,
          tname varchat(20) not null,
          tsex varchat(10) not null,
          birthday date time,
          prof varchat(20) not null,
          depart varchat(20) not null
);
// 3. 课程表course(课程号、课程名称、教师编号)
create table course(
          cno varchat(20) primary key,
          cname varchat(20) not null,
          tno varchat(20) not null,
          foreign key(tno) references teacher(tno)
);
// 4. 成绩表score(学号、课程号、成绩)
create table score(
          sno varchat(20) not null,
          cno varchat(20) not null,
          dgree decimal,
          foreign key(sno) references student(sno),
          foreign key(cno) references course(cno),
          primary key(sno, cno)
);

student-table.png

teacher-table.png

course-table.png

score-table.png

3.1 十类查询方式

1、查询student表中所有记录：

select * from student；

查询结果（1）

2、查询student表中所有记录的sname、ssex和class列：

select sname,  ssex,  class from student；

查询结果（2）

3、查询教师所有的单位即不重复的depart列：
$\color{#999}{去重关键字:} \color{#aa1111}{distinct}$

select distinct depart from teacher；

查询结果（3）

4、查询score表中成绩在60-80之间的所有记录：
4.1 $\color{#999}{查询区间关键词:} \color{#aa1111}{between...and...}$
4.2 $\color{#999}{查询区间:} \color{#aa1111}{运算符比较}$

select * from score where degree between 60 and 80；
select * from score where degree > 60 and degree  < 80；

查询结果（4）

5、查询score表中成绩为85，86或88的记录：
$\color{#999}{同列表示“或者”关系的查询:} \color{#aa1111}{in}$

select * from score where degree in(85, 86, 88)；

查询结果（5）

6、查询student表中"95031"班或者性别为“女”的同学记录：
$\color{#999}{不同列表示“或者”关系的查询:} \color{#aa1111}{or}$

select * from student where class='95031' or ssex='女'；

查询结果（6）

7、以class降序查询student表中的所有记录：
$\color{#999}{升序:} \color{#aa1111}{asc}$ (默认是升序)
$\color{#999}{降序:} \color{#aa1111}{desc}$

select * from student order by class desc；

查询结果（7）

8、以cno升序、 degree将序查询score表中的所有记录：
$\color{#999}{多列的排序:}$ order by 列名1 asc, 列名2 desc

select * from score order by cno asc,  degree desc；

查询结果（8）

9、查询“95031”班的学生人数：
$\color{#999}{统计:} \color{#aa1111}{count}$

select count(*) from student where class='95031'；

查询结果（9）

10、查询score表中的最高分的学生学号、课程号和成绩（子查询或排序）：
$\color{#999}{最大:} \color{#aa1111}{max}$
$\color{#999}{limit:} \color{#aa1111}{第一个数字表示「开始的下标」，第二个数字表示「要筛选出的条数」}$

// 子查询
select sno, cno, degree from score where degree=(select max(degree) from score);

// 排序(一般不推荐使用排序，因为如果有多个最高分同学，则存在结果不准确的情况)
select sno, cno, degree from score order by degree desc limit 0,1;

查询结果（10）

3.2 分组计算

1、分组查询：--查询每门课的平均成绩：
$\color{#999}{平均:} \color{#aa1111}{avg}$
$\color{#999}{分组:}$ group by

select cno, avg(degree) from score group by cno；

分组（group by）

2、分组条件与模糊查询：--查询score表中至少有2名学生选修的并以3开头的课程的平均分数
$\color{#999}{累计:} \color{#aa1111}{count}$
$\color{#999}{模糊:} \color{#aa1111}{like}$
having： $\color{#aa0000}{过滤声明，即在分组之后过滤数据。having字句中可以包含聚组函数}$

select cno, avg(degree), count(*) from score group by cno
having count(cno)>=2 and cno like '3%'；

查询结果

3.3 多表查询

1、两表关联：--查询所有学生的 sname、cno和degree列：

select sname,cno,degree from student, score where student.sno=score.sno;

查询结果1

2、三表关联：--查询所有学生的 sname、cname和degree列：

// sname -> student
// name -> course
// degree -> score

select sname,cname,degree from student, course,score
where student.sno=score.sno and course.cno=score.cno;

查询结果2

3.4 子查询

1、查询“95031”班学生每门课的平均分

select cno,avg(degree) from score
where sno in (select sno from student where class='95031')
group by cno;

查询结果1

2、查询选修“3-105”课程的成绩高于“109”号同学“3-105”成绩的所有同学的记录

// 先找出“109”号同学“3-105”的成绩
select degree from score where sno='109' and cno='3-105';

// 再去查结果
select * from score
where degree>(select degree from score where sno='109' and cno='3-105');

查询结果2

4. 实战练习

1. 在SQL语句中，如果使用联合检索的话，很有可能会出现ambiguous column name的错误。
  原因：原来在两张表里都有的字段名称，在选择语句中要标明是哪张表里的字段。

// 错误写法：
select emp_no,salary,from_date,to_date,dept_no
from salaries,dept_manager
where salaries.emp_no=dept_manager.emp_no;

// 正确写法：
//相同的字段必须标注是来自哪张表
select salaries.emp_no,salary,from_date,salaries.to_date,dept_no
from salaries,dept_manager
where salaries.emp_no=dept_manager.emp_no;

1. 查找薪水记录超过15次的员工号emp_no以及其对应的次数t
  笔记：having之前一定要用group by 来标注统计维度

select emp_no,count(salary) from salaries
group by emp_no
having count(salary)>15;

3.不存在：not in
获取所有非manager的员工emp_no

select emp_no from employees
where employees.emp_no not in(select emp_no from dept_manager);

1. 统计出当前各个title类型对应的员工当前薪水对应的平均工资
  INNER JOIN:在表中存在至少一个匹配时，INNER JOIN 关键字返回行

SELECT t.title, AVG(salary)
FROM titles as t
INNER JOIN salaries as s
ON(WHERE) t.emp_no=s.emp_no
GROUP BY t.title
ORDER BY AVG(salary) ASC;

1. 获取当前薪水第二多的员工的emp_no以及其对应的薪水

select s.emp_no, s.salary, e.last_name, e.first_name
from salaries s join employees e
on s.emp_no = e.emp_no
where s.salary =             //- 第三步: 将第二高工资作为查询条件
    (
    select max(salary)        // 第二步: 查出除了原表最高工资以外的最高工资(第二高工资)
    from salaries
    where salary <   
        (
        select max(salary)    // 第一步: 查出原表最高工资
        from salaries
        where to_date = '9999-01-01'  
        )
    and to_date = '9999-01-01'
    )
and s.to_date = '9999-01-01';

SQL

1. 基本命令

2. 建表约束

3. 查询练习

建表&插入数据

3.1 十类查询方式

3.2 分组计算

3.3 多表查询

3.4 子查询

4. 实战练习

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读