mysql基础篇（知识点+实战）

学习路上的bug

于 2023-11-11 11:07:29 发布

阅读量92

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/sinat_59974786/article/details/134345640

版权

温馨提示：实战篇很重要，一些知识篇中没有提到的，实战篇有详细的讲解。

相信读完这篇文章后，你会有一定的收获。

读前总结

数据库和表操作：
CREATE DATABASE：创建新数据库。
USE：指定要使用的数据库。
CREATE TABLE：创建带有指定列和数据类型的新表。
SHOW TABLES：显示当前数据库中的所有表。
DROP TABLE：删除表。
DESC：显示表的结构。

表修改：
ALTER TABLE：修改表的结构（添加、修改或删除列）。
INSERT INTO：将数据添加到表中。
TRUNCATE TABLE：从表中删除所有记录。
UPDATE：修改表中的数据。
DELETE FROM：根据条件从表中删除记录。

数据查询：
SELECT：从一个或多个表中检索数据。
DISTINCT：从结果集中删除重复的值。
WHERE：基于指定条件过滤数据。
ORDER BY：对结果集进行排序。
LIMIT：限制返回的行数。
GROUP BY：根据指定列对行进行分组。
HAVING：根据聚合值过滤组。

用户和权限管理：
CREATE USER：创建新用户。
ALTER USER：修改用户信息。
DROP USER：删除用户。
GRANT：将权限分配给用户。
REVOKE：从用户中删除权限。

函数：
字符串函数：CONCAT、LOWER、UPPER、LPAD、RPAD、TRIM、SUBSTRING。
数值函数：CEIL、FLOOR、MOD、RAND、ROUND。
日期函数：CURDATE、CURTIME、NOW、YEAR、MONTH、DAY、DATE_ADD、DATEDIFF。

连接和子查询：
JOIN：基于相关列组合两个或多个表的行。
INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN：不同类型的连接。
子查询：在各种上下文中使用（标量、列、行、表子查询）。

事务：
START TRANSACTION / BEGIN：启动事务。
COMMIT：确认并应用事务期间所做的更改。
ROLLBACK：回滚事务期间所做的更改。

事务隔离级别：
READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE：不同级别的事务隔离。

并发问题：
提到了脏读、不可重复读和幻读等概念。

Union和Union All：
UNION和UNION ALL：将来自多个SELECT语句的结果集合并在一起。

一、知识篇

DDL数据库操作

DDL表操作查询

DLL表操作创建

DQL总结

DCL

函数

外键约束

约束总结

子查询

子查询总结

事务四大特性

事务并发问题

事务总结

二、实战篇

# 创建一个数据库
create database user;
# 使用这个数据库
use user;
# 创建一个表
create table userInformation(
    id int comment 'id',
    name varchar(20) comment 'name',
    age int comment 'age',
    gender char(1) comment 'gender',
    phoneNumber long comment 'phoneNumber',
    idCard long comment 'idCard',
    homeInformation varchar(50) comment 'homeinformation'
);
drop table userInformation;
# 显示数据库中所有的表
show tables ;
# 再次创建一个表
create table  password(
    id int comment 'id',
    password varchar(15) comment 'password'
);
# 删除表
drop table if exists password;

show tables ;
# 显示表的结构信息
desc userInformation;

# 修改表中的字段数据类型
alter table userInformation modify phoneNumber long;
alter table userInformation modify idCard long;
# 向表中添加字段
alter table userInformation add test varchar(10);
# 删除表中字段
alter table userInformation drop test;

desc userInformation;
# 给表中字段赋值
insert into userInformation
values
(1,'张三',18,'男',15178362156,340321200001007777,'安徽'),
(2,'李四',19,'男',15178362157,340321200001008888,'南京'),
(3,'王五',20,'男',15178362158,340321200001009999,'北京'),
(4,'李梅',21,'女',15178362159,340321200001009990,'西京'),
(5,'张欣',22,'女',15178362150,340321200001009991,'东京'),
(6,'王美',23,'女',15178362151,340321200001009992,'中京');

# 表数据格式化
truncate table userInformation;

# 修改表中字段的值
#提示：如果没有where判断条件的话，就是对表中的全字段进行修改
update userInformation set gender = '女'where id = 3;

# 删除表中一行字段的值
#提示：如果没有where判断条件的话，就是对表中的所有行的字段的值
delete from userInformation where id  = 2;
delete from userInformation;

# 查询表中的字段数据
select id,name,phoneNumber from userInformation;
select * from userInformation;
# 给查询的数据一个名字,方便分析数据
select homeInformation as '家庭地址' from userInformation;
# 去重
select distinct homeInformation as '家庭地址' from userInformation;


#使用条件判断查询数据
select *from userInformation where age<=20 and age>=18;
# 使用between-and注意事项:
# 小的数据放在前面,大的数据放在后面
# 包含左右的值,进行查询
select *from userInformation where age between 18 and 20;

# in()查询的时候会和括号中的数据进行匹配
select *from userInformation where age = 18 or age = 19;
select *from userInformation where age in(18,19);

#占位符   _    %
# 查询名字是两个字
select *from userInformation where name like'__';
# 查询最后一个数据是9的字段
select *from userInformation where idCard like'%9';

# 聚合函数 —— 进行纵向运算
# count 统计数量
# max 最大值
# min 最小值
# avg 平均值
# sum 求和
# 语法：select 聚合函数 from 作用表名
select *from userInformation;
select count(id) as'id的数量' from userInformation;
select avg(age) as'平均年龄' from userInformation ;
select avg(age) as'平均年龄' from userInformation where id = 1;


# 分组查询
# 语法：
# select 字段列表 from 表名 【where 条件】 group by 分组字段名 【having 分组后过滤条件】;
# where和having区别：
# 前者作用于分组前，后者作用于分组后
# 执行顺序：where > 聚合函数 > having
select gender, count(*) from userInformation group by gender;

# 排序查询
# 语法：
# select 字段列表 from 表名 order by  字段1 排序方式, 字段2 排序方式;
# 排序方式
# ASC 升序（默认）
# desc 降序
# 提示：字段1相同, 按照后序字段的排序方式进行排序
select * from userInformation order by age desc ;
select * from userInformation order by age;

# 分页查询
# 语法
# select 字段列表 from 表名 Limit 起始索引，查询记录数
# 提示：起始索引 = （页数 - 1）* 查询记录数
# 数据库不同，分页查询会不一样
# 起始索引为0，可以不写
select *from userInformation limit 3;
select *from userInformation limit 3,3;

#总结-DQL执行顺序
# from > where > group by > select > order by >  limit




# DCL-管理数据库用户,访问数据库权限

# 查询用户
# use 数据库名;
# select * from user;
# 创建用户
# create  user '用户名'@'主机名' identified by '密码';
# 修改用户密码
# alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
# 删除用户
# drop user '用户名'@'主机名';

use mysql;
select * from user;
# localhost代表只能在本地访问，%代表任意主机访问
create user 'testRoot'@'localhost' identified by '123';
create user 'testRoot2'@'%' identified by '123';
alter user 'testRoot'@'localhost' identified with mysql_native_password by '888888';
drop user 'testRoot'@'localhost';

# DCL-权限控制
# all ,all privileges 所有权限
# select              查询数据
# insert              插入数据
# update              更新数据
# delete              删除数据
# alter               更新表
# drop                删除数据库/表/视图
# create              创建数据库/表

# 查询权限
# show grants for '用户名'@'主机名';
# 授予权限
# grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
# 撤销权限
# revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

# 提示：* 代表所有

create user 'test'@'localhost' identified by '123';
select * from user;
show grants for 'test'@'localhost';
grant all on user.* to 'test'@'localhost';
revoke all on user.* from 'test'@'localhost';

drop user 'test'@'localhost';
select * from user;


# 函数
# 字符串函数
# concat(s1,s2,......sn)      将字符串拼接，将s1、s2、sn拼接成一个字符串
# lower(str)                  将字符串str全部转化为小写
# upper(str)                  将字符串str全部转化为大写
# lpad(str,n,pad)             左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
# rpad(str,n,pad)             右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
# trim(str)                   去除字符串头部和尾部的空格
# substring(str,start,len)    返回从字符串str从start开始长度为len的字符串

select concat('hello', 'world');
select lower('HELLO');
select upper('world');
select lpad('hello',10,'A');
select rpad('hello',10,'B');
select trim('       ajkfjalkj    ');
# 注意mysql中字符串的索引是从1开始的
select substring('helloWorld',1,5);


# 数值函数
# ceil(x)     向上取整;
# floor(x)    向下取整
# mod(x,y)    返回x/y的值
# rand()      返回0~1内的随机数
# round(x,y)  求参数x的四舍五入的值，保留y位小数

select ceil(2.1);
select floor(3.1);
select mod(6.1,2);
select rand();
select round(3.218390823,4);

# 透过函数生成纯数字的验证码
select lpad(round(rand()*1000000, 0),6,'0');



# 日期函数
# curdate()                           返回当前日期
# curtime()                           返回当前时间
# now()                               返回当前日期和时间
# year(date)                          获取指定date的年份
# month(date)                         获取指定date的月份
# day(date)                           获取指定date的日期
# date_add(add,interval expr type)    返回一个日期/时间值加上一个时间间隔expr后的时间值
# datediff(date1,date2)               返回起始时间date1和结束时间之间的天数

select curdate();
select curtime();
select now();
select year(now());
select month(now());
select day(now());
select date_add(now(),interval 70 day);
select date_add(now(),interval 70 month);
select date_add(now(),interval 70 year);
#提示：其计算的逻辑是前者-后者，返回值转化为天数返回
# 填写日期的时候，需要注意格式（默认格式）
select datediff('2020-10-10',curdate());



# 流程控制函数
# if(value, t, f)                                           如果value为true，则返回t，否则返回f
# ifnull(value1, value2)                                    如果value1不为空，返回value,否则返回value2
# case when[value] then [result] else[default] end          如果value为true，返回result，否则返回default默认值
# case [test] when[value] then [result] else[default] end   如果test等于value，返回result，否则返回default默认值


select if(2<3,'true','false');
select ifnull('true','false');

use  mysql;

select name,
       case homeInformation when'北京' then '首都' else '无' end
from userinformation;


# 约束 - 作用于表中字段上的规则，限制存储在表中的数据
# not null        非空约束        限制该字段的数据不能为null
# unique          唯一约束        保证该字段的所有数据都是唯一的
# primary key     主键约束        主键是一行数据的唯一标识且非空
# default         默认约束        保存数据时，未指定该字段的值，采用默认值
# check           检查约束        保证字段值满足某一个条件
# foreign key     外键约束        用来让两张表的数据建立连接，保证数据的一致性和完整性
#外键语法
# 创建表时添加外键
# create table 表名(
#   表字段
#        ...
#   [constraint][外键名] foreign key(外键字段名) references 主表(主表列名)
# );
# 创建表后添加外键
# alter table 表名 add constraint 外键名 foreign key(外键字段名) references  主表(主表列名)
#删除外键
# alter table 表名 drop foreign key 外键名
#设置外键删除/更新行为
# no action 禁止删除\更新
# restrict  禁止删除\更新
# cascade   删除\更新父表-子表自动删除\更新
# set null  删除\更新父表-子表制空
# set default 删除\更新父表-自定义子表的值
# 语法
# alter table 表名 add constraint 外键名 foreign key (外键字段) references 主表(主表列名) on update cascade on delete cascade ;

create database test;
use test;
create table user(
    id int primary key  auto_increment comment '主键',
    name varchar(20) not null  unique  comment '姓名',
    age int check ( age > 0 and age <= 120 )comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
)comment '用户表';

insert into test.user (name,age, status, gender) value('张三',18,'0','男');

select * from user;

drop table user;
drop database test;


#DQL——多表查询
# （一对多）【部门-员工】 1
# （多对多）【学生-课程】 2
# (一对一) 【用户-用户】 3

#     3-实现思路：创建两个表，在一个表中创建主键，另一个表中创建一个副键，
#     并在给副键设置外键的时候，设置unique标识符



# 多对多的演示
create database student;
use student;
create table stu(
    id int auto_increment primary key comment '学号',
    name varchar(20) comment '姓名',
    gender char(1) comment '性别'
)comment '学生信息';

insert into stu values (null, '张三', '男'),(null,'李四','男'),(null,'王五','男'),(null,'赵六','男');
select * from stu;

create table course(
    id int auto_increment primary key  comment '主键id',
    name varchar(20) comment '课程名称'
)comment '课程表';

insert into course values (null,'java'),(null,'php'),(null,'MySql'),(null,'C++');
select *from course;

create table stu_course(
    id int auto_increment primary key comment '主键' ,
    stuId int  not null comment '学生id',
    courseId int not null comment '课程id',
    constraint fk_courseId foreign key (courseId) references course(id),
    constraint fk_stu foreign key (stuId) references stu(id)
)comment '学生课程中间表';

insert into stu_course values (null,1,1),(null,2,2),(null,3,3),(null,1,3);
select *from stu_course;


# 多表查询

# 笛卡尔积查询
select *from stu,course;

# 使用where消除笛卡尔积的影响
select *from stu,course where stu.id = course.id;

# 详细演示
create database company;
use company;
create table employee(
    id int primary key auto_increment comment '主键ID',
    name varchar(20) comment '姓名',
    depId int comment '部门',
    constraint fk_department foreign key(depId) references department(id)
)comment '员工表';

insert into employee values (null, '张三',2),(null,'李四',4),(null,'王五',1),(null,'赵六',3);
select *from employee;


create table department(
    id int primary key auto_increment comment '主键ID',
    depName varchar(20) comment '部门名称'
)comment '部门表';

insert into department values (1,'一号'),(2,'二号'),(3,'三号'),(4,'四号');
select *from department;

select *from employee,department where employee.depId = department.id;


# 多表查询分类-连接查询【内连接&外连接【左外&右外】&自连接【使用别名】】&子查询
# 内连接：查询多表间的交集
# 外连接【左外】：查询左表全部数据及两表交集
# 外连接【右外】：查询右表全部数据及两表交集

# 内连接：查询多表间的交集

# 隐式语法：
# select 字段列表 from 表1,表2 where 条件;
select employee.name,department.depName from employee,department where employee.depId = department.id;
# 使用别名的写法
select e.name,d.depName from employee e ,department d where e.depId = d.id;

# 显式语法：
# select 字段列表 from 表1 [inner] join 表2 on 条件;
# inner可以省略
select e.name,d.depName from employee e inner join department d on e.depId = d.id;
select e.name,d.depName from employee e  join department d on e.depId = d.id;


# 外连接【左外】：查询左表全部数据及两表交集
# select 字段列表 from 表1 left [outer] join 表2 on 条件;
# outer可以省略
select e.* ,d.depName from employee e left outer join  department d on e.depId = d.id;
select e.* ,d.depName from employee e left  join  department d on e.depId = d.id;

# 外连接【右外】：查询右表全部数据及两表交集
# select 字段列表 from 表1 right join 表2 on 条件;
select e.name ,d.* from employee e right join department d on d.id = e.depId;

# 自连接【使用别名】
# select 字段列表 from 表名 别名A , 表名 别名B  where 条件;

# 联合查询——union&union all
# union all 是直接将两次的查询结果进行合并
# union 在联合的时候会去重
# 将多次查询的结果合并起来，形成一个新的查询结果集
# select 字段列表 from 表A union [all] select 字段列表 from 表B




# 子查询（嵌套查询）
# select *from t1 where column1 = (select column1 from t2)
# 其中外部的select 可以为update&delete&insert&select
# 分类：
# 标量子查询[返回单个值]: = <> > >= < <=
select *from employee where depId = (select id from department where depName = '四号');

# 列子查询[返回一列或多行值]: in&not in&any&some&all
select *from employee where employee.depId in (select id from department where depName = '四号' or depName = '一号');
select *from employee where employee.depId not in (select id from department where depName = '四号' or depName = '一号');

# 行子查询[返回一行或多列值]-=&<>&in&not in
# select *from 表名 where (字段列表1,字段列表2...) 条件 (select 字段列表 from 表名 where 条件);

# 表子查询[返回值是多行多列]-in
# 因为这个查询返回的是一个表,所以一般放在where的前面




# 事务:一组操作的集合
# 语法:

# 方式一:
# 查看/设置事务提交方式
# select @@autocommit;
# set @@autocommit = 0;
# 提交事务
# commit ;
# 回滚事务
# rollback;

# 方式二:
# 开启事务
# start transaction; & begin;
# 提交事务
# commit;
# 回滚事务
# rollback;

use company;
create table counts(
    name varchar(20) comment '姓名',
    money int comment '余额'
)comment '账户表';

insert into counts values ('张三',1000),('李四',1000),('王五',2000),('赵六',2000);

update counts set money =1000 where name = '张三' or name = '李四';
update counts set money =2000 where name = '王五' or name = '赵六';

select *from counts;

select @@autocommit;
set @@autocommit = 0;
select *from counts where name = '张三';
update counts set money = money - 500 where name='张三';
update counts set money = money + 500 where name='李四';
commit ;
rollback ;

# 恢复默认事务自动提交
set @@autocommit = 1;

# 开启事务的两种方式
start transaction ;
begin;
# 转账操作
select *from counts where name = '张三';
update counts set money = money - 500 where name='张三';
update counts set money = money + 500 where name='李四';
# 回滚操作
rollback ;
# 查看账户信息
select *from counts;

# 事务四大特性-原子性、一致性、隔离性、持久性

# 并发事务问题-脏读、不可重复读、幻读

# 事务隔离级别:
# read uncommitted;
# read committed;
# repeatable read; //mysql默认级别
# serializable;

# 查看事务隔离级别
# select @@transaction_isolation;
# 设置事务隔离级别
# set [session | global ] transaction isolation level {级别}


select @@transaction_isolation;