MySQL数据库（一）基础入门

admin 管理员组

文章数量: 887021

MySQL数据库（一）

一·、基本知识

数据库是长期存储在计算机内，有组织，可共享的数据集合。

特点：

1、实现了数据的独立性

2、实现了数据共享

3、减少了数据冗余度

4、实习了数据的集中控制

5、维护了数据的完整性

数据模型

概念数据模型

逻辑数据模型

物理数据模型

数据模型三要素

数据结构、数据操作、完整性约束条件

数据库类型

1、层次模型数据库

类似有向树

2、网状模型数据库

类似蜘蛛网

3、关系模型数据库

二维表结构：数据库、表、记录、字段。

MySQL是常见的关系型数据库管理系统

阿里云安装docker docker 安装mysq数据库

CentOS Docker 安装

Docker 支持以下的 64 位 CentOS 版本：

• CentOS 7
• CentOS 8
• 更高版本…

使用官方安装脚本自动安装

安装命令如下：

curl -fsSL https://get.docker | bash -s docker --mirror aliyun

也可以使用国内 daocloud 一键安装命令：

curl -sSL https://get.daocloud.io/docker | sh

手动安装

卸载旧版本

较旧的 Docker 版本称为 docker 或 docker-engine 。如果已安装这些程序，请卸载它们以及相关的依赖项。

 $ **sudo** **yum remove** docker \
          docker-client \
          docker-client-latest \
          docker-common \
          docker-latest \
          docker-latest-logrotate \
          docker-logrotate \
          docker-engine

安装 Docker Engine-Community

使用 Docker 仓库进行安装

在新主机上首次安装 Docker Engine-Community 之前，需要设置 Docker 仓库。之后，您可以从仓库安装和更新 Docker。

设置仓库

安装所需的软件包。yum-utils 提供了 yum-config-manager ，并且 device mapper 存储驱动程序需要 device-mapper-persistent-data 和 lvm2。

 $ **sudo** **yum install** -y yum-utils \
  device-mapper-persistent-data \
  lvm2

使用以下命令来设置稳定的仓库。

使用官方源地址（比较慢）

 $ **sudo** yum-config-manager \
   --add-repo \
   https:**//**download.docker**/**linux**/**centos**/**docker-ce.repo

可以选择国内的一些源地址：

阿里云

 $ **sudo** yum-config-manager \
   --add-repo \
   http:**//**mirrors.aliyun**/**docker-ce**/**linux**/**centos**/**docker-ce.repo

清华大学源

 $ **sudo** yum-config-manager \
   --add-repo \
   https:**//**mirrors.tuna.tsinghua.edu**/**docker-ce**/**linux**/**centos**/**docker-ce.repo

安装 Docker Engine-Community

安装最新版本的 Docker Engine-Community 和 containerd，或者转到下一步安装特定版本：

$ sudo yum install docker-ce docker-ce-cli containerd.io

如果提示您接受 GPG 密钥，请选是。

有多个 Docker 仓库吗？

如果启用了多个 Docker 仓库，则在未在 yum install 或 yum update 命令中指定版本的情况下，进行的安装或更新将始终安装最高版本，这可能不适合您的稳定性需求。

Docker 安装完默认未启动。并且已经创建好 docker 用户组，但该用户组下没有用户。

要安装特定版本的 Docker Engine-Community，请在存储库中列出可用版本，然后选择并安装：

1、列出并排序您存储库中可用的版本。此示例按版本号（从高到低）对结果进行排序。

$ **yum list** docker-ce --showduplicates **|** **sort** -r

 docker-ce.x86_64  3:18.09.1-3.el7           docker-ce-stable
 docker-ce.x86_64  3:18.09.0-3.el7           docker-ce-stable
 docker-ce.x86_64  18.06.1.ce-3.el7           docker-ce-stable
 docker-ce.x86_64  18.06.0.ce-3.el7           docker-ce-stable

2、通过其完整的软件包名称安装特定版本，该软件包名称是软件包名称（docker-ce）加上版本字符串（第二列），从第一个冒号（:）一直到第一个连字符，并用连字符（-）分隔。例如：docker-ce-18.09.1。

$ sudo yum install docker-ce-<VERSION_STRING> docker-ce-cli-<VERSION_STRING> containerd.io

启动 Docker。

$ sudo systemctl start docker

通过运行 hello-world 映像来验证是否正确安装了 Docker Engine-Community 。

$ sudo docker run hello-world

卸载 docker

删除安装包：

yum remove docker-ce

删除镜像、容器、配置文件等内容：

rm -rf /var/lib/docker

docker安装mysql

1、查询MySQL可以安装版本 docker search mysql

2、拉取MySQL镜像最新版 docker pull mysql:latest

3、查看镜像 docker images

4、运行容器（端口号映射 以及root 密码）

docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql

云服务器好像要设置安全组（开启3306端口 这要navcat能连接上数据库）

如果出现端口号被占用（查看端口号使用的进程，杀死该进程）

5、查看docker 运行中的容器 docker ps

6、进入MySQL mysql -h 47.108.170.87 -u root -p 你自己的ip

7、navicat 连接云服务器的数据库

7、1设置安全组规则，开启3306端口、允许你电脑ip能访问 设置你的ip 也可以设置0.0.0.0所有能都能访问。

7、2进入docker里面MySQL数据库设置允许远程连接的密码

navicat新建mysql连接输入你的ip和密码，连接就可以了。

进入docker mysql: docker exec -it mysql3307(你的mysql docker容器名字) /bin/bash

5.7设置远程连接

GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘root’ WITH GRANT OPTION;

flush privileges;刷新权限

8.0设置远程连接

mysql>use mysql;

//修改8.0密码策略
mysql> ALTER USER ‘root’@’%’ IDENTIFIED WITH mysql_native_password BY ‘root’;

//重新修改密码后可连接成功
mysql> alter user ‘root’@’%’ identified by ‘123456’;

//刷新数据库
mysql> flush privileges;

二、基本命令

1、数据库CRUD

创建数据库（不能存在才能创建，并且设置字符集以及排序规则）

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

修改数据库名

rename database oldname TO newname；8.0之前的版本；

8.0之后

1）使用mysql的图形客户端Navicat for MySQL，然后连接好本地mysql服务器

2）右键点击需要修改名字的数据库 -> 转存SQL文件 -> 结构和数据... -> 导出world.sql文件 -> 在进度表中显示Finished - Successfully，表示导出成功

3）在world.sql找到Source Database : world，然后将这里world改为new_world。

4）右键 -> 新建名为new_world数据库 -> 运行SQL文件 -> 选择刚刚的world.sql文件 -> 点击开始（等到提示Finished - Successfully，表示导入成功了）

删除数据库

drop database test;

使用数据库

use test；

2、单表字段操作

创建数据表(先要使用数据库)

没有约束条件

create table student (id int(11),name varchar(11),age int(3));

某学生姓名有非空约束

create table test(student_id int(11) not null,student_name varchar(25) not null);

修数据表名

rename table test to student;

删除数据表

drop table student;

查询数据所有表

show tables;

查看数据表中字段名字与类型

第一种

desc student;

第二种

show columns from student;

1、使用SQL语句设置约束条件

#主键约束：PRIMARY KEY 要求被装饰的字段：唯一和非空

#唯一约束：UNIQUE 要求被装饰的字段：唯一，

#联合唯一：在结尾：unique(字段1，字段2)

#非空约束：NOT NULL 要求被装饰的字段：非空

#外键约束：FOREIGN KEY 某主表的外键

#自动增加：AUTO_INCREMENT 自动增加(需要和主键 PRIMARY KEY 同时用)

#设置默认值:DEFAULT 为该属性设置默认值

#在int、char中：zerofill 不足位数默认填充0

创建一张乘客表（t_passenger）

字段 类型 约束
编号（pk_id） 整型 主键 自动增长
乘客姓名（name） 字符串（20） 非空
年龄（age） 整型
性别（gender） 字符串（3） 非空 默认为"男"
出生日期（birthday） 日期类型 非空

create table t_passenger(
pk_id int(11) primary key auto_increment,
name varchar(11) not null,
age int(11),gender varchar(3) not null default "男",
birthday datetime not null);

第一种创建主键方式在创建表时创建

第二种添加主键

 alter table t_passenger primary key(pk_id);

添加外键约束

创建表时和后面添加外键约束

CREATE TABLE score1(
    score DOUBLE,
    sid INT,
    CONSTRAINT fk_stu_score1_sid FOREIGN KEY(sid) REFERENCES stu(sid)
);

ALTER TABLE score1 ADD CONSTRAINT fk_sid FOREIGN KEY(sid) REFERENCES stu(sid)

添加字段

ALTER TABLE student ADD grade int(11);

修改字段名，类型

ALTER TABLE student change grade score int(11);

修改字段类型

alter table student modfiy score int(11);

删除字段

alter table student drop score;

3、单表CRUD

添加数据

insert into study(student_id,student_name,grade)values(1831310109,"王一辉",78);

修改数据

updata study set grade=89 where student_id = 1831310109;

删除数据

delete from study where student_id = 1831310109;

1、简单查询

书写顺序

select distinct * from ‘表名’ where ‘限制条件’ group by ‘分组依据’ having ‘过滤条件’ order by limit ‘展示条数’ 执行顺序

from – 查询

where – 限制条件

group by – 分组

having – 过滤条件

order by – 排序

limit – 展示条数

distinct – 去重

select – 查询的结果

正则：select * from emp where name regexp ‘^j.*(n|y)$’;

集合查询：max 、min 、avg 、sum 、count 、group_concat 。

内连接：inner join

左连接：left join

右连接：right join

全连接： 左连接 union

右连接 replace 替换

注意：

执行顺序：

1. FROM, including JOINs
2. WHERE
3. GROUP BY
4. HAVING
5. WINDOW functions
6. SELECT
7. DISTINCT
8. UNION
9. ORDER BY
10. LIMIT and OFFSET

模糊查询

select * from study where student_name like "王%";

查询以王开头的学生姓名

mysql中有两个通配符 _ 和 %

_一个字符（不包括0字符）

%多个字符（包括0字符

排序查询

select * from 表名 order by field1,field2 desc;  

降序

select grade from study order by grade desc;

±-------------±-------------+

| student_name | count(grade) |

±-------------±-------------+

| 王一辉 | 2 |

| 王一 | 1 |

| 李白 | 1 |

| 杜甫 | 1 |

条件查询

select student_name,count(grade) as sum from study group by(student_name) having sum=2;

±-------------±----+

| student_name | sum |

±-------------±----+

| 王一辉 | 2 |

±-------------±----+

1 row in set (0.00 sec)

查询为空：

select * from study where student_id is null;

查询不为空：

select * from study where student_id is not null;

算术查询

select count as 需要统计总数的字段名 from 表名             -- 总数
select sum(field1) as sumvalue from 表名               -- 求和
select avg(field1) as avgvalue from 表名               -- 平均
select max(field1) as maxvalue from 表名               -- 最大
select min(field1) as minvalue from 表名               -- 最小

2、where查询

1.where 查询
# between 在...之间
select id,name from emp where id >= 3 and id <= 6;
相当于：
select *  from emp where id between 3 and 6;  

# or 或者
select *  from emp where id >= 3 or id <= 6;

# in，后面可以跟多个值
select * from 表名 where 字段名 in (条件1,条件2,条件三); 

# like （见上18）
# char——length()  取字符长度
select * from 表名 where char_length(需要获取长度的字段名) = 4;

not 配合使用
注意：判断空不能用 = ，只能用 is 

3、group by分组查询

select 查询字段1,查询字段2,... from 表名
      where 过滤条件
      group by分组依据  # 分组后取出的是每个组的第一条数据
      
select student_name,count(grade) from study group by(student_name);

3.聚合查询 ：以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)

max 最大值

每个部门的最高工资 select post,max(salary) from emp group by post;

min 最小值

每个部门的最低工资 select post,min(salary) from emp group by post; 

avg 平均值

每个部门的平均工资 select post,avg(salary) from emp group by post;

每个部门的平均工资 select post,avg(isnull(salary,0))from emp group by post;

sum每个部门的工资总和

求和 select post,sum(salary) from emp group by post;

count（需要计数字段） 计数

(*/列名/distinct 列名)

每个部门的人数 select post,count(id) from emp group by post; 

group_concat(需要分组后的字段)

不仅可以用来显示除分组外字段还有拼接字符串的作用 select post,group_concat(name) from emp group by post; – post：分组字段，name 需要分组后显示的字段 拼接： concat（不分组时用）拼接字符串达到更好的显示效果 as语法使用

举例：

select concat("NAME: ",name) as 姓名 from emp;      concat_ws: 如果拼接的符号是统一的可以用 

举例：

 select concat_ws(':',name,age,sex) as info from emp;  group_concat：   

举例：

select post,group_concat(name,'DSB') from emp group by post;      

补充：as语法 起别名 select name as 姓名,salary as 薪资 from emp;

4.having 过滤查询

having的语法格式与where一致，只不过having是在分组之后进行的过滤，即where虽然不能用聚合函数，但是having可以！

用法

select 查询字段1,查询字段2,... from 表名      

where 过滤条件1      group by分组依据 having avg(过滤条件2) > 10000; 

5.distinct 去重

对有重复的展示数据进行去重操作 select distinct 需取重字段 from 表名;

6.order by 排序

select * from emp order by salary asc; #默认升序排 select * from emp order by salary desc; #降序排 # 多条件排序 #先按照age降序排，在年轻相同的情况下再按照薪资升序排 select * from emp order by age desc,salary asc;

7.limit 限制展示条数

限制展示条数 select * from emp limit 3;
查询工资最高的人的详细信息 select * from emp order by salary desc limit 1; 
分页显示 select * from emp limit 0,5; 
第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置 select * from emp limit 5,5;

8.正则

select * from emp where name regexp '^j.*(n|y)$';

9.replace 替换

replace(str1,old,new) – str1：需要替换的字段名 update gd_km set mc=replace（mc,‘土地’,‘房子’） 说明：new替换str1中出现的所有old，返回新的字符串，如果有某个参数为NULL，此函数返回NULL 该函数可以多次替换，只要str1中还有old存在，最后都被替换成new 若new为空，则删除old

10、练习

商品表
create table t_shop(
	s_id int primary key auto_increment,
	s_shopcode varchar(30),    -- 商品编号
	s_name varchar(40),        -- 商品名称
	s_price int ,              -- 商品价格
	s_class   varchar(50)      -- 商品类别
);
insert into t_shop(s_shopcode,s_name,s_price,s_class) values 
('n11','橙子',9,'水果'),
('x330','血橙',11,'水果'),
('yx673','柚子',7,'水果'),
('n12','白菜',2,'蔬菜'),
('a13','冬瓜',3,'蔬菜'),
('n14','西瓜',4,'水果'),
('n15','丝瓜',5,'蔬菜'),
('c16','苦瓜',6,'蔬菜'),
('m17','南瓜',5,'蔬菜'),
('d18','茄子',6,'蔬菜');

-- 1  查询所有包含瓜的商品名称信息
select * from t_shop where s_name like '%瓜%';
-- 2  查询价格在4 到8 的所有商品信息
select * from t_shop where s_price >= 4 and s_price <= 8;
-- 3  查询商品的最高价格的值是多少
select max(s_price) from t_shop;
-- 4  查询商品价格最高的前三个商品的信息
select * from t_shop order by s_price desc limit 0, 3;
-- 5  查询所有商品的平均价格
select avg(s_price) from t_shop;
-- 6  查询所有包含瓜的商品的平均价格
select avg(s_price) from t_shop where s_name like '%瓜%';
-- 7  查询最高商品的价格是最低商品的价格的倍数是多少
select max(s_price)/min(s_price) from t_shop;
-- 8  查询商品名称中包含橙字的有多少个商品
select count(0) from t_shop where s_name like '%橙%';
-- 9  修改 西瓜的价格为2块
 update t_shop set s_price = 2 where s_name = '西瓜';
-- 10  删除id 为，4,9,1 的商品信息
delete from t_shop where s_id in (1,9,4);
-- 11  查询蔬菜类别中最高的价格是多少？
select max(s_price) from t_shop;

姓名	年龄	性别	学号	笔试成绩	机试成绩
Jacky	20	男	xh1001	   90		70
Simth	30	男	xh1002	   75		80
Jay	18	男	xh1003	   80		92
Helen	19	女	xh1004	   75		85
lily	22	女	xh1005	   90		95
Green	23	男	xh1006	   85		85
RedChar	18		xh1007	   60		55
Kevin	17		xh1008	   45		50

1.统计学员的数量
select count(0) from student;
2.查询笔试最高分，机试最高分，笔试平均分，机试平均分，笔试总分，机试总分，分别用别名显示出来
select max(write_test) '笔试最高分' from student;
select avg(write_test) '笔试平均分' from student;
select avg(mac_test) '机试平均分' from student;
select sum(write_test) '笔试总分' from student;
 select sum(mac_test) '机试总分' from student;
3.统计笔试和机试 都不及格的人数
select count(*) from student where write_test < 60 and mac_test < 60;
4.统计笔试和机试都上80分的男生人数
 select count(*) from student where write_test >= 80 and mac_test >= 80 and sex = '男';
5.按笔试成绩从高到低 排序 查出前5名的数据
select * from student order by write_test desc limit 0,5;
6.按笔试成绩和机试成绩 从高到低，排序 查出所有数据
select * from student order by write_test,mac_test desc;
7.查询名字为“J”开头的学生信息
 select * from student where name like 'J%';
8.查询性别为空的数据
select * from student where sex is null;
9.查询名字中有“e”字符的学生信息
 select * from student where name like '%e%';
10.查询出笔试成绩在80 85 90的学生信息
select * from student where write_test in (80,85,90);

4、多表连接查询

1、连接查询

/* 1左连接 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;

/* 2右连接*/
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 3内连接 */
SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;

/* 4 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;

/* 5 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

/* 6 全连接*/
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 1+2 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 7 */
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 4+5 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

2、内连接查询

select * from grade where score = (select max(score) from grade where courseId = jx101)

本文标签： 入门 数据库 基础 MySQL