开封做网站推广,wordpress搭建官网步骤,网站维护目标,logo在线制作免费生成器无水印视频来源#xff1a;软件测试工程师所需的MySQL数据库技术#xff0c;mysql系统精讲课后练习_哔哩哔哩_bilibili
环境准备#xff1a;
虚拟机Linux服务器安装mysql数据库。本机安装Navicat。使Navicat连接虚拟机的数据库。#xff08;麻烦一点的是Navicat连接虚拟机的数据…视频来源软件测试工程师所需的MySQL数据库技术mysql系统精讲课后练习_哔哩哔哩_bilibili
环境准备
虚拟机Linux服务器安装mysql数据库。本机安装Navicat。使Navicat连接虚拟机的数据库。麻烦一点的是Navicat连接虚拟机的数据库。网上自查怎么做就行。 我遇到的一些麻烦点1.虚拟机安装mysql可以不用更新软件包。先查看是否安装了虚拟机没有就直接用命令安装mysql-service即可。 2.网上教程会让你开启安全模式这里不建议开启因为开启安全模式之后它会有一个密码的限制比如你用123456就会提示你密码不符合要求这里我们是用作练习就不要开安全模式。 3.虚拟机连接数据库只要在数据库的配置文件注释那一行以及给root用户添加所有权限就可以了其它额外的命令都不要去弄。
psnavicat不会连接虚拟机的linux直接翻到最下面介绍了mysql命令是怎么连接的并比对了其它一些博客的不必要的点。
一、sql语言基础
注释
--单行注释、/* */多行注释。通过快捷键ctrl/可以快速注释同时再使用此快捷键可以取消注释。
数据类型 int、tinyint、decimal(整数,小数)、varchar(字符串的数量)、datetime。用法id intname varchar(10)height decimal(5,2)
创建/删除表
语法create table 表名{ 字段名 数据类型... }; 例如create table a{ name varchar(10) }; //创建表a数据类型是字符串长度为10.
删除表语法drop table 表名; drop table if exists 表名;
insert添加数据
插入一条语句 格式1insert into 表名 values(.....); //字符串信息可以用单引号或者双引号引起来数字省略引号 格式2insert into 表名 (字段1,......) values(值1,........) //部分字段设置值
插入多条语句 格式1insert into c(id,name,age) values(2, adada,20); 再这样写几条写多条语句用英文隔开。 格式2insert into 表名 values(....),(.....)..... 写一条语句添加多条记录。省略字段像插入一条语句的格式1 格式3insert into 表名(字段1....)values(值1,.....),(值1,.....)......; 写一条语句设置指定字段值。
select简单查询
select *from 表名 查询所有字段 例如select * from c; select 字段1字段2.... from 表名 查询指定字段 select age,name from c;
update修改数据
update 表名 set 字段1值1字段2值2....where 条件; 例如update c set age50update c set name元芳,age33 where id3
删除表记录:
1.delete from 表名 where 条件; //通过delete删除也可以删除整个表记录 2.truncate table 表名; ///通过truncate删除只能删除整个表记录但速度比delete快。 两种删除表记录的方式delete from c truncate table c
二、字段的约束
主要约束主键primary key值不能重复auto_increment代表自动增长 非空(not null)、唯一(unique)、默认值(default)。 一般用于创建表使用
主键 例如create table d ( id int primary key auto_incrementname varchar(10) );//创建id字段非空并自增长。 错误用法所有字段都插入但没写自增长字段值会出错。而不指定id字段值时系统会自动填写。 解决方案使用占位符通常使用0或者null来占位。
非空 create table 表名 字段名 数据类型 not null....... ;
唯一: create table 表名字段名 数据类型 unique......;
默认值 create table 表名字段名 数据类型 default 值,..........; 进阶
执行下列sql语句生成students方便后续实验。
/* 如果学生表students存在就删除学生表students */
drop table if exists students;/* 创建学生表students */
create table students (studentNo varchar(10) primary key, /*学号主键值不能重复*/name varchar(10), /*姓名*/sex varchar(1), /*性别*/hometown varchar(20), /*家乡*/age tinyint, /*年龄*/class varchar(10), /*班级*/card varchar(20) /*身份证号码*/
);/* 向学生表students插入数据 */
insert into students values
(001, 王昭君, 女, 北京, 30, 1班, 110101199003157654),
(002, 诸葛亮, 男, 上海, 29, 2班, 310102199104262354),
(003, 张飞, 男, 南京, 30, 3班, 320102199003047654),
(004, 白起, 男, 安徽, 35, 4班, 340202198505177654),
(005, 大乔, 女, 天津, 28, 3班, 120101199204067654),
(006, 孙尚香, 女, 河北, 25, 1班, 130502199506137654),
(007, 百里玄策, 男, 山西, 39, 2班, 140102198107277654),
(008, 小乔, 女, 河南, 25, 3班, null),
(009, 百里守约, 男, 湖南, 31, 1班, ),
(010, 妲己, 女, 广东, 24, 2班, 440701199607147654),
(011, 李白, 男, 北京, 30, 4班, 110202199005017754),
(012, 孙膑, 男, 新疆, 36, 3班, 650102198401297655);
别名与消除重复出现
通过as语句可以代替字段名。例如:select name as 姓名 from students查询得到姓名表而不是name的表。 且as可以省略。例如select name 姓名 from students效果和上面一样。 表的别名select name ,age,sex from students ss///表的别名一般用的较少。 消除重复记录就是使用查询语句加上这个下次再使用查询就会取消重复出现。 语法select distinct sex from 表名; //这样就只会出现男女而不是多次出现。 注意但是当出现多个字段值时例如sex后面再添加一个name值则只有sex和name都不一样才不会重复出现。
条件查询
其实就是where字句. 支持多种运算符比较运算符、、、逻辑运算符、模糊查询、范围查询、空判断. 模糊查询like取代、%表示多个字符、_表示一个字符。例如where name like 孙% 注意%这个会把一个字符的多个字符的都显示出来。 例题查询名字为两个字的学生SELECT * from students where name like __; 例题匹配最后一个字符的语句SELECT * from students where studentNo like %1 ;//匹配学号最后一个字符为1的学号。而不用管中间是否会出现1
范围查询in(...,....,....)表示一个非连续的范围。between ... and... 表示一个连续的范围。 例如SELECT * from students where age not BETWEEN 25 and 30 ; 年龄在25到30以外的记录。也可以这样写SELECT * from students where age25 or age30;
空判断例句where card is null //用is表示。
排序
语法select * from 表名 order by 字段1 asc | desc 字段2 asc |desc.... asc从小到大升序desc从大到小降序。默认情况下不给asc或desc就是升序。 例句select * from students order by age desc,studentNo按年龄降序若年龄相同则按学号升序。再比如SELECT * from students ORDER BY class ,studentNo DESC;
聚合函数(计数、求和)
聚合函数不能放在在where条件语句后中而是放在select 后。 计数语法count(*); 例句select count(*) from students where sex女; //查询学生是女生的学生数量。
最大值max(*); 例如查询最大年龄select max(age) from students;
求和sum(*)例如select sum(age) from students;
平均值avg(*);例如select avg(age) from students;
数据分组
按字段分组表示字段相同的数据放在同一个组中。group by 区别一下order by 语法select 字段1字段2聚合函数... from 表名 group by 字段1字段2 .... 例如SELECT sex,count(*) from students GROUP BY sex; 根据性别分别计数男女的个数。 练习用数据分组方法统计各个班级学生总数平均年龄、最大年龄、最小年龄。
分组后的数据筛选
语法:select 字段1字段2聚合函数... from 表名 group by 字段1字段2 .... having 字段1,....聚合... //搭配group by 使用的 having 练习查询除了1班以外其它各个班级学生的平均年龄、最大年龄、最小年龄。 注意凡是关于查询“各个”都是分组查询group by。这里还用到了筛选。 SELECT class,count(*),AVG(age),MAX(age),MIN(age) from students GROUP BY class having class!1班 练习查询班级总人数大于2人的班级总称以及班级对应的总人数。
数据分页显示
当数据量过大通过此方法分页查看。分成页数只展示第几页这样。 1.获取部分行。语法select * from 表名 limit start,count 从start开始获取count条数据若省略start默认从0开始。例如查看第三行开始的3条语句SELECT * from students LIMIT 2,3;因为默认从第0行开始所以第三行是从数字2开始。
2.分页 语法每页显示m条数据查询第n页的数据。select *from students limit(n-1)*mm 例如SELECT * from students LIMIT 8,4; 每页显示4条查询第3页的数据。 多表进阶
连接查询对多个表进行查询。 分为内连接只查询两个表共有的。左连接只查询左表存在的数据。
内连接(inner join)
语法1 select * from 表1 inner join 表2 on 表1.字段 表2.字段. 例如 select * from students inner join score on students.stu score.stu 查询students和score表stu编号相同的数据。 // inner join ... on ....
语法2select * from 表1表2 where 表1.字段 表2.字段 ; 上一个例子改写SELECT * from students,scores where students.studentNoscores.studentNo;
练习查询学生信息只显示姓名、课程号、成绩。两个表students,scores 练习查询姓名为“王昭君”的信息要求只显示姓名、课程号、成绩。 练习三表查询学生信息和成绩以及成绩对应的课程名称。三表students.scores.courses 解SELECT * from students INNER JOIN scores on students.studentNoscores.studentNo INNER JOIN courses on courses.courseNo scores.courseNo;
练习查询成绩最高的男生信息要求显示姓名、课程名、成绩。三表 注意max(scores.score)只能用于select之后但是要求显示是select name,cousename,score。 故这里考虑使用排序在最后面加上limit 1只显示1位。 解SELECT name,coursename,score from students INNER JOIN scores on students.studentNoscores.studentNo INNER JOIN courses on courses.courseNo scores.courseNo where students.sex男 ORDER BY scores.score DESC LIMIT 1;
左连接(left join)
语法select * from 表1 left join 表1.字段表2.字段 例题查询所有学生的信息以及成绩包括没有成绩的学生。 select * from studetns left join scores on students.studentNo scores.studentNo
mysql数据库本身的一些命令
选择数据库 use 数据库名称。退出mysql命令模式exit 创建数据库create database 数据库名 default charset[默认字符集]; 例如create database mytest default charset utf8 删除数据库drop databse 数据库名. 显示已有数据库show database
增加新用户
这个比较重要在远程连接Linux服务器时会用得到。
用root身份登录mysql 语法: grant all on 数据库名.表名 to 用户名‘登录主机’ identified by ‘密码’ with grant option
grant all on代表为用户赋权 数据库名可以是* ; 表名可以是*代表所有表。 ‘localhost’ 代表只能在本机登录 ‘%’ 代表可以远程登录。 with grant option 表示该用户是否能把权限分配给其它用户。
例如创建一个root用户可以操作所有数据库和表该用户可以远程登录密码为123456该用户可以为其它用户分配权限。 grant all on *.* to root% identified by 123456 with grant option
使用navicat 连接虚拟机的linux
之前都是参考别人的博客做的有些做的要么是淘汰了要么就多次一举了接下来可以分析为什么别人那么做的了。
第一步linux下载mysql这一步基本上都只有一句sudo apt-get install mysql-service。可以省略更新软件表sudo apt-get update然后使用mysql --version 验证版本即可。
第二步开启Mysql服务 sudo service mysql start
第三步打开 /etc/mysql/mysql.conf.d/ 目录下的 mysqld.cnf 文件修改其中的 bind-address 参数将其注释或改为0.0.0.0 。在重启MySQL服务sudo service mysql restart
第四步关键使用 指令登录mysql命令模式。到了这里就用到前面学的知识点了
sudo mysql -u root -p;
登录mysql之后
use mysql;
进入到mysql这个数据库里注意mysql数据库和mysql软件是两回事。如果对这个有疑问可以先用show datebase可以看到默认情况下是存在mysql这个数据库的一般默认下在mysql软件都有默认的四个数据库。
直接更新root用户的host字段改为%即表示允许远程登录。 我看了不同的博客里面有两种方式:
方式1
update user set host % where user root and hostlocalhost;
//这里用了和运算符因为默认情况下root这个用户的host是‘localhost’。不用and也可以。
方式2update user set host % where user root; //其实就是and可以省。
第五步刷新。
flush privileges;
