SQL簡單查詢後續記錄

--首先創建資料庫TEST
CREATE DATABASE TEST
--創建表tb_user
USE TEST
CREATE TABLE [tb_user](
[name] [nvarchar] (50) NOT NULL,
[age] [int] NOT NULL,
[sex] [nvarchar] (4)
)
--表中插入測試數據
USE TEST
INSERT INTO [dbo].[tb_user] (name,age,sex) values('張三','18','男')
INSERT INTO [dbo].[tb_user] (name,age,sex) values('李斯','19','男')
INSERT INTO [dbo].[tb_user] (name,age,sex) values('校花','18','女')
INSERT INTO [dbo].[tb_user] (name,age,sex) values('校草','20','男')
INSERT INTO [dbo].[tb_user] (name,age,sex) values('張三2','19','男')
INSERT INTO [dbo].[tb_user] (name,age,sex) values('張小花','19','女')

--查詢
SELECT * FROM TB_USER
--根據年齡排序(升序 ASC 降序DESC)升序中ASC可以不寫
SELECT * FROM TB_USER ORDER BY AGE DESC

--根據性別統計人數
SELECT SEX,COUNT(*) AS P_NUM FROM TB_USER GROUP BY SEX

--查詢性別為男的用戶詳細
SELECT NAME,AGE,SEX FROM TB_USER WHERE SEX='男'

--查詢用戶中最大年齡
SELECT MAX(AGE) FROM TB_USER

--查詢用戶中最小年齡
SELECT MIN(AGE) FROM TB_USER

--查詢最大年齡的用戶(最小同理將MAX函數換為MIN)
SELECT * FROM TB_USER WHERE AGE=(SELECT MAX(AGE) FROM TB_USER)

--求男女平均年齡
SELECT SEX, AVG(AGE) AS AVG_AGE FROM TB_USER GROUP BY SEX

--根據年齡統計人數
SELECT AGE,COUNT(*) AS NUM FROM TB_USER GROUP BY AGE

--查詢姓張的男女人數
SELECT SEX,COUNT(*) AS NUM FROM TB_USER WHERE NAME LIKE'張%' GROUP BY SEX
--查詢姓張的男女人數行列轉換
SELECT
CASE SEX WHEN '男' THEN COUNT(*) ELSE 0 END AS 男,
CASE SEX WHEN '女' THEN COUNT(*) ELSE 0 END AS 女
FROM TB_USER WHERE NAME LIKE '張%' GROUP BY SEX