Mysql基础篇

Mysql概述

数据库相关概念:

数据库(DataBase) DB:存储数据的仓库,数据是有组织结构的进行存储

数据库管理系统:操作管理数据库的软件(DataBase Management System(DBMS)) 

关系型数据库管理系统也成为RDBMS

SQL:操作关系型数据库的编程语言,定义了一套操作关系型数据库统一标准

主流的一些关系型数据库管理系统

Mysql数据库的下载与安装

MySQL提供了社区版本(Mysql Community Server)免费版本和商业版(Mysql Enterprise Edition)

www.mysql.com

windows安装

卸载Mysql(如已安装过)

卸载mysql相关组件

我经常把他们放在一起  

在控制面板里卸载mysql相关组件

默认情况下安装在 数据存放目录是在 C:\ProgramData\MySQL，直接将该文件夹删除。

安装完成之后,默认自带的4个数据库

因为我没有删除数据库 Data  因此 原先的表都是保留着的

Mysqk服务的启动与关闭

安装的时候我们设置了mysql服务名为mysql80

net stop mysql80   需要用管理员权限

nte start mysql80  需要用管理员权限

mysql连接

需要配置环境变量

这样mysql命令就可以使用了

mysql -uroot -p回车输入密码

完整命令

mysql -h xx.xx.xx.x -P 端口  -u 用户  -p后面不要用空格   不然会认为密码带空格

SQL

SQL的通用语法

SQL的分类

DDL

数据定义语言,用来定义数据库,数据库表,字段

DDL数据库操作

create database if not exists test1 default charset utf8mb4 collate utf8mb4_general_ci

如果你不指定数据库字符集和排序

create database test2

DDL表操作

DDL表操作(数据类型及案例)

数值类型

字符类型

日期时间类型

create table emp (
    id int COMMENT '编号',
    workno varchar(10) COMMENT '工号',
    name varchar(10) COMMENT '姓名',
    gender char(1) COMMENT '性别',
    age tinyint unsigned COMMENT '年龄',
    idcard char(18) COMMENT '身份证号',
    entrydate date COMMENT '入职时间'
) COMMENT '员工表';
 

DDL表操作(修改删除字段) 

默认添加到最后

alter table emp add nickname varchar(20) cooment '昵称';

desc emp;查看表结构

alter table emp change nickname username varchar(30) comment '用户名';

alter table emp drop username;

alter table emp rename to employee;

在某个字段之后添加字段,但是并不支持在某个字段之前添加字段

alter table emp add username2 varchar(20) comment '用户名2' after id;

DDL操作总结

DML

数据操作语言,用来对数据库表中的数据进行增删改

DML-插入(新增)INSERT

DML-修改UPDATE

DML-删除DELETE

DML总结

DQL

SELECT关键字

数据查询语言,用来查询库表中的记录

单表

DQL-基础查询

DQL-条件查询

NULL的查询

不等于的查询

DQL-聚合查询

DQL-分组查询

DQL-排序查询

DQL-分页查询

DQL-SQL执行顺序

DQL-小结

DCL

数据控制语言,用来创建数据库用户,控制数据库访问权限

有哪些用户可以来连接数据库以及这些用户的访问权限

DCL-用户管理

这些用户只能访问一个库

DCL-用户管理

DCL-小结

Mysql内置函数

内置函数:是指一段可以直接被另一段程序调用的程序或代码

字符串函数

字符串函数练习

数值函数

数值函数练习

上面这样是存在BUG的  例如 生成的小数是0.0123456  乘以 1000000 是12345  

用填充的方式   至于左填充还是右填充随你自己

日期函数

是第一个时间减去第二个时间

日期函数练习

流程函数

内置函数小结

Mysql约束

外键约束(现在不常用)

约束小结

DQL-多表查询

多表关系

CREATE TABLE dept(
    id INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
    name VARCHAR(50) NOT NULL COMMENT '部门名称'
) COMMENT '部门表';

INSERT INTO dept(id, name) VALUES 
(1, '研发部'), 
(2, '市场部'), 
(3, '财务部'), 
(4, '销售部'), 
(5, '总经办'), 
(6, '人事部');

CREATE TABLE emp(
    id INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    age INT COMMENT '年龄',
    job VARCHAR(20) COMMENT '职位',
    salary INT COMMENT '薪资',
    entrydate DATE COMMENT '入职时间',
    managerid INT COMMENT '直属领导ID',
    dept_id INT COMMENT '部门ID'
) COMMENT '员工表';

INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id) VALUES
(1, '金鹏', 66, '总裁', 20000, '2000-01-01', NULL, 5),
(2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
(3, '杨道', 33, '开发', 8400, '2000-11-03', 2, 1),
(4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
(5, '常逢春', 43, '开发', 10500, '2004-09-07', 3, 1),
(6, '小昭', 19, '程序员/鼓励师', 6600, '2004-10-12', 2, 1),
(7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),
(8, '周宜若', 19, '会计', 48000, '2006-06-02', 7, 3),
(9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),
(10, '赵敏', 20, '市场总监', 12500, '2004-10-12', 1, 2),
(11, '鹿杖客', 56, '员工', 3750, '2006-10-03', 10, 2),
(12, '鹤笔翁', 19, '员工', 3750, '2007-05-09', 10, 2),
(13, '方东白', 19, '员工', 5500, '2009-02-12', 10, 2),
(14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),
(15, '前蓬舟', 38, '销售', 4600, '2004-10-12', 14, 4),
(16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),
(17, '陈友谅', 42, NULL, 2000, '2011-10-12', 1, NULL);
 

-- 创建学生表
create table student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';

-- 插入学生数据
insert into student values 
(null, '袁锦丝', '2000100101'),
(null, '谢渊', '2000100102'),
(null, '段天正', '2000100103'),
(null, '李一笑', '2000100104');

-- 创建课程表
create table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';

-- 插入课程数据
insert into course values 
(null, 'Java'),
(null, 'PHP'),
(null, 'MySQL'),
(null, 'Hadoop');

-- 创建学生课程中间表
create table student_course(
    id int auto_increment comment '主键' primary key,
    studentid int not null comment '学生ID',
    courseid int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course(id),
    constraint fk_studentid foreign key (studentid) references student(id)
) comment '学生课程中间表';

-- 插入学生选课数据
insert into student_course values 
(null, 1, 1),
(null, 1, 2),
(null, 1, 3),
(null, 2, 2),
(null, 2, 3),
(null, 3, 4);
 

 

create table tb_user(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '1: 男，2: 女',
    phone char(11) comment '手机号'
) comment '用户基本信息表';

create table tb_user_edu(
    id int auto_increment primary key comment '主键ID',
    degree varchar(20) comment '学历',
    major varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool varchar(50) comment '中学',
    university varchar(50) comment '大学',
    userid int unique comment '用户ID',
    constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';
 

insert into tb_user(id, name, age, gender, phone) values
(null, '黄涛', 45, '1', '18800001111'),
(null, '冰冰', 35, '2', '18800002222'),
(null, '玛云', 55, '1', '18800008888'),
(null, '李彦宏', 50, '1', '18800009999');

insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values
(null, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院', 1),
(null, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院', 2),
(null, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学', 3),
(null, '本科', '应用数学', '阳泉第一小学', '阳泉区第一中学', '清华大学', 4);
 

多表查询-笛卡尔积

select * from emp,dept;

select * from emp,dept where emp.dept_id=dept.id; 这个是sql92写法 使用的内连接 因此emp表中部门是null的数据被排除了

相当于sql99写法的内连接

SELECT * 
FROM emp
INNER JOIN dept ON emp.dept_id = dept.id;

可以省区INNER

SELECT * 
FROM emp
JOIN dept ON emp.dept_id = dept.id;
 

多表查询的分类

内连接

内连接返回的是表之间的交集部分

#内连接演示
#1.查询每个员工的姓名及关联的部门的名称(隐式内连接实现)
SELECT
    a.NAME username,
    b.NAME deptname 
FROM
    emp a,
    dept b 
WHERE
    a.dept_id = b.id;

#查询每一个员工的姓名及关联的部门的名称(显示内连接实现)  INNER可以省略
SELECT
    a.NAME username,
    b.NAME deptname 
FROM
    emp a
    INNER JOIN DEPT b ON a.dept_id = b.id;

外连接

#外连接演示
#1.查询emp表的所有数据和对应的部门信息(左外连接)  LEFT OUTER JOIN  OUTER可以不写
SELECT
    * 
FROM
    emp a
    LEFT JOIN dept b ON a.dept_id = b.id

#2.查询dept表的所有数据和对应的员工的信息(右外连接)RIGHT OUTER JOIN OUTER可以不写
SELECT
    * 
FROM
    emp a
    RIGHT JOIN dept b ON a.dept_id = b.id

自连接

联合查询-union,union all

可以是不同表,列名和列数必须相同  列明不同可以用别名

子查询

标量子查询(子查询结果为单个值-即一列一行)

列子查询(子查询结果为一列一行或一列多行)

行子查询(子查询结果为一行)

注意这里返回的是单行

表子查询(子查询结果为多行多列)

多表查询案例

新加一张薪资等级表

grade是薪资等级    losal是该等级最低薪资    hisal是该等级最高薪资

CREATE TABLE salgrade (
    grade INT,
    losal INT,
    hisal INT
) COMMENT = '薪资等级表';

insert into salgrade values (1, 0, 3000);
insert into salgrade values (2, 3001, 5000);
insert into salgrade values (3, 5001, 8000);
insert into salgrade values (4, 8001, 10000);
insert into salgrade values (5, 10001, 15000);
insert into salgrade values (6, 15001, 20000);
insert into salgrade values (7, 20001, 25000);
insert into salgrade values (8, 25001, 30000);

 

下面这个连接条件可以修改成between

多表联查小结

事务相关-TCL

事务控制语言，用于管理数据库中的事务，确保事务的原子性、一致性、隔离性和持久性（ACID）。

新建张表

CREATE TABLE account(
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(10) COMMENT '姓名',
    money INT COMMENT '余额'
) COMMENT '账户表';
INSERT INTO account(id, name, money) 
VALUES (NULL, '张三', 2000), 
       (NULL, '李四', 2000);

下面这种情况就会出现异常

要把这几步做为一个整体,要么全部成功  要么全部失败 

 

-- 查看当前的自动提交状态
SELECT @@autocommit;

-- 设置为手动提交事务
SET @@autocommit = 0;
 

 下面是显示启动一个事务

START TRANSACTION;
-- 或者
BEGIN TRANSACTION;
 

设置回滚点

SAVEPOINT savepoint_name;
 

撤销回滚点

RELEASE SAVEPOINT savepoint_name;
 

下面只是一个各种操作示例

START TRANSACTION;

-- 执行一些操作
UPDATE account SET money = money - 1000 WHERE name = '张三';

-- 设置保存点
SAVEPOINT savepoint1;

-- 执行更多操作
UPDATE account SET money = money + 1000 WHERE name = '李四';

-- 如果出现问题，回滚到保存点
ROLLBACK TO SAVEPOINT savepoint1;

-- 提交事务
COMMIT;
 

 

ACID四大特性

并发事务问题

事务隔离级别

1.读未提交

2.读已提交

3.可重复读

4.序列化读