MySQL|数据库简介、基本的增删改查操作

1.数据库简介

数据库(DataBase,简称DB)

概念：长期存放在计算机内，有组织，可共享的大量数据的集合，是一个数据"仓库"

作用：保存，并能安全管理数据(如:增删改查等)，减少冗余…

数据库总览 :

• 关系型数据库 (SQL)
  MySQL , Oracle , SQL Server , SQLite , DB2 , …
  关系型数据库通过外键关联来建立表与表之间的关系
• 非关系型数据库(NOSQL)
  Redis , MongoDB , …
  非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

2.软件的安装与卸载

2.1 Mysql 5.5.27 的安装与卸载

Mysql的版本：mysql-5.5.27-winx64

MySql软件在安装之前，要确保之前的版本卸载干净，否则非常容易安装失败。

2.1.1 卸载步骤：

• 关闭mysql服务
  cmd–>services.msc,打开服务面板
  
• 在卸载程序中卸载
  
• 删除C盘中的残留文件
  寻找残留文件地址的方法：找到mysql的安装目录中my.ini文件，打开后，找到datadir对应的目录，删除c盘残留的整个文件夹。

2.1.2 与数据库使用相关的命令：

登陆数据库：cmd–>mysql -uroot -p密码

以管理员身份运行cmd：（右击cmd）
启动服务： net start mysql
停止服务： net stop mysql
删除服务 sc delete mysql

2.1.3 修改mysql的密码步骤：

1. 停止mysql服务 net stop mysql
2. 在cmd下 输入 mysqld --skip-grant-tables 启动服务器 光标不动 （不要关闭该窗口）
3. 新打开cmd 输入mysql -u root -p 旧密码
   use mysql;
   update user set password=password(‘123456’) WHERE User=‘root’;
4. 关闭两个cmd窗口 在任务管理器结束mysqld 进程
5. 在服务管理页面 重启mysql 服务 net start mysql

2.2 Mysql 5.7.19 的安装

1. 按照2.1.1的步骤进行卸载

2. 安装Mysql需要的vc++环境
   链接: https://pan.baidu.com/s/1QLy-ocUfqlys-BZs9ZtWwA
   提取码: i4vr
   直接运行exe文件即可

3. 载后得到zip压缩包
   链接: https://pan.baidu.com/s/1fuPdDBsfE5Nsl9LJYU5mxw
   提取码: akiz

4. 解压到自己想要安装到的目录，本人解压到的是D:\environment\mysql-5.7.19-winx64

5. 添加环境变量
   选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹
   
   

6. 在D:\Environment\mysql-5.7.19下新建 my.ini 文件

7. 编辑 my.ini 文件 ,注意替换路径位置
   basedir设置为自己MYSQL的安装目录
   datadir设置为MYSQL的数据目录

[mysqld]
basedir=D:\environment\mysql-5.7.19-winx64
datadir=D:\environment\mysql-5.7.19-winx64\data
port=3306
skip-grant-tables

8. 启动管理员模式下的CMD，并将路径切换至mysql下的bin目录cd/d D:\environment\mysql-5.7.19-winx64\bin，然后输入mysqld-install安装mysql
9. 再输入mysqld --initialize-insecure --user=mysql 初始化数据文件
10. 然后再次启动mysqlnet start mysql ，输入mysql，然后用命令 mysql –u root –p 进入mysql管理界面（密码可为空）
    
11. 更改root密码update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
    
12. flush privileges 刷新权限
13. 修改 my.ini文件：删除最后一句skip-grant-tables
14. 重启mysql即可正常使用net stop mysql，net start mysql

2.3 SQLyog

这是一个数据库可视化软件，比在cmd中操作数据库方便与便捷。

此软件的安装很简单，注意一下在安装成功后与数据库的连接即可。

3 SQL：结构化查询语言

概念：通过sql语言可以操作所有的关系型数据库。每种数据库之间会存在差异，称为 “方言”.

sql分类

名称	解释	命令
DDL（数据定义语言）	定义和管理数据对象，如数据库，数据表等	CREATE、DROP、ALTER
DML（数据操作语言）	用于操作数据库对象中所包含的数据	INSERT、UPDATE、DELETE
DQL（数据查询语言）	用于查询数据库数据	SELECT
DCL（数据控制语言）	用来管理数据库的语言，包括管理权限及数据更改	GRANT、COMMIT、ROLLBACK

3.1 DDL：操作数据库和表

操作数据库

• 创建：create database 数据库名称
• 删除：drop database 数据库名称
• 修改：ALTER DATABASE mydb1 CHARACTER SET 要修改的字符集名称
• 查询：
  show databases; 查询所有数据库的名称
  show create database 数据库名称; 查询创建数据库语句

操作表

列的数据类型：

类型	说明	举例
int	整型	id int
double	浮点型 double(5,2) 表示最多5位，其中必须有2位小数，即最大值为999.99	money double(5,2)
char	固定长度字符串类型	name char(10)“张三”
varchar	可变长度字符串类型	name varchar(10) “张三”
text	字符串类型	存大格式的文本 比如存个小说 一般不用
blob	字节类型	存字节类型的数据 比如电影字节 图片字节 但是一般不会把字节数据存到数据库当中
date	日期类型	格式为：yyyy-MM-dd
time	时间类型	格式为：hh:mm:ss
datetime	日期时间类型	yyyy-MM-dd hh:mm:ss
timestamp	时间戳类型 如果该类型的字段不给赋值，则默认当前时间	yyyy-MM-dd hh:mm:ss

• 创建

语法：
create table 表名(
						列名1 类型1,
						列名2 类型2,
						...
						列名n 类型n
					
					);
					
举例：
create table student(
						sname varchar(20),
						age int,
						gender varchar(6)
					);

• 删除：drop table 表名;

• 修改
  
  1.添加列
  给stu表添加classname列：
  ALTER TABLE stu ADD (classname varchar(100));
  
  2.修改列类型
  修改stu表的gender列类型为CHAR(2)：
  ALTER TABLE stu MODIFY gender CHAR(2);
  
  3.修改列名
  修改stu表的gender列名为sex:
  ALTER TABLE stu change gender sex CHAR(2);
  
  4.删除列
  删除stu表的classname列：
  ALTER TABLE stu DROP classname;
  
  5.修改表名称
  修改stu表名称为student:
  ALTER TABLE stu RENAME TO student;

• 查询

1. show tables; 查询该数据库下所有表名称
2. desc 表名; 查询表结构

注意：操作表之前，应该先选择一个数据库使用：use 数据库名称;

3.2 DML：操作表中的数据

• 增
  语法：insert into 表名(列名1,列名2,…) values(值1,值2,…);
  
  举例：
  insert into stu(sname,age) values(“lisi”,23);
  insert into stu(sname,age,sex) values(“zhangsan”,23,‘1’);
  insert into stu values(“ww”,23,‘2’);
  
  注意：如果表的所有列都添加值，则列名可以省略

• 删
  语法1：delete from 表名 [where 条件];
  语法2：truncate table 表名;删除所有记录
  
  举例：delete from stu where name = ‘lisi’
  
  注意：先删除表，再创建表

• 改
  语法： update 表名 set 列名1 = 值1 , 列名2 = 值2… [where 条件]
  举例：update stu set sex = “1” where name = “lisi”;

3.3 DQL：查询表中的数据

• 语法

			SELECT 
				selection_list /*要查询的列名称*/
			FROM 
				table_list /*要查询的表名称*/
			WHERE 
				condition /*行条件*/
			GROUP BY 
				grouping_columns /*对结果分组*/
			HAVING 
				condition /*分组后的行条件*/
			ORDER BY 
				sorting_columns /*对结果分组*/
			LIMIT 
				offset_start, row_count /*结果限定*/

• 条件查询：where 子句

			=、!=、<>(不等于)、<、<=、>、>=；
			BETWEEN…AND；  在什么范围之间
			IN(set)；
			IS NULL；为空
			IS NOT　NULL　不为空
			AND； 并且
			OR；   或者
			NOT；非

• 模糊查询：like
  _：匹配单个任意字符
  %：匹配多个任意字符

查询姓名是3个任意字符组成：
select  * from student sname like='___';
查询第二个字符是m的学生：
select * from student where sanme like '_m%';
查询名字中包含m的学生：
select * from student where sname like '%m%';
查询名字是a开头的学生：
select * from student where sname like 'a%';
查询名字是b结尾的学生：
select * from student wher like '%b';

• 字段控制

修改字段的别名：AS (可以省略)

1.给字段起别名
select sname as 姓名, sage 年龄 from student;

2.给运算字段起别名：
select sname as 姓名, (工资+奖金) as 总收入 from student;

3.给表起别名：给每一张表起一个别名。简化书写
select stu.sname, stu.sage from student as stu;

字段运算

 - null参与的运算，结果都为null  
如：工资是100， 奖金是null 。让这两个字段运算，其结果为null。

 - ifnull（参数1，参数2） 参数1：字段名称   参数2：null的替换值
   注意：ifnull 是mysql的方言
如：所有如果奖金字段为null 一般我们把奖金替换为0
select sname,(工资+ifnull(奖金,0)) as 总收入from student;
						
 - DISTINCT 去除重复记录   
如：查询工资是3000的员工，出现了多条工资为3000 的记录，如果只想展示一条3000的记录 ，可以用 distinct 去除重复记录
select distinct 工资 from student;

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

1.按工资从小到大排
select * from student order by 工资 asc;
2.按工资从大到小排
select * from student order by 工资 desc; 
3.如果出现多条工资一样的 那我们可以指定第二排序条件 
select * from student order by 工资 desc,奖金desc;

• 聚合函数
  聚合函数是用来做纵向运算的函数

1. COUNT()：统计指定列不为NULL的记录行数
2. MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
3. MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
4. SUM()：计算指定列的数值和，如果指 定列类型不是数值类型，那么计算结果为0
5. AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

• 分组查询：group by
  group by一般配合聚合函数使用，查出的数据才有意义。
  

1.某公司有三个部门，求每个部门的平均工资 。
分析：展示出来的数据应该有三条

select 部门编号,AVG(工资) from student group by 部门编号；

2.查询 每个部门的部门编号以及每个部门工资大于1500的人数

select 部门编号, count(*) from student where 工资>1500 group by 部门编号;

3.查询部门平均工资大于2000的部门，查出这些部门的编号与人数。

SELECT deptno AS 部门号, COUNT(*) AS 部门人数,AVG(sal)AS avgsal FROM emp GROUP BY deptno HAVING avgsal>2000 ; 

where和having的区别：
where：在分组之前对条件进行限定。不满足条件，就不会参与分组
having：在分组之后，对结果集的筛选

4.查询 各个部门 员工工资大于1500 的平均工资 并且平均工资 大于2000的部门
分析：
1）首先查询各个部门
按部门编号分组，分组前的条件限定是：员工工资大于1500的才参与分组
2）计算出平均工资
3）对结果再进行筛选：筛选出 平均工资 大于2000的部门

select 部门编号,avg(工资) from student group by 部门编号 where avg(工资)>1500 having  avg(工资)>2000;

• 分页查询
  MySQl使用limit作为分页语句；
  oracle使用rownum作为分页语句；
  sqlserver使用top作为分页语句。

select * from student limit 0,5 ;  这是第一页 5 条记录
select * from student limit 5,5 ; 这是第二页 5条记录
select * from student limit 10,5 这是第三页 5条记录