SQL：表的创建修改与删除

OceanStar的学习笔记

已于 2023-04-12 14:54:50 修改

阅读量3.2k

点赞数 3

分类专栏：数据库文章标签： sql 数据库 java

于 2018-12-18 17:03:09 首次发布

本文链接：https://blog.csdn.net/zhizhengguan/article/details/85070091

版权

数据库专栏收录该内容

289 篇文章 17 订阅

订阅专栏

0、显示建表语句

show create table  表名

1、操作表

1.1、创建表:CREATE TABLE

顾客信息表

CREATE TABLE  IF NOT EXISTS  customers (  #当表名不存在时创建customers表
	cust_id      INT       NOT NULL AUTO_INCREMENT,
	cust_name    VARCHAR(50)  NOT NULL,
	cust_address VARCHAR(50)  NULL,  #NULL是默认设置，如果不是为null，默认值是null
	cust_city    VARCHAR(50)  NULL,
	cust_state   VARCHAR(5)   NULL,
	cust_zip     VARCHAR(10)  NULL,
	cust_country VARCHAR(50)  NULL,  
	cust_contact VARCHAR(50)  NULL,
	cust_email   VARCHAR(255) NULL,
	PRIMARY KEY(cust_id)                    #指定主键
)ENGINE = INNODB DEFAULT CHARACTER SET = utf8mb4;

上架商品信息表：商品名prod_id的订单编号是order_num

CREATE TABLE orderitems(
	order_num  INT 		NOT NULL,
	order_item INT          NOT NULL,
	prod_id    CHAR(10)	NOT NULL,
	quantity   INT          NOT NULL DEFAULT 1,
	item_price DECIMAL(8,2) NOT NULL,
	PRIMARY KEY(order_num, order_item) #订单号和订单物品的组合是唯一的，因此设置为主键
)ENGINE = INNODB;

顾客订单表：客户cust_id在order_date那天购买了订单编号为order_num的商品

CREATE TABLE orders(
	order_num  INT        NOT NULL AUTO_INCREMENT,
	order_date DATETIME   NOT NULL,
	cust_id    INT        NOT NULL,
	PRIMARY KEY(order_num)
)ENGINE = INNODB;

CREATE TABLE products
(
  prod_id    CHAR(10)      NOT NULL,
  vend_id    INT           NOT NULL ,
  prod_name  CHAR(255)     NOT NULL ,
  prod_price DECIMAL(8,2)  NOT NULL ,
  prod_desc  TEXT          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=INNODB;

CREATE TABLE vendors
(
  vend_id      INT      NOT NULL AUTO_INCREMENT,
  vend_name    CHAR(50) NOT NULL ,
  vend_address CHAR(50)  ,
  vend_city    CHAR(50)  ,
  vend_state   CHAR(5)   ,
  vend_zip     CHAR(10)  ,
  vend_country CHAR(50)  ,
  PRIMARY KEY (vend_id)
) ENGINE=INNODB;
DESC vendors;

CREATE TABLE productnotes
(
  note_id    INT           NOT NULL AUTO_INCREMENT,
  prod_id    CHAR(10)      NOT NULL,
  note_date DATETIME       NOT NULL,
  note_text  TEXT          NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)      #启动全文本搜索
) ENGINE=MYISAM;

MySQL引擎[ENGINE = INNODB]：
- MySQL有一个具体管理和处理数据的内部引擎。当使用CREATE TABLE时该引擎创建表，当进行数据库处理时，该引擎在内部处理请求。
- MySQL服务器具有多种引擎，具备不同的功能和特性：
  - InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索
  - Memory在功能等同于MyISAM，但是由于数据存储在内存中，速度很快，可以用来创建临时表
  - MyISAM是一个性能很高的引擎，它支持全文本搜索但是不支持事务处理
- 引擎类型可以混用
- 外键不能跨引擎：使用一个引擎的表不能引用具有不同引擎的外键
语法：
- create table 表名(
  列名列的类型【(长度) 约束】,
  列名列的类型【(长度) 约束】,
  列名列的类型【(长度) 约束】,
  …
  列名列的类型【(长度) 约束】

① 指定表名

* 命名规则：数据库名、表名、列名：英文字母、数字、下划线(_)，<font color=red>必须以英文开头开头</font>，以数字为开头是不规范的。
* 在同一个数据库中不能创建两个相同名称的表。在同一个表中不能创建相同名称的列。否则会报错。
* 建议数据库的首字母大写。我没有大写的原因是笔记进行了更新，为了避免代码出现错误，就没有对原先已经验证的代码进行修改。

② 指定数据类型

所有列必须指定数据类型。每一列都不能存储与该列数据类型不相同的数据。基本的数据类型有：

INTERGER型
- 存储整数不能存储小数
CHAR型
- 存储定长字符串。所谓定长字符串，就是当列中存储的长度达不到最大长度时，使用半角空格不足；当字符串超过最大长度的部分是无法存储到该列中的。比如CHAR(8)，指定存储8个字符的字符串
- 表中存储的字符串是区分大小写的
VARCHAR()型。
- 存储变长字符串。当字符串未达到最大长度，不会用半角空格补足。
- Oracle中使用VARCHAR2型，不推荐使用VARCHAR()型
DATE型：
- 用来存储日期

③ 约束

约束就是除了数据类型之外，对列中存储的数据进行显示或者追缴条件。

NOT NULL约束
- 必须输入数据，如果什么也不输入会报错。
主键约束
成为主键的条件[PRIMARY KEY]：主键必须唯一
- 如果主键是单个列，值必须唯一
- 如果主键是多个列，这些列的组合值必须唯一
  
  注：主键必须唯一，因此主键不能为null值
默认值约束[DEFAULT]:
- 如果没有设置DEFAULT默认值就是NULL
- 如果设置了DEFAULT,当执行INSERT子句是没有指定值时使用DEFAULT设置的值
  
  注：与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量
  最好使用默认值而不是Null值，特别对于计算或者数据分组的列

alter table 表名 alter column 字段名 drop default; (若本身存在默认值，则先删除)

alter table 表名 alter column 字段名 set default 默认值;(若本身不存在则可以直接设定)

自动增量[AUTO_INCREMENT]：
- 每执行insert子句时，该列自动增量。
  
  注：每个表只允许一个AUTO_INCREMENT列，而且它必须被索引。
  SELECT LAST_INSERT_ID(); 返回最后一个AUTO_INCREMENT的值

补充

CREATE TABLE table_new like table_old;

整个SQL语句会模仿table_old的数据结果创建一个一模一样的数据表table_new，但是并没有table_old的数据。

1.2、查看表结构

DESC `products`;
DESC `orderitems`;
DESC `orders`;
DESC `customers`;

查看表中所有的数据

SELECT * FROM 表名

1.3、修改表结构[不常用]：ALTER TABLE

①修改列名

ALTER TABLE 表名 CHANGE COLUMN 列名 新列名 数据类型;

②修改列的数据类型

ALTER TABLE 表名 MODIFY COLUMN 列名 新数据类型;

③添加新列

MySQL、DB2、PostgreSQL

#插入新列vend_phone，数据类型是VARCHAR(20)
ALTER TABLE vendors ADD COLUMN vend_phone VARCHAR(20);

Oracle：

ALTER TABLE vendors ADD (vend_phone VARCHAR(20));

SQL Server：

ALTER TABLE vendors ADD vend_phone VARCHAR(20);

④删除列

SQL Server、DB2、PostgreSQL、MySQL

#删除旧列vend_phone。
ALTER TABLE vendors DROP COLUMN vend_phone ;

Oracle

ALTER TABLE vendors DROP (vend_phone) ;

⑤ 定义外键

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

>ALTER TABLE orderitems 
	ADD CONSTRAINT fk_orderitems_products
	FOREIGN KEY(prod_id) REFERENCES products(prod_id);

>ALTER TABLE orders 
	ADD CONSTRAINT fk_orders_customers
	FOREIGN KEY(cust_id) REFERENCES customers(cust_id);

>ALTER TABLE products 
	ADD CONSTRAINT fk_products_vendors
	FOREIGN KEY(vend_id) REFERENCES vendors(vend_id);
>products 只存储产品信息，它除了存储供应商ID[vendors的主键]外不愁吃其他vendors的信息。vendors表的主键又叫做products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出其他相应供应商的信息

修改表名

ALTER TABLE vendors RENAME TO 新表名;

外键：外键为某个表的一列，它包含另一个表的主键值，定义了两个表之间的关系。
- 供应商信息不重复，从而不浪费时间和空间。处理数据也更简单
- 如果供应商信息变动，可以只更新verdos表中的单个记录，相关表中的数据不用改动

可伸缩性：能够适应不断增加的工作量而不失败。

每个表只允许一个主键(PRI)[一列或者多个列组成唯一值]，但是可以拥有多个外键(MUL)

语法

alter table 表名 add|drop|modify|change column 列名【列类型约束】;

注：当设计表的时候要考虑好表的结构，更新表结构并不是一个好事
数据库的更改不可撤销，因此在改动之前最好做一个备份，因为改动表是不可以撤销的

1.4、修改表名

Mysql：

RENAME TABLE 表名 TO 新表名, 表名TO 新表名;

DB2：

RENAME TABLE 表名TO 新表名;

Oracle PostgreSQL：

ALTER TABLE 表名 RENAME TO 新表名;

SQL Server：

sp_rename ‘ 表名’, ‘新表名’;

1.5、删除表DROP TABLE [慎用]

SET FOREIGN_KEY_CHECKS = 0;    
DROP TABLE IF EXISTS  `customers`;
SET FOREIGN_KEY_CHECKS = 1;

当设置了表的关联时执行DROP TABLE customers;报错：

ERROR: Cannot delete or update a parent row: a foreign key constraint fails：
- 原因：MySQL在InnoDB中设置了foreign key关联，造成无法更新或删除数据。可以通过设置FOREIGN_KEY_CHECKS变量来避免这种情况。
- 解决：
  
  SET FOREIGN_KEY_CHECKS = 0;
  删除完成后设置
  SET FOREIGN_KEY_CHECKS = 1;
  注：删除customers外键依旧存在

1.6、显示当前数据库可用的表

SHOW TABLES;

总结：通用的写法：

DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();

2、修改表数据

2.1、插入INSERT

官方文档

可以针对每个表或者每个用户，利用MySQL的安全机制禁止使用INSERT语句
insert语句一般不会产生输出

①插入完整的行

INSERT INTO customers
VALUES(NULL, ‘Pep E.LaPew’, ‘100 Main Street’, ‘Los Angles’, ‘CA’, ‘90046’, ‘USA’, NULL, NULL);

注意：
- 主键[当主键设置了自动增长]或者不想插入值将值设置为NULL；
- 不建议这种语法，因为它高度依赖表的结构，如果值设置的顺序不对或者回来表的结构变动就不好了
- INTO可以省略
- 如果想要插入NULL值，直接写成NULL就可以了。

INSERT customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
VALUES(‘Pep E.LaPew’, ‘100 Main Street’, ‘Los Angles’, ‘CA’, ‘90046’, ‘USA’); 【效果和上面的一样】

推荐使用,优点：
- 不担心表的结构变动
- 可以省略不想设置的值[前提是这些值必须是允许NULL值或者已经设置了默认值]
注意：
- 列的顺序可以改变但是值与列一一对应。
- 通过降低优先级提高整体性能：一般数据检索是最重要的，所以我们可以通过在insert和info之间添加关键字LOW_PRIORITY以降低INSERT的优先级。注意：UPDATE和DELETE同理
  
  INSERT LOW_PRIORITY table_name(row_name1,row_name2,row_name3) VALUES(new_value1, new_value2, new_value3);

如果想要给有了默认约束的值插入默认值，有两种方法

> * INSERT INTO `customers` 
> VALUES(NULL,DEFAULT '100 Main Street', 'Los Angles', 'CA', '90046', 'USA', NULL, NULL);  # 用DEFAULT代替【推荐】
> * INSERT INTO `customers` 
> VALUES(NULL,  '100 Main Street', 'Los Angles', 'CA', '90046', 'USA', NULL, NULL); #省略
> * 如果省略了没有设置默认值的列，该列的值会被设定为NULL。如果省略了设置了NOT NULL约束的列，会报错。

② 一次插入多行

MySQL

> INSERT `customers`(`cust_name`,  `cust_address`, `cust_city`,`cust_state`, `cust_zip`,`cust_country`) 
>VALUES('Pep E.LaPew', '100 Main Street', 'Los Angles', 'CA', '90046', 'USA'), 
>('M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');

注：
- 只要值得次序相同，可以一次插入多行。
- MYSQL用单挑INSERT语句处理多个插入比使用多条语句INSERT快。

Mysql方法二：

>START TRANSACTION;         
INSERT INTO Product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT;

* START TRANSACTION;   开始插入；COMMIT：确认插入

Oracle DB2
>INSERT INTO Product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT;

SQL Server PostgreSQL

>BEGIN TRANSACTION;
INSERT INTO Product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT;

Mysql完整的.sql文本。

##########################
# Populate customers table
##########################
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');


########################
# Populate vendors table
########################
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');


#########################
# Populate products table
#########################
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');



#######################
# Populate orders table
#######################
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);


###########################
# Populate orderitems table
###########################
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

#############################
# Populate productnotes table
#############################
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);

③从其他表中复制数据

新创建表：

CREATE TABLE concust(
	con_id INT NOT NULL AUTO_INCREMENT,
	con_name VARCHAR(50),
	con_city VARCHAR(20),
	con_email VARCHAR(255),
	PRIMARY KEY(con_id)
)ENGINE=INNODB;;

从customers表中查询数据，然后将结果插入concust表中：

INSERT concust(con_name, con_city, con_email)
SELECT cust_name, cust_city, cust_email FROM customers;

查看数据：

SELECT * FROM concust;

注意：
- 可以利用这个完成对数据的迁移
- INSERT语句的SELECT语句中，可以使用WHERE子句汇总GROUP BY子句等任何SQL语法，除了ORDER BY

2.2、更新UPDATE

官方文档

> #查询
>SELECT * FROM `customers` WHERE `cust_id` = 10005;

①更新单列

>UPDATE `customers` SET `cust_email` = 'elmer@163.com' WHERE `cust_id` = 10005;

>UPDATE `customers` SET `cust_email` = NULL WHERE `cust_id` = 10005;

②更新多列

>UPDATE `customers` SET `cust_name` = 'The Fudds',`cust_email` = 'elmer@fudd.com' WHERE `cust_id` = 10005; 【推荐】

只能在PostgreSQL和DB2中可以使用

>UPDATE `customers` SET (`cust_name` ,`cust_email`) = ('The Fudds', 'elmer@fudd.com') WHERE `cust_id` = 10005;

③删除单列

> #删除某列:将值设置为null[前提是该值可以设置为null]
UPDATE `customers` SET `cust_email` = NULL WHERE `cust_id` = 10005;

注意：
- 在使用不带where的update时一定要特别注意，一不小心就会更新表中所有行
- update可以使用子查询

备注：关联后更新

--
-- 关联后更新
--
mysql> select * from t1;
+------+
| a    |
+------+
|   10 |
|    4 |  -- 和t2中的4相等
|    1 |
|    2 |
|    3 |  -- 和t2中的3相等
|   -1 |
|    8 |
+------+
7 rows in set (0.00 sec)

mysql> select * from t2;
+------+
| a    |
+------+
|    5 |
|    4 |  -- 和t1中的4相等
|    3 |  -- 和t1中的3相等
+------+
3 rows in set (0.00 sec)

mysql> update t1 join t2 on t1.a = t2.a set t1.a=100;  -- 先得到t1.a=t2.a的结果集
                                                       -- 然后将结果集中的t1.a设置为100
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from t1;
+------+
| a    |
+------+
|   10 |
|  100 |  -- 该行被更新成100
|    1 |
|    2 |
|  100 |  -- 该行被更新成100
|   -1 |
|    8 |
+------+
7 rows in set (0.00 sec)

更新有关系的值

mysql> create table t5 (a int, b int);

mysql>  insert into t5 values(1,1);

mysql> select * from t5;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
+------+------+

mysql>  update t5 set a=a+1, b=a where a=1;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t5;
+------+------+
| a    | b    |
+------+------+
|    2 |    2 |
+------+------+

2.3、删除delete

官方文档

①删除一行数据

>   #查询
>SELECT * FROM `customers` WHERE `cust_id` = 10006;  
>   ****
>   #删除`cust_id` = 10006这一整行
DELETE FROM `customers` WHERE `cust_id` = 10006;

②删除全部行

> #查询
SELECT * FROM `customers` WHERE `cust_id` = 10006; 
> ***
> #删除表的所有行
TRUNCATE TABLE `concust`; 【推荐】
DELETE TABLE `concust`; 【不推荐】
> ***
>#查询
SELECT * FROM `concust`;

注意：
- 在使用不带where的delete时一定要特别注意，一不小心就会删除表中所有行
  - delete是删除整行而不是删除某一列，如果需要删除指定的列，请用update。
  - delete子句删除整行但是不删除表本身。delete子句不带where可以删除表的所有行
  - 如果想要删除表的所有行,不要用delete，用TRUNCATE TABLE更快[TRUNCATE 实际是删除原来的表并重新创建一个同样结构的表，并不是逐行删除表中的数据]
- 可以限制和控制delete子句的使用
- 与SELECT子句不同的是，DELETE语句中不能使用GROUP BY、HAVING和GROUP BY三列子句，而只能使用WHERE子句。因为GROUP BY和HAVING子句是从表中选取数据时用来改变抽取数据的，而ORDER BY是用来指定取得结果显示顺序的，因此，在表中数据它们都起不到什么作用

规则

绝对不要使用不带where子句的update或者delete语句，除非确定想要更新和删除表的每一行
确保每个表都有主键，并且在搜索中尽可能使用主键
再对update或者delete语句使用where之前，请用select测试确保操作正确
使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行

2.4、REPLACE

官方文档

mysql> create table t4(a int primary key auto_increment, b int);

mysql> insert into t4 values(NULL, 10),(NULL,11),(NULL,12);

mysql> select * from t4;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |   11 |
| 3 |   12 |
+---+------+

mysql> insert into t4 values(1, 100);  -- 报错，说存在重复的主键记录 "1"
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> replace into t4 values(1, 100); -- 替换该主键对应的值  
Query OK, 2 rows affected (0.03 sec)   -- 两行记录受到影响

mysql> select * from t4;
+---+------+
| a | b    |
+---+------+
| 1 |  100 |  -- 已经被替换
| 2 |   11 |
| 3 |   12 |
+---+------+
3 rows in set (0.00 sec)
-----
-- replace的原理是：先delete，在insert
-----

mysql> replace into t4 values(5, 50);  -- 没有替换对象时，类似插入效果
Query OK, 1 row affected (0.03 sec)    -- 只影响1行

mysql> select * from t4;
+---+------+
| a | b    |
+---+------+
| 1 |  100 |
| 2 |   11 |
| 3 |   12 |
| 5 |   50 |  -- 插入了1行
+---+------+
4 rows in set (0.00 sec)

replace原理更明显的例子

mysql> create table t6(a int primary key, b int auto_increment, c int, key(b));

mysql> insert into t6 values(10, NULL, 100),(20,NULL,200);  -- b自增长

mysql> select * from t6;
+----+---+------+
| a  | b | c    |
+----+---+------+
| 10 | 1 |  100 |  -- b为1
| 20 | 2 |  200 |  -- b为2
+----+---+------+

mysql> replace into t6 values(10,NULL,150);  -- 将a=10的替换掉

mysql> select * from t6;
+----+---+------+
| a  | b | c    |
+----+---+------+
| 10 | 3 |  150 |  -- 替换后b从1变成了3，说明是先删除，再插入
| 20 | 2 |  200 |
+----+---+------+

insert on duplicate 效果和 replace类似

mysql>  select * from t4;
+---+------+
| a | b    |
+---+------+
| 1 |  100 |
| 2 |   11 |
| 3 |   12 |
| 5 |   50 |
+---+------+

mysql> insert into t4 values(1,1);  -- 插入报错，存在key为1的记录
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> insert into t4 values(1,1) on duplicate key update b=1;  -- 带上on duplicate参数
                                                               -- 非SQL标准，不推荐
Query OK, 2 rows affected (0.03 sec)

mysql> select * from t4;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |  -- 该行的b列从100被替换成1
| 2 |   11 |
| 3 |   12 |
| 5 |   50 |
+---+------+

insert ignore

mysql> insert ignore into t4 values(1,1);   -- 忽略重复的错误
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

OceanStar的学习笔记

关注

3
点赞
踩
10

收藏

觉得还不错? 一键收藏
1
评论
SQL：表的创建修改与删除

文章目录使用redis的常见数据类型准备使用字符串string类型使用list列表类型使用HASH类型使用set集合类型使用有序集合sorted-set类型使用HyperLogLog类型使用Geo类型使用redis的常见数据类型Mysql：首先应该考虑建立什么样的表或者模式使用SQL来操作redis中的数据Redis：首先应该考虑的是redis支持的哪种数据类型最适合我们的场景。不...
复制链接

扫一扫

专栏目录