数据库基础

最新推荐文章于 2022-03-22 18:51:04 发布

把csdn当日记本的菜鸡

最新推荐文章于 2022-03-22 18:51:04 发布

阅读量606

点赞数

分类专栏： # JAVA基础文章标签：数据库 mysql 服务器

本文链接：https://blog.csdn.net/weixin_38472038/article/details/121885949

版权

JAVA基础专栏收录该内容

20 篇文章 0 订阅

订阅专栏

数据库/数据表基本操作
- 数据库
  - MySql用户管理
    - 修改密码:首先在DOS 下进入mysql安装路径的bin目录下，然后键入以下命令:mysqladmin -uroot -proot;
    - 增加用户：格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by ‘密码’
  - 数据库基本操作
    - 启动mysql服务器net start mysql
      - 关闭net stop mysql
        进入 mysql -h 主机地址 -u 用户名－p 用户密码
        退出 exit
    - status 显示当前mysql的version的各种信息
    - 判断是否存在数据库abc,有的话先删除 drop database if exists abc;
    - create database 数据库名称;
      - 创建一个叫db1的数据库show create database db1
    - 显示数据库show databases;
    - 将数据库的字符集修改为gbk MySQL命令：
      - alter database 表名 character set gbk;
    - 切换数据库 MySQL命令：
      - use db1;
    - 查看当前使用的数据库 MySQL命令：
      - select database();
- 数据表
  - 创建数据表
    - create table student( id int, name varchar(20), gender varchar(10), birthday date );
  - 查看数据表
    - 查看表名
      - show tables;
    - 查看表中的字段信息
      - desc student;
  - 修改数据表
    - 修改表名
      - alter table student rename to stu;
    - 修改字段名
      - alter table stu change name sname varchar(10);
    - 修改字段数据类型
      - alter table stu modify sname int;
    - 增加字段
      - alter table stu add address varchar(50);
    - 删除字段
      - alter table stu drop address;
  - 复制表
    - 不会复制主键类型，索引
      - 复制表结构及其数据
        create table 新表名 as （select*from旧表名）
        create table 新表名 as select column1,column2,.... from旧表名
        
        create table 新表名 as select*from旧表名
        
        create table新表名 as select *from 旧表名 where 条件
        复制了表结构和该条元组
        CREATE TABLE abcde AS SELECT * FROM test WHERE 编号=1;
      - 只复制表结构（字段）
        create table 新表名 like 旧表名
        把旧表的所有字段都复制到新表但是不复制元组
        
        CREATE TABLE 新表名 AS SELECT * FROM 旧表名 WHERE 1=2 ;
        1=2 并不是代指
        CREATE TABLE 新表名 AS SELECT column1,column2,.... FROM 旧表名 WHERE 1=2 ;
        1=2 并不是代指
      - 只复制表数据
        表结构一致时
        insert into 新表名 select*from 旧表名
        
        表结构不一致
        insert into 表名（column1,column2,....）select column1,column2,.... from旧表名
  - 删除表
    - 删除表字段与元组
      - drop table 表名
        drop 是直接删除表信息，速度最快，但是无法找回数据
    - 删除表元组
      - truncate table 表名
        truncate 是删除表数据，不删除表的结构，速度排第二，但不能与where一起使用
      - delete from 表名
        delete 是删除表中的数据，不删除表结构，速度最慢，但可以与where连用，可以删除指定的行
        删除user表的所有数据delete from user;
        
        删除user表的指定记录delete from user where user_id = 1;
    - 区别
      - 相同点
        truncate和不带where子句的delete，drop都会删除表内的数据；
        
        drop，truncate都是DDL语句（数据定义语言），执行后会自动提交；
      - 不同点
        语句类型：delete语句是数据库操作语言（DML），truncate，drop是数据库定义语言（DDL）；
        
        效率：一般来说 drop > truncate> delete；
        
        是否删除表结构：truncate和delete 只删除数据不删除表结构，truncate 删除后将重建索引（新插入数据后id从0开始记起），而 delete不会删除索引（新插入的数据将在删除数据的索引后继续增加），drop语句将删除表的结构包括依赖的约束，触发器，索引等；
        
        安全性：drop和truncate删除时不记录MySQL日志，不能回滚，delete删除会记录MySQL日志，可以回滚；
        
        返回值：delete 操作后返回删除的记录数，而 truncate 返回的是0或者-1（成功则返回0，失败返回-1）；
- 数据
  - 插入语句
    - 常用插入语句
      - insert into 表名 values();
        重复则报错，不重复则插入
      - insert ignore into 表名 values();
        重复则忽略，不重复则插入
      - insert into 表名 values() on duplicate key update....
        重复则更新指定字段，不重复则插入
      - replace into 表名 values();
        重复则先删除再插入新记录，不重复则插入
      - 使用场景总结
        如果出现重复异常，希望捕获异常，则使用insert into
        据库会检查主键（PrimaryKey），如果出现重复会报错
        
        如果出现重复异常，希望保存旧纪录，忽略新纪录，则使用insert ignore into
        只有主键重复才会忽略而不报错，非主键重复成功插入
        
        如果出现重复异常，希望更新指定字段，则使用insert into … on duplicate key update
        
        如果出现重复异常，希望删除旧记录，插入新记录，则使用replace into
        replace into表示插入替换数据，需求表中有PrimaryKey，或者unique索引的话，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
        
        REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和。如果对于一个单行REPLACE该数为1，则一行被插入，同时没有行被删除。如果该数大于1，则在新行被插入前，有一个或多个旧行被删除。如果表包含多个唯一索引，并且新行复制了在不同的唯一索引中的不同旧行的值，则有可能是一个单一行替换了多个旧行。
  - 更新数据
    - 基本语法
      - UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];
        更新部分数据
        update student set age=20,gender='female' where name='tom';
        仅更新指定字段
        
        更新全部数据
        update student set age=18;
        更新所有字段的数据（部分版本不能使用）
        23:11:40 update test set age=18 Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 0.000 sec
  - 删除数据
    - 基本语法
      - DELETE FROM 表名 [WHERE 条件表达式];
        删除指定数据
        delete from student where age=14;
        
        删除全部数据
        delete from student;

概述
- 数据库概述
  - DBMS
    - DB：数据库（database）
    - DBS:数据库系统
      - DBS=DB+DBMS
      - 核心是DBMS
    - DBA:数据库管理员
    - DBMS：数据库管理系统（database manage system）
    - SQL：结构化查询语言（Structure Query Language）与数据库通讯的语言
  - 什么是数据库
    - 存储数据，管理数据的仓库
  - 常见的数据库
    - 关系型数据库， Oracle、MySQL、SQLServer、Access
      - 建立在数据的紧密关系基础之上的数据库
    - 非关系型数据库， MongoDB、Redis、Solr、ElasticSearch、Hive、HBase
      - 数据的松散关系基础之上的数据库
  - Mysql数据库
    - mysql服务端，它来处理具体数据维护，保存磁盘
    - mysql客户端，CRUD新增，修改，删除，查询
  - MySQL数据存放在哪里？
    - 配置文件my.ini中会进行默认配置
  - 数据库结构
  - 数据库基本操作
    - 创建一个数据库
      - create database 数据库名称;
    - 删除数据库MySQL命令
      - drop database db1;
    - 查询出MySQL中所有的数据库MySQL命令
      - show databases;
    - 将数据库的字符集修改为gbk MySQL命令
      - alter database db1 character set gbk;
    - 切换数据库 MySQL命令
      - use db1;
    - 查看当前使用的数据库 MySQL命令
      - select database();
  - 中文乱码
    - 如果在dos命令下执行insert插入中文数据，数据又乱码，那现在sqlYog客户端执行下面命令
      - set names gbk;
    - Mysql数据库默认字符集是lantin1，也就是以后网页中遇到的ISO8859-1，它是英文字符集，不支持存放中文。
      - create database yhdb charset utf8;
  - 关键字
    - 一、字段（列）：某一个事物的一个特征，或者说是属性
      - 某一个字段就是对“员工“控件中的属性。
    - 二、记录（元组）：事物特征的组合，可以描述一个具体的事物。
      - 元组是记录的另个一称呼。
    - 三、表：记录的组合表示同一类事物的组合
      - 表，相当于具有相似特征事物的一个集合
    - 主键：能唯一标识信息的事物
    - 外键：一个执行另个一个表的指针
      - 减少的信息冗余
- SQL简述
  - 简述
    - Structure Query Language结构化查询语言
      - 一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
        数据库管理系统可以通过SQL管理数据库；定义和操作数据，维护数据的完整性和安全性。
  - 安装MySQL数据库
    - 1, 安装路径: 不要包含中文或者特殊符号
    - 2, 字符集: 默认是latin1,不认识中文,一定会发生中文乱码现象.要改成utf-8
    - 3, 端口号: 每个软件都有唯一的端口号,其中,MySQL默认是3306
    - 4, 开机服务: MySQL/Mariadb ,要保证访问数据库时服务是开启状态
    - 5, 设置密码: root,也可以自定义
    - 6, 安装服务器端: 真正存数据的地方
    - 7, 安装客户端: 连接服务器 , 操作服务器中的数据 , , 新增删除修改查询 / CRUD ,相对来说就是对数据的查询动作多
    - 8, 客户端有两种: DOS窗口…可视化的小工具sqlyog
  - SQL的优点
    - 1、简单易学，具有很强的操作性
    - 2、绝大多数重要的数据库管理系统均支持SQL
    - 3、高度非过程化：用SQL操作数据库时大部分的工作由DBMS自动完成
  - 语句分类
    - 数据定义语言 DDL
      - Data Definition Language 用来操作数据库、表、列等
        创建数据库 create database ;
        
        删除数据库 drop database;
        
        创建、修改、重命名、删除表 create table; alter table; rename table; drop table;
        
        创建、删除索引 create index; drop index;
    - 数据查询语言DQL
      - Data Query Language用来操作数据库中表里的数据
        select
        select * from student;
        
        select id,name from student;
    - 数据操纵语言DML
      - Data Manipulation Language 数据控制语言，用来操作访问权限和安全级别
        CRUD是4个单词的首字母，CRUD分别指增加（Create）、读取查询（Retrieve）、更新（Update）和删除（Delete）这4个单词的首字母，就是增删改查。
        C：Create增加对应CREATE TBL ...；
        
        R：Retrieve查询SELECT * from TBL；
        
        U：Update修改UPDATE TBL ..SET ...；
        
        D：Delete删除DELETE FROM TBL WHERE ....；
    - 数据控制语言DCL
      - Data Control Language 数据控制语言，用来操作访问权限和安全级别
    - 事务控制语言TCL
      - Transaction Control Language
  - 客户端工具sqlyog
    - –1,连接数据库的服务器
      - 新建连接 – 输入了密码 – 确定
    - –2,创建库
      - 右键 – 创建数据库 – 输入库名和选成utf8 – 确定
    - –3,创建表
      - 选中Tables — 右键 — 新建表 — 输入字段名称/字段类型/字段长度 — 设置表名 — 成功
    - –4,添加数据
      - 可以直接在工具里录入数据,保存就行了.
      - 也可以执行SQL,刷新数据就行了.
  - 注释
    - /* 很多注释内容 */
    - #行注释内容
    - – 行注释内容，这个使用较多

数据库的数据类型
- 1.整数类型
  - 根据数值取值范围的不同MySQL 中的整数类型可分为5种，分别是TINYINT、SMALUNT、MEDIUMINT、INT和 BIGINT。
- 2.浮点数类型和定点数类型
  - 在MySQL数据库中使用浮点数和定点数来存储小数。浮点数的类型有两种：单精度浮点数类型（FLOAT)和双精度浮点数类型（DOUBLE)。而定点数类型只有一种即DECIMAL类型。
  - DECIMAL类型的取值范围与DOUBLE类型相同。
    - 但是，请注意：DECIMAL类型的有效取值范围是由M和D决定的。其中，M表示的是数据的长度，D表示的是小数点后的长度。比如，将数据类型为DECIMAL(6,2)的数据6.5243 插人数据库后显示的结果为6.52
- 3.字符串类型
  - 常用CHAR 和 VARCHAR 表示字符串。两者不同的是：VARCHAR存储可变长度的字符串。
  - 不同：
    - char为定长字符串，char(n)，n最大为255；varchar为不定长字符串（可变长度），varchar(n)，n最大长度为65535
      - 当数据为CHAR(M)类型时，不管插入值的长度是实际是多少它所占用的存储空间都是M个字节；而VARCHAR(M)所对应的数据所占用的字节数为实际长度加1
    - char(10)和varchar(10)存储abc，那它们有什么差别呢？
      - char保存10个字符，abc三个，其它会用空格补齐；而varchar只用abc四个位置
  - 汉字在数据库中占几个字节
    - UTF-8 编码：
      - 占2个字节的汉字：〇
      - 占3个字节的：基本等同于GBK，含21000多个汉字
      - 占4个字节的：中日韩超大字符集里面的汉字，有5万多个
      - 一个utf8数字或英文占1个字节
    - GBK编码：一个汉字占2个字节，英文或数字占一个字节
    - 总结：UTF-8 编码汉字占用2-4个字节，GBK编码汉字占用2个字
- 4.文本类型/TEXT 类型
  - 文本类型用于表示大文本数据，例如，文章内容、评论、详情等，它的类型分为4种
- 5.日期与时间类型
  - MySQL提供的表示日期和时间的数据类型分别是：YEAR、DATE、TIME、DATETIME 和 TIMESTAMP。
    - - date数据如何录入
        INSERT IGNORE test VALUES(7,"剑豪",'男','0000-00-00');
  - datetime和timestamp有什么区别？
    - datetime年月日时分秒，存储和显示是一样的
    - timestamp时间戳，存储的不是个日期，而是从1970年1月1日到指定日期的毫秒数
- 6.二进制类型
  - MySQL中常用BLOB存储二进制类型的数据

函数
- 1.聚合函数aggregation
  - 概念
    - 所谓聚合，就是将多行汇总成一行；其实，所有的聚合函数均如此——输入多行，输出一行。聚合函数具有自动滤空的功能，若某一个值为NULL，那么会自动将其过滤使其不参与运算。
  - 聚合函数使用规则：
    - 只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。例如，在WHERE子句中使用聚合函数是错误的。但是可以同时使用
  - 常见聚合函数
    - max（）
      - select max(age) from student;
    - min（）
      - select sname,min(age) from student;
    - sum（）求和
      - select sum(age) from student;
    - avg（）平均数
      - select avg(age) from student;
    - count
      - count() 的语义
        count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。
        count(可空字段)
        扫描全表，读到server层，判断字段可空，拿出该字段所有值，判断每一个值是否为空，不为空则累加
        
        count(非空字段)与count(主键 id)
        扫描全表，读到server层，判断字段不可空，按行累加。
        
        count(1)
        扫描全表，但不取值，server层收到的每一行都是1，判断不可能是null，按值累加。
        count(1)执行速度比count(主键 id)快的原因：从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。
        
        count(*)
        MySQL 执行count(*)在优化器做了专门优化。因为count(*)返回的行一定不是空。扫描全表，但是不取值，按行累加。
      - 性能对比结论
        count(可空字段) < count(非空字段) = count(主键 id) < count(1) ≈ count(*)
        count（）记录总数
        select count(*) from 表明 --底层优化了
        
        select count(1) from emp --效果和*一样
        
        select count(字段名) from emp --慢,只统计非NULL的
- 其他常用函数
  全部重写一遍
  - 基础函数
    - lower
      - SELECT 'ABC',LOWER('ABC') from dept; --数据转小写
    - upper
      - select upper(dname) from dept --数据转大写
    - length
      - select length(dname) from dept --数据的长度
    - substr
      - SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]
    - concat
      - select 字段,concat(字段,'123') from 表名 --拼接数据
    - replace
      - select dname,replace(dname,'a','666') X from dept --把a字符替换成666
    - ifnull
      - select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换
    - round & ceil & floor
      - round四舍五入，ceil向上取整，floor向下取整
        –直接四舍五入取整
        select comm,round(comm) from emp
        
        –四舍五入并保留一位小数
        select comm,round(comm,1) from emp
        
        –ceil向上取整,floor向下取整
        select comm,ceil(comm) ,floor(comm) from emp
    - uuid
      - SELECT UUID()
        返回uuid：a08528ca-741c-11ea-a9a1-005056c00001
    - now
      - select now() -- 年与日时分秒
      - select curdate() --年与日
      - select curtime() --时分秒
    - year & month & day
      - –hour()时 minute()分 second()秒
        select now(),hour(now()),minute(now()),second(now()) from emp ;
      - –year()年 month()月 day()日
        select now(),year(now()),month(now()),day(now()) from emp ;
    - 转义字符
      - 单引号会引起错误，进行转义即可
        select 'ab'cd' -- 单引号是一个SQL语句的特殊字符
        
        select 'ab\'cd' --数据中有单引号时,用一个\转义变成普通字符
  - 字符串函数
    - --连接函数SELECT CONCAT ()；
    - --统计长度SELECT LENGTH();
  - 数学函数
    - -- 绝对值SELECT ABS(-136);
    - -- 向下取整SELECT FLOOR(3.14);
    - -- 向上取整SELECT CEILING(3.14);
  - 时间函数
    - SELECT NOW();
    - SELECT DAY (NOW());
    - SELECT DATE (NOW());
    - SELECT TIME (NOW());
    - SELECT YEAR (NOW());
    - SELECT MONTH (NOW());
    - SELECT CURRENT_DATE();
    - SELECT CURRENT_TIME();
    - SELECT CURRENT_TIMESTAMP();
    - SELECT ADDTIME('14:23:12','01:02:01');
    - SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
    - SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
    - SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
    - SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);
    - SELECT DATEDIFF('2019-07-22','2019-05-05');

视图
- 优点
  - 提高了SQL语句的复用性
  - 结构简单
  - 屏蔽了业务表的复杂性
  - 被所有用户共享
- 缺点
  - 数据重复造成冗余数据
  - SQL无法被优化

查询
- 条件查询
  - 数据库中存有大量数据，我们可根据需求获取指定的数据。此时，我们可在查询语句中通过WHERE子句指定查询条件对查询结果进行过滤。
    - 1.使用关系运算符查询
      - select * from student where age>=17;
    - 2.使用IN关键字查询
      - IN关键字用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中，则将字段所在的记录将査询出来。
        select * from student where sid in ('S_1002','S_1003');
    - 3.使用BETWEEN AND关键字查询
      - BETWEEN AND用于判断某个字段的值是否在指定的范围之内。如果字段的值在指定范围内，则将所在的记录将查询出来
        BETWEEN AND用于判断某个字段的值是否在指定的范围之内。如果字段的值在指定范围内，则将所在的记录将查询出来
    - 4.使用空值查询
      - 在MySQL中，使用 IS NULL关键字判断字段的值是否为空值。请注意：空值NULL不同于0，也不同于空字符串
        select * from student where sname is not null;
    - 5.使用AND关键字查询
      - 在MySQL中可使用AND关键字可以连接两个或者多个查询条件。
        select * from student where age>15 and gender='male';
    - 6.使用OR关键字查询
      - 在使用SELECT语句查询数据时可使用OR关键字连接多个査询条件。在使用OR关键字时，只要记录满足其中任意一个条件就会被查询出来
        select * from student where age>15 or gender='male';
    - 7.使用LIKE关键字查询
      - MySQL中可使用LIKE关键字可以判断两个字符串是否相匹配
        7.1 普通字符串
        select * from student where sname like 'wang';
        
        7.2 含有%通配的字符串
        %用于匹配任意长度的字符串。例如，字符串“a%”匹配以字符a开始任意长度的字符串
        select * from student where sname like 'li%';查询学生姓名以li开始的记录 MySQL命令：;
        
        select * from student where sname like '%g';查询学生姓名以g结尾的记录 MySQL命令：
        
        查询学生姓名包含s的记录 MySQL命令：select * from student where sname like '%s%';
        
        7.3 含有_通配的字符串
        划线通配符只匹配单个字符，如果要匹配多个字符，需要连续使用多个下划线通配符。
        查询学生姓名以zx开头且长度为4的记录 MySQL命令：select * from student where sname like 'zx__';
    - 8.使用LIMIT限制查询结果的数量
      - 当执行查询数据时可能会返回很多条记录，而用户需要的数据可能只是其中的一条或者几条
        查询学生表中年纪最小的3位同学 MySQL命令：select * from student order by age asc limit 3;
    - 9.使用GROUP BY进行分组查询
      - GROUP BY 子句可像切蛋糕一样将表中的数据进行分组，再进行查询等操作。换言之，可通俗地理解为：通过GROUP BY将原来的表拆分成了几张小表。
        9.1 GROUP BY和聚合函数一起使用
        select count(*), departmentnumber from employee group by departmentnumber;
        
        9.2 GROUP BY和聚合函数以及HAVING一起使用
        select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;
    - 10.使用ORDER BY对查询结果排序
      - 使用ORDER BY对查询结果进行排序
        select * from student order by age asc;
        
        数字排序
        
        汉字排序
        转化为utf-8对应的整数排序
      - 参数 ASC表示按照升序排序，DESC表示按照降序排序；默认情况下，按照ASC方式排序。
    - 11.使用distinct关键字，去除重复的记录行
      - SELECT DISTINCT loc FROM dept;
    - 12.where中
      - 不能使用列别名
      - 不能单独使用聚合函数
        只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。例如，在WHERE子句中使用聚合函数是错误的。但是可以同时使用
    - 13.null
      - select * from emp where mgr is null --过滤字段值为空的
      - select * from emp where mgr is not null --过滤字段值不为空的
- 多表连接查询
  - 1.交叉连接查询
    - 交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积；比如：集合A={a,b}，集合B={0,1,2}，则集合A和B的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。所以，交叉连接也被称为笛卡尔连接
      - SELECT * FROM 表1 CROSS JOIN 表2;
        CROSS JOIN用于连接两个要查询的表，通过该语句可以查询两个表中所有的数据组合。
  - 2.内连接查询
    - 内连接(Inner Join)又称简单连接或自然连接，是一种非常常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的记录。也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中
      - SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段
        在该语法中：INNER JOIN用于连接两个表，ON来指定连接条件；其中INNER可以省略。
        select employee.ename,department.dname from department inner join employee on department.did=employee.departmentid;
  - 3.外连接查询
    - 在使用内连接查询时我们发现：返回的结果只包含符合查询条件和连接条件的数据。但是，有时还需要在返回查询结果中不仅包含符合条件的数据，而且还包括左表、右表或两个表中的所有数据，此时我们就需要使用外连接查询。外连接又分为左(外)连接和右(外)连接。
      - SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件
        外连接的语法格式和内连接非常相似，只不过使用的是LEFT [OUTER] JOIN、RIGHT [OUTER] JOIN关键字。其中，关键字左边的表被称为左表，关键字右边的表被称为右表；OUTER可以省略。
    - 在使用左(外)连接和右(外)连接查询时，查询结果是不一致的，具体如下
      - 1、LEFT [OUTER] JOIN 左(外)连接：返回包括左表中的所有记录和右表中符合连接条件的记录。
        左（外）连接查询
        左(外)连接的结果包括LEFT JOIN子句中指定的左表的所有记录，以及所有满足连接条件的记录。如果左表的某条记录在右表中不存在则在右表中显示为空。
        select class.cid,class.cname,student.sname from class left outer join student on class.cid=student.classid;
      - 2、RIGHT [OUTER] JOIN 右(外)连接：返回包括右表中的所有记录和左表中符合连接条件的记录。
        右（外）连接查询
        右(外)连接的结果包括RIGHT JOIN子句中指定的右表的所有记录，以及所有满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表将返回空值。
        select class.cid,class.cname,student.sname from class right outer join student on class.cid=student.classid;
    - inner join、left join、right join的区别？
      - INNER JOIN两边都对应有记录的才展示，其他去掉
      - LEFT JOIN左边表中的数据都出现，右边没有数据以NULL填充
      - RIGHT JOIN右边表中的数据都出现，左边没有数据以NULL填充
  - 多表联查原则
    - 小表驱动大表原则
- 子查询
  - 概念
    - 子查询是指一个查询语句嵌套在另一个查询语句内部的查询；该查询语句可以嵌套在一个 SELECT、SELECT…INTO、INSERT…INTO等语句中。在执行查询时，首先会执行子查询中的语句，再将返回的结果作为外层查询的过滤条件。在子査询中通常可以使用比较运算符和IN、EXISTS、ANY、ALL等关键字。
  - 分类
    - 1.带比较运算符的子查询
      - >、<、=、>=、<=、!=
        select * from class where cid=(select classid from student where sname='张三');
    - 2.带EXISTS关键字的子查询
      - EXISTS关键字后面的参数可以是任意一个子查询，它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会执行
        select * from class where exists (select * from student where sname='王五');
    - 3.带ANY关键字的子查询
      - ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。
        ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。
    - 4.带ALL关键字的子查询
      - ALL关键字与ANY有点类似，只不过带ALL关键字的子査询返回的结果需同时满足所有内层査询条件。
        select * from class where cid > all (select classid from student);
    - 5.子查询 in

三大范式
- 概念
  - 数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的的数据库是需要满足一些规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式,也是作为数据库设计的一些规则
- 第一范式:
  - 确保每列的原子性.
    - 指数据库表的每一列都是不可分割的基本数据线
- 第二范式:
  - 在第一范式的基础上更进一层,目标是确保表中的每列都和主键相关.
    - 如果表是单主键，那么主键以外的列必须完全依赖于主键；
    - 如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。
- 第三范式:
  - 在第二范式的基础上更进一层,目标是确保每列都和主键列直接相关,而不是间接相关. 减少字段间的依赖传递。
    - 非主键列之间不能相关依赖。

数据表的约束
- 概念
  - 为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则；这些规则常称为表的约束。
    - 针对表中字段进行限制从而保证数据表中数据的正确性和唯一性。换句话说，表的约束实际上就是表中数据的限制条件。
- 常见约束
  - 1.主键约束
    - 主键约束即primary key用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。
      - 字段名数据类型 primary key;
        id int primary key
        
        primary key(id)
    - 自增特性
    - 联合主键
  - 2.非空约束
    - 非空约束即 NOT NULL指的是字段的值不能为空
      - 字段名数据类型 NOT NULL;
        name varchar(20) not null
  - 3.默认值约束
    - 默认值约束即DEFAULT用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插人默认值；
      - 字段名数据类型 DEFAULT 默认值；
        gender varchar(10) default 'male'
    - check 约束针对语句 insert/update/replace/load data/load xml 生效；针对对应的 ignore 语句失效。
  - 4.唯一性约束
    - 唯一性约束即UNIQUE用于保证数据表中字段的唯一性，即表中字段的值不能重复出现
      - 字段名数据类型 UNIQUE;
        name varchar(20) unique
  - 5.外键约束
    - 外键约束即FOREIGN KEY常用于多张表之间的约束
      - CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)
      - ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);
        alter table class add constraint fk_class_studentid foreign key(studentid) references student05(id);
    - 数据一致性概念
      - 建立外键是为了保证数据的完整和统一性。但是，如果主表中的数据被删除或修改从表中对应的数据该怎么办呢？很明显，从表中对应的数据也应该被删除，否则数据库中会存在很多无意义的垃圾数据。
    - 删除外键
      - alter table 从表名 drop foreign key 外键名；
        alter table class drop foreign key fk_class_studentid;
    - 关于外键约束需要注意的细节
      - 1、从表里的外键通常为主表的主键
      - 2、从表里外键的数据类型必须与主表中主键的数据类型一致
      - 3、主表发生变化时应注意主表与从表的数据一致性问题
      - 4、从表的主键必须从主表中提取并且不能递增
  - 6.检查约束
    - CHECK <表达式>
      - 创建时检查约束
        CHECK(<检查约束>)
        -> CHECK(salary>0 AND salary<100),
      - 修改表时添加检查约束
        ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)
        ALTER TABLE tb_emp7 -> ADD CONSTRAINT check_id -> CHECK(id>0);
      - 删除检查约束
        ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;
        ALTER TABLE tb_emp7 -> DROP CONSTRAINT check_id

别名设置
- 概述
  - 在査询数据时可为表和字段取別名，该别名代替表和字段的原名参与查询操作。
- 1.为表取别名
  - 在查询操作时，假若表名很长使用起来就不太方便，此时可为表取一个別名，用该别名来代替表的名称。
    - SELECT * FROM 表名 [AS] 表的别名 WHERE .... ;
      - select * from student as stu;
    - SELECT * FROM 表名空格表的别名 WHERE .... ;
- 2.为字段取别名
  - 在查询操作时，假若字段名很长使用起来就不太方便，此时可该字段取一个別名，用该别名来代替字段的名称
    - SELECT 字段名1 [AS] 别名1 , 字段名2 [AS] 别名2 , ... FROM 表名 WHERE ... ;
      - select name as '姓名',id from student;
    - SELECT 字段名1 空格别名1 , 字段名2 空格别名2 , ... FROM 表名 WHERE ... ;
  - 取别名后不能用where

表的关联关系association
- MySQL中数据表的三种关联关系
  - 多对一
    - many to one
  - 多对多
    - many to many
  - 一对一
    - one to one
- 1.关联查询
  - select * from student where classid=(select cid from class where cname='Java');
- 2.关于关联关系的删除数据
  没写清楚

事务与索引
- 事务transaction
  - 什么是事务
    必问
    - 数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。
      - 事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。
      - mysql一条语句一个事务
  - 事务4个特性ACID
    必问
    - 事务是必须满足4个条件（ACID）：
      - 原子性（Atomicity）：又称不可分割性，一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
        事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
      - 一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
        这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
      - 隔离性（lsolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
        隔离级别
        读未提交（Read uncommitted）安全性最差,可能发生并发数据问题,性能最好
        
        读提交（read committed）安全性好，效率较差 Oracle默认的隔离级别
        
        可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般
        
        串行化（Serializable）表级锁，读写都加锁，效率低下,安全性高,不能并发
      - 持久性(Durability)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
  - 事务的使用
    必问
    - 默认情况下MySQL每执行一条SQL语句，都是一个单独的事务
    - 如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。或rollback（回滚事务）。
      - 开启事务：start transaction；/ begin
      - 执行多条SQL(增删改)
      - 结束事务：commit（提交事务）
  - 事务处理
    - 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
    - 事务处理可以用来维护数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
    - 事务用来管理 insert、update、delete 语句，因为这些操作才会“破坏”数据，查询select语句是不会的
    - MySQL默认数据库的事务是开启的，执行SQL后自动提交。
    - MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后，再手动提交。
- 索引index
  - 定义
    - 排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。
    - 索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。
  - 分类
    - 单值索引：一个索引只包括一个列，一个表可以有多个列
    - 唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
    - 复合索引：一个索引同时包括多列
  - 创建索引
    - 查看索引，主键会自动创建索引
      - show index from dept;
        创建普通索引
        create index 索引名字 on 表名(字段名);
        
        创建唯一索引
        CREATE UNIQUE INDEX 索引名 ON 表名(字段名)
        
        创建复合索引
        CREATE INDEX 索引名 ON 表名 (字段1, 字段2)
      - explain 查询语句
        该条语句索引
    - 删除索引
      - alter table dept drop index fuhe_index
  - 索引扫描类型
    - ALL 全表扫描，没有优化，最慢的方式
    - index 索引全扫描，其次慢的方式
    - range 索引范围扫描，常用语<，<=，>=，between等操作
    - ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
    - eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
    - const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况
    - null MySQL不访问任何表或索引，直接返回结果
  - 最左特性
  - 为何索引快？
    - 明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。
  - 总结
    面试
    - 优点:
      - 索引是数据库优化
      - 表的主键会默认自动创建索引
      - 每个字段都可以被索引
      - 大量降低数据库的IO磁盘读写成本，极大提高了检索速度
      - 索引事先对数据进行了排序，大大提高了查询效率
    - 缺点:
      - 索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
      - 索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
      - 虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
      - 随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

把csdn当日记本的菜鸡

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库基础

数据库/数据表基本操作数据库 MySql用户管理修改密码:首先在DOS 下进入mysql安装路径的bin目录下，然后键入以下命令:mysqladmin -uroot -proot; 增加用户：格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by ‘密码’ 数据库基本操作启动mysql服务器net start mysql 关闭net stop mysql 进入 mysql -h 主机地址 -u 用户名－p 用
复制链接

扫一扫

专栏目录