数据库入门笔记

songxh
308 阅读15分钟

一、基础篇

1、普通查询

去重

在表名的前方加上'distinct'

image.png

'+' 的作用

在sql中，'+' 的作用只有运算功能。若两个参数皆为数字，则做加法运算；若存在字符型，则试图将其转换成整型，若能成功，则继续做加法运算；若失败，则视为0，继续做加法运算

image.png

拼接

使用concat(v1,v2,v3...)来进行拼接操作

image.png

2、条件查询

语法

image.png

执行顺序：①查看是否存在该表-->②执行筛选-->③查询符合条件的列表

条件运算符: '>' '<' '>=' '<=' '!=' '<>(不等于)' 逻辑运算符: '&&' '||' '!' 'and or not' 模糊查询符号: 'like' 'between and' 'in' 'is null'

通配符的使用

%表示任意个字符，包括0个 表示一个字符 若查询时，存在查询包含''字符的条件，可以使用 " \ " 来进行转义

还可以指定某个字符为转义字符，使用 'escape [str]' 来进行定义(写在代码末尾)

image.png

between and

注意：包含临界值；必须数据小的写在前面，不能颠倒顺序。

image.png

in

当筛选条件为一列中查询多个条件时，可以使用in。等价于'=' image.png

安全等于<=>

安全等于不仅可以判断NULL，还可以判断普通类型的值

image.png

3、排序查询

语法

image.png

asc： 升序，可省略，因为默认为升序

desc： 降序

按照字段的长度来进行排序

可以通过length (字段名) 来获取其字节长度(utf-8中，一个单词占一个字节，一个汉字占三个字节)

image.png

多个字段排序

多个字段排序，使用逗号将字段隔开即可

image.png

3、字符函数

length获取参数的字节个数

select length('abcd')  结果为4
select length('abc哈哈哈')   结果为12，因为utf-8中，一个字母占一个字节，一个汉字占3个字节

concat拼接字符串

select concat(字段名1，字段名2，字段名3...) from 表名

upper 、lower

select upper('abcd')   结果为ABCD,变大写
select lower('AAAA')   结果为aaaa，变小写

substr(substring)截取字符

select substr('这是一个例子',4)  结果为“个例子”，下标从1开始
select substr('这是一个例子',1,3)  结果为“这是一”，截取指定范围内的字符

instr返回字符中指定字符第一次出现的索引位置

select instr('这是一个例子','一个')  返回结果为‘3’.如果找不到，返回0

trim 去掉前后空格

select trim('  aa  ')  返回结果为‘aa’，将前后空格给去掉
select trim('a' from 'aaaaaaa例aa子aaaaa')  结果为‘例aa子’去掉指定的字符，只能是前后，中间的字符不能去掉

lpad在左边填充指定的字符使得其长度达到指定的长度

select lpad('例子',10,'*')   结果为‘******例子’
select lpad('这是一个例子',3,'*')  结果为‘这是一’，即当字符长度超过指定的长度，便从左往右截取

lpad右填充类似

replace 替换

select replace('这是一个例子一个例子','一个','个一')  结果为‘这是个一例子个一例子’。替换全部

4、数学函数

round四舍五入

select round(1.45)  结果为1
select round(1.445,2)  结果为1.45，小数点后保留两位

ceil向上取整，返回大于等于该参数的最小整数

select ceil(1.1)  结果为2
select ceil(1.00)  结果为1

floor向下取整，类似ceil

truncate截断

select truncate(1.456,1)   结果为1.4.表示小数点后保留几位

mod取余

select mod(10,3)  结果为1
取模运算中有一个公式： a-a/b*b ,可以很好的判断符号

5、日期函数

now返回当前日期和时间

select now();

curdate返回当前其他日期，不包含时间

select curdate();

curtime返回时间，不返回日期

select curtime();

str_to_date将字符通过指定的格式转换城时间

select str_to_date('1998-3-2','%Y-%c-%d')

image.png

date_format将日期转换成指定格式的字符

select date_format(now(),'%Y年%m月%d日')

6、其他函数

select version();   查看版本号
select database();  查看当前的库
select user();      查看当前用户

7、流程控制函数

if函数，类似if else

select if(10>6,'大'，'小')  结果为‘大’

case函数

使用一：类似switch case

mysql中的使用和Java中类似，只是改变了词的使用。

mysql中的case等价于java中的switch；mysql中的when等价于java中的case；mysql中的then等价于java中的冒号；mysql中的else等价于java中的default；end表示结束.

image.png

使用二：类似与多重if

image.png

8、分组查询

group by

语法

image.png

注意：查询列表必须特殊，要求是分组函数和group by后面出现的字段

image.png

添加分组后的筛选使用having

image.png

分组查询的筛选条件可以分为两类：分组前筛选，分组后筛选。区别是数据源不同。前者数据源在原始表中，后者的数据源是分组后的结果集。前者放在group by前用where来声明，后者放在group by后面用having来声明

按多个字段分组

image.png

9、连接查询

即多表查询，当查询字段来自多个表时，就使用连接查询

多表查询时添加筛选条件

筛选条件使用and关键字来声明

image.png

自链接

自己连接自己，总共只涉及到一张表。需要注意使用别名来区分不同的含义。

image.png

sql99语法-->内连接，外连接，交叉连接

语法

image.png

分类

image.png

内连接

语法

select 查询列表
from  表1 别名
inner join 表2 别名  (inner可省略)
on  连接条件

等值连接

经典案例

image.png

image.png

自连接

经典案例

image.png

外连接

应用场景：应用于查询一个表中有，另一个表中没有的记录

特点：外连接查询的结果为主表中的所有记录，如果从表中有与它匹配的，则显示匹配的值；若从表中没有相匹配的，则显示NULL

外连接查询结果=内连接查询结果+主表有而从表没有的记录

左外连接，left join左边的是主表

右外连接： right join右边的是主表

（可以交换顺序来改变谁是主表）

使用左外连接来查询

image.png

交叉连接

使用关键字cross join来操作

大致意思就是交叉匹配，比如a表有4条数据，b表有3条数据，结果就有12条数据

案例

image.png

sql99与sql92的区别

功能：sql99功能更多

可读性：sql99实现连接条件和筛选条件的分离，可读性高

10、子查询

含义：出现在其他语句中的select语句，成为子查询或者内查询。外部的查询语句称为主查询或外查询

分类

image.png

放在where和having之后

特点：

  • 子查询放在小括号内
  • 子查询一般放在条件后面
  • 标量子查询一般搭配着单行操作符使用：> < >= <=
  • 列子查询一般搭配多行操作符使用：in、any、some、all

标量子查询

结果集为一行一列 image.png

列子查询（多行子查询）

结果集只有一列多行 image.png 案例

image.png

放在select后

案例

image.png 注意：在select后写子查询，必须是标量子查询，即一行一列！

放在exists后面(相关子查询)

exists:判断是否存在

select exits(select XX from 表名);  判断括号内的查询是否有结果，若有，返回1；没有返回0.boolean类型

案例

image.png 注意：该查询的顺序与上述两种不同，是先执行主查询内容然后再执行子查询的内容。

还可以使用连接查询来实现

image.png

11、分页查询

应用场景：当现实的内容一页显示不全时，需要分页提交sql请求

语法，使用 'limit offset ,size;'

image.png

案例

image.png

12、联合查询

将多条查询语句的结果合并成一个结果

案例

image.png 注意：

  • 多次查询的结果的列数必须一致！
  • 字段名默认为第一条查询的字段，所以查询的每一列的类型和顺序最好一致！
  • union关键字查询结果默认去重，若不想去重，则改为 'union all'关键字

13、DML语言（数据操作语言）

对表中数据的管理

  • 插入：insert
  • 删除：delete
  • 修改：update

插入语句insert

语法一

image.png

案例，只要不是设置了非空的字段，便可以插入null

image.png

语法二

image.png

案例

image.png

注意：

  • 列名与值要相对应
  • 语法一支持多行插入
  • 语法一支持子查询

修改语句update

语法，修改单表的记录

image.png

案例

image.png

删除语句

单表删除语法

image.png

多表删除语法

image.png

image.png

truncate语句 清空表，不能加删选条件，效率较高

如果要删除表中的自增长列，delete删除后又添加数据从断点处开始，truncate删除后添加是从1开始

14、DDL数据定义语言

对库和表的管理

  • 创建：create
  • 修改：alter
  • 删除：drop

库的管理

1、库的创建

语法

image.png

注意：该执行语句只能执行一次，若不想报错，可以加上if not exists关键字

create database if not exists book  如果不存在book库，则创建，反之，则不创建

2、库的修改

目前没有相应的语句来进行库名的修改，可以去文件夹中直接改名字。

注意：库名的修改风险高，容易导致数据丢失。故而尽量不要有该操作！

库的字符集的修改

alert database 库名 character set 字符集；

3、库的删除

drop database 库名

重复执行该语句会报错，可以添加if exists来改善

表的管理

1、表的创建

语法

image.png

2、表的修改

修改列名 alter table 表名 change column 旧列名 新列名 类型;

修改列的类型或约束 alter table 表名 midify column 列名 新类型;

添加新列 alter table 表名 add column 新列名 类型;

删除列 alter table 表名 drop column 列名;

修改表名 alter table 表名 rename to 新表名;

3、表的删除

drop table if exists 表名;

4、表的复制

仅复制表的结构 create table 新表 like 旧表名;

复制表的结构和数据 create table 新表 select * from 旧表;

也可以使用类似的子查询来复制部分数据

15、常见约束

一种限制，用于限制表中的数据，为了保证表中数据的准确和可靠性

六大约束

  • NOT NULL ：非空，保证该字段的值不能为空。入id、学号、姓名等字段
  • DEFAULT ：默认，保证该字段有默认值
  • PRIMARY KEY ：主键，保证字段的值具有唯一性，并且非空
  • UNIQUE ： 唯一，用于保证字段的值唯一性，可以为空
  • CHECK ：检查约束【mysql中不支持】
  • FOREIGN KEY ：外键，用于限制两个表的关系，保证该字段的值必须来自于主表的关联列的值。在从表中添加外键约束，用于引用主表中某列的值

添加约束的时机

  • 创建表时
  • 修改表时

约束的添加级别

  • 表级约束：在各个字段下面添加。使用constraint 约束名 约束类型(字段名)来实现
  • 列级约束：直接在字段名后追加约束即可，支持默认、非空、主键、唯一

image.png

列级约束案例 (根据结果可以发现列级约束不支持检查约束和外键约束)

image.png

表级约束案例

image.png

主键和唯一(*)

  • 主键primary key：保证唯一性；不能为NULL；一个表中至多有1个主键
  • 唯一unique：保证唯一性；可以为NULL，但只能存在一个空；一个表中可以有多个唯一约束

image.png

外键

  • 要求在从表设置外键关系
  • 从表外键列的类型和主表的关联列的类型保持一致或兼容，名称无要求
  • 要求主表的关联列必须是key（一般是主键、唯一）
  • 要求插入数据时，先插入主表的数据，在插入从表
  • 删除数据时，先删从表，再删主表

添加外键通用的写法(该表为从表)

image.png

修改表时添加约束

案例，修改表时添加非空约束

image.png

案例，修改表时添加主键

image.png

案例，修改表时添加外键

image.png

16、标识列（自增长列）

又称为自增长列。可以不用手动的插入值，系统提供默认的序列值

注意：

  • 标识列必须是一个key（一般是唯一、主键）
  • 一个表至多有一个标识列
  • 标识列的类型只能是数值型

创建表时设置标识列

案例，通过auto_increment来实现

image.png

auto_increment_increment表示偏移量，一次添加多少 | auto_increment_offset表示起始值

image.png

修改表时设置标识列

案例

image.png

17、TCL事务控制语言

transaction controller language事务控制语言

事务：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

事务的ACID属性（*）

  • 原子性：指事务是一个不可分割的单位，事务中的操作要么都发生要么都不发生
  • 一致性：事务必须使数据库从一个一致性状态变换到另一个一致性状态
  • 隔离性：指一个事务执行不能被其他事务干扰。即一个事务内部的操作以及使用的数据对并发的事务是隔离的，并发执行的各个事务之间不能相互干扰
  • 持久性：指事务一旦提交，那么对数据库中的数据的改变是永久性的，接下来的操作或者数据库故障不应该对其有任何影响

事务的创建

隐式的事务 事务没有明显的开启和结束的标志。入insert、delete、update语句 显示事务 事务具有明显的开启和结束标记，前提是先将自动提交(autocommit)设置为关闭状态

步骤一：开启事务
set autocommit = 0;  禁用自动提交
步骤二：编写sqk语句（select,insert,update,delete）
步骤三：结束事务
commit:提交事务
rollback:回滚事务

多个事务同时运行参生的并发问题

对于同时运行多个事务时，当这些事务访问数据库当中相同的数据时，如果没有采取必要的隔离机制，就会导致以下并发问题：

脏读

对于两个事务A、B，此时，B更新了数据但并未提交，A却将该数据读取，若B事务进行回滚，那么A读取的内容就是临时并且无效的。

不可重复读

对于两个事务A、B，A读取了一个字段，B将该字段更新，A又读取该字段时，值就不同了。(两次查询的结果不一致)

幻读

对于两个事务A、B，A从表中读取一个字段，此时B在该表中插入了几行数据之后，A再次读同一个表，就会多出几行.

MySQL四个隔离级别

  • read uncommitted（读未提交数据）：允许事务读取未提交的数据。脏读、不可重复读、幻读都可能会出现
  • read commited（读已提交数据）：允许事务读取其他事务已经提交变更的数据。可以避免脏读，不可重复读和幻读还是可能出现
  • repeatable read（可重复读）：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对该字段更新，避免脏读和不可重复读，但幻读仍可能出现（mysql默认隔离级别）
  • serializable（串行化）：确保一个事务从一个表中读取相同的行，在这个事务运行期间，禁止其他事务对该表进行插入操作。所有问题都可以避免，但性能低

18、视图

虚拟表，和普通表一样使用。临时创建一个（复杂）表，便于下次使用时调用

mysql5.1之后出现的新特性，是通过表动态生成的数据

特点：临时性

视图的创建

语法

image.png

案例

image.png

视图的删除

语法

image.png

19、存储过程和函数（PROCEDURE）

） 存储过程和函数：类似于java中的方法

  • 提高代码重用性
  • 简化操作
  • 减少了编译次数，减少于数据库服务器连接的次数，提高了效率

存储过程

一组预先编译好的sql语句的集合

语法

image.png

参数列表包含三部分

参数模式+参数名+参数类型（例：IN 名字 VARCAHR(20)）

参数模式：

  • IN ：该参数可以作为输入，也就是该参数需要调用方传入值
  • OUT ：该参数可以作为输出，也就是该参数可以作为返回值
  • INOUT ：该参数既可以作为输入也可以作为输出，既可以作为传入值，也可以作为返回值

BEGIN END

  • 如果存储过程体仅仅只有一句话，BEGIN AND 便可以省略
  • 存储过程体的每一句sql语句结尾都需要加分号
  • 使用DELIMITER来定义结束标记

调用语法

语法

image.png

案例

image.png

OUT案例

image.png

综合案例

image.png

### 函数（FUNCTION）

区别：

image.png

语法

image.png

  • 参数列表包含两部分：参数名+参数类型
  • 函数体必须有return语句，没有就会报错
  • 函数只有一句话时，可以省略begin end
  • 使用delimiter来定义结束标记

调用语句

image.png

案例，无参数

image.png

案例，有参数

image.png

avatar
文章
阅读
粉丝