MYSQL之简单语句查询一

使用命令行方式操作mysql数据库:

• 登录数据库:mysql -uroot -p
• 创建数据库:

create database python_test charset=utf-8;

• 查看创建的数据库:

show databases;

• 使用刚才创建的数据库:

use python_test;

• 查询当前使用的数据库:

select database();

• 创建表:

create table students(
    id int unsigned primary key auto_increment,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    gender enum('男','女','中性','保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);

• 查看表信息:

desc students

• 查看创建表信息:

show create table students;

• 插入数据:

insert into students values     
(0,'小明',18,2,1,0,180.00),     
 (0,'小月月',18,2,2,1,180.00),      
(0,'彭于晏',18,1,1,0,185.00),      
(0,'刘德华',29,1,2,0,175.00),      
(0,'黄蓉',38,2,1,0,160.00),     
 (0,'凤姐',28,4,2,1,150.00),      
(0,'王祖贤',18,2,1,1,172.00),     
(0,'周杰伦',36,1,1,0,NULL),      
(0,'程坤',27,1,2,0,181.00),      
(0,'刘亦菲',25,2,2,0,166.00),     
 (0,'金星',33,3,3,1,162.00),      
(0,'静香',12,2,4,0,180.00),      
(0,'郭靖',11,1,4,0,170.00),      
(0,'周杰',34,2,5,0,176.00);

• 查询数据:

select * from students;

• 添加字段:

alter table students add height decimal(5,2);

• 使用别名:as

select s.name,s.age from students as s;

• 去重查询:distinct

select distinct s.gender from students as s;

• 条件语句查询:

select * from students where age > 18;
select * from students where age > 18 and age < 30;
select * from students where age < 18 or age > 30;
select * from students where not age > 30;

• 模糊查询:

//%表示无或者多个匹配位置,_表示一个匹配位置
select name from students where name like "小%"; 
select name from students where name like "小_";
select name from students where name like "%小%";
//rlike使用正则匹配  

• 范围查询:in

select name,age from students where age in (12,18,30);
select name,age from students where age not in (12,18,30);

select name,age from students where age between 12 and 34;
select name,age from students where age not between 12 and 34;
select name,age from students where not age between 12 and 34;

//错误写法:select name,age from students where age not (between 12 and 34);

• 空判断:

select * from students where height is null;
select * from students where height is not null;