MySQL(二)

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

  • 创建用户

    我们现在默认使用的都是root用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

    创建用户

    CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

    • 用户名：将创建的用户名

    • 主机名：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%

    • 密码：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

    例子：

    ‐‐ user1用户只能在localhost这个IP登录mysql服务器
CREATE USER 'user1'@'localhost' IDENTIFIED BY '1234';
‐‐ user2用户可以在任何电脑上登录mysql服务器
CREATE USER 'user2'@'%' IDENTIFIED BY '1234';

    授权

    用户创建之后，没什么权限,需要给用户授权

    GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';

    • GRANT 授权关键字

    • 授予用户的权限，如SELECT，INSERT，UPDATE等。如果要授予所有的权限则使用ALL

    • 数据库名.表名：该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

    • '用户名'@'主机名': 给哪个用户授权

    例子：

    1. 给user1用户分配对test这个数据库操作的权限

    GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON test.* TO 'user1'@'localhost';

    1. 给user2用户分配对所有数据库操作的权限

    GRANT ALL ON *.* TO 'user2'@'%';

    撤销授权

    REVOKE 权限1, 权限2... ON 数据库.表名 FROM '用户名'@'主机名';

    例子：

    撤销user1用户对test操作的权限

    REVOKE ALL ON test.* FROM 'user1'@'localhost';

    查看权限

    SHOW GRANTS FOR '用户名'@'主机名';

    例子：

    查看user1用户的权限

    SHOW GRANTS FOR 'user1'@'localhost';

    删除用户

    DROP USER '用户名'@'主机名';

    例子：

    DROP USER 'user2'@'%';

    修改密码

    修改管理员密码

    mysqladmin -uroot -p password 新密码

    -- 新密码不需要加上引号

    -- 注意：需要在未登陆MySQL的情况下操作。

    例子：

    mysqladmin ‐uroot ‐p password 123456
输入老密码

    修改普通用户密码

    set password for '用户名'@'主机名' = '新密码';

    -- 注意：需要在登陆MySQL的情况下操作。

    set password for 'user1'@'localhost' = '666666';

数据类型名称描述
SMALLINT小的整数，带符号的范围是-32768到32767.无符号的范围是0到65535
MEDIUMINT中等大小整数-8388608到8388607，0到16777215
INT/INTEGER普通大小的整数，-2147483648到2,147,483,647，0到4294967295
BIGINT大整数，-9223372036854775808到9223372036854775807，0到18446744073709551615
FLOAT小(单精度)浮点数，允许的值-3.402823466E+38到-1.175494351E-38,0和1.175494351E-38到3.402823466E+38,这些是理论限制，基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些
DOUBLE普通大小(双精度)浮点数，允许的值-1.7976931348623157E+380到-2.2250738585072014E-308,0和2.2250738585072014E-38到 1.7976931348623157E+308.这些事理论限制，基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些
DATE日期，支持的范围为‘1000-01-01’到‘9999-12-31’，MySQL以'YYYY-MM-DD'格式显示DATE值，但允许使用字符串或数字为DATE列分配值
DATETIME日期和时间的组合。支持的范围是‘上面加上00：00：00’到‘上面第二个加上23：59：59’.MySQL以YYYY-MM-DD HH:MM:SS“格式显示DATETIME值，但允许使用字符串或数字为DATETIME列分配值
TIMESTAMP时间戳，范围是'1970-01-01 00:00:00'到2037年
TIME时间，范围是‘-838：59：59’到‘838：59：59’.MySQL以‘HH:MM:SS’格式显示TIME值，但允许使用字符串或数字为TIME列分配值
YEAR两位或四位格式的年。默认是四位格式。在四位格式中，允许的值是1901到2155和0000.在两位格式中，允许的值是70到69，表示从1970到2069年。MySQL以yyyy格式显示YEAR值，但允许使用字符串或数字为YEAR列分配值
CHAR(M)固定长度字符串，当保存时在右侧填充空格以达到指定长度。M表示列长度。M的范围是0到255个字符
VARCHAR(M)变长字符串。M表示最大列长度。M的范围是0到65535.（VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定。最大有效长度是65355字节）
BLOB[(M)]最大长度为65535(216-1)字节，=的BLOB列，可以给出该类型的可选长度M。如果给出，则MySQL将列创建为最小的但是足以容纳M字节长度的值的BLOB类型
TEXT[(M)]长字符串，最大长度为65535(216-1)字符的TEXT列。可以给出可选长度M。则MySQL将列创建为最小的但是足以容纳M字符长度的值的TEXT类型。

查看表结构

-- 查看表结构
DESCRIBE dept;
DESC dept;

练习：

创建一张user表, 可以存储以下内容：姓名，性别，年龄，生日，电话，家庭住址，邮箱

	CREATE TABLE `user`(
		`name` VARCHAR(20) COMMENT '姓名',
		`gender` CHAR(1) COMMENT '性别',
		`age` INT COMMENT '年龄',
		`birthday` DATE COMMENT '生日',
		`phone` VARCHAR(20) COMMENT '电话',
		`address` VARCHAR(50) COMMENT '住址',
		`email` VARCHAR(20) COMMENT '邮箱'
	);

创建和某表结构一样的表

-- 创建和dept结构一样的表
CREATE TABLE d LIKE dept;

-- 创建表
CREATE TABLE t AS select * from dept;

删除表

DROP TABLE table_name

添加列

ALTER TABLE d ADD id INT;

修改列属性

ALTER TABLE d MODIFY id VARCHAR(20);

修改列名

ALTER TABLE d CHANGE id ss VARCHAR(20);

删除列

ALTER TABLE d DROP ss;

重命名表

RENAME TABLE d TO dd;

CRUD操作

  • 对数据表中的数据操作通常有添加(Create)、查询(Retrieve)、修改(Update)、删除(Delete)、简称为CRUD。

添加数据

INSERT INTO table_name VALUES(值列表)

INSERT INTO table_name (列列表) VALUES(值列表)

-- 不推荐使用
INSERT INTO dept VALUE(1,'研发部');
-- 2
INSERT INTO dept VALUES(2,'销售部');
INSERT INTO dept VALUES(3,'行政部'),(4,'技术部');
-- 3
INSERT INTO dept(deptno,deptname)VALUES(5,'安保部');

区别：

  • value和values的区别，values可以同时插入多条数据用逗号隔开

  • dept和dept(列名，列名。。。)区别，如果不写列表必须按照列表创建时的顺序每一列都要添加

  • 有列名的按照列名排列顺序添加

查询数据

-- 查询所有数据
SELECT *FROM dept;
-- 查询某列的数据
SELECT deptname FROM dept;
-- 根据条件查询*
SELECT deptno FROM dept WHERE deptname='销售部';

修改数据

--全部修改为6
UPDATE dept SET deptno=6;
--根据条件修改
UPDATE dept SET deptno=1 WHERE deptname='研发部';
UPDATE dept SET deptno=2 WHERE deptname='销售部';
UPDATE dept SET deptno=3 WHERE deptname='行政部';
UPDATE dept SET deptno=4 WHERE deptname='技术部';
UPDATE dept SET deptno=5 WHERE deptname='安保部';

删除数据

-- 删除数据 ,一定要加 where 条件
DELETE FROM dept WHERE deptno=5;
-- 全部删除
DELETE FROM dept;
-- 清空/截断 所有数据(慎用)
TRUNCATE TABLE dept;

区别

  • delete from dd;

  • truncate table dd;

  • delete 是清空表中的数据,DML

  • truncate 是清空表数据（删除表后重新创建一个一样表），DDL

where条件连接

当sql语句中的条件有多条时，可以将多个条件连接起来。他们之间的关系有一下几种：

and

a and b: 表示 需要同时满足 a 条件 和 b 条件

or

a or b: 表示 满足 a 条件 或 b 条件都可以

in

in(a, ... ,b)： 表示在 a 及 b 这些值中都可以

like

模糊查询, % 表示任意个字符 _ 表示一个字符

数据备份和还原

命令行备份

备份结构

1.备份表结构

mysqldump -u root -p -d dbname table1 table2 ... > a.sql

2.备份数据库的所有表结构

mysqldump -u root -p -d dbname > b.sql

3.备份多个数据库的所有表结构

mysqldump -u root -p -d --databases db1 db2... > c.sql

4.备份所有数据库的表结构

mysqldump -u root -p -d --all-databases > d.sql

备份数据和结构

(相当于在备份结构的语法上去掉-d选项)

1.备份表结构和数据

mysqldump -u root -p dbname table1 table2 ... > a.sql

2.备份数据库的所有表结构和数据

mysqldump -u root -p dbname  > b.sql

3.备份多个数据库的表结构和数据

mysqldump -u root -p --databases db1 db2  > c.sql

4.备份所有数据库的表结构和数据

mysqldump -u root -p --all-databases > d.sql

  • mysqldump -h 127.0.0.1 -u root -p root db_name>path;

    • 使用mysqldump 命令备份数据库

    • -h指定数据库所在的服务器的ip地址

    • -u指定登录数据库的密码

    • db_name是要备份的数据库的名称

    • 使用输出目标操作符>,指定输出的文件具体路径c:/back.sql

备份表数据

mysql -u root -p -e "selec 语句" dbname > 目标文件名

select * from xxx into outfile ‘/tmp/stud.txt' ;

命令行还原

还原表结构和数据

mysql -u root -p [dbname] < 目标文件
mysql -h127.0.0.1 -uroot -proot db_name<back.sql

load data infile '/tmp/stud.txt' into table students;
source /backup/all_db_2013-09-08.sql

高级查询

distinct

在select语句中，可以使用distinct关键字对查询的结果集进行去重。

select distinct 列1, ... , 列n  from table_name [其他子句];

去重必须结果集中每个列的值都相同。

order by

order by用于对结果进行排序显示，可以使用ASC / DESC两种方式进行排序，可以有多个排序条件

  • ASC：表示升序排序，如果不写即为此排序方式

  • DESC：表示降序排序

select [distinct] 列1, ... , 列n from table_name [其他子句] order by 排序列1 [DESC], 排序列2 [DESC];

分页查询limit子句

select * from emp limit 0,2;

  • 第一个参数0是表示从第几条开始查询 (这里的 0 是可以省略不写的)；

  • 第二个参数 表示查询出几条数据

  • 后面不够的，有多少写多少；

select * from emp order by empNo limit 5;
select * from emp limit 5,5;

-- 
select * from table_name  limit (页码 - 1) * 每页数量, 每页数量;

聚合函数

Mysql中内置了 5 种聚合函数，分别是：SUM 、max、min、avg、count。

  • sum： 求和

    select sum(列) from table_name [其他子句];

  • max: 求最大值

    select max(列) from table_name [其他子句];

  • min: 求最小值

    select min(列) from table_name [其他子句];

  • avg： 求平均值

    select avg(列) from table_name [其他子句];

  • count: 求数量

    select count(列) from table_name [其他子句];

group by

group by 是对数据进行分组，分组时，表中有相同值的分为一组。分组后可以进行聚合查询。

group by分组后的查询中，select的列不能出现除了group by分组条件以及聚合函数外的其他列。

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2;

having

having是对group by分组后的结果集进行筛选。

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2 having 分组后条件;

综合查询

SELECT DISTINCT emp.deptno FROM emp JOIN dept ON emp.deptno = dept.deptno WHERE bridate >= '2000-01-01' GROUP BY emp.deptno HAVING count(*) >= 2 ORDER BY count(*) DESC  LIMIT 0, 5;

书写顺序是以上。

SQL语句的执行顺序

from --> on --> join --> where --> group by --> having --> select --> distinct-- > order by--> limit

sql语句定义和执行顺序

Corsin
关注
Mysql 二进制日志
u010648194的博客
03-24 6315
MySQL的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的MySQL语句。二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作。 一、二进制日志的参数配置 ① 开启二进制日志 show variables lik
MySQL二进制安装
Haibolun的博客
03-21 6432
目录 1.准备 1.1操作系统版本： 1.2 MySQL安装包5.7和8.0（二进制通用版） 1.3远程连接工具： 2.安装 MySQL（以8.0版本为例） 2.1本地上传安装包 2.2解压安装包 tar -xf 2.3设置软链接 ln -s 2.4设置环境变量 2.5创建MySQL用户和组 2.6创建相关目录并修改权限 2.7写配置文件 2.8初始化数据(initialize参数的异同) 2.9 My...
mysql 二级索引
wmsjlihuan的专栏
11-07 2万+
mysql中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。 以InnoDB来说，每个InnoDB表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键，该主键索引是聚集索引。如果你不定义为您的表的主键时，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作
Linux下开启MySQL二进制日志
txl13109187932的博客
12-20 2705
1、修改 mysql 的配置文件 my.cnf 2、在 [mysqld] 中添加内容 3、重启 mysql 4、验证
02-mysql升级篇（rpm方式+二进制方式升级）
✨ 欢迎来到【Seal ^_^ 的CSDN博客】！这里是我记录技术心得、分享经验的地方。✨
05-10 1万+
mysql升级篇（rpm方式+二进制方式升级）
二级mysql大题_二级MySQL操作题考试时怎么答题
weixin_34131379的博客
01-19 4378
1.启动MySQL问题。进入考试系统后，在大题的左上角有一个“启动MySQL”点击之后出现两个选项，一个是“localhost”，一个是“MySQL console”(后面那个单词忘了是不是这个了)，选择“MySQL console”，点击之后会出现下面的DOS窗口，密码一般是空，所以直接回车就行。回车之后会出现一大段话“Welcome to the ............”，下面有个命令提示符...
MySQL之二进制日志
weixin_52690231的博客
03-23 2439
MySQL之二进制日志
mysql二进制转成字符串
huge040430512的专栏
07-26 1万+
二进制转成字符串 SELECT  cast(param_key as CHAR)  FROM `stark_param_conf`
Linux系统下mysql开启二进制日志bin_log
qq_43294932的博客
04-21 3584
二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。 二进制日志，默认情况下是没有开启的，需要到MySQL的配置文件中开启，并配置MySQL日志的格式。 配置文件位置 : /usr/my.cnf 或者/etc/my.cnf 日志存放位置 : 配置时，给定了文件名但是没有指定路径，日志默认写入Mysql的数据目录。 1、登录mysql
MySQL安装配置教程（超级详细、保姆级）
热门推荐
Soloversion的博客
03-26 42万+
一、 下载MySQL Mysql官网下载地址https://downloads.mysql.com/archives/installer/ 1. 选择想要安装的版本，本篇文章选择的是5.7.31版本，下面的那个文件，点击Download下载 二、 安装MySQL 1. 选择设置类型 双击运行mysql-installer-community-5.7.31.0.msi 这里选择是自定义安装，所以直接选择“Custom”，点击“Next” “Developer Default”是开发者默认
mysql二进制安装
馆主的博客
07-26 3206
mysql进阶
2019全国MySql二级考试 复习笔记（考后体验更新）
xzy565143480的博客
02-15 1万+
全国MySql二级考试 复习笔记 操作题因为本地题库mysql根本无法运行，只能手打记事本对照答案 做了几套题发现有些规律，题库总是会考察几类东西 所以做一下笔记，顺带复习一下sql 文章目录全国MySql二级考试 复习笔记重点复习对象视图创建视图修改视图删除视图事件创建事件修改事件删除事件触发器创建触发器删除触发器储存过程将分号；转义创建储存过程DECLARE声明局部变量SET语句进行赋值...
mysql二进制数据类型_MySQL的数据类型（4）：二进制类型
weixin_36250977的博客
01-19 2363
3.2.5 二进制数据类型MySQL支持的二进制数据类型主要有：BIT(M)、BINARY(M)、VARBINARY(M)、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB等7种，它们主要用来存储由“0”、“1”组成的字符串，跟字符串类型数据一样，不同的二进制数据类型允许的最多字符个数不一样、占用存储空间也不一样，详见下表：字符串类型取值范围占用字节数说明BIT(n)0-6...
第79讲： MySQL Binlog二进制日志恢复误删数据的实践指南
江晓龙的博客
12-18 1万+
根据需求我们得知要恢复误删除的这张表，那么在事件信息中找到创建该表的事件，这个事件的开始标识位就作为我们截取Binlog日志的开始标识位号，结束标识位更好确定了，在事件信息中找到删除表的事件，这个事件的开始标识位也就作为我们截取Binlog日志的结束标识位，开始标识位号是上一个事件的结束表示为号。注意：一定不要将删除表事件的结束标识位号作为截取Binlog日志的结束标识位号，如果截取Binlog日志的结束标识位号是删除表的结束标识位号，就会把删除表的操作在还原时又进行了一遍，数据就不能恢复了。
开启MySQL二进制日志(binlog)
SuiHao
04-04 2395
前言binlog即二进制日志 Binary Log，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。一般来说开启二进制日志大概会有一定的性能损耗。二进制日志有两个最重要的使用场景:Mysql主从复制模式下，Master(主机)把它的二进制日志传递给Slaves(从机)来达到master-slave数据一致的目的。数据恢复，如果出现数据误删通过使用mysqlbinlog工具进行数据恢复。
【MySQL】数据库表的基本查询——增删查改
m0_74755811的博客
09-27 1080
在MySQL中，对表进行增删查改操作是数据库管理的基础。增（Insert）操作用于添加新记录，使用`INSERT INTO 表名 (列1 ...) VALUES (值1 ...);`。删（Delete）操作用于移除记录，使用`DELETE FROM 表名 WHERE 条件;`，需注意慎用以防误删。查（Select）操作用于检索数据，基本形式为`SELECT 列名 FROM 表名 WHERE 条件;`，可配合`ORDER BY`进行排序，`LIMIT`限制结果数量。改（Update）操作用于修改现有记录.
【MySQL】数据库基础认识
m0_74755811的博客
09-25 1279
存储数据用文件就可以了，为什么还要弄个数据库?文件保存数据有以下几个缺点：文件的安全性问题文件不利于数据查询和管理文件不利于存储海量数据文件在程序中控制不方便数据库存储介质：磁盘、内存。为了解决上述问题，专家们设计出更加利于管理数据的东西——数据库，它能更有效的管理数据。数据库的水平是衡量一个程序员水平的重要指标。
【mysql相关总结】
最新发布
Charles D
09-30 176
数据库小的表,全表扫描效率更高，不用建索引。索引的类型1.普通索引：基本的索引，没有任何约束限制2.唯一索引：类似普通索引,有唯一约束性3.主键索引：特殊的唯一索引,不允许有空值4.组合索引：多个列组合创建索引5.外键索引：只有innodb可以使用6.全文索引：只有myisam可以使用，且只能对英文进行全文检索注意事项1.复合索引遵循前缀原则（最左匹配原则）2.like查询,%号不能在前面，否则索引失效3.如果or前面的条件有索引,也会失效。
docker容器mysql数据备份 mysql容器无法启动备份数据
zhangxu1998lq的博客
09-30 273
mysql是旧容器名 因为mysql的配置文件my.cnf的路径没有映射到服务器，我修改了配置文件，配置文件有错误导致，容器启动不了，一直restarting。nmysql是新容器名 mysql是旧容器名 mysql:5.7是我的镜像名。新容器正常启动，旧容器的数据都还在。
MySQL二进制包安装全攻略
"MySQL二进制包安装步骤" MySQL二进制包安装是一种快速部署MySQL服务器的方法，无需编译源码，适用于对系统性能要求不高的环境。以下详细讲解这个过程： 一．准备的资料文件 在开始安装前，你需要下载对应的MySQL...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值