MySQL学习之基础语法详解

墨蓝 2024-04-08 09:35 75阅读 0赞

这篇记录一下MySQL的基础语法

##### DDL数据定义语言 #####

> 主要是针对数据库，表，列的操作，即创建，查询，修改，删除

**查询所有数据库**

show databases;

**创建数据库**

create database 库名;

**修改数据库**

-- 创建数据库时,默认字符集是utf8.
    -- 修改命令只能修改字符集,不能修改数据库名.
    alter database ....

**删除数据库**

drop database 库名;

**选择或者进入数据库**

use 库名;

**查询当前所在库**

select database();

**查询当前库下的所有表**

show tables;

**创建表**

create table 表名(
    字段1 数据类型(长度),
    字段2 数据类型(长度),
    ...
    );

**常用数据类型**  
![在这里插入图片描述][20f86424a6124763a022ac716b5a2d33.png]  
注意：varchar是变长字符串，char是定长字符串

--------------------

**修改表名**

rename table 旧名 to 新名;

**修改列名**

alter table 表名 change 旧名 新名 数据类型(长度)

**查看表结构**

desc 表名;

**增加新列**

alter table 表名 add 新列名 数据类型(长度);

**删除一列**

alter table 表名 drop 列名;

**删除表**

drop table 表名;

##### DML #####

> 数据操作语言,针对表中数据进行操作,主要是对表数据进行增,删,改。

**1、 新增insert**

insert into 表名(字段1,字段2,...) values (值1,值2,...);

*  当要插入全部列时,表名后的列名可以省略 此时要注意,值的顺序要与原表的顺序一致
 *  可以指定部分列,插入数据 列和值的顺序,数量,数据类型要一一对应
 *  字符串使用单引号 日期,暂时也是使用单引号括起
 *  可以支持同时插入多条数据values后用()分别写多条数据,用逗号隔开

insert into stu (id,name,age,sex,birthday) 
    values (1,'张三丰',130,'男','1890-01-01');
    -- 省略
    insert into stu values(2,'张无忌',18,'男','1890-01-01');
    -- 插入部分数据
    insert into stu (id,name) values (3,'赵敏');
    insert into stu (id,name) values (4,6); -- 可以,因为mysql支持类型转换,单不建议
    insert into stu (id,name) values ('5',6);-- 可以,因为mysql支持类型转换,单不建议
    -- 日期类型,支持yyyy-MM-dd或yyyy/MM/dd
    insert into stu values(7,'武器大师',18,'男','1990/01/01')
    -- 插入多条数据
    insert into stu values 
    (8,'狗头',18,'公','1990/01/01'),
    (9,'雷克顿',19,'公','1991/01/01'),
    (10,'阿兹尔',20,'男','1981/01/01');

**2、 修改update**

update 表名 set 字段1=值1[,字段2=值2,...] [where 字段 = 值];

注意：修改语句必须要加where条件，否则将修改全部的数据。

-- 标准
    update stu set age = 25,name = '阿花' where id = 2;

**3、 删除delete**

delete from 表名 [where 字段 = 值];

注意：删除语句必须要加where条件，否则将删除全部的数据。

delete from stu where id = 10;

###### 约束的定义 ######

约束：对数据表中的数据进行限制的一种设置。  
约束的统一写法，是在建立数据表时，直接对字段的后边添加一个约束。

create table 表名(
     字段1 数据类型(长度) 约束,
     字段2 数据类型(长度) 约束,
      ....    
    );

**主键约束**

> 一般是数据表的id字段，用于标识当前数据的唯一性，每一个表至少需要一个主键。  
> 主键约束的作用:
> 
>  *  不能重复
>  *  不能为空

在建表时设置主键，int类型的主键一般会设置自增（auto\_increment）

create table emp(
    		id int(5) auto_increment primary key,
    		name varchar(20),
    		hiredate date,
    		sal double(10,2)
     );

联合主键，即多个列组合成一个主键

create table emp2(
    		id int(5),
    		name varchar(20),
    		hiredate date,
    		sal double(10,2),
            primary key(id,name)   -- 设计id和name为联合主键
      	);

需要注意的是，联合主键只有两个字段的值都一样的时候，才会不允许添加数据。  
**自增约束**  
自增约束一般配合主键一起使用的,即主键自增。  
语法：在需要自增约束的字段数据类型后加上 auto\_increment  
**非空约束**  
限制列值不能为空  
关键字是：not null

create table emp4(
    		id int primary key auto_increment, -- 主键自增
    		name varchar(20) not null, -- 不为空
    		hiredate date,
    		sal double(10,2)
      	);
      	-- 限制name列插入值时,不能为空

**默认约束**  
当给该列设置值时，如果没有默认值，就会用设置的默认约束进行填充。

create table emp5(
    		id int primary key auto_increment, -- 主键自增
    		name varchar(20) not null, -- 不为空
    		hiredate date,
    		sal double(10,2) default 3000.0  -- 默认值
      	);
      	-- 没有值时就会使用默认值填充,有值是不用默认值

**唯一约束**  
唯一约束,限制该列的值不能重复,但是可以为空

create table emp6(
    		id int primary key auto_increment, -- 主键自增
    		name varchar(20) unique, -- 唯一
    		hiredate date,
    		sal double(10,2) default 3000.0  -- 默认值
      	);
      	-- 该值不能重复
      	-- 但是可以给null,且允许多个null
    insert into emp6 (name) values('安琪拉');
    insert into emp6 (name) values(null);

**外键约束**  
外键约束又叫引用约束,外键约束是是要在表和表之间的约束。用来维护表与表之间的实体完整性。

-- 学生表
    CREATE TABLE `stu3` (
      `sid` int(11) primary key,
      `sname` varchar(20) DEFAULT NULL,
      `tid` int(11) DEFAULT NULL -- 关联老师id
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    -- 老师表
    CREATE TABLE `teacher` (
      `tid` int(11) primary key,
      `tname` varchar(20) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

> 外键是一个表中的字段属性值引用来自于另一个字段的属性值，当设置了外键，那么引用外键的表就必须根据被引用表的现存数据进行新增。

在建表时直接创建外键

-- 学生表
    CREATE TABLE `stu3` (
      `sid` int(11) DEFAULT NULL,
      `sname` varchar(20) DEFAULT NULL,
      `tid` int(11) DEFAULT NULL, -- 关联老师id
       constraint fk_stu_tea foreign key(tid) references teacher(tid)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

可以使用alter命令修改表，添加外键

alter table stu3 add constraint fk_stu_tea foreign key(tid) references teacher(tid);

关于外键的四个删除时的操作

删除时/更新时:
    RESTRICT：如果想要删除父表的记录时，而在子表中有关联该父表的记录，则不允许删除父表中的记录；
    NO ACTION：同 RESTRICT，也是首先先检查外键；
    CASCADE：父表delete、update的时候，子表会delete、update掉关联记录；
    SET NULL：父表delete、update的时候，子表会将关联记录的外键字段所在列设为null，所以注意在设计子表时外键不能设为not null；

##### DQL #####

查询语言

> 查询就是从数据库查询出结果,查询数据不会对原数据表数有任何影响。

语法：

SELECT selection_list      /*要查询的列名称*/
      FROM table_list         /*要查询的表名称*/
      WHERE condition       /*行条件*/
      GROUP BY grouping_columns  /*对结果分组*/
      HAVING condition      /*分组后的行条件*/
      ORDER BY sorting_columns /*对结果分组*/
      LIMIT offset_start, row_count  /*结果限定*/

执行顺序：  
![在这里插入图片描述][9173eea1175a46b3aa44b6b1b439e9d3.png]  
**基本查询**

-- select 字段1,字段2,.. from 表名
    -- 查询所有列
    select sid,sname,age,sex,score,cid,groupLeaderId
    from stu;
    -- 查询的结果是一张虚拟表
    -- 查询所有列,一般在测试时,可以使用*代表所有
    select * from stu;
    -- 查询指定 列
    -- 只查询学号,姓名,性别
    select sid,sname,sex from stu;

**条件查询**

-- 条件查询,就将查询结果过滤一部分,只保留满足条件的数据
        -- select 字段1,字段2,.. from 表名 where 条件
        /*
        条件: 字段 运算 值; 例如 id = 1;
        	=、!=(不等)、<>(不等)、<、<=、>、>=；
        	BETWEEN…AND；
        	IN(set)；
        	IS NULL(判断是否为空:)；IS NOT NULL(判断是否不会空)  
        	AND；
        	OR；
        	NOT；
        */
    
        -- 查询学号为1001的学生信息
        select * from stu where sid = 1001;
        -- 查询学生成绩大于60的学生id 姓名,成绩
        select sid,sname,score from stu where score > 60;
        -- 查询学生性别为女，并且年龄小于50的记录
        select * from stu where sex = '女' and age < 50;
        -- 查询学生学号为1001，或者姓名为李四的记录
        select * from stu where sid = 1001 or sname = '李四';
        -- 查询学号为1001，1002，1004的记录
        select * from stu where sid in (1001,1002,1003);
        select * from stu where sid = 1001 or sid = 1002 or sid = 1003;
        -- 查询学号不是1001,1002,1003的记录
        select * from stu where sid not in (1001,1002,1003);
        select * from stu where sid != 1001 and sid != 1002 and sid != 1003;
        
        -- 查询学生年龄在20到40之间的学生记录
        -- between..and是闭区间,即包含两端数据
        select * from stu where age between 20 and 40;
        select * from stu where age >= 20 and age <=40;
        
        -- 查询性别非男的学生记录
        select * from stu where sex != '男';
        select * from stu where sex <> '男';
        -- 查询学生性别为空的数据
        -- null不能用等号判断
        select * from stu where sex = null; -- 不对!!
        select * from stu where sex is null; 
        select * from stu where sex is not  null; --注意是is not!!不是not is!!!

**模糊查询**

-- 模糊查询也属于条件查询
        -- 使用的关键词是like
        -- 语法: where 字段 like %关键词%
        --      where 字段 like _关键词-
        /**
        模糊查询
        	%: 匹配任意个数的任意字符
        	_: 匹配一个任意字符
        */
        -- 查询姓名以“张”开头的学生记录
        select * from stu where sname like '张%'
        select * from stu where sname like '张_'
        -- 查询姓名中包含“三”的学生记录
        select * from stu where sname like '%三%'

**聚合函数**

对查询的结果进行计算的一种函数,聚合函数是将多行数据计算返回一个结果的函数.  
即,有聚合函数的sql,查询返回的结果只有一条记录。

- COUNT(字段)：统计指定列不为NULL的记录行数；
    - MAX(字段)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
    - MIN(字段)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
    - SUM(字段)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
    - AVG(字段)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
    
        -- 查询stu表中记录数：
        select count(sid) from stu;
        select count(*) from stu;
        select count(sname) from stu;
        -- 查询stu表中有成绩的人数：(不为null的人数)
        select count(score) from stu;
        -- 查询stu表中成绩大于60的人数：
        select count(sid) from stu where score > 60;
        -- 查询所有学生成绩和：
        select sum(score) from stu;
        select sum(sname) from stu;
        -- 统计所有学生平均成绩
        -- avg不统计null数据
        select avg(score) from stu;
        -- 后续会学习ifnull函数,再来理解
        select avg(ifnull(score,0)) from stu;
        
        -- 统计所有人的平均成绩,包括null
        -- !!!!可以在select后的字段上做运算!!!!
        select sum(score)/count(sid) from stu;
        update stu set score = score +100 where sid = 1001
        -- 查询最高成绩和最低成绩：
        select max(score),min(score) from stu;
    
    	可以给列取别名.
    	
    	select max(score)  as '最高分',min(score) '最低分' from stu;
    	
    	as可以省略

**去重**

对查询返回的结果去重

-- 去除该列重复的值,但是一般都会配合count函数一起使用
    select distinct age from stu;
    -- 查询年龄不重复的共有多少人
    select count(distinct age) from stu;

**分组查询**

/*分组查询 
        语法: group by 字段 
        	根据该字段分组,字段相同的分一组
        一般用法: 分组后一般都会进行聚合操作
        特点:
        	1) 凡是分组查询,分为几组,结果只有几行
        	2) 聚合函数是对每组内的数据进行聚合
        	3) 出现在groupby后的字段,才可以出现在select后
        	   凡是和聚合函数出现在一起的字段,必须出现在group by后
        ----------------------------------------------------
        分组之后再过滤使用having
        where和having
        1) where是分组前过滤,having后过滤
        2) where后不能使用,having后是可以使用聚合函数
        */
        -- 查询男生多少人,女生多少人
        select sid,sex from stu group by sex;
        -- 查询每个班级的班级编号和每个班级的成绩和：
        select cid,sum(score) from stu group by cid;
        -- 查询每个班级的班级编号以及每个班级的人数：
        select cid,count(sid) from stu group by cid;
        -- 查询成绩总和大于200的班级编号以及成绩和：
        select cid,sum(score) from stu 
        group by cid having sum(score) > 200;

**排序**

/*排序查询
        语法:
        	order by 字段 [asc|desc];
        */
        -- 查询所有学生记录，按年龄升序排序
        select * from stu order by age asc;
        select * from stu order by age; -- 默认即升序
        	查询所有学生记录，按年龄降序排序
        select * from stu order by age desc;
        -- 查询所有学生记录，按年龄升序排序,如果年龄相同时，按编号降序排序
        select * from stu order by age asc,sid desc;
        -- 查询成绩大于60的学生id,姓名,成绩,并根据成绩降序
        select sid,sname,score from stu  where score > 60 order by score desc;

**限制limit**  
limit m,n;  
限制查询显示的条数，m表示从第m+1条开始，n表示取出n条数据。

-- 获取第6-10条的所有学生的信息
    select * from student limit 5,5;

**合并结果集(联合查询)**

合并结果集就是将多个查询结果,纵向拼接在一起。

语法

select * from tb_a
        union
        select * from tb_b
        --================================
        select id,name,age from tb_a 
        union all
        select birthday,name,age from tb_b
        /*
        union 会将完全一致的数据去重
        union all 会将所有数据保留
        合并的两表的列数,数据类型要一致!!
        ps: 一般用在将一个大表拆成两个表是,这两张表的拼接使用
        */

**连接查询**

连接查询是将多个表的查询结果,横向拼接后展现

**内连接**

/*语法:
        select * from tb1 inner join tb2 on tb1.字段 = tb2.字段; 
        */
        -- 查询学生信息以及对应的班级信息
        -- 一定要加关联条件,否则会产生"笛卡尔积"
        select * from stu inner join class on stu.cid = class.cid
        -- 内连接只会保留完全符合关联条件的数据
        
        -- 内连接的简化写法
        select * from stu s,class c where s.cid = c.cid
        -- 查询学生编号为1007的学生名称、学生成绩、班级名称、班级地址
        select s.sname,s.score,c.cname,c.caddress from stu s,class c where s.cid = c.cid and s.sid = 1007

内连接只会保留完全符合关联条件的数据

**外连接**

外连接分左外连接和右外连接.

外连接会保留不符合关联条件的数据,左外保留左表中不符合条件的数据,右外保留右表中不符合条件的数据,

/*
        语法: select * from tb1 left outer join tb2 on tb1.字段 = tb2.字段
        */
        -- 查询学生信息,如果有班级信息就查询班级信息
        select * from stu left outer join class on stu.cid = class.cid;
        select * from stu left join class on stu.cid = class.cid;
        select * from stu right outer join class on stu.cid = class.cid;
        -- 可以省略outer

**子查询(subquery)**

子查询其实就是嵌套查询

子查询嵌套,

*  可以嵌套在where后面当条件,
 *  可以嵌套在from后面当表

-- 查询与张三同一个班级的学生。(子查询当条件)
        -- 1) 先找到张三所在班级
        select cid from stu where sname = '张三'
        -- 2) 找到1班所有人
        select * from stu where cid = 1
        -- 改造成:子查询
        select * from stu where cid = 
        (select cid from stu where sname = '张三')
        
        -- =======================================================================
        
        /*
        子查询当条件时需要注意返回的结果的个数
        如果是一个返回值,条件可以使用=,>,<,等等
        如果是多个返回值,条件用in
        */
        select * from stu where cid in 
        (select cid from stu where age = 45)
        
        -- =======================================================================
        
        -- 成绩高于3号班级所有人的学生信息
        -- 1) 查询3班最高分
        select max(score) from stu where cid = 3;
        -- 2) 大于最高分
        select * from stu where score > 96;
        -- 改造
        select * from stu where score > 
        (select max(score) from stu where cid = 3)
        
        -- =======================================================================
        
        -- 有2个以上直接组员的学生信息
        -- 2个以上组员的组长编号,组内人数
        select groupLeaderId,count(sid) from stu group by groupLeaderId having count(sid) > 2
        -- 查询组长信息
        select * from stu where sid in (1007,1010)
        -- 改造
        select * from stu where sid in 
        (select groupLeaderId from stu group by groupLeaderId having count(sid) > 2)
        
        -- =======================================================================
        
        -- 变式写法:子查询当表
        select * from stu s,(select groupLeaderId,count(sid) from stu group by groupLeaderId having count(sid) > 2) z 
        where s.sid= z.groupLeaderId
        -- 子查询当表
        select s.sname,s.score,a.avg,(s.score - a.avg) as '差值' 
        from student s right join
        (select cid,avg(score) as avg from student group by cid) as a on a.cid = s.cid;
        -- =======================================================================
        
        -- 求1008学生编号、姓名、组长编号和组长姓名
        select s.sid,s.sname,s.groupLeaderId,z.sname from stu s,stu z where s.groupLeaderId = z.sid and s.sid =1008
        
        -- =======================================================================
        
        -- 查询每个学生成绩大于等于60且成绩总和大于200的班级编号以及成绩和并根据成绩和升序
        select sum(score),cid from stu group by cid having min(score) >=60 and sum(score) > 200 order by sum(score);
        
        -- =======================================================================
        
        -- 自连接
        -- 查询学生信息以及对应的组长信息
        select * from stu s,stu z where s.groupLeaderId = z.sid

[20f86424a6124763a022ac716b5a2d33.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/00e6805f88074693b6b48ee638701a14.png
[9173eea1175a46b3aa44b6b1b439e9d3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/b940faa85fd94ee981f6eac76b77c860.png