<導圖>Mysql常用查詢文法

普通查詢

檢視整個表

• 格式:
  • select * from 表名;
• 示例:

  • select * from students;

查詢指定字段

• 格式
  • select 字段名1,字段名2 from 表名;
• 示例

  • select id, name from students;

給字段起别名

• • select 字段名1 as 新名字,字段名2 as 新名字 from 表名;

• • select name as 姓名, age as 年齡 from students;

消除重複行

• • select distinct 字段名 from 表名;

• • select distinct gender from students;

條件查詢

比較運算

• • select 字段集合 from 表名 where 所需資料的範圍;

• • select * from students where id > 5;

正則

• • select 字段集合 from 表名 where name rlike "正規表達式";

• • select * from students where name rlike "^張";

範圍

• 連續
  • • select 字段集合 from 表名 where 字段名 between 點值 and 點值;

  • • select * from students where id between 3 and 6;

• 非連續
  • • select 字段集合 from 表名 where 字段名 in 不連續"值";

  • • select * from students where id in (5, 8 , 9);

資料排序

正序 asc

• • select 字段集合 from 表名 where 所需資料的範圍 order by "作為排序标準"的字段名 asc;

• • select * from students where id >5 order by age asc;

反序 desc

• • select 字段集合 from 表名 where 所需資料的範圍 order by "作為排序标準"的字段名 desc;

• • select * from students where id >5 order by age desc;

聚合函數

總數 count

• • select count(字段集合) from 表名;

• • select count(*) from students;

最大值 max

• • select max(字段名) from 表名;

• • select max(age) from students;

最小值 min

• • select min(字段名) from 表名;

• • select min(age) from students;

求和 sum

• • select sum(字段名) from 表名;

• • select sum(age) from students;

平均值 avg

• • select avg(字段名) from 表名;

• • select avg(height) from students;

分組 group by

group by + group concat()

• • select 字段名1, group_concat(字段名2 ,字段名3) from 表名 group by 字段名1;

• • select id, group_concat(name, age) from students group by id;

group by + 聚合函數

• • select 字段名1, 函數名(字段名2) from 表名 group by 字段名1;

• • select id, avg(height) from students group by id;

分頁 limit

• select 字段集合 from 表名 limit 起始索引号, 每次顯示數量;

• select * from students limit 3, 5;

連接配接查詢

内連接配接查詢(結果為,兩個表共有的資料)

• • select 字段集合 表名1 inner join 表名2 on 表名1.表1字段 = 表名2.表2字段;

• • select * from students inner join classes on students.classes_id = classes.id;

左連接配接查詢(對右表不存在的資料用null填充)

• • select 字段集合 表名1 left join 表名2 on 表名1.表1字段 = 表名2.表2字段;

• • select * from students left join classes on students.classes_id = classes.id;

子查詢(一條查詢語句中嵌入了另一條查詢語句)

• • select * from students where age > (select avg(age) from students);;

小結

查詢語句文法順序

• select distinct 字段集合

  from 表名

  where 取值範圍

  group by 字段名

  order by 字段名

  limit 起始索引号, 每次顯示數量

附 建立表sql

create database school_of_three_kindoms charset=utf8;

use school_of_three_kindoms;


-- 建立學生基本資訊表
create table students(
    -- 學籍号:int unsigned無符号整型, auto_increment自增,primary key設定為主鍵,not null非空 
    id int unsigned auto_increment primary key not null,
    -- 姓名: varchar(30)可變字元類型, default ""預設為空字元
    name varchar(30) default "",
    -- 年齡: tinyint unsigned無符号整型, default 0 預設為 0
    age tinyint unsigned default 0,
    -- 身高: 浮點型(5個數字,包含2個小數,如 180.05)
    height decimal(5, 2),
    -- 性别: enum枚舉類型("1"對應"男","2"對應"女","3"對應"保密"")
    gender enum("男","女","保密"),
    -- 所屬班級: int unsigned 無符号整型,預設值為0
    classes_id int unsigned default 0

);

-- 建立班級
create table classes(

    id int unsigned auto_increment primary key not null,
    name varchar(20)



);






insert into students values
    (null, "曹操", 50, 183.05, 1, 1),
    (null, "夏侯惇", 40, 193.05, 1, 1),
    (null, "許褚", 42, 186.05, 1, 1),
    (null, "司馬懿", 48, 188.05, 1, 1),
    (null, "劉備", 48, 179.01, 1, 2),
    (null, "張飛", 46, 179.60, 1, 2),
    (null, "關羽", 47, 188.01, 1, 2),
    (null, "孫權", 39, 185.09, 1, 3),
    (null, "周瑜", 30, 190.09, 1, 3),
    (null, "大喬", 28, 162.32, 2, 3),
    (null, "小喬", 26, 160.19, 2, 3),
    (null, "刑天", 100, 900.15, 1, 4),
    (null, "鬼符三通", 59, 179.68, 1, 5),
    (null, "曹焱兵", 20, 186.34, 1, 5),
    (null, "曹玄亮", 13, 160.21, 1, 5),
    (null, "夏玲", 21, 176.02, 2, 5);



insert into classes values
    (0, "班級1_魏"),
    (0, "班級2_蜀"),
    (0, "班級3_吳");
  
           

Mysql常用查詢文法