MySQL数据库和表基础操作

目录

一、数据库的基本操作

二、表的基本操作

1. 表结构操作

 2. 表数据操作（CRUD增删查改）

---

一、数据库的基本操作

● 创建数据库： create database [数据库名];

● 切换数据库： use [数据库名];

● 删除数据库： drop database [数据库名];

● 查询所有数据库： show databases;

● 查询当前所在数据库： select database();

二、表的基本操作

命名⼤⼩写和规范的问题：

MySQL 忽略⼤⼩写匹配，但创建数据库和表的时候⼀定要注意，默认采⽤全部⼩写的⽅式来操

作，防⽌编写的 sql 不通⽤。

表名采⽤下划线连接，⽐如学⽣成绩表，可使⽤ student_score，⽽⾮ java 的中的 studentScore。

1. 表结构操作

1.1 创建表： 

        create table 表名(

                属性1,

                属性2,

                属性3

        );

可以使⽤ comment 增加字段说明。

示例：

create table stu_test ( 
    id int, name varchar(20) comment '姓名',
    password varchar(50) comment '密码', 
    age int, 
    sex varchar(1), 
    birthday timestamp, 
    amout decimal(13,2), 
    resume text 
);

1.2 删除表 ：

        drop table 表名;

        或者  drop table if exists 表名;

1.3 查看表结构 ：

        desc 表名 ;

如果desc 查看不到说明信息，可以使⽤另⼀种查询表结构的⽅法：

        show create table student;

或者使⽤ show full columns from 表名;

查询⼀张表的所有字段信息，效果如下：

 查看所有表：

        show tables;

1.4 修改表结果 ：

1.4.1 添加列 ：

        alter table 表名 add column 列名 数据类型;

1.4.2 删除列：

        alter table 表名 drop column 列名;

1.4.3 修改列 ：

列已经有数据了，不能删除，只能修改，可以使⽤ change 语句，具体语法如下：

        alter table 表名 change 原字段名 新字段名 类型 [约束];

1.4.4 修改表名：

        alter table 旧表名 rename 新表名;

1.4.5 修改某个表编码格式（⽀持中⽂）：

         alter table table_name convert to character set utf8mb4;

 2. 表数据操作（CRUD增删查改）

2.1 新增 ：

2.1.1 单行数据 + 全列插入 ：

  insert into score values(1,'刘备','85.5','20','80'); 

2.1.2 多行数据 + 指定列插入 ：

    insert into score(id,name,math) values(5,'⼤乔',60), (6,'张⻜',70);

扩展：表和表数据的存储⽬录：

show variables like '%dir%';

也可以使⽤： select @@datadir;

2.2 查询 ：

2.2.1 全列查询：

-- 通常情况下不建议使用 * 进行全列查询
-- 1. 查询的列越多，意味着需要传输的数据量越大
-- 2. 可能会影响到索引的使用。
    
    select * from exam_result;

2.2.2 指定列查询：

-- 指定列的顺序不需要按定义表的顺序来

    select id, name, english from exam_result;

2.2.3 表达式查询：

-- 表达式不包含字段
    select id, name, 10 from exam_result;
-- 表达式包含一个字段
    select id, name, english + 10 from exam_result;
-- 表达式包含多个字段
    select id, name, chinese + math + english from exam_result;

2.2.4 别名：

为查询结果中的列指定别名，表示返回的结果集中，以别名作为该列的名称，语法：

-- 结果集中，表头的列名=别名

        select id, name, chinese + math + english 总分 from exam_result;

2.2.5 去重：

使用 DISTINCT 关键字对某列数据进行去重：

select distinct math from exam_result;

2.2.6 排序：

-- ASC 为升序（从小到大）

-- DESC 为降序（从大到小）

-- 默认为 ASC

1. 没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

2. NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面

-- 查询同学姓名和 qq_mail ，按 qq_mail 排序显示

select name, qq_mail from student order by qq_mail;

select name, qq_mail fromstudent  order by qq_mail desc;

3. 使用 表达式 及 别名 排序

-- 查询同学及总分，由高到低

select name, chinese + english + math from  exam_result order by chinese + english + math desc ;

select  name, chinese + english + math total from  exam_result order by  total desc ;

4. 可以对多个字段进行排序，排序优先级随书写顺序

-- 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示

select name, math, english, chinese from exam_result

order by  math desc , english, chinese;

2.2.7 条件查询 ：where

比较运算符：

 逻辑运算符：

注 ：

1. WHERE 条件可以使用表达式，但不能使用别名。

2. AND 的优先级高于 OR ，在同时使用时，需要使用小括号 () 包裹优先执行的部分

● 基本查询：

-- 查询英语不及格的同学及英语成绩 ( < 60 )

SELECT name, english FROM exam_result WHERE english < 60 ;

-- 查询语文成绩好于英语成绩的同学

SELECT name, chinese, english FROM exam_result WHERE chinese > english;

-- 查询总分在 200 分以下的同学

SELECT name, chinese + math + english 总分 FROM exam_result WHERE chinese + math + english < 200 ;

● AND与OR：

-- 查询语文成绩大于 80 分，且英语成绩大于 80 分的同学

SELECT * FROM exam_result WHERE chinese > 80 and english > 80 ;

-- 查询语文成绩大于 80 分，或英语成绩大于 80 分的同学

SELECT * FROM exam_result WHERE chinese > 80 or english > 80 ;

-- 观察 AND 和 OR 的优先级：

SELECT * FROM exam_result WHERE chinese > 80 or math> 70 and english > 70 ;

SELECT * FROM exam_result WHERE (chinese > 80 or math> 70 ) and english > 70 ;

● 范围查询：

1. BETWEEN ... AND ...

查询语文成绩在 [80, 90] 分的同学及语文成绩

SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90 ;

-- 使用 AND 也可以实现

SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90 ;

2. IN

-- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

SELECT name, math FROM exam_result WHERE math IN ( 58 , 59 , 98 , 99 );

-- 使用 OR 也可以实现

SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math = 98 OR math = 99 ;

● 模糊查询：LIKE

-- % 匹配任意多个（包括 0 个）字符

SELECT name FROM exam_result WHERE name LIKE ' 孙 %' ; -- 匹配到孙悟空、孙权

-- _ 匹配严格的一个任意字符

SELECT name FROM exam_result WHERE name LIKE ' 孙 _' ; -- 匹配到孙权

● NULL 的查询：IS [NOT] NULL

-- 查询 qq_mail 已知的同学姓名

SELECT name, qq_mail FROM student WHERE qq_mail IS NOT NULL ;

-- 查询 qq_mail 未知的同学姓名

SELECT name, qq_mail FROM student WHERE qq_mail IS NULL;

2.2.8 分页查询 ：LIMIT

-- 第 1 页

SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3

OFFSET 0 ;

-- 第 2 页

SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3

OFFSET 3 ;

-- 第 3 页，如果结果不足 3 个，不会有影响

SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3 OFFSET 6 ;

2.3. 修改 :

-- 将孙悟空同学的数学成绩变更为 80 分

UPDATE exam_result SET math = 80 WHERE name = ' 孙悟空 ' ;

-- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

UPDATE exam_result SET math = 60 , chinese = 70 WHERE name = ' 曹孟德 ' ;

-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT

3 ;

-- 将所有同学的语文成绩更新为原来的 2 倍

UPDATE exam_result SET chinese = chinese * 2 ;

2.4. 删除：

-- 删除孙悟空同学的考试成绩

DELETE FROM exam_result WHERE name = ' 孙悟空 ' ;

-- 删除整张表数据

-- 准备测试表

DROP TABLE IF EXISTS for_delete;

CREATE TABLE for_delete (

id INT ,

name VARCHAR ( 20 )

);

-- 插入测试数据

INSERT INTO for_delete (name) VALUES ( 'A' ), ( 'B' ), ( 'C' );

-- 删除整表数据

DELETE FROM for_delete;