存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理

存储带参类型 删除数据：

mysql> create procedure removeUserById(in p_id int unsigned)

    -> begin

    -> delete from bbb where id=p_id;

    -> end//

 create procedure sp1() select version();

call spq();

DELIMITER//修改分号；结束符为//

mysql> create function f7(username varchar(20))

    -> returns int unsigned

    -> begin

    -> insert test(username) values(username);

    -> return last_insert_id();

    -> end

    -> //

删除函数：drop function f1;

带参函数:

CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)

RETURNS FLOAT(10,2) UNSIGNED

RETURN(num1+num2)/2;

set names gbk;

 CREATE FUNCTION f4() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');

MD5()  信息摘要算法

PASSWORD()    密码算法

修改密码SET PASSWORD=PASSWORD('234567');

CONNECTION_ID() 连接ID 

DATEBASE()    当前数据库

LAST_INSERT_ID()    最后插入记录ID号

USER() 当前用户

VERSION() 版本信息

NOW()  当前日期和时间

CURDATE() 当前日期

CURTIME()    当前时间

DATA_ADD()    日期变化

select date_add('2020.3.27',interval 3 year)

DATEDIFF()    日期差值

select datediff('2020.3.27','2019.3.27')

DATE_FORMAT()    日期格式化

select date_format('2020.3.27','%m/%d/%Y');

CEIL() 向上取整  3.01-》4

FLOOR()向下取整  3.99-》3

DIV() 整数除法   3/4 -》0

MOD()取余  即%

POWER() 幂运算 power(3,3)->9

ROUND()四舍五入 round(3.1415,3)->3.142

TRUNCATE()数字截取 （1355.555,1）-》1355.5

1、CONCAT() 字符连接: 2、CONCAT_WS()

SELECT CONCAT('A','B');  AB

SELECT CONCAT_WS('|','A','B');   A|B

SELECT CONCAT(firstname,lastname) from aaa;

DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2  ON t1.goods_name = t2.goods_name  WHERE t1.goods_id > t2.goods_id;

自身连接：

mysql> select s.type_id,s.type_name,p.type_name from tdb_goods_types as s

    -> left join tdb_goods_types as p

    -> on s.parent_id=p.type_id;

创建表的同时插入数据：

mysql> create table tdb_goods_brands(

    -> brand_id smallint unsigned auto_increment primary key,

    -> brand_name varchar(40) not null

    -> )

    -> select brand_name from tdb_goods group by brand_name;

重名时使用别名as:

 update tdb_goods as g inner join tdb_goods_brands as b on g.brand_name=b.brand_name

    -> set g.brand_name=b.brand_id;

内连接 inner on

update tdb_goods inner join tdb_goods_cates on goods_cate=cate_name set goods_cate=cate_id;