基于MySql数据库的基本操作

一、概述

   描述事物的符号称之为数据。数据有多种表现形式，可以是数字，也可以是文字、图形、图像、声音、语言等。在数据库中数据表示为一条记录。
   数据库(Database)指长期存储在计算机内的、有组织的、可共享的数据集合。
   从数据的管理形式上来划分，数据库有两种类型：关系型数据库和非关系型数据库。

二、基本操作

  SQL的书写规则：
      SQL 语句要以分号;结尾。
      SQL 语句不区分大小写。
      SQL语句中的常量(字符串用''(单引号)表示；日期一般用(2020-08-08)形式；数字不使用任何符号标识)。

// 创建数据库
CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

一、数据定义语言（DDL）：

 用来创建或删除数据库以及表等对象，主要包含：
 **DROP:   删除数据库和表等对象**
 **CREATE:  创建数据库和表等对象**
 **ALTER： 修改数据库和表等对象的结构**

   查看数据库：
  SHOW DATABASES [LIKE '数据库名'];

// 删除数据库
DROP DATABASE [ IF EXISTS ] <数据库名>
DROP DATABASE test_db_del;
DROP DATABASE IF EXISTS test_db_del;

// 创建数据库
CREATE DATABASE test_db;
CREATE DATABASE IF NOT EXISTS test_db;
//创建表
CREATE TABLE tb_courses

二、数据操作语言(DML)：

用来变更表中的记录：
  **INSERT：向表中插入新数据**
  **UPDATE：更新表中的数据**
  **DELETE：删除表中的数据**

INSERT：插入数据
语法格式一：
INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];。
 在 tb_courses 表中插入一条新记录，course_id 值为 1，course_name 值为“Network”，course_grade 值为 3，info 值为“Computer Network”。
 INSERT INTO tb_courses
(course_id,course_name,course_grade,course_info)
VALUES(1,'Network',3,'Computer Network');

语法格式二：
INSERT INTO <表名>
SET <列名1> = <值1>,
    <列名2> = <值2>,
        …

UPDATE：修改数据
语法格式：
 UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]
 在 tb_courses_new 表中，更新所有行的 course_grade 字段值为 4。
UPDATE tb_courses_new
SET course_grade=4;

在 tb_courses 表中，更新 course_id 值为 2 的记录，将 course_grade 字段值改为 3.5，将 course_name 字段值改为“DB”
UPDATE tb_courses_new
SET course_name='DB',course_grade=3.5
WHERE course_id=2;

DELETE：删除数据
语法格式：DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
 删除 tb_courses_new 表中的全部数据
DELETE FROM tb_courses_new;

TRUNCATE：清空表记录
语法格式：TRUNCATE [TABLE] 表名
 使用 TRUNCATE 语句清空 tb_student_course 表中的记录：
TRUNCATE TABLE tb_student_course;

三、数据查询语言(DQL)：

用来查询表中的记录，主要包含SELECT命令，来查询表中的数据。

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

   使用“*”查询表的所有字段
   SELECT * FROM 表名;
   eg：
   SELECT * FROM tb_students_info;
    查询 tb_students_info 表中的所有数据
   SELECT id,name,dept_id,age,sex,height,login_date FROM tb_students_info;

查询表中指定字段
SELECT < 列名 > FROM < 表名 >;
查询 tb_students_info 表中 name列所有学生的姓名
SELECT name FROM tb_students_info;
或者：
SELECT <字段名1>,<字段名2>,…,<字段名n> FROM <表名>;
 从 tb_students_info 表中获取 id、name 和 height 三列
SELECT id,name,height FROM tb_students_info;

使用DISTINCT过滤重复数据
SELECT DISTINCT <字段名> FROM <表名>;
 把student表的 age 字段进行去重
SELECT DISTINCT age FROM student;
 student表的  name 和 age字段进行去重
SELECT DISTINCT name,age FROM student;

通过AS设置别名：
<表名> [AS] <别名>
 为 tb_students_info 表指定别名 stu
SELECT stu.name,stu.height FROM tb_students_info AS stu;
<字段名> [AS] <别名>
 查询 tb_students_info 表，为 name 指定别名 student_name，为 age 指定别名 student_age
 SELECT name AS student_name, age AS student_age FROM tb_students_info;

limit：限制查询结果的条数
语法格式：LIMIT 初始位置，记录数
 在 tb_students_info 表中，使用 LIMIT 子句返回从第 4 条记录开始的行 数为 5 的记录
 SELECT * FROM tb_students_info LIMIT 3,5;
 
 不指定初始位置：
 LIMIT 记录数
  显示 tb_students_info 表查询结果的前 4 行
  SELECT * FROM tb_students_info LIMIT 4;

LIMIT和OFFSET组合使用：
语法格式：LIMIT 记录数 OFFSET 初始位置
在 tb_students_info 表中，使用 LIMIT OFFSET 返回从第 4 条记录开始的行数为 5 的记录
SELECT * FROM tb_students_info LIMIT 5 OFFSET 3;

order by : 对查询结果排序：
语法格式：ORDER BY <字段名> [ASC|DESC]
 查询 tb_students_info 表的所有记录，并对 height 单字段进行升序排序
 SELECT * FROM tb_students_info ORDER BY height;
 
  查询 tb_students_info 表中的 name 和 height多 字段，先按 height 排序，再按 name 排序
  SELECT name,height FROM tb_students_info ORDER BY height,name;

WHERE：条件查询数据
语法格式：WHERE 查询条件
● 带比较运算符和逻辑运算符的查询条件
● 带 BETWEEN AND 关键字的查询条件
● 带 IS NULL 关键字的查询条件
● 带 IN 关键字的查询条件
● 带 LIKE 关键字的查询条件

 在 tb_students_info 数据表中查询身高为 170cm 的学生姓名（但条件查询）
SELECT name,height FROM tb_students_info
WHERE height=170;

 在 tb_students_info 表中查询 age 大于 21，并且 height 大于等于 175 的学生信息
 SELECT name,age,height FROM tb_students_info 
WHERE age>21 AND height>=175;

LIKE：模糊查询
语法格式：[NOT] LIKE  '字符串'
在 tb_students_info 表中，查找所有以字母“T”开头的学生姓名
SELECT name FROM tb_students_info
WHERE name LIKE 'T%';

在 tb_students_info 表中，查找所有包含字母“e”的学生姓名
SELECT name FROM tb_students_info
WHERE name LIKE '%e%';

带有“_”通配符的查询
 在 tb_students_info 表中，查找所有以字母“y”结尾，且“y”前面只有 4 个字母的学生姓名：
 SELECT name FROM tb_students_info
WHERE name LIKE '____y';

LIKE 区分大小写：默认情况下，LIKE 关键字匹配字符的时候是不区分大小写的。如果需要区分大小写，可以加入 BINARY 关键字。
 在 tb_students_info 表中，查找所有以字母“t”开头的学生姓名，区分大小写。
SELECT name FROM tb_students_info WHERE name LIKE BINARY 't%';

BETWEEN AND：范围查询
语法格式：[NOT] BETWEEN 取值1 AND 取值2
 在表 tb_students_info 中查询年龄在 20 到 23 之间的学生姓名和年龄
 SELECT name,age FROM tb_students_info 
WHERE age BETWEEN 20 AND 23;

IS NULL：空值查询
语法格式：IS [NOT] NULL
 使用 IS NULL 关键字来查询 tb_students_info 表中 login_date 字段是 NULL 的记录
 SELECT name,login_date FROM tb_students_info 
WHERE login_date IS NULL;

MySQL 分组查询
语法格式：GROUP BY  <字段名>
 根据 tb_students_info 表中的 sex字段进行分组查询
 SELECT sex FROM tb_students_info GROUP BY sex;
 
GROUP BY 与 GROUP_CONCAT() 
GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。

GROUP BY 与聚合函数
 根据 tb_students_info 表的 sex 字段进行分组查询，使用 COUNT() 函数计算每一组的记录数。
 SELECT sex,COUNT(sex) FROM tb_students_info  GROUP BY sex;

GROUP BY 与 WITH ROLLUP（统计记录数量）
 下面根据 tb_students_info 表中的 sex 字段进行分组查询，并使用 WITH ROLLUP 显示记录的总和
 SELECT sex,GROUP_CONCAT(name) FROM tb_students_info 
GROUP BY sex WITH ROLLUP;

HAVING：过滤分组
语法格式：HAVING <查询条件>
 分别使用 HAVING 和 WHERE 关键字查询出 tb_students_info 表中身高大于 150 的学生姓名，性别和身高。
SELECT name,sex,height FROM tb_students_info
HAVING height>150;

根据 height 字段对 tb_students_info 表中的数据进行分组，并使用 HAVING 和 WHERE 关键字分别查询出分组后平均身高大于 170 的学生姓名、性别和身高。
SELECT GROUP_CONCAT(name),sex,height FROM tb_students_info
GROUP BY height
HAVING AVG(height)>170;

四、数据控制语言(DCL)：

用来确认或者取消对数据库中的数据进行变更。除此之外，还可以对数据库中的用户设定权限：
   GRANT：赋予用户操作权限
   REVOKE：取消用户的操作权限
   COMMIT：确认对数据库中的数据进行的变更
   ROLLBACK：取消对数据库中的数据进行的变更