mysql SQL语言

最新推荐文章于 2022-08-30 16:50:36 发布

kingyal

最新推荐文章于 2022-08-30 16:50:36 发布

阅读量395

点赞数

文章标签： sql mysql java

本文链接：https://blog.csdn.net/qq_15897815/article/details/115435637

版权

一. SQL语言分类：
数据查询语言：DQL（data duery language）：SELECT , WHERE, ORDER BY, GROUP BY, HAVING
数据定义语言：DDL（data definition language）:CREATE, ALTER, DROP
数据操作语言：DML（data manipulation language）：INSERT, UPDATE, DELETE
事务处理语言：TPL（transaction PROCESS language）:COMMIT, ROLLBACK
数据控制语言：DCL（data control languare）:GRANT, REVOKE

二. SQL语言学习

#使用控制台方式登录
mysql -uroot -p1234
#使用SQLLog登录
输入账号与密码即可

#创建用户
CREATE USER ‘kingyal’ IDENTIFIED BY ‘1234’;

#显示所有表
SHOW TABLES;
#查询所有数据库
SHOW DATABASES;
CREATE DATABASE IF NOT EXISTS mydb1;
#查看创建信息
SHOW CREATE DATABASE mydb1;
#修改数据库
ALTER DATABASE mydb1 CHARACTER SET utf8;
#删除数据库
DROP DATABASE mydb1;
#查看当前所使用的数据库
SELECT DATABASE();
#使用数据库
USE companydb;

#查询部分类
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY FROM t_employees;
#查询所有列
SELECT * FROM t_employees;
#实际环境下,优先使用列名查询；*的方式效率低，可读性差
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 *
FROM t_departments;

SELECT DEPARTMENT_ID,DEPARTMENT_NAME, MANAGER_ID,LOCATION_ID
FROM t_departments;

SELECT *
FROM t_countries;

SELECT *
FROM t_jobs;

#对列中的数据进行计算（算数运算符：+,-,,/）
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY12
FROM t_employees;

#列的别名
SELECT EMPLOYEE_ID AS ‘编号’,FIRST_NAME AS ‘姓氏’, SALARY*12 AS ‘年薪’
FROM t_employees;

#查询结果去重
SELECT DISTINCT MANAGER_ID
FROM t_employees;

#排序
SELECT DISTINCT MANAGER_ID
FROM t_employees
ORDER BY manager_id ASC;

#排序
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY
FROM t_employees ORDER BY SALARY;

#条件查询
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM t_employees
WHERE salary >= 11000;

#l逻辑判断
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM t_employees
WHERE salary >= 11000 && COMMISSION_PCT = 0.30;

#查询员工薪资在6000-10000之间的员工信息（编号，名字，薪资）
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY
FROM t_employees
WHERE SALARY >= 6000 && SALARY <= 10000;

#区间判断(between and),闭区间，包含区间两个值
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY
FROM t_employees
WHERE SALARY BETWEEN 6000 AND 10000;

#Null值判断
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
FROM t_employees
WHERE COMMISSION_PCT IS NULL;

SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
FROM t_employees
WHERE COMMISSION_PCT IS NOT NULL;

#枚举查询(IN(值1，值2，值3)),但是 IN的查询效率较低。可通过多条件拼接
#查询部门编号为70，80，90的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID
FROM t_employees
WHERE DEPARTMENT_ID IN (70,80,90);

#模糊查询
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID
FROM t_employees
WHERE FIRST_NAME LIKE ‘L%’;

SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID
FROM t_employees
WHERE FIRST_NAME LIKE ‘L__’;

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,
CASE
WHEN SALARY >= 10000 THEN ‘A’
WHEN SALARY >= 8000 && SALARY < 10000 THEN ‘B’
WHEN SALARY >= 6000 && SALARY < 8000 THEN ‘C’
WHEN SALARY >= 4000 && SALARY < 6000 THEN ‘D’
ELSE ‘E’
END AS ‘级别’
FROM t_employees;

#时间查询
#当前系统时间（年月日时分秒）
SELECT SYSDATE();
#获取当前日期（年月日）
SELECT CURDATE();
#查询当前时间（年月日时分秒）
SELECT NOW();
#获取当前时间（时分秒）
SELECT CURTIME();
#指定日期为当前的第几周
SELECT WEEK(‘2022-04-03’);
SELECT WEEK(CURDATE());
#获取指定日期的年份
SELECT YEAR(‘2021-04-03’);
SELECT YEAR(CURDATE());
#获取指定时间的小时值
SELECT HOUR(‘21:58:26’);
SELECT HOUR(CURTIME());
#获取时间的分钟值
SELECT MINUTE(‘21:58:26’);
SELECT MINUTE(CURTIME());
#获取date1与date2之间相隔的天数
SELECT DATEDIFF(‘2021-4-3’, ‘2021-4-1’);
SELECT DATEDIFF(CURDATE(), ‘2019-7-15’);
#计算date加上n天后的日期
SELECT ADDDATE(‘2021-04-10’, 5);
SELECT ADDDATE(CURDATE(), 3);

#字符串应用
SELECT CONCAT(‘my’,‘S’,‘ql’);
SELECT INSERT(‘mySql’, 3, 4, ‘java’);
SELECT LOWER(‘MYSQL’);
SELECT UPPER(‘mysql’);
SELECT SUBSTRING(‘javaSqlOracle’, 5, 3);

#聚合函数,对多条数据的单列进行统计，返回统计后的一行结果;聚合函数自动忽略null值，不进行统计
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(SALARY) FROM t_employees;
SELECT COUNT(COMMISSION_PCT) FROM t_employees;
SELECT COUNT(EMPLOYEE_ID) FROM t_employees;

#分组查询
#查询各个部门的总人数
#1. 按照部门编号进行分组
#2. 再针对各部门人数进行统计
SELECT DEPARTMENT_ID, COUNT(EMPLOYEE_ID)
FROM t_employees
GROUP BY DEPARTMENT_ID;

#查询各部门的平均工资
#1. 按照部门编号进行分组
#2. 再对各个部门的平均工资进行统计
SELECT DEPARTMENT_ID, AVG(salary)
FROM t_employees
GROUP BY DEPARTMENT_ID;

#查询各部门的各个岗位的人数
#1. 按照部门编号进行分组
#2. 按照岗位名称进行分子
#3. 针对每个部门的各个岗位进行人数统计
SELECT DEPARTMENT_ID, job_id, COUNT(EMPLOYEE_ID)
FROM t_employees
GROUP BY DEPARTMENT_ID, job_id;

SELECT DEPARTMENT_ID, job_id, COUNT(EMPLOYEE_ID)
FROM t_employees
WHERE DEPARTMENT_ID IN(70,80,90,100)
GROUP BY DEPARTMENT_ID, job_id;

#分组过滤
#统计部门最高工资
#确定分组依据
#对分组后的数据，过滤出部门编号是60，70，90的信息
#max()函数处理
SELECT DEPARTMENT_ID, MAX(salary)
FROM t_employees
WHERE DEPARTMENT_ID IN(60, 70, 90)
GROUP BY DEPARTMENT_ID;

SELECT DEPARTMENT_ID AS ‘部门编号’, MAX(salary) AS ‘最高薪资’
FROM t_employees
GROUP BY DEPARTMENT_ID
HAVING DEPARTMENT_ID IN(60, 70, 90);

#限定查询
SELECT department_id,SALARY
FROM t_employees
LIMIT 0, 10;

#查询范围记录
SELECT * FROM t_employees LIMIT 0,10;
SELECT * FROM t_employees LIMIT 10,10;
SELECT * FROM t_employees LIMIT 20,10;

#子查询（作为条件判断）
#查询工资大于bruce的员工信息
#1 查到bruce的工资，一行一列
SELECT SALARY
FROM t_employees
WHERE FIRST_NAME = ‘bruce’;
#2 查询工资大于bruce的员工信息
SELECT *
FROM t_employees
WHERE SALARY > 6000;
#3 整合
SELECT *
FROM t_employees
WHERE SALARY > (SELECT SALARY FROM t_employees WHERE FIRST_NAME = ‘bruce’);

#子查询（作为枚举条件）
#查询与名为king同一部门的员工信息
#查询king在哪个部门
SELECT department_id
FROM t_employees
WHERE last_name = ‘King’;
#查询对应部门的员工信息
SELECT *
FROM t_employees
WHERE department_id IN(SELECT department_id FROM t_employees WHERE last_name = ‘King’);

#工资高于60部门所有人的信息
#查询60部门所有人的工资
SELECT salary
FROM t_employees
WHERE department_id = 60;
#查询高于60部门所有人的工资的员工信息（高于所有人）
SELECT *
FROM t_employees
WHERE salary > ALL(SELECT salary FROM t_employees WHERE department_id = 60);
#查询高于60部门的工资的员工信息（高于部分人）
SELECT *
FROM t_employees
WHERE salary > ANY(SELECT salary FROM t_employees WHERE department_id = 60);

#子查询（作为一张表）
#查询员工工资中，排名前五的员工信息
#先进行排序，得到临时表
SELECT *
FROM t_employees
ORDER BY SALARY DESC;
#再查询临时表中前五行员工信息,使用临时表时，需要为其赋一个名字
SELECT *
FROM (SELECT * FROM t_employees ORDER BY SALARY DESC) AS tmp
LIMIT 0, 5;

#合并查询
#union去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;
#union all 不去除重复记录
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

#表连接查询
#内连接查询
SELECT * FROM t_employees INNER JOIN t_jobs ON t_employees.JOB_ID = t_jobs.JOB_ID; #SQL通用写法
SELECT * FROM t_employees, t_jobs WHERE t_employees.JOB_ID = t_jobs.JOB_ID; #mysql写法

#查询所有员工的工号，名字，部门名称，部门所在国家id
SELECT * FROM t_employees
INNER JOIN t_departments
ON t_employees.DEPARTMENT_ID = t_departments.DEPARTMENT_ID
INNER JOIN t_locations
ON t_departments.LOCATION_ID = t_locations.LOCATION_ID;

SELECT * FROM t_employees e
INNER JOIN t_departments d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
INNER JOIN t_locations l
ON d.LOCATION_ID = l.LOCATION_ID;

#左外连接:以左表为主，依次向右匹配，匹配到，则返回结果；匹配不到，以null填充。
#查询所有员工的信息，以及对应的部门名称（没有部门的员工，也在查询结果中，部门名称以null填充）
SELECT * FROM t_employees
LEFT JOIN t_departments
ON t_employees.DEPARTMENT_ID = t_departments.DEPARTMENT_ID;

SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.LAST_NAME, d.DEPARTMENT_ID
FROM t_employees e
LEFT JOIN t_departments d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

#右外连接：以左表为主，依次向右匹配，匹配到，则返回结果；匹配不到，以null填充。
SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.LAST_NAME, d.DEPARTMENT_ID
FROM t_employees e
RIGHT JOIN t_departments d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

#新增,表名后的列名要和values里面的值一一对应起来（个数，顺序，类型）
INSERT t_jobs(job_ID, job_title, MIN_SALARY, MAX_SALARY) VALUES (‘IT_GTS’, ‘SROFWARE_MANAGER’, 15000, 500000);
SELECT * FROM t_jobs;

INSERT t1(id, NAME) VALUES(4,‘liqi’);
INSERT t1(id) VALUES(6);
SELECT * FROM t1;

INSERT t2(st_id, st_NAME) VALUES(1,‘liqi’);

#修改,set后面多个列名=值，绝大多数情况下要加where条件作为指定修改，否则为整表更新。
UPDATE t1 SET NAME = ‘kinga’ WHERE id = 6;

#删除，如果不加where条件，删除的是整张表的数据
DELETE FROM t_jobs WHERE MAX_SALARY >= 50000;
DELETE FROM t2;
#清空整张表的数据:不同于delete，truncate是将整张表销毁，再按照原表格式创建一张新表
SELECT * FROM t2;
TRUNCATE TABLE t2;

#数据表操作
CREATE TABLE subject1 (
subjectId INT,
subjectName VARCHAR(20),
subjectHours INT
) CHARSET = utf8;

TRUNCATE TABLE subject1;
SELECT * FROM subject1;

INSERT INTO subject1(subjectId, subjectName, subjectHours) VALUES(1, ‘java’, 10);
INSERT INTO subject1(subjectId, subjectName, subjectHours) VALUES(2, ‘c++’, 10);

#数据表的修改
#向现有表中添加列
ALTER TABLE subject1 ADD grade INT;
#修改表中的列的属性
ALTER TABLE subject1 MODIFY subjectName VARCHAR(30);
#删除表中的列,每次只能删除一列
ALTER TABLE subject1 DROP grade;
#修改列名
ALTER TABLE subject1 CHANGE subjectHours classHours INT;
#数据表的删除
#数据表的删除
DROP TABLE subject1;

#实体完整性约束
#主键约束:唯一性，标识表中的一行数据，此列的值不可重复，且不能为null
CREATE TABLE subject1 (
subjectId INT PRIMARY KEY, #课程编号标识每个课程的编号唯一，且不能为null
subjectName VARCHAR(20) UNIQUE,
classHours INT
) CHARSET = utf8;
INSERT INTO subject1(subjectId, subjectName, classHours) VALUES(1, ‘java’, 10);
INSERT INTO subject1(subjectId, subjectName, classHours) VALUES(2, ‘c++’, 10);
INSERT INTO subject1(subjectId, subjectName, classHours) VALUES(3, ‘c#’, 10);
SELECT * FROM subject1;
#唯一约束:unique，唯一，标识表中的一行数据，不可重复，可以为null
CREATE TABLE subject1 (
subjectId INT PRIMARY KEY, #课程编号标识每个课程的编号唯一，且不能为null
subjectName VARCHAR(20) UNIQUE,
classHours INT
) CHARSET = utf8;
#自动增长列 auto_increment：自动增长，给主键数值列添加自动增长，从1开始，每次加1，不能单独使用，和主键配合
#数据表的删除
DROP TABLE subject1;
CREATE TABLE subject1 (
subjectId INT PRIMARY KEY AUTO_INCREMENT, #课程编号标识每个课程的编号唯一，且不能为null
subjectName VARCHAR(20) UNIQUE,
classHours INT
) CHARSET = utf8;
INSERT INTO subject1(subjectName, classHours) VALUES(‘javascript’, 10);
INSERT INTO subject1(subjectName, classHours) VALUES(‘c’, 10);
SELECT * FROM subject1;

#域完整性约束：限制单元格的数据正确性
#非空约束 not null
DROP TABLE subject1;
CREATE TABLE subject1 (
subjectId INT PRIMARY KEY AUTO_INCREMENT, #课程编号标识每个课程的编号唯一，且不能为null
subjectName VARCHAR(20) UNIQUE NOT NULL,
classHours INT
) CHARSET = utf8;
INSERT INTO subject1(subjectName, classHours) VALUES(‘javascript’, 10);
INSERT INTO subject1(subjectName, classHours) VALUES(‘c++’, 10);
SELECT * FROM subject1;
#默认值约束 DEFAULT:为列赋予默认值，当新增数据不指定值时，书写default,以指定的默认值进行填充
CREATE TABLE subject1 (
subjectId INT PRIMARY KEY AUTO_INCREMENT, #课程编号标识每个课程的编号唯一，且不能为null
subjectName VARCHAR(20) UNIQUE NOT NULL,
classHours INT DEFAULT 20
) CHARSET = utf8;
INSERT INTO subject1(subjectName, classHours) VALUES(‘javascript’, DEFAULT);
INSERT INTO subject1(subjectName) VALUES(‘c++’);

#引用完整性约束
DROP TABLE Spectality;
SELECT * FROM Spectality;
CREATE TABLE Spectality ( #专业表
Id INT PRIMARY KEY AUTO_INCREMENT,
specialName VARCHAR(20) UNIQUE NOT NULL
) CHARSET = utf8;

#引用专业表里面的id作为外键，新增课程信息时，约束课程所属的专业
) CHARSET = utf8;

DROP TABLE subject1;
CREATE TABLE subject1 ( #课程表(课程表的SpecialId, 引用专业表的id)
subjectId INT PRIMARY KEY AUTO_INCREMENT, #课程编号标识每个课程的编号唯一，且不能为null
subjectName VARCHAR(20) UNIQUE NOT NULL,
classHours INT DEFAULT 20,
specialId INT NOT NULL,
CONSTRAINT fk_subject1_specialId FOREIGN KEY(specialId) REFERENCES Spectality(Id)
)CHARSET = utf8;

SELECT * FROM Spectality;
INSERT INTO Spectality(specialName) VALUES(‘嵌入式’);
INSERT INTO Spectality(specialName) VALUES(‘互联网’);
INSERT INTO Spectality(specialName) VALUES(‘大数据’);

SELECT * FROM subject1;
INSERT INTO subject1(subjectName,specialId) VALUES(‘java’, 1);
INSERT INTO subject1(subjectName,specialId) VALUES(‘c’, 1);

#约束创建整合
DROP TABLE grade;
SELECT * FROM grade;
CREATE TABLE grade (
GradeId INT PRIMARY KEY AUTO_INCREMENT,
gradeName VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;

#使用上表中的gradeId作为外键
DROP TABLE student;
SELECT * FROM student;
CREATE TABLE student (
studentId VARCHAR(50) PRIMARY KEY,
studnetName VARCHAR(50) NOT NULL,
sex CHAR(2) DEFAULT ‘男’,
borndate DATE NOT NULL,
phone VARCHAR(11),
gradeId INT NOT NULL,
CONSTRAINT fk_student_gradeId FOREIGN KEY(gradeId) REFERENCES Grade(GradeId)
) CHARSET=utf8;
#创建时，要先创建主表，再创建从表
#删除时，要先删除从表，在删除主表。

#事务:是一个原子操作，是一个最小单元，可以又一个或多个SQL语句组成，在同一个事务中，所有的SQL语句都成功执行时，整个事务
#成功，有一条SQL语句失败时，整个事务都执行失败。
#事务的特性：
#Atomicity(原子性):表示事务是一个整体，要么全部成功，要么全部失败。
#Consistency(一致性):表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前的状态
#Isolation(隔离性):事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，
#要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。
#Durability(持久性):持久性事务完成后，它对于系统的影响是永久性的。
#事务的应用环境：基于增删改语句的操作结果（均返回操作后受影响的行数），可通过程序逻辑手动控制事务提交或者回滚。

DROP TABLE Account;
SELECT * FROM Account;
CREATE TABLE Account (
id INT,
money INT
)CHARSET=utf8;

INSERT INTO Account(id, money) VALUES (1, 10000);
INSERT INTO Account(id, money) VALUES (2, 5000);
UPDATE Account SET money = money + 1000 WHERE id = 1;
UPDATE Account SET money = money - 1000 WHERE id = 2;

#SET AutoCommit = 0; # 0,禁止自动提交；1,开启自动提交
START TRANSACTION; SET AutoCommit = 0;
UPDATE Account SET money = money + 1000 WHERE id = 1;
UPDATE Account SET money = money - 1000 WHERE id = 2;
COMMIT;
ROLLBACK;

#创建用户
CREATE USER ‘kingyal’ IDENTIFIED BY ‘1234’;
#删除账号
DROP USER ‘kingyal’;
#授权
GRANT ALL ON companydb.* TO ‘kingyal’;
#取消授权
REVOKE ALL ON companydb.* FROM ‘kingyal’;

#视图
#即虚拟表，从一个或者多个表中查询出来的表，作用和真实表一样，包含了一系列带有行和列的数据，视图中，用户可以使用
#select语句查询语句，也可以使用insert,update，delete修改记录，试图可以使得用户操作方便，病保障数据库系统安全。
#特点
#优点：
#简单，所见即所得。
#安全性，用户只能查询或者修改他们所能见到的数据。
#逻辑独立性：可以屏蔽真实表结构变化带来的影响。
#缺点：
#性能较差，查询复杂；修改不方便，特别是复杂的聚合视图基本无法修改。

#视图不会独立存储数据，原表发生改变，视图也得发生改变，没有优化任何查询性能。
#如果视图包含以下结构中的一种，则视图不可更新：#
#1.聚合函数的结果
#2. GROUP by 分组后的结果
#3. having筛选过滤后的结果
#4. union, union all联合后的结果

#创建视图
CREATE VIEW t_emoinfo
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY FROM t_employees;
#使用视图
SELECT * FROM t_emoinfo WHERE EMPLOYEE_ID = ‘161’;
#视图的修改
CREATE OR REPLACE VIEW t_emoinfo
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY, DEPARTMENT_ID FROM t_employees;
#视图的删除
DROP VIEW t_emoinfo;