人工智能（mysql）—— mysql完整的sql查詢

老張的菜地 2019-08-16

展開全文

一、SQL查詢

        SQL查詢的執行順序，先對表中現有的字段進行篩選（1、where），再進行聚合分組（2、group），然后根據查詢條件進行查找（3、select），緊接著對篩選的結果在再篩選（4、having），接下來根據條件進行排序（5、order），最后對記錄的顯示進行限制（6、limit）。
            3、select ...聚合函數 from 表名
            1、where ...
            2、group by ...
            4、having ...
            5、order by ...
            6、limit ...;

1、order by

            給查詢的結果進行排序
            語法：order by 字段名排序方式
            排序方式
                    a、升序：ASC(默認)
                    b、降序：DESC


1、將蜀國的英雄按照攻擊值從高到低排序
select * from sanguo
where country='蜀國'
order by gongji DESC;
2、將魏蜀兩國的男英雄中名字為三個字符的英雄按防御值升序排列
select * from sanguo
where country in('蜀國','魏國') and sex='男' and name like '___'
order by fangyu ASC;

2、limit(永遠放在SQL語句的最后)

限制顯示查詢記錄的個數

a、用法

                1）limit n -->顯示n條記錄
                2）limit m,n
                        m ：表示從 m+1 條記錄開始顯示
                        n ：表示顯示 n 條
                        limit 2,4 ：顯示第3、4、5、6四條記錄
                        limit 0,2 ：顯示第1、2兩條記錄

b、示例


1、在蜀國英雄中查找攻擊值前三名且名字不為NULL的英雄姓名、攻擊值和國家
select name,gongji,country from sanguo
where country='蜀國' and name is not NULL
order by gongji DESC
limit 3;
2、在蜀國英雄中,查找防御值倒數第2名至倒數第4名的英雄信息
select * from sanguo
where country='蜀國' order by fangyu limit 1,3;

c、分頁查詢

                每頁顯示5(n)條記錄，顯示第4(m)頁
                        第1頁：limit 0,5   ## 1 2 3 4 5
                        第2頁：limit 5,5   ## 6 7 8 9 10          ## (2-1)*5
                        第3頁：limit 10,5 ## 11 12 13 14 15 ##(3-1)*5
                        第4頁：limit 15,5 ## 16 17 18 19 20 ##(4-1)*5

                分頁公式：limit (m-1)*n,n m:第幾頁 n:每頁顯示記錄條數

3、聚合函數

                avg(字段名) : 平均值
                max(字段名) : 最大值
                min(字段名) : 最小值
                sum(字段名) : 求和
                count(字段名) : 統計該字段記錄的個數


1、攻擊力最強值
select max(gongji) as best from sanguo;
2、統計一下表中id、name字段分別有多少條記錄
select count(id),count(name) from sanguo;
## 空值NULL不會被統計
select count(*) from sanguo;
3、統計蜀國英雄中攻擊值大于200的英雄的數量
select count(*) from sanguo where country='蜀國' and gongji > 200;

4、group by(先分組-再聚合-去重)

給查詢的結果進行分組

a、示例

計算所有國家的平均攻擊力，顯示國家名和平均攻擊力

                select country,avg(gongji) from sanguo group by country;

先分組 - 再聚合 - 去重

                        蜀國
                        蜀國         400       蜀國
                        蜀國
                        魏國         300    魏國
                        魏國
                        吳國         200       吳國


查找所有國家中,英雄數量最多的前2名,顯示國家名稱和英雄數量
select country,count(*) as number from sanguo
group by country
order by number DESC limit 2;

b、注意

1）group by之后的字段名必須要為select之后的字段名
2）如果select后的字段名和group by之后的字段不一致，則必須對該字段進行聚合處理(聚合函數)

5、having

對查詢結果進一步篩選


找出平均攻擊力大于105的國家的前2名,顯示國家名稱和平均攻擊力
select country,avg(gongji) from sanguo
group by country
having avg(gongji)>105
order by avg(gongji) DESC limit 2;

                注意
                       1）having語句通常與group by語句聯合使用，用來過濾由group by語句返回的記錄集
                       2）having語句的存在彌補了where條件子句不能與聚合函數聯合使用的不足，where操作的是表中實際存在的字段，having操作的是聚合函數生成的顯示列

6、distinct

不顯示字段的重復值


1、sanguo表中有哪些國家
select distinct country from sanguo;
2、計算魏國一共有多少個英雄
select count(distinct name) from sanguo
where country='魏國';

                注意
                        1）distinct處理的是distinct和from之間的所有字段，所有字段的值必須完全相同才可以去重
                        2）distinct不能對任何字段做聚合處理

7、查詢表記錄時的數學運算

a、運算符

+ - * / %

b、示例


查詢時顯示所有英雄的攻擊力 * 10
select name,gongji*10,country from sanguo;

二、約束

1、作用

為了保證數據的完整性、一致性、有效性，可以限制無效的數據插入到數據表中

2、約束分類

a、默認約束(default)

在插入記錄時，如果不給該字段賦值，則使用默認值
格式：字段名數據類型 default 默認值，


create  table  tt(sex  enum('男','女','保密') default  '保密');
desc   tt ;

b、非空約束

不允許該字段的值有空值NULL記錄
格式：字段名數據類型 not null,


create  table xx(id  int  not  null,name  varchar(20)  not  null);
desc  xx;

三、嵌套查詢(子查詢)

把內層的查詢結果作為外層的查詢條件

格式：select ... from 表名 where 字段名運算符(select ....);


1、把攻擊值小于 平均攻擊值 的名字和攻擊值顯示出來（分兩步）
select name,gongji from MOSHOU.sanguo
where gongji < (select avg(gongji) from MOSHOU.sanguo);
2、找出每個國家攻擊力最高的英雄名字和攻擊值
select name,gongji from sanguo
where (country,gongji) in (select country,max(gongji) from sanguo group by country);

四、附錄1：數據庫數據創建

為了方便，建立直接復制、黏貼以下代碼，快速創建數據庫和表。


create database MOSHOU;
use MOSHOU;
create table hero(
id int,
name char(15),
sex enum('男','女'),
country char(10)
)default charset=utf8;
insert into hero values
(1,'曹操','男','魏國'),
(2,'小喬','女','吳國'),
(3,'諸葛亮','男','蜀國'),
(4,'貂蟬','女','東漢'),
(5,'趙子龍','男','蜀國'),
(6,'魏延','男','蜀國');
use MOSHOU;
create table sanguo(
id int,
name char(20),
gongji int,
fangyu tinyint unsigned,
sex enum('男','女'),
country varchar(20)
)default charset=utf8;
insert into sanguo values
(1,'諸葛亮',120,20,'男','蜀國'),
(2,'司馬懿',119,25,'男','魏國'),
(3,'關6羽',188,60,'男','蜀國'),
(4,'趙云666',200,66,'男','魏國'),
(5,'8孫權',110,20,'男','吳國'),
(6,'貂蟬',666,10,'女','魏國'),
(7,null,1000,99,'男','蜀國'),
(8,'',1005,88,'女','蜀國');