MySQL基础语句(存储过程)

# 创建并选择数据库
create database mydata;
use mydata

# 显示数据库
show databases;
# 显示表
show tables；
# 查看表
desc 表的名字;

# 客户表
create table customers
(
cust_id int primary key, #主键
cust_name varchar(20),
cust_emil varchar(50)
);

# 订单表
create table orders
(
orders_num int primary key,
orders_date datetime,
cust_id int, #客户id 外键
foreign key (cust_id) references customers(cust_id)
);

insert into prod values (1, 'm', 'apq', '20', '1998-02-28');
commit;

select * from prod;

# 删除表
drop table orders;

# 删除表数据
delete from orders where orders_num=100; #删除一行
delete from orders; #删除表里的所有数据

# 自动递增字段
create table article
(
id int primary key auto_increment,
title varchar(10)
);

# 自动递增字段表里插入数据
insert into article (title) values('a');
insert into article (title) values('b');
+----+-------+
| id | title |
+----+-------+
|  1 | a     |
|  2 | b     |
+----+-------+

select * from prod limit 0,2;
select * from prod order by prod_id desc limit 3,2;

# 日期格式化
select date_format(now(), '%y-%m-%d %h:%i:%s');

可以显示数据库的编码
show create table table_name;

在写SQL语句之前，请加上这一句，以保证前台和后台的编码一致
set names gbk;
MySql安装目录下，请找到my.ini文件，在[mysql]下面把编码改成gbk, 在[mysqld]下面
编码改成utf8 这样以后就不用写set names gbk了!!之后重启MYSql
[mysql]
# 客户端的编码
default-character-set=gbk
[mysqld]
# 服务器端的编码
default-character-set=utf8

# 创建存储过程(不带参数的)
delimiter //
create procedure user_avg()
begin
	select avg(money) as avg_money from user;
end //
delimiter ;

# 调用存储过程
call user_avg();

# 删除存储过程
drop procedure if exists user_avg;

-------------------------------------
# 创建存储过程(带参数的), out是输出值
delimiter //
create procedure proce(
	out low decimal(8, 2),
	out high decimal(8, 2),
	out average decimal(8, 2)
)

begin
	select min(money) from user into low;
	select max(money) from user into high;
	select avg(money) from user into average;
end //
delimiter ;

# 调用存储过程
call proce(@low, @high, @average);

# 检索
select @low;
select @high;
select @average;
select @low, @high, @average;

-------------------------------------
# 创建存储过程(带参数的), in是输入值
create table products
(
	id int primary key auto_increment,
	name varchar(30),
	price float,
	quantity int
);

insert into products(name, price, quantity) values('umbrella', 20.0, 5);
insert into products(name, price, quantity) values('knife', 200.0, 10);
insert into products(name, price, quantity) values('trousers', 300.0, 20);

delimiter //
create procedure ordertotal(
	in number int,
	out total decimal(8, 2)
)
begin
	select sum(price * quantity) from products where id = number into total;
end //
delimiter ;

call ordertotal(1, @sum);
select @sum;

-------------------------------------
# 显示所有存储过程
show procedure status;
# 删除存储过程
drop procedure if exists ordertotal;
-------------------------------------

-------------------------------------
# \. D:\\SQL\\procedure.sql
-------------------------------------

public class Procedure {

	public static void main(String[] args) throws Exception {
		orderTotal("umbrella", 1); //umbrella: 100.0
		orderTotal("knife", 2); //knife: 2000.0
		orderTotal("trousers", 3); //trousers: 6000.0
	}
	
	static void orderTotal(String name, int id) throws Exception {
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			//创建语句
			String sql = "{ call ordertotal(?,?) }";
			cs = conn.prepareCall(sql);
			//注册输出参数
			cs.registerOutParameter(2, Types.DECIMAL);
			cs.setInt(1, id);
			cs.executeUpdate();
			
			//拿出输出参数
			float total = cs.getFloat(2);
			System.out.println(name + ": " + total);
		} finally {
			JdbcUtils.free(rs, cs, conn);
		}
	}

}