Mysql自学笔记—增删查改

基础知识

客户端软件：Dbever

Dbever 下载网址：dbever.jkiss.org

基础代码

Show databases；                      查看目前所有数据库

Use 数据库名称；                       进入数据库

Show tables；                             查看目前所在数据库中的所有表

Desc 库名.表名；                        查看表结构

Select * from 表名.列名 limit N； 如果对数据库或数据库下面的表不熟悉：用来限制查询的记录数，主要是为了了解表

Distinct                                        对查询结果值去重；用在 select 后面，也经常会与汇总函数一起使用

As                                                用来给表或者列取别名

在数据库下面创建表并复制其他表的数据

通过create与select生成数据

第一步∶选择数据库
把数据库切入到你要的数据库下面;如果没有切换，需要使用:库名.表名

• 使用:use库名;
• 手工切换;

第二步∶创建表与插入数据是同时进行︰

-- 创建一个新的test表，从已有表sn_member_20210501里提取数据
create table sn_test_20210513
As
Select *
From sn_member_20210501;

数据库定义语言——create  drop  alter

目标：学会对数据库与数据表进行创建、修改、删除等操作

数据对象包括：数据库、表、视图、索引

基本语句：

create，drop 语句

创建数据库：Create database 数据库名

删除数据库：Drop database 数据库名

创建表：create table 表名 (列名 新列数据类型）

create table sn_member_20210412(id varchar(100),name varchar(100),age int,birthday date);

删除表：drop table 表名

命名规则：以姓名缩写_开始 , 以日期结束; 中间反应表的内容; 公司的其他规范

                 eg: sn_order_20170411

alter 语句

主要解决问题：用于在已有的表中添加、修改、删除列，更新表名，字段类型等

增加一列：alter table 表名 add 列名 新列数据类型

alter table demo add birthday date；

删除一列：alter table 表名 drop 列名

alter table demo drop birthday；

修改字段名：alter table 表名 change 旧字段名 新字段名 类型

修改表名：alter table 旧表名 rename 新表名

创建一个新的数据库demo，在该数据库下面创建数据表，并进行一系列的练习

1. 创建数据库sn_demo；创建数据表sn_member_20210506
2. 对表进行列的增加和删除、修改某一列的列名和字段名、重命名表
3. 删除表；删除数据库

create database sn_demo;
use sn_demo;                                   /*使用sn_demo数据库进行操作*/
create table sn_member_20210506(id varchar(100),name varchar(100),age int,birthday date);
desc sn_demo.sn_member_20210506;               /*查看表的结构*/
alter table sn_demo.sn_member_20210506 add sex int;
alter table sn_demo.sn_member_20210506  drop sex;
alter table sn_demo.sn_member_20210506 change id member_id int;
alter table sn_demo.sn_member_20210506 rename sn_member_20210507;
drop table sn_demo.sn_member_20210507 ;
drop database sn_demo;

注意事项：

1. 要创建数据库表，一定要先创建或指定数据库，因为表是存在数据库下面的
2. 创建表，必须现有表名，然后创建字段名，以及每个字段都要指定对应的数据类型
3. 数据类型：一般数值型：整型 int，浮点型 float，日期/时间型 date/time，字符型 char(n)/varchar(n)，文本型 text
4. 如果不在同一个数据库中，要对另外一个数据库中的表操作，一定要把库名带上：库名.表民

数据库操作语言——insert   update  delect

目标：学会对表进行各类操作，例如：增加、删除、修改等

基本语句

1. Insert：向表中插入数据
2. Update 更新表中相关数据
3. Delete 删除表中的记录（删除行）

Insert 语句

insert into 表名（列名）values（各列值）

• 如果是字符类型的，需要用单引号
• 注意插入的值要与定义的字段类型一致

创建数据表并向表中插入数据

1. 常规插入；注意：列名后不需要跟数据类型，否则会报错
2. 一下常见的插入及错误的插入

create database sn_demo;
use sn_demo;
create table sn_member_20210506(id varchar(100),name varchar(100),age int,birthday date);
insert into sn_demo.sn_member_20210506(id,name,age,birthday) values('A001','sn',18,'2020-01-01');
select *
from sn_demo.sn_member_20210506;    /*查看插入结果*/

-- 不使用列名(注意 1.插入的数据需要与原表数据一致；2.插入的数据与原表的字段数是一样的)
insert into sn_demo.sn_member_20210506 values ('A002','xty',19,'2020-01-02');
-- 列的顺序与原表不一致 (需要加上列名)
insert into sn_demo.sn_member_20210506 (name,id,birthday,age) values ('ty','A003','2020-01-03',20);
-- 插入部分字段
insert into sn_demo.sn_member_20210506 (name,age) values ('xx',17);
select*
from sn_demo.sn_member_20210506;      /*查看插入结果*/

-- 错误插入 （不指定列名且数据不完整会报错）
insert into sn_demo.sn_member_20210506 values ('xx',17);

注意事项：

• 增加数据，也就是往表里插入数据，针对的是行的操作

1.  如果不指定列名：添加的数据与列的顺序与列的数量是一致的
2. 以下下情况需要指定列名，且列名必须与后面的数据顺序保持一致：

a) 列的顺序与实际列的顺序不一致；b) 插入列的数据不完整，并不是所有列都有数据

update 语句

Update  表名  set 列名=新值 （where条件）

• 如果没有where条件，那表示对整个列进行赋值，而且是同一个值
• 所以在日常工作中，update一定要与where一起使用

1. 更新 A001 的年龄为 22 岁
2. 不带 where 则会对整个列进行赋值

update sn_demo.sn_member_20210506 set age=22 where id='A001';

-- 错误示范:不使用where会对整列进行赋值
update sn_demo.sn_member_20210506 set age=20;

delect 语句

Delete from 表名 （where 列名称 = 值）

• 删除表中的某行
• 删除表中的所有行

1. 从表中把name为xx的这一行删除
2. 不带where，会删除整张表

-- 删除指定行
delete from sn_demo.sn_member_20210506 where name ='xx';
-- 删除表中所有数据
delete from sn_demo.sn_member_20210506 ;

数据库查询语言——select

以下用已经创建好的数据库来进行操作，主要看语法，如有需要也可以点击链接查看数据自行创建数据库

点击传送：数据传送门

补充：基本统计汇总函数

• max( )        统计最大
• min( )         统计最小值
• count( )      计数：如果需要去计算列进行去重，则count(distinct )
• avg( )         计算平均数
• sum( )        汇总：有汇总函数一定要用 group by

补充：distinct 知识点介绍

1. 用于去重，需要加上where限制条件
2. 可以对多个字段去重：对distinct后面所有字段都一样的数据去重，有某一个字段不一样的不会去重

限制member_id为1，对order_money，member_id，id三个字段都一样的结果去重

select distinct order_money
      ,member_id
      ,id
from sn_order_20210412；
where member_id = 1;

distinct 经常与 count 一起使用

• 在计数时，要考虑是否要去重，例如统计累计顾客数，需要对顾客去重后再计数，而不是先计数再去重 

select count(distinct member_id)
from db_order.sn_order_20210412;

where 语句

1. where是针对行进行操作
2. 对列的过滤是用 select 的选择的列名
3. 如果过滤的是文本型，一般要加''

where 常使用的限制条件

• 比较：=，<，<=，>，>=，<>
• 指定范围：between and （between and 包含两头数据， not between and 不包含两头数据）
• 集合： in( ) ，not in( )  （in 后面的数值要加括号）
• 空值判断：is not null ，is null（Null  空值：不是0，而是没有数值；0表示值为0的数据，不是空值）
• And：并列，同时满足条件
• OR：或，满足其中一个条件​​​​​

-- 查询金额大于100的记录
select *
from db_order.sn_order_20210412
where order_money > 100;
-- 查找订单ID为A001的记录
select *
from db_order.sn_order_20210412
where id = 'A001';
-- 找出会员号为：1，2，21，的会员消费记录
select *
from db_order.sn_order_20210412
where member_id in (1,2,21);
-- 查找订单金额为空的记录,金额为0的不会显示
select *
from db_order.sn_order_20210412
where order_money is null;
-- 不为空就会把值为0的记录一起查找出来
select *
from db_order.sn_order_20210412
where order_money is not null;
-- 购买金额大于20小于200的会员表  不能使用between and 因为结果会包含两头数据 查询结果为大于等于20小于等于200的记录
select *
from db_order.sn_order_20210412
where order_money > 20 and order_money <200;
-- 购买金额大于100或者购买记录小于10的记录  可以用not between and 因为结果不包含两头数据
select *
from db_order.sn_order_20210412
where order_money not between 10 and 100;

where like——模糊查询

• %：表示任意长度的字符串，长度可以为0

例如: like 'a%b’，以a开头，b结尾任意长度的内容

• _：一个下划线就表示一个字符

例如:like 'a_b’，以a开头，b结尾长度为3个字符的内容

-- 查询订单表ID中以5结尾的记录
select *
from db_order.sn_order_20210412
where id like '%5';
-- 查询订单表ID中以A开头5结尾，ID长度为4的记录
select *
from db_order.sn_order_20210412
where id like 'A__5';

group by 语句

1. 可以放入到group by 的后面的字段：select 后面，汇总函数 sum( )前面的所有字段
2. 一定是跟着汇总函数 sum( ) 一起使用
3. 可以针对多个字段进行分组

1. 汇总函数的使用，统计累计购买金额
2. 使用 group by 对一个字段进行分组
3. 使用 group by 对两个字段进行分组

-- 统计累计购买金额
select sum(order_money) as money
from db_order.sn_order_20210412;
-- 统计各个顾客购买金额
select member_id 
      ,sum(order_money) 
from db_order.sn_order_20210412
group by member_id;
-- 统计各个顾客不同订单状态下的累计购买金额
select member_id 
      ,status 
      ,sum(order_money) 
from db_order.sn_order_20210412
group by member_id 
        ,status;           /*相当于把订单金额从顾客与订单状态二个维度进行拆分*/
-- 统计各个顾客，在不同订单状态下的平均购买金额，累计购买金额，订单数
select member_id 
      ,status 
      ,avg(order_money) 
      ,sum(order_money)
      ,count(distinct id) 
from db_order.sn_order_20210412
group by member_id 
        ,status;

having 语句

• 一定是对汇总后的数据进行过滤
• 如果不是针对汇总后的数据过滤，用的是where
• 一定是和 group by 一起使用，放在 group by 后面

1. 先对会员的订单金额累计汇总后，只取大于100的记录，这个用Having
2. 统计订单金额大于100的会员记录，用Where过滤

-- 统计每个会员累计购买金额，找出累计购买金额大于100的会员
select member_id 
      ,sum(order_money) as money 
from db_order.sn_order_20210412
group by member_id 
having sum(order_money)>100
order by sum(order_money) desc ;        /*对查询结构进行排序：累计购买金额进行降序排序*/   
-- 统计会员的消费金额在100以上的累计购买金额
select member_id 
      ,sum(order_money) as money 
from db_order.sn_order_20210412
where order_money >100
group by member_id ;
-- 统计订单金额大于100的会员记录
select *
from db_order.sn_order_20210412
where order_money >10;

补充: order by / limit 用法

• order by 是对查询结果进行排序，默认升序，如需要降序，可是有 desc
• limit 当对数据不了解时，可以用 limit 提取前几行数据查看
• limit 一定用在所有语句的后面

1. 对订单金额进行降序，并显示前5行的记录

select *
from db_order.sn_order_20210412
order by order_money desc
limit 5;