mysql必知必会重点总结

目录

一、数据库

二、检索数据

三、排序检索数据

四、过滤数据(order语句位于where语句之后）

五、用通配符进行过滤

七、创建计算字段

八、使用数据处理函数

九、汇总数据

十、分组数据

十一、子查询

十二、联结表

十三、创建高级联结

十四、组合查询

十五、全文本搜索

十六、插入数据

十七、更新或删除数据

十八、创建和操纵表

十九、使用视图

二十、管理用户

二十一、数据库维护

二十二、读后感

---

数据库表的创建说明：

《MySQL 必知必会》创建样例表_mysql必知必会创建样例表_csndzt的博客-CSDN博客

数据库创建表详细说明：

《SQL必知必会》笔记1-样例表说明及建表语句 - 简书

一、数据库

1.选择数据库

crashcourse为数据库名
USE crashcourse;

2.显示数据库

SHOW DATABASES;

3.显示表

SHOW TABLES;

4.显示列表

user为列表名

SHOW COLUMNS FROM user;

每个字段返回一行，包括字段名，数据类型，是否允许NULL，键信息，默认值以及其他信息。

5.显示服务器状态信息

SHOW STATUS;

6.显示授权用户（所有用户或特定用户）的安全权限

SHOW GRANTS;

二、检索数据

1.检索所有列

SELECT * FROM 表名；

2 .搜索特定列

单列：SELECT 列名 FROM 表名；
多列：SELECT 列名1，列名2，...... FROM 表名；

3.检索不同行

SELECT DISTINCT 列名 from 表名；

4.限制结果

num类型:检索条数，开始位置

注意开始位置是从0开始的且这个语句会先从小到大排序再限制。

select 列名 from 表名 limit 检索条数
select 列名 from 表名 limit 开始位置，检索条数

5.使用完全限定表名

select 表名.列名 from 表名；

三、排序检索数据

单个列排序

select 列名 from 表名 order by 列名；

按多个指定列排序

select 列名1，列名2，列名3 from 表名 order by 列名1，列名2；

按指定列降序排序(升序换ASC，不指定的话都为ASC）

select 列名1，列名2，列名3 from 表名 order by 列名1 DESC；

先将产品按价格降序排序再对产品名排序

select prod_id,prod_price,prod_name from products order by
prod_price DESC,prod_name;

综合,order语句使用必须在from后limit前

select prod_price from products order by prod_price desc limit 1;

四、过滤数据(order语句位于where语句之后）

select pro_name,prod_price from products where prod_price = 2.5;

1、where语句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

2、范围值检查：

select prod_name,pro_price from products where prod_price
between 5 and 10;
select prod_name,pro_price from products where prod_price
between 条件1 and 条件2;

3、空值检查：

select prod_name from products where pro_price is null;

注：null--无值，与字段包含0，空字符串或仅仅包含空格不同。

4、OR操作符：

select prod_name,pro_price from products where prod_price
between 条件1 OR 条件2;

由于AND的优先级高于OR，所以采用以下方法连用：

select prod_name,pro_price from products where prod_price
between （条件1 OR 条件2）AND 条件3;

5、IN操作符：

select prod_name,prod_price from products where vend_id in(1002,1003) order by prod_name;

筛选vend_id 为1002或1003制造的所有产品

IN WHERE字句中用来指定要匹配值的清单关键字功能与OR相当。

6、NOT操作符

否定之后所有跟的任何条件

select prod_name,prod_price from products where vend_id not in(1002,1003) order by prod_name;

匹配1002和1003之外供应商的vend_id

五、用通配符进行过滤

1、百分号通配符

%：表示任何字符出现的任意次数，但不能匹配NULL

select type,currency from exchange_asset_details where type like 'transfer%';

执行这条语句时，检索任意以transfer起头的词。%告诉mysql接受transfer之后的任意字符，不管他有多少个字符。

select type,currency from exchange_asset_details where type like '%transfer%';

'%transfer%'表示匹配任何包含transfer的字符，不管前后是什么字符。

2、下划线通配符

用途与%一样，但下划线只匹配单个字符而不是多个字符

select type,currency from exchange_asset_details where currency like 'BT_';

六、使用正则表达式

1、REGEXP用法类似like，.表示匹配任意一个字符

例1：

输入：

select prod_name from products where prod_name REGEXP '1000'
order by prod_name;

输出

prod_name: jetpark 1000

例2:

输入：

select prod_name from products where prod_name REGEXP '.000'
order by prod_name;

输出：

prod_name:

jetpark 1000

jetpark 2000

2、REGEXP与like区别

like匹配整个列（匹配prod_name）而REGEXP只是是在列值内匹配（匹配jetpark或1000）

例如：prod_name: jetpark 1000

匹配区分大小写可使用BINARY关键字

3、进行OR匹配

select prod_name from products where prod_name REGEXP '1000 | 2000' order by prod_name;

4.匹配特定字符

输入：

select prod_name from products where prod_name REGEXP '[123] Ton' order by prod_name;

输出：

prod_name:

1 ton anvil

2 ton anvil

注意：'[^123] Ton'将匹配除这些字符以外的任何东西

[1-9]:匹配0-9

[a-z]：匹配a-z

5、匹配所有字符字段：

select prod_name from products where prod_name REGEXP '.' order by prod_name;

匹配查找特殊字符：

select prod_name from products where prod_name REGEXP '\\.' order by prod_name;

输出：

prod_name:

ball inc.

6.匹配多个实例

元字符	说明
*	0个或多个匹配
+	一个或多个匹配（等于{1,}
？	0个或1个匹配（等于{0,1}
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目范围（m不超过255)

例如：

select prod_name from products where prod_name REGEXP
'\\([0-9] sticks?\\)' order by prod_name;

输出：

prod_name:

TNT (1 stick)

TNT (2 sticks)

说明：sticks?匹配 stick和 sticks

7、匹配连在一起的4位数字：

select prod_name from products where prod_name REGEXP '[[:digit:]] {4}' order by prod_name;

输出：

prod_name

jeck 1000

jeck 2000

说明：[[:digit:]] 匹配任意数字{4}匹配4位

8.定位符

元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

select prod_name from products where prod_name REGEXP
'^[0-9\\.]' order by prod_name;

输出：

prod_name:

.5 ton an

1 ton an

2 ton an

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

七、创建计算字段

1、拼接两个字段用concat

select Concat(user_id,'(',currency,')') from exchange_asset_details order by user_id;

输出：

1001(BTC)

1001(BTC)

1001(USDT)

2、去空格拼接

select Concat(RTrim(user_id),'(',RTrim(currency),')') from exchange_asset_details order by user_id;

RTrim（）函数去掉值右边的所有空格

LTrim（）函数去掉值左边的所有空格

Trim（）函数去掉值两边的所有空格

3、对拼接的新列取别名（AS关键字赋予）

select Concat(RTrim(user_id),'(',RTrim(currency),')') As user_title from exchange_asset_details order by user_id;

输出：

user_title ：

1001(BTC)

1001(BTC)

1001(USDT)

3、执行算术计算

乘法和计算：

select user_id,quantity,fee,quantity*fee as total from exchange_asset_details where user_id = '1000000123';

八、使用数据处理函数

1.文本处理函数

Upper()将文本转换为大写

select vend_name,Upper(vend_name) as vend_name_upcase from vendors order by vend_name

输出：

vend_name vend_name_upcase

Anvils R Us ANVILS R US

LT Supplies LT SUPPLIES

ACME ACME

常用文本处理函数

函数	说明
Left（）	返回串左边的字符
Length()	返回串长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim()	去掉左边的空格
Ringt()	返回右边的字符
RTrim()	去掉右边的空格
Soundex()	返回串的SOUNDEX的值
SubString()	返回子串的字符
Upper()	将串转换为大写

2、日期和时间处理函数

如果只要日期就用date（）；只要时间用time（）；

select cust_id,order_num from orders where date(order_date) = '2005-09-01'

如果要9月所有订单：

select cust_id,order_num from orders where date(order_date) between '2005-09-01' and '2005-09-30'
或
select cust_id,order_num from orders where year(order_date)=2005 and month(order_date)=9

3、数值处理函数

函数	说明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的 余弦 值
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正 弦 值
Sqrt()	返回一个角度的正切

九、汇总数据

sql聚集函数

函数	说明
AVG（）	返回某列平均值
COUNT()	返回某列行数
MAX()	返回某列最大值
MIN()	返回某列最小值
SUM()	返回某列值之和

1、AVG函数

select AVG(prod_price) As avg_price from products

2、count函数

返回客户总数5

select count(*) AS num_cust from customers

返回具有电子邮件地址的客户总数3

select count(cust_email) as num_cust from customers

distact只能用于count(),不能用于count(*)

十、分组数据

where过滤行，having过滤分组

例如：

两个以上订单：

select cust_id,count(*) as orders from orders group by cust_id having count(*) >= 2

具有两个（含）以上，价格为10（含）以上的产品供应商：

select vend_id,count(*) as num_prods from products where prod_price >= 10 GROUP BY vend_id having count(*) >=2

检索总计订单价格大于等于50的订单的订单号和总订单价格且按总计订单价格排序：

select order_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >= 50 order by ordertotal

十一、子查询

子查询都是从内到外

select cust_name,cust_contact from customers where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2'));

十二、联结表

1、inner join .... on...用法：

select vend_name, prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

与子查询结果等同的用法：

select cust_name,cust_contact from customers,orders,orderitems where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num and prod_id = 'TNT2'

十三、创建高级联结

自然联结：

select c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price from customers as c,orders as o,orderitems as oi where c.cust_id = o.cust_id and oi.order_num= o.order_num and prod_id = 'FB';

外部联结(left or right) outer join on与内部联结inner join on的区别在于外部联结包含了那些没有下单，也就是单量为0的客户。

十四、组合查询

单个查询从中从不同的表返回类似结构的数据

对单个表执行多个查询，按单个查询返回数据

1、使用union

select vend_id,prod_id,prod_price from products where prod_price <= 5;
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);

组合上述查询：

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)

或者

select vend_id,prod_id,prod_price from products where prod_price <= 5 or vend_id in(1001,1002)

注：union从查询结果集中自动去除了重复的行，若要返回所有匹配行，则使用union all

十五、全文本搜索

最常用搜索引擎MyISAM：支持全文本搜索，InnoDB：不支持

match()指定被搜索的列，against()指定要使用的搜索表达式

select note_text, match(note_text) against('rabbit') as rank from productnotes

十六、插入数据

1、插入完整的行(根据表参数结构填充）

insert into customers values('10006','leiyuxing','100 Main Street','fujian','CA','90047','USA',NULL,NULL);

也可指定，顺序自定，值对应即可

insert into customers（cust_id,cust_name,cust_address,cust_city,cust_state, cust_zip,cust_country,cust_contact,cust_email)
values('10006','leiyuxing','100 Main Street','fujian','CA','90047','USA',NULL,NULL);

注：可以用insert low_priority info来降低插入优先级

2、插入多行

第一种方式：多个单行插入封号隔开

insert info 表名1（表参1）values(对应表参值1)；
insert info 表名1（表参1）values(对应表参值2)；

第二种方式：

insert info 表名1 （表参） values(对应表参值1)，(对应表参值2)；

3、插入检索出的数据

创建一个空的新表 custnew

CREATE TABLE custnew
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL ,
cust_address char(50) NULL ,
cust_city char(50) NULL ,
cust_state char(5) NULL ,
cust_zip char(10) NULL ,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL ,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

将 customers的数据导入新表操作：

insert into custnew (cust_id,cust_name,cust_address,cust_city,cust_state, cust_zip,cust_country,cust_contact,cust_email)
select cust_id,cust_name,cust_address,cust_city,cust_state, cust_zip,cust_country,cust_contact,cust_email from customers;

插入某些查询结果

十七、更新或删除数据

更新表中特定行

更新表中所有行

1、更新数据

1.1、update语句由3部分组成：要更新的表，列名和他们新值，确定要更新行的过滤条件

update customers set cust_email = 'lyx@qq.com' where cust_id ='10005';

注意，没有where语句将更新所有行，更新多个列时用，隔开。

为了防止即使发生错误也继续更新可以用update ignore

1.2、删除某个列的值，可设置它为NULL

update customers set cust_mail = null where cust_id=10005

2、删除数据

删除特定行

删除所有行

注意：删除指定列请用update

2.1、删除一行：

delete from customers where cust_id = 10006

2.2、删除所有行

truncate table

十八、创建和操纵表

1、创建表

create table 表名(
列名1 数据类型 约束,
列名2 数据类型 约束,
列名3 数据类型 约束 default 1,
primary key (列名)
)enging=innoDB;

1.1、NULL值是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，即在插入行或更新行时，该列必须有值。

1.2、AUTO_INCREMENT告诉mysql，本列每当增加一行时自动增量（要为主键），就是执行一次insert操作，mysql会自动对该列增量，且每个表只允许一个AUTO_INCREMENT。

1.3、如何使用AUTO_INCREMENT列获得这个值：

select last_insert_id();

1.4、指定默认值default

2、引擎类型

InnoDB：不支持全文本搜索,支持事物处理

MEMORY：功能等同MyISAM，数据存储在内存，适合临时表

MyISAM：支持全文本搜索，不支持事物处理

3、更新表

3.1、给表增加一个列

alter table vendors add vend_phone CHAR(20);

3.2、删除列

alter table vendors drop column vend_phone ;

4、删除表

drop table 表名;

5、重命名表

rename table a to b;

十九、使用视图

1、创建视图

create view

例如：

create view productcustomers as select cust_name,cust_contact,prod_id from customers,orders,orderitems where customers.cust_id = orders.cust_id and orderitems.order_num =orders.order_num;

2、查看创建视图

show create view viewname

3、更新视图

可以先drop再用create

或者直接用create or replace view

4、可以使用视图过滤不想要的数据、计算字段

5、如果视图中有以下操作则不能进行视图更新

分组（使用group by和having），联结，子查询，并，聚集函数（min(),count(),sum()等),distinct,导出（计算）列

二十、管理用户

1、查看用户

use mysql;
select user from user;

2、创建用户账号和密码

create user jack IDENTIFIED by '1234'

3、重命名一个用户账号

rename user jack to james;

4、删除用户

drop user james

drop只能删除用户账号，若要删除相关权限，则要先使用revoke删除与账号相关的权限，然后再用drop user删除账号

5、设置访问权限

5.1、查看用户权限

show grants for jack

GRANT， USAGE ON *.* TO 'jack'@'%',其中 USAGE ON *.* 表示根本没有权限，如果不指定主机名，则使用默认主机名%

5.2、设置权限

默认使用grant语句：

• 要授予的权限
• 被授予访问权限的数据库或表
• 用户名

例如：

grant select on crashcourse.* to jack;

说明允许用户在crashcourse.*（crashcourse数据库的所有表）上使用select。用户jack对crashcourse数据库中的所有数据具有只读访问权限。

查看下权限更改：

show grants for jack：
GRANT SELECT ON `crashcourse`.* TO 'jack'@'%'

5.3、撤销特定权限：

revoke select on crashcourse.* from jack;

5.4、grant和revoke的访问层次：

整个服务器：使用grant all 和revoke all；

整个数据库，使用on database.*;

特定的表：使用on database.table

 表 GRANT和REVOKE管理的权限

权限	描述
ALL PRIVILEGES	影响除 WITH GRANT OPTION 之外的所有权限
ALTER	影响 ALTER TABLE 命令的使用
ALTER ROUTINE	影响创建存储例程的能力
CREATE	影响 CREATE TABLE 命令的使用
CREATE ROUTINE	影响更改和弃用存储例程的能力
CREATE TEMPORARY TABLES	影响 CREATE TEMPORARY TABLE 命令的使用
CREATE USER	影响创建、弃用；重命名和撤销用户权限的能力
CREATE VIEW	影响 CREATE VIEW 命令的使用
DELETE	影响 DELETE 命令的使用
DROP	影响 DROP TABLE 命令的使用
EXECUTE	影响用户运行存储过程的能力
EVENT	影响执行事件的能力（从 MySQL5.1.6 开始）
FILE	影响 SELECT INTO OUTFILE 和 LOAD DATA INFILE 的使用
GRANT OPTION	影响用户委派权限的能力
INDEX	影响 CREATE INDEX 和 DROP INDEX 命令的使用
INSERT	影响 INSERT 命令的使用
LOCK TABLES	影响 LOCK TABLES 命令的使用
PROCESS	影响 SHOW PROCESSLIST 命令的使用
REFERENCES	未来 MySQL 特性的占位符
RELOAD	影响 FLUSH 命令集的使用
REPLICATION CLIENT	影响用户查询从服务器和主服务器位置的能力

（续）

权限	描述
REPLICATION SLAVE	复制从服务器所需的权限
SELECT	影响 SELECT 命令的使用
SHOW DATABASES	影响 SHOW DATABASES 命令的使用
SHOW VIEW	影响 SHOW CREATE VIEW 命令的使用
SHUTDOWN	影响 SHUTDOWN 命令的使用
SUPER	影响管理员级命令的使用，如 CHANGE 、 MASTER 、 KILL thread 、 mysqladmin debug 、 PURGE MASTER LOGS 和 SET GLOBAL
TRIGGER	影响执行触发器的能力（从 MySQL5.1.6 开始）
UPDATE	影响 UPDATE 命令的使用
USAGE	只连接，不授予权限

5.5、更改口令

set password for jack = password('12345')

二十一、数据库维护

1、analyze table 用来检查表键是否正确

analyze table 表名
check table 表名

2、显示所有活动进程

show processlist

可以用kill命令终结某个特定进程（管理员登录权限）

3、查看当前设置或状态

show variables或show status

二十二、读后感

作为一本介绍MySQL基础知识的书籍，《MySQL必知必会》非常适合初学者学习使用MySQL数据库。

第一部分介绍了MySQL的基础知识，如安装和配置。对于新手来说，这些都是非常重要的基础知识，能够快速掌握这些知识对于后续的学习非常有帮助。

第二部分介绍了如何使用MySQL操作数据，包括增加、删除、修改和查询数据等操作。这些都是MySQL的最基本的操作，作者使用了简单易懂的语言和实际的实例来讲解，读者可以轻松理解并实践。

第三部分则介绍了如何利用MySQL建立和管理表。这部分知识对于前端和后端开发来说非常重要，尤其是前端开发，需要对数据库有一定的了解。

最后，作者还介绍了如何使用MySQL进行高级操作，如联结表、子查询和事务等。对于一些高级应用开发，这部分知识也是非常有用的。

总之，《MySQL必知必会》是一本非常好的MySQL入门书籍，通过本书的学习，能够掌握MySQL的基础和进阶知识，非常适合初学者进行实践和学习。