数据库MySQL学习笔记（二）

这篇博客主要讲一下MySQL的常用数据类型和常用自带函数。

常用数据类型

一、数值类型

           tinyint,smallint,mediumint,int/integer,bigint,float,double,decimal

           int类型：

        （1）tinyint : 1字节大小,范围-128~127

create table temp_03 (tid tinyint);
insert into temp_03 values(-128);
insert into temp_03 values(127);

       （2）smallint: 2字节大小，范围-32768~32767

       （3）int/integer:常用数值类型，占 4字节，范围 -21~21左右

       （4）bigint:大整数类型， 范围与java语言的long的范围类似

          float与double

       （5）float:单精度类型，占4字节。不规定长度时，默认进行四舍五入，保留0或1位小数。规定长度时:float(m,n):m表示数据的

有效位数，n表示小数点后保留的位数。整数位：最大值为m-n。float(m,0)与float(m),指的是整数位的最大位数。

create table salary_t (salary float);
insert into salary_t values(10000.123456);    －10000.1
insert into salary_t values(10000.56789);     －10000.6
insert into salary_t values(10000);           －10000

create table salary_t01 (salary float(6,2));
insert into salary_t01 values(10.5678);        －10.57
insert into salary_t01 values(10000.5678);     不合定义
insert into salary_t01 values(9999.5678);      －9999.57
insert into salary_t01 values(0.5678);         －0.57

create table salary_t02 (salary float(6,0));
insert into salary_t02 values(9999.5678);
insert into salary_t02 values(999999);
select * from salary_t02;

     （6）double:双精度类型，占8字节，规定长度写法： double(m,n):有效位数是m,小数点后保留n位，整数位:m-n。

create table salary_t03 (salary double(4,2));
insert into salary_t03 values(100);
insert into salary_t03 values(99.5678);
select * from salary_t03;

      （7）decimal:与float和double的用法一样，decimal(m,n)。

二、时间和日期类型

            date,time,datetime,timestamp,year

         （1） date:日期类型，默认格式:yyyy-mm-dd  范围 1000-1-1/9999-12-31

create table date_t01 (birth date);
insert into date_t01 values('2008-11-21');
insert into date_t01 values('2009/10/20');
insert into date_t01 values('89/10/20');
insert into date_t01 values('2000/2/29 12:30:29');
insert into date_t01 values('12:30:29 2008/10/10');    －－格式不ok

       （2）time : 默认格式：HH:mm:ss，范围1：不写年月日时，-838:59:59/838:59:59，范围2: 写年月日时， -23:59:59/23:59:59

       （3）datetime:默认格式： yyyy-mm-dd hh:mm:ss，范围：1000:1:1 -23:59:59/9999:12:31 23:59:59

       （4）timestamp:时间戳类型，范围：1970-01-01 00:00:00/2038-1-18 23:59:59

       （5）year:年，默认格式:yyyy，范围 1901~2155

三、字符串类型

            char,varchar,tinyblob,tinytext,blob,text,mediumblob  mediumtext longblob longtext

（1）char:定长字符串类型,范围0~255个字节，定义数据类型长度时，最长为255。

          char(10):插入数据时，最多插入10个字节，如果插入的字节数不足，10字节时，也占内存10字节大小。

（2）varchar:可变字符串类型，范围0~255，规定长度时：最大值为255个字节

         varchar(10)：表示最多存储10个字节，如果不满足10个字节，在内存中按照实际字节数占用。

（3）blob:一种字符串类型，存储数据时存储的时字符对应的字节码。字节范围：0~65535

（4）text:一种字符串类型   字节范围：0~65535

（5） longblob:极大字符串类型，存储的是字符对应的字节码。

（6） longtext:极大字符串类型，存储的是字符。

  上述最后两种类型的字节范围： 0~Java语言的int的最大值的2倍+1。

四、虚表dual

       数据库为程序员提供的一个没有字段的表。

       作用：一般用来当作一个查询语句的基表。

       计算1+2+2*3： select 1+2+2*3 from dual;

       mysql数据库的特点:在运算，可以不基于表操作。（select 1+2可直接输出3）

 

常用自带函数之字符串函数

      1、ascii(str):返回指定字符串的第一个字符的ascii码。字符串为空字符串时，返回0，字符串为null时，返回null。

            select ascii('你好');--返回的是第一个字符的字节码的第一个字节

      2、ord(str):如果字符串的第一个字符是单字符时，与函数ascii(str)一样。如果不是单字节。如 UTF8的中，是三个字节。返回值为： 第一个字节*256+第二个字节*256+第三个字节。

      3、conv(n,from_base,to_base),将from_base进制中的数n转成to_base进制下的表示方式。

            16进制中的字符a转成2进制来表示：select conv('f',16,2);

      4、bin(n),oct(n),hex(n)：将数值n转成对应的二进制，八进制，十六进制。

            查看十进制数 13的二进制，100的八进制，128的十六进制：select bin(13),oct(100),hex(128);

      5、char(n...):返回多个ascii码组成的字符串：select char(104,101,108,108,111);

      6、concat(str1,str2,...):将多个字符串拼接成一个字符串，如果有一个参数为null,返回的就是null。

            将hello 和kitty进行拼接：select concat('hello','kitty');

            将hello 和null进行拼接： select concat('hello',null);    －－结果为null

       7、length(str)/octet_length(str) 返回的是默认字符集下的字符串的所有字节数。

             char_length(str)/character_length(str) 返回字符串的字符长度。

             统计一下'上海自来水来自海上'的字节长度：

select length('上海自来水来自海上');             －－27
select octet_length('上海自来水来自海上');
select character_length('上海自来水来自海上');   －－9
select char_length('上海自来水来自海上');

     8、locate(substr,str)/position(substr in str)/instr(str,substr)：返回substr在str第一次出现的位置。如果没有，返回0;

           计算出'zuo'在'no zuo no die'第一次出现的位置：select locate('zuo','no zuo no die')

           计算'bar'在'foobarfoobarfoo'中第一次出现的位置：select position('bar' in 'foobarfoobarfoo')

           计算'baa'在'foobarfoobarfoo'中第一次出现的位置：select instr('foobarfoobarfoo','baa');

     9、locate(substr,str,pos) ：从str的下标pos开始向后查找，substr第一次出现的位置。找不到返回0

           查找'bar'在'foobarfoobarfoo'中 第二次出现的位置：

           select locate('bar','foobarfoobarfoo',locate('bar','foobarfoobarfoo')+1);

     10、lpad(str,len,padstr)/rpad(str,len,padstr);：在字符串str的左边/右边添加padstr,添加后的总长度为len。可称之为左补全/右补全函数、右对齐/左对齐函数。

      11、left(str,len)/right(str,len)：返回字符串左端/右端的len个字符。

             获取'helloworld'的左端和右端的4个字符：select left('helloworld',4), right('helloworld',4);

      12、substring(str,pos)/substring(str from pos)：截取str的子串，从pos下标开始截取到最后。

              截取"no zuo no die"，从下标4开始截取：select substring('no zuo no die',4)

              截取"no zuo no die"的最后六个字符：select substring('no zuo no die',-6);

      13、substring(str,pos,len)/substring(str from pos for len)/mid(str,pos,len)  :对str进行截取操作，从pos下标开始，截取len长度个。数据库的字符串的下标从1开始。

              对"i like you"进行截取，从第三个位置开始,截取4个长度：

select substring('i like you',3,4);
select substring('i like you' from 3 for 4);
select mid('i like you',3,4);

             截取like字样：select substring('i like you',locate('like','i like you'),length('like'));

      14、substring_index(str,delim,count) :截取str，截取到第count个分隔符delim

              截取'www.mysql.com'，截取到第二个"."：select substring_index('www.mysql.com','.',2);

      15、ltrim(str)/rtrim(str) ：删除str的左端/右端的空格。

              删除'    bar  '字符串的左右空格：select ltrim(rtrim('    bar  ')), length(ltrim(rtrim('    bar  ')));

      16、trim([[both | leading | trailing] [remstr] from] str)：删除str里两端的remstr。左右两端由 both|leading|trailing来控制。默认情况:both。

             删除' hello '前后的空格：select trim(' hello '),trim(both ' ' from ' hello ');

      17、replace(str,from_str,to_str)：使用to_str替换掉str中的from_str;

             使用'#' 替换掉'hellohello'里的"ll"：select replace('hellohello','ll','#');

     18、insert(str,pos,len,newstr)：使用newstr替换str中的一部分,这部分从pos下标开始，len个。

             使用"mysql"替换掉"i like you"中的'you'：select insert('i like you',8,3,'mysql');

     19、lcase(str)/lower(str)/ucase(str)/upper(str)：将字符串中的英文字母全变成大/小写。

             将‘I like yourName 3’变成全大写 /全小写：select lower('I like yourName 3'),upper('I like yourName 3');

常用自带函数之函数之数学函数

      1、abs(n)：取参数n的绝对值

      2、sign(n)：判断参数的符号，如果是正数，返回1，如果是负数，返回-1，如果是0，返回0。

      3、mod(m,n)：返回m对n的取余的结果。同(m%n)。

      4、floor(n)/ceiling(n)，floor(n) 返回小于等于n的最大整数，ceiling(n) 返回大于等于n的最小整数。

      5、round(n,d)：对n进行四舍五入，保留d位小数。

      6、pow(x,y)/power(x,y)：计算x的y次方

      7、sqrt(n)：对n进行开平方。

      8、rand()：返回0~1.0的一个随机小数。

      9、truncate(n,d)：对数值n进行截断。d为正数时，保留小数点后d位。d为0时，保留整数位。d为负数时，保留小书店前d位。

      10、least(n.....)/greatest(n....)：统计多个参数中的最小值/最大值。参数类型可以不一致，但是必须能互相自动转换。

 

常用函数函数之日期时间函数

      1、dayofweek(date)：查看指定日期date是所在周的第几天,返回的是数字,1--周日.......7--周六。

      2、weekday(date)：返回指定date是所在周的周几。返回的也是数字。0-周1........6--周日。

      3、dayname(date)：返回指定日期是周几，注意:返回的是英文描述。

      4、dayofmonth(date)/dayofyear(date)：返回指定日期是当月/当年的第几天

      5、monthname(date)：返回指定日期所在月的英文描述。

      6、week(date,first)：返回指定日期是当年的第几周。first可以取值0或1。0表示周日是一周的第一天，1表示周1是一周的第一天。

      7、year(date),month(date),day(date),hour(time),minute(time),second(time)：返回指定日期或时间的时间分量。

      8、extract(part from date)：截取指定日期的一部分，part:year,month,day,hour,minute,second。

            select extract(year from now()),extract(day from now());

      9、date_format(date,format) ：将指定日期date,按照自定义格式，转成字符串显示。

            将当前系统时间格式化   yyyy年mm月dd日 mm分ss秒HH时。

            select now();

            select date_format(now(),'%Y年%m月%d日 %i分%s秒%H');

      10、curdate()/curtime();：获取当前系统的日期/时间

      11、now()/sysdate()/current_timestamp()：获取当前系统时间:都包含年月日时分秒。

      12、sec_to_time(n)：将指定数值n转成时间格式

      13、time_to_sec(time)：将指定时间转成秒的总数。

              select time_to_sec(now());--只转时间部分