首页 > 数据库 >【SQL必知必会】-12月

【SQL必知必会】-12月

时间：2022-11-23 23:24:09浏览次数：56  
标签：12 cust 必知 order vend VALUES SQL prod id

了解Mysql

一般叫DBMS DataBase Management System 数据库管理系统

表 table : 一个结构化的文件。来存储数据。一个数据库中表名是唯一的。
列 column: 存储表中的某一部分信息。有一个或多个主键primary key 来区分唯一的记录/行，正确的将数据分成列很重要。
行 row: 有的叫记录record.

SQL: Structured Query Language 结构化查询语言。与数据库沟通的语言。
方便查询和读写。

kail linux登录数据库

样例表

创建数据库
create database sample
选择数据库
use database sample
四个任务

  • 管理供应商
  • 管理产品目录
  • 管理顾客列表
  • 录入顾客顶单
    5个表

1.Vendors表

存储销售产品的供应商
vend_id用于进行产品与供应商的匹配

列 说明
vend_id 唯一的供应商ID，主键
vend_name 供应商名
vend_address 供应商地址
vend_city 所在城市
vend_state 所在州
vend_zip 邮政编码
vend_country 所在国家

创建Vendors表

create table Vendors
(
vend_id char(10) NOT NULL,
vend_name char(50) NOT NULL,
vend_address char(50)  NULL,
vend_city char(50) NULL,
vend_state char(5)  NULL,
vend_zip char(10) NULL,
vend_country char(50) NULL
);

定义主键
alter table Vendors add primary key (vend_id);
image

Products表

列 说明
prod_id 唯一的产品ID
vend_id 供应商ID
prod_name 产品名
prod_price 产品价格
prod_desc 产品描述 
create table Products
(
prod_id char(10) NOT NULL,
vend_id char(10) NOT NULL,
prod_name char(255) NOT NULL,
prod_price decimal(8,2) NOT NULL,
prod_desc text NULL
);

定义主键
ALTER TABLE Products ADD PRIMARY KEY (prod_id);
关联外键

ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);

image

3.Customers表

列 说明
cust_id 唯一的顾客ID
cust_name 顾客名
cust_address 顾客地址
cust_city 城市
cust_state 州
cust_zip 邮政编码
cust_country 国家
cust_contact 联系名
cust_email 电子邮件 
CREATE TABLE Customers
(
  cust_id      char(10)  NOT NULL ,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL 
);
ALTER TABLE Customers ADD PRIMARY KEY (cust_id);

4.Orders表

列 说明
order_num 订单号
order_date 订单日期
cust_id 顾客ID 
CREATE TABLE Orders
(
  order_num  int      NOT NULL ,
  order_date datetime NOT NULL ,
  cust_id    char(10) NOT NULL 
);
ALTER TABLE Orders ADD PRIMARY KEY (order_num);

5.OrderItems 表

列 说明
order_num 订单号
order_item 订单物品号，订单内的顺序
prod_id 产品ID
quantity 物品数量
item_price 物品价格 
CREATE TABLE OrderItems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL 
);
ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item);
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);

插入数据

Customers

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard');

Vendors

INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FNG01','Fun and Games','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

Products

INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown');

Orders

INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20005, '2020-05-01', '1000000001');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20006, '2020-01-12', '1000000003');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20007, '2020-01-30', '1000000004');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20008, '2020-02-03', '1000000005');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20009, '2020-02-08', '1000000001');

OrderItems

INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'BR01', 100, 5.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'BR03', 100, 10.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'BR01', 20, 5.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 2, 'BR02', 10, 8.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 3, 'BR03', 10, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'BR03', 50, 11.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 2, 'BNBG01', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 3, 'BNBG02', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 4, 'BNBG03', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 5, 'RGAN01', 50, 4.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'RGAN01', 5, 4.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 2, 'BR03', 5, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 3, 'BNBG01', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 4, 'BNBG02', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 5, 'BNBG03', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'BNBG01', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'BNBG02', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'BNBG03', 250, 2.49);

检索数据

select 关键字
从哪里取 from
取哪些：select

获取具体某列的数据,多个列使用逗号分割。 空格会忽略。所有列使用*。


获取多少。 limit

从第多少行开始获取， offset

简化版 limit 5,2 从第5行开始，获取2行数据

获取唯一的数据，是多列组合唯一 distinct , 作用于所有select列

单行注释 -- 。多行注释 /* sdfasdf */

关键字不区分大小写，但是表名，列名区分。

排序检索数据 Order By 子句

未加Order By子句检索出来的数据没有任何顺序上的意义
子句clause . order by必须是select最后的一个子句。否则会出错。
sql由子句构成，有些子句是必须的，有些是可选的。 一个子句由一个关键字加上所提供的的数据组成。 from 也是子句。

根据某一列排序，默认是升序

降序 DESC 全名 descending, 在列名的后面。

根据排序的列名不一定要在检索的列名里

多个列名排序，每个列名后面写desc关键字，不能作用于所有列名。

where 子句

根据某列的值来选择

操作符

  • 不等于 ： <> 或者 !=
  • 某个范围内 between x1 and x2
  • NULL: IS NULL

注意：order by子句必须在where子句之后。

高级数据过滤，组合where 子句 and or .

多个过滤条件。and or 逻辑组合

求值顺序

括号 大于 and 大于 or

优先处理and操作符，导致理解为 vend_id为BRS01的价格大于5 和 所有的vend_id为 DLL01.

IN操作符

和多个and 一样。但是方便，直观，效率快。

IN还可以包含SELECT语句。

NOT操作符

否定 跟在其后的条件。

通配符进行过滤

前面的搜索条件都是具体已知的。

使用like操作符 和 % _ [] 通配符来搜索符合条件的数据

  • % 代表 0 个 1 个 和 多个 任意字符
  • _ 代表1个字符 __就是俩个
  • [] 中括号内的一个字符。 mysql不支持。


标签：12,cust,必知,order,vend,VALUES,SQL,prod,id
From： https://www.cnblogs.com/clllll/p/16909724.html

相关文章

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99