本章复习MySQL与其基本概念

一、SQL基本概念

1、定义：结构化查询语言（Structred Query Language）其实就是定义了操作所有关系型数据库的规则。

二、SQL通用语法

1、SQL语句可以单行或多行书写，以分号结尾；

2、可使用空格和缩进来增强语句的可读性；

3、MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

4、3中注释

​ （1）单行注释： – 注释内容或#注释内容

​ （2）多行注释： /* 注释 */

三、SQL分类

1、DDL（Data Definition Language）：数据库定义语言 用来定义数据库、表和列等。关键字：create drop alter

2、DML（Data Manipulation Language）：数据库操作语言 用来对数据库中表的数据进行增删改。关键字：insert updata delete

3、DQL（Data Query Language）：数据库查询语言 用来对数据库表中的数据进行查询。关键字：select where

4、DCL（Data Control Language）：数据库控制语言 用来定义数据库的访问权限和安全级别及创建用户。关键字：GRANT REVOKE等。

四、DDL（操作数据库、表和列）

1、C（create）：创建 create database if not exists 数据库；character set 编码格式（utf-8）；

2、R（Retrieve）：查询 show database：查询所有的数据库 show create database 数据库名称：查询具体数据库；

3、U（Updata）：修改 alter database 数据库名称 character set 字符集名称：修改字符集；

4、D（Delete）：删除 drop database if exists 数据库名称：如果存在就删除数据库；

5、使用数据库：select database()：查询当前正在使用的数据库名称 use 数据库名称：使用指定的数据库。

五、操作表

1、查询表

（1）查询某个数据库中所有表的数据：show tables：需要提前使用一个数据库。

（2）查询表结构：desc 表名；

2、创建表

（1）语法：

​ create table 表名（

​ 列名1 数据结构1，

​ 列名2 数据结构2，

​ 列名3 数据结构3

​ ………

​ 列名n 数据结构n

​ ）

3、数据类型

① 整数类型：int

② 小数类型：double

③ 日期，只包含年月日（yyyy-MM-dd）：date

④ 日期包含年月日时分秒（yyyy-MM-dd HH:mm:ss）：datetime

⑤ 时间戳类型，包含内容与④一致：timestamp （若将来不给该字段赋值或赋值为null，则默认使用当前系统时间来自动赋值）

⑥ 字符串：varchar 姓名最大20个字符 zhangsan 8个字符 张三 2个字符

三种事件类型的区别：

• data：正如字面意思，只标识日期
• datatime：在原来日期的基础上加上了时间（time），所以在原来显示日期的基础上再加上了时间。
• timestamp：时间戳

4、删除表

（1）删除表：drop table 表名；

（2）如果存在就删除表：drop table if exists 表名；

5、修改表

（1）修改表名：alter table 表名 rename to 新的表名；

（2）修改表的字符集：alter table 表名 character set 字符集名称；

（3）添加一列：alter table 表名 add 列名 数据结构；

（4）修改列名称 类型：alter table 表名 change 列名 新列名 新数据类型；

​ alter table 表名 modify 列名 新数据类型；

6、删除列

alter table 表名 drop 列名；

一、添加数据

1、语法：insert into 表名（列名1，列名2，…，列名n） values（数据1，数据2，…，数据n）；

2、注意事项

（1）列名和值要一一对应；

（2）如果表名后，不定义列名，则默认给所有列添加值；

（3）除了数字类型（整数、小数）其它类型需要使用引号（单双都可以）引起来。

二、删除数据

1、语法：delete from 表名 [where条件]

1

delete from xuexi where id = 1;--删除xuexi表中id为1的行。

注：若不添加条件则默认删除掉所有记录

三、三种删除表的方式比较（drop、truncate、delete）

1、drop（删除表）：删除内容和定义，释放空间。将整个表完全删除，想要再添加数据是不可能的，除非新添加一个表。

drop语句将会删除该表的结构被依赖的约束（constrain）、触发器（trigger）、索引（index）；依赖于该表的存储过程/函数将被保留，但其状态会变为invalid（无效的）。

2、truncate（清空表中的数据）：删除内容、释放空间但不删除定义（保留表的数据结构）。该语句知识清除表中的所有数据。

truncate table 表名；

3、delete（删除表中的数据）：delete用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，并且将该行的删除操作作为事务记录在日志中保存，以便进行回滚操作。

注意事项：truncate与不带where的delete只删除表中的数据而不删除表的结构（定义）。

4、truncate table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用delete。（标识计数值？）

5、对于由foreign key（外键）约束引用的表，不能使用truncate table，而应该使用不带where字句的delete语句。由于truncate table记录在日志中，所以它不能激活触发器。

6、执行速度：drop > truncate > delete

7、delete语句是DML（数据库操作语言），这个操作会放到rollback segement中，事务提交之后才生效；如果有相应的trigger（触发器），执行时将被激发。drop和truncate是DDL（数据库定义语言），操作立即生效，原数据不放到rollback segement中，不能回滚，操作不触发tirgger。

四、rollback segment

1、定义：是数据库中的一些存储空间。它用来保存数据库数据发生改变时的先前值。

2、主要目的：

（1）如果因为某种原因或其他用户想要通过ROLLCACK来取消一个人的数据操作，数据就会复原到改变时的值。这种情况只在transaction（交易）的过程中有效，如果用户执行了COMMIT（事务提交）命令，那么ROLLBACK SEGMENT里面的值就会标识为失效的，数据改变就将永久化。

（2）另一个目的是当有并发的session访问了一个数据值改变但事务还没有提交的表。如果一个SELECT语句开始读取一个表同时一个事务也在修改这个表的值，那么修改前的值就会保存到ROLLBACK SERMENT里面，SELETC也是从ROLLBACK SEGMENT里面读取表的值。

五、修改数据

1、语法：uodate 表名 set 列名1 = 值1，列名2 = 值2 ，…，[where条件]

1

update xuexi set age = 19 where id = 3;--修改xuexi表中id为3的age为19.

2、注意事项：若不添加任何where条件则修改表中所有age的值为19。

一、基础查询

1、基础语法：

​ SELECT 字段列表（聚合函数、列名）

​ FROM 表名列表

​ WHERE 条件列表

​ GROUP BY 分组字段

​ HAVING 分组之后的条件

​ ORDER BY 排序

​ LIMIT 分页限定

2、基础查询

（1）多个字段的查询

① 语法：select 字段1，字段2….. from 表名

② 注意：若要查询所有字段，则可以使用*来替代字段列表

（2）去除重复查询结果

① 关键字：distinct

② 例：select distinct 列名 from 表名

（3）计算列

① 一般可以使用四则运算计算一些列的值。（一般只会进行数值型计算，且null参与的计算，计算结果为null）

② ifnull（表达式1，表达式2）：

​ 表达式1：那个字段需要判断是否为null

​ 表达式2：如果该字段为null后的替换值

（4）起别名

① 关键字：as 也可以由空格代替

② 例：select 列名1 as 别名，列名2 + 列名3 （我是空格） 别名 from 表名;

3、条件查询（where）

（1）where字句后跟条件

（2）运算符

​ ① >，<，<=，>=，=，<>（不等于） 例：select * from 表名 where age <> 20;查询表中所有年龄不等于20的行

​ ② BETWEEN …… AND …… 表示某个条件在某个范围内 例：select * from 表名 where age between 20 and 30;查询年龄在20到30之间的行

​ ③ IN（集合）：表示多个值，使用逗号分隔 例：select * from 表名 where age in(22,18,25);查询年龄为22，18，25的数据

​ ④ LIKE‘ ‘：模糊查询

​ ⑤ is null：查询某一列为null的值 例：select * from 表名 where 列名 is null；

​ ⑥ is not null：查询某一列不为null的值。

​ ⑦ 逻辑运算符

• **and或&&**：与
• **or或||**：或
• not或！：非

（3）模糊查询

① 占位符：

​ **_**：单个任意字段

​ **%**：任意多个字段

② 例

1
2
3
4
5

查询姓马的人：		select * from 表名 where name like’马%‘；

查询姓名第二个字为化的人：		select * from 表名 where name like’_马%‘;

查询姓名为三个字的人：		select * from 列名 where name like’_ _ _‘；

二、排序查询

1、语法：

order by 排序字段1 排序方式1，排序字段2 排序方式2… 先按照字段1进行排序，若1结果相同则按照2进行排序

2、排序方式

（1）升序：ASC（默认）

（2）降序：DESC

3、注意事项：

如果有多个排序条件，则当前边条件值一样时，才会判断第二条件

三、聚合函数

1、定义

将一列数据作为一个整体，进行纵向的计算

2、分类

（1）count：计算个数 例：select conut(列名) from 表名;计算表中这列的总数

（2）max：计算最大值

（3）min：计算最小值

（4）sum：计算和

（5）avg：计算平均值

3、注意事项

聚合函数是排除null值的，即统计的列中如果有一条数据为null则不会统计这一列。可以使用ifnull(表达式1，表达式2)解决

count(*)表示只要有一列数据不为null都是要统计的。

四、分组查询

1、语法

select 聚合函数 from 表名 group by 列名

例：按照性别分组，分别查询男女同学的平均分和人数

select sex，avg(math),count(id) from 表名 group by sex；

2、注意事项

分组之后查询字段：分组字段，聚合函数

3、where和having的区别

（1）where在分组之前进行限定，如果不满足条件，则不参与分组

（2）having在分组之后进行限定，如果不满足结果，则不会被查询出来

（3）where后不可以跟聚合函数，having可以进行聚合函数的判断

例：按照性别分组，分别查询男、女的平均分、人数。要求分数低于70的人不参与分组。分组之后，人数要大于2个人

select sex,avg(math),count(id) from 表名 where math > 70 having count(id) > 2;

三、分页查询

1、语法

select 列名 from 表名 limit 开始索引,每页查询的条数;

例：每页显示3条记录

select * from student limit 0,3;–第一页

select * from student limit 3,3;–第二页

2、公式

开始的索引 = （当前页码 - 1） * 每页显示的条数

limit是mysql的“方言”。

一、约束（概念）

1、定义：

对表中的数据进行限定，保证数据的正确性、有效性和完整性。

2、分类

（1）主键约束：primary key

（2）非空约束：not null

（3）唯一约束：unique

（4）外键约束：forgin key

二、非空约束（not null）

1
2
3
4
5
6
7
8
9
10

--1、创建表时添加约束
create table abc(
a1 int not null
)

--2、创建完表之后添加非空约束
alter table 表名 modify 列名 数据类型 not null;

--3、删除非空约束
alter table 表名 modify 列名 数据类型

三、唯一约束（unique）

1
2
3
4
5
6
7
8
9
10

--1、创建表时添加约束
create table abc(
a1 varchar(20) unique
)

--2、创建完表后，添加唯一约束
alter table 表名 modify 列名 数据类型 unique

--3、删除唯一约束
alter table 表名 drop index 列名;

注意事项：

• mysql中唯一约束限定的列的值可以有多个null（可以通过添加非空约束解决）
• 当要添加唯一约束的列中有重复元素时，约束会添加失败

四、主键约束（primary key）

1、注意事项

（1）含义：非空且唯一

（2）一张表只能有一个字段为主键

（3）主键就是该表中记录的唯一标识

2、约束添加方式

1
2
3
4
5
6
7
8
9
10

--1、在创建表时添加
create table abc(
a1 varchar(20) primary key
)

--2、创建完表后设置主键
alter table 表名 modify 列名 数据类型 primary key;

--3、删除主键
alter table 表名 drop primary key;

3、自动增长

（1）概念：如果某一列是数值类型的，使用auto_inrement可以来完成值的自动增长

（2）添加方式：

1
2
3
4
5
6
7

--1、创建表的时候添加

--2、添加自动增长
alter table 表名 modify 列名 数据结构 auto_increment;

--3、删除自动增长
alter table 表名 modify 列名 数据结构;

五、外键约束（foreign key）

1、约束添加方式

1
2
3
4
5
6
7

--1、建表时添加
create table 表名(
	外键列 constraint 外键名称 foreign key 外键列名称 references 主键名称（主键名称）
)

--2、创建表后添加外键约束
table 表名 add constraint 外键名称 foreign key 外键列名称 references 主键名称(主键名称)

2、级联操作

（1）当对一个外键的主表中的数据进行修改时，关联的外键的值也会相应的修改

（2）添加级联操作

① 语法：

1
2
3

alter table 表名 add constraint 外键名称
foreign key(外键字段名称) references 主表名称（主表列名称）
on update cascade on delete cascade;

（3）分类

① 级联更新：

1

on update cascade

② 级联删除

1

on delete cascade

二、多表之间的关系