mysql 数据库常用技巧

最新推荐文章于 2024-08-11 16:01:20 发布
最新推荐文章于 2024-08-11 16:01:20 发布
阅读量101 收藏
点赞数
文章标签： 数据库
原文链接：https://my.oschina.net/u/3826187/blog/3060726
版权

mysql 数据库常用技巧

1. 关于备份数据库
register数据库中有user表和logs表
  1. 备份整个数据库
mysqldump -uroot -p register > register.sql
// 备份register数据库并生成register.sql数据库文件
  1. 备份数据库中单张表
mysqldump -u root -p register user > backups.sql
// 备份register数据库中user表到backups.sql数据库文件中
  1. 将sql文件写入数据库中
mysql -u root -p register < backups.sql
// 将backups.sql文件写入到register数据库中，这里请注意：mysql -u root -p 后面只能跟数据库，而不能跟sql文件!
这里backups.sql中如果只含有user中的数据，那么执行这个写入过程的时候，不会对logs有任何影响。
  1. 导出数据库的表结构(如果数据库中含有数据，不会导出数据，只会导出字段名)
mysqldump -d -uroot -p register user > user.sql
// 将register数据库中的user表结构导出
  1. 备份所有的数据库
mysqldump -u root -p --all-databases > database_dump.txt
// 将所有的数据库都备份到database_dump.txt文件中去
  1. 复制表
    使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
// 显示创建 user 表的 sql 语句
SHOW CREATE TABLE workflow_oa
2. 常用命令
  1. 连接数据库
mysql -h[主机地址] -u[用户名] -p[用户密码] 

//举例：连接本地数据库 mysql -h 127.0.0.1 -u root -p 123456 (有些情况下可以不需要输入主机地址):mysql -u root -p
  1. 创建数据库
create database [库名];

//举例：创建thinkcms数据库 create database thinkcms;

create database [库名] charset utf8; //创建数据库并存储字符集为utf8

create database [库名] DEFAULT CHARSET utf8 COLLATE utf8_general_ci; # utf8编码

create database [库名] DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; # gbk编码
  1. 显示所有数据库
show databases;
  1. 打开数据库
use [库名];

//举例：使用thinkcms数据库 use thinkcms
  1. 删除数据库
drop database [库名]; 

//举例：删除thinkcms数据库 drop database thinkcms
  1. 创建数据表
CREATE TABLE [表名] ([字段名] [字段类型] [字段参数]);

//举例：创建reg数据表并将id设置成自增主键，并将innodb设置成存储引擎，字符集设置成utf8
mysql> CREATE TABLE reg(
   -> id INT NOT NULL AUTO_INCREMENT,
   -> user VARCHAR(100) NOT NULL,
   -> pwd VARCHAR(40) NOT NULL,
   -> create_time DATE,
   -> PRIMARY KEY (id)
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1. 显示数据表字段名（以及字段名备注）
desc [表名];    （或者使用：show columns from [表名];)

// 举例：显示reg表的字段名 
desc reg;  
show columns from reg;  
// 显示字段名包含备注
show full columns from reg;
  1. 显示当前数据库下所有的数据表
show tables;
  1. 删除数据表
drop table [表名];

//举例：删除reg表 drop table reg;
  1. 清空表内容
truncate [表名];

//举例：清除 reg 表中所有内容， truncate reg;
  1. 对数据表重命名
alter table [旧表名] rename as [新表名];

//举例：将register表名称修改为users
alter table register rename as users;
  1. 修改表
  • 添加列：
alter table 表名 add 列名 类型
// 举例： ALTER TABLE menus ADD status TINYINT(1) DEFAULT 1 COMMENT '是否显示'
  • 删除列：
alter table 表名 drop column 列名
  • 修改列：
alter table 表名 modify column 列名 类型;  -- 类型

alter table 表名 change column 原列名 新列名 类型; -- 列名，类型
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
  • 添加主键：
alter table 表名 add primary key(列名);
  • 删除主键：
alter table 表名 drop primary key;

alter table 表名  modify  列名 int, drop primary key;
  • 添加外键：
alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);

// 例子
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

其中，外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）
  • 删除外键：
alter table 表名 drop foreign key 外键名称

// 例子 （不会删除外键字段）
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;
  • 修改默认值：
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
  • 删除默认值：
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
  1. 对数据表的CURD
插入数据：
INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
                       
//举例：在register表中插入一行数据：
insert into register (`user`,`time`,`pwd`) values ('小明', '2016-04-22 15:25:33', '1');
//插入多行数据：
INSERT INTO employee_tbl (`pid`,`user`,`time`,`pwd`) VALUES 
('1', '小明', '2016-04-22 15:25:33', '1'), 
('2', '小王', '2016-04-20 15:25:47', '3'), 
('3', '小丽', '2016-04-19 15:26:02', '2'), 
('4', '小王', '2016-04-07 15:26:14', '4'), 
('5', '小明', '2016-04-11 15:26:40', '4'),
('6', '小明', '2016-04-04 15:26:54', '2');

更新数据：
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause];

//举例：在register表中更新第一条数据：
update register set user="王五",pwd=555 where id=1;
// 所有80分以下的同学成绩加10分
UPDATE students SET score=score+10 WHERE score<80;

删除数据：
DELETE FROM table_name [WHERE Clause];

//举例：在register表中删除第一条数据：
delete from register where id=1;

查询语句：
SELECT column_name,column_name
FROM table_name
[WHERE Clause];

=====================where 条件=====================
//举例：在register表中查询id=1的数据：
select * from register where id=1;

select * from 表 where id > 1 and name != 'aylin' and num = 12;
select * from 表 where id between 5 and 16;
select * from 表 where id in (11,22,33)
select * from 表 where id not in (11,22,33)
select * from 表 where id in (select nid from 表)

=====================通配符 like=====================
//在register表中查询所有名字name含有张三的人
select * from register where name like "%张三%";
select * from register where name like '张三_'  # 张三开头的所有（一个字符）

=====================限制 limit=====================
select * from 表 limit 5;            - 前5行
select * from 表 limit 4,5;          - 从第4行开始的5行
select * from 表 limit 5 offset 4    - 从第4行开始的5行

=====================排序 asc，desc=====================
select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

=====================分组 group by=====================
select num from 表 group by num
select num,nid from 表 group by num,nid
select num,nid from 表  where nid > 10 group by num,nid order nid desc
select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
select num from 表 group by num having max(id) > 10

特别的：group by 必须在where之后，order by之前
3. 其它简单命令
  1. 查询时间：
select now();
  1. 查询当前用户：
select user();
  1. 查询数据库版本：
// 方法01、命令行中直接敲以下命令
mysql -V (大写的V)
// 方法02、命令行中输入以下命令
mysql --help | grep Distrib
// 方法03、连接数据库之后输入
status
// 方法04、直接查询版本
select version();
  1. 查询当前使用的数据库：
select database();
  1. 查询当前日期：
select current_date;
  1. 查询当前时间距离指定时间的天数
SELECT DATEDIFF(CURDATE(), FROM_UNIXTIME(1527782400, '%Y-%m-%d'))
// 1527782400对应的是2018-06-01 00:00的时间戳，如果今天是2018-06-07，那么返回的值就是6
  1. 时间戳和时间互相转换
// 时间戳转换成时间
SELECT FROM_UNIXTIME(1234567890);
SELECT FROM_UNIXTIME(1234567890, '%Y-%m-%d %H:%i:%S')
// 会输出 2009-02-14 07:31:30

// 时间转换成时间戳
SELECT unix_timestamp("2018-6-1 00:00:00");
// 会输出 1527782400
4. 默认数据库
  • 默认数据库有如下：
  1. mysql ：用户权限相关数据
  2. test ： 用于用于测试数据
  3. information_schema ： MySQL本身架构相关数据
  • 用户管理：
  1. 创建用户
create user '用户名'@'IP地址' identified by '密码';
  1. 删除用户
drop user '用户名'@'IP地址';
  1. 修改用户
rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';
  1. 修改密码
set password for '用户名'@'IP地址' = Password('新密码');
  • 权限管理：
  1. 查看权限
show grants for '用户'@'IP地址'
  1. 授权
grant  权限 on 数据库.表 to   '用户'@'IP地址'

2.1. 创建一个alex用户，并赋予权限

grant all privileges on *.* to  alex@"%" identified by "qazwsx" with grant option;

//grant 是授权命令，其中 alex 是我们连接用的用户名、"qazwsx"是连接密码，用户名后面的 "%" 通用符表示允许各 host 操作。
  1. 取消授权
revoke 权限 on 数据库.表 from '用户名'@'IP地址'
  1. 刷新授权
flush privileges;
  1. 授权示例如下：
grant all privileges on db1.tb1 TO '用户名'@'IP'

grant select on db1.* TO '用户名'@'IP'

grant select,insert on *.* TO '用户名'@'IP'

revoke select on db1.tb1 from '用户名'@'IP'

转载于:https://my.oschina.net/u/3826187/blog/3060726

churu0608
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
登录注册数据库建立
dishuluan4175的博客
06-16 1341
学习前端的时候Ajax学习的时候主要练习讲解的就是登录注册问题，从最开始的源生代码到后来的JQuery再到后面的AngularJS。讲了这三种登录注册问题。 我先讲一下后台数据库的建立，因为不管是哪一种后台都是一样的，只有前台js的时候才有差别。 要登录和注册，首先就要先建立数据库，我们设计数据库表的时候就要先考虑清楚数据库表需要有哪些内容，我们给表格命名user,用户表首先要包括用...
MySQL数据库常用操作技巧总结
09-09
MySQL数据库是世界上最受欢迎的开源关系型数据库之一，广泛应用于各种规模的应用程序中。掌握其常用操作技巧对于数据库管理员和开发人员来说至关重要。以下是一些关键的MySQL操作技巧的详细解释： 1. **查询不同表...
ThinkPHP框架获取最后一次执行SQL语句及变量调试简单操作示例
12-17
本文实例讲述了ThinkPHP框架获取最后一次执行SQL语句及变量调试简单操作。分享给大家供大家参考，具体如下： ThinkPHP中获取最后一次执行sql语句的 方法有两种: 其一是 调用模型 获取 如： $sql = $model ->getLastSql(); Thinkphp中Model类，有getLastSql这个函数，甚至还有，getLastInsID，getDbError，getError，getPk，getDbFields等函数。这些函数都是我们经常可能会用到的model层的函数。 其二是 在3.2.3版本，新增fetchSql()函数。 如 $sql= $model->f
头歌MySQL数据库实训答案 有目录
个人博客地址https://sun98k.github.io/
12-04 25万+
计算机系统综合实训 头歌MySQL数据库实训平台作业，内容比较全比较多，内容仅供参考，如有错误部分希望联系我跟正，觉得有用记得点赞收藏。数据库1-MySQL数据定义与操作实战 数据库2-MySQL数据管理技术实战 数据库3-MySQL数据库系统设计实战 数据库4-层次、网状、关系模型实战
最全的mysql常用语句大全
吴名氏的博客
10-08 15万+
最全的mysql常用语句大全
MySQL数据库面试题（2020最新版）
ThinkWon的博客
03-10 121万+
文章目录数据库基础知识为什么要使用数据库什么是SQL？什么是MySQL?数据库三大范式是什么mysql有关权限的表都有哪几个MySQL的binlog有有几种录入格式？分别有什么区别？数据类型mysql有哪些数据类型引擎MySQL存储引擎MyISAM与InnoDB区别MyISAM索引与InnoDB索引的区别？InnoDB引擎的4大特性存储引擎选择索引什么是索引？索引有哪些优缺点？索引使用场景（重点）............
MySQL数据库查询优化技巧
qq_43842093的博客
05-02 1545
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0 3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎
MySQL数据库，从入门到精通：第四篇——MySQL中常用的运算符及其用法
沉淀、分享、成长，让自己和他人都能有所收获！
06-10 2万+
本文主要分为三个方面，第一部分介绍MySQL中的算术运算符，包括加、减、乘、除、求模等运算符的用法和注意事项。
MySQL数据库 - 存储引擎
qq_57093716的博客
03-23 1304
数据库 存储引擎
MySQL数据库面试题汇总
weixin_45014379的博客
08-23 1299
MySQL是一种关系型数据库管理系统，它使用SQL语言进行查询和管理数据。它是一种开源软件，可以在多个操作系统上运行。在数据中，索引相当于字典的目录一下，通过索引可以快速的检索数据。顾名思义，最左优先，以最左边为起点任何连续的索引都能匹配上。同时遇到范围查询（>、
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MySQL数据库常用操作和技巧(DBA必备知识)
09-11
下面将详细讲解MySQL数据库的一些常用操作和实用技巧。 **一、数据库备份** 数据库备份是确保数据安全的关键环节。在MySQL中，除了使用官方提供的`mysqldump`工具进行标准的逻辑备份外，还可以采取直接复制数据...
头歌MySQL数据库
qq_46162008的博客
10-21 9234
数据库部分一条一条的写，可鼠标手动粘贴，除特定命令外未分大小写。 第1关：创建数据库 第2关创建表 第3关：使用主键约束 第4关：外键约束 第5关：添加常用约束 MySQL数据库 - 数据库和表的基本操作（一） 第1关：查看表结构与修改表名 第2关：修改字段名与字段数据类型 第3关：添加与删除字段 第4关：修改字段的排列位置 第5关：删除表的外键约束 MySQL数据库 - 数据库和表的基本操作（二） 第1关：插入数据 第2关：更新数据 第3关：删除数据 MySQL数据库 - 单表查询（一） 第1关：基本查询
JDBC详细使用
m0_73627305的博客
08-09 495
​ JDBC是一种标准,一种规则,主要作用是使用java语言操作数据库的​ JDBC这个标准中有很多的接口,接口中有很多方法。
seatunnel2.3.3在centos7上安装
鑫哥
08-09 844
安装前需要准备点环境，因为seatunnel是基于java开发的，因此需要先安装java，我这里使用的java1.8，可以网上搜索下java安装教程，记得配置JAVA_HOME环境变量。在安装的过程中需要用到mysql命令，因此也需要安装下MySQL环境，这里也不具体讲解了，在网上搜索安装就行。接下来就是正式安装seatunnel了，
SpringBoot依赖之Quartz Scheduler定时调度器使用MySQL存储Job
ahauedu的专栏
08-07 656
接上一篇。本篇将在 Spring Boot 项目中，使用 Quartz Scheduler 结合 MySQL 数据库来存储 Job。
springboot民办高校科研项目管理系统-计算机毕业设计源码54009
最新发布
VX_DZbishe的博客
08-11 459
论文主要是对基于springboot的民办高校科研项目管理系统进行了介绍，包括研究的现状，还有涉及的开发背景，然后还对系统的设计目标进行了论述，还有系统的需求以及整个的设计方案，对系统的设计以及实现，也都论述的比较细致，最后对基于springboot的民办高校科研项目管理系统进行了一些具体测试。本次报告，首先分析了研究的背景、作用、意义，为研究工作的合理性打下了基础。
8.7-主从数据库的配置+mysql的增删改查
qq_70752758的博客
08-07 706
Slave_IO_Running和Slave_SQL_Running都是yes就表示主从数据库配置成功了。insert into 表名称 values(1,"name","word")先查看按照升序排列的qty，然后在这基础上在按照price的升序排列进行查看。当使用聚合查询以后，不能使用where，如果要使用，添加having。insert into 表名称 select * from 其他表。insert into 表 value () ,()创建可以远程登录的用户。查看升序排列的qty。
"MySQL数据库常用命令大全，实用版分享
以下将介绍MySQL数据库常用命令，并分享一些超级实用版的技巧和资料。 首先，我们需要了解MySQL数据库的一些常用命令。创建数据库是我们在使用MySQL时经常会遇到的任务，创建数据库的命令是create database name；...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值