SQL-DDL

DDL数据定义语言分为：对于表或数据库的结构进行定义，而非数据本身
1、库的管理（创建create、修改alter、删除drop）
2、表的管理（创建create、修改alter 、删除drop）
#---------------------------------------------------------------------------------------------------------------------------------
#库的管理
/*
库创建：不加if not exists，若重复执行Create时会报错
create database【if not exists】 库名（默认存储在MySQL的data文件夹中）
                容错性的处理：若库已经存在，则不再创建了
库修改：（一般不修改，容易导致数据出现丢失或错误）
1、若要修改库名，把服务器停止，在data文件夹中修改，再重新启动
2、可以更改库的字符集(创建时会默认字符集)：
alter database 库名 character set gbk/utf—8；（再刷新，右击数据库“改变数据库”即可查看）

库删除：不加if exists，若重复执行drop时会报错
drop database 【if exists】库名；(执行后刷新)
*/



#表的管理
/*
表创建：
create table 【if not exists】表名（
	列名1   列的类型【（长度）  列的约束】,
	列名2   列的类型【（长度）  列的约束】,
         ......);

表修改：
1、可以修改列名、、添加新列、删除列、修改表名
2、类型/约束
3、添加新列/删除列
4、修改表名


表删除：
drop table 【if exists】表名 ；

*/

#表创建-----------------------
 CREATE DATABASE books;
#在books库中，创建book表
USE books;
CREATE TABLE book(
	id INT,
	bname VARCHAR(20),
	price DOUBLE,
	author VARCHAR(20),
	publishdate DATETIME 

);
DESC book; #查看表结构


#表修改------------------------
#改列名（change）：ALTER TABLE 表名 CHANGE 【COLUMN】原列名 新列名 类型;
把publishdate，修改为pubdate，类型为datetime
ALTER TABLE book CHANGE COLUMN publishdate pubdate DATETIME;

#改类型（modify）:ALTER TABLE 表名 MODIFY 【COLUMN】 列名 新类型;
把pubdate的类型修改为timestamp
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

#加新列（add）：ALTER TABLE 表名 add COLUMN 新列名 新类型(长度);(COLUMN必须加)
在book中添加新列：作者国籍nation
ALTER TABLE book ADD COLUMN nation VARCHAR(10);

#删除列（drop）：ALTER TABLE 表名 drop COLUMN 新列名; (不用再加类型);(COLUMN必须加)
删除nation列
ALTER TABLE book DROP COLUMN nation;

#修改表名（rename to）
修改表名为newbook
ALTER TABLE book RENAME TO newbook;
DESC newbook;

#表删除------------------------
DROP TABLE IF EXISTS 表名


#创建表/库时，通用的写法：为了避免与原库名字重复，可以先删除一遍
DROP TABLE/DATABASE IF EXISTS 旧表名/库名；
CREATE TABLE/DATABASE 表名()/库名；
 

#表的复制：可以跨库复制表，只要指定被复制的表的库名：库名.被复制的表名
1、仅仅复制表的结构 ；复制了列名，不复制数据
CREATE TABLE 新表名 LIKE 要复制的表名；
2、复制全部结构和数据；创建子查询，把查询的结果作为要复制的表
CREATE TABLE 新表名
SELECT*FROM 要复制的表名；
3、复制部分结构和数据；只复制筛选后的结果
CREATE TABLE 新表名
SELECT*FROM 要复制的表名
WHERE 筛选条件；
4、仅仅、复制某些字段
CREATE TABLE 新表名
SELECT 要复制的字段
FROM 字段来自的表名
WHERE 筛选条件（让筛选条件的结果为空，即可以不复制字段下的数据）




#知识补充----------------------------------------------------------------------------------------------------------------------

#常见的数据类型
#选择原则：所选择的类型越简单越好，能保存的数值的类型越小越好

/*
数值型：
	整型
	小数：定点数；浮点数
	
字符型
	较短的文本：char varchar
	较长的文本：text、blob（较长的二进制数据）
日期型
*/
一、整型：各个分类都有范围，无符号表示值为0~X的非负数整数；有符号表示值可以为负数
分类：   TINYINT ；smallint；mediumint；int/integer；bigint
所占字节数：1         2；       3；           4          8
特点:若不设置无符号还是有符号，默认有符号
     若插入的数值超出整型范围，会显示边界值
     若不设置长度，会有默认长度
     长度的代表的意思为显示的最大宽度，若不够，会用0在左边填充，但是需要搭配zerofill：id int（7）zerofill    
#如何判断整型的有符号和无符号？创建一个列，类型设置为int，则为有符号，若想设置无符号整型：int后面加上unsigned即可
CREATE TABLE books(id INT UNSIGNED, bookname VARCHAR);#若插入非法数据，整型数值为默认为0或者x边界值

二、小数
分类：浮点型【float(M,D) ；double(M,D)】、定点型（dec(M,D)即decimal(M,D)）
字节：          4                8                        M+2
特点：M代表整数部位+小数部位；D代表小数部位；如果插入的值超过范围，则插入临界值
      M和D都可以省略，若是定点型，则(M,D)默认为（10，0）
      若是浮点型，会根据插入数值的精度来决定精度
对比：定点型的精度较高，若对数值精度较高，如货币运算等则可以优先考虑定点型     
 

三、字符型
较短的文本（用来保存MySQL中较短的字符串）：char（M）和varchar（M）#m代表可插入的最大的字符数
最多的字符数：                                  M         M
区别： char代表固定长度字符 （m为多大就开多大的空间，比较耗费空间）#char的m可以省略，默认为1，varchar不能省略
       varchar代表可变字符（插入几个字符就开几个字符空间，比较节省空间）
       效率上，char的性能更高一些：若插入的字符本身就有长度限制（性别），可以用char

四，日期型
分类：date DATETIME TIMESTAMP  TIME  YEAR
字节：4       8          4       3     1
 DATETIME vs TIMESTAMP
后者支持的时间范围较小
后者和实际时区有关，更能反映实际的日期。而前者只能反映插入时的当地时区
后者受数据库版本影响很大
       
       


#常见的约束：一般在创建表/修改表的时候添加
#一种限制，限制表中的列的数据，为保障插入的值的准确和一致性：例如保障学号不会重复
六大约束
NOT NULL 非空约束
DEFAULT 默认约束：该字段有默认值
PRIMARY KEY 主键约束：该字段值具有唯一性、非空#（主键每个表最多一个，不为空）
UNIQUE 唯一约束：保证值唯一性，但可以为空
CHECK 检查约束：mysql中使用无效果  ；用于加条件：例如年龄设置范围、性别
FOREIGN KEY 外键约束（添加在从表，用于引用主表的某一列）：限制两个表的关系，保证该字段的值必须来自于主表的关联列的值

#约束的添加分类
列级约束：六大约束都可以写，但外键约束没有效果
表级约束：除了非空和默认，其他都可以

#创建表时添加列级约束
CREATE TABLE water(
             id INT PRIMARY KEY,
             wname VARCHAR(20) NOT NULL UNIQUE,#可以添加多个约束
             gender CHAR(1) CHECK(gender='男'OR gender='女'),
             seat INT UNIQUE,
             age INT DEFAULT 18
             majorid INT);
             
CREATE TABLE major(
		id INT,PRIMARY KEY,
		majorname VARCHAR(20)
		
);
             
 #创建表时添加表级约束           
             
CREATE TABLE water(
             id INT ,
             wname VARCHAR(20),
             gender CHAR(1),
             seat INT,
             age INT,
             majorid INT,
            【constraint pk】  PRIMARY KEY (id),#为表中id添加主键
             【CONSTRAINT uk】  UNIQUE (seat),#为表中saet添加唯一约束
             【CONSTRAINT fk】 FOREIGN KEY  (majorid) REFERENCES major(id)#外键
             );
             
 #主键、非空、唯一、等用列级约束；外键用表级约束；外键取名最好指出表名和列名
 
 
 #主键和唯一的对比
 
            保证唯一性       是否可为空	           一个表中有几个		是否允许组合
   
   主键		是		否			至多一个		是， 不推荐
   唯一		是		是			多个			是，不推荐
   
   外键：
   1、要求在从表设置外键关系
   2、从表的外键列的类型和主表关联列的类型要求一致或兼容
   3、要求主表中的关联列必须是一个key（主键或者唯一键）
   4、插入数据时先插入主表的数据，再插入从表
   
   
   
    #修改表时添加列级约束 ：
   #使用modify,  直接在列名类型后添加列级约束
   ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP NOT NULL;
   #或者使用add  添加表级约束
   ALTER TABLE book ADD NOT NULL(pubdate ); 
  #添加外键 表级约束（列级约束无效）
  ALTER TABLE book ADD FOREIGN KEY  (majorid) REFERENCES major(id);
  
  
  #修改表时删除约束（修改时直接不写约束类型）
  ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP ;
  #或者
  ALTER TABLE book DROP NOT NULL pubdate ;
  
  
  
  列级约束vs表级约束
  
			位置			支持的约束类型			是否可以起约束名
  
 列级约束		列后面			语法都可以，但外键没效果		不可
 表级约束		所有列下面		默认与非空不可				可以（主键没效果）
 
 
 
 #标识列---------------------------------------------------------------------------------------------------------------
 
 #又称为自增长列
 含义：可以不用手动的插入值，系统提供默认的序列值
 #特点：标识列不一定必须和主键搭配，但必须和key搭配
 #      一个表至多一个标识列
 #      标识列的类型：只能是数值型
 #      标识列可以通过：set auto_increment_increment=新步长；设置步长       
 #      通过手动设置起始值
 
 
 #1、创建表时(create table语法)设置标识列：在想添加的列的后面，追加auto_increment（默认起始值1，步长1）
 若在主键中追加，可以让主键自增长1、2、3、。。，不会重复
 #2、修改表时（alter table语法）设置标识列：使用modify，在字段类型后添加
 #3、修改表时删除标识列：也是直接不添加标识列
  ALTER TABLE book MODIFY COLUMN id INT ;
 
 
 
 
 
  #TCL语言---------------------------------------------------------------------------------------------------------------
  #transaction control language事务控制语言
  事务指：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行（成功commit），要么全部不执行（失败全部回滚rollback）
  #例如：转账的例子：A（余额1000）转账给B（余额1000） 500元；转账后a余额应当为500，b为1500才算执行成功
  回滚：把所有操作撤销，回到最初状态
  
  事务具有的属性（acid四个属性）
  1、原子性（atomicity）：事务是一个不可分割的工作单位，要么都发生，要么都不发生
  2、一致性（consistency）：事务必须使数据库从一个一致性状态变换为另一个一致性状态（例如转帐前两人金额之和转账后金额之和一致）
  3、隔离性（isolation）：一个事务的执行不被其他事务干扰，即一个事务内部的操作和使用的数据对并发的其他事务是隔离的；并发执行的各事务间不相互干扰
  4、持久性（durability）：持久性是一个事务一旦被提交，对数据库的改变就是永久性的；除非使用其他事务进行控制（例如删除后又添加）
  
  #事物的创建：
  #隐式的事务：事务没有明显的开启和结束的标记（insert、update、delete语句）
  #显示事务：事务具有明显的开启和结束的标记  前提：必须设置自动提交功能为禁用
  设置自动提交关闭：set autocommit=0;（只针对当前会话有效）
  
  #显示事务书写步骤
  步骤1、开启事务
  SET autocommit=0;
  START transaciton;可选
  步骤2、编写事务中的sql语句（select、insert、update、delete）不包含ddl语言
  步骤3、结束事务
  COMMIT;提交事务
  ROLLBACK;回滚事务
 
 
 #若同时运行多个事务，当事务访问数据库相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：
    #脏读：对于两个事务T1,T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的；
    #不可重复读：对于两个事务T1,T2，T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了；
    #幻读：对于两个事务T1,T2，T1读取了一个字段，然后T2在该字段插入了一些新的行之后，若T1再次读取同一个表，就会多出几行；
 #mysql的事务隔离级别：
    #read uncommited（读未提交数据）：允许事务读取未被其他事务提交的变更。脏读、不可重复读和幻读的问题都会出现；
    #read commited（读已提交数据）：只允许事务读取已被其他事务提交的变更。可避免脏读，但不可重复读和幻读问题仍会出现
    #repeatable read（可重复度）：确保事务可以多次从一个字段中读取相同的值。在这个事务持续期间，禁止其他事务对这个字段进行更新。可避免脏读和不可重复读取，但幻读仍然会出现
    #serializable（串行化）：确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有并发都可以避免，但性能十分低下；