Mysql第一篇之基础

1.数据库相关概念

何为数据库：

• 存储和管理数据的仓库，数据是有组织的进行存储。

• 数据库英文名是 DataBase，简称DB。

数据库就是将数据存储在硬盘上，可以达到持久化存储的效果。那又是如何解决上述问题的？使用数据库管理系统。

何为数据库管理系统：

• 管理数据库的大型软件
• 英文：DataBase Management System，简称 DBMS

在电脑上安装了数据库管理系统后，就可以通过数据库管理系统创建数据库来存储数据，也可以通过该系统对数据库中的数据进行数据的增删改查相关的操作。我们平时说的MySQL数据库其实是MySQL数据库管理系统。

安装登录略

2.SQL通用语法

• SQL语句可以单行或多行书写，以分号结尾。
• Mysql数据库的SQL语句不区分大小写，关键字建议使用大写。
• 单行注释： -- 注释内容或者#注释内容（Mysql特有）注：使用--添加单行注释时，--后面一定要加空格，而#没有要求。
• 多行注释：/注释/

3.SQL分类

3.1 DDL（Data Definition Language）

定义：数据定义语言，用来定义数据库对象，数据库，表，列等。简单理解就是用来操作数据库，表等。

3.1.1 库操作

• 查询所有的数据库：show databases;

• 创建数据库：create database 数据库名称；
  

避免创建数据库已经存在情况采用：create database if not exists 数据库名称；

• 删除数据库：drop database 数据库名称；
  删除前判断是否存在：drop database if exists 数据库名称；
  

• 使用数据库：use 数据库名称

• 查看当前使用的数据库：select database（）；
  

3.1.2 操作表

操作表也就是对表进行增，删，改，查。

• 查询表：show tables;
  

• 查询表结构：desc 表名称；
  

• 创建表： CREATE TABLE 表名 (
  字段名1 数据类型1,
  字段名2 数据类型2,
  …
  字段名n 数据类型n
  );
  注：最后一行末尾，不能加逗号

简单表创建：create table t_user(id int,username varchar(12),password varchar(20));

• 删除表：drop table 表名；
  删除前判断是否存在：drop table if exists 表明；
  

• 修改表名：

alter table 表名 rename to 新的表名；
-- 将表名student修改为stu
alter table student rename to stu；

• 表添加一列

alter table 表名 add 列名 数据类型
-- 给stu表添加一列address，该字段类型是varchar（50）
alter table stu add address varchar（50）；

• 修改数据类型

alter table 表名 modify 列名 新数据类型；
-- 将stu表中的address字段类型更改为char（30）
alter table stu modify address char（30）;

• 修改列名和数据类型

alter table 表名 change 列名 新列名 新数据类型；
-- 将stu表中的address字段名改为addr，类型改为varchar（50）
alter table stu change address addr varchar(50);

• 删除列

alter table 表名 drop 列名；
-- 将stu表中的addr字段删除
alter table stu drop addr;

3.2 DML（Data Manipulation Language）

定义：数据操作语言，用来对数据库中的表的数据进行增删改，简单理解就是对表中数据进行增删改。

添加数据

• 给指定列添加数据
  insert into 表名（列名1，列名2，...） values（值1，值2，...）;
• 给全部列添加数据
  insert into 表名 values（值1，值2，...）;
• 批量添加数据
  insert into 表名 values（值1，值2，...），（值1，值2，...），（值1，值2，...）...;
  

修改数据

• 修改表数据：update 表名 set 列名1=值1，列名2=值2，...where 条件;
  注意：修改语句如果不加条件，则将所有数据都修改。

-- 将stu表中lisi的密码改成6666
update stu set password='6666' where username='lisi';

• 删除数据：delete from 表名 where 条件;
  注：如果表名后不加where 条件 则将整个表数据删除。

3.3 DQL（Data Query Language）

定义：数据查询语言，用来查询数据库中表的记录（数据），简单理解就是对数据进行查询操作。从数据库表中查询我们想要的数据。

其实实际使用中绝大多数都是DQL业务

完整语法：
select
     字段列表
from
     表名列表
where
     条件列表
group by
     分组字段
having
     分组后条件
order by
     排序字段
limit
     分页限定

-- 数据准备
-- 删除stu表
drop table if exists stu;


-- 创建stu表
CREATE TABLE stu (
 id int, -- 编号
 name varchar(20), -- 姓名
 age int, -- 年龄
 sex varchar(5), -- 性别
 address varchar(100), -- 地址
 math double(5,2), -- 数学成绩
 english double(5,2), -- 英语成绩
 hire_date date -- 入学时间
);

-- 添加数据
INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date) 
VALUES 
(1,'马运',55,'男','杭州',66,78,'1995-09-01'),
(2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
(3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
(4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
(5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
(6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
(7,'张学右',22,'女','香港',99,99,'1998-09-01'),
(8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');

基础查询

• 查询多个字段
  select 字段列表 from 表名；
  select * from 表名;
  

• 去除重复记录
  select distinct 字段列表 from 表名；

• 起别名：as as也可以省略用空格

条件查询
语法：select 字段列表 from 表名 where 条件列表；

• 条件
  条件列表可以使用以下运算符
  

*联系

• 查询年龄大于20岁的学员信息

  select * from stu where age > 20;
  

• 查询年龄大于等于20岁的学员信息

  select * from stu where age >= 20;
  

• 查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息

  select * from stu where age >= 20 &&  age <= 30;
  select * from stu where age >= 20 and  age <= 30;
  

  上面语句中 && 和 and 都表示并且的意思。建议使用 and 。

  也可以使用 between ... and 来实现上面需求

  select * from stu where age BETWEEN 20 and 30;
  

• 查询入学日期在'1998-09-01' 到 '1999-09-01' 之间的学员信息

  select * from stu where hire_date BETWEEN '1998-09-01' and '1999-09-01';
  

• 查询年龄等于18岁的学员信息

  select * from stu where age = 18;
  

• 查询年龄不等于18岁的学员信息

  select * from stu where age != 18;
  select * from stu where age <> 18;
  

• 查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息

  select * from stu where age = 18 or age = 20 or age = 22;
  select * from stu where age in (18,20 ,22);
  

• 查询英语成绩为 null的学员信息

  null值的比较不能使用 = 或者 != 。需要使用 is 或者 is not

  select * from stu where english = null; -- 这个语句是不行的
  select * from stu where english is null;
  select * from stu where english is not null;
  

== 模糊查询联系==
模糊查询使用like关键字，可以使用通配符进行占位:

（1）_ : 代表单个任意字符

（2）% : 代表任意个数字符

• 查询姓'马'的学员信息

  select * from stu where name like '马%';
  

• 查询第二个字是'花'的学员信息

  select * from stu where name like '_花%';
  

• 查询名字中包含 '德' 的学员信息

  select * from stu where name like '%德%';
  

  排序查询

• 语法
  select 字段列表 from 表名 order by 排序字段名 排序方式，排序字段名2 排序方式2...;

上述语句中的排序方式有两种，分别是：

• ASC ： 升序排列 （默认值）
• DESC ： 降序排列

注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

• 查询学生信息，按照年龄升序排列

  select * from stu order by age ;
  

• 查询学生信息，按照数学成绩降序排列

  select * from stu order by math desc ;
  

• 查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列

  select * from stu order by math desc , english asc ;
  

  聚合函数
  概念：将一列数据作为一个整体，进行纵向计算。
  如何理解呢？假设有如下表
  
  现有一需求让我们求表中所有数据的数学成绩的总和。这就是对math字段进行纵向求和。

• 语句：select id,sum(math) from stu group by id;

聚合函数分类

语法
select 聚合函数名（列名） from 表；
注意：null值不参与所有聚合函数运算

练习

• 统计班级一共有多少个学生

  select count(id) from stu;
  select count(english) from stu;
  

  上面语句根据某个字段进行统计，如果该字段某一行的值为null的话，将不会被统计。所以可以在count(*) 来实现。* 表示所有字段数据，一行中也不可能所有的数据都为null，所以建议使用 count(*)

  select count(*) from stu;
  

• 查询数学成绩的最高分

  select max(math) from stu;
  

• 查询数学成绩的最低分

  select min(math) from stu;
  

• 查询数学成绩的总分

  select sum(math) from stu;
  

• 查询数学成绩的平均分

  select avg(math) from stu;
  

• 查询英语成绩的最低分

  select min(english) from stu;
  

  分组查询

语法：select 字段列表 from 表名 [where 分组前条件限定] group by 分组字段名 [having 分组后条件过滤]；

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

练习

• 查询男同学和女同学各自的数学平均分

  select sex, avg(math) from stu group by sex;
  

  注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

  select name, sex, avg(math) from stu group by sex;  -- 这里查询name字段就没有任何意义
  

• 查询男同学和女同学各自的数学平均分，以及各自人数

  select sex, avg(math),count(*) from stu group by sex;
  

• 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组

  select sex, avg(math),count(*) from stu where math > 70 group by sex;
  

• 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的

  select sex, avg(math),count(*) from stu where math > 70 group by sex having count(*)  > 2;
  

where 和 having 区别：

• 执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。

• 可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。

分页查询

如下图所示，大家在很多网站都见过类似的效果，如京东、百度、淘宝等。分页查询是将数据一页一页的展示给用户看，用户也可以通过点击查看下一页的数据。

SELECT 字段列表 FROM 表名 LIMIT  起始索引 , 查询条目数;

注意： 上述语句中的起始索引是从0开始

练习

• 从0开始查询，查询3条数据

  select * from stu limit 0 , 3;
  

• 每页显示3条数据，查询第1页数据

  select * from stu limit 0 , 3;
  

• 每页显示3条数据，查询第2页数据

  select * from stu limit 3 , 3;
  

• 每页显示3条数据，查询第3页数据

  select * from stu limit 6 , 3;
  

从上面的练习推导出起始索引计算公式：

起始索引 = (当前页码 - 1) * 每页显示的条数

3.4 DCL（Data Control Language）

定义：数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户，简单理解就是对数据库进行权限控制，比如我让某一个数据库表只能让某一个用户进行操作等。

• 查看用户

select user from mysql.user;

• 创建用户：itcast，只能够在当前主机localhost访问，密码123456；

create user 'itcast'@'localhost' identified by '123456';

• 创建用户 heima，可以在任意主机访问该数据库，密码123456
  CREATE user 'Wang'@'%' IDENTIFIED by '123456';
  

• 修改用户Wang的访问密码为1234
  ALTER user 'Wang'@'%' IDENTIFIED with mysql_native_password by '1234';

• 删除用户Wang@%
  drop user 'Wang'@'%';
  

注意：

多个权限之间，使用逗号分隔；
授权时，数据库名和表名可以使用*进行通配，代表所有。

• 查看权限
  show grants for 'Wang'@'%';
  

• 授予权限
  语法：GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
  grant all on itcast.* to 'heima'@'%';

• 撤销权限
  revoke all on itcast.* from 'heima'@'%';

4.数据类型

MySQL 支持多种类型，可以分为三类：

• 数值

  tinyint : 小整数型，占一个字节
  int	： 大整数类型，占四个字节
  	eg ： age int
  double ： 浮点类型
  	使用格式： 字段名 double(总长度,小数点后保留的位数)
  	eg ： score double(5,2)   
  

• 日期

  date ： 日期值。只包含年月日
  	eg ：birthday date ： 
  datetime ： 混合日期和时间值。包含年月日时分秒
  

• 字符串

  char ： 定长字符串。
  	优点：存储性能高
  	缺点：浪费空间
  	eg ： name char(10)  如果存储的数据字符个数不足10个，也会占10个的空间
  varchar ： 变长字符串。
  	优点：节约空间
  	缺点：存储性能底
  	eg ： name varchar(10) 如果存储的数据字符个数不足10个，那就数据字符个数是几就占几个的空间