MySQL操作合集

最新推荐文章于 2024-05-09 17:02:27 发布
最新推荐文章于 2024-05-09 17:02:27 发布
阅读量470 收藏
点赞数
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yue152152/article/details/133808607
版权

数据库的操作

创建数据库

create database [if not exists] db_name 
[character set utf8]
[collate utf8_general_ci];

查看所有数据库

show databases;

查看数据库的创建语句

show create database db_name;

修改数据库

alter database db_name 
character set utf8
collate utf8_bin;

删除数据库

drop database [if exists] db_name;
  • 里面所有的表都会被删除

备份和恢复

备份:

在操作系统的shell中完成

mysqldump -P3306 -uroot -p密码 -B数据库名 > 备份储存的文件路径

还原:

在mysql中完成

source 备份储存的文件路径

可以使用相对路径，默认当前路径为mysql客户端打开的路径

注意事项:

  • 备份数据库下的表

    mysqldump -uroot -p 数据库名 表1 表2 > 备份储存的文件路径

  • 备份多个数据库

    mysqldump -uroot -p -B 数据库1 数据库2 >备份储存的文件路径

  • 如果备份一个数据库没有-B，恢复时需要先进入一个数据库再source

表的操作

创建表

CREATE TABLE table_name (
	field1 datatype,
	field2 datatype,
	field3 datatype
) [character set 字符集] [collate 校验规则] [engine 存储引擎];

存储引擎：

show engines;
  • MyISAM:数据目录中由三种不同的文件
    • .frm:表结构
    • .MYD:表数据
    • .MYI:表索引
  • InnoDB:由两种不同的文件
    • .ibd:索引和数据
    • .frm:表结构

查看表结构

desc 表名；

修改表

alter table tablename add column datatype [after column] [not null][default expr],column...
alter table tablename modify column datatype ...
alter table tablename drop column;

删除表

drop temporary table [if exists] tb_name,...

关于字符集和校验集

查看所有的字符集和校验集：

show character set [like 'utf8%'];
show collation [like 'utf8%'];

查看当前数据库的默认字符集和校验集：

show variables like 'character_set_database';
show variables like 'collation_database';

实际上有很多character_set 和collation ，mysql服务器的、客户端的、数据库的…它们具有一定继承关系

在当前数据库下建表的默认字符集和校验集从上面继承而来

表的字符集和校验集：

--创建时指定
create table table_name(
 ...
)character set utf8 collate utf8_bin;
--修改
alter table table_name character set utf8 collate utf8_bin;

指定某一列的字符集和校验集：

--创建时指定
create table table_name(
	name varchar(30) character set utf8 collate utf8_bin;
);
--修改某一列
alter table table_name modify name varchar(30) character set utf8 collate utf8_bin;

两个常用的校验方式：

utf8_general_ci;//不区分大小写

utf8_bin;//区分大小写

DML

插入

插入冲突解决

insert过程可能发生主键冲突或唯一键冲突

  • 更新：

    INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师')
	ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';

    当主键冲突发生，直接把当前主键对应的sn和name进行更新

  • 替换：

    REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');

    不冲突则插入，冲突则删除后重新插入

查询

别名

SELECT column [AS] alias_name [...] FROM table_name;

SELECT id, name, chinese + math + english 总分 FROM exam_result;

去重

SELECT DISTINCT math FROM exam_result;

where条件

IN (option, …)如果是 option 中的任意一个，返回 TRUE(1)
BETWEEN a0 AND a1范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
LIKE模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

where后面的表达式可以出现select字句，如：

where sal = (select max(sal) from emp);

子查询如果是多行，可能会用到如下关键词:

INwhere column in (select …) 存在即返回true
ALLwhere column > all(select …) 大于所有即返回true
ANYwhere column > all(select …) 大于任意一个即返回true

如果是多个元素的对应，即子查询是多列，可以用如下方式：

where (colum1,colum2,…) = (select colum1,colum2,… from emp);

子查询

像上面的子查询不仅可以出现在如上的where后面的表达式中

还可以直接将子查询当作一张临时的表，出现在from后：

select * from [子查询] where [exp]

或者做笛卡尔积

select * from table1，[子查询] where [exp]

排序

SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];
-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
-- column 可以是前面定义的别名

分页

-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

更新

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

找到对应的行，对响应的列元素进行替换

与插入冲突的两个语法比较：

插入冲突解决实际是查找 + 判断是否成功 + 更新的过程

删除

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

找到相应的行,进行删除

截断

TRUNCATE [TABLE] table_name
  1. 删除整表的所有数据
  2. 不经过事务,无法回滚
  3. 会重置AUTO_INCREMENT为0

插入查询的结果

从一个表中查询出n条数据,将这些数据插入一个表

这两个表可以是同一个

INSERT INTO table_name [(column [, column ...])] SELECT ...

聚合

聚合函数

COUNT([DISTINCT] expr)返回查询到的数据的 数量
SUM([DISTINCT] expr)返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)返回查询到的数据的 最小值，不是数字没有意义
  • distinct用于将数据去重后再统计

group by

select column1, column2, .. from table group by column [having expr];

显示平均工资低于2000的部门和它的平均工资 :

select avg(sal) as myavg from EMP group by deptno having myavg<2000;

having和where的区别

语法顺序:
where出现在group by前,having出现在group by后

执行顺序:

  • where先进行筛选
  • group by进行分组
  • 调用前面的聚合函数
  • having筛选聚合函数

表的内外链接

内连接

前面我们将两个表进行笛卡尔积，并在后面使用where进行筛选，我们可以将其结合成一个内连接的语法

select 字段 from 表1 inner join 表2 on 连接条件 and 其它条件;

外连接

内连接相当于取两张表的交集，连接条件一定要满足

外连接分为两种

  • 左外连接
  • 右外连接

如果是左外连接则表示需要完全显式左边的表，如果连接条件没有成立，右表的字段会为空

右连接反之

select 字段名 from 表名1 left/right join 表名2 on 连接条件

索引操作

索引创建

  1. 表定义时指定：

    --列后指定id为主键，name为唯一键
create table user(id int primary key, name varchar(30) unique);
--最后指定id为主键，name为唯一键，email为普通索引
create table user(id int, name varchar(30), email varchar(30), primary key(id), unique(name), index(email));

  2. 创建表后再添加

    --主键索引
alter table user add primary key(id);
--唯一键索引
alter table user add unique(name);
--普通索引(2种)
alter table user add index(email);
create index idx_name on user(email);
  • 主键索引值不能为空，唯一键索引可以为空

  1. 全文索引的创建及使用

    当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有
    要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进
    行全文检索，可以使用sphinx的中文版(coreseek)

    • 表定义时创建主键索引：
    CREATE TABLE articles (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	title VARCHAR(200),
	body TEXT,
	FULLTEXT (title,body)
)engine=MyISAM;
    • 创建表后再添加
    alter table articles add fulltext(title,body);

    使用全文索引

    不可如下方式进行模糊匹配

    select * from articles where body like ‘%databases%’

    正确使用方法：

    select * from articles where match(title,body) against('database');

查询索引

  • show keys from user;
  • show index from user;
  • desc user;

删除索引

  • 删除主键索引：alter table user drop primary key;
  • 删除其它索引：alter table user drop index 索引名;
  • 方法二： drop index 索引名 on 表名

视图操作

创建视图:

create view 视图名 as select ...;
  • 修改视图对基表数据又影响
  • 修改基表对视图数据有影响
  • 视图不能添加索引

删除视图：

drop view 视图名;

用户管理

基本操作

查询用户信息：

use mysql;
select host,user,authentication_string from user;
  • host：表示这个用户可以从那个主机登陆
  • user：用户名
  • authentication_string：用户密码通过password()函数加密之后
  • *_priv：用户拥有的权限

创建用户：

create user '用户名'@'登陆主机/ip' identified by '密码'

删除用户：

drop user '用户名'@‘主机名’

修改密码：

set password=password('新的密码');
set password for  '用户名'@‘主机名’=password('新的密码');

数据库权限

给用户授权

grant 权限列表 on 库.对象 to '用户名'@'登陆位置' [identified by '密码']
  • 权限列表：select, delete, create,…；或者all [privileges]
  • *.*：代表本数据库的所有对象（表、视图、存储过程）
  • 库.*：某个数据库的所有对象
  • 如果加了identified，会同时修改密码，如果用户不存在，会直接创建用户

查看权限

show grants for '用户名'@'%';

回收权限

revoke 权限列表 on 库.对象 from '用户名'@'登陆位置';

查看连接情况

show processlist;

使用C语言连接数据库

#include <iostream>
#include <mysql/mysql.h>
using namespace std;

int main()
{
    cout << "mysql client version: " << mysql_get_client_info() << endl;
    // 初始化mysql对象
    MYSQL *mfp = mysql_init(nullptr);
    if (mfp == nullptr)
    {
        cerr << "mysql_init error" << endl;
        return 0;
    }
    // 设置字符集（中文编码问题）
    mysql_set_character_set(mfp, "utf8");
    // 登陆认证：
    mfp = mysql_real_connect(mfp, "127.0.0.1", "root", "yue2983383631,", "db_test", 3306, nullptr, 0);
    if (mfp == nullptr)
    {
        cerr << "mysql_real_connect error" << endl;
        return 0;
    }
    // 向数据库发送sql语句
    string sql = "select * from account";
    int n = mysql_query(mfp, sql.c_str()); // 成功返回0，否则返回1
    if (n == 0)
    {
        cout << "mysql_query success" << endl;
    }
    else
    {
        cout << "mysql_query error" << endl;
        return 0;
    }
    // 提取数据库的返回结果
    // 初始化结果存储对象
    st_mysql_res *res = mysql_store_result(mfp);
    if (res == nullptr)
    {
        return 0;
    }
    int rows = mysql_num_rows(res);               // 获取行数
    int fields = mysql_num_fields(res);           // 获取列数
    MYSQL_FIELD *fname = mysql_fetch_fields(res); // 获取列名(返回一个数组)
    // 打印列名
    for (int i = 0; i < fields; i++)
    {
        cout << fname[i].name << "\t|";
    }
    cout << endl;
    // 按行列读取信息
    for (int i = 0; i < rows; i++)
    {
        MYSQL_ROW row = mysql_fetch_row(res); // 获取一行信息，行数会自动迭代
        for (int j = 0; j < fields; j++)      // 获取当前行的每列信息
        {
            cout << row[j] << "\t|";
        }
        cout << endl;
    }
    cout << endl;
    // 释放结果存储对象
    mysql_free_result(res);
    // 关闭mysql对象
    mysql_close(mfp);
}

makefile:

test:test.cc
	g++ -o $@ $^ -L/lib64/mysql -lmysqlclient
Massachusetts_11
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql日期操作技巧
09-24
mysql日期操作技巧mysql日期操作技巧mysql日期操作技巧mysql日期操作技巧mysql日期操作技巧mysql日期操作技巧mysql日期操作技巧mysql日期操作技巧
易语言MySQL使用操作
08-15
易语言MySQL使用操作源码 系统结构:刷新显示子程序,连接数据库, ======窗口程序集1 | | | |------ _创建表按钮_被单击 | | | |------ _建库按钮_被单击 | | | |------ _增加记录按钮_被单击 | | | |--
mysql 基本操作
09-11
早打算写些关于数据库操作方面的知识了，现在终于完成了第一篇，以下记录了关于mysql操作方面的基础知识
C#操作Mysql
04-09
简单的 C#操作Mysql，可以运行，里面有增删查改等主要借助于MySql.Data.dll脚本，不过我的工程里的Debug文件夹里已经含有此dll只要引用就可以使用了
mysql操作文档
12-17
mysql操作文档！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！...
mysql 按字段查询重复的数据
退役熬夜选手的博客
05-06 270
如果您只想识别具有重复值的列名，则方法 1 或 2 可能就足够了。如果您需要有关重复值的更多信息，例如每个值的计数，则方法 3 或 4 可能更好。主键是唯一索引的一种特殊类型，它还标识表中的每个行。在 MySQL 中，可以使用多种方法来查询按字段重复的数据。您可以使用 DISTINCT 关键字来删除结果中的重复行。该查询将为每个行返回列名的值，以及该值在同一列中出现的次数。该查询将返回所有具有重复值的列名的值，以及每个值的计数。该查询将返回所有具有重复值的列名的值。该查询将返回所有具有重复值的列名的值。
MySQL没有初始化配置文件设置属性
zwjzone的博客
05-06 288
安装mysql的时候，为了速度，并没有配置my.ini或者my.cnf文件，数据库因为断电，导致数据都看不见了，一直提示不存在，这时候需要修改配置文件，将innodb_force_recovery设置为0到6的值，依次尝试，比如innodb_force_recovery = 1,但是现在没有配置文件，该怎么设置呢？
springboot整合mybatis配置多数据源（mysql/oracle）
WLKUYM的博客
05-03 1015
springboot整合mybatis配置多数据源，可以都是mysql数据源，也可以都是oracle数据源，也可以mysql/oracle数据源都有，下面是配置多数据源的步骤（不局限与某一类数据库），之前配过都是oracle数据源的配置，下面是验证这个想法的实现，实现效果后，好像也可以同时配MongoDB、Redis等其他的数据源配置，但是好像也没碰到有人在一个项目里面这么干过…要么都是mysql数据源要么都是oracle数据源。
MySQL变量的定义与使用
2301_80888280的博客
05-09 300
MySQL变量的定义与使用
数据库（MySQL）—— 子查询
qq_67693066的博客
05-03 713
数据库（MySQL）—— 子查询
MySQL系统变量
最新发布
2301_80388658的博客
05-09 183
MySQL的系统变量是由MySQL服务器管理的，用于控制服务器的各种行为和特性。这些变量由系统提供，不是用户定义的，并且属于服务器层面的语法。它们包括全局变量和会话变量。：这些变量影响整个MySQL服务器，它们的更改需要具有相应的权限，并且通常需要重启服务器才能生效。：这些变量仅对当前连接到服务器的客户端有效，每个连接都有自己的会话变量设置。
AS_Path Filter在BGP中的应用
更多内容查看博客描述。
05-09 878
AS_Path属性按矢量顺序记录了某条路由从本地到目的地址所要经过的所有AS编号。如图10-14所示，某条BGP路由的AS_Path属性实际上可以看作是一个包含空格的字符串，所以可以通过正则表达式来进行匹配。图10-14BGP路由的AS_Path属性正则表达式就是用一个“字符串”来描述一个特征，然后去验证另一个“字符串”是否符合这个特征。BGP的AS_Path过滤器主要是定义AS_Path正则表达式，然后去匹配BGP路由的AS_Path属性信息，从而实现对BGP路由信息的过滤。例如。
MySQL以其他表作为条件更新指定表
Programming Talk
05-09 165
在MySQL数据库管理中，经常需要根据另一个表的数据来更新指定表中的记录。这种操作涉及到多表联合查询和更新语句的结合，是数据库操作中较为复杂且实用的技能之一。本文将深入介绍如何利用MySQL的​​UPDATE​​​语句结合​​​、​​等联接类型，实现依据其他表的条件来更新数据，同时提供具体示例和注意事项，帮助你高效处理此类需求。
基于springboot+vue+Mysql的自习室预订系统
老卓爱爪哇 的博客
05-05 1198
系统采用了Java技术，将所有业务模块采用以浏览器交互的模式，选择MySQL作为系统的数据库，开发工具选择 eclipse来进行系统的设计。基本实现了自习室预订系统应有的主要功能模块，本系统有管理员；首页、个人中心、学生管理、公告信息管理、座位预订管理、自习室管理、留言板管理、系统管理，学生；首页、个人中心、座位预订管理、留言板管理，前台首页；首页、公告信息、自习室、留言反馈、个人中心、后台管理、客服等功能。 对系统进行测试后，改善了程序逻辑和代码。同时确保系统中所有的程序都能正常运行，所有的功能都能操作
MySQL如何设计库表结构
Dxy1239310216的博客
05-02 705
在设计MySQL表结构时，我们需要根据业务需求和数据特点进行需求分析，并遵循规范化、字段设计、索引设计、主键设计和关联设计等原则。通过合理的表结构设计，我们可以提高数据库的性能和可维护性，为系统的稳定运行提供有力的支持。同时，还需要注意在实际应用中权衡利弊，选择适合的表结构和索引策略。希望本文能对您在设计MySQL表结构时提供一些帮助。
MYSQL-使用事务保证数据完整性
m0_68047268的博客
05-07 733
事务是一个完整的操作，事务的各步操作是不可分割的（原子的），要么都执行，要么都不执行。并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务。事务必须具备以下四种属性，简称ACID属性。事务完成后，它对数据库的修改被永久保持。当事务完成时，数据必须处于一致状态。
MySQL表的增删查改【基础部分】
m0_64799333的博客
05-09 782
（1）delete和update都是很危险的操作，delete一旦删除的条件没有设置好，就可能把不该删的数据删掉（2）这的删除/修改都是持久生效的，都会影响到数据库服务器硬盘中的数据。
数据库（MySQL）基础：约束
m0_74698759的博客
05-02 916
数据库（MySQL）基础：约束
MySQL#MySql表的操作
m0_75109118的博客
05-05 306
MySql表的操作
linux mysql操作
03-16
Linux下MySQL操作可以通过命令行或者图形化工具进行。 命令行操作： 1. 登录MySQL：在终端输入命令“mysql -u 用户名 -p”，然后输入密码即可登录MySQL。 2. 创建数据库：在MySQL命令行中输入“create database ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值