11.15 SQL学习代码

#11.12
SELECT* FROM persons;
ALTER TABLE persons
ADD DateOfBirth date; #在 "Persons" 表中添加一个名为 "DateOfBirth" 的列。
ALTER TABLE persons
MODIFY column DateOfBirth year;#改变 "Persons" 表中 "DateOfBirth" 列的数据类型

ALTER TABLE persons
DROP column DateOfBirth; #删除 "Person" 表中的 "DateOfBirth" 列

CREATE TABLE renmen
(
renmenID int NOT NULL AUTO_INCREMENT,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY key(renmenID)
);
ALTER TABLE renmen AUTO_INCREMENT = 100;
SELECT * from renmen;
INSERT INTO renmen(LastName,FirstName,Address,City)
VALUES('Hansen','Ola','Timpteivn10','Sandnes'),('Svendson','Tove','Borgvn23','Sandnes'),('Pettersen','Kair','Storgt20','Stavanger');
SELECT * FROM renmen;
INSERT INTO renmen(FirstName,LastName)
VALUES ('Lars','Monsen');
SELECT * FROM renmen;
#创建products表
CREATE TABLE Products
(
  prod_id      char(10)       NOT NULL ,
  vend_id      char(10)       NOT NULL ,
  prod_name    char(255)      NOT NULL ,
  prod_price   decimal(8,2)   NOT NULL ,
  prod_desc    text           NULL ,
  PRIMARY KEY (prod_id)
);

#插入数据
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket');

INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket');

INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket');

INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it');

INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included');

INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots');

INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll');

INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown');

INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown');
SELECT * FROM products;
#价格为3.49的商品的视图
CREATE VIEW JIAGE_WWEI_SANSIJIU AS
SELECT prod_id,vend_id, prod_name
FROM products
WHERE prod_price = 3.49;
SELECT*FROM JIAGE_WWEI_SANSIJIU;
CREATE VIEW FIshing AS
SELECT prod_id,vend_id
FROM JIAGE_WWEI_SANSIJIU
WHERE prod_name = 'Fish bean bag toy';
SELECT*FROM FIshing;
#在价格为3.49的商品的视图添加其价格
ALTER VIEW JIAGE_WWEI_SANSIJIU AS
SELECT prod_id,vend_id, prod_name,prod_price
FROM products
WHERE prod_price = 3.49;
SELECT*FROM JIAGE_WWEI_SANSIJIU;
SELECT*FROM orders;
#创建order表
CREATE TABLE Orders
(
  order_num   int        NOT NULL ,
  order_date  datetime   NOT NULL ,
  cust_id     char(10)   NOT NULL ,
  primary key(order_num)
);
#添加数据
INSERT INTO Orders(order_num, order_date, cust_id) VALUES(20005, '2012-05-01', '1000000001');
INSERT INTO Orders(order_num, order_date, cust_id) VALUES(20006, '2012-01-12', '1000000003');
INSERT INTO Orders(order_num, order_date, cust_id) VALUES(20007, '2012-01-30', '1000000004');
INSERT INTO Orders(order_num, order_date, cust_id) VALUES(20008, '2012-02-03', '1000000005');
INSERT INTO Orders(order_num, order_date, cust_id) VALUES(20009, '2012-02-08', '1000000001');
#选取在 "Address" 列中带有 NULL 值的记录
select renmenID, LastName,FirstName from renmen where Address IS NULL;
#选取在 "Address" 列中不带有 NULL 值的记录
select renmenID, LastName,FirstName from renmen where Address IS NOT NULL;
CREATE TABLE CHANPIN
(
ProductName varchar(255),
UnitPrice int,
UnitStock int,
UnitOnOrder int
);
INSERT into CHANPIN(ProductName,UnitPrice,UnitStock,UnitOnOrder) VALUES('Jarlsberg',10.45,16,15);
INSERT into CHANPIN(ProductName,UnitPrice,UnitStock) VALUES('Mascarpone',32.56,23);
INSERT into CHANPIN(ProductName,UnitPrice,UnitStock,UnitOnOrder) VALUES('Gorgonzola',15.67,9,20);
SELECT * FROM chanpin;
SELECT ProductName,UnitPrice*(UnitStock+UnitOnOrder)
FROM CHANPIN;
SELECT ProductName,UnitPrice*(UnitStock+IFNULL(UnitOnOrder,0))
FROM chanpin;
SELECT ProductName,UnitPrice*(UnitStock+COALESCE(UnitOnOrder,0))
FROM chanpin;