第 7 讲 mysql 数据库
1、数据库的定义：是存放数据的电子仓库 ==》例子：教室是一个仓库（数据库）==》
里面的桌子可以理解为表==》表里面存储数据
2、关系型数据库：
依据关系模型创建的数据库，把数据保存在不同的表中，表
与表存在着某些关系。
3、非关系型数据库：非关系型数据库也叫nosql数据库,全称not only sql。通常
数据以对象的形式存储在数据库中，不固定结构，例如列模
型，键值对模型。
4、关系型数据库特点
1.安全
2.保持数据的一致性
3.实现对表与表进行复杂的数据查询
5、非关系型数据库特点
1.效率高
2.容易扩展
3.使用更加灵活
6、关系型数据库有哪些
1.db2
2.oracle ==》收费 金融公司
3.mysql ==》开源 免费 体积小
4.sql server ==》微软公司
7、非关系型数据库有哪些
1.hbase(列模型) ==》大数据
2.redis(键值对模型) ==》缓存 ==》中间件
3.mongodb(文档类模型) ==》文档类模型
8、MySQL 数据库的安装
yum install mysql ==》安装数据库的客户端
yum install mysql-server ==》安装MySQL数据库的服务端
rpm -qa|grep -i mysql ==》查看是否安装了MySQL数据库
9、MySQL 数据库的操作
service mysqld status ==》查看数据库的状态（d ==》daemon 守护的进程）
service mysqld start ==》启动数据库
service mysqld stop ==》关闭数据库
service mysqld restart ==》重启数据库
10、登录数据库 ==》第一次登录数据库不需要密码
[root@localhost /]# mysql -u root -p
Enter password: 不需要密码 ==》按enter键
-u 表示user root 用户
-p 表示password 密码
mysql>exit; ==》退出数据库 也可以按ctrl +c 【注意sql语句后需要加英文分号】
[root@localhost /]mysqladmin -uroot password "123456" ==》只能修改初始密码123456
11、[root@localhost /]# mysql -u root -p123456 ==》登录数据库
[root@localhost /]# mysql -u root -p
Enter password: 123456
以上两种登录方式
12、mysql>use mysql; ==》进入到MySQL数据库
13、mysql>show databases; ==》展示所有的库
14、create database +库名
mysql>create databasse dcs11; ==》创建一个名为dcs11的数据库
15、drop database dcs11; ==》删除dcs11这个库
16、select database(); ==》当前在那个库（dcs11）
17、show tables; ==》展示dcs11这个库里面所有的表
18、create table +表名（
字段1名称，数据类型, 约束 ，
字段2名称，数据类型，约束，
字段3名称，数据类型，约束，
create table tab11(id int(10) primary key auto_increment,score float(10,2) not null,
name varchar(10),phone bigint(10) default 13977778888,time date);
desc tab11; ==》查看表结构（desc ==》全称description 描述）
+-------+-------------+------+-----+-------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-------------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| score | float(10,2) | NO | | NULL | |
| name | varchar(10) | YES | | NULL | |
| phone | bigint(10) | YES | | 13977778888 | |
| time | date | YES | | NULL | |
+-------+-------------+------+-----+-------------+----------------+
field ==》字段
type ==》数据类型
key ==》primary key 主键 一个表只能有一个主键，好比身份证
default ==》默认值 如果不给它值，就使用默认值
extra ==》额外的备注
数据库当中的数据类型
数值型
int ==》整型 最大存储的值 2147483647
bigint ==》整型 超过int就是用bigint
float ==》浮点型 float(10,2) 整数部分和小数部分加以来是10位，2精确到2位小数点
文本型 （这两种存储的值需要加单引号或者双引号）
varchar：字符串
char：字符串
区别：
1）char是固定长度的 varchar是可变长度
2）假设char(10)和varchar(10) 同样存储‘abc’ 对于char用个字节，剩下的7个
用空格不上，varchar只会占用3个字节，剩下不会用空格不上
3）char 效率要比varchar高
4）varchar 节省空间
日期型（这两种存储的值需要加单引号或者双引号）
date ==>'2022-10-17'
数据库的约束
约束用于对表中字段进行限制，保证表中数据的正确性和唯一性
约束类型
1 ）primary key 主键约束
说明：非空，唯一，用于唯一标识对应的记录。类似身份证。
2 ）foreign key 外键约束
说明：用于表与表建立关系模型，使表与表紧密的结合起来。
3 ）not null 非空约束
4 ）default 默认值约束
说明：默认给字段指定默认值
5 ）auto_increment 自增约束 ==》只能和主键一起使用
说明：作用在整数类型，字段默认从1开始自增，不给id值，会默认在上一个id的基础上加1
19、修改表结构
1.查看表结构
desc +表名
显示字段名称，数据类型，约束，备注
desc tab11; ==》查看tab11这个表的结构
2.修改表名
alter table +表名 rename + 新表名
alter table tab11 rename tab12 ==》把tab11名称改为tab12
3.修改表字段
alter table +表名 change +原字段名 +新字段名 数据类型，约束
alter table tab11 change id sid int(10); ==》把id字段改为sid并且取消自增长
alter table tab11 change sid id int(10) auto_increment; ==》把sid 改为id加上自增长
4.添加表字段，并放到第一个字段前
alter table +表名 add +字段名 数据类型 约束 first
alter table tab11 add class int(10) first; ==》添加一个字段放在最前面
5.添加表字段，并放到某个字段后
alter table +表名 add +字段名 数据类型 约束 after +字段名
alter table tab11 add sex int(10) after id; ==》添加一个字段sex放在id的后面
6.同时添加两个字段，默认添加到字段最后
alter table +表名 add(字段1 数据类型，字段2 数据类型)
alter table tab11 add(age1 int(10),age2 int(10)); ==》同时添加两个字段age1 age2
7. 删除表字段
alter table +表名 drop + 字段
alter table tab11 drop class;==》把class字段删除
8.删除表两个字段
alter table +表名 drop 字段1,drop 字段2
alter table tab11 drop age1,drop age2;==》同时删除两个字段age1 age2
9.修改主键id为自增长 alter table +表名 change +字段名 数据类型 auto_increment
alter table tab11 change id id int(10) auto_increment;==》不修改id字段增加自增长
10.删除主键
alter table tab11 change id id int(10); ==》先删除自增长
alter table tab drop primary key; ==》删除主键
11.alter table tab11 change id id int(10) primary key auto_incerment; ==》同时增加主键和自增长
10.删除表drop table +表名
drop table tab11; ==》删除tab11这个表
20、数据库增删改查
1）增加 insert into +表名
单条数据插入
insert into tab11(id,score,name,phone,time)values(1,66.88,'xiaoduan',13988886666,'2022-5-31');
insert into tab11 values(1,88.66,'xiaoliu',13466668888,'2022-5-31');
多条数据插入
insert into tab11(id,score,name,phone,time)values(3,66.88,'xiaoduan',13988886666,'2022-5-31'),
(4,66.33,'xiaoyi',13456789876,'2022-5-31'),(5,66.66,'xiaduan',13499998888,'2022-1-3');
insert into tab11 values(6,66.34,'xiaoming',13666668888,'2022-5-12'),(7,66.99,'xiaohe',13866667777,'2022-5-30'),
(8,66.68,'xiaohu',13734567890,'2022-5-31');
单个字段插入
insert into tab11(name)values('xiaotao');==》只给name 插入字段时，id字段自动加一，
可以为空的字段以null填充，score字段以0.00填充
insert into tab11(score)values(66.7899);==》只给score 插入字段时
0 不等于null，0时一个值，null是一个属性，空
数据库怎么去测试？
数据库类型
约束类型
存储的大小 边界值
2）删 delete from 表名
delete from tab11 where id =10;==》把id等于10 的数据删除
delete from tab11 where id > 6;==》把id大于6的数据删除
truncate tab11;==》快速删除全表数据
注：TRUNCATE,DELETE,DROP放在一起比较：
TRUNCATE TABLE：快速删除表数据。
DELETE TABLE: 按条件删除表数据 和where 条件使用
DROP TABLE：删除表结构和表数据
3）改 update 表名 +set 字段= ？
update +表名 set 字段名=值 where 条件
update tab11 set name = 'xiaoduan' where id = 4;==》把id等于4的数据姓名改为xiaoduan
update tab11 set name = 'xiaoduan'; ==》把全表的数据姓名字段改为xiaoduan
update tab11 set score = 100 where id = 1;==》把id等于1的score分数改为100分
4）查
1.查询表中所有数据
select * from +表名
”*“ 代表所有
select * from tab11; ==》查询tab11这个表的全部数据
2. 查询某个字段的数据
select 字段 from +表名
select id from tab11; ---》查询tab11表中id 字段的值
3. 查询多个字段的数据
select 字段1，字段2 from +表名
select id，name，score from tab11;---》查询tab11表中 id name score 字段值
创建stu
/*create table stu(id int(4) primary key,age int(8),sex int(4),name varchar(20),class int(4),math int(4));
insert into stu values(1,25,1,"zhangsan",1833,90),
(2,25,1,"lisi",1833,67),(3,28,0,"xiaowang",1835,79),(4,35,1,"xiaoliu",1835,96),(5,27,0,"xiaoli",1833,86),
(6,32,1,"xiaochen",1835,48),(7,22,1,"wangwu",1834,70),(8,31,0,"xiaoqi",1825,88),
(9,27,0,"xiaoqi",1833,74),(10,27,1,"niuqi",NULL,80);
*/
4.查询满足某个条件的所有数据
select * from +表名 where 字段=值
where 后面接满足的条件
select * from stu where id = 1; ==》查询id为1的数据
5.查询不满足某个条件的所有数据
select * from +表名 where 字段！=value
“！=
” 代表不等于 ，也可以用符号 ”<>“ 代表不等于
#查询sex 字段不等于1的数据
#select * from stu where sex <>1;(select * from student where sex != 1;)
6.查询同时满足多个条件数据
select * from +表名 where 条件1 and 条件2
and 关键字左右的两个条件必须同时满足
select * from stu where class = 1833 and sex = 1; ==》查询class为1833并且sex为1的数据
7.查询满足至少1个条件的数据
select * from +表名 where 条件1 or 条件2
or 关键字左右的两个条件至少满足1个，否则返回空
#查询class为1833或者class为1835的数据
select *from stu where class = 1833 or class = 1835;
select * from stu where class in (1833,1835);
8.查询一个条件范围内的数据
select * from +表名 where 字段 between m and n
between...and ... 指定一个范围
select * from stu where age between 25 and 31; #查询年龄在25和31 之间的数据
9查询字段满足在指定的集合中的数据
select * from +表名 where 字段 in(值1，值2，值3)
select * from stu where class in (1833,1835); ==》#查询class为1833或者class为1835的数据
10查询字段不满足在指定集合中的数据
select * from +表名 where 字段 not in (值1，值2，值3)
select * from stu where id not in (1,3); ==》查询id 不在1和3里面的数据
11.查询字段值为空的数据
select * from +表名 where 字段 is null
注意：字段是空不能写成 字段=null
select * from stu where class is null; ==》查询班级为空的数据
注：0不等于null，0是一个值， null 是一个空属性，所以用is
12.查询字段不为空的数据
select * from +表名 where 字段 is not null
select * from stu where class is not null; ==》查询class 字段不为空的数据
13.查询某个字段模糊匹配成功的数据
select * from +表名 where 字段 like “%值%”
%用于匹配字段开头和结尾
select *from stu where name like 'ci%'; ==》查询姓名以“ci”开头的数据
select * from stu where name like '%qi'; ==》查询姓名以‘qi’结尾的数据
select * from stu where name like '%qi%'; ==》查询姓名含有‘qi’的数据
14.查询限定的数量的数据
select * from +表名 where 字段 limit m,n
m 指下标，n指限定的数量，下标为m的开始的n条数据
前5行 ==》1到5行 ==》limit 0,5 就是前面这个数1-1=0， 然后减完后+？后面这个数5 ==》0+5=5
查看2到6行的数据 ==》limit 1,5 ==>2-1 =1 1+?=6 ==》1+5=6
查看4，7行的数据 ==》limit 3,4
#查询前5行的数据
#select * from stu limit 0,5; (select * from student limit 5;) --》一般数据库从0开始算起的
15.查询的数据根据某个字段从小到大排序
select * from +表名 order by 字段 asc
order by ...asc 从小到大排序
select * from stu order by id asc; （ascend） ==》#查询表数据按id从小到大排序
16.查询的数据根据某个字段从大到小排序
select * from +表名 order by 字段 desc
order by ... desc 从大到小排序
select * from stu ORDER BY id desc; （descend） ==》#查询表数据按id从大到小排序
17.查询的数据根据某个字段进行分组
select * from +表名 group by 字段
group by ... 根据条件分组
select class,count(*) from stu group by class; ==》#根据班级class字段分组、然后求出每个班级的人数
帮class和count(*)分别取别名a和b
select class as a,count(*) as b from stu group by class;
帮class和count(*)分别取别名"班级"和"每个班级总人数"
elect class as 班级,count(*) as 每个班级总人数 from stu group by class;
18.查询的数据根据某个字段进行分组再条件过滤
select * from +表名 group by 字段 having 条件
having跟在group by 后面，作用相当于where
求出每个班的数学总成绩大于200的班级和成绩信息
select class,sum(math) from stu group by class having sum(math)>200;
mysql聚合函数
19. 统计查询数据的数量
select count(*) from +表名
20. 查询某个字段求和
select sum(字段) from +表名
21. 查询某个字段进行平均值
select avg(字段) from +表名
22. 查询某个字段最大值
select max(字段) from +表名
23. 查询某个字段最小值
select min(字段) from +表名
24 对某个字段进行去重
select distinct(字段) from +表名 ==》select distinct(sex) from stu; ==》查询按性别去重
题目：
求每个班的数学总分
select class,sum(math) from stu group by class;
求每个班的数学平均分
select class,avg(math) from stu group by class;
求每个班的数学最低分
select class,min(math) from stu group by class;
求每个班的数最高分
select class,max(math) from stu group by class;
题目：
求出每个班级的数学成绩大于80的人数
select class,count(*) from stu where math >80 group by class;
求出每个班中性别为1的数学总成绩
select class,sum(math) from stu where sex = 1 group by class;
求出每个班的数学总成绩大于200的班级和成绩信息
select class,sum(math) from stu group by class having sum(math)>200;
【单表查询总结】
1、分组函数group by 只能和聚合函数一起使用，要加只能加分组字段
2、where 后面可以接group by 分组函数，但是group by 后面不能跟where
3、group by 前面加where是先过滤在分组
4、having 可以接在group by 后面，类似于where ，筛选分组后满足条件的
求出每个班英语成绩最高的那个人的姓名和班级名称 --每个班英语成绩 最高
select class,max(english) from grade group by class; t
grade g
select g.name,g.class from grade g,(select class,max(english)m from grade group by class)t
where g.class = t.class and g.english = t.m;
21、[数据库的备份]
1）备份表
create table tab12 like tab11; ==》创建一个tab12表，和tab11表一样的结构（只是备份结构）
insert into tab12 select * from tab11; ==》往tab12表中插入和tab11表一样的数据（全部数据）
insert into tab12(id,name,phone)select id,name,phone from tab11;
==》往tab12 表中插入和tab11 表中id name phone 一样的数据
注意点
1. 插入的表必须存在
2. 插入的表是新表,没有数据。
2）备份库
mysqldump -uroot -p123456 dcs11 >/dcs11/dcs11_bak ==》把dcs7这个库备份到根目录下dcs11下名为dcs11_bak
mysql -uroot -p123456 dcs11 </dcs11/dcs11_bak ==》把根目录下dcs11下名为dcs11_bak 还原到dcs11这个库里面
22、数据库权限
1、use mysql; ==》使用MySQL数据库
2、select host,user from user; ==》查看mysql库用户表有哪些用户
localhost 和127.0.0.1表示本地用户
% 表示具有远程访问的权限用户
3、insert into user(host,user,password)values('localhost','dcs11',password('123456'));
==》往user表插入一个dcs11用户，没有赋权
flush privileges; ==》刷新权限
4、show grants for 'dcs11'@'localhost'; ==》查看dcs11这个用户是否具有权限
GRANT USAGE ON *.* TO 'dcs11'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
==》没有权限
5、grant select,update,delete,alter,drop on *.* to 'dcs11'@'localhost' identified by '123456';
==》给dcs11这个用户进行赋权
6、flush privileges; ==》刷新权限
7、revoke all on *.* from 'dcs11'@'localhost'; ==》移除本地用户dcs11的所欲权限
flush privileges; ==》刷新权限
show grants for 'dcs11'@'localhost'; ==》查看dcs11这个用户的权限
8、update user set password=password('654321') where user = 'dcs11'; ==》更改dcs11这个用户的密码
flush privileges; ==》刷新权限
9、delete from user where user = 'dcs11'; ==》删除dcs11这个用户
【多表连接】
navicat ==》是连接数据库的客户端
怎么用navicat去连接数据库
1）自定义一个连接名
2）输入ip地址
3）端口3306 ==》mysql数据库的默认端口 oracle 默认端口1521 redies 默认端口 6379
4）账号
5）密码
如果连接不上
1）service mysqld restart 重启数据库
2）service iptables stop 关闭防火墙
3）检查登录用户是否有 %权限 （grant all privileges on *.* to 'root'@'%' identified by '123456'）
4)检查是否填写有误
快捷键
ctrl+q ==》新建一个窗口
ctrl+w ==》关闭窗口
CTRL+r ==》运行语句
ctrl+鼠标 放大缩小
运行三种
1）左上角绿色三角形
2）ctrl+r
3）鼠标右键 选中
注释
# ==》单行注释
/* */ ==》多行注释
段注释/*
1、基本连接 ==》2张表中有字段值相同则可以进行连接，结果显示相同字段值的数据
select * from aa,cc where aa.id=cc.s_id;
2、内连接 ==》2张表中有字段值相同则可以进行连接，结果显示相同字段值的数据
select* from aa inner join cc on aa.id = cc.s_id;
3、左连接 ==》以左表为主，显示左表的全部数据，右表没有的数据以null填充
aa是左表 cc是右表
select * from aa left join cc on aa.id = cc.s_id;
4、右连接 ==》以右表为主，显示右表的全部数据，左表没有的数据以null填充
aa是右表 cc左表
select * from cc right join aa on aa.id = cc.s_id;
5、硬链接 （条件：2张表的字段必须相同）
select * from aa union select * from cc;
求张三的成绩？
select score from aa,cc where aa.id=cc.s_id and name = 'zhangsan';
select score from aa inner join cc on aa.id = cc.s_id where name = 'zhangsan';
select score from aa left join cc on aa.id = cc.s_id where name = 'zhangsan';
6、临时表法 先连表 select * from aa,cc where aa.id=cc.s_id;
select score from (select * from aa,cc where aa.id=cc.s_id)t where name = 'zhangsan';
求张三的成绩？
7、嵌套法 （in ）
select id from aa where name = 'zhangsan'; ==>1001
select score from cc where s_id in (select id from aa where name = 'zhangsan');
8、嵌套 法 =
select id from aa where name = 'zhangsan'; ==>1001
select score from cc where s_id =(select id from aa where name = 'zhangsan');
求出谁没参加考试？
select name from aa LEFT JOIN cc on aa.id = cc.s_id where score is null;
select s_id from cc; ==》参加的学生 1001
select name from aa where id not in (select s_id from cc);
【处理mysql数据库中文乱码的问题】
1、vim /etc/my.cnf ==》数据库的配置文件
2、加入这行character_set_server=utf8
3、重启数据库
4、dcs这个库的数据库属性把Latin改为utf8编码格式 （1.字符集：utf8 -- UTF-8 Unicode 2.排序规则：utf8_general_ci）
==》重新创建表就可以看到中文了
|
|
228
0
多测师社区 
