刘智豪
2020-03-08
 764
原创

Mysql基础


一、SQL概述

1.1、DML: Data Manipulation Language 数据操纵语言

DML用于查询与修改数据记录，包括如下SQL语句：

INSERT：添加数据到数据库中 UPDATE：修改数据库中的数据 DELETE：删除数据库中的数据 SELECT：选择（查询）数据 SELECT是SQL语言的基础，最为重要。

1.2、DDL: Data Definition Language 数据定义语言

DDL用于定义数据库的结构，比如创建、修改或删除数据库对象，包括如下SQL语句：

CREATE TABLE：创建数据库表 ALTER TABLE：更改表结构、添加、删除、修改列长度 DROP TABLE：删除表 CREATE INDEX：在表上建立索引 DROP INDEX：删除索引

1.3、DCL: Data Control Language 数据控制语言

DCL用来控制数据库的访问，包括如下SQL语句：

GRANT：授予访问权限 REVOKE：撤销访问权限 COMMIT：提交事务处理 ROLLBACK：事务处理回退 SAVEPOINT：设置保存点 LOCK：对数据库的特定部分进行锁定

二、sql查询

2.1、基本的select语句

SELECT 标识选择哪些列, 别名紧跟列名，也可以在列名和别名之间加入关键字‘AS’，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。

FROM 标识从哪个表中选择

select last_name as name，salary from employees

2.2、过滤和排序数据

使用WHERE 子句，将不满足条件的行过滤掉。WHERE紧跟FROM。

BETWEEN ：使用 BETWEEN 运算来显示在一个区间内的值

# 工资在2500和3000的员工姓名和工资
SELECT last_name, salary
FROM   employees
WHERE  salary BETWEEN 2500 AND 3500;

LIKE : 使用 LIKE 运算选择类似的值,选择条件可以包含字符或数字:% 代表零个或多个字符(任意个字符),_ 代表一个字符。

# 查找名字中第二个字母是a的员工姓名
SELECT last_name 
From  employees
WHERE last_name like '_a%';

NULL ：使用 IS (NOT) NULL 判断空值。

# 选择公司中没有管理者的员工姓名及job_id
SELECT last_name,job_id FROM employees 
WHERE manager_id IS NULL;

逻辑运算 : AND OR NOT

ORDER BY : ASC（ascend）: 升序DESC（descend）: 降序,ORDER BY 子句在SELECT语句的结尾。

SELECT last_name, department_id, salary
FROM   employees
WHERE salary<3000 OR salary>5000
ORDER BY department_id, salary DESC;

2.3、多表查询

连接 n个表,至少需要 n-1个连接条件。

SQL99：使用ON 子句创建连接.

自然连接中是以具有相同名字的列为连接条件的。 可以使用 ON 子句指定额外的连接条件。 这个连接条件是与其它条件分开的。 ON 子句使语句具有更高的易读性。

# 选择city在Toronto工作的员工的

SELECT last_name,job_id,d.department_id,department_name 
FROM employees e JOIN departments d 
ON e .department_id=d.department_id
JOIN locations l ON d.location_id=l.location_id
WHERE city='Toronto';

2.4、单行函数

大小写控制函数：

image-20210303082756773

字符控制函数 :

image-20210303082903170

数字函数 ：

ROUND: 四舍五入

TRUNCATE: 截断

MOD: 求余

CASE 表达式

#查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数

SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
       ELSE      1.3*salary 
       END     "REVISED_SALARY"
FROM   employees;

2.5、分组函数

AVG() ，COUNT() ，MAX() ，MIN() ，SUM()

GROUP BY : 在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。

# 查询所有部门的名字，location_id，员工数量和工资平均值

SELECT department_name,location_id,COUNT(*),AVG(salary)

FROM employees e JOIN departments d

ON e.department_id=d.department_id 

GROUP BY department_name,location_id;

HAVING : 不能在 WHERE 子句中使用组函数,可以在 HAVING 子句中使用组函数。

# 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

SELECT MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

2.6、子查询

子查询 (内查询) 在主查询之前一次执行完成，子查询的结果被主查询(外查询)使用 。

# 查询最低工资大于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);

# 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(相关子查询)

SELECT employee_id,last_name,salary
FROM employees e1
WHERE salary > (
	SELECT AVG(salary) avg_salary
	FROM employees e2
	WHERE e2.department_id=e1.department_id);

# 返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary(多行子查询 all)

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary < ALL
                    (SELECT salary
                     FROM   employees
                     WHERE  job_id = 'IT_PROG')
AND    job_id <> 'IT_PROG';

三、创建和管理表

# CREATE及其相关语句
create database employees;

# 查看当前所有数据库
show databases;

# “使用”一个数据库，使其作为当前数据库
use employees;

# 使用子查询创建表
create table emp2 as select * from employees where 1=2;

#ALTER TABLE 语句

# 追加一个新列
ALTER TABLE dept80 
ADD job_id varchar(15);

# 修改一个列 ：可以修改列的数据类型, 尺寸和默认值
Alter table dept80
MODIFY	last_name VARCHAR(30);

# 删除一个列 ：使用 DROP COLUMN 子句删除不再需要的列.
ALTER TABLE  dept80
DROP COLUMN  job_id;

# 重命名一个列 ：使用 CHANGE  old_column  new_column  dataType子句重命名列
ALTER TABLE  dept80
CHANGE department_name dept_name varchar(15);

# 删除表
DROP TABLE dept80;

#清空表 
TRUNCATE TABLE detail_dept;

# TRUNCATE语句不能回滚，DELETE 语句删除数据,可以回滚

四、数据管理

# INSERT :使用 INSERT 语句向表中插入数据。 

INSERT INTO departments(department_id, department_name, 
                        manager_id, location_id)
VALUES      (70, 'Public Relations', 100, 1700);


# UPDATE :使用 UPDATE 语句更新数据。

UPDATE employees
SET    department_id = 70
WHERE  employee_id = 113;

# DELETE :使用 DELETE 语句从表中删除数据，如果省略 WHERE 子句，则表中的全部数据将被删除。

DELETE FROM departments
WHERE  department_name = 'Finance';

#事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。数据库事务由一个或多个DML 语句组成

五、约束和分页

5.1、约束

什么是约束？

为了保证数据的一致性和完整性，SQL规范以约束的方式对表数据进行额外的条件限制。约束是表级的强制规定，

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）。

约束的分类：

  1. NOT NULL 非空约束，规定某个字段不能为空
  2. UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
  3. PRIMARY KEY 主键(非空且唯一)
  4. FOREIGN KEY 外键
  5. CHECK 检查约束
  6. DEFAULT 默认值

MySQL不支持check约束，但可以使用check约束，而没有任何效果；

  • not null 约束
    # 创建 not null 约束

CREATE TABLE emp(
id INT(10) NOT NULL,
NAME VARCHAR(20) NOT NULL DEFAULT 'abc',
sex CHAR NULL
);
# 增加 not null 约束
ALTER TABLE emp
modify sex varchar(30) not null;
  • UNIQUE约束
    # 同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，
# 就默认和列名相同。MySQL会给唯一约束的列上默认创建一个唯一索引
CREATE TABLE USER(
 id INT NOT NULL,
 NAME VARCHAR(25),
 PASSWORD VARCHAR(16),
 #使用表级约束语法
 CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);

#添加唯一约束
ALTER TABLE USER
ADD UNIQUE(NAME,PASSWORD);

ALTER TABLE USER
MODIFY NAME VARCHAR(30) UNIQUE;

ALTER TABLE USER
ADD CONSTRAINT uk_name_pwd UNIQUE(name,pwd);

#删除唯一约束
ALTER TABLE USER
DROP INDEX uk_name_pwd;
  • PRIMARY KEY

    主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值

    每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。

    MySQL的主键名总是PRIMARY，当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

    # 列级
CREATE TABLE emp4(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20)
);

 # 表级
 CREATE TABLE emp5(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

#组合
CREATE TABLE emp6(
id INT NOT NULL,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp7_pk PRIMARY KEY(NAME,pwd)
);
  • FOREIGN KEY

    外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

    从表的外键值必须在主表中能找到或者为空。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，

    需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。还有一种就是级联删除子表数据。

    注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列， 一个表可以有多个外键约束

    # 创建多列外键组合，必须使用表级约束:
# 主表
CREATE TABLE classes(
id INT,
NAME VARCHAR(20),
number INT,
PRIMARY KEY(NAME,number)
);

# 从表
CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,
classes_name VARCHAR(20),
classes_number INT,
FOREIGN KEY(classes_name,classes_number) 
REFERENCES classes(NAME,number)
);

5.2、分页

MySQL中使用limit实现分页，原理：所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。

limit语句必须放在整个查询语句的最后。

#公式：（当前页数-1）*每页条数，每页条数
SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;
Mysql

评论