MySql数据库世界

MySql

1. 数据库基本概念

1. 数据库：

DateBase 简写DB

2.概述：

数据库是用于存储和管理数据的仓库

3.特点：

1. 用于持久化存储数据的，数据库其实就是一个文件系统
2. 方便存储和管理数据
3. 使用统一的操作数据语言—SQL（普通话）

4.常见的数据库：

• MySQL
  • 特点：开源，免费，深受中小企业喜爱
• Oracle
  • 特点：安全，扩展性强，功能完备
• SQL Server
  • 特点：C# 和 .NET项目中应用比较多

5.启动和关闭

​ MySQL安装好之后，需要启动服务，什么是**服务？**服务就是没有界面的应用程序

• 启动，关闭服务
  • 方式1：（开始键 + R） 运行-> services.msc
  • 方式2：管理员身份cmd -> net start MySql 或 net stopMySql
    • 注意：命令中的MySql 一定要与你的mysql服务的名称一致。

6. MySql 登录

• 方式1：
  • mysql -u用户名 -p密码
• 方式2：
  • mysql -h目标主机IP地址 -u用户名 -p密码
• 方式3：
  • mysql --host=目标主机IP地址 --user=用户名 --password=密码

7. MySql 退出

• quit
• exit

2 SQL

2.1 概述：

SQL：结构化查询语言(Structured Query Language)简称SQL。只要是关系型数据库，都可以用SQL语言区操作数据。不同的数据库之间的SQL操作略有不同，但是主体部分还是一样的。对于数据库之间的差异，我们称之为：“方言”。

2.2 SQL语法

1. SQL语句可以是单行，也可以是多行，多行以分号结尾

   ->show databases
   ->;
   

2. 使用空格 和 缩进来增强语句的可读性

3. MySql数据库的SQL语句不区分大小写，关键词建议使用大写

4. 注释

   1. 单行注释 ：--注释内容 或 #注释内容（Mysql特有）
   2. 多行注释：/* 注释内容 */

2.3 SQL语言的分类

1. DDL（Data Definition Language）:数据库定义语言

用来定义数据库对象：数据库，表，列表等。关键字：create ,drop ,alter 等

2. DML（Data Manipulation Language）：数据库操作语言

   用来对数据库中表的数据进行增删改操作的。关键字：insert,delete,update等

3. DQL（Data Query Language）:数据库查询语言

   用来查询表中的数据。关键字：select , where

4. DCL(Data Control Language）: 数据库控制语言

   用来定义数据库的访问权限 和 安全级别，创建用户。关键字 grant ,revoke等

3.DDL- 数据库定义语言

3.1 操作数据库 - CRUD

1.C – Create 创建

-- 创建数据库
CREATE DATABASE test;

-- 查看test数据库-编码集
SHOW CREATE DATABASE test;

注意：

1. 数据库已存在，再次创建就会报错

-- 如果test库不存在，就创建
CREATE DATABASE IF NOT EXISTS test;

2. 创建数据库时指定编码集

-- 创建数据时指定编码集
CREATE DATABASE test2 char set utf8;-- 将数据库test2 的编码集指定为utf-8
show create database test2; --查看test2数据库-编码集

练习: 创建一个数据库test3 ，并在创建时判断其是否存在，然后指定它的编码集为gbk；

-- 练习：创建一个数据库test3 ，并在创建时判断其是否存在，然后指定它的编码集为gbk
CREATE DATABASE IF NOT EXISTS test3 CHAR SET gbk;
SHOW CREATE DATABASE test3; 

2.R – Retrieve 查询

1. 查询所有数据库名称：

show databases;

2. 查询指定数据库的创建语句 及 给数据库的编码集

SHOW CREATE DATABASE 数据库名; 

3.U – Update 修改

​ 修改数据库编码集

ALTER DATABASE 数据库名 CHAR SET 字符集;

4.D – Drop 删除

DROP DATABASE IF EXISTS 数据库名;

5.使用数据库

USE 数据库名;

3.2 操作表

1.C – Create 创建

学生表

学号	姓名	年龄

语法：

CREATE TABLE 表名（
	列名1 数据类型1,
	列名2 数据类型2，
	……
	列名n 数据类型n
）;

注意：

1. 最后一个列，不需要加 ,

2. 常用数据类型

   1. int :整型
   

   例：age INT

   2. float , double 小数类型

   例：score float(5,2)共5个数字，小数位为2为

   3. date:日期类型：只包含年月日 yyyy-MM-dd

   4. datetime:日期类型：包含年月日时分秒 yyyy-MM-dd HH-mm-ss

   5. timestamp 时间戳类型：包含年月日时分秒 yyyy-MM-dd HH-mm-ss

      该类型在插入数据时，不赋值或赋null,则数据库会使用当前系统时间，来自动赋值。

   6. char,varchar 字符串

      char(n)：定长度数据类型，该类型在存储数据时，如果数据长度<n，则剩余部分用空格补齐，查询时， 再去掉空格。

      varchar(n)：变长度数据类型，该类型比较常用。

-- 创建学生表
create table IF NOT EXISTS student(
	id int,-- 学号
	name varchar(20), -- 姓名
	age int, -- 年龄
	score double(5,2), -- 成绩
	birthday date, -- 生日
	insert_time timestamp -- 插入时间
);
-- 查询当前数据库中所有的表
show tables;

2. R – Retrieve 查询

1. 查询当前数据库中所有的表

SHOW TABLES;

2. 查询表结构

DESC 表名;

3.U – Update 修改

1. 修改表名

ALTER TABLE 旧表名 RENAME TO 新表名;

2. 修改表的字符集

-- 修改字符集
ALTER TABLE stu CHAR SET utf8;

3. 添加字段

-- 添加字段
alter table stu add sex char(2);

4. 修改列名，数据类型

方式1：ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型;

ALTER TABLE stu  CHANGE sex gender VARCHAR(10);

方式2：ALTER TABLE 表名 MODIFY 列名 新数据类型；

ALTER TABLE stu  MODIFY gender VARCHAR(5);

5. 删除列

-- 删除列
ALTER TABLE stu DROP gender;

4.D – Drop 删除

-- 删除表
DROP TABLE IF EXISTS stu;

4.DML-数据库操作语言

4.1 添加数据

语法：

​ insert into 表名 [（字段1，字段2，…）] values ( value1 , value2 , … );

注意：

1. 列名 和 值 需要 一一对应
2. 如果插入语句中不加列名，则表示默认向所有字段添加数据
3. 除了数字类型，其他数据类型都需要加引号（单双引号都可以）。

-- 插入一条数据
insert into student(id,name,age,score,birthday,insert_time) values (1,'周杰伦',18,98.50,'1988-1-18',null);
insert into student values (2,'林俊杰',17,99.50,'1989-1-18',null);
INSERT INTO student (id,name,age,score,insert_time) VALUES (3,'蔡依林',17,99.50,NULL);
DESC student;

-- DQL 查询表中所有数据
select * from student;

4.2 删除数据

语法：

​ delete from 表名 [ where 条件]

注意：

1.  如果不带条件，则会把表中的所有数据都删除
2.  如果删除表中所有记录
   	1.  delete from 表名，
   	2.  truncate table 表名，

-- 删除数据
DELETE FROM student WHERE id =1;
DELETE FROM student; -- 清空表中数据
TRUNCATE TABLE student;-- 清空表中数据

4.3 修改数据

语法：

update 表名 set 列名1 = value1 , 列名2 = value2 ,…[ where 条件]

注意：

​ 不加条件，默认修改所有数据对应的字段。

-- 修改数据
UPDATE student SET age=20;

-- 将id= 2的学生的年龄修改成18
UPDATE student SET age=18 WHERE id=2;

-- 将姓名为周杰伦的学生成绩修改为100分
UPDATE student SET score=100 WHERE NAME='周杰伦';

5. DQL-数据库查询语言

语法：

​ select

​ 字段列表

​ from

​ 表名列表

where

​ 条件列表

​ group by

​ 分组字段

​ having

​ 分组之后的条件

​ order by

​ 排序

​ limit

​ 分页限定

测试表：

学生表(utf8)： 学号，姓名，年龄，性别，地址，java(int) , c

5.1 基础查询

1. SELECT * FROM 表名；-- 查询该表中所有字段对应的数据

2.SELECT 字段1，字段2，… FROM 表名；-- 查询该表中指定字段对应的数据

-- SELECT  字段1，字段2，... FROM 表名； 查询该表中指定字段对应的数据
-- 查询学生表中所有信息的姓名和年龄
SELECT NAME,age FROM student;

**3. 去重查询： SELECT DISTINCT 字段1，字段2，… FROM 表名 **

-- 查询学生表中的地址字段，并去重
SELECT DISTINCT adress FROM student;

4. 运算查询

-- 查询学生的java，c的成绩还有总成绩
select java,c,(java+c) as 总成绩 from student;-- as 别名

注意：

​ 当字段中有null值参与运算，结果也为null，这样的结果有些不太合理，可以使用一个函数ifnull()

5.2 条件查询

1. where 子句后跟条件

2.运算符

• > , < , >= , <= , = , <>
• BETWEEN…AND
• IN( 集合)
• LIKE :模糊查询
  • 占位符
    • _: 一个任意字符
    • %：多个任意字符
• IS NULL
• AND 或者 &&
• OR 或 ||
• NOT 或 ！

-- 查询年龄大于20岁的学生信息
select * from student where age>20;

-- 查询年龄大于20岁的学生信息包含20岁
SELECT * FROM student WHERE age>=27;

-- 查询年龄不等于27岁的学生信息
select * from student where age!=27;
select * from student where age<>27;

-- 查询年龄在 20岁 - 30岁之间的学生信息
select * from student where age>=20 and age<=30;
select * from student where age>=20 && age <=30;
select * from student where age between 20 and 30 ;

-- 查询年龄是16，18，19岁的学生信息
select * from student where age=16 or age=18 or age=19;
SELECT * FROM student WHERE age=16 || age=18 || age=19;
SELECT * FROM student WHERE age in(16,18,19);

-- 查询c语言缺考的学生
SELECT * FROM student WHERE c=null;-- 该条语句不正确，判断值是否为null要使用 is null 判断
select * from student where c is null;

-- 查询有C语言成绩的学生
SELECT * FROM student WHERE c IS not NULL;

5.3 like- 模糊查询

模糊查询在项目中应用比较多，基本上所有搜索框都是模糊查询

#like查询
-- 查询 姓马的学生信息
select * from student where name like '马%';

-- 查询第二个字有克学生信息
select * from student where name like '_克%';

-- 查询名字中包含马字的学生信息
select * from student where name like '%马%';

-- 查询名字是三个字的学生信息
SELECT * FROM student WHERE NAME LIKE '___';

5.4 排序查询

格式：

​ order by 子句

具体写法：

​ order by 排序字段1 排序方式 ，排序字段2 排序方式2 …

排序方式

• ASC ：升序（默认）
• DESC: 降序

-- 查询学生信息，并按照java成绩升序排列
SELECT * FROM student ORDER BY java;
SELECT * FROM student ORDER BY java ASC;

-- 查询学生成绩 按照java成绩降序排列
SELECT * FROM student ORDER BY java DESC;

-- 查询学生成绩 照java成绩降序排列,如果java成绩相同，再按照C成绩进行降序排列
SELECT * FROM student ORDER BY java DESC,c DESC;

注意：

​ 如果有多个排序条件，当前一个条件值相同时，才会按照第二个条件进行排序

5.5 聚合函数

概述：

​ 将一列数据作为整体，进行纵向计算

格式：

​ select 聚合函数（字段名） [ as 别名 ] from 表名

分类

1. count : 统计个数
2. max:计算最大值
3. min:计算最小值
4. sum:求和
5. avg:求平均值

注意事项：

​ 聚合函数在进行计算时，会自动排除null

解决方案：

1.  选择非空字段进行计算
2.  ifnull函数

-- 统计学生表中有多少条学生信息
select count(id) from student;
SELECT COUNT(ifnull(c,0)) as 总记录数 FROM student;

-- 求学生中c语言成绩最高分
select max(ifnull(c,0)) as C最高分 from student;

-- 求学生中c语言成绩最低分
SELECT min(IFNULL(c,0)) AS C最低分 FROM student;
SELECT MIN(c) AS C最低分 FROM student;

-- 求所有学生的java成绩的总和
select sum(java) as java总成绩 from student;

-- 求所有学生的java的平均成绩
select AVG(java) as java平均成绩 from student;

5.6 分组查询

格式：

​ group by 分组字段

注意：

​ 1. 分组之后查询的字段：分组字段，聚合函数

select * from student group by sex;-- 其他字段是没有任何意义的
select sex from student group by sex;

-- 按照性别分组分别查询男生和女生的java平均分
SELECT sex,AVG(java) java平均分 FROM student GROUP BY sex;

-- 按照性别分组分别查询男生和女生的java平均分，及相应性别对应的人数
select sex,avg(java) java平均分,count(id) 人数 from student group by sex;

-- 分组前添加一些限制条件
-- 按照性别分组分别查询男生和女生的c平均分，及相应性别对应的人数,分组要求：分数<=60分就不参与统计
select sex,AVG(ifnull(c,0)) java平均分,COUNT(id) 人数 from student where c > 60 group by sex;

-- 按照性别分组分别查询男生和女生的c平均分，及相应性别对应的人数,分组要求：分数<=60分就不参与统计 
-- 并且人数<2人结果我不查看

SELECT sex,AVG(IFNULL(c,0)) java平均分,COUNT(id) 人数 FROM student WHERE c > 60 GROUP BY sex having 人数 >=2;

2. where 和 having 的区别

• where 对分组前进行限定，如果不满足，则不参与分组，having是对分组后结果进行限定，如果不满足，就不会本查询出来
• where 后不能跟聚合函数，having后可以跟聚合函数