激情久久久_欧美视频区_成人av免费_不卡视频一二三区_欧美精品在欧美一区二区少妇_欧美一区二区三区的

服務(wù)器之家：專注于服務(wù)器技術(shù)及軟件下載分享
分類導航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|數(shù)據(jù)庫技術(shù)|

服務(wù)器之家 - 數(shù)據(jù)庫 - Mysql - MySQL單表查詢常見操作實例總結(jié)

MySQL單表查詢常見操作實例總結(jié)

2019-07-07 15:37程志偉 Mysql

這篇文章主要介紹了MySQL單表查詢常見操作,結(jié)合實例形式總結(jié)分析了MySQL創(chuàng)建表以及條件查詢常見操作技巧,需要的朋友可以參考下

本文實例總結(jié)了MySQL單表查詢常見操作。分享給大家供大家參考，具體如下：

創(chuàng)建fruits表：

CREATE TABLE fruits
(
 f_id  char(10)   NOT NULL,
 s_id  INT NOT NULL,
 f_name char(255)   NOT NULL,
 f_price decimal(8,2) NOT NULL,
 PRIMARY KEY(f_id)
) ;
INSERT INTO fruits (f_id, s_id, f_name, f_price)
VALUES('a1', 101,'apple',5.2),
('b1',101,'blackberry', 10.2),
('bs1',102,'orange', 11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana', 10.3),
('t2',102,'grape', 5.3),
('o2',103,'coconut', 9.2),
('c0',101,'cherry', 3.2),
('a2',103, 'apricot',2.2),
('l2',104,'lemon', 6.4),
('b2',104,'berry', 7.6),
('m1',106,'mango', 15.6),
('m2',105,'xbabay', 2.6),
('t4',107,'xbababa', 3.6),
('m3',105,'xxtt', 11.6),
('b5',107,'xxxx', 3.6);

常用查詢：

SELECT * FROM fruits;
select f_name,f_price from fruits;
select f_name,f_price from fruits where f_price=10.2;
select f_name,f_price from fruits where f_price<10;
select * from fruits where s_id in (101,102) order by f_name;
select * from fruits where s_id not in (101,102) order by f_name;
select f_name,f_price from fruits where f_price between 2.00 and 10.20;
select f_name,f_price from fruits where f_price not between 2.00 and 10.20;
select f_name,f_price from fruits where f_name like "b%";
select f_name,f_price from fruits where f_name like "%g%";
select f_name,f_price from fruits where f_name like "b%y";
select f_name,f_price from fruits where f_name like "____y";

創(chuàng)建customers表：

CREATE TABLE customers
(
 c_id   int    NOT NULL AUTO_INCREMENT,
 c_name  char(50) NOT NULL,
 c_address char(50) NULL,
 c_city  char(50) NULL,
 c_zip   char(10) NULL,
 c_contact char(50) NULL,
 c_email  char(255) NULL,
 PRIMARY KEY (c_id)
);
INSERT INTO customers(c_id, c_name, c_address, c_city, c_zip, c_contact, c_email)
VALUES(10001, 'RedHook', '200 Street ', 'Tianjin', '300000', 'LiMing', 'LMing@163.com'),
(10002, 'Stars', '333 Fromage Lane', 'Dalian', '116000', 'Zhangbo','Jerry@hotmail.com'),
(10003, 'Netbhood', '1 Sunny Place', 'Qingdao', '266000', 'LuoCong', NULL),
(10004, 'JOTO', '829 Riverside Drive', 'Haikou', '570000', 'YangShan', 'sam@hotmail.com');

常用查詢語句：

select c_id,c_name,c_email from customers where c_email is null;
select c_id,c_name,c_email from customers where c_email is not null;
select f_name,f_price from fruits where s_id=101 and f_price>=5;
select f_name,f_price from fruits where s_id in (101,102) and f_price >=5 and f_name="apple";
select f_name,f_price from fruits where s_id=101 or s_id=102;

使用in操作更加簡潔明了

select f_name,f_price
from fruits
where s_id in (101 ,102);

字段不重復

SELECT DISTINCT s_id FROM fruits;
select f_name from fruits ORDER BY f_name;

如果第一列數(shù)據(jù)中沒有相同值，將不再對第二列進行排序。

SELECT f_name, f_price
FROM fruits
ORDER BY f_name, f_price;

按價格降序排列,desc為降序，默認為升序。

SELECT f_name, f_price FROM fruits ORDER BY f_price desc;
SELECT f_name, f_price FROM fruits ORDER BY f_price desc,f_name;
SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id;
SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id;

使用having過濾分組

SELECT s_id, GROUP_CONCAT(f_name) AS Names
FROM fruits
GROUP BY s_id having count(f_name)>1;

在group by 子句中使用with rollup

SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id WITH ROLLUP;
SELECT * from fruits group by s_id,f_name;

創(chuàng)建orderitems表：

CREATE TABLE orderitems
(
 o_num   int     NOT NULL,
 o_item   int     NOT NULL,
 f_id    char(10)   NOT NULL,
 quantity  int     NOT NULL,
 item_price decimal(8,2) NOT NULL,
 PRIMARY KEY (o_num,o_item)
) ;
INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price)
VALUES(30001, 1, 'a1', 10, 5.2),
(30001, 2, 'b2', 3, 7.6),
(30001, 3, 'bs1', 5, 11.2),
(30001, 4, 'bs2', 15, 9.2),
(30002, 1, 'b3', 2, 20.0),
(30003, 1, 'c0', 100, 10),
(30004, 1, 'o2', 50, 2.50),
(30005, 1, 'c0', 5, 10),
(30005, 2, 'b1', 10, 8.99),
(30005, 3, 'a2', 10, 2.2),
(30005, 4, 'm1', 5, 14.99);

常用查詢語句：

SELECT o_num, SUM(quantity * item_price) AS orderTotal
FROM orderitems
GROUP BY o_num
HAVING SUM(quantity*item_price) >= 100;
SELECT o_num, SUM(quantity * item_price) AS orderTotal
FROM orderitems
GROUP BY o_num
HAVING SUM(quantity*item_price) >= 100
order by ordertotal;
SELECT * From fruits LIMIT 8;

從第五行開始，讀取3行

SELECT * From fruits LIMIT 4,3;

【例.1】從fruits表中檢索所有字段的數(shù)據(jù)

SELECT * FROM fruits;
SELECT f_id, s_id ,f_name, f_price FROM fruits;

【例.2】查詢當前表中f_name列所有水果名稱，輸入如下語句：

SELECT f_name FROM fruits;

【例.3】例如，從fruits表中獲取f_name和f_price兩列，輸入如下語句：

SELECT f_name, f_price FROM fruits;

【例.4】查詢價格為10.2元的水果的名稱，輸入如下語句：

SELECT f_name, f_price
FROM fruits
WHERE f_price = 10.2;

【例.5】查找名稱為“apple”的水果的價格,輸入如下語句：

SELECT f_name, f_price
FROM fruits
WHERE f_name = 'apple';

【例.6】查詢價格小于10的水果的名稱，輸入如下語句：

SELECT f_name, f_price
FROM fruits
WHERE f_price < 10;

【例.7】s_id為101和102的記錄，輸入如下語句：

SELECT s_id,f_name, f_price
FROM fruits
WHERE s_id IN (101,102)
ORDER BY f_name;

【例.8】查詢所有s_id不等于101也不等于102的記錄，輸入如下語句：

SELECT s_id,f_name, f_price
FROM fruits
WHERE s_id NOT IN (101,102)
ORDER BY f_name;

【例.9】查詢價格在2.00元到10.5元之間水果名稱和價格

SELECT f_name, f_price
FROM fruits
WHERE f_price BETWEEN 2.00 AND 10.20;

【例.10】查詢價格在2.00元到10.5元之外的水果名稱和價格

SELECT f_name, f_price
FROM fruits
WHERE f_price NOT BETWEEN 2.00 AND 10.20;

【例.11】查找所有以‘b'字母開頭的水果，輸入如下語句：

SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE 'b%';

【例.12】在fruits表中，查詢f_name中包含字母‘g'的記錄

SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE '%g%';

【例.13】查詢以‘b'開頭，并以‘y'結(jié)尾的水果的名稱

SELECT f_name
FROM fruits
WHERE f_name LIKE 'b%y';

【例7.14】在fruits表中，查詢以字母‘y'結(jié)尾，且‘y'前面只有4個字母的記錄

SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE '____y';

【例.15】查詢customers表中c_email為空的記錄的c_id、c_name和c_email字段值：

SELECT c_id, c_name,c_email
FROM customers
WHERE c_email IS NULL;

【例.16】查詢customers表中c_email不為空的記錄的c_id、c_name和c_email字段值

SELECT c_id, c_name,c_email
FROM customers
WHERE c_email IS NOT NULL;

【例.17】在fruits表中查詢s_id = ‘101'，并且f_price大于5的記錄價格和名稱

SELECT f_id, f_price, f_name
FROM fruits
WHERE s_id = '101' AND f_price >=5;

【例.18】在fruits表中查詢s_id = ‘101'或者'102'，并且f_price大于5，并且f_name='apple'的記錄價格和名稱

SELECT f_id, f_price, f_name
FROM fruits
WHERE s_id IN('101', '102') AND f_price >= 5 AND f_name = 'apple';

【例.19】查詢s_id=101或者s_id=102的水果供應(yīng)商的f_price和f_name，SQL語句如下：

SELECT s_id,f_name, f_price
FROM fruits
WHERE s_id = 101 OR s_id = 102;

【例.20】查詢s_id=101或者s_id=102的水果供應(yīng)商的f_price和f_name

SELECT s_id,f_name, f_price
FROM fruits
WHERE s_id IN(101,102);

【例.21】查詢fruits表中s_id字段的值，并返回s_id字段值不得重復

SELECT DISTINCT s_id FROM fruits;

【例.22】查詢fruits表的f_name字段值，并對其進行排序

select f_name from fruits ORDER BY f_name;

【例.23】查詢fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序

SELECT f_name, f_price
FROM fruits
ORDER BY f_name, f_price;

【例.24】查詢fruits表中的f_name和f_price字段，對結(jié)果按f_price降序方式排序

SELECT f_name, f_price
FROM fruits
ORDER BY f_price DESC;

【例.25】查詢fruits表，先按f_price降序排序，再按f_name字段升序排序，SQL語句如下：

SELECT f_price, f_name
FROM fruits
ORDER BY f_price DESC, f_name;

【例.26】根據(jù)s_id對fruits表中的數(shù)據(jù)進行分組

SELECT s_id, COUNT(*) AS Total
FROM fruits
GROUP BY s_id;

【例.27】根據(jù)s_id對fruits表中的數(shù)據(jù)進行分組，將每個供應(yīng)商的水果名稱顯示出來

SELECT s_id, GROUP_CONCAT(f_name) AS Names
FROM fruits
GROUP BY s_id;

【例.28】根據(jù)s_id對fruits表中的數(shù)據(jù)進行分組，并顯示水果種類大于1的分組信息

SELECT s_id, GROUP_CONCAT(f_name) AS Names
FROM fruits
GROUP BY s_id HAVING COUNT(f_name) > 1;

【例.29】根據(jù)s_id對fruits表中的數(shù)據(jù)進行分組，并顯示記錄數(shù)量

SELECT s_id, COUNT(*) AS Total
FROM fruits
GROUP BY s_id WITH ROLLUP;

【例.30】根據(jù)s_id和f_name字段對fruits表中的數(shù)據(jù)進行分組， SQL語句如下，

SELECT * from fruits group by s_id,f_name;

【例.31】查詢訂單價格大于100的訂單號和總訂單價格

SELECT o_num, SUM(quantity * item_price) AS orderTotal
FROM orderitems
GROUP BY o_num
HAVING SUM(quantity*item_price) >= 100;

【例.32】顯示fruits表查詢結(jié)果的前4行，輸入如下語句：

SELECT * From fruits LIMIT 4;

【例.33】在fruits 表中，使用LIMIT子句，返回從第5個記錄開始的，行數(shù)長度為3的記錄

SELECT * From fruits LIMIT 4, 3;

希望本文所述對大家MySQL數(shù)據(jù)庫計有所幫助。

延伸 · 閱讀

精彩推薦
Weibo Article 1 Weibo Article 2 Weibo Article 3 Weibo Article 4 Weibo Article 5 Weibo Article 6 Weibo Article 7 Weibo Article 8 Weibo Article 9 Weibo Article 10 Weibo Article 11 Weibo Article 12 Weibo Article 13 Weibo Article 14 Weibo Article 15 Weibo Article 16 Weibo Article 17 Weibo Article 18 Weibo Article 19 Weibo Article 20 Weibo Article 21 Weibo Article 22 Weibo Article 23 Weibo Article 24 Weibo Article 25
主站蜘蛛池模板： 国产成人免费高清激情视频 | 欧美中文字幕一区二区三区亚洲 | 国产精品视频中文字幕 | 国产va在线观看 | 97人操| 国产91丝袜在线播放0 | 欧美a级理论片 | 性毛片视频| 国产在线一级片 | 欧美a∨一区二区三区久久黄 | 91精品动漫在线观看 | 成年人黄色片视频 | 成人免费一区二区三区在线观看 | 欧美成人高清视频 | 毛片大全免费看 | 小视频免费在线观看 | 日本一区二区视频在线 | 1级黄色毛片 | 护士xxxx | 日本一区二区在线 | 久久出精品 | 久久精品综合视频 | 免费黄色大片在线观看 | 黄色片网站在线看 | 在火车上摸两乳爽的大叫 | 免费国产在线视频 | 欧美激情图区 | 亚洲国产精品一区二区久久 | 欧美黄 片免费观看 | www.99tv| 成人毛片在线免费观看 | www.精品视频| 免费在线观看国产 | 欧美成人理论片乱 | 欧美特黄一级高清免费的香蕉 | 欧美精品久久久久久久久久 | 91久久线看在观草草青青 | 毛片视频网址 | 国产一区二区精品在线观看 | 毛片大全| 国产影院在线观看 |