sql2练习

USE ex02
#字符串相关函数
CHARSET(str)#返回子串字符集比如utf8
CONCAT(STRING[,...])#连接子串
INSTR(STRING,SUBSTRING) #返回substring在string中的位置

#字符串相关函数的使用
#字符串相关函数
#返回字符串字符集
SELECT  CHARSET(ename) FROM emp
#concat( , )连接字符串
SELECT CONCAT(ename,'工作是',job) AS '个人信息' FROM emp

#查询字符在字符串中出现的位置,在没表的时候使用dual亚元表
SELECT INSTR('yukewei','wei') FROM DUAL
 
CREATE TABLE animals(
 
num MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',   #编号
NAME VARCHAR(20) NOT NULL DEFAULT '', #名字
food VARCHAR(9) NOT NULL DEFAULT ''
  
 )
SELECT*FROM animals
INSERT INTO animals VALUES(001,'DOG','meat')
INSERT INTO animals VALUES(002,'CAT','manyfish')
INSERT INTO animals VALUES(003,'COW','eggs')
UPDATE animal SET num=003 WHERE NAME='cat';
#ucase(stirng)转大写 lcase转小写
SELECT UCASE(NAME),food FROM animals
SELECT num,LCASE(NAME) AS '名字',food FROM animals

#left（string,2）从左边开始取两个字节
SELECT LEFT(food,2) FROM animals
SELECT RIGHT(food,3) FROM animals

#length（）返回长度，按照字节
SELECT LENGTH(123456) FROM DUAL
SELECT LENGTH('余科葳') FROM DUAL

#replace(列, 替换前,替换后 )替换字符
SELECT NAME,REPLACE(food,'eggs','草') AS food FROM animals;

#strcmp逐字符比较两字符大小
SELECT STRCMP('aaa','ccc') FROM DUAL

#substring(string,a,b)截取从第a个字符开始取b个
SELECT SUBSTRING(NAME,1,2) FROM animal

#LTRIM(),RTRIM(),TRIM()去除左边，右边，两边的空格
SELECT RTRIM('ykw'         )AS NAME FROM DUAL
SELECT LTRIM(                'ykw')AS NAME FROM DUAL
SELECT TRIM(                'ykw'     )AS NAME FROM DUAL

SELECT num,REPLACE(NAME,SUBSTRING(NAME,1,1),LCASE(SUBSTRING(NAME,1,1))) AS NAME FROM animals
SELECT num,REPLACE(NAME,LEFT(NAME,1),LCASE(LEFT(NAME,1))) AS NAME FROM animals
SELECT num,CONCAT(LCASE(LEFT(NAME,1)),SUBSTRING(NAME,2))AS NAME FROM animals

#数学相关函数
#绝对值
SELECT ABS(-10) FROM DUAL
#十进制转二进制
SELECT BIN(3) FROM DUAL
#向上取整
SELECT CEILING(5.2) FROM DUAL
#向下取整
SELECT FLOOR(-1.1) FROM DUAL
#进制转换,把十进制的8转换成2进制
SELECT CONV(8,10,2)
#保留小数
SELECT FORMAT(7.123545666,2) FROM DUAL
#十进制转十六进制
SELECT HEX(10) FROM DUAL
#least( , , , ,)取最小值
SELECT LEAST(1,2,3,4,5,6,8) FROM DUAL
#求余
SELECT MOD(10,4) FROM DUAL;
#返回随机数0《v《1.0
 SELECT RAND() FROM DUAL;
 #中间加数字返回随机数不变
  SELECT RAND(1) FROM DUAL;
   SELECT RAND(2) FROM DUAL;