目录
一、数据库相关概念
MySQL、Oracle等数据库实际上并非是数据库而是数据库管理系统。
1、MySQL环境变量配置
- win+R 键 输入sysdm.cpl进入系统高级设置，进入环境变量
- 在系统环境变量新建一个MYSQL_HOME变量，将mysql安装包地址粘贴
- 在系统Path变量中新建%MYSQL_HOME%\bin
2、新建配置文件
- 在mysql的安装目录下新建my.ini文件 复制代码
[client] port=3306 default-character-set=utf8 [mysqld] # 设置为自己MYSQL的安装目录 basedir=D:\2ProgramTool\MySQL\mysql-5.7.24-winx64 # 设置为MYSQL的数据目录 datadir=D:\2ProgramTool\MySQL\mysql-5.7.24-winx64\\data port=3306 character_set_server=utf8 #跳过安全检查 skip-grant-tables
3、DOS命令安装mysql
- 初始化mysql，在dos命令中输入命令 mysqld --initialize-insecure 回车后会在安装目录下生成Data文件夹。中间可能会报错：Install/Remove of the Service Denied!，则在电脑中搜索cmd以管理员身份运行。
- 继续输入：net start mysql 启动mysql服务
- 修改数据库初始账户密码：mysqladmin -u root password 12345 账户名：root 密码：12345
- 开启客户端与服务的连接：mysql -uroot -p1234
- 退出mysql服务：exit、quit
- 登录其他人的数据库：
mysql -u用户名 -p密码 -h要连接的mysql服务器ip地址 -p端口号
二、关系型数据库
电脑中安装好mysql服务后，电脑就能够作为数据库服务器与客户端进行交互
数据库在电脑中就是以文件夹的形式存在，数据表和数据以文件形式存在
三、SQL语句
1、SQL简介
2、SQL通用语法
--注释后一定要加一个空格 。
3、SQL的分类
4、DDL
DDL操作库
mysql中自带的几个数据库
| information_schema ---记录了mysql中有哪些库、哪些表的信息，它存储数据是采用视 图，而视图是一种逻辑表不存在具体的文件
| mysql ---存储mysql中最为核心的一些信息，比如权限、安全等信息
| performance_schema ---存储mysql中性能相关的信息
| sys ---存储系统相关的信息
- 创建库
CREATE DATABASE 库名; #基本创建方式
CREATE DATABASE IF NOT EXISTS 库名; #先判断数据库是否存在再创建- 展示所有库
SHOW DATABASES;- 删除库
DROP DATABASE 库名; #基本方式删除库
DROP DATABASE IF EXISTS 库名； #先判断库是否存在再删除- 使用库
USE 库名;
#查看当前正在使用的数据库
SELECT DATABASE();MySQL数据类型
DOUBLE类型 有两个参数
字段名 DOUBLE(总长度，小数点后保留的位数) score double(5,2) 总长度包括小数位数
小数类型使用decimal使用double、float会引起精度确实，小数过大时将小数拆分成整数和小数分开存储。
char (10)和 varchar(10)的区别
两者都需要指定定长，但是如果存入一个占两位字符空间的数据，char中仍然占用10个字符空间，而varchar只占用2个字符空间，大小在范围内可变。所以char的存储性能高，varchar性能低，但是char会浪费不必要的空间，以空间换时间，varchar以时间换空间
当知道具体的字符个数时可以使用char，不知道具体字符时用varchar
MySQL5.0之后，有多少个字符括号内就填多少
DML操作表
- 创建表(CREATE)
CREATE TABLE 表名 (
字段名1 数据类型1，
字段名2 数据类型2，
...
#最后一行末尾不能加逗号
字段名n 数据类型n
);创建表案例
- 查询表(RETRIEVE)
#查询当前数据库下所有表的名称
SHOW TABLES;
#查询表结构 descripition 查询到的只是结构不是数据
DESC 表名称;- 修改表(ALTER)
#1、修改表名
ALTER TABLE 表名 RENAME TO 新的表名；
#2、添加一列
ALTER TABLE 表名 ADD 列名 数据类型；
#3、修改列名的数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型；
#4、修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
#5、删除列
ALTER TABLE 表名 DROP 列名；- 删除表(DROP)
#删除表
DROP TABLE 表名;
#删除表时判断表是否存在
DROP TABLE IF EXISTS 表名;5、DML(增删改)
- 添加数据(INSERT)
#查询数据
SELECT*FROM stu;
#给指定列添加数据
INSERT INTO STU (ID,NAME) VALUES (3,'王五');
#给所有列添加数据
INSERT INTO STU (ID,NAME,GENDER,BIRTHDAY,SCORE,EMAIL,TEL,STATUS) VALUES (4,'赵四','男','2000-10-10',67,'WW@1234','12478963456','1');
#给所有列添加数据时可省略列名不写，建议不省略
INSERT INTO STU VALUES (4,'赵四','男','2000-10-10',67,'WW@1234','12478963456','1');
#一次添加多个数据
INSERT INTO STU VALUES (4,'赵四','男','2000-10-10',67,'WW@1234','12478963456','1'),(4,'赵四','男','2000-10-10',67,'WW@1234','12478963456','1');- 特殊的增加数据的方式
-- 特殊的新增
-- 在insert语句中增加其他关键字
-- 主键或者唯一键相同不会增加，否则新增
-- ignore
insert ignore into test(tid,num,name,gendar,birthday)
values(7499,201,'朱雀','男','1990-10-10');
-- 只修改指定的字段
-- on duplicate key Update 修改的字段
insert into test(tid,num,name,gendar,birthday)
values(7369,222,'朱雀','男','1990-10-10') on duplicate key update name='变化',num=333
-- replace into
-- 与ignore作用相同
-- 主键或者唯一键相同会将所有数据修改，否则新增
replace into test(tid,num,name,gendar,birthday)
values(7499,201,'朱雀','女','1990-10-10');
复制表
create table copy_test
select * from test;- 删除数据(DELETE)
#从表中删除数据
DELETE FROM STU WHERE NAME = '李四';
#如果不加where会删除表中所有数据
DELETE FROM STU;- 修改数据(UPDATE)
#修改数据
UPDATE STU SET GENDER = '女' WHERE NAME = '李四';
#对多个字段修改
UPDATE STU SET ID = 4,BIRTHDAY = '2010-2-10' WHERE NAME = '李四';
#注意：如果UPDATE语句没有加WHERE条件，则会将表中的所有数据全部修改！！！6、DQL
6.1、基础查询
#基础查询
-- 1、查询某列信息
SELECT NAME,TEL FROM STU;
-- 查询所有列的数据，列名的列表可以使用*替代
-- SELECT * FROM STU *号表示列表中所有的列名，并不建议使用！！
-- 使用 SELECT 列名 FROM STU 查询所有数据
-- 2、使用distinct去除查询到的重复记录
SELECT DISTINCT ADDRESS FROM STU;
-- 3、使用as关键字给字段起别名，增强可读性，as可以省略，中间要加空格
SELECT NAME 姓名,MATH_SC 数学成绩 FROM STU;6.2、条件查询(WHERE)
-- 1、查询年龄大于26的
SELECT * FROM STU WHERE AGE > 26;
-- 2、查询大于等于26
SELECT * FROM STU WHERE AGE >= 26;
-- 3、大于等于20 且 小于等于30
SELECT * FROM STU WHERE AGE >= 20 AND AGE <= 30;
-- 4、BETWEEN AND
SELECT * FROM STU WHERE AGE BETWEEN 20 AND 30;
-- 5、查询日期在1980-1-1 到 1999-1-1
SELECT * FROM STU WHERE BIRTHDAY BETWEEN '1980-1-1' AND '1999-1-1';
-- 6、查询年龄等于25的信息，不等于！=、<>两种
SELECT * FROM STU WHERE AGE = 25;
-- 7、查询多个年龄的信息
SELECT * FROM STU WHERE AGE = 25 OR AGE = 88 OR SEX = '女';
#使用关键字in表示查询在该范围内的数据
SELECT * FROM STU WHERE AGE IN (25,88,45);
-- 8、查询值为null的数据
#null值的比较不能直接使用等于号，要使用is null,is not null
SELECT * FROM STU WHERE AGE IS NULL;模糊查询
-- 模糊查询 like
/*
通配符：
1、_:代表单个任意字符
2、%：代表任意个数字字符
*/
-- 1、查询姓‘马’的学员信息，条件不明使用模糊查找
SELECT * FROM STU WHERE NAME LIKE '王%';
-- 2、查询第二个字是'五'的信息
SELECT * FROM STU WHERE NAME LIKE '_五';
-- 3、查询名字包含明的信息
SELECT * FROM STU WHERE NAME LIKE '%明%';6.3、排序查询(ORDER BY)
/*
排序方法：
ASC:升序排列
DESC：降序排列
*/
-- 1、查询信息，按年龄升序
SELECT * FROM STU ORDER BY AGE ; -- 默认升序
-- 2、查询信息，按数学成绩降序
SELECT * FROM STU ORDER BY MATH_SC DESC;
-- 3、按照数学成绩降序排序，如果成绩一样，再按照年龄升序
#多条件排序时，前一个相同时才会执行后一个
SELECT * FROM STU ORDER BY MATH_SC DESC,AGE ASC;6.4、分组查询(GROUP BY)
聚合函数
-- 1、统计一共有多少学生
#COUNT()列名不能为空，不会统计值为null的信息
SELECT COUNT(NAME) FROM STU;
#可以使用*代表列名
SELECT COUNT(*) FROM STU;
-- 2、数学成绩最高分
SELECT MAX(MATH_SC) FROM STU;
-- 3、数学最低分
SELECT MIN(MATH_SC) FROM STU;
-- 4、数学平均分
SELECT AVG(MATH_SC) FROM STU;
-- 5、数学总分
SELECT SUM(MATH_SC) FROM STU;分组查询
#查询男女同学各自的平均分
SELECT SEX,AVG(MATH_SC) FROM STU GROUP BY SEX;
#查询男女同学各自的平均分,以及各自的人数
SELECT SEX,AVG(MATH_SC),COUNT(*) FROM STU GROUP BY SEX;
#查询男女同学各自的平均分,以及各自的人数 要求分数低于60分不参与分组
#分组之前做条件查询
SELECT SEX,AVG(MATH_SC),COUNT(*) FROM STU WHERE MATH_SC > 60 GROUP BY SEX;
#查询男女同学各自的平均分,以及各自的人数 要求分数低于50分不参与分组,分组之后人数大于2个的
SELECT SEX,AVG(MATH_SC),COUNT(*) FROM STU WHERE MATH_SC > 50 GROUP BY SEX HAVING COUNT(*) > 2;6.5、分页查询(LIMIT)
翻页查询数据，降低系统性能消耗，一页一页的展示在网页。
#从0开始查询，查询3条数据
SELECT * FROM STU LIMIT 0 , 2;
#每页显示两条数据，查询第一页
SELECT * FROM STU LIMIT 0 , 2;
#每页显示两条数据，查询第二页
SELECT * FROM STU LIMIT 2 , 2;
#每页显示两条数据，查询第三页
SELECT * FROM STU LIMIT 4 , 2;
-- 公式：每页的起始索引 = （每一页的页码-1）* 每页的数据四、函数
1、字符串函数
五、约束
1、约束概念和分类
2、基本约束使用
定义约束名规范：主键约束名以 pk_ 开头，唯一约束名以uk_开头，普通索引名以idx_开头
CREATE TABLE EMP(
ID INT PRIMARY KEY AUTO_INCREMENT, -- 员工id，主键且自增长
NAME VARCHAR(20) NOT NULL UNIQUE, -- 员工姓名，非空且唯一
DATE DATE NOT NULL, -- 员工入职时间，非空
SALARY DOUBLE(7,2) NOT NULL, -- 员工薪水，非空
BONUS DOUBLE(7,2) DEFAULT 0 -- 员工奖金，默认为0
#在表末尾声明约束
CONSTRAINT KEY_EMP PRIMARY KEY(ID)
);
-- auto_increase 自增长。
INSERT INTO EMP(NAME,DATE,SALARY,BONUS) VALUES('张三','2022-1-1',4000,1000);
#主键使用默认值
INSERT INTO EMP(ID,NAME,DATE,SALARY,BONUS) VALUES(NULL,'张三2','2022-1-1',4000,1000);
INSERT INTO EMP(ID,NAME,DATE,SALARY,BONUS) VALUES(NULL,'张三3','2022-1-1',4000,1000);
#删除唯一约束
ALTER TABLE EMP DROP INDEX NAME;
#删除主键约束
ALTER TABLE EMP DROP PRIMARY KEY;
#删除默认约束
ALTER TABLE EMP ALTER BONUS DROP DEFAULT;
#删除非空约束
ALTER TABLE EMP MODIFY SALARY DOUBLE(7,2);3、外键约束
-- 再创建员工表
CREATE TABLE EMP1(
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
AGE CHAR(4),
DEP_ID INT,
#添加外键约束
CONSTRAINT FK_DEPT FOREIGN KEY(DEP_ID) REFERENCES DEPT(ID)
);
-- 先创建部门表
CREATE TABLE DEPT(
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10), -- 部门名称
ADDR VARCHAR(20) -- 地址
);
-- 向部门表中添加数据
INSERT INTO DEPT(NAME,ADDR) VALUES
('研发部','广州'),
('销售部','广州')
-- 向员工表添加数据
INSERT INTO EMP1(NAME,AGE,DEP_ID) VALUES
('张三',23,1),
('张三',23,1),
('张三',23,2),
('张三',23,2)
-- 此时删除部门表中的数据会出错，必须先开除部门的成员才能解散部门六、数据库设计
1、软件研发的步骤
2、数据库设计
3、表关系
3.1、一对多
3.2、多对多
#创建订单表
CREATE TABLE tb_order(
ID INT PRIMARY KEY AUTO_INCREMENT,
PAYMENT DOUBLE(10,2) NOT NULL,
PAY_TYPE VARCHAR(10) NOT NULL,
STATUS CHAR(3) NOT NULL
);
#创建商品表
CREATE TABLE tb_goods(
ID INT PRIMARY KEY AUTO_INCREMENT,
TITLE VARCHAR(20) NOT NULL,
PRICE DOUBLE(7,2)
);
#创建订单商品中间表
CREATE TABLE tb_other(
ID INT PRIMARY KEY AUTO_INCREMENT,
ORDER_ID INT,
GOODS_ID INT,
COUNT INT
)
-- 创建表后添加外键约束
# 中间表的外键 对应 订单表的主键
ALTER TABLE TB_OTHER ADD CONSTRAINT FK1_OTHER FOREIGN KEY (ORDER_ID) REFERENCES TB_ORDER(ID);
ALTER TABLE TB_OTHER ADD CONSTRAINT FK2_OTHER FOREIGN KEY (GOODS_ID) REFERENCES TB_GOODS(ID);3.3、一对一
#创建用户表
CREATE TABLE USER(
ID INT PRIMARY KEY ,
PHOTO VARCHAR(10),
NAME VARCHAR(8),
AGE INT,
DESC_ID INT UNIQUE -- 外键设唯一
)
#创建用户详情表
CREATE TABLE USER_DESC(
ID INT PRIMARY KEY,
CITY VARCHAR(10),
EDU VARCHAR(20)
)
#为用户表添加外键约束
ALTER TABLE USER ADD CONSTRAINT FK_DESC FOREIGN KEY (DESC_ID) REFERENCES USER_DESC(ID);4、数据库设计案例
七、多表查询
1、内连接
-- 隐式内连接
#查询两表满足条件的所有情况 A,B交集
SELECT * FROM DEPT,EMP WHERE DEPT.DID = EMP.DEPT_ID;
#仅查询几个字段 表名太长可以用别名
SELECT t1.DEP,t1.ADDR,t2.NAME,t2.AGE FROM DEPT t1,EMP t2 WHERE t1.DID = t2.DEPT_ID;
-- 显示内连接
SELECT * FROM EMP INNER JOIN DEPT ON DEPT.DID = EMP.DEPT_ID;
#INNER 可省略
SELECT * FROM EMP JOIN DEPT ON DEPT.DID = EMP.DEPT_ID;2、外连接
-- 左外连接
SELECT * FROM EMP LEFT JOIN DEPT ON EMP.DEPT_ID = DEPT.DID;
-- 右外连接
SELECT * FROM EMP RIGHT JOIN DEPT ON EMP.DEPT_ID = DEPT.DID;3、子查询
-- 单行单列子查询
#查询年龄大于张三的人
#1、先查询张三的年龄
SELECT AGE FROM EMP WHERE NAME = '张三';
#2、再查询年龄大于他的人
SELECT * FROM EMP WHERE AGE > (SELECT AGE FROM EMP WHERE NAME = '张三');
-- 多行单列子查询
-- 查询‘管理部’ 和‘开发部’中编号对应的所有员工信息
#1、先查询‘管理部’和‘开发部’的编号
SELECT DID FROM DEPT WHERE DEP = '管理部' OR DEP = '开发部';
#2、查询编号对应的所有员工信息
SELECT * FROM EMP WHERE DEPT_ID IN (SELECT DID FROM DEPT WHERE DEP = '管理部' OR DEP = '开发部');
-- 多行多列子查询
#年龄大于23的员工信息和部门信息
#1、查询年龄大于23的员工信息
SELECT * FROM EMP WHERE AGE > 23;
#2、将查询到的员工信息作为一个虚拟表做内连接查询
SELECT * FROM (SELECT * FROM EMP WHERE AGE > 23) T1,DEPT WHERE T1.DEPT_ID = DEPT.DID;#给emp2添加外键
ALTER TABLE emp2 ADD CONSTRAINT fk_job FOREIGN KEY (job_id) REFERENCES job(id);
ALTER TABLE emp2 ADD CONSTRAINT fk2_dept FOREIGN KEY (dept_id) REFERENCES dept1(id);
#> 1022 - Can't write; duplicate key in table '#sql-1508_5' 有名称相同的键，复查发现数据库将fk_job与fk_dept视为了相同键。in、any和all的使用
in 是指范围内的任意一个，any和all要搭配符号使用，后面接着子查询
= any 与任意一条数据都相同 =all 与所有数据都相同
>any 比任意数据都大 >all 大于所有的数据
八、事务
-- 开启事务
BEGIN;
#李四金额减500
UPDATE account SET money = money - 500 WHERE name = '李四';
#模拟中间异常，导致李四-500而张三没有增加
#错误解决了出错了！
#提交事务
COMMIT;
#张三金额加500
UPDATE account SET money = money + 500 WHERE name = '张三';
#出错了回滚事务，回滚到事务开启前的状态
ROLLBACK;开启事务后如果不提交事务，金额发生的改变只有当前用户能够查询到，而其他用户查询的仍然是事务开启前的金额。
事务四大特征
533
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
