mysql数据库基础(三)

一.DQL语句的其他语法:

1.分组:group by

1)语法格式:

select 字段列表 from 表名 group by 分组字段;

2)注意事项:

①查询的字段列表中可以使用分组字段;

②分组group by关键字后面不能跟聚合函数,否则语法错误;

③分组group by关键字后面可以有多个字段,多个字段之间用","隔开;

④分组group by关键字后面的分组字段中,如果有重复的数据信息,只显示最早出现的那条数据信息;

⑤使用group by分组之后,你所划分的组就是根据你所选中的分组字段进行分组,并且会形成单个组别;

⑥如果想要显示出分组之后所有的信息,我们可以根据非业务字段与你所想要分组的字段结合来分组;

语法格式:select*from 表名 group by 分组字段(你所希望根据某字段进行分组的字段) 非业务字段(字段值不重复,且唯一)

3)带条件分组查询

①语法格式

select 字段列表 from 表名 where 条件 group by 分组字段;

②注意事项

根据条件分组必须把条件语句放置在分组语句之前,否则,语法会出现错误;

2.筛选 having

1)语法格式:

select 字段列表 from 表名 group up 分组字段 having 筛选条件;

2)带条件,分组并筛选;

①语法格式

select 字段列表 from 表名 where 条件 group by 分组字段 having 筛选条件;

②注意事项:

>带条件,分组并筛选的时候,其顺序是条件语句(where 条件),分组语句(group by 分组字段),筛选语句(having 筛选条件);

>筛选语句中,having后面可以跟聚合函数;

3.分页查询limit

1)语法格式: select 字段列表 from 表名 limit 起始行数,每页显示的条数;

2)注意:①起始行数,从0开始算

② 起始行数=(当前页码-1)*每页显示的条数;

③前提条件:每页显示2条

4.分页查询使用场景

二.数据库的备份和还原

1.图形化方式的备份和还原

1)备份:

选中库名--->右键--->backup/export(备份导出)--->选中Sql脚本,存到本地某个磁盘上--->StrutureAndDate:表的结构和数据都需要备份;

举例:

步骤一:

步骤二:

步骤三:选中要备份的文件

步骤四:

步骤五

2)还原:

在SqYog中删除之前的旧库--->新建库--->右键--->import(导入)--->选中你所备份的文件执行;如图:

步骤一:选中你之前的旧库,按键盘上的 "Delete"键,点击"是";

步骤二:新建库:(刷新找到新建的库);

CREATE DATABASE mysql_ee2203;

步骤三:右键,选择import(导入);

步骤四:选择导入脚本Execute SQL Script...

完成;

2.命令行方式的备份和还原

1)备份:

以管理员身份进入命令行,不需要登录myaql

mysqldump -uroot -p密码库名> 磁盘上的路径xx\xx\xxx\xx.sql;如下:

C:\Users\User>mysqldump -uroot -pf971014 mysql_2203 > D:\Java\QianFeng\note\javascript\mysql02mysql.sql;

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: Got error: 1049: Unknown database 'mysql_2203' when selecting the database

2)还原:

① 进入dos控制台,登录mysql

② 先将库删除,新进一个库

③使用这个库,use库名;

④source 备份的sql文件路径;

⑤source: 指定路径的sql脚本;

三.数据库的约束

概念:限定OBA(数据库管理员)操作数据库的时候的一种行为;

我们使用表stu来举例

创建一张新表

CREATE TABLE stu (
  id INT,
  NAME VARCHAR (20),
  gender VARCHAR (3)  
) ;

没有数据库约束我们可以直接插入一个null值或某个非业务字段(如:id)

INSERT INTO stu VALUES(1,'高圆圆','女'),(2,'文章','男');
INSERT INTO stu VALUES(3,'王宝强','男');
INSERT INTO stu (id,NAME)VALUES(2,'小小');
--查看表中数据
SELECT*FROM stu;

表中数据如 下:(实际开发中通常id为非业务字段,不能重复,并且有些值不能为null);

上述表中的gender字段没有插入数据,默认就是null,空值(没有意义)

1.默认约束 default;

1)意义:如果某个字段没有插入数据,那么我们我加入的默认约束就定到了作用;

2)设置默认约束的方式

①方式一: 创建表的时候直接在约束字段后面加上default 默认字符;

举例:

CREATE TABLE stu (
  id INT,
  NAME VARCHAR (20),
  gender VARCHAR (3) DEFAULT '男'--默认约束字段
) ;
--执行下面的操作:
INSERT INTO stu VALUES(1,'高圆圆','女'),(2,'文章','男');
INSERT INTO stu VALUES(3,'王宝强','男');
INSERT INTO stu (id,NAME)VALUES(2,'小小');--
SELECT*FROM stu;--查看表中的数据;

上述代码中INSERT INTO stu (id,NAME)VALUES(2,'小小');没有设置gender字段的数据,但是我们设置了默认数据 gender VARCHAR (3) DEFAULT '男',故数据显示的时候"小小"的gender数据为男;

②方式二: -通过sql语句给gender字段加上默认约束

语法格式:alter table 表名 modify 字段名称字段类型 defualt 默认字符;

3)删除默认约束:通过sql语句删除默认约束

语法格式:alter table 表名 modify 字段名称字段类型;

2.非空约束 not null;

1)意义:表示当前字段不能有null,

2)添加非空约束;

①方式一:直接在创建表的时候在约束字段后面增加非空约束(not null);

举例:

--新建表
CREATE TABLE stu (
  id INT,
  NAME VARCHAR (20),
  gender VARCHAR (3),
  address VARCHAR (50) NOT NULL-- 添加非空约束;
) ;
-- 1>address字段插入一个null数据
INSERT INTO stu VALUES(5,'张佳宁','女',NULL);
--2>新插入一条数据address不给数据,
INSERT INTO stu (id,NAME,gender)VALUES(6,'李飞','男');

1>结果会出现: 列“地址”不能为空的错误;

Error Code: 1048

Column 'address' cannot be null

2>结果会出现: 字段“地址”没有默认值;

Error Code: 1364

Field 'address' doesn't have a default value

总结:对于添加非空约束的子段来说

1)当约束字段创建标注数据为null时,直接报错;

2)当约束字段创建的数据没有给值,也会报错;

②方式二:通过sql语句设置非空约束

语法格式:alter table 表名 modify 字段名称字段类型 not null;

3)删除非空约束:通过sql语句去掉非空约束(修改表的类型)

语法格式: alter table 表名 modify 约束字段名称字段类型;

3.唯一约束 unique;(属于一种索引(index))

1 )意义:使该字段的数据唯一,不重复

2)添加唯一约束的方式:

①方式一:直接在创建表的时候添加唯一约束unique;

举例:

--新建一个表
CREATE TABLE stu(
    id INT,
    NAME VARCHAR(20),
    phone_number VARCHAR(11) UNIQUE -- 添加唯一约束
);
--插入两个数据,phone_number:的数据相同;
INSERT INTO stu VALUES(1,'文章','18295738261');
INSERT INTO stu VALUES(2,'马伊琍','18295738261');

结果显示是: 键“phone_number”的重复条目“18295738261”

Error Code: 1062

Duplicate entry '18295738261' for key 'phone_number'

②方式二: 通过sql语句设置唯一约束, 此时语法就和修改表的字段类型的语法一致了

语法格式:alter table 表名 modify 约束字段名称字段类型 unique;

3)取消唯一约束设置,不能使用sql语句来设置,因为用sql语句只能修改表的类型,所以我们采取下面这种语法结构,针对索引约束进行删除;

语法格式:alter table 表名 drop index 约束字段名称;

4.主键约束 primary key

1)意义:一般设置在非业务字段上(如:id),使之不能为空,并且不能重复;

2)添加主键约束的方式

①方式一:在创建表的时候直接在约束字段后面街上关键字 primary key;

举例:

-- 删除之前插件的表
DROP TABLE stu;
-- 创建新表
CREATE TABLE stu (
  id INT PRIMARY KEY -- 设置主键约束,
  NAME VARCHAR (20)
) ;
-- 插入数据
INSERT INTO stu VALUES (1,'文章'),(2,'马伊琍'); -- 第一次插入数据
INSERT INTO stu VALUES (1,'赵又廷'); --第二次插入数据
INSERT INTO stu (NAME)VALUES ('高圆圆'); -- 第三次插入数据
TABLE INTO stu VALUES (NULL,'王宝强'); -- 第四次插入数据
-- 查询表的数据
SELECT
  id '编号',
  NAME '姓名'
FROM
  stu ;

第一次插入查询表的结果为:

第二次插入数据的结果为:

出现错误:Duplicate entry '1' for key 'PRIMARY',id是主键不能重复

第三次插入数据的结果为:

出现错误:Field 'id' doesn't have a default value,主键没有设置默认值;

第四次插入数据的结果为:

-- 出现错误:

--You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table into stu values (null,'王宝强')' at line 1

-- 也就是所谓的 语法错误

②方式二:通过sql设置主键约束

语法格式: alter table 表名 modify 约束字段名称字段类型 primary key;

3)取消主键设置约束的方式,不能采用sql语句,只能使用一下语句

语句格式:alter table表名 drop primary key;

5.自增长约束 auto_increment

1)意义:加入自增长约束后不需要给该字段赋值,他会自动增长;

2)设置自增长约束的方式

①方式一:直接在创建表的时候设置自增长约束;

举例:

-- 删除之前的表

DROP TABLE stu;
--新建stu表
CREATE TABLE stu (
  id INT PRIMARY KEY AUTO_INCREMENT,
  -- 设置自增长约束
  NAME VARCHAR (20)
) ;
-- 插入数据
INSERT INTO stu (NAME)VALUES('文章'),('马伊琍'),('文爱马'); --第一次插入数据
-- 清空表中数据
DELETE FROM stu;
-- 添加数据(第二次添加数据)
INSERT INTO stu (id,NAME) VALUES(12,'小小');
INSERT INTO stu (NAME)VALUES('大大'),('阿南');

第一次插入数据的结果显示:

第二次添加数据的结果显示:自增长约束可以自己插入新的数据,然后在之前约束字段的数据上继续自增

3)获取第一次增长主键

语法格式:select last_insert_约束字段();

举例:

SELECT LAST_INSERT_ID() '第一次约束字段自增数据';

结果显示:

4)注意:

①设置自增长约束只能针对int类型;改变约束字段类型会出

ncorrect column specifier for column 'id',列“id”的列说明符不正确的错误;

② 自增长约束需要配合主键约束共同使用

否则会出现错误

Incorrect table definition; there can be only one auto column and it must be defined as a key

表定义不正确;只能有一个 auto 列，并且必须将其定义为键.

6.外键约束 foreign key

1)引入外键约束

举例:

 -- 需求:创建一个表用来描述,员工编号,员工姓名,员工性别,员工所在部门;
CREATE TABLE employee (
  employee_id INT, -- 员工编号
  employee_name VARCHAR (10),-- 员工姓名
  empioyee_sex VARCHAR (2),-- 员工性别
  employee_dept_name VARCHAR (10) -- 表示员工所在部门
) ;
-- 插入数据
INSERT INTO employee
VALUES
  (01, '文章', '男', '开发部'),
  (02, '姚笛', '女', '测试部'),
  (03, '马伊琍', '女', '运维部'),
  (04, '张三丰', '男', '运维部'),
  (05, '张无忌', '男', '开发部'),
  (06, '赵敏', '女', '测试部') ;
SELECT
  employee_id '员工编号',
  employee_name '员工姓名',
  empioyee_sex  '员工性别',
  employee_dept_name  '员工所在部门'
FROM
  employee ;

查询数据结果显示为:

/*在面的图片中我们发现的的问题一:
员工信息表中包含了员工信息以及员工部门信息,但是员工部门信息出现大量重复数据
对此我们应将其数据归结在同一个表中,降低其数据的冗余现象,避免重复数据过多;    
对此优化改进:一张表只描述一件事情(各司其职)
将员工信息放入一张表,员工部门放入一张表
*/
-- 创建员工部门表
CREATE TABLE dept (
  id INT PRIMARY KEY AUTO_INCREMENT,
  -- 根据部门信息进行编号,设置为主键并且自增约束;
  NAME VARCHAR (20) -- 部门名称;
) ;
-- 插入三个部门
INSERT INTO dept (NAME) VALUES ('开发部'),('运维部'),('测试部');
-- 查询dept中的数据
SELECT
  id '部门编号',
  NAME '部名称'
FROM
  dept ;
-- 创建员工表
-- 删除之前所创建的员工表
DROP TABLE employee;
-- 创建新的员工表
CREATE TABLE employee (
  employee_id INT PRIMARY KEY AUTO_INCREMENT,
  -- 员工编号设置为主键约束,并且为其设置自增约束
  employee_name VARCHAR (10),
  -- 员工姓名
  employee_sex VARCHAR (2),
  -- 员工性别
  dept_id INT
  -- 员工部门编号
) ;
- 插入员工表中的数据
INSERT INTO employee (
  employee_name,
  employee_sex,
  dept_id
)
VALUES
  ('文章', '男', 1),
  ('姚笛', '女', 3),
  ('马伊琍', '女', 2),
  ('张三丰', '男', 2),
  ('张无忌', '男', 1),
  ('赵敏', '女', 3) ;
-- 查询员工表的数据
SELECT
  employee_id,
  employee_name,
  employee_sex,
  dept_id
FROM
  employee ;

部门表的显示信息:

员工表的数据显示

-- 插入一条员工部门表中没有的数据
INSERT INTO employee(employee_name, employee_sex, dept_id)VALUES('小明','男',4);
-- 再次查看员工表中的数据
SELECT
  employee_id,
  employee_name,
  employee_sex,
  dept_id
FROM
  employee ;

员工表的数据显示:

-- 该数据能插入进去
-- 从上面的图片中我们可以看出:
-- 我们所创建的两个表之间并没有关联,只是单个的表,没有任何的先限制和关联
-- 因此我们引入外键约束foreign key来解决此问题,将员工表与员工部门表之间产生关联,
-- 此问题的根本就是让员工表的dept_id 与 部门表中的id产生关联已达到我们想要的效果
-- 外键所在的表称为从表(这里指的就是员工表)
-- 部门表也就是主表(主表是相对于外表来讲的,)
-- 重新创建我们员工表(从表)
-- 删除之前所创建的员工表
DROP TABLE employee;
--创建新的员工表
CREATE TABLE employee(
employee_id INT PRIMARY KEY AUTO_INCREMENT,
  -- 员工编号设置为主键约束,并且为其设置自增约束
  employee_name VARCHAR (10),
  -- 员工姓名
  employee_sex VARCHAR (2),
  -- 员工性别
  dept_id INT ,
  -- 员工部门编号
  CONSTRAINT -- 声明
  dept_employee_fk -- 外键名称 命名规则:主表名称_从表名称_fk
  FOREIGN KEY (dept_id) -- 作用在这个dept_id字段上,语法格式:foreign key (作用字段),
  REFERENCES dept(id) -- 关联主表的主键references 主表名称(主键);
  );
-- 此时我们将从表与主表已经关联
-- 那我们再一次插入一条与主表无关的数据
INSERT INTO employee(employee_name, employee_sex, dept_id)VALUES('小明','男',4);

结果显示为

Error Code: 1452

Cannot add or update a child row: a foreign key constraint fails (`mysql_ee2203`.`employee`, CONSTRAINT `dept_employee_fk` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))

翻译为:无法添加或更新子行：外键约束失败（“mysql_ee2203”。员工“、”约束dept_employee_fk“外键（”dept_id“）引用”部门“（”id“））

2)增加外部约束之后如果我们想要删除主表征的数据;(相对于之前是比较麻烦的)

步骤一:

将从表中的数据删除或者修改

步骤二:

然后在修改或者删除主表中的数据,

举例:我们想要删除主表中的部门3,

-- 在从表中找到部门编号为3的员工,将其删除
DELETE
FROM
  employee
WHERE employee_id = 19
  OR employee_id = 15 ;
-- 删除部门表中id=3的数据;
DELETE FROM dept WHERE id = 3;

查询部门表中的数据显示为:

原文链接：https://blog.csdn.net/MasterXiaoFan/article/details/124769011