MySQL常见函数使用

一：使用dual表

1 返回值:：大于或等于数值n的最小整数

SELECT CEIL(11.6) FROM DUAL;

 

 

 

 

 

2 返回值:：小于或等于数值n的最小整数

SELECT FLOOR(7.3) FROM DUAL;

 

 

 

3 mod（m,n）函数：将四舍五入，保留小数点后m位

SELECT MOD(7,5) FROM DUAL;

 

 

4 round（n，m）函数：将n四舍五入，保留小数点后m位

SELECT ROUND(324.234) FROM DUAL;

 

 

5 整个字符串小写

SELECT LOWER('CHINA') FROM DUAL;

 

6  replace（char，str1,str2）函数:字符串中所有str1换成str2

SELECT REPLACE('scott','s','Boy') FROM DUAL;

 

 

7  substr(char,m,n)函数：取出从m字符开始的n个字符的子串

SELECT SUBSTR('BILLYU',2,2) FROM DUAL;

 

 

8 substring(char,m,n)函数：取出从m字符开始的n个字符的子串

SELECT SUBSTRING('qwert',2,2) FROM DUAL;

 

 

9 

length(char)函数:求字符串的长度

SELECT LENGTH('ACD') FROM DUAL;

 

 

 

10 连接字符

SELECT CONCAT('Hello','--', 'world');

 

11 查看当前时间

SELECT NOW() FROM DUAL;

 

 

12 本月最后一天;

SELECT LAST_DAY(NOW()) FROM DUAL;

 

13 // 计算相差天数 TO_DAYS 把一个日期转为数字

SELECT TO_DAYS(NOW()) - TO_DAYS('20120512');

 

14 // 计算相差天数

SELECT DATEDIFF(NOW(),'20120512');

 

二  创建guanliyuan 表 并进行操作

CREATE TABLE guanliyuan(

id INT(30) NOT NULL AUTO_INCREMENT,

username VARCHAR(30) DEFAULT NULL,

sex VARCHAR(30) DEFAULT NULL,

grade INT(10),

dept VARCHAR(30) DEFAULT NULL,

PRIMARY KEY(id))

ENGINE = INNODB AUTO_INCREMENT =15 DEFAULT CHARSET =gb2312;

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('root','男',80,'CS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('12','男',90,'CS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('123','男',10,'CS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('root','男',80,'CS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('1234','男',90,'CS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('qq','男',35,'CS');

 

 

 

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('root','男',80,'BS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('12','男',90,'BS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('123','男',10,'BS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('root','男',80,'BS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('1234','男',90,'BS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('qq','男',35,'BS');

 

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('root','女',80,'CS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('12','女',90,'CS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('123','女',10,'CS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('root','女',80,'CS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('1234','女',90,'CS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('qq','女',35,'CS');

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('root','男',76,'CS'),('12','男',76,'CS');

 

SELECT * FROM guanliyuan WHERE id = 1;

DESC guanliyuan;

INSERT INTO guanliyuan(username,sex,grade,dept) VALUES('qer','女',25,'CS'),('bs','女',93,'CS');

SELECT id, username,sex FROM guanliyuan WHERE dept IN('CS','BC');

 

SELECT * FROM guanliyuan ORDER BY dept,grade DESC,id;

SELECT * FROM guanliyuan ORDER BY grade DESC,dept;

 

 

SELECT MAX(grade) FROM guanliyuan;

SELECT username,grade FROM guanliyuan WHERE grade = 90 ;

SELECT username ,grade,sex ,dept FROM guanliyuan WHERE grade = (SELECT MAX(grade) FROM guanliyuan);

SELECT * FROM guanliyuan WHERE grade =90;

SELECT COUNT(*) FROM guanliyuan; // 查看部门总人数

SELECT dept ,MAX(grade) FROM guanliyuan GROUP BY dept; // 查看部门中最搞分数

SELECT dept ,COUNT(id) FROM guanliyuan GROUP BY dept; // 查看每个部门的人数

SELECT * FROM guanliyuan WHERE username ='root' AND grade = 90;

 

SELECT * FROM guanliyuan;

SELECT * FROM guanliyuan WHERE grade = 90;

 

 

SELECT dept,sex ,COUNT(*) FROM guanliyuan GROUP BY dept,sex; // 查看两个组中的男女人数

SELECT dept ,sex,AVG(grade) FROM guanliyuan GROUP BY dept,sex; // 查看两个组中的男女平均分

 

SELECT dept,AVG(grade) FROM guanliyuan GROUP BY dept HAVING AVG(grade) >=64.2; //

 

 

CREATE INDEX susername ON guanliyuan(grade);

ALTER TABLE guanliyuan DROP INDEX susername;

 

SELECT username ,dept FROM guanliyuan WHERE grade <= 60

SELECT DISTINCT username ,dept FROM guanliyuan WHERE grade <= 60 ORDER BY dept;

 

// 复制表:

CREATE TABLE guanliyuan1 SELECT * FROM guanliyuan;

CREATE TABLE guanliyuan2(

id INT(30) NOT NULL AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(30) DEFAULT NULL);

INSERT INTO guanliyuan2(id,username) SELECT id,username FROM guanliyuan;

SELECT * FROM guanliyuan2;

 

SELECT * FROM guanliyuan1;

 

CREATE VIEW s_g(username,sex,gavg) AS SELECT dept ,sex,AVG(grade) FROM guanliyuan GROUP BY dept,sex;

// 创建视图表，数据来源于 guanliyuan表中的按照组分类，求平均值

SELECT * FROM s_g;

CREATE VIEW sbs(id,username,sex,grade,dept) AS SELECT * FROM guanliyuan WHERE dept = 'BS';

SELECT * FROM sbs;

INSERT INTO sbs(username,sex,grade,dept) VALUES('阿斯顿','女',25,'BS');

SELECT * FROM guanliyuan;

COMMIT;

 

 

15  // 将表的username字段以ro开头的改为adminot

UPDATE guanliyuan SET username = CONCAT('admin',SUBSTRING(username,3,2)) WHERE SUBSTRING(username,1,2) = 'ro';

16 / // 将表的username字段以admin开头的改为ro

UPDATE guanliyuan SET username = REPLACE(SUBSTRING(username,1,5),'admin','ro') WHERE SUBSTRING(username,1,5) = 'admin';

 

17  显示username的长度（不重复显示）

SELECT DISTINCT LENGTH(username) FROM guanliyuan;

 

18 显示username长度为2 的名字

SELECT username FROM guanliyuan WHERE LENGTH(username) = 2;