SQL 基础用法语句

来源：互联网发布：java从入门到精通mobi 编辑：程序博客网时间：2024/06/04 18:08
--CREATE DATABASE PRODUCTMANAGER--USE PRODUCTMANAGER--CREATE TABLE CLASS(--ID INT PRIMARY KEY IDENTITY(1,1),--C_NAME VARCHAR(30) NOT NULL--)--USE PRODUCTMANAGER----创建商品表--CREATE TABLE PRODUCTS(--ID INT PRIMARY KEY IDENTITY(1,1),      --主键--P_NAME VARCHAR(50) NOT NULL,           --商品名--P_PRICE MONEY DEFAULT(1.0000),         --商品价格--P_STORAGE INT DEFAULT(0),              --商品库存--P_DATE DATETIME DEFAULT(GETDATE()),    --生产日期--P_CLASS INT REFERENCES CLASS(ID)       --分类外键--)----创建商品备份表（该表和PRODUCTS 结构一样）--CREATE TABLE PRODUCTS_BAK(--ID INT PRIMARY KEY IDENTITY(1,1),      --主键--P_NAME VARCHAR(50) NOT NULL,           --商品名--P_PRICE MONEY DEFAULT(1.0000),         --商品价格--P_STORAGE INT DEFAULT(0),              --商品库存--P_DATE DATETIME DEFAULT(GETDATE()),    --生产日期--P_CLASS INT REFERENCES CLASS(ID)       --分类外键--)--USE PRODUCTMANAGER----向商品分类表插入第1条数据--INSERT INTO CLASS(C_NAME) VALUES('家用电器')----向商品表插入数据--INSERT INTO PRODUCTS(P_NAME,P_PRICE,P_STORAGE,p_DATE,P_CLASS) VALUES('海尔洗衣机',3599.99,20,'2009-8-20',1)----向商品分类插入第2到4条数据--INSERT INTO CLASS(C_NAME) VALUES('商用电器')--INSERT INTO PRODUCTS(P_NAME,P_PRICE,P_STORAGE,P_DATE,P_CLASS) VALUES('海尔洗衣机',3599.99,20,'2009-8-20',1)--INSERT INTO PRODUCTS(P_NAME,P_PRICE,p_STORAGE,P_DATE,P_CLASS) VALUES('指纹记录器',800,10,'2009-10-20',2)--INSERT INTO PRODUCTS(P_NAME,P_PRICE,p_STORAGE,P_DATE,P_CLASS) VALUES('海尔洗衣机',3599.99,20,'2009-8-20',1)---- 后面的值2，值3也可以使表达式 ，比如--INSERT INTO PRODUCTS(P_NAME,P_PRICE,p_STORAGE,P_DATE,P_CLASS) VALUES('小豆浆机',200*0.9,30+1,'2009-8-20',1)----查看数据--SELECT *FROM CLASS--SELECT * FROM PRODUCTS--USE PRODUCTMANAGER--SELECT * FROM CLASS--SELECT * FROM PRODUCTS---- 按列查询--USE PRODUCTMANAGER--SELECT '产品名称',P_NAME,'产品价格',P_PRICE  FROM PRODUCTS--USE PRODUCTMANAGER--SELECT '产品名称' = P_NAME,'总价'=P_STORAGE*P_PRICE  FROM PRODUCTS--USE PRODUCTMANAGER--SELECT DISTINCT P_NAME FROM PRODUCTS--USE PRODUCTMANAGER--SELECT TOP 3 * FROM PRODUCTS--SELECT TOP (80)PERCENT ID,P_NAME FROM PRODUCTS--SELECT TOP 4 ID,P_NAME FROM PRODUCTS--pa--SELECT P_NAME,P_STORAGE,P_PRICE FROM PRODUCTS ORDER BY P_PRICE--SELECT * FROM PRODUCTS WHERE ID>3--SELECT ID,P_NAME FROM PRODUCTS WHERE P_NAME LIKE '%机%' --SELECT *FROM  PRODUCTS WHERE P_NAME LIKE '海____'--USE PRODUCTMANAGER--SELECT * FROM PRODUCTS WHERE P_NAME LIKE  '_[^尔和哈厚]%'--SELECT * FROM PRODUCTS WHERE P_nAME LIKE '%机%' AND P_STORAGE>10--SELECT * FROM PRODUCTS WHERE ID = 1 OR ID =3 OR ID =5--SELECT * FROM PRODUCTS WHERE ID IN(1,3,5)--SELECT * FROM PRODUCTS WHERE ID>=2 AND ID <=5--SELECT * FROM PRODUCTS WHERE ID BETWEEN 2 AND 5--判断后面的是否为真 为真 就查询 子查询没查到就不执行--SELECT * FROM PRODUCTS WHERE EXISTS (SELECT *FROM PRODUCTS WHERE ID =5)USE PRODUCTMANAGER--SELECT * FROM PRODUCTS--UPDATE PRODUCTS SET  P_PRICE = P_PRICE*0.8--SELECT *FROM PRODUCTS--UPDATE PRODUCTS SET P_PRICE = 0 WHERE ID = 2 --SELECT * FROM PRODUCTS --DELETE FROM PRODUCTS WHERE ID =2 --SELECT * FROM PRODUCTS DELETE FROM PRODUCTS
0 0