MySQL最基础(一):单表增删改查

by konley

复习笔记第一弹:数据库和SQL的概念、最基础和最常用的数据库单表的增删改查

一、数据库的基本概念

数据库的英文单词:DataBase,简称:DB

顾名思义就是用于存储和管理数据的仓库

数据库的特点:

  1. 持久化存储数据的。其实数据库就是一个文件系统
  2. 方便存储和管理数据
  3. 使用了统一的方式操作数据库 -- SQL

二、MySQL数据库软件

MySQL服务启动

  1. 手动
  2. cmd--> services.msc 打开服务的窗口
  3. 使用管理员打开cmd

    • net start mysql : 启动mysql的服务
    • net stop mysql:关闭mysql服务

MySQL登录

  1. mysql -uroot -p密码

如果不希望以明文显示密码,-p后直接回车

  1. mysql -hip -uroot -p连接目标的密码
  2. mysql --host=ip --user=root --password=连接目标的密码

MySQL退出

  1. exit
  2. quit

三、SQL什么是sql?

什么是sql?

Structured Query Language结构化查询语言

每一种数据库操作的方式存在不一样的地方,称为“方言”,而SQL定义了操作所有关系型数据库的规则,相当于通用语言。

sql通用语法

  1. SQL 语句可以单行或多行书写,以分号结尾
  2. 可使用空格和缩进来增强语句的可读性
  3. MySQL 数据库的 SQL 语句不区分大小写关键字建议使用大写
  4. 3 种注释

    • 单行注释: -- 注释内容 或 # 注释内容(mysql 特有)
    • 多行注释: / 注释 /

sql分类

  1. DDL(Data Definition Language)数据定义语言

​ 用来定义数据库对象:数据库,表,列等。关键字:create, drop,alter 等

  1. DML(Data Manipulation Language)数据操作语言

​ 用来对数据库中表的数据进行增删改。关键字:insert, delete, update 等

  1. DQL(Data Query Language)数据查询语言

​ 用来查询数据库中表的记录(数据)。关键字:select, where 等

  1. DCL(Data Control Language)数据控制语言(了解)

​ 用来定义数据库的访问权限和安全级别,及创建用户。关键字:GRANT, REVOKE 等

mysql体系

四、DDL:操作数据库、表

4.1 操作数据库:CRUD

C(Create):创建

  • 创建数据库:

    create database 数据库名称;
  • 创建数据库,判断不存在,再创建:

    create database if not exists 数据库名称;
  • 创建数据库,并指定字符集

    create database 数据库名称 character set 字符集名;
  • 练习: 创建db4数据库,判断是否存在,并制定字符集为gbk

    create database if not exists db4 character set gbk;

R(Retrieve):查询

查询所有数据库的名称:
show databases;

查询某个数据库的字符集、查询某个数据库的创建语句
show create database 数据库名称;

U(Update):修改

修改数据库的字符集
alter database 数据库名称 character set 字符集名称;

D(Delete):删除

删除数据库
drop database 数据库名称

判断数据库存在,存在再删除
drop database if exists 数据库名称;

其他操作

查询当前正在使用的数据库名称
select database();

使用数据库
use 数据库名称;

4.2 操作表

C(Create):创建

create table 表名(
        列名1 数据类型1,
        列名2 数据类型2,
        ....
        列名n 数据类型n
    );

注意:最后一列,不需要加逗号(,)

数据库类型:

1. int:整数类型
   age int,
   
2. double:小数类型
   score double(5,2)
   最多5位,取小数点后2位
   
3. date:日期,只包含年月日,yyyy-MM-dd

4. datetime:日期,包含年月日时分秒     yyyy-MM-dd HH:mm:ss

5. timestamp:时间戳类型    包含年月日时分秒     yyyy-MM-dd HH:mm:ss    
   * 如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值
   
6. varchar:字符串
    * name varchar(20):姓名最大20个字符
    * zhangsan 8个字符  张三 2个字符

创建表的代码

create table student(
            id int,
            name varchar(32),
            age int ,
            score double(4,1),
            birthday date,
            insert_time timestamp
        );

复制表:

create table 表名 like 被复制的表名;     

R(Retrieve):查询

1. 查询某个数据库中所有的表名称:

show tables;

2. 查询表结构:

desc 表名;

U(Update):修改

1. 修改表名

   alter table 表名 rename to 新的表名;   

2. 修改表的字符集

   alter table 表名 character set 字符集名称;
   
3. 添加一列

   alter table 表名 add 列名 数据类型;
   
4. 修改列名称 类型

   alter table 表名 change 列名 新列别 新数据类型;
   alter table 表名 modify 列名 新数据类型;

5. 删除列

   alter table 表名 drop 列名;

D(Delete):删除

drop table 表名;

drop table  if exists 表名;

五、DML:增删改表中数据

5.1 添加数据:

语法:

insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

注意:

  1. 列名和值要一一对应。
  2. 如果表名后,不定义列名,则默认给所有列添加值
    insert into 表名 values(值1,值2,...值n);
  3. 除了数字类型,其他类型需要使用引号(单双都可以)引起来

5.2 删除数据:

语法:

 delete from 表名 [where 条件]

注意:

  1. 如果不加条件,则删除表中所有记录。
  2. 如果要删除所有记录

    TRUNCATE TABLE 表名; 
    -- 推荐使用,效率更高 先删除表,然后再创建一张一样的表。

5.3 修改数据:

语法:

update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];

注意:

如果不加任何条件,则会将表中所有记录全部修改。

六、DQL:查询表中的记录

6.1 语法

select
    字段列表
from
    表名列表
where
    条件列表
group by
    分组字段
having
    分组之后的条件
order by
    排序
limit
    分页限定

6.2 基础查询

多个字段查询

select 字段名1,字段名2... from 表名;

注意:

如果查询所有字段,则可以使用*来替代字段列表。

去除重复distinct

select distinct 字段1,字段2.. from 表名

计算列

SELECT 字段1 ,字段2,字段1+字段2 FROM 表名

一般可以使用四则运算计算一些列的值。(一般只会进行数值型的计算)

ifnull(表达式1,表达式2):null参与的运算,计算结果都为null

SELECT 字段1 ,字段2,ifnull(字段1,0)+字段2 FROM 表名
如果字段1为null,则用0代替它

起别名 as

SELECT 字段1 as 数学,字段2 as 英语,字段1+字段2 as 总分 FROM 表名

6.3 条件查询

1.where子句后跟条件

...where 条件

2.运算符

* > 、< 、<= 、>= 、= 、<>
* BETWEEN...AND
* IN( 集合) 
* LIKE:模糊查询
     _:单个任意字符
     %:多个任意字符,可以为0
* IS NULL  
* and  或 &&
* or  或 || 
* not  或 !

3.代码示例:

一般查询

--查询年龄大于20岁
select * from stu where age>20;
select * from stu where age>=20;

--查询年龄等于20岁
select * from stu where age=20;

--查询年龄不等于20岁
select * from stu where age!=20;

--查询年龄大于等于20 小于等于30
select * from stu where age>=20 and age<=30;
select * from stu where age>=20 && age<=30;
select * from stu where age between 20 and 30;

--查询年龄22岁,18岁,25岁的信息
select * from stu where age in (22,18,25);
select * from stu where age=22 or age=18 or age=25;

--查询英语成绩为null
select * from stu where english is null;

--查询英语成绩不为null
select * from stu where english is not null;


模糊查询
---------------------

--查询姓马的有哪些 like
select * from stu where name like '马%';

--查询第二个字是马的
select * from stu where name like '_化%';

--查询姓名是三个字的
select * from stu where name like '___';

--查询姓名是三个字和三个字以上的
select * from stu where name like '___%'

--查询姓名中含德的人
select * from stu where name like '%德%'

6.4 排序查询

语法

order by 子句
order by 排序字段1,排序方式1[,排序字段2,排序方式2...]

排序方式

ASC : 默认,升序
DESC: 降序

注意:如果有多个排序条件时,则在排序条件1排完后,在判断排序条件2

--按数学成绩降序排列
select * from stu order by math desc;

--按数学成绩升序排列
select * from stu order by math asc;
select * from stu order by math;

--先按数学成绩降序排列,再按英语成绩降序排列
select * from stu order by math desc,english desc;

6.5 聚合函数

一列数据作为一个整体,进行纵向的计算

语法:

select 聚合函数1(字段1) [as 别名1] [,聚合函数2(字段2) [as 别名2]..] from 表名...

常用函数

  • count:计算个数

    • 一般选择非空的列:比如主键
  • max:计算最大值
  • min:计算最小值
  • sum:求和
  • avg:计算平均值

注意:聚合函数的计算,排除掉了null值字段

解决方案:1. 选择非空的字段(列)进行计算(推荐),2. IFNULL函数

示范代码

--计算数学成绩的平均分
select avg(math) as '数学平均分' from  stu;

--计算班里的人数
select count(id) as '人数' from stu;

--计算班里数学最高分和最低分
select max(math) as '最高分',min(math) as '最低分' from stu;

--计算班里的数学总分
select sum(math) as '数学总分' from stu;

6.6 分组查询

相当于共性抽取,将某个字段一致的看成一个整体,如性别,然后再加上聚合函数查看这些分组有什么特点

语法

group by 分组字段
select [分组字段][聚合函数] from 表名 [where..] group by 分组字段

注意

  1. 分组之后查询的字段:分组字段、聚合函数,不接其他字段
  2. where和having的区别

    1. where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来
    2. where 后不可以跟聚合函数,having可以进行聚合函数的判断
--按性别分组,分别计算男女同学的人数和平均分
select sex,count(id),avg(math),avg(english) from stu group by sex;

--按照性别分组。分别查询男、女同学的人数和平均分 要求:分数低于70分的人,不参与分组
SELECT
    sex,
    count( id ),
    avg( math ),
    avg( english ) 
FROM
    stu 
WHERE
    math > 70 
    AND english > 70 
GROUP BY
    sex

--按照性别分组。分别查询男、女同学的平均分,人数 要求:分数低于70分的人,不参与分组,分组之后。人数要大于2个人
SELECT
    sex,
    count( id ),
    avg( math ),
    avg( english ) 
FROM
    stu 
WHERE
    math > 70 
    AND english > 70 
GROUP BY
    sex
HAVING
    count(id)>2
    

6.7 分页查询

通俗地说就是限定查询的条数,实际开发是很重要的技能

语法

limit 开始索引,每页条数
--索引从0开始
--每页显示3条记录
--第1页
select * from stu limit 0,3
--第2页
select * from stu limit 3,3
--第3页
select * from stu limit 6,3

当参数1可以动态变化时,就可以直接查询指定的分页

公式:开始索引 = (当前页码 - 1)* 每页条数 ==> 当前页数 = 每页条数 / (开始页码+1)

即当一页显示m条,查询第n页的公式为m ( n - 1 ) , m

--第n页,一页m条
select * from stu limit m(n-1),m

注意:limit为mysql特有的,其他数据库不支持