《MySQL必知必会》学习笔记十一(操作表)------掌握部分

最新推荐文章于 2022-11-20 11:34:24 发布

發V發

最新推荐文章于 2022-11-20 11:34:24 发布

阅读量459

点赞数

分类专栏： MySQL笔记文章标签： mysql

本文链接：https://blog.csdn.net/sinat_28978689/article/details/55107425

版权

MySQL笔记专栏收录该内容

19 篇文章 1 订阅

订阅专栏

MySQL必知必会知识预览
第一章——了解SQL
第二章——MySQL简介
第三章——使用MySQL
第四章——检索数据
第五章——排序检索数据
第六章——过滤数据
第七章——数据过滤
第八章——用通配符进行过滤
第九章——用正则表达式进行搜索
第十章——创建计算字段
第十一章——使用数据处理函数
第十二章——汇总数据
第十三章——分组数据
第十四章——使用子查询
第十五章——联结表
第十六章——创建高级联结
第十七章——组合查询
第十八章——全文本搜索
第十九章——插入数据
第二十章——更新和删除数据
第二十一章——创建和操纵表
第二十二章——使用视图
第二十三章——使用储存过程
第二十四章——使用游标
第二十五章——使用触发器
第二十六章——管理事务处理
第二十七章——全球化和本地化
第二十八章——安全管理
第二十九章——数据库维护
第三十章——改善性能
————————————– 华丽的分隔符 ————————————————–

第二十一章(CREATE&ALTER&DROP)

创建表CREATE TABLE

格式：create table name(字段类型属性);


CREATE TABLE customers (
 cust_id  int(11)  NOT NULL AUTO_INCREMENT,
 cust_name  char(50) NOT NULL,
 cust_address  char(50) DEFAULT NULL,
 cust_city  char(50) DEFAULT NULL,
 cust_state  char(5) DEFAULT NULL,
 cust_zip  char(10) DEFAULT NULL,
 cust_country  char(50) DEFAULT NULL,
 cust_contact  char(50) DEFAULT NULL,
 cust_email  char(255) DEFAULT NULL,
PRIMARY KEY ( cust_id )
) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8

一个完整的创建表语句，由create table tablename引出，然后在括号中填写对应的字段名，数据类型，以及相关属性。并且在最后由primary key(字段)设定主键【不加引号】,设置自增开始值，设置编码集。

在创建新表时，指定的表名必须不存在，否则将会出错，如果要防止意外覆盖已有的表，sql要求应该先手工删除该表，然后再重新建立，而不是简单的利用创建表语句进行覆盖。如果想在一个表仅不存在的情况下进行创建她，需要在表名后面关键字 IF NOT EXISTS。这样做不检查已有表的模式是否与你打算创建的表的模式相匹配。他只是查看表名是否存在，并且仅在表名不存在时创建。

NULL值就是没有值或者是缺值。允许NULL值的列就是可以在插入数据时，此列可以不进行赋值。
不允许为NULL就是指在插入或者更新数据时，此列必须有值。另外不要把NULL与空字符串混淆，NULL值是没有值，她不是空串，空串是一个有效的值，她不是无值，在NOT NULL列中是允许空串存在的。

主键：主键值必须唯一，表中的每个行必须具有唯一的主键值，如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

 CREATE TABLE  orderitems (
    order_num   int(11) NOT NULL,
    order_item   int(11) NOT NULL,
    prod_id   char(10) NOT NULL,
    quantity   int(11) NOT NULL default 1,
    item_price   decimal(8,2) NOT NULL,
    KEY fk_orderitems_products   (  prod_id  ),
  CONSTRAINT   fk_orderitems_orders   FOREIGN KEY (  order_num  ) REFERENCES   orders
   (  order_num  ),
  CONSTRAINT   fk_orderitems_products   FOREIGN KEY (  prod_id  ) REFERENCES   produc
ts   (  prod_id  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

//orderitems 包含orders表中的每个订单的细节。每个订单有多项物品，但是每个订单任何时候都只有1个第一项物品，1个第二项物品，如此等等，因此，订单号(order_num列)和订单物品(order_item列)的组合是唯一的，从而适合做主键。

PRIMARY KEY (  order_num  ,  order_item  )//多个主键定义形式

AUTO_INCREMENT：自动增量。他会自动告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量(因此才有了这个关键字AUTO_INCREAMENT),给该列赋值一个可用的值。这样就会给每行分配一个唯一的ID。从而用作主键。
每个表只允许一个auto_increment列，而且它必须被索引(如，通过使他成为主键)
可以设置值去覆盖auto_increment，后续的增量将开始于手工插入的值继续增加。
确定AUTO_INCREMENT：让MySQL生成(通过自动增量)主键的一个缺点就是不知道这些值都是谁。那么我们可以使用 select last_insert_id()语句进行输出最后一个id。前提是此语句必须使用在INSERT语句之后才可以正确的查出最后一个AUTO_INCREMENT的值。

指定默认值使用关键字default。不允许使用函数作为默认值，只支持常量。

引擎类型简单介绍这是一个大的方面：
为什么会有这么多种引擎，这是因为不同的任务选择正确的引擎能获得良好的功能和灵活性。不同的引擎有不同的功能和特性。
InnoDB：可靠的事务处理引擎，它不支持全文本搜索；
MyISAM：性能极高的引擎，支持全文本搜索，不支持事务处理；
MEMORY：在功能上等同于MyISAM，但由于数据存储在内存(不是磁盘)中，速度很快(特别适合临时表)。

引擎类型可以混用，但是外键不能夸引擎。

更新表ALTER TABLE

ALTER TABLE vendors ADD vend_phone char(20); // 增加表字段

ALTER TABL vendors DROPCOLUMN vend_phone； // 删除表字段

设置外键

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders  FOREIGN KEY ( order_num ) REFERENCES  orders ( order_num );

 ALTER TABLE orderitems  CONSTRAINT  fk_orderitems_products  FOREIGN KEY ( prod_id ) REFERENCES  products  ( prod_id );  
 // 为orderitems设置外键prod_id 来自于products表中的prod_id，将orderitems与products关联起来

复杂的表结构更改一般需要手动删除过程，通常有步骤：
1、用新的列布局穿件一个新表；
2、使用INSERT SELECT 语句从旧表复制数据到新表。如果有必要可以使用转换函数和计算字段；
3、检验包含所需数据的新表；
4、重命名旧表(或删除)；
5、用旧表名命名新创建的新表；
6、根据需要重新创建触发器，存储过程，索引和外键。

小心使用ALTER TABLE，改动之前尽量备份，毕竟数据库表更改是不能撤销的，