SQL server 学习笔记
目录
一、数据库基础操作
1.表的创建
--创建商店数据库
CREATE DATABASE shop;
--创建商品信息表
CREATE TABLE MER
(MER_id CHAR(4) NOT NULL,
MER_name VARCHAR(100) NOT NULL,
kind VARCHAR(32) NOT NULL,
sellprice INTEGER ,
purchaseprice INTEGER ,
indate DATE ,
PRIMARY KEY (MER_id));
2.表的删除与更新
--删除商品表MER
DROP TABLE MER;
--更新商品表MER
ALTER TABLE MER ADD meikana VARCHAR(100);
--删除meikana列
ALTER TABLE MER DROP COLUMN meikana;
--DML:向MER表中插入数据
BEGIN TRANSACTION;
INSERT INTO MER VALUES('0001','T恤衫','衣服',100,50,'2018-09-20');
INSERT INTO MER VALUES('0002','打孔器','办公用品',25,32,'2018-09-11');
INSERT INTO MER VALUES('0003','运动服','衣服',150,110,'2018-09-10');
INSERT INTO MER VALUES('0004','菜刀','厨房用具',20,15,'2018-01-25');
INSERT INTO MER VALUES('0005','高压锅','厨房用具',450,400,'2018-08-10');
INSERT INTO MER VALUES('0006','叉子','厨房用具',10,7,'2018-09-20');
INSERT INTO MER VALUES('0007','切菜板','厨房用具',70,60,'2018-04-20');
INSERT INTO MER VALUES('0008','圆珠笔','办公用品',10,8,'2018-05-15');
COMMIT;
二、查询操作基础
1.SELECT语句
--简单列的查询
SELECT KIND FROM MER;
--查询商品表MER中的商品编号、名称以及进货单价
SELECT MER_id,MER_name,purchaseprice
FROM MER;
--查询出表中所有列
SELECT *
FROM MER;
--为列设置别名
SELECT MER_id AS "商品编号",
MER_name AS "商品名称",
purchaseprice AS "进货单价"
FROM MER;
--根据WHERE语句来选择记录
SELECT kind,MER_name
FROM MER
WHERE kind='厨房用具';
2.算术运算符和比较运算符
--读取各个商品单价的两倍
SELECT MER_name , sellprice ,
sellprice * 2 AS "sellprice_x2"
FROM MER;
--选出单价大于等于50的记录
SELECT MER_name , kind,sellprice
FROM MER
WHERE sellprice >=50;
3.逻辑运算符
--选出2018-01-25或2018-09-20日登记的厨房用具
SELECT MER_name , kind,indate
FROM MER
WHERE kind='厨房用具'
AND ( indate='2018-01-25'
OR indate='2018-09-20' );
三、聚合与排序
1.对表进行聚合查询
--聚合函数:
COUNT 计算表中的记录数(行数)
SUM 计算表中数值列的数据合计值
AVG 计算表中数值列的数据平均值
MAX 求出表中任意列中数据的最大值
MIN 求出表中任意列中数据的最小值
2.对表进行分组
3.对查询结果进行排序
四、数据更新
1.数据的插入(INSERT语句)
--向表中插入几行数据
INSERT INTO MER VALUES('0009','羽绒服','衣服',750,550,'2018-07-15');
INSERT INTO MER VALUES('0010','笔记本','办公用品',15,12,'2018-09-11');
INSERT INTO MER VALUES('0011','休闲裤','衣服',200,156,'2018-05-19');
INSERT INTO MER VALUES('0012','电磁炉','厨房用具',580,415,'2018-10-01');
SELECT *
FROM MER;
--查询结果
2.数据的删除(DELETE语句)
--删除销售单价大于等于500的DELETE语句
DELETE FROM MER
WHERE sellprice >=500;
--删除指定行的DELETE语句
DELETE FROM MER
WHERE MER_id='0011';
3.数据的更新(UPDATE语句)
--将登记日期全部更新为“2018-10-01”
UPDATE MER
SET indate='2018-10-01';
SELECT *
FROM MER ORDER BY MER_id;
--查询结果
五、复杂查询
1.视图
--创建视图
CREATE VIEW MERSum(kind,shopcount)
AS
SELECT kind,COUNT(*)
FROM MER
GROUP BY kind;
SELECT shopkind,shopcount
FROM MERSum;
2.子查询
--创建子查询
SELECT KIND,SHOP_COUNT
FROM ( SELECT KIND,COUNT(*)AS SHOP_COUNT
FROM MER
GROUP BY KIND)
AS MER_SUM;
--查询结果
六、表的连接
1. 内连接
--创建第二张商品表MER2
CREATE TABLE MERT
(MER_id CHAR(4) NOT NULL,
MER_name VARCHAR(100) NOT NULL,
company VARCHAR(32) NOT NULL,
amount INTEGER ,
shop_id VARCHAR(100) NOT NULL,
PRIMARY KEY (MER_id)
);
--向表中插入数据
BEGIN TRANSACTION;
INSERT INTO MERT VALUES('0001','T恤衫','优衣库',30, '000A' );
INSERT INTO MERT VALUES('0002','打孔器','得力',50,'000B' );
INSERT INTO MERT VALUES('0003','运动服','优衣库',100,'000A' );
INSERT INTO MERT VALUES('0004','菜刀','厨房用具',20,'000C' );
INSERT INTO MERT VALUES('0005','高压锅','厨房用具',15,'000C');
INSERT INTO MERT VALUES('0006','叉子','厨房用具',25,'000C' );
INSERT INTO MERT VALUES('0007','切菜板','厨房用具',10,'000C');
INSERT INTO MERT VALUES('0008','圆珠笔','得力',100,'000B' );
COMMIT;
SELECT *
FROM MERT;
--查询结果
--内连接
SELECT MT.company,MT.amount,MT.shop_id,M.MER_id ,M.MER_name ,M.kind ,M.indate
FROM MERT AS MT INNER JOIN MER AS M
ON MT.MER_id=M.MER_id;
2. 外连接
--外连接
SELECT MT.company,MT.amount,MT.shop_id,
M.MER_id ,M.MER_name ,M.kind ,
M.indate,M.purchaseprice ,M.sellprice
FROM MERT AS MT RIGHT OUTER JOIN MER AS M
ON MT.MER_id=M.MER_id;
--查询结果