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;