MySQL基础入门

1、操作数据库表

show database;  --查看所有数据库
use student； --切换 student 数据库
show table; --查看所有的数据库表
describe student; --显示所有的字段
create database person; --创建一个数据库

操作数据库>操作数据库的表>操作数据库的字段

1.1、操作数据库

1.创建数据库

CREATE DATABASE IF NOT EXISTS test;

2.删除数据库

DROP DATABASE IF EXISTS test;

3.使用数据库

USE student;

1.2、操作数据表

1.创建表

CREATE TABLE IF NOT EXISTS `student`(
   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
   `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
   `pwd` VARCHAR(30) NOT NULL DEFAULT '123456' COMMENT '密码',
   `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
   `birthday` DATETIME DEFAULT NULL COMMENT '生日',
   `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
   `email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
   PRIMARY KEY (`id`)
)ENGINE = INNODB CHARACTER SET = utf8mb4;

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释]
)ENGINE = INNODB CHARACTER SET = utf8mb4;

2.修改表

-- 修改表名  ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS student1
-- 增加表的字段 ALTER TABLE 表名 ADD `字段名` 列属性
ALTER TABLE student1 ADD age INT(5)
-- 修改表的字段 （重命名、修改约束）
ALTER TABLE student1 CHANGE age age1 INT(5) -- 字段重命名
ALTER TABLE student1 MODIFY age VARCHAR(11)  -- 修改约束
-- 删除表的字段 
ALTER TABLE student1 DROP age1

3.删除表

DROP TABLE IF EXISTS student;

2、MySQL数据管理

2.1、外键约束(不建议使用)

2.2、DML语言

• insert
• update
• delete

2.3、添加 (注意字段名(可以省略)与值一一对应)

-- 插入语句 （添加）
-- INSERT INTO 表名 （字段名1，字段名2，字段名3...） VALUES (值1，值2，值3。。。)
INSERT INTO `teacher` (`name`) VALUES ('你好')

INSERT INTO `teacher` (`name`) VALUES 
('你好1'),('你好2'),('你好3')

2.4、删除(delete)

-- 删除数据（避免使用，会删除所有数据）
-- DELETE FROM 表名
DELETE FROM `teacher`
-- 删除指定的数据
DELETE FROM `teacher` WHERE id = 1;

2.4、删除(TRUNCATE)

-- 清空表
TRUNCATE `teacher`

作用：完全清空一个数据表，表的索引和结构不变

2.4、更新

-- 修改表
UPDATE `teacher` SET `name` = 'hello' WHERE id = 1
-- 修改多个属性用逗号隔开
UPDATE `student` SET age = 12,`name` = 'hello' WHERE id = 1
-- 不指定条件会全部更改
UPDATE `teacher` SET `name` = 'hello'

• 条件
  1、where子句 运算符id等于某个值 、大于小于某个值 、在某个区间…
  2、在区间BETWEEN… AND…
  3、多条件定位数据使用AND或者OR连接

2.5、DQL语言

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

2.5.1、基本查询

-- 查询所有字段
SELECT * FROM `teacher`
-- 查询指定字段
SELECT id,`name` FROM `teacher`
-- 别名查询 字段和表都可以重名
SELECT `name` AS 姓名,id 
FROM `teacher` AS t
-- 函数查询 Concat(a,b) 查出来字段拼接a
SELECT CONCAT('我的：',`name`) AS 姓名,id 
FROM `teacher` AS t

去重 distinct：去除select查询出来的结果中重复的数据，重复的数据只显示一条

SELECT DISTINCT id FROM `teacher`  --去除重复数据

数据库的列(表达式)

SELECT VERSION() -- 查询系统版本
SELECT 98*556-52 AS '计算结果'; -- 计算
SELECT id+1 FROM `teacher` -- 对查询结果操作

2.5.2、where条件查询
作用：检索数据中符合条件的结果

逻辑运算符 AND 、OR、Not

模糊查询：比较运算符

• IS NULL
• IS NOT NULL
• BETWEEN
• LIKE
• IN

-- '刘%' '刘_'  %代表多个字符 _代表一个字符
SELECT `name` FROM `teacher`
WHERE `name` LIKE '刘%'
-- =====IN======
SELECT `name` FROM `teacher`
WHERE `id` IN (2,6,7)

2.5.2、连表查询

-- =======连表查询 JOIN======
SELECT * FROM student;
SELECT * FROM teacher;
/*
1.分析查询的字段来自哪些表，（连接查询）
2.确定使用哪种连接查询？7种
确定交叉点？（这两个表中的数据哪两个相同的）
判断的条件：学生表中的studentNo = 成绩表中的 studentNo
*/
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo = r.studentNo

-- right Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student s
RIGHT JOIN result r
ON s.studentNo = r.studentNo

-- left Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.studentNo = r.studentNo

-- 增加条件
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.studentNo = r.studentNo
WHERE studentResult IS NULL

-- 三表查询
SELECT s.studentNo,studentName,SubjectName,`studentResult`
FROM student s
RIGHT JOIN result r
ON s.studentNo = r.studentNo
INNER JOIN `subject` sub
ON r.sbujectNo = sub.subjectNo

• 我要查询哪些数据 select …
• 从哪几个表查 from 表 xxx Join 连接的表 on 交叉条件
• 假设存在多表查询，先查两张表，再增加一张表

from a left join b 以a为基准
from a right join b 以b为基准

自连接

自己的表和自己的表连接 ，核心：一张表拆为两张一样的表即可

排序和分页 order by 和 limit

排序：order by 字段名 desc

SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student s
RIGHT JOIN result r
ON s.studentNo = r.studentNo
WHERE studentName = '六号'
ORDER BY studentResult ASC

分页：LIMIT 起始值，每页数据
公式：LIMIT (n-1)*pageSize,pageSize

SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student s
RIGHT JOIN result r
ON s.studentNo = r.studentNo
WHERE studentName = '六号'
ORDER BY studentResult ASC
LIMIT 1,5

子查询

2.6、MySQL函数

2.6.1、常用函数

SELECT ABS(14.5) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND()  -- 0-1之间随机数
-- 字符串函数
SELECT CHAR_LENGTH('sdfdsafsadfds'); -- 字符串长度
SELECT CONCAT ('我','爱','你') -- 拼接字符串
-- 时间和日期
SELECT CURRENT_DATE() -- 获取当前日期
SELECT NOW() -- 当前时间

2.6.2、聚合函数

函数名	描述
COUNT()	计数
SUM()	求和
AVG()	求平均值
MAX()	最大值
MIN()	最小值
…	…

--  =========聚合函数====================
SELECT COUNT(`name`) FROM `student1` -- COUNT(字段) 会忽略NULL值
SELECT COUNT(*) FROM `student1` -- COUNT(*) 不会忽略NULL值
SELECT COUNT(1) FROM `student1` -- COUNT(1) 不会忽略NULL值

SELECT SUM('StudentResult') AS 总和 FROM `student1`
SELECT AVG('StudentResult') AS 平均分 FROM `student1`
SELECT MAX('StudentResult') AS 最高分 FROM `student1`
SELECT MIN('StudentResult') AS 最低分 FROM `student1`

-- 查询不同课程的平均分，最高分  平均分大于80
-- 根据课程SubjectNo分组
SELECT SubjectName,AVG('StudentResult') AS 平均分,MAX('StudentResult'),MIN('StudentResult')
FROM result r
INNER JOIN `subject` s 
ON r.SubjectNo = s.SubjectNo
-- where 平均分>80
GROUP BY r.SubjectNo -- 通过什么字段分组
HAVING 平均分>80