MySql(翌日)
34038 ワード
文書ディレクトリデータベース照会操作 1.単一テーブルクエリ .マルチテーブルクエリ 3.MySqlリモート接続 データベース・クエリー・アクション
1.単一テーブルクエリ
2.マルチテーブルクエリ
3.MySqlリモート接続
1.単一テーブルクエリ
--
--
-- create database python_test_1 charset=utf8;
--
-- use python_test_1;
-- 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
-- );
--
--
-- select * from ;
select * from students;
--
-- select 1, 2,... from ;
select name,age from students;
-- as
-- select as .... from ;
select name as name2 from students;
-- select . .... from ;
select students.name from students;
-- as
-- select . .... from as ;
select name from students as s;
select s.name from students as s;
-- ( )
-- distinct
select distinct gender from students;
--
--
> >= < <= != = <>
-- select .... from where .....
-- >
-- 18
select * from students where age > 18;
-- <
-- 18
select * from students where age < 18;
-- >=
-- <=
-- 18
select * from students where age <= 18;
-- =
-- 18
select * from students where age = 18;
-- != <>
-- 18
select * from students where age != 18;
select * from students where age <> 18;
--
and or not
-- and
-- 18 28
select * from students where age > 18 and age < 28;
-- 18
select * from students where age > 18 and gender = ' ';
-- 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 = ' ');
select * from students not age > 18 and gender = ' ';
-- (where name like )
-- like
-- %
-- _ 1
-- " "
select * from students where name like ' %';
-- " "
select * from students where name like '% %';
-- 2
select * from students where name like '__';
-- 3
select * from students where name like '___';
-- 2
select * from students where name like '__%';
--
-- in (1, 3, 8)
-- 18 34
select name from students where age = 18 or age = 34 or age = 56;
select * from students where age in (18,34,56);
-- not in
-- 18 34
select * from students where age not in (18,34,56);
-- between ... and ...
-- 18 34
-- ,
select * from students where age between 18 and 34;
-- not between ... and ...
-- 18 34
select * from students where age not between 18 and 34;
--
-- is null
--
select * from students where height is null;
-- is not null
select * from students where height is not null;
--
-- order by
-- asc
-- asc ,
-- desc
-- desc ,
-- 18 34 ,
select * from students where age between 18 and 34
and gender = ' ' order by age asc ;
-- 18 34 ,
select * from students where age >= 18 and age <= 34 and gender=' '
order by height desc;
-- order by
-- 18 34 , ,
--
select * from students where age >= 18 and age <= 34 and gender=' '
order by height desc , age asc;
-- id
select * from students where age >= 18 and age <= 34 and gender=' '
order by height desc , age asc, id desc;
-- ,
--
--
-- count
--
select count(*) from students where gender = ' ';
--
-- max
--
select max(age) from students;
--
select max(height) from students where gender = ' ';
--
-- min
select min(height) from students where gender = ' ';
--
-- 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( avg(age),2) from students ;
-- 2
select avg(height) from students;
select sum(height) / count(*) from students;
select count(height) from students;
-- sql null
select sum(height) / count(height) from students;
--
select from group by ;
-- group by
-- ,
select gender from students group by gender;
--
select gender,count(*) from students group by gender;
-- group_concat(...)
--
select gender,group_concat(name) from students group by
gender;
--
select gender,avg(age) from students group by gender;
-- having( having group by having )
-- 30 ,
select gender,avg(age),group_concat(name) from students group by gender
having avg(age) > 30;
-- 2
select gender,count(*) from students group by gender
having count(*) > 2;
-- with rollup ( )
select gender,count(*) from students group by gender
with rollup having count(*) > 2;
--
-- limit start, count
-- limit ( )
: ( -1)*
--
-- 5
select * from students limit 0,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;
-- 2 , 6 ,
select * from students order by age asc limit 10,2;
-- ,
2.マルチテーブルクエリ
--
-- inner join ... on
-- select ... from A inner join B;
--
select * 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;
-- , students.*, classes.name.
select students.*,classes.name from students inner join classes
on students.cls_id = classes.id;
-- , 1
select classes.name, students.* from students inner join classes
on students.cls_id = classes.id;
-- ,
select classes.name, students.* from students inner join classes
on students.cls_id = classes.id order by classes.name asc;
-- , id
select classes.name, students.* from students inner join classes
on students.cls_id = classes.id order by classes.name asc ,
students.id asc;
-- left join
--
-- ,
select * from students left join classes
on students.cls_id = classes.id;
--
select * from students left join classes
on students.cls_id = classes.id where classes.id is null;
-- right join on
-- , left join
select * from students right join classes
on classes.id = students.cls_id;
--
-- : ( )
-- : ( )
-- : ( )
-- (height)
select avg(height) from students;
select * from students where height > 172.076923;
select * from students where height >
(select avg(height) from students);
--
select id from classes;
select * from students where cls_id in(1,2);
select * from students where cls_id in (select id from classes);
--
--
create table areas(
aid int primary key,
atitle varchar(20),
pid int
);
-- sql
-- source /areas.sql;
-- , ,
source xxx/areas.sql;
--
select * from areas where pid is null;
-- 1: “ ”
select aid from areas where atitle = ' ';
select * from areas where pid =
(select aid from areas where atitle = ' ');
select * from areas as a1 inner join areas as a2
on a1.pid = a2.aid where a2.atitle = ' ';
-- 2: “ ”
select * from areas where pid =
(select aid from areas where atitle = ' ');
select * from areas as a1 inner join areas as a2
on a1.pid = a2.aid where a2.atitle = ' ';
3.MySqlリモート接続