MySQL数据库使用教程
2018-01-09 本文已影响0人
黑夜_Eric
SQL的分类
DDL: 数据定义语言
DML: 数据操作语言
DQL: 数据查询语言
DCL: 数据控制语言
DTL: 事务处理语言
创建数据库
创建数据库 create database databaseName(数据库名字）;
查询数据库 show databases;
删除数据库 drop database databaseName;
使用数据库 use databaseName;
show create database databaseName 创建数据库的一个简要描述，可以看到数据库的默认字符集
创建表格
create table 表名(字段名 数据类型, 字段名 数据类型); 创建数据表的方式
创建表格 create table tablename(id int not null auto_increment primary key,name varchar(20));
--id自增--
drop table tableName; 删除数据表格
desc tableName; 可以查看表的详细信息
show create table tableName 创建数据表格的一个简要描述，可以看到数据表格的默认存储引擎和字符集
例如：
create table stuInfo(
#字段名 字段数据类型
#学生ID
stuId int,
#姓名 varchar(30) char(30)
#varchar是一个可变长数据
stuName varchar(30),
#性别 tinyint 1个字节
stuGender tinyint,
#年龄 tinyint 1个字节，保存年龄足够
stuAge tinyint
);
修改默认存储引擎和字符集的方式
方式1：
engine=MyISAM default charset=GBK;
例如:
create table test (
name varchar(15),
age int
)engine=MyISAM default charset=GBK;
方式2：
修改数据的配置文件，一处修改，处处可用
my.ini
文件位置：
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
备注：ProgramData该文件夹是一个隐藏文件，需要开始系统的显示隐藏文件夹
大约在98 ~ 103
- show character set; 查看当前MySQL支持的所有字符集
- show engines; 查看当前MySQL支持的所有存储引擎
修改表 alter
添加新字段
alter table tableName add xinxi text;
alter table tableName add name int after age;(在年龄后添加新字段name)
修改老字段的数据类型
alter table tableName modify name char(29);
删除已有字段
alter table tableName drop age;
删除table数据
delete from tableName;
delete from tableName where name="张三";
truncate table stuInfo;
！！！！！慎用,小心从删除到跑路哈哈
修改已有字段的字段名和数据类型
alte table tableName change name mingZi char(30);#(数据库不区分大小写）把字段name改为mingZi 并修改数据类型为char(30)
插入数据
#按照数据库字段顺序插入一条完整的数据
insert into tableName(Id, name,sex, age)
values(1, "张三", '男', 16);插入的时候要求顺序一致
#选中一些字段添加数据 剩余数据会按照默认值处理
insert into tableName(Id, name) values(2, "李四");
#不需要指定字段名，但是要求插入的数据是和字段顺序一致
insert into tableName values(3, "王五", '男', 50);
查询数据
select * from tableName;
暂时可以使用，但是在实际的开发场景中，不会这么使用，会按照业务的需求，尽量少的查询数据
实际上：
select 字段1, 字段2 from 表名;
从指定的数据表中，获取指定字段的信息
select 字段 from 表名 where 条件;
从指定的数据表中，获取指定的字段信息，但是有条件限制
select distinct 字段 from 表名;
从指定的数据表中，获取指定的字段信息，但是会过滤掉重复信息
MySQL常用数据类型
- 数值类：tinyint，int，float， double
- 文本类：text，tinytext，longtext
-
时间：date，timestamp 时间戳， 默认可以设置current_timestamp，这样可以帮助我们记录当前时间
例如：用户的登录时间，用户注册时间，订单生成时间 - 二进制：blob
数据库大文件处理方法
一般情况下：图片，视频，大体积文本，一般不会直接保存到数据库，这样过多的占用数据库的空间会降低数据库的使用效率，通常是采用一下方法：
1.视频上传到服务器
2.服务器接受到视频。放到服务器指定的保存视频的文件夹下
3.保存时会给视频按照时间和随机数关系给视频重新命名，防止同一个文件名存在
4.将保存视频文件的服务器地址，放入到数据库中
5.之后就可以通过数据库，查询到视频地址，通过地址直接访问
MySQL四中数据类型：布尔类型、微整形、枚举类型、集合类型
- 布尔类型
MySQL数据库数据类型分类中确实存在布尔类型，但是MySQL数据库并没有真正实现布尔类型，而是借助微整型的方式实现，并且创建数据库表结构的时候，即使字段定义属性设置为布尔类型BOOL或布尔类型BOOLEAN，都会被默认改写成TINYINT
MySQL数据库产品没有真正实现对布尔类型的支持，建议大家不要使用MySQL布尔类型BOOL或布尔类型BOOLEAN，而是使用数据库类型微整型TINYINT替代。
-
enum：枚举
处理互斥的关系 例如：性别，籍贯，单选
每一个选项表示的数值是从1开始
例如：
create table test(
enumValue('河南', '山西', '福建')
)
河南 对应的枚举值为1
山西 对应的枚举值为2
枚举优点：
- MySQL枚举类型的枚举元素允许最大65535个，基本够绝大多数业务场景使用；
- 引入枚举类型数据存储，有利于缩减数据库存储数据的容量，尤其能达到减少数据库瓶颈最大的物理IO，逻辑IO也能减小，提高主机的处理能力；
- 引入枚举类型数据存储，有利于简化工程师的代码复杂度、工作量，增加代码的可读性和可维护性；
- 可以通过枚举类型元素值访问数据，也可以根据枚举类型元素编号进行访问数据；
枚举缺点
- MySQL数据库枚举类型的引入，可能给软件程序的版本发布，存在遗忘数据库结构变更的隐患；
- MySQL数据库枚举类型字段的元素增加，必须以尾部追加的方式，否则影响数据库提供数据服务；
- 枚举类型字段不再需要的元素，也不能进行删除，否则影响数据库提供数据服务；
- MySQL数据库枚举类型的字段定义属性元素值，不能随意调整其顺序，否则影响数据库提供数据服务；
数据库枚举类型是一种有应用场景广泛的数据类型，若是抛开网站程序或软件版本发布，可能会导致开发工程师与数据库维护人员之间没有配合好的问题，非常推荐大家把枚举类型引入到生产环境的数据库应用中，对企业而言也可以起到节省人力、物理等成本。建议大家使用枚举类型的时候，尽量把可能需要用到的枚举元素，都写到MySQL数据库表字段的定义属性中，减少出现漏做DDL变更的故障。
- set：集合
处理并列关系，例如：多选
集合优点：
- 数据库的数据存储容量相应缩小，利于减少数据操纵的逻辑IO和物理IO；
- 集合类型的数据读取方便，可根据字符串值，也可以根据字符串集合的顺序编号；
- 集合类型字段的定义属性维护与其他数据类型类似，并不特殊化；
- 开发工程师，不需要借助额外的集合元素编码表或程序中使用编号替代集合的字符串元素，达到减少开发成本、提高代码的可读性和可维护性；
集合缺点：
- 集合类型字段的集合元素限制最大为64个；
- 集合类型字段的定义属性的集合元素，删除导致锁表而影响数据服务提供；
- 集合类型字段的定义属性的集合元素增加，只能以尾部追加的方式，若是此特性没有掌握，则会导致数据服务提供受影响；
MySQL数据库支持集合类型，对解决一些特殊的业务场景提供了非常好的解决方案，经典应用场景案例：
人才招聘网站的用户，设置工作意向城市一项，则往往会选择1-3个城市，甚至更多城市，采用集合类型字段作为数据存储结果的话，将可以大量简化程序复杂度，以及大规模降低数据存储的容量，唯一的遗憾则是集合元素限制为64个，会导致无法满足招聘网站后期业务发展需要。
电子商务等行业特点：
- 数据分类的种类较多，往往超过总数64的限制；
- 公司或产品开发维护的技术工程师更迭频繁；
- 电子商务、招聘网站等行业的产品运营周期长；
- 多数公司的软件程序版本发布流程不健全；
- 数据库维护人员和开发工程师的工作配合，容易出现信息同步不到位或不周全的情况；
- 用户的数据安全性和正确性，对企业非常重要，往往都是花费较贵的推广费用吸引而来的；
MySQL数据库中采用集合类型存储数据，生产环境的网站程序或软件版本更新发布时，一旦出现数据库维护人员没有优先更新数据库表字段的定义属性，则会导致重大的数据丢失事故，给企业造成直接的经济损失。
网络游戏行业的特点：
- 有大量业务是分类不多的数据需要存储（注：往往是几个分类，最多不超过30个分类）；
- 网络游戏行业要求开发成本降低、开发速度快等特点；
- 网络游戏公司采用的数据库服务器的硬件配置差，甚至单硬盘的主机支撑多个数据库提供数据服务，主机的存储空间也有限；
- 网络游戏产品的生命周期绝大部分不超过5年，一般的网络游戏产品运营1年以上就很少再更新软件的版本；
网络游戏产品使用MySQL数据库集合类型字段存储业务数据，是非常值得推荐的方式，对开发人员而言，跟使用其他数据类型是一样的，也无额外学习成本，还可以为企业降低开发成本、硬件资源成本。
数据库常用操作
学生资料与成绩查询
91owFA.md.png
91o0JI.md.png
- 创建表
CREATE TABLE student (
id INT(10) NOT NULL UNIQUE PRIMARY KEY ,
name VARCHAR(20) NOT NULL ,
sex VARCHAR(4) ,
birth YEAR,
department VARCHAR(20) ,
address VARCHAR(50)
);
向student表插入记录的INSERT语句如下：
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
CREATE TABLE score (
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT ,
stu_id INT(10) NOT NULL ,
c_name VARCHAR(20) ,
grade INT(10)
);
向score表插入记录的INSERT语句如下：
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);
-
查询所有记录
SELECT * FROM student
SELECT * FROM score -
查询student表的第2条到4条记录
SELECT * FROM student LIMIT 1,3; -
从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
SELECT id,name,department FROM student; -
从student表中查询计算机系和英语系的学生的信息
SELECT * FROM student WHERE department IN ('计算机系','英语系'); - 从student表中查询年龄18~22岁的学生信息
SELECT id,name,sex,2013-birth AS age,department,address
FROM student
WHERE year(now())-birth BETWEEN 22 AND 30;
SELECT id,name,sex,2013birth AS age,department,address
FROM student
WHERE 2018-birth>=22 AND 2018-birth<=30;
-查询每个院系有多少人
SELECT department,COUNT(id) FROM student GROUP BY department;
- 查询每个科目的最高分
SELECT c_name,MAX(grade) FROM score GROUP BY c_name;
- 查询李四的考试科目（c_name）和考试成绩（grade）
SELECT c_name,grade FROM score WHERE
stu_id=(SELECT id FROM student WHERE name='李四');
- 所有学生的信息和考试信息
SELECT student.id,name,sex,birth,department,address,c_name,grade
FROM student,score
WHERE student.id=stu_id;
- 计算每个学生的总成绩
SELECT student.id,name,department SUM(grade) FROM student,score
WHERE student.id=stu_id
GROUP BY id;
-- GROUP BY name;
- 计算每个考试科目的平均成绩
SELECT c_name,AVG(grade) FROM score GROUP BY c_name;
- 查询计算机成绩低于95的学生信息
SELECT*FROMstudent
WHEREid IN (
SELECT stu_id FROM score WHERE c_name = '计算机' AND grade < 95);
- 查询同时参加计算机和英语考试的学生的信息
SELECT * FROM student WHERE id IN(
SELECT stu_id FROM score
WHERE c_name='英语' AND stu_id IN(
SELECT stu_id FROM score WHERE c_name='计算机')
);
- 将计算机考试成绩按从高到低进行排序
SELECT stu_id,grade FROM score WHERE c_name='计算机'
ORDER BY grade DESC;
- 查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
SELECT name,department,c_name,grade FROM student,score
WHERE (name LIKE '张%' OR '王%')
AND student.id=stu_id;
- 查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
SELECT student.id,name,department,c_name,grade FROM student,score
WHERE address LIKE '湖南%' AND stu.id=student.id;
``
#未完待续。。。。。。。。
