MySQL学习篇-CRUD之一

掌握MYSQL的基本数据库,表及表中的记录的操作

MySQL乱码问题分析：

MySQL乱码问题：

数据存取分析：

MySQL数据简介

完成对MYSQL数据库中的数据库,表及数据的CRUD的操作
    C:Create
    R:retrieve
    U:update
    D:delete

数据库概述:
什么数据库: 存放数据和管理的仓库。
    数据库就是一个文件系统,只不过我们需要通过命令(SQL)来操作这个文件系统，数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。数据库是长期储存在计算机内、有组织的、可共享的数据集合。数据库中的数据指的是以一定的数据模型组织、描述和储存在一起、具有尽可能小的冗余度、较高的数据独立性和易扩展性的特点并可在一定范围内为多个用户共享。

常见的数据库:
    关系型数据库:
mysql : 小型.开源免费,大多数中小型互联网公司都在使用这个,sun公司收购了 (集群),5.5以前的都是免费的,6.X之后的版本都是收费的
mariadb: 开源免费数据, 和mysql基本上一模一样,但是它是免费的,并且在不停的更新,是mysql开源版本的一个分支
Oracle : 中大型的数据库,服务收费的,安全稳定. 证券,银行,保险公司, Oracle收购了sun公司, mysql6.x之后就开始收费了
SQL Server : 大学里面教学使用比较多, 政府官网, 图形化界面做的很好 c#
DB2 : IBM公司, 主要运用在银行系统, 服务器软硬件解决方案
sybase : powerdesinger 数据建模工具
SQLite : 运用在嵌入式系统中的小型数据库,android
查看数据库排名网站: https://db-engines.com/en/ranking

关系型数据库: E-R关系图
E:Entity实体
R:relation关系
主要是用来存储实体与实体之间的关系:
例如用户和订单之间的关系,订单和商品之间的关系,学生和班级. 学生和课程 老师和学生等
请画出用户和订单之间的E-R关系图

MYSQL的安装和卸载
卸载
如果已经安装,先停止服务 输入service.msc 找到MySQL 停止服务
在控制面板中删除mysql
要将安装路径下的: C:\Program Files\MySQL 文件夹全部删除
要将C:\ProgramData\MySQL 文件夹删除
注意:如果删除完之后,最好到注册表查看是否还有所残留,如果都删除完,建议电脑重启后安装
注册表进入方式 window + r–>运行窗口输入 regedit –> ctrl+f 搜索MySQL(全文匹配)
安装
字符集必须是设置为UTF8
include bin dirctory in windows path 前面的勾必须勾上
密码必须得自己记住
MYSQL的SQL语句
SQL: Structrued Query Langauage 结构化的查询语言
DDL: 数据定义语言 : 定义数据库或者表结构. alter(修改) create(创建) drop(删除) truncate(截断)
DML: 数据操纵语言: 用来操作表中数据: insert(插入) update(更新) delete(删除)
DQL: 数据查询语言: 用来查询数据 select(选择) from(从哪里查) where(哪一条,判断条件)
DCL: 数据控制语言: 用来控制用户权限 grant(授权) revoke(取消授权)
数据库管理员（Database Administrator，简称DBA），是从事管理和维护数据库管理系统(DBMS)的相关工作人员的统称，属于运维工程师的一个分支，主要负责业务数据库从设计、测试到部署交付的全生命周期管理。
登录数据库:
方式一: mysql -u root -p 按回车后提示后输入密码
方式二:mysql -uroot -p自己的密码
完整格式:mysql -h主机地址 -u用户名 -p密码
连接本机:
mysql -hlocalhost -uroot -p123456
mysql -h127.0.0.1 -uroot -p123456
注意:不写-hlocalhost代表默认访问本机数据库服务器
-u username 用户名
-p password密码

数据库的CRUD的操作

  CRUD是指在做计算处理时的增加(Create)、读取查询(Retrieve)、更新(Update)和删除(Delete)几个单词的首字母简写。主要被用在描述软件系统中数据库或者持久层的基本操作功能。
创建数据库
查看数据库
修改数据库的操作
删除数据库
其他数据库操作命令
create database 数据库的名字 ‐‐‐用的非常多的方式
create database day08_1;
create database 数据库的名字 character set 字符集; ‐‐ 了解
create database day08_2 character set gbk;
create database 数据库的名字 character set 字符集 collate 校对规则; ‐‐ 了解
create database day08_3 character set utf8 collate utf8_bin;
‐‐ 查看数据库定义的语句: 查询的是数据库创建的语句
show create database 数据库的名字
‐‐查看所有数据库
show databases;
‐‐修改数据的字符集
alter database 数据库的名字 character set 字符集
drop database 数据库名字;
‐‐切换数据库(选中数据库)
use 数据库名字
‐‐当前选中是哪个数据库,当前正在使用哪个数据库
select database(); DBA 数据库管理员

表的CRUD操作

 语法:
create table 表名(
列名 列的类型 [列的约束],
列名 列的类型 [列的约束]
);
列名:也称为字段名
列的类型 分为如下:
数字类型
整数: tinyint、smallint、mediumint、int、bigint
浮点数: float、double、real、decimal
日期和时间: date、time、datetime、timestamp、year
字符串类型
字符串: char、varchar
文本: tinytext、text、mediumtext、longtext
二进制(可用来存储图片、音乐等): tinyblob、blob、mediumblob、longblob
对比Java中的类型:列的类型
java mysql
byte TinyInt
short SMALLINT
int int
float float
double double
String char(长度)/varchar(长度)
char(10): 固定长度字符 输入hello 没有填满用空格补齐10个
char(5):haha存储的时候不足会补齐空格
varchar(10):可变长度字符 要存储hello 只占5个字符,在数据库存
储的时候就存储5个,不会以空格补齐
都不能超过最大长度
可以存放10个汉字 ,10个字符
char 和varchar:区别主要体现在存储字符时候,如果是char存储类型,
不足的字符会以空格存储,varchar则不会
boolean boolean
Date date : YYYY‐MM‐DD 年/月/日
time : hh:mm:ss 时:分:秒
datetime: YYYY‐MM‐DD hh:mm:ss 默认值是null 年/月/日 时/分/
秒
timestamp:YYYY‐MM‐DD hh:mm:ss 默认值是当前的时间, 最大取值
是2038年.时间戳
text: 大文本类型
blob: 二进制大对象类型 大电影 java‐存放和读取
列的约束
**列的约束**
主键约束: primary key不能重复,不能为空 通常用在编号ID
唯一约束: unique 唯一约束: 不能重复,但是可以为空
非空约束: not null 不能为空
创建表查看表
修改表
添加列(add),修改列(modify),修改列名(change),删除列(drop),修改表名(rename),修改表的字符集
‐‐ 创建一个不带约束的员工表
创建员工(employee)表: 员工编号empno,员工名称ename,工资sal,部门编号deptno
create table employee(
empno int,
ename varchar(20),
sal double,
deptno int
);
‐‐ 创建一个带有约束的员工表 要求员工的编号必须唯一并可以自增长 用户名必须唯一 员工部门编号不能为空
create table employee2(
empno int primary key auto_increment, ‐‐ ID为主键 并且唯一不能重复 也不能为空 自动增长
ename varchar(20) unique,
sal double,
deptno int not null
);
‐‐ 查看所有的表
show tables;
‐‐ 查看表的创建过程,建表语句
show create table 表名
‐‐ 查看表结构
desc 表名删除表
Sql完成对表中数据的CRUD的操作
插入数据
添加列(add)语法
alter table 表名 add 列名 列名类型
‐‐添加性别
alter table employee add gender varchar(2);
修改列(modify)语法
alter table 表名 modify 列名 列的类型
‐‐ 重定义列的类型
alter table employee modify gender varchar(4);
修改列名(change)语法
alter table 表名 change 旧的列名 新列名 列的类型
‐‐ 将gender 改成 sex
alter table employee change gender sex varchar(4);
删除列(drop) 语法
alter table 表名 drop 列名;
alter table employee drop sex;
修改表名(rename) 语法
rename table 旧表名 to 新表名
rename table employee to emp;

修改表的字符集

*修改表的字符集 alter table 表名 character set 字符集*
    alter table emp character set 'utf8';
    drop table 表名
方法1临时解决：drop table emp;临时解决方案:cmd窗口设置编码 set names gbk;
方法2终身解决方案:
    1. 先关闭掉MYSQL服务器
    使用window+r --> services.msc-->MySQL--停止
    2. 找到安装路径C:\Program Files (x86)\MySQL\MySQL Server 5.5\my.ini
    注意: 在C盘下修改该文件可能需要修改权限,吧文件拷贝到其他硬盘修改完之后再替换
    修改第57行的编码为gbk
    3. 打开mysql服务
    使用window+r --> services.msc-->MySQL--启动服务
    4. 重新打开cmd窗口 重新登录MySQL即可
    注意: ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061):代表数据库服务已停止,
    如果要使用必须启动服务

删除记录

‐‐创建一张表
‐‐ 学生表 : 学号, 姓名,性别
create table stu(
stuno int primary key auto_increment,
sname varchar(20),
gender varchar(2)
);
auto_increment:自动增长,插入一条记录则增加1
‐‐指定列名插入:insert into 表名(列名1,列名2) values(值1,值2)
注意: 列名的数量和值的数量要一一对应
方式一:insert into 表名(字段名1,字段名2) values(值1,值2);
insert into stu(sname,gender) values('zs','ma');
insert into stu(sname,gender) values('ls','nv');
方式二:insert into 表名 values(值1,值2,值3)
注意:如果不写列名,那么在插入数据的时候,要根据表的所有字段一一对应插入;
insert into stu values(3,'ww','nv');
insert into stu values('zl','na'); ‐‐ 错误:ERROR 1136 (21S01): Column count doesn't match value
count at row 1
insert into stu values(1,'tq','na'); ‐‐错误:主键重复 ERROR 1062 (23000): Duplicate entry '1' for
key 'PRIMARY'
insert into stu values(4,'tq','nan'); ‐‐错误:字符超出长度 ERROR 1406 (22001): Data too long for
column 'gender' at row 1
insert into stu values(4,'tq','男'); ‐‐ 错误:ERROR 1366 (HY000): Incorrect string value:
'\xC4\xD0' for column 'gender' at row 1
insert into stu(stuno,sname) values(1,'zs');
insert into stu(stuno,sname) values(null,'lisi');
‐‐不指定列名插入:必须按照顺序,把表中所有的列的数据都填上,如果不填必须填null
insert into stu values(null,'wangwu','nv');更新表记录

查询记录

数据素材准备
        delete from 表名 [ where 条件 ]
        ‐‐删除 stuno = 1的学生信息
        delete from stu where stuno=1;
        ‐‐全部删除 , 逐条删除数据
        delete from stu;
        delete删除表数据和truncate删除表数据区别?
        区别一:
        使用delete这种方式删除,自增id会继续自增下去,不会重置为0开始;
        truncate 表: 直接整个先删除再重新创建表,主键id直接从0开始
        区别二:
        truncate属于DDL: 数据定义语言
        delete属于DML:数据操纵语言
        区别三:
        在事务使用中,delete删除表数据可以恢复,但是truncate不可以;
        update 表名 set 列名=列的值,列名2=列的值2 [where 条件]
        需求:修改6号胡八的删除结果为1
        update stu set isDelete=1 where stuno=6;简单查询
        别名查询
        表别名语法 : select 列名1,列名2.....from 表名 as 别名;
        列别名语法: select 列名1 as 列别名1,列名2 as 列别名2 .... from 表名
        注意: as可以省略不写
        ‐‐准备一张商品分类表(分类ID,分类名称,分类描述)
        create table category(
        cid int primary key auto_increment,
        cname varchar(10) not null,
        cdesc varchar(100)
        );
        ‐‐准备一张商品表(商品编号,商品名称,商品价格,商品描述,商品分类编号)
        create table product(
        pid int primary key auto_increment,
        pname varchar(10) not null,
        price double,
        pdesc varchar(20),
        cno int
        );
        ‐‐数据的准备
        insert into category values(null,'手机数码','黑马生产的小手机');
        insert into category values(null,'鞋靴箱包','传智生产的包包');
        insert into category values(null,'香烟酒水','黄鹤楼,茅台');
        insert into category values(null,'馋嘴零食','卫龙辣条,周黑鸭');
        insert into product values(null,'锤子',2999,'可以砸榴莲的手机',1);
        insert into product values(null,'小米',1599,'为发烧而生',1);
        insert into product values(null,'李宁',99,'不走寻常路',2);
        insert into product values(null,'耐克',399,'just do it',2);
        insert into product values(null,'黄鹤楼',20,'饭后一根烟,胜做活神仙',3);
        insert into product values(null,'卫龙辣条',5,'卫龙辣条加料不加价',4);
        insert into product values(null,'老干妈辣椒酱',9,'永远不变的味道',4);
        insert into product values(null,'老干娘辣椒酱',19,'永远不变的味道',4);
        insert into product values(null,'黄鹤楼',null,'饭后一根烟,胜做活神仙',3);
        查询语句的基本结构: select 列名,列名... from 表名
        只是查询出结果,不会改变数据库里面原有的内容
        ‐‐简单查询:
        ‐‐‐查询所有商品信息:
        select * from product;
        select * from category;
        ‐‐‐查询商品名称和商品价格:
        select pname,price from product;运算查询
        运算符 + - * /
        条件查询/逻辑查询/比较查询/模糊查询
        select 要显示的列名 from 从哪张表里面去查询数据 [where 条件:查询的是哪条记录]
        语法格式 : select [distinct][列名,列名] [*] from 表名 [where 条件]
        关系/比较运算符: = > >= < <= != <>
        逻辑运算: and , or , not
        其他运算符:
        is null 判断是否是空
        is not null 判断是否不为空
        in (集合) 在某个范围内
        between..and.. 在某个区间内 ,包含两端 闭区间 [10,100] (10,100)
        ‐‐表别名: 多表查询里面
        select * from product as p;
        select p.pname,p.price from product p;
        select pname,price from product p;
        ‐‐列别名:
        select pname as 商品名称,price as 商品价格 from product;
        select pname 商品名称,price 商品价格 from product;
        ‐‐‐去掉重复的值 distinct
        ‐‐查询所有商品的名称
        select pname from product;
        ‐‐去除重复的商品名称
        select distinct pname from product;
        select distinct * from product; ‐‐ 要求每一列都相同的才去除重复
        select distinct pname,price,pdesc,cno from product;
        ‐‐ 多列去除重复的, 必须是每一列都一样才算重复
        ‐‐ 查询商品折后价
        select pname as 商品名称,price * 0.75 as 折后价 from product;排序查询
        --排序查询: order by 关键字/列名
        语法:select [distinct][列名,列名] [*] from 表名 where 列名 条件 order by 列名 asc/desc;
        ‐‐ 查询商品价格>60元的所有商品信息
        select * from product where price > 60;
        ‐‐ 查询商品cno为4,价格为9的商品
        select * from product where cno=4 and price=9;
        ‐‐ where 后的条件写法
        ‐‐查询商品价格不等于99的所有商品
        select * from product where price != 99;
        select * from product where price <> 99;
        ‐‐查询出商品价格不为null的所有商品
        select * from product where price != null; ‐‐错误
        select * from product where price is not null;
        ‐‐ 查询商品价格在10 到 100之间 price >=10 and price <=100;
        select * from product where price >=10 and price <=100;
        select * from product where price between 10 and 100; ‐‐ 正确书写方式
        注意:
        select * from product where price between 100 and 10; ‐‐ 错误书写
        ‐‐ 查询出商品价格 小于35 或者商品价格 大于900
        select * from product where price <35 or price >900;
        ‐‐ like: 模糊查询
        % 匹配多个字符
        _ 匹配单个字符
        ‐‐ 查询出名字中带有辣的所有商品:
        select * from product where pname like '%辣%';
        ‐‐ 查询出名字第三个字是辣字的所有商品信息
        select * from product where pname like '__辣_'; ‐‐ 占位
        select * from product where pname like '__辣%';
        ‐‐ in 判断值是否在某个集合/范围
        ‐‐查询出商品分类ID在 1,3,4里面的所有商品
        select * from product where cno in (1,3,4);
        select * from product where cno in (7,8,9);升序: asc ascend 默认是升序
        降序: desc descend
        ‐‐0. 查询所有商品,按照价格进行排序
        select * from product order by price asc;
        ‐‐1.查询所有的商品,按价格进行降序排序 (asc‐升序 desc‐降序);
        select * from product order by price desc;
        ‐‐2.查询名称有 辣 的商品,按价格降序排序
        ‐‐‐1. 查询出所有 辣的商品
        select * from product where pname like '%辣%'
        ‐‐‐2 排序
        select * from product where pname like '%辣%' order by price desc;

聚合函数查询

    常见聚合函数:
    sum() 求和
    avg()求平均值
    count()统计个数
    max()最大值
    min()最小值

‐‐1.获得所有商品价格的总和:
    select sum(price) from product;
    ‐‐2.获得所有商品的平均价格:
    select avg(price) as 平均价 from product;
    ‐‐3.获得所有商品的个数:
    select count(pid) from product;
    select count(*) from product;
    select count(1) from product; ‐‐ 1只是代表一个常量,SQL语句优化
    ‐‐注意: where 条件后面不能接聚合函数 having 后面可以加
    ‐‐ 找出商品 > 平均价格的商品
    select * from product where price > avg(price); ‐‐ 错误: where后面不能接聚合函数 ERROR 1111 (HY000):
    Invalid use of group function
    分组查询
    --分组: group by
    select 分组的条件,分组之后的操作 from 表名 group by 分组条件 having 条件过滤;
    注意:聚合函数可以书写在having后面,但是不能书写在where 条件后面‐‐1.根据cno字段分组,分组后统计商品的个数
    select cno as 类别,count(1) 总数 from product group by cno;
    ‐‐分组之后的条件过滤: having
    ‐‐2.根据cno分组,分组统计每组商品的平均价格 并且商品平均价格 > 60
    select cno 类别,avg(price) 平均价格 from product group by cno having avg(price) > 60; ‐‐ 注意:聚合函数可以书写在having后面,但是不能书写在where 条件后面
    select cno 类别,avg(price) 平均价格 from product group by cno;
    select pname from product group by cno having avg(price) > 60;
    SELECT cno,AVG(price) FROM product WHERE price IS NOT NULL GROUP BY cno HAVING AVG(price) > 60 ORDER BY price ASC;

SQL语句编写顺序及查询顺序

SQL编写顺序
    S..F..W..G..H..O
    select ... From .. where .. group by ... having ....order by
SQL执行顺序
    F..W..G..H..S..O
    from ... where .. group by ... having ...select ...order by
解释如下：、
![

> 这里写图片描述

](https://img-blog.csdn.net/20170825133117974?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMDg0NzE0OA==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
    1,先from去查找表
    2,根据where的条件过滤
    3,where条件过滤完之后再group by分组
    4,having是对分组的数据进行过滤
    5,执行select查询
    6,最后order by排序

最后附上一张学习的思维导图：