sql自用

javaweb-sql

简介

• 结构化查询序言

• 方言 不同关系型数据库自己的语言

语法

• 可单行多行书写,分号结尾

• SQL语句不分大小写

• 注释

  ​ 单行注释: – 或 #

  ​ 多行注释: /* */

SQL分类

• DDL 定义数据库对象
• DML 数据库操作语言
• DQL 数据库查询语言
• DCL 数据控制语言

关键字

####数据库

• show 查询当前mysq下有多少数据库

  • show 连接名

  show database;
  

• create database ＋ 所创建的数据库名称

create database student;
--创建同名数据库会报错 使用判断
create database if not exists student;

• drop database 删除数据库

  drop database student;
  --删除前判断
  drop database if not exists student;
  

表

• 创建表

  create table tb_user (
  	id int,
  	username varchar(20),
  	password varchar(20)
  );
  

  id	username	password

  • 数据类型

    • 数值类型

    • 日期类型

    • 字符串

      --案例
      create table student (
      	id int,
          name varchar(10),
          gender char(1),
          birthday date,
          socre double(5,2),
          email varchar(64),
          tell varchar(15),
          status tinyint
      )
      

      编号	姓名	性别	生日	入学成绩	邮件地址	联系电话	学生状态

• 删除表

  drop table 表名;
  --判断后删除
  drop table if exists 表名;
  

• 查询表

  show tables; --查询全部表名称
  desc 表名; --查询表结构
  

• 修改表

  -- 修改表名
  ALTER TABLE 表名 RENAME TO 新表名;
  -- 添加一列
  ALTER TABLE 表名 ADD 列名 数据类型;
  -- 修改数据类型
  ALTER TABLE 表名 MODIFY 列名 新数据类型;
  -- 修改列名和数据类型
  ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
  -- 删除列
  ALTER TABLE 表名 DROP 列名;
  

表中数据操作

• 添加数据

  -- 指定列添加数据
  INSERT INTO 表名(列名1,列名2) VALUES(值1,值2);
  -- 给全部列添加数据
  INSERT INTO 表名 VALUES(值1,值2);
  -- 批量添加
  INSERT INTO 表名(列名1,列名2) VALUES(值1,值2),(值1,值2);
  INSERT INTO 表名 VALUES(值1,值2),(值1,值2);
  -- 修改数据
  
  UPDATE 表名 SET 列名 操作 WHERE 条件
  -- 案例 
  
  
  CREATE TABLE stu (
  	id INT,-- 编号
  	NAME VARCHAR (20),-- 姓名
  	age INT,-- 年龄
  	sex VARCHAR (5), -- 性 别
  	address VARCHAR (100),-- 地址
  	math DOUBLE (5, 2),-- 数学成绩
  	english DOUBLE (5, 2),-- 英语成绩
  	hire_date date-- 入学时间
  );
  
  INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date)
  VALUES
  (1,'码云',55,'男','杭州',66,78,'1995-09-01'),
  (2,'马化腾',55,'女','深圳',66,48,'1895-02-01'),
  (3,'马斯克',55,'男','香港',23,78,'1995-03-01'),
  (4,'流白',55,'男','湖南',66,58,'1994-09-04'),
  (5,'留情',55,'女','湖南',66,NULL,'1595-09-01'),
  (6,'刘德华',55,'男','香港',76,78,'1695-09-02'),
  (7,'张学友',55,'男','香港',64,28,'1795-09-05'),
  (8,'德玛西亚',55,'女','南京',96,78,'1195-09-09');
  

• 查询语法

  -- 基础查询
  
  -- 查询指定列
  SELECT 列名1,列名2 FROM STU;
  
  -- 查询所有列
  SELECT * FROM STU;
  
  -- 去除重复记录
  SELECT DISTINCT 列名 FROM STU;
  
  -- 别名 (会替换原有字段)
  SELECT 列名1 AS 列1,列名2 AS 列2 FROM STU;
  

  • 条件查询

    SELECT 列名1,列名2 FROM STU WHERE 条件;-- 条件 : 列名 <>=&& 值
    -- =, <>, !=
    -- 或者 or 且 AND
    --  or-> 列名 IN (V1,V2,V3) 
    -- NULL值不能使用 =, != 需要 is ,is not; 
    -- 日期类型 可直接比较 例如
    
    WHERE DATE1 BETWEEN '1999-09-01' AND '1998-09-01';
    -- 可查询日期之间的元组
    
    -- 模糊查询 like
    
    SELECT * FROM STU WHERE name LIKE '马%';
    
    -- _ 代表任意单个字符 
    -- % 代表任意个数字符串
    

• 排序查询

  SELECT * FROM STU ORDER BY AGE ASC;
  
  -- ASC 升序  DESC 降序排列
  -- 可嵌套 先执行前面的排序再执行后面的排序 对相同项排序 逗号隔开
  
  

• 分组查询

  -- 聚合函数
  -- count 统计数量 只统计非null
  SELECT COUNT (列名) FROM STU;
  -- max 查询最高分
  -- min 查询最低分
  -- sum 求和
  -- avg 求平均分
  -- null 值不参与聚合函数运算
  

  -- 分组查询
  
  SELECT sex,AVG(math) FROM GROUP BY SEX;
  
   -- 不参与分组 少于给定分数的 分组前筛选
  SELECT sex,AVG(math),count(*) FROM stu WHERE math > 60 GROUP BY sex;
  
  -- 分组后筛选 having 
  SELECT sex,AVG(math),count(*) FROM stu WHERE math > 60 GROUP BY sex having count(*) > 2;
  
  

  执行顺序 WHERE > 聚合函数 > having

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LirEesfc-1651123931451)(D:\firstdate\笔记\image-20220209200729753.png)]

• 分页查询

  SELECT * FROM STU LIMIT 起始索引,查询条目数; -- 第N页起始索引为 页码减一乘以条数 //方言
  

约束

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mNHyUkqu-1651123931453)(D:\firstdate\笔记\image-20220209213201689-16444135233832.png)]

CREATE TABLE emp (
	id INT PRIMARY KEY, -- 主键
	ename VARCHAR ( 10 ) NOT NULL UNIQUE,-- 非空且唯一
	joindata DATE NOT NULL,-- 非空
	salary DOUBLE ( 7, 2 ) NOT NULL,-- 非空
bonus DOUBLE ( 7, 2 ) DEFAULT 0 -- 默认值为0 
)

外键约束

CREATE TABLE emp (
	id INT PRIMARY KEY,
	ename VARCHAR ( 10 ) NOT NULL UNIQUE,
	joindata DATE NOT NULL,
	salary DOUBLE ( 7, 2 ) NOT NULL,
bonus DOUBLE ( 7, 2 ) DEFAULT 0,
    dept_id int,
    -- 添加外键 
    CONSTRAINT fk_emp_dept FOREIGN KEY(dept_id) REFERENcES dept(id) 
)

• 先创建主表再创建从表 删除时先删除从表被相关的记录

-- 删除外键
ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept;

-- 创建表之后添加外键
alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dept_id) references dept(id);
-- 修改表 emp    添加

数据库设计

表关系

1. 一对一

   用户表–用户详情

   分 AB表 提高效率和查询性能

2. 一对多

   部门–员工

   在多的一方添加外键 关联少的一方的主键

3. 多对多

   商品–订单

   借助第三张中间表

多表查询

笛卡儿积: 取AB集合所有组合情况 需要消去无效数据

内连接

SELECT * FROM EMP,DEPT WHERE EMP.DEPT_ID = DEPT.DID;
-- 内连接 求ab两集合的交集

SELECT T1.NAME,T1.AGE,T2.NAME FROM emp T1,tept T2 WHERE T1.DEMP_ID = T2.DID;
-- 表明也可以起别名

-- 显式内连接

SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPT_ID = DID;

外连接

-- 左外连接
SELECT * FROM EMP LEFT OUTER JOIN DEPT ON EMP.DEPT_ID = DID;
-- 右外连接
SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON EMP.DEPT_ID = DID;
-- 左外连接显示表1和交集
-- 右外连接显示表2和交集

子查询

再查询里面嵌套一个查询

-- 查询大于马云工资的员工信息
-- 1.查询马云工资
SELECT SLARY FROM EMP WHERE NAME = '马云';
-- 2.查询目标
SELECT * FROM EMP WHERE SLARY > 3600;
-- 子查询嵌套写法

SELECT * FROM EMP WHERE SLARY > (SELECT SLARY FROM EMP WHERE NAME = '马云');

• 单行单列

  -- 可作为条件值 使用比较运算符比较
  SELECT *FROM 表 WHERE 字段名 > (子查询);
  

• 多行单列

  -- 也可以作为条件值 但使用 in 关键字 
  SELECT *FROM 表 WHERE 字段名 in (子查询);
  -- 不同表之间查询
  

• 多行多列

  -- 作为i虚拟表
  SELECT * FROM (子查询) WHERE 条件;
  
  子查询作为有效信息表参与内连接
  

事务(transaction)

事务简介

1. 包含了一组数据库命令
2. 作为整体向系统提交或者撤销操作请求, 同一组数据库命令要么同时成功要么同时失败
3. 事务是一个不可分割的工作逻辑单元

开启事务->关闭事务

出错则回滚事务

-- 开启事务
BEGIN;
-- 写操作
-- 我是操作
-- 我是操作
-- 我是操作
-- 我是操作
-- 不出错则可以运行提交事务 出错的话可执行回滚事务撤销所有操作
COMMIT; -- 提交事务

ROLLBACK;-- 回滚事务

事务的四大特征

• 原子性
• 一致性
• 隔离性
• 持久性

事务提交默认自动提交 可更改

非默认提交好处:

可通过条件判断避免非语法错误

SET @@AUTOCOMMIT = 0(1);

---

MYSQL 函数

---

用户权限管理

dcl 管理用户

1 查询用户

use mysql;
select * from user;
-- 系统数据库的user表中存放着所有用户信息 --

2 创建用户

create user 'yonghu1' @ '127.0.0.1' identified by 'root1'
 --          用户名		地址						密码
 
 -- 创建任意主机可使用的用户
 create user 'heima'@ '%' identified by 'root1';

3 修改用户密码

alter user 'heima'@'%' identified with mysql_native_password by '123456';
-- @ 前后不要有空格

4 删除用户

drop user 'heima'@'%';

5 权限

可以直接参考 官方文档

控制权限

1. 查询权限

   show grants for ''@'';
   -- 结果中 usage 代表无任何权限
   

2. 授予权限

   grant 权限列表 on 数据库名.表名 to ''@'';
   --     all            * . * 
   

3. 撤销权限

   revoke 权限列表 on 数据库.表 from ''@'';
   

函数

###常用函数

-- 拼接字符串
select concat('hello ','mysql');

-- upper 全部转da写
select upper('helLo');
-- lower 转小写
select lower('HEllo');
-- lpad 左填充 三个参数
select lpad('01',5,'1234');
-- rpad 右填充
select rpad('01',5,'-');

-- trim 去前后空格
select trim('  j j ');

-- substring 索引从1开始
select substring('hello',1,3);

数值函数

-- 数值函数
-- 向上
select ceil(1.5);
-- 向下取整
select floor(1.9);
-- 球磨
select mod(8,4);
-- 随机数
select rand();
-- 四舍五入
select round(2.345,0);

-- 随机六位的验证码
select lpad(round(rand()*1000000,0),6,'0');

日期函数

-- curdate 当前日期

select curdate();
-- curtime 当前时间
select curtime();
-- now()
select now();

-- year month day
select year(now());
select day(now());
-- date_add
select date_add(now(), interval -1000 year );
-- date diff
select datediff('2021-1-01','2022-5-01');

---

流程控制函数

索引

帮助MySQL高效获取数据的数据结构(有序).

B+Tree(多路平衡查找数)

B-Tree

以最大度数(max-degree)为5的b-tree为例(每个节点最多存四个key,5个指针)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5PXSxIiA-1651123931455)(D:\firstdate\笔记\image-20220426003914435-16509047576691.png)]

B+Tree

以最大度数(max-degree)为4的b+tree为例

索引在节点中,值在叶子上

优势

​ 所有值都在底层叶子节点,双向链表,查询效率稳定,可范围查找

索引分类

1. 主键索引

2. 非空索引

3. 常规索引

4. 全文索引