mysql数据库操作笔记

//查询最后生成的ID
select @@session.identity
//创建视图
create view v_add_friends
as (select u.userID,u.real_name,u.sex,d.college,d.academe,d.speciality,
d.init_college_date,d.head_pic
 from user_info_list  u,user_details d where u.userID=d.userID);
select * from v_add_friends where real_name='11' and userID=1;
drop view v_add_friends;
select * from v_add_friends limit 0,10;
//创建存储过程
create PROCEDURE  proc_sel_friends ()
begin
declare pageStart int;
declare pageEnd int;
set pageStart=0;
set pageEnd=10;
  select * from v_add_friends ;
end;
//删除或运行存储过程
drop PROCEDURE proc_sel_friends;
call proc_sel_friends();
//写一个主从表存储过程，主表用自增主键，从表需要插入主表“自增的主键ID”
create PROCEDURE pro_product_book(user_id char(20),pro_name char(20),pro_price char(20),pro_pic char(20),
pro_state char(20),pro_use_day char(20),pro_amount varchar(20),pro_type_name char(20),pro_describe char(200),
book_publishing char(20),book_isbn char(20),book_pub_date char(20),book_page_num char(20),
book_pub_time char(20),book_author char(20),book_formats char(20),book_industry char(20),
book_school_step char(20),book_language char(20),book_describe varchar(200),book_attr char(20),book_pri_price char(20))
begin
//用于保存最后一个自增ID
declare last_id int;
 insert into product_info values(default,user_id,pro_name,pro_price,pro_pic,default,pro_state,pro_use_day,pro_amount,pro_type_name,pro_describe);
//获得表中最后生成的自增主键ID
set last_id = (select @@session.identity);

    insert into book_type values(default,last_id,book_publishing,book_isbn,book_pub_date,book_page_num,book_pub_time,book_author,book_formats,book_industry,
    book_school_step,book_language,book_describe,book_attr,book_pri_price);

end;
//执行存储过程
call pro_product_book('4','2','2','2','2','2','2','2','2','2','2','2009-8-7','2','2','2','2','2','2','2','2','2','2');
drop PROCEDURE pro_product_book;

mysql乱码，在控制台显示汉字的方法

//查询表编码格式
SHOW VARIABLES LIKE 'character_set%';
//设置控制台显示编码格式
set character_set_results='gbk'; #将编码改为gbk，控制台就可以显示中文了
//查询某表的中编码方式
mysql> status;
//修改某表的编码
alter table users character set utf8;
//查看生成某表的结构
show create table users;
//修改表中某列的字符集
alter table users modify username char(20) character set gbk;
//清空表中的数据
truncate table users;
//切换连接数据库的语法:use new_dbname;

使用MySQL应该注意的小要点

//MySQL不支持top语句，用Limit代替，下句从表中第9个行开始，查1行
select * from product_info order by productID asc limit 9,1;
select count(*) from product_info where userID=1;