系统学测试 - Mysql数据库的认识和使用（上）

1. 前后台数据交换过程

前后台数据交换图

2. 数据库基本知识

2.1.关系型数据库概念

关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。 

2.2. 常见的数据库系统

常见的数据库管理系统有：

    Oracle 甲骨文

    DB2 IBM出品的一些列关系型数据库

    Mysql 开源数据库软件

    SQL Server 微软

    Redis 最好用的缓存数据库



2.3. MySQL数据库服务器、数据库和表的关系

所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。

为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。数据库服务器、数据库和表的关系如图所示：


2.4. 数据在数据库中的存储方式

表的一行称之为一条记录


2.5. SQL语言

Structured Query Language, 结构化查询语言

SQL是用来存取关系数据库的语言，具有查询、操纵、定义和控制关系型数据库的四方面功能


3. 数据库的安装

安装5.7.3版本

参考MySQL安装文件

{详细视频和资料，请前往腾讯课堂搜索育华志远查收}


4. SQL分类

4.1. DDL（数据定义语句--了解）

数据定义语言- Data Definition Language

用来定义数据库对象：库、表、列等；创建、删除、修改：库、表结构

4.2. DML（数据操纵语句--掌握）

数据处理语言- Data Manipulation Language

在数据库表中更新，增加和删除记录

如update， insert， delete --- 增删改

4.3. DQL（数据查询语句--掌握）

数据查询语言– Data Query Language

select

4.4. DCL（数据控制语句--了解）

数据控制语言– Data Control Language

指用于设置用户权限和控制事务语句

如grant，revoke，if…else，while，begin transaction


5.DDL（数据定义语句）

5.1. 数据库创建和操作

create database  数据库名;  创建数据库    

create database testDB;     

show databases;  展示数据库

show create database; 展示数据库的创建细节

use testDB;    切换数据库     

select database(); 查看当前数据库  

alter database character set 字符集修改数据库

drop database 数据库名;    删除数据库    

drop database testDB; 


5.2. 表的创建

1. [endif]CREATE TABLE table_name  (  

field1  datatype,  

field2  datatype,  

 field3  datatype  

); 

field：指定列名　datatype：指定列类型  

注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。


5.3. 数据类型

int：整型

float/double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99最小值是0.01；  

decimal：浮点型，在表单钱方面使用该类型，因为不会出现精度缺失问题；对十进制运算比较精确的类型

char：固定长度字符串类型；char(255)，数据的长度不足指定长度，补足到指定长度！

sex Char(2)       男   

varchar：可变长度字符串类型；varchar(65535),

name varchar(10)   

text(clob)：字符串类型；  

blob：字节类型；  

date：日期类型，格式为：yyyy-MM-dd；

time：时间类型，格式为：hh:mm:ss

timestamp：时间戳类型；若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

5.4. 表的删除修改查看

alter table 表名 add 列名 列的类型 列的约束  

alter table 表名 modify 列名 列的类型 列的约束   

show tables ; 查看当前数据库中所有的表名  

show create table 表名: 查看表的定义结构/创建语句  

desc 表名 : 查看表的结构

drop table 表名  


5.5.创建表练习

创建一个员工表employee ----查看表结构: desc 表名;

CREATE TABLE employee (

id INT ,

NAME VARCHAR (20),

gender VARCHAR (20),

birthday date,

entry_date date,

job VARCHAR (20),

salary FLOAT (10, 2),

resume text

);


创建一个商品表product 有pid,price价格,shelves_date 上架日期 shelves_time上架时间，tamp时间戳

CREATE TABLE product (

pid INT,

price FLOAT (9,2),

shelves_date date,

shelves_time time,

tamp TIMESTAMP

);

{大家自己可以上手试一下，多多练习！}

5.6. 单表字段的约束

定义主键约束

        primary key:不允许为空，不允许重复

        删除主键：alter table tablename drop primary key ;

        主键自动增长：auto_increment


定义唯一约束   unique  

例如：name varchar(20) unique

定义非空约束   not null  

例如：salary double not null


6. DML（数据操纵语句）

6.1.数据库CRUD语句

Insert语句    (增加数据)

Update语句  (更新数据)

Delete语句    (删除数据)

Select语句　  (查找数据)


6.2. Insert语句

1. 方式一:INSERT INTO 表名(列名1,列名2, ...) VALUES(列值1, 列值2, ...);  

2. 方式二:INSERT INTO 表名(列名1,列名3) VALUES(列值1, 列值3);  

3. 方式三:INTERTINTO 表名 VALUES(列值1, 列值2)



插入的数据应与字段的数据类型相同。

数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。

在values中列出的数据位置必须与被加入的列的排列位置相对应。

字符和日期型数据应包含在单引号中。

插入空值：不指定或insert into table value(null)


6.3. 使用insert语句向表中插入三个员工的信息。


方式一:

insert into employee (id,name,gender,birthday,entry_date,job,salary,resume) values(1,"张三","男","1999-10-10","2020-12-20","文员",4000.01,"来自山东");

方式二:

insert into employee  values(2,"李四","男","1998-10-10","2020-12-30","会计",4500.01,"来自北京");

方式三:-多条插入

insert into employee  values(3,"王五","男","1998-10-10","2020-12-30","会计",4500.01,"来自北京"),

(4,"赵六","男","1998-10-10","2020-12-30","会计",4500.01,"来自北京"),

(5,"小花","女","1998-10-10","2020-12-30","出纳",4500.01,"来自上海");


6.4. update语句

修改数据:UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ... [WHERE 条件] 

UPDATE语法可以用新值更新原有表行中的各列。

SET子句指示要修改哪些列和要给予哪些值。

WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。


练习:

-- 将所有员工薪水修改为5000元。

update employee set salary=5000;

-- 将姓名为张三的员工薪水修改为3000元。

update employee set salary=3000 where name="张三";

-- 将姓名为李四的员工薪水修改为4000元,job改为ccc。

update employee set salary=4000,job="ccc" where name="李四";

-- 将王五的薪水在原有基础上增加1000元。

update employee set salary=salary+1000 where name="王五";


6.5. delete语句

1. DELETE FROM 表名 [WHERE 条件];  


如果不使用where子句，将删除表中所有数据。

delete语句不能删除某一列的值（可使用update）

使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。

同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。

删除表中数据也可使用TRUNCATE TABLE语句，它和delete有所不同，参看mysql文档。

* delete可以加条件 truncate是删除整张表

* delete支持回滚 truncate不支持

* delete清理速度慢 trucate快


练习:

删除表中名称为张三的记录。      delete from employee where name="张三";

删除表中所有记录。                     delete from employee;

使用truncate删除表中记录。         truncate employee;


7. DQL（数据查询语句--掌握）

7.1. 简单查询

1.SELECT * FROM 表名;    {其中“*”表示查询所有列}  

7.2. 去重复查询

关键字distinct

select distinct job from employee;

7.3. 查询指定列

SELECT 列1 [, 列2, ... 列N] FROM 表名;

select name,job,salary from employee;

7.4. 列运算

数据类型的列可以做加、减、乘、除运算

给员工涨工资【倍数1.5】：select salary*1.5 from employee;

工资加奖金：select *,salary+bonus from employee;  

当数字碰到null的时候整个就会变成null  

select *,salary*12+ifnull(bonus,0) from employee;

给列起别名  ：查询出的结果集中的列名称不好看，可以给列名起个别名，

select *,salary*12+ifnull(bonus,0) 年薪 from employee;


7.5. 模糊查询

关键字：like

_表示的单个字符 。%表示的是多个字符

select * from employee where name like "小_"; 查询的结果是两个字的名字 并以小字开头

select * from employee where name like "小%"; 查询的结果是未知个字的名字但是是以小字开头

select * from employee where name like "%花%"; 查询的结果最少也是一个字，有可能花开头也有可能花结尾 也有可能花在中间的位置 【这种模糊查询才是经常使用的】


7.6. 关系运算符

= 等于 ， != 不等于， > ,  < , >= , <=

select * from employee where job >3000;

select * from employee where job != 3000;


7.7. 逻辑运算符

and 并且的意思   多个条件的话需要都要满足

or或者的意思    多个条件的话有一个满足即可


select * from employee where job="会计" and salary>3000;

select * from employee where job="会计" && salary>3000;

select * from employee where gender="女" or salary>9000;

select * from employee where gender="女" || salary>9000;


7.8.limit 分页查询

Limit 开始索引，长度

limit m,n    第一页:limit 0,10  (下标从0开始,10表示加载10条数据) 第二页:limit 10,10 第三页: limit 20,10


7.9.between ... and ...

查询薪资是6000到9000之间的员工  【不包含开始和结束】

select * from employee where salary between 6000 and 9000;


7.10. in在范围中

查询名字是小王/张/赵的员工

select * from employee where name in ("小张","小王","小赵");

not in    是不在范围内的意思


7.11. 排序order by

1. ASC【升序排序】    默认就是升序排序

2. DESC【降序排序】  


select * from employee order by birthdayasc;按照出生时间排序(数字从小到大)

select * from employee order by birthday desc;按照出生时间倒序(数字从大到小)

select * from employeeorder by salary desc,birthday desc;(多条件排序)

select * from employee order by salary desc limit 0,3;查询salary 最大的三名


8. 聚合函数

8.1. count() :统计数量

Count(列名)返回某一列，行的总数

1. Select count(*)|count(列名) from 表名[WHERE 条件]   

coun(*) 和count(1) 的结果都是一样的最终都会得到总条数

练习：

-- 统计一个班级共有多少学生？                  select count(*) from student;

-- 统计数学成绩大于90的学生有多少个？    select count(*) from student where math>90;

-- 统计总分大于250的人数有多少？

select count(*) from student where (math+english+chinese)>250;


8.2. sum :求和

1. Select sum(列名)｛,sum(列名)…｝ from 表名[WHERE 条件]     


练习：

统计一个班级数学总成绩？   select sum(math) 数学总成绩 from student;

统计一个班级语文、英语、数学各科的总成绩

select sum(chinese),sum(english),sum(math) from student;

统计一个班级语文、英语、数学的成绩总和

select sum(chinese+english+math) from student;


8.3.  avg()  :平均值

AVG函数返回满足where条件的一列的平均值

1. Select avg(列名)｛,avg(列名)…｝ from 表名 [WHERE]   

练习：

统计一个班级语文成绩平均分            select avg(chinese) from student;

求一个班级数学平均分？                   select avg(math) from student;

求一个班级总分平均分                       select avg(chinese+math+english) from student;


8.4. max() :最大值/min() :最小值

Max/min函数返回满足where条件的一列的最大/最小值

1. Select max(列名)　from tablename [WHERE 条件]   


练习：

求班级数学最高分和最低分

select max(math),min(math) from student;

求班级总分最高分和最低分

select max(math+english+chinese) from student;

求数学最高分的同学

select * from student where math=(select max(math) from student);

select * from student order by math desc limit 0,1;(本条语句结果不准确 最高分相同 只能查出一条数据)

求班级总分最高的同学

select * from student where (chinese+english+math)=(select max(chinese+math+english) from student );

select * from student order by (chinese+english+math) desc limit 0,1;(本条语句结果不准确 最高分相同 只能查出一条数据)

9. 分组查询

分组原理：按照分组的列名，将该列中数据相同的部分分到一组,然后对每一组中的数据进行计算。【分组查询不是用来查询个人信息的】

1. select  列名 from 表名 where  限定条件 group by 分组的列 [having  分组之后的筛选条件]  

Having和where均可实现过滤，但在having可以使用聚集函数,having通常跟在group by后，它作用于分组


练习表数据

create table orders(id int,product varchar(20),price float);


insert into orders(id,product,price) values(1,'电视',900);

insert into orders(id,product,price) values(2,'洗衣机',100);

insert into orders(id,product,price) values(3,'洗衣粉',90);

insert into orders(id,product,price) values(4,'桔子',9);

insert into orders(id,product,price) values(5,'洗衣粉',90);



练习：

对订单表中商品归类后，显示每一类商品的总价

select product, sum(price) from orders group by product;

查询购买的商品类别，并且每类总价大于100的商品

select product, sum(price) from orders group by product having sum(price)>100;

查询每一类商品买的个数

select product,count(*) from orders group by product;

查询出购买个数大于1的商品

select product,count(*) from orders group by product having count(*)>1;


10. Navicat工具使用

10.1. 安装

10.2. 连接

{详细安装视频和使用步骤，腾讯课堂请搜索育华志远查看}

今天先聊到这里，休息一下！后期努力更新中...........

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,684评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,143评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,214评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,788评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,796评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,665评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,027评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,679评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 41,346评论 1赞 299
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,664评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,766评论 1赞 331
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,412评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,015评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,974评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,203评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,073评论 2赞 350
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,501评论 2赞 343

推荐阅读更多精彩内容

  • Mysql数据库
    基础查询 select 查询列表 from 表名; USE myemployees; //最好写上 1.查询表中的...
    暧莓悠阅读 571评论 0赞 1
  • 数据库(5) | MySQL SQL 语言查询
    SQL语言基础 本章，我们将会重点探讨SQL语言基础，学习用SQL进行数据库的基本数据查询操作。另外请注意本章的S...
    厲铆兄阅读 5,299评论 2赞 46
  • 一 MySQL 关系型数据库
    7.1数据库表的基本结构 关系结构数据库是以表格（Table）进行数据存储，表格由“行和“列”组成。 执查询语句返...
    Ten_tones阅读 225评论 0赞 0
  • 面试题——MySQL数据库
    数据库基础知识 为什么要使用数据库 数据保存在内存优点： 存取速度快缺点： 数据不能永久保存 数据保存在文件优点：...
    淺時咣阅读 325评论 0赞 1
  • MySQL数据库
    一、数据库概述 1.为什么软件测试工程师还需要学习数据库以及开发方面的知识？ 测试工程师的目的是找出软件的不足，并...
    故里里阅读 566评论 0赞 1