美文网首页
05-Mysql数据库02

05-Mysql数据库02

作者: 努力爬行中的蜗牛 | 来源:发表于2018-11-21 22:41 被阅读1次

mysql数据查询

-- 创建一个数据库
create database python_test charset=utf8;
-- 使用一个数据库
use python_test;
-- 显示使用的当前数据库是哪个
select database();
-- 创建数据表
-- students
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('man','woman','zhongxin','secret') default 'secret',
    cls_id int unsigned default 0,
    is_delete bit default 0
    );
-- class表
create table classes(
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null
    );
-- 向students表中插入数据
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,175.00,1,1,0),
(0,'刘德华',59,180.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'刘亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'静香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0);
-- 向classes插入数据
insert into classes values
(0,'python_01期'),
(0,'python_02期'),
(0,'python_04期');

-- 查询
--查询所有字段
select * from students;
select * from classes;
select id, name from classes;
-- 查询指定字段
select name, age from students;
-- 使用as给字段起别名
select name as 姓名, age as 年龄 from students;
select students.name, students.age from students;
-- 可以通过as给表起别名
select s.name,s.age from students as s;
-- 消除重复行
select distinct gender from students;

条件查询

-- 比较运算符
select * from students where age>18;
select * from students where age!=18;
-- 逻辑运算符
-- and
select * from students where age>18 and age<28;
-- or
select * from students where age>18 or gender='woman';
-- not
select * from students where not age>18;
select * from students where not (age>18 and gender=2);
select * from students where (not age>18) and gender=2;

模糊查询

-- like
-- % 替换一个或者多个
-- _替换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 "__";
-- 查询至少有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 不非连续的范围内
select name,age from students where age not in (12, 18, 34);
-- between ... and ... 表示在一个连续的范围内
select name,age from students where age between 18 and 34;
select name,age from students where age not between 18 and 34;
select name,age from students where not age between 18 and 34;

判断空

-- 判断 is null
-- 查询身高为空的信息
select * from students where height is null;
select * from students where height is NULL;
select * from students where height is Null;
-- 判断非空 is not null
select * from students where height is not null;

排序

-- order by 字段
-- asc从小到大排列,即升序
-- desc从大到小排序,即降序
select * from students where (age between 18 and 34) and  gender=1 order by age asc;
select * from students where (age between 18 and 34) and  gender=1 order by height desc;
-- 排序先按照heigt 然后 age,id来
select * from students where (age between 18 and 34) and  gender=1 order by height desc,age ascc,id desc;
select * from students order by age asc,height desc;

聚合函数

-- 总数
-- count
-- 查询男性有多少人,女性有多少人
select * from students where gender=1;
select count(*) as 男性人数 from students where gender=1;
-- 最大值
-- max
-- 查询最大的年龄
select max(age) from students;
select max(height) from students where gender=2;
-- min 最小值
select min(height) from students where gender=2;
-- 求和
-- sum
select sum(height) from students where gender=2;
-- 平均值
-- avg
select avg(height) from students where gender=2;
-- 计算平均年龄
select sum(age)/count(*) from students;
-- 四舍五入 round(123.23,1) 保留1位小数
select round(sum(age)/count(*), 2) from students;
-- 计算男性平均身高,保留两位小数
select round(avg(height),2) from students where gender=1;

分组 分组要和聚合一起用

-- group by
-- 按照性别分组,查询所有的性别
select gender from students group by gender;
-- 计算每种性别的人数
select gender, count(*) from students group by gender;
-- 计算男性的人数
select gender,group_concat(name) from students where gender=1 group by gender;
-- group_concat
-- 查询同种性别中的姓名
select gender, group_concat(name,age,id) from students group by gender;
select gender, group_concat(name,"_",age,"_",id) from students group by gender;
select gender, group_concat(name," ",age," ",id) from students group by gender;
-- having
-- 查询平均年龄超过30岁的性别 以及姓名 having avg(age) > 30
-- where是对原表进行判断,having是对查询出来的分组进行判断
select gender,group_concat(name),avg(age) from students group by gender having avg(age)>30;
-- 查询每种性别中人数大于2的
select gender, group_concat(name) from students group by gender having count(*)>2;

分页

-- limit start, count
-- 限制查询出来的数据最大个数
select * from students  where gender=1 limit 2;
-- 查询数据前5个
select * from students limit 0,5;
select * from students where gender=1 order by age asc limit 0,2;

连接查询

  • 内连接查询
    取两个表共有的信息
  • 右连接查询
    谁在right右边就以谁为基准,找不到则左边的显示NULL
  • 左连接查询
    谁在left左边就以谁为基准,找不到则右边的显示NULL
-- inner join ... on
-- select ... from 表A inner join 表B;
select * from students inner join classes;
-- 查询 有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=classes.id;
-- 按照要求显示姓名和班级
select students.*,classes.name from students inner join classes on students.cls_id=classes.id;
-- 给数据表起别名
select s.*,c.name from students as s inner join classes as c on s.cls_id=c.id;
-- 在以上查询中将班级显示在前面
select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;
select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name,s.id;
-- left join on
select c.name,s.* from students as s left join classes as c on s.cls_id=c.id order by c.name,s.id;
select * from students as s left join classes as c on s.cls_id=c.id  having c.id is null;
--right join on
select c.name,s.* from students as s right join classes as c on s.cls_id=c.id order by c.name,s.id;

自关联

-- 创建area表
create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
);

cd到areas.sql目录,然后source areas.sql导入数据到表;

select * from areas where pid=370200;

-- 查询一张表里面的省份对应的城市
select province.atitle,city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle="山东省";

子查询

-- 标量子查询
-- 查询最高男生的信息
 select * from students where height = (select max(height) from students);
数据库设计

三范式
目前有迹可循的共有8总范式,一般需要遵守3中范式即可。

  • 第一范式(1NF):强调的是列的原子性,即列不能够再分成其他几列。
  • 第二范式(2NF):首先是1NF,另外包含两部分内容,一是表必须有一个主键,二是没有包含在主键中的列必须完全依赖于主键,而不能只依赖主键的一部分。
  • 第三范式(3NF):首先是2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖。即不能存在:非主键列A依赖于非主键列B,非主键列B依赖于主键的情况。
    E-R模型
  • E表示entry,实体,设计实体就想定义一个类一样,指定从哪些方面描述对象,一个实体转换为数据库中的一个表;
  • R表示Relationship关系,关系描述两个实体之间的对应规则,关系的类型包括一对一,一对多,多对多
  • 关系也是一种数据,需要通过一个字段存储在表中
  • 实体A对实体B为1对1,则在表A或者表B中创建一个字段,存储另一个表的主键值

相关文章

网友评论

      本文标题:05-Mysql数据库02

      本文链接:https://www.haomeiwen.com/subject/bdmhqqtx.html