Java_14_MySQL数据库第1天：数据库安装与使用、SQL语句（DDL、DML、DCL、DQL）、数据库约束

admin管理员组

文章数量:1547960

摘要

Java_14_MySQL数据库第1天：
数据库（数据库分类）
数据库安装与使用、
SQL语句（作用、分类、通用语法）
DDL数据定义语言【（操作数据库：使用，创建，查看，修改字符集，删除）（操作表：创建表，查看所有表/表结构/建表语句，创建同结构表，删除表，修改表结构）】
DML数据操作语言（插入记录、更新记录、删除记录）
DCL数据控制语言（）
DQL数据查询语言【（简单查询：所有列，指定列，别名，清除重复值，结果参与运算）（条件查询：比较，逻辑，in关键字，范围，like关键字，排序，聚合函数，IFNULL函数，分组，LIMIT关键字）】
数据库约束（主键约束、唯一约束、非空约束、外键约束）

一、数据库

1、数据的存储方式

1、数据保存在内存（程序运行时临时保存）

int[] arr = new int[]{1, 2, 3, 4};
ArrayList<Integer>list = new ArrayList<Integer>(); 
list.add(1);

new出来的对象存储在堆中堆是内存中的一小块空间
优点：内存速度快
缺点：断电/程序退出，数据就清除了内存价格贵

2、数据保存在普通文件
优点：永久保存
缺点：查找,增加,修改,删除数据比较麻烦,效率低

3、数据保存在数据库
优点：永久保存，通过SQL语句比较方便的操作数据库

2、什么是数据库

存储数据的仓库. 其本质是一个文件系统，数据库按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。

理解：数据库一个用来存储数据的仓库

3、数据库的优点（为什么要使用数据库）

数据库是按照特定的格式将数据存储在文件中，通过SQL语句可以方便便的对大量数据进行增、删、改、查操作。数据库是对大量的信息进行管理的高效的解决方案

理解：方便对数据进行增删改查操作

4、数据库的分类

关系型数据库

存储数据时需要有二维表：比如MySQL、Oracle

非关系型数据库

没有表的概念，以键值对方式存储到内存中或文档中：比如：Redis，MongoDB

5、常见数据库

MYSQL：开源免费的数据库,小型的数据库.已经被Oracle收购了.MySQL6.x版本也开始收费。
Oracle：收费的大型数据库, Oracle公司的产品。Oracle收购SUN公司,收购MYSQL
DB2：IBM公司的数据库产品,收费的。常应用在银行系统中
SQLServer：MicroSoft公司收费的中型的数据库。C#、等语言常使用。
SyBase：已经淡出历史舞台。提供了一个非常专业数据建模的工具PowerDesigner（PowerDesigner也可以用来设计MySql表结构）
sQLite：嵌入式的小型数据库，应用在手机端。

常用数据库：MYSQL, Oracle
在web应用中使用的最多的就是MysQL数据库，原因如下:
1、开源、免费
2、功能足够强大，足以应付web应用开发(最高支持千万级别的并发访问)

二、数据库的安装与使用

1、安装

1、打开下载的mysq安装文件双击解压缩,运行"mysq-5.5.40-win32.msi"

2、选择安装类型,有"Typical (默认) "、"Complete (完全) "、"Custom (用户自定义) “三个选项,选择"Custom”,按"next"键继续。

3、点选"Browse",手动指定安装目录。

4、填上安装目录,我的是"d\Program Files (x86)MysQLMysQL Server 5.0",按"OK"继续。

5、确认一下先前的设置,如果有误,按"Back"返回重做。按"install"开始安装。

6、正在安装中,请稍候,直到出现下面的界面,则完成MYSQL的安装

数据库安装好了还需要对数据库进行配置才能使用MYSQL的配置

配置（安装完成需要配置）

1、安装完成了,出现如下界面将进入mysq配置向导。

2、选择配置方式, "Detailed Configuration (手动精确配置) “、“Standard Configuration (标准配置)”
我们选择"Detailed Configuration”, 方便熟悉配置过程。

3、选择服务器类型, "Developer Machine (开发测试类, mysql占用很少资源) "、 "ServerMachine (服务器类型, mysql占用较多资源) "、“Dedicated MySQL Server Machine (专门的数据库服务器, mysql占用所有可用资源)
我们选择"Developer Machine”

4、选择mysq数据库的大致用途, "Multifunctional Database (通用多功能型,好) "、"Transactional Database Only (服务器类型,专注于事务处理,一般) "、“NonTransactional Database Only (非事务处理型,较简单,主要做一些监控、记数用,对MyISAM数据类型的支持仅限于non-transactional)
我们选择"Multifunctional Database”，按"Next"继续。

5、选择网站并发连接数,同时连接的数目, "Decision Support(DSS)/OLAP (20个左右) "、"OnlineTransaction Processing(OLTP) (500个左右) “、“Manual Setting (手动设置,自己输一个数)”
我们选择"Manual Setting”，选20，

6、是否启用TCP/IP连接,设定端口,如果不启用,就只能在自己的机器上访问mysq数据库了,在这个页面上,您还可以选择"启用标准模式" (Enable Strict Mode) ,这样MySQL就不会允许细小的语法错误。如果是新手,建议您取消标准模式以减少麻烦。但熟悉MySQL以后,尽量使用标准模式,因为它可以降低有害数据进入数据库的可能性。
我们勾选启用TCP/IP连接、勾选"启用标准模式"、按"Next"继续

7、就是对mysql默认数据库语言编码进行设置(重要) ,一般选UTF-8,按"Next"继续。

8、选择是否将mysql安装为windows服务,还可以指定Service Name (服务标识名称) ,是否将mysql的bin目录加入到Windows PATH (加入后,就可以直接使用bin下的文件,而不用指出目录名,比如连接, "mysql.exe-uusername-ppassword;"就可以了,不用指出mysql.exe的完整地止,很方便)
我们这里全部打上了勾, Service Name不变。按"Next"继续。

9、询问是否要修改默认root用户(超级管理)的密码。"Enable root access from remotemachines (是否允许root用户在其它的机器上登陆,如果要安全,就不要勾上,如果要方便,就勾上它) “。最后"Create An Anonymous Account (新建一个匿名用户,匿名用户可以连接数据库,不能操作数据,包括查询) “,一般就不用勾了
我们勾选"Modify Security Settings”，设置账号密码都为root，方便记忆。不勾选"Enable root access from remotemachines”。按"Next"继续。

10、确认设置无误,按"Execute"使设置生效,即完成MYSQL的安装和配置。

注意：设置完毕,按"Finish"后有一个比较常见的错误,就是不能"Start service",一般出现在以前有安装mysql的服务器上,解决的办法,先保证以前安装的mysq服务器彻底卸载掉了;不行的话,检查是否按上面一步所说,之前的密码是否有修改,照上面的操作;如果依然不行,将mysql安装目录下的data文件夹备份,然后删除,在安装完成后,将安装生成的data文件夹删除,备份的data文件夹移回来,再重启mysq服务就可以了,这种情况下,可能需要将数据库检查一下,然后修复一次,防止数据出错。
解决方法：卸载MySQL，重装MySQL

2、数据库的卸载

1、停止window的MySQL服务。找到"控制面板"> “管理工具”>“服务”,停止MysQL后台服务。

2、卸载MySQL安装程序。找到"控制面板"> “程序和功能”,卸载MySQL程序

3、删除MySQL安装目录下的所有文件。

4、删除c盘ProgramDate目录中关于MySQL的目录。路径为: C:ProgramDatalMysQL是隐藏文件,需要显示出来)

3、数据库的启动

MySQL启动方式和普通的windows程序双击启动方式不同,分为以下2种:
1、通过Windows服务方式启动
右键我的电脑->管理->服务和应用程序->服务，找到MySql，启动MySQL服务

2、通过DOS命令方式启动
管理员身份运行dos窗口
启动MYSQL：net start mysql
停止MYSQL：net stop mysql

4、MySql的使用

通过控制台连接MySql数据库

MysQL是一个需要账户名密码登录的数据库,登陆后使用,它提供了一个默认的rot账号,使用安装时设置的密码即可登录
1、登录格式1：mysql-u户名 -p密码
例如: mysql -uroot -proot

2、登录格式2：mysql -hip地址 -u用户名 -p密码
例如: mysql -h127.0.0.1 -uroot -proot

3、登录格式3: mysql --host=ip地址 --user=用户名 --password=密码
例如: mysqlhost=localhost --user=root --password=root

4、退出MySQL：quit或exit

通过SQLyog图形化工具连接MySql数据库

SQLyog是业界著名的webyog公司出品的一款简洁高效、功能强大的图形化MysQL数据库管理工具。使用SQLyog可以快速直观地让您从世界的任何角落通过网络来维护远端的MySQL数据库

SQLyog图形化工具安装（网上找破解版）

SQLyog连接MySQL

5、MySQL安装目录的结构解析

│-- bin：存储MySQL所有的可执行文件(重要)
   │-- MySQLInstanceConfig.exe  启动配置向导文件
│-- data: 存储MySQL安装之后自带数据库（不要删除）
│-- include: 存储C语言相关头文件
│-- lib: 存储C++相关的动态链接库
│-- my.ini MySQL重要配置文件(重要)

6、数据库管理系统

数据库管理系统(DataBase Management System, *DBMS) :指一种操作和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制,以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据

SQLyog内置有数据库管理系统

数据库管理系统、数据库和表的关系

数据库管理程序(DBMS)可以管理多个数据库,一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据,一般会在数据库创建多个表,以保存程序中实体的数据。数据库管理系统、数据库和表的关系如图所示:

先有数据库→再有表→再有数据，一个数据库包含多个表。
图例2

7、实体类与表的对应关系

实体类的成员变量，对应数据库表中的字段名（表顶头一行）

图例1

图例2

三、SQL语句

结构化查询语言(Structured Query Language)简称SQL，SQL语句就是对数据库进行操作的一种语言。

理解：SQL语句就是用来对数据库执行增删改查操作

面试题：SQL全称是什么？
结构化查询语句structured Query Language

SQL作用

通过SQL语句我们可以方便的操作数据库中的数据、表、数据库。

SQL是数据库管理系统都需要遵循的规范。不同的数据库生产厂商都支持SQL语句,但都有特有内容。

SQL语句分类

1、DDL(Data Definition Language) 数据定义语言

用来定义数据库对象:数据库,表,列等。关键字: create, drop,alter等

2、DML(Data Manipulation Language) 数据操作语言

用来对数据库中表的数据进行增删改。关键字: insert, delete, update等

3、DCL(Data Control Language) 数据控制语言(了解)

用来定义数据库的访问权限和安全级别,及创建用户。关键字: GRANT, REVOKE等

4、DQL(Data Query Language) 数据查询语言

DQL语言并不是属于MYSQL官方的分类,但是对数据库的操作最多就是查询,所以我们的程序员把查询语句的语句称作为DQL语言

SQL通用语法

1、SQL语句可以单行或多行书写,以分号结尾。

2、可使用空格和缩进来增强语句的可读性。

3、MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。如

SELECT * FROM student; 

4、3种注释
单行注释：

--注释内容 
或 
#注释内容(mysq特有)

多行注释：

/*注释*/

四、DDL语句（数据定义语言语句）

DDL操作数据库

1、创建数据库

1、直接创建数据库

CREATE DATABASE 数据库名;

2、判断是否存在并创建数据库

CREATE DATABASE IF NOT EXISTS 数据库名;

3、创建数据库并指定字符集(编码表)

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

代码示例

直接创建数据库

CREATE DATABASE db1; 

判断是否存在并创建数据库

CREATE DATABASE IF NOT EXISTS db2; 

创建数据库并指定字符集(编码表)

CREATE DATABASE db2 CHARACTER SET gbk; 

2、查看数据库

查看所有的数据库

SHOW databases;

查看某个数据库的定义信息（创建数据库的信息）

SHOW CREATE DATABASE 数据库名;

代码示例

查看某个数据库的定义信息（创建数据库的信息）

SHOW CREATE DATABASE test;

3、修改数据库字符集

修改数据库字符集格式

ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;

ALTER ：改变，更改

代码示例

将db3数据库的字符集改成utf8

ALTER DATABASE db3 DEFAULT CHARACTER SET utf8; 

4、删除数据库

DROP DATABASE 数据库名;

DROP：在数据库可理解为"删除"
drop 一般用于删除整体性数据 如表，模式，索引，视图，完整性限制等
delete 用于删除局部性数据 如表中的某一元组

代码示例

删除db2数据库

DROP DATABASE db2; 

5、使用数据库

1、查看E在据库

SELECT DATABASE ();

2、使用/切换数据库

USE 数据库名;

代码示例

查看正在使用的数据库

SELECT DATABASE(); 

使用db1数据库

USE db1; 

MySQL表字段常用数据类型（建表使用）

int 整型
varchar 字符串类型
date 日期类型：yyyy-MM-dd
double 小数

四个大类型下细分的数据类型（了解）
MYSQL中数据类型介绍：https://wwwblogs/-xlp/p/8617760.html

DDL操作表

操作表前提先使用某个数据库

1、创建表

CREATE TABLE 表名 (字段名1 字段类型1, 字段名2 字段类型2…);

# 建议写成如下格式:
CREATE TABLE 表名 (
	字段名1 字段类型1,
	字段名2 字段类型2
);

CREATE – 表示创建
TABLE – 表示创建一张表

代码示例

创建student表包含id，name，birthday字段

CREATE TABLE student (
	id INT,
	name VARCHAR(20),
	birthday DATE
);

2、查看表

1、查看某个数据库中的所有表

SHOW TABLES;

2、查看表结构（字段、字段数据类型、主键、默认值等信息）

DESC 表名;

3、查看创建表的SQL语句

SHOW CREATE TABLE 表名;

代码示例

查看mysql数据库中的所有表

SHOW TABLES; 

查看student表的结构（字段、字段数据类型、主键、默认值等信息）

DESC student; 

查看student的创建表SQL语句

SHOW CREATE TABLE student; 

3、快速创建一个表结构相同的表

只复制结构，不复制数据（即字段名、字段数据类型等相同，不含表存储数据）。关键字：LIKE

CREATE TABLE 新表名 LIKE 旧表名;

代码示例

创建s1表，s1表结构和student表结构相同（即字段名、字段数据类型等相同，不含表存储数据）

CREATE TABLE s1 LIKE student; 

4、删除表

1、直接删除表

DROP TABLE 表名;

2、判断表是否存在并删除表

DROP TABLE IF EXISTS 表名;

当你不再需要该表时， 用 drop;
当你仍要保留该表，但要删除所有记录时， 用 truncate;
当你要删除部分记录时（always with a WHERE clause), 用 delete.

代码示例

直接删除表s1表

DROP TABLE s1; 

判断表是否存在并删除s1表

DROP TABLE IF EXISTS s1;

5、修改表结构

修改表结构使用不是很频繁，只需要了解，等需要使用的时候再回来查即可。关键字：ALTER（修改，更改）

1、添加表列

ALTER TABLE 表名 ADD 列名类型;

2、修改列类型

 ALTER TABLE 表名 MODIFY 列名 新的类型;

3、修改列名

 ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;

4、删除列

ALTER TABLE 表名 DROP 列名;

5、修改表名

RENAME TABLE 表名 TO 新表名; 

6、修改字符集

ALTER TABLE 表名 character set 字符集;

代码示例

为学生表添加一个新的字段remark，类型为varchar(20)

ALTER TABLE student ADD remark VARCHAR(20); 

将student表中的remark字段的数据类型改成varchar(100)

ALTER TABLE student MODIFY remark VARCHAR(100); 

将student表中的remark字段名改成intro，类型varchar(30)

ALTER TABLE student CHANGE remark intro varchar(30); 

删除student表中的字段intro

ALTER TABLE student DROP intro; 

将学生表student改名成student2

RENAME TABLE student TO student2; 

将sutden2表的编码修改成gbk

ALTER TABLE student2 character set gbk; 

五、DML语句（数据操作语言语句）

插入记录

1、插入全部字段的数据

关键字：INSERT（插入）

字段名全写格式

INSERT INTO 表名(字段名1,字段名2,字段名3.) VALUES (值1,值2,值3);

字段名一个不写格式

INSERT INTO 表名 VALUES (值1,值2,值3..);

2、插入部分字段的数据

INSERT INTO 表名(字段名1,字段名2, ...) VALUES (值1,值2, .); 
/*
INSERT INTO 表名 – 表示往哪张表中添加数据
(字段名1, 字段名2, …) -- 要给哪些字段设置值
VALUES (值1, 值2, …); -- 设置具体的值
*/

没有添加数据的字段，会使用NULL填充
理解：即插入时写部分字段，其他不写的字段的值自动插入null。或者，写字段名自己手动插入null

注意：
1、值与字段必须对应，个数相同，类型相同
2、值的数据大小必须在字段的长度范围内
3、除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）
4、如果要插入空值，可以不写字段，或者插入null

代码示例

插入部分数据，往学生表中添加id, name, age, sex字段的数据

INSERT INTO student (id, NAME, age, sex) VALUES (1, '张三', 20, '男'); 

向表中插入所有字段的数据

# 字段名全写插入
INSERT INTO student (NAME, id, age, sex, address) VALUES ('李四', 2, 23, '女', '广州');

# 不写字段名
INSERT INTO student VALUES (3, '王五', 18, '男', '北京'); 

3、蠕虫复制（相同的表结构复制数据）

蠕虫复制就是：在已有的数据基础之上，将原来的数据进行复制，插入到对应的表中。
前提：两张表结构必须相同（列数量相同，字段类型相同…）

语法格式：

INSERT INTO 表名1 SELECT * FROM 表名2;

作用：将 表名2 中的数据复制到 表名1 中

代码示例

创建student2表，student2结构和student表结构一样

CREATE TABLE student2 LIKE student; 

将student表中的数据添加到student2表中（全部复制）

INSERT INTO student2 SELECT * FROM student; 

将student表中的数据添加到student2表中（部分复制）
注意：如果只想复制student表中name,age字段数据到student2表中使用如下格式

INSERT INTO student2(NAME, age) SELECT NAME, age FROM student;

DOS命令窗口操作数据乱码问题的解决

当我们使用DOS命令行进行SQL语句操作时，SQL如带有中文会出现乱码，导致SQL执行失败

错误原因：

因为MySQL的客户端设置编码是utf8，而系统的DOS命令行编码是gbk，编码不一致导致的乱码

查看 MySQL 内部设置的编码

show variables like 'character%';

解决方案

修改client, connection, results的编码为GBK，保证和DOS命令行编码保持一致
1、单独设置（临时生效）

set character_set_client=gbk;
set character_set_connection=gbk;
set character_set_results=gbk;

2、快捷设置（临时生效）

set names gbk; 

3、直接修改MySQL安装目录下的my.ini文件，重启服务所有地方生效。(永久生效)
此方案将所有编码都修改了[不建议]

更新表记录

关键字：UPDATE（更新）

1、不带条件修改数据

UPDATE 表名 SET 字段名=值;

2、带条件修改数据

UPDATE 表名 SET 字段名=值 WHERE 字段名=值;

UPDATE：修改数据
SET：修改哪些字段
WHERE：指定条件

代码示例

不带条件修改数据,将所有的性别改成女

UPDATE student SET sex='女'; 

带条件修改数据，将id号为2的学生性别改成男

UPDATE student SET sex='男' WHERE id=2; 

一次修改多个列，把id为3的学生，年龄改成26岁，address改成北京

UPDATE student SET age=26, address='北京' WHERE id-3;

删除表记录

当你不再需要该表时， 用 drop;
当你仍要保留该表，但要删除所有记录时， 用 truncate;
当你要删除部分记录时（always with a WHERE clause), 用 delete.

1、不带条件删除数据

DELETE FROM 表名;

2、带条件删除数据

DELETE FROM 表名 WHERE 字段名=值;

3、truncate删除表记录

TRUNCATE TABLE 表名;

truncate和delete的区别：
1、delete是将表中的数据一条一条删除
2、truncate是将整个表摧毁，重新创建一个新的表,新的表结构和原来表结构一模一样
3、delete是数据操作语言（DML）命令；而truncate是数据定义语言（DDL）命令。
4、从效果上来看：truncate是删除整个表，然后重构整个表。delete只是删除逐条删除每一条数据。
5、从空间上来看：delete会产生碎片，并不会释放空间，而truncate不会产生碎片。
6、从事务的角度：truncate不可以回滚，delete可以回滚。

delete和truncate之间的差别有哪些：
https://www.php/sql/421588.html
truncate和delete的区别：
https://blog.csdn/qpc672456416/article/details/80738193
truncate table时存在外键约束的解决办法
https://wwwblogs/dingjiaoyang/p/9931171.html

代码示例

带条件删除数据，删除id为3的记录

DELETE FROM student WHERE id=3; 

不带条件删除数据，删除表中的所有数据

DELETE FROM student; 

六、数据库约束

数据库约束是对表中的数据进行进一步的限制,保证数据的正确性、有效性和完整性。

约束种类:

约束名	约束关键字
主键约束	primary key
唯一约束	unique
非空约束	not null
默认约束	default 默认值
外键约束	constraints foreign key(外键列名) reference 主表(主键)
检查约束	MySQL不支持，Oracle支持

七、主键约束

用来唯一标识一条记录，每个表都应该有一个主键，并且每个表只能有一个主键。

有些记录的name,age,score字段的值都一样时,那么就没法区分这些数据,造成数据库的记录不唯一,这样就不方便管理数据

哪个字段应该作为表的主键？通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键。主键是给数据库和程序使用的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复,非空就行。

1、创建主键

主键关键字：PRIMARY KEY（主要的键）

主键的特点：
1、主键必须包含唯一的值（每条记录一个主键，与其他记录主键不能重复）
2、主键列不能包含NULL值

在创建表的时候给字段添加主键

字段名 字段类型 PRIMARY KEY

在已有的表中添加主键

alter table 表名 add primary key (主键列名)

代码示例

创建表学⽣生表st5, 包含字段(id, name, age)将id做为主键

CREATE TABLE st5 (
	id INT PRIMARY KEY, -- id是主键
	NAME VARCHAR(20),
	age INT
);

创建后的主键标识如图

添加数据

INSERT INTO st5 (id, NAME) VALUES (1,'唐伯虎');
INSERT INTO st5 (id, NAME) VALUES (2,'周文宾');
INSERT INTO st5 (id, NAME) VALUES (3,'祝枝山');
INSERT INTO st5 (id, NAME) VALUES (4,'文征明');

报错
插入重复的主键值，报错：Duplicate entry ‘1’ for key ‘PRIMARY’

-- 主键是唯一的不能重复，插入重复的会报错Duplicate entry '1' for key 'PRIMARY'
INSERT INTO st5 (id, NAME) VALUES (1, '文征明2');

插入NULL的主键值，报错：Column ‘id’ cannot be null

-- 主键是不能为空的，插入null值会报错Column 'id' cannot be null
INSERT INTO st5 (id, NAME) VALUES (NULL, '文征明3');

小结

主键的作用：记录的唯一标识
主键的特点：非空且唯一

添加主键约束
通过创表时添加：字段名 数据类型 primary key;
通过修改表结构添加：alter table 表名 add primary key(主键列名)

2、主键自增

主键增长关键字：AUTO_INCREMENT（自动增加）

主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
AUTO_INCREMENT：表示自动增长(字段类型必须是整数类型)

字段名 数据类型 primary key auto_increment;

代码示例

创建学生表st6, 包含字段(id, name, age)将id做为主键并自动增长

CREATE TABLE st6 (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT
);

-- 插入数据
-- 自增主键，插入数据时不用再自己插入，主键会自动从1增长
-- 主键默认从1开始自动增长
INSERT INTO st6 (NAME, age) VALUES ('唐僧', 22);
INSERT INTO st6 (NAME, age) VALUES ('孙悟空', 26);
INSERT INTO st6 (NAME, age) VALUES ('猪八戒', 25);
INSERT INTO st6 (NAME, age) VALUES ('沙僧', 20);

修改主键自增开始值（默认1）

默认地 AUTO_INCREMENT 的开始值是1，如果希望修改起始值，使用下列SQL语法

ALTER TABLE 表名 AUTO_INCREMENT=起始值;

代码示例

# 创建学生表st2, 包含字段(id, name, age)将id做为主键并自动增长
create table st2(
	id int primary key auto_increment, 
	name varchar(20),
	age int 
);
-- 修改自增长值从1000开始
alter table st2 auto_increment = 1000;
-- 插入数据
...

DELETE和TRUNCATE删除记录时对主键自增的影响

DELETE 删除表中的数据，但不重置AUTO_INCREMENT的值。

插入记录时会接着在已删除的记录主键值继续增加

TRUNCATE 摧毁表，重建表，AUTO_INCREMENT重置为默认值1

3、零填充补充（zerofill）

什么是零填充：当指定列的值不满指定位数时,前面使用零填充补齐

零填充关键字：zerofill
使用位置：数据类型后面

零填充补充 代码示例

-- 创建学生表st3，id为整数，长4位，零填充，主键，自动增长。名字varchar(20)。
create table st3(
	id int(4) zerofill  primary key auto_increment,
	name varchar(20)
);
-- 插入数据 id为null值
insert into st3 values('jack');

-- 查询，id为null值，自增将null值改为1。1又自动填充为0001，符合int(4)。
select * from st3;  -- 0001 

4、删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

代码示例

删除st5表的主键

ALTER TABLE st5 DROP PRIMARY KEY; 

主键小结

主键约束小结	说明
主键的作用	记录的唯一标识
主键的特点	非空且唯一
添加主键约束	创表时添加：字段名 数据类型 primary key; 修改表结构：alter table 表名 add primary key(主键列名)
删除主键约束	alert table 表名 drop primary key;

八、唯一约束

唯一约束关键字：UNIQUE

唯一约束就是在这张表中这个字段的值不能重复。
null是没有值，所以不存在重复问题

唯一约束语法

字段名 字段类型 UNIQUE

代码示例

创建学生表st7，包含字段(id, name)，name这一列设置唯一约束，不能出现同名的学生

CREATE TABLE st7 (
	id INT,
	NAME VARCHAR(20) UNIQUE
);

添加一个学生。出现相同的name值时会报错

INSERT INTO st7 VALUES (1, '貂蝉');
INSERT INTO st7 VALUES (2, '西施');
INSERT INTO st7 VALUES (3, '王昭君');
INSERT INTO st7 VALUES (4, '杨玉环');

-- 插入相同的名字出现name重复: Duplicate entry '貂蝉' for key 'name'
INSERT INTO st7 VALUES (5, '貂蝉');

-- 出现多个null的时候会怎样？因为null是没有值，所以不存在重复的问题
INSERT INTO st3 VALUES (5, NULL);
INSERT INTO st3 VALUES (6, NULL);

九、非空约束

非空约束关键字：NOT NULL

这个字段必须设置值，不能是NULL。
非空约束如果插入null值则报错

非空约束语法

字段名 字段类型 NOT NULL

代码示例

创建表学生表st8，包含字段(id,name,gender)其中name不能为NULL

CREATE TABLE st8 (
	id INT,
	NAME VARCHAR(20) NOT NULL,
	gender CHAR(2)
);

-- 添加一条完整的记录
INSERT INTO st8 VALUES (1, '郭富城', '男');
INSERT INTO st8 VALUES (2, '黎明', '男');
INSERT INTO st8 VALUES (3, '张学友', '男');
INSERT INTO st8 VALUES (4, '刘德华', '男');

-- 姓名不赋值，出现姓名不能为null: Column 'name' cannot be null
INSERT INTO st8 VALUES (5, NULL, '男');

默认值

默认值关键字：DEFAULT

往表中添加数据时，如果不指定这个字段的数据就使用默认值。

默认值语法：

字段名 字段类型 DEFAULT 默认值;

代码示例

CREATE TABLE st9 (
	id INT,
	NAME VARCHAR(20),
	address VARCHAR(50) DEFAULT '广州'
);

-- 添加一条记录,使用默认地址
INSERT INTO st9 (id, NAME) VALUES (1, '刘德华'); 
-- 添加一条记录,不使用默认地址
INSERT INTO st9 VALUES (2, '张学友', '香港'); 

疑问：非空与唯一约束字段与主键的区别

一张表中只有一个字段可以设置为主键
一张表中可以多个字段设置非空与唯一约束
主键可以自动增长，非空与唯一约束的字段不能自动增长

十、单表存在的问题

单表问题示例

创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id主键并自动增长,添加6条数据

-- 创建员工表
create table emp(
	id int primary key auto_increment,
	name varchar(20) not null,
	age int,
	dep_name varchar(20) not null,
	dep_location varchar(20) not null
);
-- 添加员工数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');

INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');
select * from emp;

问题分析

表中出现了很多重复的数据(数据冗余)，如果要修改研发部的地址需要修改3个地方。

解决方案

将一张表分成2张表(员工表和部门表)

-- 创建部门表
create table dept(
	id int primary  key auto_increment,
	dept_name varchar(20) not null,
	dept_loc varchar(200) not null
);

-- 创建员工表
create table employee(
	id int primary key auto_increment,
	name varchar(20) not null,
	age int,
	dept_id int -- 部门id
);
-- 添加2个部门
insert into dept(dept_name,dept_loc) values('研发部','广州'),('销售部','深圳');

-- 添加员工,dep_id表示员工所在的部门
insert into employee values(null,'张三',20,1)
,(null,'李四',21,1)
,(null,'王五',20,1)
,(null,'老王',20,2)
,(null,'大王',22,2)
,(null,'小王',18,2);

select * from dept;
select * from employee;

分表存在的问题：

当我们在employee表的dep-id字段里面输入不存在的部门，数据依然可以添加。但是department表，并没有对应的部门，不能出现这种情况。employee的depid中的内容只能是department表中存在的id

目标：需要约束dep.id只能是department表中已经存在id
解决方式：使用外键约束

十一、外键约束（解决单表数据冗余）

外键约束：一张表中的某个字段引用另一个表的主键的值

主表

约束别人

副表/从表

使用别人的数据，被别人约束

如上面案例，部门表是主表，员工表是副表/从表

1、创建外键

外键约束关键字：constraint foreign key

新建表时增加外键

新建的表为从表

constraint foreign key(外键列名) references 主表(主键列名)

在已有表增加外键

alter table 从表名 add constraint foreign key(从表中要使用外键约束的列名) references 主表名(主表列名);

代码示例

建表时添加外键

-- 创建从表 employee 并添加外键约束
create table employee(
	id int primary key auto_increment,
	name varchar(20) not null,
	age int,
	dept_id int, 
	constraint foreign key (dept_id) references dept(id)
);

在已有表上添加外键

alter table employee add constraint foreign key(dept_id) references dept(id);

2、删除外键

删除外键语句

ALTER TABLE 从表 drop foreign key 外键名称;

删除外键前，需要先通过查询从表结构获得外键名称。

-- 查询外键约束名称
show create table 表名;

删除外键是对从表而言，主表没有外键删除

代码示例

-- 查询创表语句：获得外键约束名employee_ibfk_1
show create table employee;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dept_id` (`dept_id`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

# 删除employee表的emp_depid_fk外键
alter table employee drop foreign key employee_ibfk_1;

# 在employee表存在况下添加外键
alter table employee add constraint foreign key(dept_id) references dept(id);

3、使用外键约束的效果

部门表dept主键列id的值只有1，2
员工表employee的部门列dept_id插入的值只能1，2。插入3则报错。
即建立外键约束后，从表被约束的列插入主表主键列没有的值则报错

-- 员工错误的数据添加失败
-- Cannot add or update a child row: a foreign key constraint fails 
-- 违反了外键约束的规则
insert into employee values(null,'中王',20,3);

外键约束没有级联存在的问题

副表(员工表)中字段有引用主表主键的数据，主表(部门表)主键的值既不能直接修改，又不能直接删除

代码示例

# 将主表主键id=2修改为id=5
UPDATE department SET id=5 WHERE id=2; 

修改报错：Cannot delete or update a parent row: a foreign key constraint fails

# 直接删除主表id
DELETE FROM department WHERE id = 1; 

删除报错：Cannot delete or update a parent row: a foreign key constraint fails

解决方案

使用外键级联

4、外键的级联

什么是级联操作？
在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

级联语法

级联更新,主键发生更新时,外键也会更新

ON UPDATE CASCADE

级联删除，主键发生删除时，外键也会删除

ON DELETE CASCADE

使用级联代码示例

删除employee表
重新创建employee表，添加级联更新和级联删除

CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT,
	dep_id INT,
	-- 添加外键约束,并且添加级联更更新和级联删除
	CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE
);

再次添加数据到员工表和部门表

INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);

级联效果

把部门表（主表）中id等于1的部门改成d等于10

UPDATE department SET id=10 WHERE id=1; 

从表也跟随改变为10

删除部门号是2的部门

DELETE FROM department WHERE id=2; 

从表不显示已删除的部门的员工

十二、DQL语句（数据查询语言）

查询不会对数据库中的数据进行修改，只是一种显示数据的方式

简单查询

查询关键字：SELECT

1、查询所有列

星号*表示所有列

SELECT * FROM student; 

2、查询指定列

SELECT 字段名1, 字段名2，... FROM student; 

查询student表中的name 和 age 列

SELECT NAME, age FROM student; 

3、别名查询

别名关键字：AS
查询时给列、表指定别名。AS关键字可以省略

使用别名的好处：方便观看和处理查询到的数据。
单表查询别名好处不明显，多表查询的时候才能特别体现出别名查询的好处

# 字段起别名
SELECT 字段名1 AS 别名, 字段名2 AS 别名...FROM 表名; 
# 字段和表起别名
SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名 AS 表别名;

代码示例

查询sudent表中name和age列, name列的别名为"姓名", age列的别名为"年龄"

SELECT NAME AS 姓名, age AS 年龄 FROM student; 

#省略AS
SELECT NAME 姓名, age 年龄 FROM student; 

查询sudent表中name和age列，student表别名为s

SELECT NAME, age FROM student AS s; 

#省略AS
SELECT NAME, age FROM student s; 

4、清除重复值查询

清除重复值关键字：Distinct
查询指定列并且结果不出现重复数据

SELECT DISTINCT 字段名 FROM 表名;

代码示例

查询name, age列并且结果不出现重复name和age

SELECT DISTINCT NAME, age FROM student; 

5、查询结果参与运算

1、某列数据和固定值运算

SELECT 列名1 + 固定值 FROM 表名;

2、某列数据和其他列数据参与运算

SELECT 列名1 + 列名2 FROM 表名;

注意: 参与运算的列/固定值的数据类型必须是数值类型（int、double）

代码示例

添加数学，英语成绩列，给每条记录添加对应的数学和英语成绩
查询的时候将数学和英语的成绩相加

实现步骤：
1、修改student表结构,添加数学和英语成绩列

ALTER TABLE student ADD math INT;
ALTER TABLE student ADD english INT;

2、给每条记录添加对应的数学和英语成绩

3、查询math + english的和

SELECT math + english FROM student; 

4、直接查询处理效果不好看，查询math + english的和使用别名”总成绩”

SELECT math + english 总成绩 FROM student; 

5、查询所有列与math + english的和并使用别名”总成绩”

6、查询姓名、年龄，将每个人的年龄增加10岁

SELECT name, age + 10 FROM student; 

条件查询

条件查询关键字：WHERE

前面我们的查询都是将所有数据都查询出来,但是有时候我们只想获取到满足条件的数据语法格式：

SELECT 字段名 FROM 表名 WHERE 条件;

流程：取出表中的每条数据,满足条件的记录就返回,不满足条件的记录不返回

准备数据

# 创建表
CREATE TABLE student3 (
  id int,
  name varchar(20),
  age int,
  sex varchar(5),
  address varchar(100),
  math int,
  english int 
);

# 插入数据
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES 
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);

1、比较运算符查询

> 大于  
< 小于  
<= 小于等于  
>= 大于等于  
>= 等于  
<>、!= 不等于

代码示例

查询math分数大于80分的学生

SELECT * FROM student3 WHERE math>80; 

查询english分数小于或等于80分的学生

SELECT * FROM student3 WHERE english<=80; 

查询age等于20岁的学生

SELECT * FROM student3 WHERE age=20; 

查询age不等于20岁的学生

SELECT * FROM student3 WHERE age!=20;
SELECT * FROM student3 WHERE age<>20;

2、逻辑运算符查询

and(&&) 多个条件同时满足
or(||) 多个条件其中一个满足
not(!) 不满足

代码示例

查询age大于35且性别为男的学生(两个条件同时满足)

SELECT * FROM student3 WHERE age>35 AND sex='男'; 

查询age大于35或性别为男的学生(两个条件其中一个满足)

SELECT * FROM student333 WHERE age>35 OR sex='男'; 

查询id是1或3或5的学生

SELECT * FROM student3 WHERE id=1 OR id=3 OR id=5; 

in关键字（简化or逻辑）

in里面的每个数据都会作为一次条件，只要满足条件的就会显示

语法格式：

SELECT 字段名 FROM 表名 WHERE 字段 in (数据1, 数据2...); 

代码示例

查询id是1或3或5的学生

SELECT * FROM student3 WHERE id IN (1,3,5); 

查询id不是1或3或5的学生

SELECT * FROM student3 WHERE id NOT IN (1,3,5); 

3、范围查询

范围查询关键字：BETWEEN

范围查询语法：表示从值1到值2范围（包头又包尾）

BETWEEN 值1 AND 值2 

比如 age BETWEEN 80 AND 100 相当于age >= 80 && age <= 100

代码示例

查询english成绩大于等于75，且小于等于90的学生

# 用and
SELECT * FROM student3 WHERE english>=75 AND english<=90;
# 用between
SELECT * FROM student3 WHERE english BETWEEN 75 AND 90;

4、like查询（模糊查询）

模糊查询关键字：LIKE

LIKE一般配合通配符字符串使用，模糊查询语法格式

SELECT * FROM 表名 WHERE 字段名 LIKE 通配符字符串;

满足通配符字符串规则的数据就会显示出来
通配符字符串就是含有通配符的字符串。

MySQL通配符有两个

% 表示0个或多个字符(任意个字符) 
_ 表示一个字符

代码示例

查询姓马的学生

SELECT * FROM student3 WHERE NAME LIKE '马%'; 

查询姓名中包含’德’字的学生

SELECT * FROM student3 WHERE NAME LIKE '%德%'; 

查询姓马，且姓名有三个字的学生

SELECT * FROM student3 WHERE NAME LIKE '马__'; 

5、排序查询

排序查询关键字：ORDER BY

通过ORDER BY关键字后面的子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)

# []代表 可不写 或 可选ASC或DESC
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC | DESC]; 

ASC：升序，默认是升序
DESC：降序
两个都不写默认ASC

排序查询示例

单列排序

单列排序就是使用一个字段作为条件进行排序

需求：查询所有数据,使用年龄降序排序

SELECT * FROM student3 ORDER BY age DESC; 

组合排序

组合排序就是先按第一个字段进行排序，如果第一个字段相同，才按第二个字段进行排序，依次类推。上面的例子中，年龄是有相同的。

当年龄相同再使用math进行排序

SELECT 字段名 FROM 表名 WHERE 字段值 ORDER BY 字段名1 [ASC|DESC],字段名2 [ASC|DESC];

需求：查询所有数据，在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序

SELECT * FROM student3 ORDER BY age DESC, math DESC; 

6、聚合函数

以上我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断。
而使用聚合函数查询是纵向查询,它是对一列的值进行计算,然后返回一个结果值。另外聚合函数会忽略空值。

聚合函数有五个:
count：统计指定列记录数,记录为NULL的不统计
sum：计算指定列的数值和,如果不是数值类型,那么计算结果为0
max：计算指定列的最大值
min：计算指定列的最小值
avg：计算指定列的平均值,如果不是数值类型,那么计算结果为0

聚合函数关键字的使用位置：写在SQL句 SELECT 关键字后面

SELECT 字段名...  FROM 表名; 
SELECT COUNT (age) FROM 表名;

代码示例

1、查询学生总数

SELECT COUNT(english) FROM student3; 

用IFNULL函数，解决不统计english为null的记录的问题

SELECT COUNT(IFNULL(english,0)) FROM student3; 

统计结果

IFNULL()函数（解决不统计null记录问题）

使用聚合函数COUNT我们发现对于NULL的记录不会统计，可以用IFNULL解决

IFNULL(expr1, expr2)的用法
假如expr1不为NULL，则 IFNULL()的返回值为 expr1; 否则其返回值为expr2（和三元运算符相似）

我们可以利用IFNULL()函数，如果记录为NULL，给个默认值，这样统计的数据就不会遗漏

代码示例2

# 查询学生总数
select count(*) 总数 from student;

# 查询年龄大于40的总数
select count(*) 总数 from student where age > 40;

# 查询数学成绩总分
select sum(math) 数学成绩总分 from student;

# 查询数学成绩平均分
select avg(math) 数学平均分 from student;

# 查询数学成绩最高分
select max(math) 数学最高分 from student;

# 查询数学成绩最低分
select min(math) 数学最低分 from student;

7、分组查询

分组查询关键字：GROUP BY
分组查询是指使用GROUP BY语句对查询信息进行分组，相同数据作为一组。分组语法格式：

SELECT 字段1,字段2....FROM 表名 GROUP BY 组字段 [HAVING条件];

having与where的区别：

having：对分组之后的数据进行过滤，后面可以使用聚合函数
where：对行数据执行过滤，后面不能使用聚合函数

分组的目的就是为了统计，一般分组会跟聚合函数一起使用

注意实现
1、分组后聚合函数操作的不再是行数据，而是组数据了。
2、对于分组后的条件需要使用having子句
3、having语句后使用的字段名必须是出现在select语句中的

代码示例1

单独分组

将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。
分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

# 这句话会将sex相同的数据作为一组
SELECT * FROM student3 GROUP BY sex;

结合聚合函数使用分组

# 将每组的math进行求和，返回每组统计的结果
SELECT SUM(math), sex FROM student3 GROUP BY sex;

注意事项

当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来，否则看不到数据属于哪组的。
理解：即分组查询时，order by关键字后面的分组条件字段，也要放在select关键字后面作为查询字段显示

没有分组条件字段作为查询字段显示（没查询出分组条件）

没有分组条件字段作为查询字段显示（查询出分组条件）

代码示例2

# 分组查询
-- 分组语法：group by 字段名 having 条件
# 按性别分组
-- 分组的目的是为了统计，单独分组没有意义
select * from student group by sex;

# 查询男女各多少人
select sex 性别, count(*) 人数 from student group by sex;

# 查询年龄大于25岁的人,按性别分组,统计每组的人数
select sex 性别, count(*) 人数 from student where age > 25 group by sex;

# 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
select sex 性别, count(*) 人数 from student where age > 25 group by sex  having count(*)>2 ;

-- 注意事项：having语句后面如果要使用字段，则该字段必须出现在select 语句后面
# *已包含字段age
select * from student where age > 25 group by sex  having age>25;

查询时，order by、having后面的字段，都要出现在select后面，不让不知道分组信息属于一组

8、limit语句（限制查询，分页查询）

分页查询关键字：LIMIT

LIMIT是限制的意思，LIMIT的作用就是限制查询记录的条数。
使用位置：LIMIT关键字放在SQL语句的最后

LIMIT语法格式:

# 格式1
LIMIT offset, length;

# 格式2
LIMIT length; 

select 字段名 from 表名 limit 要跳过的行数,要查询的行数;

offset是指偏移量，可以认为是跳过的记录数量，默认为0
length是指需要显示的总记录数

理解：
offset偏移量，即跳过这组的前面多少条再显示，
length总记录数，即一页显示多少条，不够这个数则有多少显示多少

思考: limit子句为什么排在最后?
因为前面所有的限制条件都处理完了,只剩下显示多少条记录的问题了!

代码示例1

-- 分页查询语法：limit 要跳过的行数, 要查询的记录数;
# 查询学生表中数据，从第三条开始显示，显示6条
select * from student limit 2,6;

代码示例2

-- 每页显示5条
-- 第一页： LIMIT 0,5;	跳过0条，显示5条
-- int pageNo = 1;
-- int pageSize = 5;
-- select * from student limit (pageNo-1) * pageSize,pageSize; 
select * from student limit 0,5;
-- 第二页： LIMIT 5,5;  跳过5条，显示5条
select * from student limit 5,5;
-- 第三页： LIMIT 10,5; 跳过10条，显示5条
select * from student limit 10,5; 

-- 分页和排序同时使用：排序要写在分页语句之前
select * from student order by age limit 0,5 ;

面试题：MySQL函数有哪些？

Mysql函数：
https://www.csdn/gather_22/MtTaIgysMjk0Mi1ibG9n.html

本文标签： 数据库语句sqlJava14MySQLDQL