MySQL基础

虚拟机 - 模拟器
VMWare
Virtual Box
Parallel Desktop

重量级虚拟机 - 轻量级容器

Docker -- MySQL服务器

1.安装Docker
yum -y install docker-io

2.启动Docker
systemctl start docker

3.下载MySQL官方镜像
docker pull mysql:5.7

4.查看镜像
docker images

5.创建容器运行MySQL

docker run -d -p 3306:3306 --name mysql57 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

数据库 - 数据的仓库 - database
关系型数据库 - 数据持久化 - 管理数据 - 方便的检索数据
理论基础：集合论、关系代数
表象：用二维表存储数据
行 - row - 记录
列 - column - 字段 - 数据类型
主键列 - primary key - 唯一标识一条记录
有自己的编程语言：结构化查询语言 - SQL

DB DBMS DBA DBS

关系型数据库的产品（数据库管理系统）：

1. Oracle
2. MySQL
   c / s应用模式 - Client / Server

---

IBM - DB2
微软 - SQL Server
PostgreSQL

---

Redis - 键值对数据库
MongoDB - 文档数据库
ElasticSearch / Solr - 搜索引擎

-- SQL: Structured Query Language
-- 1.DDL:Data Definition Language - 数据定义语言 - create / drop /alter
-- 2.DML:Data Manipulation Language - 数据操作语言 - insert / delete / update / select
-- 3.DCL:Data Control Language - 数据控制语言 - grant / revoke / commit / rollback

查看MySQL支持的数据类型

? data types
? int

-- 如果存在名为school的数据库就删除

drop database if exists school;

-- 创建school数据库并设置默认字符集为utf8

create database school default charset utf8;

-- 切换到school数据库的上下文环境

use school;

-- 如果存在名为tb_student的表就删除

drop table if exists tb_student;

-- 创建学生表tb_student

create table tb_student
(
stuid int not null comment '学号',
stuname varchar(31) not null comment '姓名',
stusex enum('男', '女')  default '男' comment '性别',
stubirth date comment '出生日期',
stuaddr varchar(255) comment '家庭住址',
primary key (stuid)
);

-- 修改tb_student表的表结构添加一个新列

alter table tb_student add column stutel char(11) comment '联系电话';

-- 修改tb_student表的表结构删除一个列

alter table tb_student drop column stutel;

-- 修改tb_student表的表结构修改一个列

alter table tb_student change column stuname stuname varchar(20) 
not null comment '姓名';

-- 如果存在名为tb_course的表就删除

drop table if exists tb_course;

-- 创建课程表tb_course

create table tb_course
(
cid int not null comment '课程编号',
cname varchar(31) not null comment '课程名称',
credit tinyint unsigned not null comment '学分',
cdate timestamp default now() comment '开课日期',
primary key (cid)
);

-- 录入完整的学生数据

insert into tb_student values 
(1001, '骆昊', '男', '1980-11-28', '四川成都');

-- 指定列录入对应的数据

insert into tb_student (stuid, stuname, stubirth) 
values (1002, '王大锤', '1990-2-2');

-- 一次性插入多条数据

insert into tb_student 
    (stuid, stuname, stusex) 
values 
    (1003, '白元芳', default),
    (1004, '白洁', '女'),
    (1005, '狄仁杰', '男'),
    (1006, '武则天', '女');

-- 录入课程数据

insert into tb_course
    (cid, cname, credit)
values
    (1111, 'Python程序设计', 3),
    (2222, 'Web前端开发', 2),
    (3333, 'Linux系统入门', 2),
    (4444, '数据库理论和实践', 3),
    (5555, '企业应用架构', 2);

-- 删除记录

delete from tb_student where stuid=3002;
delete from tb_student where stuid>1005;

-- 删全表

truncate table tb_student;

-- 更新记录

update tb_student set stubirth='2000-1-1' where stuid='1003';
update tb_student set stubirth='1996-1-1',stuaddr='成都' where stuid=1004;

update tb_course set credit=4 where cid=333;
update tb_course set credit=3 where cid in (111, 333);

-- 查询

select * from tb_student;
select * from tb_course;

-- 投影

select stuid, stuname, stusex from tb_student;

-- 别名

select stuid as 学号, stuname as 姓名, stusex as 性别 from tb_student;

-- 筛选

select stuid, stuname, stusex from tb_student where stusex='男';
select stuname, stubirth from tb_student where stubirth between '2000-1-1' and '2010-1-1' and stusex='男';

-- 判断空值（null）不能用=或者<>，需要使用is或者not null

select * from tb_student where stuaddr is null;

insert into tb_student values (1006, '李白', '男', '2008-8-8', '北京');
insert into tb_student values (1007, '李白太', '女', '2008-8-8', '北京');

-- 模糊查询

select * from tb_student where stuname like '魔%';
select * from tb_student where stuname like '魔__';
select * from tb_student where stuname like '_白';
select * from tb_student where stuname like '_白_';

-- 排序

select * from tb_student order by stuid desc;
select * from tb_student order by stuname asc;
select * from tb_student order by stusex desc, stuid desc;

select * from tb_student order by stuid desc limit 3;
select * from tb_student order by stuid desc limit 3 offset 6;
select * from tb_student order by stuid desc limit 6,3;

select distinct substr(stuname, 1, 1) from tb_student;
select substr(stuname, 1, length(stuname)) from tb_student;
select concat(stuname, stusex) from tb_student;

-- 聚合函数（统计功能）

select max(credit) from tb_course;
select min(credit) from tb_course;

select max(stubirth) from tb_student;

select sum(credit) from tb_course;

select avg(credit) from tb_course;

select count(stuid) from tb_student;
select count(cid) from tb_course;

-- 分组查询

select stusex, count(stuid) from tb_student group by stusex;

-- 创建用户

create user 'wangdachui'@'%' identified by '123';

-- 授权操作

grant all privileges on *.* to 'wangdachui'@'%' with grant option;
revoke all privileges on *.* from 'wangdachui'@'%';

grant select on school.* to 'wangdachui'@'%';
grant delete, update, insert on school.* to 'wangdachui'@'%';
grant create, drop, alter on school.* to 'wangdachui'@'%';

-- 表与表之间的关系
-- 一对一 （人和身份证、用户和购物车）
-- 一对多 / 多对一 （人和银行卡、用户和订单）
-- 多对多 （学生和课程、订单和商品）

-- 外键：外来的主键（其他表的主键）