MySQL常规操作

作者使用的数据库为是MySQL8, 图形化工具--SQLyog<获取pj版可留言>

1. DDL(Data Definition language)操作数据库--主要用于创建数据库、数据表

1.1 创建数据库、查询数据库

 # 创建数据库
CREATE DATABASE db1;

# 判断数据库是否存在, 不存在就创建
CREATE DATABASE IF NOT EXISTS db2;

# 创建数据库并指定字符集
CREATE DATABASE db3 DEFAULT CHARACTER SET utf8;

# 查询数据库
SHOW DATABASES ;
# 操作以后自行查询即可

1.2 查看某个数据库的定义信息

# 查看某个数据库的定义信息
SHOW CREATE DATABASE db3;
SHOW CREATE DATABASE db1;

1.3 修改数据库

# 修改数据库默认的字符集【将原先设置的utf8字符集改为gbk，当然开发中使用的utf8】
ALTER DATABASE db3 CHARACTER SET gbk;

注意1.4的操作在开发中，千万不要， 不要， 不要删库， 重要的事情说三遍

1.4 删除数据库

# 删除数据库
DROP DATABASE db2;

1.5 使用数据库

# 查看目前所在的是哪个数据库
SELECT DATABASE();

# 改变要使用的数据库
USE db1;

2. DDL操作表结构--创建表操作

2.1 创建数据表的格式

CREATE TABLE table_name(
    字段1 字段类型1,
    字段2 字段类型2,
    ...
);

# 常见字段类型可以自行查阅， 因为官方出品必属精品， 细节上个人绝对没有集众人之力做的好， 感谢

2.2 创建student表包含id，name，birthday等三个字段【这里我是在前面创建的db1数据库中操作， 当然你可以自行创建一个属于自己的】

# INT(整数) VARCHAR(字符串) DATE(日期)
CREATE TABLE student(
    id INT,
    NAME VARCHAR(32),
    birthday DATE
);

2.3 查看表

# 查看db1【db1可以换成任意数据库】中的数据库中的所有表
SHOW TABLES;

# 查看创建的表的结构
DESC student;

# 查看创建表的SQL语句
SHOW CREATE TABLE student;

2.4 快速创建一个表结构相同的表

# 创建new_student表, new_student表结构和student结构相同
CREATE TABLE new_student LIKE student;

2.5 删除表

# 删除新创建的new_student表
DROP TABLE new_student;

# 下面是另一种格式: 判断表是否存在, 如果存在则删除, 可以用已经删除的new_student表进行测试
DROP TABLE IF EXISTS new_student;
【SQLyog控制台输出的信息如下: 共 0 行受到影响， 1 个警告】

2.6 修改表结构: 增加、删除、修改

# 添加表列关键字: ALTER  ADD
# 在student表中添加学生的地址信息address
ALTER TABLE student ADD address VARCHAR(32);

# 修改列类型关键字：MODIFY 
# 将student表中的address列类型修改为int类型【这里是不合理的仅供测试使用】
ALTER TABLE student MODIFY address INT;

# 修改列名关键字： CHANGE
# 将address该字段修改为new_addr， 并且字段类型改为varchar
ALTER TABLE student CHANGE address new_addr VARCHAR(32);

# 删除列关键字： DROP
# 删除new_addr列
ALTER TABLE student DROP new_addr;

# 修改表名关键字： RENAME   TO
# 将student表名该为old_student
RENAME TABLE student TO old_student;

3. DML(Data Manipulation Language数据操纵语言)操作表中的数据【对表中的数据进行增删改操作】

3.1 插入数据

# 格式： INSERT  [INTO] 表名 [字段名] VALUES(字段值);
# 三种插入的方式【字段名用k表示， 字段值用v表示】
# 1. 插入全部字段
INSERT INTO table_name (k1, k2, k3....) VALUES(v1, v2, v3...);
# 2. 不写字段
INSERT INTO table_name VALUES(v1, v2, v3...);
# 3. 插入部分数据
INSERT INTO table_name (k1, k2) VALUES(v1, v2);
# 注意： 没有添加数据的字段会使用NULL

# 例子： 向student表中添加一个scott的成员
INSERT INTO student(id, NAME, birthday) VALUES(1, 'scott', '1981-01-02');
# 可以自行添加更多的数据， 本次这里只是举个例子
# 查询student中的数据
SELECT * FROM student；

3.2 更新表的数据

# 格式： UPDATE 表名 SET 列名 = 值 [WHERE 条件表达式];
# 1. 不带条件修改数据
UPDATE 表名 SET 字段名=值；# 修改所有的行
# 2. 待条件修改数据
UPDATE 表名 SET 字段名 = 值 WHERE 字段名 = 值；# 待条件修改数据

# 例子： 将student中的scott学生的name字段改为gunner
UPDATE student SET NAME='gunner' WHERE id=1;

3.3 删除表的数据【三思而后行啊】

# 格式：DELETE FROM 表名 [WHERE 条件表达式];
# 如果没有指定WHERE子句， MySQL表中的所有记录将被删除【慎重】

4. DQL（Data Query Language数据查询语句）查询表中的数据

# 先创建一个拥有多条数据的表
# 创建的student， 包含的字段由id, name, gender, math_score, english_score,
CREATE TABLE student(
    id INT, # 学生编号
    NAME VARCHAR(32), # 学生姓名
    gender INT, # 学生性别， 1表示男生， 0表示女孩
    math_score DOUBLE, # 数学成绩， 因为可能为"99.5"所以使用DOUBLE
    english_score DOUBLE# 英语成绩
);

# 查询student表是否创建成功
DESC student;

# 插入学生数据
INSERT INTO student(id, NAME, gender, math_score, english_score)
VALUES(1,'张三', 1, 90, 90.5);

# 查询数据是否插入成功
SELECT * FROM student;

# 批量插入数据: 值得注意的是列与列要一一对应
INSERT INTO student
(id, NAME, gender, math_score, english_score) 
VALUES
(2, '李四', 0, 100, 100),
(3, '王五', 1, 60, 70.5), 
(4, '赵六', 1, 80, 88.5);

# 查询现在student的数据有哪些【下面的SQL语句是最基础的查询结构】
SELECT * FROM student;

4.1 简单查询

# 1. 查询指定列
# 格式： SELECT 字段1, 字段2... FROM 表名;
# 例子： 查询id， name列的学生信息
SELECT id, NAME FROM student;

# 2. 指定列的别名进行查询【作为国人有时候看咱们的汉语才是正道】
# 格式： SELECT 字段1 AS 别名1, 字段2 AS 别名2... FROM 表名;
# 例子： 查询id别名为学生编号， name别名为学生姓名【别名不用加“ '' ”引号】
SELECT id AS 学生编号, NAME AS 学生姓名 FROM student;

# 3. 清除重复值
# 格式： SELECT DISTINCT 字段名 FROM 表名;
# 例子： 查询学生的性别有哪些【1表示男， 0表示女； 本次案例可能不是很适合这个场景， 你懂的】
SELECT DISTINCT gender FROM student;

4.2 稍微复杂一点

# 查询结果参与运算【实际中可能会涉及到薪资、工龄等数据】
# 格式1 ： SELECT 列名1 + 固定值 FROM 表名；
# 例子1： 每个学生的数学成绩加10分
SELECT math_score + 10 FROM student;
# 例子2： 例子1中的进行查询的后字段名显示的结果为 “math_score + 10”这样很不好看， 这就是我们使用AS别名的时候到了
SELECT (math_score + 10) AS 数学最终成绩 FROM student;

# 格式2： 某列数据和其他列数据参与运算
# 例子： 查询学生总成绩
SELECT id, NAME, (math_score + english_score) AS 总成绩 FROM student; 

4.3 条件查询

# 我们在进行数据查询的时候， 都是需要查询自己想要的数据， 那么自己心中一定有对数据有一定的要求， 这个要求对于数据来说就是满足的某种条件。例如： 我们要查询数学成绩大于80分的几个同学姓名， 那么低于80分的就要给他过滤掉， 这就是条件查询的意义所在！
# 关键字： WHERE
# 格式： SELECT 字段名 FROM 表名 WHERE 所需要的条件;

比较运算符	说明
>、<、<=、>=、= 、<>	<>在SQL中表示不等于, 在msyql中也可以使用!=, 但是没有==
BETWEEN...AND	在一个范围内, 比如: BETWEEN 80 AND 100相当于条件在80到100之间, 这是一个闭区间(也就是包括首尾的80和100算在其中)
IN()	集合表示多个值, 使用逗号将每个值分离, IN从英语上来说是在其中的意思, 只要满足其中一个就会查询出来
LIKE	模糊查询, [本次案例不适合LIKE的讲解, 在未来的更新中会再次讲解]
IS NULL	查询某一列为NULL的值, 注意: 不能写成 = NULL [本次案例不适合IS NULL的讲解, 在未来的更新中会再次讲解]

# 例子1: 查询数学成绩大于80分的学生都有谁
SELECT NAME, math_score  # 第三步: 你想要显示的信息是姓名和分数, 还是该学生的所有信息
FROM student # 第一步: 先确定是在哪个表中查询
WHERE math_score > 80;   # 第二步: 查询的条件是什么

# 例子2: 查询英语成绩在60分到90分之间的学生都有谁
SELECT NAME, english_score
FROM student
WHERE english_score BETWEEN 60 AND 90;

# 例子3: 查询数学成绩为60或者80或者100的学生都有谁
SELECT NAME, math_score
FROM student
WHERE math_score IN(60, 80, 100);

逻辑运算符	说明
and 或 &&	与, SQL中建议使用前者, 后者并不通用
or 或 ll	或
not 或 !	非

# 例子1: 查询数学和英语成绩都是100分的学生都有谁
SELECT NAME AS 姓名 , math_score AS 数学成绩, english_score AS 英语成绩
FROM student
WHERE math_score=100 AND english_score=100;

# 例子2: 查询数学成绩大于90[包括90]或者英语成绩大于90[包括90]的学生都有谁
SELECT NAME AS 姓名 , math_score AS 数学成绩, english_score AS 英语成绩
FROM student
WHERE math_score >= 90 OR english_score >= 90;

# 查询数学成绩不合格的学生都有谁, [不合格的学生分数小于60[不包括60]]
SELECT NAME AS 姓名 , math_score AS 数学成绩
FROM student
WHERE math_score < 60;
# 备注: 本人设置的每个学生都及格了, 毕竟大学时代最盼望的就是能够及格☺