初识SQL

CREATE DATABASE shop; #数据库创建
CREATE TABLE product
(product_id CHAR(4) NOT NULL,
 product_name VARCHAR(100) NOT NULL,
 product_type VARCHAR(32) NOT NULL,
 sale_price INTEGER ,
 purchase_prise INTEGER ,
 regist_date DATE ,
 PRIMARY KEY (product_id));
 
  # 修改列：删除，更新
 DROP TABLE product;
 ALTER TABLE product DROP COLUMN product_type;
 ALTER TABLE product ADD COLUMN product_name_pinynig VARCHAR(100);
 DELETE FROM product WHERE COLUMN_NAME="produnt_name";
 TRUNCATE TABLE product;  # 具有更高的清除效率
 
 UPDATE product
 SET regist_date="2022-8-15";
 
 UPDATE  product
 SET sale_price = sale_price * 10, 
	 purchase_price = purchase_price / 2
 WHERE product_name = "厨房用具";
 
 # 插入数据
 CREATE TABLE productins (
    product_id CHAR(4) NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    product_type VARCHAR(32) NOT NULL,
    sale_price INTEGER DEFAULT 0,
    purchase_price INTEGER,
    regist_date DATE,
    PRIMARY KEY (product_id)
);

  
INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES("005","高压锅","厨房用具", 6800, 5000,"2022-8-15");
# 可以省略列名称
INSERT INTO productins VALUES("002","打孔器","办公用具",500,320,NULL),
							 ("003","运动T恤","生活用品",2000,1600,"2022-1-25");
-- 复制数据
INSERT INTO produnctins (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
FROM product;

SELECT 
    product_id
FROM
    productins;
    
# 创建索引
create table mytable
(
ID int not null,
username varchar(16) not null);
insert into mytable VALUE(1,"xiao");
CREATE INDEX MYID ON mytable(username);
SELECT 
    *
FROM
    mytable;
show index from mytable;

-- 练习
CREATE TABLE Addressbook
(
regist_no    INT             NOT NULL,
name         VARCHAR(128)    NOT NULL,
address      VARCHAR(128)    NOT NULL,
tel_no       CHAR(10),
mail_address CHAR(20),
PRIMARY KEY(regist_no));

ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL;
SELECT * FROM Addressbook;

SQL的数据库，表的创建，表的修改，插入和更新，并且认识了数据的基本格式和相关要求。