MySQL基本操作

最新推荐文章于 2024-11-22 06:15:47 发布

A_YT

最新推荐文章于 2024-11-22 06:15:47 发布

阅读量908

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/ZX714311728/article/details/72438747

数据库专栏收录该内容

1 篇文章

订阅专栏

这篇博客详细介绍了MySQL的基本操作，包括检索、排序、过滤数据、正则表达式的应用、计算字段等。还深入讨论了函数、聚集函数、分组、联结、组合查询以及全文本搜索等高级功能。同时，讲解了如何插入数据、创建和操纵表、使用视图、存储过程，以及事务处理和安全管理等核心概念。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

初步知识

数据库是一个以某种有组织方式存储的数据的集合

DBMS是数据库管理系统也叫数据库软件

MySQL是一种DBMS

SQL语句是DBMS用来操控数据库的语言

MySQL主配置文件

/etc/my.cnf

MySQL数据库文件存放位置

/var/lib/mysql

MySQL日志文件

/var/log/mysqld.log

查看MySQL版本

rpm -qi mysql-server

rpm -qi mysql-devel

rpm -qi mysql

MySQL默认绑定的端口号

3306

SQL语句不分大小写

show databases;

ShOW databases;

上面两条语句相同

初步操作

登录MySQL

mysql -u root -p

退出MySQL

quit

exit

查看帮助

查看每条命令的用法

help 命令名

如：help create

创建数据库

create database zhouxin;

查看可用数据库

show databases;

打开数据库

use zhouxin;

返回当前数据库内可用的表

show tables;

显示表中每个字段（即每列的名字）的字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息

describe 表名;

检索

从表中检索某一列

select 列名 from 表名;

如：select Host from db;

从表中检索多列

SELECT 列名，列名，列名 FROM 表名; （列名之间加逗号，最后一个列名后不加）

如： SELECT Host, User, Drop_priv FROM db;

检索表的所有列

SELECT * FROM 表名

如：SELECT * FROM db;

只检索一列当中不同的行

SELECT DISTINCT 列名

FROM 表名

如：SELECT DISTINCT vend_id

FROM products;

返回检索当中的前n行

select 列名

from 表名

LIMIT n;

如：select prod_name from products limit 5;

返回检索当中从第m行开始的n行

select 列名

from 表名

LIMIT m,n; （不包括第m行）

如：select prod_name from products limit 3,6;

排序

按某一列排序

SELECT 列名

FROM 表名

ORDER BY 列名;

如： SELECT prod_name

FROM products

ORDER BY prod_price;

按多列排序

SELECT 列名

FROM 表名

ORDER BY 列名，列名，列名,...；

如： SELECT prod_id,prod_price,prod_name

FROM products

ORDER BY prod_price, prod_name;

按降序排列

SELECT 列名

FROM 表名

ORDER BY 列名 DESC;

如： SELECT prod_name, prod_price, prod_id

FROM products

ORDER BY prod_price DESC;

找出最大的一行

SELECT 列名

FROM 表名

ORDER BY 列名 DESC

LIMIT 1;

过滤数据

过滤行

SELECT 列名

FROM 表名

WHERE 列名 = 值；

(

还可为：

WHERE 列名 < 值

WHERE 列名 <= 值

WHERE 列名 > 值

WHERE 列名 >= 值

WHERE 列名 != 值

WHERE 列名 <> 值

WHERE 列名 BETWEEN 值 AND 值

)

如： SELECT prod_name, prod_price, prod_id

FROM products

WHERE prod_price = 2.50;

又如： SELECT prod_id, prod_name, prod_price

FROM products

WHERE prod_price BETWEEN 3 AND 5;

空值检查

SELECT 列名

FROM 表名

WHERE 列名 IS NULL；

如： SELECT cust_id

FROM customers

WHERE cust_email IS NULL;

ORDER BY和WHERE同时使用时，ORDER BY 必须位于 WHERE之后，否则会出错。

WHERE子句的AND子句

SELECT 列名

FROM 表名

WHERE where子句 AND where子句

如： SELECT prod_id

FROM products

WHERE vend_id = 1003 AND prod_price < 5;

WHERE子句的OR子句

SELECT 列名

FROM 表名

WHERE where子句 OR where子句

如： SELECT prod_id

FROM products

WHERE vend_id = 1003 OR prod_price < 5;

WHERE子句的混合AND OR子句

WHERE子句中既有AND又有OR时，最好应以小括号限定计算次序。否则默认是先计算AND再计算OR

如： SELECT prod_name, prod_price

FROM products

WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

WHERE子句的IN操作符

IN操作符完成与OR操作符相同的功能

如： SELECT prod_id

FROM products

WHERE vend_id IN (1002,1003);

NOT操作符

NOT操作符用来否定它之后所跟的所有条件

如： SELECT prod_id

FROM products

WHERE vend_id NOT IN (1002, 1003)

ORDER BY prod_name;

LIKE操作符

LIKE操作符使用通配符进行过滤

百分号（%）通配符、下划线（_）通配符

%通配符可以匹配0个或多个字符

_通配符只能匹配1个字符

%通配符

SELECT 列名

FROM 表名

WHERE 列名 LIKE '字符%'

(

还可为：

WHERE LIKE '%字符'

WHERE LIKE '字符%字符'

)

如： SELECT prod_id, prod_name

FROM products

WHERE prod_name LIKE '%anvil%';

_通配符

SELECT 列名

FROM 表名

WHERE 列名 LIKE '字符_'

(

还可为：

WHERE LIKE '_字符'

WHERE LIKE '字符_字符'

)

如： SELECT prod_id, prod_name

FROM products

WHERE prod_name LIKE '_ ton anvil';

使用正则表达式

基本字符匹配(.)

SELECT 列名

FROM 表名

WHERE 列名 REGEXP '匹配字符串'；

如： SELECT prod_id, prod_name

FROM products

WHERE prod_name REGEXP '.000'

ORDER BY prod_name;

'.000'中的.是正则表达式语言中一个特殊的字符，它表示匹配任意一个字符。

OR匹配(|)

SELECT 列名

FROM 表名

WHERE 列名 REGEXP '表达式|表达式'

ORDER BY 列名；

如： SELECT prod_name

FROM products

WHERE prod_name REGEXP '1000|2000'

ORDER BY prod_name;

匹配几个字符之一([ ])

SELECT 列名

FROM 表名

WHERE 列名 REGEXP '[要匹配的字符]表达式'

ORDER BY 列名；

如： SELECT prod_name

FROM products

WHERE prod_name REGEXP '[123] Ton'

ORDER BY prod_name;

[ ]是另一种形式的OR语句。事实上，正则表达式[123]Ton为[1|2|3]Ton的缩写

匹配指定字符之外的任何东西([^])

SELECT 列名

FROM 表名

WHERE 列名 REGEXP '[^要匹配的字符]表达式'

ORDER BY 列名；

如： SELECT prod_name

FROM products

WHERE prod_name REGEXP '[^123] Ton'

ORDER BY prod_name;

匹配范围([m-n])

SELECT 列名

FROM 表名

WHERE 列名 REGEXP '[m-n]表达式'

ORDER BY 列名；

如： SELECT prod_name

FROM products

WHERE prod_name REGEXP '[1-5] Ton'

ORDER BY prod_name;

匹配特殊字符(\\)

SELECT 列名

FROM 表名

WHERE 列名 REGEXP '\\特殊字符'

ORDER BY 列名；

如： SELECT prod_name

FROM products

WHERE prod_name REGEXP '\\.'

ORDER BY prod_name;

定位符(^[ ])

SELECT 列名

FROM 表名

WHERE 列名 REGEXP '^[匹配字符]'

ORDER BY 列名；

如： SELECT prod_name

FROM products

WHERE prod_name REGEXP '^[0-9\\.]'

ORDER BY prod_name;

^匹配串的开始。因此，^[0-9\\.]只在.或任意数字为串中第一个字符时才匹配它们.

定位符([ ]$)

SELECT 列名

FROM 表名

WHERE 列名 REGEXP '[匹配字符]$'

ORDER BY 列名；

如： SELECT prod_name

FROM products

WHERE prod_name REGEXP '[l]$'

ORDER BY prod_name;

$匹配串的结尾。因此，[l]$只在l为串中最后一个字符时才匹配它们.

计算字段

拼接字段(Concat( ))

SELECT Concat(拼接字段)

FROM 表名

ORDER BY 列名

如： SELECT Concat(vend_name, '(', vend_country, ')')

FROM vendors

ORDER BY vend_name;

上述Concat(vend_name, '(', vend_country, ')')语句中由4部分组成：vend_name, (, vend_country, )

RTrim去掉右边空格(Concat(RTrim(列), 语句))

SELECT Concat(RTrim(列), 拼接字段)

FROM 表名

ORDER BY 列名

如： SELECT Concat(RTrim(vend_name), '(', vend_country, ')')

FROM vendors

ORDER BY vend_name;

上述RTrim(vend_name)去掉vend_name右侧空格

类似的还有 LTrim：去掉串左侧空格；Trim：去掉串两侧空格

为计算字段创建别名(AS)

SELECT Concat(拼接字段) AS 别名

FROM 表名

ORDER BY 列名

如： SELECT Concat(vend_name, '(', vend_country, ')') AS vend_title

FROM vendors

ORDER BY vend_name;

执行算术计算

SELECT 列名, 列名, 列名

列名 * 列名 AS 别名

FROM 表名

WHERE WHERE子句；

如： SELECT prod_id, quantity, item_price,

quantity * item_price AS expanded_price

FROM orderitems

WHERE order_num = 20005;

列名 * 列名 AS 别名中的*号还可换为+, - , /号

小结

SELECT：

列、多列、*、DISTINCT、Concat( )、RTrim( )、列计算、函数、AS 别名

FROM：

表名

WHERE：

=、！=、<、<=、>、>=、BETWEEN AND、IS NULL、

OR、AND、IN、NOT、LIKE '%'、LIKE '_'、

REGEXP '.[123][^123][1-3]^[1][l]$\\.|'

ORDER BY：

列、多列、DESC

LIMIT：

行、行m,行n

函数

文本处理函数

Left() 返回串左边的字符

Length() 返回串的长度

Locate() 找出串的一个子串

Lower() 将串转换为小写

LTrim() 去掉串左边的空格

Right() 返回串右边的字符

RTrim() 去掉串右边的空格

Soundex() 返回串的SOUNDEX值

SubString() 返回子串的字符

Upper() 将串转换为大写

如： SELECT vend_name, Upper(vend_name) AS vend_name_upcase

FROM vendors

ORDER BY vend_name;

日期和时间函数

数值处理函数

聚集函数

AVG() 返回某列的平均值

COUNT() 返回某列的行数

MAX() 返回某列的最大值

MIN() 返回某列的最小值

SUM() 返回某列值之和

AVG( )：

SELECT AVG(列名) AS 别名

FROM 表名；

或

SELECT AVG(列名) AS 别名

FROM 表名

WHERE WHERE子句；

如： SELECT AVG(prod_price) AS avg_price

FROM products;

或

SELECT AVG(prod_price) AS avg_price

FROM products

WHERE vend_id = 1003;

COUNT( )

SELECT COUNT(*) AS 别名

FROM 表名;

或

SELECT COUNT(列名) AS 别名

FROM 表名;

如果指定列名，则指定列的值为空的行被COUNT()函数忽略，但如果COUNT()函数中用的是星号（*），则不忽略。

如：

SELECT COUNT(*) AS num_cust

FROM customers;

或

SELECT COUNT(cust_email) AS num_cust

FROM customers;

MAX( )

SELECT MAX(列名) AS 别名

FROM 表名

如：

SELECT MAX(prod_price) AS max_price

FROM products;

MIN( )

SELECT MIN(列名) AS 别名

FROM 表名

如：

SELECT MIN(prod_price) AS max_price

FROM products;

SUM( )

SELECT SUM(列名) AS 别名

FROM 表名

WHERE WEHRE子句;

如：

SELECT SUM(quantity) AS items_ordered

FROM orderitems

WHERE order_num = 20005;

组合聚集函数

SELECT COUNT(*) AS num_items,

MIN(prod_price) AS min_price,

MAX(prod_price) AS max_price,

AVG(prod_price) AS avg_price

FROM products;

分组

GROUP BY

对数据进行分组

SELECT 列名

FROM 表名

GROUP BY 列名；

如：

SELECT COUNT(*) AS TOTAL

FROM products

GROUP BY vend_id;

GROPU BY必须出现在WHERE语句之后，ORDER BY 语句之前（如果它俩有的话）

不要依赖GROUP BY的排序，如果要排序应该明确的用ORDER BY

HAVING

过滤分组

SELECT 列

FROM 表

GROUP BY 列

HAVING 条件；

如：

SELECT vend_id , COUNT(*) AS TOTAL

FROM products

GROUP BY vend_id

HAVING COUNT(*) >= 2;

WHERE和HAVING区别：WHERE在分组前过滤数据，HAVING在分组后过滤数据

SELECT子句顺序

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

SELECT 子查询

嵌套使用SELECT语句

SELECT 列

FROM 表

WHERE 列 IN （SELECT 子查询语句）；

如：

SELECT cust_id

FROM orders

WHERE order_num IN (SELECT order_num

FROM orderitems

WHERE prod_id = 'TNT2');

联结

外键：外键为某个表中的一列，它包含另一个表的主键值。

联结是一种机制，用来在一条SELECT语句中关联表。联结使得可以同时显示处于不同表中的内容

创建联结

SELECT 表A中的列，表B中的列

FROM 表A，表B

WHERE 表A.列x=表B.列x

如：

SELECT vend_name, prod_name, prod_price

FROM vendors, products

WHERE vendors.vend_id = products.vend_id

ORDER BY vend_name, prod_name;

应该保证所有的联结都有WHERE语句

联结多个表

SELECT 表A中的列,表B中的列,表C中的列

FROM 表A，表B，表C

WHERE 表A.列x = 表B.列x

AND 表A.列y = 表C.列y;

如：

SELECT prod_name, vend_name, prod_price, quantity

FROM orderitems, products, vendors

WHERE products.vend_id = vendors.vend_id

AND orderitems.prod_id = products.prod_id;

表也可以用AS建立别名

前面的联结称为“等值联结”

自联结

相同的表通过别名多次出现

SELECT A.列x，B.列y

FROM 表A AS 别名A，表A AS 别名B

WHERE A.列x = B.列x

AND A.列语句

如：

SELECT prod_id, prod_name

FROM products AS p1, products AS p2

WHERE p1.vend_id = p2.vend_id

AND p2.prod_id = 'DINTR';

自联结通常代替同一表中使用的子查询，比子查询快的多。

组合查询

UNION

可用UNION操作符来组合数条SQL查询。称为并

使用UNION可极大地简化复杂的WHERE子句，简化从多个表中检索数据的工作。

查询语句1

UNION

查询语句2；

如：

SELECT vend_id, prod_id, prod_price

FROM products

WHERE prod_price <= 5

UNION

SELECT vend_id, prod_id, prod_price

FROM products

WHERE vend_id IN (1001,1002);

使用UNION查询会自动取消在多个查询中重复的行，若想不取消重复的行，可使用UNION ALL

在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。

全文本搜索

Mysql中两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。

使用全文本搜索时，MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。

全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回。

SELECT 列

FROM 行

WHERE Match(列) Against('要搜索的词');

如：

SELECT note_text

FROM productnotes

WHERE Match(note_text) Against('rabbit');

Match指定要全文本搜索的行，Against指定要搜索的字符串。

查询扩展

利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词。

SELECT 列

FROM 行

WHERE Match(列) Against('要搜索的词' WITH QUERY EXPANSION);

布尔文本搜索

使用布尔搜索可以完成：要匹配的词；要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）；排列提示（指定某些词比其他词更重要，更重要的词等级更高）；表达式分组；另外一些内容。

如：

SELECT note_text

FROM productnotes

WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

搜索包含heavy的行，并且排除包含rope或以rope为开始单词的行

插入数据

插入一行

INSERT INTO 表名(

列1，

列2，

列3，

列4)

VALUES(

值1，

值2，

值3，

值4);

如：

INSERT INTO customers(

cust_address,

cust_city,

cust_country)

VALUES(

NULL,

'Los Angles',

'USA');

插入时，值的顺序和前面语句中列出现的顺序相同，但不需要与表中列的顺序相同。

插入多行

只要每条INSERT语句中的列名（和次序）相同，可以如下插入

INSERT INTO 表名(

列1，

列2，

列3，

列4)

VALUES(

值1，

值2，

值3，

值4)，

VALUES(

值12，

值22，

值32，

值42);

插入检索出来的数据

如：从另一个表中检索出来的数据插入这个表

INSERT INTO 表A(

列1，

列2，

列3，

列4)

SELECT 列1，

列2，

列3，

列4

FROM 表B

更新和删除数据

UPDATE

UPDATE 表

SET 列1 = 新值

WHERE WHERE子句；

如：

UPDATE customers

SET cust_email = 'elmer@fudd.com'

WHERE cust_id = 10005;

上述中，cust_email是要更新的列，通过cust_id = 10005确定了要更新哪一行，若没有这条语句，则更新所有行。

更新多列

UPDATE 表

SET 列1 = 新值，

SET 列2 = 新值

WHERE WHERE子句；

删除某列的值

将该列更新为NULL（如果允许NULL的话）即可

UPDATE 表

SET 列 = NULL

WHERE WHERE子句；

DELETE

DELETE删除整行而不是删除列。为了删除指定的列，请使用UPDATE语句。

DELETE FROM 表

WHERE WHERE子句；

MySQL没有撤销（undo）按钮。应该非常小心地使用UPDATE和DELETE。

UPDATE和DELETE语句最好必须具有WHERE子句，否则UPDATE会更新该列的所有行，DELETE会删除表的所有数据。

创建和操纵表

CREATE TABLE

如：

CREATE TABLE customers

(

cust_id int NOT NULL AUTO_INCREMENT,

cust_name char(50) NOT NULL,

cust_address char(50) NOT NULL,

cust_city char(50) NULL,

cust_email char(50) NULL,

cust_quantity int NOT NULL DEFAULT 1,

PRIMARY KEY (cust_id)

)ENGINE=InnoDB;

其中：

customers：表名

cust_*：列名

int, char(...)：数据类型

NOT NULL：这一列不允许有空值

NULL：这一列运行有空值

AUTO_INCREMENT：插入新行时，该列的值会自动增加

DEFAULT 1：该列的默认值，插入新行时，若不指定具体指，则用该默认值代替。

PRIMARY KEY：表的主键，主键不能允许空值；可以有多个主键：PRIMARY KEY(主键1，主键2)

ENGINE=InnoDB：采用哪种引擎。

在创建新表时，指定的表名必须不存在，否则将出错

每个表列或者是NULL列，或者是NOT NULL列。NULL为默认设置，如果不指定NOT NULL，则认为指定的是NULL。

主键值必须唯一。为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名。

主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引

以下是几个需要知道的引擎：

 InnoDB是一个可靠的事务处理引擎（参见第26章），它不支持全文

本搜索；

 MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）

中，速度很快（特别适合于临时表）；

 MyISAM是一个性能极高的引擎，它支持全文本搜索（参见第18章），

但不支持事务处理。

删除表

DROP TABLE 表名；

重命名表

RENAME TABLE 旧表名 TO 新表名；

视图

创建视图

CREATE VIEW productcustomers AS

SELECT cust_name, cust_contact, prod_id

FROM customers, orders, orderitems

WHERE customers.cust_id = orders.cust_id

AND orders.order_num = orderitems.order_num;

使用视图

SELECT cust_name, cust_contact

FROM productcustomers

WHERE prod_id = 'TNT2';

如上，productcustomers就是创建的视图。

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询

视图的最常见的应用之一是隐藏复杂的SQL，这通常都会涉及联结。

可以看出，视图极大地简化了复杂SQL语句的使用。利用视图，可一次性编写基础的SQL，然后根据需要多次使用。

存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。

事务处理

MyISAM和InnoDB是两种最常使用的引擎。前者不支持明确的事务处理管理，而后者支持。

事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

事务开始

START TRANSACTION

回退

ROLLBACK;

提交

COMMIT；

在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句

保留点

SAVEPOINT 保留点名字；

如：SAVEPOINT deletel;

回退到保留点

ROLLBACK TO 保留点名字；

如：

START TRANSACTION;

SELECT * FROM products;

SAVEPOINT deletel;

DELETE products;

SELECT *FROM products;

ROLLBACK TO deletel;

COMMIT;

安全管理

在现实世界的日常工作中，决不能使用root。应该创建一系列的账号，有的用于管理，有的供用户使用，有的供开发人员使用，等等。

MySQL用户账号和信息存储在名为mysql的MySQL数据库中，mysql数据库有一个名为user的表，它包含所有用户账号。user表有一个名为user的列，它存储用户登录名。

USE mysql;

SELECT user

FROM user;

创建用户账号

CREATE USER 账号 IDENTIFIED BY '密码';

如：

CREATE USER zhouxin IDENTIFIED BY '111111';

重命名账号

RENAME USER 旧账号名 TO 新账号名；

如：

RENAME USER zhouxin TO zx;

删除账号

DROP USER 账号名

如：

DROP USER zx;

更改账号的密码

SET PASSWORD FOR 账号 = Password('新密码');

如：

SET PASSWORD FOR zx = Password('111');

更改当前账号的密码

SET PASSWORD = Password('新密码');

设置账号权限

1.显示账号的当前权限

SHOW GRANTS FOR 账号名；

2.GRANT设置权限

GRANT 要授予的权限 ON 被授予访问权限的数据库或表 TO 账号名；

如：

GRANT SELECT ON crashcourse.* TO zx;

上面SQL意思是：授予账号zx对数据库crashcourse的所有表(.*)有SELECT权限。

3.REVOKE撤销权限

REVOKE 要撤销的权限 ON 被撤销访问权限的数据库或表 FROM 账号名；

如：

REVOKE SELECT ON crashcourse.* FROM zx;