文章目录
1.概述
数据库是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。是按照数据结构来组织、存储和管理数据的仓库。
2.配置
-
[mysqld] # 设置3306端口 port=3306 # 设置mysql的安装目录 basedir=G:\WWW\mysql # 设置mysql数据库的数据的存放目录 datadir=G:\WWW\mysql\data # 允许最大连接数 max_connections=200 #允许连接失败的次数。防止有人从该主机试图攻击数据库系统 max_connect_errors=10 # 服务端使用的字符集默认为UTF8 character-set-server=utf8 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB # 默认使用“mysql_native_password”插件认证 default_authentication_plugin=mysql_native_password [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8 [client] # 设置mysql客户端连接服务端时默认使用的端口 port=3306 default-character-set=utf8
-
cd /d G:\WWW\mysql\bin
-
mysqld --initialize --console
-
Jw#4D*?(wDsf //root密码
-
mysqld install
-
net start mysql # 启动命令 net stop mysql # 关闭命令
-
//如果要卸载 命令 : cd /d G:\WWW\mysql\bin 命令 : mysqld remove
-
//登录 mysql -uroot -p
-
//重新设置密码 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
-
//设置子账号 CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';
-
查看数据库 SHOW DATABASES;
-
新建数据库 CREATE DATABASE item_name;
-
删除数据库 DROP DATABASE item_name;
-
选中数据库 use item_name;
3.设计数据表
- 新建学生选课相关数据表需要注意的是每一张表都必须有一个主键,一般建议选定为无符号整型 id 作为主键,并且 id 一般作为主键一般设置为自增的(特殊情况可使用其他非自增 id 作为主键)
4.数据表设计规范
-
第一设计范式要求表中字段都是不可再分的
第二设计范式要求表中必须存在业务主键,并且全部非主键依赖于业务主键。即满足第一范式前提,当存在多个主键的时候,才会发生不符合第二范式的情况。
第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主键字段。一张表最多只存两层同类型信息。遵循数据表设计三范式可以避免字段值的重复存储,提升存储效率,节省存储空间
降低范式就是增加字段,减少了查询时的关联,提高查询效率,因为在数据库的操作中查询的比例要远远大于 DML 的比例。
5.修改命令表
-
//修该表名 ALTER TABLE 旧的表名 RENAME TO 新的表名;
-
//查看表字段类型命令 SHOW CREATE table_name;
-
//修改表字段数据类型命令 ALTER TABLE `new_student` MODIFY COLUMN `name` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '无名' AFTER `id`;
-
//新增数据表的字段命令 ALTER TABLE `new_student` ADD COLUMN `sex` tinyint(2) UNSIGNED NOT NULL DEFAULT 1 COMMENT '性别 : 1:男 2:女' AFTER `id_number`;
-
//删除表字段命令 ALTER TABLE `new_student` DROP COLUMN `sex`;
-
//修改表字段数据类型命令 ALTER TABLE `new_student` CHANGE COLUMN `name` `new_name` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '无名' AFTER `id`;
-
//修改表字段默认值命令 ALTER TABLE `new_student` MODIFY COLUMN `new_name` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '小花' AFTER `id`;
6.删除数据表
-
DROP TABLE new_student;
7.插入数据表
-
INSERT INTO teacher (name,age,id_number) VALUES ('秦小贤',18,'42011720200604088X');
-
//查询表内数据 SELECT * FROM teacher;
-
//表内插入多条数据 INSERT INTO teacher (name,age,id_number) VALUES ('王小花',19,'42011720200604077X'), ('张晓丽',18,'42011720200604099X'), ('刘美丽',20,'42011720200604020X'), ('吴帅',21,'42011720200604022X'), ('张平',22,'42011720200604033X')
-
需要注意的是本节中 teacher 表的业务主键为自增 id,因此插入数据的时候不需要插入 id 字段的值。id 字段的默认是从 1 开始自增的,也可以指定自增起始值
8.删除数据
-
删一条 DELETE FROM teacher WHERE id = 8;
-
删全部数据 DELETE FROM teacher;
-
清空数据表 TRUNCATE TABLE new_student;
-
TRUNCATE 清空表数据的实际过程是先删除数据表,然后新建一张和原来表结构一模一样的表来替代清空。
DELETE 删除表数据不会改变自增主键的增长值,例如本小节删除示例图介绍的 8 条数据使用 DELETE 全部删除完之后再向数据表插入一条数据 id 是从 9 开始自增的。
9.查询数据
-
查询所有 SELECT * FEOM teacher;
-
查询指定条数 SELECT * FROM teacher LIMIT 10;
-
有时想要查询指定起始位置指定条数的结果集,例如想要查询第 11 条开始的后面 10 条数据可以使用命令 : SELECT * FROM teacher LIMIT 10,10;
-
查询指定字段列的结果集 以 teacher 表为例,只需要查询 name、age 字段的列表值,命令如下: SELECT name,age FROM teacher LIMIT 6,5;
-
也可以给指定的字段重命名: SELECT name AS new_name,age FROM teacher LIMIT 6,5;
10.更新数据
-
更新某一列字段的值 以 teacher 表为例,需要把前 3 条数据的 age 更新为 33,命令如下: UPDATE teacher SET age = 33 LIMIT 3;
-
更新多列字段的值 以 teacher 为例,更新 id = 30 这条数据的 age=18,id_number=44444444440604099X,命令如下: UPDATE teacher SET age=18,id_number='44444444440604099X' WHERE id = 30;
11.模糊数据
-
模糊查询表达式
%
表示指代任意内容,例如'%小%'
表示包含小
的表达式,且小
前后都有内容,'%小'
表示以小
结尾的表达式,王
前面有内容,后面没有内容,'小%'
表示以小
开头的表达式,小
前面没有内容,后面有内容 -
使用LIKE模糊查询 以 teacher 表为例,需要查询出姓 王 的教师信息结果集: SELECT * FROM teacher WHERE name LIKE '王%';
12.WHERE条件查询
- 需要注意的是当使用
>
、<
、这样的操作符和空值比较的时候,NULL值与任何其它值的比较(即使是NULL)永远不会为TRUE
,例如要查询name
为 NULL 的结果集要写成name IS NULL
,而不能使用name=NULL
。
13.联合查询
-
有时候需要把满足多种独立条件的结果集整合到一起,就可以使用 UNOIN 联合查询
-
使用 UNION ALL 把满足两种查询条件的结果集并到一起: SELECT * FROM teacher WHERE age > 20 UNION ALL SELECT * FROM teacher WHERE age > 25;
-
Tips:如上图所示,
UNION ALL
将两种查询结果并到一起,仔细观察可以发现结果集中有重复的数据,所以使用UNION ALL
联合查询的结果集没有去掉重复的数据。 -
现在使用 UNION 把上面两种结果集并到一起: SELECT * FROM teacher WHERE age > 20 UNION SELECT * FROM teacher WHERE age > 25;
-
Tips:如上图所示,
UNION
将两种查询结果并到一起,可以看到结果集中已经去掉重复的数据,需要根据具体业务选择使用UNION
还是UNION ALL
。
14.排序
-
ASC 是对结果集按照字段从小到大排序(升序) SELECT * FROM teacher ORDER BY age ASC;
-
DESC 是对结果集按照字段从大到小排序(降序) SELECT * FROM teacher ORDER BY id DESC;
-
使用多字段混合排序 以 teacher 表为例,将查询出来的结果集按照 age 从大到小排序之后,再按照 id 字段从小到大排序: SELECT * FROM teacher ORDER BY age DESC,id ASC;
-
对字符串类型字段排序 SELECT * FROM teacher ORDER BY name ASC;
-
Tips:如上图所示,使用 ASC 对结果集按照 name 字段升序,其实是对 name 字段字符串编码的排序,英文字符排序在前,中文在后,其中排序规则是按照字符 ASCII码 对应值的大小排序的。
-
需要注意的是各种数据的字符集可能不同,如中文字符编码 GBK编码、utf-8编码, 若需要经常对字符串类型字段进行排序,可以给该字符串字段加上普通 Bree索引,二级索引树字符串默认存储方式是按照字符集升序存储的(MySQL8.0 可自定义排序存储方式),所以有索引的字段排序性能比没有索引的字段排序性能好。
15.表连接
16.分组
-
按照 teacher_id 字段分组 SELECT teacher_id FROM course GROUP BY teacher_id;
-
根据上面分组结果还可以使用 LEFT JOIN 连接查询分组 SELECT c.teacher_id,t.* FROM course c LEFT JOIN teacher t ON c.teacher_id=t.id GROUP BY c.teacher_id;
-
使用 GROUP BY 按照 teacher_id、course_id 分组来展示所有教师的所有课程信息: SELECT c.teacher_id,a.course_id,c.course_name,d.name FROM student_course a INNER JOIN student b ON a.student_id=b.id INNER JOIN course c ON a.course_id=c.id INNER JOIN teacher d ON c.teacher_id=d.id GROUP BY c.teacher_id,a.course_id;
17.去重
-
单字段去重 SELECT DISTINCT student_id FROM student_course a INNER JOIN student b ON a.student_id=b.id;
-
多字段去重
18.聚合函数
-
可以使用 AVG() 函数求出全部教师平均年龄: SELECT AVG(age) FROM teacher;
-
使用 COUNT() 函数统计全部学生数量: SELECT COUNT(*) FROM student; 也可以对某一列使用 COUNT() 函数: SELECT COUNT(id) FROM teacher;
-
SUM函数统计总和 SELECT SUM(age) FROM teacher;
-
MIN函数取最小值 SELECT MIN(age) FROM teacher; MAX函数取最大值 SELECT MAX(age) FROM student;
-
注意聚合函数产生的数据列最好重命名,这是因为后端程序语言在处理这些数据时需要规范的字段名
19.条件判断函数
- IF、IFNULL、CASE 三种条件判断,并且可以对指定字段进行条件判断得到满足需求的结果,需要注意的是,实际业务中这些条件判断用的很少,建议这些数据的处理交给后端程序语言去处理,可以更好的维护和管理业务逻辑代码。
20.系统函数
- 本小节介绍了 MySQL 提供的系统函数
21.正则表达式
- 正则表达式中的元字符
22.储存引擎概述
- MySQL 数据库提供了独有的插件式存储引擎,常见存储引擎有 InnoDB、MyISAM、NDB、Memory、Archive、Federated、Maria 等等,并且不同的存储引擎有着完全不同的功能,建表的时候可以指定存储引擎的类型,若不指定存储引擎类型,MySQL8.0 默认的存储引擎就是 InnoDB。
数据列最好重命名,这是因为后端程序语言在处理这些数据时需要规范的字段名
19.条件判断函数
- IF、IFNULL、CASE 三种条件判断,并且可以对指定字段进行条件判断得到满足需求的结果,需要注意的是,实际业务中这些条件判断用的很少,建议这些数据的处理交给后端程序语言去处理,可以更好的维护和管理业务逻辑代码。
20.系统函数
- 本小节介绍了 MySQL 提供的系统函数
21.正则表达式
- 正则表达式中的元字符
22.储存引擎概述
- MySQL 数据库提供了独有的插件式存储引擎,常见存储引擎有 InnoDB、MyISAM、NDB、Memory、Archive、Federated、Maria 等等,并且不同的存储引擎有着完全不同的功能,建表的时候可以指定存储引擎的类型,若不指定存储引擎类型,MySQL8.0 默认的存储引擎就是 InnoDB。