java 数据库学习-CSDN博客

本文链接：https://blog.csdn.net/ITjingxinbao/article/details/130609020

时间变量的应用

CREATE TABLE t14 (
birthday DATE,
jobtime DATETIME,
logintime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP);

SELECT
	* 
FROM
	t14;
INSERT INTO t14(birthday,jobtime) VALUES (
'2022-11-11','2022-11-11 10:10:10');

小作业

CREATE TABLE `emp`(
id INT,
`name` VARCHAR(32),
sex CHAR(1),
brithday DATE,
entrydata DATETIME,
job VARCHAR(32),
salary DOUBLE,
`resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
INSERT INTO `emp` VALUES(100,'小妖怪','男','2000-11-11',
'2000-11-10 11:11:11','恤衫',3000,'客诉');
SELECT *FROM `emp`;

修改练习表的emp

ALTER TABLE emp ADD image VARCHAR(32) NOT NULL DEFAULT ''
AFTER RESUME -- 在emp表添加一个image列
DESC emp -- 显示表结构 
ALTER TABLE emp 
MODIFY job VARCHAR(60) NOT NULL DEFAULT ''-- 修改job列长度60
ALTER TABLE emp DROP sex
RENAME TABLE emp TO employee
ALTER TABLE employee CHARACTER SET utf8
ALTER TABLE employee CHANGE name user_name VARCHAR(64) NOT NULL
DEFAULT ''
DESC employee

update命令练习

UPDATE employee SET salary=5000
UPDATE employee SET slary=3000 WHERE user_name='小妖怪'
INSERT INTO employee VALUES(200,'老妖怪','1990-11-11','2000-11-11 10:10:10'
,'捶背的',5000,'给大王捶背','adfg');
UPDATE employee SET salary=salary+1000 WHERE user_name='老妖怪'
SELECT *FROM employee;

delete语句

DELETE FROM employee WHERE user_name='老妖怪';
DELETE  FROM employee;
SELECT *FROM employee;
-- delete不能删除某一列的值
UPDATE employee SET job='' WHERE user_name='小妖怪';

select语句的使用

-- select 语句【重点 难点】
CREATE TABLE student(
	id INT NOT NULL DEFAULT 1,
	NAME VARCHAR(20) NOT NULL DEFAULT '',
	chinese FLOAT NOT NULL DEFAULT 0.0,
	english FLOAT NOT NULL DEFAULT 0.0,
	math FLOAT NOT NULL DEFAULT 0.0
);

INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'韩顺平',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'张飞',67,98,56);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'宋江',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'关羽',88,98,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'赵云',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'欧阳锋',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(8,'韩信',45,65,99);

SELECT * FROM student;

-- 查询表中所有学生的信息。
SELECT * FROM student;
-- 查询表中所有学生的姓名和对应的英语成绩。
SELECT `name`,english FROM student;
-- 过滤表中重复数据 distinct 。
SELECT DISTINCT english FROM student;
-- 要查询的记录，每个字段都相同，才会去重
SELECT DISTINCT `name`, english FROM student;

select继续使用

-- select 语句的使用

-- 统计每个学生的总分
SELECT `name`, (chinese+english+math) FROM student;
-- 在所有学生总分加10分的情况
SELECT `name`, (chinese + english + math + 10) FROM student;
-- 使用别名表示学生分数。
SELECT `name` AS '名字', (chinese + english + math + 10) AS total_score 
	FROM student;
	-- select 语句
-- 查询姓名为赵云的学生成绩
SELECT * FROM student 
	WHERE `name` = '赵云'
-- 查询英语成绩大于90分的同学
SELECT * FROM student 
	WHERE english > 90
-- 查询总分大于200分的所有同学

SELECT * FROM student 
	WHERE (chinese + english + math) > 200
	
-- 查询math大于60 并且(and) id大于4的学生成绩
SELECT * FROM student
	WHERE math >60 AND id > 4
-- 查询英语成绩大于语文成绩的同学
SELECT * FROM student
	WHERE english > chinese
-- 查询总分大于200分 并且 数学成绩小于语文成绩,的姓赵的学生.
-- 赵% 表示 名字以韩开头的就可以
SELECT * FROM student
	WHERE (chinese + english + math) > 200 AND 
		math < chinese AND `name` LIKE '赵%'
-- 查询英语分数在 80－90之间的同学。
SELECT * FROM student
	WHERE english >= 80 AND english <= 90;
SELECT * FROM student
	WHERE english BETWEEN 80 AND 90; -- between .. and .. 是 闭区间
-- 查询数学分数为89,90,91的同学。
SELECT * FROM student 
	WHERE math = 89 OR math = 90 OR math = 91;
SELECT * FROM student 
	WHERE math IN (89, 90, 91);
-- 查询所有姓李的学生成绩。
SELECT * FROM student 
	WHERE `name` LIKE '韩%'
-- 查询数学分>80，语文分>80的同学

排序`

SELECT * FROM student ORDER BY math;
SELECT `name`,(chinese+english+math) AS total FROM student 
ORDER BY total DESC;
SELECT * FROM  student WHERE `name` LIKE '韩%'
ORDER BY math;

统计函数

SELECT COUNT(*) FROM student;
SELECT COUNT(*) FROM student WHERE math>80
SELECT COUNT(*) FROM student WHERE (math+chinese+english)>250
CREATE TABLE t15( `name` VARCHAR(20));
INSERT INTO  t15 VALUES ('tom');
INSERT INTO t15 VALUES('jack');
INSERT INTO t15 VALUES ('hsp');
INSERT into t15 VALUES(NULL);
SELECT *FROM t15;
SELECT COUNT(*) FROM t15;-- 4
SELECT COUNT(`name`) FROM t15;-- 3
SELECT SUM(math) FROM student; 
SELECT SUM(math) AS mathtotal ,SUM(chinese) AS china, SUM(english) AS
englishtotal  FROM student;
SELECT SUM(math+chinese+english) FROM student;
SELECT SUM(math)/COUNT(*) FROM student;--求总和
SELECT AVG(math) FROM student;--求平均值
SELECT MAX(math+english+chinese),MIN(math+english+chinese) FROM student;
SELECT MAX(math) AS mathhigh,MIN(math) AS math_low FROM student;

分组使用

CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0, 
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
);

INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), 
(20, 'RESEARCH', 'DALLAS'), 
(30, 'SALES', 'CHICAGO'), 
(40, 'OPERATIONS', 'BOSTON');

SELECT * FROM dept;

-- 员工表

CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED ,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) ,/*红利 奖金*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
);

-- 添加测试数据
 INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), 
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),  
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),  
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),  
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),  
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),  
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),  
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),  
(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),  
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),  
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),  
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),  
(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);

SELECT * FROM emp;

-- 工资级别
#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*工资级别*/ 
losal DECIMAL(17,2)  NOT NULL, /* 该级别的最低工资 */
hisal DECIMAL(17,2)  NOT NULL /* 该级别的最高工资*/
);

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

SELECT * FROM salgrade;
SELECT * FROM dept;
SELECT * FROM emp;

# 演示group by + having
GROUP by用于对查询的结果分组统计, (示意图)
-- having子句用于限制分组显示结果.
-- ?如何显示每个部门的平均工资和最高工资
-- 老韩分析: avg(sal) max(sal)
-- 按照部分来分组查询
SELECT AVG(sal), MAX(sal) , deptno 
	FROM  emp GROUP BY deptno; 
-- 使用数学方法，对小数点进行处理
SELECT FORMAT(AVG(sal),2), MAX(sal) , deptno 
	FROM  emp GROUP BY deptno; 

-- ?显示每个部门的每种岗位的平均工资和最低工资
-- 老师分析 1. 显示每个部门的平均工资和最低工资
--          2. 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal) , deptno, job 
	FROM  emp GROUP BY deptno, job; 

-- ?显示平均工资低于2000的部门号和它的平均工资 // 别名

-- 老师分析 [写sql语句的思路是化繁为简,各个击破]
-- 1. 显示各个部门的平均工资和部门号
-- 2. 在1的结果基础上，进行过滤，保留 AVG(sal) < 2000
-- 3. 使用别名进行过滤 

SELECT AVG(sal), deptno 
	FROM emp GROUP BY deptno
		HAVING AVG(sal) < 2000;
-- 使用别名		
SELECT AVG(sal) AS avg_sal, deptno 
	FROM emp GROUP BY deptno
		HAVING avg_sal < 2000;

字符串函数使用

SELECT CHARSET(ename) from emp;--返回字串字符集
SELECT CONCAT(ename,' job is ',job) FROM emp;--连接字串，将多列拼成一列
SELECT INSTR ('hanshunping','ping') FROM DUAL;--返回substring在string中出现的位置，没有返回0，daul是亚元素作为测试表
SELECT LCASE(ename) FROM emp;--转小写
SELECT LEFT(ename,2) FROM emp;--left从左边取length个字符，right从右边取length个字符
SELECT LENGTH(ename) FROM emp;--length计算字符串的长度按照字节计算 
select ename,REPLACE(job,'MANAGER','经理')FROM emp;--将str中的replace——str替换成search-str
SELECT STRCMP('hsp','jsp')FROM DUAL;--逐字符比较字串大小
SELECT SUBSTRING(ename,1,2) FROM emp;--从ename列的第一个位置开始取出二个字符
SELECT LTRIM('   韩水平教育')FROM DUAL;--去除左空格
SELECT RTRIM('韩水平教育   ')FROM DUAL;--去除右空格
SELECT TRIM('    韩水平教育   ')FROM DUAL;--去除左右空格
-- 以首字母小写显示所有员工
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)), SUBSTRING(ename,2))  AS name  FROM emp;

时间函数的使用

SELECT CURRENT_DATE() FROM DUAL;--当前日期
SELECT CURRENT_TIME() FROM DUAL;--当前时间
SELECT CURRENT_TIMESTAMP() FROM DUAL;--当前时间戳
CREATE TABLE mes(
id INT,
content VARCHAR(30),
send_time DATETIME);
INSERT INTO mes VALUES(1,'北京新闻',CURRENT_TIMESTAMP());
INSERT INTO mes VALUES(2,'北京新闻',NOW());
INSERT INTO mes VALUES(3,'北京新闻','2022-11-11');
SELECT * FROM mes;
SELECT NOW()FROM DUAL;
--显示所有新闻消息，不显示消息
SELECT id,content,DATE(send_time) FROM mes;
--请查询10分钟内的发布消息
SELECT * FROM mes WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE)>=NOW();
SELECT *FROM mes WHERE send_time>=DATE_SUB(NOW(),INTERVAL 10 MINUTE);
SELECT DATEDIFF('2011-11-11','1990-01-01') FROM DUAL;--时间相差多少天
SELECT DATEDIFF(NOW(),'2001-11-26') FROM DUAL;--求活了多少天
 SELECT DATEDIFF(DATE_ADD('2001-11-26',INTERVAL 80 YEAR),NOW()) FROM DUAL;--如果能活80岁，求出还能活多久
 SELECT TIMEDIFF('10:11:11','06:10:10') FROM DUAL;