学习资料为:sql基础教程【日】MICK
第1章数据库和SQL
1.SQL书写的基本规则
- SQL分句以分号结尾
- SQL语句不区分大小写
- 创建数据库
CREATE DATABASE shop;
- 创建表
USE shop; CREATE TABLE shoin( product_id CHAR(4) NOT NULL COMMENT "商品id", product_name VARCHAR(100) NOT NULL COMMENT "商品名称", product_cate VARCHAR(32) NOT NULL COMMENT "商品分类", product_price INTEGER COMMENT "销售价格", product_cost INTEGER COMMENT "进货单价", register_date DATE COMMENT "登记日期", PRIMARY KEY (product_id) );
- 删除表
DROP shop;
- 更新表定义
- 添加列
ALTER TABLE shoin ADD COLUMN product_nickname VARCHAR(50) COMMENT "商品别称";
- 删除列
ALTER TABLE shoin DROP COLUMN product_nickname;
- 插入数据
-- DML:插入数据 INSERT INTO shoin VALUES ('0001','T恤衫','衣服',1000,500,'2009-09-20'); INSERT INTO shoin VALUES ('0002','打孔器','办公用品',500,320,'2009-09-11'); INSERT INTO shoin VALUES ('0003','运动T恤衫','衣服',4000,2800,NULL); INSERT INTO shoin VALUES ('0004','菜刀','厨房用具',3000,2800,'2009-09-20'); INSERT INTO shoin VALUES ('0005','高压锅','厨房用具',6800,5000,'2009-01-15'); INSERT INTO shoin VALUES ('0006','叉子','厨房用具',500,NULL,'2009-09-20'); INSERT INTO shoin VALUES ('0007','擦菜板','厨房用具',880,790,'2009-04-28'); INSERT INTO shoin VALUES ('0008','圆珠笔','办公用品',100,NULL,'2009-11-11'); COMMIT;
第2章 查询基础
- 列的查询
- 查询三列
SELECT product_id,product_name,product_cost FROM shoin;
- 查询所有列
SELECT * FROM shoin;
- 设置列的别名
SELECT product_id as id, product_name as name, product_cost as cost FROM shoin;
- 设置常数
这里不要使用双引号
- 删除重复行
- 对一列使用关键字 DISTINCT
SELECT DISTINCT product_cate FROM shoin;
- 对多列使用关键字DISTINCT
- 根据where选择数据
SELECT product_name,product_cate FROM shoin WHERE product_cate = '衣服';
- 注释的书写方法
--单行注释/*和*/多行注释
