INSERT 插入数据
INSERT 用来插入行到数据库表,插入可以有以下几种方式:
插入完整的行;
插入行的一部分;
插入多行;
插入某些查询的结果;
【INSERT 一般没有输出】
插入单行
#插入一个新客户到customers表
#存储到每个表列中的数据在VALUES子句中给出,对每个列必须提供一个值。
#如果某 个列没有值,应该使用NULL 值(假定表允许对该列指定空值)。
#各个列必须以它们在表定义中出现的次序填充。
#第一列cust_id为NULL。这是因为每次插入一个新行时,该列由MySQL自动增量。当然,如果不想自动增量,也可以自己设定一个值。
INSERT INTO customers
VALUES (NULL,'Pep E. LaPew','100 Main Street','Los Angles','CA','90046','USA',NULL,NULL) ;
#上面语法很简单,但并不安全,当表的结构变动,插入数据混乱,因此在插入数据的时候可以指定表头并和数据对应起来
INSERT INTO customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
VALUES (NULL,'Pep E. LaPew','100 Main Street','Los Angles','CA','90046','USA',NULL,NULL) ;
#当然,使用这种语法,可以省略列,比如cust_id使用自动增量,那么可以省略不写
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
VALUES ('Pep E. LaPew','100 Main Street','Los Angles','CA','90046','USA',NULL,NULL) ;
插入多个行
#使用多条insert语句,一次提交它们
INSERT INTO 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');
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
VALUES('M. Martian','42 Galaxy Way','New York','NY','11213','USA');
#或者,如果每条INSTER语句的列明次序相同,可以如下组合
INSERT INTO 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');
插入检索出的数据
INSERT一般用来给表插入一个指定列值的行。但是INSERT还可以将一条SELECT语句插入到表中,这就是所谓的INSERT SELECT。
比如你想从另一表中合并客户列表到你的customers表。不需要每次读取一行然后再INSERT插入,可以如下进行:
#比如将custnew表中的数据导入到customers表
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
SELECT cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country FROM custnew;
需要注意的是:两表中cust_id都是主键,所以在导入时custnew中的cust_id不能和customers中的重复,否则接下来的INSERT操作失败。或者直接省略cust_id列,让mysql在导入时自动增量
更新和删除数据
更新数据
使用UPDATE关键字,可采用两种方式使用UPDATE:
更新表中特定行;
更新表中所有行;
【使用UPDATE时一定要注意,因为稍不注意就会更新表中的所有行,这不是我们想要的,所以特别注意where过滤条件,如果没有where,会直接更新表中的所有行】
#更新customers表中cust_id为10005的消费者的email为elmer@fudd.com
UPDATE customers SET cust_email='elmer@fudd.com' WHERE cust_id=10005;
#同时更新一行的多个列
UPDATE customers
SET
cust_email = 'elmer@fudd.com',
cust_name= 'The fudds'
WHERE cust_id = 10005 ;
#为了删除某一列的值,可以设置它为NULL
#比如删除消费者10005的email
UPDATE customers SET cust_email=NULL WHERE cust_id=10005;
删除数据
使用DELETE关键字,有如下两种方式:
从表中删除特定行;
从表中删除所有行;
【使用DELETE时一定要注意,因为稍不注意就会删除表中的所有行,这不是我们想要的,所以特别注意where过滤条件,如果没有where,会直接删除表中的所有行】
#从customers表中删除消费者30008的数据;如果没有where条件,会删除所有消费者信息
DELETE FROM customers WHERE cust_id=30008;
注意:
1)DELETE是删除整行不是删除列,如果要删除列,请使用UPDATE
2)DELETE只是删除表里面的内容,但不删除表本身,即使删除了所有数据,仍旧留下一张空表
3)若想删除表中所有行,可以使用TRUNCATE TABLE,效果相同速度更快;本质上TRUNCATE TABLE是直接删除表并新建一张同名空表,所以速度更快。
创建表、操纵表
两种创建表的方法:
使用具有交互式创建和管理表的工具
直接用MySQL语句CREATE TABLE创建表
注意:
1)创建新表时,指定的表明必须不存在,否则报错,这是MySQL为了防止意外覆盖已有的表指定的规则,如果新表名已经存在,就需要手动删除之后才能重新建立该表;
2)如果仅想在一个表不存在时创建它,应该在表明后加关键字IF NOT EXISTS;
创建一个bilibili表
格式为:CREATE TABLE 表名 (字段1 数据类型 是否允许是NULL值,字段2 数据类型 是否允许是NULL值,...)
CREATE TABLE bilibili (
cust_id INT NOT NULL AUTO_INCREMENT,
cust_name CHAR(50) NOT NULL,
cust_address CHAR(50) NULL,
cust_city CHAR(50) NULL,
cust_state CHAR(5) NULL,
cust_zip CHAR(10) NULL,
cust_country CHAR(50) NULL,
cust_contact CHAR(50) NULL,
cust_email CHAR(255) NULL,
PRIMARY KEY (cust_id)
) ENGINE = INNODB
代码解释:
1)可以看到字段数据类型有int和char,其它数据类型也可以
2)cust_id和cust_name不允许有NULL值
3)PRIMARY KEY (cust_id)定义cust_id为主键,如果主键使用单个列,那么这个列的值必须是唯一的,如果使用多个列作为主键,那么这些列的组合值必须是唯一的。
4)ENGINE = INNODB定义引擎为INNODB
5)AUTO_INCREMENT规定cust_id可以自动增量,即本列新增一行时编号自动加1,每个表只允许一个AUTO_INCREMENT,且它必须被索引(比如使它成为主键)。当然AUTO_INCREMENT产生的值可以被覆盖,只要覆盖赋予的值没被使用过就行,接下来的AUTO_INCREMENT以这个值为基础自动增量。
6)使用select last_insert_id();可以获得AUTO_INCREMENT值
可以使用多个列作为主键
CREATE TABLE bilibili (
cust_id INT NOT NULL AUTO_INCREMENT,
cust_name CHAR(50) NOT NULL,
cust_address CHAR(50) NULL,
PRIMARY KEY (cust_id,cust_name)
) ENGINE = INNODB
指定默认值DEFAULT
可以看到下列的prod_price列,在未给出具体价格的情况下,默认使用价格1。
另外,DEFAULT不允许使用函数,只支持常量。
CREATE TABLE cilicili (
cust_id INT NOT NULL AUTO_INCREMENT,
cust_name CHAR(50) NOT NULL,
cust_address CHAR(50) NULL,
cust_city CHAR(50) NULL,
prod_price INT NOT NULL DEFAULT 1,
PRIMARY KEY (cust_id)
) ENGINE = INNODB
引擎类型
指定引擎类型:可以看到,上面的创建表程序,末尾都有一句ENGINE=INNODB,这是用来指定用什么引擎的,几个常用的引擎有InnoDB,MyISAM,MEMORY。
引擎类型可以混用:也就是说同一个数据库内,创建表customers可以用引擎InnoDB,创建表orders时又可以使用引擎MyISAM。
混用引擎有一个缺陷:外键不能跨引擎,也就是说一个引擎类型的表不能引用其他引擎类型的表的外键。
ALTER TABLE更新表结构,ADD,DROP
前面学习的UPDATE只是更新表内的数据,而不涉及到更改表结构(比如说增删字段)。
#给表添加一个列,必须明确其数据类型
ALTER TABLE bilibili ADD vend_name CHAR(20);
#删除刚刚添加的列
ALTER TABLE bilibili DROP vend_name;
ALTER TABLE另一种常见用途是用来定义外键。
外键:是指引用另外一个表中的一列或多列数据,用来建立和加强两个表数据之间的连接,(被引用的列应该具有主键约束或者唯一性约束)。
格式为:ALTER TABLE 表名 ADD CONSTRAINT 自定义外键名称 FOREIGN KEY(外键字段名) REFERENCES 外表表名(主键字段名)
删除、重命名表
#删除表
DROP TABLE bilibili;
#重命名表
RENAME TABLE bilibili TO cilicili;
#重命名多个表
RENAME TABLE customers TO cu,
orderitems TO or1;