MySql教程

MySQL笔记

本文主要是介绍MySQL笔记,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

一、数据库是什么

(一)概念

  • DB(database):存储数据的“仓库”,保存了一系列有组织的数据;
  • DBMS(Database Management System):数据库管理系统,数据库是通过DBMS创建和操作的容器;
  • SQL(Structure Query Language):结构化查询语言,专门用来与数据库通信的语言。

(二)MySQL语法规范

  • 不区分大小写,建议关键字大写;
  • 关键字不能被缩写也不能分行;
  • 字符串型和日期时间类型使用单引号(’ ');
  • 别名,尽量使用双引号(" ");
  • 每条命令最好用分号;
  • 注释:
    • 单行注释:#注释文字
    • 单行注释:-- 注释文字(注意中间有空格
    • 多行注释:/* 注释文字 */

(三)SQL分类

  • DDL(Data Definition Language):数据定义语言。定义了不同的数据段、数据库、表、列、索引等数据库对象。
    • 主要语句关键字有CREATEDROPALTER等。
  • DML(Data Manipulation Language):数据操作语言。用于添加、删除、更新和查询数据库记录,并检查数据完整性。
    • 主要语句关键字有INSERTDELETEUPDATESELECT等。
  • DCL(Data Control Language):数据控制语言。用于控制不同数据段直接的许可和访问级别的语句,这些语句定义了数据库、表、字段、用户的访问权限和安全权限。
    • 主要语句关键字有GRANTREVOKECOMMITROLLBACKSAVEPOINT等。

二、数据的查询

(一)基本的SELECT语句

基本使用

SELECT id,name,email FROM employees;
# *号代表全部字段
SELECT * FROM employees;

列的别名

# AS : Alias的缩写
# 可以使用一对双引号给字段起别名
# 当使用双引号时,调用别名时也需要加上双引号
SELECT id employee_id,name AS employee_name,email "employee_email" FROM employees;

去除重复行

# 去除表中id重复的数据
# 通常只针对一个字段进行操作
SELECT DISTINCT id FROM employees;

空值问题

# 空值表示没有赋值,null
# 空值参与运算:结果也为null
SELECT id,commission_pct,salary,salary * (1 + commission_pct) AS "salary_sum" FROM employees;

# 应该写成
SELECT id,commission_pct,salary,salary * (1 + IFNULL(commission_pct,0)) AS "salary_sum" FROM employees;

显示表结构

DESC employees;
DESCRIBE employees;

(二)过滤数据

不能在过滤条件中使用别名

where

# 查询id=100的员工
SELECT id,name,email FROM employees WHERE id = 100;

between … and …

# 查询id在 [0,100] 的员工
SELECT id,name,email FROM employees WHERE id BETWEEN 0 AND 100;

in(set)

# 查询id是10,20,30的员工
SELECT id,name,email FROM employees WHERE id IN (10,20,30);

like

# 查询姓名中包含字符'a'的员工
# '%'表示0个,1个或多个字符
SELECT id,name,email FROM employees WHERE name LIKE '%a%';

# 查询姓名中包含字符'a'和'e'的员工
SELECT id,name,email FROM employees WHERE name LIKE '%a%' AND name LIKE '%e%';

# 查询姓名中第二个字符为'a'的员工
# '_'表示一个字符
SELECT id,name,email FROM employees WHERE name LIKE '_a%';

# 查询姓名中第二个字符为'_',第三个字符为'a'的员工
# '\'表示转义字符
SELECT id,name,email FROM employees WHERE name LIKE '_\_a%';

is null

# 查询id为null的员工
SELECT id,name,email FROM employees WHERE id IS NULL;

# 查询id为不为null的员工
SELECT id,name,email FROM employees WHERE id IS NOT NULL;

(三)排序操作

升序降序

# 按照员工的工资从高到低排序
# DESC-降序,ASC-升序
SELECT id,salary FROM employees ORDER BY salary DESC;

SELECT id,salary FROM employees WHERE salary > 1000 ORDER BY salary DESC;

二级排序

# 先按照工资降序排列,再按照姓名升序排列
SELECT id,name,salary FROM employees ORDER BY salary DESC,name ASC;

(四)分页操作

# 每页显示20条记录,先显示第1页
SELECT id,name FROM employees LIMIT 0,20;
# 显示第2页
SELECT id,name FROM employees LIMIT 20,20;
# (pageNo - 1) * pageSize,pageSize

# 查询工资最高的10名员工
SELECT id,salary FROM employees ORDER BY salary DESC LIMIT 0,20;

(五)多表查询

分类:

  • 等值连接 / 非等值连接;
  • 自连接 / 非自连接;
  • 内连接 / 外连接
    • 内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另外一个表不匹配的行(只查得出匹配的数据)
    • 外连接:两个表在连接过程中除了返回满足连接条件的行以外,还返回左(或右)表中不满足条件的行,称为左(右)外连接。没有匹配的行时,返回null。
# 笛卡尔积错误
# 每一个员工都和每一个部门匹配
SELECT id,name,department_name FROM employees,departments;

# 正确写法
# 如果查询的字段在多个表中出现,则一定要指明来自于哪个表
# 可以使用表的别名
SELECT e.id,e.name,d.department_name 
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;
# 不等值连接
# 根据员工的工资划分等级
SELECT employee_id,salary,grade_level
FROM employee e,job_grade j
WHERE e.`salary` BETWEEN j.`lowest_level` AND j.`highest_level`;
# 自连接
# 查询员工的id,name和其管理者的id,name
SELECT emp.id,emp.name,mgr.id,mgr.name
FROM employees emp,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`
# 外连接
# 员工表中有一个员工没有部门,查询出来的数据少一条,这就是内连接
# 使用外连接,需要使用sql-99的相关结构

# 左外连接
SELECT e.id,e.name,d.department_name 
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右外连接
# RIGHT OUTER JOIN

# 满外连接,但是MySQL不支持
# FULL JOIN
# 左外连接去重 + 右外连接 使用UNION ALL(UNION会去重,效率低)
SELECT e.id,e.name,d.department_name 
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT e.id,e.name,d.department_name 
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

三、单行函数

函数:

  • 单行函数:
    • 操作数据对象;
    • 接收参数返回一个结果;
    • 只对一行进行变换;
    • 每行返回一个结果;
    • 可以嵌套;
    • 参数可以是一列或者一个值。
  • 多行函数:

(一)字符串函数

//连接str1,str2...为一个字符串
CONCAT(str1,str2...)

//同CONCAT函数,但是每个字符之间要加上s
CONCAT_WS(s,str1,str2...)

//字符长度
CHAR_LENGTH(s)
//字符长度,和字符集有关(utf-8中,每个汉字占三个字节)
LENGTH(s)

//将字符串str从第index个开始,len长度换成instr
//sql中索引从1开始
INSERT(str,index,len,instr)
  
//转换大小写
UPPER(s)
LOWER(s)

//返回字符串最左(右)边n个字符
LEFT(s,n)
RIGHT(s,n)
  
//左边补齐,用字符串pad填充,直到长度为len
LPAD(str,len,pad)
//右边补齐
RPAD(str,len,pad)
  
//去空格
LTRIM(s)
RTRIM(s)
TRIM(s)
  
//去字符
TRIM(s1 FROM s)//开头和结尾
TRIM(LEADING s1 FROM s)//开头
TRIM(TRAILING s1 FROM s)//结尾
  
//返回str重复n次
REPEAT(str,n)
  
//用字符串b替换字符串str中出现的字符串a
PEPLACE(str,a,b)
  
//比较大小
STRCMP(s1,s2)
  
//返回从字符串s的index位置长度为len的字符
SUBSTRING(s,index,len)

(二)数值函数

//绝对值
ABS()
  
//大于x的最小整数
CEIL(x)
//小于x的最大整数
FLOOR(x)
  
//返回x/y模,符号和被模数一致
MOD(x,y)
  
//随机值
RAND()
  
//四舍五入,返回参数x的四舍五入的y位小数的值
ROUND(x,y)
  
//返回数字x截断为y位小数的结果
TRUNCATE(x,y)
  
//平方根
SQRT(x)
  
//x的y次方
POW(x,y)

(三)日期函数

//返回当前日期
CURDATE()
  
//返回当前时间
CURTIME()
  
//返回当前日期时间
NOW()
  
//返回具体的时间值
YEAR(date)
MONTH(date)
DAY(date)
HOUR(time)
MINUTE(time)
SECOND(time)
  
//一年中的第几周
WEEKOFYEAR(date)
//第几周,周日是1
DAYOFWEEK(date)
//第几周,周日是0
WEEKDAY(date)
//返回星期名:MONDAY,TUESDAY...
DAYNAME(date)
  
//格式化
DATE_FORMAT(datetime,fmt)
//解析
STR_TO_DATE(str,fmt)
SELECT DATE_FORMAT(NOW(),'%Y/%m/%d %H:%i:%s') FROM DUAL;

SELECT STR_TO_DATE('2021/03/03 09:34:34','%Y/%m/%d %H:%i:%s') FROM DUAL;

(四)流程控制

if

SELECT id,name,salary,
IF(salary > 1000,'高工资','低工资') AS salary_details,
IF(commission_pct IS NOT NULL,commission_pct,0) AS commission_details
FROM employees;

ifnull

SELECT id,name,salary,
IF(salary > 1000,'高工资','低工资') AS salary_details,
IFNULL(commission_pct,0) AS commission_details
FROM employees;

case when … then … when … then … (else) … end

SELECT id,name,salary,CASE WHEN salary > 10000 THEN '第一等级'
													 WHEN salary > 5000 THEN '第二等级'
                           WHEN salary > 1000 THEN '第三等级'
                           ELSE '底层' END AS details
FROM employees;

case … when … then … when … then … else … end

SELECT id,name,salary,CASE name WHEN 'tom' THEN '帅哥'
																WHEN 'jerry' THEN '还行'
																END AS details
FROM employees;

(五)其他函数

//返回当前数据库名
database()
  
//返回数据库版本
version()
  
//返回当前登陆的用户名
user()
  
//返回字符串str的加密版本,41位长的字符串
password(str)
  
//返回字符串str的md5值
md5(str)

四、分组函数

avg() / sum()

# 只适用于数值类型的字段
SELECT AVG(salary),SUM(salary) FROM employees;

min() / max()

# 适用于数值类型,字符串类型,日期时间的字段
SELECT MIN(salary),MAX(salary),MIN(name),MAX(hire_date) FROM employees;

count()

# 适用于数值类型,字符串类型,日期时间的字段
# 计算个数时,null值不考虑在内,比如commission_pct字段
SELECT COUNT(salary),COUNT(name),COUNT(commission_pct) FROM employees;

# 可以使用COUNT(1),COUNT(2),COUNT(*)
# 表示遍历时取的值是1或2或*,只要非空就加1,直到遍历完整张表

avg = sum / count

avg 和 sum 在运算的时候,null值都不参与运算

group by

# 查询公司的平均工资
SELECT AVG(salary) FROM employees;

# 查询各部门的平均工资
SELECT department_id,AVG(salary) 
FROM employees
GROUP BY department_id;

#查询各部门的各工种的平均工资
SELECT department_id,job_id,AVG(salary) 
FROM employees
GROUP BY department_id,job_id;

having

# 如果过滤条件出现了组函数,则需要使用having替换where实现过滤
# 如果过滤条件中没有组函数,推荐放在where中,因为效率高
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30)
GROUP BY department_id
HAVING MAX(salary) > 10000

五、sql语句的执行顺序

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

六、子查询

(一)单行子查询

# 问题:谁的工资比Allen高
# 自连接
SELECT e1.name,e1.salary
FROM employees e1 JOIN employees e2
ON e1.`salary` > e2.`salary`
WHERE e2.name = 'Allen';

# 子查询
SELECT name,salary
FROM employees
WHERE salary >(
  						SELECT salary
  						FROM employees
  						WHERE name = 'Allen'
							);
# 练习:返回job_id与140号员工相同,salary比141号员工多的员工的name,job_id,salary
SELECT name,job_id,salary
FROM employees
WHERE job_id = (
  						SELECT job_id
  						FROM employees
  						WHERE id = 140
							);
AND salary > (
  						SELECT salary
  						FROM employees
  						WHERE id = 141
							);

(二)多行子查询

in:等于列表中的任意一个

SELECT id,name
FROM employees
WHERE salary in (
  						SELECT MIN (salary)
  						FROM employees
  						GROUP BY department_id
							);

any:和子查询返回的某一个值比较

# 返回其他job_id中比job_id等于'IT_PROG'部门任一工资低的员工id,name,job_id以及salary
SELECT id,name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
  						SELECT salary
  						FROM employees
  						WHERE job_id = 'IT_PROG'
							);

all:和子查询返回的所有值比较

# 返回其他job_id中比job_id等于'IT_PROG'部门的所有工资都低的员工id,name,job_id以及salary
SELECT id,name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (
  						SELECT salary
  						FROM employees
  						WHERE job_id = 'IT_PROG'
							);

(三)相关子查询

# 不相关子查询
# 查询员工中工资大于公司平均公司的员工的name,salary和department_id
SELECT name,salary,department_id
FROM employees
WHERE salary > (
  						SELECT AVG(salary)
  						FROM employees
							);
							
# 相关子查询
# 查询员工中工资大于本部门平均公司的员工的name,salary和department_id
SELECT name,salary,department_id
FROM employees e1
WHERE salary > (
  						SELECT AVG(salary)
  						FROM employees e2
              WHERE e2.department_id = e1.department_id
							);

# 查询员工的id,name,要求按照department_name从小到大排序
SELECT id,name
FROM employees e
ORDER BY (
  				SELECT department_name
  				FROM departments d
  				WHERE e.department_id = d.department_id
					) DESC;

(四)EXISTS

# 查询公司管理者的id,name,job_id,department_id
# 方式一:
SELECT id,name,job_id,department_id
FROM employees
WHERE id IN (
  						SELECT DISTINCT manager_id
  						FROM employees
						);
						
# 方式二:
SELECT id,name,job_id,department_id
FROM employees e1
WHERE EXISTS (
  						# 找到就返回true
  						SELECT '*'
  						FROM employees e2
  						WHERE e1.id = e2.manager_id
						);

七、创建和管理表

数据库对象:表、视图、函数、触发器、存储过程等

# 创建库
CREATE DATABASE mydatabase;
# 使用库
USE mydatabase;
# 删除库
DROP DATABASE mydatabase;

(一)创建表

# 方式一:
CREATE TABLE mytable(
  id INT,
  name VARCHAR(15),
  email VARCHAR(20),
  salary DOUBLE(10,2),
  birthday DATE
);

# 方式二:基于现有的表创建新表
CREATE TABLE mytable2
AS 
SELECT id,name,salary
FROM employees;

(二)修改表

# 增加一个字段
ALTER TABLE mytable
ADD age INT;

# 删除一个字段
ALTER TABLE mytable
DROP age;

# 修改字段
ALTER TABLE mytable
MODIFY name VARCHAR(10);

# 重命名字段
ALTER TABLE mytable
CHANGE birthday birth DATE;

(三)重命名表

RENAME TABLE mytable2 TO mytable3;

(四)删除表

DROP TABLE mytable3;

(五)清空表

# 清空表中的数据,但是表结构保留
# 会自动commit,一旦操作,就不可以回滚数据
TRUNCATE TABLE mytable;

八、数据的增删改

# 根据已有的表
CREATE TABLE mytable(
  id INT,
  name VARCHAR(15),
  email VARCHAR(20),
  birthday DATE
);

(一)添加数据

# 添加数据
INSERT INTO mytable(id,name,email,birthday)
VALUES (1,'tom','tom@qq.com','2000-02-03')

# 基于现有的表
INSERT INTO mytable(id,name)
SELECT id,name
FROM employees
WHERE department_id IN (10,20,30);

(二)删除数据

# 支持回滚
DELETE FROM mytable
WHERE id = 1;

(三)修改数据

UPDATE mytable
SET name = 'jerry',email = 'jerry@126.com'
WHERE id = 1;

九、约束constraint

(一)什么是约束

  • 为了保证数据的一致性和完整性,SQL规范以约束的方式对表数据进行额外的条件限制
    • 实体完整性:同一个表中,不能存在两条完全相同的数据;
    • 域完整性:年龄范围0~120,性别男/女;
    • 引用完整性:员工所在部门,在部门表中要能找到这个部门;
    • 用户自定义完整性:用户名唯一,密码不能为空。

(二)非空约束:not null

# 创建表的时候添加约束
CREATE TABLE mytable(
  id INT NOT NULL,
  name VARCHAR(15) NOT NULL,
  email VARCHAR(20),
  birthday DATE
);

# 修改表的时候删除约束
ALTER TABLE mytable
MODIFY name VARCHAR(15) NULL;

(三)唯一性约束:unique

  • 在创建唯一约束时,如果不给唯一约束起名,则默认与列名相同;
  • MySQL会给唯一约束的字段默认创建一个唯一索引;
  • 删除唯一约束只能通过删除唯一索引的方式。
# 创建表的时候添加约束
CREATE TABLE mytable(
  # 列级约束
  id INT UNIQUE,
  name VARCHAR(15),
  email VARCHAR(20),
  birthday DATE,
  # 表级约束
  CONSTRAINT mytable_email_uni UNIQUE (email)
);

# 修改表的时候删除约束
# 删除索引
ALTER TABLE mytable
DROP INDEX mytable_email_uni;

ALTER TABLE mytable
DROP INDEX id;

(四)主键约束:primary key

主键约束,既满足唯一性,也满足非空性。一个表中只能声明一个主键约束

# 通常在创建表的时候添加约束
# 列级约束
CREATE TABLE mytable(
  id INT PRIMARY KEY AUTO_INCREMENT,# 自增长
  name VARCHAR(15),
  email VARCHAR(20),
  birthday DATE
);

# 表级约束
CREATE TABLE mytable(
  id INT AUTO_INCREMENT,
  name VARCHAR(15),
  email VARCHAR(20),
  birthday DATE,
  CONSTRAINT mytable_id_pri PRIMARY KEY (id)
);

# 删除主键
# 如果设置了自动增长,则会删除失败
# 删除主键之后,字段还会保留not null的约束
ALTER TABLE mytable
DROP PRIMARY KEY;

# 添加主键
ALTER TABLE mytable
ADD CONSTRAINT mytable_id_pri PRIMARY KEY (id);

(五)外键约束:foreign key

外键约束:在表1的字段a声明有一个外键约束,与表2的字段b相关联,则字段a在操作数据时,其数据一定是字段b中出现的数据

要求:要想关联成功,必须要求字段b声明有主键约束或者唯一性约束。

CREATE TABLE dept(
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(10)
);

CREATE TABLE emp(
  emp_id INT,
  emp_name VARCHAR(15),
  dept_id INT,
  
  # 声明外键
  CONSTRAINT emp_dept_id_for FOREIGN KEY (dept_id) REFERENCES dept (dept_id)
);

(六)检查约束:check

# 对MySQL失效
CREATE TABLE mytable(
  id INT,
  name VARCHAR(15),
  salary DOUBLE(10,2) CHECK (salary > 3000) # 添加的工资要大于3000
);

(七)默认值约束:default

CREATE TABLE mytable(
  id INT,
  name VARCHAR(15),
  salary DOUBLE(10,2) DEFAULT 2500 # 提供默认值
);
这篇关于MySQL笔记的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!