MySQL 使用标准 SQL 检索和处理数据,体积小、开源、免费,易于快速部署。正是因为这些特点,使得其在互联网行业,特别是 Web 应用方面使用相当广泛。至今最新的版本已到 8.0。
一 基本操作
MySQL 和 SQL Server 不同,它没有官方的图形化操作管理软件,它主要通过命令行的方式操作。
本文主要介绍 MySQL 编程相关的知识,安装和部署不在本文讲解范围,对于 MySQL 的安装和部署,请自行查阅官方文档或其他资料。
1,基本命令
系统级-登录和退出
1 net start mysql80(服务名)2 net stop mysql803 /*启动 mysql 服务,不同版本的 MySQL 服务名默认不一致,但安装的时候可以自己指定*/
4 mysql -h 主机地址 -u 用户名 -p 用户密码5 /*登录 MySQL 服务器,如果是在本地登录可以省略主机地址*/
6 exit
7 /*退出登录*/
数据库-常用命令
1 show databases;--查看所有数据库的列表
2 use dbname;--选择数据库
3 show tables;--查看当前库所有表的列表
4 show columns from tablename;--查看某个表的所有字段信息
5 describe tablename;--另一种查看表结构的方式
6 show status;--查看服务器状态信息
7 show create database/table dbname/tbname;--查看创建数据库/表的语句
8 show grants;--查看当前用户权限列表
9 show errors/warnings;--查看服务器错误和警告
2,SQL基础语法
MySQL 采用标准 SQL 语法,他们同样分为 DDL、DQL、DML、DCL。
3,注释
MySQL 使用 # 标记单行注释,/* */ 标记多行注释。
1 #这是单行注释2 /*
3 这是多行注释4 */
5 --这也是单行注释
使用双横线注释时,双横线后面至少需要一个空格。
4,语句结束
由于 MySQL 使用命令行的方式操作数据库,所以对于每一条命令,我们必须明确的指定结束符。MySQL 默认的语句结束符是分号";"。
1 语句 1;2 语句 2;3delimiter $4 /*通过 delimiter 指定新的结束标识*/
5语句3$6 语句4$
比如,我们在创建存储过程或函数时,函数或存储过程内部有多条语句,但创建过程本身也是一条语句,为了避免冲突,所以在创建存储过程和函数时一般会临时修改语句结束符。不要忘记在创建完后再把结束符改为默认的分号哦。
5,约束
MySQL 中对约束进行了分类:表约束和列约束,语法稍有差异。
1 create tablemyTable(2 col1 type[AUTO_INCREMENT],3 col2 type,4 col3 type,5 col4 type,6 col5 type NOT NULL,7 col6 type DEFAULT 'something',8 PRIMARY KEY(col1),9 FOREIGN KEY (col2) REFERENCESmytable1(somecol),10 UNIQUE(col3),11 CHECK(col4<200)12 );13 /*AUTO_INCREMENT 设置主键自增,相似的,MsSQL 使用IDENTITY(1,1) 设置自增*/
列约束只有 NOT NULL 和 DEFAULT,其他的均属于表约束。
6,查询相关
HAVING 关键字对聚合后的数据进行再筛选。WHERE 关键字是对原始数据的筛选。
1 select a.dep,avg(a.sal) as 平均工资 fromsalary a2 left joindepartment b3 on a.dep=b.dep4 where b.sta_num>10
5 having avg(sal)>10000;
6 /*连接查询部门和部门平均工资,部门人数在10以上,且平均工资大于10000*/
LIMIT 关键字,可以用来返回指定条数的记录,常用来做分页查询(MsSQL 使用 top 关键字)。limit 需要指定两个 int 类型的参数,分别表示从哪条记录开始(第一条数据索引为0),提取多少条记录。当从第一条数据开始时,第一个参数可以省略。
1 /*基础用法:返回指定数量记录*/
2 select * from department order by sta_num limit 10;3 /*查询人数最少的10各部门情况*/
1 /*分页查询*/
2 SET @num:=10;#定义用户变量,稍后会详细讲解;每页需要查询的记录数3 SET @page:=2;#需要查询第几页4 SET@index=(@page-1)*@num;#开始索引5 PREPARE mysql FROM "select * from country limit ?,?";#由于 limit 不支持使用变量,所以必须先使用 prepare预编译 SQL 语句,? 标识变量6 EXECUTE mysql USING @index,@num;#使用 execute 执行预编译的语句,并使用 using 传递参数,顺序必须和预编译时一致
子查询又称嵌套查询,是指一个 SELECT 语句的查询结果被嵌套在另一个语句之中,子查询既可以放在 FROM 关键字后面,也可以放在 WHERE 后面。外部的语句可以是 SELECT、INSERT、DELETE、UPDATE。
1 select * from(2 selec * from tableA where col1>(3 select min(col1) fromtableB4 )5 )a;
7,自定义排序
我们知道,order by 关键字可以实现数据的排序,添加 asc 或 desc 可以决定正序还是倒序,但这种排序方式是基于 ASCⅡ 或日期进行的,实际的业务需求可能比这复杂的多,这时候我们往往需要自定义排序,MySQL 的 field() 函数可以实现。
1 select * fromtableA2 order by field(col_name,'val1','val2','val3',...)
上述例子实现了 col_name 列按照指定的值顺序展示数据,需要注意的是,用来指定顺序的值应该是指定列中存在的值。
那些没有被指定顺序的值将在后面按默认顺序展示,如果还需要根据其他列排序,通过逗号分隔即可。
二 函数
1,字符函数
函数
功能
例子
说明
concat()
连接多个字符串
concat('a','b')
连接‘a’和‘b’,返回新的字符串‘ab’
substr()
截取字符串
substr('abcde',1,3)
在字符串‘abcde’中从第1个字符开始截取3个字符,并返回
lower()/upper()
字符串大小写转换
lower('abD')
把字符串‘abD’都转换成小写,并返回
replace()
替换指定字符
replace('abcda','a','x')
把字符串‘abcda’中的‘a’都替换成‘x’,并返回新的字符串
length()
计算字符串长度
length('abcde')
返回字符串‘abcde’的长度
trim()
去掉前后空格
trim(' abc ')
返回去掉了前后空格的新字符串
lpad()/rpad()
左右填充指定字符
lpad('abc',10,'-')
用‘-’填充字符串‘abc’至长度为10
instr()
子串第一次出现的索引
instr('abcde','c')
返回在‘abcde’中第一次出现‘c’的索引
2,数学函数
函数
功能
例子
说明
ceil()
向上取整
ceil(1.1)
2
floor()
向下取整
ceil(1.9)
1
round()
四舍五入
round(2.5)
3
mod()
取模
mod(21,4)
1
truncate()
截断小数
truncate(1.258,2)
1.25(多余的小数位直接截断)
rand()
返回随机数
rand()
随机数[0,1)
format()
截断位数
format(1.258,2)
1.26(最后一位四舍五入得来)
3,日期函数
函数
功能
例子
说明
now()
返回当前日期时间
now()
2020-05-15 16:27:59
year()/month()/day()
返回年/月/日
year(now())
2020
hour()/minute()/secend()
返回时/分/秒
hour(now())
16
curtime()
返回时间部分
curtime(now())
16:27:59
curdate()
返回日期部分
curdate(now())
2020-05-15
date_format()
格式化日期
date_format(now(),'%Y/%m/%d')
2020/05/15
str_to_date()
把字符串表示的日期转换成日期格式
str_to_date('2020-05-15 00:00:00','%Y%m%d')
2020/05/15
datediff()
返回两个日期之间的天数差
datediff(now(),'2020-01-01')
135
4,聚合函数
函数
功能
例子
说明
max()
返回最大值
max(列名)
返回一列的最大值
min()
返回最小值
min(列名)
返回一列的最小值
avg()
返回平均值
avg(列名)
返回一列的平均值
sum()
返回和
sum(列名)
返回一列的和
count()
返回数量
count(列名)
返回一列的数量(不计算NULL)
5,其他函数
A:IFNULL()
1 /*MySQL*/
2 select IFNULL(NULL,1)3 /*如果第一个参数是 NULL ,则返回第二个参数,否则返回第一个*/
4 /*MsSQL*/
5 select ISNULL(NULL,1)6 /*如果第一个参数是 NULL,则返回第二个参数,否则原样返回*/
B:IF()
1 IF(expr1,expr2,expr3)2 /*如果 expr1 的值为 true,则返回 expr2 的值,否则返回 expr3 的值。*/
C:其他
1 SELECT DATABASE()2 SELECTVERSION()3 SELECT USER()4 /*分别返回当前连接数据库,当前数据库版本,当前用户*/
三 编程对象
1,视图
A:创建
1 useworld;2 create viewv_13 as
4 select * from country limit 10;5 /*mysql8.0 自带一个 world 库*/
B:删除
1 drop view v_name;
C:修改
1 /*方式一:*/
2 alter viewv_name3 as
4 语句;5 /*方式二:*/
6 create or replace viewv_name7 as
8 语句;
2,变量
MySQL 中变量分为 4 种:系统变量,会话变量,用户变量,局部变量。
A:系统变量
1 SHOW GLOBAL VARIABLES;2 /*查看系统变量。当服务启动时,它将所有系统变量初始化为默认值。要想更改全局变量,必须具有super权限。*/
B:会话变量
1 SHOW SESSION VARIABLES;2 /*查看会话变量,只作用于当前连接,查看指定系统或会话变量使用:select @@+变量名*/
C:用户变量
1 set @name='张三';2 set @age:=12;3 select @sex='男';4 select @var:=max(col) from table
5 /*用户变量直接赋值即可,使用 select 赋值时只能使用 :=,这是为了让系统避免混淆赋值和等于操作,推荐都使用 :=*/
D:局部变量
1 1 declare age int default 0;2 2 set age:=12;3 3 select @age:=18;4 4 select max(col) into age from table;5 5 /*declare 语句专门用于定义局部变量,可以使用 default 来指定默认值,直接赋值时,使用 set,则不需要用 @ 标记变量,使用 select 则需要。如果使用查询赋值,则需要使用 select into.
局部变量只能在 BENGIN...END 语句块中使用,比如下面将要讲到的存储过程或函数中。如果如上例中那样直接运行将报错。
3,存储过程
A:创建
1 create procedureproc_name(参数列表)2 begin
3 语句块;4 end
参数包含三个部分:参数模式,参数名,参数类型。
参数模式也分为三种:IN(仅用来像存储过程输入),OUT(仅用来像存储过程外部输出),INOUT(即可以用来输入也可以用来输出)。
存储过程也可以没有参数,括号内留空即可。
B:调用
1 call proc_name(实参列表);
1 DELIMITER $2 CREATE PROCEDURE p_1(IN cname VARCHAR(20),OUT cregion VARCHAR(20))3 BEGIN
4 SELECT region INTO cregion FROMcountry5 WHERE country.name=cname;6 END$7 /*修改默认结束符后,这种语法只能在命令行模式使用,图形化界面会报错(SQLyog,navicat等)8 这里使用的依然是 MySQL8.0 自带的 world 库*/
9 CALL('Aruba',@cregion);
10 SELECT@cregion;
C:删除
1 drop procedure proc_name;
存储过程是一组预编译的 SQL 语句,使用存储过程,可以提高代码的重用性,简化操作,因为已经预编译好了 SQL 代码,执行时减少了预编译环节,可以提高执行效率,一般用来对表数据进行增删改。
4,自定义函数
A:创建
1 DELIMITER $2 CREATE FUNCTIONfunc_name(参数列表)3 RETURNS返回类型4 BEGIN
5 函数体6 END$
函数的参数和存储过程不同,只需要指定参数名和参数类型。并且单独使用关键字 RETURNS 指定返回值类型。由于函数必须返回值,所以函数内部必须使用 RETURN 关键字。RETURN 建议放在函数末尾,因为它会阻断后面语句的执行。
1 delimiter $2 create function f_1(num1 float,num2 float)3 returns float
4 begin
5 declare sum float default 0;6 set sum:=num1+num2;7 return sum;8 end$9 select f_1(1.1,2.2)$
MySQL 不支持存储过程和函数的修改,如果需要修改,必须删除原来的,并新建同名的过程名或函数名。
1 create percedure|function if not existsname2 ...3 /*创建之前先判断是否已存在,免得报错,删除时也应该检测是否不存在*/
5,事务
MySQL 中事务分为两类:显示事务和隐式事务。
隐式事务不用显式的指定开始和结束标记。比如 INSERT、UPDATE、DELETE语句都数据隐式事务,他们不需要指定开始和结束,执行就开启并自动提交。
显式事务是指有显式指定开始和结束标记的事务,这类事务一般由用户创建。由于系统默认是开启了事务自动提交功能的,所以在创建显式事务时我们必须先手动的关闭自动提交功能。
1 set autocommit=0;2 select @@autocommit;3 /*0为关闭,1为开启,该设置只在本次会话生效*/
语法:
1 start transaction;2 /*开启显式事务*/
3 语句1;4 savepoint point_name;5 /*设置事务断点,不是必须的*/
6 语句2;7 ...8 rollback topoint_name;9 /*回滚到指定断点,该断点之前的将不会被回滚*/
10 commit;11 /*提交事务,所有的操作被提交到数据库,源数据被修改*/
如果没有设置事务断点,那么回滚将对所有事务中的语句生效,当然 rollback 后也不需要指定断点名称了。提交和回滚不一定会同时存在。
事务一般是结合应用程序来使用的,直接在 MySQL 中无法测试。比如,在应用程序中,用户输入的信息要被提交到数据库,当用户输入完毕后,点击提交,我们可以设置等待时间,并提示用户是否确认提交。如果在等待时间结束之后用户无任何操作,或用户点击确认,那么我们直接提交事务,如果用户点击了取消,那么我们可以回滚事务。
需要注意的是,MySQL 默认配置了许多不同的存储引擎(又称表类型),不同的存储引擎使用了不同的存储技术,它们在插入、读取、修改、事务等方面各有优劣,你可以为不同的表或库配置不同的存储引擎。存储引擎不在本文的讲解范围,这里专门提到它是想指明另一个问题:并不是所有的 MySQL 存储引擎都支持事务,只有 InnoDB 引擎支持。所以,如果你希望在 MySQL 中使用事务,请为其配置 InnoDB 存储引擎。
四 流程控制
1,选择
A:CASE 结构
和其他编程语言一样,MySQL 中的 case 语句也是用来实现多分支结构的。不同的是,MySQL 中的 case 不仅可以用来做等值判断,还可以用来实现类似多重 IF 的逻辑。
等值判断:
1 case表达式或字段2 when 用来比较的值1 then返回的值1或语句1;3 when 用来比较的值2 then返回的值2或语句2;4 ...5 else返回的值n或语句n;6 end case;7 /*case 后面直接跟需要被做等值判断的表达式或表字段*/
区间判断:
1 case
2 when 判断条件1 then返回的值1或语句1;3 when 判断条件2 then返回的值2或语句2;4 ...5 else返回的值n或语句n;6 end case;7 /*case 后面不跟任何值或表达式,在 when 后通过条件表达式来判断所属情况*/
如果把 case 结构作为表达式,那么它可以被嵌套在其他语句中(then 后不需要分号),用在任何地方。如果作为独立的语句使用,那么必须在 begin...end 中。
B:IF 结构
IF 结构主要用来实现多重分支的结构,IF 结构只能包含在 BEGIN...END 中使用,不能作为独表达式使用,如果想作为表达式,可以使用 IF() 函数,但它只能实现两个分支。
1 if判断条件12 then语句1;3 elseif 判断条件24 then语句2;5 ...6 else7 then语句n;8 end if;9 /*与其他编程语言不同,这里的 elseif 中间没有空格*/
2,循环
MySQL 中的循环控制语句有两个:iterate(结束本次循环,执行下一次循环,类似其他语言中的 continue),leave(结束整个循环,类似 break)。
另外,循环控制语句必须指明当前退出的是哪个循环。所以,如果你要使用它们精准控制循环执行,那么你需要在循环语句最开始指明当前循环的别称。当然,这不是必须的。
A:while 循环
1 [循环名称:]while循环条件2 do3 循环体;4 end while[循环名称];5 /*通过判断循环条件来决定是否执行循环体*/
B:repeat 循环
1 [循环名称:]repeat2 循环体;3 until 循环条件4 end repeat [循环名称];5 /*先执行一次循环,再检查循环条件*/
C:loop 循环
1 [循环名称:]loop2 循环条件;3 end loop [循环名称];4 /*没有循环条件,只能通过循环控制语句结束*/
MySQL 中的循环语句都只能包含在 begin...end 中使用,所以一般在存储过程和函数中使用。