--select * from 表名
select * from students;
select * from students where name='小李飞刀';
select * from students where id>3;
select name, gender from students;
select name as 姓名, gender as 性别 from students;
select id as 序号, gender as 性别, name as 姓名 from students;
create table students (
id int unsigned not null auto_increment primary key,
name varchar(20) default '',
age tinyint unsigned default 0,
high decimal(5,2),
gender enum('男', '女', '中性', '保密') default '保密',
cls_id int unsigned default 0,
is_delete bit default 0
);
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(20) not null
);
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月月',19,180.00,2,2,0),
(0,'布莱恩特',28,185.00,1,1,0),
(0,'刘德华',58,175.00,1,2,0),
(0,'黄蓉',108,160.00,2,1,0),
(0,'凤姐',44,150.00,4,2,1),
(0,'李白',52,170.00,2,1,1),
(0,'周杰伦儿',34,null,1,1,0),
(0,'程坤',44,181.00,1,2,0),
(0,'金镖十三郎',55,166.00,2,2,0),
(0,'刘亦菲',29,162.00,3,3,0),
(0,'金星',45,180.00,2,4,0),
(0,'静香',18,170.00,1,4,0),
(0,'项羽',22,167.00,2,5,0),
(0,'周杰',33,178.00,1,1,0);
insert into classes values (0, 'python_01期'),(0, 'python_02期');
select * from students;
select * from classes;
select name, age from students;
select name as 姓名, age as 年龄 from students;
select students.name, students.age from students;
select s.name, s.age from students as s;
-- distinct
select distinct gender from students;
select * from students where age > 18;
select id, name, gender from students where age > 18;
select * from students where age>18 and age<28;
select * from students where age>18 or high>=180;
select * from students where name like '小%';
select * from students where name like '%小%';
select * from students where name like '__';
select * from students where name like '__%';
select * from students where name rlike '^周.*';
select * from students where name rlike '^周.*儿$';
select * from students where age=18 or age=34;
select * from students where age=18 or age=34 or age=12;
select * from students where age in (12,18,34);
select * from students where age between 18 and 34;--(不包含34岁)
select * from students where age not between 18 and 34;
-- 判断is null
-- 查询身高为空的信息
select * from students where high is null;
-- 判断非空is not null
select * from students where high is not null;
select * from students order by age asc;
asc从小到大排序
desc从小到大
手机扫一扫
移动阅读更方便
你可能感兴趣的文章