数据库及SQL语句

一、基本概念

1. 数据库：

   数据库是一个存储和组织数据的容器，MySQL可以管理多个数据库。

2. 表：

   表是数据库中的基本组成单元，用来存储具有相同结构的数据。每个表都有一组列和行。

3. 列：

   表中的每个数据字段称为列，每列有一个数据类型（如整数、字符串、日期等）来定义其内容。

4. 行：

   表中的每个记录称为行，每行包含了表定义的所有列的数据。

5. 主键：

   主键是唯一标识表中每行数据的列，它确保每行都有一个唯一的标识符。

6. 外键：

   外键是一个表中的字段，它指向另一个表中的主键，用来建立表之间的关系。

7. 索引：

   索引是提高数据库查询效率的数据结构，可以加速数据的检索速度。

 二、SQL语句

-- 列出所有的数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE study DEFAULT CHARACTER SET utf8;

-- 数据库表的操作
-- 切换数据库
USE study;

-- 创建表
CREATE TABLE student(
	id INT,
	`name` CHAR(10),
	age INT,
	gender CHAR(1)
);

-- 查看所有的表
SHOW TABLES;
-- 查看表的结构
-- description 描述
DESC student;

-- 删除表
DROP TABLE student;


-- 修改表的结构
-- 添加字段
ALTER TABLE student ADD COLUMN address CHAR(10);
-- 删除字段
ALTER TABLE student DROP COLUMN address;
-- 修改表的字段
ALTER TABLE student CHANGE address addr CHAR(20);
-- 修改表的名字
ALTER TABLE student RENAME TO stu;

-- 创建表
CREATE TABLE student(
	id INT，
	`name` CHAR(10),
	age INT,
	geder CHAR(1)
);

-- 插入数据
INSERT INTO student(name,age,gender) VALUES('王五',23,'男');
INSERT INTO student(id,name,age,gender) VALUES(2,'王五1',23,'男');
INSERT INTO student VALUES(3,'李四',22,'男');
-- 插入多条语句
INSERT INTO student(name,age,gender) VALUES('赵六',24,'男'),('赵六2',24,'男');
-- 修改数据
UPDATE student SET age=age+1;
UPDATE student SET name='张三',age=22,gender='女' WHERE id=1;

-- 删除数据
DELETE FROM student;
DELETE FROM student WHERE id=1 OR id=2;
DELETE FROM student WHERE id IN(1,2);
-- TRUNCATE删除表里面所有的数据，自增的id会重新初始化为1
TRUNCATE TABLE student;


-- 查询数据
-- *代表所有列
SELECT * FROM student;-- 在工作中一定不要这么写
-- 查询指定列
SELECT id,`name`,age FROM student;
SELECT id,`name` FROM student;
-- 查询的时候指定常量列
SELECT id,`name` AS '姓名',age AS '年龄',gender AS '性别','计科2001' AS '班级'FROM student;
-- 查询时候合并列，字段可以当成java中的变量进行计算
-- 计算字段（计算列）
SELECT id,`name`,(php+java)  AS '总成绩' FROM student;
-- 查询的时候去除掉重复的数据
SELECT DISTINCT address FROM student;

-- 条件查询 WHERE
-- 逻辑：AND（同时成立） OR（只要有一个成立）
SELECT * FROM student WHERE `name`='王五' AND php>=60;
SELECT * FROM student WHERE `name`='王五' OR php>=60;

-- 比较运算：> < >= <= != <>
SELECT * FROM student WHERE java>=70 AND java<=80;
-- BETWEEN AND (等价于 >= AND <=)
SELECT * FROM student WHERE java>=70 AND java<=90;
SELECT * FROM student WHERE java BETWEEN 70 AND 90;
SELECT * FROM student WHERE address!='青岛';


-- 聚合函数
-- sum() avg() max() min() count()
-- php的总成绩
SELECT SUM(php) AS 'php总成绩' FROM student;
-- php平均成绩
SELECT AVG(php) AS 'php平均值' FROM student;
-- php最大值
SELECT MAX(php) AS 'php最大值' FROM student;


-- 统计学生表里一共有多少学生
SELECT COUNT(*) AS '总人数' FROM student;
SELECT COUNT(id) AS '总人数' FROM student;
-- 注意：指定列不能出现为null的值，否则count会将其忽略


-- 查询排序
-- ORDER BY 字段 ASC（升序）/DESC（降序）不写默认升序
SELECT * FROM student ORDER BY php;
SELECT * FROM student ORDER BY php ASC;
-- 多个条件排序
-- 需求：按php降序，php相同的按java升序
SELECT * FROM student ORDER BY php DESC, java ASC;
-- ORDER BY 要放在sql语句的最后


-- 分组查询（GROUP BY）
-- 需求：查询男女分别有多少人
-- 性别   人数
-- 男   	3
-- 女   	2
-- 分了几组，就去查询几条数据
SELECT gender AS '性别',count(*) AS '人数' FROM student GROUP BY gender;
SELECT gender AS '性别',count(*) AS '人数' FROM student GROUP BY gender HAVING COUNT(*)>=2;
-- WHERE 过滤行，HAVING 过滤组

CREATE TABLE teacher(
	id INT PRIMARY KEY auto_increment,
	`name` CHAR(10) NOT NULL,
	age INT COMMENT '年龄',
	address CHAR(10) DEFAULT '中国',
	UNIQUE KEY(`name`)
);


-- 多表
-- 班级表
CREATE TABLE banji(
	id INT PRIMARY KEY auto_increment,
	`name` VARCHAR(10) NOT NULL
);

-- 学生表
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    age INT,
    gender CHAR(1),
    banji_id INT,
    FOREIGN KEY(banji_id) REFERENCES banji(id)
);
INSERT INTO student(`name`,age,gender,banji_id)
VALUES('张三',20,'男',1),('李四',21,'男',2),('王五',20,'女',1);

INSERT INTO student(`name`,age,gender,banji_id)VALUES('张三',20,'男',3);

-- 课程表
CREATE TABLE course(
	id INT PRIMARY KEY auto_increment,
	`name` CHAR(10) NOT NULL,
		credit INT COMMENT '学分'
);

INSERT INTO course (`name`,credit) VALUES('java',5),('UI',4),('H5',4);

-- 班级-课程表
CREATE TABLE banji_course(
	-- id INT PRIMARY KEY auto_increment,
	banji_id INT,
	course_id INT,
	PRIMARY KEY(banji_id,course_id), -- 联合主键
	FOREIGN KEY(banji_id) REFERENCES banji(id),
	FOREIGN KEY(course_id) REFERENCES course(id)
);
INSERT INTO banji_course(banji_id,course_id) VALUES(1,1),(1,3),(2,1),(2,2),(2,3);

-- 子查询（嵌套查询）一个查询语句的结果是另一个查询语句的条件
-- 查询班级是class2的所有学生信息
SELECT * FROM student WHERE banji_id=2;
SELECT id FROM banji WHERE `name`='class2';
SELECT * FROM student WHERE banji_id=(SELECT id FROM banji WHERE `name`='class2');

-- 查询班级是class3或者class2的所有学生信息
SELECT * FROM student WHERE banji_id=1 OR banji_id=2;
SELECT * FROM student WHERE banji_id IN(1,2);
SELECT id FROM banji WHERE `name`='class1' OR `name`='class2';

SELECT * FROM student WHERE banji_id
IN (SELECT id FROM banji WHERE `name`='class1' OR `name`='class2');

-- 计算字段使用子查询：班级id 班级名字 班级人数
-- 执行这条查询遵循下面的步骤：
-- 1、从banji表检索班级列表
-- 2、对检索出的每个banji，统计其在student表中的数量
SELECT id,`name`,(SELECT count(*) FROM student
WHERE student.banji_id=banji.id) AS '班级人数' FROM banji;


-- 笛卡尔积
SELECT * FROM student,banji;
-- 等值链接
SELECT * FROM student,banji WHERE student.banji_id=banji.id;


-- 内连接
SELECT * FROM student AS s INNER JOIN banji as b;
-- 查询的结果也是一张表，
SELECT * FROM student AS s INNER JOIN banji as b ON s.banji_id=b.id;

-- 查询 班级id 班级名字 班级人数
SELECT b.id,b.`name`,count(*)
FROM student AS s INNER JOIN banji AS b ON s.banji_id=b.id GROUP BY b.id;

-- 学生姓名 班级名字 课程名 学分
SELECT s.`name` AS '学生姓名',b.`name` AS '班级名字',c.`name` AS '课程名',c.credit AS '学分'
FROM student AS s INNER JOIN banji AS b ON s.banji_id=b.id
INNER JOIN banji_course AS bc ON b.id=bc.banji_id
INNER JOIN course AS c ON c.id=bc.course_id
ORDER BY s.id;

-- 模糊查找
SELECT * FROM student WHERE `name` LIKE '张%';
SELECT * FROM student WHERE `name` LIKE '张_';