Mysql查詢-表資料的查詢

文章目錄

           0.資料的準備

           1.MySQL查詢

           2.條件查詢

               2.1比較運算符

               2.2邏輯運算符

               2.3模糊查詢

               2.4 範圍查詢

           3 排序

           4 聚合函數

           5 分組

           6 分頁

           7 連接配接查詢

           8 自關聯

           9 子查詢

0.資料的準備

   建立一個資料庫

   create database python_test charset=utf8;

   使用一個資料庫

   use python_test;

   顯示使用的目前資料是那個？

   select database();

   建立一個資料表

   create table students(

   id int unsigned not null auto_increment primary key,

   name varchar(30),

   age tinyint unsigned default 0,

   high decimal(5,2),

   gender enum(“男”,“女”,“中性”,“保密”) default “保密”,

   cls_id int unsigned default 0,

   is_delete bit default 0

   );

   students表

   id int unsigned primary key auto_increment not null,

   name varchar(20) default ‘’,

   height decimal(5,2),

   gender enum(‘男’,‘女’,‘中性’,‘保密’) default ‘保密’,

   classes表

   create table classes (

   id int unsigned auto_increment primary key not null,

   name varchar(30) not null

準備資料

– 向students表中插入資料

insert into students values

(0,‘小明’,18,180.00,2,1,0),

(0,‘小月月’,18,180.00,2,2,1),

(0,‘彭于晏’,29,185.00,1,1,0),

(0,‘劉德華’,59,175.00,1,2,1),

(0,‘黃蓉’,38,160.00,2,1,0),

(0,‘鳳姐’,28,150.00,4,2,1),

(0,‘王祖賢’,18,172.00,2,1,1),

(0,‘周傑倫’,36,NULL,1,1,0),

(0,‘程坤’,27,181.00,1,2,0),

(0,‘劉亦菲’,25,166.00,2,2,0),

(0,‘金星’,33,162.00,3,3,1),

(0,‘靜香’,12,180.00,2,4,0),

(0,‘郭靖’,12,170.00,1,4,0),

(0,‘周傑’,34,176.00,2,5,0);

– 向classes表中插入資料

insert into classes values (0, “python_01期”),

(0, “python_02期”),

(0, “python_04期”);

1.MySQL查詢

   查詢所有字段

   select * from 表名;

   select * from students;

   select id,name from classes;

   查詢指定字段

   select 列1,列2,… from 表名;

   select age,name from students;

   使用as給字段起别名

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

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

   select 表名.字段 … from 表名;

   select students.name, students.age from students;

   select s.name,s.age from students as s;

   select students.name, students.age from students as s;(失敗)

   消除重複行

   distinct 字段

   select distinct gender from students;

2.條件查詢

2.1比較運算符

   select … from 表名 where …

   查詢大于18歲的資訊

   select * from students where age>18;

   select id,name,gender from students where age>18;

   <

   查詢小于18歲的資訊

   select * from stundents where age<18;

       =

   <=

   查詢小于或者等于18歲的資訊

   select * from stundents where age=18;

   =

   查詢年齡為18歲的所有學生的名字

   != 或者 <>

2.2邏輯運算符

   and

   – 18到28之間的是以學生資訊

   select * from students where age>18 and age<28;

   失敗select * from students where age>18 and <28;

   – 18歲以上的女性

   select * from students where age>18 and gender=“女”;

   select * from students where age>18 and gender=2;

   or

   – 18以上或者身高查過180(包含)以上

   select * from students where age>18 or height>=180;

   not

   – 不在 18歲以上的女性 這個範圍内的資訊

   select * from students where not age>18 and gender=2;

   select * from students where not (age>18 and gender=2);

   – 年齡不是小于或者等于18 并且是女性

   select * from students where (not age<=18) and gender=2;

2.3模糊查詢

   like

   – % 替換1個或者多個

   – _ 替換1個

   – 查詢姓名中 以 “小” 開始的名字

   select name from students where name=“小”;

   select name from students where name like “小%”;

– 查詢姓名中 有 “小” 所有的名字

select name from students where name like “%小%”;

– 查詢有2個字的名字

select name from students where name like “__”;

– 查詢有3個字的名字

– 查詢至少有2個字的名字

select name from students where name like “__%”;

   rlike 正則

   – 查詢以 周開始的姓名

   select name from students where name rlike “^周.*”;

– 查詢以 周開始、倫結尾的姓名

select name from students where name rlike “^周.*倫$”;

2.4 範圍查詢

   in (1, 3, 8)表示在一個非連續的範圍内

   – 查詢 年齡為18、34的姓名

   select name,age from students where age=18 or age=34;

   select name,age from students where age=18 or age=34 or age=12;

   select name,age from students where age in (12, 18, 34);

   not in 不非連續的範圍之内

   – 年齡不是 18、34歲之間的資訊

   select name,age from students where age not in (12, 18, 34);

   between … and …表示在一個連續的範圍内

   – 查詢 年齡在18到34之間的的資訊

   select name, age from students where age between 18 and 34;

   not between … and …表示不在一個連續的範圍内

   – 查詢 年齡不在在18到34之間的的資訊

   select * from students where age not between 18 and 34;

   select * from students where not age between 18 and 34;

   – 失敗的select * from students where age not (between 18 and 34);

   空判斷

   – 判空is null

   – 查詢身高為空的資訊

   select * from students where height is null;

   select * from students where height is NULL;

   select * from students where height is Null;

   判非空is not null

   select * from students where height is not null;

3 排序

   order by 字段

   – asc從小到大排列，即升序

   – desc從大到小排序，即降序

– 查詢年齡在18到34歲之間的男性，按照年齡從小到到排序

select * from students where (age between 18 and 34) and gender=1;

select * from students where (age between 18 and 34) and gender=1 order by age;

select * from students where (age between 18 and 34) and gender=1 order by age asc;

– 查詢年齡在18到34歲之間的女性，身高從高到矮排序

select * from students where (age between 18 and 34) and gender=2 order by height desc;

   order by 多個字段

   – 查詢年齡在18到34歲之間的女性，身高從高到矮排序, 如果身高相同的情況下按照年齡從小到大排序

   select * from students where (age between 18 and 34) and gender=2 order by height desc,id desc;

– 查詢年齡在18到34歲之間的女性，身高從高到矮排序, 如果身高相同的情況下按照年齡從小到大排序,

– 如果年齡也相同那麼按照id從大到小排序

select * from students where (age between 18 and 34) and gender=2 order by height desc,age asc,id desc;

– 按照年齡從小到大、身高從高到矮的排序

select * from students order by age asc, height desc;

4 聚合函數

   總數

   – count

   – 查詢男性有多少人，女性有多少人

   select * from students where gender=1;

   select count() from students where gender=1;

   select count() as 男性人數 from students where gender=1;

   select count(*) as 女性人數 from students where gender=2;

   最大值

   – max

   – 查詢最大的年齡

   select age from students;

   select max(age) from students;

– 查詢女性的最高 身高

select max(height) from students where gender=2;

   最小值

   – min

   求和

   – sum

   – 計算所有人的年齡總和

   select sum(age) from students;

   平均值

   – avg

   – 計算平均年齡

   select avg(age) from students;

– 計算平均年齡 sum(age)/count(* )

select sum(age)/count( * ) from students;

   四舍五入 round(123.23 , 1) 保留1位小數

   – 計算所有人的平均年齡，保留2位小數

   select round(sum(age)/count(* ), 2) from students;

   select round(sum(age)/count(* ), 3) from students;

– 計算男性的平均身高 保留2位小數

select round(avg(height), 2) from students where gender=1;

– select name, round(avg(height), 2) from students where gender=1;

5 分組

   group by

   – 按照性别分組,查詢所有的性别

   失敗select name from students group by gender;

   失敗select * from students group by gender;

   select gender from students group by gender;

– 計算每種性别中的人數

select gender,count(* ) from students group by gender;

– 計算男性的人數

select gender,count(* ) from students where gender=1 group by gender;

select gender,count(* ),group_concat(name) as 人數 from students where gender=1 group by gender;

   group_concat(…)

   – 查詢同種性别中的姓名

   select gender,group_concat(name) from students where gender=1 group by gender;

   select gender,group_concat(name, age, id) from students where gender=1 group by gender;

   select gender,group_concat(name, "_ ", age, " ", id) from students where gender=1 group by gender;

   having

   – 查詢平均年齡超過30歲的性别，以及姓名 having avg(age) > 30

   select gender, group_concat(name),avg(age) from students group by gender having avg(age)>30;

– 查詢每種性别中的人數多于2個的資訊

select gender, group_concat(name) from students group by gender having count(* )>2;

6 分頁

   limit start, count

– 限制查詢出來的資料個數

select * from students where gender=1 limit 2;

– 查詢前5個資料

select * from students limit 0, 5;

– 查詢id6-10（包含）的書序

select * from students limit 5, 5;

– 每頁顯示2個，第1個頁面

select * from students limit 0,2;

– 每頁顯示2個，第2個頁面

select * from students limit 2,2;

– 每頁顯示2個，第3個頁面

select * from students limit 4,2;

– 每頁顯示2個，第4個頁面

select * from students limit 6,2; – -----> limit (第N頁-1)* 每個的個數, 每頁的個數;

– 每頁顯示2個，顯示第6頁的資訊, 按照年齡從小到大排序

– 失敗select * from students limit 2*(6-1),2;

– 失敗select * from students limit 10,2 order by age asc;

select * from students order by age asc limit 10,2;

select * from students where gender=2 order by height desc limit 0,2;

7 連接配接查詢

   内連接配接 inner join … on

– select … from 表A inner join 表B;

select * from students inner join classes;

– 查詢 有能夠對應班級的學生以及班級資訊

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

– 按照要求顯示姓名、班級

select students.* , classes.name from students inner join classes on students.cls_id=classes.id;

select students.name, classes.name from students inner join classes on students.cls_id=classes.id;

– 給資料表起名字

select s.name as 學生, c.name as 班級 from students as s inner join classes as c on s.cls_id=c.id;

– 查詢 有能夠對應班級的學生以及班級資訊，顯示學生的所有資訊，隻顯示班級名稱

select s.* , c.name from students as s inner join classes as c on s.cls_id=c.id;

– 在以上的查詢中，将班級姓名顯示在第1列

select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id;

– 查詢 有能夠對應班級的學生以及班級資訊, 按照班級進行排序

– select c.xxx s.xxx from student as s inner join clssses as c on … order by …;

select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;

– 當時同一個班級的時候，按照學生的id進行從小到大排序

select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name,s.id;

   left join

   – 查詢每位學生對應的班級資訊

   select * from students as s left join classes as c on s.cls_id=c.id;

   select c.name,s.* from classes as c left join students as s on s.cls_id = c.id order by c.name,s.id;

– 查詢沒有對應班級資訊的學生

– select … from xxx as s left join xxx as c on… where …

– select … from xxx as s left join xxx as c on… having …

select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;

select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;

– right join on

– 将資料表名字互換位置，用left join完成

8 自關聯

– 省級關聯 url:http://demo.lanrenzhijia.com/2014/city0605/

create table booktest_areainfo(

aid int primary key,

atitle varchar(20),

pid int

);

– 查詢所有省份

select * from booktest_areainfo where pid is null;

– 查詢出山東省有哪些市

select * from booktest_areainfo as province inner join booktest_areainfo as city on city.pid=province.aid having province.atitle=“山東省”;

select province.atitle, city.atitle from booktest_areainfo as province inner join booktest_areainfo as city on city.pid=province.aid having province.atitle=“山東省”;

– 查詢出青島市有哪些縣城

select province.atitle, city.atitle from booktest_areainfo as province inner join areas as city on city.pid=province.aid having province.atitle=“青島市”;

select * from areas where pid=(select aid from areas where atitle=“青島市”)

9 子查詢

   标量子查詢

   – 查詢出高于平均身高的資訊

   查詢最高的男生資訊

   select * from students where height = 188;

   select * from students where height = (select max(height) from students);

   列級子查詢

   – 查詢學生的班級号能夠對應的學生資訊

   – select * from students where cls_id in (select id from classes);