一、数据库的基本操作
1. 连接数据库: mysql -u root -p
参数 | 描述 |
| 用户名 |
| 密码 |
| 输出版本信息并且退出 |
| 主机地址 |
2、启动和停止
3、修改用户密码:mysqladmin;
mysqladmin命令行: mysqladmin -u 用户名 -p 旧密码 password 新密码
4. 查看所有数据库: show databases;
5. 创建数据库: create database 数据库名字;
6、use 数据库的名称:
- 显示当前连接的数据库:
select database();
- 显示当前服务器版本:
select version();
- 显示当前日期时间:
select now();
- 显示当前用户:
select user();
7. 查看数据库的定义: how create database 数据库名;
8. 删除数据库(包含该数据库中所有的数据表和数据,且不提示): drop database 数据库名;
9. 查看MySQL存储引擎: how engines \G
10. 查看默认存储引擎: show variables like 'default_storage_engine%';
11. 修改数据库临时的默认存储引擎: set default_storage_engine=MyISAM;
12. 退出MySQL: EXIT
13、MySQL的目录结构
bin
目录:用于存储一些可执行文件include
目录:用于存储包含的一些头文件lib
目录:用于存储一些库文件share
目录:用于存储错误信息,字符集文件等data
目录:用于放置一些日志文件以及数据库my.ini
文件:数据库的配置文件
二、数据表的基本操作
1. 先选择数据库: USE<数据库名>;
2. 创建数据表:
->create table <数据表名>
->(
->字段名1 数据类型 [列级别约束条件] [默认值] ,
->字段名2 数据类型 [列级别约束条件] [默认值] ,
->.........
->[表级别约束条件]
->);
3. 查看数据表: show tables;
删除表: drop table 数据表名。
主键约束:主键列的数据唯一且不允许为NULL,主键能唯一地标识表中一条记录。分为单字段主键和多字段主键。
4. 单字段主键 :
在定义列的同时指定主键: 字段名 数据类型 primary key [默认值]
在列定义的后边指定主键: [CONSTRAINT <约束名 STH >]PRIMARY KEY [字段名]
PS:
删除主键约束: ALTER TALBE 表名 DROP PRIMARY KEY;
通过修改表的方式添加主键约束:ALTER TABLE 表名 ADD PRIMARY KEY (字段名);
5. 多字段联合主键:
PRIMARY KEY [字段1,字段2,...,字段n]
6. 使用外键约束:
- 外键是用来在两个表的数据之间建立链接,可以是一列或者多列,一个表可以有一个或者多个外键。
- 外键对应的是参照完整性,一个表的外键可以为空值,若不为空值,则每一个外键必须等于另一个表中主键的某个值。
- 作用:保持数据的一致性,完整性。
例如:部门表tb_dept的主键是id,在员工表中有一个键deptID与这个id关联。
图书类别表(父表)
CREATE TABLE bookcategory(
category_id INT PRIMARY KEY,
category VARCHAR(20),
parent_id INT
);
图书信息表(子表)
CREATE TABLE bookinfo(
book_id INT PRIMARY KEY,
book_category_id INT,
CONSTRAINT fk_cid FOREIGN KEY(book_category_id) REFERENCES bookcategory(category_id)
);
删除外键:
alter table bookinfo drop foreign key fk_bcid;
恢复关联:
add constraint fk_bcid foreign key(book_category_id)references bookcategory(category_id);
7. 使用非空约束格式 :字段名 数据类型 not null;
删除非空约束:alter table 表名 modify 字段名 数据类型;
通过修改表添加非空约束: alter table 表名 modify 字段名 数据类型 NOT NULL;
8. 使用唯一性约束:(允许为NULL,可确保一列或几列不出现重复值)
字段名 数据类型 unique;
[constraint <约束名 STH >] unique (<字段名>);
9. 使用默认约束格式:字段名 数据类型 default 默认值
10. 设置表的属性值自动增加(主键)格式:字段名 数据类型 auto_increment
测试自增列:
insert into bookcategory_tmp(category,parent_id)values('dadaqianduan',0);
修改自增列的起始值:
alter table bookcategory_tmp auto_increment = 15;
insert into bookcategory_tmp(category,parent_id)values('文学',0);
11、通过对表的行或列的数据做出限制,来确保表数据的完整性、一致性;
约束类型 | 非空约束 | 主键约束 | 唯一约束 | 默认约束 | 外键约束 |
关键字 |
|
|
|
|
|
12. 给表中输入数据:
这里使用insert声明向表中插入记录的方法,并不是SQL标准语法,不一定被其他的数据库支持,只能在MySQL中使用。
-> insert into tb_emp8(name ,salary)
-> values('lucy',1000),('lura',1200),('kevin',1500);
13. 查看数据表的数据:select*from tb_emp8;
14. 查看表基本结构语句:
describe/desc 表名;
查看表的字段信息,包括:字段名、字段数据类型、是否为主键、是否有默认值等。
NULL :表示该列是否可以存储NULL值。
Key: 表示该列是否已编制索引。
PRI:该列是表主键的一部分;
UNI:表示该列是UNIQUE索引的一部分;
MUL:表示在列中某个给定值允许出现多次。
Default:表示该列是否有默认值,如有的话值是多少;
Extra:表示可以获取的与给定列有关的附加信息。
15. 查看表的详细结构语句:show create table <表名\G>;
16. 修改数据表:
修改表名: alter table <旧表名> rename [to] <新表名>;
修改表字段的数据类型:alter table <表名> modify <字段名><数据类型>
修改表字段名:alter table <表名> change <旧字段名><新字段名><新数据类型>;
PS: 当数据库表中已经有数据时,不要轻易修改数据类型。
添加字段: alter table <表名> add<新字段名><数据类型>[约束条件] [first | afier 已存在字段名]
删除字段: alter table <表名> drop <字段名>;
修改字段的排列位置: alter table<表名> modify <字段1><数据类型> first |after <字段2>;
更改表的存储引擎: alter table <表名> engine=<更改后的存储引擎名>;
删除表的外键约束:alter table <表名>drop foreign key <外键约束名>
17. 删除数据表:drop table [if exists] 表1,表2,......表n;
PS : 参数“if exists”用于在删除前判断表是否存在,加上该参数后,若表存在,SQL语句会顺利执行,但会发出警告。
删除被其他表关联的主表:
alter table <子表名> drop foreign key <外键约束名>
drop table <父表名>
删除表中的数据:DELETE FROM tmp3;
18、查看表分区:SHOW PLUGINS;
创建表分区:使用partition by 类型(字段)
使用values less than 操作符定义分区。
create table bookinfo
(
book_id int,
book_name varchar(20)
)
partition by range(book_id)(
partition p1 values less than(20101010),
partition p3 values less than MAXVALUE
);
三、 数据类型和运算符
1. 数据类型
1) 整数类型:
INT(11) :
11 表示该数据类型指定的显示宽度,指定能够显示的数值中数字的个数。
显示宽度和数据类型的取值范围是无关的。
当数据大于显示宽度的值,只要该值不超过该类型整数的取值范围,数值仍可以插入,并且能够显示出来。默认显示宽度。
CREATE TABLE tmp1(x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT );
整型:TINYINT-1字节 ; SMALLINT-2字节 ; MEDIUMINT-3字节 ; INT-4字节 BIGINT-8字节;
2) 浮点数类型和定点数类型:
FLOAT :4个字节; DOUBLE : 8个字节;
CREATE TABLE tmp2(x FLOAT(5,1), y DOUBLE(5,1), z DECIMAL(5,1));
INSERT INTO tmp2 VALUES(5.12, 5.28, 5.233);
3) 日期与时间类型
向 tmp4 插入系统当前时间。
INSERT INTO tmp4 values (CURRENT_TIME),(NOW());
向 tmp4 插入系统当前日期。
INSERT INTO tmp4 values (CURRENT_DATE()),(NOW());
4)字符型
2. 常见运算符
1) 算数运算符
2)比较运算符
3)逻辑运算符
4)位运算符
5)运算符的优先级
例:
>-- mysql> SELECT price, price+10, price-10 , price*2, price /2, price%3 FROM tmp15;
>-- mysql> SELECT price, price>10, price<10, price !=10,price =10,price<=>10, price <>10 FROM tmp15;
>--mysql> SELECT price ,price BETWEEN 30 AND 80 ,GREATEST(price ,70 ,30),price IN(10,20,50,35) FROM tmp15;
问题列
问题1:MySQL中如何使用特殊字符?
特殊字符 单引号( ' )、双引号( " )、反斜线( \ )在MySQL中成为转义字符,在输入时候以反斜线符号( \ )为开头。
其他的特殊字符: 回车符( \r )、换行符( \n )、制表符( \tab )、退格符( \b )等。
问题2:MySQL可以存储文件吗?
MySQL中 BLOB 和 TEXT 字段类型可以存储数据量较大的文件,可以使用这些数据类型存储图像、声音或者大容量的文本内容,例如网页或文档。
但这些字段的处理会降低数据库的性能,如非必要,可以选择只存储文件的路径。
问题3:MySQL中如何执行区分大小写的字符串比较?
在Windows平台下,MySQL是不区分大小写的,因此字符串比较函数也是不区分大小写的。
若想执行区分大小写的比较,可在字符串前面添加 BINARY 关键字。
四、MySQL函数
概念:函数类似于java的方法(将实现某个功能的逻辑语句封装到方法中,对外暴露一个公开的名字,就是方法名)。
好处:
- 1.隐藏类具体功能的实现细节
- 2.提高代码的重用性
调用:
SELECT 函数名(实参列表) (实参和形参的个数、类型 必须一致)
FROM 表(根据需要:函数的参数用到表中的字段)
过程:
调用该函数,把函数的逻辑语句执行完,将它的返回值显示出来。
分类:
1.单行函数:处理数据使用。传入一个参数,处理完后,最终会有一个返回值
如:CONCAT(str1,str2,...)、LENGTH(str)、IFNULL(expr1,expr2)等
2.分组函数:简称 组函数
功能:做统计使用。传入一组参数,最终返回一个值。又称为统计函数、聚合函数。
1.单行函数: 输入一行,输出也还是一行,检索一行处理一次;
2.多行函数: 输入多行数据,输出是一个结果,检索出来的数据分成组后再进行处理;
根据参数类型不同,可以分为:
1)字符类函数:
是专门用于字符处理的函数,处理的对象可以是字符或字符串常量,也可以是字符类型的列;
函数 | 说明 | 分类 |
ASCII(char) | 返回字符的ASCII码值 |
|
BIT_LENGTH(str) | 返回字符串的比特长度 |
|
CONCAT_WS(sep,s1,s2...,sn) | 将s1,s2...,sn连接成字符串,并用sep字符间隔 |
|
QUOTE(str) | 用反斜杠转义str中的单引号 |
|
CHAR_LENGTH(str) | 返回值为字符串str所包含的字符个数。 | 计算字符串字符数函数 |
concat(s1,s2) | 将字符串s2连接到字符s1的后面; 若s1为null,则返回s2; 若s2为null,则返回s1; 若s1和s2都为空,则返回null | 计算字符串长度函数 |
INSERT(s1,x,len ,s2) | 返回字符串s1,其子字符串起始于x位置和被字符串s2取代的字符。 | 替换字符串函数 |
UCASE(str)或UPPER(str) | 返回将字符串str中所有字符转变为大写后的结果 | 字符大小写函数 |
LOWER(str)或LCASE(str) | 返回将字符串str中所有字符转变为小写后的结果 | 字符大小写函数 |
LEFT(s,n)或 RIGHT(s,n) | 返回字符串s开始的最左边(右边)n个字符 | 获取指定长度的字符串的函数 |
LPAD(s1,len,s2)或RPAD(s1,len,s2) | 返回字符串s1,其左边(右边)由字符串s2填补到len字符长度 | 填充字符串的函数 |
LTRIM(s)或RTRIM(s)或TRIM(s) | 返回字符串s,字符串左侧(右侧/两边)空格字符被删除 | 删除空格的函数 |
TRIM(s1 FROM s) | 删除字符串s中两端所有的子字符串s1。s1为可选项,未指定时,删除空格。 | 删除指定字符的函数 |
REPEAT(s,n) | 返回一个由重复的字符串s组成的字符串,字符串s的数目等于n。 | 重复生成字符串的函数 |
SPACE(n) | 返回一个由n个空格组成的字符串 | 空格函数 |
REPLACE(s,s1,s2) | 使用字符串s2替代字符串s中所有的字符串s1. | 替换函数 |
STRCMP(s1,s2) | 比较字符串s1和s2 | 比较字符串大小的函数 |
SUBSTRING(s,n,len)或MID(s,n,len) | 带有len参数的格式,从字符串s返回一个长度同len字符相同的字符串,起始于位置n。 | 获取子串的函数 |
LOCATE(str1,str)或POSITION(str1 IN str)或INSTR(str,str1) | 返回子字符串str1在字符串str中的开始位置 | 匹配子串开始位置的函数 |
REVERSE(s) | 将字符串s反转,返回的字符串的顺序和s字符串顺序相反。 | 字符串逆序的函数 |
ELT(N,字符串1,字符串2,字符串3……) | 若N=1,则返回字符串1。 若N<1或者>参数的数目,则返回值为NULL。 | 返回指定位置的字符串函数 |
FIELD(s,s1,s2,…) | 返回字符串s在列表s1,s2,…中第一次出现的位置。 找不到则为返回0;若s为NULL,则返回值为0. | 返回指定字符串位置的函数 |
FIND_IN_SET(s1,s2) | 分析逗号分隔的s2列表,如果发现s1,返回s1在s2中的位置 | 返回子串位置的函数 |
MAKE_SET(x,s1,s2…) | 返回由x的二进制数指定的相应位的字符串组成的字符串 | 选取字符串的函数 |
PS:
1) LENGTH(str)返回值为字符串的字节长度,使用utf8(UNICODE的一种变长字符编码,又称万国码)编码字符串集时,一个汉字为3个字节,一个数字或字符算一个字节。
2)REPEAT(s,n)函数,若n≤0,则返回一个空字符串。若s或n为NULL,则返回NULL。
#1.length获取参数长度'字节个数'
>-- mysql> SELECT LENGTH('john');
#2.CONCAT(str1,str2,...) 拼接字符串
>-- mysql> SELECT CONCAT(last_name,'_',first_name) AS 姓名 FROM employees;
#3.upper、lower
>-- mysql> SELECT UPPER('john');#小转大
>-- mysql> SELECT LOWER('JOHN');#大转小
2) 数字类函数:
主要用于执行各种数据计算,所有的数字类函数都有数字参数并返回数字值;
函数 | 说明 |
abs(n) | 返回n的绝对值 ;PI()返回圆周率的值,默认显示小数位数为6位数。 |
SQRT(x) | 返回非负数x的二次方根; |
RAND() | 返回一个随机浮点值v(0≤v≤1.0)。若已指定一个整数参数x,则它被用作种子值,用来产生重复序列。 RAND(x)带参数时,参数相同时,将产生相同的随机数。 |
ROUND(x) | 返回最接近于参数x的整数,对x值进行四舍五入。 |
sign(x) | 返回参数的符号,x的值为负、零和正时返回结构依次为-1、0或1. |
ceil(n) | 返回大于或等于数值n的最小整数 |
cos(n) | 返回n的余弦值,n为弧度 |
exp(n) | 返回e的n次幂,e=2.71828183 |
florr(n) | 返回小于或等于n的最大整数 |
log(n1,n2) | 返回以n1为底n2的对数 |
mod(n1,n2) | 返回n1除以n2的余数 |
power(n1,n2) | 返回n1的n2次方 |
LOG(x) | 返回x的自然对数,x相当于基数e的对数。 特例:LOG10(x) |
pound(n1,n2) | 返回舍入小数点右边n2位的n1的值,n2的默认值为0,这会返回小数点最接近的整数; 如果n2为负数,就舍入到小数点左边相应的位上,n2必须是整数 |
sign(n) | 若n为负数,则返回-1;若n为正数,则返回1;若n=0,则返回0 |
sin(n) | 返回n的正弦值,n为弧度 |
sqrt(n) | 返回n的平方根,n为弧度 |
trunc(n1,n2) | 返回结尾到n2位小数的n1的值,n2的默认设置为0, 当n2为默认设置时,会将n1截尾为整数,如果n2为负值,就截尾在小数点左边相应的位上 |
#round 四舍五入
'绝对值四舍五入,然后再去加正负号'
>-- mysql> SELECT ROUND(-1.65);
'小数点后保留几位'
>-- mysql> SELECT ROUND(1.567,2);
#ceil 屋顶
'向上取整',返回 >= 该参数的最小整数,大于里面最小的整数
>-- mysql> SELECT CEIL(-1.56);
#floor 地板
'向下取整',返回 <= 该参数的最大整数,小于里面最大的整数
>-- mysql> SELECT FLOOR(-9.99);
#truncate 截断
'小数点后保留几位'
>-- mysql> SELECT TRUNCATE(1.65,1);
rand:获取随机数(默认返回0-1之间的小数,无限接近于1,到不了1)
区间有开区间和闭区间,其中又分为全开区间( ),全闭区间[ ],
左开右闭区间( ] 和左闭右开区间 [ ),
开区间的意思是区间两处的端点值取不到,而闭区间的端点值就可以取到。
例如区间[2,6),他是一个左闭右开的区间,
那么在这2~6之间的数字我都可以取到,而且可以取到2,但不可以取到6.
3) 日期和时间类函数:
用于处理日期和时间的函数,可以实现计算需要的特定日期和时间;
日期类型的默认格式是“DD-MON-YY”,DD表示两位数字的“日”,MON表示3位数字的“月份”,YY表示两位数字的“年份”;
函数 | 说明 |
CURDATE()或CURRENT_DATE() | 返回当前的日期 |
CURTIME()或CURRENT_TIME() | 返回当前的时间 |
DATE_ADD(date,INTERVAL int keyword) | 返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化), |
DATE_FORMAT(date,fmt) | 依照指定的fmt格式格式化日期date值 |
DATE_SUB(date,INTERVAL int keyword) | 返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化) |
DAYOFWEEK(date) | 返回date所代表的一星期中的第几天(1~7) |
DAYOFMONTH(date) | 返回date是一个月的第几天(1~31) |
DAYOFYEAR(date) | 返回date是一年的第几天(1~366) |
DAYNAME(date) | 返回date的星期名 |
FROM_UNIXTIME(ts,fmt) | 根据指定的fmt格式,格式化UNIX时间戳ts |
HOUR(time) | 返回time的小时值(0~23) |
MINUTE(time) | 返回time的分钟值(0~59) |
MONTH(date) | 返回date的月份值(1~12) |
MONTHNAME(date) | 返回date的月份名 |
NOW() | 返回当前的日期和时间 |
QUARTER(date) | 返回date在一年中的季度(1~4) |
WEEK(date) | 返回日期date为一年中第几周(0~53) |
YEAR(date) | 返回日期date的年份(1000~9999) |
#now '返回系统当前日期+时间'
>-- mysql> SELECT NOW();
#查询入职日期为1992-4-3的员工信息
>-- mysql> SELECT * FROM employees WHERE hiredate = '1992-4-3';
#查询有奖金员工和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %Y年') 入职日期 FROM employees WHERE commission_pct IS NOT NULL;
4) 流程控制函数
#if函数:三元运算符
SELECT IF(expr1,expr2,expr3): 条件1;成立返回2;不成立返回3
IF()返回值为数字值或者字符串值。
IFNULL(v1,v2),假如v1不为NULL,则IFNULL() 的返回值为v1;否则其返回值为v2.
#2.case函数
使用一:switch-case的效果
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END
case 要判断的字段或表达式
when 常量1 then 要显示的值1 或要显示的语句1(语句要加;号,值不用)
when 常量2 then 要显示的值2 或要显示的语句2
.....
else(代表默认情况,不满足以上的条件时执行) 要显示的值n或语句n;
end(case的结尾)
案例:查询员工的工资,要求:
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门,显示的工资为原工资
SELECT
salary AS 原始工资,
department_id,
CASE department_id
WHEN 30 THEN salary * 1.1
WHEN 40 THEN salary * 1.2
WHEN 50 THEN salary * 1.3
ELSE salary
END AS 新工资
FROM
employees;
#3.case 函数的使用二:类似于 多重if 判断区间
Java中:
if(条件1){
语句1;
}else if(条件2){
语句2;
}
...
else{
语句n;
}
mysql中:
case
when 条件1 then 要显示的值1或语句1(如果是true就执行,不是换下一个)
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
案例:查询员工的工资情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
5)系统信息函数
函数 | 说明 |
DATABASE() | 返回当前数据库名 |
BENCHMARK(count,expr) | 将表达式expr重复运行count次 |
CONNECTION_ID() | 返回当前客户的连接ID |
FOUND_ROWS() | 返回最后一个SELECT查询进行检索的总行数 |
USER()或SYSTEM_USER() | 返回当前登陆用户名 |
VERSION() | 返回MySQL服务器的版本 |
SHOW PROCESSLIST; | 结果显示有哪些线程在运行,可查看当期那所有的连接数,也可查看当前的连接状态,帮助识别出有问题的查询语句等。 |
PS:SHOW PROCESSLIST; 若是ROOT用户,能查看所有用户的当前连接;若普通账号,则显示自己占用的连接;显示连接数量默认为100个,若查看全部,则SHOW FULL PROCESSLIST;
6)加密函数
函数 | 说明 |
AES_ENCRYPT(str,key) | 返回用密钥key对字符串str利用高级加密标准算法加密后的结果,调用AES_ENCRYPT的结果是一个二进制字符串,以BLOB类型存储 |
AES_DECRYPT(str,key) | 返回用密钥key对字符串str利用高级加密标准算法解密后的结果 |
DECODE(str,key) | 使用key作为密钥解密加密字符串str |
ENCRYPT(str,salt) | 使用UNIX crypt()函数,用关键词salt(一个可以惟一确定口令的字符串,就像钥匙一样)加密字符串str |
ENCODE(str,key) | 使用key作为密钥加密字符串str,调用ENCODE()的结果是一个二进制字符串,它以BLOB类型存储 |
MD5(str) | 计算字符串str的MD5 128比特校验和 |
PASSWORD(str) | 返回字符串str的加密版本,这个加密过程是不可逆转的,和UNIX密码加密过程使用不同的算法。 在MySQL服务器的鉴定系统中使用,不应用在个人的应用程序中。 |
SHA() | 计算字符串str的安全散列算法(SHA)校验和 |
7)转换类函数:
用于将数据从一种类型转换为另外一种类型;
函数 | 说明 |
chartorwida(s) | 该函数将字符串s转换为rwid数据类型 |
convert(s,aset[,bset]) | 该函数将字符串s由bset字符集转换为aset字符集 |
rowidtochar() | 该函数将rowid数据类型转换为char类型 |
to_char(x[,format]]) | 该函数实现将表达式转换为字符串,format表示字符串格式 |
to_date(s[,format[lan]]) | 该函数将字符串s转换为date类型,format表示字符串格式,lan表示所使用的语言 |
to_number(s[,format[lan]]) | 该函数将返回字符串s代表的数字,返回值按照format格式进行显示,format表示字符串格式,lan表示所使用的语言 |
8)格式化函数
函数 | 说明 |
DATE_FORMAT(date,fmt) | 依照字符串fmt格式化日期date值 |
FORMAT(x,y) | 把x格式化为以逗号隔开的数字序列,y是结果的小数位数 |
INET_ATON(ip) | 返回IP地址的数字表示 |
INET_NTOA(num) | 返回数字所代表的IP地址 |
TIME_FORMAT(time,fmt) | 依照字符串fmt格式化时间time值 |
9)自定义函数
1)创建函数:
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
2)调用函数: SELECT 函数名(参数名);
3)查看函数: SHOW FUNCTION STATUS;
4)删除函数:DROP FUNCTION IF EXISTS function_name;
函数:需要有返回值,可以指定0~n个参数。
(1)创建自定义函数
create function function_name([func_parameter])
returns type
[characteristics..] routine_body
Characteristics
指定存储函数的特性,取值举例:
sql security{definer|invoker}:指明谁有权限来执行。
definer : 表示只有定义者才能执行。
invoker : 表示拥有权限的调用者才可以执行,默认情况下,系统指定为definer。
comment 'string' : 注释信息,可以用来描述存储函数。
函数体是由sql代码构成,可以简单的sql语句。如果为复合结构需要使用begin...end
语句,复合结构可以包含声明,流程控制。
select length('hello');
select date_format(pubdate,'%Y-%m') from bookinfo;
delimiter //
create function ym_date(mydate date)
returns varchar(15)
begin
return date_format(mydate,'%Y-%m');
end//
delimiter;
使用(调用)自定义函数
select ym_date(pubdate) from bookinfo;
实例:创建函数
创建一个函数
delimiter $$ --定界符
--- 开始创建函数
create function user_main_fn(v_id int)
returns varchar(50)
begin
--定义变量
declare v_userName varchar(50);
--给定义的变量赋值
select f_userName info v_userName from t_user_main
where f_userId = v_id;
--返回函数处理结果
return v_userName;
end $$ --函数创建定界符
delimiter;
自定义函数的两个必要条件:参数、返回值。
create function function_name
returns
{string|integer|real|decimal}
routine_body
语法格式:
CREATE FUNCTION function_name([func_parameter])
RETURNS type
[characteristics … ] routine_body
- function_name : 函数名称
- func_parameter : 函数的参数列表
- RETURNS type : 指定返回值的类型
- Characteristics : 指定存储函数的特性
- routine_body : 函数体
(2)创建无参的自定义函数:
(3)复合结构体的函数:
-- 将语句结束符改为$$,为了防止下面的函数将;看成是语句的结束
DELIMITER $$
CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
RETURN
BEGIN
INSERT INTO table_1(username) VALUES(username);
LAST_INSERT_ID();
END;
-- 将分隔符改回来
DELIMITER ;
10)流程控制语句
常用的流程控制语句:
- IF条件判断语句-if
- CASE条件判断语句-case
- WHILE循环语句-while
- LOOP循环语句-loop
- REPEAT循环语句-repeat
问题列
1)如何改变默认的字符集?
修改配置文件。
在windows中,MySQL配置文件名称为my.ini,该文件在MySQL的安装目录下。
修改配置文件中default-character-set 和 character-set-server参数值,将其改为想要的字符集名称,如gbk,gb2312,latin1等,修改完后重新启动MySQL服务,即可生效。
可通过 SHOW VARIABLES LIKE 'character_set_%';查看当前字符集。
2)如何从日期时间值中获取年、月、日等部分日期或时间值?
MySQL中,日期时间值以字符串形式存储在数据表中,因此可以使用字符串函数分别截取日期时间值的不同部分。
3)如何区别父表和子表?
例1:1对多的情况。
产品表:产品ID, 产品名称, 产品类型
产品ID 产品名称 产品类型
1 敌敌畏 农药
2 加多宝 饮料
3 可乐 饮料
接下来我们把这个产品表拆分开来(实际开发中),因为在数据库设计中除了外键以外其他字段是不可以重复的(产生冗余),因为不可能你一个界面就设计一张表,你需要分析这样一对多的关系,便于管理维护:
产品表:产品ID,产品名称,产品类型ID
产品类型表:产品类型ID,产品类型名称
产品ID 产品名称 产品类型ID
1 敌敌畏 1
2 加多宝 2
3 可乐 2
产品类型ID 产品类型名称
1 农药
2 饮料
因为一个产品类型对应多个产品,是一对多的关系,所以我们把这个产品类型表的主键放到产品表中做外键,形成主外键的关联,那么产品表相当于继承了产品类型表的东西。
所以产品表是子表,产品类型表是父表。也有一种说法是主表和从表的说法。
【简单的讲:当两个表建立一对多关系的时候,"一"的那一端是父表,"多"的那一端是子表.
父表设置一个主键
子表设置一个外键
外键与主键相关联】
4)唯一约束和主键约束的区别?
- 一个表中可以有多个
unique
声明,但只能有一个primary key
声明 - 声明为
primary key
的列不允许有空值 - 声明为
unique
的列允许空值
5)还有一种比较棘手的数据库设计模式就是多对多的关系:
学生表:学生ID,学生名称。。。
选课表:课程ID,课程名称。。。
学生_选课表:学生选课ID,学生ID,选课ID。。。
解决方法: 采用递归表。