一.字符集设置:
mysql的字符集包括字符集和校对规则，字符集用来定义mysql存储字符串的方式，校对规则用来定义比较字符串的方式.字符集和校对规则是一对多的关系.
显示mysql可以使用的字符集：
mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| charset | description | default collation | maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | big5 traditional chinese | big5_chinese_ci | 2 |
| dec8 | dec west european | dec8_swedish_ci | 1 |
| cp850 | dos west european | cp850_general_ci | 1 |
| hp8 | hp west european | hp8_english_ci | 1 |
| koi8r | koi8-r relcom russian | koi8r_general_ci | 1 |
| latin1 | cp1252 west european | latin1_swedish_ci | 1 |
| latin2 | iso 8859-2 central european | latin2_general_ci | 1 |
| swe7 | 7bit swedish | swe7_swedish_ci | 1 |
| ascii | us ascii | ascii_general_ci | 1 |
| ujis | euc-jp japanese | ujis_japanese_ci | 3 |
| sjis | shift-jis japanese | sjis_japanese_ci | 2 |
| hebrew | iso 8859-8 hebrew | hebrew_general_ci | 1 |
| tis620 | tis620 thai | tis620_thai_ci | 1 |
| euckr | euc-kr korean | euckr_korean_ci | 2 |
| koi8u | koi8-u ukrainian | koi8u_general_ci | 1 |
| gb2312 | gb2312 simplified chinese | gb2312_chinese_ci | 2 |
| greek | iso 8859-7 greek | greek_general_ci | 1 |
| cp1250 | windows central european | cp1250_general_ci | 1 |
| gbk | gbk simplified chinese | gbk_chinese_ci | 2 |
| latin5 | iso 8859-9 turkish | latin5_turkish_ci | 1 |
| armscii8 | armscii-8 armenian | armscii8_general_ci | 1 |
| utf8 | utf-8 unicode | utf8_general_ci | 3 |
| ucs2 | ucs-2 unicode | ucs2_general_ci | 2 |
| cp866 | dos russian | cp866_general_ci | 1 |
| keybcs2 | dos kamenicky czech-slovak | keybcs2_general_ci | 1 |
| macce | mac central european | macce_general_ci | 1 |
| macroman | mac west european | macroman_general_ci | 1 |
| cp852 | dos central european | cp852_general_ci | 1 |
| latin7 | iso 8859-13 baltic | latin7_general_ci | 1 |
| cp1251 | windows cyrillic | cp1251_general_ci | 1 |
| cp1256 | windows arabic | cp1256_general_ci | 1 |
| cp1257 | windows baltic | cp1257_general_ci | 1 |
| binary | binary pseudo charset | binary | 1 |
| geostd8 | geostd8 georgian | geostd8_general_ci | 1 |
| cp932 | sjis for windows japanese | cp932_japanese_ci | 2 |
| eucjpms | ujis for windows japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
36 rows in set (0.08 sec)
显示某种字符集的校对规则:
mysql> show collation like 'gbk%';
+----------------+---------+----+---------+----------+---------+
| collation | charset | id | default | compiled | sortlen |
+----------------+---------+----+---------+----------+---------+
| gbk_chinese_ci | gbk | 28 | yes | yes | 1 |
| gbk_bin | gbk | 87 | | yes | 1 |
+----------------+---------+----+---------+----------+---------+
2 rows in set (0.03 sec)
校对规则命名约定:校对规则=字符集名+语言名+(_ci,_cs,_bin)结束.
_ci对字符的大小写不敏感
_cs对字符大小写敏感
_bin比较的是基于字符编码的值而与language无关
对于gbk的两个校验规则：gbk_chinese_ci和 gbk_bin，前者对大小写不敏感，后者按编码的值进行比较，对大小写敏感：
mysql> select case when 'a' collate gbk_chinese_ci = 'a' collate gbk_chinese_
then 1 else 0 end;
+----------------------------------------------------------------------------
| 1 |
+----------------------------------------------------------------------------
1 row in set (0.06 sec)
然而:
mysql> select case when 'a' collate gbk_bin = 'a' collate gbk_bin
-> then 1 else 0 end;
+-----------------------------------------------------------------------+
| 0 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
显示database的字符集及校验规则:
mysql> show variables like 'character_set_database';
+------------------------+--------+
| variable_name | value |
+------------------------+--------+
| character_set_database | gb2312 |
+------------------------+--------+
1 row in set (0.08 sec)
mysql> show variables like 'collation_database';
+--------------------+-------------------+
| variable_name | value |
+--------------------+-------------------+
| collation_database | gb2312_chinese_ci |
+--------------------+-------------------+
1 row in set (0.00 sec)
字符集的级别:服务器级别，数据库级别，表级别，字段级别.
设置数据库字符集的基本规则:
a.如果指定了字符集和校验规则，则使用指定的；
b.如果指定了字符集但没有指定校验规则，则使用字符集默认的校验规则；
c.如果指定了校验规则但未指定字符集，则使用与该校验规则绑定的字符集；
d.如果没有指定字符集和校验规则，则使用服务器的字符集和校验规则.
表的字符集设定 same with the above：
mysql> show create table z1 \g;
*************************** 1. row ***************************
table: z1
create table: create table `z1` (
`id` varchar(11) character set gb2312 default null
) engine=innodb default charset=latin1
1 row in set (0.23 sec)
修改z1表的字符集:
mysql> alter table z1 character set gbk;
query ok, 0 rows affected (1.05 sec)
records: 0 duplicates: 0 warnings: 0
mysql> show create table z1 \g;
*************************** 1. row ***************************
table: z1
create table: create table `z1` (
`id` varchar(11) character set gb2312 default null
) engine=innodb default charset=gbk
1 row in set (0.00 sec)
mysql> insert into z1 values('有钱先生就是我');
query ok, 1 row affected (0.09 sec)
mysql> select * from z1;
+----------------+
| id |
+----------------+
| 有钱先生就是我 |
+----------------+
1 row in set (0.03 sec)
中文问题就解决了.
二.索引的一点小知识:
项目中有时候会有些select查询特别慢，后来大牛加了个索引，速度超快，用的就是索引,
mysql的存储引擎myisam和innodb默认使用的都是btree索引.
create index x on table y(a,b);
在y表,a,b字段建立x索引
a.最适合索引的列是where子句中的列，而不是select中列;
b.使用唯一索引，列的基数越大，索引的效果越好;
c.索引会占用额外的磁盘空间，莫滥用，否则会降低写操作的性能.
对于使用=或操作符的比较，hash索引会比btree索引快
对于>,=,
三.存储过程及自定义函数(面试必备):
1.delimiter一个函数或sp终结符;
2.create一个function或sp;
3.将终结符替换成mysql使用的';';
4.直接select或call调用.
mysql> delimiter $$
mysql> create function myfunction2
-> (
-> in_string varchar(255),
-> in_find_str varchar(20),
-> in_repl_str varchar(20)
-> )
-> returns varchar(255)
-> begin
-> declare l_new_string varchar(255);
-> declare l_find_pos int;
-> set l_find_pos=instr(in_string,in_find_str);
->
-> if (l_find_pos>0) then
-> set l_new_string=insert(in_string,l_find_pos,length(in_find_s
tr),in_repl_str);
-> else
-> set l_new_string=in_string;
-> end if;
-> return(l_new_string);
-> end $$
query ok, 0 rows affected (0.06 sec)
mysql> delimiter ;
mysql> select myfunction2('abc','a','z');
+----------------------------+
| myfunction2('abc','a','z') |
+----------------------------+
| zbc |
+----------------------------+
1 row in set (0.00 sec)
另一个demo:
mysql> delimiter $$
mysql> create function myfunction3(
-> in_title varchar(4),
-> in_gender char(1),
-> in_firstname varchar(20),
-> in_middle_initial char(1),
-> in_surname varchar(20))
->
-> returns varchar(60)
-> begin
-> declare l_title varchar(4);
-> declare l_name_string varchar(60);
->
-> if isnull(in_title) then
-> if in_gender='m' then
-> set l_title=mr;
-> else
-> set l_title=ms;
-> end if;
-> end if;
->
-> if isnull(in_middle_initial) then
-> set l_name_string=concat(l_title,' ',in_firstname,' ',in_surnam
e);
-> else
-> set l_name_string=concat(l_title,' ',in_firstname,' ',
-> in_middle_initial,' ',in_surname);
-> end if;
->
-> return(l_name_string);
-> end$$
query ok, 0 rows affected (0.00 sec)
mysql> select myfunction3('mrs','m','first','m','last');
-> select myfunction3('mrs','m','first','m','last')$$
+-------------------------------------------+
| myfunction3('mrs','m','first','m','last') |
+-------------------------------------------+
| null |
+-------------------------------------------+
1 row in set (0.01 sec)
+-------------------------------------------+
| myfunction3('mrs','m','first','m','last') |
+-------------------------------------------+
| null |
+-------------------------------------------+
1 row in set (0.03 sec)
mysql> delimiter ;
mysql> select myfunction3(null,'m','first','m','last');
+------------------------------------------+
| myfunction3(null,'m','first','m','last') |
+------------------------------------------+
| mr first m last |
+------------------------------------------+
1 row in set (0.00 sec)
红色部分，因为没有delimiter ;，从而以分号结尾的调用没有成功，使用$$就可以.
创建完函数或过程，一定delimiter ;
当然可以指定别的分隔符:
mysql> delimiter //
mysql> create function myfunction4(rush_ship int(10)) returns decimal(10,2)
-> begin
-> declare rush_shipping_cost decimal(10,2);
-> case rush_ship
-> when 1 then
-> set rush_shipping_cost = 20.00;
-> when 2 then
-> set rush_shipping_cost = 15.00;
-> when 3 then
-> set rush_shipping_cost = 10.00;
-> else
-> set rush_shipping_cost = 0.00;
-> end case;
-> return rush_shipping_cost;
-> end//
query ok, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> select myfunction4(2);
+----------------+
| myfunction4(2) |
+----------------+
| 15.00 |
+----------------+
1 row in set (0.00 sec)
一个简单的sp:
mysql> delimiter //
mysql> create procedure tom2.myproc (in in_count int)
-> begin
-> declare count int default 0;
-> increment: loop
-> set count = count + 1;
-> if count -> end if;
-> if count > in_count then leave increment;
-> end if;
-> end loop increment;
-> select count;
-> end//
query ok, 0 rows affected (0.00 sec)
mysql> call tom2.myproc(5);
-> call tom2.myproc(5)//
+-------+
| count |
+-------+
| 20 |
+-------+
1 row in set (0.00 sec)
查看sp(stored procedure)状态:
mysql> show procedure status like 'myproc' \g;
*************************** 1. row ***************************
db: tom2
name: myproc
type: procedure
definer: root@localhost
modified: 2014-10-12 23:18:28
created: 2014-10-12 23:18:28
security_type: definer
comment:
1 row in set (0.00 sec)
为sp添加characteristics特征值:
1.language sql:说明该sp由sql编写;
2.contains sql(包含sql)|no sql|reads sql data|modifies sql data;
3.sql security{definer|invoker},指定sp的调用者权限;
4.comment:sp的说明
实例:
mysql> alter procedure tom2.myproc sql security invoker
-> comment tom's procedure;
query ok, 0 rows affected (0.00 sec)
mysql> show procedure status like 'myproc' \g;
*************************** 1. row ***************************
db: tom2
name: myproc
type: procedure
definer: root@localhost
modified: 2014-10-12 23:27:54
created: 2014-10-12 23:18:28
security_type: invoker
comment: tom's procedure
1 row in set (0.02 sec)
一个完整的demo：
mysql> create table employee(
-> id int,
-> first_name varchar(15),
-> last_name varchar(15),
-> start_date date,
-> end_date date,
-> salary float(8,2),
-> city varchar(10),
-> description varchar(15)
-> );
query ok, 0 rows affected (0.09 sec)
mysql> insert into employee(id,first_name, last_name, start_date, end_date, sa
lary, city, description)
-> values (1,'jason', 'martin', '19960725', '20060725', 1234.56, 'toron
to', 'programmer'),
-> (2,'alison', 'mathews', '19760321', '19860221', 6661.78, 'vancouve
r','tester'),
-> (3,'james', 'smith', '19781212', '19900315', 6544.78, 'vancouve
r','tester'),
-> (4,'celia', 'rice', '19821024', '19990421', 2344.78, 'vancouve
r','manager'),
-> (5,'robert', 'black', '19840115', '19980808', 2334.78, 'vancouve
r','tester'),
-> (6,'linda', 'green', '19870730', '19960104', 4322.78,'new york'
, 'tester'),
-> (7,'david', 'larry', '19901231', '19980212', 7897.78,'new york'
, 'manager'),
-> (8,'james', 'cat', '19960917', '20020415', 1232.78,'vancouver
', 'tester');
query ok, 8 rows affected (0.03 sec)
records: 8 duplicates: 0 warnings: 0
mysql> delimiter //
mysql> create procedure merge_employee (in old_id int, in new_id int, out error
varchar(100))
-> sql security definer
-> comment 'this is the comment'
-> begin
-> declare old_count int default 0;
-> declare new_count int default 0;
-> declare addresses_changed int default 0;
->
-> ## check to make sure the old_id and new_id exists
-> select count(*) into old_count from employee where id = old_
id;
-> select count(*) into new_count from employee where id = new_
id;
->
-> if !old_count then
-> set error = 'old id does not exist';
-> elseif !new_count then
-> set error = 'new id does not exist';
-> else
-> update employee set id = new_id where id = old_id;
-> select row_count() into addresses_changed;
->
-> delete from employee where id = old_id;
->
-> select addresses_changed;
->
-> end if;
-> end//
query ok, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call merge_employee(1,4,@error);
+-------------------+
| addresses_changed |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.08 sec)
query ok, 0 rows affected (0.09 sec)
mysql> select @error;
+--------+
| @error |
+--------+
| null |
+--------+
1 row in set (0.00 sec)
一个简单的while循环,计算前四个字母的ascii码:
mysql> delimiter $$
mysql> create function myfunction5(in_string varchar(80) )
-> returns varchar(256)
-> no sql
-> begin
-> declare i int default 1;
-> declare string_len int;
-> declare out_string varchar(256) default '';
-> set string_len=length(in_string);
-> while (i -> set out_string=concat(out_string,ascii(substr(in_string,i,1
)),' ');
-> set i=i+1;
-> end while;
-> return (out_string);
-> end$$
query ok, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> select myfunction5('abcde');
+----------------------+
| myfunction5('abcde') |
+----------------------+
| 65 66 67 68 |
+----------------------+
1 row in set (0.00 sec)
另一个demo(拼sql语句)：
mysql> delimiter $$
mysql> create procedure sp_customer_search
-> (in_description varchar(30
-> in_contact_surname varcha
-> in_contact_firstname varc
-> in_city varchar(10))
-> begin
-> declare l_where_clause varch
-> if in_description is not nul
-> set l_where_clause=conca
-> ' description=',in_d
-> end if;
-> select l_where_clause;
-> if in_contact_surname is not
-> if l_where_clause'where
-> set l_where_clause=con
-> end if;
-> set l_where_clause=concat
-> ' last_name=',in_con
-> end if;
-> select l_where_clause;
-> if in_contact_firstname is n
-> if l_where_clause'where
-> set l_where_clause=con
-> end if;
-> set l_where_clause=concat
-> ' first_name=',in_co
-> end if;
-> select l_where_clause;
-> if in_city is not null then
-> if l_where_clause'where
-> set l_where_clause=con
-> end if;
-> set l_where_clause=concat
-> ' city=',in_city,''
-> end if;
-> select l_where_clause;
-> set @sql=concat('select * fr
-> select @sql;
-> prepare s1 from @sql;
-> execute s1;
-> deallocate prepare s1;
-> end$$
query ok, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call sp_customer_search_dyn('tester','smith','james','vancouver');
+----------------------------+
| l_where_clause |
+----------------------------+
| where description=tester |
+----------------------------+
1 row in set (0.00 sec)
+---------------------------------------------------+
| l_where_clause |
+---------------------------------------------------+
| where description=tester and last_name=smith |
+---------------------------------------------------+
1 row in set (0.00 sec)
+---------------------------------------------------------------------------+
| l_where_clause |
+---------------------------------------------------------------------------+
| where description=tester and last_name=smith and first_name=james |
+---------------------------------------------------------------------------+
1 row in set (0.02 sec)
+-------------------------------------------------------------------------------
------------------+
| l_where_clause
|
+-------------------------------------------------------------------------------
------------------+
| where description=tester and last_name=smith and first_name=james and
city=vancouver |
+-------------------------------------------------------------------------------
------------------+
1 row in set (0.03 sec)
+-------------------------------------------------------------------------------
-----------------------------------------+
| @sql
|
+-------------------------------------------------------------------------------
-----------------------------------------+
| select * from employee where description=tester and last_name=smith and
first_name=james and city=vancouver |
+-------------------------------------------------------------------------------
-----------------------------------------+
1 row in set (0.06 sec)
+------+------------+-----------+------------+------------+---------+-----------
+-------------+
| id | first_name | last_name | start_date | end_date | salary | city
| description |
+------+------------+-----------+------------+------------+---------+-----------
+-------------+
| 3 | james | smith | 1978-12-12 | 1990-03-15 | 6544.78 | vancouver
| tester |
+------+------------+-----------+------------+------------+---------+-----------
+-------------+
1 row in set (0.08 sec)
query ok, 0 rows affected (0.09 sec)
sp还可以捕获错误做出正确的处理:
mysql> delimiter $$
mysql> create procedure myproc11
-> (in_first_name varchar(30),
-> in_last_name varchar(30),
-> in_city varchar(30),
-> in_description varchar(10),
-> out out_status varchar(30))
-> modifies sql data
-> begin
-> declare continue handler for 1406
-> set out_status=desc is to long;
->
-> set out_status='ok';
-> insert into employee
-> (first_name,last_name,city,description)
-> values
-> (in_first_name,in_last_name,in_city,in_description);
-> end$$
query ok, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> set @mymessage = 0;
query ok, 0 rows affected (0.00 sec)
mysql> call myproc11('jason','martin','ddddddddddddddd','new desc',@mymessage);
query ok, 0 rows affected (0.27 sec)
mysql> select @mymessage;
+-----------------+
| @mymessage |
+-----------------+
| desc is to long |
+-----------------+
1 row in set (0.00 sec)