mysql数据库操作笔记

//查询最后生成的IDselect @@session.identity//创建视图create view v_add_friendsas (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 ()begindeclare 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//用于保存最后一个自增IDdeclare 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);//获得表中最后生成的自增主键IDset 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;