mysqlデータベース-すべてのクエリー構文
9084 ワード
，
，
mysql -uroot -p
quit exit ctrl+d
，
：select version();
：select now();
\D
\U
1.クエリー
--
-- select * from ;
select * from students;
--
-- select 1, 2,... from ;
select name,age from students;
-- as
-- select as .... from ;
select name as ' ',age as ' ' from students; -- as
select name ' ',age ' ' from students;
-- sql
select students.* from students;
select python_test_1.students.* from students;
--
-- sql
-- select . .... from ;
select students.name,students.age from students;
-- as
-- select . .... from as ;
select s.name, s.age from students as s; -- sql students;
--
-- distinct
--
select gender from students;
--
select distinct gender from students;
select distinct id,gender from students;
2.条件照会
select * from students where 1 > 0;
--
-- >
-- 18
-- <
-- 18
-- >=
-- <=
-- 18
-- = '=='
-- 18
-- != <> !
select * from students where age != 18;
-- <>
--
-- and
-- 18
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 age != 18;
select * from students where not age = 18;
-- 18
select * from students where age <= 18 and gender = 2;
--
-- like
-- %
-- " "
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 "___";
-- rlike
-- match findall re
-- r"[a-z]{6}"
--
select * from students where name rlike "^ .*";
select * from students where name rlike "^ .* $";
--
-- in
-- 18、34
select * from students where age = 18 or age = 34;
select * from students where age in (18, 34);
-- not in
-- 18、34
select * from students where age not in (18, 34);
-- 18、34
select * from students where age < 18 or age > 34;
-- 18 ~ 34
select * from students where not (age < 18 or age > 34);
-- 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;
select * from students where not age between 18 and 34;
-- null
--
# select * from students where height = null;
select * from students where height is null;
--
select * from students where height is not null;
3.並べ替え大から小へ->降順並べ替え小から大へ->昇順並べ替え
-- order by asc
-- asc ，
-- 18 34 ，
select * from students where age between 18 and 34 and gender = 1 order by age asc;
-- desc
-- desc ，
-- 18 34 ，
select * from students where age between 18 and 34 and gender = 2 order by height desc;
-- order by order by age asc, height desc
-- 18 34 ， ,
select * from students where age between 18 and 34 and gender = 2 order by height desc,age asc;
-- 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;
, asc ,
4.集約関数-->統計のために生成
--
--
select count(*) from students;
select count(id) from students;
-- count(*)
-- count(*) , :count(id)--> --> NULL
-- ，
select count(*) from students where gender = 1;
select count(*) from students where gender = 2;
--
-- max()
--
select max(age) from students;
--
select max(height) from students where gender = 2;
--
# select max(age),name from students where gender = 2;
select name from students where age = (select max(age) from students);
# : select name, max(height) from students;
--
-- min()
--
-- sum()
--
select sum(age) from students;
--
select sum(age) / count(*) from students;
# select sum(height) / count(*) from students;
select sum(height) / count(height) from students;
--
-- avg()
--
select avg(age) from students;
--
select sum(height) / count(height) from students;
-- round(123.23 , 1) 1
-- ， 2
select round(avg(age),2) from students;
-- 2
5.グループ化
-- group by
--
select gender from students;
--
select distinct gender from students;
--
select gender 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,name from students group by gender;
: select gender, * from students group by gender;
, ..
, , ..
--
select gender,group_concat(name,height) from students group by gender;
--
select count(*) from students where gender = 1;
--
select gender, count(*) from students group by gender having gender = 1;
-- having where
-- having
-- having
--
select gender, count(*) from students group by gender having gender != 1;
select gender, count(*) from students group by gender having not gender = 1;
# select gender, count(*) from students group by gender not having gender = 1;
-- having
-- avg(age)
select gender, avg(age) from students group by gender;
-- avg(age), , ,
select gender, avg(age),max(age), avg(height), max(height) from students group by gender;
-- 30 ， 30
select gender,group_concat(name) from students group by gender having avg(age) > 30;
-- having where
having
where
having group by group by having
6.ページング
-- limit start, count
-- start , 0 ,
-- count,
select * from students limit 5;
n(n 1 ), m
limit (n - 1) * m, m;
1
select * from students limit 0,5;
2
select * from students limit 5,5;
3
select * from students limit 10,5;
-- 4 ， 3 ,
select * from students limit 8,4 order by age asc;
select * from students order by age asc limit 8,4;
7.接続クエリー2つのテーブルをある条件で結合する
--
-- : students;
-- : classes
select students.name,classes.name from students,classes where students.cls_id = classes.id;
select * from students,classes;
--
--
-- A B
-- A inner join B... on A.cls_id = B.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 * from students as s inner join classes as c on s.cls_id = c.id;
-- 、 ( )
select s.name, c.name from students as s inner join classes as c on s.cls_id = c.id;
-- ， 1
select c.name, s.name from students as s inner join classes as c on s.cls_id = c.id;
-- ,
select c.name, s.name from students as s inner join classes as c on s.cls_id = c.id order by s.name asc;
-- : left join + right join
-- left join
--
-- ( + )
select * from students as s left join classes as c on s.cls_id = c.id;
-- right join
-- ， left join
--
--
--
select c.name, s.name from students as s join classes as c on s.cls_id = c.id;
select c.name, s.name from students as s cross join classes as c on s.cls_id = c.id;
--
select * from students as s left outer join classes as c on s.cls_id = c.id;
--
-- alter table goods drop foreign key ;
--
alter table goods drop foreign key goods_ibfk_1;
--
show create table goods;