Mysql数据库常用sql语句介绍

在逃圣母
49 阅读11分钟

MySQL介绍

  • MySQL：关系型数据库。分为社区版和商业版，社区版免费

    1. 公司使用商业版，个人学习使用社区版

  • 特点

    1. 支持多种操作系统(windows，mac，linux)
      • 公司MySQL安装在服务器Linux系统
    2. 支持多种编程语言(可以借助编程语言完成SQL语句的执行，以及结果的处理)

  • 组成

    1. MySQL服务：存储数据，执行传入的SQL语句
    2. MySQL客户端：接收用户输入的SQL语句，以及展示SQL语句的执行结果
      • MySQL数据库默认端口号：3306
    3. 说明：MySQL客户端对于SQL语句执行和结果查看不是很方便，所以可以使用数据库编辑工具更好完成对于数据库的数据进行操作

  • 常用数据库编辑工具

    1. DBeaver，navicat
    2. 连接命令
    1.连接数据库： mysql -h (host) -P (端口号) -u (用户名) -p
2.输入密码 ：.....
    1. 工具连接
    • 使用工具：DBeaver
    • 原因：开源免费
    • 作用：拥有可视化界面，让我们操作数据库以及结果的展示更加的直观
    • 必填内容：服务器ip地址，MySQL的端口号，MySQL的用户名和密码
    • 注意点：1.服务器(虚拟机)更改IP地址，需要在工具中修改连接ip地址；2.服务器未开机，工具无法连接数据库

  • 数据类型与约束

    1. 默认值：当不填写时使用默认值，填写时使用填写内容
    2. 非空：此字段不允许填写空值
      • 说明1：NULL(数据为空)
      • 说明2：看起来字段为空，其实是空字符串
    3. 主键： 提高数据的查询速度
      • 1：唯一性：主键值必须是唯一的，不能重复
      • 2：主键不能为空
      • 3：不变性：主键使用了，不可更改
      • 4：单一性：一张数据表，仅能设置一个主键
    4. 唯一性： 此字段的值不允许重复
    5. 外键：维护两个表之间的依赖关系

数据库表操作

  • 创建表

    • 语法格式

        create table 表名(
     id int unsigned primary key auto_increment,
     字段名称1 数据类型1,
     字段名称2 数据类型2,
     字段名称3 数据类型3
   );

  • 案例练习

         创建学生表(表名：student)，字段要求如下：
     姓名[长度为20]:(字段名称：name)
     年龄[整型]:(字段名称：age)
     身高[保留2位小数]:(字段名称：height)
     提示：字符串(varchar),整型(int),小数(decimal),非符号-非负数(unsigned)
     
     create table student(
         id int unsigned primary key auto_increment,
         name varchar(20),
         age int unsigned,
         height decimal(5,2)
     )

  • 删除表

    • 模板命令
drop table if exists 表名;
  • 课堂练习
-- 删除创建的成绩表
drop table if exists grade;

数据增删改查

  • 数据增加

    • 语法格式

image.png

  • 增加一行数据
    • 方式一：全字段添加
模板命令： insert into 表名 values(…);
练习：在学生表(表名：student)中插入三条数据
         第一条信息：姓名--小张，年龄--25，身高--178.50
         第二条信息：姓名--小王，年龄--16，身高--183.88
         第三条信息：姓名--小陈，年龄--23，身高--163.00
         insert into student values(0,'小张',25,178.50);
         insert into student values(null,'小王',16,183.88);
         insert into student values(null,'小陈',23,163.00);
  • 方式二：选择字段添加
模板命令： insert into 表名 (字段1, 字段2, …) values(值1, 值2, …);
练习：在学生表(表名：student)中插入三条数据---特别说明：每条记录不添加身高信息
      第一条信息：姓名--小赵，年龄--18
      第二条信息：姓名--小钱，年龄--19
      第三条信息：姓名--小孙，年龄--20
      insert into student (name, age) values('小赵', 18);
      insert into student (name, age) values('小钱', 19);
      insert into student (name, age) values('小孙', 20);
  • 增加多行数据
  • 方式一：全字段添加
模板命令： insert into 表名 values (...),(...),(...),...;
练习：在学生表(表名：student)中插入三条数据
       第一条信息：姓名--小周，年龄--19，身高--168.50
       第二条信息：姓名--小武，年龄--21，身高--185.03
       第三条信息：姓名--小王，年龄--22，身高--170.05
       insert into student values (0,'小周',19,168.50),(null,'小武',21,185.03),(null,'小王',22,170.05);
  • 方式二：选择字段添加
模板命令： insert into 表名(字段名1,...) values(值1,...),(值1,...),...;
练习：在学生表(表名：student)中插入三条数据---特别说明：每条记录不添加身高信息
       第一条信息：姓名--小郑，年龄--20
       第二条信息：姓名--小黄，年龄--20
       第三条信息：姓名--小牛，年龄--21
       insert into student(name,age) values('小郑',20),('小黄',20),('小牛',21);

  • 数据的修改

  • 修改语法格式

image.png

  • 命令模板
update 表名 set 字段名1=值1,字段名2=值2,... where 条件;
  • 练习
    将学生表(表名：student)中，id为1的数据修改为(姓名：张汤姆，年龄：78)
    update student set name='张汤姆',age=78 where id = 1;

  • 数据的删除

  • 删除语法格式

image.png

  • 命令模板
delete from 表名 where 条件;
  • 练习
        将学生表(表名：student)中，id为2的数据删除
        delete  from student where id = 2;

  • 三种删除数据区别

image.png

  • 数据的简单查询

    • 模板命令
select * from 表名;
select 字段1,字段2... from 表名;
  • 练习
     查询学生表(表名：student)全部字段的数据
     select  *  from  student;

     查询学生表(表名：student),仅查看name和age字段的数据
     select   name,age  from  student;

数据查询操作

  • 查询基本语法

  1. 起别名

    • 关键字：as

    • 作用：给表名、字段名起别名

      表名——起别名语法格式：表名 as 别名
字段名——起别名语法格式：字段名 as 别名

    • 使用场景：

      对表名起别名，主要用作表关联
对字段名起别名，主要用作执行结果字段的中文展示

    • 练习

      仅查询学生表(表名：students)中的姓名(字段名称：name)，性别(字段名称：sex)，  家乡(字段名称：hometown)字段的值。
要求：1.将表名起别名为stu；2.使用表的别名的方式，指定需要查询的字段；3.针对name字段起别名，执行后展示为姓名
 select stu.name as 姓名,stu.sex,stu.hometown from students as stu;
  2. 去重

image.png

  • 关键字： distinct

  • 语法格式：distinct 字段名称

  • 作用：针对某一个字段去除掉重复的数据

  • 使用场景：通过结合聚合函数中的"查询总记录数"一起使用，用来统计某一个字段有几种数据

  • 练习

            查询学生表(表名：students)中，学生都来自于哪些省份(字段名称：hometown)
        需求分析：如果有重复的省份，就展示一次即可
        语句格式：distinct 字段名称
        select distinct(hometown) from students;

  • 条件查询

  1. 比较运算符

    • 语法格式：表名 where 查询的字段名称 比较运算符 预期数据

image.png

  • 编写SQL语句思路

        找出最终需要展示的字段有哪些
    找出查询字段(条件字段)
    使用哪一种运算符
    找出预期数据

  • 练习

    -- 例1：查询小乔的年龄(表名：students，年龄-字段名称：age，姓名-字段名称：name)
-- 分析1-需要展示字段:年龄
-- 分析2-查询的条件：姓名
-- 分析3-使用的比较运算符：等于
select  age  from  students  where  name = '小乔';

-- 例2：查询20岁以下的学生全部信息(表名：students，字段名称：age)
-- 分析1-需要展示字段：全部
-- 分析2-查询的条件：年龄
-- 分析3-使用的比较运算符：小于
select * from students where age < 20;

-- 例3：查询家乡不在北京的学生全部信息(表名：students，字段名称：hometown)
-- 分析1-需要展示字段：全部
-- 分析2-查询的条件：家乡
-- 分析3-使用的比较运算符：不等于
select * from students where hometown != '北京';

  1. 逻辑运算符

    • 语法格式

image.png

  • 练习

    -- 例1：查询年龄小于20的'女'同学(表名：students，年龄-字段名称：age，性别-字段名 称：sex)
-- 分析1-条件：年龄小于20,'女'同学
-- 分析2-使用逻辑运算符：and
select * from students where age < 20 and sex = '女';

-- 例2：查询'女'学生或'1班'的学生(表名：students，性别-字段名称：sex，班级-字段名称：class)
 -- 分析1-条件：'女'学生,'1班'
 -- 分析2-使用逻辑运算符：or
 select * from students where sex = '女' or class = '1班';

 -- 例3：查询非'天津'的学生(表名：students，字段名称：hometown)
 -- 分析1-条件：非'天津'
 -- 分析2-使用逻辑运算符：not
 select * from students where not hometown = '天津';

  1. 模糊查询

image.png

  关键字：like
  注意：仅针对**字符串**进行模糊匹配
  语法格式：where  字段  like  匹配条件
  匹配多个字符：%
  匹配一个字符：_
  • 编写SQL语句思路

    1. 分析模糊匹配符放置位置(前面，后面，前面+后面)

    • 放置--前面：以XXX结尾(查询手机号，以1009结尾)

    • 前置--后面：以XXX开头(查询手机号，以138开头)

    • 前面+后面：匹配包含(查询手机号，包含123)

    1. 分析使用哪一个符号

    • 练习

           -- 例1：查询姓孙的学生(表名：students，姓名-字段名称：name)
     -- 分析1-符号放置位置：后面
     -- 分析2-使用符号：%
     select * from students where name like '孙%';

     -- 例2：查询姓孙且名字是一个字的学生(表名：students，姓名-字段名称：name)
     -- 分析1-符号放置位置：后面
     -- 分析2-使用符号：_
     select * from students where name like '孙_';

     -- 例3：查询姓名以‘乔’结尾的学生(表名：students，姓名-字段名称：name)
     -- 分析1-符号放置位置：前面
     -- 分析2-使用符号：%
     select * from students where name like '%乔';

     -- 例4：查询姓名中包含‘白’的学生(表名：students，姓名-字段名称：name)
     -- 分析1-符号放置位置：前面，后面
     -- 分析2-使用符号：%
     select * from students where name like '%白%';

  1. 范围查询

    • 语法格式

image.png

  • 练习

        -- 查询家乡是'北京'或'上海'或'广东'的学生(表名：students，家乡-字段名称：hometown)
    -- 分析1-查询字段：家乡
    -- 分析2-是否为连续范围：非连续
    -- 分析3-使用关键字：in
    select * from students where hometown in ('北京','上海','广东');

    -- 查询年龄为18至20的学生(表名：students，年龄-字段名称：age)
    -- 分析1-查询字段：年龄
    -- 分析2-是否为连续范围：是连续
    -- 分析3-使用关键字：between...and...
    select * from students where age BETWEEN 18 and 20;

  1. 空判断

    • 语法格式：

image.png

  • 练习

        -- 例：查询没有填写身份证的学生(表名：students，身份证号-字段名称：card)
    select * from students where card is null;

    -- 例：查询填写了身份证的学生(表名：students，身份证号-字段名称：card)
    select * from students where card is not null;
    select * from students where card is not null and card != '';

  • 排序

    • 语法格式

image.png

  • 练习

     -- 例1：查询所有学生信息，按年龄从小到大排序(表名：students，年龄-字段名称：age)
 -- 分析1-需要排序的字段：年龄
 -- 分析2-排序的方式以及使用关键字：从小到大--asc
 select * from students order by age asc;

-- 例2：查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序
-- (表名：students，年龄-字段名称：age，学号-字段名称：studentNo)
-- 分析1-需要排序的字段：年龄,学号
-- 分析2-排序的方式以及使用关键字：年龄--从大到小--desc,学号--从小到大--asc
select * from students order by age desc,studentNo asc;

  • 聚合函数

    • 语法格式

image.png

  • 练习

    -- 1、查询所有学生的最大年龄、最小年龄、平均年龄(表名：students,年龄-字段名称：age)
-- 分析1-需要处理字段：年龄
-- 分析2--使用聚合函数：max,min,avg
 SELECT   MAX(age),MIN(age),AVG(age)   FROM  students ;


 -- 2、1班共有多少个学生(表名：students,班级-字段名称：class)
 -- 分析1-需要处理字段：全部
 -- 分析2--使用聚合函数：count
 SELECT  COUNT(*)     FROM  students  WHERE class ='1班';


-- 3、查询3班年龄小于18岁的同学有几个(表名：students,班级-字段名称：class,年龄-字段名称：age)
-- 分析1-需要处理字段：全部
-- 分析2--使用聚合函数：count
 SELECT  COUNT(*) from students WHERE class ='3班' and  age < 18;

  • 分组

  1. 分组查询
    • 语法格式

image.png

  • 练习

     -- 例1：查询各种性别的人数(表名：students,性别-字段名称：sex)
 -- 分析1-确认需要分组的字段：性别
 -- 分析2-确认需要使用聚合函数和处理字段：count
 select sex,count(*) from students group by sex;

-- 例2：查询各种性别年龄最大的(表名：students,性别-字段名称：sex，年龄-字段名称：age)
-- 分析1-确认需要分组的字段：性别
-- 分析2-确认需要使用聚合函数和处理字段：max--年龄
select sex,max(age) from students group by sex;

-- 例3：查询各个班级的人数(表名：students,班级-字段名称：class)
-- 分析1-确认需要分组的字段：班级
-- 分析2-确认需要使用聚合函数和处理字段：count
 select class,count(*) from students group by class;

 -- 例4：查询各个班级中不同性别的人数(表名：students,班级-字段名称：class,性别-字段名称：sex)
-- 分析1-确认需要分组的字段：班级,性别
-- 分析2-确认需要使用聚合函数和处理字段：count
select class,sex,count(*) from students group by class,sex;
  1. 分组筛选
  • 作用：对于分组完之后的数据进行筛选
  • 语法格式

image.png

  • 练习

         -- 例1：查询男生总人数-使用分组的方式(表名：students,性别-字段名称：sex)
     -- 分析1-确认需要分组的字段：性别
     -- 分析2-确认需要使用聚合函数和处理字段：count
     -- 分析3-确认需要筛选的字段和条件：性别
     select sex,count(*) from students group by sex HAVING sex = '男';

     -- 例2：查询每个班级男生的总记录数(表名：students,班级-字段名称：class,性别-字段名称：sex)
      -- 分析1-确认需要分组的字段：班级,性别
      -- 分析2-确认需要使用聚合函数和处理字段：count
      -- 分析3-确认需要筛选的字段和条件：性别
      select class,sex,count(*) from students group by class,sex HAVING sex = '男';

     -- 例3：查询各个班级中不同性别的人数大于1的记录数(表名：students,班级-字段名称：class,性别-字段名称：sex)
     -- 分析1-确认需要分组的字段：班级,性别
     -- 分析2-确认需要使用聚合函数和处理字段：count
     -- 分析3-确认需要筛选的字段和条件：count(*) > 1
     select class,sex,count(*) from students group by class,sex HAVING count(*) > 1;

  • 分页

  • 语法格式

image.png

  • 练习

           -- 例1：查询前3行学生信息(表名：students)
       select * from students limit 3;

       -- 1、查询第4到第6行学生信息(表名：students)
       select * from students limit 3,3;

连接查询

  • 内连接

  • 语法格式

image.png

  • 练习
-- 例1：查询学生信息及学生的成绩
-- (学生信息表:students,学生成绩表:scores,学号：studentNo)
-- 分析1-关联表：学生表，成绩表
-- 分析2-关联字段：学生表，成绩表--学号
select * from students stu  inner join scores sc on stu.studentNo = sc.studentno;

 -- 例2：查询课程信息及学生的成绩
 -- (课程信息表:courses,学生成绩表:scores,课程号：courseNo)
 -- 分析1-关联表：课程表，成绩表
 -- 分析2-关联字段：课程表，成绩表---课程号
 select * from courses cou  inner join scores sc on cou.courseNo = sc.courseNo; 

-- 例3：查询王昭君的成绩，要求显示姓名、课程号、成绩
-- (学生信息表:students,学生成绩表:scores,学号：studentNo,课程号:courseNo,成绩：score)
-- 分析1-关联表：学生表，成绩表
-- 分析2-关联字段：学生表，成绩表--学号
select * from students stu
	inner join scores sc on stu.studentNo = sc.studentno
	where stu.name  = '王昭君';

image.png

  • 左连接

  • 语法格式

image.png

  • 练习
-- 例1:查询所有学生的成绩，包括没有成绩的学生
-- (学生信息表:students,学生成绩表:scores,学号：studentNo)
-- 分析1-关联表：学生表，成绩表
-- 分析2-关联字段：学生表，成绩表--学号
-- 分析3-找出需要范围大的表：学生表
select * from students stu  left join scores sc on stu.studentNo = sc.studentno; 

-- 例2:查询所有学生的成绩，包括没有成绩的学生，需要显示课程名
-- (学生信息表:students,学生成绩表:scores,课程信息表:courses,学号：studentNo,课程号：courseNo)
-- 分析1-关联表：学生表，成绩表,课程表
-- 分析2-关联字段：学生表，成绩表---学号；课程表，成绩表---课程号
-- 分析3-找出需要范围大的表：学生表
select * from students stu
	left join scores sc on stu.studentNo = sc.studentno
	left join courses cou on cou.courseNo = sc.courseNo;

  • 右连接

  • 与左查询相反

  • 自关联

  • 使用场景：当表存在等级关系时，查询其中数据需要使用自关联

         比如：省，市，县
     比如：总经理，部门主管，组长，组员

  • 自关联思路

把一张表当成多张表来使用
   说明：将表定义为不同的别名

A表的字段与B表的字段进行关联
   强调：一定是表中不同的字段

  • 数据准备


-- 创建表:
drop table if exists areas;
create table areas(aid int primary key, atitle varchar(20),pid int);
-- 插入数据:
insert into areas values ('130000', '河北省', NULL), ('130100', '石家庄市', '130000'),
('130400', '邯郸市', '130000'), ('130600', '保定市', '130000'),('130700', '张家口市', '130000'),
('130800', '承德市', '130000'),('410000', '河南省', NULL), ('410100', '郑州市', '410000'),
('410300', '洛阳市', '410000'),('410500', '安阳市', '410000'),('410700', '新乡市', '410000'),
('410800', '焦作市', '410000'),('410101', '中原区', '410100'),('410102', '二七区', '410100'),
('410301', '洛龙区', '410300');
  • 数据介绍

image.png

  • 练习
例1：查询河南省所有的市
select * from areas a1
	inner join areas a2 on a1.aid = a2.pid
	where a1.atitle = '河南省';

例2：查询郑州市的所有的区
select * from areas a1
	inner join areas a2 on a1.aid = a2.pid
	where a1.atitle = '郑州市';

例3：查询河南省的所有的市区
select * from areas a1
	inner join areas a2 on a1.aid = a2.pid
	left join areas a3 on a2.aid = a3.pid
	where a1.atitle = '河南省';

子查询

  • 充当条件-标量子查询

  • 案例：查询王昭君的成绩，要求显示成绩（标量子查询）
步骤一：获取王昭君的学号
select studentNo from students where name = '王昭君';
-- 步骤二：通过王昭君的学号，获取她的成绩
select * from scores where studentno = (select studentNo from students where name = '王昭君');

image.png

  • 充当条件-列子查询

  • 案例：查询18岁的学生的成绩，要求显示成绩（列子查询）
-- 步骤1：获取18岁学生的学号
select studentNo from students where age = 18;
-- 步骤2：根据18岁学生的学号，获取对应的成绩
select * from scores where studentno in (select studentNo from students where age = 18);

image.png

  • 充当条件-行子查询

  • 案例：查询和王昭君同班且同龄的学生信息（行子查询）

-- 步骤一：通过王昭君，获取年龄和班级
select age,class from students where name = '王昭君';
-- 步骤二：通过获取的年龄和班级，查询数据
select * from students where (age,class) = (select age,class from students where name = '王昭君');

image.png

  • 充当充当数据源

  • 查询数据库和系统测试的课程成绩

-- 步骤1：使用内连接组成一个新表(课程表+成绩表)
select * from courses cou
	inner join scores sc on cou.courseNo = sc.courseNo;
        
-- 步骤2：
select * from (select cou.courseNo,cou.name,sc.id,sc.studentno,sc.score from courses cou
	inner join scores sc on cou.courseNo = sc.courseNo) as A
	where A.name in ('数据库','系统测试');
avatar
文章
阅读
粉丝