数据库

MariaDB

1，数据库

1.1：概述

• • 数据存储
  • 文件管理阶段：
  • 优点：数据可以长期保存，可可以存储大量的数据，使用简单
  • 缺点：数据一致性查，数据查找修改不方便，,数据冗余度可能比较大。
  • 数据管理阶段：
  • 优点 ： 数据组织结构化降低了冗余度,提高了增删改查的效率,容易扩展,方便程序调用处
    理
  • 缺点 ： 需要使用sql 或者其他特定的语句，相对比较专业
• 数据应用范围：数据库的应用领域几乎涉及到了需要数据管理的方方面面，金融机构、游戏网站、购物网站、论坛
  网站 ... ...都需要数据库进行数据存储管理。

1.2：基本概念

• 数据库 Database：按照数据特定的结构，存储管理数据的仓库。

• 数据库管理系统 DBMS：一套独立可运行的管理数据库的软件，用于维护磁盘上的数据；维护性好，性能好，可扩展性强。

　　

• 关系型数据库和非关系型数据库
  • 关系型数据库：采用关系模型（行列构成的二维表）来组织操作呈现数据的数据库，也称为SQL数据库。

　　　　　　　　　　　　　比如： MySQL、MariaDB、Oracle、SQL Server 等。

• • 非关系型数据库：没有固定的表结构和关系模型，数据通常以键值对的形式存储，也被称为NoSQL数据库。

　　　　　　　　　　　　　　比如：MongoDB、Redis等。

1.3：关于mariaDB

MariaDB是关系型数据库，由MySQL的原创开发者在MySQL被甲骨文公司收购后创建的。

MariaDB的目标是成为MySQL的替代品，因此它与MySQL具有很高的兼容性。

• 特点
• 开源
• 跨平台（Windows、Linux、Mac）
• 提供众多语言的API：C、C++、Python、Java、Perl、PHP、Ruby等
• 运行速度快

安装

• 详细查看mariadb安装手册

1.4：数据库管理系统常见概念

DBMS - 库 - 表 - 表记录

1）关系说明

在DBMS数据库管理系统中，可以根据不同的项目创建多个库，在每个库中可以根据项目需求创建不同

的数据表，数据表中存放的是具体的数据。

库：database

是表的集合，一个库中可以存放若干张表，通常库服务于项目。

3）表 table

是数据的集合，具有一组相同属性的数据存放在同一张表中。

行（row）：称为是 记录，表中每一条数据

列（column）：称为是 字段，每一条记录的属性

 

1.5 SQL语言

1）数据库角色

数据库是一个独立运行的软件，并且是以 服务端 的形式体现的，我们要操作数据库则需要以客户端的角

度与服务端建立连接并进行相关的操作。

2）SQL语言

我们连接上数据库后，向其发送 SQL 语句，数据库理解该SQL语句的含义并执行相关操作并回馈结果。

什么是SQL语言

结构化查询语言(Structured Query Language)，是一种数据库查询和程序设计语言，用于存取数

据以及查询、更新和管理关系数据库。

SQL语言特点

• SQL语言基本上独立于数据库本身
• 各种不同的数据库对SQL语言的支持与标准存在着细微的不同
• 每条命令最好以 ; 结尾
• SQL语句不区分大小写，但是好的书写习惯：关键字大写，非关键字大小

3）SQL分类

• DDL：数据定义语言，是操作数据库对象的语言。数据库对象（库，表，视图，索引，序列）
• DML：数据操作语言，是操作表中数据的语言。对表中数据操作的语言(增INSERT，删DELETE，
• 改UPDATE)
• DQL：数据查询语言，是查询表中数据的语言。SELECT语句(学习的重点和难点)
• DCL：数据控制语言，管理数据库的语言(权限分配等，DBA关心的)
• TCL：事务控制语言，事务控制语言。

1.6 数据库的连接方式

1）常用的客户端

• 命令行
• 图形化界面
• JDBC
• 集成开发环境(IDE)

2）IDEA连接MariaDB数据库

第1步：点击idea窗口右侧 Database，在 Data Source 中选择 MySQL

 

第2步：填写连接名称Name 或 描述Comment，输入用户名和密码都为***，首次连接需要下载驱动，

点击 Download　　

 

第3步：输入SQL语句，选中后，可以点击左上方 绿色箭头 执行SQL语句

 

2 DDL数据定义语言

对数据库对象进行操作的语言，涉及到的关键字CREATE、ALTER、DROP等

2.1 数据库管理

1）DDL指令 

　　1，查看已有库:

SHOW DATABASES;

　　2,创建库

CREATE DATABASE 数据库名 [CHARSET=字符集];
eg1. 创建数据库mydb
CREATE DATABASE mydb;
eg2. 创建数据库mydb1，指定字符集为UTF8
CREATE DATABASE mydb1 CHARSET=UTF8;
eg3. 创建数据库mydb2，指定字符集为GBK
CREATE DATABASE mydb2 CHARSET=GBK;

注意：数据库中标识符命名规则
1. 数字、字母、下划线，但不要使用数字开头
2. 尽量不要使用特殊字符和mysql关键字
3. 多个单词使用下划线连接，尽量见名知意

　　3，查看创建数据库的信息

SHOW CREATE DATABASE 数据库名;
eg. SHOW CREATE DATABASE mydb;

　　4，切换库

USE 库名;
eg. 切换到数据库mydb
USE mydb;

　　5，删除库

DROP DATABASE 库名;
eg. 删除mydb库
DROP DATABASE mydb;

2.2 数据表管理

1）DDL指令

　　1. 创建表

CREATE TABLE 表名(
字段名名1 类型[(长度)] [DEFALUT 默认值] [约束],
字段名名2 类型,
...
)[CHARSET=字符集]

　　eg：

# 1.创建库mydb，并切换到该库
CREATE DATABASE mydb; # 创建数据库mydb
USE mydb; # 切换到mydb，后面创建表都是创建到这个库中
# 2.创建一张表user，保存用户信息(用户名，密码，昵称，年龄)
CREATE TABLE user(
id INT, # 整型
username VARCHAR(32), # 字符类型，长度为最大字符数，即不能超过32个字符
password VARCHAR(32), # 字符类型，字符长度最大不能超过32个
nickname VARCHAR(32),
age INT # 整型
)

　　2，查看当前库中所有的数据表

 

SHOW TABLES;

　　3，查看表结构

DESC 表名;
eg. 查看user表的表结构
DESC user;

　　4，查看数据表创建信息

SHOW CREATE TABLE 表名;
eg. 查看user表的创建信息[字符编码]
SHOW CREATE TABLE user;

　　5，修改表名：

RENAME TABLE 原表名 TO 新表名;
eg. 将user表重命名为 userinfo 表
RENAME TABLE user TO userinfo

　　6，删除表

DROP TABLE 表名;
eg. 删除user表
DROP TABLE user;

3）修改表结构ALTER

准备一张表hero 

CREATE TABLE hero(
name VARCHAR(32),
age INT(3)
);

1. 添加字段 - ADD

• 表末尾追加字段：ALTER TABLE 表名 ADD 字段名 类型[(长度) 默认值 约束];

• 表始终加字段：ALTER TABLE 表名 ADD 字段名 类型 FIRST;

• 指定位置增加字段：ALTER TABLE 表名 ADD 字段名 类型 AFTER 表中现有字段名
• 示例:

• 1. 向表hero的末尾添加一个新字段gender，它的类型是字符串，最大宽度为10
  ALTER TABLE hero ADD gender VARCHAR(10);
  2. 在hero表最开始添加id字段，类型为int
  ALTER TABLE hero ADD id INT FIRST;
  3. 在name字段后面添加密码pwd字段
  ALTER TABLE hero ADD pwd VARCHAR(32) AFTER name;

2,删除字段-ordp

ALTER TABLE 表名 DROP 字段名;
eg.将hero表中的pwd字段删除
ALTER TABLE hero DROP pwd;

3. 修改字段数据类型 - MODIFY 

 

ALTER TABLE 表名 MODIFY 字段名 新数据类型;
eg.修改hero表中的name字段长度为64
ALTER TABLE hero MODIFY name VARCHAR(64);

4. 替换字段 - CHANGE 

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;
eg.修改hero表中的gender字段，改名为nickname 类型为字符串，最大宽度为32
ALTER TABLE hero CHANGE gender nickname VARCHAR(32);

修改表结构的注意事项

• 修改表结构最好是在表中没有数据的情况下进行
• 当表中含有数据时
  • 尽量不修改表中某字段的类型，否则可能因为现有数据不满足新修改的类型导致修改失败
  • 尽量不缩短字段长度
  • 若为字段新添加约束，该字段现有的数据不能违反该约束

3 DML表记录管理 

DML是对表中数据 进行操作的语言，涉及的关键字：INSERT UPDATE DELETE

　　INSERT：向表中插入新数据的语句

 　　UPDATE：修改表中数据的语句

　　DELETE：删除表中数据的语句 

整备工作

USE mydb; # 使用mydb库
CREATE TABLE person( # 创建表person
name VARCHAR(30),
age INT
)

3.1 插入数据INSERT

向数据表中插入数据

1）语法

　　全列插入

　　在INSERT语句中不指定字段时，为全列插入，此时要求VALUES子句后面 指定的值的个数，顺序，类型必须与表结构中字段完全一致

INSERT INTO 表名 VALUES(值1,值2,...) # 要求VALUES子句中指定的值必须与表结构一致
eg. 在person表中插入1条数据
INSERT INTO person VALUES('张三',12);
补充：查询表中数据
SELECT * FROM person;

　　选择字段插入

INSERT INTO 表名 (字段1,字段2,字段3,...) VALUES (值1,值2,值3,...);
向表中插入一条记录，将值1插入的到字段1中，将值2插入到字段2中
eg.
INSERT INTO person (name,age) VALUES ('李四',22);
INSERT INTO person (age,name) VALUES (33,'王五');
INSERT INTO person (name) VALUES ("赵六");

注意事项：

注意
1. 在数据库中，字符串的字面量 是使用 单引号括起来的，需要注意
2. 在INSERT语句中 指定的字段 与VALUES后面 指定的值 的 顺序、个数、类型要完全一致
3. 在INSERT语句中字段指定时的顺序和个数可以与表结构定义时不一致

　　批量插入：

　　一次在数据表总插入多条记录

INSERT INTO 表名(字段...) VALUES(第一组值...),(第二组值...),....;
eg.一次插入三条记录
INSERT INTO person(name,age) VALUES('阿猫',11),('阿狗',22),('阿三',33);

 

2）关于默认值

INSERT语句中指定字段时，忽略的字段辉插入默认值，当表中字段没有被明确指定默认值时，默认值一律位NULL

使用default关键字来为字段指定默认值， 可以在创建表时，也可以在修改表时只当

　　创建表时指定默认值

CREATE TABLE person(
name VARCHAR(30) DEFAULT '无名氏',
age INT(3) DEFAULT 30
)

修改表时为字段指定默认值

ALTER TABLE person MODIFY name VARCHAR(30) DEFAULT '无名氏';

测试

此时向person表中插入数据时，若不指定name字段，该字段默认值插入'无名氏' 

INSERT INTO person (age) VALUES (55);

3.2 更新数据UPDATE

修改表数据

1）语法

UPDATE 表名
SET 字段1=新值,字段2=新值,...
[WHERE 过滤条件];

示例：

UPDATE person
SET age=40;
此时该SQL执行后，person表中每条记录的age字段都会被改为40

注:当UPDATE语句中不添加WHERE子句时，会将表中所有记录进行修改，这种操作实际开发中很少见

2）WHERE子句

　　当UPDATE语句添加了WHERE子句时，只有满足WHERE子句要求的记录才会被修改

　　示例1：将张三的年龄修改为22

UPDATE person
SET age=22
WHERE name='张三';
数据库在修改记录时对表是逐行进行的，每一行是否进行修改取决于该行记录是否满足WHERE子句的条件要求

示例2：将40岁的人改为36岁

UPDATE person
SET age=36
WHERE age=40;

示例3：将李四的名字改为'李老四'，年龄改为66

UPDATE person
SET name='李老四',age=66
WHERE name='李四';

3）WHERE子句基础条件

　　WHERE子句会在后面DQL中详细展开介绍

　　WHERE中基础条件：>, >=, <, <=, =, !=

　　示例1：将年龄高于30岁的人改为29

UPDATE person
SET age=29
WHERE age>30;

3）使用表达式修改

将每个人的年龄涨一岁

UPDATE person
SET age=age+1;

3.3 删除数据DELETE

删除表中数据

1）语法

DELETE FROM 表名
[WHERE 过滤条件];

注:DELETE语句通常不会忽略WHERE子句，否则是清空表操作!

示例

删除person表中'李老四'
DELETE FROM person
WHERE name='李老四'; # 仅删除person表中name字段为'李老四'的记录
删除年龄小于25岁的人
DELETE FROM person
WHERE age<25;

2，清空表：

DELETE FROM person

3）DML总结

• INSERT语句，向表中插入数据的语言
  • INSERT语句指定的字段顺序，个数可以与表结构不一致，但是VALUES子句后指定的值必须与指定的字段顺序，类型，个数完全一致
• INSERT语句在指定字段时，可以忽略某些字段，此时插入数据时被忽略的字段会插入默认值
• INSERT语句中可以使用关键字 NULL 显示声明插入NULL值
• INSERT语句中不指定字段时为全列插入，此时VALUES后指定的值的顺序，个数，类型要与表结构完全一致。实际开发中不推荐这样的写法。
• UPDATE语句，修改表中记录的语言
  • UPDATE语句通常要指定WHERE子句，如果不指定则为全表修改
• DELETE语句，删除表中记录的语言
  • DELETE语句通常要指定WHERE子句，否则是清空表操作

4 数据类型

　　设计一张表时，要为表中每一个字段指定类型以确保该字段可以保存正确类型的数据 

4.1 数值类型

1）整数类型

INT类型

占用4个字节，保存范围+-21亿

BIGINT类型

占用8个字节

2）浮点数类型

DOUBLE(M,N)

M是一个数字，表示数字的总位数

N是一个数字，表示小数位的位数

DOUBLE(7,2) 可以保存一个7位数，其中2为是小数，整数5位

示例

CREATE TABLE person1(
age INT,
salary DOUBLE(7,2) # 最大99999.99
);

插入小数

INSERT INTO person1(salary) VALUES(99999.99);

如果小数位的精度超过了允许精度时，会进行四舍五入

INSERT INTO person1(salary) VALUES(1234.867);
^7会进行四舍五入
实际插入表中后，该记录salary字段的值位1234.87

如果四舍五入后得到的值超过了该字段可以保存的最大值时会报错

4.2 字符类型

1）定长字符串

CHAR类型是定长字符串

• CHAR(m):m是一个数字，表示长度，单位是字符。m最大可取255
• CHAR类型在磁盘中开辟的长度是固定的。
• 优点：在磁盘中保存每条记录的长度是固定的，因此读取速度快
• 缺点：在磁盘中占用空间固定，无论实际保存数据多少，都要占够空间，因此对磁盘空间有浪费

2）变长字符串

VARCHAR类型

• VARCHAR(m)：m是一个数字，表示字符串的长度
• 实际占用磁盘空间按照实际保存的数据而定
• 优点：由于磁盘空间占用取决于实际保存的数据，因此不造成磁盘空间浪费
• 缺点：由于数据长度不一致，会导致读取效率低

TEXT类型

• TEXT(m):m是一个数字，m最大值为65535

4.3 日期类型

• DATE 用于保存年月日
• TIME 用于保存时分秒
• DATETIME 用于保存年月日时分秒
• TIMESTAMP (时间戳)，保存UTC时间，记录自1970年元旦至表示的时间所经过的毫秒

示例

CREATE TABLE userinfo(
id INT, # id整数类型
name VARCHAR(30), # name字符串类型，最大宽度为30
gender CHAR(1), # gender字符串类型，最多1个字符
birth DATETIME, # birth日期类型，保存年月日时分秒
salary DOUBLE(7,2) # salary浮点数类型，最多保存99999.99
);

插入一个日期类型的数据时，DATETIME类型为例(可以保存年月日时分秒)，可以使用字符串来表

示，但是格式必须为"yyyy-MM-dd hh:mm:ss"(M表示月，m表示分)

INSERT INTO userinfo(id,name,gender,birth,salary)
VALUES(1,'张三','男','1998-08-03 10:22:35',5000.65);

5 约束条件

 

我们可以为表添加约束条件，可以限制对表的操作必须符合我们施加的约束条件，否则数据库会拒绝该

操作

5.1 主键约束PrimaryKey

1）主键约束

主键约束要求该字段的值必须 非空且唯一

主键约束一般会为表中第一个字段添加，并且通常该字段取名为"id"

示例

CREATE TABLE user1(
id INT PRIMARY KEY, # 为id字段添加主键约束
name VARCHAR(30),
age INT
);
INSERT INTO user1 (id,name,age) VALUES(1,'张三',22),(2,'李四',33);

主键字段不能插入重复的值，并且在修改表记录时，也不可以将重复的值修改到主键字段。

2）自增长

具有主键约束的字段通常主键的生成方式由系统完成，而自增就是其中一种方式

示例

创建表时为主键字段添加自增

CREATE TABLE user2(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30),
age INT(3)
)

 

当主键字段具有自增时，插入数据则可以忽略主键字段

INSERT INTO user2(name,age) VALUES('张三',33);
INSERT INTO user2(name,age) VALUES('李四',26)

当主键字段具有自增时，可以显示的向主键中插入NULL值，但此时主键字段仍然使用自增

INSERT INTO user2(id,name,age) VALUES(NULL,'王五',45);

5.2 非空约束(NOT NULL)

被非空约束施加的字段，该字段的值不能为NULL

示例

　　创建表时为字段添加非空约束

CREATE TABLE user3(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL, # name字段施加非空约束
age INT
)

插入数据时，不能向具有非空约束的字段中插入NULL值

INSERT INTO user3(name,age) VALUES(NULL,22); # 不可以将NULL值插入name字段

 

5.3 唯一性约束(UNIQUE)

具有唯一性约束的字段值不允许重复，但是可以为NULL。

示例

　　创建表时为字段添加唯一性约束

CREATE TABLE user4(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) UNIQUE,
age INT
)

插入数据时，不可以将重复的值插入到具有唯一性约束的字段中

INSERT INTO user4(name,age) VALUES('张三',22); # 可以
INSERT INTO user4(name,age) VALUES('张三',45); # 不可以再次插入'张三'到name字
段

)5.4 检查约束(CHECK)

检查约束允许我们自定义

示例：

• 创建表时添加check约束：

• CREATE TABLE user5(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30),
  age INT CHECK(age>0 AND age<120) # 要求age字段的值>0且<120
  );

  插入数据时，age字段的取值必须符合CHECK约束

• INSERT INTO user5(name,age) VALUES('张三',22); # 成功
  INSERT INTO user5(name,age) VALUES('李四',160); # 失败

  

5.5 外键约束(了解)

A）外键约束

外键约束

外键约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、关联性

功能

建立表与表之间的某种约束的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完

整，关连性更强，为了具体说明创建如下部门表和人员表。

示例

• -- 创建部门表
  CREATE TABLE dept
  (
  id int PRIMARY KEY auto_increment,
  dname VARCHAR(50) not null
  );
  INSERT INTO dept
  VALUES (1, "技术部"),
  (2, "销售部"),
  (3, "市场部"),
  (4, "行政部"),
  (5, '财务部'),
  (6, '总裁办公室');
  -- 创建人员表
  CREATE TABLE emp
  (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(32) NOT NULL,
  age tinyint,
  salary double(8, 2),
  dept_id int
  );
  INSERT INTO emp
  VALUES (1, "Lily", 29, 20000, 2),
  (2, "Tom", 27, 16000, 1),
  (3, "Joy", 30, 28000, 1),
  (4, "Emma", 24, 8000, 4),
  (5, "Abby", 28, 17000, 3),
  (6, "Jame", 32, 22000, 3);

  上面两个表中每个人员都应该有指定的部门，但是实际上在没有约束的情况下人员是可以没有部门
  的或者也可以添加一个不存在的部门，这显然是不合理的。
  主表和从表
  若同一个数据库中，B表的外键与A表的主键相对应，则A表为主表，B表为从表。

B）语句

foreign key 外键的定义语法：

[CONSTRAINT symbol] FOREIGN KEY(外键字段)
REFERENCES tbl_name (主表主键)
[ON DELETE {RESTRICT | CASCADE | SET NULL}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL}]

• 创建表时指定外键

• -- 创建表时直接建立外键
  CREATE TABLE emp
  (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(32) NOT NULL,
  age INT,
  salary DOUBLE(10, 2),
  dept_id INT,
  CONSTRAINT dept_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
  );

  修改表时添加外键

• ALTER TABLE emp
  ADD CONSTRAINT dept_fk FOREIGN KEY (dept_id) REFERENCES dept (id);

  通过外键名称解除外键约束

ALTER TABLE emp DROP FOREIGN KEY dept_fk;

C）级联动作

• RESTRICT(默认) : ON DELETE RESTRICT ON UPDATE RESTRICT
  • 当主表删除记录时，如果从表中有相关联记录则不允许主表删除
  • 当主表更改主键字段值时，如果从表有相关记录则不允许更改

• CASCADE：数据级联更新 ON DELETE CASCADE ON UPDATE CASCADE

• • 当主表删除记录或更改被参照字段的值时,从表会级联更新

  • ALTER TABLE emp
    ADD
    FOREIGN KEY (dept_id)
    REFERENCES dept (id)
    ON DELETE CASCADE ON UPDATE CASCADE;

     

• SET NULL : ON DELETE SET NULL ON UPDATE SET NULL
  • 当主表删除记录时，从表外键字段值变为null
  • 当主表更改主键字段值时，从表外键字段值变为null

ALTER TABLE emp
ADD
CONSTRAINT dept_fk FOREIGN KEY (dept_id)
REFERENCES dept (id)
ON DELETE SET NULL ON UPDATE SET NULL;

6 IDEA中导入sql脚本文件

第1步：鼠标右键选择tedu.sql文件，并进行配置

 

第2步：在弹出的配置窗口中点击"+"来设置数据库源,目的是在哪个数据库中执行这个tedu.sql脚本

 第三步：我们点击的数据源点击Run按钮

 

第4步：等待执行完毕即可

数据量较大，所以执行完毕需要一定时间，等待即可

 

数据库查询

tedu.sql 数据表说明

 

1 DQL基本查询

DQL语言用来检索表中数据的语言，涉及到的关键字SELECT

语法

　　　　　　　　　　执行顺序
SELECT 子句 　　　　6
FROM 子句 　　　　　1
JOIN... ON...子句 2
WHERE 子句 　　　  3
GROUP BY 子句　　  4 
HAVING 子句       5
ORDER BY 子句     7
LIMIT 子句        8

1.1 基础查询

语法

SELECT 字段1，字段2.。。FROM 表名: 

• SELECT 子句用于指定查询表中的字段
• FROM子句用于指定数据来自那张表

示例

• 查询表中所有字段，以及指定字段

• USE tedu;
  eg1. 检索teacher表中的所有字段的所有记录
  SELECT * FROM teacher;
  eg2. 检索所有老师的id、姓名和年龄
  SELECT id,name,age FROM teacher;

  注意
  实际开发中，java代码若执行DQL语句，尽量不要用"*" .
  因为当使用SELECT * 时数据库首先要查询数据字典了解待查询表的表结构，了解全字段后才
  能进行查询。因此会给数据库带来额外的开销，这不划算。因为java代码仅需要编写一次，
  因此我们应当在SELECT后将所有字段列出。

  1.2 WHERE子句
  在DQL语句中，WHERE同样用于指定过滤条件，此时仅将满足该过滤条件的记录查询出来
  示例
• 查看"队长"的信息，列出:名字，职位，工资，年龄

• SELECT name,title,salary,age
  FROM teacher
  WHERE title='队长';

•  

1)算术运算符

 示例：

查看年龄为偶像的队长信息

SELECT * FROM student
WHERE age%2=0;

查看年薪高于6000的队长都有谁？

SELECT name,salary
FROM student
WHERE salary*12>60000;

2）逻辑运算符

 

逻辑运算符说明

AND："与"，都为真时才为真

OR："或"，都为假时才为假

1，示例：

查看7岁的"大队长"都有谁?列出这些学生的名字,年龄,性别和职位

SELECT name,age,gender,job
FROM student
WHERE age=7 AND job='大队长';

 

查看班级编号小于6的所有中队长都有谁?列明名字，年龄，性别，班级编号(class_id)，职位

SELECT name,age,gender,class_id,job
FROM student
WHERE class_id<6 AND job='中队长';

 

查看所有一级讲师和三级讲师的名字，职称，工资?

SELECT name,title,salary
FROM teacher
WHERE title='一级讲师' OR title='三级讲师';

 

查看所有大队长，中队长和小队长的名字，性别，年龄和职位?

SELECT name,gender,age,job
FROM student
WHERE job='大队长' OR job='中队长' OR job='小队长';

关于 AND 和 OR 的优先级

　　AND的优先级高于OR，为了提高OR的优先级，可以使用小括号 "()"

　　查看班级编号在6(含)以下的所有大队长和中队长的名字，年龄，性别，班级编号和职位

SELECT name,age,gender,class_id,job
FROM student
WHERE class_id<=6 AND job='大队长' OR job='中队长';
# 班级编号在6以下的所有大队长 或者 所有班级的中队长
SELECT name,age,gender,class_id,job
FROM student
WHERE class_id<=6 AND (job='大队长' OR job='中队长');
# 使用()来提高OR的优先级达到查询需求

3）比较运算符

 

A）BETWEEN... AND ...

在一个范围内

示例

• 查看工资在2000到5000之间的队长的名字,性别,年龄,工资

• SELECT name,gender,age,salary
  FROM student
  WHERE salary>=2000 AND salary<=5000;
  等价于
  SELECT name,gender,age,salary
  FROM student
  WHERE salary BETWEEN 2000 AND 5000;

  查看年龄 除了 8到10岁的学生的名字，性别，年龄

• SELECT name,gender,age
  FROM student
  WHERE age NOT BETWEEN 8 AND 10;

   

B）IN(列表)

• IN(列表)：等于列表其中之一
• NOT IN(列表)：不在列表中

示例

SELECT name,gender,age,job
FROM student
WHERE job='大队长' OR job='中队长' OR job='小队长';
等价于
SELECT name,gender,age,job
FROM student
WHERE job IN('大队长','中队长','小队长');

• 查看所有大队长，中队长，小队长的名字，职称，工资和性别

SELECT name,title,salary,gender
FROM student
WHERE title IN ('大队长','中队长','小队长');

• 查看除大队长和中队长之外的所有队长的名字，职称，工资

• SELECT name,title,salary
  FROM student
  WHERE title NOT IN('大队长','中队长');

   

C）NULL值判断

判断一个字段值是否为NULL

　　IS NULL：判断字段的值是否为NULL

　　IS NOT NULL：判断字段的值是否不是NULL

　　不可以用 = 和 != 判断NULL值

示例

查看那些老师的奖金为空

SELECT name,salary,comm
FROM teacher
WHERE comm IS NULL;

查看有奖金的老师

SELECT name,salary,comm
FROM teacher
WHERE comm IS NOT NULL;

2 DQL高级查询

2.1 LIKE 模糊查询

LIKE有两个可用的通配符

• _：表示任意一个字符。确切的数量表示1个字符
• %：表示任意个字符。不确切的数量表示0-任意个字符

格式示例

示例

LIKE '%X%' 表示字符串中包含字符X
LIKE '_X%' 表示字符串中第二个字符是X
LIKE 'X%' 表示字符串以X开始
LIKE '%X' 表示字符串以X结束
LIKE '%X_Y' 表示字符串倒数第三个字符数X并且最后一个字符是Y

 

查看名字中含有'王'的老师又有谁?

SELECT name,title,salary
FROM teacher
WHERE name LIKE '%王%';

2.2 AS 别名

语法格式：字段名<空格>别名

别名通常使用在SELECT子句和FROM子句中

• 在SELECT子句中我们可以为字段取别名。
  • 当字段为函数或表达式时，我们通常给字段添加别名，为了增加可读性
  • 为SELECT中的子查询取别名
• 在FROM 子句中可以为表添加别名

eg1：查询字段重命名

查询所有老师的姓名、工资和年薪

# AS 关键字可以省略不写
SELECT name,salary,salary*12 AS annusal
FROM teacher;

eg2：表重命名

-- 关键字可以省略不写
SELECT t.name, t.salary, salary*12 AS annusal
FROM teacher AS t;

2.3 ORDER BY排序

ORDER BY子句用于对查询结果集进行排序，可以按照ORDER BY后指定的字段值进行升序或降序排序

• 升序：ORDER BY 字段 [ASC]：将当前结果集按照指定的字段值从小到大排序。
• 降序：ORDER BY 字段 DESC：将当前结果集按照指定的字段值从大到小排序。

ORDER BY可以按照多个字段排序，此时排序存在优先级。

• 首先按照ORDER BY后指定的第一个字段的值将结果集按照该字段排序方式(升序或降序)排序
• 排序后再按照第二个字段指定的值排序，仅会对第一个字段值相同的记录按照第二个字段排序
• 若有第三个字段以此类推

不指定排序方式时，默认为升序

示例

查看老师的工资排名,从多到少

-- 将结果集按照salary的降序进行排序
SELECT name,salary
FROM teacher
ORDER BY salary DESC;

查看老师奖金的排名?

SELECT name,comm
FROM teacher
ORDER BY comm DESC;

查看学生的生日,按照从远到近

-- 日期是可以比较大小的，规则"远小近大"
SELECT name,birth
FROM student
ORDER BY birth;

查看7-10岁的学生信息,学生按照年龄从大到小排序(同年龄的看生日)

SELECT name,age,birth
FROM student
WHERE age BETWEEN 7 AND 10
ORDER BY birth;

查看老师的工资和奖金，首先按照奖金的升序，再按照工资的降序

SELECT name,comm,salary
FROM teacher
ORDER BY comm ASC,salary DESC; # 每个字段都要单独指定排序方式
多字段排序时，首先将结果集按照comm的排序方式排序，之后将comm字段值相同的记录再按照salary
的方式排序
当comm字段值不同的记录，则不会再按照salary字段排序

 

2.4 LIMIT分页查询

LIMIT 子句用于限制由 SELECT 语句返回的数据数量 或者 UPDATE,DELETE语句的操作数量

1）使用场景

当一条DQL语句可以查询出非常大量的数据时，我们可以分批将数据查询，例如淘宝检索一件商品时，

分出现第一页，第二页等信息，每页展示30条记录。一次仅查询30条记录，避免因为数据庞大导致系统

过度的资源开销。

2）语法说明

SELECT ...
FROM ...
WHERE ...
ORDER BY ...
LIMIT M,N;

M：表示跳过结果集M条记录

N：表示从跳过的M条记录后连续检索N条记录

在分页查询中有两个常见的参数

• 页数:当前显示第几页内容
• 条目数:每页显示的条目数

分页公式

• M:(页数-1)*条目数
• N:条目数

• 例如:显示第3页，每页显示5条记录
  M:(3-1)*5 ->M:10
  N:5 ->N:5

   

示例：

查看工资

SELECT name,salary
FROM teacher
ORDER BY salary DESC
LIMIT 0,5;

查看老师奖金信息，按照降序排序后，每页显示3条，显示第5页?

页数:5 条目数:3
- M:(页数-1)*条目数 (5-1)*3->M:12
- N:条目数
SELECT name,comm
FROM teacher
ORDER BY comm DESC
LIMIT 12,3;

3 聚合操作

聚合操作指的是在数据查找基础上对数据的进一步整理筛选行为，实际上聚合操作也属于数据的查询筛

选范围。

3.1 聚合函数

 

注意

• MIN、MAX、SUM、AVG是对值的统计；而COUNT是对记录数的统计
• 聚合函数忽略NULL值；这个在AVG，COUNT上尤为明显

1）聚合函数示例

查看老师的平均工资是多少?

SELECT AVG(salary)
FROM teacher;

 

查看老师的最高工资,最低工资,平均工资和工资总和都是多少?

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM teacher;

 

查看负责课程编号1的老师的平均工资是多少?

SELECT AVG(salary)
FROM teacher
WHERE subject_id=1;

 

查看总共多少位老师?

SELECT COUNT(name) FROM teacher; # 统计name字段值不为NULL的记录共多少条
SELECT COUNT(comm) FROM teacher; # 因为comm字段有NULL值，聚合函数忽略对NULL的统
计
SELECT COUNT(*) FROM teacher; # DBMS基本都对COUNT(*)采取了优化

3.2 GROUP BY 分组

GROUP BY子句可以将结果集按照指定字段值相同的记录进行分组，配合聚合函数可以实现组内统计。

在SELECT子句中出现聚合函数时，那么不在聚合函数中的字段都要出现在GROUP BY子句中。

GROUP BY子句是配合聚合函数使用的，如果SELECT子句中没有聚合函数，通常不写GROUP BY。

1）单字段分组

示例

查看每种职位的老师平均工资是多少?

SELECT AVG(salary),title
FROM teacher
GROUP BY title;

 ***内容不全，还要补充，希望理解，大家互相学习,谢谢!!