1. 列类型
1.1 数值类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 字节 | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 字节 | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 字节 | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 字节 | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 字节 | (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 字节 | (-3.402 823 466 E+38,1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度,浮点数值 |
DOUBLE | 8 字节 | (1.797 693 134 862 315 7 E+308,2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度,浮点数值 |
1.2 日期和时间类型
类型 | 大小(字节) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 8 | 1970-01-01 00:00:00/2037 年某时 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
1.3 字符串类型
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255字节 | 定长字符串 |
VARCHAR | 0-65535 字节 | 变长字符串 |
TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255字节 | 短文本字符串 |
BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
TEXT | 0-65 535字节 | 长文本数据 |
2. 数据字段属性
UnSigned
- 无符号的
- 声明该数据列不允许负数 .
ZEROFILL
- 0填充,不足位数的用0来填充 , 如int(3), 输入5则为005
Auto_InCrement
-
自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)
-
通常用于设置主键 , 且为整数类型
-
可定义起始值和步长
-
当前表设置自增起始值(AUTO_INCREMENT=10) ,起始值只能设置成比现有数据最大值更大的数,否则无效
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CGft1mDy-1629008683553)(C:\Users\14284\Desktop\Markdown文档\数据库\img数据库操作\19.png)]
NULL 和 NOT NULL
-
默认为NULL , 即没有插入该列的数值
-
如果设置为NOT NULL , 则该列必须有值
DEFAULT
-
用于设置默认值
-
例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值
CREATE TABLE IF NOT EXISTS `student`(
`id` int(10) AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` int(2) NOT NULL DEFAULT '1' COMMENT '性别',
`address` VARCHAR(100) COMMENT '地址',
`phone_num` VARCHAR(100) NOT NULL COMMENT '联系方式',
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
3. 数据表的类型
名称 | MyISAM | InnoDB |
---|---|---|
事务处理 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 较大,约 2 倍 |
4. 数据表字符集
DEFAULT CHARSET=utf8
可通过上述sql语句设置
也可以在修改MySQL数据库配置文件 my.ini
中的参数设定。但不建议这样设置,因为当我们这样设置后,数据库字符集配置是全局的,在我们自己电脑上看是这个默认的字符集。但是当sql语句放到其他电脑上运行时,就可能会出现乱码。
5. 修改数据表
修改表名 : ALTER TABLE 旧表名 RENAME AS 新表名
添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]
修改字段 :
- ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
删除字段 : ALTER TABLE 表名 DROP 字段名
6. 外键
通过sql语句设置外键
CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
CREATE TABLE `student` (
`studentno` INT(4) NOT NULL COMMENT '学号',
`studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
`gradeid` INT(10) DEFAULT NULL COMMENT '年级',
`phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
`address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
`borndate` DATETIME DEFAULT NULL COMMENT '生日',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
或者通过可视化工具设置外键
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Lyd8m2Ix-1629008683558)(C:\Users\14284\Desktop\Markdown文档\数据库\img数据库操作\17.jpg)]
创建外键时,自动生成索引
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1FMxrPXy-1629008683561)(C:\Users\14284\Desktop\Markdown文档\数据库\img数据库操作\18.png)]
注意 : 删除具有主外键关系的表时 , 要先删子表 , 后删主表
-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;
7. INSERT
INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
-- 一次插入多条数据
INSERT INTO grade(gradename) VALUES ('大三'),('大四');
8. UPDATE
UPDATE 表名 SET column_name=value
9. DELETE
DELETE FROM 表名 WHERE condition
10. TRUNCATE
TRUNCATE table_name;
有外键约束时不可用
区别于DELETE命令:
- 相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
- 不同 :
- 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
- 使用TRUNCATE TABLE不会对事务有影响
-- 删除表数据(truncate)
TRUNCATE TABLE test;
-- 结论:truncate删除数据,自增当前值会恢复到初始值重新开始;不会记录日志.
-- 同样使用DELETE清空不同引擎的数据库表数据.重启数据库服务后
-- InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)
-- MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)
11. SELECT
select column_name from table_name where ...
11.1 使用AS 取别名
AS也可以省略不写
SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;
-- 使用as,为查询结果取一个新名字
-- CONCAT()函数拼接字符串
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;
11.2 DISTINCT去重
如果distinct关键字的后面有多个字段,则会组合进行去重,意思就是当distinct后面的字段内容均一致时才会被认为是重复的。
SELECT DISTINCT studentno FROM result; -- DISTINCT 去除重复项
操作符名称 | 语法 | 描述 |
---|---|---|
IS NULL | a IS NULL | 若操作符为NULL,则结果为真 |
IS NOT NULL | a IS NOT NULL | 若操作符不为NULL,则结果为真 |
BETWEEN | a BETWEEN b AND c | 若 a 范围在 b 与 c 之间(包括b,c),则结果为真 |
LIKE | a LIKE b | SQL 模式匹配,若a匹配b,则结果为真 |
IN | a IN (a1,a2,a3,…) | 若 a 等于 a1,a2… 中的某一个,则结果为真 |
11.3 LIKE
like结合使用的通配符 :
- % :代表0到任意个字符)
- _ :一个字符)
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘%';
-- 查询姓刘的同学,后面只有一个字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘_';
查询姓名中含有特殊字符的需要使用转义符号 \
SELECT * from grade where gradename LIKE '%\%%'
11.4 IN
-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);
-- 查询地址在北京,南京,河南洛阳的学生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛阳')
11.5 NULL
SELECT studentname FROM student
WHERE BornDate IS NULL;
-- 查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;
-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;
12. 连接查询
操作符名称 | 描述 |
---|---|
INNER JOIN | 如果两个表都有匹配,则返回行 |
LEFT JOIN | 即使右表中没有匹配,也从左表中返回所有的行 |
RIGHT JOIN | 即使左表中没有匹配,也从右表中返回所有的行 |
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9S1d2i1Y-1629008683567)(C:\Users\14284\Desktop\Markdown文档\数据库\img数据库操作\20.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UbFQqSSQ-1629008683571)(C:\Users\14284\Desktop\Markdown文档\数据库\img数据库操作\21.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GUIpcL2e-1629008683573)(C:\Users\14284\Desktop\Markdown文档\数据库\img数据库操作\22.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rghpStvr-1629008683576)(C:\Users\14284\Desktop\Markdown文档\数据库\img数据库操作\23.png)]
练习:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-v6dGHLdX-1629008683578)(C:\Users\14284\Desktop\Markdown文档\数据库\img数据库操作\24.png)]
查找教师名为曾导所教授的课程的学生成绩
select t.tno, t.tname 教师, c.cno, c.cname 课程, s.mark, stu.sno 学号, stu.sname 学生, stu.class 班级 from teacher t
INNER JOIN course c ON t.tname = '曾导' and t.tno = c.tno
INNER JOIN score s ON c.cno = s.cno
INNER JOIN student stu ON s.sno = stu.sno
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cbeEOuKS-1629008683581)(C:\Users\14284\Desktop\Markdown文档\数据库\img数据库操作\25.png)]
查询linux课的所有学生成绩
select c.cno, c.cname 课程, stu.sno 学号, stu.sname 姓名, s.mark 成绩 from course c INNER JOIN score s ON c.cno = s.cno AND c.cname = 'linux'
INNER JOIN student stu ON s.sno = stu.sno
查询陈妙的各科考试成绩
select stu.sno 学号, stu.sname 姓名, c.cname 课程, s.mark 成绩 from student stu
INNER JOIN score s ON stu.sno = s.sno AND stu.sname = '陈妙'
INNER JOIN course c ON s.cno = c.cno
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mr8YK6yj-1629008683584)(C:\Users\14284\Desktop\Markdown文档\数据库\img数据库操作\27.png)]
查询Java课程成绩低于80分的同学
select stu.sname 姓名, c.cname 课程, s.mark 成绩 from score s
INNER JOIN course c ON c.cno = s.cno AND s.cno = 1 AND mark < 80
INNER JOIN student stu ON s.sno = stu.sno
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SHla3NWU-1629008683585)(C:\Users\14284\Desktop\Markdown文档\数据库\img数据库操作\30.png)]
13. 自连接
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-u02nE3R6-1629008683587)(C:\Users\14284\Desktop\Markdown文档\数据库\img数据库操作\28.png)]
select a.cate_name 一级分类, b.cate_name 二级分类 from tdb_cates a
INNER JOIN tdb_cates b ON b.parent_id = a.id
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OWXZSsU6-1629008683588)(C:\Users\14284\Desktop\Markdown文档\数据库\img数据库操作\29.png)]
14. 子查询
查询Java课程成绩低于80分的同学
select stu.sname, c.cname, m.mark from (select * from score s where cno = 1 AND mark < 80) m
INNER JOIN student stu ON m.sno = stu.sno
INNER JOIN course c ON c.cno = m.cno
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6mwm4sFb-1629008683589)(C:\Users\14284\Desktop\Markdown文档\数据库\img数据库操作\30.png)]
15. 排序和分页
ORDER BY 字段 ASC -- 升序
ORDER BY 字段 DESC -- 降序
第一页 : limit 0,5
第二页 : limit 5,5
第三页 : limit 10,5
…
第N页 : limit (pageNo-1)*pageSzie,pageSzie
[pageNo:页码,pageSize:单页面显示条数]
limit 0,5 -- 从0开始的5条数据
16. 常用函数
SELECT ABS(-8); -- 绝对值
SELECT CEILING(9.4); -- 向上取整
SELECT FLOOR(9.4); -- 向下取整
SELECT RAND(); -- 随机数,返回一个0-1之间的随机数
SELECT SIGN(0); -- 符号函数: 负数返回-1,正数返回1,0返回0
SELECT CHAR_LENGTH('哈哈哈哈哈哈'); -- 返回字符串包含的字符数
SELECT CONCAT('我','爱','程序'); -- 合并字符串,参数可以有多个
SELECT INSERT('我爱编程helloworld',2,3,'hh'); -- 我hhhelloworld
SELECT INSERT('我爱编程helloworld',2,1,'hh'); -- 我hh编程helloworld
SELECT INSERT('我爱编程helloworld',2,2,'hh'); -- 我hh程helloworld
SELECT LOWER('hhhHHH'); -- 小写
SELECT UPPER('hhhHHH'); -- 大写
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE(); -- 获取当前日期
SELECT NOW(); -- 获取当前日期和时间
SELECT LOCALTIME(); -- 获取当前日期和时间
SELECT SYSDATE(); -- 获取当前日期和时间
-- 获取年月日,时分秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
-- 查找入职员工时间排名倒数第三的员工所有信息
select * from employees ORDER BY hire_date DESC LIMIT 2,1
17. 聚合函数
函数名称 | 描述 |
---|---|
COUNT() | 返回满足Select条件的记录总和数,如 select count(*) 【不建议使用 *,效率低】 |
SUM() | 返回数字字段或表达式列作统计,返回一列的总和。 |
AVG() | 通常为数值字段或表达列作统计,返回一列的平均值 |
MAX() | 可以为数值字段,字符字段或表达式列作统计,返回最大的值。 |
MIN() | 可以为数值字段,字符字段或表达式列作统计,返回最小的值。 |
练习:
查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t
select emp_no, count(emp_no) as t from salaries GROUP BY emp_no HAVING t > 15
查找最晚入职员工的所有信息
select * from employees where hire_date=(select MAX(hire_date) from employees)
18.事务
事务是必须满足4个条件(ACID)::原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。
-
原子性
即不可分割,事务要么全部被执行,要么全部不执行。如果事务的所有子事务全部提交成功,则所有的数据库操作被提交,数据库状态发生变化;如果有子事务失败,则其他子事务的数据库操作被回滚,即数据库回到事务执行前的状态,不会发生状态转换
-
一致性
事务的执行使得数据库从一种正确状态转换成另外一种正确状态
-
隔离性
在事务正确提交之前,不允许把事务对该数据的改变提供给任何其他事务,即在事务正确提交之前,它可能的结果不应该显示给其他事务
-
持久性
事务正确提交之后,其结果将永远保存在数据库之中,即使在事务提交之后有了其他故障,事务的处理结果也会得到保存
-- 使用set语句来改变自动提交模式
SET autocommit = 0; /*关闭*/
SET autocommit = 1; /*开启*/
-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交
-- 开始一个事务,标记事务的起始点
START TRANSACTION
-- 提交一个事务给数据库
COMMIT
-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK
-- 还原MySQL数据库的自动提交
SET autocommit =1;
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点
1、脏读
所谓脏读,就是指事务A读到了事务B还没有提交的数据,比如银行取钱,事务A开启事务,此时切换到事务B,事务B开启事务–>取走100元,此时切换回事务A,事务A读取的肯定是数据库里面的原始数据,因为事务B取走了100块钱,并没有提交,数据库里面的账务余额肯定还是原始余额,这就是脏读。
2、不可重复读
所谓不可重复读,就是指在一个事务里面读取了两次某个数据,读出来的数据不一致。还是以银行取钱为例,事务A开启事务–>查出银行卡余额为1000元,此时切换到事务B事务B开启事务–>事务B取走100元–>提交,数据库里面余额变为900元,此时切换回事务A,事务A再查一次查出账户余额为900元,这样对事务A而言,在同一个事务内两次读取账户余额数据不一致,这就是不可重复读。
3、幻读
所谓幻读,就是指在一个事务里面的操作中发现了未被操作的数据。比如学生信息,事务A开启事务–>修改所有学生当天签到状况为false,此时切换到事务B,事务B开启事务–>事务B插入了一条学生数据,此时切换回事务A,事务A提交的时候发现了一条自己没有修改过的数据,这就是幻读,就好像发生了幻觉一样。幻读出现的前提是并发的事务中有事务发生了插入、删除操作。
19.索引
19.1 主键索引
主键 : 某一个属性组能唯一标识一条记录
特点 :
- 最常见的索引类型
- 确保数据记录的唯一性
- 确定特定数据记录在数据库中的位置
19.2 唯一索引
作用 : 避免同一个表中某数据列中的值重复
与主键索引的区别:
- 主键索引只能有一个
- 唯一索引可能有多个
CREATE TABLE `Grade`( -- 方式一
`GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
-- 或 UNIQUE KEY GradeID (GradeID)
)
ALTER TABLE student ADD UNIQUE (email) -- 方式二
-- 删除索引:DROP INDEX 索引名 ON 表名字;
-- 删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
19.3 常规索引
作用 : 快速定位特定数据
注意 :
- index 和 key 关键字都可以设置常规索引
- 应加在查询找条件的字段
- 不宜添加太多常规索引,影响数据的插入,删除和修改操作
ALTER TABLE student ADD INDEX ind (sex)
CREATE TABLE `result`(
-- 省略一些代码
INDEX/KEY ind (studentNo,subjectNo) -- 创建表时添加
)
19.4 全文索引
/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname`
(`StudentName`);
/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';
/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串做为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行,MATCH() 返回一个相关性值。即,在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');