Mysql 基础操作

数据库

概述

数据库(Database，DB)是按照数据结构来组织、存储和管理数据的仓库，其本身可被看作电子化的文件柜，用户可以对文件中的数据进行增删改查等操作。

数据库系统是指在计算机系统中引入数据库后的系统，除了数据库，还包括数据库管理系统(Database Management System，DBMS)、数据库应用程序。

数据库技术的发展

🐬人工管理阶段

数据不在计算机中长期保存
没有专门的数据管理软件，数据需要应用程序自己管理
数据是面向应用程序的，不同的应用程序之间无法共享数据
数据不具有独立性，完全依赖于应用程序

🐬文件系统阶段

数据可以在计算机外存设备上长期保存，可以对数据反复进行操作
通过文件系统管理数据，文件系统提供了文件管理功能和存取方法
在一定程度上实现了数据独立性和共享性，但非常的薄弱

🐬数据库系统阶段

数据结构化
数据共享
数据独立性高
数据统一管理与控制

数据库相关的人员

数据库系统涉及一些人员，主要包括数据库管理员(Database Administrator,DBA)、应用程序员(Application Programmer)和最终用户(End User)。

🐬数据库管理员

数据库管理员负责管理和维护数据库，参与数据库的设计、测试和部暑。

🐬应用程序员

应用程序员负责为最终用户设计和编写程序，并进行调试和安装，以便最终用户利用应用程序来对数据库进行存取操作。

🐬最终用户

最终用户一般为非计算机专业人员，通过应用程序访问数据库。

SQL (Structured Query Language,结构化查询语言) 是一种数据库查询语言和程序设计语言，主要用于管理数据库中的数据，如存取数据、查询数据、更新数据等。

SQL是IBM公司于1975-1979年开发出来的，在20世纪80年代，SQL被美国国家标准学会（ANSI）和国际标准化组织（International Organization for Standardization,ISO）定义为关系数据库语言的标准。

SQL是由4部分组成的。

SQL语言

SQL (Structured Query Language,结构化查询语言) 是一种数据库查询语言和程序设计语言，主要用于管理数据库中的数据，如存取数据、查询数据、更新数据等。

SQL是IBM公司于1975-1979年开发出来的，在20世纪80年代，SQL被美国国家标准学会（ANSI）和国际标准化组织（International Organization for Standardization,ISO）定义为关系数据库语言的标准。

SQL是由4部分组成的。

🐬数据定义语言

数据库定义语言(Data Definition Language,DDL)主要用于定义数据库、表等。

例如，CREATE语句用于创建数据库、数据表等，ALTER语句用于修改表的定义等，DROP语句用于删除数据库、删除表等。

🐬数据操作语言

数据操作语言(Data Manipulation Language,DML)主要用于对数据库进行添加、修改和删除操作。

例如，INSERT语句用于插入数据，UPDATE语句用于修改数据，DELETE语句用于删除数据。

🐬数据查询语言

数据查询语言(Data Query Language,DQL)主要用于查询数据。

例如，使用SELECT语句可以查询数据库中的一条数据或多条数据。

🐬数据控制语言

数据控制语言(Data Control Language，DCL)主要用于控制用户的访问权限。

例如，GRANT语句用于给用户增加权限，REVOKE语句用于收回用户的权限，COMMIT语句用于提交事务，ROLLBACK语句用于回滚事务。

SQL语句

SQL分类

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等； DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）； DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；

DQL（Data Query Language）：数据查询语言，用来查询记录（数据）；

DDL：数据定义语言

创建数据库

🐬语法格式

CREATE DATABASE [IF NOT EXISTS] 数据库名称

例如：CREATE DATABASE K1; 创建数据库K1，如果这个数据库存在会报错

例如：CREATE DATABASE IF NOT EXISTS K2; 在库K2不存在的时候创建，有就不生效

在创建数据库后，MySQL.会在存储数据的data目录中创建一个与数据库同名的子目录（即k1），同时会在mydb目录下生成一个 db.opt 文件，保存数据库选项，打开data\k1\db.opt文件，如下所示。
default-character-set=latin1
default-collation-Latin1——swedish_ci
mydb 数据库的默认字符集为 latin1，校对集为 Latin1_swedish_ci

删除数据库

🐬语法格式

DROP DATABASE 数据库名称;

例如：DROP DATABASE K1;删除名字为K1的数据库，如果不存在会报错

例如：DROP DATABASE K2;删除名字为K2的数据库，如果不存在就什么事情都没发生

修改数据库编码

🐬语法格式

ALTER DATABASE K1 CHARACTER SET utf8;

修改数据库 K1 的编码为 utf8。注意，在 MySQL 中所有的 UTF-8 编码都不能使用中间的 “-” ，即 UTF-8 要书写为 UTF8。

FLUSH PRIVILEGES; 			#####立马数据生效

数据类型

MySQL 与 Java、C 一样，也有数据类型MySQL 中数据类型主要应用在列上。

常用类型：

int：整型

double：浮点型，例如 double(5,2)表示最多 5 位，其中必须有 2 位小数，即最大值为 999.99；

decimal：泛型型，在表单线方面使用该类型，因为不会出现精度缺失问题；

char：固定长度字符串类型；(当输入的字符不够长度时会补空格)

varchar：固定长度字符串类型；

text：字符串类型；

blob：字节类型；

date：日期类型，格式为：yyyy-MM-dd；

time：时间类型，格式为：hh:mm:ss

timestamp：时间戳类型；

创建表

🐬语法格式

CREATE TABLE 表名(
列名 列类型,
列名 列类型,
......
);

例如：

CREATE TABLE B1(
	ID	INT(5),
	NAME	VARCHAR(10),
    AGE	CHAR(2)
);

在MySQL中，若创建的数据表未指定字符集，则数据表及表中的字段将使用默认的字符集latin1。

因此，若用户插入的数据中含有中文，则会出现错误提示。

为为了解决以上中文插入的问题，通常在创建数据表时添加表选项，设置数据表的字符集。

CREATE  TABLE [IF NOT EXISTS] 表名 (字段名 字段类型[字段属性] ...) [DEFAULT] {CHARACTER SET[CHARSET} [=] utf8;

删除表

🐬语法格式

DROP TABLE 表名;

例如：DROP TABLE B1; 删除表B1

查看表结构

🐬语法格式

DESC 表名;

修改表结构

🐬语法格式

修改表名

ALTER TABLE 旧表名 RENAME TO 新表名;

添加列

ALTER TABLE 表名 ADD (列名 varchar(100));

删除列

ALTER TABLE 表名 DROP 列名;

修改列名

ALTER TABLE 数据表名 CHANGE 旧字段名 新字段名 字段类型 [字段属性];

例如：ALTER　TABLE　B1 CHANGE name user CHAR(5);

修改列的数据类型

ALTER TABLE 表名 表选项 = 值;

例如：ALTER TABLE B1 MODIFY ID CHAR(2);将表B1的ID字段数据类型更改为CHAR(2)

DML：数据操作语言

添加数据

🐬语法格式

INSERT INTO 表名(列名 1,列名 2, …) VALUES (值 1,值 2,…);;

INSERT INTO 表名 VALUES(值 1,值 2,…);

例如：INSERT INTO B1(ID,NAME,AGE) VALUES('1','Z3','19');

例如：INSERT INTO B1 VALUES('2','li4','20');

修改数据

🐬语法格式

UPDATE 数据表名 SET 字段名1 = 值1[,字段名2 = 值2] [WHERE 条件表达式];

例如：UPDATE b2 SET NAME='ZHANG3' WHERE NAME='Z3'

例如：UPDATE b2 SET name=’zhangSanSan’, age=’32’, gender=’female’ WHERE id=’11’;

若没有where条件，那么表中对应的字段全部都会被修改

删除数据

🐬语法格式

DELETE FROM 数据表名 [WHERE 条件表达式];

例如：DELETE FROM B1 WHERE ID ='1';删除id为1的数据行

DCL：数据控制语言

创建用户

🐬语法格式

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

删除用户

🐬语法格式

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

在 MySQL5.x 版本之后 DROP USER 语句可以同时删除一个或多个MySQL中的指定用户，并会同时从授权表中删除账户对应的权限行。在 MySQL5.x 之前的版本中，在删除用户前必须先回收用户的权限。其中，账户名与创建用户的格式相同，由 用户名@主机地址 组成。

下面以删除 mysql.user 中test7用户为例进行演示。
DROP USER IF EXISTS test7;
在不添加 IF EXISTS 关键字时，若删除了一个不存在的用户，则该语句的执行会发生错误；在添加后，会在删除不存在的用户时生成一个警告作为提示。其中，在删除账户时，如果省略主机地址，则默认为“%”。

当 DROP USER 语句删除当前正在打开的用户时，则该用户的会话不会被自动关闭。只有在该用户会话关闭后，删除操作才会生效，再次登录将会失败。另外，利用已删除的用户登录服务器创建的数据库或对象不会因此删除操作而失效。

设置密码

🐬语法格式

当新安装mysql时root无密码时可以添加skip-grant-tables无密码登录

vim /etc/my.cnf 
###添加
skip-grant-tables

修改root密码

先以无密码登录数据库

-- 对于 MySQL 5.7.6 及更高版本
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

-- 对于旧版本
UPDATE mysql.user SET authentication_string=PASSWORD('new_password') WHERE User='root';
FLUSH PRIVILEGES;

成功后注释最后一句

密码策略

##遇到错误 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements 时，这意味着您尝试设置的新密码不符合 MySQL 服务器配置的密码策略要求。


SET GLOBAL validate_password_length = 4;  -- 将最小密码长度设置为4
SET GLOBAL validate_password_policy = LOW; -- 将密码策略设置为最低要求
##重新尝试更改密码

FLUSH PRIVILEGES;

用户授权

🐬语法格式

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

例如：grant all on . to 'z3'@'%'; 授权给z3在任何库任何表所有权限

撤销权限

🐬语法格式

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

查看用户权限

🐬语法格式

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

例如：show grants for 'z3'@'%';

DQL：数据查询语言

DQL执行顺序 FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT（编写顺序）

语法顺序

SELECT
	字段列表
FROM
	表名字段
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后的条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

基础查询

🐬语法格式

SELECT * FROM 表名;

(* ：通配符，表示所有列)

查询指定列

SELECT 列名 1, 列名 2, …列名 n FROM 表名;

例如：SELECT id, name, age FROM b1; 查询b1表中id,name,age列

查询正在执行的语句

SHOW PROCESSLIST;

这条语句用于显示当前数据库服务器上的所有活动进程列表。这可以帮助数据库管理员了解哪些查询正在执行，以及它们的执行状态。

条件查询

条件查询就是在查询时给出 WHERE 子句，在 WHERE 子句中可以使用如下运算符及关键字：

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
<>或!=	不等于
BETWEEN … AND …	在某个范围内（含最小、最大值）
IN(…)	在in之后的列表中的值，多选一
LIKE 占位符	模糊匹配（_匹配单个字符，%匹配任意个字符）
IS NULL	是NULL
=	等于

逻辑运算符	功能
AND 或 &&	并且（多个条件同时成立）
OR 或	或者（多个条件任意一个成立）
NOT 或 !	非，不是

🐬语法格式

SELECT * FROM 表名 WHERE 条件;

例如：SELECT * FROM B1 WHERE ID ='1';查询b1表中id为1的数据行

-- 年龄等于30

select * from employee where age = 30;

-- 年龄小于30

select * from employee where age < 30;

-- 小于等于

select * from employee where age <= 30;

-- 没有身份证

select * from employee where idcard is null or idcard = '';

-- 有身份证

select * from employee where idcard;

select * from employee where idcard is not null;

-- 不等于

select * from employee where age != 30;

-- 年龄在20到30之间

select * from employee where age between 20 and 30;
select * from employee where age >= 20 and age <= 30;

-- 性别为女且年龄小于30

select * from employee where age < 30 and gender = '女';

-- 年龄等于25或30或35

select * from employee where age = 25 or age = 30 or age = 35;

select * from employee where age in (25, 30, 35);

-- 姓名为两个字

select * from employee where name like '__';

-- 身份证最后为X

select * from employee where idcard like '%X';

模糊查询

🐬语法格式

SELECT 字段 FROM 表 WHERE 某字段 Like 条件

其中关于条件，SQL 提供了两种匹配模式：

% ：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。

_ ：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。

举例说明查询姓名由 5 个字母构成的学生记录 SELECT * FROM stu WHERE sname LIKE '_ _ _ _ _';

字段控制查询

去掉重复记录

去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如 emp 表中 sal 字段就存在相同的记录。当只查询 emp 表的 sal 字段时，那么会出现重复记录，那么想去除重复记录，需要使用 DISTINCT：

SELECT DISTINCT sal FROM emp;

查看雇员的月薪与佣金之和

因为 sal 和 comm 两列的类型都是数值类型，所以可以做加运算。如果 sal 或 comm 中有一个字段不是数值类型，那么会出错。

SELECT *, sal+comm FROM emp;

comm 列有很多记录的值为 NULL，因为任何东西与 NULL 相加结果还是 NULL，所以结算结果可能会出现 NULL。下面使用了把 NULL 转换成数值 0 的函数 IFNULL：

SELECT *, sal+IFNULL(comm,0) FROM emp;

给列名添加别名

在上面查询中出现列名为 sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为 total： SELECT *, sal+IFNULL(comm,0) AS total FROM emp; 给列起别名时，是可以省略 AS 关键字的：

SELECT *, sal+IFNULL(comm,0) total FROM emp;

排序查询

ASC 升序（金字塔：上面小）

DESC 降序（倒金字塔：下面大）

查询所有学生记录，按年龄升序排序

SELECT * FROM stu ORDER BY sage ASC;

或者

SELECT * FROM stu ORDER BY sage;

查询所有学生记录，按年龄降序排序

SELECT * FROM stu ORDER BY age DESC;

查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

SELECT * FROM emp ORDER BY sal DESC ,empno ASC;

聚合查询（聚合函数）

聚合函数是用来做纵向运算的函数：

COUNT()：统计指定列不为 NULL 的记录行数；

MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为 0；

AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为 0；

COUNT：当需要纵向统计时可以使用 COUNT()

 #统计多少条数据
SELECT COUNT(*) FROM b1;  
  #统计id=1的个数
select count(*) from b2 where id =1 ;

SUM 和 AVG：当需要纵向求和时使用 sum()函数。

#查询所有雇员月薪和：
SELECT SUM(sal) FROM b1;
#查询所有雇员月薪和，以及所有雇员佣金和：
SELECT SUM(sal), SUM(comm) FROM b1;
#查询所有雇员月薪+佣金和：
SELECT SUM(sal+IFNULL(comm,0)) FROM b1;
#统计所有员工平均工资：
SELECT SUM(sal), COUNT(sal) FROM b1;
#或者
SELECT AVG(sal) FROM b1;

MAX 和 MIN

#查询最高工资和最低工资：
SELECT MAX(sal), MIN(sal) FROM emp;

分组查询

分组查询 当需要分组查询时需要使用 GROUP BY 子句，例如查询每个性别人数，这说明要使用部分来分组。

MariaDB [k2]> select gender,count(*) from b2 group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 女     |        7 |
| 男     |        9 |
+--------+----------+
2 rows in set (0.00 sec)

分页查询

当查询显示数据过多时可以使用分页查询指定显示行数

🐬语法格式

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

例如：

-- 查询第一页数据，展示10条
SELECT * FROM b1 LIMIT 0, 10;
-- 查询第二页
SELECT * FROM b1 LIMIT 10, 10;

注意事项

起始索引从0开始，起始索引 = （查询页码 - 1） * 每页显示记录数

分页查询是数据库的方言，不同数据库有不同实现，MySQL是LIMIT

如果查询的是第一页数据，起始索引可以省略，直接简写 LIMIT 10

数据库常见问题

解决MySQL、MariaDB新建用户后无法登录问题

问题描述：新建用户授予权限后刷新配置发现不管在本地还是远程都死活登录不上。

解决方案：MySQL中默认存在一个用户名为空的账户，只要在本地，可以不用输入账号密码即可登录到MySQL中。而因为这个账户的存在，导致了使用密码登录无法正确登录。

删除空账号即可

MariaDB [mysql]> select host,user,password from mysql.user;
+-----------------------+--------+-------------------------------------------+
| host                  | user   | password                                  |
+-----------------------+--------+-------------------------------------------+
| localhost             | root   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost.localdomain | root   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1             | root   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1                   | root   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost             |        |                                           |
| localhost.localdomain |        |                                           |
| %                     | root   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| %                     | z3     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------------------+--------+-------------------------------------------+
11 rows in set (0.00 sec)

MariaDB [mysql]> drop user ''@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select host,user,password from mysql.user;
+-----------------------+--------+-------------------------------------------+
| host                  | user   | password                                  |
+-----------------------+--------+-------------------------------------------+
| localhost             | root   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost.localdomain | root   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1             | root   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1                   | root   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost.localdomain |        |                                           |
| %                     | root   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| %                     | z3     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------------------+--------+-------------------------------------------+
10 rows in set (0.00 sec)

MariaDB [mysql]> Ctrl-C -- exit!
Aborted
[root@localhost ~]# mysql -uz3 -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

posted @ 2025-04-28 10:15 DYF-linux 阅读(62) 评论(0) 收藏举报

刷新页面返回顶部

DYF

Mysql 基础操作

数据库

概述

数据库技术的发展

数据库相关的人员

SQL语言

SQL语句

DDL：数据定义语言

创建数据库

删除数据库

修改数据库编码

数据类型

创建表

删除表

查看表结构

修改表结构

修改表名

添加列

删除列

修改列名

修改列的数据类型

DML：数据操作语言

添加数据

修改数据

删除数据

DCL：数据控制语言

创建用户

删除用户

设置密码

修改root密码

密码策略

用户授权

撤销权限

查看用户权限

DQL：数据查询语言

基础查询

条件查询

模糊查询

字段控制查询

排序查询

聚合查询（聚合函数）

分组查询

分页查询

数据库常见问题

解决MySQL、MariaDB新建用户后无法登录问题

公告