【数据库基础】SQL语法基础

2021-10-28 02:21:50

DQL 数据查询语言

基础查询

语法：
SELECT 要查询的东西
FROM 表名;
 
类似于Java中 :System.out.println(要打印的东西);
特点：

  • 通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
  • 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

条件查询

条件查询：根据条件过滤原始表的数据，查询到想要的数据
 
语法：
SELECT
要查询的字段|表达式|常量值|函数
FROM
表
WHERE
条件 ;
 
分类：
一、条件表达式
示例：salary>10000
条件运算符：
< >= <= = != <>
 
二、逻辑表达式
示例：salary>10000 && salary<20000

逻辑运算符：

  • and（&&）:两个条件如果同时成立，结果为true，否则为false
  • or(||)：两个条件只要有一个成立，结果为true，否则为false
  • not(!)：如果条件成立，则not后为false，否则为true
     

三、模糊查询
示例：last_name like ‘a%’

排序查询

语法：
SELECT
要查询的东西
FROM
表
WHERE
条件
ORDER BY 排序的字段|表达式|函数|别名 【asc | desc】;

分组查询

用于对查询的结果进行分组统计
 
语法
SELECT
查询的字段，分组函数
FROM
表
GROUP BY 分组的字段;
 
特点：
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选

  • 针对的表 位置 关键字
  • 分组前筛选： 原始表 group by的前面 where
  • 分组后筛选： 分组后的结果集 group by的后面 having

4、可以按多个字段分组，字段之间用逗号隔开
5、可以支持排序
6、having后可以支持别名

准备测试数据

部门表 dept

字段名称 数据类型 是否为空 备注
deptno int 部门编号，PK主键
dname varchar(20) Y 部门名称
loc varchar(13) Y 部门所在地点 
#部门表
CREATE TABLE dept(
depton INT PRIMARY KEY AUTO_INCREMENT,#设置主键，自动递增
dname VARCHAR(20),
loc VARCHAR(13),
);

INSERT INTO dept VALUES(NULL,'accounting','一区');
INSERT INTO dept VALUES(NULL,'research','二区');
INSERT INTO dept VALUES(NULL,'operations','二区');

【数据库基础】SQL语法基础

员工表 emp

字段名称 数据类型 是否为空 备注
empno int 员工编号，PK主键
ename varchar(10) Y 员工名称
job varchar(10) Y 职位
mgr int Y 上级编号
hiredate datetime Y 入职时间
sal double Y 月工资
comm NUMERIC(8,2) Y 奖金
deptno int Y 所属部门,FK外键 
CREATE TABLE emp(
empno int primary key auto_increment,
ename VARCHAR(10),
job VARCHAR(10),
mgr int,
hiredate DATE,
sal double,
comm NUMERIC(7,2),#奖金
deptno int
);

INSERT INTO emp VALUES(100,'jack','副总',NULL,'2002-05-1',90000,NULL,1);
INSERT INTO emp VALUES(200,'tony','总监',100,'2015-02-02',10000,2000,2);
INSERT INTO emp VALUES(300,'hana','经理',200,'2017-02-02',8000,1000,2);
INSERT INTO emp VALUES(400,'leo','员工',300,'2019-02-22',3000,200.12,2);
INSERT INTO emp VALUES(500,'liu','员工',300,'2019-03-19',3500,200.58,2);

【数据库基础】SQL语法基础

单行函数

字符函数

1）lower

lower–数据转小写

SELECT LOWER(字段名) FROM 表名;
 
SELECT dname,LOWER(dname) FROM dept;

【数据库基础】SQL语法基础

2）upper

upper–数据转大写

SELECT UPPER(字段名) FROM 表名; 

SELECT dname,UPPER(dname) FROM dept;

【数据库基础】SQL语法基础

3）length

length–数据的长度（底层用了UTF-8字符集，一个字母或数字占1字节，一个汉字占3字节）

SELECT LENGTH(字段名) FROM 表名;
SELECT dname,LENGTH(dname),loc,LENGTH(loc) FROM dept;

【数据库基础】SQL语法基础

4）substr

substr–截取子串，[num1，num2]，从num1到num2截取数据，数据下标从1开始

SELECT SUBSTR(字段名,num1,num2) FROM 表名; 

SELECT dname,SUBSTR(dname,1,3) FROM dept;#从第一个字符开始截取，截取出来3个字符
SELECT dname,SUBSTR(dname,2) FROM dept;#从第二个字符开始都截取完
SELECT dname,SUBSTR(dname,2,5) FROM dept;#从第二个字符开始，截取出来5个字符

SELECT dname,SUBSTR(dname,1,3),SUBSTR(dname,2),SUBSTR(dname,2,5) FROM dept;

【数据库基础】SQL语法基础

concat拼接
trim去前后指定的空格和字符
ltrim去左边空格
rtrim去右边空格
replace替换
lpad左填充
rpad右填充
instr返回子串第一次出现的索引

数学函数

round 四舍五入
rand 随机数
floor向下取整
ceil向上取整
mod取余
truncate截断

日期函数

now当前系统日期+时间
curdate当前系统日期
curtime当前系统时间
str_to_date 将字符转换成日期
date_format将日期转换成字符

流程控制函数

if 处理双分支
case语句 处理多分支
情况1：处理等值判断
情况2：处理条件判断

其他函数

version版本
database当前库
user当前连接用户

5）

6）

7）

8）

9）

分组函数

sum 求和
max 最大值
min 最小值
avg 平均值
count 计数

特点：
1、以上五个分组函数都忽略null值，除了count(*)
2、sum和avg一般用于处理数值型
    max、min、count可以处理任何数据类型
3、都可以搭配distinct使用，用于统计去重后的结果
4、count的参数可以支持：
    字段、*、常量值，一般放1

   建议使用 count(*)

事务 transaction

什么是事务

数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。

下面以银行转账为例，A转100块到B的账户，这至少需要两条SQL语句：

  • 给A的账户减去100元；
    update 账户表 set money=money**-100** where name=‘A’;

  • 给B的账户加上100元。
    update 账户表 set money=money**+100** where name=‘B’;

如果在第一条SQL语句执行成功后，在执行第二条SQL语句之前，程序被中断了（可能是抛出了某个异常，也可能是其他什么原因），那么B的账户没有加上100元，而A却减去了100元，在现实生活中这肯定是不允许的。

如果在转账过程中加入事务，则整个转账过程中执行的所有SQL语句会在一个事务中，而事务中的所有操作，要么全都成功，要么全都失败，不可能存在成功一半的情况。

也就是说给A的账户减去100元如果成功了，那么给B的账户加上100元的操作也必须是成功的；否则，给A减去100元以及给B加上100元都是失败的。

事务的分类

  • 隐式事务，没有明显的开启和结束事务的标志

比如 insert、update、delete 语句本身就是一个事务

  • 显式事务，具有明显的开启和结束事务的标志

相关步骤：

  1. 开启事务 （取消自动提交事务的功能）
  2. 编写事务的一组逻辑操作单元（多条sql语句）
    insert
    update
    delete
  3. 提交事务或回滚事务

使用到的关键字

set autocommit=0;
start transaction;
commit;
rollback;
 
savepoint 断点
commit to 断点
rollback to 断点

事务4个特性ACID

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力（一个事务的执行不受另外一个事务的干扰），隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
  • 持久性：事务处理结束后，对数据的修改就是永久的，数据将持久化到本地,即便系统故障也不会丢失，除非其他事务对其进行修改。

隔离级别

事务隔离分为不同级别，包括：

  • 读未提交（Read uncommitted） 安全性最差,可能发生并发数据问题,性能最好
  • 读提交（read committed） Oracle默认的隔离级别
  • 可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般
  • 串行化（Serializable） 表级锁，读写都加锁，效率低下,安全性高,不能并发
    事务并发问题如何发生？
    当多个事务同时操作同一个数据库的相同数据时

事务的并发问题有哪些？

  • 脏读：一个事务读取到了另外一个事务未提交的数据
    不可重复读：同一个事务中，多次读取到的数据不一致
  • 幻读：一个事务读取数据时，另外一个事务进行更新，导致第一个事务读取到了没有更新的数据
     

如何避免事务的并发问题？
  通过设置事务的隔离级别

  1. READ UNCOMMITTED
  2. READ COMMITTED 可以避免脏读
  3. REPEATABLE READ 可以避免脏读、不可重复读和一部分幻读
  4. SERIALIZABLE可以避免脏读、不可重复读和幻读
     

设置隔离级别：
set session|global transaction isolation level 隔离级别名;
 
查看隔离级别：
select @@tx_isolation;

查询mysql的隔离级别

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。

  • 开启事务：start transaction；
  • 结束事务：commit（提交事务）或rollback（回滚事务）。

在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了！

SELECT @@tx_isolation;

Repeatable Read（可重读）

MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

上一篇：jdbc中PreparedStatement替换Statement实现CRUD操作


下一篇：Mysql-解释字段