MySQL数据库
MySQL数据库
JDBC
MySQL入门
1、数据库介绍及安装
1.1. 数据库的介绍
数据的存储方式：
Java中创建对象 Student s = new Student(1,“张三”) 存在内存中
学习了IO流，可以把数据保存到文件中
| 存储位置 | 优点 | 缺点 |
|---|---|---|
| 内存 | 快 | 不能持久保存 |
| 文件 | 持久保存 | 操作不方便 |
| 数据库 | 1、持久保存 2、方便查询 3、对数据管理方便 | 需要购买，占用资源 |
“去IOE化”
1.2.常见的数据库
Oracle:收费，Oracle公司的产品 大型
DB2:IBM 收费 大型
Microsoft SQL Server：微软 中型
Access：微软，小型
MySQL：中小型，免费开源，Sun，Oracle收费，
MariaDB
SQLite:嵌入式的小型数据库，应用在手机端
我们学习使用的是MySQL? 免费，功能强大
1.3.数据库的安装和使用
数据库用途：
1.多用途数据库
2.事务数据库
3.非事务数据库
表空间路径
并发连接上限：15
是否启动TCP/IP连接，设置端口（MySQL默认端口3306），如果不启动的话，就只能在自己电脑上使用MySQL了。“Enable Strict Mode”启动标准模式，不允许MySQL有任何细小的语法错误！
编码：utf-8
注意：选择最后一个选项，选择utf-8编码
把MySql设置为WIndow服务
注意：选中第二选项，自动配置window的环境变量
设置root用户的密码
安装成功
注意：如果安装失败，可能存在现象是“Start service”长时间卡住了，则需要重新卸载，再次安装，一般卸载不是彻底
1.4.MySQL数据库的启动和登录
1.4.1 通过控制台访问MySQL
默认安装好MySQL之后，会自动启动MySQL服务
启动MySQL服务的方式1：
直接去window的服务器中去找MySQL服务
也可以在DOS窗口中通过命令启动
说明该DOS窗口中没有启动MySQL服务权限，这个时候DOS窗口必须是管理员身份去启动
在控制台中连接MySQL数据库
登录方式1：
mysql -u用户名 -p密码 #默认登录的是本机数据库
说明MySQL安装的时候没有将环境变量配置在PATH中
所以，需要重新配置MySQL的环境变量
说明已经以root用户身份登录进MySQL数据库中了
登录方式2:
mysql -hIP地址 -u用户名 -p密码
“10.11.52.46”
退出MySQL:
exit或者quit
1.4.2 SQLyog图形化工具–MySQL的客户端
连接成功后
1.4.3 Navicat图形化工具连接MySQL
连接名：给当前连接起个名称
主机名或IP地址：如果是本机就是localhost，如果是远程的则写上对方服务器的ip地址
端口：默认就是3306
用户名：root
密码：
JDK->JRE->JVM
2、SQL概念
2.1 什么是SQL
Structured Query Language:结构化查询语言
2.2 SQL的作用
1.是数据库中一种规范语言，各种数据库都支持
2.通用的数据库操作语言
3.不同的数据库还有存在个别的SQL差异
2.3 SQL语句分类
- DDL(data definition language):数据定义语言，创建数据库，创建表
- DML:数据操作语言， 增加数据、删除数据、修改数据
- DQL：数据查询语言，查询操作
- DCL：数据控制语言，用户权限
2.4 MySQL语法
1.SQL中不区分大小写，建议关键词用大写
2.每条SQL语句都需要一个分号结束
3.3种注释
| 注释的语法 | 说明 |
|---|---|
| – | 单行注释 |
| /* */ | 多行注释 |
| # | mysql特有的注释方式 |
3、DDL操作数据库
数据库的定义语言，主要用来创建库和表
库：可以理解成各种数据的集合 学科 Java学科
表：某类数据的集合 班级 Java2009 学员 数据
3.1 创建数据库
- 方式1
CREATE DATABASE 数据库名;--创建数据库，如果存在则报错
- 方式2
CREATE DATABASE IF NOT EXISTS 数据库名;--如果数据库不存在则创建
- 方式3
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
CREATE DATABASE MyFirst;#创建一个MyFirst数据库，如果存在则提示错误
CREATE DATABASE IF NOT EXISTS MyFirst; #如果不存在则创建一个MyFirst数据库
CREATE DATABASE MyThird CHARACTER SET utf8;#创建一个指定utf8编码的数据格式，默认Mysql的编码是按照安装时指定的编码格式
编码：ANSII,Unicode,GBK,GB2312,BIG5,UTF-8
3.2 查看数据库
SHOW DATABASES; #显示所有数据库
SHOW CREATE DATABASE MySecond;#显示MySecond数据库信息
3.3 修改数据库
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集；
ALTER DATABASE MyFirst CHARACTER SET UTF8;
3.4 删除数据库
DROP DATABASE 数据库名；
DROP DATABASE MyFirst;
3.5 使用数据库
USE 数据库名;
存储引擎：Innodb/myisam
MySQL基础
4、DDL操作表
创建好数据库之后，接下来我们可以考虑向该库中存放数据，但是数据不能直接存储在库中，数据需要存储在库中的“货架”中。这个货架就是数据库中
表
4.1 创建表
CREATE TABLE 表名(
字段名1 字段类型1，
字段名2 字段类型2，
...
)
create table student(
学号 int,
姓名 varchar(10),
年龄 int,
性别 char(2),
地址 varchar(200),
身高 decimal(3,2),
注册时间 date
)
字段、列：描述表中的标题
行、记录：表中描述一个信息的记录
4.2 MySQL中的数据类型
4.2.1 常用的数据类型
| 类型 | 说明 |
|---|---|
| int（4bytes） | 整性 |
| double | 浮点类型 |
| varchar/char | 字符串类型 |
| date | 日期类型，格式为yyyy-MM-dd |
0000000000
1.2.2 详细的类型如下
1、数字类型
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
2、字符串类型
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
char和varchar的区别(面试题)
char：指定好大小之后就固定了，如果没有占用则用空格补充
varchar：指定好大小之后，会根据实际大小进行计算
所以，varchar占用空间比char灵活，但是会耗费效率
3、日期类型
| 类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:mm:ss | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
Date/Time/Datetime
4.3 在MySQL中创建表
显示当前库所有表
mysql> show tables;
删除表
mysql> drop table bb;
mysql> drop table if exists bb;
创建学员表
mysql> create table student(
-> id int,
-> name varchar(10),
-> birthday date,
-> height decimal(3,2),
-> gender char(2)
-> );
Query OK, 0 rows affected (0.04 sec)
查看表结构
mysql> desc student;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| Id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| height | decimal(3,2) | YES | | NULL | |
| gender | char(2) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.04 sec)
4.4 修改表结构
4.4.1 添加列：ADD
alter table 表名 add 列名 类型；
#给学生表添加一个分数字段
mysql> alter table student add score double;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.4.2 修改列类型：modify
alter table 表名 modify 列名 新类型；
#修改学生表分数字段类型
mysql> alter table student modify score decimal(3,1);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.4.3 修改列的名称：change
alter table 表名 change 旧名称 新名称 类型;
mysql> alter table student change score avg_score decimal(3,1);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.4.4 删除列：drop
alter table 表名 drop 列名；
#删除student表中的height字段
mysql> alter table student drop height;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
总结:
修改表中类型和字段名的命令：add:添加，modify：修改类型，change：修改字段名称，drop：删除字段
4.4.5 修改表名
rename table 表名 to 新表名；
mysql> rename table student to student_info;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student_info |
+---------------+
1 row in set (0.00 sec)
4.4.6 修改字符集
alter table 表名 character set 字符集；
mysql> create table scores(
-> id int,
-> subject_name varchar(20),
-> student_name varchar(20),
-> score double
-> ) character set utf8;
Query OK, 0 rows affected (0.04 sec)
mysql> show create table scores;
CREATE TABLE `scores` (
`id` int(11) DEFAULT NULL,
`subject_name` varchar(20) DEFAULT NULL,
`student_name` varchar(20) DEFAULT NULL,
`score` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
注意：在表名和字段名上添加，这个``符号的作用是为了防止与关键词重名
作业：用SQL语句创建如下表
- 国家表
- 类型表
- 员工表
create table employee(
employee_id int,
first_name varchar(20),
last_name varchar(20),
email varchar(20),
phone_number varchar(200),
hire_date date,
job_id varchar(20),
salary double,
commission_pct double,
manager_id int,
department_id int
)
- 岗位表
- 地区表
5、DML操作数据（重点）
DDL语句，建库建表。向表中存储数据，增加数据、修改数据、删除数据，DML操作
5.1 插入记录
5.1.1 语法
insert [into] 表名 ([字段名1,字段名2,字段名N...]) values (字段值1,字段值2,字段值N...)
- insert into 表名：表示向哪张表中添加数据
- 字段名1,字段名2,字段名N…：表示给该表的哪些字段添加值
- values (字段值1,字段值2,字段值N…)：给字段添加的具体值
insert into employee(employee_id,first_name, last_name, email, phone_number,hire_date, job_id, salary, commission_pct, manager_id, department_id) values(1001,'san','zhang','zhangsan@126.com','110','2020-2-28','100', 2000,0.1,1003,2001);
#如果给当前表中的所有字段都添加了值，则可以将字段名省略
insert into employee
values(1002,'si','li','lisi@126.com','112','2020-3-25','200', 2400,0.5,1003,2002);
insert into employee(employee_id,first_name, last_name) values(1003,'wu','wang');
#将表中所有的字段全部都写出来了
insert into student_info(id, name, birthday,gender,avg_score) values (1001,'tom','1998-3-4','N', 89.9);
#如果需要向该表所有字段添加数据，则字段名可以省略
insert into student_info values (1003,'jack','1997-12-5','N', 90);
#也可以插入部分字段数据
insert into student_info(id,birthday,name) values (1003,'jack','1997-12-5');
通过DOS创建向表中添加中文
mysql> insert into student_info(id, name) values(1004,'张曼玉');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC2\xFC\xD3\xF1' for column 'name' at row 1
mysql>
#设置dos窗口编码格式
mysql> set names
gbk; #只能在当次窗口有效
Query OK, 0 rows affected (0.00 sec)
5.1.2 批量插入数据（蠕虫复制）
insert into 表名（字段1，字段2，字段3...） select 字段1，字段2，字段3... from 表名；
mysql> insert into student_info(id, name, birthday,gender,avg_score) select id,name,birthday,gender,avg_score from student_info;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
insert into employee select * from employee;
5.1.3 INSERT注意事项
1.插入的数据应该要和字段的类型保持一致
2.数据的大小应该在列类型的范围之间
3.在values中的值位置要和字段名的位置一一对应
4.字符和日期都需要用引号括起来
5.不指定列名或使用null，表示插入空值了
5.2 更新表记录
5.2.1 基本语法格式
update 表名 set 字段1=值1,字段2=值2… [where 条件表达式]
- update 表名 ：修改对应表的数据
- set 字段1=值1：表示修改字段1的值为值1
- where：满足条件的数据进行修改
5.2.2 不带条件的数据进行修改
mysql> update student_info set gender='男';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
不带条件会影响表中的所有行字段数据
5.2.3 带条件的数据修改
update 表名 set 字段=值 where 字段名=值
mysql> update student_info set birthday='1995-3-4' where name='jack';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
修改的动作是一行记录一行记录的进行where条件判断，如果该行的判断成立则会修改该行对应字段值。
mysql> update student_info set birthday='1996-3-4',gender='女',avg_score=99 where name='jack';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
where后面的条件可以有多种操作符
| 操作符 | 说明 |
|---|---|
| = | 判断是否相等 |
| != <> | 判断不相同 |
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| 在范围之中 |
#将编号大于1003的性别修改成男的
mysql> update student_info set gender='男' where id>1003;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#空判断需要使用is或者 is not
mysql> update student_info set birthday='1997-3-9' where avg_score is null;
update employee set last_name='song' where email = null
5.3 删除表记录
5.3.1 语法格式
delete from 表名 [where 条件表达式]
- 如果不指定where则表示将表中的所有数据全部删除
5.3.2 不带条件删除数据
#将student_info表中的所有数据全部删除
delete from student_info;
#删除表中的所有数据，比delete更彻底
truncate table student_info;
delete和truncate的区别是，truncate相当于删除了该表然后重新创建了一个
5.3.3 带条件的删除数据
delete from student_info where 条件
delete from student_info where avg_score is null;
DML总结：
- insert:增加
- insert into 表名 (字段1， 字段2…) values（值1，值2…）
- 蠕虫复制：insert into 表名(字段) select 字段 from 表
- update：修改
- update 表名 set 字段1=值1，字段2=值2… where 条件
- delete：删除
- delete from 表名 where 条件
6、DQL查询数据（重点、难点）
DQL:数据查询语言，通过select实现数据的查询
6.1 简单查询
SELECT 字段1，字段2，字段3… FROM 表名;
select id,name,gender,birthday,avg_score from student_info;
mysql> select id,name,gender,birthday,avg_score from student_info;
+------+------+--------+------------+-----------+
| id | name | gender | birthday | avg_score |
+------+------+--------+------------+-----------+
| 1001 | tom | 女 | 1998-03-04 | 89.9 |
| 1003 | jack | 女 | 1996-03-04 | 99.0 |
+------+------+--------+------------+-----------+
2 rows in set (0.01 sec)
select * from 表名;#将表中的所有字段全部显示出来
#显示所有字段
mysql> select * from student_info;
+------+------+------------+--------+-----------+
| Id | name | birthday | gender | avg_score |
+------+------+------------+--------+-----------+
| 1001 | tom | 1998-03-04 | 女 | 89.9 |
| 1003 | jack | 1996-03-04 | 女 | 99.0 |
+------+------+------------+--------+-----------+
2 rows in set (0.00 sec)
#显示部分字段的数据
mysql> select name, birthday from student_info;
+------+------------+
| name | birthday |
+------+------------+
| tom | 1998-03-04 |
| jack | 1996-03-04 |
+------+------------+
2 rows in set (0.00 sec)
给字段起别名:as
#as可以省略
mysql> select name,gender as 性别, birthday from student_info;
+------+------+------------+
| name | 性别 | birthday |
+------+------+------------+
| tom | 女 | 1998-03-04 |
| jack | 女 | 1996-03-04 |
+------+------+------------+
2 rows in set (0.00 sec)
mysql> select name,gender 性别, birthday from student_info;
+------+------+------------+
| name | 性别 | birthday |
+------+------+------------+
| tom | 女 | 1998-03-04 |
| jack | 女 | 1996-03-04 |
+------+------+------------+
2 rows in set (0.00 sec)
6.2 带条件的查询
select 字段1，字段2… from 表 where [条件表达式]
mysql> select * from student_info where gender='男';
+------+-------+------------+--------+-----------+
| Id | name | birthday | gender | avg_score |
+------+-------+------------+--------+-----------+
| 1004 | jerry | 1997-12-03 | 男 | 58.0 |
| 1005 | trump | 1945-03-06 | 男 | 10.0 |
+------+-------+------------+--------+-----------+
2 rows in set (0.01 sec)
Java中&& 和& 区别？
表达式1 && 表达式2：如果第一个表达式的结果为false,则第二个表达式不执行了
表达式1 & 表达式2：第二个表达式不管第一个表达式的结果是什么都需要执行
mysql> select * from student_info where gender='男' and avg_score>50;
+------+-------+------------+--------+-----------+
| Id | name | birthday | gender | avg_score |
+------+-------+------------+--------+-----------+
| 1004 | jerry | 1997-12-03 | 男 | 58.0 |
+------+-------+------------+--------+-----------+
1 row in set (0.01 sec)
6.3 除重复
将对应字段中值相同的进行合并
select distinct 字段名1，字段2… from 表名
mysql> select gender from student_info;
+--------+
| gender |
+--------+
| 女 |
| 女 |
| 男 |
| 男 |
+--------+
4 rows in set (0.00 sec)
mysql> select distinct gender from student_info;
+--------+
| gender |
+--------+
| 女 |
| 男 |
+--------+
2 rows in set (0.01 sec)
MySQL多表操作和数据库设计
7、DQL中的数据查询
select 字段1，字段2… from 表名 [where 条件表达式]
7.1 查询的结果参与运行
某列数据和固定值运行
select 列名1+固定值 from 表名
mysql> select name,avg_score+10 from student_info;
+-------+--------------+
| name | avg_score+10 |
+-------+--------------+
| tom | 99.9 |
| jack | 109.0 |
| jerry | 68.0 |
| trump | 20.0 |
+-------+--------------+
4 rows in set (0.01 sec)
mysql> select name,avg_score+10 as new_score from student_info;
+-------+-----------+
| name | new_score |
+-------+-----------+
| tom | 99.9 |
| jack | 109.0 |
| jerry | 68.0 |
| trump | 20.0 |
+-------+-----------+
4 rows in set (0.00 sec)
某列和其他列数据参与运算
select 列名1 + 列名2 from 表名；
注意：参入运行的列类型必须是数值类型
有时候我们可能会需要将两个列进行字符串拼接
mysql> select name+gender from student_info;
+-------------+
| name+gender |
+-------------+
| 0 |
| 0 |
| 0 |
| 0 |
+-------------+
4 rows in set, 8 warnings (0.00 sec)
#注意在mysql中+符合不能直接进行字符串拼接
#concat(字符串1，字符串2，字符串...):表示将多个字符串进行拼接
mysql> select concat(name, '-',gender) from student_info;
+--------------------------+
| concat(name, '-',gender) |
+--------------------------+
| tom-女 |
| jack-女 |
| jerry-男 |
| trump-男 |
+--------------------------+
4 rows in set (0.00 sec)
#now():显示当前时期
#year():获取年
#month():获取月
#day():获取日
"2020年6月11日"
select concat(year(now()),'年',month(now()),'月', day(now()),'日');
7.2 条件查询
没有条件则会将所有的数据全部查询显示，一般我们需要对记录做一些过滤，就需要添加条件进行查询了
select 字段名 from 表名 where 条件；
1.运算符
| 比较运算符 | 说明 |
|---|---|
| =，>，<，<=，>=，!=，<> | 判断 |
| between … and … | “包头又包尾” |
| in(集合) | 集合表示多个值，值之间用，隔开 |
| is null/ is not null | 判断是否为Null |
| like ‘值’ | 模糊查询 |
| and && 、or || 、 ! not | 逻辑运行 |
mysql> select employee_id, salary from t_employees where salary>=10000 and salary<=17000;
mysql> select employee_id, salary from t_employees where salary between 10000 and 17000;
mysql> select employee_id,salary,job_id from t_employees where JOB_ID in ('IT_PROG','AC_ACCOUNT');
mysql> select employee_id,salary,job_id from t_employees where JOB_ID='IT_PROG' or JOB_ID='AC_ACCOUNT'
select 字段名 from 表名 where 字段名 like '通配符字符串'模糊查询、通配符查询：_ %
%:表示任意多个字符
_:表示任意一个字符
mysql> select first_name from t_employees where first_name like 'A%';
+------------+
| first_name |
+------------+
| Alexander |
| Alexander |
| Adam |
| Alberto |
| Allan |
| Amit |
| Alyssa |
| Alexis |
| Anthony |
| Alana |
+------------+
10 rows in set (0.00 sec)
mysql> select first_name from t_employees where first_name like '_A%';
+------------+
| first_name |
+------------+
| David |
| Valli |
| Nancy |
| Daniel |
| Karen |
#多种条件组合查询
mysql> select * from t_departments where manager_id is not null and location_id > 1700;
+---------------+------------------+------------+-------------+
| DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID |
+---------------+------------------+------------+-------------+
| 20 | Marketing | 201 | 1800 |
| 40 | Human Resources | 203 | 2400 |
| 70 | Public Relations | 204 | 2700 |
| 80 | Sales | 145 | 2500 |
+---------------+------------------+------------+-------------+
4 rows in set (0.00 sec)
7.3 排序
select 字段 from 表名 where 字段=值
order by 字段 asc|desc
- asc：升序，默认值
- desc：降序
可以通过将查询出来的结果按照指定的字段排序显示，不会影响数据库中数据的顺序
mysql> select employee_id,first_name,salary from t_employees order by salary asc;
组合排序：同时对多个字段进行排序，如果第一个字段相同，则按照第二字段进行排序，依次类推！
select 字段 from 表名 where 字段=值
order by 字段1 asc|desc,字段2 asc|desc
mysql> select employee_id,first_name,salary from t_employees order by salary asc,employee_id desc;
#注意：先过滤数据（where条件），后排序
mysql> select employee_id,first_name,salary from t_employees where salary>10000 order by salary asc,employee_id desc;
7.4 聚合函数
将表中某列的数据进行统计，统计过程中所使用的一些函数我们成为聚合函数。注意：聚合函数默认会忽略空值NULL
| SQL中的聚合函数 | 说明 |
|---|---|
| min(列名) | 求这一列的最小值 |
| max(列名) | 求这一列的最大值 |
| sum(列名) | 求这一列的总和 |
| avg(列名) | 求这一列的平均值 |
| count(列名、*) | 统计该列有多少行 |
mysql> select avg(comm) from t_employees;
mysql> select sum(comm) from t_employees;
mysql> select min(comm) from t_employees;
mysql> select max(comm) from t_employees;
#注意会自动忽略该列的空行
mysql> select count(comm) from t_employees;
mysql> select count(*) from t_employees;
mysql> select job_id,count(job_id),max(salary) max,min(salary) min,avg(salary) avg from t_employees where count(job_id)>10 group by job_id;
ERROR 1111 (HY000): Invalid use of group function
#在where后面不能直接使用聚合函数
#显示薪水最低员工的信息
#错误的，where后面不能直接使用聚合函数
mysql> select * from t_employees where salary=min(salary);
select min(salary) from t_employees;
#子查询
select * from t_employees where salary=(select min(salary) from t_employees);
7.5 分组
根据指定的列将对应数据放在一个组中
select 字段名 from 表名 group by 分组字段 [having 条件]
注意：分组之后只能显示该组的公共信息
先筛选年龄大于18岁的，然后按照性别分组，之后显示每个组中最大的年龄！
select gender,max(age) from student_info where age >18 group by gender
#将Job_id相同的员工放在一个组中，然后显示组名
mysql> select job_id from t_employees group by job_id;
分组一般是和
聚合函数在一起使用！
#job_id:是组信息
#count(job_id):是统计该组中的数据个数
mysql> select job_id,count(job_id) from t_employees group by job_id;
+------------+---------------+
| job_id | count(job_id) |
+------------+---------------+
| AC_ACCOUNT | 1 |
| AC_MGR | 1 |
分组以后如果需要对组中数据进行筛选则需要使用having
#筛选出员工个数大于10以上的组
mysql> select job_id,count(job_id),max(salary) max,min(salary) min,avg(salary) avg from t_employees group by job_id having count(job_id)>10;
+----------+---------------+-------+------+-----------+
| job_id | count(job_id) | max | min | avg |
+----------+---------------+-------+------+-----------+
| SA_REP | 30 | 11500 | 6100 | 8350.0000 |
| SH_CLERK | 20 | 4200 | 2500 | 3215.0000 |
| ST_CLERK | 20 | 3600 | 2100 | 2785.0000 |
+----------+---------------+-------+------+-----------+
3 rows in set (0.00 sec)
having是分组以后的条件判断
7.6 Limit
用来控制显示的记录个数，默认是全部都显示。主要用在分页上
limit 起始位置, 个数
limt 0, 5;#从第0个位置开始，显示5条件记录
mysql> select *from t_employees order by salary asc limit 0,1;
# select *from t_employees limit 0, 10 第一页
# select * from t_employees limit 10, 10 第二页
# select * from t_employees limit (currentPage-1)*pageSize, pageSize 第78页
select * from t_employees limit 770, 10
总结：
select 字段 from 表名 where 条件查询 group by 字段 having 分组条件 order by 排序字段 limit 起始位置，个数
8、综合练习
#1、 查询Student表中的所有记录的Sname、Ssex和Class列。
mysql> select sname, ssex,class from student;
#2、 查询教师所有的单位即不重复的Depart列。
mysql> select distinct depart from teacher;
#3、 查询Student表的所有记录。
mysql> select * from student;
#4、 查询Score表中成绩在60到80之间的所有记录。
mysql> select * from score where degree between 60 and 80;
#5、 查询Score表中成绩为85，86或88的记录。
mysql> select * from score where degree in(85,86,88);
#6、 查询Student表中“95031”班或性别为“女”的同学记录。
mysql> select * from student where class='95031' or ssex='女';
#7、 以Class降序查询Student表的所有记录。
mysql> select * from student order by class desc;
#8、 以Cno升序、Degree降序查询Score表的所有记录。
mysql> select * from score order by cno asc, degree desc;
#9、 查询“95031”班的学生人数。
mysql> select count(*) from student where class='95031';
#10、查询Score表中的最高分的学生学号和课程号。
mysql> select * from score order by degree desc limit 0,1 ;
#11、查询‘3-105’号课程的平均分。
mysql> select avg(degree) from score where cno='3-105';
#12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。（重点掌握）
1.筛选出3开头的课程
mysql> select * from score where cno like '3%';
2.分组
mysql> select cno, count(cno) from score where cno like '3%' group by cno;
3.对分组中的元素进行筛选
mysql> select cno, count(cno) from score where cno like '3%' group by cno having count(cno)>=5;
4.计算平均分
mysql> select cno, count(cno),avg(degree) from score where cno like '3%' group by cno having count(cno)>=5;
+-------+------------+-------------+
| cno | count(cno) | avg(degree) |
+-------+------------+-------------+
| 3-105 | 6 | 81.50000 |
+-------+------------+-------------+
1 row in set (0.00 sec)
#13、查询最低分大于70，最高分小于90的Sno列。
mysql> select sno from score group by sno having max(degree)<90 and min(degree)>70;
14、查询所有学生的Sname、Cno和Degree列。
15、查询所有学生的Sno、Cname和Degree列。
16、查询所有学生的Sname、Cname和Degree列。
17、查询“95033”班所选课程的平均分。
18、假设使用如下命令建立了一个grade表：
CREATE TABLE Grade
(
low NUMERIC(3,0),
upp NUMERIC(3,0),
rank CHAR(1)
);
INSERT INTO grade VALUES(90,100,'A');
INSERT INTO grade VALUES(80,89,'B');
INSERT INTO grade VALUES(70,79,'C');
INSERT INTO grade VALUES(60,69,'D');
INSERT INTO grade VALUES(0,59,'E');
现查询所有同学的Sno、Cno和rank列。
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
9、表约束
对表中的数据进行限制，保证数据具有正确性、有效性和完整性。
3.1 约束类型
| 约束名 | 关键词 |
|---|---|
| 主键 | primary key |
| 唯一 | unique |
| 非空 | not null |
| 外键 | foreign key |
| 检查约束 | check约束：mysql不支持 |
3.2 主键约束
用来唯一标识表中的每一条记录
创建主键：
- primary key
- 主键的特点：1、唯一 2、非空 not null
- 自动排序
1、创建表的时候直接指定主键约束
create table 表(
字段名1 类型1 primary key，#主键约束
字段2 类型2
)
2、通过修改的方式给表添加主键约束
alter table 表名 add primary key (字段名)；
mysql> alter table student_info add primary key(Id);
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from student_info;
+------+-------+------------+--------+-----------+
| Id | name | birthday | gender | avg_score |
+------+-------+------------+--------+-----------+
| 1001 | tom | 1998-03-04 | 女 | 89.9 |
| 1002 | bush | 1921-12-02 | 男 | 90.0 |
| 1003 | jack | 1996-03-04 | 女 | 99.0 |
| 1004 | jerry | 1997-12-03 | 男 | 58.0 |
| 1005 | trump | 1945-03-06 | 男 | 10.0 |
+------+-------+------------+--------+-----------+
5 rows in set (0.00 sec)
mysql> insert into student_info values(1002, 'bush', '1921-12-2', '男', 90);
ERROR 1062 (23000): Duplicate entry '1002' for key 'PRIMARY'
mysql> insert into student_info values(null, 'bush', '1921-12-2', '男', 90);
ERROR 1048 (23000): Column 'Id' cannot be null
总结：
一般每张表都需要设置一个主键字段，而且该主键字段不作为业务字段。大部分我们都是设置一个id字段，把这个id字段作为主键。
删除主键：
alter table 表名 drop primary key (字段名)；
自增主键
这种主键的特点是数据是自动增长，在添加数据时可以不用指定auto_increment 表示自动增长（字段类型必须是整数类型）
#默认auto_increment从1开始自增
mysql> create table bb(
-> id int primary key auto_increment,
-> name varchar(200)
-> );
Query OK, 0 rows affected (0.03 sec)
#null:表示自增生成
mysql> insert into bb values(null, 'aaa');
Query OK, 1 row affected (0.01 sec)
mysql> select * from bb;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
+----+------+
1 row in set (0.00 sec)
#表示只给name字段提供值，id是自增
mysql> insert into bb(name) values('bbbb');
#默认auto_increment从1000开始自增
mysql> create table bb(
-> id int primary key auto_increment,
-> name varchar(200)
-> )auto_increment=1000;
alter table 表名 auto_increment=1000;
MySQL链表操作
10、数据库表的约束
10.1 唯一约束
保证该列的数据不会重复
语法格式：
字段名 字段类型 unique
create table 表名(
id int primary key,
name varchar(20) unique
)
mysql> alter table student_info modify name varchar(200) unique;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
#设置唯一约束时会生成一个唯一索引，而该索引长度不能太长
mysql> alter table student_info modify name varchar(20) unique;
ERROR 1062 (23000): Duplicate entry 'tom' for key 'name'
#能否重复插入null值？
mysql> insert into student_info values(1007, null, '1996-10-3', '男', 78);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student_info values(1008, null, '1999-10-3', '男', 78);
Query OK, 1 row affected (0.00 sec)
null没有数据，不存在重复的问题
10.2 非空约束
就是该列不能为Null
字段名 字段类型 not null
create table 表名(
id int primary key,
name varchar(20) unique,
birthday date not null
)
#通过修改表结构给该字段添加非空约束
mysql> alter table student_info modify birthday date not null;
Query OK, 8 rows affected (0.04 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into student_info values(1009, null, null, '男', 78);
ERROR 1048 (23000): Column 'birthday' cannot be null
非空+唯一：可以适用于表中的多个字段，但是主键在一张表中只能适用一次
10.3 默认值
如果添加数据时没有提供具体数则有数据库默认提供
字段名 字段类型 default 默认值
create table users
(
id int primary key auto_increment,
name varchar(20) unique not null,
birthday date not null,
address varchar(200) default '地址不详'
)
10.4 外键约束
create table emp(
id int primary key auto_increment,
name varchar(30),
age int,
dep_name varchar(30),
dep_location varchar(20)
);
insert into emp values(null, '张三', 20, '研发部', '上海');
insert into emp values(null, '刘会操', 21, '研发部', '上海');
insert into emp values(null, '缪家成', 19, '市场', '北京');
insert into emp values(null, '汪玮', 19, '市场', '北京');
insert into emp values(null, '董奇', 22, '财务', '成都');
上述数据表的问题：
- 数据冗余：存在大量重复的数据
- 后期修改数据会存在问题
解决方案：拆表
拆成部门和员工表
#员工表
create table emp(
eid int primary key auto_increment,
emp_name varchar(30),
emp_age int,
dep_id int #存在该员工的部门编号
);
#部门表
create table dep(
did int primary key auto_increment,
dep_name varchar(30),
dep_location varchar(30)
);
insert into dep values(null, '研发部', '上海');
insert into dep values(null, '市场', '北京');
insert into dep values(null, '财务', '成都');
insert into emp values(null, '张三', 20, 1);
insert into emp values(null, '刘会操', 21, 1);
insert into emp values(null, '缪家成', 19, 2);
insert into emp values(null, '汪玮', 19, 2);
insert into emp values(null, '董奇', 22,3);
#这数据就是有问题的数据了，因为9这个编号没有对应的部门信息
insert into emp values(null, '朱顺永', 16,9);
可以通过外键约束进行数据约束
- 主表：用来约束别人的数据
- 从表：被约束的表
constraint 约束名称 foreign key (字段) references 主键表(字段)
create table emp(
eid int primary key auto_increment,
emp_name varchar(30),
emp_age int,
dep_id int, #存在该员工的部门编号
constraint dep_id_fk foreign key (dep_id) references dep(did)
);
mysql> insert into emp values(null, '朱顺永', 16,9);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`emp`, CONSTRAINT `dep_id_fk` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`did`))
外键级联问题：对主表的数据进行修改需要考虑从表的感受
先处理从表的数据，然后才能修改主表的数据
mysql> delete from dep where did=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`emp`, CONSTRAINT `dep_id_fk` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`did`))
级联操作：在修改或删除主表的主键时，同时修改或删除从表的外键值
| 语法 | 说明 |
|---|---|
| on update cascade | 级联更新，只能在创建表时候创建级联更新。在更新主表中的主键，从表的外键列也会自动同步更新 |
| on delete cascade | 级联删除 |
create table emp(
eid int primary key auto_increment,
emp_name varchar(30),
emp_age int,
dep_id int, #存在该员工的部门编号
constraint dep_id_fk foreign key (dep_id) references dep(did) on update cascade on delete cascade
);
mysql> select * from emp;
+-----+----------+---------+--------+
| eid | emp_name | emp_age | dep_id |
+-----+----------+---------+--------+
| 1 | 张三 | 20 | 1 |
| 2 | 刘会操 | 21 | 1 |
| 3 | 缪家成 | 19 | 2 |
| 4 | 汪玮 | 19 | 2 |
+-----+----------+---------+--------+
4 rows in set (0.00 sec)
mysql> select * from dep;
+-----+----------+--------------+
| did | dep_name | dep_location |
+-----+----------+--------------+
| 1 | 研发部 | 上海 |
| 2 | 市场 | 北京 |
+-----+----------+--------------+
2 rows in set (0.00 sec)
#修改主表的数据，因为我们设置了级联操作，则从表的外键数据会自动更新
mysql> update dep set did=5 where did=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp;
+-----+----------+---------+--------+
| eid | emp_name | emp_age | dep_id |
+-----+----------+---------+--------+
| 1 | 张三 | 20 | 1 |
| 2 | 刘会操 | 21 | 1 |
| 3 | 缪家成 | 19 | 5 |
| 4 | 汪玮 | 19 | 5 |
+-----+----------+---------+--------+
4 rows in set (0.00 sec)
#删除主表时，会自动级联删除从表的数据
mysql> delete from dep where did=5;
Query OK, 1 row affected (0.01 sec)
mysql> select *from dep;
+-----+----------+--------------+
| did | dep_name | dep_location |
+-----+----------+--------------+
| 1 | 研发部 | 上海 |
+-----+----------+--------------+
1 row in set (0.00 sec)
mysql> select * from emp;
+-----+----------+---------+--------+
| eid | emp_name | emp_age | dep_id |
+-----+----------+---------+--------+
| 1 | 张三 | 20 | 1 |
| 2 | 刘会操 | 21 | 1 |
+-----+----------+---------+--------+
2 rows in set (0.00 sec)
10.5 数据约束总结
| 约束名 | 关键词 | 特点 |
|---|---|---|
| 主键 | primary key | 非空、唯一、自动排序 |
| 唯一 | unique | 不重复 |
| 非空 | not null | 值不能为空 |
| 默认 | default | 如果没有值，则使用默认值 |
| 外键 | foreign key | 从表的数据参照主表对应的数据 |
11、表与表之间的关系
11.1 表之间的关系
在现实中存在对象之间的关系，比如：老师和学生，领导和员工。那么我们在设置这些表的时候也需要考虑关系问题，表关系
| 表与表之间的关系 | 说明 |
|---|---|
| 一对多 | 老师和学生 |
| 多对多 | 课程和学生，一门课程可以被多个学生选，一个学生也可以选择多门课程 |
| 一对一 | 员工和简历，老婆和老公 |
11.2 一对多
一对多：(1:N)，班级和学员
一般创建一对多关系的原则是：在多的一方定义一个外键字段
create table 班级表(
id int primary key,
name varchar(200)
);
create table 学员表(
id int primary key,
name varchar(200),
age int,
classId int ,
constraint classId_fk foreign key(classId) references 班级表(id)
)
11.3 多对多
多对多（N:M）:学生和课程，用户和角色
多对多的关系表建立原则：需要创建第三方表，这个第三方表至少需要两个字段，这两个字段是外键字段分别指向每一方的主键。
create table 学生表(
id int primary key,
name varchar(200)
)
create table 课程表(
id int primary key,
name varchar(200)
)
#创建关系表
create table 学生课程关系表(
学生编号 int ,
课程编号 int,
constraint 学生编号_fk foreign key(学生编号) references 学生表(id),
constraint 课程编号_fk foreign key(课程编号) references 课程表(id),
)
11.4 一对一
一对一（1：1）：在实际中很好用到，因为可以设计成一张表
创建一对一关系的原则：
- 外键唯一：主表的主键和从表外键，形成主外键关系，外键唯一unique
- 外键为主键
create table 学生表(
id int primary key,
name varchar(200)
)
create table 身份证(
id int unique,
cardId varchar(18) not null,
constraint id_fk foreign key(id) references 学生表(id)
)
create table 学生表(
id int primary key,
name varchar(200)
)
create table 身份证(
id int primary key,
cardId varchar(18) not null
)
insert into 学生表 values(1, '张三');
insert into 身份证 values(1, '111111111111');
总结：
| 表关系 | 关系的维护 |
|---|---|
| 1：M | 在多的一方通过外键维护 |
| N：M | 通过第三方表中的外键字段维护 |
| 1：1 | 外键唯一，主键相同 |
12、数据库设计规范
范式：在设计数据库是需要遵守的一些规则
第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以此类推。一般说来，数据库只需满足第三范式(3NF）就行了。所以这里就只记录三范式相关的知识。
12.1 1NF
表中每一个列都是不可分割的，具有原子性
| 学号 | 姓名 | 年龄 |
|---|---|---|
| 1001 | 张三 | 20 |
| 1002 | 李四 | 19 |
12.2 2NF
第二范式：在满足第一范式的基础上，每个字段都完全依赖于主键
特点：
1.一张表只描述一件事
2.当前表中的所有字段都和主键直接依赖
不满足第二范式的学员信息表
| 学号 | 姓名 | 学院编号 | 学院名称 |
|---|---|---|---|
| 1001 | 张三 | A0002 | 计院 |
| 1002 | 李四 | B004 | 法院 |
分成多张表
| 学号 | 姓名 |
|---|---|
| 1001 | 张三 |
| 学员编号 | 学员名称 |
|---|---|
| A0002 | 计院 |
12.3 3NF
第三范式：满足第二范式的基础上，当前表中每个列直接依赖于主键，而不是通过其他字段间接依赖于主键
不符合第三范式要求的数据
学号 | 姓名 | 所在学院 | 学院地址 |
|---|---|---|---|
| 1001 | 张三 | 计院 | 浦东新区 |
拆表
| 学号 | 姓名 | 学院编号（外键） |
|---|---|---|
| 1001 | 张三 | 1 |
| 学院编号 | 学院 | 学院地址 |
|---|---|---|
| 1 | 计院 | 浦东新区 |
12.4 总结
| 范式 | 特点 |
|---|---|
| 1NF | 原子性：每个列都不可拆分 |
| 2NF | 不能存在局部依赖，每张表值描述一件事情 |
| 3NF | 不能有传递依赖，每个列都必须直接依赖于主键 |
反范式
13、连表查询（重要）
今后的数据查询可能需要来源于多张表的综合数据
13.1 笛卡尔积现象
什么是笛卡尔积？
mysql> select * from t_countries, t_locations;
mysql> select *from t_countries,t_locations where t_countries.country_id=t_locations.country_id;
mysql> select *from t_countries,t_locations where t_countries.country_id=t_locations.country_id and t_countries.country_id='CN';
select 字段 from 表1,表2 where 表1.字段 = 表2.字段
select 字段 from 表1 [inner / left/ right] join 表2 on 表1.字段=表2.字段 where 条件表达式
13.2 内连接
前后两张表
同时存在记录则显示
select 字段 from 表1 inner join 表2 on 表1.字段=表2.字段 where 条件表达式
mysql> select * from b;
+----+-------+------+
| id | title | pid |
+----+-------+------+
| 1 | james | 1 |
| 2 | clerk | 2 |
| 3 | cat | 3 |
+----+-------+------+
3 rows in set (0.00 sec)
mysql> select * from a;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
+----+------+
2 rows in set (0.00 sec)
mysql> select *from a inner join b on a.id=b.pid;
+----+------+----+-------+------+
| id | name | id | title | pid |
+----+------+----+-------+------+
| 1 | aa | 1 | james | 1 |
| 2 | bb | 2 | clerk | 2 |
+----+------+----+-------+------+
2 rows in set (0.00 sec)
13.3 左外连接
select 字段 from 表1 left join 表2 on 表1.字段=表2.字段 where 条件表达式
表1的数据全部显示，而表2的数据是满足条件的才会显示
mysql> select *from a left join b on a.id=b.pid;
+----+------+------+-------+------+
| id | name | id | title | pid |
+----+------+------+-------+------+
| 1 | aa | 1 | james | 1 |
| 2 | bb | 2 | clerk | 2 |
| 4 | cc | NULL | NULL | NULL |
+----+------+------+-------+------+
3 rows in set (0.01 sec)
13.4 右外连接
select 字段 from 表1 right join 表2 on 表1.字段=表2.字段 where 条件表达式
表2的数据全部显示，而表1的数据是满足条件的才会显示
mysql> select *from a right join b on a.id=b.pid;
+------+------+----+-------+------+
| id | name | id | title | pid |
+------+------+----+-------+------+
| 1 | aa | 1 | james | 1 |
| 2 | bb | 2 | clerk | 2 |
| NULL | NULL | 3 | cat | 3 |
+------+------+----+-------+------+
3 rows in set (0.00 sec)
综合练习
#显示员工的基本信息和部门信息
select first_name,department_name,t1.department_id,salary from t_employees t1 join t_departments t2 on t1.department_id=t2.department_id;
#显示所有员工中最高薪水的员工信息和部门信息
select first_name,department_name,t1.department_id,salary from t_employees t1 join t_departments t2 on t1.department_id=t2.department_id order by salary desc limit 0,1
#显示所有员工中最高薪水的员工信息和部门信息和工作岗位
select first_name,department_name,t1.department_id,salary,t3.job_id,t3.job_title from t_employees t1 join t_departments t2 on t1.department_id=t2.department_id join t_jobs t3 on t1.job_id=t3.job_id order by t1.salary desc limit 0, 1;
#显示所有员工中最高薪水的员工信息和部门信息和工作岗位和国家
select first_name,department_name,salary,t3.job_title,t5.country_name from t_employees t1 join t_departments t2 on t1.department_id=t2.department_id join t_jobs t3 on t1.job_id=t3.job_id join t_locations t4 on t2.location_id=t4.location_id join t_countries t5 on t4.country_id=t5.country_id order by t1.salary desc limit 0,1;
14、子查询
一个查询的结果是另外一个查询的条件，这也叫嵌套查询，内部的查询我们称为子查询，注意子查询需要用括号
mysql> select * from t_employees where salary = (select max(salary) from t_employees);
#找出中国的员工
select location_id from t_locations where country_id='cn';
select department_id from t_departments where location_id=(select location_id from t_locations where country_id='cn');
select * from t_employees where department_id=(select department_id from t_departments where location_id=(select location_id from t_locations where country_id='cn'))
MySQL事务
15、事务
transaction事务
在实际的业务中，完成某个功能可能需要多条SQL语句共同参入。如果其中某条出现了问题，怎么办？
张三–>500–>李四
update account set money=money-500 where name=‘张三’;
update account set money=money+500 where name=‘李四’;
事务：将业务当做一个执行的整体，所有的SQL语句都必须成功执行，如果其中某条SQL出现问题，所有的SQL语句都可以
回滚，整个业务就算失败，如果都没有问题，我们可以提交事务，则业务算执行成功！
通过转账案例：
create table account(
id int primary key auto_increment,
name varchar(200),
balance double
);
insert into account values(null, '张三', 1000);
insert into account values(null, '李四', 1000);
update account set balance=balance-500 where name='张三';
update account set balance=balance+500 where name='李四';
15.1 事务的基本使用
MySQL中可以有两个事务的使用方式：
1、手动提交事务
2、自动提交事务
- 手动提交事务
- 开启事务：start transaction;
- 提交事务：commit;
- 回滚事务：rollback
- 手动提交事务的过程：
1） 执行成功 : 开启事务–》执行N条件SQL–》提交事务
2） 执行失败 : 开启事务–》执行N条件SQL–》回滚事务
#在事务中实现转账过程
# 1.开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set balance=balance-500 where name='张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update account set balance=balance+500 where name='李四';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | 张三 | 500 |
| 2 | 李四 | 1500 |
+----+------+---------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
自动事务提交：MYSQL中默认每条(DML语句)都是一个单独事务，每条SQL语句都会自动开启一个事务，语句执行完毕自动提交事务，MYSQL默认开启自动提交事务！
关闭自动提交
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
##@@autocommit是mysql中记录自动提交的状态，1表示开启了自动提交，0,表示关闭了自动提交事务
mysql> set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
注意：一旦关闭了自动提交，则今后每条DML语句执行结束后都需要手动提交事务
15.2 事务的原理
1、客户端连接数据库服务器之后，服务器会自动创建一个日志临时文件
2、开始事务后，所有的DML操作都是写入到临时日志文件了
3、所有的查询操作，也会将结果写到日志文件，然后再返回给客户端
4、如果提交事务则将日志数据同步到数据库，否则清空日志文件
16、事务的隔离级别
16.1 事务的四大特性ACID
| 事务特性 | 说明 |
|---|---|
| 原子性（Atomicity） | 每个事务都是一个整理，不在拆分 |
| 一致性(Consistency) | 事务在执行前和执行的数据状态保持一致 |
| 隔离性(Isolation) | 事务之间不应该有任何相互影响 |
| 持久性(Durability) | 事务执行结束后，对数据库的修改就是持久的 |
16.2 事务的隔离级别
因为事务具有隔离性，就是多个事务之间是互不影响。因为并发操作，多个用户存在访问同一数据的出现并发问题
| 并发问题 | 说明 |
|---|---|
| 脏读 | 在一个事务中读取了另外一个事务还没结束的数据 |
| 不可重复读 | 在一个事务中两次读取的数据内容不一样，主要是因为update操作引起的。 |
| 幻读 | 在一个事务中两次读取数据的数量不一致。主要是因为insert和delete操作引起的。 |
在MySQL中提供了四种隔离级别
从低到高的隔离级别分别是
| 级别 | 名称 | 脏读 | 不可重复读 | 幻读 | 数据库默认 | |
|---|---|---|---|---|---|---|
| 1 | 读未提交 | read uncommitted | 是 | 是 | 是 | |
| 2 | 读已提交 | read committed | 否 | 是 | 是 | Oracle默认的隔离级别 |
| 3 | 可重复读 | repeatable read | 否 | 否 | 是 | MySQL默认隔离级别 |
| 4 | 串行化 | serializable | 否 | 否 | 否 |
隔离级别越高，性能就越差，安全性就越高
#查看隔离级别：@@tx_isolation
select @@tx_isolation;
#修改隔离级别
set global transaction isolation level 级别字符串;
- 脏读问题
打开两个窗口（A,B）,来模拟两个事务的操作情况
#打开第一个窗口，并修改隔离级别
mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 sec)
在当前窗口中修改好隔离级别之后需要重新打开窗口才能看到！
解决脏读的办法：提高隔离级别
set global transaction isolation level read committed;
需要退出重新登录才能看到最新的隔离级别
- 不可重复读
将数据恢复现场之后，开启A窗口，设置数据库的隔离级别为read committed
set global transaction isolation level read committed
#退出mysql，并重新进入，隔离级别才会生效
在B窗口中，同一事务内两次读取数据不一致，这个主要是因为read committed不能解决不可重复读问题！
解决不可重复读问题：提高隔离级别，提高为repeatable read
mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
- 幻读问题
mysql中无法直接看出幻读现象，但是我们可以通过提高隔离级别，解决幻读问题
mysql> set global transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)
总结：使用Serializable隔离级别，一个事务没有结束，其他事务的SQL执行不了，可以解决幻读问题！
DDL、DML、DQL、DCL
17、DCL(Data Controller Language)
- DDL:create、drop、alter
- DML:insert、update、delete
- DQL：select、show
- DCL:grant、revoke
DCL:主要用来进行权限的分配，授予某个用户拥有某些权限，我们现在都是root用户身份，root拥有最高权限。
17.1 创建新用户
create user ‘用户名’@‘主机名’ identified by ‘密码’;
- 用户名：创建的用户名
- 主机名：指定用户将来可以通过哪台主机访问该服务器，如果是本机可以使用localhost,如果想让用户通过任意一台服务器进行访问可以使用’%’
- 密码：用户的登录密码，可以为空，如果为空则表示不需要密码就可以直接登录
#创建一个user1用户，密码为123,并且只能通过本机登录
create user 'user1'@'localhost' identified by '123';
A:192.168.1.222；
B:192.168.1.120;
create user 'user2'@'192.168.1.222' identified by '123';
17.2 给用户授权
grant 权限1，权限2，权限3… on 数据库.表名 to ‘用户名’@‘主机名’
- grant … on … to … :授权的关键词
- 权限：授予用户的具体权限，create、alter、select、update等等。也可以使用all
- 数据库.表名:该用户可以操作哪个库的哪张表。如果想给库中的所有表设置对应权限可以使用：
数据库.*【*.*:所有库的所有表】- ‘用户名’@‘主机名’：给哪个用户了
#给user1设置权限：创建表、查询、修改、删除、更新权限
grant create,select,update,delete,alter on db1.* to 'user1'@'localhost';
#因为user用户没有insert权限
mysql> insert into account values(null, 'aaa', 20);
ERROR 1142 (42000): INSERT command denied to user 'user1'@'localhost' for table 'account'
#将user1用户设置所有权限
grant all on *.* to ‘user1’@'localhost'
17.3 撤销权限
revoke 权限1,权限2,权限3… on 数据库.表名 from ‘用户名’@‘主机名’
- revoke:撤销对应权限命令
mysql> revoke delete on db1.* from 'user1'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> use db1;
Database changed
mysql> delete from account;
ERROR 1142 (42000): DELETE command denied to user 'user1'@'localhost' for table 'account'
查看用户权限:show grants for ‘user1’@‘localhost’;
mysql> show grants for 'user1'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, UPDATE, CREATE, ALTER ON `db1`.* TO 'user1'@'localhost' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
usage:表示登录权限
125
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
