MySQL

了解数据库

优点

– 其他 : 临时存储

IO流:读写流到磁盘上. 但是存储大文件时,费时

– 数据库

• 永久存储
• 支持事务
• 支持批量操作
• 支持行级锁

分类

– 关系型DB

MySQL(中小型企业)(免费)(常用)
Oracle(大型公司,收费)
MariaDB
SQLServer(中小型企业)(和.net平台兼容性较好)
SQLLite

– 非关系型DB:NO-SQL

常见redis—分布式缓存使用

安装

.msi : windows安装版
.zip : windows压缩版
tar.gz : Linux压缩版

---

下载网址:dev.mysql.com/downloads

---

配置环境变量

交互

安装mysql-server数据库服务器端,
客户端通过SQL语句与服务器端进行交互

---

三种方式

• cmd

mysql -uroot -p
root
-- 关闭服务 : 如果修改mysql的my.ini配置文件 : 修改字符集:mysql5.7(gbk-->utf8)
net stop mysql57

-- 开启服务
net start mysql57

• MySQL命令行
• 图形化工具

工具SQLyog

先配置

-- cmd创建的数据只支持gbk,所以需要在SQLyog(支持gbk与utf8)打开前,
-- 先配置:修改my.ini配置文件
net stop mysql57  -- 首先终止mysql服务
default-character-set=utf8mb4  -- 添加在[client]下
default-character-set=utf8mb4  --添加在[mysql]下
-- 下面的语句都加在[mysqld]下面
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

注释

-- 单行注释
/*
多行注释
*/
# 标记注释

备份与还原

• 命令行的备份与还原

-- 备份a.sql文件到c:\sql\b.sql
mysqldump  -uroot  -proot  a.sql  c:\sql\b.sql;          --备份

-- 还原
-- cmd,先登录mysql
-- 先删除之间的库
-- 新建库,使用库
source C:\sql\b.sql

• 图形化操作

右键export[import] SQLquery

语句

DDL (构建表与库框架)

对库

• 查询mysql服务器默认数据库

show databases;  --查询有哪些数据库 
-- 对系统数据库的解释
information_schema  -- mysql相关默认配置库
mysql  -- mysql库里的user表(管理员,普通用户)
performance_schema  -- mysql相关性能配置库
sys  -- mysql引擎相关的表参数,一些测试库

• 展示指定库信息

show create database 库名;

• 修改库默认的字符集

show variables like '%characters%';   -- 查询mysql的variables变量中包含characters
alter database 库名 DEFAULT CHARACTER SET 字符集名;

• 建与删数据库

create database 库名;
create database if not exits 库名;

drop database 库名;
drop database if exits 库名;

对表

• 创建表

use 数据库名;
create table student(
    id  int  primary  key  auto_increment  '编号' ,               -- '别称'
    name varchar(10)  '姓名' ,                            --'别称'
    age int            -- 最后一个不要标点符号
);

• 约束 :ALTER TABLE stu MODIFY (非空 与 默认 )
  [[ADD] [DROP] (唯一 与 主键 约束) ]…

• 默认约束 : null赋为默认值,但是如果手动输入null,默认值就失效

-- 创建表时定义约束 
age  int  default  20
-- 语句添加约束
ALTER  TABLE  stu  MODIFY  age  int  default  30;

• 非空约束

-- 创建表时定义
name  varchar(10)  not null
-- 语句添加非空
ALTER  TABLE  stu  MODIFY  age  not null;

• 唯一约束

-- 创建表时声明唯一
name  varchar(10)  UNIQUE
-- 删除唯一约束
ALTER  TABLE  stu  DROP  INDEX  name;
-- 添加唯一约束
ALTER  TABLE  stu  ADD[CONSTRAINT  my_name]  UNIQUE(name);

主键约束 : 非空 + 唯一
一般作用于非业务字段(id) , 与AUTO_INCREMENT混搭使用 ,
AUTO_INCREMENT(默认0开始,如果插入10,系统下次从11开始) (对modify无效) (SELECT LAST_INSERT_id)
– 删除主键 (只删除唯一,没有删除非空)(非空需要modify)

-- 定义时添加主键
id  int  primary  key  auto_increment  
-- 删除主键
ALTER  TABLE  stu  DROP  PRIMARY KEY;
-- 添加主键
ALTER  TABLE  stu  ADD  [CONSTRAINT  约束名]  PRIMARY  KEY(id);

外键

-- 定义表时定义外键
create  table  employee  (
name  varchar(10)  ,
age  int  ,
dept_id  int  ,
[constraint  dept_emp_fk]
foreign key(dept_id)
references dept(id)
on  update  cascade
on  delete  cascade
)
-- 语句添加外键
alter  table  emp  add  [constraint  dept_emp_fk]  foreign  key  dept_id  references  dept(id)    on  update  cascade  on  delete  cascade;
-- 语句删除外键
alter  table  emp  drop  foreign  key  dept_id;

• 查表结构

desc student  ;

• 重命名表

alter  table  student  rename  to  stu;

• 复制一模一样的表

create  table stu2  like  stu  ;

• 删除表

drop  table  sut2 ;
drop  table  if  exists  st  ;

• 修改表 ALTER TABLE stu …

alter  table  stu  CHANGE  old_name  new_name  varchar(10)  ;         -- 修改字段名称
alter  table  stu  MODIFY  name  varchar(11)  ;                    -- 修改字段类型
alter  table  stu  ADD  salary  int  ;                        -- 添加新字段
alter  table stu  DROP  salary  ;                            -- 删除字段

DML (操作表数据)

• INSERT INTO stu VALUES …

-- 给学生的部分属性赋值
INSERT INTO student (name,  age)  
VALUES
('张三',  2),                            -- varchar赋值时需要带单引号或者双引号
("李四",  3);                           -- 未指明赋值的默认为null

• UPDATE stu SET …

--  将id为7的学生姓名改为张麻子,性别改为男
UPDATE  stu
SET  name = '张麻子'  ,  gender = '男'
WHERE  id  =  7  ;

• DELETE FROM stu …

-- 删除名字为王宝强的学生
DELETE  FROM  stu
WHERE  name  = '王宝强'  ;

delete from与 truncate table 的区别
前者: 只是删除表的数据, 表的结构还在(约束,自增等等)------------显示被影响的条数数据
后者: 干掉全表,新建一张一样名字的空表-------------------不显示影响记录

DQL

单表

• 基本查询

-- 去重
select  distinct  grade  from  student;

-- 查询每个人的总成绩
select  name  '名字',
(math  +  english  +  IFNULL(chinese,  0) )  '总成绩'
from  student;

• 条件查询

-- 不等于:<>,  >=,  <=,  !=,  between  值1  and  值2,  in(值1,  值2,  值3)
-- 年龄不等于20的学生
select  *  from  stu  where  age  <>  20;
-- 查询名字不为null的学生
select *  from  stu  where  name  is  not  null;
-- 查询名字第二个字是马的人
select  *  from  stu
where  name  like  '_马%'  ;

• 聚合函数

count(id )  :  只统计非null,  一般用于查询非业务字段:id
avg( math)  :  select  avg(math)  from  stu ;
max( age )  [min(age)]
sum(salary)

• 排序查询 : ASC(升序)(默认) DESC(降序)

-- 年龄>20 的学生,成绩降序排序,成绩一样的按照年龄升序排序
select *  from  stu
where  age > 20
order  by  grade [ASC]  , age  DESC;

• 分组查询 : 与having筛选一起使用

-- 按照性别分组,统计每个组的总人数,条件:数学成绩不大于70分的不参与分组, 筛选出总人数大于2的这一组
SELECT 
  sex '性别',
  COUNT(id)  '总人数' -- 别名
FROM
  student2 
WHERE math > 70 
GROUP BY sex 
HAVING  '总人数' > 2 ;

• 分页查询

select * from student 
limit 
开始的行数(从0开始)  : (当前页数(前端传递)-1)  * 每页的行数`,
每页的行数(一般固定)

-- 限制n条,默认从0开始
SELECT * FROM student LIMIT n;

多表

• 关系

一对一
一对多
多对多(多表查询)

• 查询

• 内连接查询
  • 隐式内连接

  -- 查询emp中员工所有信息,同时展现员工部门名称
  select e.*, d.name
  from employee e,department d
  where e.dept_id = d.id;
  

  • 显示内连接

  -- 查询员工表所有信息,同时展现员工部门名称
  select e.*, d.name
  from employee e inner join department d
  on e.dept_id = d.id;
  

• 外连接 : 展示左表(左外连接)[右表(右外连接)]全部信息 ,包括没有部门的员工
  • 左外连接

  -- 展示所有员工所有信息,包含员工部门名称
  select e.*,d.name
  from employee e
  left join department d
  on e.dept_id = d.id;
  

  • 右外连接

• 子查询 : 嵌套查询
  • where

  -- 查询工资大于平均工资的员工所有信息与部门名称
  SELECT e.*,d.name
  FROM employee e, department d
  WHERE e.dept_id = d.id   -- 先建立关系
  AND e.salary > (   
  SELECT AVG(salary)
  FROM employee
  );
  -- 查询年龄大于20岁的员工的所有信息与部门名称 : 虚表
  select t.* ,d.name
  from(
  select * from employee where age > 20) t , department d
  where t.dept_id = d.id ;
  

  • in

  -- 查询在2,3,4号部门的所有员工信息与部门名称
  SELEC e.*,d.name
  FROM employee e, department d
  WHERE e.dept_id = d.id
  AND d.id IN (
  SELECT d.id
  FROM department
  WHERE d.name = '测试部' OR d.name = '财务部'
  

  • 虚表

  -- 查询入职日期大于'2000-01-01'的员工所有信息和部门名称
  -- 日期大于的员工先是一张表,起个名字t
  SELECT t.*,d.name
   FROM (
   SELECT *
   FROM employee
   WHERE join_date > '2000-01-01'
   )t,department d
   WHERE 
   t.dept-id = d.id
  

三大范式

1NF

2NF

3NF

事务