数据库 -- day01

一、数据库简介

1、为什么需要数据库

• 磁盘->高级缓存->寄存器->CPU

  • 数据存储在内存中，但是内存大小有限、不可能存储所有数据，并且掉电后数据丢失

• 为了让程序在关机重启后数据依然可以使用，必须把数据保存在磁盘文件中

• 随着程序功能越来越复杂、数据量越来越多、数据关系也越来越复杂，从文件中读写数据需要大量复杂、重复性高的操作，因此需要执行非常复杂的逻辑

• 不同程序、系统访问文件的操作不同，意味着读写文件的代码很难做到代码复用

• 所以程序员非常需要一个统一的快速的访问磁盘数据的工具

• 使用数据库本质就是管理磁盘数据，不需要程序员自己管理，通过数据库提供接口进行操作即可，至于数据在磁盘中是如何保存、查找与程序员无关

2、什么是数据库 DB、DBMS

• 数据库指的是管理数据的软件、工具，是一个文件系统，而不是额外存储数据的仓库

3、数据库的类型

• 层次型：以上下级的层次关系来组织数据的一类数据库，类似倒悬树结构，早期的数据库采用

• 网状型：通过网状关系连接组织数据，形成一张数据网，复杂度高

• 关系型：把数据看作一张二维表，由行和列组成，这也是普通人最容易理解的一种数据组织方式，因此变成目前主流

• 非关系型key-value型：是一种内存数据库，适合存储高频访问的数据、对时间有要求的数据

• 公司中一般是关系型、非关系型结合使用：大量数据存储在关系型数据库中，从中提取经常查找的数据存储在非关系型数据库中，方便查找、使用，提升效率

4、主流的关系型数据库

• 商用型：Oracle(甲骨文)、DB2(IBM)、SQL Sever(微软)

• 开源型：MySQL、mariaDB

• 桌面型：Access(微软) 通过界面操作

• 嵌入式：SQLite、适合嵌入式移动设备使用的小型数据库

二、SQL语言简介

1、什么是SQL

• 结构化查询语言的缩写，是数据库标准委员会制定，用于访问、控制数据库的统一语言，所有的数据库都支持SQL语言，所以我们只需要学习SQL语句基本可以操作所有数据库

• 虽然都支持SQL语言，但是不同的数据库厂商对SQL的支持程度不同，有的还增加新的语言，把各个数据库特有语法称为SQL方言

2、SQL 语句分类：

• 数据控制语句DCL:

  • 用于用户权限的赋予、回收

• 数据定义语句DDL:

  • 用于建立、修改、删除数据库对象(数据库、表、视图等)

• 数据操作语句DML:

  • 用于改变表中数据(增、删、改)

• 数据查询语句DQL:

  • 根据不同的条件来查询同一张、不同的表中的数据

• 事务控制语句TCL:

  • 用于维护数据表的一致性的语句

3、SQL通用语法

• SQL语句可以单行或者换行书写，以分号结尾

• 可以使用合适的空格、缩进来提高可读性

• SQL的关键字不区分大小写，一般大写，但是标识符(表名、字段名等)是区分大小写

• -- 单行注释

• /* 多行注释 */

4、SQL语句中的基础数据类型

• int

• double

• 字段名 double(总位数,小数点后保留几位)

• datetime

• date

• CHAR 0-­255 bytes 定长字符串
  存储性能更优，浪费空间

• VARCHAR 0-­65535 bytes 变长字符串
  存储性能较差、节约空间

• 字符、字符串 使用 单、双引号都可以

三、MySQL数据库登录方式

1、本地登录

• mysql -u 用户名 -p 密码 方便但有泄漏密码风险

• mysql -u 用户名 -p 更安全

2、远程登录

• 先通过ssh或者telent 远程登录具有MySQL服务器 再通过mysql命令登录MySQL数据库

3、客户端登录

• 例如:MySQL WorkBench的登录(Ctrl+Enter执行单行)

无论哪种登录，登录后都需要先 use 数据库名; 选择操作的数据库

四、数据定义语句DDL

1、创建表

create table 表名(字段名 字段类型,...);
create table Student(name char(20),sex char,age int,id int,addr varchar(100));

-- 查看当前数据库中所有表名 
show tables; 

-- 查看表结构 
desc 表名; 

-- 查看创建该表的SQL语句 
show create table 表名;

2、复制表结构创建

create table 新表名 like 旧表名; --不复制数据

3、修改表结构

--修改表名 
rename table 旧表名 to 新表名; 

--增加列 
alter table 表名 add(新字段名 类型,...); 

--修改列 
alter table 表名 modify 原字段名 新类型名; 
alter table 表名 modify 原字段名 新字段名 新类型名; 

--删除列 
alter table 表名 drop 字段名;

4、删除表

drop table 表名; 
--数据、结构都删除 

truncate 表名; 
--删除数据，保留表结构

五、数据操作语句DML

1、插入数据

-- 按照字段顺序插入数据 
insert into 表名 values(数据); 
-- 必须按照字段顺序，若想要插入空值，写null 

-- 指定字段插入数据 
insert into 表名(字段名) values(数据);

2、删除数据

delete from 表名 [where 条件]; 
delete from student where id=1001; 
delete from student where name='xixi'; 
-- SQL中所有数据比较可以直接用 = 进行
-- 注意：如果不写where条件，则整张表删除

3、修改数据

update 表名 set 字段名=新数据 [where 条件];
update student set id=8888 where name="xixi1";  
-- 注意：如果不写where条件，则整张表删全部修改
--注意：Work Bench中需要把 Edit->Prefrence->SQL Editor->最后选项不打钩->重启->才能使用update、delete语句

六、数据查询语句DQL:

select 字段名1,字段名2,... from 表名; 
-- * 在SQL中是通配符，能够通配代表所有字段

七、事务控制语句TCL:

1、commit 提交

• 默认下，在数据库中，当某一个用户操作了某一条数据时，只有它自己能够查询到结果，其它用户不能立即查看到变化，只有执行了commit语句后，其他用户才能看到变化

• 例如：A向B转账1000块：

  • A -1000 B +1000 要么同时成功、要么同时失败
    if(A余额>=1000)
    {
        A -= 1000;
        if(检查B是否存在)
        {
            B += 1000;
            commit;
        }
        else
        {
            取消;
        }
    }

2、MySQL中的自动提交

-- 查看当前登录用户是否开启自动提交
show variables like 'autocommit';
-- 设置当前用户的自动提交
set session autocommit=0 | 1;
-- 注意：MySQL中自动提交默认都是开启的，通过SQL语句关闭只能当前临时生效，如果想要长期设置，需要修改配置文件

3、rollback

• 当用户对数据修改，如果发现有错误，可以使用rollback返回上一次commit时的数据状态

• 使用commit的好处：

  1. 让数据库批量执行操作，提高数据库的操作效率

  2. 降低磁盘的读写次数，提高磁盘寿命

4、设置保存点 savepoint

• 只使用rollback取消操作，会把全部的操作取消返回上一次commit状态，这样可能会浪费一部分有意义但不想commit的操作，可以设置savepoint，让rollback返回到指定的保存点

savepoint 标签名;        
insert into student 
    values('haha2','w',null,1002,null);
savepoint s1;
insert into student 
    values('haha2','w',null,1003,null);
savepoint s2;
insert into student 
    values('haha2','w',null,1004,null);
savepoint s3;
rollback to savepoint s2;   
-- 返回到s2时的状态

八、SQL脚本

• SQL脚本就是由SQL语句组成的文本文件，一般是创建表、插入数据等操作语句为主，不需要执行权限也可以执行

• 如何执行SQL脚本

  • 方法1：在MySQL中执行 source SQL脚本文件名;

  • 方法2：在客户端中打开 SQL脚本 全部执行

• s_emp 员工表字段：

  id                      员工ID
  last_name               姓
  first_name              名
  userid                  用户名   
  start_date              入职时间
  comments                备注
  manager_id              领导ID
  title                   职位
  dept_id                 部门
  salary                  工资
  commission_pct          佣金
  

九、排重 distinct

• 在查询时，一个字段的值可能有多个重复的数据，通过distinct可以排除重复数据

select distinct 字段名,... from 表名; 
-- select distinct title from s_emp;

• 练习：查询该公司目前有哪些岗位，分别属于哪个部门，不能重复显示

  select distinct dept_id,title from s_emp;
  

• 注意：

  1. distinct只能在select语句中使用

  2. distinct必须在所有字段名前面

  3. 如果distinct对多个字段排重，当所有字段的内容完全一样时，才会被排重

十、算术运算符 + - * / %

• select语句中，可以对表中的数据直接进行算术运算，并且把运算结果查询出来

  select 字段名 +-*% ... from 表名; 
  

• 练习：查询所有员工补贴200块后的每个人的年薪

  select (salary+200)*12,salary from s_emp;
  

• 注意：如果想要改变运算符优先级可以使用小括号

十一、where子句

select 字段 from 表名 where 子句

• 在where子句中可以使用关系运算符、逻辑运算符，当子句的条件为真的数据才会显示对应的字段数据

where 子句可以有：

1、关系运算符

= != > < >= <=
注意：因为在SQL中无需、也不能定义变量，因此=运算符只能用来判断关系是否相等
练习：公司打算给工资低于1000的员工补贴200块，显示补贴员工的名字和补贴前后工资

select first_name,salary,salary+200 
    from s_emp
        where salary < 1000;

2、逻辑运算符

and or not

&& || ! 还可以继续使用

练习：查询属于部门41的工资低于1000的员工的信息

select *
    from s_emp
        where dept_id=41 and salary<1000;

注意：在SQL中 not(!) 比关系运算符的优先级要低

3、特殊条件

is null\is not null:

对于null状态，只能通过 is null、is not null 来判断该字段是否为空状态，在SQL中null不是一个特定的值，而是一种状态，因此不能直接使用关系运算符比较

select * from 表名 where 字段 is null;
select * from 表名 where 字段 is not null;

between a and b

当字段的值出现在[a,b]之间时为真，如果使用关系、逻辑配合也可以达到同样效果

select * from 表名 where 字段 between a and b;

in( a , b , c , ... )

当字段的值出现在in后面的数值列表中，为真

select * from 表名 where 字段 in(a,b,c,...);

like ' %str '

进行模糊查询，%可以通配任意多个字符 -可以通配任意1个字符
注意：一般字符型字段比较适合模糊查询，但是数值型也可以，数据库会自动转换成字符串比较
例子：查询名字以M开头的员工姓名

select first_name
    from s_emp
        where first_name like "M%";