MySQL数据库（一）

目录

一、创建用户

二、用户登录注意事项

三、数据库操作

四、表相关的操作

五. 表记录相关的操作

1 新增、更新、删除表记录

2 查询表记录(单表)

2.1 where子句查询

2.2 模糊查询

2.3 多行函数查询

2.4 分组查询

2.5 排序查询

2.6 分页查询

2.7 其他函数

3 多表查询

3.1 连接查询(db30数据库)

3.2 子查询(db40数据库)

3.3 多表查询练习(db40数据库)

四、扩展

1. SQL注解

2. 取消当前sql语句的执行用：\c

3. MySQL的数据类型

4. mysql的字段约束

5. mysql的外键约束

5.1 添加外键约束

5.2 删除外键约束

5.3 添加外键约束(多对多)

5.4 级联更新、级联删除

6. 表关系

7. 数据库的备份与恢复

7.1 数据库的备份

7.2 数据库的恢复

---

一、创建用户

说明：装好的MySQL服务器自带四个数据库，其中用户信息存在名称为mysql的数据库中的user表中。

方式一(密码暗文)：
 select password("adam");
 create user "adam"@"%" identified by password "*46D8C7BAEFF40601D8F821A601F07CD006FFB2D0";

方式二(明文)：
 create user 'tom'@'localhost' identified by 'tom';

说明：
1) 单双引号都可以；
2) 工作中建议用暗文；
3) %代表任意的ip地址都可以访问。

参考专栏：MySQL教程：MySQL数据库学习宝典（从入门到精通）

其中，左侧菜单“MySQL用户管理” 中还有用户授权等内容。

二、用户登录注意事项

       1. 连接MySQL服务器的语句结尾不写分号；

       2. -h 后面跟的是主机名或者IP地址，如果不写，默认连接localhost(127.0.0.1)

       3. -P 后面跟的是端口，不写默认是3306，其中P要大写，小写的-p是密码。

三、数据库操作

查看所有数据库：

进入某一数据库：（进入数据库后，才能操作数据库中的表和表记录）

查看已进入的库：select database();

查看当前数据库中所有的表：

删除某一数据库：drop database 库名； drop database if exists hhh;

创建数据库：

DROP DATABASE IF EXISTS `ry-cloud`;
CREATE DATABASE  `ry-cloud` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
USE `ry-cloud`

          语法：create database 库名 charset 编码;

                    eg: -> create database if not exists mytest01 charset utf8; 

          注意：mysql中不支持横杠（-），所以utf-8要写成utf8 .

查看建库时的语句(并校验数据库使用的编码)：

           语法：show create database 库名;

                     eg: -> show create database mytest01;

四、表相关的操作

删除表：语法——drop table 表名；

               eg: drop table if exist stu;

创建表：

查看建表时的语句：show create table +表名;

拓展 ENGINE=InnoDB  ：ENGINE=InnoDB_低调丶生活的博客-CSDN博客_engine=innodb

查看表结构：

修改表的字段名：

更新表的id为主键自增：

 alter table 表名 modify id int primary key auto_increment;

删除一列： alter table stu drop grade;

增加一列：alter table stu add score double;

修改某一列的类型用modify(见上面)：alter table stu modify ......;

修改字段名用change(见上面)：alter table stu change ......;

五. 表记录相关的操作

1 新增、更新、删除表记录

插入表记录：

        语法：见下图

 说明：1）mysql中推荐使用单引号包裹字符串和日期，有些版本的数据库双引号会报错；

            2）如果在cmd窗口中执行插入记录的语句，先set names gbk; 再插入记录；且一个cmd窗口只需设置一次即可；

            3）创建数据库时一定要指定utf8，这样在库中创建的表也是utf8的编码；

            4）如果给表中所有的列都插入值，列名可以省略不写，值的个数和顺序必须和表中创建的列的个数和顺序一致。

查询表中所有的表记录：

修改表中的某条记录：

       语法：update 表名 set 列=值,列=值...[where子句];

        e.g:  update 表名 set name='八戒',gender='男',score=score+10 where id=7;

删除某条表记录：

       语法：delete from 表名 [where子句];

 delete from stu;  -- 没有where默认删除所有记录！！！谨慎
 delete from stu where id=5;

2 查询表记录(单表)

2.1 where子句查询

① ifnull(列名,值)函数:

select name,sal+ifnull(bonus,0) 总薪资 from emp where sal+ifnull(bonus,0)>3000;

② in(,,,) 和 not in(,,,)

select name,sal from emp where sal=1400 or sal=1600 or sal=1800;

select name,sal from emp where sal in(1400,1600,1800);  

select name,sal from emp where sal not in(1400,1600,1800);

select name,sal from emp where sal<>1400 and sal<>1600 and sal<>1800;

select name,sal from emp where not (sal=1400 or sal=1600 or sal=1800);

select name,sal from emp where !(sal=1400 or sal=1600 or sal=1800);

查询薪资大于3000且奖金小于600的员工姓名,薪资和奖金:

select name,sal,bonus from emp where sal>3000 and ifnull(bonus,0)<600;

③查询没有部门的员工(即部门不为null)

select * from emp where dept is null;  -- 这里必须是is,不能是=, 因为null非常特殊,它和任何值都不相等,包括和它自己。

查询有部门的员工： select * from emp where dept is not null;

                                  select * from emp where !(dept is null);

                                  select * from emp where not(dept is null);

2.2 模糊查询

like可以用作模糊查询, 需要结合%和_使用;  其中%代表0个或多个字符, _只能表示一个任意字符。

select * from emp where name like '刘%';   -- 以'刘'字开头;

select * from emp where name like '刘_';    -- 查以'刘'开头且名字只有两个字的;

select * from emp where name like '%刘';   -- 以'刘'字结尾;

select * from emp where name like '%刘%';   -- 包含'刘'字;

2.3 多行函数查询

多行函数也叫聚合函数（聚集函数）； 多行函数默认过滤null值。

①统计emp表中薪资大于3000的员工个数：

select count(*) from emp where sal>3000;

②求emp表中最高薪资：select max(sal) from emp;

③求emp表中最低薪资：select min(sal) from emp;

④统计所有员工总薪资(不包含奖金)：select sum(sal) from emp;

⑤统计所有奖金的平均值：

⑥统计emp表员工的平均总薪资(包含奖金)：select avg(sal+ifnull(bonus,0)) from emp;

2.4 分组查询

语法：select 列 | * from 表名 [where子句] group by 列;

e.g:   select dept from emp group by dept;

         select job,count(*) from emp group by job;

         select max(sal),dept from emp group by dept;

思考：不分组使用多行函数和分了组使用多行函数的区别？

2.5 排序查询

语法：select 列名 from 表名 [where子句] [group by 列] order by 列名 [asc | desc]; 

e.g:   select name,sal from emp order by sal asc;   --  默认是asc升序

         select name,bonus from emp order by bonus desc;

         按照奖金降序排序，如果奖金相同，再按照薪资降序排序：

         select name,bonus,sal from emp group by bonus desc,sal desc;  -- 谁在先就按谁先排序

2.6 分页查询

在mysql中，通过limit进行分页查询，查询公式为：

       limit (页码-1)*每页显示记录数，每页显示记录数

 练习：求emp表中薪资最高的前3名员工的信息，显示姓名和薪资

             select name,sal from emp order by sal desc limit 0,3;

2.7 其他函数

select curdate();    获取当前日期：年月日

select curtime();     获取当前日期：时分秒

select sysdate(); select now();     获取当前日期：年月日时分秒

select year('2022-01-16');

select month('2022-01-16');

select day('2022-01-16');

select hour('2022-02-17 17:25:11');

select minute('2022-02-17 17:25:11');

select second('2022-02-17 17:25:11');

concat(s1,s2,s3);  将s1,s2,s3拼接在一起返回；

concat_ws(x,s1,s2,s3);  将s1,s2,s3拼接在一起返回，且拼接时以x作为分隔符；  

练习一：查询emp表中所有在1993年和1995年之间出生的员工，显示姓名和出生日期

方式一：将1993和1995转换为日期格式再和birthday比较
select * from emp where birthday between '1993-1-1' and '1995-12-31';
方式二：将birthday中的年份用year函数提取出来，再和1993和1995比较
select * from emp where year(birthday) between 1993 and 1995;

练习二：查询emp表中本月过生日的员工

 select * from emp where month(birthday) = month(now());

练习三：查询emp表中员工的姓名和薪资（薪资格式为：xxx（元））

 select name,concat(sal,'(元)') 薪资 from emp;

练习四：查询emp表中员工的姓名和薪资（薪资格式为：xxx/元 ）

 select name,concat_ws('/',sal,'元') from emp;

 

3 多表查询

3.1 连接查询(db30数据库)

说明：内连接还有一种隐式的写法，即不需要显示的指定 INNER JOIN 关键字（见上图）。 

SELECT e.id,e.name,e.dept_id,d.name
FROM emp e  INNER JOIN
     dept d
ON e.dept_id = d.id;   --这里ON写成WHERE也可以执行，试过了。

或

SELECT e.id,e.name,e.dept_id,d.name
FROM emp e,
     dept d
WHERE e.dept_id = d.id;

left join 是 left outer join 的简写，它的全称是左外连接，是外连接中的一种。

right join 是 right outer join 的简写，它的全称是右外连接，是外连接中的一种。

mysql不支持全外连接查询,但可以通过union来模拟这种查询:

SELECT e.id,e.name,e.dept_id,d.name
FROM emp e LEFT JOIN dept d
ON e.dept_id = d.id
UNION
SELECT e.id,e.name,e.dept_id,d.name
FROM emp e RIGHT JOIN dept d
ON e.dept_id = d.id;

能够使用union和union all合并结果的查询语句,必须符合:
1) 两条sql语句查询的结果列数必须相同;
2) 两条sql语句查询的结果列名必须相同;(低版本mysql要求)

内连接：指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。

外连接：连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。

左外连接：左边表数据行全部保留，右边表保留符合连接条件的行。

右外连接：右边表数据行全部保留，左边表保留符合连接条件的行。

全外连接：左外连接 union 右外连接，Mysql 中暂不支持。

进入查看: MySQL查询之内连接，外连接查询场景的区别与不同_daTou-CSDN博客_内连接查询和外连接查询区别

3.2 子查询(db40数据库)

将一个sql语句的执行结果作为另外一条sql语句的条件来执行，这就是子查询。

练习一：列出薪资比'王海涛'的薪资高的所有员工，显示姓名、薪资

 select name,sal from emp where sal>(select sal from emp where name='王海涛');

练习二：列出与'王海涛'从事相同职位的所有员工，显示姓名、职位

 select name,job from emp where job=(select job from emp where name='王海涛');

3.3 多表查询练习(db40数据库)

-- 47.列出在'培优部'任职的员工，假定不知道'培优部'的部门编号，显示部门名称，员工名称;

        方法思路：

        sql语句为(两种写法)： INNER JOIN （我写的left join 和 right join都是错的）

-- 48.(自查询)列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名;

-- 49.列出最低薪资大于1500的各种职位，显示职位和该职位的最低薪资;

   错误写法：select job,min(sal) from emp group by job where min(sal)>1500;

   正确写法：select job,min(sal) from emp group by job having min(sal)>1500;

①where应放在from子句后，group by子句前；

②where中不能使用多行函数(列别名也不能用在where中)；

③where是在分组之前执行，先过滤掉一些记录，再基于剩余的记录进行分组，而本地是先分组再过滤，所以不能使用where，应该使用having。

    ----------------------------------
    where和having的区别?
    1)where和having都是用于对表中的记录进行筛选过滤
    2)where用于在分组之前对记录进行筛选过滤,而having用于对分组之后的记录进行筛选过滤
    3)where子句中不能使用多行函数 和 列别名,但可以使用表别名!
        select name as 姓名, sal as 薪资 from emp e;
        -- 其中上面的'姓名','薪资'都是列别名, e是表别名
    4)having子句中可以使用多行函数 和 列别名 以及 表别名!
    ----------------------------------

-- 50.列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资;

-- 51.列出受雇日期早于直接上级的所有员工，显示员工编号、员工姓名、部门名称、上级编号、上级姓名。（三张表查询&自查询）

SELECT e1.id,e1.name,e1.topid,d.name,e2.name -- e1是员工表，e2是上级表
FROM emp e1,emp e2,dept d
WHERE e1.topid=e2.id 
   AND e1.dept_id=d.id 
   AND e1.hdate<e2.hdate;   -- 三张表查询&自查询

说明：连接条件数量至少是表的数量减去1，否则都是笛卡尔查询。

四、扩展

1. SQL注解

      --  单行注解内容（注意：--后面的空格不要省去）；

      /*  多行注解  */

2. 取消当前sql语句的执行用：\c

3. MySQL的数据类型

1）数值类型：

其中decimal数据类型用于在数据库中存储精确的数值。

 

2）字符串类型：

char类型是定长字符串，char(n)中n的范围是0~255个字符；例如name char(10)，存入'贾宝玉'，存入了3个字符，剩余的空间会用空格补全；

varchar类型是变长字符串，不会浪费空间。varchar(n)，n的范围是0~?个字符，varchar最大能存的数据量是0~65535个字节。latin1编码中，1个字符对应1个字节；gbk编码中，1个字符对应2个字节；utf8编码中，1个字符对应3个字节。

               面试题：char和varchar有什么区别？

3）日期类型：

date：日期类型，格式是：年月日

time：时间类型，格式是：时分秒

datetime：日期+时间，格式是：年月日时分秒

timestamp：时间戳，格式和datetime相同，也是：年月日时分秒，和datetime不同的是：①范围上：datetime 范围是1000-9999(年份)； ② 实际存的数据：datetime存的就是一个'年月日 时分秒'格式的日期+时间，而timestamp实际存储的是这个从1970.1.1日到这个日期+时间的时间毫秒值； ③在使用上：timestamp可以设置自动获取当前时间作为值插入到表中，而datetime不可以。

4. mysql的字段约束

主键约束：非空的且不能重复  id int primary key [auto_increment],主键可以是字符串，但不能自增；

非空约束：gender varchar(10) not null,

唯一约束：不能重复，但可以为空；email varchar(50) unique,   

username varchar(50) unique not null,   -- 既不能重复，也不能为空。

5. mysql的外键约束

表示两张表的对应关系，保证了数据的完整性和一致性。思考：如何保存表与表之间的对应关系？

5.1 添加外键约束

（以员工表emp表和部门表dept来说明）

方式一：建表时添加外键

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(20),
  `dept_id` int(11),
  foreign key(dept_id) references dept(id)  -- 指定dept_id为外键
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

建表时添加了外键，当在emp表中添加员工时，dept_id必须是dept表的部门编号才行，否则会报错： Cannot add or update a child row: a foreign key constraint fails......

当删除emp表中的表记录时，会报错：Cannot delete or update a parent row: a foreign key constraint fails......

方式二： 建表后添加外键

alter TABLE emp add constraint fk_dept_id 
foreign key(dept_id) references dept(id);

其中 fk_dept_id (名字由自己定义)，是指外键约束名称，也可以将【constraint
fk_dept_id】省略，MySQL会自动分配一个外键名称，将来可以通过该名称删除外键。
foreign key(dept_id)中的dept_id为外键

执行这一条语句也是得emp表中的dept_id列中的数据在dept表中都有才行，否则会报错：
Cannot add or update a child row: a foreign key constraint fails (`db3000`.`#sql-alter-174c-3`, 
CONSTRAINT `fk_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))

5.2 删除外键约束

首先，使用 show create table 表名;查询含有外键表的建表语句：

其中，fk_dept_id 为自定义的外键的名字，删除这个名字即可删除外键关系。

然后，执行 alter table emp drop foreign key fk_dept_id; 即可。

参考文章：mysql 外键（foreign key）的详解和实例_差不多先生-CSDN博客_mysql 外键

5.3 添加外键约束(多对多)

         打印的讲义上

5.4 级联更新、级联删除

         打印的讲义上

6. 表关系

1对多(多对1)

1对1

多对多：如学生表和老师表，在任何一方添加列保存另一方的主键都不合适，此时可以再创建一张表，在这张表中分别添加两个列stu_id，tea_id，来保存两张表的对应关系。

7. 数据库的备份与恢复

7.1 数据库的备份

1、备份单个数据库

在cmd窗口（未登录、未连接到mysql服务器的界面）中，可以通过如下命令对指定的数据库进行备份：

mysqldump -u用户名 -p密码 库名 > 备份文件的位置

示例1: 对db40库中的数据(表,表记录)进行备份,备份到 d:/db40.sql 文件中

mysqldump -uroot -proot db40 > d:/db40.sql

注意: 1)如果输入命令回车之后没有提示错误,就说明备份成功了!

         2)备份单个数据库,其实只会备份这个库中的表和表记录,并不会备份库本身!

2、备份多个数据库（比如备份两个数据库）

在cmd窗口（未登录、未连接到mysql服务器的界面）中

mysqldump -u用户名 -p密码 --databases 库名1 库名2 ... > 备份文件的位置

示例2: 对db20 和 db40 库中的数据进行备份,备份到 d:/db2040.sql 文件中

mysqldump -uroot -proot --databases db20 db40 > d:/db2040.sql

注意: 1)如果输入命令回车之后没有提示错误,就说明备份成功了!

2)备份多个数据库,不仅会备份这个库中的表和表记录,同时会备份库本身!

或者如果想备份mysql服务器中的所有的库以及库中的表和表记录,可以通过如下命令:

mysqldump -uroot -proot -A > d:/dball.sql

输入完后回车如果没有提示错误(error是错误,警告不是错误可以忽略),即表示备份成功!

7.2 数据库的恢复

1、恢复数据库方式一(单个数据库)：

在cmd窗口中（未登录的状态下），可以通过如下命令对指定的数据库进行恢复

mysql -u用户名 -p密码 库名 < 备份文件的位置

示例1: 将 d:/db40.sql 文件中的数据恢复到 db60 库中

1) 先在cmd窗口中(已登录的状态下), 先创建db60库

create database db60 charset utf8;

2) 再回到cmd窗口中(未登录的状态下), 执行下面恢复的命令

mysql -uroot -proot db60 < d:/db40.sql

2、恢复数据库方式二(多个数据库)

在cmd窗口中(已登录的状态下)，可以通过source命令来执行指定位置的sql文件中的sql语句：

source sql文件的位置

示例2: 将 d:/db40.sql 文件中的数据恢复到 db80 库中

1) 先创建db80库, 并进入到db80库

create database db80 charset utf8;

use db80;

2) 再通过source命令执行 d:/db40.sql 文件中的sql语句

source d:/db40.sql

示例3: 将 d:/db2040.sql 文件中的数据恢复回来

1) 将db20,db40库删除(模拟数据丢失)

drop database db20;

drop database db40;

2) 再通过source命令执行 d:/db2040.sql 文件中的sql语句

source d:/db2040.sql