1.新建数据库与新建表
1.1 DDL语言
其语句主要有CREATE,ALTER和DROP。
MySQL 是一个关系型数据库,库里包含若干个表,而每一张表都是由行和列组成的。
1.1.1 关于数据库操作的命令
-- 创建数据库
create database mydb;
-- 当前都有什么数据库
show databases;
-- 切换到当前数据库下
use mydb;
-- 删除数据库
drop database mydb;
注意:删除时一定要注意数据库名字,确认删除的是自己的数据库。
1.1.2 关于数据表的操作命令
创建表结构
create table 表名(
列名 数据类型,
列名 数据类型,
列名 数据类型
)
-- 创建表
create table student(
int id primary key auto_increment comment '学生学号',
name varchar(10) comment '学生姓名',
sex varchar(2) comment '学生性别',
age int comment '年龄',
major varchar(10) comment '专业',
college varchar(10) comment '学院'
)
-- 显示当前数据库具有的表
show tables;
-- 显示表结构
desc student;
-- 删除表
drop table student;
修改表结构
-- 增加一列
alter table student add column aaa varchar(2);
-- 修改列名称
alter table student change column aa bb varchar(2);
-- 修改列的数据类型
alter table student modify column bb int ;
-- 删除某一列
alter table student drop bb;
1.2 DML语言
数据操作语言(DML:Data Manipulation Language): 其语句包括动词INSERT(添加)、UPDATE(修改)和DELETE(删除)。
1.2.1 添加(insert)
-- 添加语句
insert into student(id,name,sex,age,major,college) values(901,'张三','男',20,'计科','信工学院');
insert into student(id,name,sex,age,major,college) values(902,'李四','女',20,'计科','信工学院');
1.2.2 修改操作(update)
-- 修改操作
update student set name='李四' , sex='女' where id=901;
update student set name='张三' , sex='男' where id=902;
1.2.3 删除操作(delete)
-- 删除操作
delete from student where id = 901;
delete from student where id = 902;
1.3 数据类型
1.3.1 数值类型
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
1.3.2 日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
1.3.3 字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
注意:char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
varchar(20) 长度可变----如果你存入的字符个数小于n 则mysql底层安装你存储字符个数分配空间。
char(20) 长度不可变----如果你存入的字符个数小于n mysql底层按照20的空间来分配。他一般用于长度固定的字符串。比如: 手机号,身份证号
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
问题: MySQL能不能存储音频 视频图片文件 ?
可以的 将音视频图片 转换成流 存储到数据库 blob 但是我们数据库 不存文件
文件一般存到磁盘 然后数据库中存储文件路径
有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。
2.查询
2.1准备工作
创建表(student)并放入数据
-- 查询是否存在student表,若存在则删除,不存在则不运行
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生表的主键列',
`name` varchar(255) ,
`age` int(11) ,
`address` varchar(255),
PRIMARY KEY (`id`) USING BTREE
) ;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', 18, '北京');
INSERT INTO `student` VALUES (2, '李四', 19, '北京');
INSERT INTO `student` VALUES (3, '王五', 19, '北京');
INSERT INTO `student` VALUES (4, '钱七', 19, '北京');
INSERT INTO `student` VALUES (5, '关羽', 25, '南京');
INSERT INTO `student` VALUES (6, '张飞', 25, '南京');
INSERT INTO `student` VALUES (7, '赵云', 28, '南京');
INSERT INTO `student` VALUES (8, '项羽', 19, '南京');
INSERT INTO `student` VALUES (9, '刘备', 30, '上海');
INSERT INTO `student` VALUES (10, '曹操', 40, '许昌');
INSERT INTO `student` VALUES (11, '夏侯', 36, '许昌');
INSERT INTO `student` VALUES (12, '诸葛亮', 38, '上海');
INSERT INTO `student` VALUES (13, '周瑜', 78, '江东');
INSERT INTO `student` VALUES (14, '小乔', 56, '江东');
INSERT INTO `student` VALUES (15, '孙尚香', 59, '江东');
2.2 查询的开始
as 另起别名 、 * 所有的
-- 查询
select * from student ; -- *表示所有的
select name as 姓名 from student; -- as 另起别名
2.3 去除重复的值
distinct 关键字
-- 去除重复值
select distinct address 城市 from student;
2.4 条件查询
and or like _ %
1. 判断
select * from student where age>19;
select * from student where age>=19;
select * from student where age!=19;
select * from student where age<19;
select * from student where age<=19;
2. 多条件查询 and(与) or(或)
select * from student where age>=19 and address='上海'
select * from student where age<=19 or address='上海'
3. 范围语句
select * from student where age>=19 and age<=30;
select * from student where age between 19 and 30; -- 等价于上面
4. 使用in
-- 此时我们有这样一个需求 查询年龄是 18 或者 25 或者 30 或者 36 或者 48 的学生
select * from student where age=18 or age=25 or age=30 or age=36 or age=48
-- 上面这种方式比较麻烦 我们可以使用in来替换上面的模式
select * from student where age in(18,25,30,36,48)
5. 模糊搜索 like
通配符: _统配一个字符 %统配n个字符。
-- 模糊查询
-- 查询名字中第二个字符为三 _
select * from student where name like '_三';
-- 查询名字姓李 %统配任意字符
select * from student where name like '李%'
select * from student where name like '%三%'
2.5 排序查询
order by desc : 降序 asc : 升序
-- 根据多列进行排序年龄按照降序 姓名按照升序。
select * from student order by age desc, id desc;
如果既有where又有order by 则where在前order by 在后
2.6 查询列上进行计算
可以进行加减乘除等
-- 年龄乘三
select age*3,name from student;
2.7 聚合函数
聚合函数总共有5个:
max(列名):求某列的最大值。
min(列名):求某列的最小值。
sum(列名):求某列的和
avg(列名):求某列的平均值
count(列名):求某列的个数。
-- 求最大的年龄
select max(age) from student;
-- 求最小的年龄
select min(age) from student;
-- 求年龄的和
select sum(age) from student;
-- 求学生的个数.
select count(id) from student;
-- 求年龄平局值
select avg(age) from student;
2.8 分组查询
group by 语句将某一列相同数据视为一组, 然后进行查询与聚合函数连用。
having 对分组进行条件检索。
-- 求各个地区的人数
select count(*),address from student group by address;
-- 查询 最大值小于30 的地区 人数 和 平均年龄
select address,count(*),avg(age),max(age) from t_student group by address having max(age)<30;
注意: 如果使用了group by 那么select后只能根分组的条件列和聚合函数。
2.9 分页查询
limit关键字
当数据库表数据量比较大 例如: 1000w行数据 如果我们执行 select * from student 此时有可能数据库卡死 -- 拿出1000w数据到内存里,你的内存可能会不够。导致电脑卡死。
如果在java中 有可能内存直接溢出 所以实际开发中 都是分页查询 (部门查询)
-- 从第3条记录查询 查询5条
select * from student limit 3,5;
--- 分页:
select * from 表名 limit (n-1)*m,m; -- 查询第n页得m条记录。
--- n表示页码 m:表示每页得条数,
-- 第1页每页显示5条记录:
select * from t_student limit 0,5;
-- 第2页每页显示5条记录:
select * from t_student limit 5,5;
-- 第3页每页显示5条记录:
select * from t_student limit 10,5;
-- 第4页每页显示5条记录:
select * from t_student limit 15,5;
2.10 SQL的优先级
sql语法:
select distinct * from 表名
where 条件
group by 分组
having 分组条件
order by 排序
limit 分页。
上面为他的语法结构。顺序不能乱。
mysql执行引擎:执行的顺序。
3 其他的函数
SQL也是一种编程语言,它里面也有函数,分为内置的函数个自定义的函数(高级)。
内置函数:
1. 聚合函数 。
2. 字符串函数。
3. 时间函数。
4. 算术函数。
5. 其他函数。
3.1 字符串函数
常用函数
实例
-- 字符串拼接函数
select name from t_student;
select concat('我的名字叫:',name,",年龄:",age) as n from t_student;
-- 字符串转换为大写和小写
select name,upper(name),LOWER(name) from t_student;
-- 去除前后空格
select address,trim(address) from t_student;
-- 找到姓李得学生 字符串得截取
select * from t_student where name like '李%';
select * from t_student where substr(name,1,1)='李'
3.2 数字函数
常见函数
实例
-- 向上取整 5.1你给定的值
select ceil(5.1),floor(5.9),mod(10,3),abs(-5.9) from t_student;
-- 要求如果status为0变为1 为1变为0 update
update aaa set status=abs(status-1);
3.3 时间函数
常用函数
实例
-- CURDATE()返回当前日期 后面可以不用跟表名
select curdate();
-- 可以用在where
select * from tb_emp where curdate()-entrydate=1
-- CURTIME()返回当前时间
select curtime() ;
-- NOW()返回当前日期和时间
select now();
-- YEAR(date)获取指定date的年份
select year('2022-04-25');
select * from tb_emp where year(entrydate)=2022
-- DATE_ADD(date, INTERVAL expr type)返回一个日期/时间值加上一个时间间隔expr后的时间值
SELECT DATE_ADD('2020-04-25', INTERVAL 3 YEAR);
-- DATEDIFF(date1, date2)返回起始时间date1和结束时间date2之间的天数
select datediff('2020-05-25','2020-04-25');
-- 入职30天得员工.
select * from tb_emp where DATEDIFF(now(),entrydate)>30
3.4 流程函数
常用函数
实例
-- 如果年龄>30妇女 否则少女
select age,if(age>30,'妇女','少女') from tb_emp;
-- ifnull 如果为null,则
select job,ifnull(job,'工作待定') from tb_emp;
4 约束
约束:在表中为某些列添加约束,使得该列必须符合这个约束
4.1 常见约束
create table t_teacher(
id int primary key auto_increment, -- 要求该列得值必须唯一且不能为null.
tname varchar(20) unique, -- unique 唯一约束 表示该列得值必须唯一。但是可以运行多个null
age int not null, -- not null 非空约束 表示该列得值不能为null.
sex char(2) default '男' -- default 默认约束 表示如果该列没有给定值 则默认为男
)
4.2 外键约束
外键实例
外键约束用来让两张图的数据之间建立连接,保证数据的一致性和完整性。
-- 外键
create table t_class(
cid int primary key auto_increment,
cname varchar(20)
);
create table t_stu(
sid int primary key auto_increment,
sname varchar(20),
classid int -- 表示外键列。
);
-- add contraint 约束得名称 foregin key 外键得列名 REFERENCES 主表名(主键)
-- alter table 从表名 add contraint 约束名随便起 foreign key 外键列名 references 主表(主键)
alter table t_stu add CONSTRAINT fk_stu_class FOREIGN key (classid) REFERENCES t_class(cid);
insert into t_class values(null,'QY145'),(null,'QY151');
insert into t_stu values(null,'李青华',2);
-- 因为classid=3 这个值 在我们得班级表中不存在。所以不能添加
insert into t_stu values(null,'张青',1);
图表解释
5 连表查询
1. 如果查询的结果在一张表中无法获取,需要关联多张表,那么这时候就需要连表查询。
2. 连表查询大多数都作用在外键的基础上。 --- 需要表与表之间有关联
5.1 表与表之间的关系
一对一:在多的一方添加外键列
多对一:需要在创建一张表,该表中至少有两个外键列
5.2 多表连接查询
5.2.1 连接查询
内连接:相当于A、B 的交集部分数据
外连接:
左外连接:查询左表所有数据,以及两张表交集部分
右外连接:查询右表所有数据,以及两张表交集部分
自连接:当前表与自身的连接查询,自连接必须使用表别名
子查询:
5.3 内连接查询语法
5.3.1 隐式内间接
5.3.2 显式内连接
内连接查询的是交集部分
实例
-- 隐式查询 select 列名.... from 表1,表2 where 连表得条件。
select * from tb_emp e, tb_dept d where e.dept_id=d.id;
-- 显示连接: A表 inner join B表 on 连表条件。
select * from tb_emp e join tb_dept d on e.dept_id=d.id;
5.4 外连接
外连接的语法:
5.4.1 左外连接
相当于查询表1(左表)的所有数据包括表一与表二交集部分的数据
5.4.2 右外连接
相当于查询表二(右表)的所有数据,包含表一与表二交集部分的数据
实例
-- 1.查询emp表的所有数据, 和对应的部门信息(左外连接)
select * from tb_emp e left outer join tb_dept d on e.dept_id = d.id;
select * from tb_emp e left join tb_dept d on e.dept_id=d.id;
-- 2.查询dept表的所有数据,和对应的员工信息(右外连接)
select * from tb_emp e right join tb_dept d on e.dept_id=d.id;
5.5 自连查询
自己和自己相连接查询。
select * from A表 join A表 on 连表条件。
实例
-- 查询所有员工 emp及其领导的名字emp ,如果员工没有领导,也需要查询出来
select a.name,b.name from tb_emp a left join tb_emp b on a.managerid=b.id;
5.6 子查询 ---- 嵌套查询
-- 根据财务部最高工资查询其他员工信息
select * from tb_emp where salary>( select max(salary) from tb_emp e join tb_dept d on e.dept_id=d.id where d.name='财务部')
5.7 组合查询
多个查询的结果 组合到一起。
sql union sql --->把这两条sql查询的结果组合到一起。如果有重复记录则合并成一条。
sql union all sql--->把这两条sql查询的结果组合到一起。如果有重复记录,不合并。
注意: 这两条sql返回的字段必须一样。
select name from tb_emp where salary>8000
UNION
select * from tb_emp where age>40;