第21天 数据库 Mysql

MySQL

数据库

概述

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库

数据库分类

关系型数据库

关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。在关系 型数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关联的表格 分类、合并、连接或选取等运算来实现数据库的管理。
简单理解：保存的实体与实体之间的关系。（用户、商品、订单）

菲关系型数据库

NoSQL，泛指非关系型的数据库。随着互联网 web2.0 网站的兴起，传统的关系数据库在应 付 web2.0 网站，特别是超大规模和高并发的 SNS 类型的 web2.0 纯动态网站已经显得力不从 心，暴露了很多难以克服的问题，而非关系型的数据库则由于其本身的特点得到了非常迅速 的发展。NoSql 数据库在特定的场景下可以发挥出难以想象的高效率和高性能，它是作为对 传统关系型数据库的一个有效的补充。
NoSQL(NoSQL=NotOnlySQL)，意即“不仅仅是 SQL”，是一项全新的数据库革命性运动， 早期就有人提出，发展至 2009 年趋势越发高涨。NoSQL 的拥护者们提倡运用非关系型的数 据存储，相对于铺天盖地的关系型数据库运用，这一概念无疑是一种全新的思维的注入。

常见数据库

• Oracle 公司 Oracle（甲骨文）数据产品，收费的大型的数据库。
• MySQL 开源的，被 Oracle 收购了，小型的数据库。5.x 版本免费，6.x 收费了
• SQLServer 微软的，收费的中型的数据库。
• DB2 IBM 公司收费的大型的数据库。
• SyBASE PowerDigener 软件（非常牛）

数据库常见操作

启动和关闭

• 启动：net start mysql
• 关闭：netstopmysql；

注意：在启动 mysql 服务后，打开 windows 任务管理器，会有一个名为 mysqld.exe 的进程运 行，所以 mysqld.exe 才是 MySQL 服务器程序。

登录和退出

在启动 MySQL 服务器后，我们需要使用管理员用户登录 MySQL 服务器，然后来对服务 器进行操作。登录 MySQL 需要使用 MySQL 的客户端程序：mysql.exe

• 登陆
  

  mysql-u root -p root-h localhost
  

• -u：后面的 root 是用户名，这里使用的是超级管理员 root
• -p：后面的 123 是密码，这是在安装 MySQL 时就已经指定的密码
• -h：后面给出的 localhost 是服务器主机名，它是可以省略的，例如：mysql

- 退出

quit 或 exit

SQL 简介

- SQL（StructuredQueryLanguage）是“结构化查询语言”，它是对关系型数据库的操作 语言。它可以应用到所有关系型数据库中，例如：MySQL、Oracle、SQLServer 等。

• SQL 标准（ANSI/ISO）有自己标准，这些标准就与 JDK 的版本一样，在新的版本中总要 有一些语法的变化。不同时期的数据库对不同标准做了实现。
• 虽然 SQL 可以用在所有关系型数据库中，但很多数据库还都有标准之后的一些语法，我 们可以称之为”方言”。

例如 MySQL 中的 LIMIT 语句就是 MySQL 独有的方言，其它数据库都不支持！当然， Oracle 或 SQLServer 都有自己的”方言”。

SQL 语言分类

• DDL（DataDefinitionLanguage）：数据定义语言，用来定义数据库对象：库、表、列等；
• DML（DataManipulationLanguage）：数据操作语言，用来定义数据库记录（数据）；
• DCL（DataControlLanguage）：数据控制语言，用来定义访问权限和安全级别；
• DQL（DataQueryLanguage）：数据查询语言，用来查询记录（数据）。

数据库和表的关系

• 一个数据库的服务器中有多个数据库
• 一个数据库中有多张表，每个表有多个字段，字段和 Java 中类的属性是一一对应的。
• 表中每一条记录对应的是一个 Java 实例对象

数据库的基本操作

创建和查看数据库

创建数据库

• 创建一个数据库语法

  CREATEDATABASE 数据库名称;

• 创建一个使用 utf8 字符集的 mydb1 数据库

  CREATEDATABASEmydb1CHARACTERSET’UTF8’;

- 案例演示

创建一个名称为employee的数据库
创建一个 创建一个使用 utf-8 编码字符集的employee2 数据库
默认字符集

指定字符集

查看数据库

• 查询所有数据库

  语法：SHOWDATABASES;

• 查询数据库的定义

  语法：SHOWCREATEDATABASE 数据库名称;

- 案例演示

查询所有数据库
查看数据库信息

修改数据库

• 语法：ALTERDATABASE 数据库名称 CHARACTERSET 编码;

• 案例演示 ：修改数据库编码为 GBK，并查看

• 

删除数据库

删除数据库是将数据库系统中已经存在的数据库删除。

语法 DROP DATABASE数据库名称

• 案例演示：删除数据库
• 

切换数据库

• use 数据库名
• 

数据类型

使用 MySQL 数据库存储数据时，不同的数据类型决定了 MySQL 存储数据方式的不同。为 此， MySQL 数据库提供了多种数据类型，其中包括整数类型、浮点数类型、定点数类型、 日期和时间类型、字符串类型和二进制类型。

数据类型介绍

• int：整型

• double：浮点型，例如 double(5,2)表示最多 5 位，其中必须有 2 位小数，即最大值为 999.99

• decimal：泛型型，在表单钱方面使用该类型，因为不会出现精度缺失问题

• char：固定长度字符串类型  varchar：可变长度字符串类型

• text：字符串类型  blob：字节类型

• date：日期类型，格式为：yyyy-MM-dd

• time：时间类型，格式为：hh:mm:ss

• timestamp：时间戳类型

表的基本操作

数据库创建成功后，就需要创建数据表。所谓创建数据表指的是在已存在的数据库中建立新 表，
注意：在操作数据表之前，应该使用“USE 数据库名”指定操作是在哪个数据库中进行，否 则会抛出“ Nodatabaseselected”错误。

创建表

-语法：
create table 表名(
字段名称 类型(长度) 约束,
字段名称 类型(长度) 约束,
字段名称 类型(长度) 约束,
….
);

- 注意事项

表名小括号，后面有分号结束。
每一行字段后面要有逗号分隔，但是最后一行没有逗号。
数据的类型后面有长度，如果是字符型，长度必须加。如果是其他类型，可以不加。 有默认长度。int 默认长度 11

案例演示

-- 新建Student 表，包含列学号(主键)，姓名，学号，语文成绩，数学成绩，英语成绩
CREATE TABLE Student(
    sid VARCHAR(10) PRIMARY KEY,
    sname VARCHAR(20),
    sage INT,
    cscore INT,
    mscore INT,
    escore INT
);
![](https://i.imgur.com/2YU5Bbt.png)

查看表

查看所有表

语法：SHOWTABLES;

查看指定表

使用 SQL 创建好数据表后，可以通过查看数据表结构的定义，以确认数据表的定义是否正确。

• SHOW CREATE TABLE 表名;
  
  • -

该语句可以查看数据库表的定义信息，还可以查看表的字符集。

• DESCRIBE 表名
  

  该语句可以查看表的字段名、字段类型等信息。
  简化写法为：DESC 表名;

修改表

有时候，希望对表中的某些信息进行修改，这时就需要修改数据表。所谓修改数据表指的是 修改数据库中已经存在的数据表结构。 比如，修改表名、修改字段名、修改字段的数据类型等。在 MySQL 中，修改数据表的操作 都是使用 ALTERTABLE 语句来完成的。

修改表名

• 语法 RENAME TABLE 旧表名 TO 新表名;

修改字段名

• 语法：ALTER TABLE 表名 CHANGE 旧字段 新字段 类型(长度) 约束;

修改字段类型

• 语法：ALTER TABLE 表名 MODIFY 字段名 数据类型;

添加和删除字段

• 添加字段 ALTER TABLE 表名 ADD 字段 类型(长度) 约束;

• 删除字段 ALTER TABLE 表名 DROP 字段;

删除表

删除数据表是指删除数据库中已存在的表。在删除数据表的同时，数据表中存储的数据都将 被删除。
注意：创建数据表时，表和表之间可能会存在关联，要删除这些被其他表关联的表比较复杂，将在之后学习
语法 DROP TABLE 表名;

表的约束

为了防止数据表中插入错误的数据，在 MySQL 中，定义了一些维护数据完整性的规则，即 表的约束

• 注意：
  

  主键列的值不能为 NULL，也不能重复！
  指定主键约束使用 PRIMARYKEY 关键字

主键约束

在 MySQL 中，为了快速查找表中的某条信息，可以通过设置主键来实现。主键约是通过 PRIMARYKEY 定义的，它可以唯一标识表中的记录，这就好比身份证可以用标识人的身份一 样。 语法：字段名 数据类型 PRIMARY KEY; id INT PRIMARY KEY;

非空约束

指定非空约束的列不能没有值，也就是说在插入记录时，对添加了非空约束的列一定要给值； 在修改记录时，不能把非空列的值设置为 NULL。

- 语法：字段名 数据类型 NOTNULL; name varchar(10) not null

设置为非空后，列必须有值

唯一约束

当为字段指定唯一约束后，那么字段的值必须是唯一的。这一点与主键相似！例如给 stu 表 的 sname 字段指定唯一约束： 语法：字段名 数据类型 UNIQUE name varchar(20) unique

完整代码示例

CREATE TABLE Student( 
    id INT,
        NAME VARCHAR(20), 
    age INT, 
    scode INT, 
    mScore INT
)

设置表字段的值自动增加

在数据库表中,若想为表中插入的新记录自动生成唯一的 ID，可以使用 AUTO_INCREMENT 约束来实现，AUTO_INCREMENT 约束的字段可以是任何整数类型，默认情况下，该字段的值 是从 1 开始自增的

• 语法：字段名 数据类型 AUTO_INCREMENT;

数据库增删改查

想要操作数据表中的数据，首先要保证数据表中存在数据，MySQL 使用 INSERT 语句想数据 表中添加数据，并且根据添加方式的不同分为三种，分别是为表中的所有字段添加数据，为 表中指定字段添加数据、同时添加多条记录。

INSERT 插入语句

• 语法 1 :

INSERT INTO 表名(列名 1,列名 2, …)VALUES(值 1, 值 2)
注意：列和值必须匹配
示例 INSERT INTO stu(id,name,age,idcard) VALUES (1,’lisi’,18,1111111111);

• 语法 2：

INSERT INTO 表名 VALUES(值 1,值 2,…)
注意：因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值：
示例 INSERT INTO stu VALUES(2,’zhaoliu’,32,2222222222222222222);
注意： 数据与字段类型要相同 字段长度需要控制 所有字符串数据必须使用单引用

SELECT 查询语句

语法：

查询所有数据：SELECT*FROM 表名;
【*】通配符表示表中所有字段

查询指定字段：SELECT 字段名 1,字段名 2,字段名 3FROM 表名;
字段 1、字段 2、字段 3 表示从表中查询的指定字段

去除重复的数据：SELECTDISTINCT 字段名 FROM 表名;
DISTINCT 是可选参数，用于剔除查询结果中重复的数据

附上全部代码

-- 新建Student 表，包含列学号(主键)，姓名，年龄，语文成绩，数学成绩，英语成绩
CREATE TABLE Student(
    sid VARCHAR(10) PRIMARY KEY,
    sname VARCHAR(20),
    sage INT,
    cscore INT,
    mscore INT,
    escore INT
);
DROP TABLE Student;
-- 新建Teacher 表，包含列工号(主键)，姓名，年龄
CREATE TABLE Teacher(
    tid VARCHAR(10) PRIMARY KEY,
    tname VARCHAR(20),
    tage INT
);
-- 查看所有表
SHOW TABLES;
-- 该语句可以查看数据库表的定义信息，还可以查看表的字符集
SHOW CREATE TABLE Student;
--  该语句可以查看表的字段名、字段类型等信息
DESCRIBE Student;

-- 修改表名 RENAMETABLE 旧表名 TO 新表名;
RENAME TABLE Teacher TO Worker;

-- 修改字段名 语法：ALTER TABLE 表名 MODIFY 字段名 数据类型;
ALTER TABLE worker MODIFY tage INT;

-- 添加字段 ALTER TABLE 表名 ADD 字段 类型(长度) 约束;
ALTER TABLE worker ADD tsalary DOUBLE;

-- 删除字段 ALTER TABLE 表名 DROP 字段;
ALTER TABLE worker DROP tsalary;

-- 删除表 DROP TABLE 表名;
DROP TABLE worker;

-- 给创建 person 表，给表的 id 字段设置主键约束， name 字段非空约束， idcard 字段唯一约束。 
CREATE TABLE person(
    pid INT PRIMARY KEY,
    pname VARCHAR(20) NOT NULL,
    pidcard INT UNIQUE
);
DROP TABLE person;
-- 设置表字段的值自动增加 语法：字段名 数据类型 AUTO_INCREMENT;
CREATE TABLE person(
    pid INT PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(20) NOT NULL,
    pidcard INT UNIQUE
);

-- INSERT 插入语句 
-- 语法 1 INSERTINTO 表名(列名 1,列名 2, …)VALUES(值 1, 值 2) 
INSERT INTO person(pid,pname,pidcard) VALUES(1,'zhangsan',1);
INSERT INTO person(pid,pname,pidcard) VALUES(2,'lisi',2);

-- 语法 2： INSERTINTO 表名 VALUES(值 1,值 2,…) 
INSERT INTO person VALUES(3,'wangwu',3);

-- 查询所有数据：SELECT*FROM 表名; 
SELECT * FROM person;

-- 查询指定字段：SELECT 字段名 1,字段名 2,字段名 3FROM 表名; 
SELECT pid FROM person;

-- 去除重复的数据：`SELECT DISTINCT 字段名 FROM 表名;
SELECT DISTINCT pid FROM person;

-- UPDATE 更新语句 UPDATE 表名称 SET 列名称 1= 新值 1, 列名称 2= 新值 2, ... WHERE 列名称 = 某值(旧值，条件值)
UPDATE person SET pname = 'haha' WHERE pid = 1;
UPDATE person SET pname = 'haha' WHERE pid < 3;

-- DELETE 删除语句 DELETE FROM 表名 WHERE 条件; 
DELETE FROM person WHERE pid = 1;

-- 删除全部数据方式一  DELETEFROMperson; 
DELETE FROM person; 

-- 删除所有记录方式二  `TRUNCATE TABLE 表名 `  `TRUNCATE TABLE student;`
TRUNCATE TABLE person;

CREATE TABLE Student( 
    id INT,
        NAME VARCHAR(20), 
    age INT, 
    scode INT, 
    mScore INT
)
INSERT INTO Student VALUES(1,'李四',25,001,59.5); 
INSERT INTO Student VALUES(2,'王五',26,002,75); 
INSERT INTO Student VALUES(3,'赵六',25,003,80); 
INSERT INTO Student VALUES(3,'赵六',25,003,80); 
INSERT INTO Student VALUES(4,'铁铲',50,NULL,50); 
INSERT INTO Student VALUES(5,'weidong',50,NULL,50); 
INSERT INTO Student VALUES(6,'yangguo',50,NULL,50); 
INSERT INTO Student VALUES(9,'zhaoliu',50,NULL,50); 
INSERT INTO Student VALUES(9,'da%niu',50,NULL,50);

-- 查询表中所有数据
SELECT * FROM Student;

-- 指定字段：SELECT 字段名 1,字段名 2FROM student; 
SELECT NAME,age  FROM Student;

-- 条件查询（必会）
-- 语法：SELECT*FROM 表名 WHERE 条件表达式; 
SELECT * FROM Student WHERE age = 25;
SELECT * FROM Student WHERE age > 25;
SELECT * FROM Student WHERE NAME = '赵六';

-- IN 关键字查询 语法 SELECT * FROM 表名 WHERE 字段名 IN(元素 1，元素 2)
SELECT * FROM Student WHERE id IN(1,2,3,4);
SELECT * FROM Student WHERE NAME IN('李四','赵六');

-- BETWEEN...AND...  语法 SELECT 字段名 FROM 表名 WHERE 字段名 BETWEEN 值 1AND 值 2;  
-- 值 1 表示范围的起始值  
-- 值 2 表示范围的结束值
SELECT * FROM Student WHERE id BETWEEN 2 AND 2;
SELECT * FROM Student WHERE id NOT BETWEEN 2 AND 2; -- 使用not表示不再这个范围内

-- 空值查询 使用 ISNULL 关键字来判断字段的值是否为空值
-- 语法 SELECT*FROM 表名 WHERE 字段名 IS[NOT]NULL;
SELECT * FROM Student WHERE scode IS NULL;
SELECT * FROM Student WHERE scode IS NOT NULL;


-- 带 DISTINCT 关键字的查询 语法： SELECT DISTINCT 列名称 FROM 表名称 //只列出不重复的元素
SELECT DISTINCT * FROM Student;


-- 带 LIKE 关键字查询 语法 SELECT* FROM 表名 WHERE 字段名 [NOT]LIKE'匹配字符串';  
-- NOT 表示查询与指定字符串不匹配的记录 
-- 百分号（%）通配符 匹配任意长度的字符串，包括空字符串，
SELECT * FROM Student WHERE NAME LIKE 'w%';
SELECT * FROM Student WHERE NAME LIKE '%a%';
-- 下划线通配符值匹配单个字符串，如果需要匹配多个，那么需要多个下划线
SELECT* FROM student WHERE NAME LIKE 'we__ong';

-- 查询带百分号的字段`aa%bb`
SELECT * FROM student WHERE NAME LIKE'%\%%';

-- AND 关键字多条件查询 
-- 语法 SELECT* 字段 1、字段 2 FROM 表名 WHERE 条件表达式 1 AND 条件表达式 2...
SELECT age ,NAME FROM Student WHERE id>3 AND age>25;

-- 带 OR 关键字的多条件查询
--  语法 SELECT* 字段名 1、字段 2 FROM 表名 WHERE 条件表达式 1 OR 条件表达式 2;
SELECT NAME FROM Student WHERE id<3 OR age>26;

-- COUNT 用来统计记录的条数  语法 SELECT COUNT(*) FROM 表名;
SELECT COUNT(*) FROM Student;
-- 统计成绩大于 60 的学生有多少个？ 
SELECT COUNT(*) AS '人数' FROM student WHERE mScore>60;
--  SUM 求和  语法 SELECT SUM(字段名)FROM 表名;
SELECT SUM(age) FROM Student;
-- 统计一个班的平均年龄
SELECT SUM(age)/COUNT(*) FROM Student;

-- AVG 求出某个字段所有值的平均值  语法 SELECT AVG(字段名) FROM student;
SELECT AVG(age) FROM Student;

--  MAX 最大值 语法 SELECT MAX(字段名) FROM student;
SELECT MAX(age) FROM Student;

--  MIN 最小值 语法 SELECT MIN(字段名) FROM student;
SELECT MIN(age) FROM Student;

-- 排序查询 语法 SELECT 字段名 1 FROM 表明 ORDER BY 字段名 1;  默认的排序是升序的
SELECT NAME FROM Student ORDER BY mScore;
SELECT * FROM Student ORDER BY mScore ASC;-- ASC 升序排列
SELECT * FROM Student ORDER BY mScore DESC;-- DESC 降序排列


-- 分组操作 语法 SELECT 字段名 1,字段名 2 FROM 表名 GROUPBY 字段名 1,字段名 2;  
-- 注意：该字段显示分组的第一条数据
SELECT*FROM student GROUP BY age; -- 只显示某一字段的值不同的数据

-- 查询班级各个年龄段学生个数
SELECT age,COUNT(*) AS '人数'FROM student GROUP BY age;

-- GROUPBY 和 HAVING 关键字一起使用
-- HAVING 关键字和 WHERE 关键字作用相同，区别在于 HAVING 关键字后可以跟聚合函数， WHERE 关键字不能
-- 将 student 表按照 mscore 字段进行分组查询，查询出 mscore 字段值之和小于 150 的分组 
SELECT SUM(mscore),age FROM student GROUP BY mScore HAVING SUM(mscore)<150;

-- LIMIT 限制查询结果的数量 语法 SELECT 字段名 FROM 表名 LIMIT 记录数;
SELECT * FROM Student LIMIT 4;
SELECT * FROM Student LIMIT 4,5;