MySQL基础用法

1 SQL介绍（Structured Query Language，结构化查询语言）

        定义： SQL是一门特殊的语言，专门用来操作关系型数据库，当前关系型数据库都支持使用SQL语言进行操作，也就是说可以通过 SQL语言操作oracle、mysql、sql server、sqlite等等所有的关系型数据库。

        分类：DQL：数据查询语言，用于对数据进行查询，如select

                   DDL：数据定义语言，进行数据库、表的管理，如create,drop

                   DML：数据操作语言，对数据进行增删改，如insert,update,delete

                   TPL：事务处理语言，对事物进行处理，如begin transaction,commint,rollback

2 sql语言中的注释

        -- 行注释：--空格

        /*内容*/：多行注释

3 常用数据类型

        整数 int：有符号范围（-2147483648 ,2147483647），无符号int unsigned 范围（0，4294967295）

        小整数 tinyint：有符号范围（-128，,127），无符号 tinyint unsigned 范围（0,255）

        小数 decimal：如decimal（5,2）表示共有5位数，小数占2位，整数占3位

        字符串：varchar 可变长字符串，varchar（10）表示最多存10个字符，一个中文或英文都占一个，char 定长字符串

        日期时间 datetime：范围（1000-01-01 00:00:00 ~ 9999-12-31 23:59:59）

4 create创建表

        

-- 创建表b，字段要求姓名 可变长10 身高 5位数字，其中2位小数
create table b(
bname varchar(10),
height decimal(5,2)
)engine=innodb character set 'utf8';

-- 创建表c
create table c(
id int,
cname varchar(20),
age tinyint unsigned
);

5 insert表中插入数据

        添加一条数据：

                格式一：insert into 表名 values (...,...,...);  适用于对全部字段都插入

                格式二：insert into 表名 (字段名1,字段名2)  values (值1,值2); 适用于对部分字段进行插入

        添加多条数据：

                格式一：insert into 表名 values (...,...,...),(...,...,...),(...,...,...);

                格式二：insert into 表名 (字段名1,字段名2)  values (值1,值2)(值1,值2),(值1,值2);

-- 插入一条数据
insert into c values (0,'cxk',18);
insert into c (id,cname) values (1,'kk');

-- 插入多条数据
insert into c values (2,'cxk1',25),(3,'cxk2',18);
insert into c (id,cname) values (4,'kk1'),(5,'kk2');

6 select查找数据

        查询表的所有字段：select * from 表名;

        查询指定字段：select 字段名1,字段名2 from 表名;

7 update修改数据

        用法：update 表名 set 字段=值,字段=值 where 条件;

                如果没有where条件，则代表修改表中所有的对应数据

8 delete，truncate 删除数据

        用法：delete from 表名 where 条件;

                如果没有where，则表示删除表中所有数据，等同于 truncate table 表名;

        两者区别：速度上，truncate>delete；

                          使用truncate删除数据后，自增长字段恢复从1开始，使用delete还是从删除前的值开始

9 drop删除表

        drop table 表名;

         drop table if exists 表名; ：如果表a存在，则删除，不存在则什么也不做

 10 字段的约束

        常用约束：

                主键（primary key）：值不能重复，auto_increment代表自动增长

                非空（not null）：此字段的值不允许填写空值

                唯一（unique）：此字段的值不允许重复

                默认值（defalut）：当不填写此字段时，会自动使用默认值

        

11 主键与自增长

        带有primary key 的字段，值不能重复

        auto_increment为自增长

        如果不指定自增长字段的值，想要在前一个数据的基础上+1，则需要使用占位符0或者null，不然的话，由于字段数量不匹配，添加信息会报错

        在已有的表中增加主键：1) alter table 表名 add 字段名 数据类型 primary key;

                                                2) alter table 表名 modify 字段名 数据类型 primary key;

        主键的删除：alter table 表名 drop primary key;

        主键的重要性：如果有两个相同的数据，使用主键时可以方便其修改

12 字段的别名

        格式一：select 字段名 as 别名, 字段名 as 别名, 字段名 as 别名 from 表名;

        格式二：select 字段名 别名, 字段名 别名, 字段名 别名 from 表名; 字段名和别名中间加个空格就行

13 表的别名

        格式一：select 字段名, 字段名, 字段名 from 表名 as 别名;

        格式二：select 字段名, 字段名, 字段名 from 表名 别名;  表名和别名中间加个空格就行

14 distinct 过滤重复数据

        select distinct 字段名,字段名 from 表名;

15 where的多种运算符

        比较运算符、逻辑运算符、模糊查询、范围查询、空判断

16 比较运算符

        等于：=

        大于：>

        小于：<

        小于等于：<=

        大于等于：>=

        不等于：!=

17 逻辑运算符

        与 and：由两个条件，两个条件必须同时满足

        或 or：两个条件，两个条件满足任意一个即可

        非 not：一个条件，条件成立时，not之后就为不成立，条件不成立时，not之后就为成立

18 模糊查询

        where 字段名 like ‘ ’;

        %表示多个任意字符

        _表示一个任意字符

        例子：like '孙%'：孙开头的数据

                   like ‘孙_’:孙开头且长度为2的数据

19 范围查找

        在查找非连续的范围内时：where 字段名 in (‘条件1’，‘条件2’);

        在查找连续的范围时：where 字段名 between a and b;

20 空判断

        null 与 ‘’ 不同，null表示字符为空，而‘’表示字符长度为0的字符串

        空判断：where 字段名 is null;

        非空判断：where 字段名 is not null;

21 order by排序

        select * from 表名 order by 字段名1 asc|desc,字段名2 asc|desc;

        如果不写asc或者desc默认为从小到大排序，效果与asc一致，desc为降序排序

        注：排序要在where子句之后，不然无法提取数据

22 count 函数

        计算总数据的数量

        select count( [distinct] */字段名) from 表名 where 条件;

23 max 最大值

        查找数据中的最大值

         select max( [distinct] 字段名) from 表名 where 条件;

        注：max后面必须加字段名

24 min 最小值

        查找数据中的最小值

         select min( [distinct] 字段名) from 表名 where 条件;

25 sum 求和

        select sum( [distinct] 字段名) from 表名 where 条件;

26 avg 平均值

        select avg( [distinct] 字段名) from 表名 where 条件;

        使用avg时，会忽略null值，即null值不能作为分母计算平均值

27 group by 数据分组

        select 聚合函数 from 表名 where 条件 group by 字段;

        gruop by就是配合聚合函数使用的，where子句可有可无

28 having 分组聚合之后的数据筛选

        select * from 表名 group by 字段 having 条件;

        having总是出现在group by之后

        having与where 的区别：1）where先筛选符合条件的数据，然后聚合统计；having先分组聚合统计数据，在统计结果中筛选需要数据；2）where中不能出现聚合函数，having中可以出现聚合函数

29 limit 显示制定的行数

        select * from 表名 where 条件 group by 条件 orderby 条件 limit start,count; 

        查询时下标从0开始，start=1时，表示从第二条记录开始查询显示

        start省略不写时，默认从下标0开始

        limit总是在select语句的最后

30 数据分页显示

        m表示每页显示多少数据，n表示第n页

        select * from 表名 limit (n-1)*m,m;

31 连接查询

        连接查询可以通过连接运算符（连接条件）实现多个表查询

        内连接：查询的结果只显示两个表中满足连接条件的部分，即两表共有部分

        左连接：查询的结果为两表共有部分加上左表特有部分

        右连接：查询结果为两表共有部分加上右表特有部分

32 内连接

        语法一：select * from 表1 inner join 表2 on 表1.字段=表2.字段;

        语法二：select * from 表1,表2 where表1.字段=表2.字段；（隐式内连接）

        内连接的关键是找到两张表相关联的字段

        与where一起使用：select * from 表1 inner join 表2 on 表1.字段=表2.字段 where 条件;

        多表的内连接查询：select * from 表1 inner join 表2 on 表1.字段=表2.字段 inner join 表3 on 表2.字段=表3.字段;

                可以把第一次的内连接看成一个整体即：select * from (表1 inner join 表2 on 表1.字段=表2.字段) inner join 表3 on 表2.字段=表3.字段;

        与order by一起使用：select * from 表1 inner join 表2 on 表1.字段=表2.字段 where 条件 order by 字段名 asc|desc limit start,end;

33 左连接

        select * from 表1 left join 表2 on 表1.字段=表2.字段 where 条件;

34 右连接

        select * from 表1 right join 表2 on 表1.字段=表2.字段 where 条件;

35 多表联合查询，同名字段的处理方式

        select 表名.字段名 from 表1 inner join 表2 on 表1.字段=表2.字段;

36 自关联

        自关联是对同一张表做连接查询，并且保证关联字段名不同

        select * from 表 as a1 inner join 表 as a2 on a1.字段名1=a2.字段名2 where 条件;

37 子查询

        子查询与主查询的关系：1）子查询是嵌入主查询中的；2）子查询是辅助主查询的，要么充当条件，要么充当数据源；3）子查询是可以独立存在的雨具，是一条完整的select语句

        标量子查询：子查询返回结果只有一行一列的时候，称为标量子查询

        格式：select * from 表名 where age > (select avg(age) from 表名);

        列子查询：子查询返回结果只有多行一列的时候，称为列子查询

        格式：select * from 表名 where class in (select class from 表名 where 条件);

        表级子查询：子查询返回结果只有多行多列的时候，称为表级子查询

        格式：select * from (select * from 表1 where 条件) t1 inner join 表2 on t1.字段=表2.字段;

38 练习

-- 列出男职工总数和女职工总数
select sex,count(*) from employees group by sex;

-- 列出非党员职工的总数
select count(*) from employees where poliyicalsta!='党员';

-- 列出所有职工工号，姓名以及所在部门
select empid,empname,deptname from employees inner join departments on employees.deptid=departments.deptid;

-- 列出所有职工工号，姓名和工资
select e.empid,empname,salary from employees e inner join salary s on e.empid=s.empid;

-- 列出领导岗的姓名以及所在部门名称
select empname,deptname from employees e inner join departments d on e.detpid=d.detpid where leader is NULL;

-- 列出职工总人数大于4的部门号和总人数
select detpid,count(*) from employees group by detpid having count(*)>4;

-- 列出职工总人数大于4的部门号和部门名称
select e.detpid,deptname from employees e inner join departments d on e.deptid=d.detpid group by e.detpid having count(*)>4;

-- 列出开发部和测试部的职工号，姓名
select empid,empname from employees e inner join departments d on e.deptid=d.deptod where deptname in ('测试部','开发部');

-- 列出市场部所有女职工的姓名和政治面貌
select empname,politicalsta from employees e inner join departments d on e.deptid=d.deptid where deptname='市场部' and sex='女';

-- 显示所有职工姓名和工资，包括没有工资的职工姓名
select empname,salary from employees e left join salary s on e.empid=s.empid;

-- 求不姓‘孙’的所有职工工资总和
select sum(salary) from employees e inner join salary s on e.empid=s.empid where not empname like '孙%';
-- 这里采用左连接也可以，不影响

39 MySQL内置函数

        1）拼接字符串：concat(参数1,参数2,...)：参数可以为数字，也可以是字符串

        

        2）字符长度：length(str)：如果字符串编码为utf8，一个汉字长度为3

        

        3）左侧截取字符：left(str,len)：返回字符串的左端len个长度的字符，与length规则不同，中文英文字符长度都为1

        

        4）右侧截取字符：right(str,len)：返回字符串的右端len个长度的字符，与length规则不同，中文英文字符长度都为1

        5）指定位置截取字符串：substring(str,start,len)：start下标从1开始，

        

        6）去除左侧空格：ltrim(str)：去除字符串左侧的空格

        7）去除右侧空格：rtrim(str)：去除字符串右侧的空格

        8）去除两边空格：trim(str)：去除字符串两侧的空格

        9）四舍五入：round(float,位数)：位数表示保留具体的小数位

        10）随机数：rand()：生成一个0-1.0的随机浮点数，可以与order by使用，order by rand()表示随机排序

        11）当前日期：current_date()：返回当前日期

        12）当前时间：current_time()：返回当前时间

        13）当前日期与时间：now()：返回当前日期与时间

        注：内置函数可以在where，order by子句中使用

40 存储过程

        定义：存储过程procedure，也称存储程序，是一条或者多条SQL语句的集合。

        创建存储过程：

                

        当使用命令窗口时，由于 ; 表示换行，因此需要设置分割符

                

-- 例1：创建存储过程stu()，查询students表所有学生信息
create procedure stu()
begin
    select * from students;
end

-- 调用存储过程，需要加()
call stu();

-- 删除存储过程，这里不需要加()
drop procedure stu;
drop procedure if exists stu;

41 视图

        定义：视图是对查询结果的封装，不能对其中数据进行增删操作

        创建视图：create view 视图名称 as select 语句;

        删除视图：drop view 视图名称;

                          drop view if exists 视图名称;

        注：可以把视图看成一张表，不过不能对其中数据进行增删操作，可以使用内连接对其与其他表进行操作

42 事务

        定义：事务是一个操作序列，这些操作要么都执行，要么都不执行，是一个不可分割的工作单位。

                   事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

        开始事务：begin，开始事务后执行修改update或删除delete记录语句，变更会写到缓存中，而不会立刻生效

        回滚事务：rollback，放弃修改

        提交事务：commit，将修改的数据写入实际的表中

-- 同时删除students和scores表中studentNo为001学生的记录
-- 回滚事务，放弃删除
begin;
delete from students where studentNo='001';
delete from scores where studentNo='001';
rollback;

        如果开始一个事物，但是执行中出现异常，最终没有出现rollback或者commit，退出时，自动会执行rollback

43 索引

        作用：加速select查询的速度

        创建索引：create index 索引名称 on 表名(字段名(长度))；

                如果创建索引的字段是字符串，需要指定长度，建议长度与创建表时的长度一致。

        查看索引：show index from 表名;

        删除索引：drop index 索引名称 on 表名;

        索引的优点：大大提高了select查询的速度

        索引的缺点：降低更新表的速度，如进行insert，update和delete操作时，不仅要保存数据，还要保存所有文件

        索引的必要性：1）实际应用中，执行select次数远远大于insert等语句，因此建立索引的必要的。2）在大量数据插入时，可以先删除索引，在批量插入数据，在添加索引，这样可以提高数据插入效率。

44 使用cmd进入mysql

        如果mysql.exe没有添加到环境变量中的话，则需要先将路径切换到mysql/bin文件所在路径

        指令：mysql -h [主机名] -u [用户名] -p

                参数说明：-h [主机名]，表示连接mysql地址，如果省略则默认为本地连接

                                  -u [用户名]，表示连接用户名

                                  -p，执行之后会提示输入密码

        

45 cmd下常用的指令

        1）查看数据库：show databases;

        2）只用制定数据库：use 数据库名;

        3）查看数据库中的表：show tables;

        4）命令行默认字符与数据库不同，需要修改显示方式：set names gbk;

        5）查看表结构：a. desc 表名；   b. show create table 表名;

        6）创建数据库：create database [ if not exists ] 数据库名 character set utf8; character set utf8表示字符集为utf8

        7）删除数据库：drop database 数据库名;

        8）修改数据库字符集：alter database 数据库名 character set 'utf8';

46 检查约束

        格式一：字段名 enum('','','')，单选

        格式二：字段名 set('','','')，多选

47 外键约束

        作用：从表中用来和主表建立关联关系的字段

        格式：

create table a(
字段1 数据类型,
字段2 数据类型,
foregin key (字段1) references 主表名 (字段1),
);

        增加外键：alter table 表名1 add foregin key (字段名) references 表名2 (字段名);

        外键关联数据变更操作指令设置：

        

                删除更新操作种类：cascade(级联),no action(不能改),restrict(不能改),set null(设置为空)

                任何设置：alter table 表名1 add foregin key (字段名) references 表名2 (字段名) on delete (cascade/no action/restrict/set null) on update (cascade/no action/restrict/set null);

        删除外键：alter table 表名 drop foregin key 外键名;

48 修改表中数据类型

         alter table 表名 modify 字段名 数据类型;

49 修改表中数据的字段名

        alter table 表名 change 旧字段名 新字段名 数据类型;

50. 例题

        商品表order，字段：oi表示订单编号，catei表示商品种类，oa表示订单金额，od表示订单日期

        顾客表customer，字段：custi表示顾客编号，oi表示订单编号，cg表示顾客性别

        现要求：根据上述表格，查询每个商品类别在2024-01-01到2024-06-30间的销售总额以及购买该商品的男性和女性顾客的数量

select 
    o.catei,
    sum(o.oa),
    count(distinct case when c.cg='男' then c.custi end),
    count(distinct case when c.cg='女' then c.custi end) 
from
    order o
inner join
    customer c on o.oi=c.oi
where
    o.od>='2024-01-01' and o.od<='2024-06-30'
group by
    o.catei