数据库学习笔记

mysql语法松散

orcal语法严格

sql

数据库需要：

字段名 数据类型 相关约束

DQL(数据查询语言) 查询 select都是DQL

DML(数据操作语言)insert delete upda对表数据进行增删改

DDL(数据定义语言)creat drop alter对表结构进行增删改

TCL(事务控制语言)commit提交事务 rollback回滚事务

DCL(数据控制语言)grant授权 rovoke撤销权限

初始化数据使用source命令、source在DOS下执行数据库文件。.sql文件

mysqle>source 数据库文件目录

null不是一个值，不能用=衡量，为空。必须使用is null或者is not null进行操作

sql语句

查询 ：

select 字段名1，字段名2，.......from表名；

sql语句以分号结束，并且不区分大小写

并且字段可以参与数***算

中文需要加 ‘ ’ 不能直接写中文，标准sql语句 字符串使用单引号括起来

查询全部字段 select * from emp；实际开发不建议使用*，效率较低

条件查询 ：

select字段......，字段 from 表名 where 条件；

执行顺序，先执行from在执行where在执行select

between and

求的是区间selsct enams， sal from emp where sal between 1000 and 3000 （求的是emp表enams字段中大于等于一千小于等于三千的）

使用时必须左小右大，是闭区间

in相当于or

模糊查询like

selsct enams from emp where ename like'%数据%'

_表示任意字符，需要查询 _时需要在 _ 前加/

%代表任意多个字符

排序：order by

默认表示升序 ，asc表示升序，desc表示降序

order by 字段 asc/desc；

order by 字段 asc/desc，字段asc/desc；

越靠前字段越能起主导作用

找出工作岗位是sal的员工，并按照工资降序排列

select ename， job，sal from emp where job=‘sal’ order by sal dose：

排序输出一般在最后执行

分组函数

count 计数

sum求和

avg平均值

max 最大值

min 最小值

一共五个，又叫多行处理函数//输入多行，输出一行

分组函数自动忽略null

单行处理函数 //输入一行输出一行。

只要在数据库中有null出现，无论进行什么运算操作，值只会为null

ifnull（）空处理函数 属于单行处理函数

分组函数不可直接出现在where子句中

count *与count(具体字段)的差别

count *不是查询某个字段中数据的个数而是查出总记录条数

count（具体字段）表示的是字段中不为null的数据数量

分组函数也可以组合运用

group by与having

group by：按照某个字段或者某些字段进行分组

having：having是对分组之后的数据进行再次过滤

select xxx(max(sal)薪资最大值)(查询条件) from xxx(查询的表) group by(进行分组) xxx(按照这个进行分组):

分组函数一般与group by联合使用，并且分组函数一般在group by执行结束后才会执行，sql语句如果没有group by整张表自成一组。

当一个语句中有group by时，select后只能跟分组函数与参与分组的字段

select xxx(job，avg(sal))求工作岗位平均薪资)(查询条件) from xxx(查询的表) group by(进行分组) xxx(按照这个进行分组):

where与having相比效率较高

写了group by才会出现having

一个完整的DQL语句

select(4.选出数据)。。。。from()。。。where(1.首先执行where语句过滤原始数据)。。。group by(2.进行分组)。。。having(3.操作分组数据)。。。orde by(排序)。。。

写了group by才会出现having,能在where中过滤的数据尽量在where中过滤，效率较高

distinct关于查询结果集的去重，只能出现在所有字段的最前面

多表链接查询

一个业务分为多张表，在多张表中进行查询数据

分类 SQL92

     SQL99

链接方式划分

内连接：等值连接，非等值连接，自连接、

外连接：左外连接，右外链接，全连接（全连接使用较少）

链接查询方右一种现象叫做（笛卡尔积现象）

select (表一字段)(表二字段) from (表一)(表二)

表示在对两张表进行链接查询时，没有对任何条件进行限制，最终查询结果条数是两张表的记录条数的乘积

关于表的别名

select (e.表一字段)(m.表二字段) from (e.表一)(m.表二)

执行效率高，可读性好

如何避免笛卡尔积现象

加条件进行过滤。不会减少匹配次数，只不过显示出来的是有效数据

select (e.表一字段)(m.表二字段)

from (e.表一)(m.表二)

where (e.条件=(m.条件)

内连接之等值连接

select (e.表一字段)(m.表二字段)

from (e.表一)

join (m.表二)

on (e.条件)=(m.条件)

连接与where分离

内连接之非等值连接,

连接条件中的关系是非等量关系

select (e.表一字段)(e.表一字段)(m.表二字段)

from (e.表一)

join (m.表二)

on (e.条件）between (m.条件) and(m.条件)

自连接

一张表看做两张表，自己连接自己

select

(a.xxx)as‘信息段 ’ (b.xxx)as‘信息段’

from

    emp a

inner join

     emp b

on a.xxx=b.xxx（可以等量关系也可以非等量关系）

a表的xxx等于b表的xxx

外连接

与内连接区别

内连接 AB两孩子那个表没有主副，两张表是平等的

外连接 AB两张表有一张表是主表有一个是附表，主要查询主表，顺带查询附表。当附表中的数据没有和主表中的数据匹配，附表自动生成null

左外连接，表示左边的表是主表

右外连接，表示右边的表是主表

...A join B join C on。。。

三张表进行查询

（ select

e.表一 ，d.表二， c.表三

from

表名 e

join

表名 d

on

e.表一字段=d.表二字段

join

表名 c

on

e.表一 between d.表二 and c.表三 ；）

left join 表名e1

on

e.字段=e1.字段

子查询

select中嵌套select语句，嵌套的select是字查询

应用

select 。。。。。（select）

from 。。。。。（select）

where 。。。。。（select）

where

from后嵌套子查询

select

union可以将查询结果相加显示（两个不相干的表也可以）

limit 可以完成分页查询显示

mysql特有，不通用（oracle中有一个相同机制叫rownum）

limit取结果中的部分数据

语法机制

limit startIndex length （startIndex表示起始位置 length表示取几个 ）

limit（pageNO-1）*pagesize，pagesize

建表

1.

creat table 表名（

  字段名1  数据类型

   字段名2  数据类型

 。。。。。

）；

数据类型 int 整型 bigint长整型 float浮点型 char定长字符串 varchar不定长字符串 data日期类型 BLOB二进制大对象 CLOB字符型大队象，可以存储4g的字符串

char与varchar如何选择

字段数据长度不变化时选择char变化或者不确定时选varchar

BLOL与CLOB类型应用

电影等

insert语句插入数据

语法要求：insert into (表名) (字段名1，字段名2.。。。) values (值1，值2.。。。)

1. 可以同时插入多行

语法要求：insert into (表名) (字段名1，字段名2.。。。) values (),(),()

(字段名1，字段名2.。。。)//字段名可以省略、

5.表的复制

creat table 表名 as select 语句

将查询结果复制到一张表中

insert into deptl select *from dept

6.删除数据

delete from 表名 where 条件

删除表  truncate  table  表;//表被截断不可回滚

7.update

    update 表名 set 字段名1=值1,字段名2=值2... where 条件;

注意：没有条件整张表数据全部更新。

约束

对表中的字段添加约束，为了保证表数据的合法性，有效性，完整性。

非空约束 not null

唯一约束 unique（唯一性约束字段具有唯一性，不能重复，但可以为null）

主键约束 primary key

外键约束 foreign key

检查约束 check(oracle数据库有，mysql没有并且不支持)

约束直接加在字段后

create table 表名（

                字段名   数据类型   约束）

主键约束

主键相关的术语？

主键约束 : primary key

主键字段 : id字段添加primary key之后，id叫做主键字段
主键值 : id字段中的每一个值都是主键值。

主键有什么作用？

• 表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
• 主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样。）

主键的分类？

根据主键字段的字段数量来划分：

单一主键（推荐的，常用的。）

复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）

根据主键性质来划分：

自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）

业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）

          最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要

          随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

一张表的主键约束只能有1个。（必须记住）

外键约束

顺序要求：

    删除数据的时候，先删除子表，再删除父表。

    添加数据的时候，先添加父表，在添加子表。

    创建表的时候，先创建父表，再创建子表。

    删除表的时候，先删除子表，在删除父表。

关于外键约束的相关术语：

外键约束: foreign key

外键字段：添加有外键约束的字段

外键值：外键字段中的每一个值。

外键值可以为NULL？

外键可以为NULL。

外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？

注意：被引用的字段不一定是主键，但至少具有unique约束

存储引擎

什么是存储引擎呢？

    存储引擎这个名字只有在mysql中存在。（Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，
    就是“表的存储方式”）

    mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。
    每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

查看当前mysql支持的存储引擎？

    show engines \G

常见的存储引擎？

        Engine: MyISAM
              Support: YES
              Comment: MyISAM storage engine
        Transactions: NO
                     XA: NO
          Savepoints: NO

        MyISAM这种存储引擎不支持事务。
        MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。
        MyISAM采用三个文件组织一张表：
            xxx.frm（存储格式的文件）
            xxx.MYD（存储表中数据的文件）
            xxx.MYI（存储表中索引的文件）
        优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
        缺点：不支持事务。

    -----------------------------------------------------------------------------

              Engine: InnoDB
              Support: DEFAULT
              Comment: Supports transactions, row-level locking, and foreign keys
        Transactions: YES
                     XA: YES
          Savepoints: YES

        优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。

        表的结构存储在xxx.frm文件中
        数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。
        这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。
        InnoDB支持级联删除和级联更新。

    -------------------------------------------------------------------------------------

                Engine: MEMORY
              Support: YES
              Comment: Hash based, stored in memory, useful for temporary tables
        Transactions: NO
                     XA: NO
          Savepoints: NO

        缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
        优点：查询速度最快。
        以前叫做HEPA引擎。

事务

一个事务是一个完整的逻辑单元不可再分

两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。

要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

3.2、和事务相关的语句只有：DML语句。（insert delete update）

    为什么？因为它们这三个语句都是和数据库表当中的“数据”相关的。
    事务的存在是为了保证数据的完整性，安全性。

3.3、假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？
    不需要事务。
    但实际情况不是这样的，通常一个“事儿（事务【业务】）”需要多条DML语句共同联合完成。

3.4、事务的特性？
    事务包括四大特性：ACID
    A: 原子性：事务是最小的工作单元，不可再分。
    C: 一致性：事务必须保证多条DML语句同时成功或者同时失败。
    I：隔离性：事务A与事务B之间具有隔离。
    D：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。

3.5、关于事务之间的隔离性
    事务隔离性存在隔离级别，理论上隔离级别包括4个：
        第一级别：读未提交（read uncommitted）
            对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
            读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
        第二级别：读已提交（read committed）
            对方事务提交之后的数据我方可以读取到。
            这种隔离级别解决了: 脏读现象没有了。
            读已提交存在的问题是：不可重复读。
        第三级别：可重复读（repeatable read）
            这种隔离级别解决了：不可重复读问题。
            这种隔离级别存在的问题是：读取到的数据是幻象。
        第四级别：序列化读/串行化读（serializable） 
            解决了所有问题。
            效率低。需要事务排队。

        oracle数据库默认的隔离级别是：读已提交。
        mysql数据库默认的隔离级别是：可重复读。

3.6、演示事务
    * mysql事务默认情况下是自动提交的。
        （什么是自动提交？只要执行任意一条DML语句则提交一次。）怎么关闭自动提交？start transaction;

    * 准备表：
        drop table if exists t_user;
        create table t_user(
            id int primary key auto_increment,
            username varchar(255)
        );

    * 演示：mysql中的事务是支持自动提交的，只要执行一条DML，则提交一次。
        mysql> insert into t_user(username) values('zs');
        Query OK, 1 row affected (0.03 sec)

        mysql> select * from t_user;
        +----+----------+
        | id | username |
        +----+----------+
        |  1 | zs       |
        +----+----------+
        1 row in set (0.00 sec)

        mysql> rollback;
        Query OK, 0 rows affected (0.00 sec)

        mysql> select * from t_user;
        +----+----------+
        | id | username |
        +----+----------+
        |  1 | zs       |
        +----+----------+
        1 row in set (0.00 sec)

    * 演示：使用start transaction;关闭自动提交机制。
        mysql> start transaction;
        Query OK, 0 rows affected (0.00 sec)

        mysql> insert into t_user(username) values('lisi');
        Query OK, 1 row affected (0.00 sec)

        mysql> select * from t_user;
        +----+----------+
        | id | username |
        +----+----------+
        |  1 | zs       |
        |  2 | lisi     |
        +----+----------+
        2 rows in set (0.00 sec)

        mysql> insert into t_user(username) values('wangwu');
        Query OK, 1 row affected (0.00 sec)

        mysql> select * from t_user;
        +----+----------+
        | id | username |
        +----+----------+
        |  1 | zs       |
        |  2 | lisi     |
        |  3 | wangwu   |
        +----+----------+
        3 rows in set (0.00 sec)

        mysql> rollback;
        Query OK, 0 rows affected (0.02 sec)

        mysql> select * from t_user;
        +----+----------+
        | id | username |
        +----+----------+
        |  1 | zs       |
        +----+----------+
        1 row in set (0.00 sec)
        --------------------------------------------------------------------
        mysql> start transaction;
        Query OK, 0 rows affected (0.00 sec)

        mysql> insert into t_user(username) values('wangwu');
        Query OK, 1 row affected (0.00 sec)

        mysql> insert into t_user(username) values('rose');
        Query OK, 1 row affected (0.00 sec)

        mysql> insert into t_user(username) values('jack');
        Query OK, 1 row affected (0.00 sec)

        mysql> select * from t_user;
        +----+----------+
        | id | username |
        +----+----------+
        |  1 | zs       |
        |  4 | wangwu   |
        |  5 | rose     |
        |  6 | jack     |
        +----+----------+
        4 rows in set (0.00 sec)

        mysql> commit;
        Query OK, 0 rows affected (0.04 sec)

        mysql> select * from t_user;
        +----+----------+
        | id | username |
        +----+----------+
        |  1 | zs       |
        |  4 | wangwu   |
        |  5 | rose     |
        |  6 | jack     |
        +----+----------+
        4 rows in set (0.00 sec)

        mysql> rollback;
        Query OK, 0 rows affected (0.00 sec)

        mysql> select * from t_user;
        +----+----------+
        | id | username |
        +----+----------+
        |  1 | zs       |
        |  4 | wangwu   |
        |  5 | rose     |
        |  6 | jack     |
        +----+----------+
        4 rows in set (0.00 sec)

    * 演示两个事务，假如隔离级别
        演示第1级别：读未提交
            set global transaction isolation level read uncommitted;
        演示第2级别：读已提交
            set global transaction isolation level read committed;
        演示第3级别：可重复读
            set global transaction isolation level repeatable read;

    * mysql远程登录：mysql -h192.168.151.18 -uroot -p444

4、索引

4.1、什么是索引？有什么用？
    索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。
    在数据库方面，查询一张表的时候有两种检索方式：
        第一种方式：全表扫描
        第二种方式：根据索引检索（效率很高）

    索引为什么可以提高检索效率呢？
        其实最根本的原理是缩小了扫描的范围。

    索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中
    的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改
    这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。

    添加索引是给某一个字段，或者说某些字段添加索引。

    select ename,sal from emp where ename = 'SMITH';
    当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。
    当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。

4.2、怎么创建索引对象？怎么删除索引对象？
    创建索引对象：
        create index 索引名称 on 表名(字段名);
    删除索引对象：
        drop index 索引名称 on 表名;

4.3、什么时候考虑给字段添加索引？（满足什么条件）
    * 数据量庞大。（根据客户的需求，根据线上的环境）
    * 该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
    * 该字段经常出现在where子句中。（经常根据哪个字段查询）

4.4、注意：主键和具有unique约束的字段自动会添加索引。
    根据主键查询效率较高。尽量根据主键检索。

4.5、查看sql语句的执行计划：
    mysql> explain select ename,sal from emp where sal = 5000;
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

    给薪资sal字段添加索引：
        create index emp_sal_index on emp(sal);

    mysql> explain select ename,sal from emp where sal = 5000;
    +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
    | id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
    +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
    |  1 | SIMPLE      | emp   | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 | Using where |
    +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

4.6、索引底层采用的数据结构是：B + Tree

4.7、索引的实现原理？
    通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，
    最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率
    是最高的。
        select ename from emp where ename = 'SMITH';
        通过索引转换为：
        select ename from emp where 物理地址 = 0x3;

4.8、索引的分类？
    单一索引：给单个字段添加索引
    复合索引: 给多个字段联合起来添加1个索引
    主键索引：主键上会自动添加索引
    唯一索引：有unique约束的字段上会自动添加索引
    ....

4.9、索引什么时候失效？
    select ename from emp where ename like '%A%';
    模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

5、视图(view)

5.1、什么是视图？
    站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）。

5.2、怎么创建视图？怎么删除视图？
    create view myview as select empno,ename from emp;
    drop view myview;

    注意：只有DQL语句才能以视图对象的方式创建出来。

5.3、对视图进行增删改查，会影响到原表数据。（通过视图影响原表数据的，不是直接操作的原表）
可以对视图进行CRUD操作。

5.4、面向视图操作？
    mysql> select * from myview;
    +-------+--------+
    | empno | ename  |
    +-------+--------+
    |  7369 | SMITH  |
    |  7499 | ALLEN  |
    |  7521 | WARD   |
    |  7566 | JONES  |
    |  7654 | MARTIN |
    |  7698 | BLAKE  |
    |  7782 | CLARK  |
    |  7788 | SCOTT  |
    |  7839 | KING   |
    |  7844 | TURNER |
    |  7876 | ADAMS  |
    |  7900 | JAMES  |
    |  7902 | FORD   |
    |  7934 | MILLER |
    +-------+--------+

    create table emp_bak as select * from emp;
    create view myview1 as select empno,ename,sal from emp_bak;
    update myview1 set ename='hehe',sal=1 where empno = 7369; // 通过视图修改原表数据。
    delete from myview1 where empno = 7369; // 通过视图删除原表数据。

5.5、视图的作用？
    视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员
    只对视图对象进行CRUD。

6、DBA命令

6.1、将数据库当中的数据导出
    在windows的dos命令窗口中执行：（导出整个库）
        mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p333

    在windows的dos命令窗口中执行：（导出指定数据库当中的指定表）
        mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot –p123

6.2、导入数据
    create database bjpowernode;
    use bjpowernode;
    source D:\bjpowernode.sql

7、数据库设计三范式（重点内容，面试经常问）

7.1、什么是设计范式？
    设计表的依据。按照这个三范式设计的表不会出现数据冗余。

7.2、三范式都是哪些？

    第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。

    第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。
        多对多？三张表，关系表两个外键。
        t_student学生表
        sno(pk)        sname
        -------------------
        1                张三
        2                李四
        3                王五

        t_teacher 讲师表
        tno(pk)        tname
        ---------------------
        1                王老师
        2                张老师
        3                李老师

        t_student_teacher_relation 学生讲师关系表
        id(pk)        sno(fk)        tno(fk)
        ----------------------------------
        1                1                3
        2                1                1
        3                2                2
        4                2                3
        5                3                1
        6                3                3

    第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。
        一对多？两张表，多的表加外键。
        班级t_class
        cno(pk)            cname
        --------------------------
        1                    班级1
        2                    班级2

        学生t_student
        sno(pk)            sname                classno(fk)
        ---------------------------------------------
        101                张1                1
        102                张2                1
        103                张3                2
        104                张4                2
        105                张5                2

    提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。

7.3、一对一怎么设计？

    一对一设计有两种方案：主键共享
        t_user_login  用户登录表
        id(pk)        username            password
        --------------------------------------
        1                zs                    123
        2                ls                    456

        t_user_detail 用户详细信息表
        id(pk+fk)    realname            tel            ....
        ------------------------------------------------
        1                张三                1111111111
        2                李四                1111415621

    一对一设计有两种方案：外键唯一。
        t_user_login  用户登录表
        id(pk)        username            password
        --------------------------------------
        1                zs                    123
        2                ls                    456

        t_user_detail 用户详细信息表
        id(pk)       realname            tel                userid(fk+unique)....
        -----------------------------------------------------------
        1                张三                1111111111        2
        2                李四                1111415621        1