MySQLデータベース基本操作、コマンドライン作成データベース、データテーブル、クエリー文、曖昧クエリー、範囲クエリー、判断クエリー

久しぶりにデータベースに触ったので、温故知新、捨てたくないので、いくつかの練習命令をしました.

#*******************************  **************************************************

#     
create database python_test charset=utf8;

#         
show create database students;

#     
use python_test;

#              
select database();

#       
#    students   students(id,name,age,high,gender,cls_id,is_delete);
create table students(
    id int unsigned not null auto_increment primary key,
    name varchar(30),
    age tinyint unsigned,
    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
);

#          
show create  table students;

# students      
insert into students values
(0,"  ",18,188.00,2,1,0),
(0,"  ",18,188.00,2,1,1),
(0,"   ",29,185.00,1,1,0),
(0,"  ",59,175.00,1,1,1),
(0,"  ",38,160.00,2,1,0),
(0,"   ",28,150.00,4,2,1),
(0,"   ",18,172.00,2,2,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,2,3,1),
(0,"  ",12,180.00,2,4,0),
(0,"  ",12,170.00,1,1,0),
(0,"  ",37,176.00,2,5,0);

#      
insert into classes values(0,"python_01 "),(0,"python_021 "),(0,"python_04 ");

#-----------------------------  ----------------------------------------
#  --    ,--------------select *from   
select *from students;
select *from classes;
select id ,name from classes;

#      ----------------select  1, 2,....from   ;
select name ,age from students;

#  as      ----------select    as    ...from   ;
select name as   ,age as    from students;

#--------------------------select students.name,students.age from students;
select students.name,students.age from students;

#   as      ---------select   .  ...from    as   ;
select students.name,students.age from students;
select    .name,   .age from students as    ;

#     ----------------select distinct    from   ;
select distinct gender from students;

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

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

#--------------------------age>18   >180.00
select *from students where age>18 or high>180;

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


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

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

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

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

#-----------------    ------------------------------------------------
#---in(1,3,8)            ---     18、34   
select name,age from students where  age in(12,18,34);

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

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


#----------------   ---------------------------------------------------
#      is null  ------     ,     
select *from students where height is null;
select *from students where height is not null;