数据库——数据库的创建

--定义数据库
CREATE DATABASE bookstore


--基本表的定义

--用户表
CREATE TABLE userInfo
(
	userID INT PRIMARY KEY,/*列级完整性，userID为表的主键*/
	userName VARCHAR(20)NOT NULL UNIQUE,/*非空并具有唯一值约束*/
	sex VARCHAR(2) NOT NULL,
	passord  VARCHAR(8) NOT NULL,
	birthdate DATETIME,
	userState VARCHAR(20) NOT NULL
);
--图书类别表
CREATE TABLE category
(
categoryID Int PRIMARY KEY,
categoryName Varchar(40) NOT NULL,
description Varchar(250) 
);
--图书表
CREATE TABLE book
(
	bookID Int PRIMARY KEY,
	title Varchar(50) NOT NULL,
	author Varchar(50) NOT NULL,
	press Varchar(80) NOT NULL,
	price Numeric(17,2) NOT NULL,
	categoryID Int NOT NULL FOREIGN KEY REFERENCES  category(categoryID),
	stockAmount Int NOT NULL
);
--订单表
CREATE TABLE orderInfo
(
	orderID Int PRIMARY KEY,
	userID INT FOREIGN KEY REFERENCES userInfo(userID),
	payment Numeric(17,2),
	orderTime Datetime NOT NULL,
	orderState Varchar(20)
);
--订单明细表
CREATE TABLE orderBook
(
	orderID Int  FOREIGN  KEY REFERENCES orderInfo(orderID),
	bookID Int  FOREIGN  KEY REFERENCES book(bookID),
	quantity Int NOT NULL DEFAULT(1),
	PRIMARY KEY(orderID,bookID)
);

使用SQL语句完成对表的定义的修改

--给图书表增加如下约束条件：stockAmount必须大于0.
ALTER TABLE book
ADD CONSTRAINT stockAmount  check (stockAmount>0)
--给订单详情增加相应约束条件：quantity默认值为1，且必须大于0
ALTER TABLE orderBook
ADD CONSTRAINT D1 check(quantity>0)
ALTER TABLE orderBook
ADD CONSTRAINT D2 DEFAULT 1 FOR quantity
--给用户表增加相应约束条件:userName 必须为一
--add constraint 约束名 unique (列名[也可称为字段])
ALTER TABLE userInfo
ADD CONSTRAINT C3 UNIQUE (userName) 

--插入用户表
INSERT INTO userInfo VALUES(101,'张三','男','101','1993-8-19','正常使用');
INSERT INTO userInfo VALUES(102,'王丽','女','102','1984-2-18','正常使用');
INSERT INTO userInfo VALUES(103,'李明','男','103','1996-6-15','正常使用');
INSERT INTO userInfo VALUES(104,'张艳丽','女','104','1993-12-3','锁定');
INSERT INTO userInfo VALUES(105,'刘大海','男','105','1993-08-19','停用');
--插入category
INSERT INTO category VALUES(1,'理工类','自然科学，工程技术等')
INSERT INTO category VALUES(2,'人文社科类','哲学，经济，教育学等')
--插入book
INSERT INTO book VALUES(1001,'数据库系统原理','王珊','高等教育出版社',39,1,200)
INSERT INTO book VALUES(1002,'数据结构C语言版','严蔚敏','清华大学出版社',35,1,250)
INSERT INTO book VALUES(1003,'计算机网络','谢希仁','电子工业出版社',39,1,50)
INSERT INTO book VALUES(1004,'经济学原理','曼昆','清华大学出版社',64,2,25)
INSERT INTO book VALUES(1005,'中国哲学简史','冯友兰','北京大学出版社',38,2,10)
INSERT INTO book VALUES(1006,'教育心理学','莫雷','教学科学出版社',36,2,180)
--插入orderInfo
INSERT INTO orderInfo VALUES(2016001,101,109.00,'2016/8/1 7:56:32','已完成')
INSERT INTO orderInfo VALUES(2016002,102,138.00,'2016/8/3 8:34:38','已完成')
INSERT INTO orderInfo VALUES(2016003,102,143.00,'2016/8/3 21:34:53','已完成')
INSERT INTO orderInfo VALUES(2016004,102,117.00,'2016/8/7 8:50:29','未提交')
INSERT INTO orderInfo VALUES(2016005,103,117.00,'2016/8/8 15:50:28','已支付')
INSERT INTO orderInfo VALUES(2016006,103,73.00,'2016/8/8 23:28:28','已完成')
INSERT INTO orderInfo VALUES(2016007,104,175.00,'2016/8/9 12:50:33','已完成')
--插入orderBook
INSERT INTO orderBook VALUES(2016001,1001,1)
INSERT INTO orderBook VALUES(2016001,1002,2)
INSERT INTO orderBook VALUES(2016002,1004,1)
INSERT INTO orderBook VALUES(2016002,1005,1)
INSERT INTO orderBook VALUES(2016002,1006,1)
INSERT INTO orderBook VALUES(2016003,1002,1)
INSERT INTO orderBook VALUES(2016003,1006,3)
INSERT INTO orderBook VALUES(2016004,1001,2)
INSERT INTO orderBook VALUES(2016004,1003,1)
INSERT INTO orderBook VALUES(2016005,1001,2)
INSERT INTO orderBook VALUES(2016005,1003,1)
INSERT INTO orderBook VALUES(2016006,1002,1)
INSERT INTO orderBook VALUES(2016006,1005,1)
INSERT INTO orderBook VALUES(2016007,1001,1)
INSERT INTO orderBook VALUES(2016007,1004,1)
INSERT INTO orderBook VALUES(2016007,1006,2)

SELECT GETDATE() AS TIME
--2019-09-27 09:22:00.963
--转义的写法如下，使用ESCAPE指定转义符： (a%b)
SELECT * FROM dbo.Member WHERE Name LIKE '%~%%' ESCAPE '~'

https://www.w3school.com.cn/sql/func_datediff.asp
单行注释‘–’

多行注释
首‘/’
尾‘/’

快捷键注释
选中需要注释的语句
先Ctrl+k，再Ctrl+c

取消注释
先Crtrl+k，在Ctrl+u