Mysql错误及处理总结

（1）error 1045 28000

通过匿名用户登录MySQL服务器的。没有创建数据库的权限。

mysql -hlocalhost -u root –p。

（2）mysql 5.6.25  出现中文乱码问题

旧版本中my.ini有详细的设置，但是5.6.25中简化了，而且参数名变了，具体设置为

# For advice onhow to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# *** DO NOTEDIT THIS FILE. It's a template which will be copied to the

# *** defaultlocation during install, and will be replaced if you

# *** upgrade toa newer version of MySQL.

 

[mysqld]

character_set_server=utf8

character_set_client=utf8

# Remove leading# and set to the amount of RAM for the most important data

# cache inMySQL. Start at 70% of total RAM for dedicated server, else 10%.

#innodb_buffer_pool_size = 128M

 

# Remove leading# to turn on a very important data integrity option: logging

# changes to thebinary log between backups.

# log_bin

 

# These arecommonly set, remove the # and set as required.

#basedir=D:\mysql-5.6.25-winx64

#datadir=D:\mysql-5.6.25-winx64\bin\data

# port = .....

# server_id =.....

 

 

# Remove leading# to set options mainly useful for reporting servers.

# The serverdefaults are faster for transactions and fast SELECTs.

# Adjust sizesas needed, experiment to find the optimal values.

# join_buffer_size= 128M

#sort_buffer_size = 2M

#read_rnd_buffer_size = 2M

 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 

[client]

port=3306

default-character-set=utf8

除此之外，在建立数据库时

Create databasestudents character set utf8;

 

以上两步完成顺利显示中文。

（3）ERROR 1364 (HY000):Field 'ssl_cipher' doesn't have a default value解决方法

添加用户 insert into mysql.user(Host,User,Password)values("localhost","test",password("1234"));

报以下的错误 ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a defaultvalue错误

正确的添加用户方法：

GRANT USAGE ON *.* TO 'user01'@'localhost' IDENTIFIED BY'123456' WITH GRANT OPTION;

用户：user01，密码：123456，这样就添加了一个新的用户，不会出以上的错误了。

（4）mysql批处理中出现的问题

d:mysqlbin mysql < mytest.sql

出现没有权限操作的时

用mysql –u root –p <mytest.sql ，输入密码进行验证获得权限后再进行操作。

（5）存储过程的函数

1 (1).字符串类

  2 CHARSET(str)//返回字串字符集

  3 CONCAT (string2 [,... ]) //连接字串

  4 INSTR (string ,substring )//返回substring首次在string中出现的位置,不存在返回0

  5 LCASE (string2 ) //转换成小写

  6 LEFT (string2 ,length)//从string2中的左边起取length个字符

  7 LENGTH (string ) //string长度

  8 LOAD_FILE (file_name )//从文件读取内容

  9 LOCATE (substring , string[,start_position ] ) 同INSTR,但可指定开始位置

 10 LPAD (string2,length ,pad ) //重复用pad加在string开头,直到字串长度为length

 11 LTRIM (string2 )//去除前端空格

 12 REPEAT (string2 ,count )//重复count次

 13 REPLACE (str ,search_str,replace_str )//在str中用replace_str替换search_str

 14 RPAD (string2,length ,pad) //在str后用pad补充,直到长度为length

 15 RTRIM (string2 )//去除后端空格

 16 STRCMP (string1,string2 ) //逐字符比较两字串大小,

 17 SUBSTRING (str , position[,length ])//从str的position开始,取length个字符,

 18 注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

 19 

 20 mysql>selectsubstring('abcd',0,2); 

 21 +-----------------------+ 

 22 |substring('abcd',0,2)| 

 23 +-----------------------+ 

 24 |                      | 

 25 +-----------------------+ 

 26 1 rowinset (0.00 sec) 

 27 

 28 mysql>selectsubstring('abcd',1,2); 

 29 +-----------------------+ 

 30 |substring('abcd',1,2)| 

 31 +-----------------------+ 

 32 |     ab               | 

 33 +-----------------------+ 

 34 1 rowinset (0.02 sec) 

 35 TRIM([[BOTH|LEADING|TRAILING][padding]FROM]string2)//去除指定位置的指定字符

 36 UCASE (string2 ) //转换成大写

 37 RIGHT(string2,length)//取string2最后length个字符

 38 SPACE(count)//生成count个空格

 39 (2).数学类

 40 ABS (number2 )//绝对值

 41 BIN(decimal_number ) //十进制转二进制

 42 CEILING (number2 )//向上取整

 43CONV(number2,from_base,to_base)//进制转换

 44 FLOOR (number2 )//向下取整

 45 FORMAT (number,decimal_places )//保留小数位数

 46 HEX(DecimalNumber ) //转十六进制

 47 注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143

 48 也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19

 49 LEAST (number , number2[,..])//求最小值

 50 MOD (numerator,denominator ) //求余

 51 POWER (number ,power )//求指数

 52 RAND([seed])//随机数

 53 ROUND (number[,decimals ])//四舍五入,decimals为小数位数]

 54 注：返回类型并非均为整数，如：

 55 (1)默认变为整形值

 56 mysql>selectround(1.23); 

 57 +-------------+ 

 58 |round(1.23)| 

 59 +-------------+ 

 60 |          1| 

 61 +-------------+ 

 62 1 rowinset (0.00 sec) 

 63 

 64 mysql>selectround(1.56); 

 65 +-------------+ 

 66 |round(1.56)| 

 67 +-------------+ 

 68 |          2| 

 69 +-------------+ 

 70 1 rowinset (0.00 sec)

 71

 72

 73 (2)可以设定小数位数，返回浮点型数据

 74 mysql>selectround(1.567,2); 

 75 +----------------+ 

 76 |round(1.567,2)| 

 77 +----------------+ 

 78 |          1.57| 

 79 +----------------+ 

 80 1 rowinset (0.00 sec)

 81 SIGN (number2 )//

 82 

 83 (3).日期时间类

 84 ADDTIME (date2,time_interval )//将time_interval加到date2

 85 CONVERT_TZ(datetime2 ,fromTZ ,toTZ )//转换时区

 86 CURRENT_DATE ( )//当前日期

 87 CURRENT_TIME ( )//当前时间

 88 CURRENT_TIMESTAMP ( )//当前时间戳

 89 DATE (datetime )//返回datetime的日期部分

 90 DATE_ADD (date2 ,INTERVAL d_value d_type )//在date2中加上日期或时间

 91 DATE_FORMAT (datetime ,FormatCodes )//使用formatcodes格式显示datetime

 92 DATE_SUB (date2 ,INTERVAL d_value d_type )//在date2上减去一个时间

 93 DATEDIFF (date1 ,date2 )//两个日期差

 94 DAY (date )//返回日期的天

 95 DAYNAME (date ) //英文星期

 96 DAYOFWEEK (date )//星期(1-7) ,1为星期天

 97 DAYOFYEAR (date )//一年中的第几天

 98 EXTRACT(interval_name FROM date ) //从date中提取日期的指定部分

 99 MAKEDATE (year ,day )//给出年及年中的第几天,生成日期串

100 MAKETIME (hour ,minute ,second )//生成时间串

101 MONTHNAME (date ) //英文月份名

102 NOW ( ) //当前时间

103 SEC_TO_TIME (seconds ) //秒数转成时间

104 STR_TO_DATE (string ,format )//字串转成时间,以format格式显示

105 TIMEDIFF (datetime1 ,datetime2 )//两个时间差

106 TIME_TO_SEC (time ) //时间转秒数]

107 WEEK (date_time [,start_of_week ]) //第几周

108 YEAR (datetime )//年份

109 DAYOFMONTH(datetime)//月的第几天

110 HOUR(datetime)//小时

111 LAST_DAY(date) //date的月的最后日期

112 MICROSECOND(datetime)//微秒

113 MONTH(datetime)//月

114 MINUTE(datetime)//分返回符号,正负或0

115 SQRT(number2)//开平方

（6）Mysql Workbench基础教程

http://blog.csdn.net/janefir/article/details/17142963

持续更新，未完待续。