MySQL数据库学习

基础

• MySQL服务启动
  1. 手动。
  2. cmd–> services.msc 打开服务的窗口
  3. 使用管理员打开cmd
  * net start mysql : 启动mysql的服务
  * net stop mysql:关闭mysql服务
• MySQL登录
  1. mysql -uroot -p密码
  2. mysql -hip -uroot -p连接目标的密码
  3. mysql --host=ip --user=root --password=连接目标的密码
• 3 种注释
  * 单行注释: – 注释内容 或 # 注释内容(mysql 特有)
  * 多行注释: /* 注释 */
• 数据库的备份与还原
  • 备份： mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
  • 还原：
    1. 登录数据库
    2. 创建数据库
    3. 使用数据库
    4. 执行文件。source 文件路径

mysql数据的基本类型

参考学习：
https://blog.youkuaiyun.com/superit401/article/details/51547552

数据库的CRUD

1. C(Create):创建
2. R(Retrieve):查询
3. U(Update):修改
4. D(Delete):删除

• 创建数据库
  create database <name>; 创建名称为name的数据库
  create database if not exists <name>; 创建数据库，判断不存在，再创建
  create database if not exists db1 character set gbk; 创建数据库db1，并指定字符集

• 查询
  show databases; // 查询所有数据库的名称
  show create database <name>; – 查询某个数据库的字符集:查询某个数据库的创建语句

• 修改
  alter database <mysql_name> character set 字符集名称; – 修改数据库的字符集

• 删除
  drop database <name>; 删除数据库
  drop database if exists <name>; 判断数据库是否存在，存在再删除

• 使用数据库
  select database(); 查询当前正在使用的数据库名称
  use <name>; 使用数据库

操作表

• 创建表

 create table student(
    -> id int primary key auto_increment,
    -> name varchar(32),
    -> age int,
    -> score double(4,1),
    -> birthday date,
    -> insert_time timestamp
    );

• 复制表
  create table stu_new like student; // 复制student表结构到新表stu_new
  create table stu_new select * from stu; – 复制student表结构和数据到新表stu_new
• 删除表
  drop table <name>;
  drop table if exists <name>;
• 修改表

1. 修改表名
   alter table 表名 rename to 新的表名;
2. 修改表的字符集
   alter table 表名 character set 字符集名称;
3. 添加一列
   alter table 表名 add 列名 数据类型;
4. 修改列名称 类型
   alter table 表名 change 列名 新列名 新数据类型;
   alter table 表名 modify 列名 新数据类型;
5. 删除列
   alter table 表名 drop 列名;

• 查询表
  show tables; // 查询某个数据库中所有的表名称
  desc 表名; // 查询表结构
  show create table 表名; // 查询表的创建语句

DML：操作表中的数据

1. 添加数据：
   insert into 表名(列名1,列名2,…列名n) values(值1,值2,…值n);
   insert into 表名 values(值1,值2,…值n);
2. 删除数据
   delete from 表名 where 条件;
   注：如果不加条件，则删除表中所有记录。
   2.2 如果要删除所有记录
   delete from 表名; – 不推荐使用。有多少条记录就会执行多少次删除操作
   truncate table 表名; – 推荐使用，效率更高 先删除表，然后再创建一张一样的表。
3. 修改数据
   update 表名 set 列名1=值1,列名2=值2 where 条件;
   注：如果不加任何条件，则会将表中所有记录全部修改。

DQL：查询表中的记录

select * from 表名;

1. 语法：
   select 字段列表
   from 表名列表
   where 条件列表
   group by 分组字段
   having 分组之后的条件
   order by 排序
   limit 分页限定
2. 基础查询
   1. 多个字段的查询
      select 字段名1，字段名2… from 表名；
   2. 去除重复：
      * select distinct address from stu;
   3. ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null
      * 表达式1：哪个字段需要判断是否为null
      * 表达式2：如果表达式1字段为null后的替换值。
   4. 起别名：
      as：as也可以省略
3. 运算符，条件查询

比较运算符	说明
>、<、<=、>=、=、<>	<>在 SQL 中表示不等于，在 mysql 中也可以使用!=，mysql中没有==
BETWEEN…AND	在一个范围之内，如：between 100 and 200 相当于条件在 100 到 200 之间，包头又包尾
IN(集合)	集合表示多个值，使用逗号分隔
LIKE	‘张%’ 模糊查询 姓名中包含’张’，‘张_’ 姓名为两个字 _匹配一个字符
IS NULL	查询某一列为 NULL 的值，注：不能写=NULL

• 具体操作：
  – 查询math分数大于80分的学生
  select * from student3 where math>80;
  – 查询english分数小于或等于80分的学生
  select * from student3 where english <=80;
  – 查询age等于20岁的学
  select * from student3 where age = 20;
  – 查询age不等于20岁的学生，注：不等于有两种写法
  select * from student3 where age <> 20;
  select * from student3 where age != 20;

逻辑运算符	说明
and 或 &&	与，SQL 中建议使用前者，后者并不通用。
or 或 ||	或
not 或 !	非

4. 排序查询

SELECT * FROM student ORDER BY math; -- 默认升序 asc
SELECT * FROM student ORDER BY math DESC; -- 降序
-- 如果由多个排序条件，当前边的条件值一样时，才会判断第二条件
SELECT * FROM student ORDER BY math,english;  

5. 聚合函数：将一列数据作为一个整体，进行纵向的计算。

1. count：计算个数
   1.1. 一般选择非空的列：主键
   1.2. count(*)
2. max：计算最大值
3. min：计算最小值
4. sum：计算和
5. avg：计算平均值

6. 分组查询
   group by 分组字段；

-- 1.按照性别分组。分别查询男、女同学数学成绩的平均分
SELECT sex , AVG(math) FROM student GROUP BY sex;
-- 2.按照性别分组。分别查询男、女同学的平均分,人数
SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;
-- 3.按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
-- 4.按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

where 和 having 的区别？

1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。

7. 分页查询

SELECT * FROM student LIMIT 0,3; -- 第1页 从0索引查询3条记录
SELECT * FROM student LIMIT 3,3; -- 第2页
SELECT * FROM student LIMIT 6,3; -- 第3页

公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数
注：limit 是一个MySQL"方言"

约束

• 概念： 对表中的数据进行限定，保证数据的正确性、有效性和完整性。
• 分类：
  1. 主键约束：primary key
  2. 非空约束：not null
  3. 唯一约束：unique
  4. 外键约束：foreign key
• 非空约束：not null，值不能为null

-- 1. 创建表时添加约束
	CREATE TABLE stu(
		id INT,
		NAME VARCHAR(20) NOT NULL -- name为非空
	);
-- 2. 创建表完后，添加非空约束
	ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
-- 3. 删除name的非空约束
	ALTER TABLE stu MODIFY NAME VARCHAR(20);

• 唯一约束：unique，值不能重复

-- 1. 创建表时，添加唯一约束
CREATE TABLE stu(
	id INT,
	phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
);
-- 2. 删除唯一约束
	ALTER TABLE stu DROP INDEX phone_number;
-- 3. 在创建表后，添加唯一约束
	ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

注意mysql中，唯一约束限定的列的值可以有多个null

• 主键约束：primary key和自动增长auto_increment

1. 注意：
   1. 含义：非空且唯一
   2. 一张表只能有一个字段为主键
   3. 主键就是表中记录的唯一标识

-- 1. 在创建表时，添加主键约束
	create table stu(
		id int primary key,-- 给id添加主键约束
		name varchar(20)
	);
-- 2. 删除主键
	ALTER TABLE stu DROP PRIMARY KEY;
-- 3. 创建完表后，添加主键
	ALTER TABLE stu MODIFY id INT PRIMARY KEY;
-- 4.1 自动增长
	create table stu(
		id int primary key auto_increment,-- 给id添加主键约束
		name varchar(20)
	);
-- 4.2. 删除自动增长
	ALTER TABLE stu MODIFY id INT;
-- 4.3. 添加自动增长
	ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

• 外键约束：foreign key,让表与表产生关系，从而保证数据的正确性。

-- 1. 在创建表时，可以添加外键
	-- * 语法：
create table 表名(
	....
	外键列
	constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
-- 2. 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

-- 3. 创建表之后，添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
-- 4. 级联操作:在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作 
	-- 1. 添加级联操作
语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
	-- 2. 分类：
		1. 级联更新：ON UPDATE CASCADE 
		2. 级联删除：ON DELETE CASCADE 

例：

-- 创建部门表(id,dep_name,dep_location) -- 一方，主表 
CREATE TABLE department(  
	id INT PRIMARY KEY AUTO_INCREMENT,  
	dep_name VARCHAR(20),  
	dep_location VARCHAR(20) 
); 
-- 创建员工表(id,name,age,dep_id) -- 多方，从表 
CREATE TABLE employee(  
	id INT PRIMARY KEY AUTO_INCREMENT,  
	NAME VARCHAR(20),
	age INT,
	dep_id INT,  -- 部门id，外键对应主表的主键 
	CONSTRAINT emp_dept FOREIGN KEY (dep_id) REFERENCES department(id)
);

存储过程

创建存储过程

DELIMITER // 
create procedure del_data()
BEGIN
	DELETE FROM table WHERE created_on < DATE_SUB(CURDATE(),INTERVAL 3 DAY);
END//
DELIMITER ;

DELIMITER是分割符的意思，声明存储过程前将"//"声明为分隔符，这样存储过程中的“;”才不会被当作分隔符处理。声明结束后再还原分隔符。
存储过程也可以带参数，存储过程名(参数)
在声明存储过程前要先用use database_name切换到想要应用的数据库，否则存储过程会应用到默认数据库中

查看存储过程

select * from mysql.proc where db=’数据库名’;

使用存储过程

call del_data()

一次只能删除一个存储过程或函数，需 ALTER ROUTINE 权限

Drop procedure name；

event事件

参考学习：
https://www.cnblogs.com/lonecloud/p/11044182.html

查看事件开关有没有打开

show variables like ‘%event_scheduler%’;

开启事件调度器

SET GLOBAL event_scheduler = ON;

创建event事件

create event delete_log
on schedule every 10 second
do call clean_data();

说明：

1. delete_log：事件名称
2. clean_data(): 定义好的存储过程

###############################################################
注(有待验证)：
SQL语句书写顺序select、form、where、group by、having、select、order by、limit SQL语句执行顺序from、where、group by、having、select、order by、limit