[大数据]数据库 笔记

MySQL

注释格式：

? 单行注释 – 内容

? 多行注释 /* */

单双引号格式：

? 字符串、时间 单引号

? as（重命名）后单双都可以

大小写敏感：

? 语法大小写不敏感

? 字符串大小写不敏感（oracle字符串大小写敏感）

?

mysql 字符集的设置

1. 参考配置

   • 操作系统：win10
   • 数据库安装路径 ：C:\Program Files\MySQL\MySQL Server 5.6

2. 字符集的设置

   • 在cmd中进入Mysql，通过 show variables like '%char%' 语句，查看Mysql数据库默认的字符集

   • 在Win10系统中，打开my.ini 文件，其位置在 C:\ProgramData\MySQL\MySQL Server 5.6\my.ini
     （注：ProgramData 是C盘下的隐藏文件夹，记得显示隐藏的项目）

   • 修改配置文件
     在[client]下添加语句：
     default-character-set=utf8
     在[mysql]下添加语句：
     default-character-set=utf8
     在[mysqld]下添加语句：
     init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

   • 保存配置文件

     保存修改后的my.ini 文件，若发现没有修改 my.ini 文件的权限，需要对该文件的属性做如下设置

     

3. 重启服务器

   以管理员身份启动cmd，先执行net stop mysql 命令，然后执行net start mysql 命令

数据类型

• 数据库类型：

  1. int，整数类型 。age int
  2. double，小数类型。score double（5，2）
  3. date：日期，只包含年月日。'yyyy-MM-dd'
  4. datetime：日期，包含年月日时分秒。'yyyy-MM-dd HH:mm:ss'
  5. timestamp：时间戳类型 包含年月日时分秒 'yyyy-MM-dd HH:mm:ss'
     • 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
  6. varchar，字符串。name varchar（20)：姓名最大20个字符。

• 创建表实例

  ? create table student(

  ? id int,

  ? name varchar(32),

  ? age int,

  ? score double(4,1),

  ? birthday date,

  ? insert_time timestamp

  ? )；

SQL语句

SQL 分类

• DDL(Data Definition Language)数据定义语言。用来定义数据库对象：数据库，表，列等。关键字：create,drop,alter等
• DML(Data Manipulation Language)数据操作语言。用来对数据库中表的数据进行增删改。关键字：insert,delete,update等
• DQL(Data Query Language)数据查询语言。用来查询数据库中表的记录（数据）。关键字：select,where等
• DCL(Data Control Language)数据控制语言。用来定义数据库的访问权限和安全级别，及创建用户。关键字：grant,revoke等。

DDL：操作数据库、表

• 操作数据库：CRUD

  • C（Create）：创建
    • 创建db4数据库，判断是否存在，并指定字符集为gbk: create database if not exists 数据库名称 character set gbk;
  • R（Retrieve）：查询
    • 查询所有数据库的名称：show databases;
    • 查询某个数据库的字符集：show create database 数据库名称；`
  • U（Update）：修改
    • 修改数据库的字符集：alter database 数据库名称 character set 字符集名称；
  • D（Delete）：删除
    • drop database 数据库名称；
    • 判断数据库存在，存在再删除：drop database if exists 数据库名称；
  • 使用数据库
    • 查询当前正在使用的数据库名称：select database（）；
    • 使用数据库：use 数据库名称；

• 操作表

  • C（Create）：创建

    • 创建表：create table 表名（

      ? 列名1 数据类型1，

      ? 列名2 数据类型2，

      ? ·····

      ? 列名n 数据类型n

      ? ）；

    • 复制表（只复制字段头）：create table 表名 like 被复制的表名；

    • 复制表（完全复制）：create table 表名 select * from 被复制的表名（或者是查询内容）;

    • 默认时间

      • DEFAULT CURRENT_TIMESTAMP 表示当插入数据的时候，该字段默认值为当前时间
      • ON UPDATE CURRENT_TIMESTAMP表示每次更新这条数据的时候，该字段都会更新成当前时间

  • R（Retrieve）：查询

  • 查询某个数据库中所有的表名称：show tables;

• 查询表结构：desc 表名；

• U（Update）：修改

  • 修改表名：alter table 表名 rename to 新的表名；

    ? rename table 表名 to 新的表名；

  • 修改表的字符集：alter table 表名 character set 字符集名称；

  • 添加一列：alter table 表名 add 列名 数据类型；

  • 删除一列：alter table 表名 drop 列名；

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

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

  • D（Delete）：删除

    • drop table 表名；
    • drop table if exists 表名；

DML:增删改表中数据

• 添加数据
  • 语法格式：insert into 表名 （列名1，列名2，...列名n） values （值1，值2，...值n）；
  • 注意事项
    • insert into 表名 values（值1，值2，...值n）。默认给所有列添加值
    • 除了数字类型，其他类型需要使用引号（单双都可以）引起来
    • 复制（目标表已存在）
      • 表2复制到表1：insert into 表名1 select * from 表名2；
      • 表2部分复制到表1：insert into 表名1（列1，列2） select 列1，列2 from 表名2；
    • 复制（目标表不存在）
      • CREATE TABLE s SELECT * FROM student;
• 删除数据
  • 语法格式：delete from 表名 [where 条件]
  • 注意事项
    • 如果不加条件则删除表中所有记录
    • 删除所有记录方法
      • delete from 表名；--不推荐。有多少条记录就会执行多少次删除操作
      • truncate table 表名；--推荐。先删除表，在创建一张表头一样的表
• 修改数据
  • 语法格式：update 表名 set 列名1 = 值1，列名2 = 值2，...[where 条件]；
  • 注意事项
    • 如果不加任何条件，则会将表中所有记录全部修改。

DQL：查询表中的记录

• 语法格式：select * from 表名；

  • select

    ? 字段列表

    from

    ? 表名列表

    where

    ? 条件列表

    group by

    ? 分组字段

    having

    ? 分组之后的条件

    order by

    ? 排序

    limit

    ? 分页限定

• 基础查询

  • 多个字段的查询：select 字段名1，字段名2...from 表名；
  • 去除重复：select distinct 字段名1，字段名2...from 表名；
  • 计算列：select count（字段名） from 表名；
    • 注意：ifnull（表达式1，表达式2）：null 参与的运算，计算结果都为null
      • 表达式1：那个字段需要判断是否为null
      • 表达式2：如果表达式1为null的替换值
  • 起别名：关键字as，as也可以省略用空格代替

• 条件查询

  • 运算符

    • >、<、<=、>=、=、<>

    • BETWEEN ...AND -- 闭合 事例1 select * from student where age between 20 and 30;

      ? 事例2 select * from student where age not between 20 and 30;

      ? 事例3 时间规则 BETWEEN '1999-08-10 22:19:11' AND '1999-08-10 22:19:12';

    • IN(集合) where age in(11,2,33);

    • LIKE：模糊查询

      • 占位符：
        • _：单个任意字符
        • %：多个任意字符。LIKE ‘%张三%’

    • IS -- null 需要用is来判断。is null is not null

    • AND 或 &&

    • OR 或 ||（oracle中的|| 表示 字符串连接符）

    • NOT 或 !

• 排序查询

  • 语法格式：select * from 表名 order by 排序字段1 排序方式1，排序字段2 排序方式2...
  • 排序方式：ASC，升序，默认；DESC：降序
    • 注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。
    • 排序时null最小（oracle中排序时null值最大）

• 聚合函数：将一列数据作为一个整体，进行纵向的计算。

  • count：计算个数 事例：select count(age) from student ;
    • 一般选择非空的列：主键
    • count（*）
  • max：计算最大值
  • min：计算最小值
  • sum：计算和
  • avg：计算平均值
  • std：标准差
  • timestampdiff：计算时间戳数据差。TIMESTAMPDIFF(SECOND，timestamp 1, timestamp 2)

• 分组查询（分为几种）：语法格式 ：group by 分组字段；

  事例：select sex, avg(math),count(id) from where math>70 group by sex having count(id) > 2;

  注意：

  ? group by 只会返回分组后查询的一条数据。

• 分页查询：语法格式：limit 开始的索引，每页查询的条数； — 索引由0开始

  ? — (page -1) * pagesize

  ? limit 每页查询的条数；

• 集合查询：union用于把两个或者多个select查询的结果集合并成一个

  SELECT ...
  
  UNION [ALL | DISTINCT]
  
  SELECT ...
  

  默认情况下，UNION = UNION DISTINCT – 去重

  ? UNION ALL不去掉结果集中重复的行

  ? 在去重操作时，如果列值中包含NULL值，认为它们是相等的

  mysql> select * from t1;
  +------+------+
  | num  | addr |
  +------+------+
  |  123 | abc  |
  |  321 | cba  |
  +------+------+
  2 rows in set (0.00 sec)
  
  mysql> select * from t2;
  +------+------+
  | id   | name |
  +------+------+
  |    1 | a    |
  |    2 | A    |
  +------+------+
  2 rows in set (0.00 sec)
  
  mysql> select * from t1
      -> union
      -> select * from t2;
  +------+------+
  | num  | addr |
  +------+------+
  |  123 | abc  |
  |  321 | cba  |
  |    1 | a    |
  |    2 | A    |
  +------+------+
  4 rows in set (0.00 sec)
  

DCL：管理用户，授权

需要先切换到mysql数据库下

1. 管理用户
	1. 添加用户：
		* 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
		
               CREATE USER 'root'@'%' IDENTIFIED BY 'root';
               CREATE USER 'root'@'localhost' IDENTIFIED BY 'root';
	2. 删除用户：
		* 语法：DROP USER '用户名'@'主机名';
    3. 修改用户密码：
        SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
        SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

        * mysql中忘记了root用户的密码？
            1. cmd(在搜索框中以管理员身份运行) -- > net stop mysql57 停止mysql服务      
            2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables（管理员身份）
            3. 打开新的cmd窗口（无需管理员身份）,直接输入mysql命令，敲回车。就可以登录成功
            4. use mysql;
            5. update user set authentication_string = password('root') where user = 'root';
            6. 关闭两个窗口
            7. 打开任务管理器，手动结束mysqld.exe 的进程（我的电脑是关闭窗口后自动消失了）
            8. 启动mysql服务（管理员身份）
            9. 使用新密码登录。

权限管理

1. 查询权限：
    -- 查询权限
    SHOW GRANTS FOR '用户名'@'主机名';
    SHOW GRANTS FOR 'lisi'@'%';

2. 授予权限：
    -- 授予权限
    grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
    -- 给张三用户授予所有权限，在任意数据库任意表上

    GRANT ALL ON *.* TO 'zhangsan'@'localhost';
3. 撤销权限：
    -- 撤销权限：
    revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
    REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

count(1)、count(*)与count(列名)的执行区别

• 执行效果上 ：
  count(*)包括了所有的列，相当于行数，在统计结果的时候， 不会忽略列值为NULL
  count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候， 不会忽略列值为NULL
  count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为NULL
• 执行效率上：
  列名为主键，count(列名)会比count(1)快
  列名不为主键，count(1)会比count(列名)快
  如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（）
  如果有主键，则 select count（主键）的执行效率是最优的
  如果表只有一个字段，则 select count（）最优。
• 总结
  • 有主键用count（主键），会忽略列值为NULL
  • 没主键用count（1），不会忽略列值为NULL

函数

group_concat – 将一列的数据连接成一个字符串

基本语法：group_concat([DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’])

• 结合group by 语法使用

DATE_FORMAT( 时间数据 ,'%Y-%m-%d %H:%i:%s') 格式化时间数据为字符串

类型转换函数

CAST(源转换类型 AS  目标转换类型)，CONVERT(源转换类型 ，目标转换类型)

类型：
  二进制,同带binary前缀的效果 : BINARY    
  字符型,可带参数 : CHAR  事例： CHAR 或 CHAR(10)     
  日期 : DATE     
  时间: TIME     
  日期时间型 : DATETIME     
  浮点数 : DECIMAL      
  整数 : SIGNED     
  无符号整数 : UNSIGNED

事例：
 SELECT CONVERT('23',SIGNED);   -- 字符串转为整数

SQL 事例

查询截止到当前时间新增的用户总数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HmO5fiCO-1656837880946)(图片/snipaste20220331_175733.PNG)]

-- 查询截止到当前时间新增的用户总数
SELECT
    reg_time,
    min_total AS '小计',
    @total := @total + min_total AS '总计' 
FROM
    ( SELECT date( reg_time ) AS reg_time, 
    sum( reg_count ) AS min_total FROM t_reg GROUP BY date( reg_time ) ) AS temp,
    ( SELECT @total := 0 ) AS T1 
ORDER BY
    reg_time;
-- 排序分组后取第一个（先排序，加distinct 字段固定表，最后分组）
  select * from (select distinct user_phone,crt_date  from yxcenter.cimp_cm_activity_user order by crt_date asc ) t   group by user_phone

SELECT @total := 0,这句的意思是给临时变量@total设置值为0;

mysql分组后合并显示一个字段的多条数据方式

group_concat – 将一列的数据连接成一个字符串

基本语法：group_concat([DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’])

• 结合group by 语法使用

select name,group_concat(hobby separator '，') hobbies from student_hobbt group by name;

约束

主键约束：primary key —主键有唯一属性

非空约束：not null

唯一约束：unique

外键约束：foreign key

• 非空约束：

  • CREATE TABLE student (

    id INT,

    NAME VARCHAR(20) NOT NULL,-- 创建表时添加非空约束

    age INT)

    -- 删除非空约束
ALTER TABLE student MODIFY NAME VARCHAR(20);

    -- 创建表后添加非空约束
    ``ALTER TABLE student MODIFY NAME VARCHAR(20) NOT NULL；`

• 唯一约束：

  • CREATE TABLE student(
id INT,
phone_number VARCHAR(20) UNIQUE-- 创建表时添加唯一约束
)

    -- 删除唯一约束
ALTER TABLE student DROP INDEX phone_number;

    -- 创建表后添加唯一约束
ALTER TABLE student MODIFY phone_number VARCHAR(20) UNIQUE;

    • 注意 unique 限定的值可以有多个null——null并不唯一

• 主键约束：

  • CREATE TABLE student(
id INT PRIMARY KEY,-- 创建表时添加主键约束
NAME VARCHAR(20)
)

    -- 删除主键约束
ALTER TABLE student DROP PRIMARY KEY;

    -- 创建表后添加主键约束
ALTER TABLE student MODIFY id INT PRIMARY KEY;

  • 自动增长：

    • CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,-- 创建表时自动增长
NAME VARCHAR(20)
)

      -- id 为null时，自动写入序号
INSERT INTO student VALUES(NULL,'王佳旭');

      -- 删除自动增长
ALTER TABLE student MODIFY id INT;

      -- 创建表后添加自动增长
ALTER TABLE student MODIFY id INT AUTO_INCREMENT;

• 外键约束：

  • CREATE TABLE employee(
id INT,
NAME VARCHAR(20),
department_id INT,
CONSTRAINT emp_depId_fk FOREIGN KEY (department_id) REFERENCES department(id)-- 创建表时创建外键约束
-- 外键约束格式 ：CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称)
);
INSERT INTO employee VALUES(1,'翟宇飞',1);
INSERT INTO employee VALUES(2,'李卓',2);

    -- 删除外键约束 ALTER TABLE employee DROP FOREIGN KEY 外键名称 ;
ALTER TABLE employee DROP FOREIGN KEY emp_depId_fk;

    -- 创建表后添加外键约束
ALTER TABLE employee ADD CONSTRAINT emp_depId_fk FOREIGN KEY (department_id) REFERENCES department(id);

  • 级联删除与更新（在设置外键约束时进行添加）

    • -- 级联更新（删除主键，外键为null）
      CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE
-- 级联删除
CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称) ON DELETE CASCADE

数据库的设计

实现关系

• 一对多（多对一的理解：多个用一个）

  • 实现方式：在多的一方建立外键，指向一的一方的主键

• 多对多

  • 实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键

  • 复合主键：PRIMARY KEY (外键名1，外键名2)

    事例：CREATE TABLE tab_favorite(

    ? rid INT,

    ? DATE DATETIME,

    ? uid INT,

    ? -- 创建复合主键

    ? PRIMARY KEY(rid,uid),

    ? FOREIGN KEY (rid) REFERENCES tab_route(rid),

    ? FOREIGN KEY (uid) REFERENCES tab_user(uid)

    ? )

• 一对多和多对多的区别：判断两个关系表存储字段的形式，可以存储一个信息的是一对多，能存储多个信息的是多对多

三大范式

• 第一范式：每一列都是不可分割的原子数据项
• 第二范式：在1NF的基础上，非码属性必须完全依赖码（在1NF基础上消除非主属性对主码的部分函数依赖）
  • 心得：必须先找到码
  • 码：如果一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性组）为该表的码
• 第三范式：在2NF基础上，任何非主属性不依赖于其他非主属性（在2NF基础上消除传递依赖）

数据库的备份和还原

• 命令行

  • 备份 ：（在cmd中）mysqldump -u用户名 -p密码 存储文件名称 > 存储文件路径

    ? 事例：mysqldump -uroot -proot db1 > D:\\a.sql

  • 还原：登录数据库—>创建数据库—>使用数据库—> source 还原文件路径

• 图形化界面（SQLyog）

  • 备份：右键数据库—>备份/导出—>备份数据库，转存为SQL
  • 还原：右键“root@localhost"—>执行SQL脚本

多表查询

内连接查询

• 隐式内连接

  		-- 查询员工表的名称，性别。部门表的名称
  		
  		-- 事例一
  			SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
  			
  		-- 事例二（正确格式）
  		SELECT 
  			t1.name, -- 员工表的姓名
  			t1.gender,-- 员工表的性别
  			t2.name -- 部门表的名称
  		FROM
  			emp t1,
  			dept t2
  		WHERE 
  			t1.`dept_id` = t2.`id`;
  

• 显式内连接

  语法：select 字段 from 表名1 [inner] join 表名2 on 条件；

外连接查询

• 左外连接

  语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；

  注意：内连接：显示左边右边共有的；左连接：左边有的，右边没有的为null

• 右外连接

  语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；

子查询

• 子查询的结果是单行单列

  				-- 查询员工工资小于平均工资的人
  				SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
  

• 子查询的结果是多行单列

  				-- 查询'财务部'和'市场部'所有的员工信息
  				SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
  

• 子查询的结果是多行多列

  				-- 子查询可以作为一张虚拟表参与查询
      			-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
      			SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
      			WHERE t1.id = t2.dept_id;
  
  
  

事务

概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

操作

• 开启事务：start transaction；
• 回滚：rollback；
• 提交：commit；
• mysql如果只开启事务没有回滚或者提交，则在关闭窗口后不会对源数据进行修改

事务提交方式

• 自动提交
  • mysql就是自动提交的
  • 一条DML（增删改)语句会自动提交一次事务
  • 如果开启事务（start transaction）则为手动提交
• 手动提交
  • oracle 数据库默认是手动提交事务
  • 需要先开启事务，再提交
• 修改事务的默认提交方式
  • 查看事务的默认提交方式：SELECT @@autocommit；-- 1代表自动提交 0代表手动提交
  • 修改默认提交方式：set @@autocommit = 0;修改后如果不写commit将会在关闭窗口后失效

事务的四大特征

• 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
• 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
• 隔离性：多个事务之间。相互独立。
• 一致性：事务操作前后，数据总量不变

事务的隔离级别

• 存在问题

  • 脏读（读未提交）：一个事务，读取到另一个事务中没有提交的数据。无论是回滚还是提交都是在源数据上进行了修改

  • 不可重复读(虚读，读已提交)：在同一个事务中，两次读取到的数据不一样。

  • 幻读：幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，同时再查询时还是发现某记录不存在。

    • 举例说明：
      step1 T1: SELECT * FROM \`users\` WHERE \`id\` = 1;
      step2 T2: INSERT INTO \`users\` VALUES (1, 'big cat');
      step3 T1: INSERT INTO \`users\` VALUES (1, 'big cat');
      step4 T1: SELECT * FROM \`users\` WHERE \`id\` = 1;
      
      T1 ：主事务，检测表中是否有 id 为 1 的记录，没有则插入，这是我们期望的正常业务逻辑。
      
      T2 ：干扰事务，目的在于扰乱 T1 的正常的事务执行。
      
      在 RR 隔离级别下，step1、step2 是会正常执行的，step3 则会报错主键冲突，对于 T1 的业务来说是执行失败的，这里 T1 就是发生了幻读，因为 T1 在 step1 中读取的数据状态并不能支撑后续的业务操作，T1：“见鬼了，我刚才读到的结果应该可以支持我这样操作才对啊，为什么现在不可以”。T1 不敢相信的又执行了 step4，发现和 setp1 读取的结果是一样的（RR下的 MMVC机制）。此时，幻读无疑已经发生，T1 无论读取多少次，都查不到 id = 1 的记录，但它的确无法插入这条他通过读取来认定不存在的记录（此数据已被T2插入），对于 T1 来说，它幻读了。
      

• 隔离级别

  • read uncommitted：读未提交。产生的问题：脏读、不可重复读、幻读

  • read committed：读已提交 （Oracle）。产生的问题：不可重复读、幻读

  • repeatable read：可重复读 （MySQL默认）。产生的问题：幻读

  • serializable：串行化。可以解决所有的问题

  • 注意：隔离级别从小到大安全性越来越高，但是效率越来越低

  • 数据库查询隔离级别：select @@tx_isolation;

  • 数据库设置隔离级别（设置隔离级别后需要重新打开数据库才能生效）：set global transaction isolation level 级别字符串;

注意事项

• 如果事务中所有sql语句执行正确则需要自己手动提交commit；否则有任何一条执行错误，需要自己提交一条rollback，这时会回滚所有操作，而不是commit会给你自动判断和回滚。
• 新开一个事务会将该连接中的其他未提交的事务提交，相当于commit！
• 事务既没有提交也没有回滚时连接断开数据库会自动回滚
• 事务中，update语句如果没有commit的话，你再重新执行update语句，就会等待锁定，当等待时间过长的时候，就会报ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction的错误。

JDBC

1. setint 设置错误直接查询的结果

   当age为int类型时，在数据库中也支持 where age = ‘112’ 的查询方式。

1. 概念：Java DataBase Connectivity  Java 数据库连接， Java语言操作数据库
	* JDBC本质：其实是官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包。我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。

2. 快速入门：
	* 步骤：
		1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar
			1.复制mysql-connector-java-5.1.37-bin.jar到项目的libs目录下
			2.右键-->Add As Library
		2. 注册驱动
		3. 获取数据库连接对象 Connection
		4. 定义sql
		5. 获取执行sql语句的对象 Statement
		6. 执行sql，接受返回结果
		7. 处理结果
		8. 释放资源

	    

* 代码实现（异常直接抛出）
    //注册驱动
    Class.forName("com.mysql.jdbc.Driver");
    //获取数据库连接对象 Connection
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/self_study", "root", "root");
    //定义sql
    String sql = "update bank set money=10";
    //获取执行sql语句的对象 Statement
    Statement statement = conn.createStatement();
    //执行sql，接受返回结果
    int count = statement.executeUpdate(sql);
    //处理结果
    System.out.println(count);
    //释放资源
    statement.close();
    conn.close();


* 代码实现（异常进行抓取）
Connection conn = null;
Statement stmt = null;

try {
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection("jdbc:mysql:///self_study","root","root");
    stmt = conn.createStatement();
    String sql = "update bank set money = 1";
    stmt.executeUpdate(sql);
} catch (SQLException e) {
    e.printStackTrace();
} catch (ClassNotFoundException e) {
    e.printStackTrace();
} finally {
    if (stmt != null) {
        try {
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

3. 详解各个对象：
	1. DriverManager：驱动管理对象
		* 功能：
			1. 注册驱动：告诉程序该使用哪一个数据库驱动jar
				static void registerDriver(Driver driver) :注册与给定的驱动程序 DriverManager 。 
				写代码使用：  Class.forName("com.mysql.jdbc.Driver");
				通过查看源码发现：在com.mysql.jdbc.Driver类中存在静态代码块
				注意：mysql5之后的驱动jar包可以省略注册驱动的步骤。
			2. 获取数据库连接：
				* 方法：static Connection getConnection(String url, String user, String password) 
				* 参数：
					* url：指定连接的路径
						* 语法：jdbc:mysql://ip地址(域名):端口号/数据库名称
						* 例子：jdbc:mysql://localhost:3306/db3
						* 细节：如果连接的是本机mysql服务器，并且mysql服务默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称
    2. Connection：数据库连接对象
		1. 功能：
			1. 获取执行sql 的对象
				* Statement createStatement()
				* PreparedStatement prepareStatement(String sql)  
			2. 管理事务：
				* 开启事务：setAutoCommit(boolean autoCommit) ：调用该方法设置参数为false，即开启事务
				* 提交事务：commit() 
				* 回滚事务：rollback() 
	3. Statement：执行sql的对象
		1. 执行sql
			1. boolean execute(String sql) ：可以执行任意的sql 了解 
			2. int executeUpdate(String sql) ：执行DML（insert、update、delete）语句、DDL(create，alter、drop)语句
				* 返回值：影响的行数
			3. ResultSet executeQuery(String sql)  ：执行DQL（select)语句
	4. ResultSet：结果集对象,封装查询结果
		* boolean next(): 游标向下移动一行，判断当前行是否是最后一行末尾(是否有数据)，如果是，则返回false，如果不是则返回true
		* getXxx(参数):获取数据
			* Xxx：代表数据类型   如： int getInt() ,	String getString()
			* 参数：
				1. int：代表列的编号,从1开始   如： getString(1)
				2. String：代表列名称。 如： getDouble("balance")
		
		* 注意：
			* 使用步骤：
				1. 游标向下移动一行
				2. 判断是否有数据
				3. 获取数据
			* 代码实现：
			        Connection conn = null;
                    Statement state = null;
                    try {
                        Class.forName("com.mysql.jdbc.Driver");
                        conn = DriverManager.getConnection("jdbc:mysql:///self_study", "root", "root");
                        state = conn.createStatement();
                        String sql = "select * from bank";
                        ResultSet resultSet = state.executeQuery(sql);
                        while (resultSet.next()){
                            String name = resultSet.getString("name");
                            int money = resultSet.getInt("money");

                            //或者用以下形式
                            /*String name = resultSet.getString(1);
                            int money = resultSet.getInt(2);*/
                            System.out.println(name + "------" + money);
                        }
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }finally {
                        if (state != null){
                            try {
                                state.close();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                        if (conn != null){
                            try {
                                conn.close();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    }
	5. PreparedStatement：执行sql的对象（常使用，能够解决数据库中where true -- a' or 'a'='a 返回全部的问题）
	经常使用PrepareStatement而不用Statement createStatement()；
            conn =  JDBCUtils.getConnection();
            //定义sql
            String sql = "select * from user where username = ? and password = ?";
            //获取执行sql的对象
            pstmt = conn.prepareStatement(sql);
            //给?赋值
            pstmt.setString(1,username);
            pstmt.setString(2,password);
            //4.执行查询,不需要传递sql   曾删改都是 ps.executeUpdate();
            rs = pstmt.executeQuery();
    6. 批处理 --- 优点：在exccutebatch时会将之前的sql语句在数据库中一次性处理完毕，节约数据库开销。
    	* Statement 批处理
			//添加要批量执行的SQL
			 Statement st = conn.createStatement();
             st.addBatch(sql1);
             st.addBatch(sql2);
             st.addBatch(sql3);
             //执行批处理SQL语句
             st.executeBatch();
             //清除批处理命令
             st.clearBatch();
             
        * PrepareStatement 批处理
            ps = conn.prepareStatement("insert into t_u_h values(?,?) ");
            for (String hobby : hobbies) {
                ps.setInt(1,Integer.valueOf(id));
                ps.setInt(2,Integer.valueOf(hobby));
                ps.addBatch();
            }
            ps.executeBatch();
            ps.clearBatch();

JDBC管理事务

? 使用Connection对象来管理事务

    * 开启事务：`setAutoCommit(boolean autoCommit)` ：调用该方法设置参数为false，即开启事务
        * 在执行sql之前开启事务
            * 提交事务：`commit()` 
        * 当所有sql都执行完提交事务
            * 回滚事务：`rollback()`  。rollback用不用效果都一样，但是不用rollback()可能导致被锁住的数据不能及时的释放（需要等事物超时释放）,会影响下一次的事物操作。
        * 在catch中回滚事务,而且要抓取所有的异常
        	catch (<u>Exception</u> e) {
        e.printStackTrace();
        try {
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }  

数据库连接池

1. 概念：其实就是一个容器(集合)，存放数据库连接的容器。
   当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器。
2. 实现：

   1. 标准接口：DataSource javax.sql包下的

      1. 方法：
         • 获取连接：getConnection()
         • 归还连接：Connection.close()。如果连接对象Connection是从连接池中获取的，那么调用Connection.close()方法，则不会再关闭连接了。而是归还连接

   2. 一般我们不去实现它，有数据库厂商来实现

      1. C3P0：数据库连接池技术
      2. Druid：数据库连接池实现技术，由阿里巴巴提供的

C3P0：数据库连接池技术
	* 步骤：
		1. 导入jar包 (两个) c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar ，
			* 不要忘记导入数据库驱动jar包
		2. 定义配置文件：
			* 名称： c3p0.properties 或者 c3p0-config.xml
			* 路径：直接将文件放在src目录下即可。

		3. 创建核心对象 数据库连接池对象 ComboPooledDataSource
		4. 获取连接： getConnection
            ComboPooledDataSource comb = new ComboPooledDataSource();
            Connection conn = comb.getConnection();
	* 可能出现的错误
		数据库连接不上：配置文件的数据库名字没有改
		

5. Druid：数据库连接池实现技术，由阿里巴巴提供的
	1. 步骤：
		1. 导入jar包 druid-1.0.9.jar
		2. 定义配置文件：
			* 是properties形式的
			* 可以叫任意名称
		3. 加载配置文件。Properties
		4. 获取数据库连接池对象：通过工厂来来获取  DruidDataSourceFactory
		5. 获取连接：getConnection
	* 代码：
        Properties pro = new Properties();
        InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
        pro.load(is);
        
        DataSource ds = DruidDataSourceFactory.createDataSource(pro);
        Connection conn = ds.getConnection();

Spring JDBC

* Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发
* 步骤：
	1. 导入jar包
		commons-logging-1.2.jar
		spring-beans-5.0.0.RELEASE.jar
		spring-core-5.0.0.RELEASE.jar
		spring-jdbc-5.0.0.RELEASE.jar
		spring-tx-5.0.0.RELEASE.jar
		
	2. 创建JdbcTemplate对象。在构造中填入DataSource对象
		* JdbcTemplate template = new JdbcTemplate(DataSource);
		
		//该DataSource对象可以利用DruidDataSourceFactory对象获得
		//DataSource ds = DruidDataSourceFactory.createDataSource(pro);
		
	3. 调用JdbcTemplate的方法来完成CRUD的操作
		* update():执行DML语句。增、删、改语句
			jdbcTemplate = new JdbcTemplate(DruidDataSourceFactory.createDataSource(properties));
            jdbcTemplate.update("update bank set name = 'zhangsan' where name = ?","wangwu");
		* queryForMap():查询结果将结果集封装为map集合，将列名作为key，将值作为value 将这条记录封装为一个map集合
			* 注意：这个方法查询的结果集长度只能是1
			Map<String, Object> map = jdbcTemplate.queryForMap("select * from bank where name = 'lisi'");
		* queryForList():查询结果将结果集封装为list集合
			List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from bank");
			* 注意：将每一条记录封装为一个Map集合，再将Map集合装载到List集合中
		* query():查询结果，将结果封装为JavaBean对象（javaBean对象的属性一定要用包装类，因为查询到的数据如果为null，就不能赋值）
			* query的参数：RowMapper
				*（常用）一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装。javaBean对象的属性一定要用包装类，因为查询到的数据如果为null，就不能赋值
					List<Emp> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
					Emp为一个自己写的封装类，将想要查询表的信息封装在Emp类中，写上get、set、toString方法
				*（不常用）实现RowMapper接口，需要自己将返回的值填充到Emp对象中
				List<Emp> list = template.query(sql, new RowMapper<Emp>() {
                		@Override
			            public Emp mapRow(ResultSet rs, int i) throws SQLException {
			                Emp emp = new Emp();
			                String ename = rs.getString("ename");
			                emp.setEname(ename);
			                return emp;
			            }
		* queryForObject：查询结果，将结果封装为对象
			* 一般用于聚合函数的查询
			 Long total = template.queryForObject(sql, Long.class);

设置数据库时间

1 select now(); #查看现在的时间，可以见到跟实际时间差8小时
2 show variables like "%time_zone%"; #可以看到时区设置，可以看到是使用SYSTEM时间
3 set global time_zone = '+8:00'; #修改mysql全局时区为北京时间，即我们所在的东8区
4 set time_zone = '+8:00'; #修改当前会话时区
5 flush privileges; #立即生效

Oracle

单双引号格式：

? 字符串、时间 单引号

? as（重命名）后必须双引号

大小写敏感：

? 语法大小写不敏感

? 字符串大小写敏感

用户

• 以sys超级用户名，dba角色，即超级管理员身份解锁scott方案/用户，并为scott设置一个密码为tiger

  在cmd中：

  ? C:\Documents and Settings\Administrator>sqlplus / as sysdba

  ? SQL>alter user scott account unlock;

  ? SQL>alter user scott identified by tiger;

  普通用户scott
  密码tiger

• 进入与退出orcl数据库(sqlplus)
  ------以超级管管理员角色进入
  c:/>sqlplus / as sysdba
  sql>exit
  ------以普通用户进入
  c:/>sqlplus scott/tiger
  sql>exit

创建表

oracle中的数据类型有：

字符串 varchar2（10）/varchar（10） — varchar2变长/varchar 定长 。10的单位为byte（字节）,gbk中一个汉字为两个字节

? — varchar2更加的节省资源，且上下兼容

数值类型 number(5,2) — 5位（包含小数点后2位），小数点后保留2位

日期类型 date

-- 创建表
create table tb_emp(
       name varchar(12),
       ename varchar2(12),
       age number(4,2),
       birth date default sysdate      
)

查询

NVL(Expression 1, Expression 2)

如果Expression 1 表达式为null，则返回值为Expression 2。不为null，返回值为Expression 1

哑表 dual表

select ename || '的薪水是' || sal || '美元' from emp; — （oracle中的||表示字符串连接符，mysql中的||表示逻辑运算符）

between··and··

select * from emp where hiredate between '20-2月-81' and '23-1月-82';

注意：
1)对于数值型，小数值在前，大数值在后
2)对于日期型，年长值在前，年小值在后（mysql不是）

order by

null 值在排序时最大（mysql 中null值在排序时最小）

escape

指定转义符号

select * from emp where ename like '%$_%' escape '$';

case···when···

select 
       case job
         when 'CLERK' then sal+10
           when 'SALESMAN' then sal + 20
             else sal
               end
from emp;

-- 另一种方法
-- decode(字段,条件1,表达式1,条件2,表达式2,...表达式n)
select decode(job,'CLERK',sal + 10,'SALESMAN',sal + 1,sal) from emp;

回收站

进入回收站
drop table users;

查询回收站中的对象
show recyclebin; — PLsql 中不能使用，可以在sqlplus中使用

闪回，即将回收站还原
flashback table 表名 to before drop;
flashback table 表名 to before drop rename to 新表名;

彻底删除users表
drop table users purge;

清空回收站
purge recyclebin;

函数

[oracle函数api](资料\oracle API.chm)

类型转换

三大类型转换（number 和 date之间不能转换）

• varchar2变长/char定长---->number
• varchar2/char---->date
• number---->varchar2/char
• date---->varchar2/char
• 转换函数
  • to_char()
  • to_number()
  • to_date()

-- 时期转换为字符串
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;
-- 时间转换为字符串
select to_char(sysdate,'yyyy-mm-dd HH24:MI:SS') from dual;
select to_char(sysdate,'yyyy-mm-dd HH12:MI:SS AM') from dual;
-- 数字转为字符串, to_char中有规定能够用那些字符($)
select to_char(1124,'$9999.99') from dual;--$1124.00
select to_char(1124,'L9999.99') from dual;--￥1124.00
-- 字符串转为日期
select to_date('2021-12-11 11:11:11','yyyy-mm-dd hh24:mi:ss') from dual;
-- 字符串转数字
select to_number('333') from dual;

隐式转换：

• 在转换时，要确保合法合理，否则转换会失败，例如：12月不会有32天，一年中不会有13月

-- 隐式转换案例
-- 查询1980年12月17日入职的员工（日期隐示式转换）
select * from emp where hiredate = '17-12月-80';

PostgreSQL

PostgreSQL 9.5.3 中文手册

存储过程

SQL语句

EXISTS

• EXISTS的参数是一个任意的SELECT语句， 或者说子查询。系统对子查询进行运算以判断它是否返回行。如果它至少返回一行，那么EXISTS的结果就为"真"； 如果子查询没有返回行，那么EXISTS的结果是"假"。

• SELECT col1
  FROM tab1
  WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
  

左查询

• postgresql 左查询 之后还要跟个where条件才能筛选

函数

数组变为字符串
array_to_string(array_agg(要连接的字段 ORDER BY 排序字段 ASC/DESC),'分隔符')
	- array_agg	将要连接的字段行成数组返回
	- array_to_string	将数组变为字符串

字段信息变为字符串
string_agg(expression,delimiter)
	- 事例：
		select string_agg(name,',') from tbl_test;
		张三,李四,王五

获取序列值
SELECT nextval('SYS_C0036029')	
	-根据序列'SYS_C0036029'获取主键的id号

主键与序列绑定
alter table 表名 alter column 列名 set default nextval('序列名');

类型转换
order by cast(字段 as 数据类型)
order by cast(pic_id as int)

条件判断函数

CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END

COALESCE
	返回参数中的第一个非null的值
	
	

数据类型格式化函数

格式化函数

日期时间格式模板

sql事例

-- 根据编号分组后取第一条数据
SELECT * FROM 
    (SELECT ROW_NUMBER() OVER (partition BY 分组字段 order by 排序字段 desc) rowId,*
    from 要查询的表) t
WHERE rowId=1

-- 日期加减
SELECT now()::timestamp + '1 year';  --当前时间加1年
SELECT now()::timestamp + '1 month';  --当前时间加一个月
SELECT now()::timestamp + '1 day';  --当前时间加一天
SELECT now()::timestamp + '1 hour';  --当前时间加一个小时
SELECT now()::timestamp + '1 min';  --当前时间加一分钟
SELECT now()::timestamp + '1 sec';  --加一秒钟
select now()::timestamp + '1 year 1 month 1 day 1 hour 1 min 1 sec';  --加1年1月1天1时1分1秒
SELECT now()::timestamp + (col || ' day')::interval FROM table --把col字段转换成天 然后相加

-- 获取下一个day（如星期一）的日期。对标mysql中的next_day函数
SELECT 'TOMORROW'::date + ( 5 + 7 - extract ( dow FROM 'TOMORROW'::date))::int%7; -- 5代表星期五的序列

-- queryKeyWord不存在查询所有，存在则查询queryKeyWord = '123'的结果
?queryKeyWord is null or ?queryKeyWord = '123'