mysql基础

一、数据库简介

1.什么是数据库

数据库（DB） database,简单的说是一个存储数据的地方

2.关系型数据库

2.1概念

简单的说就是有多张有关联得表组成的数据库

2.2核心元素

数据库：表的集合，一个数据库中可以有多张表
表：由行和列组成的二维表
行：记录一条数据
列：字段

3关系型数据库的产品

• Oracle:在大型项目中使用，如银行，电信项目
• MySQL：互联网时代使用最广泛的关系型数据库
• SQL Server：在微软平台中的项目常用
• SQLite：轻量级数据库，主要应用在移动平台

4.SQL

SQL语言：结构化查询语言
SQL，专门用来操作关系型数据库
SQL不分大小写

二、SQL基础

1.MySQL常用数据类型

• 整数（int）
  • 有符号范围（-2147483648,2147483647）
  • 无符号范围（0,4294967295），int unsigned代表设置一个无符号的整数
• 小整数（tinyint）
  • 有符号范围（-128,127）
  • 无符号范围（0，255）
• 小数（decimal）
  • 如decimal（2,5）表示共存5位数，小数位占2位（不能超过2位），整数占3位
• 字符串（varchar）
  *如varchar（3），表示最多存3个字符，一个中文或者字母都占一个字符
• 时间范围（datetime）
• 文本（text）
  • 用于存储一段无法确定长度的文本内容

面试题

数据库中常用的数据类型[1]

整数，小数，字符串，日期，文本 

有没有发现过和数据类型有关的bug[1]

有，我记得当时开发的时是一个论坛项目，再一次开发评审会议上
后端设计的数据表里，文章标题用的数据类型是varchar（10）
我做了调研，发现10个字符不够，后来建议改为20个字符的限制

2.创建表

2.1 语法

# 语法格式
create table 表名（字段名 数据类型，字段名 数据类型
）

2.2 创建单个字段的表

# 创建表 a 字段要求：name（姓名），数据类型varchar（字符串），长度10create table a (name varchar(10));

2.3 创建多个字段的表

create table b（
id int,
name varchar(20),
age tinyint unsigned
);

3.insert插入数据

语法:insert into 表名 value(值1，值2，值3);

# 例1：往b表里，插入一条记录
insert into b value (0,"张飞"，30);# 例2： 往b表里，插入多条数据
insert into b
values
(1,"关羽",31);
(2,"刘备",32);

指定字段插入的语法

insert into b (id,name) value  (3,"吕布");

面试题

插入数据的SQL命令是什么[1]

insert into 表名 value 括号里面是各个字段名

什么时候插入数据[1]

• 没有硬件设备：如查询产品出库信息，如果没有，就可以插入一条出库的数据
• 需要付款的时候：如，游戏客户端上有个按钮，可以查询游戏皮肤，就可以插入一条相关数据，把账号和拥有的皮肤id等信息关联起来

4.select查询数据

生活中软件需要查询数据的场景

• 购物类软件：搜商品，看详情，看评价，看物流信息，看各种订单
• 学生管理系统：搜学生，看班级信息，看成绩
• 聊天系统：搜附近，搜在线，看朋友圈，看对方详情，看聊天记录

4.1 查询所有的字段

语法：select * from 表名

 # 查询b表所有字段select * from b;

4.2查询指定字段

语法：

select 字段名1，字段名2 from 表名

# 查询表中name，age字段
select name,age from b;

5.update修改数据

语法：

update 表名 set 字段=值,字段=值 where 条件

• 没有where条件

# 将b表所有人的年龄改为50
update b set age = 50;

• 有where条件

# 将b表中id = 3的记录的name改为狄仁杰，age改为20
update b set name = "狄仁杰",age = 20 where id = 3;

6.delete 删除数据

语法：

delete from 表名 where 条件

# 删除表b中id=3的记录
delete from b where id = 3; 
# 删除b中所有的数据
delete from b;

7. truncate删除数据

语法：

# 删除
truncate table 表名;

8.drop删除表

语法1：

# 删除表（包括表结构＋表数据）
drop table 表名

语法2：

#如果表存在，则删除表，如果表不存在，则什么都不做，也不会报错
drop table if exits 表名;

面试题

delete / truncate / drop 三者的区别[3]

• delete 删除表数据，保留主键记录和表结构
• truncate 删除表数据，会同时删除主键记录，保留表结构
• drop 删表，包括表结构，主键记录，表数据全部删除

三、字段约束

语法格式

create table b(
字段名1 数据类型 约束,
字段名1 数据类型 约束,
字段名1 数据类型,
)

常用约束

• 主键primary key，表示值是唯一的，不可以重复，auto_increment代表自动增长
• 非空not null，此字段不允许填写空值
• 默认值default ，当不填此字段的值时，会使用默认值

四、条件查询

1.where

语法：

select * from 表名 where 条件

# 例1:查询student 表中id 为1的数据
select * from student where id = 1;# 例2：查询 student 表中 ， age 为30的姓名 name， 班级class
select name,class from student where age = 30;

where 关键词也可以在update 和delete 语句中使用

工作中用到的场景

• 搜产品
• 看详情
• 搜学生
• 搜附近

2.比较运算符

# = 表示等于
select * from student where age = 25;# < 表示小于
select * from student where age < 25;# > 表示等于
select * from student where age > 25;# >= 表示等于
select * from student where age >= 25;# != 表示等于
select * from student where age != 25;

3.逻辑运算符

• and 与
• or 或
• not 非

# 例1 ：查询 age 年龄小于30，并且sex性别为女 的同学记录
select *  from student where age < 30 and sex = "女"; # 例2 查询sex性别为女或者class 班级为1班的学生记录
select * from student where sex = "女" or class = 1;# 例3： 查询position 职业非辅助的学生记录
select * from student where not position = "辅助";

4.模糊查询

4.1生活中的场景

• 精确查询（等于）
  • 开车出入停车场，车牌号经过精确查询后进行缴费
  • 去药店买药，报精确会员手机号，以便于会员积分
• 查询条件（包含）
  • 百度，搜索关键字
  • 淘宝，根据关键字搜索商品

使用 like实现模糊查询

• %代表任意多个字符
• _代表任意一个字符

# 查询name 以“狂”开头的学生数据
select * from student where name like "狂%";
select * from student where name like "狂_";# 查询name 以“铁”结尾的学生数据
select * from student where name like "%铁";
select * from student where name like "_铁";# 查询name 含有“周”的学生数据
select * from student where name like "%周%";

5.模糊查询

• 通过 between...and...在连续的范围内进行查找

# 例1 查询age年龄为25至30的学生记录
select * from student where age between 25 and 30;

• 通过in，在非连续的范围内进行查找

# 例2 查询position 职业是“刺客”或“射手”或“辅助”的学生
select * from student where position = "刺客" or position = "辅助" or position = "射手";
select * from student where position in ("刺客","射手","辅助"); 

6.null

# 例 查询sex性别为空的学生记录
select * from student where sex is null;
# 例 查询sex性别为非空的学生记录
select * from student where sex is not null;

注意
-null在SQL中代表空，而不是0，也不是""(空串)，而是什么也没有
有 is null 判断为空
is notnull 判断非空
null 不能用比较运算符判断

五、别名

1.别名

表的别名

# 通过 as 给表student 起个别名
select * from student as stu;# as 可以省略
select * from student stu;

六、排序

1.语法

# 升序（从小到大）：
order by 字段名 asc; # 其中asc是默认排序，可以不写
# 降序（从大到小）
order by 字段名 desc;

单字段排序

# 查询student学生数据，按age年龄从小到大排序
select * from student order by age asc;
select * from student order by age ;# 查询student学生数据，按age年龄从大到小排序
select * from student order by age desc;

多字段排序

# 查询student学生数据，按age 年龄从小到大排序
# 当年龄相同时再按照id从小到大的排序
select * from student order by  age,id;

当一个select语句中出现了where 和order by

语法：

select * from 表名 where 条件 order by 字段1，字段2;

# 查询student所有男生数据，按班级升序,班级相同再按年龄升序
select*
fromstudent
wheresex = "男"
order byclass,age;

七.聚合函数

1.count 计数

count(字段名)或者count(*)

# 查询student表的学生总数
select count(*) from student;
select count(id) from student;
# count(*)和count(id)区别
#查询结果是一样的，效率不同，大量数据时，后者更快
# 查询 student表的女生总数
select count(*) from student where sex = "女";
select count(id) from student where sex = "女";

去重 distinct

# 查询student 表的班级总数
select class from student; --只查出class这一列的数据
select count(class) from student;--查出的是有班级数据的数据总条数
select distinct class from student;--去重后都有什么班级
select count(distinct class) from student; --去重后，统计班级总数

2.max最大值

语法

max(字段名)--查询指定字段里的最大值

# 查询 student 中的最大年龄
select max(age) from student;# 查询student中女生的最大年龄
select max(age) from student where sex = "女";

3.min 最小值

语法：

min(字段名) --查询指定字段里的最小值

# 查询student 中的最小年龄
select min(age) from student;# 查询student 中女生的最小年龄
select min(age) from student where sex = "女";

4.sum 求和

语法：

sum(字段名) --指定字段的值求和

# 查询student表中 年龄的总和
select sum(age) from student
# 查询student表中女生年龄的总和
select sum(age) from student where sex = "女";

5.avg求平均数

语法

# 查询student表中 年龄的平均值
select avg(age) from student;# 查询student 表中女生年龄的平均值
select avg(age) from student where sex = "女";

如果值中含有null， null不参与平均值的计算

聚合函数不能用到where 条件中

九.数据分组

1.分组的实现

生活中需要分组统计的场景

• 老师查全班成绩，需要统计及格和不及格分别有多少人
• 记录日常开销的软件，我们可能需要统计本月收入和开支总计多少钱
  实现
• 分组一般需要配合聚合函数来使用
• group by 字段名
• select 聚合函数 from 表名 group by 字段名
• select 字段名，聚合函数 from 表名 group by 字段名；
• select 字段名，聚合函数 from 表名 where 条件 group by 字段名

# 按不同性别分组，来来查询student 表中男女的总数
select count(*) from student where sex = "男";
select count(*) from student where sex = "女";select count(*) from student group by sex;
select sex,count(*) from student group by sex;# 按不同性别分组，来查询student 表中3班的男女的总数
select sex, count(*) from student where class = 3 group by sex;

• 多字段分组

# 按不同班级，不同性别分组，来查询 student 表中男女的总数
select class,sex, count(*) from student group by class ,sex;# 按不同班级，不同职业分组，来统计student 表中男女的总数
select class,position,count(*) from student group by class,position;

• 分组后的排序
• select 字段名，聚合函数 from 表名 where 条件 group by 字段 order by 字段;
• 先条件再分组再排序

# 按照不同班级不同性别进行分组，按班级降序，来统计student 表中 男女的总数
select class , sex , count(*) from student group by class,sex, order by class desc;# 按照不同班级不同性别进行分组，按班级降序，来统计student 表中成年的男女的总数
select class , sex , count(*) from student where age>=18 group by class,sex, order by class desc;

2. 分组后的数据筛选

• group by 字段 having 条件

# 查询student 表的男生总数# where ，先筛选，在统计
select count(*) from student where sex = "男";
# having 先分组，再筛选 ，再统计
select count(*) from student group by sex having sex = "男";

• having 配合聚合函数的使用

# 查询student 表，统计班级人数大于3人的班级是
select class from student group by class having count(*)>3;

面试题

where 与 having的筛选区别[2]

• 筛选逻辑
  • where对原始数据进行筛选，效率较低
  • having 是对group by 这个分组 后的数据进行筛选，效率较高
• having 可以使用聚合函数，where不可以