MySQL 知识点小结

2019-08-23  本文已影响0人  梦想旅行家Jirry

利用控制台命令连接 MySQL

mysql -hlocalhost -uroot -p123456
h:host（IP地址）
u：username（用户名）
p：password（密码）

MySQL默认端口号：3306

Oracle默认端口号: 1521

数据库基础操作

  1. 利用控制台
    • 创建数据库 creat database student;
    • 显示所有数据库 show databases;
    • 切换（使用）数据库 use student;
    • 显示数据库下的数据库表 show tables;
    • 删除数据库 drop database student;
  2. 利用 Navicat Premium 命令列界面

SQL (Structured Query Language) 语言入门

  1. SQL语言 分为 5 个部分：
    • 数据查询语言 （ Data Query Language , DQL ）
      • DQL 主要用于 数据的查询 ，基本结构是使用 SELECT 子句 ，FROM 子句 ，WHERE 子句的组合来查询一条或多条数据。
    • 数据操作语言 （ Data Manipulation Language ,DML）
      • DML 主要是用于 数据库中的 数据 进行 增加、删除、修改、删除 的操作 ，主要包括 ：
        1. INSERT : 增加
        2. UPDATE : 修改（更新）
        3. DELETE : 删除
    • 数据定义语言 （ Data Definition Language , DDL）
      • DDL 主要用于针对是 数据库对象 （表，索引，视图，触发器，存储过程，函数，表空间等）进行创建、修改 和 删除 操作 。主要包括：
        1. CREATE : 创建数据库对象
        2. ALTER : 修改数据库对象
        3. DROP : 删除数据库对象
    • 数据控制语言 （Data Control Language , DCL）
      • DCL 用来 授予 或 回收 访问数据库的权限 。主要包括：
        1. GRANT : 授予用户某种权限
        2. REVOKE : 回收授予的某种权限
    • 事务控制语言 （Transaction Control Language , TCL）
      • TCL 用于 数据库的事务管理 。主要包括：
        1. START TRANSACTION : 开启事务
        2. COMMIT : 提交事务
        3. ROLLBACK : 回滚事务
1. 数据查询语言（ Data Query Language ）
单表查询
  1. 注释语句
1.方式一：# 注释
2.方式二：-- 注释
3.多行注释：
/*

多行注释

*/

  1. 基本查询

    • 查看所有数据

      selecet * from emp;

      运行方式1. 选中要运行的，右键，运行已选中 ;

      运行方式2. Ctrl + Shift + R ；

    • 查看部分字段

      select ename,empno,sal from emp;

      SELECT ENAME,EMPNO,SAL FROM EMP;

      大小写：对于关键字，字段名，表名，不区分大小写。

      使用算数表达式

      select ename,empno,sal*12+50000 from emp;

      使用别名

      • select ename 员工姓名,empno 员工编号,sal 月工资,sal*12+50000 年薪 from emp;
      • select ename '员工姓名',empno '员工编号',sal '月工资',sal*12+50000 '年薪' from emp;# 单引号可以
      • select ename "员工姓名",empno "员工编号",sal "月工资",sal*12+50000 "年薪" from emp;# 双引号可以
      • select ename as 员工姓名,empno as 员工编号,sal as 月工资,sal*12+50000 as 年薪 from emp;# 中间加上as可以
      • select ename as '员工 姓名',empno as '员工+编号',sal as '（月工资）',sal*12+50000 as '年薪' from emp;# 如果别名中有特殊符号，那么 单引号 或者 双引号 必须加上，但是别名中要是没有特殊符号，单引号 双引号 可以省略不写。

    • 查看：

      • 有哪些职位：

        select job from emp;# 不去重查找职位

        select distinct job from emp;# 去重查找职位

      • 有几个职位：

        select count(distinct job) from emp;

        select count(distinct job) 职位个数 from emp;# 加别名

      • 查询组合的（去重）

        select distinct job,sal from emp # 两个字段组合起来看 是不重复的

        select distinct (job,sal)from emp # () 不用写，写了报错，跟上面的意思一样。

    • 排序查询：

      • 升序：(按工资排序)

        select ename,sal from emp order by sal; # 升序

        select ename,sal from emp order by sal asc;# 升序

      • 降序：

        select ename,sal from emp order by sal desc;# 降序

      • 升序降序都有：

        select * from emp order by sal asc,hiredate desc;# 先按照sal升序排列，在sal相同情况下，按照hiredate降序排列

  2. 条件查询：

    • WHERE

      where 条件查询过滤的是行数据

      • 简单查询：

        select * from emp where sal>2000; # 大于

        select * from emp where sal=2000;# 等于

        select * from emp where sal<2000; # 小于

        select * from emp where sal!=2000; # 不等于

        select * from emp where sal<>2000; # 不等于

        select ename,sal,deptno,hiredate from emp where hiredate < '1982-01-22'; # < 代表的是早于82年的数据

        select * from emp where job='MANAGER';

        select * from emp where job="MANAGER";# 单双引号都可以

        select * from emp where job='manager';# mysql中在精准查询的时候，发现是忽略大小写查询的。

        select * from emp where BINARY job='MANAGER'; # 想要精准的查询大小写，要在前面加上关键词：binary（大小写均可）

    • AND

      • 查询

        select * from emp sal>1000 and sal<3000;# 不包括1000 和 3000

        select * from emp sal>=1000 and sal<=3000;# 包括1000 和 3000

        select * from emp sal>=1000 && sal<=3000; # 包括1000 和 3000 （与上面and的结果一致）

        select * from emp where sal between 1000 and 3000; # 包括 1000 和 3000 （与2、3相同）

        select * from emp where sal not between 1000 and 3000; # 不在1000 和 3000 之间的

    • OR

      • 查询

        select * from emp where deptno=30 and deptno=10;# 查询不到同时是10 和 30 的数据

        select * from emp where deptno=30 or deptno=10;# 查询 30 或 10

        select * from emp where deptno=30 || deptno=10;# 查询 30 或 10（与2相同）

        select * from emp where deptno=30 or 10;# 错误，不能这么写，虽然不报错，但是查出来的数据与想得到的数据差别很大

        select * from emp where deptno in (30,10); # 查询 30 和 10 的数据

        select * from emp where deptno not in (30,10);#查询不在30 和 20 的数据

    • 模糊查询

      • 查询名字中带字母s数据：（任意位置）

        select * from emp where ename='S';# 精准查询：查询名字带s的

        select * from emp where ename='%S%'; # 模糊查询 % :0 - n 个字符，中间用 = 不行，正确写法：

        select * from emp where ename like '%S%'; # 查询都是带s的

        select * from emp where ename like '%o%'; # 忽略大小写

        select * from emp where binary ename like '%o%';# 关注大小写，前面加 binary

      • 查询名字中第二个字母带o的数据：（第二个位置）

        select * from emp where binary ename like '_o%';# _代表一个字符

      • 查询名字中第三个字母带o的数据：（第三个位置）

        select * from emp where binary ename like '__o%'; #查询 名字中第三个字母为o的

    • 代码执行顺序

      1. select * from emp where job='saleman' or job ='manager' and deptno =10;

      2. select * from emp where (job='saleman' or job ='manager') and deptno =10;

      3. select * from emp where job='saleman' or (job ='manager' and deptno =10);

        结论：and 的运算的优先级 高于 or ，写sql时用括号控制优先顺序

        （1 和 3 结果一致）

  3. 函数

    select avg(sal) from emp; # 求平均值

    • 函数只是对查询结果中的数据进行处理，不会改变数据库中数据表的值。

    • 分类：

      • 单行函数：单行函数是指对每一条记录，输入值并进行计算，得到相应的计算结果，然后返回给 用户 ，也就是说，每一条记录作为一个输入参数，经过函数计算，得到每条记录的计算结果。
        1. 常用的单行函数：字符串函数、数值函数、日期与时间函数、流程函数 以及 其他函数 。
        2. 一对一行 进行处理，产生一个结果 。
      • 多行函数：多行函数是指对一组数据进行运算，针对这一组数据（多行记录）只返回一个结果，也称为 分组函数 。
        1. 一对多行 进行处理，产生一个结果 。

    • 区别：

      1. 多行函数：count 、sum、avg、max、min ，除了这五个以外，都是 单行函数 。

      2. 单行函数：对一行数据进行处理，返回一个结果 。

        多行函数：对多行数据进行处理，返回一个结果 。

    • 字符串函数

      • 伪表

        select 1+1 计算结果 from dual;

        -- 字符函数
select length('abc') from dual;
SELECT LOWER('ABC') from dual;
select UPPER('abc') from dual
select REPLACE('abcaig','a','o');
select SUBSTRING('abcdef',4,2) from dual



-- 数值函数
SELECT ABS(-4) from dual;
select CEIL(9.1) from dual;
select FLOOR(10.9)from dual;
select MOD(10,3) from dual;
select POW(2,3) from dual;
select RAND() from dual;
SELECT ROUND(6.4) FROM dual;

-- 日期函数
SELECT NOW(),SYSDATE(),SLEEP(3),SYSDATE() from dual;

-- 其他函数
# 查看数据库名
SELECT DATABASE() from dual;
# 查看数据库版本
SELECT VERSION() from dual;
# 查看数据库用户
SELECT USER() from dual;
# 查看数据库IP
SELECT INET_ATON('10.0.0.5') FROM dual;
-- 167772165
SELECT INET_NTOA('167772165') from dual;
-- 10.0.0.5
# 查看数据库密码
SELECT PASSWORD('sad') from dual;

-- 流程函数
SELECT if(1=1,'a','b') from DUAL;
SELECT IFNULL(null,'前面为空') from dual;
SELECT NULLIF(13,13) from dual;
SELECT NULLIF(139,13) from dual;
SELECT ename CASE job
 WHEN 'SALESMAN' THEN
     '销售'
 ELSE
     '其他职位'
END CASE form emp;


SELECT ename,
job,
sal, 
CASE when sal>=500 and sal<=800 then '工资最低的人'
when sal>800 and sal<=2750 then '工资稍高的人'
else '工资高'
end
 from emp;

    -- JSON函数

  

- 多行函数

  ```mysql
  -- 多行函数：sum , avg , count , max ,min 
  -- max，min 可以修饰所有类型
  -- sum,avg 仅限于修饰 数值类型
  -- count 可以修饰所有类型
  SELECT max(sal),min(sal),sum(sal),count(*),count(1),sum(sal)/count(*) from emp;
  
  -- count ： 计数
  -- 统计有多少员工
  SELECT count(ename) from emp;
  SELECT count(job) from emp;
  SELECT count(comm) from emp; -- 计数：null值不会被计数
  -- 最好不要用普通字段来统计全表的数据的个数，因为不知道哪个字段有null值
  
  SELECT count(*) from emp;
  SELECT 1 from emp;-- 每一行置成 1
  SELECT count(1) from emp;-- 效率高：直接计算1的个数
  
  -- sql判断
  SELECT avg(sal) from emp;-- 对
  SELECT avg(sal),ename from emp; -- 不对
  SELECT avg(sal),lower(ename) from emp;-- 不对
  -- 结论：多行函数不能跟普通字段和单行函数一起使用。

-- 计算部门个数
SELECT depetno from emp;
select count(desptno) from emp;
select count(DISTINCT desptno) from emp; -- 先去重 在计算

   
     
   
   - GROUP BY
   
     ```mysql
     -- group by
     -- 显示所有员工最高工资 人数
     SELECT max(sal),count(1) from emp;
     -- 显示每个部门的最高工资 人数（按照部门进行分组）
     SELECT max(sal),count(1) from emp group by deptno;
     /* 多行函数可以和group by分组之后的哪个字段结合到一起使用，但是跟普通字段依然不能结合使用 */
     SELECT deptno,max(sal),count(1) from emp group by deptno;
  -- 求出每个工作岗位的员工薪水最大值，每组人数
     SELECT job,max(sal),count(1) from emp group job;
  -- 显示每个部门的不同岗位的人数
     SELECT job,job,count(1) from emp group by deptno,job;
多表查询
子查询
集合查询
分页查询
-- 分页查询
select * from emp order by sal desc;# 按照工资进行降序排列

select * from emp order by sal desc limt 3;# 过滤出来的就是前三条数据

select * from emp order by sal desc limt 3,2;# 3:从索引为3开始取数据（下标从0开始） 2：长度为2

-- 实际分页 一共 14 条记录，每页 5 条数据，一共 3 页。
-- 第一页
select * from emp order by sal desc limit 0,5;-- 0:(当前页数-1)*5
-- 第二页
select * from emp order by sal desc limit 5,5;-- 5:（当前页数-1）*5
-- 第三页
select * from emp order by sal desc limit 10,5;-- 10:（当前页数-1）*5
-- 总结：每页起始索引就是 （当前页数-1）* 5
2. 数据操作语言 （ Data Manipulation Language ）& 数据定义语言 （ Data Definition Language ）

  1. 表 ：表（ Table ）是数据库中数据存储最常见和最简单的一种形式，数据库可以将复杂的数据结构用较为简单的二维表表示 。

    • 二维表是由 行 和 列 组成的，分别都包含着数据 。

      学号 姓名 性别 年龄
      1211101 张三 男 18
      1211102 李四 女 19
      1211103 王五 男 20
      1211104 赵六 女 21

      每个表中由若干行和列组成，在数据库表中的 行 称为 记录 ；列 称为 字段 。

    • 字段类型：

      • 数值类型：

        1. SMALLINT : 2 字节
        2. INT : 4 字节
        3. INTEGER : INT同义词
        4. BIGINT : 8 字节
        5. FLOAT : 4 字节
        6. DOUBLE : 8 字节

      • 字符串（ 字符 ）类型：

        1. CHAR : 固定长度字符串

        2. VARCHAR : 可变长度字符串

          VARCHAR 使用起来较为灵活；CHAR 处理速度更快 。

        3. TEXT : 非二进制大对象( 字符 )

        4. BLOB : 二进制大对象( 非字符 )

      • 日期、时间类型

        1. DATE : YYYY-MM-DD

        2. DATETIME : YYYY-MM-DD HH:MM:SS

        3. TIMESTAMP : YYYY-MM-DD HH:MM:SS

        4. TIME : HH:MM:SS

        5. YEAR : YYYY

          主键自增 ：不使用序列，通过 auto_increment

  2. 创建，删除 表，向表中 添加 、删除 、修改 数据

    ##########################################
-- 创建表:学生表
create table t_student(
 sno INT(4),
 sname varchar(4),
 sex char(1),
 age int(3),
 enterdate DATE,
 score double(3,1),
 clsname VARCHAR(4),
 email varchar(20)
)

/*
注意：
     1.表后面是() 不是{}
     2.先写字段名，再写字段类型
     3.最后定义的字段后面不用再写,
     4.每个字段类型要自己判定清楚
     5.()中的数字意思：
     int(4)    --->  4. "显示长度"    存入1234  存入 12345 --->  实际开发一般都写：int(11) 
     char(4)     --->    数字代表字符： 比如 4 ：存入：你好世界  存入：你好吗世界  存入：你好吗
     varchar(4) --->   数字代表字符： 比如 4 ：存入：你好世界  存入：你好吗世界  存入：你好吗
     double(4,1) ---> 1 位小数，全部位数为 4位 ： eg.123.1 行 4123.1 （小数前面超了不行）   12.113 （小数点超了行）  12.11  行
*/

-- 查看表数据:
select * from t_student;

-- 添加数据：
-- 全字段添加：一共添加了 8 个字段
insert into t_student (sno,sname,sex,age,enterdate,score,clsname,email) values (1,'lili','女',18,'2019-3-3',90.4,'302班','lilichina@163.com');

-- 全字段添加时可省略表后面的字段名
insert into t_student  values (2,'nana','女',19,'2019-3-3',90.4,'302班','nanachina@163.com');

-- 添加部分字段：
insert into t_student(sno,sname)  values (3,'fei');

-- 添加多条记录：
insert into t_student  values (4,'gang','男',17,'2019-3-3',90.4,'302班','gangchina@163.com'),(5,'ming','男',16,'2019-3-3',90.4,'302班','mingchina@163.com');

-- 目前缺点：
-- insert into t_student  values (1,'lina','仙'（或者填 null）,190,'2019/3/3',90.4,'302班','nanachina@163.com');
-- 1.学号可以重复插入
-- 2.名字可以插入 null
-- 3.性别可以是 男 女 以外的数值
-- 4.年龄可以超出范围 --- mysql中解决不了
-- 5.邮箱可以重复

-- 修改数据：
-- nana性别修改为男
update t_student set sex='男' where sname='nana';
-- nana性别修改为女，性别改 18
update t_student set sex='女',age=18 where sname='nana'; - 修改多个字段，中间连接符必须是,

-- 删除数据：
delete from t_student where sname='lili';-- from 必须写

truncate table t_student;-- 删除表数据，但是不删除表
delete from t_student;-- -- 删除表数据，但是不删除表
drop table t_student;-- 删除表

  3. 修改表的表名，修改列名，长度，类型 等 。

    ##########################################
-- 创建表:学生表
create table t_student(
 sno INT(4),
 sname varchar(4),
 sex char(1),
 age int(3),
 enterdate DATE,
 clsname VARCHAR(4),
 email varchar(20)
)

#####################################
-- 查看表结构：
desc t_student;

-- 已经建表后,再增加一列：
alter table t_student add score double(4,1);# 行，默认在表的最后一个位置添加列
-- 在表的开头添加一列
alter table t_student add height double(3,1) FIRST;# 行
-- 在某位之后添加列（sname）
alter table t_student add weight double(3,1) after sname;# 行
-- 在某位之前添加列是不可以的
alter table t_student add idcard int(11) before sname;# 错误

-- 删除列：
alter table t_student drop height;

-- 修改列：
-- 修改类型和长度：
alter table t_student modify score double (5,2);
-- 修改列名，类型和长度：
alter table t_student change score score2 double (4,1);

-- 修改表名：
alter table t_student rename to stu;

select * from stu;

  4. 约束

    • 为了防止不符合规范的数据存入数据库，在用户对数据进行 插入 、 修改 、删除 等操作时，MySQL 数据库管理系统提供了一种机制来检查数据库中的数据是否满足规定条件，以保证数据库中数据的准确性和一致性，这种机制就是 约束 。

    • 完整性约束 ：（ MySQL 中主要支持六种完整性约束 ）

      约束条件 约束描述
      PRIMARY KEY 主键约束，约束字段的值可唯一地标识对应的记录
      UNIQUE 唯一约束，约束字段的值是唯一的（只对非空数据有效，这个字段可以为null）
      NOT NULL 非空约束，约束字段的值不能为空
      DEFAULT 默认值约束，约束字段的默认值（添加null，可以添加，不会走默认值；‘ ’能添加进去，不会走默认值；添加部分字段时，才会将默认值添加进去）
      AUTO_INCREMENT 自动增加约束，约束字段的值自动递增
      FOREIGN KEY 外键约束，约束表与表之间的关系

      约束从作用上可以分为两类：

      1. 表级约束：可以约束表中 任意一个 或 多个字段 。
      2. 列级约束：只能约束其所在的 某一个字段 。
      • 主键约束 （PRIMARY KEY）： 约束表中的某个字段可以 唯一标识一条记录 。设置为主键的字段取值不能重复（唯一），也不能为空（非空） 。**主键可以是单个字段，也可以是多个字段的组合 ** 。

    • 代码：

      ##########################################
-- 创建表:学生表
create table t_student(
   sno INT(4) primary key auto_increment,
   sname varchar(4) not null,
   sex char(1) DEFAULT'男',
   age int(3),
   enterdate DATE,
   score double(3,1),
   clsname VARCHAR(4),
   email varchar(20) unique
)

-- 查看数据
select * from t_student;

-- 删除表
drop table t_student;

-- 插入数据：
insert into t_student values (null,'lili','男',17,'2019-2-4',98.5,'503班','lili@126.com');

insert into t_student values (null,'lili',null,17,'2019-2-4',98.5,'503班','lili@126.com');

insert into t_student values (null,'lili','',17,'2019-2-4',98.5,'503班','lili@126.com');

insert into t_student(sno,sname,age,score) values(null,'ming',19,99.5);

insert into t_student values (null,null,'男',17,'2019-2-4',98.5,'503班','lili@126.com');

insert into t_student values (null,'','男',17,'2019-2-4',98.5,'503班','lili@126.com');

insert into t_student values (null,'null','男',17,'2019-2-4',98.5,'503班','lili@126.com');

insert into t_student values (null,'lili','男',17,'2019-2-4',98.5,'503班','null');

insert into t_student values (null,'nana','男',17,'2019-2-4',98.5,'503班','lili@126.com');# 主键（学号）设置成功后，插不进去;加入 auto_increment 可以自增（主键自动添加） 可以添加，

alter table t_student drop PRIMARY KEY;# 删除主键约束
alter table t_student add PRIMARY KEY(sno);# 为已存在表中的字段添加主键约束

-- 给主键约束起名
create table t_student(
   sno INT(4),
   sname varchar(4),
   sex char(1),
   age int(3),
   enterdate DATE,
   clsname VARCHAR(4),
   email varchar(20)，
    constraint pk_stu primary key(sno)
)

    • 非空约束：

      • 规定了一张表中指定的某个字段值不能为空（ null ）。设置了非空约束的字段，插入数据为NULL时，数据库会提示错误，导致数据无法插入 。非空约束只能在 列 后面定义 。

      • 为已存在的表中的字段添加非空约束

        alter table t_student modify sex varchar(1) not null;

      • 使用 ALTER TABLE 语句删除非空约束

        alter table t_studnet modify sex varchar(1) null;

    • 唯一约束：

      • 规定了一张表中指定的字段值不能重复，即这一字段每个值都是唯一的。

        # 给唯一约束起名
create table t_student(
 sno INT(4),
 sname varchar(4),
 sex char(1),
 age int(3),
 enterdate DATE,
 clsname VARCHAR(4),
 email varchar(20)，
    constraint uk_stu_email unique(email)
)

# 删除唯一约束
alter table t_student drop index uk_stu_email;

    • 默认值约束：

      • 用来规定字段的默认值。如果某个被设置为 DEFAULT 约束的字段没插入具体值，那么该字段的值将会被默认值填充。默认值只能在列后面定义 。

        默认值约束的设置与非空约束一样，也只能使用列级约束。

    • 字段值自动增加约束：

      • 可以使表中某个字段的值自动增加。一张表中只能有一个自增长字段，并且该字段必须定义了约束（该约束可以是主键约束、唯一约束 以及 外键约束），如果自增字段没有定义约束，数据库会提示 ” Incorrect table definition;there can be only one auto column and it must be defined as a key “ 错误 。

        因为自增约束会自动生成唯一的 ID ，所以自增约束通常会配合主键使用，并且只适用于整数类型 。一般情况下，设置为自增约束字段的值会从 1 开始，每增加一条记录，该字段值加 1 。

        # 为已存在表中的字段添加自增约束
alter table t_student modify stu_id int(10) auto_increment;
# 使用 ALTER TABLE 语句删除自增约束
alter table t_student modify stu_id int(10);

    • 外键约束：

      • 用来实现数据库表的参照完整性的 。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性 。

      • 外键是指 表中某个字段的值 依赖于 另一张表 中某个字段的值，而被依赖的字段必须是具有 主键约束 或者 唯一约束 。被依赖的表我们通常称之为 父表 或 主表 ， 设置外键约束的表称为 子表 或者 从表 。

        drop table t_student;
drop table t_class;

-- 创建表
create table t_student(
     sno int primary key,
     sname varchar(10),
     cid int,
     constraint fk_student_cid FOREIGN KEY(cid) REFERENCES t_class(cno)
)

-- 查看学生表数据
select * from t_student;


-- 创建一个班级表
create table t_class(
     cno int primary key,
     cname varchar(10)
)

-- 查看班级表数据
select * from t_class;

-- 插入数据
-- 插入班级
insert into t_class values (1,'java01');
insert into t_class values (2,'java02');

-- 插入学生
insert into t_student values(1001,'lili',1);
insert into t_student values(1002,'nana',2);
insert into t_student values(1003,'feifei',1);

insert into t_student values(1004,'sisi',4);# Q:没有班级四 ， 但是学生还是插入了班级四

delete from t_class where cno =1;# Q：班级一删掉了，那么班级一的学生怎么办？

update t_class set cno =5 where cno =2;

-- 添加了外键之后，上面两个问题解决了，但还是有缺点

-- 删除外键：
alter table t_student drop foreign key fk_student_cid;

-- 加入外键：
alter table t_student add constraint fk_student_cid foreign key(cid) references t_class(cno) on delete set null on update cascade;

-- on delete set null 在班级表数据删除时，另一张表的数据：班级数据 set null
-- on delete cascade  级联，删除班级，这个班级学生直接被删除
-- on update cascade  在班级表数据更改的情况下，另一张学生表的班级跟着更改了
-- on updateset null  在修改班级时，学生表变为null

-- 实际开发中，不用外键约束 。

        补充：关于备份表和其他小点

        # 创建表
-- 多用于数据库的备份：
-- 备份表：表的结构和表的数据都在
CREATE table t_emp
as
select * from emp;
-- 查看表
select * from t_emp;
# t_emp 和 emp 完全相同 

# 创建表
-- 备份表：表的结构在，数据不在
CREATE table t_emp
as
select * from emp where 1=2;

# 创建表
-- 备份表：部分部分字段
CREATE table t_emp
as
select ename,job,mgr from emp;

-- 添加数据
insert into t_student values(1004,'lili',5);
insert into t_student set sno = 1005,sname='nana';

-- 删除表数据
delete from t_student;
truncate table t_student;

      • DELETE 和 TRUNCATE

        1. DELETE 为数据操作语言（ DML ），TRUNCATE 为数据定义语言 （ DDL ）；
        2. DELETE 操作是将表中所有记录一条一条删除，知道删除完，TRUNCATE 操作则是保留了表的结构，重新创建了这个表，所有的状态都相当于新表，因此，TRUNCATE 操作效率更高；
        3. DELETE 操作可以回滚，TRUNCATE 操作会导致隐式提交，因此不能回滚 。
        4. DELETE 操作执行成功后会返回已删除的行数 （如：删除 4 行记录，则会显示 “ Affected rows ：4 ”），TRUNCATE 操作不会返回已删除的行量，结果通常是： “ Affected rows ：0 ” 。
        5. DELETE 操作删除表中记录后，再次向表中添加新纪录时，对于设置由自增约束字段的值会从删除前表中该字段的最大值加 1 开始自增，TRUNCATE 操作则会重新从1 开始自增 。

索引

  1. 索引作用 ： 提高了查询速度 。

  2. 常见问题 ：

    Q1 ：索引占用空间吗？

    A1 ：占用空间，但是空间小，可以带来速度明显的提升 。

    Q2 ： 索引是不是越多越好？---> 创建索引时，要考虑索引是否值得添加

    A2 ： 不是，(1) 索引也占用空间，多个索引就会占用更多的空间；

    ​ (2) 给经常需要用到的内容建立索引，否则会查询建立了索 引，占用了空间，很少使用；

    ​ (3) 索引会提高查询速度，但会降低 添加，更新 ， 删除 的 速度 （ 因为不仅操作数据库，也要维护索引的增加、删 除的变化 ）

  3. 一般给哪些建立索引：

    Emp(empno,ename,job,mgr,hiredate,sal,comm,deptno,email)

    数据库会自动给 主键 empno 和 唯一键 email 建立索引

    要给经常出现在 where 子句中的或者 order by 子句中的 列 建立索引

    sal deptno ename

  4. 索引查询原理

    利用索引：B树数据结构 ---------- 提供的是快捷的查找方式

  5. 索引存储结构：B-Tree、R-Tree 和 Hash 。

# 查看表索引
show index from emp;

# 针对ename列创建索引
create index index_emp_name on emp(ename);

# 创建联合索引
create index index_emp_sal_desc_hiredate on emp(sal desc,hiredate desc);

事务

事务的概念
事务的特性（特征）
事务操作
-- 创建账户表
create table t_account(
    id int primary key auto_increment,
    username varchar(30) not null,
    balance double
)

select * from t_account;

insert into t_account (id,username,balance) values (null,'lili',2000),(null,'nana',2000);

-- 完成转帐
-- 开启转账事务:
start transaction;
update t_account set balance = balance -100 where id = 1;
update t_account set balance = balance +100 where id = 2;
-- 手动提交(事务提交成功)
commit;
-- 回滚（回滚事务：撤销刚执行的事）
rollback;

事务的隔离级别

事务的并发问题
脏读 （ Dirty read ）
不可重复读 （ Unrepeatable read ）
幻读 （ Phantom read ）

不可重复读 和 幻读 区别：

  1. 不可重复读 的重点是 修改 ，幻读 的重点是 新增 或 删除 。
  2. 解决不可重复读的问题，只需要 锁住满足条件的行 ，解决幻读需要 锁表 。
隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED Y Y Y
READ COMMITTED N Y Y
REPEATABLE READ N N Y
SERIALIZABLE N N N

Y : 出现问题 N ：不会出现问题，问题已解决！

-- 查看默认的事务隔离级别  MySQL 默认级别是 ：REPEATABLE READ
select @@tx_isolation;

-- 设置事务的隔离级别
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;
上一篇 下一篇

猜你喜欢

热点阅读