黑马测试mysql基础学习

视频来源：软件测试工程师所需的MySQL数据库技术，mysql系统精讲+课后练习_哔哩哔哩_bilibili

环境准备：

虚拟机Linux服务器安装mysql数据库。本机安装Navicat。使Navicat连接虚拟机的数据库。（麻烦一点的是Navicat连接虚拟机的数据库。网上自查怎么做就行。）
我遇到的一些麻烦点：1.虚拟机安装mysql，可以不用更新软件包。先查看是否安装了虚拟机，没有，就直接用命令安装mysql-service即可。
2.网上教程会让你开启安全模式，这里不建议开启，因为开启安全模式之后，它会有一个密码的限制，比如你用123456就会提示你密码不符合要求，这里我们是用作练习，就不要开安全模式。
3.虚拟机连接数据库，只要在数据库的配置文件注释那一行，以及给root用户添加所有权限就可以了，其它额外的命令都不要去弄。

ps：navicat不会连接虚拟机的linux直接翻到最下面，介绍了mysql命令是怎么连接的，并比对了其它一些博客的不必要的点。

一、sql语言基础

注释：

--单行注释、/*   */多行注释。通过快捷键ctrl+/可以快速注释，同时再使用此快捷键可以取消注释。

数据类型：
int、tinyint、decimal(整数,小数)、varchar(字符串的数量)、datetime。用法：id int，name varchar(10)，height decimal(5,2)；

创建/删除表：

语法：create table 表名{ 字段名 数据类型，...  };
例如：create table a{ name varchar(10) };   //创建表a，数据类型是字符串，长度为10.

删除表语法：drop table 表名;  drop table if exists 表名;

insert添加数据：

插入一条语句：
格式1：insert into 表名 values(.....);  //字符串信息可以用单引号或者双引号引起来，数字省略引号
格式2：insert into 表名 (字段1,......) values(值1,........)  //部分字段设置值

插入多条语句：
格式1：insert into c(id,name,age) values(2,' adada',20);  再这样写几条，写多条语句，用英文隔开。
格式2：insert into 表名 values(....),(.....).....；  写一条语句，添加多条记录。（省略字段，像插入一条语句的格式1）
格式3：insert 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 age=50；update c set name="元芳",age=33 where id=3；

删除表记录:

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_increment，name 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 age<25 or age>30;

空判断：例句：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)*m，m；
例如：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 ....

语法2：select * from 表1，表2 where 表1.字段 = 表2.字段 ;
上一个例子改写：SELECT * from students,scores where students.studentNo=scores.studentNo;

练习：查询学生信息，只显示姓名、课程号、成绩。（两个表students,scores）
练习：查询姓名为“王昭君”的信息，要求只显示姓名、课程号、成绩。
练习（三表）：查询学生信息和成绩以及成绩对应的课程名称。（三表students.scores.courses）
   解：SELECT * from students INNER JOIN scores on students.studentNo=scores.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.studentNo=scores.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 host='localhost';

//这里用了和运算符，因为默认情况下，root这个用户的host是‘localhost’。不用and也可以。

方式2：update user set host ='%' where user ='root';    //其实就是and可以省。

第五步：刷新。

flush privileges;