数据库简介:
操作系统中数据存放的载体:
widows.linux和macos都是基于文件的操作系统
数据库系统:是指一个能成为用户提供信息服务的系统。它实现了有组织地。动态地储存大量相关数据的功能,提供了数据处理和信息资源共享的便利手段。
**关系型数据库:**是指使用了关系模型的数据库系统
关系模型中,数据是分类存放的,数据之间可以有联系
例如:学生信息 考试信息 教师信息
关系型数据库被应用在非常多的领域:1.教育系统 2.商业系统 3.医疗系统
关系型数据库可以有效组织和管理大量复杂的数据,所有关系型数据库才是更重要的数据库产品
主流关系型数据库:DB2 Oracle MySQL SQL Server
NoSQL数据库指的是数据分类存放,但是数据之间没有关联关系的数据库系统
MySQL是应用最广泛,普及度最高的开源关系型数据库
Mysql
mysql:
MySQL是最流行的关系型数据库管理系统,在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。
什么是数据库?
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,每个数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据。我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢。所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理的大数据量。所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。RDBMS即关系数据库管理系统(Relational Database Management System)的特点:
- 数据以表格的形式出现
- 每行为各种记录名称
- 每列为记录名称所对应的数据域
- 许多的行和列组成一张表单
- 若干的表单组成database
mysql数据库:
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB公司开发,目前属于Oracle公司。MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
- MySQL是开源的,所以你不需要支付额外的费用。
- MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
- MySQL使用标准的SQL数据语言形式。
- MySQL可以允许于多个系统上,并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
- MySQL对PHP有很好的支持,PHP是目前最流行的Web开发语言。
- MySQL支持大型数据库,支持5000万条记录的数据仓库,32位系统表文件最大可支持4GB,64位系统支持最大的表文件为8TB。
- MySQL是可以定制的,采用了GPL协议,你可以修改源码来开发自己的MySQL系统。
基本的数据库操作步骤:
创建一个数据库:
CREATE DATABASE zhangdong;
删除一个数据库:
DROP DATABASE zhangdong;
创建一个使用uft8字符集的zhang数据库:
CREATE DATABASE zhang CHARACTER SET utf8;
创建一个使用uft8字符集的zd并带校对规则的数据库:
CREATE DATABASE zd CHARACTER SET utf8 COLLATE utf8_bin; #utf8_bin这个是区分大小写的。
查询数据库中的数据,(utf8_bin)区分大小写只能查出小写的内容,如果是默认的话,就可以查出大小写都符合的内容。
SELECT * FROM ti WHERE NAME = "zhangdong";
删除和查询数据库:
-- 演示删除和查询数据库
-- 查看当前数据库服务器中的所有数据库
SHOW DATABASES
-- c查看前面创建的(数据库名字)的定义信息
SHOW CREATE DATABASE zhang
-- 删除前面创建的数据库(在创建数据库和表的时候,为了规避关键字,可以使用反引号解决)
DROP DATABASE db_name;
-- 备份数据库和恢复数据库
-- (在dos命令中运行)mysqldump -u root -p -B zhang zhangdong > d:\\abc.sql
-- 删除数据库
DROP DATABASE db_name;
-- sour d:\\abc.sql
-- 恢复数据库备份,可以把文件直接运行生成新的数据库(也就是原来的数据库)
-- 图形化创建和命令创建
-- 注意:创建表时,要根据需保存的数据创建响应的列,并根据数据的类型定义相应的列类
-- id 整型
-- name 字符串
-- password 字符串
-- birthday 日期
CREATE TABLE zhang (
id INT,
`name` VARCHAR(255),
`password` VARCHAR(255),
`birthday` DATE)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
整型:
-- 演示整型
-- TINYINT -128-127(两种有符号和无符号(0-255))
CREATE TABLE table_z(
id TINYINT
);
INSERT INTO table_z VALUES(-128);#这是一个简单的添加语句
-- INSERT INTO table_z VALUES(-129);#这是一个简单的添加语句
SELECT * FROM table_z
CREATE TABLE table_zh(
id TINYINT UNSIGNED
);#这是一个无符号的
INSERT INTO table_zh VALUES(255);#这是一个无符号的
INSERT INTO table_zh VALUES(-1);#这是一个无符号的
SELECT * FROM table_zh
-- 这是演示int
CREATE TABLE table_zha(
id INT
)
INSERT INTO table_zha VALUES(65535);
INSERT INTO table_zha VALUES(-1);
SELECT * FROM table_zha
bit类型使用
-- 演示bit类型使用
-- 说明
-- 1.bit(m) m在1-64
-- 2.添加数据范围
-- 3.显示按照bit
-- 4.查询时,仍然可以按照数字查询
CREATE TABLE table_zhan(num BIT(8));
INSERT INTO table_zhan VALUES(5);
SELECT * FROM table_zhan;
SELECT * FROM table_zhan WHERE NUM=255;
00000001
00000011
11111111
00000101
演示数据类型decimal,float double使用:
-- 演示数据类型decimal,float double使用
-- 创建一张表
CREATE TABLE table_zhangd(
num1 FLOAT,
num2 DOUBLE,
num3 DECIMAL(30,20)
)
-- 添加数据
INSERT INTO table_zhangd VALUES(88.1234567891234,88.1234567891234,88.1234567891234);
SELECT * FROM table_zhangd
DECIMAL(D,M)这个范围可以自己设置,decimal的范围要比bigint大好多。
例如:(这个数字binint就存放不了,但是decimal就可以存放)89168198198168518694189169891981898198198156
演示字符串类型的使用:
-- 演示
-- CHAR固定长度为255,charvarchar 长度为0-65535
-- 65535-3,三个字节是不可以用的
-- utf8编码,用字节存放字符(要用字节存放数65535-3除以3才是字符存放数)21844
-- GBK编码,用字节存放字符(要用字节存放数65535-3除以2才是字符存放数)32766
-- 创建一个表
CREATE TABLE table_zhangdo(
num1 CHAR(255)
);
CREATE TABLE table_zhangdon(
num VARCHAR(32766)
)CHARACTER SET gbk;
-- 演示字符串类型的使用细节
-- char(4)和varchar(4)表示的是字符,不是字节,不区分字符是汉字还是字母
CREATE TABLE table_zhangdong(
num1 CHAR(4));
-- 存入两个字符也会分配四个字符的空间
INSERT INTO table_zhangdong VALUES('abcd');
SELECT * FROM table_zhangdong;
CREATE TABLE table_1(
num1 VARCHAR(4)
);
INSERT INTO table_1 VALUES('你好张栋');
-- 空格也算字符
-- 存入两个字符也会分配两个字符的空间(需要几个就给你分配几个)
INSERT INTO table_1 VALUES('Na');
SELECT * FROM table_1;
-- 如果varchar不够用,可以考虑使用mediumtext或者longtext
-- 如果想简单点就用text
CREATE TABLE table_2(
content TEXT,
content1 LONGTEXT,
content2 MEDIUMTEXT
);
INSERT INTO table_2 VALUES('你好,世界','你好,世界2021!!!','你好,世界2021');
SELECT * FROM table_2;
时间的相关类型
-- 演示时间的相关类型
-- date datetime TIMESTAMP
CREATE TABLE table_3(
-- 生日
brithday DATE,
job_time DATETIME,
login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
-- 查询表内的内容
SELECT * FROM table_3;
INSERT INTO table_3 (brithday,job_time) VALUES('2021-11-11','2021-11-11 00:00:00');
-- 2021-11-11 2021-11-11 00:00:00 2021-11-10 15:03:10
创建一张员工表:
-- 创建一张员工表
-- id name sex brithday job_time salary resume
CREATE TABLE employee(
id INT,
`name` VARCHAR(32),
sex CHAR(1),
brithday DATE,
job_time DATETIME,
job VARCHAR(32),
salary DOUBLE,
resume TEXT
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
-- 添加数据
INSERT INTO employee VALUES(
1998,'张栋','男','1998-08-07','2022-03-01 12:00:00','数据库',10000,'负责数据库维护工作'
);
SELECT * FROM employee;
添加及修改列内容
--添加及修改列内容
ALTER TABLE employee ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER resume;
SELECT * FROM employee;
DESC employee;
-- 修改列的类型
ALTER TABLE employee MODIFY job VARCHAR(60);
desc employee;
-- 删除列
ALTER TABLE employee ADD pc VARCHAR(32) NOT NULL DEFAULT '' AFTER image;
ALTER TABLE employee DROP pc;
desc employee;
-- 修改表名
RENAME TABLE employee TO emp;
-- x修改表的字符集
ALTER TABLE emp CHARACTER SET utf8;
-- 修改类
ALTER TABLE emp CHANGE `name` `user_name` VARCHAR(32) NOT NULL DEFAULT '';
DESC EMP;
INSERT语句添加数据
-- 使用INSERT语句添加数据
CREATE TABLE goods(
id INT,
goods_name VARCHAR(10),
price DOUBLE
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
-- 添加数据
INSERT INTO goods(id,goods_name,price)VALUES(1,'饼干',5);
INSERT INTO goods(id,goods_name,price)VALUES(2,'牛奶',4),(3,'火腿',2);
SELECT * FROM GOODS;
-- 演示update语句
-- 语句中没有使用where,是把这一列全部都改成20000,
UPDATE emp SET salary = 20000;
SELECT * FROM emp;
-- 用where就可以指定其中一个选项去修改内容
UPDATE emp SET salary = 30000 WHERE user_name = '张栋';
INSERT INTO emp VALUES(
2000,'赵四','男','1990-08-07','2022-03-01 12:00:00','开发',10000,'负责数据库开发工作','图片'
);
-- 在原有的工资上加一万
UPDATE emp SET salary = salary+10000 WHERE user_name = '赵四';
-- 演示delete语句
-- 删除赵四的内容
DELETE FROM emp WHERE user_name= '赵四';
SELECT * FROM emp;
-- 删除所有的表
DELETE FROM tablename;
-- 演示select语句
CREATE TABLE student(
id int not null DEFAULT 1,
`name` VARCHAR(20) not null DEFAULT '',
ch FLOAT not null DEFAULT 0.0,
en FLOAT not null DEFAULT 0.0,
math FLOAT not null DEFAULT 0.0
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
INSERT INTO student(id,name,ch,en,math) VALUES(01,'张三',88,69,50),(02,'张四',50,89,99),(03,'张五',85,65,56),(04,'张六',85,99,80);
SELECT * FROM student;
-- 查询表中所有学生的信息
SELECT * FROM student;
-- 查询所有同学的英语成绩
SELECT `name`,en FROM student;
-- 去重distinct
SELECT DISTINCT FROM student;
-- 查询每个学生的总分
SELECT `name`,(ch+en+math) FROM student;
-- 在所有学生总分加10分
SELECT `name`,(ch+en+math+10) FROM student;
-- 使用别名表示学生的分数
SELECT `name`,(ch+en+math+10)AS ac FROM student;
SELECT `name`AS '名字',(ch+en+math+10)AS '总分' FROM student;
-- 按照名字查询学生成绩
SELECT * FROM student WHERE `name` = '张三';
-- 查询成绩大于80的人
SELECT * FROM student WHERE en>80;
-- 查询总分大于230分的人
SELECT * FROM student WHERE(ch+en+math)>230;
-- 查询两个要求的内容时,用AND连接两个要求,姓什么的同学利用(张%)
-- BETWEEN是闭区间between....and....
-- 查询满足三种任意的要求用or连接,也可以使用IN(区间)
-- select中的order by排序 ASC升序 DESC降序
SELECT * FROM student ORDER BY en ASC;
SELECT `name`,(ch+en+math)AS '总分' FROM student ORDER BY '总分' ASC;
统计函数的使用:
-- 演示mysql的统计函数的使用
-- 统计student里面有多少个学生
SELECT COUNT(*) FROM student;
-- 统计数学成绩大于80的学生有几个?
SELECT COUNT(*) FROM student WHERE math >80;
-- 统计总分大于230的人数有多少?
SELECT COUNT(*) FROM student WHERE (en+ch+math)>230;
-- count(*)和count(列)的区别
-- 解释:count(*)返回满足条件的记录的行数
-- count(列):统计满足条件的某列有多少个,按时会排除null
CREATE TABLE table_4(
`name` VARCHAR(20)
);
INSERT INTO table_4 VALUES('tom'),('jack'),('mary'),(NULL);
SELECT * FROM table_4;
SELECT COUNT(*) FROM table_4;
SELECT COUNT(`name`) FROM table_4;
-- 统计一个班的数学成绩
SELECT SUM(math) FROM student;
-- 统计各科目的总成绩
SELECT SUM(math)AS '数学总分',SUM(en)AS '英语总分',SUM(ch)AS '语文总分'FROM student;
-- 统计各科的班级成绩
SELECT SUM(math+en+ch) FROM student;
-- 统计一个班级的语文平均分
SELECT SUM(ch)/8 FROM student;
-- 求一个班的数学平均分
SELECT AVG(ch) FROM student;
SELECT AVG(math+en+ch) FROM student;
-- 演示max min
SELECT MAX(math+en+ch),MIN(math+en+ch) FROM student;
字符串相关函数的使用:
-- 演示字符串相关函数的使用
-- CHARSET(str)返回字串字符集
SELECT CHARSET(`name`) FROM student;
-- CONCAT(str1,str2,...)连接字符串
SELECT CONCAT(`name`,'match score',math) FROM student;
-- 转换大小写 转换大写(UCASE(str)) 转换小写(LCASE(str))
SELECT UCASE(`name`) FROM student; -- 其中包含小写字母才可以转换
SELECT LCASE(`name`) FROM student; -- 其中包含大写字母才可以转换
-- 从左边和右边取字符
SELECT RIGHT(`name`,1) FROM student;
SELECT LEFT(`name`,1) FROM student;
-- 返回字节的长度
SELECT LENGTH(`name`) FROM student;
-- REPLACE替换内容
-- LTRIM(str) RTRIM(str) 取出左右空格
SELECT LTRIM(' 张栋 ') FROM DUAL;
SELECT RTRIM(' 张栋 ') FROM DUAL;
SELECT TRIM(' 张栋 ') FROM DUAL;
演示数学相关函数:
演示数学相关函数
-- ABS(-10) 绝对值
SELECT ABS(-10) FROM DUAL;
-- CEILING(X) 向上取整
SELECT CEILING(1.5) FROM DUAL;
SELECT CEILING(-1.5) FROM DUAL;
-- BIN 十进制转二进制
SELECT BIN(10) FROM DUAL;
-- conv 进制之间的转换
-- 8是十进制,把十进制8转换成二进制
SELECT CONV(8,10,2) FROM DUAL;
SELECT CONV(8,16,10) FROM DUAL;
-- 求最小值
SELECT LEAST(0,10,-1,-5,50,90,100) FROM DUAL;
-- 求余
SELECT MOD(100,3) FROM DUAL;
-- RAND()返回随机数 其范围为0<=v<=1.0;
SELECT RAND(3)FROM DUAL;
时间日期相关函数:
-- 时间日期相关函数
-- CURRENT_DATE 当前日期
SELECT CURRENT_DATE() FROM DUAL;
-- CURRENT_TIME 当前时间
SELECT CURRENT_TIME() FROM DUAL;
-- CURRENT_TIMESTAMP 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;
-- 创建测试表
CREATE TABLE mes(
id INT,
content VARCHAR(30),
send_time DATETIME
);
-- 添加一条数据
INSERT INTO mes VALUES(1,'1新闻',CURRENT_TIMESTAMP());
SELECT * FROM mes;
-- add statictis
INSERT INTO mes VALUES(2,'2新闻',NOW());
-- 显示新闻信息,发布日期,只显示日期,不显示时间
SELECT id,content,DATE(send_time) FROM mes;
-- 显示十分钟以内发布的信息
SELECT * FROM mes WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE)>=NOW()
-- 计算一下2022年8月7日与1998年8月7日相差多少天
SELECT DATEDIFF('2022-8-7','1998-8-7')FROM DUAL;
-- 求出你活了多长时间了
SELECT DATEDIFF(NOW(),'1998-08-07') FROM DUAL;
-- 如果活了80岁,求出你还能活多少天,
SELECT DATEDIFF(DATE_ADD('1998-08-07',INTERVAL 80 YEAR),NOW()) FROM DUAL;
-- YEAR|MONTH|DAY|DATE (DATETIME)
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2021-10-1') FROM DUAL;
-- UNIX_TIMESTAMP() 返回的是1970-1-1到现在的毫秒数
SELECT UNIX_TIMESTAMP()FROM DUAL;
-- FROM_UNIXTIME() :可以把一个unix_timestamp秒数,转成指定格式的日期
SELECT FROM_UNIXTIME(1636789308,'%Y-%m-%d')FROM DUAL;
SELECT FROM_UNIXTIME(1636789308,'%Y-%m-%d %H:%i:%s')FROM DUAL;
演示加密函数和系统函数:
-- 演示加密函数和系统函数
-- USER() 查询用户,可以查看那些用户,以及登录的ip
SELECT USER() FROM DUAL;
-- DATABASE() 查询当前使用数据库名称
SELECT DATABASE();
-- MD5(STR) 为字符串算出一个md5 32的字符串,常用(用户密码)加密
SELECT MD5('zhang') FROM DUAL;
SELECT LENGTH(MD5('zhang')) FROM DUAL;-- 查询长度
-- 演示用户表,存放密码时是md5
CREATE TABLE table_user(
id INT,
`name` VARCHAR(32) NOT NULL DEFAULT ' ',
`password` CHAR(32) NOT NULL DEFAULT ' '
);
-- 插入数据
INSERT INTO table_user VALUES(100,'张栋',MD5('123456'));
SELECT* FROM table_user;
-- 查询
SELECT * FROM table_user WHERE `name` = '张栋' AND `password`=MD5('123456');
SELECT PASSWORD('123456') FROM DUAL;
-- 加密后的密码:*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
流程控制语句
-- 演示流程控制语句
-- IF(expr1,expr2,expr3),如果exper1为true,则返回exper2否则返回exper3;
SELECT IF(TRUE,'正确','错误') FROM DUAL;
-- IFNULL(expr1,expr2)如果exper1不为空null则返回exper1,否则返回exper2
SELECT IFNULL(NULL,'为空') FROM DUAL;
-- 多重分支结构
SELECT CASE
WHEN FALSE THEN 'ERROR'
WHEN TRUE THEN 'RIGHT'
ELSE 'NULL' END
-- 多重分支结构
SELECT CASE
WHEN FALSE THEN 'ERROR'
WHEN TRUE THEN 'RIGHT'
ELSE 'NULL' END
-- 分页查询
SELECT * FROM student ORDER BY `name` LIMIT 0,3;
SELECT * FROM student ORDER BY `name` LIMIT 3,3;
SELECT * FROM student ORDER BY `name` LIMIT 6,3;
子表查询的演示:
-- 子查询的演示
-- 请思考:如何显示与**同一个部门的所有员工
-- 1.先查询到**de 部门号得到
INSERT INTO student VALUES(10,'张栋',90,80,100);
SELECT id AS 'ID为:'
FROM student
WHERE en=80;
SELECT *
FROM student
WHERE id = (
SELECT id
FROM student
WHERE en=80)
-- 多列子查询
SELECT *
FROM student
WHERE(ch,en,math) = (
SELECT ch,en,math
FROM student
WHERE `name` = '张栋')
表的复制和去重:
-- 表的复制和去重
-- 自我复制数据(蠕虫复制)
-- 如何删掉一张表的重复记录
CREATE TABLE table_5 (
id INT,
`name` VARCHAR(32),
sal DOUBLE,
job VARCHAR(32),
deptno INT
);
DESC table_5;
SELECT * FROM table_5;
-- 如何自我复制
-- 1.如何把student复制到table_6中
CREATE TABLE table_6(
id int not null DEFAULT 1,
`name` VARCHAR(20) not null DEFAULT '',
ch FLOAT not null DEFAULT 0.0,
en FLOAT not null DEFAULT 0.0,
math FLOAT not null DEFAULT 0.0
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
INSERT INTO table_6(id,`name`,ch,en,math)
SELECT id,`name`,ch,EN,math FROM student;
SELECT * FROM table_6;
-- 自我复制
INSERT INTO table_6
SELECT * FROM table_6;
SELECT COUNT(*) FROM table_6; -- 2304条数据
-- 如何删掉一张表重复记录(去重)
-- 1.先创建一张表,table_7,拥有重复的记录
CREATE TABLE table_7 LIKE table_6;
-- 把这个表的内容(列)复制到新的表中
INSERT INTO table_7 SELECT * FROM student;
SELECT * FROM table_7;
-- 如何去重问题?
/*
思路:
1.先创建一张临时表,和table_7的表结构一样
2.把临时表的记录通过distinct关键字处理后,把记录复制到临时表中
3.清除掉临时表中的重复记录,
4.将临时表中的记录复制到table_7中
5.DROP删掉临时表
*/
-- 1.先创建一张临时表,和table_7的表结构一样
CREATE TABLE my_temp LIKE table_7;
-- 2.把临时表的记录通过distinct关键字处理后,把记录复制到临时表中
INSERT INTO my_temp SELECT DISTINCT * FROM table_7;
-- 3.清除掉临时表中的重复记录,
DELETE FROM table_7;
-- 4.将临时表中的记录复制到table_7中
INSERT INTO table_7 SELECT * FROM my_temp;
-- 5.DROP删掉临时表
DROP TABLE my_temp;
SELECT * FROM table_7;
-- 合并查询 -- UNION UNION ALL
外连接:
/*
外连接:
1.左外连接(如果左侧的表完全显示我们就说是左外连接)
2.右外连接(如果右侧的表完全显示我们就说是右外连接)
*/
-- 创建两张表
-- 1.stu 2.exam
CREATE TABLE stu(
id INT,
`name` VARCHAR(32)
);
INSERT INTO stu (id,`name`)VALUES (1,'赵同学'),(2,'田同学'),(3,'孙同学'),(4,'李同学'),(5,'周同学');
-- 测试左外连接添加的数据
INSERT INTO stu VALUES(6,'吴同学');
SELECT * FROM stu;
CREATE TABLE exam(
id INT,
grade INT
);
INSERT INTO exam (id,grade)VALUES (1,80),(2,70),(3,90),(4,60),(5,100);
-- 测试右外连接添加的数据
INSERT INTO exam (id,grade)VALUES (6,86),(7,77);
SELECT * FROM exam;
-- 使用左外连接(显示所有人的成绩,如果没有成绩也要显示这个人的姓名和id,成绩显示为空)
SELECT `name`,stu.id,grade FROM stu,exam WHERE stu.id = exam.id;
-- 改成左外连接,左侧表完全显示,没有数据的就显示为null
/*
SELECT .... FROM table_1 LEFT JOIN table_2 ON 条件,table_1就是左表。table_2就是右表
*/
SELECT `name`,stu.id,grade FROM stu LEFT JOIN exam ON stu.id = exam.id;
-- 使用右外连接,右侧完全显示,没有对应数据就显示为null;
/*
SELECT .... FROM table_1 RIGHT JOIN table_2 ON 条件,table_1就是左表。table_2就是右表
*/
SELECT `name`,stu.id,grade FROM stu RIGHT JOIN exam ON stu.id = exam.id;
/*
在实际开发中,绝大数情况下使用的是我们学过的连接(内连接),
*/
主键:
-- mysql的约束:
-- 基本介绍
/*
约束就是用于确保数据库的数据库满足特定的商业规则
PRIMARY KEY(主键)-基本使用
字段名 字段类型 PRIMARY KEY
用于唯一的标识表示行的数据,当定义主键的约束后,该列不能重复
*/
-- PRIMARY KEY
CREATE TABLE table_8(
id INT PRIMARY KEY, -- 表示id列是主键
`name` VARCHAR(32),
email VARCHAR(32)
);
-- 主键列的值是不能重复的,而且不能为空null
INSERT INTO table_8(id,`name`,email)VALUES (1,'张栋','1791182296@qq.com');
INSERT INTO table_8(id,`name`,email)VALUES (2,'zhangdong','1791182296@souhu.com');
-- INSERT INTO table_8(id,`name`,email)VALUES (1,'张栋','1791182296@qq.com');
-- INSERT INTO table_8(id,`name`,email)VALUES (1,'张栋','1791182296@qq.com')
-- > 1062 - Duplicate entry '1' for key 'PRIMARY'
-- > 时间: 0.001s
/*
一张表中最多只能有一个主键,但是可以是复合主键
主键的指定方式有两种
*/
-- 演示复合主键(id 和name做成一个复合主键)两种必须都相同才违反复合主键原则
CREATE TABLE table_9(
id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(id,`name`) -- 这里就是复合主键
);
INSERT INTO table_9(id,`name`,email)VALUES (1,'张栋','1791182296@qq.com');
INSERT INTO table_9(id,`name`,email)VALUES (2,'张栋','1791182296@souhu.com');
-- INSERT INTO table_9(id,`name`,email)VALUES (1,'张栋','1791182296@qq.com');
-- INSERT INTO table_9(id,`name`,email)VALUES (1,'张栋','1791182296@qq.com')
-- > 1062 - Duplicate entry '1-张栋' for key 'PRIMARY' 违反了复合主键
-- > 时间: 0s
NOT NULL 非空 UNIQUE 的使用
-- NOT NULL 非空
-- UNIQUE 的使用
CREATE TABLE table_10(
id INT UNIQUE, -- 表示id列时不可以重复的。
`name` VARCHAR(32),
email VARCHAR(32)
);
INSERT INTO table_10 VALUES(1,'张栋','1791182296@qq.com');
-- INSERT INTO table_10 VALUES(1,'张栋','1791182296@qq.com')
-- > 1062 - Duplicate entry '1' for key 'id'
-- > 时间: 0.009s
-- 使用细节:如果没有指定not null ,则unique字段可以有多个null
-- 一张表可以有多个unique字段(unique+not null使用效果类似于 primary key)
外键:
-- mysql约束
-- 外键演示:
-- 创建主表(班级表)
CREATE TABLE class(
id INT PRIMARY KEY,
`name` VARCHAR(32)NOT NULL DEFAULT ''
);
-- 创建从表(学生表)
CREATE TABLE c_student(
id INT PRIMARY KEY, -- 学生编号
`name` VARCHAR(32)NOT NULL DEFAULT '',
class_id INT, -- 学生所在班级的编号
-- 下面指定外键关系
FOREIGN KEY(class_id) REFERENCES class(id));
INSERT INTO class VALUES(100,'java'),(200,'H5');
INSERT INTO c_student VALUES (1,'tom',100),(2,'jack',200);
-- 添加300下面的语句就会成功INSERT INTO class VALUES(300,'PHP');
-- INSERT INTO c_student VALUES (3,'hu',300)
-- > 1452 - Cannot add or update a child row: a foreign ke y constraint fails (`zhang`.`c_student`, CONSTRAINT `c_student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
-- > 时间: 0.011s
SELECT * FROM CLASS;
SELECT * FROM c_student;
check的使用:
-- 演示check的使用
-- mysql 5.7不支持check,只做语法校验,但不会生效
-- 测试
CREATE TABLE table_11(
id INT PRIMARY KEY,
`name` VARCHAR(32),
sex VARCHAR(6)CHECK(sex IN('man','woman')),
sal DOUBLE CHECK(sal>1000 AND sal<2000)
);
INSERT INTO table_11 VALUES(1,'jack','mid',1);
SELECT * FROM table_11;
-- 练习(商店表)
CREATE DATABASE table_shop;
-- 商品表 goods
CREATE TABLE goods(
goods_id INT PRIMARY KEY,
goods_name VARCHAR(64) NOT NULL DEFAULT '',
unitprice DECIMAL(10,2) NOT NULL DEFAULT 0
CHECK (unitprice>=1.0 AND unitprice<=9999),
category INT NOT NULL DEFAULT 0,
provider VARCHAR(64) NOT NULL DEFAULT ''
);
-- 客户表 customer
CREATE TABLE customer(
customer_id INT PRIMARY KEY,
customer_name VARCHAR(64) NOT NULL DEFAULT '',
address VARCHAR(64) NOT NULL DEFAULT '',
email VARCHAR(64) UNIQUE NOT NULL,
sex ENUM('男','女')NOT NULL,
card_Id CHAR(18)
);
-- 订单表(purchase)
CREATE TABLE purchase(
order_id INT UNSIGNED PRIMARY KEY,
customer_id CHAR(8)NOT NULL DEFAULT '',
goods_id INT NOT NULL DEFAULT 0,
nums INT NOT NULL DEFAULT 0,
FOREIGN KEY(customer_id) REFERENCES customer(customer_id),
FOREIGN KEY(goods_id) REFERENCES goods(goods_id)
);
DESC goods;
DESC customer;
自增长:
-- 演示自增长
-- 字段名 整型 primary key auto_increment
-- 添加自增长的方式
CREATE TABLE table_12(
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT ''
);
DESC table_12;
-- 测试自增长的使用
INSERT INTO table_12 VALUES(NULL,'1791182296@qq.com','zd');
INSERT INTO table_12(email,`name`) VALUES('1791182296@SOUHU.COM','ZD');
SELECT * FROM table_12;
-- 自增长和主键配合使用,UNIQUE也可以配合使用
-- 自增长也可可以单独使用
-- 自增的其实值可以自己设置
ALTER TABLE table_13 AUTO_INCREMENT=100
CREATE TABLE table_13(
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT ''
);
INSERT INTO table_13 VALUES(NULL,'1791182296@qq.com','zd');
INSERT INTO table_13(email,`name`) VALUES('1791182296@SOUHU.COM','ZD');
INSERT INTO table_13 VALUES(666,'1791182296@SOUHU.CO','ZD1');
-- 如果添加了666id的序号,后面就会从666开始自增长。
SELECT * FROM table_13;
-- 如果设置自增长的话,就按照自增的规则来添加数据。这样比较好点
mysql索引:
-- mysql索引:
-- 数据库的高性能,索引是最物美价廉的东西。
-- 创建一个测试数据库
-- 索引的类型
/*
1.主键索引,主键自动的为主索引(类型primary key)
2.唯一索引(UNIQUE)
3.普通索引(INDEX)
4.全文索引(FULLTEXT)适用于mylsam(一篇文章,里面有很多的字,在里面搜索一个汉字词语,创建一个全文索引,一般不会使用mysql自带的索引,SOLR和ELASTICSEARCH(ES))
1.1 CREATE table_1(ID INT PRIMARY KEY)主键索引
2.1 ID INT UNIQUE 是唯一的
*/
-- 演示mysql的索引
-- 创建索引
CREATE TABLE table_14(
id INT,
`name` VARCHAR(32)
);
-- 查询表是否有索引
SHOW INDEXES FROM table_14;
-- 添加索引
-- 添加唯一索引(唯一约束) id不会重复就优先考虑唯一索引
CREATE UNIQUE INDEX id_index ON table_14(id); -- 0代表唯一索引,1代表非唯一索引
-- 创建普通索引
CREATE INDEX id_index ON table_14(id);
-- 第二种普通索引方式
ALTER TABLE table_14 ADD INDEX id_index(id);
-- 如何选择索引
/*
1.如果某列的值是不会重复的,则优先考虑使用unique索引,否则使用普通索引
*/
-- 添加主键索引
CREATE TABLE table_15(
id INT,
`name` VARCHAR(32)
);
ALTER TABLE table_15 ADD PRIMARY KEY(id);
SHOW INDEX FROM table_15;
-- 删除索引
DROP INDEX id_index ON table_14;
-- 删除主键索引
ALTER TABLE table_15 DROP PRIMARY KEY;
-- 修改索引就是先删除索引,再添加索引
-- 查询索引
-- 1.方式
SHOW INDEX FROM table_15;
-- 2.方式
SHOW INDEXES FROM table_15;
-- 3.方式
SHOW KEYS FROM table_15;
-- 4.方式
DESC table_15;
mysql事务:
-- mysql事务
/*
事务:
事务用于保证书怒的一致性,它由一组相关的dml语句组成,改组的dml语句要么全部成功,要么全部失败,转账就是用事务来处理的,保证一致性
事务和锁:
当执行事务操作时,mysql会在表上加锁,防止其他用户该表的结构,这对用户来说是非常重要的
*/
-- 1.事务的一个重要的概念和具体操作
-- 2.
-- 创建一张测试表
CREATE TABLE table_16(
id INT,
`name` VARCHAR(32)
);
-- 1.开始事务
START TRANSACTION;
-- 2.设置一个保存点a
SAVEPOINT a;
-- 3.执行dml操作
INSERT INTO table_16(id,`name`) VALUES (1,'TOM');
SELECT * FROM table_16;
-- 又设置一个保存点b
SAVEPOINT b;
-- 执行dml操作
INSERT INTO table_16(id,`name`) VALUES(2,'jack');
-- 回退到b
ROLLBACK TO b;
-- 还可以继续回退 a
ROLLBACK TO a;
-- 如果rollback后面不附加什么就是回退全部事务。
-- 直接回退到a点不能回到b点的,中间的保存点都会被删除掉。
-- 提交事务,COMMIT删除保存点,释放锁,生成最终的数据所有的数据就是正式生效
-- mysql事务细节:
-- 如果不开始事务,默认情况下,dml操作是自动提交的,是不能够返回的
-- 事务的隔离级别:
-- 1.过个连接开启各自事务操作数据库中数据时,数据库系统要负责分离操作,以保证各个连接在获取数据时的准确性。
-- 2.如果不考虑隔离性,可能会引发如下问题:
-- 脏读 不可重复读 幻读
-- 演示mysql的事物隔离级别
/*
1.开启了两个命令框mysql控制台
2.查看当前mysql的隔离级别
SELECT @@tx_isolation;
SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
3.把其中一个控制台的隔离级别设置为read UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)
*/
CREATE TABLE `account`(
id INT,
`name` VARCHAR(32),
money INT
);
INSERT INTO `account` VALUES(1,'zd',1000);
CREATE TABLE table_17(id INT,`name` VARCHAR(32)NOT NULL DEFAULT '',sal DOUBLE);
CREAT TABLE table_18(id INT,`name` VARCHAR(32),money DOUBLE);
-- 查看当前的隔离级别
SELECT @@tx_isolation;
-- 查看系统当前隔离级别
SELECT @@global,tx_isolation
-- 设置当前会话的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
如果没有设置的话,事务隔离级别默认是可重复读
表类型和存储引擎:
-- 表类型和存储引擎
-- 查看所有的存储引擎
SHOW ENGINES;
-- INNODB 支持事务 支持外键 支持行级锁
-- myisam
CREATE TABLE table_19(
id INT ,
`name` VARCHAR(32)) ENGINE MYISAM;
-- 1.不支持外键和事务 2.添加速度快 3.支持表级锁
START TRANSACTION;
SAVEPOINT T1;
INSERT INTO table_19 VALUES(1,'ZD');
SELECT * FROM table_19;
ROLLBACK TO T1;
-- MEMORY 存储引擎
-- 1.数据存储在内存中 2.执行速度很快,没有io读写 3.默认支持索引(hash表)
CREATE TABLE table_20(
id INT ,
`name` VARCHAR(32)) ENGINE MEMORY;
INSERT INTO table_20 VALUES(1,'TOM'),(2,'JACK'),(3,'ZD');
SELECT * FROM table_20;
-- 关闭mysql服务数据就会消失,但是表结构还在
-- 2.用户在线状态就可以使用memory引擎(内存存储引擎,服务一关就没有了)
-- 1.如果你的应用不需要事务,myisam就是首选
-- 修改存储引擎(指令修改)
ALTER TABLE 'table_name' ENGINE = '引擎名';
视图:
-- 视图:
-- 视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含列,其数据来自对应的真实表(基表)
-- 创建一个视图
CREATE VIEW emp_view AS SELECT id,user_name,sex,job FROM emp;
-- 查看视图
DESC emp_view;
SELECT * FROM emp_view;
-- 查找其他的列就会找不到
-- 查看创建视图的指令
SHOW CREATE VIEW emp_view;
-- 删除视图
DROP VIEW view_name;
-- 修改视图
UPDATE view_name
SET job = '修改的内容'
WHERE id = 'id名字';
UPDATE emp_view SET job = 'java工程师' WHERE id ='1';
SELECT * FROM emp_view;
SELECT * FROM emp;
-- 修改视图会应该基表(也就是原来的数据表) 修改基表也会影响视图
UPDATE emp SET job = '数据库' WHERE id ='1';
-- 视图中还可以使用视图
DESC emp_view;
-- 从emp_view中做一个新的视图,
CREATE VIEW emp_view1 AS SELECT id,user_name FROM emp_view;
DESC emp_view1;
SELECT * FROM emp_view1;
mysql管理:
-- mysql管理
-- mysql
/*
1.host允许登录的位置,localhost表示用户只允许本机登录,也可以指定ip地址,比如192.168.12.12
2.user 用户名
3.authentication_string: 密码,是通过mysql的password()函数加密之后的密码
*/
/*
创建用户:
CREATE USER '用户名' @ '允许登录的位置'
*/
-- mysql用户的管理
-- 原因:当我们做项目开发时,可以根据不同的开发人员,赋给它相应的mysql操作权限
-- 所以mysql数据库管理人员根据需要创建不同的用户,赋给响应的权限,供人员使用
-- 创建新的用户
-- 1.'zhangdong'用户名@'localhost'登录的IP
-- 2.123456密码,但是注意,存放到mysql.user表时,是password('123456')加密后的密码
CREATE USER 'zhangdong'@'localhost' IDENTIFIED BY '123456';
SELECT * FROM mysql.user;
SELECT PASSWORD('123456');
-- 删除用户
DROP USER '用户名' @ 'IP';
-- 不同的数据库用户,操作的库和表不相同
SC emp_view;
SELECT * FROM emp_view;
-- 查找其他的列就会找不到
-- 查看创建视图的指令
SHOW CREATE VIEW emp_view;
-- 删除视图
DROP VIEW view_name;
-- 修改视图
UPDATE view_name
SET job = '修改的内容'
WHERE id = 'id名字';
UPDATE emp_view SET job = 'java工程师' WHERE id ='1';
SELECT * FROM emp_view;
SELECT * FROM emp;
-- 修改视图会应该基表(也就是原来的数据表) 修改基表也会影响视图
UPDATE emp SET job = '数据库' WHERE id ='1';
-- 视图中还可以使用视图
DESC emp_view;
-- 从emp_view中做一个新的视图,
CREATE VIEW emp_view1 AS SELECT id,user_name FROM emp_view;
DESC emp_view1;
SELECT * FROM emp_view1;
使用三表联合查询,得到结果。
表的个数-1,表名.列名
[外链图片转存中...(img-9GdoNIh5-1638603177801)]
### mysql管理:
```mysql
-- mysql管理
-- mysql
/*
1.host允许登录的位置,localhost表示用户只允许本机登录,也可以指定ip地址,比如192.168.12.12
2.user 用户名
3.authentication_string: 密码,是通过mysql的password()函数加密之后的密码
*/
/*
创建用户:
CREATE USER '用户名' @ '允许登录的位置'
*/
-- mysql用户的管理
-- 原因:当我们做项目开发时,可以根据不同的开发人员,赋给它相应的mysql操作权限
-- 所以mysql数据库管理人员根据需要创建不同的用户,赋给响应的权限,供人员使用
-- 创建新的用户
-- 1.'zhangdong'用户名@'localhost'登录的IP
-- 2.123456密码,但是注意,存放到mysql.user表时,是password('123456')加密后的密码
CREATE USER 'zhangdong'@'localhost' IDENTIFIED BY '123456';
SELECT * FROM mysql.user;
SELECT PASSWORD('123456');
-- 删除用户
DROP USER '用户名' @ 'IP';
-- 不同的数据库用户,操作的库和表不相同