MySQL数据库基础

数据库的一些概念题：

DB DBMS 和DBS三者之间的关系

DBS(数据库系统) 包含DB(数据库)和DBMS(数据库管理系统)

DB：是指datebase（数据库）
DBS:是指datebase systerm (数据库系统）
DBMS:是指datebase mangement systerm（数据库管理系统）
区别：数据库就是存放数据的仓库，数据库管理系统就是用来管理数据库的，其中数据库系统是一个通称，包括数据库、数据库管理系统、数据库管理人员等的统称，是最大的范畴。

常见存储引擎的类型

需改表alter

4.修改表结构：
        语法：
            1. 修改表名
                ALTER TABLE 表名
                                    RENAME 新表名;
            2. 增加字段
                  ALTER TABLE 表名
                                      ADD 字段名  数据类型 [完整性约束条件…],
                                      ADD 字段名  数据类型 [完整性约束条件…];
                  ALTER TABLE 表名
                                      ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
                  ALTER TABLE 表名
                                      ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
            3. 删除字段
                  ALTER TABLE 表名
                                      DROP 字段名;
            4. 修改字段
                  ALTER TABLE 表名
                                      MODIFY  字段名 数据类型 [完整性约束条件…];
                  ALTER TABLE 表名
                                      CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
                  ALTER TABLE 表名
                                      CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

外键的设置

在员工表里面先设置 emp_id的字段然后再关联 emp表里面的字段

1、安装mysql和navicat

2、Mysql数据语法学习

数据库的种类：关系型数据库和非关系型数据库

mysql数据库的操作

查看数据库

show databases;

show create database db1;

select database();

选择数据库

USE 数据库名

删除数据库

DROP DATABASE 数据库名;

修改数据库

alter database db1 charset utf8;

查入数据insert

1. 插入完整数据（顺序插入）
    语法一：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录
    语法：
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);

4. 插入查询结果
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2
                    WHERE …;

更新数据UPDATE

语法：
    UPDATE 表名 SET
        字段1=值1,
        字段2=值2,
        WHERE CONDITION（条件）;

示例：
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;

一定要加where

mysql表的操作

表的数据类型：

#1. 数字：
    整型：tinyinit  int  bigint
    小数：
        float ：在位数比较短的情况下不精准
        double ：在位数比较长的情况下不精准
            0.000001230123123123
            存成：0.000001230000

        decimal：（如果用小数，则用推荐使用decimal）
            精准
            内部原理是以字符串形式去存

#2. 字符串：
    char（10）：简单粗暴，浪费空间，存取速度快
        root存成root000000
    varchar：精准，节省空间，存取速度慢

    sql优化：创建表时，定长的类型往前放，变长的往后放
                    比如性别           比如地址或描述信息

    >255个字符，超了就把文件路径存放到数据库中。
            比如图片，视频等找一个文件服务器，数据库中只存路径或url。


#3. 时间类型：
    最常用：datetime


#4. 枚举类型与集合类型
enum('male','female')
set('play','music','read','study')

MariaDB [db1]> create table consumer( 
    -> name varchar(50),
    -> sex enum('male','female'),
    -> level enum('vip1','vip2','vip3','vip4','vip5'), #在指定范围内，多选一
    -> hobby set('play','music','read','study') #在指定范围内，多选多
    -> );

表的约束

#1、建立表关系：
    #先建被关联的表,并且保证被关联的字段唯一 #----- 父表--------
    create table dep(
        id int primary key,
        name char(16),
        comment char(50)
    );
部门表和员工表    foreign key设置在员工表

    #再建立关联的表
    create table emp(
        id int primary key,
        name char(10),
        sex enum('male','female'),
        dep_id int,
        foreign key(dep_id) references dep(id) 
        on delete cascade # 删除同步 只要被关联的 ，关联的 也删除 解散it部门，其他关联it部门的员工也删除
        on update cascade # 更新同步，
    );

表与表之间的关系

分析步骤：
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key（外键） 右表一个字段（通常是id）

#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）

#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表

#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

mysql字段的操作

mysql表的查询

单表查询和多表查询

select count(学号)  as “总人数” FROM student;

这个as可以写也可以不写

单表查询：
    http://www.cnblogs.com/linhaifeng/articles/7267592.html
    1.单表查询的语法
    2.关键字的执行优先级（重点）
    3.简单查询
    4.where 约束
    5.分组查询：group by 在where之后查询
    6.having 过滤
    7.查询排序：order by

8.限制查询得记录数：limit
    9.使用正则表达式查询

多表查询：

SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>


内连接：只取两张表的共同部分
select * from employee inner join department on employee.dep_id = department.id ;

左连接:在内连接的基础上保留左表的记录
select * from employee left join department on employee.dep_id = department.id ;

右连接：在内连接的基础上保留右表的记录
select * from employee right join department on employee.dep_id = department.id ;

全外连接：在内连接的基础上保留左右两表没有对应关系的记录
select * from employee full join department on employee.dep_id = department.id ;


select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id ;

7.4 使用事务保证数据完整性

事务就是将一组SQL语句放在同一批次内去执行，如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行。

注意：

MySQL事务处理只支持InnoDB和Berkeley DB数据表类型

ACID，指数据库事务正确执行的四个基本要素的缩写。
包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。
一个支持事务（Transaction）的数据库，必须要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性。
使用场景：
    银行的交易系统

原子性：执行事务要么都成功，要么都不成功

一致性：执行后，保存数据的一致性

隔离性：事务之间相互独立，互不影响

持久性：数据永远存储在数据库中

start transaction = begin等效

start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元
commit;

#出现异常，回滚到初始状态
start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到
rollback; # 回滚到原来的状态
commit;  # 只有提交了，事务操作才完成

问题：有没有遇到脏读问题，怎么解决，用事务(隔离级别处理)，事务是什么？

查看默认事务隔离级别

select @@tx_isolation;

如何关闭和开启MySQL的自动提交？

在MySQL中执行命令时，通常都直接被确定提交了。也就是说用户不用意识此事，所有的命令都会被自动COMMIT。特别是当存储引擎为MyISAM的情况下，本身它是不支持事务处理的，只要执行了命令，所有的命令部会被提交。

这样的默认自动提交的功能就被称为自动提交功能。自动提交功能默认被置为ON的状态。但是，如果存储引擎为InnoDB 时，当执行了START TRANSACTION或BEGIN命令（MySQL事务处理-删除后回滚）后，将不会自动提交了，只有明确执行了COMMIT命令后才会被提交，在这之前可以执行ROLLBACK 命令回滚更新操作。

用户可以将自动提交功能强制置为OFF。这样用户执行SQL语句后将不会被提交了，而执行COMMIT命令才提交，执行ROLLBACK命令回壤。

下面是将自动提交功能置为ON以及置为OFF的具体语法。

将自动提交功能置为ON

SET AUTOCOMMIT=0;
将自动提交功能置为OFF

SET AUTOCOMMIT=1;
我们将演示自动提交功能设置为OFF后，会出现的什么样的变化。置为OFF，我们向表user里插入一条数据后，看看是否能回滚。

将自动提交功能设置为OFF，执行结果如下。

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

View Code

MySQL的事务处理实现流程是什么？

如果存储引擎为InnoDB 时，不需要

将自动提交功能置为ON
SET AUTOCOMMIT=0;

1、关闭自动提交
2、开始事务
3、执行SQL语句操作
4、要么rollback 要么commit
如果存储引擎为InnoDB 时，当执行了START TRANSACTION或BEGIN命令（MySQL事务处理-删除后回滚）后，将不会自动提交了，只有明确执行了COMMIT命令后才会被提交，在这之前可以执行ROLLBACK 命令回滚更新操作。
5、set autocommit=1;把提交变为初试状态

7.5 使用DQL命令查询数据

mysql语言的分类

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

1. 数据查询语言DQL
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

2 .数据操纵语言DML
数据操纵语言DML主要有三种形式：
1) 插入：INSERT
2) 更新：UPDATE
3) 删除：DELETE

3. 数据定义语言DDL
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、
索引、同义词、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
| | | | |
表 视图 索引 同义词 簇

DDL操作是隐性提交的！不能rollback 

4. 数据控制语言DCL
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
1) GRANT：授权。


2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
回滚---ROLLBACK
回滚命令使数据库状态回到上次最后提交的状态。其格式为：
SQL>ROLLBACK;


3) COMMIT [WORK]：提交。


    在数据库的插入、删除和修改操作时，只有当事务在提交到数据
库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看
到所做的事情，别人只有在最后提交完成后才可以看到。
提交数据有三种类型：显式提交、隐式提交及自动提交。下面分
别说明这三种类型。


(1) 显式提交
用COMMIT命令直接完成的提交为显式提交。其格式为：
SQL>COMMIT；


(2) 隐式提交
用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。


(3) 自动提交
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，
系统将自动进行提交，这就是自动提交。其格式为：
SQL>SET AUTOCOMMIT ON；