数据查询

- 创建数据库
 create database ch04 default charset=utf8;
 
 -- 使用数据库
 use ch04;
 
 -- 创建表格
 create table if not exists studentinfo(
   id int primary key auto_increment,
     name varchar(20),
     sex char,
     age int,
     address varchar(100)
);

-- 查看数据表
show tables;

-- 插入数据
insert into studentinfo(name,sex,age,address)values("贾宝玉","男",18,"北平"),
("贾政","男",40,"浙江"),
("晴雯","女",20,"四川"),
("袭人","女",29,"贵州"),
("薛宝钗","女",19,"北平"),
("林黛玉","女",17,"浙江");
-- 查看数据  通配符*表示所有
select* from studentinfo;

-- 手动写出资源 全量查询
select id,name,sex,age,address from studentinfo;

-- 只查询指定的数据列
select id,name,sex from studentinfo;

-- 条件查询
select*from studentinfo where sex="女";

-- 根据查询结果排序
select * from studentinfo order by age desc;
select * from studentinfo order by age desc, id desc;

-- 显示查询结果的条数
select * from studentinfo limit 3; 
-- 显示查询结果中从索引为5开始的往后的3条数据
select * from studentinfo limit 5 ,3;

-- 复合
select * from studentinfo where sex='女' order by age desc limit 3;

-- 别名 AS   as可以省略
select id as 编号,name as 姓名,sex as 性别,age as 年龄,address as 地点 from studentinfo;
select id  编号,name  姓名,sex  性别,age  年龄,address  地点 from studentinfo;

-- 单条件查询
select*from studentinfo where age<20;
select*from studentinfo where age is not null;

-- 多条件查询
-- 不等于
select*from studentinfo where age!=20;
select*from studentinfo where age<>20;
select*from studentinfo where not(age=20);

-- 与  或
select * from studentinfo where age>20 && sex = '女';
select * from studentinfo where age>20 and sex = '女'; -- 建议这中写法
select * from studentinfo where age>20 || sex = '女';
select * from studentinfo where age>20 or sex = '女';

-- 去重  去除重复的数据
select distinct name,sex,age,address from studentinfo;