[MySQL]数据库学习-基础篇

目录

MySQL的安装，配置，登录退出，卸载

MySQL的数据模型

关系型数据库：

SQL语言

SQL通用语法：

SQL实现增删改：

DDL操作数据库：

DDL操作表：

SQL中的数据类型： 

DML对表中数据进行增删改

SQL实现数据查询： 

基础查询：

条件查询：

排序查询：

 分组查询：

 分页查询：

 约束：

外键约束： 

数据库设计：

数据库设计的步骤：

表关系： 

事务：

---

MySQL的安装，配置，登录退出，卸载

安装好之后，可以通过客户端访问MySQL服务器

1.初始化MySQL

mysqld --initialize -insecure

2.注册MySQL服务

mysqld -install

3.启动和停止MySQL

net start mysql

net stop mysql

4.修改默认账号密码

mysqladmin -u root password 1234

（相当于开了一个超级管理员的账户，并且用户名为 root，密码设为1234）

5.登录MySQL服务器

mysql.exe 为默认客户端，可以通过这个客户端与MySQL服务进行通信

mysql -uroot -p1234//mysql即mysql.exe
//这是在连接本机的MySQL

登录之后就可以写SQL语句了

退出登录：

exit
quit

连接别人的MySQL：

-uroot -p1234 -h127.0.0.1 -P3306//h（IP地址）和P（端口号）默认值是本机的

MySQL的数据模型

关系型数据库：

通过表来存储数据的数据库

 一个数据库下面可以存放多个表，每个表下面又可以存放多条数据。表和数据都是文件，表文件以.frm结尾,数据文件以.MYD结尾。

SQL语言

结构化查询语言，一门操作关系型数据库的编程语言

定义操作所有关系型数据库的统一标准

对于同一需求，每一种数据库的操作方式可能会存在一些不一样的地方，称为方言

SQL通用语法：

 （-- 后面一定要有空格）

SQL实现增删改：

DDL操作数据库：

查询：

查询所有数据库名称

创建数据库： 

 在MySQL数据库管理系统里面，可以通过这个管理系统创建出数据库，在MySQL里面，数据库是以文件夹（在data目录下面，在硬盘上）的形式体现的。

create database db1;//有分号

DDL操作表：

查询表：

 创建表：

删除表：

 修改表：

SQL中的数据类型： 

MySQL支持的类型：数值，日期，字符串。

double:score double(总长度，小数点后保留的位数)

char:name char(10),不管存储几个字节都是占10个字节空间，多了就报错

varchar:存储几个就是几个，因此char存储性能高一点

DML对表中数据进行增删改

-- 给指定列添加数据
INSERT INTO stu(id,name,sex,birthday,score,email,tel,status,address) value(2,'李四','男','1999-11-11','88.88','lisi@itcast.cn','13812345678','1','山东');
-- 如果是给所有列添加数据，列名的列表是可以省略的
INSERT INTO stu value(3,'王五','男','1999-11-11','88.88','wang@itcast.cn','13812345677','1','山东');
-- 批量添加
INSERT INTO stu value(2,'李四','男','1999-11-11','88.88','lisi@itcast.cn','13812345678','1','山东'),
(3,'王五','男','1999-11-11','88.88','wang@itcast.cn','13812345677','1','山东');


-- 查询所有数据
SELECT * FROM stu;

-- 如果不能添加中文数据就执行下面这行代码：
-- ALTER TABLE stu CONVERT to CHARACTER set utf8;

-- 将张三的性别改为女
UPDATE stu set sex = '女' WHERE NAME = '张三';

-- 将张三生日改为1999-12-12，分数改为99.99
UPDATE stu set birthday = '1999-12-12',score = 99.99 WHERE NAME = '张三';

-- 注意：如果没有加where条件，所有数据都会被修改 

-- 查询所有数据
SELECT * FROM stu;

SQL实现数据查询： 

 

 基础查询：

-- 基础查询==================
-- 查询name，age两列
SELECT name,age FROM student; 
-- 查询所有数据,列名的列表可以使用*替代
SELECT * FROM student;
-- 去除重复记录
SELECT address FROM student;
SELECT DISTINCT address FROM student;

-- 起别名
SELECT name AS 姓名,math AS 数学成绩,english AS 英语成绩 FROM student;

条件查询：

-- 条件查询====================

-- 1.查询年龄大于10的学生信息
SELECT * FROM student WHERE age > 10;

-- 2.查询年龄大于等于10的学生信息
SELECT * FROM student WHERE age >= 10;


-- 3.查询年龄大于10，并且小于20的学生信息
SELECT * FROM student WHERE age > 10 and age < 20;
SELECT * FROM student WHERE age > 10 && age < 20;
SELECT * FROM student WHERE age BETWEEN 10 and 20;

-- 4.查询入学日期在2001-02-01到2003-02-01的学生信息
SELECT * FROM student WHERE hire_date BETWEEN '2001-02-01' and '2003-02-01';#date型也可以这样按大小比较


-- 5.查询年龄等于15的学生信息
SELECT * FROM student WHERE age = 15;


-- 6.查询年龄不等于15的学生信息
SELECT * FROM student WHERE age != 15;
SELECT * FROM student WHERE age <> 15;

-- 7.查询年龄等于5/14/15的学生信息
SELECT * FROM student WHERE age = 5 or age = 14 or age = 15 ;
SELECT * FROM student WHERE age in (5,14,15);
SELECT * FROM student WHERE age = 5 || age = 14 || age = 15 ;

-- 8.查询英语成绩为null的学生信息
#NULL值的使用不能用=或!=，而是要用is/is not
SELECT * FROM student WHERE english is NULL;

-- 模糊查询========================
/*
通配符：
（1）_：代表单个任意字符
（2）%：代表任意个数字符
*/
-- 查询姓猴的学生信息
SELECT * FROM student WHERE name LIKE '猴%';

-- 查询第二个字是王的学生信息
SELECT * FROM student WHERE name LIKE '_王%';

-- 查询名字里面有‘小’的学生信息
SELECT * FROM student WHERE name LIKE '%小%';

排序查询：

-- 模糊查询========================
-- 查询学生信息，按照年龄升序排列
SELECT * FROM student ORDER BY age ASC;

-- 查询学生信息，按照数学成绩降序排列
SELECT * FROM student ORDER BY math DESC;


-- 查询学生信息，按照数学成绩降序排列，如果数学成绩一样，则按照英语升序排列(多字段排序)
SELECT * FROM student ORDER BY math DESC,english ASC;

 分组查询：

聚合函数：

-- 分组查询==========================
/*
聚合函数
  * count：统计数量
		* 取值
        1.主键（特点：非空且唯一）
				2.*：所有数据有一个不为空，*就能统计这一行（而且*会计算速度最快的那一列进行统计）
*/
-- 1.统计班级一共有多少个学生
SELECT COUNT(id) FROM student;#count统计的列名不能为空即可

-- 2.查询数学成绩的最高分
SELECT MAX(math) FROM student;

-- 3.查询数学成绩的最低分
SELECT MIN(math) FROM student;

-- 4.查询数学成绩总分
SELECT SUM(math) FROM student;

-- 5.查询数学成绩的平均分
SELECT AVG(math) FROM student;

#NULL不参与聚合函数的运算

-- 分组查询==========================
-- 查询男生女生各自的数学平均分
SELECT sex,AVG(math) FROM student GROUP BY sex;#查询的要么是分组字段，要么是聚合函数，查其他的没有意义

-- 查询男生女生各自的数学平均分，以及各自人数
SELECT sex,AVG(math),COUNT(*) FROM student GROUP BY sex;

-- 查询男生女生各自的数学平均分，以及各自人数，要求；分数低于80的不参与分组
SELECT sex,AVG(math),COUNT(*) FROM student WHERE math>80 GROUP BY sex;

-- 查询男生女生各自的数学平均分，以及各自人数，要求；分数低于80的不参与分组，分组之后的人数要大于两个
SELECT sex,AVG(math),COUNT(*) FROM student WHERE math>80 GROUP BY sex HAVING COUNT(*)>2;

 分页查询：

-- 分页查询==========================

-- 从0开始查询，查询3条数据
SELECT * FROM student LIMIT 0,3;

-- 每页显示3条数据查询第1页数据
SELECT * FROM student LIMIT 0,3;

-- 每页显示3条数据查询第2页数据
SELECT * FROM student LIMIT 3,3;

-- 每页显示3条数据查询第3页数据
SELECT * FROM student LIMIT 6,3;

#起始索引原理类似于数组

 约束：

约束是作用于表中列上的规则，用于限制进入表的数据；

约束的存在保证了数据的正确性，有效性，完整性。

CREATE TABLE emp (
	id INT PRIMARY KEY auto_increment,-- 员工id，主键并且自增长
	ename VARCHAR (50) NOT NULL UNIQUE,-- 员工姓名，非空且唯一
	joindate DATE NOT NULL,-- 入职日期，非空
	salary DOUBLE (7, 2) not NULL,-- 工资，非空
	bonus DOUBLE (7, 2) DEFAULT 0-- 奖金，如果没有奖金则默认为0；注意：null也算值
);

#约束也可以在建完表之后在加
#auto_increment:自增长

外键约束： 

外键用来让两个表的数据之间建立连接，保证数据的一致性和完整性。

数据库设计：

数据库设计的步骤：

构造数据存储模型，建立数据库中的表结构以及表与表之间的关联关系。 

表关系： 

 

一对一：

在任意一方进入外键，关联另一方的主键，并设置外键唯一（UNIQUE） 

一对多：

实现方式：在多的一方建立外键，指向另一方的主键

-- 部门表
CREATE TABLE dept(
	id INT PRIMARY key auto_increment,
	dep_name VARCHAR(20),
  addr VARCHAR(20)
);
-- 员工表
#把dep指定为外键，关联dept部门表中id这个主键
#员工表为从表，部门表为主表
#部门表作为主表，应该先创建
#添加数据时，应该先添加部门的数据
#要删除dept中某一列，必须先删除emp中对应的数据
CREATE TABLE emp(
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20),
	age INT,
	dep_id INT,

	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)#fk_emp_dept为外键名称
);
DELETE FROM emp;
DELETE FROM dept;
-- 添加两个部门
INSERT INTO dept(id,dep_name,addr)
VALUES
(1,'研发部','广州'),(2,'销售部','深圳');

-- 添加员工，dep_id表示员工所在部门
INSERT INTO emp(id,name,age,dep_id)
VALUES
(1,'张三',20,1),
(2,'李四',20,1),
(3,'王五',20,1),
(4,'赵六',20,2),
(5,'孙七',22,2),
(6,'周八',18,2);

SELECT * FROM dept;
SELECT * FROM emp;

-- 删除外键：
alter table emp drop FOREING key fk_emp_dept;
-- 建完表后，添加外键：
alter table emp add CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id);

多对多：

实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

-- 订单表
CREATE TABLE tb_order(
	id INT PRIMARY KEY auto_increment,
	payment DOUBLE(10,2),
	payment_type TINYINT,
	status TINYINT
);

-- 商品表
CREATE TABLE tb_goods(
	id INT PRIMARY KEY auto_increment,
	title VARCHAR(100),
	price DOUBLE(10,2)
);

-- 订单商品中间表
CREATE TABLE tb_order_goods(
	id INT PRIMARY KEY auto_increment,
	order_id INT,
	goods_id INT,
	count INT
);

-- 建完表后，添加外键
ALTER TABLE tb_order_goods ADD CONSTRAINT fk_order_id FOREIGN KEY(order_id) REFERENCES tb_order(id);
ALTER TABLE tb_order_goods ADD CONSTRAINT fk_goods_id FOREIGN KEY(goods_id) REFERENCES tb_goods(id);

多表查询：

直接用select *from 表1,表2会产生笛卡尔积（得到的结果会是两个集合的所有组合情况）

内连接：

-- 隐式内连接
-- 查询emp的id，name，dept的dep_name
SELECT emp.id,emp.name,dept.dep_name FROM emp,dept WHERE emp.dep_id=dept.id;

-- 给表起别名
SELECT t1.id,t1.name,t2.dep_name FROM t1,t2 WHERE t1.dep_id=t2.id;

-- 显式内连接
SELECT * FROM emp INNER JOIN dept ON emp.dep_id = dept.id;#INNER可以省略不写

外连接：

-- 左外连接
-- 查询emp表所有数据和对应的部门信息
SELECT * FROM emp LEFT JOIN dept ON emp.dep_id=dept.id;

-- 右外连接
-- 查询dept表所有数据和对应的员工信息
SELECT * FROM emp RIGHT JOIN dept ON emp.dep_id=dept.id;
SELECT * FROM dept LEFT JOIN emp ON emp.dep_id=dept.id;

  子查询：

查询中的嵌套查询。

-- 单行单列
-- 查询年龄大于张三的员工信息
SELECT * FROM emp;

-- 1.查询猪八戒的工资
SELECT age FROM emp WHERE name = '张三';

-- 2.查询工资高于猪八戒的员工信息
SELECT * FROM emp WHERE age > 20;

-- 合二为一
SELECT * FROM emp WHERE age > (SELECT age FROM emp WHERE name = '张三');


-- 单行多列
-- 查询研发部和市场部的所有员工信息
SELECT id FROM dept WHERE dep_name = '研发部' or dep_name = '销售部';
SELECT * FROM emp WHERE dep_id in (SELECT id FROM dept WHERE dep_name = '研发部' or dep_name = '销售部');#in


-- 多行多列
-- 查询年龄大于18员工信息和部门信息
-- 查询年龄大于18员工信息
SELECT * FROM emp WHERE age > 18;
SELECT * FROM emp ,dept WHERE emp.dep_id = dept.id;
SELECT * FROM (SELECT * FROM emp WHERE age > 18)t1 ,dept WHERE t1.dep_id = dept.id;

事务：

开启事务->（执行成功之后）->提交事务，（出现异常）->回滚事务

只有提交事务之后数据才真正更改 

-- 转账操作

-- 开启事务
BEGIN;
-- 李四金额-500
UPDATE account SET money = money - 500 WHERE name = '李四';
-- 张三金额+500
UPDATE account SET money = money + 500 WHERE name = '张三';
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

事务的四大特征：