因为好久没碰数据库了，温故而知新，不想丢掉，做了一些练习命令：
#*******************************练习**************************************************#创建数据库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.00select *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;
如果觉得《MySQL数据库基本操作 命令行创建数据库 数据表 查询语句 模糊查询 范围查询 判断查询》对你有帮助，请点赞、收藏，并留下你的观点哦！
