Mysql语句速览

醒着的睡着的人

已于 2024-04-26 07:29:13 修改

阅读量1.7k

点赞数 30

文章标签： mysql adb 数据库

于 2024-04-25 18:08:03 首次发布

本文链接：https://blog.csdn.net/weixin_74942037/article/details/138193986

版权

速览了，想必阁下定有一点基础或深厚功力，简单语句不做过多赘述，即使啥都不了解，也可让你有一定收获。

5、排序检索数据 ORDER BY | DESC

17、更新和删除数据 UPDATE DELETE

18、创建表和操纵表

19、视图(虚拟的表)

20、存储过程//(类似于其他语言的函数)

21、使用游标cursor

22、触发器(TRIGGER)

23、事务处理(transaction processing)

1 基本概念

1、database 保存有组织的数据的容器

2、table

3、column

4、datatype

5、row

6、primary key（主键，唯一标志，查找）

2 Mysql

1、Mysql(数据库软件)

various information(version,Server information,Service control,User administration)

3 USE SHOW

3.1 USE

USE user;

//使用某个库

3.2 SHOW

SHOW databases;

SHOW tables;

SHOW columns;

DESCREAT customers <=>SHOW columns FROM customers

SHOW STATUS;

HELP SHOW;

4 SELECT

SELECT id,job FROM users; 搜索多列从表中，列名用 ,隔开

SELECT * FROM users; *通配符

SELECT DISTINCT id FROM users; 关键字DISTINCT,之后搜索到的仅出现一次

SELECT id FROM users LIMIT 5; 限制返回5行(第一个参数默认为0)

SELECT id FROM users LIMIT 5,5; 从第五行开始返回五行

注：行0（其搜索的是行1）

LIMIT 3,4 <=> LIMIT 4 OFFSET 3<=>//等价

5、排序检索数据 ORDER BY | DESC

5.1 ORDER BY

SELECT id ORDER BY name;#(按某个列的数值排列)

SELECT id,price,name FROM products ORDER BY price,name;

（先按price排列之后再按name排列，name改变只会在price相同的情况下排列）

默认排序为升序，那怎样降序呢？

5.2 DESC

SELECT id,price,name FROM products ORDER BY price DESC;

DESC:使用时放列名之后，要使用的话必须放列名之后，多次使用必须多次输入

字句优先级：ORDER BY>LIMIT

6、过滤数据(WHERE)

6.1 一些注意

ORDER BY在WHERE之后

WHERE之后接条件

<>与!=相同（不等于）

6.2 BETWEEN---AND---

demo:

SELECT name FROM products WHERE id BETWEEN 4 AND 10;

6.3 NULL

NULL 无值（no value）

WHERE id IS NULL;

6.4 AND OR

AND 可连接双条件

OR 可连接双条件

AND高于OR

(-----)配合使用可使先进行OR

6.5 IN NOT IN

7、用通配符进行过滤 (LIKE)

%：匹配任意数量的字符(包括0个，但不包括NULL)

_ :只能匹配一个字符

LIKE:匹配整个字符串

8、正则表达式 REGEXP()

8.1 .

REGEXP中的特殊字符 '.':任意匹配单个字符

8.2 BINARY(区分大小写)

demo:WHERE name REGEXP BINARY '.OOOV';

8.3 |:OR

demo:

WHERE id REGEXP '1000 | 2000 | 3000';

8.4 匹配几个字符之一

WHERE name REGEXP '[123]ton';

[1|2|3]

[1-3]

8.5 匹配特殊字符

\\ :前导，转义符

\\f :换页

\\n:换行

\\r:回车

\\t:制表

\\v:纵向制表

表 8-1 字符类正则匹配

表8-2 重复元字符

WHERE name REGEXP '\\([0-9] sticks?\\)';

?:此符号之前的一个字符可有可无

WHERE name REGEXP '[[:digit:]]{4}';

'[0-9][0-9][0-9][0-9]'可代替[[:digit:]]{4}

表8-3 定位元字符

WHERE name REGEXP '^[0-9\\.]';

9、创建计算字段

9.1 Concat() Trim() R|L AS Now() 返回当前日期和时间

SELECT Concat(RTrim(id),'(',RTrim(name),')') AS products;

9.2 执行算数计算

SELECT 2*3;

SELECT num*price; (num可以是某个常数也可以是某列可用数值)

10、使用数据处理函数

10.1字符文本操作函数

Left():返回字符串左边的字符

Left(sql_strings,num_of_character)

Right():返回字符串右边的字符，用法和Left()一样

Length():返回字符串长度

Locate():寻找字串，返回出现的第一个字母的索引

LOCATE(substring, string, [start_position])

Lower():转化为小写

Upper():转化为大写

Trim() R|L:去除字符串两边的空格，R|L 右边或左边

Soundex():返回SOUNDEX值

WHERE Soundex(name)=Soundex('demo');

SubString():返回子串

SUBSTRING(input_string, start, length);

10.2 日期和时间处理函数

Date() Time()

WHERE Date(order_date)='2024-2-28';

日期和时间有特定的格式

10.3 数值处理函数

Abs() Sqrt()

Cos() Sin() Tan()

Exp():返回一个数的指数

Mod()

Mod(a,b) 返回a%b

Pi():返回圆周率

Rand():返回伪随机数

SELECT Rand()*3 //返回3以内的数，大概率是小数

floor():小数取整数

返回小于等于该值的最大整数

11、汇总数据

AVG()

AVG()忽略NULL的行

COUNT()

COUNT(*)不管是空值还是NULL

COUNT(column):忽略NULL

MAX()

MIN()

要求指定列名，忽略NULL

SUM()

12、分组数据

12.1GROUP BY

GROUP BY id

以id(value不同值各自分组)分组

WITH ROLLUP关键字：每组数值求和，与group by联合使用

HAVING关键字：分组过滤条件，where无法完成

12.2 select 子句优先级

SELECT>FROM>WHERE>GROUP BY>HAVING>ORDER BY>LIMIT

13、联结(表)

13.1 自然联结

FROM之后跟所需要的表

13.2 外部联结

使用关键字：INNER JOIN ON(内部联结)

SELECT id,name FROM custer INNER JOIN products ON custer.id=products.id;

OUTER JOIN (外部联结RIGHT|LEFT不能缺少)

SELECT id,name FROM custer LEFTB OUTER JOIN products ON custer.id=products.id;

14、组合查询

UNION | UNION ALL

前者默认去除重复行，后者可避免去除

15、全文本搜索

15.1 normal

FULLTEXT(column_name) //索引，定义之后MYSQL自动维护该索引

MATCH(column_name) AGAINST(key_word);


SELECT text FROM products WHERE MATCH(text) AGAINST('rabbit');//此句可以用LIKE替换

WHERE text LIKE '%rabbit%';

MATCH()与FULLTEXT()中的value应该一致

15.2 查询扩展 WITH QUERY EXPANSION

SELECT text FROM products WHERE MATCH(text) AGAINST('rabbit'WITH QUERY EXPANSION);

15.3 布尔文本搜索(in boolean mode)

SELECT text FROM products WHERE MATCH(text) AGAINST('rabbit -rope*'IN BOOLEAN MODE);//检索含有rabbit除掉带有rope开头的text

表15-1 全文本布尔操作符

+:包含，词必须存在

-:排除，词必须不能出现

>:包含，且增加等级

<:包含，且减少等级

():把词组成子表达式

~:取消一个词的排序值

*:词尾通配符

"":定义一个短语

WHERE MATCH(text) AGAINST('+root+kali'IN BOOLEAN MODE);//即含root又含kali

WHERE MATCH(text) AGAINST('root kali'IN BOOLEAN MODE);//含root或kali

WHERE MATCH(text) AGAINST('"root kali"'IN BOOLEAN MODE);//root kali作为一个词

WHERE MATCH(text) AGAINST('>root <kali'IN BOOLEAN MODE);//前者升级后者降级

WHERE MATCH(text) AGAINST('+root+(<kali)'IN BOOLEAN MODFE);//搜索root,kali并降级后者，两者都必须存在

16、插入数据

16.1 INSERT

INSERT INTO customers VALUES(NULL,'root','p@SSwoRd');//（排序id一般直接由MYSQL系统自动填值，但为防止空着，只能填NULL）//为表中每一列填值

以上省略了一些，以下比较详细，可任意改变顺序和省略，常用

INSERT INTO customers(id,name,password) VALUES(1,'kali','P@ssWord');//前列名后值

INSERT LOW_PRIORITY INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);

16.2 多行插入

笨方法;多次使用INSERT INTO语句,';'隔开

改良方法：

INSERT INTO customers(id,name,password) VALUES(1,'kali','p@ssWord'),VALUES(2,'hacker','PPass@word');

16.3 插入另一个表中的元素(INSERT SELECT)

INSERT INTO customers(id,name,password) SELECT id,name,password FROM newcustomer;//不要求两个表列名相同，数量相同即可

17、更新和删除数据 UPDATE DELETE

17.1UPDATE 更新数据

UPDATE table_name SET column_name='value' WHERE 条件；

若要修改多行，只需在SET WHERE之间加入即可，用','隔开

IGNORE关键字，在UPDATE之后直接加，其余不变，即使出错，也会恢复原值

17.2 DELETE 删除数据

DELETE FROM customer WHERE id=1001;//删除row内容，不会删除表,如果想要删除整个表中的数据可使用

TRUNCATE TABLE语句//删除原来的表并新建立一个表

TRUNCATE TABLE example_table;

18、创建表和操纵表

18.1 创建表

CREAT TABLE table_name

(

id int NOT NULL AUTO_INCREMENT,

name char(30) NOT NULL DEFAULT 'name_name',

PRIMARY KEY (id),//主值必须唯一，可设置多个列为primary key

)ENGING=InnoDB;//第三个为数据类型解释，可省略

IF NOT EXISTS//表名后添加(关键字)

AUTO_INCREMENT：每一个表只允许一个列为其类型，而且必须被索引(如使它成为primary key)

function:每进行一处insert此列值自动增量

SELECT last_insert_id();//返回最后一个AUTO_INCREMENT值

DEFAULT:给出默认值(可省略)

ENGING=：引擎类型(可省略，使用默认引擎)

使用不同引擎的表之间不能相互引用。

18.2 更新表

ALTER TABLE//谨慎使用，没有可返回上步的操作，可以通过在操作前备份以防止出现巨大错误,表名必须存在

ALTER TABLE column_name ADD style CHAR(20);//为表添加style列

ALTER TABLE column_name DROP COLUMN style;//从表中删除style列

18.3 删除表

DROP TABLE column_name;

18.4 重命名表

RENAME TABLE table_1 TO new_1_table,

table_2 TO new_2_table;

定义外键还需之后了解

19、视图(虚拟的表)

19.1 视图的基础了解

自我理解为：将SELECT语句返回的结果作为一个值，此值的类型为VIEW(视图)

作用：(一般用来搜索查找数据而不是更改数据，视图中的数据更改会使得基表中的数据改变，不如直接更改基表数值，若以硬要通过VIEW改变值会很不方便，有很多限制条件)

简化复杂的联结，提高性能
缩小用户的权限
重用SQL语句，保护数据
更改数据格式和表示

条件：

为视图取唯一名
不能索引，也不能有关联的触发器或默认值
可以和表一起连用

19.2 视图的使用

CREAT VIEW view_name AS SELECT column_1,column_2 FROM table_1,table_2 WHERE 条件;//(1)创建视图

SHOW CREAT VIEW view_name;//(2)查看创建视图VIEW的语句

DROP VIEW view_name;//(3)删除VIEW

CREAT OR REPLACE VIEW语句

CREATE OR REPLACE VIEW my_view AS

SELECT column1, column2

FROM my_table;

WHERE condition = 'value';//(4)若不存在同名视图，将创建一个视图，如果存在同名视图，则先删去已存在的同名视图，然后再创建(相当于覆盖)

20、存储过程//(类似于其他语言的函数)

20.1 存储过程的作用

安全性:减少讹误
完整性:简化复杂操作
简而言之，三个好处：简单，安全，高性能

20.2 存储过程的使用

demo1:简单的举例和解释

DELIMITER //



CREAT PROCEDURE function(

IN num INT)//()不可缺少，三要素：引入模式，名，类型

BEGIN

SELECT语句

END//



DELIMITER;

//一般不需要DELIMITER,若是MYSQL实用程序则需要

//除'\'外，都可以为分隔符

CALL function();

//使用此存储过程

DROP PROCEDURE function;

//删除存储过程，删除时存储过程不带()

demo2:扩充和填补

CREAT PROCEDURE function(

OUT p1 DECIMAL(10,2),

OUT p2 INT,

OUT p3 DECIMAL(5,2),

)COMMENT'This is a show'

BEGIN

DECLARE num DECIMAL(4,1) DEFAULT 543.2;

SELECT MIN(price) INTO p1 FROM products;

SELECT MAX(price) INTO p2 FROM products;

SELECT AVG(price) INTO p3 FROM products;

END;

OUT(从存储过程传出一个值) IN(传递给存储过程) INOUT(对存储过程传入和传出)

COMMENT:非必需，会在SHOW PROCEDURE STATUS;结果中展示

DECLARE:定义变量，可用DEFAULT设置默认值

SHOW PROCEDURE STATUS LIKE 'function_name';

INTO:位置不唯一，为CREAT中提供内容

demo3:MYSQL变量都必须以'@'开头

CALL function(@price_average);

SELECT @price_average;

--：单行注释

IF,ELSEIF,ELSE,THEN:

IF boolean_value THEN opetation_code;

21、使用游标cursor

游标CURSOR的理解：相当于鼠标的指示，直接用某一处的数据配合FETCH使用

21.1 游标的创建

CREAT PROCEDURE function()

BEGIN

DECLARE cursor_name

FOR

SELECT语句

END;//定义游标

21.2 打开和关闭游标

像其他语言中的文件一样，有打开就必须要有关闭。

OPEN cursor_name;//使用必须要OPEN

CLOSE cursor_name;//释放内存和资源

隐含关闭：如果不确定游标是否关闭，END将会将它关闭

21.3 使用游标数据 FETCH

DECLARE 语句的次序

局部变量必须定义在游标之前，句柄必须在游标之后定义

CREAT PROCEDURE function()
BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(5,1);

    DECLARE cursor_name CURSOR 
    FOR
    SELECT order_num FROM products;

    DECALRE CONTINUE HAHDLER FOR SQLSTATE '02000' SET done=1;--为终止循环设置条件
    CREAT TABLE IF NOT EXISTS table_name
    (order_num INT,total DECIMAL(6,2));

    OPEN cursor_name;

    REPEAT   --循环
        FETCH cursor_name INTO o;
        CALL ordertotals(o,1,t);
        INSERT INTO table_name(order_num,total)
        VALUES(o,t);
        UNTIL done END REPATE;   --终止循环 
        CLOSE cursor_name;
      END;

22、触发器(TRIGGER)

22.1 触发器的定义

事件发生时，自动执行一些operation

DELETE INSERT UPDATE//其他语句不支持触发器

22.2 创建/删除触发器

唯一的触发器名//在一个表中
触发器关联的表
触发器应该响应的活动
触发器何时执行

条件：只有TABLE才支持触发器

CREAT TRIGGER trigger_name AFTER INSERT ON table_name

FOR EACH ROW SELECT 'products added';

--在INSERT语句成功执行之后触发器将执行

--FOR EACH ROW 对于每一行

--如果触发器失败(BEFORE)则之后将不执行任何操作

DROP TRIGGER trigger_name;--删除触发器

//触发器不能修改或覆盖，所以更改的话，必须先得删除，再重新建立

22.3 INSERT触发器

再INSERT 触发器中可引用名为NEW的虚拟表，访问被插入的行

NEW值可被更新(允许更改被插入的值)

对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在执行之后将包含新的自动生成值

CREAT TRIGGER trigger_name AFTER INSERT ON table_name
FOR EACH ROW SELECT NEW.column_name1;
--生成新的订单号自动生成并保存在column_name1中，触发器从其column中获取返回
--测试代码
INSERT INTO table_name(n_date,n_id)
VALUES(NOW(),10002);

22.3DELETE触发器

DELETE中也有一个虚拟表OLD,此表全是只读，不可更改

CREAT TRIGGER trigger_name BEFORE DELETE ON table_name
FOR EACH ROW
BEGIN
    INSERT INTO otable_name(value1,value2)
    VALUES(OLD.value1,OLD.value2);
END;--删除前存档
--BEGIN和END语句，可使得添加多个操作，无坏处

BEFORE和AFTER

DELETE用前者更好，原因:如果由于某种原因不能存档，DELETE本身将会放弃

22.4 UPDATE触发器

可用OLD和NEW表(table)规则不变

CREAT TRIGGER trigger_name BEFORE UPDATE ON table_name
FOR EACH ROW SELECT NEW.a_value=UPPER(NEW.a_value);

23、事务处理(transaction processing)

23.1 了解transaction processing

自我理解：将一堆sql操作看为一个整体，若其中一条未完成则回退(rollback)

要么完全执行,要么不完全执行

术语

事务(transaction processing)：一组sql语句

回退(rollback)：撤销指定的sql语句

提交(commit)：将未存储的sql语句结果写入数据库表

保留点(savepoint)：临时占位符(place_holder)

23.2 控制事务处理

SELECT column_name FROM table_name;
START TRANSACTION;--开始事务处理
DELETE FROM table_name;
SELECT column_name FROM table_name;
ROLLBACK;--回退
SELECT column_name FROM table_name;

//事务处理只能回退INSERT UPDATE DELETE，其他语句可以出现在事务处理中但是不回退

23.3 COMMIT 隐含提交implicit commit

START TRANSAZTION
DELETE FROM table_name1 WHERE id=1001;
DELETE FROM table_name2 WHERE id=1001;
COMMIT;

只有上述两条DELETE语句全部执行时不出错时COMMIT才对两个表做出更改

COMMIT和ROLLBACK之后事务处理将自动关闭且这两个是全部回退，下面介绍部分回退修改

23.4 保留点savepoint

SAVEPOINT name1;
ROLLBACK TO name1;
--释放savepoint，报六点在事务处理完成时(ROOLBACK OR COMMIT 之后自动释放)，也可通过RELEASE SAVEPOINT明确地释放保留点

23.5更改默认的提交行为

一般情况下，对于表做出的更改立马生效(自动提交)

若要更改则

SET autocommit=0;--0即假

24、全球化和本地化

24.1基础了解

术语：

字符集：字母和符号的集合

编码：为某个字符集成员的内部表示

校对：为规定字符如何对比的指令

//字符集和校对取决于服务器、数据库和表级

24.2 使用字符集和校对

SHOW CHARACTER SET;--显示所有可用字符集及每个字符集的描述和默认校对
SHOW COLLATION;--显示所有可用的校对，以及适用的字符集
SHOW VARIABLES LIKE '%character';--用于显示与字符集和校对规则相关的服务器系统变量

24.3 CHARACTER SET|COLLATE(table)

CREAT TABLE table_name
(
column1 INT,
column2 VARCHAR(20),
column3 VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_2
) DEFAULE CHARACTER SET new1 COLLATE new1_2;

SELECT * FROM table_name ORDER BY column_1,column_2 COLLATE latin_new;

CAST()和CONVERT

25、安全管理

设置访问权限，避免使用root用户

25.1管理用户

MYSQL的所有用户账号和信息全部储存在mysql库user表user列中

25.2创建用户账号

CREAT USER user_name IDENTIFIED BY 'p@ssWord';

--还可通过使用GRANT和INSERTchuangjian用户

--重命名

RENAME USER user_name TO new_name;

--删除用户

DROP USER user_name;

MYSQL 5之前只能删除账号，不能删除相关权限，需要先用REVOKE删除权限再删除账号

25.3设置访问权限//在创建用户之后要设置权限

SHOW GRANT FOR user_name;--展示用户权限

GRANT设置所需三要素：

要授予的权限，数据库或表名，用户名

GRANT SELECT ON dbname.* TO user_name;

--为用户授予数据库的所有表以SELECT权限

REVOKE SELECT ON dbname.* TO user_name;

--取消权限

表25-4

ALL     除GRANT OPTION外的所有权限
ALTER 使用ALTER TABLE
ALTER ROUTINE 使用ALTER PROCEDURE和DROP PROCEDURE
CREATE 使用CREATE TABLE
CREATE ROUTINE 使用CREATE PROCEDURE
CREATE TEMPORARY 
TABLES
使用CREATE TEMPORARY TABLE
CREATE USER 使用CREATE USER、DROP USER、RENAME USER和REVOKE
ALL PRIVILEGES
CREATE VIEW 使用CREATE VIEW
DELETE 使用DELETE
DROP 使用DROP TABLE
EXECUTE 使用CALL和存储过程
FILE 使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION 使用GRANT和REVOKE
INDEX 使用CREATE INDEX和DROP INDEX
INSERT 使用INSERT
LOCK TABLES 使用LOCK TABLES
PROCESS 使用SHOW FULL PROCESSLIST
RELOAD 使用FLUSH
REPLICATION CLIENT 服务器位置的访问
REPLICATION SLAVE 由复制从属使用
SELECT 
使用SELECT 
SHOW DATABASES 
使用SHOW DATABASES 
SHOW VIEW 
使用SHOW CREATE VIEW 
SHUTDOWN 
使用mysqladmin shutdown（用来关闭MySQL） 
SUPER 
使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER 
和SET GLOBAL。还允许mysqladmin调试登录 
UPDATE 
使用UPDATE 
USAGE 
无访问权限

简化多次授权：

GRANT SELECT,INSERT ON dbname.* TO u9ser_name;

25.5更改口令

SET PASSWORD FOR user_name=PASSWORD(new_password);--PASSWORD()函数

26、数据库维护

26.1备份数据

命令行实用程序

mysqldump mysqlhotcopy

BACKUP TABLE或SELECT INTO OUTFILE

RESTORE TABLE//可复原

备份前可使用FULSH TABLES语句

26.2进行数据库维护

ANALYZE TABLE ltable_name;--检查表键是否正确

CHECK TABLE table_name;--用来针对许多问题对表进行检查//针对MYISAM表的方式

CHANGED：检查自最后一次检查以来改动过的表

EXTENDED：执行最彻底的检查

FAST：检查未正常关闭的表

MEDIUM：检查所有被删除的链接并进行键校验

QUICK：快速扫描

若MYISAM表访问产生不一致的结果，可能会需要REPAIR TABLE来修复相应的表，不常用，不易多用，否则会有更大的问题

OPTIMIZE TABLE：收回所用空间，从而优化性能

26.3诊断启动问题//在cmd

--help

--safe--mode：装载减去某些最佳配置的服务器

--verbose：显示全文本消息

--version

26.4查看日志文件

/data/hostname.err:包含启动和关闭问题以及任意关键错误细节可用--log-error(cmd)更改

/data/hostname.log:查询日志 --log(cmd)

/data/hostname-bin:二进制日志 --log-bin (MYSQL5前更新日志)

/data/hostname-slow.log:缓慢查询日志 --log-slow-queries(cmd)//在确定数据库何处优化时很有用

在使用日志时，可用FLUSH LOGS来刷新和重新开始所有日志文件

27、改善性能//最优化

SHOW VARIABLES;

SHOW STATUS；--查看当前设置

SHOW PROCESSLIST；--显示所有活动进程

KILL终结某个特定的进程

EXPLAIN语句解释

点赞私信即可免费获取学习资料哦！

醒着的睡着的人

关注

30
点赞
踩
46

收藏

觉得还不错? 一键收藏
1
评论
Mysql语句速览

/（排序id一般直接由MYSQL系统自动填值，但为防止空着，只能填NULL）//为表中每一列填值。作用：(一般用来搜索查找数据而不是更改数据，视图中的数据更改会使得基表中的数据改变，不如直接更改基表数值，若以硬要通过VIEW改变值会很不方便，有很多限制条件)若MYISAM表访问产生不一致的结果，可能会需要REPAIR TABLE来修复相应的表，不常用，不易多用，否则会有更大的问题。//(4)若不存在同名视图，将创建一个视图，如果存在同名视图，则先删去已存在的同名视图，然后再创建(相当于覆盖)
复制链接

扫一扫