MySQL学习01

一、数据库简介
1、为什么需要数据库
1、磁盘->高级缓存->寄存器->CPU
数据存储在内存中，但是内存大小有限、不可能存储所有数据，并且掉电后数据丢失
2、为了让程序在关机重启后数据依然可以使用，必须把数据保存在磁盘文件中
3、随着程序功能越来越复杂、数据量越来越多、数据关系也越来越复杂，从文件中读写数据需要大量复杂、重复性高的操作，因此需要执行非常复杂的逻辑
4、不同程序、系统访问文件的操作不同，意味着读写文件的代码很难做到代码复用
5、所以程序员非常需要一个统一的快速的访问磁盘数据的工具
6、使用数据库本质就是管理磁盘数据，不需要程序员自己管理，通过数据库提供接口进行操作即可，至于数据在磁盘中是如何保存、查找与程序员无关
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 表名 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 子句可以有：
a、关系运算符
= != > < >= <=
注意：因为在SQL中无需、也不能定义变量，因此=运算符只能用来判断关系是否相等
练习：公司打算给工资低于1000的员工补贴200块，显示补贴员工的名字和补贴前后工资
select first_name,salary,salary+200
from s_emp
where salary < 1000;
b、逻辑运算符
and or not
&& || ! 还可以继续使用
练习：查询属于部门41的工资低于1000的员工的信息
select *
from s_emp
where dept_id=41 and salary<1000;
注意：在SQL中 not(!) 比关系运算符的优先级要低

c、特殊条件
    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%";