mysql学习笔记_mysql show database

MySQL

是一个开源的关系型数据库管理系统。

MYSQL的目录结构

1、bin目录：用于存储一些可执行文件，如mysql.exe等。

2、include目录：用于存储包含的一些头文件，如mysql.h等。

3、lib目录：用于存储一些库文件。

4、share目录：用于存储错误信息、字符集文件等。

5、data目录：用于放置一些日志文件以及数据库。

6、my.ini文件：数据库的配置文件。

命令行启动mysql

net start mysql80→启动服务

net stop mysql80→停止服务

MySql的登录：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-462DDV5f-1686673586850)(mysql学习笔记.assets/image-20230425170032096.png)]

MySql的退出：

exit

quit

\q

MySql的常用命令

1、mysqladmin命令用于修改用户密码

命令格式：mysqladmin -u用户名 -p旧密码 password新密码

2、show databases命令用于显示所有数据库

命令格式：show databases;

3、use命令使用数据库

命令格式：use <数据库名>;

4、select命令用于显示当前连接（选择）的信息

显示当前连接的数据库：select database();

显示当前服务器版本：select version();

显示当前日期时间：select now();

显示当前用户：select user();

创建数据库：在系统磁盘上划分一块区域用于数据的存储和管理。

create database [if not exists] db_name; //创建数据库

[default] character set[=] charset_name; //指定默认字符集

修改数据库

alter database db_name; //修改数据库

[default] character set[=] charset_name; //指定默认字符集

删除数据库

drop database [if not exists] db_name; //删除数据库

MYSQL的数据结构

数据类型：指数据列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。

整型：

                        tinyint                 1字节
                        
                        smallint               2字节

                        mediumint               3字节

                        int                      4字节

                        bigint                   8字节
1
2
3
4
5
6
7
8
9

浮点数类型：

                      float[(m,d)]                   4字节
                      
                     double[(m,d)]                   8字节
1
2
3

定点数类型： decimal[(m,d)]

m是精度（=整数位数+小数位数），d是标度（小数点后的位数）

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值

日期时间型： 类型 字节数 表示形式

              year                      1             yyyy

              time                      3             hh:mm:ss

             date                       4            yyyy-mm-dd

            datetime                    8        yyyy-mm-dd hh:mm:ss

           timestamp                    4        yyyy-mm-dd hh:mm:ss
1
2
3
4
5
6
7
8
9

字符型： 类型 存储需求

                     char(m)                             定长型，m个字节

                     varchar(m)                        变长型，l+1个字节

                     tinytext                              0~255

                     text                                  0~65535

                     mediumtext                          0~2的24次方-1

                     longtext                             0~2的32方-1
enum(‘value1’,’value2’,…)  取决于枚举值的个数（最多65,535个值）选单个值       

                      例：性别 enum(‘男’,’女’)
 set(‘value1’,’value2’,…)     取决于set成员的数目（最多64个成员）选多个值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

​

数据库表结构的操作

创建数据库表

create table <表名>

(

列名1 数据类型[列级别约束条件][默认值],

列名2 数据类型[列级别约束条件][默认值],

……

[表级别约束条件]

);

复制数据库表

create table 表1 as select * from 表2；

查看数据库表

show tables [from db_name];

查看数据表基本结构

1．show columns from tbl_name;

2．describe <表名>;/desc <表名>;

查看表详细结构语句，可以用来显示数据表的创建语句

show create table tbl_name;

①添加列

alter table <表名>

add <新列名><数据类型>

[约束条件][first|after已存在列名];

②修改列名

alter table <表名>

          change <旧列名><新列名><新数据类型>;
1

③修改列的数据类型

alter table <表名> modify <列名><数据类型>;

alter table <表名> change <旧列名><新列名><数据类型>;

④修改列的排列位置

alter table <表名>

          modify <列1> <数据类型> first|after <列2>;
1

⑤删除列

alter table <表名> drop <列名>；

⑥修改表名

alter table <旧表名> rename [to] <新表名>;

删除数据库表

使用drop table可以一次删除一个或多个没有被其他表关联的数据表。

drop table [if exists] 表1,表2,…表n;

表分区：就是把一张表的数据分成多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上，但所有的数据还在一个表中。

show plugins; //判定数据库版本是否支持表分区

创建表分区

在创建表的时候使用：partition by 类型 (字段)

range分区：

根据指定某列的范围值进行分区。

使用values less than操作符定义分区。

例： create table bookinfo( //表定义

            book_id int,

            book_name varchar(20)

         )

         partition by range(book_id)(        //定义分区类型和字段

            partition p1 values less than (20109999),        //分区的定义

            partition p2 values less than (20159999),

            partition p3 values less than MAXVALUE

         );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

MYSQL约束

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的完整性、唯一性。

约束类型 非空约束 主键约束 唯一约束 默认约束 外键约束

关键字 not null primary key unique default foreign key

非空约束：指字段的值不能为空。对于使用了非空约束的字段如果用户在添加数据时，没有指定值，数据库系统会报错。

语法规则：列名 数据类型 not null;

null：字段值可以为空。

not null：字段值禁止为空。

主键约束：要求主键列的数据唯一，并且不允许为空，主键能够唯一的标识表中的一条记录。

1、单字段主键

①在定义列的同时指定主键

列名 数据类型 primary key;

②在列定义的后面指定主键

[constraint <约束名>] primary key (列名);

2、多字段联合主键（或者叫复合主键）

主键有多个字段（列）联合组成。

primary key(字段1,字段2,…字段n);

修改表时添加主键约束

① alter table <表名> modify <列名> <数据类型> primary key;

② alter table <表名> add primary key (列名);

③ alter table <表名> add constraint <约束名> primary key (列名);

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

唯一约束：要求该列唯一，允许为空，唯一约束可以确保列或者几列不出现重复指。

修改表时添加唯一约束

① alter table <表名> modify <列名> <数据类型> unique;

② alter table <表名> add unique(列名);

③ alter table <表名> add constraint <约束名> unique(列名);

删除唯一约束

① alter table <表名> drop index <约束名>;

② alter table <表名> drop key <约束名>;

默认约束：指定某列的默认值。

语法规则：列名 数据类型 default 默认值;

修改表时添加唯一约束

① alter table <表名> modify <列名> <数据类型> default 默认值;

② alter table <表名> alter column <列名> set default 默认值;

删除默认约束

① alter table <表名> modify <列名> <数据类型>;

② alter table <表名> alter column <列名> drop default;

外键约束

外键：用来在两个表的数据之间建立链接，它可以使一列或者多列。

外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。

语法格式： [constraint <外键约束名>] foreign key(列名)

                      references <主表名>(主键);
1

修改表时添加外键约束

alter table <表名> add foreign key(列名) references <主表名>(主键);

删除外键约束

alter table <表名> drop foreign key <约束名>;

外键约束的参照操作

cascade：从父表删除或更新且自动删除或更新子表中匹配的行

实现级联删除

[constraint <外键约束名>] foreign key(列名)

references <主表名>(主键) on delete cascade;

图形化管理工具

MySql workbench是一款专门为用户提供了用于创建、修改、执行和优化SQL的可视化工具，通过它开发人员可以很轻松的管理数据库数据。

SQLyog是一个易于使用的、快速而简洁的图形化管理MYSQL数据库的工具，它能够在任何地点有效的管理数据库。

数据库表记录的操作

① 为表的所有列插入数据

insert into 表名(数据列) values(数据);

② 插入多条记录

insert into 表名(数据列) values(数据1),(数据2),…,(数据n);

③ 将查询结果插入到表中

insert into 表名(数据列) select (数据列) from 表名 where 条件;

设置表的属性自动增加

语法格式：列名 数据类型 auto_increment

注：auto_increment约束的字段可以是任何整数类型。

建表时可用” auto_increment=n”选项来指定一个自增的初始值。

为已有的表添加自增列

alter table 表名 modify 列名 数据类型 auto_increment;

修改自增列的起始值

alter table 表名 auto_increment = x;（修改后auto_increment列起始值从x开始）

去掉自增列

alter table biaoming modify 列名 数据类型;

单表数据记录的更新

语法格式：update 表名 set 列名1=数据值1,…,列名n=数据值n where(条件);

单表记录的删除

① delete from 表名[where <条件>];

② truncate table 表名;

truncate将直接删除原来的表，并重新创建一个表。

查询结果不重复

select distinct 列名 from 表名;

使用distinct关键字指示MySQL消除重复的记录值。

查询空值

select * from 表名 where 列名 is null;

可以查询某列内容为空的记录。

分组查询

[group by 列名][having <条件表达式>];

group by通常和聚合函数一起使用。

having限定显示记录所需满足的条件，只有满足条件的分组才会被显示。

对查询结果排序

order by 列名 [asc|desc];

用limit限制查询结果的数量

limit关键字可以返回指定位置的记录

语法格式：limit [位置偏移量,]行数;

说明：第一条记录的位置偏移量为0，第二条为1，… 以此类推。

运算符与函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aXB2VhnR-1686673586851)(mysql学习笔记.assets/image-20230425170800987.png)]逻辑运算符

and 逻辑与

or 逻辑或

not 逻辑非

数值函数：主要用来处理数值数据的。

① 获取整数的函数

    ceil(x)：返回大于x的最小整数值。

    floor(x)：返回小于x的最大整数值。
1
2
3

② 四舍五入的函数

    round(x)：返回最接近于参数x的整数，对参数x进行四舍五入。

    round(x,y)：返回最接近于参数x的数，其值保留到小数点后面的y位，若y为负值，则将保留x值到小数点左边y位。
1
2
3

③ 截断函数

    truncate(x,y)：返回被舍去至小数点后y位的数字x。若y的值为0，则结果为整数，若y的值为负数，则截去x小数点左起第y位开始后面所有低位的值。
1

④ 取模（求余）

    mod(x,y)：返回x被y除后的余数
1

⑤ 随机函数

    rand()：随机产生0~1之间的浮点值。
1

字符函数：主要用来处理数据库中的字符串数据。

① 字符串连接函数

    concat(s1,s2,…)：返回结果为连接参数产生的字符串，如果任何一个参数为null，则返回值为null。

    concat_ws(x,s1,s2,…)：第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间，分隔符可以是一个字符串，也可以是其他参数，如果分隔符为null，则结果为null。
1
2
3

② 字母转换大小写函数

    lower(str)：可以将字符串str中的字母字符全部转换成小写字母。

    upper(str)：可以将字符串str中的字母字符全部转换成大写字母。
1
2
3

③ 求字符串长度的函数

    length(str)：返回值为字符串的字节长度。
1

④ 删除空格的函数

    ltrim(s)：返回字符串s，字符左侧空格字符被删除。

    rtrim(s)：返回字符串s，字符右侧空格字符被删除。

    trim(s)：删除字符串两侧的空格。
1
2
3
4
5

⑤ 截取字符串

    substring(s,n,len)：带有len参数的格式，从字符串s返回一个长度同len字符相同的子字符串，起始于位置n。n如果是负数，则子字符串的位置起始于字符串结尾的n个字符。
1

⑥ 获取指定长度的字符串函数

    left(s,n)：返回字符串s开始的最左边n个字符。

    right(s,n)：返回字符串中最右边n个字符。
1
2
3

⑦ 替换函数

    replace(str,from_str,to_str)：在字符串str中所有出现的字符串from_str均被to_str替换，然后返回这个字符串。
1

⑧ 格式化函数

    format(x,n)：将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。若n为0，则返回结果不含小数部分。
1

日期时间函数：主要用来处理日期和时间值

① 获取当前日期的函数

    curdate()和current_date()：将当前日期按照’YYYY-MM-DD’或YYYYMMDD格式的值返回，具体格式根据函数在字符串或是数字语境中而定。
1

② 获取当前时间的函数

    curtime()和current_time()：将当前时间以’HH:MM:SS’或HHMMSS的格式返回，具体格式根据函数在字符串或是数字语境中而定。
1

③ 获取当前日期和时间

    now()和sysdate()：均返回当前的日期时间值。格式为’YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS。具体格式根据函数在字符串或是数字语境中而定。
1

④ 执行日期的加运算

    date_add(date,interval expr type)：date是一个datetime或date值，用来指定起始时间。expr是一个表达式，用来指定从起始日期添加或减去的时间间隔值。type为关键字，它指示了表达式被解释的方式，如：year,month,day,week,hour等。
1

⑤ 计算两个日期之间的间隔天数

    datediff(date1,date2)：返回起始时间date1和结束时间date2之间的天数。
1

⑥ 日期格式化

    date_format(date,format)：根据format指定的格式显示date值。
1

时间日期格式： %b：月份的缩写名称（jan…dec）

                       %c：月份，数字形式（0…12）

                       %m：月份，数字形式（00…12）

                       %M：月份名称（january…december）

                       %d：该月日期，数字形式（00…31）

                       %e：该月日期，数字形式（0…31）

                       %Y：4位数形式表示年份

                       %y：2位数形式表示年份
1
2
3
4
5
6
7
8
9
10
11
12
13

聚合函数：不返回实际表中的数据，只对获取的数据进行分析和总结。

          名称                    描述

          avg()                    返回某列的平均值

          count()                返回某列的行数

          max()                   返回某列的最大值

          min()                    返回某列的最小值

          sum()                   返回某列的和
1
2
3
4
5
6
7
8
9
10
11

系统信息函数

1、version()：返回当前MySQL服务器版本的版本号。

2、connection_id()：返回MySQL服务器当前连接的次数，每个连接都有各自唯一的ID。

3、database()和schema()：返回当前的数据库名。

4、user()：获取用户名的函数，返回当前登录的用户名称。

加密函数：主要用来对数据进行加密和界面处理，以保证某些重要数据不被别人获取。

1、信息摘要算法

md5(str)：可以加密字符串，加密后的值以32位十六进制数字的二进制字符串形式返回，若参数为null，则返回null。

2、密码算法

password(str)：从原明文密码str计算并返回加密后的密码字符串，当参数为null，返回null。

子查询

子查询是指嵌套在其他SQL语句内的查询语句。

子查询指嵌套在查询内部，且必须始终出现在圆括号内。

子查询的结果作为外层另一个查询的过滤条件。

用any、all、some关键字修饰子查询

1、放在比较运算符的后面。

2、any和some是同义词，表示满足内层子循环中的任何一个条件。

3、all表示需要同时满足所有内层查询的条件。

in关键字：进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。

not in与in的作用相反

例：select * from table where exists(子查询);

① 判断子查询是否返回行；

② 如果返回，那么exists的结果为true；

③ 如果没有返回任何行，那么exists返回的结果为false。

插入记录时使用子查询

insert into select语句从一个表复制数据，然后把数据插入到一个已存在的表中。

多表连接查询

语法结构： table_reference

                     [inner] join {left|right} [outer] join

                     Table_reference

                     on conditional_ecpr
1
2
3
4
5

内连接：根据连接条件从多个表中查询选择数据，显示这些表中与连接条件相匹配的数据行，组合成新纪录。

语法结构： select column_list

                      From t1

                      [inner] join t2 on join_condition1

                      [inner join t2 on join_condition2

                      …]

                      where where_conditions;
1
2
3
4
5
6
7
8
9

外连接：查询多个表中相关联的行。

左外连接：显示左表全部记录，右表满足连接条件的记录。

右外连接：显示右表全部记录，左表满足连接条件的记录。

语法结构： select column_list

                      From t1

                      left|right [outer] join t2 on join_condition1;
1
2
3

自连接：如果在一个连接查询中，涉及的两个表都是同一个表。

自连接是一种特殊的连接查询，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。

例： select t1.category_id as ‘图书类别编号’,

         t1.category as '图书类别名称',

         t2.category as '图书上级分类名称'

         from bookcategory t1

         left join bookcategory t2 ON t.parent_id = t2.category_id;
1
2
3
4
5
6
7

多表更新

update tabl1 {[inner] join | {left | right} [outer] join} table2

on 连接条件

set 列名1 = {值1 | default}

[,列名2 = {值2 | default}]…

[where 筛选条件]

多表记录删除

delete 表1[.],表2[.]

from 表1 {内连接|外连接} 表2

on 连接条件

[where 筛选条件]

MySQL的自定义函数

自定义函数

函数（存储函数）

    1、需要返回值；

    2、可以指定0~n个参数。
1
2
3

语法格式： create function 函数名([参数])

                      returns type

                      [characteristics] 函数体
1
2
3

charcateristics指定存储函数的特性，取值举例：

1、 sql security {definer|invoker};指明谁有权限来执行；

    definer表示只有定义者才能执行；

    invoker表示拥有权的调用者才可以执行，默认情况下，系统指定为definer；
1
2
3

2、 comment’string’：注释信息，才可以用来描述存储函数。

函数体：

① 函数体是由SQL代码构成；

② 函数体可以是简单的SQL语句，比如：简单的查询语句

③ 函数体如果为复合结构需要使用begin…end语句

④ 复合结构可以包含声明、流程控制。

delimiter // – 设置结束符为//

create function ym_date(mydate date)

returns varchar(15) – returns指定函数的返回类型

begin

return date_format(mydate,‘%Y-%m’); – 函数需要返回值，return返回相应的处理结果

end//

delimiter ; – 恢复结束符

删除自定义函数

drop function [if exists] func_name;

变量：可以在存储程序（存储过程和函数）中使用变量。

语法格式：declare var_name[,var_name] … date_type [default value];

两种赋值方式：

① Set var_name = expr[,var_name = expr]…;

② Select col_name[,…] into var_name[…] table_expr;

If分支语句

if condition then

          ……;
1

[elseif condition then]

          ……;
1

[else]

          …;
1

end if;

case分支语句

① case case_expr

            when when_value then statement_list;

            [when when_value then statement_list;]…

            [else statement_list;]

        end case;
1
2
3
4
5
6
7

② case

            when expr_condition then statement_list;

            [when expr_condition then statement_list;]…

            [else statement_list;]

         end case;
1
2
3
4
5
6
7

while循环语句

语法格式： [while_label:]while condition do

                      …

                      End while[while_libe];
1
2
3

loop循环语句：该循环没有内置循环条件，但可以通过leave语句退出循环。

语法格式： [loop_label:]loop

                      Statement_list;

                      End loop [loop_label];
1
2
3

leave语句用来跳出循环,语法格式：leave label;

repeat循环语句：该语句执行一次循环体，之后判断condition条件是否为真，为真则退出循环，否则继续执行循环体。

语法格式： [repeat_label:] repeat

                      …;

                      until expr_condition

                      end repeat [repeat_label:];
1
2
3
4
5

存储过程

存储过程是SQL语句和流程控制语句的预编译集合,并以一个名称存储作为一个单元进行处理。

语法结构： create procedure proc_name([proc_parameter])

                      [characteristics…] routine_body
1

proc_parameter指定存储过程的参数列表，形式如下：

[in|out|inout] param_name type

in：表示该参数的值必须在调用存储过程时指定。

out：表示该参数的值可以被存储过程改变，并且可以返回。

inout：表示该参数的值调用时指定，并且可以被改变和返回。

过程体（routine_body）

① 过程体由合法的SQL语句构成；

② 过程体可以是任意SQL语句；

③ 过程体如果为复合结构则使用begin…end语句；

④ 复合结构可以包含声明，流程控制语句。

调用存储过程

① call proc_name([parameter[,…]]);

② call proc_name[()];

删除存储过程

drop procedure [if exists] proc_name;

存储过程与函数的区别

1. 功能上的不同：

存储过程：一般来说，存储过程实现的功能要复杂一点。功能强大，可以执行包括修改表等一系列数据库操作。

存储函数：实现的功能针对性比较强。

2. 返回值上的不同：

存储过程：可以返回多个值，也可以不返回值，只是实现某种效果或动作。

存储函数：必须有返回值，而且只能有一个返回值。

3. 参数的不同：

存储过程：存储过程的参数类型有三种，in、out、inout。

存储函数：参数类型只有一种，类似于in参数。调用函数时需要按照参数的类型指定值即可。

4. 语法结构上的不同：

存储过程：存储过程声明时不需要指定返回类型。

存储函数：函数声明时需要指定返回类型，且在函数体中必须包含一个有效的return语句。

5. 调用方式上的不同：

存储过程：一般是作为一个独立的部分来执行，用call语句进行调用。

存储函数：嵌入在sql中使用的，可以在select中调用。

MySQL的事务

事务由一个或多个SQL语句组成的一个不可分割的整体，SQL语句之间相互依赖，要么全都执行，要么全都不执行。

事务必须满足的四个条件

atomicity（原子性）

consistency（一致性）

isolation（隔离性）

durability（持久性）

控制事务处理

1、rollback：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

2、commit：会提交事务，并使已对数据库进行的所有修改成为永久性的。

3、savepoint identifier：允许在事务中创建一个保存点，一个事务中可以有多个savepoint。

4、rollback to identifier：把事务回滚到标记点

mysql事务处理主要有两种方法：

1、用begin，rollback，commit来实现

begin或start transaction开始一个事务

rollback事务回滚

commit事务确认

2、直接用set来改变MySQL的自动提交模式：

set autocommit = 0禁止自动提交

set autocommit = 1开启自动提交

innodb是事务型数据库的首选引擎，支持事务安全表（ACID）。

例：

DELIMITER //

CREATE PROCEDURE borrowproc(cid CHAR(18),bid INT)

BEGIN

DECLARE store_num INT;

DECLARE money FLOAT(7,3);

SELECT store INTO store_num INTO bookinfo WHERE book_id = bid;

SELECT balance INTO money INTO readerinfo WHERE card_id = cid;

SET autocommit = 0;

INSERT INTO borrowinfo VALUES(bid,cid,CURDATE(),DATE_ADD(CURDATE(),INTERVAL 1 MONTH),‘否’);

UPDATE bookinfo SET store = store - 1 WHERE book_id = bid;

UPDATE readerinfo SET balance = balance - (SELECT price FROM bookinfo WHERE book_id = bid) * 0.05 WHERE card_id = cid;

IF store_num = 0 OR money <= 200 THEN

          ROLLBACK;
1

ELSE

          COMMIT;
1

END IF;

END//

DELIMITER ;

存储引擎的介绍

数据库存储引擎是数据库底层软件组件。数据库管理系统使用数据引擎进行创建、查询、更新和删除数据的操作。

MYSQL的核心就是存储引擎。

可以使用show engines语句查看系统所支持的引擎类型。

innodb存储引擎

① innodb给mysql提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎。

② 对于处理巨大数据量的数据拥有很好的性能。

③ innodb存储引擎支持外键完整性约束。

④ innodb被用于在众多需要高性能的大型数据库站点上。

MyISAM存储引擎

MyISAM拥有较高的插入、查询速度，但不支持事务。

memory存储引擎

① memory存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。

② 使用mysql memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能，但当mysql守护进程奔溃时，所有的memory数据都会丢失。获取速度的同时也带来了一些缺陷。

存储引擎的选择

1、innodb：提供提交、回滚和崩溃恢复能力的事务安全能力，可以实现并发控制。

2、myisam：数据表主要用来插入和查询记录，用myisam引擎可以提供较高的处理效率。

3、memory：临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的memory引擎。

设置存储引擎

1、设置服务器的存储引擎

在配置文件my.ini中的[mysqld]下面设置需要的存储引擎：

default-storage-engine=innodb

重启mysql服务器。

2、设置客户端的存储引擎

set default_storage_engine = innodb;

3、创建表时设置存储引擎

例：create table mytest(

   Id int primary key,

   name varchar(10)

   )engine = innodb default charset = utf8;
1
2
3
4
5

查看表的存储引擎：show table status from mydata where name =‘myengine’;

4、修改表的存储引擎

alter table tablename engine = enginename;

mysql的管理与维护

mysql的用户管理：

          ①  root用户是超级管理员，拥有所有权限。

          ②  普通用户只拥有被授予的各种权限。
1
2
3

权限表

Mysql服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中。

存储账户的权限信息表主要有：user、db、host、tables_priv、columns_priv和procs_priv。

各个权限表的作用

1、user表时mysql中最重要的一个权限表，记录允许连接到服务器的账户信息，里面的权限是全局级的。

2、db表和host表是mysql数据中非常重要的权限表。

3、db表中存储了用户对某个数据库的操作权限。

4、host表中存储了某个主机对数据库的操作权限。

5、tables_priv表用来对表设置操作权限。

6、columns_priv表用来对表的某一列设置权限。

7、procs_priv表可以对存储过程和存储函数设置操作权限。

账户管理

新建普通用户

创建新用户，必须有相应的权限来执行创建操作。

在mysql数据库中，可以使用create user或grant语句。

基本语法格式：

create user ‘user’@’host’ – host指主机名

identified by [password] ‘password’

create user语句创建的新用户没有任何权限，还需要使用grant语句赋予用户权限；而grant语句不仅可以创建新用户，还可以在创建的同时对用户授权。

基本语法格式：

grant privileges on db.table

to ‘user’@’host’ [identified by ‘password’];

删除普通用户

语法：drop user user[,user];

例：drop user ‘testuser’@’localhost’;

语法：delete from mysql.user where host=’hostname’ and user = ‘username’;

权限管理主要是对登录到mysql的用户进行权限验证，所有用户的权限都存储在mysql的权限表中。

mysql权限系统的主要功能是证实连接到一台主机的用户，并且赋予该用户在数据库上的各种权限。

给用户授予权限

基本语法： grant priv_type on db.table to ‘user’@’host’

                      [identified by [password] ‘password’];
1

flush privileges; //刷新系统权限表

查看用户的授权

基本语法：show grants for ‘user’@’host’;

收回权限

语法格式：revoke privilege on db.table from ‘user’@’host’;

MySQL的日志记录了MySQL数据库的运行情况、用户操作、错误信息等，可以为MySQL管理和优化提供必要的信息。

分类：

① 错误日志：记录mysql服务的启动、运行或停止mysql服务时出现的问题。

② 查询日志：记录了mysql的所有用户操作，包括启动和关闭服务、执行查询和更新语句等。

③ 二进制日志：记录所有更改数据的语句。

④ 慢查询日志：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。

启动和设置错误日志

在默认情况下，错误日志会记录到数据库的数据目录下。如果没有在配置文件中指定文件名，则文件名默认为hostname.err。

错误日志的启动和停止以及指定日志文件名，都可以通过修改my.ini来配置。错误日志的配置项是log-error。

如果需要指定文件名，则配置项如下： [mysqld] log-error=file_name

查看错误日志

    mysql的错误日志是以文本形式存储的，可以使用文本编辑器直接查看mysql的错误日志。

    如果不知道日志文件的存储路径，可以使用show variables语句查询错误日志的存储路径。

    show variables like ‘log_error’;
1
2
3
4
5

删除错误日志

    若数据库运行时间较长，错误日志文件可能比较大，那么可以将日志文件删除，再重新创建新的错误日志文件。

    mysql的错误日志是以文本文件形式存储在文件系统中的，可以直接删除。

     在删除错误日志之后，如果需要重建日志文件，需要在服务器端执行以下命令：mysqladmin –uroot –p flush-logs
1
2
3
4
5

或者登录mysql，然后执行flush logs语句：

    在日志文件不存在的情况下，执行flush logs语句会创建新的日志文件。

    如果日志文件存在的情况下，并不做日志备份和创建的操作。
1
2
3

数据的备份

mysqldump是mysql提供的一个非常有用的数据库备份工具。

mysqldump命令执行时，可以将数据库备份成一个文本文件。

基本语法格式： mysqldump –u user –h host –p password

                                   dbname[tbname,[tbname…]]>filename.sql
1

例：mysqldump --no-defaults -uroot -p mydata > D:test.sql

备份数据库表：

例：mysqldump --no-defaults -uroot -p mydata mytest > xxx.sql

备份多个数据库：

例：mysqldump --no-defaults -uroot -p --databases mydata mysql > xxx.sql

备份全部数据库：

例：mysqldump --no-defaults -uroot -p --all-databases > xxx.sql

数据的恢复

使用mysql命令恢复，语法格式：

mysql –u user –p [dbname] < filename.sql

如果已经登录没有到期了服务器，还可以使用source命令导入SQL文件，语法：source filename

导出：就是将mysql数据库中的数据导出到外部存储文件中。

导入：就是将外部存储文件中的数据导入到mysql数据库中。

数据表的导出

使用select…into outfile导出文本文件，语法格式：select columnlist from table where condition into outfile ‘filename’ [options]

[options]为可选参数选项，比如：

① 参数fields terminated by ‘value’：设置字段之间的分割字符，可以为单个或多个字符，默认情况下为制表符’\t’。

② 参数lines terminated by ‘value’：设置每行数据结尾的字符，可以为单个或多个字符，默认情况下为制表符’\n’。

文件导入导出路径：

my.ini→ secure-file-priv=“e” //指定mysql导入导出路径

secure-file-priv=null //限制mysql导入导出

secure-file-priv=’’ //mysql导入导出不做任何限制

例：mysql> select * from mydata.classinfo into outfile ‘…/class.txt’

   -> fields terminated by ','

   -> lines terminated by '\r\n';
1
2
3

使用mysqldump命令导出文本文件

Mysqldump创建一个包含创建表的create table语句的tablename.sql文件和一个包含其数据的tablename.txt文件。

语法格式：mysqldump –T path –u root –p dbname [tables] [options]

[options]为可选参数选项，比如：

1、–fields-terminated-by=value：设置字段之间的分隔字符，可以为单个或多个字符，默认情况下为制表符’\t’。

2、–lines-terminated-by=value：设置每行数据结尾的字符，可以为单个或多个字符，默认情况下为制表符’\n’。

使用mysql命令导出文本文件

语法格式：mysql –u root –p --execute=”select语句” dbname > filename.txt

（导出文件第一行多出字段名）

数据表的导入

使用load data infile方式导入文本文件，语法格式： load data infile ‘filename.txt’ into table tablename [options] [ignore number lines]

[options]为可选参数选项，比如：

① 参数fields terminated by ‘value’：设置字段之间的分割字符，可以 为单个或多个字符，默认情况下为制表符’\t’。

② 参数lines terminated by ‘value’：设置每行数据结尾的字符，可以为单个或多个字符，默认情况下为制表符’\n’。

使用mysqlimport命令导入文本文件

语法格式：mysqlimport –u root –p dbname filename.txt [options]

[options]为可选参数选项，比如：

1、–fields-terminated-by=value：设置字段之间的分隔字符，可以为单个或多个字符，默认情况下为制表符’\t’。

2、–lines-terminated-by=value：设置每行数据结尾的字符，可以为单个或多个字符，默认情况下为制表符’\n’。

补充：

USE 数据库名 :
选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。

SHOW DATABASES:
列出 MySQL 数据库管理系统的数据库列表。

SHOW TABLES:
显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。

SHOW COLUMNS FROM 数据表:
显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。

SHOW INDEX FROM 数据表:
显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

SHOW TABLE STATUS [FROM db_name] [LIKE ‘pattern’] \G:

该命令将输出Mysql数据库管理系统的性能及统计信息。

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。

UNION [ALL | DISTINCT]
DISTINCT: 可选，删除结果集中重复的数据。

ALL: 可选，返回所有结果集，包含重复数据。

没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

当提供的查询条件字段为 NULL 时，MySQL提供了三大运算符:

IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。
事务

在MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

索引分单列索引和组合索引

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。

组合索引，即一个索引包含多个列。

创建索引

CREATE INDEX indexName ON table_name (column_name)
修改表结构（添加索引）

ALTER table tableName ADD INDEX indexName(columnName)
临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

如果使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然也可以手动销毁。

INSERT IGNORE INTO 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据

查看MySQL的安装目录和数据存放目录

select @@basedir; #安装目录

select @@datadir; #数据存放目录

show variables like ‘%secure%’; #查看secure-file-priv值

？MySQL导出文件时，遇到报错“The MySQL server is running with the --secure-file-priv option so it cannot execute this statement”

！找到my.ini配置文件，搜索secure，修改secure-file-priv路径（注意斜杠和反斜杠），重启MySQL服务。

使用 SELECT … INTO OUTFILE 语句导出数据

通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：

mysql> SELECT * FROM passwd INTO OUTFILE ‘/tmp/runoob.txt’
-> FIELDS TERMINATED BY ‘,’ [optionally] ENCLOSED BY ‘"’
-> LINES TERMINATED BY ‘\r\n’;
MySQL 中 delete where in 语句的子查询限制

句的结果组合到一个结果集合中。

UNION [ALL | DISTINCT]
DISTINCT: 可选，删除结果集中重复的数据。

ALL: 可选，返回所有结果集，包含重复数据。

没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

当提供的查询条件字段为 NULL 时，MySQL提供了三大运算符:

IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。
事务

在MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

索引分单列索引和组合索引

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。

组合索引，即一个索引包含多个列。

创建索引

CREATE INDEX indexName ON table_name (column_name)
修改表结构（添加索引）

ALTER table tableName ADD INDEX indexName(columnName)
临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

如果使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然也可以手动销毁。

INSERT IGNORE INTO 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据

查看MySQL的安装目录和数据存放目录

select @@basedir; #安装目录

select @@datadir; #数据存放目录

show variables like ‘%secure%’; #查看secure-file-priv值

？MySQL导出文件时，遇到报错“The MySQL server is running with the --secure-file-priv option so it cannot execute this statement”

！找到my.ini配置文件，搜索secure，修改secure-file-priv路径（注意斜杠和反斜杠），重启MySQL服务。

使用 SELECT … INTO OUTFILE 语句导出数据

通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：

mysql> SELECT * FROM passwd INTO OUTFILE ‘/tmp/runoob.txt’
-> FIELDS TERMINATED BY ‘,’ [optionally] ENCLOSED BY ‘"’
-> LINES TERMINATED BY ‘\r\n’;
MySQL 中 delete where in 语句的子查询限制

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PWkZYNBD-1686673586852)(mysql学习笔记.assets/image-20230425171058280.png)]