Mysql基础

什么是SQL

SQL(Structured Query Language),结构化查询语言,是一种数据库查询和程序设计语言.

SQL分类

1. 数据定义语言(DDL Data Definition Language):创建、修改、或删除数据库中表、视图、索引等对象的操作。常用命令为：create , alter , drop
2. 数据查询语言(DQL Data Query Languanage):按照指定的组合、条件表达式或排序 检索已存在的数据库中数据，不改变数据库中的数据，常用命令为select
3. 数据操纵语言(Data Manipulation Language):向表中添加、删除、修改数据操作。常用命令有insert，update，delete。
4. 数据控制语言（DCL Data Control Language）：用来授予或回收访问数据库的某种特权、控制数据操纵事务的发生时间及效果、对数据库进行监视等操作，常用命令有：GRANT，REVOKE，Commit、ROLLBACK

DDL语言

DDL操作数据库

• create database databaseName [character set]:创建数据库，可使用character set 指定字符集编码
• show databases：查看所有数据库
• show create database databaseName：查看指定数据库信息
• alter database databaseName character set utf8：设置数据库字符编码为utf-8
• use databaseName：切换数据库
• select database()：查看正在使用的数据库

DDL操作表

创建表格

语法：

    create table table_name(
        column_name data_type [default default_value][column constraint][comment ‘comment_content‘],
    ......
    [table_constraint]
    )[engine = engine_type][default charset=charset_type];

说明:
1. table_name：表名
2. column_name：字段名
3. data_type:字段类型
4. default default_value:设置默认值
5. column constraint:设置字段级约束
6. comment comment_content:设置注释
7. table_constraint:设置表级约束
8. engine:设置存储引擎
9. charset:设置字符编码

修改表格

1. 添加字段

    alter table table_name 
    add column_name data_type 
    [default default_value]
    [column_constraint]
    [comment ‘comment_content‘]
    [after 字段名]

2. 修改字段

    alter table table_name
    modify column_name data_type [default default_value]

• 注意:
  1. 字段的修改包括数据类型,大小和默认值,默认值只会影响后来插入的数据,不影响之前的数据
  2. 不能修改字段约束,字段先后顺序和注释

3. 删除字段

    alter table table_name
    drop column_name

• 注意:
  1. 一个表至少要保留一个字段
  2. 如果删除列是另一个表的外键,则该列无法删除

4. 重命名表格
   rename table old to new
5. 截断表
   truncate table table_name

• 注意
  1. 截断表后,表中数据也一并删除
  2. drop不仅用于删除表,表中保存的数据也一并删除

6. 删除表
   drop table table_name

DML

添加数据

数据添加有两种方式：

1. insert into table_name [column_name1,column_name2...] values(value1, value2,...)

   • 字段列表顺序可以不按表中列的顺徐,但value必须要一一对应
   • 当字段列表不写时,则对于values里面的值必须和表中的列一一对应
   • 如果字段值是字符或者日期类型,则需要用单引号引起来

2. insert into table_name[column_name1, column_name2,...]
   select column_name1, column_name2...from table_name
   

   • insert子句中字段名列表的数目和数据类型必须和select子句中选择的字段列表的数目和数据类型相匹配。数据类型不考虑长度，但是须比查出来的对应列的长度长或相等。
   • select查询字段名可以和insert插入表对应字段名不一致

修改数据

    update table_name 
    set column1 = value1, column2=value2...
    [where]

• 如不指定where,则会修改所有数据

删除数据

delete from table_name [where ...]

DCL之事务

什么是事务

• Transaction（事务），一个最小的不可再分工作单元，通常对应一个完整得的业务，而这些业务需要一批DML语句共同完成，不能分开

Mysql中的事务

数据库表中,对于表中数据的修改、添加、删除，均属于DML（数据操纵语言），这类的SQL语句均需要执行commit，如果撤销则需要执行rollback。在mysql中，执行DML语句后，会默认自动执行commit操作，因此，当遇到多个DML语句构成的事务时，就必须通过set autocommit=0手动关闭自动提交，而后使得多个DML操作同时成功或失败，当失败后，使用rollback回滚到保存点或者上一次commit的位置。这样，才能确保事务的原子性不变。

事务的周期

• 开始：执行第一个DML语句时，事务开始
• 结束：
  • 第一种是执行commit或者rollback语句，当前事务结束
  • 另外一种是执行DDL语句或者DCL语句，事务结束
  • 数据库客户端程序退出或者数据库崩溃时，为了保障数据的一致性，也会结束事务

事务执行期间的数据变化

1. 执行DML语句且事务没结束，如果需查看数据库中的真实情况（就是存储在硬盘中的数据），需要新建一个查询窗口，在新窗口中查询。这是由于事务在提交前，都是保存在缓存区中，以便rollback。所以，当你在当前窗口select到的都是缓冲区的数据，而非数据库中的数据。只有commit之后，数据才被写入数据库，此时也不能rollback到之前的数据了。
2. 当一个用户执行DML语句时，如果其事务没有结束，则受影响的行被锁定，其他用户这个时候就不能改变受影响的行的数据，直到事务结束。

事务的四大特性（ACID）

• 原子性（Atomicity）：事务是一个不可分割的整体，事务开始之后，要么全部成功，要么全部失败，不可能部分成功，部分失败。如果失败只会回滚到开始之前的状态。
• 一致性（Consistency）：事务必须使数据库从一个一致性状态转换到另一个一致性状态，即一个事务执行之前和执行之后必须处于一致性状态。
  • 这个不好理解，拿转账来说，用户A和用户B他们的钱加起来一共5000，那么，不管A与B之前怎么转账，事务结束之后他们的钱加起来仍然为5000，这就是事务的一致性。
• 隔离性（Isolation）：多个用户并发访问数据库时，不能被其他事务干扰，多个并发事务之间要相互隔离。
• 持久性（Durability）：一个事务一旦被提交，对数据库的所有更新将被保存到数据库中，不能回滚。

约束

• Mysql数据库通过约束(constraint)防止无效的数据进入到表中,以保护数据的实体完整性
• 约束分为字段级别约束和表级别约束
  • 字段级约束:只为单个字段添加约束
  • 表级约束:只为一个或多个字段添加约束
• 常见约束:
  • primary key:主键约束,约束字段唯一,非空
  • not null:非空
  • unique:指定字段值必须唯一,unique约束允许输入null,且可以接受多个null
  • auto_increment:自动增长,类型必须为整数类型,且必须为主键
  • check:mysql不支持check约束
  • foreign key：外键约束

Mysql基础