mysql

最新推荐文章于 2024-08-26 14:45:45 发布

闹心990

最新推荐文章于 2024-08-26 14:45:45 发布

阅读量191

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_65486601/article/details/125631619

版权

操作数据库

创建数据库表

常用命令：

show create database studb --查看创建数据库的语句
show create table text  --查看text数据表的定义语句
desc t_student  ---显示表的结构

修改删除表

修改表

---修改表名： ALTER TABLE 原表名 RENAME AS 新表名
alter table text rename as text1 
--增加表的字段：  ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE text1 ADD age INT(11)
---修改表的字段（重命名，修改约束）
--ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE text1 MODIFY age VARCHAR(11) --修改约束
--ALTER TABLE 表名 CHANGE 原名字 新名字 列属性
ALTER TABLE text1 CHANGE age age1 int(1) --字段重命名
--删除表的字段: ALTER TABLE 表名 DROP 字段名
ALTER TABLE text1 DROP age
--设置表的自增起始值(主键自增起始值)
alter table 表名 AUTO_INCREMENT=起始值;
alter table student AUTO_INCREMENT=1;

删除

--删除表(如果表存在再删除）
DROP TABLE IF EXISTS text

注意点：

`` 字段名，使用这个包裹
注释 – /**/
sql 关键字大小写不敏感
使用创建删除操作尽量加上判断以免报错

mysql数据管理

外键（了解）

DML语言（全部记住）

insert
update
delete

添加

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：
INSERT INTO 表名 VALUES (value1,value2,value3,...);
第二种形式需要指定列名及被插入的值：
INSERT INTO table_name (字段1,字段2,字段3,...)
VALUES (value1,value2,value3,...);

语法：insert into 表名([字段名1，字段名2])values(‘值1’,‘值2’)

修改

update 修改谁（条件） set 原来的值=新值

--修改学生表id为1的name为狂神
UPDATE student SET `name`='狂神' where id=1
--修改多个属性,逗号隔开
UPDATE student SET `name`='狂',`password`='123459' WHERE id=1
--语法：
--update 表名 set colnum_name=value where [条件]

条件：where子句运算符

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5<>6	true
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN…and…	在某一个范围内	[2，5]	2，3，4，5
AND	&&	5>2and1>2	false
or	\|\|	5>2and1>2	true

--通过多个条件定位数据
update `student` set `name`='长江七号' where `name`='周'and sex='男'

语法：update 表名 set colnum_name = value,[colnum_name=value…] where [条件]

注意：

colnum_name 是数据库的列，
条件，筛选的条件，如果没有指定会修改所有的列

value 是一个值也可以是变量

update `student` set `birthday`=CURRENT_TIME where `name`= '长江七号'and sex='男' ---CURRENT_TIME获取当前时间

删除

delete命令

--删除数据
delete from 表名
--删除指定数据
delete from 表名 where [条件];

TRUNCATE 命令

--清空 student表
truncate `student`

delete，truncate 和 delete之间的区别

1.首先看下语法定义：

1, drop table_name
2, truncate table_name
3, delete table_name [where column_name = value]

2.各个删除操作删除的内容：

drop：删除内容和定义，释放空间。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表
truncate：删除内容、释放空间但不删除定义。与drop不同的是,他只是清空表数据而已
delete：删除内容不删除定义，不释放空间

注意:truncate 不能删除行数据,要删就要把表清空

DQL查询数据

DQL(Data Query LANGUAGE: 数据查询语言)

所有的查询操作都用Select
数据库核心语言
使用频率最高的语句

基本查询

-- 查看表结构
desc books 
-- 查询books表使用字段
select * from books
select id,name as '书名',countbook,detail,img,datetime,typeid from books
-- 查询books表的所有书名（不重复）
select distinct name '书名' from books

条件查询

-- 查询价格等于45的书籍
select name '书名' from books where pay=45
-- 查询价格小于20的书籍名称
select name 'bookName'from books where pay<20
-- 查询价格小于等于20的书籍名称
select name from books where pay<=20
-- 查询评论信息为空的书籍id
select id from books where comments is null
-- 查询评论信息不为空的书籍id
select id from books where comments is not null
-- 查询价格在1（包含）到20（包含）之间的书籍价格
select pay 价格 from books where pay>=1 && pay<=20 
select pay 价格 from books where pay>=1 and pay<=20 
select pay 价格 from books where pay between 1 and 20
-- 查询价格小于20且 书名不为空的书籍信息
select * from books where name is not null and pay<=20
-- 查询价格等于12,或4 或3的书籍信息
select * from books where pay=12 or pay=4 or pay=3
select * from books where pay in(12,4,3)
-- 查询书名四个字的书籍信息
select * from books where name like '____'
-- 查询书名最后一位为义字的书籍信息
select * from books where name like '%义'

聚合函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P6t0agMq-1657063080930)(F:\2021年12月4号开始笔记\Mysql\img\image-20220615161125736.png)]

SELECT * FROM `goods`
#查询tyoeid为2的商品数量
select count(1) from goods where typeid=2
#查询价格最高的商品名称和价格
# select max(price)from goods 获取最大价格
select  goodsname as 商品名称,price as 价格 from goods where price=(select max(price)from goods)
# 获取商品平均价格
select avg(price) 商品平均价格 from goods
# 获取商品价格之和
select sum(price) 商品价格之和 from goods

分组查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-acb8JjQs-1657063080932)(F:\2021年12月4号开始笔记\Mysql\img\image-20220617090835036.png)]

select * from element_user
#根据性别分组统计男生女生数量
select sex,count(1) from element_user group by sex
#查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress 工作地址,count(1) from emp where age<45 group  by 工作地址 having count(1)>=3

排序查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PHby7Mj1-1657063080933)(F:\2021年12月4号开始笔记\Mysql\img\image-20220617091307836.png)]

select * from goods 
#根据id倒序排序
select * from goods order by id desc
#根据价格排序，如果价格相同根据id倒序排序
select price 价格,id from goods order by price asc,id desc

分页查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GvtWTfgP-1657063080934)(F:\2021年12月4号开始笔记\Mysql\img\image-20220617092733298.png)]

select * from goods 
select * from goods limit 0,5
select * from goods limit 10

DQL执行顺序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9PdihGhT-1657063080935)(F:\2021年12月4号开始笔记\Mysql\img\image-20220617095718825.png)]

指定查询字段

-- 查询全部的学生 ： SELECT 字段 FROM 表
SELECT * from student
-- 查询指定的字段
SELECT `Studentno`,`StudentName` FROM student
-- 别名 AS
SELECT studentname as 名字 FROM student as S
-- 函数 Concat(a,b)
select concat('姓名：',studentname) as 新名字 from student

语法： select 字段,… from 表

去重 distinct

作用：去除select查询处理的结果中重复的数据，只显示一条

-- 查询全部的考试成绩
select * from result 
-- 查询有那些学生参加了考试
select `studentNo` from result
-- 发现重复数据去重
select distinct `studentno` from result

数据库的列（表达式）

-- 查询系统版本(函数)
select VERSION()
-- 计算结果（表达式）
select 100*3-1 as 计算结果为
-- 查询自增的步长（变量）
select @@auto_increment_increment
-- 学员考试成绩+1分查看
select `studentno`,`studentresult`+1 as `提分后` from result

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量…

select 表达式 from 表

where 条件子句

-- 查询考试成绩在90到100之间
select studentno,studentresult from result where studentresult>=90 and studentresult<=100
-- 模糊查询（区间）
select studentno,studentresult from result where studentresult between 95 and 100

模糊查询：比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null,结果为真
is not null	a is not null	如果操作符不为null,结果为真
between	a between b and c	若a在b和c之间则为真
like	a like b	sql匹配，如果a匹配b,则结果为真
in	a in（a1,a2,a3…)	假设a在a1，或者a1…其中某一个值中，结果为真

-- 模糊查询
-- like 结合 %(代表0到任意个字符)  _(一个字符)
-- 查询姓刘的同学
select studentNo,studentName from student where studentName like '刘%'
-- 查询姓刘的同学,名字后面只有一个字的
select studentNo,studentName from student where studentName like '刘_'
-- in (具体的一个或者多个值)
-- 查询1001，1002，1003号学员
select studentno,studentname from student where studentNo in (1001,1002,1003);
查询在北京的学生
select studentNo,studentName from student where address in ('北京');

联表查询

--查询参加考试的同学（学号，姓名，科目编号，分数）
selec * from student
selec * from result
/*思路：
1分析需求，分析查询的字段来自那些表
2确定交叉点（这两个表中那个数据是相同的）
判断条件：学生表中的studentNo=成绩表 studentNo
*/
---join on 连接查询
--- where 等值查询
select s.studentNo,studentName,subjectNo,studentResult from student as s inner join result as r where s.studentNo=r.studentNo
--Right
select s.studentNo,studentName,subjectNo,studentResult from student s Right join result r on s.studentNo=r.studentNo
--Left join
--查询缺考的同学
select s.studentNo,studentName,subjectNo,studentResult from student s Left join result r on s.studentNo=r.studentNo where studentresult is null

操作	描述
inner join	如果表中至小有一个匹配，就返回行
left join	即使右表中没有匹配，也会从左表中返回使用的值
right join	即使左表中没有匹配，也会从右表中返回使用的值

分页和排序

排序 ORDER BY

--ORDER BY 语句默认按照升序对记录进行排序。
如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

分页

--语法：limit 起始值，页面大小
--网页应用：当前，总的页数，页面大小
--第n页  limit (n-1)*pageSize,pageSize
--【paheSize:页面大小】
--【(n-1)*pageSize:起始值】
--【n:当前页】
--【数据总数/页面大小=总页数】

语法：limit(查询起始下标，pagesize)

mysql函数

1常用函数：

--数学函数
select ABS(-8)--绝对值
select CEILING(9,4)--向上取整
select floor(9,4)--向下取整
select rand()--放回一个0~1之间的随机数
select sign(10) --判断一个数的符合 负数返回-1，正数返回1
--字符串函数
select CHAR_LENGTH('asf')--字符串长度
select concat('我','和','你')--拼接字符串

2聚合函数

函数名称	描述
count

事务

事务原则：ACID原则原子性，一致性，隔离性，持久性（脏读，幻读）

原子性(Atomicity)

要么都成功，要么都失败

一致性(Consistency):事务前后的数据完整性要保持一致

隔离性（Isolation）:多个用户并发访问数据库时，数据库为每一个用户开启事务，不能被其他事务的操作数据所干扰

持久性（Durability）:事务一旦提交不可逆

隔离导致的一些问题

**脏读：**指一个事务读取另外一个事务未提交的数据

不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同

虚读（幻读）：指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

执行事务

CREATE TABLE `account`(
`id` int(3) NOT null auto_increment,
`name` varchar(30) not null,
`money` DECIMAL(9,2) not null,
PRIMARY key(`id`)
)ENGINE=innodb default charset=utf8
INSERT into account(name,money)VALUES('a',2000.00),('b',1000)
-- 模拟转账
set autocommit=0; --关闭自动提交
start transaction --开启一个事务

UPDATE account set money=money-500 where `name`='a'
UPDATE account set money=money+500 where `name`='b'
COMMIT;--提交事务
ROLLBACK;--回滚
set autocommit =1 --恢复默认值

索引

索引的分类

主键索引(primary key)
- 唯一标识,主键不可重复,只能有一个作为主键
唯一索引(UNIQUE key)
- 避免重复出现,唯一索引可以重复,多个列都可以标识,唯一索引
常规索引(KEY/index)
- 默认的 index key 关键字来设置
全文索引(FullText)
- 快速定位数据

用户管理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D2z1tsIU-1657063080935)(F:\2021年12月4号开始笔记\Mysql\img\image-20220617105234719.png)]

权限管理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g96DAtN5-1657063080936)(F:\2021年12月4号开始笔记\Mysql\img\image-20220617110105139.png)]

字符串函数

#字符串拼接
select concat('hello','Mysql')
#将大写字符转小写
select lower('HEllo')
# 将小写字符转大写
select upper('Hello')
#左填充字符  : 000010
select lpad('10',6,'0')
#右填充字符
select rpad('10',5,'-')
#去左右两边空格
select trim('  h e  ')
#substring 字符串截取
select substring('Hello mysql',1,5)

附带java版方便记忆

package 字符串;

import cn.hutool.core.text.StrBuilder;
import org.junit.jupiter.api.Test;

public class 字符串 {
    @Test
    public void t(){
        String str=" Hello";
        //字符串拼接 (这里是new了一个新的对象)
        System.out.println("字符串拼接:"+str.concat("Word"));
        //将字符串转小写
        System.out.println("字符串转小写:"+str.toLowerCase());
        //将字符串转大写
        System.out.println("字符串转大写:"+str.toUpperCase());
        //左右填充
        String rpad = RPAD(str, 7, "-");
        System.out.println("右填充:"+rpad);
        String lpad = LPAD(str, 7, "-");
        System.out.println("左填充:"+lpad);
        //去掉左右空格
        System.out.println("去掉左右空格:"+str.trim());
        //#substring 字符串截取
        System.out.println("字符串截取:"+str.substring(0,4));

    }

    /**
     * 右填充
     * @param str 被填充值
     * @param n 达到n个字符长度
     * @param pad 填充值
     * @return
     */
    public static String RPAD(String str,int n,String pad){
        StringBuffer stringBuffer = new StringBuffer(str);
        int sum=n-str.length();
        for (int i=0;i<sum;i++){
            stringBuffer.insert(stringBuffer.length(),pad);
        }
        return String.valueOf(stringBuffer);
    }
    /**
     * 左填充
     * @param str 被填充值
     * @param n 达到n个字符长度
     * @param pad 填充值
     * @return
     */
    public static String LPAD(String str,int n,String pad){
        StringBuffer stringBuffer = new StringBuffer(str);
        int sum=n-str.length();
        for (int i=0;i<sum;i++){
            stringBuffer.insert(0,pad);
        }
        return String.valueOf(stringBuffer);
    }

}

数学函数

#数值函数
#向上取整
select ceil(1.1); 
#向下取整
select floor(1.9); 
#取余
select mod(6,4);
#0-1之间的随机数
select rand();
#四舍五入保留2位小数
select round(2.432,2)

select ceil(1.1) 向上取整,floor(1.9) 向下取整,mod(6,4) 取余,
rand() '0-1之间的随机数',ROUND(2.432,2) '四舍五入保留2位小数'

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nSR9zi8k-1657063080937)(F:\2021年12月4号开始笔记\Mysql\img\image-20220619214705268.png)]

日期函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UY1G6Svh-1657063080937)(F:\2021年12月4号开始笔记\Mysql\img\image-20220621145934356.png)]

流程函数

select * from goods 
#流程控制函数 if
select if(false,'ok','Error')
# ifnull
select ifnull('ok','Default') #ok
select ifnull(null,'Default') #Defaul

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2Zk5OGZx-1657063080937)(F:\2021年12月4号开始笔记\Mysql\img\image-20220619095728546.png)]

约束

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lFscXATo-1657063080938)(F:\2021年12月4号开始笔记\Mysql\img\image-20220621151402882.png)]

外键映射

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UY8jV5x7-1657063080938)(F:\2021年12月4号开始笔记\Mysql\img\image-20220625111147025.png)]

添加外键

CREATE TABLE 表名(
    字段名 字段类型,
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
-- 例子
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

删除和更新

select * from goods
#流程控制函数 if
select if(false,‘ok’,‘Error’)

ifnull

select ifnull(‘ok’,‘Default’) #ok
select ifnull(null,‘Default’) #Defaul


[外链图片转存中...(img-2Zk5OGZx-1657063080937)]

# 约束

[外链图片转存中...(img-lFscXATo-1657063080938)]

## 外键映射

[外链图片转存中...(img-UY8jV5x7-1657063080938)]

> 添加外键

```sql
CREATE TABLE 表名(
    字段名 字段类型,
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
-- 例子
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);