SQL

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

最后编辑于：2021.08.20 11:01:52

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 194,670评论 5赞 460
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 81,928评论 2赞 371
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 141,926评论 0赞 320
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 52,238评论 1赞 263
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 61,112评论 4赞 356
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 46,138评论 1赞 272
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 36,545评论 3赞 381
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 35,232评论 0赞 253
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 39,496评论 1赞 290
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 34,596评论 2赞 310
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 36,369评论 1赞 326
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 32,226评论 3赞 313
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 37,600评论 3赞 299
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 28,906评论 0赞 17
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,185评论 1赞 250
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 41,516评论 2赞 341
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 40,721评论 2赞 335

SQL

1. 基本命令

2. 建表约束

3. 查询练习

建表&插入数据

3.1 十类查询方式

3.2 分组计算

3.3 多表查询

3.4 子查询

4. 实战练习

推荐阅读更多精彩内容