基础知识

1、SQL、DBMS、DB的联系和区别？

DBMS-(执行)->SQL-(操作)->DB

2、关于表

基本组成单元；
行和列，行：数据/记录(data). 列：字段column
每一个字段应该包括哪些属性？字段名、数据类型、相关的约束

3、SQL语句怎么分类？

DQL：数据查询语言—查询语句，select相关
DML：数据操作语言—insert delete update对表中数据进行增删改
DDL：数据定义语言—create drop alter，对表结构进行增删改
TCL：事务控制语言—commit提交事务、rollback回滚事务
DCL：数据控制语言—grant授权、revoke撤销权限等

对于DCL；超级用户可给普通用户授权或撤销授权

4、导入数据（常用SQL命令）

  • 登录MySQL的DBMS，DOS命令窗口：

mysql -uroot -p333；

  • 查看有哪些数据库：

show databases；

  • 创建数据库(删除请替换为关键字drop)

create database xxx;

  • 使用某个数据库

use database xxx；

  • 使用某个数据库下的所有表

show tables;

  • 初始化上文表的数据

source xxxsql脚本；

下面介绍所要用到的几张结构表

结构表

show tables；
image.png
>desc dept;
image.png
>desc emp;
image.png
>desc salgrade;
image.png

数据表

image.png

image.png image.png
注意：
首先，对数据库的增删改查命令和对某个数据库下的表的增删改命令是不同的；其次，初始化数据库表的语言如下：source xxxx.sql，这个文件以sql结尾，这样的文件被称为“sql脚本”。
所以，sql脚本的定义：当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，我们就称之为sql脚本。当sql脚本中的数据量太大的时候，无法打开，请使用source命令完成初始化。

常用命令plus

  • 查看当前使用的是哪个数据库：

select database();

  • 查看其他库中的表：

show tables from xxx数据库名；

  • 查看某张表的建表语句：

show create table xxx表名；

  • 查看mysql的版本号：

select version();

  • 结束一条语句：

\c

  • 退出MySQL：

exit

数据查询语句DQL

1、简单的查询语句DQL

语法格式：
select 字段名1，字段名2…. from 表名;
几点需要注意：①as起别名，可省略 ②一定要出现中文用字符，单引号

2、条件查询

select 字段1，字段2 from 表名 where 条件 order by xxx；
image.png
查询条件：
①>,<,=,!=(也可<>),is null（数字0和空是2个概念）, or , not ,in
②and(优先级高于or，若要or先执行则加小括号)
③between and(闭区间，左小右大，写反查空；字符的话是左闭右开)
④like(%,) %代表任意多个，表示任意一个
执行顺序：form，where，select，order by

3、排序(升降序)

默认升序
asc升序，desc降序

4、分组函数

count，sum，avg，max，min，注意对象是分组，这几个函数在使用时已经考虑了null，无需再添加
分组函数一个有5个，分组函数还有另一个名字：多行处理函数
多行处理函数的特点：输入多行，最终输出的结果是1行
单行处理函数
ifnull：select ename,ifnull(comm,0) as comm from emp;

5、count(*)和count(具体字段)的区别

count(*)查询某个字段的总条数；count(具体字段)查询某个字段不为空的条数

6、group by 和having

group by：按照某个字段或者某些字段进行分组；
having：对分组之后的数据进行再次过滤

注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因，并且任何一个分组函数(count max min sum avg)都是在group by语句执行结束之后才会执行。若一条sql语句没有group by的话，整张表的数据会自成一组。

select ename,sal from emp where sal > avg(sal);//无效地使用了分组函数
原因：SQL语句当中有一个语法规则，分组函数不可直接使用在where子句中，因为group by是在where执行之后才会执行的。

当一条语句中有group by的话，select后面只能跟分组函数与参与分组的字段。having是group by的搭档，是对分组的条件再筛选

7、Sql语句执行顺序★★

select 5
….
from 1
…
where 2 —-第一层过滤
…
group by 3 —-先分组，再有分组函数，分组函数自动忽略null，不必手动去除
…
having 4 —-分组后的第二层过滤，为group by服务出现
…
order by 6
…
根据这个执行顺序，想要获得正确结果，会涉及到子查询、嵌套查询的知识点(带小括号)

8、多字段联合分组

案例：找出每个部分不同工作岗位的最高薪资
select
deptno,job,max(sal)
from
emp
group by
deptno,job;

9、查询结果去重

select distinct job from emp；//distinct关键字用在所有字段之前，去重数据

10、连接查询 (非常重要)★★

定义：多张表联合查询取出最终结果。
分类1—年代：SQL92、SQL99
分类2—方式：
内连接：等值连接、非等值连接、自连接
外连接：左外连接、右外连接
全连接：不怎么用
现象：笛卡尔（乘积）现象。
给表起别名有什么好处？ ①执行效率高 ②可读性好
如何避免笛卡尔现象？ 加条件过滤。并且加了过滤条件之后，匹配次数并没发生改变，只是显示的有效记录变了，因此没有提高程序的执行效率。下面举个例子：
select
e.ename,d.dname
from
emp e, dept d
where
e.deptno = d.dept.no; (SQL92语法，现在不怎么用了)

内连接之等值连接

条件是等量关系。
语法格式：
…
A
join
B
on
连接条件
where
…
案例：查询每个员工的部门名称，要求显示员工名和部门名。
SQL92语法（太老，不用了）
select
e.ename,d.dname
form
emp e, dept d
where
e.deptno = d.deptno;

SQL99语法（常用,join on 语法）
select
e.ename,d.dname
form
emp e
（inner）join
dept d
on
e.deptno = d.deptno;
SQL99语法结构更清晰：因为表的连接条件和后来的where条件分离了

内连接之非等值连接

连接条件的关系是非等量关系。
案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。
select
e.ename,e.sal,e.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;

内连接之自连接

一张表看作两张表，自己连接自己。
案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。
select
a.ename as ‘员工名’ ,b.ename as ‘领导名’
from
emp a
inner join
emp b
on
a.mgr = b.empno;

外连接

内连接：假设A、B两张表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接，AB两张表没有主副之分，两张表是平等的。
外连接：假设A、B表进行连接，使用外连接的话，AB两张表种有一张是主表，另一张是附表。主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出null与之匹配。

外连接之左外连接

左外连接，也称左连接。表示左边的那张表是主表。
外连接最重要的特点是：主表的数据无条件全部查出

外连接之右外连接

右外连接，也称右连接。表示右边的那张表是主表。
案例1：找出每个员工的上级领导
select
a.ename ‘员工’ ,b.ename ‘领导’
from
emp a
left join
emp b
on
a.mgr = b.empno;

案例2 ：找出哪个部门没有员工
select
e.,d.
from
emp e
right join
dept d
on e.deptno=d.deptno;

三张以上的表的连接查询

语法格式：
…
A
join
B
join
C
on
…
表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接。

11、子查询

定义：select语句嵌套select语句，被嵌套的select语句就是子查询。
出现位置：select ..(select语句). .from…(select语句)..where….(select语句)..

12、Union（存疑）

可将查询结果相加。或是拼接2张不相干的表。
案例：找出工作岗位是SALESMAN和MANAGE的员工。
select ename from union select dname from dept；

13、limit的使用（★★★★★）

①limit是MySQL特有的，其他数据库没有，不通用(Oracle有一个相同的机制，叫做rownum)
②limit取结果集中的部分数据，这是它的作用
③语法机制： limit startIndex , length；
startIndex表示起始位置；length表示取几个
案例：取出工资前5名的员工
select ename,sal from emp order by sal desc limit 0,5
④limit是SQL语句最后执行的一个环节：
select 5
……..
from 1
……..
where 2
………
group by 3
……..
having 4
……..
order by 6
……..
limit 7
……..

通用的标准分页：
每页显示pageSize条记录，那么第pageNo页为：（pageNo-1）*pageSize

数据操作语言DML

1、创建表

语法格式

Mysql字段的数据类型

int,
bigint,长整型，对应java中的long
float,
double,
char,定长字符串，对应java中的String
varchar,可变长字符串，对应java中的StringBuffer和StringBuilder
date,
BLOB, 二进制大对象(存储图片、视频等流媒体信息)，对应java中的Object
CLOB 字符大对象(存储大文本，比如可以存储4G的字符串)，对应java中的Object
使用举例：
电影表：tmovie（注意：表名在DB中一般建议以t或tbl_开头）
id—->int
name—->varchar
playtime—->date
post(海报)——>BLOB
description（简介）—->CLOB

2、insert语句插入数据

语法格式：
insert into 表名(字段名1，字段名2，字段名3，….) values(值1，值2，值3);
要求：字段的数量和值的数量相同，并且数据类型要对应相同。
举例：

  1. drop table if exists t_student;
  2. create table t_student(
  3.     no bigint,
  4.   name varchar(255),
  5.   sex char(1) default 1,
  6.   classno varchar(255),
  7.   birth char(10)
  8. );
  1. 需要注意的是：当一条insert语句执行成功之后，表格当中必然会多一行记录，即使多的这一行记录中某些字段是null，后期也没有办法在执行insert语句插入数据了，只能用update更新。 <br />一次插入多行：
  1. insert into t_student(no,name,sex,class,birth) values (),(),()

3、表的复制

语法格式：
语法：
create table 表名 as select语句；
将查询结果当作表创建出来

4、批量插入

语法格式：
语法：
insert into 表名 as select语句；
将查询结果当作表数据插入进去

5、修改表数据

语法格式：
update 表名 set 字段名1 = 值1，字段名2 = 值2…where 条件；
注意：没有条件整张表数据全部更新!(无法回滚，MySql默认已经提交事务了)

  1. update dept1 set loc = 'SHANGHAI',dname = 'RENSHIBU' where deptno = 10;

6、删除表数据

语法格式：
delete from 表名 where 条件；
注意：没有条件就表示全部删除
案例：删除10部门数据

  1. delete from dept1 where deptno = 10;
  1. 怎么删除数据量特别大的表？（这是表被截断，彻底删除，无法恢复）
  1. truncate table emp1；

数据定义语言DDL

对于表结构的修改，由于在实际开发中表一旦设计好之后就不会再轻易做修改，所以此处不做阐述。即使需要修改表的结构，也可以直接使用工具操作。因此，对表数据的操作才会出现在Java代码中，通常是增删改查，这样的操作也被称为CRUD。

约束

1、概述

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束有：
非空约束：not null：约束字段不为空
唯一约束：unique：约束字段不能重复（列级、表级）
主键约束：primary key：约束字段既不为空也不重复（列级、表级）
外键约束：foreign key
检查约束：check。注意：Oracle有但MySQL没有

2、非空约束

3、唯一约束

可为null但不可重复。

  1. //代码块1
  2. drop table if exist t_user;
  3. create table t_user(
  4.     id int,
  5.   usercode varchar(255) unique,    //列级约束
  6.   username varchar(255) unique
  7. );
  1. //代码块2
  2. //多个字段联合添加约束
  3. drop table if exist t_user;
  4. create table t_user(
  5.     id int,
  6.   usercode varchar(255),
  7.   username varchar(255),
  8.   unique(usercode,username)    //表级约束
  9. );

代码块1和代码块2的区别：代码块1是两个加约束的主体，这种约束属于列级约束；而代码块2是两个字段作为一个整体加约束，即多个字段联合添加约束，这种约束属于表级约束。注意：not null只有列级约束，没有表级约束。

4、主键约束

分类：
按主键字段数量划分：单一主键、复合主键(不建议)
性质：自然主键、业务主键(主键值与系统的业务划分，比如：银行卡号作为主键，不推荐)

5、外界约束

外键可以为null，在引用别的表的字段必须要有唯一性unique但不一定是主键。
防止大量字段冗余，因此一般用于表与表之间的关系。
image.pngimage.png
将以上的建表语句写出来：
t_student 中的classno字段引用t_class表中的字段，此时t_student表叫做子表，t_class表叫做父表。
父子表操作顺序：
删除数据的时候，先子后父；
添加数据的时候，先父后子；
删除表的时候，先子后父；
创建表的时候，先父后子。

  1. create table t_class(
  2.     cno int,
  3.   cname varchar(255),
  4.   primary key(cno)
  5. );
  7. create table t_student(
  8.     sno int,
  9.   sname varchar(255),
  10.   classno int,
  11.   primary key(sno),
  12.   foreign key(classno) references t_class(cno)
  13. );

存储引擎

  1.  MySql支持很多种存储引擎，存储引擎是MySQL专用的，Oracle称之为存储方式。本质上，每一种存储引擎就对应一种存储方式。在建数据库表的时候可以指定存储引擎，也可以指定字符集。MySQL默认使用的存储引擎是InnoDB，默认字符集是UFT-8.<br />查看当前MySQL支持的存储引擎：show engines \G。MySQL5.5.36支持的存储引擎有9个，其中包含InnoDB。<br />常见的存储引擎：<br />**MYISAM**<br />MyISAM存储引擎是MySQL最常用的引擎，它管理的表具有以下特征：
  3.      - 使用三个文件表示每个表：
  4.         - 格式文件 - 存储表结构的定义（mytable.frm）
  5.         - 数据文件 - 存储表行的内容（mytable.MYD）
  6.         - 索引文件 - 存储表上的索引（mytable.MYI）
  7.      - 灵活的AUT0_INCREMENT字段处理
  8.      - 可被转换为压缩、只读表来节省空间

InnoDB

  1.   - InnoDB存储引擎是MySQL的缺省引擎。
  2.   - 它管理的表具有以下主要特征：
  3.      - 每个InnoDB表在数据库目录中以.frm格式文件表示
  4.      - InnoDB表空间tablespace被用于存储表的内容
  5.      - 提供一组用来记录事务性活动的日志文件
  6.      - 用COMMIT,SAVEPOINT,ROLLBACK支持事务处理
  7.      - 提供ACID兼容
  8.      - 在MySQL服务器崩溃后提供自动恢复
  9.      - 多版本（MVCC）和行级锁定
  10.      - 支持外键及引用的完整性，包括级联删除和更新

MEMORY
不支持事务，数据易丢失。

  1.   - 使用了MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快
  2.   - MEMORY存储引擎管理的表具有以下特征：
  3.      - 在数据库目录内，每个表均以.frm格式的文件表示
  4.      - 表数据及索引被存储在内存中
  5.      - 表级锁机制
  6.      - 不能包含TEXT或BLOB字段
  7.   - MEMORY存储引擎以前被称为HEAP引擎

事务（Transaction）—TCL

简述

一个事务是一个完整的业务逻辑单元，不可再分。
比如银行账户转账，从A账户向B账户转账1000元，需要执行2条update语句：
update t_act set balance = balance - 1000 where actno = ‘act-001’;
update t_act set balance = balance + 1000 where actno = ‘act-002’;
以上2条DML语句必须同时成功，或者同时失败，不允许出现一条成功、一条失败。要保证以上2条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

原理

假设一件事，需要先执行一条insert，再执行一条update，最后执行一条delete，这是才算完成。

  1. 开启事务机制(开始)
  2. 执行insert语句-->insert...（这个执行成功以后，把这个执行记录到数据库的操作历史当中，
  3.         并不会向文件中保存一条数据，不会真正修改硬盘上的数据）
  4. 执行update语句-->update...（这个执行也是记录一下历史操作，不会真正地修改硬盘上的数据）
  5. 执行delete语句-->delete...（这个执行也是记录一下历史操作【记录到缓存】，不会真正地修
  6.         改硬盘上的数据）
  7. 提交事务或者回滚事务(结束)
  8. 注：提交或回滚都会清空历史记录

杜聚宾老师的理解：事务其实就是将一大段相关的SQL语句捆绑起来，一次性执行。

特性

ACID。
原子性：最小工作单位，不可再分；
一致性：多条DML语句同时成功或失败；
隔离性：事务A、B之间相互隔离；
持久性：数据最终要被保存到硬盘文件中。

隔离性

事务隔离性存在隔离级别有4个：

  • 第一级别—读未提交（read uncommitted）：

大白话：可以读到未提交的事务
我们当前事务可以读到对方未提交的数据
缺点：读未提交存在脏读现象，即读到了脏的数据

  • 第二级别—读已提交（read committed）：

大白话：可以读到已提交的事务，但是读前读后的数据是不一致的
对方事务提交之后我方可以读取到。
优点：解决脏读现象
缺点：不可重复读

  • 第三级别—可重复读（repeatable read）

大白话：可以重复读取事务，但是事务数据可能已经发生修改了
优点：解决不可重复读
缺点：读取到的问题是幻象

  • 第四级别—串行化读/序列化读

大白话：你操作完了我才能操作，所以不会出现问题
优点：解决了所有问题
缺点：效率低，需要事务排队
Oracle数据库默认的隔离级别是：读已提交
MySQL的默认隔离级别是：可重复读

演示事务

MySQL事务默认情况下是自动提交的，即执行任意一条DML语句只提交一次。
关闭自动提交的方式是start transaction；
设置全局事务隔离级别（为读未提交）：set global transaction isolation level (read uncommitted);
查看事务的全局隔离级别：select @@global.tx_isolation;

索引

1、简述

索引就相当于一本书的目录，通过目录可以快速找到相应的资源，在数据库方面，查询一张表的时候有2种检索方式：
第一种：全表扫描；
第二种：根据索引
索引可以提交查询效率的根本原因是：缩小了扫描范围。但索引不能随意添加，频繁修改的数据就不适合加索引。

2、操作索引对象

创建：create index 索引名称 on 表名(字段名)；
删除：drop index 索引名称；

3、索引满足的条件

①数据量庞大
②该字段很少DML操作
③该字段经常出现在where子句中
④主键和带有unique约束的字段是自动添加索引的(尽量用主键查询)

4、查看、添加索引

查看

  1. explain select ename,sal from emp where sal = 5000;

image.png

添加

  1. create index emp_sal_index on emp(sal);

image.png

5、索引底层原理

通过B-Tree缩小扫描范围，底层索引进行了排序分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
select ename from emp where ename = ‘SMITH’;
通过索引转换为：
select ename from emp where 物理地址 = 0x3；image.png

6、分类

单一索引：给单个字段添加索引；
复合索引：给多个字段联合起来添加一个索引；
主键索引：主键上会自动添加索引；
唯一索引：有unique约束的字段上会自动添加索引

7、失效

①模糊查询通配符首位是%：select ename from emp where ename like ‘%A%’;

视图（view）

1、简述

站在不同的角度看数据，即：同一张表的数据，通过不同的角度去看待。

2、创建和删除

创建：create view myview as select empno,ename from emp;
删除：drop view myview;
注：只有DQL语句才能以视图对象的方式创建出来

3、CRUD操作

对视图进行增删改查会影响到原表数据，即：通过视图影响原表数据，不是直接操作原表。所以，可以对视图进行CRUD操作。
增：create view emp_bak as select from emp;
删：delete from myview1 where empno = 7369;
改：update myview1 set ename = ‘hehe’,sal = 1 where empno = 7369;
查：select from myview;

4、意义

可以隐藏表的实现细节。保密级别比较高的系统，数据库只对外提供相关的视图，Java程序员只对视图对象进行CRUD。视图并不能提高查询效率。

DBA命令

1、数据库导出

mysqldump 数据库名>文件位置 用户名 密码
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p333

2、数据库导入

create database bjpowernode;
use bjpowernode;
source 数据库名
source D:\bjpowernode.sql;

数据库设计三范式(面试常问)

1、概述

设计表的数据，按照这个范式设计的表不会出现数据冗余。

2、内容

第一范式

任何一张表都应该有主键，并且每个字段原子性不可再分。

第二范式

建立在第一范式基础上，所有非主键字段完全依赖主键，不能产生部分依赖。
多对多：三张表，关系表两个外键。
t_atudent学生表
sno(pk) sname
————————————-
1 张三
2 李四
3 王五

t_teacher讲师表
tno(pk) tname
—————————————
1 王老师
2 张老师
3 李老师

t_student_teacher_relation学生讲师关联表
id(pk) sno(fk) tno(fk)
———————————————————————
1 1 3
2 1 1
3 2 2
4 2 3
5 3 1
6 3 3

第三范式

建立在第二范式基础上，所有非主键字段直接依赖主键，不能产生传递依赖。
一对多：两张表，多的表加外键
班级t_class
cno(pk) cname
—————————————-
1 班级1
2 班级2

学生t_student
sno(pk) sname classno(fk)
——————————————————————-
101 张1 1
102 张2 1
103 张3 2
104 张4 2
105 张5 2

提醒：在实际开发中，以满足客户需求为主，有时候会拿冗余换执行速度。

附：一对一

有2种设计方案：①主键共享 ②外键唯一
①：主键共享
t_user_login 用户登录表
id(pk) username password …..
——————————————————————————————————-
1 zs 123
2 ls 456
②：外键唯一
t_user_detail用户详细信息表
id(pk+fk) realname tel userid(fk+unique)….
———————————————————————————————————-
1 张三 1111111111 2
2 李四 1232121232 1

练习

  1. use 29_db2;
  2. create table student(
  3.     id int,
  4.     name varchar(20),
  5.     chinese float,
  6.     english float,
  7.     math float
  8. );
  10. -- 2. 请添加2列信息，出生日期，籍贯。
  11. alter table student add birthday varchar(20);
  12. alter table student add birthplace varchar(20);
  14. -- 3，请修改语文成绩的数据类型为int型.
  15. alter table student modify chinese int;
  17. -- 4. 请将各科成绩修改为默认值为0(★★),这里只能用modify
  18. alter table student modify column chinese int default 0;
  20. -- 5. 请在里面插入10名学生数据。（名字请以汉字输入）
  21. insert into student values
  22.         (3,"张三",98,97,96,"1996-8-19","陕西汉中"),
  23.         (4,"李四",95,94,93,"1996-8-19","湖北襄阳"),
  24.         (5,"王五",92,91,90,"1997-2-19","河南平顶山"),
  25.         (6,"赵六",98,97,96,"1997-2-19","四川达州"),
  26.         (7,"田七",95,94,93,"1997-2-19","北京大兴"),
  27.         (8,"酒八",92,91,90,"1997-2-19","山西大同"),
  28.         (9,"红九",68,67,66,"1998-8-19","河北保定"),
  29.         (10,"夏司",68,67,66,"1998-8-19","西藏林芝"),
  30.         (1,"漆一",48,47,46,"1998-8-19","内蒙古二连浩特"),
  31.         (2,"霜二",48,47,46,"2002-8-19","广东湛江");
  32. delete from student;
  34. -- 6. 请分别找出单科成绩最高的学生名单。语文最高的，英语最高的，数学最高的。(★★)
  35. select id,name,chinese from student where chinese = (select max(chinese) from student);
  36. select id,name,english from student where english = (select max(english) from student);
  37. select id,name,math from student where math = (select max(math) from student);
  39. -- 7. 请找出总成绩最高的学生。(★★)
  40. select id,name,chinese+english+math as total from student where 
  41. (chinese+english+math) = (select max(chinese+english+math) from student);
  43. -- 10.假设10名同学中有同姓的，如王，请找出姓王同学的信息.(★★)
  44. select * from student where name like '王%';
  46. -- 11.请找出各科不及格学生的信息。
  47. select * from student where chinese<60;
  48. select * from student where english<60;
  49. select * from student where math<60;
  51. -- 12.请找出有任何一科不及格学生的名称
  52. select name from student where chinese<60 or english<60 or math<60;
  54. -- 13.请找出两科成绩在90分以上的学生名称。(★★)
  55. select name from student where(chinese>90 and english>90 and math<=90) 
  56. or (chinese>90 and math>90 and english<=90) 
  57. or(math>90 and english>90 and chinese<=90);
  59. -- 14.请找出没有一科挂科的学生名称。
  60. select name from student where chinese>=60 and english>=60 and math>=60;
  1. -- 新建一个学生表S，有包含如下信息
  2. -- 学号 id，
  3. -- 学生姓名 name，
  4. -- 性别 gender，
  5. -- 年龄 age，
  6. -- 专业 dept
  7. -- 
  8. -- 并插入10条数据。
  9. -- 
  10. -- 学号格式为 201801 201802 201803...
  11. -- 性别只有 'male' & 'female'
  12. -- 院系包含（信息系、数学系，计算机科学系 等）
  15. use 29_db2;
  16. create table S(
  17. id bigint,
  18. name varchar(20),
  19. gender varchar(20),
  20. age int,
  21. dept varchar(20)
  22. );
  23. drop table S;
  24. insert into S values
  25. (201803,"张三","male",20,"信息系"),
  26. (201804,"李四","female",21,"数学系"),
  27. (201805,"王五","male",20,"信息系"),
  28. (201806,"赵六","female",22,"电子信息工程系"),
  29. (201807,"田七","male",20,"信息系"),
  30. (201808,"酒八","male",23,"信息系"),
  31. (201809,"甜九","female",20,"建筑工程系"),
  32. (2018010,"耳十","male",24,"信息系"),
  33. (201801,"漆艺","female",20,"音乐系"),
  34. (201802,"刘二","male",26,"信息系");
  36. -- 1.查询全体学生的学号与姓名。
  37. select id,name from S;
  39. -- 3.查询全体学生的详细记录。
  40. select * from S;
  42. -- 5.查询全体学生的姓名、出生年份和所有系使用列别名改变查询结果的列标题
  43. select name,(2021-age) as birthyear,dept from S;
  45. -- 7.查询所有年龄在20岁以下的学生姓名及其年龄。
  46. select name,age from S where age<20;
  48. -- 8.   查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。
  49. select name,dept,age from S where age between 20 and 23;
  51. -- 9.   查询年龄不在20~23岁之间的学生姓名、系别和年龄。
  52. select name,dept,age from S where age not between 20 and 23;
  54. -- 10  查询信息系、数学系和计算机系学生的姓名和性别。
  55. select name,gender from S where dept in("信息系","数学系","计算机系");
  57. -- 11  查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。
  58. select name,gender from S where dept not in("信息系","数学系","计算机系");
  61. -- 12  查询学号为200518的学生的详细情况。
  62. select * from S where id=200518;
  64. -- 13  查询所有姓刘学生的姓名、学号和性别。
  65. select name,id,gender from S where name like "刘%";
  67. -- 14  查询姓“李”且全名为两个汉字的学生的姓名。
  68. select name from S where name like "李_";
  70. -- 15  查询名字中第2个字为“立"字的学生的姓名和学号。
  71. select name,id from S where name like "_立%";
  73. -- 16  查询所有不姓刘的学生姓名。
  74. select name from S where name not like "刘%";
  76. -- 17  查询学号在201801~201809之间的学生姓名。
  77. select name from S where id between 201801 and 201809;
  79. -- 18     查询不同院系学生的人数。
  80. select dept,count(*) from S group by dept;
  82. -- 20  查询计算机系年龄在20岁以下的学生姓名。
  83. select name from S where age > 20 and dept = "信息系";

上：s表 下：student表
image.png
image.png

— 交叉连接
select * from s cross join student;
image.png

— 显示内连接
select from s inner join student on s.name=student.name;
— 隐式内连接
select from s,student where s.name=student.name;
image.png

— 左外连接
select * from s left join student on s.name=student.name;
image.png

— 右外连接
select * from s right join student on s.name=student.name;
image.png

附

我做的word笔记和sql脚本，希望能对你们有所帮助！
笔记链接：https://pan.baidu.com/s/1e6QQYnUzYYUI_JmluyIPpQ
提取码：1234
脚本链接：https://pan.baidu.com/s/1IaeODXfw6oPVjQgyI1BA5A
提取码：1234

https://downloads.mysql.com/archives/community/下载安装地址 mysql5.5

sql脚本文件找不到的来这儿看看，亲测有效
链接: https://pan.baidu.com/s/1Y2M9vjE2I7FuSC1XZ0rKyQ
提取码: 7mjj