mysql四种语言 mysql的语言

转载
mob64ca14163a4f 2024-01-11 12:54:58
文章标签 mysql四种语言 sql mysql java 数据 文章分类 MySQL 数据库
一. 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;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。