MySQL简介和安装

1. 了解SQL

1.1 数据库基础

​ 学习到目前这个阶段，我们就需要以某种方式与数据库打交道。在深入学习MySQL及其SQL语言之前，应该先对数据库及数据库技术的某些基本概念有所了解。
​ 你可能还没意识到，其实你一直在使用数据库。

  • 你使用QQ和微信查找好友的时候，你就在使用数据库。
  • 你在百度上进行搜索，也是在使用数据库。
  • 你每天来学习进行打卡的指纹验证，也是在使用数据库。
  • 自动取款机上使用ATM卡，还是在使用数据库。
  • 。。。。。太多太多。。。。
    其实我们做的所有产品（包括客户端和服务器端）最终都是建立在数据上。我们每一款软件最核心、最宝贵的就是数据。

1.1.1 数据库

数据库（database）是按照数据结构来组织、存储和管理数据的仓库（通常是一个文件或一组文件）。
​ 理解数据库的一种最简单的办法就是将其想象为一个文件柜，此文件柜是一个存放数据的物理位置。

  • 注意：有些人通常使用数据库这个术语来代表他们使用的数据库软件，这是不正确的。确切的说，数据库软件应称为DBMS（数据库管理系统）。数据库是通过DBMS创建和操作的容器。数据库可以是保存在硬件设备上的文件，但也可以不是。对我们来说数据库究竟是文件还是其他什么东西并不重要，因为我们并不直接访问数据库，我们使用的是DBMS，它替我们访问数据库。

1.1.2 表

表（table）是数据库中存储数据的基本单位。

image

1.1.3 列

列（column）表中的一个字段。所有的表都是由一个或多个列组成。

1.1.4 数据类型

数据类型（datatype）每个列都有相应的数据类型，用来限制该列存储的数据。

1.1.5 行

行（row）表中的一个（行）记录。
​ 表中的数据是按行存储的，所保存的每个记录存储在自己的行内，如果将表想象为网格，网格中垂直的列为表列，水平行为表行。

1.1.6 主键

主键（primary key）一列（或一组列），其值能够唯一区分表中的每一行。
​ 表中每一行都应该有可以唯一标识自己的一列（或一组列）。一个顾客表中可以使用顾客编码列，而订单表可以使用订单ID等。一个表中没有主键的话，更新或删除表中特定行很困难，因为没有安全的方法保证只涉及相关的行。
​主键的规则表中的任何列都可以作为主键，只要它满足一下条件：

  • 任何两行都不具有相同的主键值（每一行的主键值是唯一的）。
  • 每个行都必须具有一个主键值（主键不允许null值）
    主键的好习惯除了强制的规则外，应该坚持的几个普遍认可的好习惯：
  • 不更新主键列中的值
  • 不重用主键列的值
  • 不在主键列中使用可能会更改的值

MySQL数据库操作

MySql中添加用户,新建数据库,用户授权,删除用户,修改密码(注意每行后边都跟个;表示一个命令语句结束):

1.新建用户

1.1 登录MYSQL：

@>mysql -u root -p

@>密码

1.2 创建用户：

mysql> CREATE USER 'username'@'host' [IDENTIFIED BY 'PASSWORD'] 其中密码是可选项；

例子：CREATE USER 'john'@'192.168.189.71' IDENTIFIED BY "123";

CREATE USER 'john'@'192.168.189.%' IDENTIFIED BY "123";

CREATE USER 'john'@' %' ;

1.3 然后登录一下：

mysql>exit;

@>mysql -u test -p

@>输入密码

mysql>登录成功

2.为用户授权

授权格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";

2.1 登录MYSQL（有ROOT权限），这里以ROOT身份登录：

@>mysql -u root -p

@>密码

2.2 首先为用户创建一个数据库(testDB)：

mysql>CREATE DATABASEtestDBDEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

2.3 授权test用户拥有testDB数据库的所有权限（某个数据库的所有权限）：

mysql>grant all privileges on testDB.* to test@localhost identified by '1234';

mysql>flush privileges;//刷新系统权限表

格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";

2.4 如果想指定部分权限给一用户，可以这样来写:

mysql>grant select,update on testDB.* to test@localhost identified by '1234';

mysql>flush privileges; //刷新系统权限表

2.5 授权test用户拥有所有数据库的某些权限：

mysql>grant select,delete,update,create,drop on_._to test@"%" identified by"1234";

 //test用户对所有数据库都有select,delete,update,create,drop 权限。

//@"%" 表示对所有非本地主机授权，不包括localhost。（localhost地址设为127.0.0.1，如果设为真实的本地地址，不知道是否可以，没有验证。）

//对localhost授权：加上一句grant all privileges on testDB.* to test@localhostidentified by '1234';即可。

3. 删除用户

@>mysql -u root -p

@>密码

mysql>use mysql; // 进入这个数据库

mysql>Delete FROM user Where User='test' and Host='localhost';

mysql>flush privileges;

mysql>drop database testDB; //删除用户的数据库

删除账户及权限：>drop user 用户名@'%';

>drop user 用户名@ localhost;

4. 修改指定用户密码

@>mysql -u root -p

@>密码

mysql>update mysql.user set password=password('新密码') where User="test"and Host="localhost";

mysql>flush privileges;

5. 列出所有数据库

mysql>show database;

6. 切换数据库

mysql>use '数据库名';

7. 列出所有表

mysql>show tables;

8. 显示数据表结构，显示表定义

mysql>desc 表名;

mysql>show create table 表名;

9. 删除数据库和数据表

mysql>drop database 数据库名;

mysql>drop table 数据表名;

MySQL 数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述。

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

表操作

创建表

MySQL不仅用于表数据操纵，而且还可以用来执行数据库和表的所有操作，包括表本身的创建和处理。
通用语法：CREATE TABLE table_name (column_name column_type);
create table test_table(test_id INT NOT NULL AUTO_INCREMENT,test_title VARCHAR(100) NOT NULL,test_author VARCHAR(40) NOT NULL DEFAULT 'larry',test_date DATE,PRIMARY KEY ( test_id ));
在这里，一些数据项需要解释：

  • 字段使用NOT NULL属性，是因为我们不希望这个字段的值为NULL。 因此，如果用户将尝试创建具有NULL值的记录，那么MySQL会产生错误。
  • 字段的AUTO_INCREMENT属性告诉MySQL自动增加id字段下一个可用编号。
  • DEFAULT 设置默认值。
  • 关键字PRIMARY KEY用于定义此列作为主键。可以使用逗号分隔多个列来定义主键。

删除表

语法：DROP TABLE IF EXISTS table_name;
删除表（删除整个表而不只是其内容）非常简单，删除表没有确认，也不能撤销，执行删除语句后将永久删除该表。

更新表

为更新表定义，可使用ALTER TABLE 语句，但是理想状态下，当表中存储数据以后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。
为了使用ALTER TABLE更改表结构，必须给出下面的信息：

  • 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）* 所做更改的列表
    给s_dept表添加一个名字为dept_name的列，必须明确其数据类型。
    alter table s_dept add dept_name varchar(20);
    删除刚刚添加的列，可以这样做：
    ALTER TABLE s_dept DROP COLUMN dept_name;
    修改某个列的名字：
    ALTER TABLE s_dept CHANGE dept_name dept_name2 varchar(30);

重命名表

RENAME TABLE s_dept TO s_dept2;

SQL

全拼：STRUCTURED QUERY LANGUAGE

是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。是一种用于数据库操作的语言。

包含6个部分

数据查询语言（DQL）

select ，where，order by ,group by，having

数据操作语言（DML）

insert，update，delete

事务处理语言（TPL）

BEGIN TRANSACTION，COMMIT和ROLLBACK。

数据控制语言（DCL）

GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问

数据定义语言（DDL）

CREATE和DROP，ALTER

指针控制语言 ****(****CCL****)

DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT

检索语句（SELECT语句

通用语法：SELECT column_name FROM table_name

为了使用SELECT检索表数据，必须至少给出两条信息- 想要选择什么，以及从什么地方去选择。

检索单个列

Q：查询所有员工的名字

select first_name from s_emp;

检索多个列

Q：查询所有员工的id和名字

select id, first_name from s_emp;

检索所有列

Q：查询所有员工的每个字段

select * from s_emp;

检索不同的行（DISTINCT）

使用DISTINCT关键字，注意必须放在列名的最前面，不能部分使用DISTINCT，DISTINCT关键字应用于所有列而不仅是前置它的列。如果给出SELECT DISTINCT column1, column2，除非指定的两个列都不同。否则所有行都将被检索出来。

Q：查询所有员工的所在部门

select dept_id from s_emp;

Q：查询所有员工的所在部门（去掉重复）

select distinct dept_id from s_emp;

限制结果（LIMIT)

LIMIT 5 和 LIMIT 5, 5 ，MySQL5.0以后 是 LIMIT 4 OFFSET 3 从行3开始取4行， 注意：两个参数的时候，索引是从0开始

Q：查询前5个员工的名字

select first_name from s_emp limit 5;

Q：查询从第3行开始后的4个员工名字

select first_name from s_emp limit 4 offset 3;

或者

select first_name from s_emp limit 3, 4;

使用完全限定的表名字（跨库访问）

Q：查询其他数据库的某个表数据 注意：events是information_schema这个库中的表

select ends from information_schema.events;

排序检索数据（ORDER BY子句)

单列排序、多列排序、降序、找出最大值或最小值（和limit组合使用）

Q: 按照工资从少到多查询员工的名字和工资

select first_name, salary from s_emp order by salary asc; 默认就是升序，asc可以省略

Q：按照工资从多到少查询员工的名字和工资

select first_name, salary from s_emp order by salary desc;

Q：按照工资从多到少，如果有相同工资的则按照部门大小查询员工的名字、工资和部门

select first_name, salary, dept_id from s_emp order by salary desc, dept_id desc;

Q：按照工资从多到少查询员工的名字和工资，显示6-10条

select first_name, salary, dept_id from s_emp order by salary desc limit 5, 5;

过滤数据（WHERE子句）

在SELECT语句中，数据根据WHER子句中指定的搜索条件进行过滤WHERE子句在表名（FROM子句）之后给出。

WHERE子句支持的操作符

  • = 等于
  • <> 不等于
  • != 不等于
  • < 小于
  • > 大于
  • >= 大于等于
  • <= 小于等于
  • BETWEEN 。。。AND 在指定的两个值之间

检查单个值(不一定是单个值，这里的单个值是指单个值过滤)

Q：查询名字叫'Garmen'的员工

select first_name, salary from s_emp where first_name = 'Garmen';

不匹配检查

Q：查询不是部门3的员工信息

select first_name, salary, dept_id from s_emp where dept_id <> 3 order by dept_id;

范围值检查

Q：查询工资在1000-1500之间的员工信息

select first_name, salary from s_emp where salary >= 1000 and salary

或者

select first_name, salary from s_emp where salary between 1000 and 1500 order by salary desc;

空值检查（IS NULL）

Q：查询xxx为NULL的员工

select first_name, last_name from s_emp where last_name is null;

组合WHERE子句

AND操作符

Q：查询部门1并且工资大于1300的员工信息

select first_name, dept_id, salary from s_emp where dept_id = 1 and salary >1300 order by salary desc;

OR操作符

Q：查询部门2和部门3的员工信息

select first_name, dept_id from s_emp where dept_id = 2 or dept_id = 3;

AND 和 OR组合操作的问题

Q：列出3或4部门下工资大于1000以上的员工

select first_name, dept_id, salary from s_emp where (dept_id = 3 or dept_id =4) and salary >= 1000;

或者

select first_name, dept_id, salary from s_emp where dept_id in(3, 4) and salary>= 1000 order by dept_id;

IN操作符

Q：查询部门2和部门3和部门5的员工信息

select first_name, dept_id from s_emp where dept_id in (2, 3, 5);

NOT操作符

Q：查询不在部门2和部门3的员工信息

select first_name, dept_id from s_emp where dept_id not in (2, 3);

通配符过滤（like操作符，%通配符，_通配符)

LIKE是谓词，%是通配符，表示任何字符出现任意次数。_是通配符，表示只能匹配一个。是否区分大小写和MySQL配置有关系

Q：查询名字以C开头的员工信息

select first_name, dept_id from s_emp where first_name like 'C%';

Q：查询部门1名字中包含e的员工信息

select first_name, dept_id from s_emp where dept_id = 1 and first_name like'%e%';

Q：查询包含这种格式的员工信息

计算字段

存储在数据库表中的数据一般不是应用程序最后所需要的格式。

例如，在一个字段中既要显示公司名字，又要显示公司的地址。

这种情况下就要用到计算字段了。解决办法是把两个列（字段）拼接起来。可以使用Concat()函数来拼接两个列，这里要注意：其他DBMS使用 + 或者 || 来进行拼接，MySQL中就是使用Concat()函数。

Q：查询部门5员工的信息，要求只显示一个字段，字段格式为 员工姓名（工资）

select Concat(first_name, '(工资：', salary, ')'), dept_id from s_emp where dept_id = 5;

别名

使用AS关键字来设置别名

Q：同上面的问题一样，只是显示的时候名字叫做员工名字和工资

select Concat(first_name, '(工资：', salary, ')') as 员工和工资, dept_id as 部门IDfrom s_emp where dept_id = 5;

执行算术计算

MySQL算术操作符：

  • 加 +
  • ➖ 减
  • 乘 *
  • 除 /

Q：显示所有员工的名字和年薪

select first_name as 姓名, (salary * 12) as 年薪 from s_emp;

数据处理函数

MySQL提供了很多内置的数据处理函数

例如Lower()、Upper()、Trim()等

select Upper(first_name) as 姓名, (salary * 12) as 年薪 from s_emp;

聚集函数

AVG() 表示某列的平均值

Q：查询员工的平均薪资

select avg(salary) as 平均工资 from s_emp;

COUNT() 表示某列的行数

Q：查询有多少个员工

select count(*) from s_emp;

MAX() 查询某列的最大值

Q：查询工资最高的员工

select max(salary) from s_emp;

MIN() 查询某列的最小值

Q：查询工资最低的员工

select min(salary) from s_emp;

SUM() 查询某列的和

Q：查询员工的工资总数

select sum(salary) from s_emp;

DISTINCT 和 AVG() 组合使用

Q：查询员工的平均薪资（去掉重复）

select avg(distinct salary) from s_emp;

分组

分组是在SELECT语句的GROUP BY子句中建立的。一般配合聚合函数来使用。一般看到每个部门xxx ，每个地区xxx，每个班级xxx等需求的时候，我们要首先想到使用分组

Q：总共有多少员工

select count(*) from s_emp;

select Concat(count(*), '人') as 员工总数 from s_emp;

Q：每个部门分别有多少员工

select dept_id, Concat(count(*), '人') as 员工总数 from s_emp group by dept_id;

Q：每个部门的平均薪资

select dept_id, avg(salary) as 平均薪资 from s_emp group by dept_id;

如果要过滤分组的话使用 HAVING子句，要记住在过滤数据普通行记录的话使用WHERE，在过滤分组的时候使用HAVING

Q：部门3、4总共有多少员工

select count(*) from s_emp where dept_id in (3, 4);

Q：部门3、4分别有多少员工

select dept_id as 部门, count(*) as 员工数量 from s_emp group by dept_id having dept_id in (3, 4);

Q：员工数大于5人的部门有哪些

select dept_id as 部门, count(id) as 员工数量 from s_emp group by dept_idhaving count(id) > 5;

总结：select子句极其顺序

1、select 要返回的列或表达式 必须使用

2、from 从中检索数据的表 仅在选择数据的时候使用

3、where 行级过滤 不必须使用

4、group by 分组说明 仅在按组计算聚集的时候使用

5、having 组级过滤 不必须使用

6、order by 排序 不必须使用

7、limit 检索行数 不必须使用

多表查询

我们平时检索出来的数据，大多数情况下都是多表联合查询出来的。先来看下面的反例：

select *from s_emp, s_dept;

-- 125 rows in set (0.00 sec) 生成的结果是125条数据，我们知道员工表中有25条记录，部门表中有5条记录。之所以产生125条记录，是因为我们使用了多表查询，没有给任何关联条件，会计算两个表的行数乘积。形成 **笛卡尔积， **很明显这125条数据并不是我们想要的结果。

在来看一个问题：

select first_name, id from s_emp, s_dept;

-- 以上代码运行会报错，先不考虑笛卡尔积问题，因为id在员工表和部门表里都存在，所以这里会有问题。

我可以给表起一个别名，可以像下面这样使用

select e.first_name, e.id, d.id from s_emp e, s_dept d;

内连接 (inner join)

内连接会匹配两个表或者多个表内相同的值的结果集。

内连接原理：

t1表和t2表做内连接，连接条件为 on t1.c1 = t2.c2，分两种情况得到结果集

1、t1做驱动表，t2做匹配表

2、t2做驱动表，t1做匹配表

无论哪种方式，所得到的结果集都是相同的，只是效率不同而已。

内连接的核心就是任何一张表的记录一定要在另外一张表中找到匹配的记录，否则不能出现在结果集中

内连接匹配流程：on t1.c1 = t2.c2

假设t1表做驱动表，t2表做匹配表，记录的匹配过程：

1、从t1表中读取一条记录r1，若他的值是c1

2、根据该值到t2表中查找匹配的记录，既需要遍历t2表，从t2表中的第一条记录开始寻找，若该记录c2列的值等于c1，我们就说这两条记录可以匹配上，那么t1中的结果和t2中的结果组合起来，作为最后结果集的一条记录。否则检测t2表中的下一条记录。

3、按照步骤2一次将t2表中的所有记录匹配完毕，只要可以匹配的就放到最后的结果集中。

4、从t1中读取第二条记录，一次重复步骤2和步骤3，产生最后的结果集。

select e.first_name, e.id, d.id

from s_emp e

inner join s_dept d

on e.dept_id = d.id

nner join 。。on 。。 语法：内连接员工表和部门表。员工表的部门id 等于 部门表的id

Q：'Garmen'所在的部门名称是什么

select e.first_name, d.name from s_emp e inner join s_dept d on e.dept_id = d.id where e.first_name = 'Garmen';

Q：每个部门所在地区的名称是什么

select d.name, r.name from s_dept d inner join s_region r on d.region_id = r.id;

Q：亚洲地区都有哪些部门

select d.name, r.name from s_dept d inner join s_region r on d.region_id = r.id where r.name = 'Asia';

Q：亚洲地区有哪些员工

select e.first_name, d.name, r.name from s_emp e inner join s_dept d on e.dept_id = d.id inner join s_region r on d.region_id = r.id;

Q：'Garmen'在哪个地区上班

select e.first_name, d.name, r.name from s_emp e inner join s_dept d on e.dept_id = d.id and e.first_name = 'Garmen' inner joins_region r on d.region_id = r.id;

内连接的三种形式

1、等值连接（上面我们描述的就是等值连接）

两张表具有描述共同属性的列，常见的写法是父表的主键和子表的外键相等。

2、非等值连接

两张表不具有描述共同属性的列，可以使用between and 这样的非等值运算符将两张表中的列写成一个表达式。所谓表之间的关系，实际是指表中的行（记录）之间的关系。该关系将通过表中的列写成表达式来体现。

Q：员工对应的工资等级。

select e.first_name, e.salary, s.grade from s_emp e inner join salgrade s on e.salary between s.losal and s.hisal;

Q：'Garmen'的工资级别是多少。

select e.first_name, e.salary, s.grade from s_emp e inner join salgrade s on e.salary between s.losal and s.hisal and e.first_name ='Garmen';

Q：在部门1中，工资等级为3、5的员工有哪些。

select e.first_name, e.salary, s.grade from s_emp e inner join salgrade s on e.salary between s.losal and s.hisal and e.dept_id = 1and s.grade in (3, 5);

3、自连接

Q：查询每个员工和这个员工对应的领导

select e1.first_name as 员工, e2.first_name as 领导 from s_emp e1 inner join s_emp e2 on e1.manager_id = e2.id;

Q：哪些人是领导

select first_name, ifnull((select first_name from s_emp i where o.manager_id = i.id), 'Boss') as 领导 from s_emp o;

外链接（outer join)

如果结果集没有匹配记录的时候，就要使用外链接。 外链接的核心是将匹配不到的记录找回来，即一个都不能少，外链接的结果集 = 内连接的结果集 + 驱动表中匹配不上的记录和一条null记录组合。

Q：每个员工对应的领导

左连接写法

select e1.first_name as 员工, e2.first_name as 领导

from s_emp e1 left outer join s_emp e2

on e1.manager_id = e2.id;

右连接写法

select e1.first_name as 员工, e2.first_name as 领导

from s_emp e2 right outer join s_emp e1

on e1.manager_id = e2.id;

Q：每个员工对应的领导（'Garmen'如果没有领导，则换成BOSS）

扩展：ifnull(字段, '替换内容') 这个函数的意思是如果现实的某个字段值为null，则替换成我们提供的值

select e1.first_name as 员工,ifnull(e2.first_name, 'Boss') as 领导 from s_emp e2 right outer join s_emp e1 on e1.manager_id = e2.id;

Q：哪个部门没有员工

select e.first_name, d.name from s_emp e right outer join s_dept d on e.dept_id = d.id where e.first_name is null;

Q：哪些人是员工（相当于问哪些人不是领导）

select e2.first_name as 普通员工 from s_emp e2 left outer join s_emp e1 on e1.manager_id = e2.id where e1.first_name is null;

子查询

子查询逻辑：

先执行子查询，子查询的返回结果作为主查询的查询条件，在执行主查询。

子查询只执行一遍。

若子查询的返回结果有多个值，则会去重在讲结果返回给主查询

Q：找出工资最少的那个人

select e.first_name, e.salary from s_emp e where e.salary = (select min(salary) from s_emp);

Q：谁和'Mark'的工资是一样的 （如果不能保证结果集唯一，应该使用any）

select e.first_name, e.salary from s_emp e where e.salary = (select salary from s_emp where first_name = 'Mark') and e.first_name<> 'Mark';

或者 （注意：子查询如果能保证结果是唯一（根据主键查询）的，可以不用any，要不就会有多个结果的风险）

select e.first_name, e.salary from s_emp e where e.salary = any(select salary from s_emp where first_name = 'Mark') and e.first_name <> 'Mark';

Q：哪些部门的平均工资比部门3的平均工资高

select e.dept_id, avg(e.salary) from s_emp e group by e.dept_id having avg(e.salary) > (select avg(salary) from s_emp where dept_id = 3);

Q：哪些人是领导

select first_name from s_emp where id in (select manager_id from s_emp);

插入数据
INSERT是用来插入（添加）行到数据库的。插入可以用几种方式使用：

注意：可以针对每个表或者每个用户，利用MySQL的安全机制禁止使用INSERT语句。

插入完整的行
insert into s_dept values ( NULL, '部门1', 2);
INSERT语句一般不会产生输出。
以上语句会对s_dept表，存储到每一个表列中的数据在values子句中给出。对每个列必须提供一个值。如果某个列没有值，应该使用NULL值。各个列必须以它们在表定义中出现的次序填充。第一列为NULL。是因为每次插入一个新行时，该列由MySQL自动增量。
虽然上面的语法很简单，但并不安全，应该尽量避免使用。上面的语句高度依赖于表中列的定义次序。
insert into s_dept ( name, region_id) values ('部门1', 3);
以上会发现，我们可以设定只给某些字段设置值，然后没有设置的字段我们不用设置为NULL，没有出现的字段，默认就会变成NULL。
insert into s_dept (region_id, name) values (3, '部门2');
这个例子给的列名字和建立表的时候顺序不一样，这样也是可以的。

更新数据
更新（修改）表中的数据，可以使用UPDATE语句，可以采用两种方式使用UPDATE：

注意：同INSERT语句一样，利用MySQL的安全机制可以限制使用UPDATE语句
UPDATE语句非常容易使用，基本的UPDATE语句由3部分组成，分别是：
要更新的表、 列名和它们的新值*、确定要更新行的过滤条件
例如：给某个用户表，用户ID为1001的用户修改新密码为123456
update user表 set user_pwd = '123456' where user_id = 1001;

注意：上面的语句中的where不要省略，如果省略就会更新这个表的所有行的密码。
更新多个列，在set子句后面使用逗号给开

update user表 set user_pwd = '新密码', user_email = 'xx@qq.com' where user_id = 1001;

删除数据
从某个表中删除数据，使用DELETE语句。有两种方式使用DELETE：
从表中删除特定的行
注意：同以上一样，利用MySQL的安全机制可以限制使用DELETE语句
DELETE语句非常容易，删除某一行
delete from user表 where user_id = 1001;

删除某个表中的所有行

delete from user表;
注意：在使用delete语句的时候，删除的是表中的数据，而不是表
如果想删除所有行，还可以有另外一种方式删除
TRUNCATE TABLE table_name;
这个可以删除所有行，其实内部实现是删除了某个表，然后在创建该表。从性能上讲它更快，但要注意，它会把和表相关联的自增序列一并删除。

事务处理

事务处理（transaction processing）可以用来维护数据的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。
在使用事务处理时，有几个关键术语：

  • 事务（transaction）指一组SQL语句* 回退（rollback）指撤销指定SQL语句的过程* 提交（commit）指将未存储的SQL语句结果写入数据库表
    MySQL使用下面的语句来标识事物的开始：
    START TRANSACTION

使用ROLLBACK

MySQL使用ROLLBACK命令来回退（撤销）MySQL语句。
先查询某个表，看下之前的数据
select * from xx表;
开启事务管理
start transaction;
删除所有数据
delete from xx表;
查询看是否删除（结果已经没有数据）
select * from xx表;
回滚
rollback;
再次查询数据（结果显示数据回到了最初删除之前的样子）
select * from xx表;
显然，ROLLBACK只能在一个事务处理内使用（在执行一一条START TRANSACTION命令之后）
事务处理用来管理INSERT、UPDATE和DELETE语句，不能回退SELECT语句（这样做也没有意义），不能回退CREATE和DROP。

使用COMMIT

一般的MySQL语句都是直接针对数据库执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作都是自动的。
但是在事务处理中，提交不会隐含的进行。为进行明确的提交，需要使用COMMIT语句。
START TRANSACTION;
DELETE FROM xx表 where user_id = 1001;
DELETE FROM xx表 where manager_id = 1001;
COMMIT;
以上语句保证不会因为某种原因，只执行了第一条，没有执行第二条删除。会保持删除的完整性，如果其中一个有问题，则不会提交，会被自动撤销。

隐含事务关闭：当COMMIT或ROLLBACK语句执行后，事务会自动关闭

约束

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。
MYSQL中，常用的几种约束：
| 约束类型： | 主键 | 默认值 | 唯一 | 外键 | 非空

关键字： | PRIMARY KEY | DEFAULT | UNIQUE | FOREIGN KEY | NOT NULL |

主键

主键(PRIMARY KEY)是用于约束表中的一行，作为这一行的标识符，在一张表中通过主键就能准确定位到一行，因此主键十分重要。主键要求这一行的数据不能有重复且不能为空。
还有一种特殊的主键——复合主键。主键不仅可以是表中的一列，也可以由表中的两列或多列来共同标识

默认值

默认值约束(DEFAULT)规定，当有DEFAULT约束的列，插入数据为空时该怎么办。
DEFAULT约束只会在使用INSERT语句时体现出来，INSERT语句中，如果被DEFAULT约束的位置没有值，那么这个位置将会被DEFAULT的值填充

唯一性

唯一约束(UNIQUE)比较简单，它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的。
当INSERT语句新插入的数据和已有数据重复的时候，如果有UNIQUE约束，则INSERT失败.

外键

外键(FOREIGN KEY)既能确保数据完整性，也能表现表之间的关系。
一个表可以有多个外键，每个外键必须REFERENCES(参考)另一个表的主键，被外键约束的列，取值必须在它参考的列中有对应值。
如果子表试图创建一个在父表中不存在的外键值，InnoDB会拒绝任何INSERT或UPDATE操作。如果父表试图UPDATE或者DELETE任何子表中存在或匹配的外键值，最终动作取决于外键约束定义中的ON UPDATE和ON DELETE选项。InnoDB支持5种不同的动作，如果没有指定ON DELETE或者ON UPDATE，默认的动作为RESTRICT:

  1. CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都被InnoDB所支持。
  2. SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。
  3. NO ACTION: InnoDB拒绝删除或者更新父表。
  4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。
  5. SET DEFAULT: InnoDB目前不支持。
    外键约束使用最多的两种情况：
    1）父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败；
    2）父表更新时子表也更新，父表删除时子表匹配的项也删除。
    前一种情况，在外键定义中，我们使用ON UPDATE CASCADE ON DELETE RESTRICT；
    后一种情况，可以使用ON UPDATE CASCADE ON DELETE CASCADE。

非空

非空约束(NOT NULL),听名字就能理解，被非空约束的列，在插入值时必须非空。
在MySQL中违反非空约束，不会报错，只会有警告.

数据库设计三范式

  • 1NF：要求一张表只描述一件事。比如学生表只能保存学生信息，和学生无关的信息不得保存；
  • 2NF：要求保证表中每一行数据的原子性。即表中不得存在完全相同的数据行，存在完全相同的数据行叫数据冗余（或叫数据副本），这是一种错误，要尽量的减少和避免，无法完全完全消除。解决方案主键或标识，一般的是主键配合标识去用的。
  • 3NF：（外键）表中其他的（外）键要依赖于主（键表）（主键）键而存在。
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 158,233评论 4赞 360
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 67,013评论 1赞 291
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 108,030评论 0赞 241
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 43,827评论 0赞 204
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,221评论 3赞 286
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,542评论 1赞 216
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,814评论 2赞 312
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,513评论 0赞 198
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,225评论 1赞 241
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,497评论 2赞 244
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,998评论 1赞 258
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,342评论 2赞 253
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 32,986评论 3赞 235
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,055评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,812评论 0赞 194
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,560评论 2赞 271
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,461评论 2赞 266

推荐阅读更多精彩内容

  • Oracle笔记（二）
    Oracle笔记系列这几篇是来自一位老师的笔记，分享给大家放在简书上，主要方便自己时常复习，还有学习Oracle的...
    panda_Hi阅读 1,008评论 0赞 0
  • Oracle笔记（一）
    Oracle笔记系列这几篇是来自一位老师的笔记，分享给大家放在简书上，主要方便自己时常复习，还有学习Oracle的...
    panda_Hi阅读 780评论 0赞 1
  • 摘录的一些知识点
    ORACLE自学教程 --create tabletestone ( id number, --序号usernam...
    落叶寂聊阅读 1,008评论 0赞 0
  • MySQL 常用语法总结
    一、SQL速成 结构查询语言(SQL)是用于查询关系数据库的标准语言，它包括若干关键字和一致的语法，便于数据库元件...
    shadow雨轩阅读 494评论 0赞 3
  • 到底有多“划不来“
    我婆婆是个很可爱的东北老太太，她迷糊、性格有点面，还经常闹一闹小孩子脾气。而我公公则是标准的东北大男子主义精神守护...
    爱生活爱sumi酱阅读 575评论 0赞 0