MySQL数据库学习

数据库的基本结构和基本查询

创建一个数据库

CREATE DATABASE companydb CHARACTER SET utf8;

查询部分列

SELECT EMPLOYEE_ID,FIRST_NAME,email FROM t_employees;

查询所有列
1.使用*的方式

SELECT * FROM t_employees;

2.使用列名的方式(推荐使用）（使用*号程序还是会将*转化为全部的列名，效率较低)

SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id
FROM t_employees;

查询所有员工的年薪

SELECT EMPLOYEE_ID AS '编号',FIRST_NAME AS '姓氏',salary*12 AS '年薪' FROM t_employees;
SELECT * FROM t_employees;

查询员工表中有多少个经理

SELECT DISTINCT manager_id FROM t_employees;
#按照员工工资进行升序排序
SELECT EMPLOYEE_ID,salary FROM t_employees ORDER BY salary ASC;
#按照员工工资进行降序排序
SELECT EMPLOYEE_ID,salary FROM t_employees ORDER BY salary DESC,EMPLOYEE_ID DESC;

条件查询
1.查询符合条件的数据

SELECT EMPLOYEE_ID,FIRST_NAME,salary
FROM t_employees
WHERE salary =11000;

SELECT EMPLOYEE_ID,FIRST_NAME,salary
FROM t_employees
WHERE salary = 24000;

2.查询符合两个条件的数据（and or not )

SELECT EMPLOYEE_ID,FIRST_NAME,salary 
FROM t_employees
WHERE salary = 11000 AND COMMISSION_PCT = 0.30 AND EMPLOYEE_ID='148';

SELECT EMPLOYEE_ID,FIRST_NAME,salary
FROM t_employees
WHERE salary = 11000 OR COMMISSION_PCT = 0.30;

SELECT EMPLOYEE_ID,FIRST_NAME,salary
FROM t_employees
WHERE NOT salary = 11000;

!= <> 两者（都为不等于）

SELECT EMPLOYEE_ID,FIRST_NAME,salary
FROM t_employees
WHERE salary <>11000;

查询区间(BETWEEN AND)

SELECT EMPLOYEE_ID,FIRST_NAME,salary
FROM t_employees
WHERE salary<=10000 AND salary >=6000;
#查询区间  between and
SELECT EMPLOYEE_ID,FIRST_NAME,salary
FROM t_employees
WHERE salary BETWEEN 10000 AND 6000;

查询NULL值（IS NULL 和IS NOT NULL 使用）

SELECT * FROM t_employees;
#查询出经理编号为NULL的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,MANAGER_ID
FROM t_employees
WHERE MANAGER_ID IS NULL;
#查询编号不为NULL的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,MANAGER_ID
FROM t_employees
WHERE MANAGER_ID IS NOT NULL;

枚举查询 IN(值1，值2，值3)

SELECT EMPLOYEE_ID,FIRST_NAME,salary,DEPARTMENT_ID 
FROM t_employees
WHERE DEPARTMENT_ID = 70 OR DEPARTMENT_ID = 80 OR DEPARTMENT_ID=90;
#两种方法的对比
SELECT EMPLOYEE_ID,FIRST_NAME,salary,DEPARTMENT_ID 
FROM t_employees
WHERE DEPARTMENT_ID IN(70,80,90);

模糊查询（LINK 'L_ '单个字符，LINK 'L%'不限字符）

#L开头的三个字的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,salary
FROM t_employees
WHERE first_name LIKE 'L__'
#查询所有以L开头的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,salary
FROM t_employees
WHERE FIRST_NAME LIKE 'L%'

when then 的使用和 AS 列命名的使用

#查询员工信息（编号，名字，薪资 , 薪资级别<对应条件表达式生成>）
SELECT EMPLOYEE_ID,FIRST_NAME,salary,
CASE
	WHEN salary>10000 THEN 'A'
	WHEN salary>=8000 AND salary<10000 THEN 'B'
	WHEN salary>=6000 AND salary<8000 THEN 'C'
	WHEN salary>=4000 AND salary<6000 THEN 'D'
	ELSE 'E'
END AS '薪资级别'

获取系统时间与日期

FROM t_employees;
#获取当前系统日期和时间
SELECT SYSDATE();
#当前系统日期
SELECT CURDATE();
#当前系统时间
SELECT CURTIME();
#获取指定日期为一年中的第几周
SELECT WEEK(SYSDATE());
#获取指定日期中的年份
SELECT YEAR('2020-4-1');
#获取小时值
SELECT HOUR(CURTIME());
#获取分钟值
SELECT MINUTE(CURTIME());
#指定日期之间的相隔天数
SELECT DATEDIFF('2020-4-1','2019-4-1');
#计算Date日期加上n天后的日期
SELECT ADDDATE('2020-4-1',4);

字符串操作

#多个字符串做拼接
SELECT CONCAT('MY','S','QL') AS 'MYSQL';
SELECT CONCAT(FIRST_NAME,LAST_NAME) AS ' 姓名' FROM t_employees;
#字符串替换(字符串函数的下标是从1开始)
#INSERT(str,pos,len,newstr)将str中指定pos位置开始len长度的内容替换为newstr
SELECT INSERT('这是一个数据库 ',3,2,'MYSQL') AS ' 替换';
#字符串大小写转换
SELECT LOWER('MYSQL') AS 'MYSQL';
SELECT UPPER('mysql') AS 'mysql';
#指定内容截取
SELECT SUBSTRING('JAVAMYSQLORACLE',5,5);

聚合函数

对多条数据的单列进行统计，返回统计后的一行结果

#求单列所有数据的和
SELECT SUM(salary) FROM t_employees;
#求单列所有数据的平均值
SELECT AVG(salary) FROM t_employees;
#求单列所有数据的最大值
SELECT MAX(SALARY) FROM t_employees;
#求单列所有数据的最小值
SELECT MIN(SALARY) FROM t_employees;
#求总行数 员工总数
SELECT COUNT(EMPLOYEE_ID) FROM t_employees;
#统计有提成的人数 会自动忽略null值，不进行统计
SELECT COUNT(COMMISSION_PCT) FROM t_employees;

分组查询和分组过滤

常见问题:分组查询中，SELECT显示的列只能是分组依据列，或者聚合函数列，不能出现其他列

#单列分组
#查询各部门的总人数；
SELECT DEPARTMENT_ID,COUNT(EMPLOYEE_ID)
FROM t_employees
GROUP BY DEPARTMENT_ID ;
#查询各部门的平均工资
SELECT DEPARTMENT_ID , AVG(salary)
FROM t_employees
GROUP BY DEPARTMENT_ID;
#多列分组
#查询各个部门，各个岗位的总人数
SELECT DEPARTMENT_ID,JOB_ID,COUNT(EMPLOYEE_ID)
FROM t_employees
GROUP BY DEPARTMENT_ID,JOB_ID;
#常见问题（分组查询中，SELECT显示的列只能是分组依据列，或者聚合函数列，不能出现其他列）
SELECT DEPARTMENT_ID,COUNT(*),FIRST_NAME
FROM t_employees
GROUP BY DEPARTMENT_ID;#ERROR

分组过滤查询(HAVING)

#统计60，0，0部门工资最高的
SELECT DEPARTMENT_ID , MAX(SALARY)
FROM t_employees
GROUP BY DEPARTMENT_ID
HAVING DEPARTMENT_ID IN (60,70,90)

限定查询 LIMIT offset_start,row_count

(注意行编号是从0开始)
应用于分页查询中，起始行是要变化的，但是一页显示的条数是不变的）

SELECT * FROM t_employees LIMIT 0,5;
SELECT * FROM t_employees LIMIT 5,5;
SELECT * FROM t_employees LIMIT 10,5;

查询范围记录

查询总结:
SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列 (ASC|DESC） LIMIT( 起始行，总条数)
SQL语句执行顺序:
– 1.FROM ; 指定数据来源表
– 2.WHERE ; 对查询数据做第一次过滤
– 3.GROUP BY ; 分组
– 4.HAVING ; 对分组后的数据第二次过滤
– 5.SELECT ; 查询各字段的值
– 6. ORDER BY ; 排序
– 7.LIMIT ; 限定查询结果

子查询

注意：将子查询“一行一列”的结果作为外部查询的条件，做第二次查询
子查询得到的一行一列的结果才能作为外部查询的等值判断条件或不等值判断

#查询工资大于Bruce的员工信息
#查询Bruce的工资
SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce';
#查询工资大于6000的员工信息
SELECT * FROM t_employees WHERE SALARY > 6000
#查询工资大于Bruce的员工信息
SELECT * FROM t_employees WHERE SALARY > (SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce');
#多行子查询(作为枚举查询的条件)
#查询名为King的部门员工信息
SELECT * FROM t_employees WHERE LAST_NAME = 'KING';

SELECT * FROM t_employees WHERE DEPARTMENT_ID IN (80,90);

SELECT * FROM t_employees	WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME = 'KING');
#多行单列时（ALL or ANY）
#查询工资高于60部门所有人的信息

#查询60部门所有人的工资
SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60;

SELECT * FROM t_employees WHERE salary > ALL(SELECT salary FROM t_employees WHERE DEPARTMENT_ID = 60)
#ALL 大于 最大值
SELECT * FROM t_employees WHERE SALARY > ALL(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60);
#ANY 大于 最小值
SELECT * FROM t_employees WHERE SALARY > ANY(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60);
#多行多列的结果集作为外部查询的表
SELECT EMPLOYEE_ID , FIRST_NAME , SALARY
FROM t_employees ORDER BY SALARY DESC;
#作为外部查询的表必须要为其赋予表名
SELECT EMPLOYEE_ID , FIRST_NAME , SALARY FROM (SELECT EMPLOYEE_ID , FIRST_NAME , SALARY FROM t_employees ORDER BY SALARY DESC) AS TEMP
LIMIT 0,5;

合并查询

– union ：合并两张表的结果（去除重复数据-所有的数据相同）
– 注意：两张表要列数相同，数据类型可以不同，数据将会纵向拼接在一起顺序为书写顺序。
– union ALL：合并两张表的结果（保留重复数据）连接形式和union一样
SELECT * FROM A1 UNION SELECT * FROM B2;
SELECT * FROM A1 UNION ALL SELECT * FROM B2;

内连接查询 两表连接（UNION JOIN ON ）

如果不指定连接条件 则会造成笛卡尔积的结果

#数据库标准
SELECT * FROM t_employees INNER JOIN t_jobs
ON t_employees.JOB_ID = t_jobs.JOB_ID;
#MYSQL
SELECT * FROM t_employees , t_jobs WHERE t_employees.JOB_ID = t_jobs.JOB_ID;
#多表连接
SELECT EMPLOYEE_ID , t_employees.DEPARTMENT_ID,t_locations.LOCATION_ID FROM t_employees
INNER JOIN t_departments
ON t_employees.DEPARTMENT_ID = t_departments.DEPARTMENT_ID
INNER JOIN t_locations
ON t_locations.LOCATION_ID = t_departments.LOCATION_ID;

左外连接和右外连接

#左外连接（以前面一张表为主表后面一张表进行匹配 ， 匹配不成功赋值为null
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,DEPARTMENT_NAME FROM t_employees
LEFT JOIN t_departments
ON t_departments.DEPARTMENT_ID = t_employees.DEPARTMENT_ID;
#右外连接与左外连接相似（以后面一张表为主表）
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,DEPARTMENT_NAME FROM t_employees
RIGHT JOIN t_departments
ON t_departments.DEPARTMENT_ID = t_employees.DEPARTMENT_ID;0

DML操作

新增

（INSERT）INSERT INTO 表名（列1，列2 ，列3，…）VALUE （值1，值2，值3…）
注意:表名后的列名和values里的值要一一对应（个数，顺序，类型）

INSERT INTO t_jobs(JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY)
VALUES('Java_Le','Java_Lecturer',2500,9000);
SELECT * FROM t_jobs;

SELECT * FROM t_employees;
INSERT INTO t_employees(EMPLOYEE_ID, FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID ,SALARY , COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES('207','STEVEN','GAVIN','GAVIN','650.501.3876','1998-07-01','SH_CLERK',8000,Null,124,50)

修改

（UPDATE）UPDATE 表名 SET 列1 =新值1，列2=新值2，… WHERE 条件；
注意：SET后多个列名 = 值，绝大多数情况下都要加 WHERE条件，指定修改，否则为整表更新

SELECT * FROM t_employees;
UPDATE t_employees SET SALARY = 25000 WHERE EMPLOYEE_ID = '100';
#多列修改
UPDATE t_employees SET JOB_ID = 'ST_MAN',SALARY = 3500 WHERE EMPLOYEE_ID = '135';

删除

（DELETE） DELETE FROM 表名 WHERE 条件 注意：删除时不加 WHERE条件，删除的是整张表的数据

#删除编号为135的员工
DELETE FROM t_employees WHERE EMPLOYEE_ID = '135';
SELECT * FROM t_employees;
#删除姓Peter，并且名为 hall的员工
SELECT * FROM t_employees WHERE FIRST_NAME= 'Peter';
DELETE FROM t_employees WHERE FIRST_NAME= 'Peter' AND LAST_NAME = 'HALL';

清空表(update 与 TRUNCATE)

(UPDATE)清空整张表数据只是清空表中数据结构不会发生改变
（TRUNCATE）TRUNCATE TABLE 表名；TRUNCATE 是将整张表销毁之后再按照原表新建一张表而 UPDATE是将这张表的数据删除表结构不变

MYSQL数据类型

数据表操作

创建表

#创建Subject表
CREATE TABLE `SUBJECT`(
		 SUBJECTID INT,
		 SUBJECTNAME VARCHAR (20),
		 SUBJECTHOURS INT
)CHARSET = utf8;#指定该表存储数据的字符集

SELECT * FROM `SUBJECT`;
INSERT INTO `SUBJECT`(SUBJECTID, SUBJECTNAME,SUBJECTHOURS)
VALUES(1,'JAVA',20);

数据表的修改操作（ALTER）

#1.向现有表中添加列
ALTER TABLE `SUBJECT` ADD GRADEID INT;
#2.修改表中的列（要写全且修改的是列的属性等内容）
ALTER TABLE `SUBJECT` MODIFY SUBJECTNAME VARCHAR(10);
#3.删除表中列(每次只能删除一列)
ALTER TABLE `SUBJECT` DROP GRADEID;
#4.修改表中的列名（修改后的列名且属性等也要写全）
ALTER TABLE `SUBJECT` CHANGE SUBJECTHOURS CLASSHOURS INT ;
#修改表名
ALTER TABLE `SUBJECT` RENAME `SUB`;
SELECT * FROM `SUB`;

删除表

DROP TABLE `SUBJECT`;

实体完整性约束

1.主键约束（PRIMARY KEY）唯一，标识表中的一行数据，此列的值不可重复，且不能为null
2.唯一约束 UNIQUE 唯一，标识表中的一行数据，不可重复，可以为null；
#自动增长列（AUTO_INCREMENT）,自动增长，给主键 数值 列添加自动增长，从1开始，每次加1，不能单独使用，和主键配合

#创建Subject表
CREATE TABLE `SUBJECT`(
		 SUBJECTID INT PRIMARY KEY AUTO_INCREMENT ,
		 SUBJECTNAME VARCHAR (20) UNIQUE,
		 SUBJECTHOURS INT
)CHARSET = utf8;
SELECT * FROM `SUBJECT`;
INSERT INTO `SUBJECT`(SUBJECTNAME,SUBJECTHOURS)
VALUES('JAVA',20);
DELETE FROM `SUBJECT` WHERE SUBJECTID = 1;