sql语句基本操作

### SQL语句分类

完整的sql语言包括语句与子句
- 语句(statement)：create、alter、drop、insert、update、delete、select等
- 子句(clause):语句的一部分，例如：where, order by等
DDL (Data Definition Language)
- 数据定义语句
- 创建数据库、创建二维表、修改二维表等。例如：create, alter, drop, truncate, rename
DML (DATA MANIPULATION LANGUAGE)
- 数据操纵语句
- 增、删、改；insert，向已建二维表中插入行记录，update，修改行记录，delete删除行记录
DQL (Data Query Language)
- 数据查询语句
- SELECT语句，从数据库的二维表中查找数据，通常把select语句划分为DML语句
DCL (DATA CONTROL LANGUAGE)
- 数据控制语句
- 例如：grant, revoke，一般数据库为多用户系统，不同用户的权限不一样，可以访问的数据不一样，可以用DCL语句为给用户赋予或收回数据库的各类权限
TCL (TRANSACTION CONTROL LANGUAGE)
- 事务控制语句
- 例如：commit 提交; rollback 回滚; savepoint 建立存储点，只有支持事务的数据库管理系统或存储引擎TCL语句才能有效
MySQL 的事务
- 主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 insert,update,delete 语句
事务的特性Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
- 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

MySQL的数据类型

常用数据类型
- 数值型
- 字符型
- 日期时间型
- 二进制
数值型
- 整形int
  - TINYINT 占用1个字节
  - SMALLINT 占用2个字节
  - INT 占用3个字节
  - MEDIUMINT 占用4个字节
  - BIGINT 占用8个字节
- 十进制 decimal(N,M) N表示总位数，M表示小数部分的位数
- 单精度 float
- 双精度 double
字符型
- 定长字符Char(N)
- 变长字符varchar(N)
- 枚举enum('value1','value2',....) 在多个值中选其中一个
- 集合SET('value1','value2',....) 在多个值中选多个
  
  当enum输入多个值时会提示警告并无法写入该值
  
  输入非列表中的选项时无法输入
- char与varchar的区别
  
  使用varchar时存储所占用的空间根据存放的内容而定，主要用于存放长度不固定的数据，而char占用固定大小的空间，用于存放有固定长度的数据
日期时间
- 日期 date yyyy-mm-dd
- 时间 time hh:mm:ss
- 日期时间 datetime
  - 占用8个字节
  - 可用范围 ‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’
  - 不受时区影响
- 时间戳 timestamp
  - 占用4个字节
  - 可用范围 ‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC
  - 记录相对与 1970-01-01 00:00:01 的时间，带有时区信息，显示时会按当前时区进行时间转换
  - insert、update 数据时，TIMESTAMP列会自动以当前时间填充/更新
- year(2),year(4) 表示2位年份或4位年份
- datetime和timestamp的区别
  
  当前时区变化时，timestamp显示时间也随之产生对应变化，而datetime则保持不变
大对象型
- TEXT, TINYTEXT, MEDIUMTEXT, LOGTEXT（文本大对象类型）
- BLOB(binary large object)，TINYBLOB，MEDIUMBLOB，LONGBLOB （二进制大对象类型）

MySQL的数据类型修饰符

unsigned 无符号整数（非负）

当输入的值为负时将使用0替代该值
zerofill 零填充，当有空位出现时使用0填充高位补齐（超出指定位数按输入显示）
default 指定字段默认值，当无用户指定输入时填充默认值
primary key 主键（唯一且非空）
unique 唯一约束（该字段的值不允许重复，但可以为空）
not null 非空（值可以重复）
- null是SQL语言中的特殊值null值代表不确定，数据类型未知，属于不明确、状态未知的数据
auto_increment 自增长（未指定时自动在前一记录的值上加1，需要与主键配合使用q且为int型）

数据类型选择原则

适当 (Appropriate)
- 需要以最适合数据类型来表示数据
简洁 (Brief)
- 选择所用存储空间最少的数据类型。这可节省资源并提高性能
完整 (Complete)
- 选择的数据类型应分配有可存储特定项的最大可能值的充足空间

数据定义语句DDL

常用create 创建、drop 删除、alter 修改字段
作用对象：数据库(database)、表(table)、索引(index)、视图(view)、用户(user)、存储过程(procedure)、存储函数(function)、触发器(trigger)、事件调度器(event)
create database语法如下
- CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...
- create_specification:
  [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
- show CHARACTER SET 查看支持的字符集
- show COLLATE 查看支持的排序方式
create table 语法如下
- CREATE TABLE [IF NOT EXISTS] tbl_name (col1 type1,col2 type2,col3 type3.......);
- show tables from db_name查看数据库中所有的表（未指定数据库时为当前数据库）
- 基于已有表创建新表并复制所有数据
- 基于已有表创建新表并复制指定内容
- 基于已有表创建新表但不复制数据
  
  或
drop 命令
- drop database
- drop table
alter 命令
- 删除字段（无法删除表中最后一个字段）
- 添加字段
- 修改字段
  
  还可以配合 not null、default、unique、primary key等修饰符使用

数据操作语句DML

insert 添加数据
Insert into 表名 (列名1,列名2,....) values(列1对应值,列2对应值,....);
Insert into 表名 values(列1对应值,列2对应值,....,列N对应值);
update 更新字段的值

update 表名 set 列1=列1值, 列2=列2值 [where 列N=列N值]; #省略where将更改所有记录该字段的值
delete 删除记录

delete from 表名 where 列N=列N值;