前言

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言,由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

mysql的特点

20200331110731265

1
2
3
4
5
6
7
mysql> select workPicPath from smbms_user where workPicPath ='123  ' and 123='123a';
+-------------+
| workPicPath |
+-------------+
| 123         |
+-------------+
1 row in set, 1 warning (0.00 sec)

数据库基本信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT user, host FROM mysql.user; --用户信息查询

SHOW VARIABLES LIKE 'log_error';
SHOW VARIABLES LIKE 'general_log_file';
SHOW VARIABLES LIKE 'slow_query_log_file';

SHOW PROCESSLIST;  -- 查看当前正在执行的MySQL进程

--查看有没有开event_scheduler（事件调度器）
SELECT @@event_scheduler;
SHOW VARIABLES LIKE 'event%';

SHOW TRIGGERS; --查看所有计划任务（触发器）
DROP EVENT [IF EXISTS] event_name  --删除计划任务（触发器）

MySQL数据库管理语法

数据库的连接

使用 PHP 脚本连接 MySQL服务

mysqli_connect(host, username, password, dbname,port, socket);

断开与MySQL数据库的链接

mysqli_close ( connection )
提示：通常不需要使用 mysqli_close()，因为已打开的非持久连接会在脚本执行完毕后自动关闭。

数据定义

创建,删除数据库(结果以query而定)

mysqli_query(connection,query,resultmode);
‘DROP DATABASE RUNOOB’
‘CREATE DATABASE RUNOOB’
微信截图_20220122232819
也可以使用mysqli_multi_query(connection,query)执行一条或多条sql语句，然后将结果全部输出。

选择MySQL数据库

mysqli_select_db(connection,dbname);

创建,删除数据表

mysqli_query(connection,query,resultmode);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE <表名>(
      <列名> <数据类型>[ <列级完整性约束条件> ]
      [，<列名> <数据类型>[ <列级完整性约束条件>] ]
       ………
      [，<表级完整性约束条件> ]
 );

    CREATE TABLE `runoob_tbl`(       
    `runoob_id` INT UNSIGNED AUTO_INCREMENT,     
    `runoob_title` VARCHAR(100) NOT NULL,    
    `runoob_author` VARCHAR(40) NULL,     
    `submission_date` DATE,      
    PRIMARY KEY (  `runoob_id` ), UNIQUE(runoob_title)        
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据类型
20200331204116766
约束条件
QQ截图20240116232033

查看列：desc 表名;或describe 表名

删除表: DROP TABLE <表名>［RESTRICT| CASCADE］；
RESTRICT：删除表是有限制的。
欲删除的基本表不能被其他表的约束所引用(如视图等)
如果存在依赖该表的对象，则此表不能被删除
CASCADE：删除该表没有限制。
在删除基本表的同时，相关的依赖对象一起删除

添加，修改，删除表名，列名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
[ DROP <完整性约束名> ]
[ MODIFY <列名> <数据类型>]
[ ALTER COLUMN <列名> <数据类型> ]
[ RENAME to <新表名> ]
[ RENAME COLUMN <列名> to <新列名> ]
[ CHANGE column <旧列名> <新列名> <数据类型>]
    alter table table_name rename table_name1; --修改表名
    
    alter table table_name add  column_name varchar(10); --添加表列
    alter table table_name add name1 int,add name2 varchar(11);--添加两个字段
    
    alter table table_name drop  column_name; --删除表列
    alter table table_name drop column_name1,drop column_name2;--删除两个字段name1,name2
    
    alter table table_name modify (NOT NULL) name char(10) --修改表列类型 例:alter table interaction modify column rcontent longtext NULL;
    alter table table_name change name address  char(40)--修改列名
    
    alter table table_name change  column name name1 varchar(30)--修改表列名address-> address1

数据操纵

insert into 插入数据

1
2
3
4
INSERT
INTO <表名> (<属性列1>[，<属性列2 >…)
VALUES (<常量1> [，<常量2>]    …           )

如果常量是数字型可以不加引号,如果数据是字符型，必须使用单引号或者双引号
INSERT 插入多条数据
INSERT INTO table_name (field1, field2,...fieldN) VALUES (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......;

如果所有的列都要添加数据可以不规定列进行添加数据
insert 还可以还select配套使用

1
2
3
4
5
6
7
    INSERT 
    INTO <表名>  [(<属性列1> [，<属性列2>…  )]
    子查询；
INSERT INTO  Dept_age(Sdept,Avg_age)
SELECT  Sdept,AVG(Sage)
FROM  Student
GROUP BY Sdept;
REPLACE INTO

REPLACE INTO在执行插入操作时，如果发现某个行与表格中已有的行具有相同的主键或唯一性字段，则会删除原有的行，并插入新记录。这种情况下不会报“重复键”错误，因为它相当于先执行了一条 DELETE 再执行 INSERT。

1
2
3
4
 REPLACE INTO `score` (`username`,`listen`,`read`,`write`)
    -> values('mayylu',150,200,200);
Query OK, 1 row affected (0.00 sec)

Update set 修改数据

1
2
3
UPDATE  <表名>
SET  <列名>=<表达式>[，<列名>=<表达式>]…
[WHERE <条件>];
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 select username,listen,`read`,`write` from `score`;
+----------+--------+------+-------+
| username | listen | read | write |
+----------+--------+------+-------+
| 火华     |    100 |  201 |   212 |
| 火华     |     11 |  201 |   212 |
+----------+--------+------+-------+

UPDATE `score` SET `read`=REPLACE(`read`,'1','');
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

select username,listen,`read`,`write` from `score`;
+----------+--------+------+-------+
| username | listen | read | write |
+----------+--------+------+-------+
| 火华     |    100 |   20 |   212 |
| 火华     |     11 |   20 |   212 |
+----------+--------+------+-------+

DELETE 删除 数据

1
2
3
4
DELETE FROM <表名> 
[WHERE 子句]
[ORDER BY 子句]
[LIMIT 子句]

删除数据 ：delete from students where id=3;

select 查询语法

1
2
3
4
5
6
7
8
SELECT [ALL|DISTINCT]   
<目标列表达式> [别名] [ ，<目标列表达式> [别名]] … 
FROM   <表名或视图名> [别名]   [ ，<表名或视图名> [别名]] …
[WHERE <条件表达式>]
[GROUP  BY<列名1>
[HAVING  <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC] 
[LIMIT N][ OFFSET M]

在php中的使用

mysqli_fetch_array(result,resulttype); 函数从结果集中取得一行作为关联数组，或数字数组，或二者兼有。

result 必需。规定由 mysqli_query()、mysqli_store_result() 或 mysqli_use_result() 返回的结果集标识符。
resulttype 可选。规定应该产生哪种类型的数组。可以是以下值中的一个：
MYSQLI_ASSOC
MYSQLI_NUM
MYSQLI_BOTH

select简单的查询

1
SELECT DISTINCT Sno FROM SC;

如果没有指定DISTINCT关键词，则缺省为ALL,关键词DISTINCT可以去掉表中重复的行

聚集函数

as起别名

为数据表和字段起别名

  • “SELECT 字段名 AS 别名 FROM 数据表;”，可为字段指定别名；
  • “SELECT 字段名 FROM 数据表 AS 别名;”，可为表指定别名。

通过as构建视图

1
2
3
4
5
6
7
select REPLACE(username,'火',' - ') as username,listen,`read`,`write` from `score`;
+----------+--------+------+-------+
| username | listen | read | write |
+----------+--------+------+-------+
|  - 华    |    100 |  201 |   212 |
|  - 华    |     11 |  201 |   212 |
+----------+--------+------+-------+

有条件有限制的查询和输出

where子句

常用的查询条件
125858

嵌套查询常用的比较运算符
QQ截图20231028154756

LIKE 语法

[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]
如WHERE Sname LIKE '欧阳_';
like 匹配/模糊匹配，会与 % 和 _ 结合使用。

1
2
3
4
5
6
7
8
9
(1)'_'表示任意单个字符
(2)'%'表示任意数量的字符
(3)如果想在A中匹配字符'_'和'%'或者';'就需要转义。以'%'为例，即用'\%'(匹配一个%字符)。

如
where id=(1) || fl4g like binary 0x5f25   0x5f25对应的是%_I% ,即限制了查询数据的fl4g字段可以为任何数据
where id=(1) || fl4g like binary 0x25544925   0x25544925对应的是%TI% ,即限制了查询数据的fl4g字段中必须包含TI

like binary 区分大小写

A(表名) rlike B (表达式) ，表示A里边只要含有B即可，B中一般放正则表达式。

regexp 正则
regexp与其用法相似，即A(表名) regexp B (表达式)。

SELECT name FROM person_tbl WHERE name REGEXP ' '

截图_20220123180747

多重条件查询

逻辑运算符：

and用 && 代替
or用 || 代替 
xor用 | 代替 
not用 ! 代替 
AND和 OR来联结多个查询条件

优先级: NOT>AND>OR
可以用括号改变优先级
EXISTS
1
2
3
4
5
6
7
SELECT Sname
FROM Student
WHERE EXISTS(SELECT *
             FROM SC
              WHERE Sno=Student.Sno 
   			AND Cno= '1');

ORDER BY子句

MySQL ORDER BY 子句排序
ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。
ORDER BY 关键字默认按照升序[ASC]对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字

也可以使用ORDER BY n以“第n个字段”排序

group by 子句

SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING aggregate_function(column_name) operator value ORDER BY column1, column2

  • group by一般和聚合函数一起使用才有意义
    select deptno,sum(sal) from emp group by deptno;#按照deptno字段分组后再求和

  • 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
    HAVING 子句可以让我们筛选 组合后的各组数据。

  • 执行顺序
    1.执行where xx对全表数据做筛选，返回第1个结果集。
    2.针对第1个结果集使用group by组和，返回第2个结果集。
    3.针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。
    4.针对第3个结集执行having xx进行筛选，返回第4个结果集
    如果没有groupby的select语句执行结果只有一条，分组后的执行结果可以是多个

    LIMIT

    数据库应用常见功能之一就是分页，尤其是当数据量很大的时候，应用性能的高低，一定程度上和分页逻辑的效率紧密相关

    1
    2
    3
    4
    5
    select* from test LIMIT 3 (提取前三条数据)

    如： select * from test limit 2,1; (从第二行的下取一行，即取：第3行数据)

    select * from test LIMIT 3 offset 2;(第二行的下一行开始向下取3条数据，即取：3，4，5行的三条数据)

集合查询

并操作UNION

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中(联合查询，必须保证字段数一致)多个 SELECT 语句会删除重复的数据
union all则只是简单地将两个结果集合并后就返回结果。因此，如果返回的两个结果集中有重复的数据，那么返回的结果就会包含重复的数据

1
2
3
4
5
6
SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION SELECT *
       FROM Student
       WHERE Sage<=19;

交操作INTERSECT
差操作EXCEPT

join连接查询结果

1
2
3
4
5
 SELECT column_name(s)
FROM table1//左表
<xxx JOIN> table2//右表
ON table1.column_name=table2.column_name;

MYSQL 常用函数

聚合函数

聚合函数的操作对象是列，一般只用输入单个指定的字段名即可，可以与group by 配合使用

1
2
3
4
5
6
7
8
9
10
11
12
13
计数
COUNT（[DISTINCT|ALL] *）
COUNT（[DISTINCT|ALL] <列名>）
计算总和
SUM（[DISTINCT|ALL] <列名>）
计算平均值
AVG（[DISTINCT|ALL] <列名>）
最大最小值
MAX（[DISTINCT|ALL] <列名>）
MIN（[DISTINCT|ALL] <列名>）
-- 字符串聚合函数  
    #group_concat()                group_concat是针对列做的数据拼接，且group_concat自动生成逗号,一般和group by 结合使用比较多 (纵向)

字符函数

LEN() -     返回某个文本字段的长度
ASCII(s)-                     返回字符串s的第一个字符或字符s的 ASCII 码 

left（a，b)-                  指从左侧读取a的前b位    
right(a,b)-                   指从右侧读取a的前b位
substr(string,start,length) - 字符串截取
substr(string from start for length) 
substring()、mid() 函数与substring()函数功能类似

replace(string,search,replace)把object对象中出现的的search全部替换成replace  

greatest(n1,n2,n3.....)返回输入参数的最大值；
least(n1,n2,n3....)返回输入参数的最小值
  • 字符串多参数函数
    #CONCAT(s1,s2…sn) 字符串 s1,s2 等多个字符串合并为一个字符串(横向)
    CONCAT_WS() 使用指定的字符将目标字符串进行拼接

数字函数

CEIL()         进一（向上）取整
FLOOR()     舍一（向下）取整
EXP(x)-     返回 e 的 x 次方计算
LN(x)         返回数字的自然对数，以 e 为底        
FLOOR(x)-   返回小于或等于 x 的最大整数 
ROUND() -   对某个数值字段进行指定小数位数的四舍五入       
RAND()         返回 0 到 1 的随机数 
FORMA(X,D)  将数字格式化为带有千位分隔符、小数点和指定位数的字符串 
ABS()     绝对值计算

时间函数

NOW() -     返回当前的系统日期和时间
CURDATE()   则单独返回当前日

流程控制函数

IF(expr,v1,v2)-               如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2   
IFNULL(a,b)                   如果a不为null返回a，否则返回b；
benchmark(重复次数, 执行的函数) 用于测试函数或者表达式的执行速度。其运行返回值都是0，在表外显示时间。测试函数的
CASE WHEN a then b else c end        判断如果a为真返回b,为假返回c

SQL 功能性函数

1
2
3
4
5
6
sleep()-    休眠几秒 
SYSTEM_USER() 	返回当前用户 
DATABASE() 	    返回当前数据库名     
VERSION() 	    返回数据库的版本号   
LOAD_FILE(file_name)           读取文件并返回文件内容为字符串

mysql中的其他查询语法

handler(句柄)
handler语句并不具备select语句的所有功能。它是mysql专用的语句，并没有包含到SQL标准中。 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 打开一个表名为 tbl_name 的表的句柄
HANDLER tbl_name OPEN [ AS 别名 ]

可选：

创建索引：
mysql> create index handler_index on handler_table(c1);

# 1、通过指定索引查看表，可以指定从索引那一行开始，通过 NEXT 继续浏览
HANDLER tbl_name READ index_name { = | <= | >= | < | > } (value1,value2,...)
    [ WHERE where_condition ] [LIMIT ... ]

# 2、通过索引查看表
# FIRST: 获取第一行（索引最小的一行）
# NEXT: 获取下一行
# PREV: 获取上一行
# LAST: 获取最后一行（索引最大的一行）
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE where_condition ] [LIMIT ... ]

# 3、不通过索引查看表
# READ FIRST: 获取句柄的第一行
# READ NEXT: 依次获取其他行（当然也可以在获取句柄后直接使用获取第一行）
# 最后一行执行之后再执行 READ NEXT 会返回一个空的结果
HANDLER tbl_name READ { FIRST | NEXT }
    [ WHERE where_condition ] [LIMIT ... ]

# 关闭已打开的句柄
HANDLER tbl_name CLOSE

MySQL其他扩展语法

Mysql自带的预编译

第一步：
set @a=concat(‘sel’,’ect * from ?;’);在SQL中，‘@’通常用于表示变量。具体而言，‘@’后面跟着一个字符串，这个字符串就是一个变量名。
将命令concat(‘sel’,’ect * from ?;’)赋给变量@a
这里的命令即是连接sql语句，形成查表语句。

第二步：
prepare stmt from @@a
编译命令，将命令编译后命名为新的可执行文件stmt

第三步：
set @tablename=’table_a’;
EXECUTE stmt using @tablename;
运行可执行文件stmt

select into outfile 文件写入

可以使用select @@secure_file_priv查看用户是否有权限
在MySQL中，‘@@’表示系统变量。
SELECT INTO…OUTFILE语句把表数据导出到一个文本文件中，并用LOAD DATA …INFILE语句恢复数据。但是这种方法只能导出或导入数据的内容，不包括表的结构，如果表的结构文件损坏，则必须先恢复原来的表的结构。

1
2
3
4
select * from Table into outfile '/路径/文件名'
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'

路径目录必须有读写权限

LOAD DATA INFILE语法

语法主要是用于读取一个文件的内容并且放到一个表中,文件可以是客户端也可以是服务端的

1
2
load data infile "/data/data.csv" into table TestTable; --+和load_file类似
load data local infile "/home/data.csv" into table TestTable; --+不受secure_file_priv限制，文件路径由客户端提供，但是需要设置local_infile=ON

mysql中的特殊库

information_schema简介

QQ截图2021131028154756

sys库

mysql5.7增加sys系统数据库，这个库是通过视图的形式把information_schema和performance_schema结合起来
QQ截图201151556

performance_schema

QQ截图21111111256