MySQL基础

最新推荐文章于 2024-05-06 10:39:48 发布

1040000

最新推荐文章于 2024-05-06 10:39:48 发布

阅读量120

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_44989678/article/details/126110342

版权

MySQL基础

第一部分：基本SELECT语句**

1.列的别名
(1) 重命名一个列，便于计算
(2) 紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
(3) AS 可以省略

#方式一：
SELECT last_name AS name, commission_pct comm 
FROM employees;
#方式二：
SELECT last_name "Name", salary*12 "Annual Salary"
 FROM employees;

2.去除重复行（DISTINCT）

#在SELECT语句中使用关键字DISTINCT去除重复行，DISTINCT 需要放到所有列名的前面，
SELECT DISTINCT department_id 
FROM employees;

3.显示表结构

DESC employees;

4.过滤数据（ WHERE）

SELECT 字段1,字段2
FROM 表名 
WHERE 过滤条件

5.运算符
5.1 空运算符（IS NULL或者ISNULL）判断一个值是否为NULL

SELECT last_name, manager_id 
FROM employees 
WHERE manager_id IS NULL;

5.2 非空运算符非空运算符（IS NOT NULL）判断一个值是否不为NULL

SELECT employee_id,commission_pct 
FROM employees 
WHERE commission_pct IS NOT NULL;

5.3 IN运算符 IN运算符用于判断给定的值是否是IN列表中的一个值

SELECT employee_id, last_name, salary, manager_id 
FROM employees 
WHERE manager_id IN (100, 101, 201);

5.4LIKE运算符 LIKE运算符主要用来匹配字符串，通常用于模糊匹配
（1）“%”：匹配0个或多个字符。
（2）“_”：只能匹配一个字符。

#查询名字中第二个字母是o的人
SELECT last_name 
FROM employees 
WHERE last_name LIKE '_o%';

部分习题：

# 1.选择工资不在5000到12000的员工的姓名和工资 

SELECT last_name,salary
FROM employees
#WHERE  salary<50000 OR salary>12000
WHERE salary NOT BETWEEN 5000 AND 12000;

# 2.选择在20或50号部门工作的员工姓名和部门号

SELECT last_name,employee_id
FROM employees
#WHERE department_id=20 OR department_id=50; 
WHERE department_id IN (20,50);

# 3.选择公司中没有管理者的员工姓名及job_id 

SELECT last_name,job_id,manager_id
FROM employees
#WHERE manager_id IS NULL;
WHERE manager_id <=> NULL;

# 4.选择公司中有奖金的员工姓名，工资和奖金级别

SELECT last_name,salary,commission_pct
FROM employees
#WHERE commission_pct IS NOT NULL;
WHERE NOT commission_pct <=> NULL;

# 5.选择员工姓名的第三个字母是a的员工姓名 

SELECT last_name
FROM employees
WHERE last_name LIKE "__a%";

# 6.选择姓名中有字母a和k的员工姓名 

SELECT last_name
FROM employees
WHERE last_name LIKE "a%k%" OR last_name LIKE "k%a%";

# 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息 

SELECT *
FROM employees
WHERE  first_name LIKE "%e";

# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种

SELECT last_name,first_name,department_id
FROM employees
#WHERE department_id IN (80,90,100);
WHERE department_id BETWEEN 80 AND 100;

# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id

 SELECT last_name,salary,manager_id 
 FROM employees
 WHERE manager_id IN (100,101,110);

第二部分：排序与分页

一、排序

排序规则
使用 ORDER BY 子句排序
ASC（ascend）: 升序
DESC（descend）:降序
ORDER BY 子句在SELECT语句的结尾。
单列排序

#升序
SELECT last_name, job_id, department_id, hire_date 
FROM employees 
ORDER BY hire_date   ASC  ;（ ASC可省略）
#降序
SELECT last_name, job_id, department_id, hire_date 
FROM employees 
ORDER BY hire_date DESC ;

3.多列排序

SELECT last_name, department_id, salary 
FROM employees 
ORDER BY department_id, salary DESC;

二、分页
MySQL中使用 LIMIT 实现分页
格式：LIMIT [位置偏移量,] 行数
注意：LIMIT 子句必须放在整个SELECT语句的最后！

--前10条记录：
 SELECT * FROM 表名 LIMIT 0,10; 
  或者
 SELECT * FROM 表名 LIMIT 10;
--第11至20条记录： 
 SELECT * FROM 表名  LIMIT 10,10;

第三部分：多表查询

首先来个列子

#案例：查询员工的姓名及其部门名称 
SELECT e.last_name, e.department_name 
FROM employees e , departments d
 WHERE e.department_id = d.department_id;

1.两表连接

#练习：查询出公司员工最高工资和最低工资的姓名，工资和等级
SELECT e.last_name, e.salary, j.grade_level 
FROM employees e, job_grades j 
WHERE e.salary 
BETWEEN j.lowest_sal 
AND j.highest_sal;

2.题目：查询employees表，返回“Xxx works for Xxx”

SELECT CONCAT(worker.last_name ,' works for ' , manager.last_name)
FROM employees worker, employees manager 
WHERE worker.manager_id = manager.employee_id ;

SQL99语法实现多表查询
`3.1 基本语法
使用JOIN…ON子句创建连接的语法结构：

SELECT table1.column, table2.column,table3.column 
FROM table1 
JOIN table2 ON table1 和 table2 的连接条件 
JOIN table3 ON table2 和 table3 的连接条件

3.2 内连接(INNER JOIN)的实现

SELECT 字段列表 
FROM A表  INNER JOIN B表 
ON 关联条件 
WHERE 等其他子句;

3.3 外连接(OUTER JOIN)的实现
3.3.1 左外连接(LEFT OUTER JOIN)

#实现查询结果是A 
SELECT 字段列表 
FROM A表 LEFT JOIN B表 
ON 关联条件 
WHERE 等其他子句;

3.3.2 右外连接(RIGHT OUTER JOIN)

#实现查询结果是B 
SELECT 字段列表 
FROM A表 RIGHT JOIN B表 
ON 关联条件 
WHERE 等其他子句;

4.7种SQL JOINS的实现

在这里插入图片描述

*

*第四部分：子查询**

4.1 HAVING 中的子查询
首先执行子查询。
向主查询中的HAVING 子句返回结果。

#题目：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary) 
FROM employees 
GROUP BY department_id 
HAVING MIN(salary) >  (
                       SELECT MIN(salary) 
                       FROM employees 
                        WHERE department_id = 50
                         );

4.2 CASE中的子查询
在CASE表达式中使用单列子查询：

#题目：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800 的department_id相同，则location为’Canada’，其余则为’USA’。
SELECT employee_id, last_name,  (
      CASE department_id 
      WHEN 
          (SELECT department_id 
           FROM departments 
           WHERE location_id = 1800) 
       THEN 'Canada' ELSE 'USA' END) location
       FROM employees;

4.3多行子查询
多行子查询操作符 IN ANY ALL SOME
4.3.1 IN

#查询与141号员工或174号的manager_id和department_id相同的其他员工的employee_id，manager_id和department_id
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id IN (
								 SELECT manager_id
								 FROM employees
								 WHERE employee_id IN (141,174)
								 )
AND department_id in (
								 SELECT department_id 
								 FROM employees
								  WHERE employee_id IN (141,174)
								 );

4.3.2 ANY/ALL

#ANY/ALL
#题目：返回其他job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、

#姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary  < ANY (SELECT salary
						    	FROM employees
							    WHERE job_id='IT_PROG'
									);

第五部分：创建和管理

5.1创建表

如果要创建的数据库已经存在，则创建不成功，但不会报错。
CREATE DATABASE IF NOT EXISTS mytest CHARACTER SET 'utf8';

5.2 管理数据库

#查看当前连接中的数据库都有哪些
SHOW DATABASES;

#切换数据库
USE mytest;

#查看当前数据库中保存的数据表
SHOW TABLES;

#查看当前使用的数据库
SELECT DATABASE() FROM DUAL;

#查看指定数据库下保存的数据库
SHOW TABLES FROM mysql;

#修改数据库
#更改数据库字符集
SHOW CREATE DATABASE mytest;

ALTER DATABASE mytest CHARACTER SET 'utf8';

5.3创建数据表

CREATE TABLE IF NOT EXISTS myemp1(#需要用户具备创建表的权限
id INT,
emp_name VARCHAR(15),  #使用VARCHAR来定义字符串，必须在使用VARCHAR时指明其长度。
hire_date DATE
);

#查看表结构
DESC myemp1;
#查看创建表的语句结构
SHOW CREATE TABLE myemp1;#如果创建表时没有指明使用的字符集，则默认使用表所在的数据库的字符集。

5.4复制原有的表‘’

CREATE TABLE employees_copy
AS
SELECT *
FROM employees;

第六部分：增删改

6.1增加数据

#指明要添加的字段（推荐）
INSERT INTO  emp1(id,hire_date,salary,name)
VALUES(002,'2022-7-27',25000,'zs');

INSERT INTO  emp1(id,salary,name)
VALUES(003,25000,'zs');

#同时插入多条记录（推荐）
INSERT INTO emp1(id,NAME,salary)
VALUES
(004,'Jim',5000),
(005,'死',5000);


#复制表数据
#方式二：将查询结果插入到表中
SELECT *
FROM emp1;

INSERT INTO emp1(id,NAME,salary,hire_date)

6.2更新数据（或修改数据）

/*
UPDATE...SET...WHERE
可以实现批量修改数据的。
*/
UPDATE emp1
SET hire_date=CURDATE()
WHERE id=4;

SELECT * FROM emp1;

#同时修改一条数据的多个字段
UPDATE emp1
SET hire_date=CURDATE(),salary=6000
WHERE id=4;

#表中姓名中包含字符a的提薪20%
UPDATE emp1
SET salary =salary*1.2
WHERE NAME LIKE '%c%';

6.3删除数据 DELETE…FROM…WHERE…

DELETE FROM emp1
WHERE id=2;

第七部分：约束

7.1添加非空约束

#建表前
CREATE TABLE 表名称( 
                  字段名 数据类型, 
                  字段名 数据类型 NOT NULL, 
                  字段名 数据类型 NOT NULL
                  );     
   #建表后
   alter table 表名称 modify 字段名 数据类型 not null;

7.2删除非空约束

alter table 表名称 modify 字段名 数据类型;#

7.3添加唯一约束

#建表前
create table 表名称(
                   字段名 数据类型, 
                   字段名 数据类型 unique, 
                   字段名 数据类型 unique key, 
                   字段名 数据类型 
                   );
create table 表名称(
                  字段名 数据类型, 
                  字段名 数据类型,
                  字段名 数据类型,
                  [constraint 约束名] unique key(字段名)
                  );
eg：
CREATE TABLE USER( 
           id INT NOT NULL,
           NAME VARCHAR(25),
           PASSWORD VARCHAR(16),
         -- 使用表级约束语法 
         CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD) );
         #表示用户名和密码组合不能重复


建表后
alter table 表名称 modify 字段名 字段类型 unique;

7.4添加主键约束

列级约束
create table 表名称( 
             字段名 数据类型 primary key, #列级模式 
             字段名 数据类型, 
             字段名 数据类型 
             );
表级约束
create table 表名称( 
             字段名 数据类型, 
             字段名 数据类型, 
             字段名 数据类型, 
             [constraint 约束名] primary key(字段名) #表级模式 
             );