Sql基础语法

1.1.相关概念

1.1.1.基本概念

DB（database）：存储数据的仓库，其中的数据是有组织有关联的

DBMS（database management system）数据库管理系统，管理DB的

SQL (structure query language) 结构化查询语言，专门与DB通信的语言，所有DBMS都支持；

1.1.2.数据表

数据库中有数据表，数据表由行和列组成，表中的每一列称为字段；每一列类似Java中的属性，每一行类似Java中的对象；

MySQL不区分字符和字符串，单引号和双引号类似；

1.1.3.数据库分类

存储位置的不同进行分类：

1.基于磁盘的存储，MySQL，Oracle，SQLServer

2.基于内存的存储，redis非常适合做缓存，

从数据间是否存在关系进行分类：

1. 关系型数据库：MySQL，Oracle，SQLServer
2. 非关系型数据库：redis，mongodb nosql(not only sql)

1.1.4.数据类型

1.整型

• tinyint(2) 等同于byte的取值范围 -128-127
• tinyint(1) 0 1 等同于java语言 boolean
• int(n) n： 查询的时候单元格宽度 int(11) int
• bigint(n): long id 时间:毫秒数

2.小数

• float(M,D),M称为精度，表示总共 的位数；D表达标度，表示小数的位数
• double(m,n):
• decimal(m,n): BigDecimal 金钱

3.字符型

• char(n): n: 可存储的字符个数 定长
• varchar(n): varchar(10)可变长类型，表示输入的字符最长是10个，存储时候是按实际长度进行存储
• text: 文本

4.日期型

• date: 年月日
• datetime: 年月日 时分秒
• timestamp: 年月日 时分秒 时间戳

1.2.SQL语句

结构化查询语言，sql语句不区分大小写

DDL:数据定义语言 create drop alter
DML： insert delete update
DQL： select
DCL：commit rollback

1.2.1.DDL语句

1.创建一个表 t_stu

CREATE TABLE t_stu(
	sid INT,
	sname VARCHAR(10),
	sgender CHAR(1),
	age INT,
	score FLOAT(4,2),
	birthday TIMESTAMP,
	createtime date
)

2.alter修改表结构

-- 新增一个字段
ALTER TABLE t_stu ADD updatetime DATE;
-- 删除一个字段
ALTER TABLE t_stu DROP updatetime;
-- 修改表字段的数据类型
ALTER TABLE t_stu MODIFY sgender VARCHAR(1);
-- 修改表名
ALTER TABLE t_stu RENAME to stu;

3.drop

-- 删除数据表/数据库的命令
DROP DATABASE test;

1.2.2. DML语句 insert delete update

1.insert 插入一行记录

-- 对所有字段依次赋值
insert INTO stu VALUES(1,'anne','m',16,6.6,'2020-10-22 11:33:20','2020-10-22');
-- 一次插入多条记录
insert INTO stu VALUES(2,'tom','m',16,6.6,'2020-10-22 11:33:20','2020-10-22'),(3,'jason','m',16,6.6,'2020-10-22 11:33:20','2020-10-22');
-- 指定字段插入
INSERT INTO stu(sid,sname) VALUES(2,'tom');

2.删除记录

-- 指定条件进行删除记录
DELETE FROM stu WHERE sid=2;

3.更新记录，多个字段之间使用逗号

-- 修改表记录内容
UPDATE stu SET age=19,score=99.99 WHERE sid=1;

1.2.3.DQL语句 select

0.基本语法

select selection_list /*要查询的字段，多个字段用逗号隔开*/
from table_list /*要查询的表名称*/
[where condition /*筛选记录的条件*/
 group by grouping_clounms /*对结果进行分组*/
 having condition /*对分组后的记录进行条件筛选*/
order by cloumns /*对结果进行排序*/
 limit  /*对记录总数进行限定*/]

• select后面可以是表中的字段，常量值，表达式，函数；查询的结果是一个虚拟的表格；

1.基础查询，查询所有的列

SELECT * FROM t_stu;
-- 指定字段进行查询
SELECT sid,sname FROM t_stu;

2.条件查询

• 按条件表达式筛选，> < = != <> >= <=
• 逻辑表达式筛选 && || ！ and ,or,not
• 模糊查询 like，between and（not between and） ,in(列表中的值不支持通配符), is null(is not null)
  • null使用is

-- 指定某一个条件进行查询
SELECT * FROM t_stu WHERE courseid=2 
-- AND 是两个条件都要满足
SELECT * FROM t_stu WHERE courseid=2 AND score>60;
-- OR是条件只要满足一个就行
SELECT * FROM t_stu WHERE courseid=2 OR score>60;
-- 查询sid为 1，6，8的记录，属于某个集合
SELECT * FROM t_stu WHERE sid IN(1,6,8);
-- 查询sid不属于 1，6，8的记录
SELECT * FROM t_stu WHERE sid  NOT IN(1,6,8);
-- 查询记录某个字段为null
SELECT * FROM t_stu WHERE sgender is null;
--  查询成绩在70到90区间范围内的记录
SELECT * FROM t_stu WHERE score BETWEEN 70 AND 90;
-- 性别非男的记录
select * FROM t_stu WHERE sgender <> 'm';
select * FROM t_stu WHERE sgender != 'm';

3.模糊查询

%：表示0或任意多个字符

_:任意一个字符

-- 名字由3个字母组成
SELECT * FROM t_stu WHERE sname LIKE '___';
-- 名字由j 开头
SELECT * FROM t_stu WHERE sname LIKE 'j%';
-- 第二个字母为a 的学生记录
SELECT * FROM t_stu WHERE sname LIKE '_a%';
-- 查询姓名中包含字母a的记录
SELECT * FROM t_stu WHERE sname LIKE '%a%';

4.字段控制查询&别名

• 表或者字段还可以起别名，起别名是便于理解，如果查询的字段有重名情况使用别名可以区分，AS可省略 使用空格

--  查询学生表中的所有性别
SELECT DISTINCT sgender FROM t_stu ;
-- 给查询出的字段起别名 
SELECT sid a,sname b,sgender gender,score c FROM t_stu;

5.排序 order by子句可以跟单个字段，多个字段，表达式，函数，别名

-- 查询所有学生记录，按成绩进行降序排序
SELECT * FROM t_stu ORDER BY score DESC;
-- 查询所有学生记录，首先先按成绩进行降序排序，如果成绩相同，按名字进行升序排序
SELECT * FROM t_stu ORDER BY score DESC, sname ASC;

6.组函数/聚合函数/分组函数

用作统计使用，又称为聚合函数或者统计函数或者组函数

• 聚合函数是用来做纵向运算的函数：
• COUNT()：统计指定列不为NULL的记录行数；一般使用count（*）统计行数
• MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
• lMIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
• SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
• AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为

sum,avg一般处理数值型,

max，min,count可以处理任意数据类型

分组函数都忽略了null值，可以和distinct搭配使用

**注意点：**组函数可以出现多个，但是不能嵌套；如果没有group by 子句，结果集中所有行数作为一组

-- 查询emp表中有佣金的人数,统计指定列不为NULL的记录行数
SELECT count(comm) a FROM emp;
-- 查询emp表中记录数
SELECT count(1) a FROM emp;
-- 查询emp表中月薪大于2500的人数
SELECT COUNT(1) FROM emp WHERE sal>2500;
-- 	统计月薪与佣金之和大于2500元的人数：
SELECT COUNT(1) FROM emp WHERE sal+IFNULL(comm,0)>2500;
-- 	查询有佣金的人数，以及有领导的人数：
SELECT count(comm) a FROM emp WHERE mgr IS NOT NULL;
-- 	查询所有雇员月薪和
SELECT sum(sal) FROM emp;
-- 	雇员月薪+佣金和
SELECT sum(sal)+SUM(IFNULL(comm,0)) allSum FROM emp;
-- 	统计所有员工平均工资
SELECT avg(sal) FROM emp;
-- 	查询最高工资和最低工资
SELECT max(sal),min(sal) FROM emp;

7.group by分组查询

和分组函数一同查询出来的字段要求是group by后的字段

group by后面可以跟聚合函数 可以起别名

-- 	查询每个部门的部门编号和每个部门的工资和
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;
-- 	查询每个部门的部门编号以及每个部门的人数
SELECT deptno,SUM(sal),COUNT(1) FROM emp GROUP BY deptno;
-- 	查询每个部门的部门编号以及每个部门员工工资大于1500的人数：
SELECT deptno,COUNT(1) FROM emp WHERE sal>1500 GROUP BY deptno;

按多个字段分组，后面字段一致的为一组

-- 按job进行分类
SELECT COUNT(*),job,mgr FROM emp GROUP BY job;
-- 按job和mgr进行分类
SELECT COUNT(*),job,mgr FROM emp GROUP BY job,mgr;

8.having子句

where是对分组前进行过滤；having是对分组后进行过滤

where中不能出现分组/聚合函数，having中可以出现

where是比分组先执行的，having是在分组之后执行的；

having后面可以跟别名

--	查询部门工资总和大于9000的部门编号以及部门工资和
SELECT deptno,SUM(sal) allsum FROM emp WHERE sal>1500 GROUP BY deptno HAVING SUM(sal)>9000 ORDER BY allsum;

-- 查询部门员工个数大于3的
SELECT COUNT(*) count,deptno FROM emp GROUP BY deptno HAVING count>3;

9.limit

-- 第一位表示起始位置，第二位表示总的长度；分页
SELECT * FROM emp LIMIT 1,5;
SELECT * FROM emp LIMIT 5; 等价与 SELECT * FROM emp LIMIT 0, 5;

10.关联查询

内连接

• 多表等值连接的结果是多表的交集部分，N表连接，至少需要N-1个连接条件，没有顺序要求，一般起别名
• 非等值连接，只要不是等号连接的都是非等值连接

外连接，有主表有从表，主表肯定会显示完整的内容

• 左外连接，以左表为主
• 右外连接，以右表为主

-- 查询员工信息，要求显示员工号，姓名，月薪，部门名称
-- 笛卡尔积 （a, b） (1,2,3) --(a,1) (a,2) (a,3) (b,1) (b,2) (b,3)--》会生成一个中间表
-- 多表查询，关联条件使用的是等号
SELECT empno,ename,sal, e.deptno, dname FROM emp e,dept d WHERE e.deptno=d.deptno; 

-- 内连接
SELECT empno,ename,sal, e.deptno, dname  FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
-- 左连接
SELECT e.*,d.*  FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;
-- 右外连接
SELECT e.*,d.*  FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;
-- 查询员工信息，要求显示：员工号，姓名，月薪，薪水的级别
SELECT empno,ename,sal,GRADE FROM emp e,salgrade sa WHERE e.sal BETWEEN sa.LowSAL AND sa.HISAL; 

11.自连接，通过别名，将同一张表视为多张表；同一张表中某个字段要去关联另外一个字段

-- 查询员工姓名和员工的老板的名称
SELECT e1.empno,e1.ename,e2.empno,e2.ename  FROM emp e1, emp e2 WHERE e1.mgr=e2.empno;

12.子查询

-- 查询工资为20号部门平均工资的员工信息
SELECT AVG(sal) FROM emp WHERE deptno=20;
SELECT * FROM emp WHERE sal >(SELECT AVG(sal) FROM emp WHERE deptno=20);

1.3.约束

1.非空约束，一定要给值才能插入

CREATE TABLE a(
	id INT NOT NULL,
	sanme VARCHAR(20)
)
    
  提示错误：Field 'id' doesn't have a default value

2.唯一性约束，值不能重复

CREATE TABLE b(
	id INT not NULL UNIQUE
	)

3.默认约束，给一个默认值

CREATE TABLE c(
	id INT not NULL DEFAULT 6,
	sname VARCHAR(3)
	)

4.主键约束，主键列自动增长，无需赋值，表格必须要有一个主键

CREATE TABLE t_stu(
-- 主键自增
	sid INT PRIMARY KEY auto_increment,
	sname VARCHAR(10) NOT NULL,
	sgender char(1),
	score FLOAT(4,1) NOT NULL,
	birthday TIMESTAMP,
	sutid VARCHAR(30) UNIQUE
	)

5.外键约束

外键代表着另外一张表的主键，外键的值必须从另外一张表的主键中进行选择

ALTER TABLE t_stu add CONSTRAINT fk_cid FOREIGN KEY(courseid) REFERENCES course(id);

标志列：自增长列

一个表只能有一个标志列

SET auto_increment_increment=2;
-- 查看标志列起始和步长
SHOW VARIABLES LIKE '%auto_increment%';

1.4.常用函数

3，MySQL中的+就只有运算符的功能；会试图将字符型数值转换为数值型再继续操作，转换失败则转为0；若其中有null则结果为null；字符串可以使用concat函数拼接；

1.4.1.字符函数：

​ length(str)得到的是字节个数 utf8中中文是3个字节

​ concat() 拼接字符串

​ upper /lower（str）

​ substr,substring 截取字串 MySQL的索引是从1开始的，截取的是字符长度

​ instr(str,substr)返回字串第一次出现的索引，找不到则为0

​ trim（）

​ replace 替换全部符合的

SELECT REPLACE('javalovejava','java','sql');
-- SQL中的索引是从1开始的，包含
SELECT SUBSTR('hellosql',6);
-- 从哪里开始，以及长度是多少
SELECT SUBSTRING('hellojava',6,2);
-- 字符拼接
SELECT CONCAT('aa','_','bb','-','cc');
-- 获取字节个数
SELECT LENGTH('中');
SELECT IFNULL(NULL,0);

1.4.2.数学函数：

• round（）四舍五入
• ceil（）向上取整
• floor（）向下取整
• truncate（）截断
• mod()取余

    -- 指定小数点位数
    SELECT TRUNCATE(2.3666,2);
    SELECT ROUND(2.5);
    -- 3表示小数的位数是3位
    SELECT ROUND(2.5,3);
    -- 取余
    SELECT MOD(10,3);

1.4.3.日期函数

• now()返回日期+时间
• curdate()返回系统日期，布包含时间
• curtime() 返回当前时间，不包含日期
• year（）年 获取指定的年 month() monthname()
• str_to_date 将字符通过指定的格式转换为日期
• ate_format将日期转换为字符
• datediff:返回两个日期相差天数
  ​

    -- 常见的日期函数
    SELECT YEAR(NOW()),MONTH(DATE('2020-10-11')),DAY(NOW()),SECOND(NOW());
    -- 日期格式的转换
    SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
    SELECT STR_TO_DATE('2020-10-11','%Y-%m-%d');

1.4.4.其他函数

• version（） 版本号
• user（）当前用户
• if（exp1,exp2,exp3）如果exp1为true，取exp2的值 否则取exp3的值

    SELECT VERSION();
    SELECT USER();
    SELECT DATABASE();

    SELECT IF(10>2,'10','2') a;

1.5.数据库引擎

存储引擎：在MySQL中的数据使用不同的存储技术存储在文件或内存中

-- 查看MySQL中所用的存储引擎
show engines;

innodb是支持事务的，

而myisam，memory不支持事务

1.6.数据库事务

1.6.1.什么是事务

• Transaction
• 事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)
• 一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成
• 事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同

1.6.2.事务四大特性ACID

• 原子性(Atomicity):事务不可分割的最小工作单元，事务内的操作要么全做，要么全不做。事务具有要么全部成功 要么全部失败 这就是原子性。
• 一致性(Consistency):在事务执行前数据库的数据处于正确的状态，需事务执行完后数据库的数据依然处于正确的状态，即数据完整性约束没有被破坏，如A给B转帐，不论转帐是否成功，转帐之后的A和B的帐户总额和转帐之前是相同的 4000 3000
• 隔离性(Isolation):当多个事务处于并发访问同一个数据库资源时，事务之间相互影响，不同的隔离级别决定了各个事务对数据资源访问的不同行为
• 持久性(Durability):事务一旦执行成功，它对数据库的数据的改变是不可逆的

​ 在业务逻辑层，一个service层可能需要执行一次或多次增删改操作，如果这期间发生了异常，数据库事务不回滚的话 数据库中的数据就会不完整，举个例子，订单信息中包含订单明细，现在在保存订单的业务逻辑方法中，先保存订单成功了，再保存订单明细，如果保存订单明细的过程中失败了，肯定希望之前的保存订单数据回滚。

1.6.3.和事务相关的语句

• 开启事务 start transaction
• commit:提交
• rollback：回滚

1.6.4.事务何时开启何时结束

开始标志：任何一条DML语句(insert、update、delete)执行，标志事务的开启

结束标志：

• 提交：成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步
• 回滚：失败的结束，将所有的DML语句操作历史记录全部清空

1.6.5.事务和底层数据库的关系

​ 在事务进行过程中，未结束之前，DML语句是不会更改底层数据，只是将历史操作记录一下，在内存中完成记录。只有在事务结束的时候，而且是成功的结束的时候，才会修改底层硬盘文件中的数据。如果执行失败，则清空所有的历史操作记录，不会对底层硬盘文件中数据做任何修改。

-- 开启事务
START TRANSACTION;
INSERT into tb_stu(sid,sname) VALUES(5,'jerry'); 
-- 此命令执行会出错
INSERT into tb_stu(sid,sname) VALUES(6,'tom','aa'); 
-- 提交 出错之后会rollback
COMMIT;

1.6.6.在MySQL中事务的提交和回滚

​ 在MySQL中，默认情况下，事务是自动提交的，也就是说，只要执行一条DML语句就开启了事务，并且提交了事务。自动提交机制是可以关闭的，可以由程序控制何时提交。

1.6.7.事务的隔离级别

不同事务之间具有隔离性，隔离级别分四个：

• 读未提交：read uncommitted（读到未提交的数据）
  • 事务A和事务B，事物A未提交的数据，事物B可以读取到
  • 这里读取到的数据叫做“脏数据”（已经被改动数据）
  • 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别
• 读已提交：read committed（读到已提交的数据）
  • 事物A和事物B，事物A提交的数据，事物B才能读取到
  • 这种隔离级别高于读未提交
  • 这种级别可以避免“脏数据”
  • 这种隔离级别会导致“不可重复读取”（事务B两次读取到的数据不一致）
  • 这是Oracle默认隔离级别
• 可重复读：repeatable read
  • 事务A和事务B，事务A提交之后的数据，事务B读取不到
  • 事务B是可重复读取数据
  • 这种隔离级别高于读已提交
  • 换句话说，对方提交之后的数据，我还是读取不到
  • 这种隔离级别可以避免“不可重复读取”，达到可重复读取
  • 这是MySQL默认级别
  • 虽然可以达到可重复读取，但是会导致“幻读”（A把所有数据都清空了，B在这个时候修改了其中一条数据，当A结束后发现还存在一条数据，就好像产生了幻觉一样，这就是幻读）
• 串行化：serializable
  • 事务A和事务B，事务A在操作数据库时，事务B只能排队等待
  • 这种隔离级别很少使用，吞吐量太低，用户体验差（特别的慢）
  • 这种级别可以避免“幻读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

show variables like 'transaction_isolation';