TASK3---表的删除，更新，更改

一. MySQL表数据类型

二. 用SQL语句创建表 

CREATE TABLE customers
(
  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.  名字在create table之后
2.  主键由primary key指定 ,主键的值必须唯一 
     但是可以有两个主键比如:primary key(order,item)表示这两个的组合值是唯一的 
     主键必须NOT NULL
3.  列名要指定数据类型，是否允许为空
4.  auto_increment自动增量 必须和主键组合使用,默认情形起始值为1，每次增量为1 
      select last_insert_id()返回最后一个auto_increment值
5.  指定默认值 default,看下例 

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;

6.  引擎类型 innofb memory myisam
    注意：不同引擎类型的表之间不能连接（外键不能连接）
 

三. 用SQL语句向表中添加数据 

INSERT...INTO 表名（字段名）VALUES（）；

INSERT...INTO 表名（字段名）SELECT（字段名）FROM (另一个表名）；

#保险做法 
insert into customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cuat_email)
values(null,'Pep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA',null,null);

注意：

1.列的数目要保持一致 

2.有的时候有些列可以省略 
---此列定义允许为null---表定义中给出默认值 

3.插入多个行

insert into customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cuat_email)
values(null,'Pep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA',null,null),
      (null,'M.Martian','42 Galaxy Way','New York','NY','11213','USA',null,null);

4.插入别的表的数据

##插入检索出的数据 insert select
insert into customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cuat_email)
select cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cuat_email
from custnew;
#把custnew中所有数据插入 customers
#这个例子中两个表名称一样，其实MySQL不关心名字对不对应，只要位置对应即可 
INSERT INTO ProductType (product_type, sum_sale_price,  sum_purchase_price) 

SELECT product_type, SUM(sale_price), SUM(purchase_price)  

FROM Product GROUP BY product_type;

 

四. 用SQL语句删除表

DROP 单独使用的时候是删除整个表

1.删除表 DROP TABLE <表名>;

  引入删除表中某个记录 DELETE FROM <表名> WHERE ...;见下

2.和alter一起使用的时候可以指定column删除某个列

alter table student
    drop column 专业;

DELETE 

1.delete语句会留下表（容器），而删除表中的数据 ，通常和where一起使用

delete语句不能使用groupby,having,order by子句
 

delete from Product
where sale_price>=4000;

 2.delete语句删除对象不是表或者列，而是行  
-- 如果删除单个列的值可以采用update将其更新为Null

update Product 
set regist_date=null
where product_id='0008';

TRUNCATE

TRUNCATE TABLE 表名

删除表中数据，很快，但是没法使用where

truncate table Product;

一句话总结：当你不再需要该表时， 用 drop；当你仍要保留该表，但要删除所有记录时， 用 truncate；当你要删除部分记录时（always with a WHERE clause), 用 delete

5. 用SQL语句修改表 修改列名 修改表中数据 删除行 删除列 新建列 新建行

UPDATE 更新表

UPDATE  表名 SET 字段名+操作 WHERE...

-- update...set...where...

update Product
set sale_price=sale_price*10
where product_type='厨房用具';

#使用null---变成清空 
update Product 
set regist_date=null
where product_id='0008';

-- 2.多列更新  
--  使用逗号对列进行分隔排列 
UPDATE Product   
SET sale_price = sale_price * 10, purchase_price = purchase_price / 2 
WHERE product_type = '厨房用具';

ALTER 改变表

ALTER TABLE 表名 ADD 列名 数据类型；

ALTER TABLE 表名 MODIFY 列名 数据类型；

ALTER TABLE 表名 DROP COLUMN 列名 ；

ALTER TABLE 表名 RENAME AS 新表名；

1.添加一条新的字段（列） add

alter table student
    add 专业 char(30);

2.改变原有字段的数据类型

alter table course
    modify 学分 smallint;

3.删除某个字段（行）

alter table student
    drop column 专业;

4.更改表的名字

alter table student
    rename as stu;

备注：

1.alter和insert都有插入的作用，但是alter插入的是列（字段），insert插入的是行（记录）

2.注意添加字段和删除字段不同，一个需要指明column一个不需要

3.alter和update都有更改表中某列数据的作用，但是alter更注重的是该列的数据结构，update则改变列的内容

当alter和drop一起使用删除某个列的时候，该列没有了，但update是该列变成null还是存在该列的    

作业

 

第三题代码： 

CREATE TABLE course 
(
student VARCHAR(50) NOT NULL,
class   VARCHAR(50) NOT NULL
);

INSERT INTO course(student,class) VALUES ('A','Math');
INSERT INTO course(student,class) VALUES ('B','English');
INSERT INTO course(student,class) VALUES ('C','Math');
INSERT INTO course(student,class) VALUES ('D','Biology');
INSERT INTO course(student,class) VALUES ('E','Math');
INSERT INTO course(student,class) VALUES ('F','Computer');
INSERT INTO course(student,class) VALUES ('G','Math');
INSERT INTO course(student,class) VALUES ('H','Math');
INSERT INTO course(student,class) VALUES ('I','Math');
INSERT INTO course(student,class) VALUES ('A','Math');

select class from course group by class having count(distinct student) >=5;

第四题代码：

CREATE TABLE salary
(
id INT NOT NULL ,
n_ame VARCHAR(50) NOT NULL,
sex VARCHAR(50) NOT NULL,
salary INT NOT NULL
);
INSERT INTO salary(id,n_ame,sex,salary) VALUES (1,'A','m',2500);
INSERT INTO salary(id,n_ame,sex,salary) VALUES (2,'B','f',1500);
INSERT INTO salary(id,n_ame,sex,salary) VALUES (3,'C','m',5500);
INSERT INTO salary(id,n_ame,sex,salary) VALUES (4,'D','f',500);


ALTER table salary add newid INT;
UPDATE salary 
   SET newid = 1 where sex = 'm';
UPDATE salary
   SET newid =2 where sex = 'f';
UPDATE salary
   SET sex = 'f' where newid = 1;
UPDATE salary
   SET sex = 'm' where newid = 2;
ALTER TABLE salary
 DROP COLUMN newid;

select * from salary;

 

问题：关于第四题我做的有点麻烦，看答案下来之后有没有更好的做法。

答案做法：

UPDATE salary 
   SET sex = CASE
               WHEN sex='m' THEN 'f'

               ELSE 'm'

              END