数据库 Database 查询数据

最新推荐文章于 2021-08-23 12:14:14 发布
最新推荐文章于 2021-08-23 12:14:14 发布
阅读量742 收藏
点赞数
分类专栏： 数据库学习 文章标签： mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xiaoyasister/article/details/116238261
版权

关于数据库的一些笔记

数据库分类

关系型数据库：Relational Databases
非关系型数据库：NoSQL

1. Relational Databases（关系型数据库）

在关系型数据库中，把数据存储在利用关系互相链接的表中

一些默认规则

因为mysql是不区分大小写的，所以默认规定关键字用大写，其余都用小写。这样比较规范（看着舒服）
一些关键字：
USE
FROM
WHERE
ORDER BY(排序)
AS(起别名)
DISTINCT(去重)
LIMIT（限制）

注释语句：

#【需要注释的话】

选择要使用的数据库语句

USE [数据库名称] ;

1.Retrieving Data（查询数据）

创建数据库和表

提供一个创造数据库和表的sql,如下
可以复制粘贴到SQLyog使用

#创建一个数据库sql_invoicing
DROP DATABASE IF EXISTS `sql_invoicing`;
CREATE DATABASE `sql_invoicing`; 
USE `sql_invoicing`;

SET NAMES utf8 ;
SET character_set_client = utf8mb4 ;

#创建一个表payment_methods
CREATE TABLE `payment_methods` (
  `payment_method_id` TINYINT(4) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`payment_method_id`)
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `payment_methods` VALUES (1,'Credit Card');
INSERT INTO `payment_methods` VALUES (2,'Cash');
INSERT INTO `payment_methods` VALUES (3,'PayPal');
INSERT INTO `payment_methods` VALUES (4,'Wire Transfer');

#创建一个表clients
CREATE TABLE `clients` (
  `client_id` INT(11) NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `address` VARCHAR(50) NOT NULL,
  `city` VARCHAR(50) NOT NULL,
  `state` CHAR(2) NOT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`client_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `clients` VALUES (1,'Vinte','3 Nevada Parkway','Syracuse','NY','315-252-7305');
INSERT INTO `clients` VALUES (2,'Myworks','34267 Glendale Parkway','Huntington','WV','304-659-1170');
INSERT INTO `clients` VALUES (3,'Yadel','096 Pawling Parkway','San Francisco','CA','415-144-6037');
INSERT INTO `clients` VALUES (4,'Kwideo','81674 Westerfield Circle','Waco','TX','254-750-0784');
INSERT INTO `clients` VALUES (5,'Topiclounge','0863 Farmco Road','Portland','OR','971-888-9129');

#创建一个表invoices
CREATE TABLE `invoices` (
  `invoice_id` INT(11) NOT NULL,
  `number` VARCHAR(50) NOT NULL,
  `client_id` INT(11) NOT NULL,
  `invoice_total` DECIMAL(9,2) NOT NULL,
  `payment_total` DECIMAL(9,2) NOT NULL DEFAULT '0.00',
  `invoice_date` DATE NOT NULL,
  `due_date` DATE NOT NULL,
  `payment_date` DATE DEFAULT NULL,
  PRIMARY KEY (`invoice_id`),
  KEY `FK_client_id` (`client_id`),
  CONSTRAINT `FK_client_id` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `invoices` VALUES (1,'91-953-3396',2,101.79,0.00,'2019-03-09','2019-03-29',NULL);
INSERT INTO `invoices` VALUES (2,'03-898-6735',5,175.32,8.18,'2019-06-11','2019-07-01','2019-02-12');
INSERT INTO `invoices` VALUES (3,'20-228-0335',5,147.99,0.00,'2019-07-31','2019-08-20',NULL);
INSERT INTO `invoices` VALUES (4,'56-934-0748',3,152.21,0.00,'2019-03-08','2019-03-28',NULL);
INSERT INTO `invoices` VALUES (5,'87-052-3121',5,169.36,0.00,'2019-07-18','2019-08-07',NULL);
INSERT INTO `invoices` VALUES (6,'75-587-6626',1,157.78,74.55,'2019-01-29','2019-02-18','2019-01-03');
INSERT INTO `invoices` VALUES (7,'68-093-9863',3,133.87,0.00,'2019-09-04','2019-09-24',NULL);
INSERT INTO `invoices` VALUES (8,'78-145-1093',1,189.12,0.00,'2019-05-20','2019-06-09',NULL);
INSERT INTO `invoices` VALUES (9,'77-593-0081',5,172.17,0.00,'2019-07-09','2019-07-29',NULL);
INSERT INTO `invoices` VALUES (10,'48-266-1517',1,159.50,0.00,'2019-06-30','2019-07-20',NULL);
INSERT INTO `invoices` VALUES (11,'20-848-0181',3,126.15,0.03,'2019-01-07','2019-01-27','2019-01-11');
INSERT INTO `invoices` VALUES (13,'41-666-1035',5,135.01,87.44,'2019-06-25','2019-07-15','2019-01-26');
INSERT INTO `invoices` VALUES (15,'55-105-9605',3,167.29,80.31,'2019-11-25','2019-12-15','2019-01-15');
INSERT INTO `invoices` VALUES (16,'10-451-8824',1,162.02,0.00,'2019-03-30','2019-04-19',NULL);
INSERT INTO `invoices` VALUES (17,'33-615-4694',3,126.38,68.10,'2019-07-30','2019-08-19','2019-01-15');
INSERT INTO `invoices` VALUES (18,'52-269-9803',5,180.17,42.77,'2019-05-23','2019-06-12','2019-01-08');
INSERT INTO `invoices` VALUES (19,'83-559-4105',1,134.47,0.00,'2019-11-23','2019-12-13',NULL);

#创建一个表payments
CREATE TABLE `payments` (
  `payment_id` INT(11) NOT NULL AUTO_INCREMENT,
  `client_id` INT(11) NOT NULL,
  `invoice_id` INT(11) NOT NULL,
  `date` DATE NOT NULL,
  `amount` DECIMAL(9,2) NOT NULL,
  `payment_method` TINYINT(4) NOT NULL,
  PRIMARY KEY (`payment_id`),
  KEY `fk_client_id_idx` (`client_id`),
  KEY `fk_invoice_id_idx` (`invoice_id`),
  KEY `fk_payment_payment_method_idx` (`payment_method`),
  CONSTRAINT `fk_payment_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`invoice_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_payment_method` FOREIGN KEY (`payment_method`) REFERENCES `payment_methods` (`payment_method_id`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `payments` VALUES (1,5,2,'2019-02-12',8.18,1);
INSERT INTO `payments` VALUES (2,1,6,'2019-01-03',74.55,1);
INSERT INTO `payments` VALUES (3,3,11,'2019-01-11',0.03,1);
INSERT INTO `payments` VALUES (4,5,13,'2019-01-26',87.44,1);
INSERT INTO `payments` VALUES (5,3,15,'2019-01-15',80.31,1);
INSERT INTO `payments` VALUES (6,3,17,'2019-01-15',68.10,1);
INSERT INTO `payments` VALUES (7,5,18,'2019-01-08',32.77,1);
INSERT INTO `payments` VALUES (8,5,18,'2019-01-08',10.00,2);


DROP DATABASE IF EXISTS `sql_store`;
CREATE DATABASE `sql_store`;
USE `sql_store`;

CREATE TABLE `products` (
  `product_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `quantity_in_stock` INT(11) NOT NULL,
  `unit_price` DECIMAL(4,2) NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=INNODB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `products` VALUES (1,'Foam Dinner Plate',70,1.21);
INSERT INTO `products` VALUES (2,'Pork - Bacon,back Peameal',49,4.65);
INSERT INTO `products` VALUES (3,'Lettuce - Romaine, Heart',38,3.35);
INSERT INTO `products` VALUES (4,'Brocolinni - Gaylan, Chinese',90,4.53);
INSERT INTO `products` VALUES (5,'Sauce - Ranch Dressing',94,1.63);
INSERT INTO `products` VALUES (6,'Petit Baguette',14,2.39);
INSERT INTO `products` VALUES (7,'Sweet Pea Sprouts',98,3.29);
INSERT INTO `products` VALUES (8,'Island Oasis - Raspberry',26,0.74);
INSERT INTO `products` VALUES (9,'Longan',67,2.26);
INSERT INTO `products` VALUES (10,'Broom - Push',6,1.09);


CREATE TABLE `shippers` (
  `shipper_id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`shipper_id`)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `shippers` VALUES (1,'Hettinger LLC');
INSERT INTO `shippers` VALUES (2,'Schinner-Predovic');
INSERT INTO `shippers` VALUES (3,'Satterfield LLC');
INSERT INTO `shippers` VALUES (4,'Mraz, Renner and Nolan');
INSERT INTO `shippers` VALUES (5,'Waters, Mayert and Prohaska');


CREATE TABLE `customers` (
  `customer_id` INT(11) NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(50) NOT NULL,
  `last_name` VARCHAR(50) NOT NULL,
  `birth_date` DATE DEFAULT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  `address` VARCHAR(50) NOT NULL,
  `city` VARCHAR(50) NOT NULL,
  `state` CHAR(2) NOT NULL,
  `points` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`customer_id`)
) ENGINE=INNODB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `customers` VALUES (1,'Babara','MacCaffrey','1986-03-28','781-932-9754','0 Sage Terrace','Waltham','MA',2273);
INSERT INTO `customers` VALUES (2,'Ines','Brushfield','1986-04-13','804-427-9456','14187 Commercial Trail','Hampton','VA',947);
INSERT INTO `customers` VALUES (3,'Freddi','Boagey','1985-02-07','719-724-7869','251 Springs Junction','Colorado Springs','CO',2967);
INSERT INTO `customers` VALUES (4,'Ambur','Roseburgh','1974-04-14','407-231-8017','30 Arapahoe Terrace','Orlando','FL',457);
INSERT INTO `customers` VALUES (5,'Clemmie','Betchley','1973-11-07',NULL,'5 Spohn Circle','Arlington','TX',3675);
INSERT INTO `customers` VALUES (6,'Elka','Twiddell','1991-09-04','312-480-8498','7 Manley Drive','Chicago','IL',3073);
INSERT INTO `customers` VALUES (7,'Ilene','Dowson','1964-08-30','615-641-4759','50 Lillian Crossing','Nashville','TN',1672);
INSERT INTO `customers` VALUES (8,'Thacher','Naseby','1993-07-17','941-527-3977','538 Mosinee Center','Sarasota','FL',205);
INSERT INTO `customers` VALUES (9,'Romola','Rumgay','1992-05-23','559-181-3744','3520 Ohio Trail','Visalia','CA',1486);
INSERT INTO `customers` VALUES (10,'Levy','Mynett','1969-10-13','404-246-3370','68 Lawn Avenue','Atlanta','GA',796);


CREATE TABLE `order_statuses` (
  `order_status_id` TINYINT(4) NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`order_status_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `order_statuses` VALUES (1,'Processed');
INSERT INTO `order_statuses` VALUES (2,'Shipped');
INSERT INTO `order_statuses` VALUES (3,'Delivered');


CREATE TABLE `orders` (
  `order_id` INT(11) NOT NULL AUTO_INCREMENT,
  `customer_id` INT(11) NOT NULL,
  `order_date` DATE NOT NULL,
  `status` TINYINT(4) NOT NULL DEFAULT '1',
  `comments` VARCHAR(2000) DEFAULT NULL,
  `shipped_date` DATE DEFAULT NULL,
  `shipper_id` SMALLINT(6) DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `fk_orders_customers_idx` (`customer_id`),
  KEY `fk_orders_shippers_idx` (`shipper_id`),
  KEY `fk_orders_order_statuses_idx` (`status`),
  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_orders_order_statuses` FOREIGN KEY (`status`) REFERENCES `order_statuses` (`order_status_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_orders_shippers` FOREIGN KEY (`shipper_id`) REFERENCES `shippers` (`shipper_id`) ON UPDATE CASCADE
) ENGINE=INNODB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `orders` VALUES (1,6,'2019-01-30',1,NULL,NULL,NULL);
INSERT INTO `orders` VALUES (2,7,'2018-08-02',2,NULL,'2018-08-03',4);
INSERT INTO `orders` VALUES (3,8,'2017-12-01',1,NULL,NULL,NULL);
INSERT INTO `orders` VALUES (4,2,'2017-01-22',1,NULL,NULL,NULL);
INSERT INTO `orders` VALUES (5,5,'2017-08-25',2,'','2017-08-26',3);
INSERT INTO `orders` VALUES (6,10,'2018-11-18',1,'Aliquam erat volutpat. In congue.',NULL,NULL);
INSERT INTO `orders` VALUES (7,2,'2018-09-22',2,NULL,'2018-09-23',4);
INSERT INTO `orders` VALUES (8,5,'2018-06-08',1,'Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis.',NULL,NULL);
INSERT INTO `orders` VALUES (9,10,'2017-07-05',2,'Nulla mollis molestie lorem. Quisque ut erat.','2017-07-06',1);
INSERT INTO `orders` VALUES (10,6,'2018-04-22',2,NULL,'2018-04-23',2);


CREATE TABLE `order_items` (
  `order_id` INT(11) NOT NULL AUTO_INCREMENT,
  `product_id` INT(11) NOT NULL,
  `quantity` INT(11) NOT NULL,
  `unit_price` DECIMAL(4,2) NOT NULL,
  PRIMARY KEY (`order_id`,`product_id`),
  KEY `fk_order_items_products_idx` (`product_id`),
  CONSTRAINT `fk_order_items_orders` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_order_items_products` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON UPDATE CASCADE
) ENGINE=INNODB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `order_items` VALUES (1,4,4,3.74);
INSERT INTO `order_items` VALUES (2,1,2,9.10);
INSERT INTO `order_items` VALUES (2,4,4,1.66);
INSERT INTO `order_items` VALUES (2,6,2,2.94);
INSERT INTO `order_items` VALUES (3,3,10,9.12);
INSERT INTO `order_items` VALUES (4,3,7,6.99);
INSERT INTO `order_items` VALUES (4,10,7,6.40);
INSERT INTO `order_items` VALUES (5,2,3,9.89);
INSERT INTO `order_items` VALUES (6,1,4,8.65);
INSERT INTO `order_items` VALUES (6,2,4,3.28);
INSERT INTO `order_items` VALUES (6,3,4,7.46);
INSERT INTO `order_items` VALUES (6,5,1,3.45);
INSERT INTO `order_items` VALUES (7,3,7,9.17);
INSERT INTO `order_items` VALUES (8,5,2,6.94);
INSERT INTO `order_items` VALUES (8,8,2,8.59);
INSERT INTO `order_items` VALUES (9,6,5,7.28);
INSERT INTO `order_items` VALUES (10,1,10,6.01);
INSERT INTO `order_items` VALUES (10,9,9,4.28);

CREATE TABLE `sql_store`.`order_item_notes` (
  `note_id` INT NOT NULL,
  `order_Id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `note` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`note_id`));

INSERT INTO `order_item_notes` (`note_id`, `order_Id`, `product_id`, `note`) VALUES ('1', '1', '2', 'first note');
INSERT INTO `order_item_notes` (`note_id`, `order_Id`, `product_id`, `note`) VALUES ('2', '1', '2', 'second note');


DROP DATABASE IF EXISTS `sql_hr`;
CREATE DATABASE `sql_hr`;
USE `sql_hr`;


CREATE TABLE `offices` (
  `office_id` INT(11) NOT NULL,
  `address` VARCHAR(50) NOT NULL,
  `city` VARCHAR(50) NOT NULL,
  `state` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`office_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `offices` VALUES (1,'03 Reinke Trail','Cincinnati','OH');
INSERT INTO `offices` VALUES (2,'5507 Becker Terrace','New York City','NY');
INSERT INTO `offices` VALUES (3,'54 Northland Court','Richmond','VA');
INSERT INTO `offices` VALUES (4,'08 South Crossing','Cincinnati','OH');
INSERT INTO `offices` VALUES (5,'553 Maple Drive','Minneapolis','MN');
INSERT INTO `offices` VALUES (6,'23 North Plaza','Aurora','CO');
INSERT INTO `offices` VALUES (7,'9658 Wayridge Court','Boise','ID');
INSERT INTO `offices` VALUES (8,'9 Grayhawk Trail','New York City','NY');
INSERT INTO `offices` VALUES (9,'16862 Westend Hill','Knoxville','TN');
INSERT INTO `offices` VALUES (10,'4 Bluestem Parkway','Savannah','GA');



CREATE TABLE `employees` (
  `employee_id` INT(11) NOT NULL,
  `first_name` VARCHAR(50) NOT NULL,
  `last_name` VARCHAR(50) NOT NULL,
  `job_title` VARCHAR(50) NOT NULL,
  `salary` INT(11) NOT NULL,
  `reports_to` INT(11) DEFAULT NULL,
  `office_id` INT(11) NOT NULL,
  PRIMARY KEY (`employee_id`),
  KEY `fk_employees_offices_idx` (`office_id`),
  KEY `fk_employees_employees_idx` (`reports_to`),
  CONSTRAINT `fk_employees_managers` FOREIGN KEY (`reports_to`) REFERENCES `employees` (`employee_id`),
  CONSTRAINT `fk_employees_offices` FOREIGN KEY (`office_id`) REFERENCES `offices` (`office_id`) ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `employees` VALUES (37270,'Yovonnda','Magrannell','Executive Secretary',63996,NULL,10);
INSERT INTO `employees` VALUES (33391,'D\'arcy','Nortunen','Account Executive',62871,37270,1);
INSERT INTO `employees` VALUES (37851,'Sayer','Matterson','Statistician III',98926,37270,1);
INSERT INTO `employees` VALUES (40448,'Mindy','Crissil','Staff Scientist',94860,37270,1);
INSERT INTO `employees` VALUES (56274,'Keriann','Alloisi','VP Marketing',110150,37270,1);
INSERT INTO `employees` VALUES (63196,'Alaster','Scutchin','Assistant Professor',32179,37270,2);
INSERT INTO `employees` VALUES (67009,'North','de Clerc','VP Product Management',114257,37270,2);
INSERT INTO `employees` VALUES (67370,'Elladine','Rising','Social Worker',96767,37270,2);
INSERT INTO `employees` VALUES (68249,'Nisse','Voysey','Financial Advisor',52832,37270,2);
INSERT INTO `employees` VALUES (72540,'Guthrey','Iacopetti','Office Assistant I',117690,37270,3);
INSERT INTO `employees` VALUES (72913,'Kass','Hefferan','Computer Systems Analyst IV',96401,37270,3);
INSERT INTO `employees` VALUES (75900,'Virge','Goodrum','Information Systems Manager',54578,37270,3);
INSERT INTO `employees` VALUES (76196,'Mirilla','Janowski','Cost Accountant',119241,37270,3);
INSERT INTO `employees` VALUES (80529,'Lynde','Aronson','Junior Executive',77182,37270,4);
INSERT INTO `employees` VALUES (80679,'Mildrid','Sokale','Geologist II',67987,37270,4);
INSERT INTO `employees` VALUES (84791,'Hazel','Tarbert','General Manager',93760,37270,4);
INSERT INTO `employees` VALUES (95213,'Cole','Kesterton','Pharmacist',86119,37270,4);
INSERT INTO `employees` VALUES (96513,'Theresa','Binney','Food Chemist',47354,37270,5);
INSERT INTO `employees` VALUES (98374,'Estrellita','Daleman','Staff Accountant IV',70187,37270,5);
INSERT INTO `employees` VALUES (115357,'Ivy','Fearey','Structural Engineer',92710,37270,5);


DROP DATABASE IF EXISTS `sql_inventory`;
CREATE DATABASE `sql_inventory`;
USE `sql_inventory`;


CREATE TABLE `products` (
  `product_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `quantity_in_stock` INT(11) NOT NULL,
  `unit_price` DECIMAL(4,2) NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=INNODB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `products` VALUES (1,'Foam Dinner Plate',70,1.21);
INSERT INTO `products` VALUES (2,'Pork - Bacon,back Peameal',49,4.65);
INSERT INTO `products` VALUES (3,'Lettuce - Romaine, Heart',38,3.35);
INSERT INTO `products` VALUES (4,'Brocolinni - Gaylan, Chinese',90,4.53);
INSERT INTO `products` VALUES (5,'Sauce - Ranch Dressing',94,1.63);
INSERT INTO `products` VALUES (6,'Petit Baguette',14,2.39);
INSERT INTO `products` VALUES (7,'Sweet Pea Sprouts',98,3.29);
INSERT INTO `products` VALUES (8,'Island Oasis - Raspberry',26,0.74);
INSERT INTO `products` VALUES (9,'Longan',67,2.26);
INSERT INTO `products` VALUES (10,'Broom - Push',6,1.09);
①.查询语句

SELECT 字段1,字段2,… FORM 表名 WHERE [条件];

例如：

#*代表查询表中所有字段，customers是表名
SELECE * FROM customers;


#代表查询customers表中的customer_id、phone 字段
SELECT customer_id,phone FORM customers;


#*代表查询表中所有字段
SELECE * 
FROM customers 
#WHERE customer_id = '1'
#查询结果根据first_name字段排序
ORDER BY first_name;

#查询 last_name，first_name，points，和将points+10一列
SELECT last_name,first_name,points,points+10 AS discount
FROM customers;

#结果：

last_namefirst_namepointsdiscount
MacCaffreyBabara22732283
BrushfieldInes947957
BoageyFreddi29672977
RoseburghAmbur457467
BetchleyClemmie36753685
TwiddellElka30733083
DowsonIlene16721682
NasebyThacher205215
RumgayRomola14861496
MynettLevy796806
②.条件语句WHERE

WHERE 后面直接加条件1 ADN 条件2 OR 条件3
ADN ：和
OR ：或者

AND优先级高于OR

NOT：非


SELECT *
FROM customers
WHERE NOT state = 'va';

SELECT *
FROM customers
WHERE state <> 'va';

比较运算符：
> : 大于
>= : 大于等于
< : 小于
< =: 小于等于
！= : 不等于
<> : 不等于

例子：

SELECT *
FROM customers
WHERE birth_date > '1991-09-04' OR POINTS>1000 AND state = 'va';

#AND优先级高于OR
SELECT *
FROM customers
WHERE birth_date > '1991-09-04' OR (POINTS>1000 AND state = 'va');
③.IN运算符
SELECT *
FROM customers
WHERE state = 'VA'OR state = 'GA'OR state = 'FL'

#用in代替
SELECT *
FROM customers
WHERE state IN ('VA','GA','FL')

#加NOT实现不在这个集合的情况
SELECT *
FROM customers
WHERE state NOT IN ('VA','GA','FL')
④.BETWEEN运算符

BETWEEN 会包含临界值，也就是会=

SELECT *
FROM customers
WHERE points>=1000 AND points<=3000

#用BETWEEN代替
SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000

#查询客人生日大于等于1990-1-1的并且小于2000-1-1
SELECT *
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'
⑤.LIKE运算符

%： 代表任意长度的任意字符
_ ：代表单个字符

#查询last_name以b开头的所有顾客，且是不区分b的大小写的。
SELECT *
FROM customers
WHERE last_name LIKE 'b%'

#查询last_name含有b的所有顾客，且是不区分b的大小写的。
SELECT *
FROM customers
WHERE last_name LIKE '%b%'

#查询last_name以y结尾的所有顾客
SELECT *
FROM customers
WHERE last_name LIKE '%y'

#'_'为占位符，在这里查询长度为8的以y结尾的客户。只能查询到Betchley
#'_'7个
SELECT *
FROM customers
WHERE last_name LIKE '_______y' 

#'_'为占位符，在这里查询长度为6的以b开头以y结尾的客户。只能查询到Boagey
#'_'4个
SELECT *
FROM customers
WHERE last_name LIKE 'b____y'
REGEXP正则表达式

REGEXP正则表达式

允许我们搜索更复杂的模式。
'^'表示字符串开头
'$'表示字符串末尾
'|'表示 逻辑or
[abcd] 表示匹配括号里任意举例的字符
[a-f] -表示一个范围

#与"%"对比如下
SELECT *
FROM customers
WHERE last_name LIKE '%field%'

#替换为正则表达式
SELECT *
FROM customers
WHERE last_name REGEXP 'field'

#'^'以开头,表示查询last_name以field开头的客户
SELECT *
FROM customers
WHERE last_name REGEXP '^field'

#'$'以结尾,表示查询last_name以field结尾的客户
SELECT *
FROM customers
WHERE last_name REGEXP 'field$'

#'|'查询last_name包含field或者max
SELECT *
FROM customers
WHERE last_name REGEXP 'field|mac'

#组合查询last_name包含filed 或 rose 或以mac开头的所有客户
#查询结果有MacCaffrey、Brushfield、Roseburgh
SELECT *
FROM customers
WHERE last_name REGEXP 'field|^mac|rose'

#查询lase_name包含ge\ie\re字符所有客户
#查询结果为：Boagey、Brushfield、MacCaffrey
SELECT *
FROM customers
WHERE last_name REGEXP '[gir]e'

#查询lase_name包含ae\be\ce~~ze字符所有客户
SELECT *
FROM customers
WHERE last_name REGEXP '[a-z]e'

#同样的查询包含ey\ed\eh字符
#查询结果为：Boagey、Betchley、MacCaffrey、Brushfield、Twiddell
SELECT *
FROM customers
WHERE last_name REGEXP 'e[ydh]'
⑥.IS NULL运算符

用来搜索缺失属性的数据


SELECT *
FROM customers
WHERE phone IS NULL
customer_idfirst_namelast_namebirth_datephoneaddresscitystatepoints
5ClemmieBetchley1973-11-07null5Spohn CircleArlingtonTX
#相反查询
SELECT *
FROM customers
WHERE phone IS  NOT NULL
⑦.ORDER BY运算符

可以用来排序
默认是升序
升序： ASC
降序： DESC
ORDER BY + 用来排序的字段

#用客户出生日期升序排序
SELECT *
FROM customers
ORDER BY birth_date

#用客户出生日期降序排序
SELECT *
FROM customers
ORDER BY birth_date DESC

#根据state降序排序，如果state一样则根据first_name升序排序
SELECT *
FROM customers
ORDER BY state DESC,first_name
⑧.LIMIT运算符

限定查询返回的记录
LIMIT永远放在最后

#跳过前面6条记录，查询后面三条记录
SELECT *
FROM customers
LIMIT 6,3

#查询积分最高的前三位顾客
SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3

2.内连接

从多个表查询
关键词:INNER JOIN
JOIN默认就是内连接，可以省略INNER关键词

#起别名AS可以省略，一般取表格的缩写
USE sql_inventory;
#如果要查询的表不在当前数据库，可以加前缀
SELECT p.product_id,p.name,oi.quantity,oi.unit_price
FROM sql_store.order_items oi
JOIN products p
ON p.product_id = oi.product_id
1.自连接

自己连接自己，具体看下面例子

USE sql_hr;

SELECT 
	e.employee_id,
	e.first_name,
	m.first_name AS manager
FROM employees e
JOIN employees m
ON e.reports_to = m.employee_id;
2.多表连接
USE sql_store;
SELECT
o.order_id,
o.order_date,
c.first_name,
c.last_name,
os.name AS STATUS
FROM orders o
JOIN customers c ON o.customer_id=c.customer_id
JOIN order_statuses os ON os.order_status_id =o.status

USE sql_invoicing;

SELECT 
	p.date,
	p.invoice_id,
	p.amount,
	c.name,
	pm.name	
FROM payments p
JOIN clients c ON c.client_id = p.client_id
JOIN payment_methods pm ON pm.payment_method_id = p.payment_method;
3.复合条件查询

使用多个条件连接两张表

SELECT * 
FROM order_items oi
JOIN order_item_notes oin
	ON oi.order_id = oin.order_Id 
	AND oi.product_id = oin.product_id;
4.隐式连接语法
#基础连接	
SELECT *
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id;

#隐式连接	
SELECT * 
FROM orders o , customers c
WHERE o.customer_id = c.customer_id;

3.外连接

LEFT JOIN 左连接
RIGHT JOIN 右连接
都是属于外连接

#左连接，不管条件成不成立，左边表的数据都会查出来
SELECT 
	c.customer_id,
	c.first_name,
	o.order_id
FROM customers c
LEFT JOIN orders o
	ON c.customer_id = o.customer_id
ORDER BY c.customer_id;
1.多表外连接
SELECT 
	c.customer_id,
	c.first_name,
	o.order_id,
	sh.name
FROM customers c
LEFT JOIN orders o
	ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
	ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id;

2.NoSQL

在非关系型数据库中，没有表或者关系，与关系型数据库有很大的区别。

视频课程推荐

链接: SQL进阶教程，非常推荐！！！.

xiaoyasister
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
wincc归档数据库数据查询
03-31
wincc归档数据库数据查询
MyBatis: databaseId的用法
最新发布
amadeus_liu2的博客
09-07 1981
这样，当MyBatis运行时，会根据当前的数据库是Oracle还是MySQL来匹配不同的SQL语句来执行。可以通过databaseId来区分执行的SQL是在MySQL上还是在Oracle上。二、根据不同的数据库语法写不同的SQL语句并配置相同的id。
SQL操作
u012512331的博客
08-23 1119
一、查询语句 1、基本查询 SELECT * FROM <表名> 查询表的所有行： SELECT * FROM students; 2、条件查询 SELECT * FROM <表名> WHERE <条件表达式> 查询分数在80分以上的学生记录： SELECT * FROM students WHERE score &g...
Oracle 企业管理器DataBase Control使用说明
程序员大阳
06-11 8985
一，简介Oracle企业管理器，英文名Oracle Enterprise Manager，是Oracle公司提供的以web界面的形式管理Oracle数据库的工具。Oracle 11g安装完毕后，在开始菜单的Oracle目录下，会有一个Database Control快捷方式，其实就是一个企业管理器的链接。二，企业管理器登录方式很简单，双击Database Control即可弹出企业管理器网页，使用s
MyBatis学习——databaseIdProvider和databaseId
bobozai86的博客
04-13 9735
有什么作用 databaseIdProvider和databaseId的作用简单来说就是让一个项目支持不同的数据库。 让一个项目支持不同的数据库在企业开发中是一个比较常见的需求。由于不同的数据库支持的sql语法稍有差别，所以某些功能需要根据数据库的不同书写不同的sql语句。对于这种需求，首先能够想到的解决方案就是针对不同的数据库维护不同的mapper.xml文件，但是这种方案会严重增加开发和维...
BSS段，代码段，堆（heap），栈(stack)
大学学习ing
04-19 965
BSS段：BSS段（bss segment）通常是指用来存放程序中未初始化的全局变量的一块内存区域。BSS是英文Block Started by Symbol的简称。BSS段属于静态内存分配。数据段：数据段（data segment）通常是指用来存放程序中已初始化的全局变量的一块内存区域。数据段属于静态内存分配。代码段：代码段（code segment/text segment）通常是指用
databasesearch数据库查询示例程序.rar_数据 查询_数据库查询
09-23
2. 数据查询技巧：有效的查询通常需要理解索引、JOIN和子查询。索引可以极大地提高查询速度；JOIN用于合并两个或多个表中的数据；子查询则是在一个查询内部嵌套另一个查询，以满足更复杂的查询需求。 3. 数据库查询...
Database：数据库查询
12-14
学习笔记：使用SQL语句进行数据查询 文章目录学习笔记：使用SQL语句进行数据查询@[toc]准备数据基本查询条件查询常用的条件表达式投影查询排序分页查询注意聚合查询分组多表查询连接查询如果你的MySQL不支持FULL ...
数据库数据查询.pdf
06-01
数据库数据查询.pdf
数据库的查询
Luna_ll的博客
08-20 1万+
数据库查询 语法 select [distinct]  * | 列名,列名  from 表  where 条件 简单查询（SELECT  select…from） 查询指定字段信息 select 字段1,字段2,...from 表名; 例如： select id,zname from zhangwu; 查询表中所有字段            select * from 表名...
数据库中select 查询详解
热门推荐
晓江的博客
09-25 5万+
数据查询操作介绍 在数据库操作中，使用频率最多的是查询操作。 查询数据时，根据不同的需求，条件对数据库中的数据进行查询 ，并返回结果 单表查询数据 查询数据库使用 select 命令。 这个命令相对比较复杂。可变化样式较多，这里分功能依次讲解。 查询数据表中所有数据 语法：select * from 表名 select * from t_student; ...
数据库——查询操作
shang_0122的博客
08-21 1万+
数据库查询操作
数据库的查询总结-----按条件查询
wpb74521wrf的博客
05-27 2万+
简单查询查询所有字段select * from 表名; 例： select * from students; 查询指定字段select 列1,列2,... from 表名; 例: select name from students; 使用 as 给字段起别名select id as 序号, name as 名字, gender as 性别 from students; 可以通过 as 给表起别名 ...
python django 数据库查询方法总结
weixin_33911824的博客
02-17 821
 __exact 精确等于 like ‘aaa’__iexact 精确等于 忽略大小写 ilike ‘aaa’__contains 包含 like ‘%aaa%’__icontains 包含 忽略大小写 ilike ‘%aaa%’，但是对于sqlite来说，contains的作用效果等同于icontains。__gt 大于__gte 大于等于__lt 小于__lte 小于等于__in 存在于一个l...
数据库的所有查询功能
yuanlingGeGe的博客
04-24 8086
1 数据的准备 1.1 创建一个数据库 create database python_test charset=utf8; -- 使用一个数据库 use python_test; --显示使用的当前数据库是哪个? select databases(); --创建一个数据表 -- students表 create table students( id int unsign...
查询数据库的数据库名和表名（MySQL、MSSQL、ORACLE）
dqcoffee的博客
03-25 1万+
MySQL中查询所有数据库名和表名 1.查询所有数据库 show databases; 2.查询指定数据库中所有表名 select table_name from information_schema.tables where table_schema='database_name' and table_type='base table'; 3.查询指定表中的所有字段名 select col...
MySQL获取所有表及表结构
mosaic&Bernie
02-20 4562
1、获取所有表属性，语句如下： select table_name tableName, engine, table_comment tableComment, table_collation tableCollation, create_time createTime from information_schema.tables where table_schema = (select database()) order by create_time desc 2、
学习数据库：已知一个guid就可以查询出这个guid多对应的表
Grace_1203的博客
07-27 1544
declare l_guid varchar2(64); cursor cu is select distinct c.TABLE_NAME from user_tab_columns c where c.COLUMN_NAME = 'GUID'; begin for cu_rec in cu loop begin execute immediate 'se...
数据库Database
06-10
数据库（Database）是指按照数据结构来组织、存储和管理数据的集合。常见的数据库管理系统（DBMS）包括 MySQL、Oracle、SQL Server、PostgreSQL 等。数据库可以用于存储和管理各种类型的数据，例如文本、图像、音频...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值