今天首先老师接着上周讲了一下内置函数,补充了一下!
1、 数学函数
2、 聚合函数
3、 字符串函数
字符串参数都可以替换成字段名
AscII():返回char类型数据中第一个对应的整型;
Char():输入0-255之间的整数,返回char类型;
Left(字符,长度):可以对字段内容进行截取
Right(字符,长度)
Trim
Ltrim
Rtrim
Rpad(’别填充对象’,最终的长度,填充内容):
Lpad :截取、填充
Replace(“被作用的对象”,被替换的内容,替换后的内容);
Concat:连接字符串,逗号作为分隔符
Substring(“被作用的对象”,开始截取位置,截取的长度);
开始截取的位置从1开始,>=1
……………………是负数,表示从后向前截取
Strcmp(字符1,字符2);按照字节比较
相等 0,大于 1,小于 -1
4、 日期和时间函数
Now();
Curtime()
Curdate()
Year() year(now()-出生日期)
练习:其他函数
5、 加密函数(自行练习)
6、 控制流函数
Ifnull()
Nullif()
If()
7、 格式化函数
Format:针对浮点
Date_format:日期格式化
Time_format:时间格式化
字符和时间日期需要单引号
8、 类型转换
Cast(要转换的对象 as 类型);
9、 系统信息函数
见表6.11
P114-129 记准确
二、语言结构
DDL:create alter drop
DML:insert delete update select(查询语言)
DCL:grant revoke等
三、常量
‘a’ 12 3.14
变量:保存运行过程中的临时结果
1)用户变量(和会话---连接服务器到退出服务器的一段时间)
@变量名
一定要初始化
Set @a=2;
Select @a;
Select 返回结果分几类:表,列,行,值(标量)
2)系统变量
@@VERSION
Select
接着又讲了新的内容,存储过程,运用了一些数据库的知识!
存储过程
1、 为什么要使用
Mysql服务器在缓存机制做了改进,使用了类似预处理的方式,由于没有编译器
因此mysql存储过程不会像外部语音(c)编写的程序运行的那么快.t提升速度
2、 要求:
Proc表
具有create routine(例程)的权限
返回标量值
隐含调用uec库名,删除数据库,存储过程也呗删除
3、 语法格式
Create procedure 存储过程的名字(参数)
特征描述
SQL语句体
4,例如
1) use 库名;
2) create procedure p1()
begin
/*this is my first procedure*/
End;
注意:begin…..end相当于{}的作用
如果SQL语句体只有一句可以省略这个标志begin end;
名称不区分大小写,尽量避免与内建函数同名,加入同名在()之间加空格
名称的长度限制为64个字符
()可以为空不能省略
通常select不会出现在存储过程体内
大部分的DDL。DML都是合法语句
非法语句:
Create procedure db p() drop database
含有use 库名等
3) 调用存储过程
Call 存储过程名(…….)
4)带有描述信息的存储过程
Create procedure p3()
Language sql----声明用什么语言来实现存储过程
目前只支持sql语句,以后有可能用php语句代替
Not deterministic---返回的结果不确定
Sql security definer ---创建者
Comment ‘a procedure’----给存储过程加注释
Select *…..------sql语句体;
4) 当语句体内容俩条以上,结束符号
Sql;
Create procedure p4()
Begin
Select * from tb1—给出错误提示,直接结束存储
Select * from tb2;
End;
Delimiter----声明结束符
Delimiter//
Select * from tb1;//
参数:【in|out|inout】参变量 数据类型
Declare:声明存储过程的局部变量
Declare 变量名 数据类型;
语句
::mysql>use db2;
Databasechanged
mysql>create procedure p1()
-> begin
-> /*this is my first procedure*/
-> end;
Query OK,0 rows affected (0.26 sec)
mysql>call p1;
Query OK,0 rows affected (0.08 sec)
mysql>call p1();
Query OK,0 rows affected (0.00 sec)
mysql>select varsion();
ERROR1305 (42000): FUNCTION db2.varsion does not exist
mysql>select version();
+----------------------+
|version() |
+----------------------+
|5.1.33-community-log |
+----------------------+
1 row inset (0.01 sec)
mysql>select version();
+----------------------+
|version() |
+----------------------+
|5.1.33-community-log |
+----------------------+
1 row in set(0.00 sec)
mysql>call p2;
ERROR1305 (42000): PROCEDURE db2.p2 does not exist
mysql>create procedure p2()
-> begin
-> /*this is my first procedure*/
-> end;
Query OK,0 rows affected (0.00 sec)
mysql>call p2;
Query OK,0 rows affected (0.00 sec)
mysql>create procedure p3()
-> language SQL
-> not deterministic
-> SQL security definer
-> comment 'a procedure'
-> select * from th1;
Query OK,0 rows affected (0.05 sec)
mysql>call p3;
ERROR1146 (42S02): Table 'db2.th1' doesn't exist
mysql>create procedure p4()
-> begin
-> select *from tb1;
ERROR1064 (42000): You have an error in your SQL syntax; check the manual that
correspondsto your MySQL server version for the right syntax to use near '' at
line 3
mysql>delimiter//
-> select *from tb1;
ERROR1064 (42000): You have an error in your SQL syntax; check the manual that
correspondsto your MySQL server version for the right syntax to use near 'delim
iter//select*from tb1' at line 1
mysql>select *from tb1;
+----+------+--------+-------+--------+
| id |sex | shuxue | yuwen | yingyu |
+----+------+--------+-------+--------+
| 1 | 1 | 78 | 67 | 89 |
| 2 | 1 | 98 | 76 | 90 |
| 3 | 1 | 80 | 82 | 90 |
| 4 | 0 | 69 | 96 | 83 |
| 4 | 0 | 69 | 96 | 83 |
| 5 | 0 | 74 | 69 | 84 |
| 0 | 0 | 0 | 0 | 0 |
| 0 | 0 | 0 | 0 | 0 |
+----+------+--------+-------+--------+
8 rows inset (0.06 sec)
mysql>delimiter;
ERROR:
DELIMITERmust be followed by a 'delimiter' character or string
mysql>use test;
ERROR1049 (42000): Unknown database 'test'
mysql>use db1;
Databasechanged
mysql>create tbele t1(s1 int);
ERROR1064 (42000): You have an error in your SQL syntax; check the manual that
correspondsto your MySQL server version for the right syntax to use near 'tbele
t1(s1 int)' at line 1
mysql>use db1;
Databasechanged
mysql>create table t1(s1 int);
Query OK,0 rows affected (0.08 sec)
mysql>insert into t1values(5);
ERROR1064 (42000): You have an error in your SQL syntax; check the manual that
correspondsto your MySQL server version for the right syntax to use near '5)' a
t line 1
mysql>insert into t1 values(5);
Query OK,1 row affected (0.01 sec)
mysql>create procedure p6(in para1 int)
-> begin
-> declare var1 char(10);
ERROR1064 (42000): You have an error in your SQL syntax; check the manual that
correspondsto your MySQL server version for the right syntax to use near '' at
line 3
mysql>delimiter//
-> create procedure p6(in para1 int)
-> begin
-> declare var1 char(10);
ERROR1064 (42000): You have an error in your SQL syntax; check the manual that
correspondsto your MySQL server version for the right syntax to use near 'delim
iter//createprocedure p6(in para1 int)
begin
declarevar1 char(10)' at line 1
mysql>create procedure p6(in para1 int)
-> begin
-> declare var1 char(10);
ERROR1064 (42000): You have an error in your SQL syntax; check the manual that
correspondsto your MySQL server version for the right syntax to use near '' at
line 3
mysql>delimiter //
mysql>create procedure p6(in para1 int)
-> begin
-> declare var1 char(10);
-> if para1=0 then
-> set var1='男';
-> else
-> set var1='女';
-> endif
-> insert into t1 values(var1);
-> end//
ERROR1064 (42000): You have an error in your SQL syntax; check the manual that
correspondsto your MySQL server version for the right syntax to use near 'inser
t into t1values(var1);
end' atline 9
mysql>create table t2(s2 char);
-> create table t2(s2 char(30));
-> //
Query OK,0 rows affected (0.05 sec)
ERROR1050 (42S01): Table 't2' already exists
mysql>create procedure p6(in para1 int)
-> begin
-> declare var1 char(10);//
ERROR1064 (42000): You have an error in your SQL syntax; check the manual that
correspondsto your MySQL server version for the right syntax to use near '' at
line 3
mysql>create procedure p7()
-> insert into t2 values('b');//
Query OK,0 rows affected (0.01 sec)
mysql>delimiter ;
mysql>select * from t2;
Empty set(0.05 sec)
mysql>callp7();
ERROR1064 (42000): You have an error in your SQL syntax; check the manual that
correspondsto your MySQL server version for the right syntax to use near 'callp
7()' atline 1
mysql>call p7();
Query OK,1 row affected (0.00 sec)
mysql>create procedure p6(in para1 int)
-> begin
-> declare var1 char(10);//
ERROR1064 (42000): You have an error in your SQL syntax; check the manual that
correspondsto your MySQL server version for the right syntax to use near '' at
line 3
->
->
-> create procedure p6(in para1 int)
-> //
-> //
-> create procedure p6(in para1 int)
-> els
mysql> delimiter //
mysql>create database db3;
-> //
Query OK,1 row affected (0.00 sec)
mysql>use db3
Databasechanged
mysql>create table t1 (s int);
-> //
Query OK,0 rows affected (0.04 sec)
mysql>create procedure p6(in para1 int)
-> begin
-> declare var1 int;
-> set var1=var1+para1;
-> insert into t1 values(var1);
-> end//
Query OK,0 rows affected (0.01 sec)
mysql>call p6(3);
-> //
Query OK,1 row affected (0.05 sec)
mysql>delimiter ;
mysql>select *from t1;
+------+
| s |
+------+
| NULL |
+------+
1 row inset (0.00 sec)
mysql>select null+3;
+--------+
| null+3|
+--------+
| NULL |
+--------+
1 row inset (0.00 sec)
mysql>
If isse$_post[‘user’]