day2-MySQL必知必会学习2
一. 插入数据
insert是用来插入（或添加）行到数据库表的。插入可以用以下方式使用：
1) 插入完整的行
2) 插入行的一部分
3) 插入多行
4) 插入某些查询的结果
1. 插入完整的行
1.1 该类方法
mysql> insert into customers values(2000, 'Yangts', 'PingLiang Road 2588', 'Shanghai', 'CA', '2000100', 'China', 'UNICER', 'yangts@sznari.com'); Query OK, 1 row affected mysql> select * from customers; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 2000 | Yangts | PingLiang Road 2588 | Shanghai | CA | 2000100 | China | UNICER | yangts@sznari.com | | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 6 rows in set
mysql> insert into customers(cust_zip, cust_name, cust_address, cust_city, cust_state, cust_country, cust_contact, cust_email) values(1000122, 'zhyu', 'Changyanglu', 'shanghai', 'd', 'china','ins', 'w2@wq.com'); Query OK, 1 row affected mysql> select * from customers; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 2000 | Yangts | PingLiang Road 2588 | Shanghai | CA | 2000100 | China | UNICER | yangts@sznari.com | | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | | 21000 | Yangts | PingLiang Road 2588 | Shanghai | CA | 2000100 | China | UNICER | yangts@sznari.com | | 21001 | tanzn | PingLiang Road 2588 | Shanghai | CA | 2000100 | China | UNICER | yangts@sznari.com | | 21002 | zhyu | Changyanglu | shanghai | d | 111 | china | ins | w2@wq.com | | 21003 | zhyu | Changyanglu | shanghai | d | 1000122 | china | ins | w2@wq.com | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 10 rows in set 注意： 此类方法还可以省略列，这标识可以只给出某些列提供值。给其他列不提供值
2. 插入多行
2.1 多次使用insert语句
2.2 其中单条insert语句有多组值，每组值用一对圆括号括起来，用逗号分隔
mysql> insert into customers(cust_zip, cust_name, cust_address, cust_city, cust_state, cust_country, cust_contact) values(1000122,'yangts', 'Changyanglu', 'shanghai', 'd', 'china','ins'), (111, 'zhyu','Changyanglu1', 'shanghai1', 'd1', 'china','ins1'); Query OK, 2 rows affected Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from customers; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | | 21009 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | NULL | NULL | NULL | | 21010 | 200 Maple Lane | Coyote Inc. | Detroit | MI | 44444 | NULL | NULL | NULL | | 21011 | yangts | Changyanglu | shanghai | d | 1000122 | china | ins | NULL | | 21012 | zhyu | Changyanglu1 | shanghai1 | d1 | 111 | china | ins1 | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 9 rows in set
3. 插入检索的数据
INSERT一般用来给表插入一个指定列值的行。但是， INSERT还存在另一种形式，可以利用它将一条SELECT语句的结果插入表中
mysql> insert into customers(cust_name, cust_address, cust_city, cust_state, cust_zip) select cust_name, cust_address, cust_city, cust_state, cust_zip from customers where cust_id = 10001; Query OK, 1 row affected Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from customers; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | | 21009 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | NULL | NULL | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 6 rows in set
二、 更新和删除数据
1. 更新数据
mysql> update customers set cust_email = 'yangts@sznari.com' where cust_id = 10005; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0
2. 删除数据
mysql> delete from customers where cust_id = 21012; Query OK, 1 row affected
三、 创建和操纵表
1.1 表创建的基础
为使用CREATE TABLE 创建表，必须给出以下信息
1）新表的名字
2）表列的名字和定义，用逗号分隔
mysql> create table customers_new(cust_id int not null auto_increment, cust_name char(50) not null, cust_address char(50) null, primary key(cust_id)) engine=InnoDB; Query OK, 0 rows affected mysql> describe customers_new; +--------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(50) | NO | | NULL | | | cust_address | char(50) | YES | | NULL | | +--------------+----------+------+-----+---------+----------------+ 3 rows in set 注意： 如果仅仅想在一个表中不存在时才创建它，则需要在表名后给出 IF NOT EXISTS
1.2. 使用NULL值
允许NULL值的列允许在插入行时不给出该列的值， 不允许NULL值的列创建时必须有值。
1.3. 主键再介绍
mysql> create table orders_new (orders_num int not null, orders_item int not null, prod_id char(10) not null, item_price decimal(8,2) not null, primary key (orders_num, orders_item)) engine=InnoDB; Query OK, 0 rows affected mysql> describe orders_new; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | orders_num | int(11) | NO | PRI | NULL | | | orders_item | int(11) | NO | PRI | NULL | | | prod_id | char(10) | NO | | NULL | | | item_price | decimal(8,2) | NO | | NULL | | +-------------+--------------+------+-----+---------+-------+ 4 rows in set
1.4. 使用默认值
mysql> create table order_new(order_num int not null, order_item int not null, prod_id int not null default 1, primary key(order_num, order_item)) engine = InnoDB; Query OK, 0 rows affected mysql> describe order_new; +------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+-------+ | order_num | int(11) | NO | PRI | NULL | | | order_item | int(11) | NO | PRI | NULL | | | prod_id | int(11) | NO | | 1 | | +------------+---------+------+-----+---------+-------+ 3 rows in set
2. 更新表
使用 ALTER TABLE 更新表，但尽量避免更新，应在设计表时确定。
2.1 在表中增加一列
mysql> alter table order_new add order_name char(20); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> describe order_new; +------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+-------+ | order_num | int(11) | NO | PRI | NULL | | | order_item | int(11) | NO | PRI | NULL | | | prod_id | int(11) | NO | | 1 | | | order_name | char(20) | YES | | NULL | | +------------+----------+------+-----+---------+-------+ 4 rows in set
2.2 在表中删除一列
mysql> alter table order_new drop column prod_id; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> describe order_new; +------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+-------+ | order_num | int(11) | NO | PRI | NULL | | | order_item | int(11) | NO | PRI | NULL | | | order_name | char(20) | YES | | NULL | | +------------+----------+------+-----+---------+-------+ 3 rows in set
2.3 重命名表
mysql> rename table order_new to order_old; Query OK, 0 rows affected mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | customers | | customers_new | | order_old | | orderitems | | orders | | productnotes | | products | | vendors | +----------------+ 8 rows in set
浙公网安备 33010602011771号