mysql使用教程-基础版

缺乏、安全感 2023-10-09 20:26 54阅读 0赞

mysql是一款关系型数据库管理系统，通过SQL语句来操作。SQL语句分为4种，分别是DDL、DML、DQL、DCL，下面介绍一下它们的基本使用。

## 一、DDL ##

DDL即Data Definition Language，是数据定义语言，用于操作数据之间的关系，例如数据库、表、索引、函数等等。

#### 1.1 操作数据库 ####

-- 1. 查看所有数据库
    
    show databases;
    
    -- 2. 创建数据库：
    create database demo;
    
    -- 3. 创建数据库时如果该数据库已存在就会报错，例如demo数据库存在时执行语句1就会报错  ERROR 1007 (HY000): Can't create database 'demo'; database exists 。
    --    为了避免这种情况可以在创建时判断数据库是否存在，不存在时才创建，例如：
    create database if not exists demo;
    
    -- 4. 修改数据库信息
    alter database demo character set utf8;
    
    -- 5. 删除数据库
    drop database demo;

#### 1.2 操作表 ####

-- 创建表
    create table t(
        id int,
        name varchar(255),
        age int，
        sex varchar(255)
    );
    
    -- 查看表结构
    desc t;
    
    -- 修改表名
    alter table t rename to teacher;
    
    -- 添加一列
    alter table teacher add birth date;
    
    -- 删除列
    alter table teacher drop birth;
    
    -- 删除表
    drop table teacher;
    
    -- 判断表存在时才删除
    drop table  if exists teacher;

## 二、DML ##

DML全称是Data Manipulation Language，即数据操纵语言，是对表数据增删改的操作。

#### 2.1. 添加数据 ####

-- 添加语法,字段与值按照顺序一一对应，值1对应字段1，以此类推。
    insert into 表名a(字段1,字段2) value(值1，值2);
    
    -- 只添加记录中部分字段需要写出要添加的字段，顺序可以自己控制
    insert into student(id,name) value(1,'zs');
    insert into student(name,id) value('zs',1);
    
    -- 添加全部字段则可以省略，省略时字段顺序默认按照表中字段顺序排列。
    insert into student value(1,'zs',20,'男');
    
    -- 添加一条数据，既可以用value也可以用values
    insert into student(id,name,age,sex) values(1,'zs',20,'男');
    -- 添加多条数据，只能用values，多条数据之间用英文逗号分割
    insert into student(id,name,age,sex) values(2,'ls',20,'男'),(3,'w5',18,'女');
    
    -- 注意：
    -- 1. 插入的字段与值数量不匹配就会报错：column count doesn't match value count;
    -- 2. 插入的字段与值类型不匹配就会自动进行类型转换，转换成功就会插入，转换失败就会报错。
    -- 例如name和sex字段是varchar类型，插入时值填数值类型就会自动转换成功插入，而反过来id，age字段是int类型，值填字符串的话自动转换失败就会报错
    insert into student(id,name,age,sex) values(2,3,4,5);
    
    --插入查询出的数据
    
    -- 插入部分字段
    insert into student(id,name) select id,name from teacher where id<5;
    
    -- 插入全部字段可以省略指定字段，但是省略时要注意两表字段顺序，因为插入数据匹配字段是按照顺序而不是列名
    insert into select * from teacher where id<5;
    
    -- 案例：一张功能表有name、function两个字段，现在新添加了一个用户李四，需要给他添加和张三一样的功能，注：系统有上百功能，而张三拥有其中几十项功能。
    -- 一项一项为李四添加功能太麻烦，直接查出张三所有功能为李四添加上。
    -- 1. 借助中间表，复制一张结构与function表完全相同的表叫function_copy;
    -- 1.1 把张三的信息插入到复制表
    insert into function_copy select * from function where name='张三';
    -- 1.2 把复制表里面的姓名改成李四
    update function_copy set name='李四';
    --1.3 再把复制表的数据插入到原表
    insert into function select * from function_copy;
    
    -- 2. 直接添加，查询时只查张三的功能不查其姓名，姓名直接写死李四，然后再添加
    insert into function(name,function) select '李四',function from function where name='张三';

#### 2.2 更新数据 ####

-- 更新数据,更新多个字段时，中间以英文逗号分割，where后面跟着筛选条件，只会更新符合条件的数据。如果不加条件就会更新全表数据，是一个比较危险的操作。
    update student set name='zs',age=20 where id = 1;

#### 2.3 删除数据 ####

-- 删除数据，和更新一样where后面跟筛选条件。如果不加条件就会删除整张表的数据，比较危险。
    delete from student where id = 1;
    
    -- 清空表，truncate没有where条件，不能根据条件删除某些数据，只会直接清空整张表，只保留结构，而delete是逐条删除。
    -- 例如student表只有一条id为1且自增的数据，使用delete删除之后再次insert插入id就是从2开始。而使用truncate删除之后再次insert插入id会从1开始。
    -- truncate运行速度比delete快。
    truncate table student;

## 三、DQL ##

DQL全称是Data Query Language，即数据查询语言，用于查询表中数据，也是最常用的语言。

#### 3.1 条件查询 ####

-- 查询所有数据，*代表所有字段
    select * from student;
    
    -- 查询所有的姓名和年龄
    select name,age from student;
    
    -- 查询年龄大于20的学生姓名
    select name from student where age > 20;
    
    -- 多个条件需要同时满足时用and连接，只需满足其中一个时用or连接
    -- 查询年龄大于20并且小于25的学生姓名
    select name from student where age > 20 and age < 25;
    -- 查询年龄大于20或者小于18的学生姓名
    select name from student where age > 20 or age < 18;

mysql通过操作符来过滤条件，其余操作符如下：

--  操作符        说明
    ------------------------------------ 
    --   =           等于 
    select * from student where id = 1;                           
    --   <>          不等于
    select * from student where id <> 1;   
    --   !=          不等于
    select * from student where id != 1;   
    --   <           小于
    select * from student where id < 1;   
    --   <=          小于等于
    select * from student where id <= 1;   
    --   >           大于
    select * from student where id > 1;   
    --   >=          大于等于
    select * from student where id >= 1;   
    --   between     在指定两个值之间,包前且包后。
    select * from student where id between 1 and 5;
    --   in          在指定值中进行匹配
    select * from student where id in(1,2,5);
    --   not         否定条件，即与条件相反的数据
    select * from student where not id = 1;
    
    -- 案例1：查询所有年龄在18-20之间并且不叫张三的男生信息
    select * from student where age >= 18 and age <= 20 and  sex = '男' and name != '张三'; 
    select * from student where age between 18 and 20  and  sex = '男' and name != '张三';
    select * from student where age between 18 and 20  and  sex = '男' and not name = '张三';
    
    -- 案例2：查询姓名叫张三或者李四的学生信息
    select * from student where name='张三' or name='李四';
    select * from student where name in('张三','李四');

#### 3.2 去重复 ####

当我们查询出许多重复数据，但是只需要一条时可以使用distinct关键字把重复数据去掉

-- 单字段去重复
    -- 查询学生表中有几种性别，此时查询结果只会返回不同的性别，如果表中只有30条男生的数据，那就只会返回一个“男”。
    select distinct sex from student;
    
    -- 多字段去重复
    -- 此时只有年龄性别同时重复的数据才会被去掉。
    select distinct sex,age from student;

#### 3.3 分页查询 ####

分页查询使用limit关键字来筛选查询结果，语法：limit 起始索引 偏移量，偏移量也就是要查询的行数。数据索引从0开始，起始索引为0时可省略。

-- 查询前5行数据
    select * from student limit 5;、
    select * from student limit 0,5;
    -- 查询第6-10行数据，即从第6行开始，查询6,7,8,9,10一共5行数据。
    select * from student limit 5,5;

#### 3.4 排序 ####

排序使用order by子句，升序为asc，降序为desc，默认为升序，可省略。

-- 查询所有student信息，按照年龄从小到大排列
    select * from student order by age;
    select * from student order by age asc;
    -- 查询所有student信息，按照id从小到大，id相同时年龄从大到小排列
    select * from student order by id ,age desc;
    select * from student order by id asc,age desc;

#### 3.5 空值检查 ####

-- 查看学生表中还没有录入年龄字段的学生，也就是年龄字段还没有值的，并不是年龄为0的
    select * from student where age is null;

#### 3.6 模糊查询 ####

模糊查询使用like关键字，模糊匹配的地方使用通配符。下划线通配符是固定匹配一个字符，%是匹配任意数量字符。

-- 三个姓名：张、张三、张三丰，'张_'只会匹配张三，'张%'则是三个都能匹配。通配符可以放在前后中间任意位置。
    
    -- 查询学生表中姓张的学生
    select * from student where name like '张%';
    
    -- 查询学生表中姓张且是两字姓名的学生
    select * from student where name like '张_';
    
    -- 查询学生表中名字带三的学生
    select * from student where name like '%三%';
    
    -- 查询学生表中叫张某丰的学生，例如张大丰、张二丰、张三丰、张十三丰
    select * from student where name like '张%丰';

#### 3.7 使用函数 ####

有的时候直接查出的数据不是我们相要的数据，这时就可以对查出的数据进行一系列的操作使它变为我们想要的数据。

--1. 拼接字段
    --1.1 将查询出的字段进行拼接，例如张三18
    select concat(name,age) from student;
    -- 1.2 拼接字符串，例如听说张三是好人
    select concat('听说',name,'是好人') from student;
    
    --2. 算术计算(+、-、*、/)
    select age+2 from student;
    
    --3. 使用函数
    --3.1 文本处理函数，常用的文本处理函数如下：
    --    RTrim()			函数去除右边空格
    --    LTrim()			函数去除左边空格
    --    Trim()			函数去除两边空格
    select trim(name) from student;
    --    Left()			返回串左边的字符，(串，返回的长度)
    --    Right()			返回串右边的字符，(串，返回的长度)
    select left(name,2) from student;
    --    Length()		    返回串的长度
    select length(name) from student;
    --    Lower()			将串转换为小写
    --    Upper()			将串转换为大写
    select upper(name) from student;
    
    --3.2 常用聚集函数
    --    AVG()			    返回某列的平均值
    --    COUNT()			返回某列的行数
    --    MAX()			    返回某列的最大值
    --    MIN()			    返回某列的最小值
    --    SUM()			    返回某列值的和
    select avg(age) from student;

#### 3.8 分组查询 ####

分组查询使用group by 子句，对分组后的条件过滤使用having子句。

-- 语法：select * from 表名 where 条件 group by 分组字段 having 分组筛选条件。where和having不是必须，但是where和having需要的时候语法顺序不能乱。
    
    -- 单字段分组：查询每个性别各有多少学生
    select sex,count(*) from student group by sex;
    
    -- 多字段分组：查询每个年龄的男女生各有多少
    select age,sex,count(*) from student group by age,sex;
    
    -- 分组过滤：查询年龄大于20的学生中每个年龄段的男女生大于10人的各有多少,并且按照数量降序排列。
    select age,sex,count(*) from student where age>20 group by age,sex having count(*) >10 order by count(*) desc;
    
    -- where 与 having 
    -- where是查询基础数据时的条件，根据where条件查询出基础数据
    -- having是分组过滤条件，查询到基础数据之后通过group by 对基础数据分组得到处理后的数据，再通过having条件对处理后的数据进行筛选
    
    -- 查询语句的顺序
    select * from 表 where 条件 group by 分组字段 having 分组筛选条件 order by 排序字段 limit 分页条件;

#### 3.9 子查询 ####

把从表中查询出来的数据当做条件会用到子查询

-- 查询出年龄比张三大的所有学生
    -- 1. 传统查询
    -- 1.1 查出张三的年龄,假设张三年龄20
    select age from student where name='张三';
    -- 1.2 查询比张三年龄大的学生
    select * from student where age >20;
    
    -- 2. 子查询
    select * from student where age > (select age from student where name='张三');
    
    -- 不仅在查询中会用到，更新、删除操作也会用到，例如把年龄比张三大的学生从表中删除
    delete from student where age > (select age from student where name='张三')

#### 3.10 联表查询 ####

有时候需要从多个表中查询其中有关联的数据，这时联表来查询

-- 1. 内连接
    -- 内连接是交集，查出来的是两表之中符合连接条件的数据，也就是两表的交集。
    -- 1.1 显式内连接，语法：select 字段 from 表1 inner join 表2 on 连接条件 join 表3 on 连接条件; inner可以省略。
    -- 例如，查询同一班级下的老师和学生
    select student.*,teacher.* from student inner join teacher on student.classID = teacher.classID;
    select student.*,teacher.* from student join teacher on student.classID = teacher.classID;
    -- 1.2 隐式内连接，语法：select 字段 from 表1,表2,表3 where 连接条件;
    select student.*,teacher.* from student,teacher where student.classID=teacher.classID;
    
    -- 2. 别名，在使用联表时需要采用表名.字段名来区别使用的哪个表的字段，这样一来名字会比较长，可以使用别名来缩短表名的使用，起别名使用"as"关键字，并且可以省略。
    -- 例如1中的案例
    select s.*,t.* from student as s,teacher as t where s.classID=t.classID;
    select s.*,t.* from student s,teacher t where s.classID=t.classID;
    
    -- 3. 外连接
    -- 3.1 左外连接，左外连接查询的是左表数据以及符合连接条件的右表数据，也就是左表+两表交集。
    select s.*,t.* from student s left join teacher t on s.classID=t.classID;
    
    -- 3.2 右外连接，右外连接查询的是右表数据以及符合连接条件的左表数据，也就是右表+两表交集。
    select s.*,t.* from student s right join teacher t on s.classID=t.classID;
    
    -- 用左外连接做到的也能用右外连接做到，如下两条SQL查询数据完全相同
    select s.*,t.* from student s left join teacher t on s.classID=t.classID;
    select s.*,t.* from teacher t right join student s on t.classID=s.classID;

#### 四、DCL ####

DCL全称Data Control Language，即数据控制语言，用于数据库权限方面。

#### 4.1 授权 ####

-- 语法
    grant 权限1,权限2 on 授权对象 to 用户
    
    -- 给本地账号授权student表的增删改查权限
    grant insert,delete,update,select on demo.student to t1@localost;
    
    -- 查看账号权限
    show grants for t1@localhost;

#### 4.2 撤销权限 ####

-- 语法：
    revoke 权限1,权限2 on 授权对象 from 用户
    revoke insert,delete,update,select on demo.student from t1@localost;