数据查询入门

时间:2021-6-9 作者:qvyue
-- 创建数据库
create database if not exists schools default charset=utf8;
-- 使用数据库
use schools;
-- 创建表格
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 = '女';
-- 根据查询结果排序  order by
select * from studentinfo order by age desc;
select * from studentinfo order by age desc ,id asc;

-- 显示查询结果的条数
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 age20;
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;
声明:本文内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:qvyue@qq.com 进行举报,并提供相关证据,工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。