python階段(10)_mysqlデータ検索

62651 ワード

Python

データの準備

は、データベース

create database python_test_1 charset=utf8;

を作成します.

は、1つのデータベース

use python_test_1;

を使用する.

使用している現在のデータベースはどれですか?

select database();

は、データテーブル

-- students 
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum(' ',' ','  ','  ') default '  ',
    cls_id int unsigned default 0,
    is_delete bit default 0
);
-- 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,'   ',59,175.00,1,2,1),
(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 ");

クエリー

すべてのフィールドをselect*fromテーブル名で検索する.

select * from students;
select * from classes;

指定フィールドselect列1,列2,…fromテーブル名を照会する.

select name,age from students;

asを使用してフィールドからエイリアスフィールド1［asエイリアス1］、フィールド2［asエイリアス2］、…fromテーブル名.

select id as   , name as   , gender as    from students;

表名.フィールド名

select students.id,students.name,students.gender from students;

は、asによって表から別名

select s.id,s.name,s.gender from students as s;
--   :select students.id,students.name,students.gender from students as s

を送ることができる.

重複行を削除し、selectの後の列の前にdistinctを使用して重複行をなくすことができます.

select distinct gender from students;

条件クエリー
select…from表名where…;

比較演算子

イコール=

は大きいです.>>

は等しいです.>=

より小さいです.

以下は等しいです.

は等しくないです.または<>

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

-- <
--     18    
select * from students where age<18;

-- >=
-- <=
--         18    

-- =
--      18         
select * from students where age=18;


-- !=    <>

論理演算子

and

not

-- 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;

あいまい検索

%を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     
select name from students where name like "___";

--      2     
select name from students where name like "__%";

カーキ正則

--           
select name from students where name rlike "^ .*";

--        、      
select name from students where name rlike "^ .* $";

範囲クエリ

in(1,3,8)は、1つの非連続的な範囲内で

を表す.

--       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;

並べ替え

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;

集計関数

総数

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;

四捨五入羅und(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;

グループ

group by

--       ,       
--select name from students group by gender;
--select * from students group by gender;
select gender from students group by gender;
--   select * from students group by gender;

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

--        
select gender,group_concat(name) from students group by gender;

--        
select gender,count(*) 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;

ページ

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;

接続クエリ

inner join…on

select…fromテーブルA inner jinテーブルB;

--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, c.name 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 jin(左のデータテーブルを基準にNULLが表示されていません)

--              
select * from students as s left join classes as c on s.cls_id=c.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;

ライトジョイン

データテーブルの名前を位置に変えて、left joinで

を完成します.

自己関連

省レベル連動url:http://demo.lanrenzhijia.com/2014/city0605/

arasテーブルを作成するステートメントは以下の通りです.

    create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
);

sqlファイルからデータを導入します.

サブクエリ

スケールの量子クエリ

source areas.sql;

--       
select * from areas where pid is null;

--           
select * from areas as province inner join areas as city on city.pid=province.aid having province.atitle="   ";
select province.atitle, city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle="   ";

--            
select province.atitle, city.atitle from areas 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="   ")

列級サブクエリ

--             

--          
select * from students where height = 188;
select * from students where height = (select max(height) from students);
select * from areas where pid = (select aid from areas where atitle="   ");

Android AlertDialogフルスクリーンは白い枠を取り除くと表示します.

クライアントがwebserviceプロジェクトを発表し、サーバー側がwebserviceを使用する簡単な例