mysql数据库总结

2023-12-22 07:06:57   0  举报





AI智能生成

MySQL是一种开源的关系型数据库管理系统，支持跨平台操作，广泛应用于Web应用程序、企业系统和嵌入式系统。MySQL数据库的核心特性包括ACID（原子性、一致性、隔离性和持久性）特性，支持多种数据类型，如整数、浮点数、日期/时间、文本和二进制数据等。它提供了许多功能和工具，如连接池、事务、锁、缓存和备份与恢复机制，以保障数据的安全性和高可用性。此外，MySQL还支持集群和复制功能，以实现数据的分布式和高可用性。它的查询语言为SQL，提供多种接口，如JDBC、ODBC和Python等，与其他编程语言的集成易于实现。随着MySQL的版本更新，越来越多的新功能被添加，如JSON支持、全文检索和空间数据存储等，使得MySQL在现代应用程序中具有更广泛的应用价值。

数据库

作者其他创作

大纲/内容

约束

**主键** primary key #用于id

字段名字段类型 primary key

主键数在一个表中，只能有一个。

alter table 数据库表名 add primary key(字段名);

alter table 数据库表名 drop primary key;

字段名字段类型 primary key auto_increment

自增长只能用在主键上

alter table su modify column id int primary key auto_increment;

将表su id字段设置为主键自增长

alter table 数据库表名 auto_increment=起始值;

记录删除后对自增的影响

delete

删除记录后，对自增长字段没有影响

truncate

删除记录后，自增长字段重新从1开始

**唯一** unique #姓名唯一

字段名字段类型 unique

**非空** not null

字段名字段类型 not null

非空与唯一约束可以设置在N个字段上

**默认值** default

字段名字段类型 default 默认值

alter table su alter column sname set default 'zhang';

给表su 中sname设置默认字段

alter table su alter column sname drop default;

删除表su 中sname设置默认字段

**外键** foreign key

[constraint][外键约束名称] foreign key(外键字段名) references 主表名称(主键字段名); #在create table时设置

alter table 从表名称 add [constraint][外键约束名称] foreign key(外键字段名) references 主表名称(主键字段名); #建好表后修改

alter table 从表 drop foreign key 外键约束名称;

删除外键约束

一张表的一个字段受限于另外一张表的一个字段对应的值。这里涉及到两张表：被引用的表叫主表（父表），另外一张叫从表（子表）。

**子表：**定义了外键的表，外键的取值要么取父表中字段对应的值，要么取NULL值，严重受限于父表

**父表：**被引用的字段要具有唯一性（绝大多数都是用的父表的主键）

**检查约束** check

行级添加

create table 表名 ( check (字段名1 != 字段名1), 字段名1 字段类型 check (表达式), 字段名2 字段类型 constraint 唯一约束名 check (表达式) [not enforced] );

表达式中可以包含该字段名: 字段名 > 0 表达式注意事项 1.允许使用文字，内置函数和运算符 2.不允许在使用了auto_increment的列上使用 3.不允许存储函数和用户定义的函数 4.不允许子查询等如果省略或指定为enforced，则创建并强制执行约束。如果指定为not enforced，则创建约束但不强制执行。

表级添加

create table 表名( 字段名1 字段类型, 字段名2 字段类型, [constraint 检查约束名] check(布尔表达式) [ not enforced]);

表后添加

alter table 数据库表名 add constraint 检查约束名 check(检查约束);

约束删除

alter table 数据库表名 drop check 检查约束名;

mysqldump备份

备份

备份整个数据库

mysqldump -u用户名 -p密码数据库名 > sql文件位置

备份数据库中的某个表

mysqldump -u用户名 -p密码数据库名数据库表名1 数据库表名2> sql文件位置

备份多个数据库

mysqldump -u用户名 -p密码 --databases 数据库名1 数据库名2> sql文件位置

还原

登录mysql数据库后

进入所要导入的数据库

source 导入文件的路径;

mysql - u root -p < .sql文件

指令

show

show character set;

show databases;

show create database 数据库名;

show tables;

desc 数据库表名;

show create table student;

use

use 数据库名;

select database();

flush privileges;

刷新数据库

DDL

create

create database 数据库名;

create database if not exists 数据库名;

create database 数据库名 character set 字符集;

create table 新数据库表名 like 旧数据库表名;

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

desc 数据库表名;

show create table student;

show databases;

show create database 数据库名;

alter

alter table 数据库表名 add 字段名字段类型；

alter table 数据库表名 modify 字段名新字段类型; #5.7版本可以改int类型 8.0版本改后不显示

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

alter table 数据库表名 drop 字段名;

rename table 数据库表名 to 新数据库表;

alter table 数据库表名 character set 字符集;

show character set;

drop

drop database 数据库名;

drop table 数据库表名;

drop table if exists 数据库表名;

DML

insert

insert [into] 数据库表名 [字段名列表] values(字段值列表)

insert into student(id,sname,birthday)values(1,"林志颖","1974-10-18");

insert student values(2,"郭德纲","1973-01-18");

insert into 数据库表名 values(字段值1,字段值2...,字段值n);

insert into student(id,sname)values(3,"柳岩");

update

update 数据库表名 set 字段名1=字段值1,字段名2=字段值2...,字段名n=字段值n [where 条件表达式];

update 数据库表名 set 字段名1=字段值1,字段名2=字段值2...,字段名n=字段值n;

delete

delete from 数据库表名 [where 条件表达式];

delete from 数据库表名;

truncate table 数据库表名; #删除表中所有的记录

DQL

select

select */字段列表 from 数据库表名 [where 条件表达式];

记录查询

select * from 数据库表名;

select 字段名1,字段名2,...,字段名n from 数据库表名;

select 字段名1 as 别名1,字段名2 as 别名2,...,字段名n as 别名n from 数据库表名;

select sname as 姓名,sex as 性别 from student;

select 字段名1 as 别名1,字段名2 as 别名2,...,字段名n as 别名n from 数据库表名 as 别名;

select sname as 姓名,sex as 性别 from student as s;

select distinct 字段名 from 数据库表名;

select distinct sex from student;

select distinct sex,sname from student;

select 字段名+固定值 from 数据库表名;

select age-10 as age ,sname from student;

select 字段名+字段名 from 数据库表名;

select age+id,age,id from student;

条件查询

基础运算符

>、<、<=、>=、=、<>

select * from students where english>80;

select * from students where english<80;

select * from students where age<=19;

select * from students where math=88;

select * from students where age<>18;

select * from students where age>=19;

select sname,sex from students where age>=20;

提高运算符

between...and...

select * from students where english between 80 and 90;

in(值1,值2...,值n)

select * from students where sid in(1,3,5);

not in(值1,值2...,值n)

select * from students where sid not in(1,3,5);

like '%值%'

mysql通配符

% :匹配任意多个字符

select * from students where sname like '张%';

_ ：匹配一个字符

select * from students where sname like '王_';

[ ]：表示括号内所列字符中的一个（类似正则表达式）。

指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

select */字段列表 from 数据库表名 where 字段名 regexp [字符串]字符串;

[^ ] ：表示不在括号所列之内的单个字符。

其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

select */字段列表 from 数据库表名 where 字段名 regexp [^字符串]字符串;

可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。

is null #表示某字段值为null

select * from students where age is null;

is not null #表示某字段值不为null

select * from students where english is not null;

逻辑运算符

and #与，二边条件同时成立，成立 &&

select * from students where english>85 and sex='男';

or #或，二边条件有一边成立，成立 ||

select * from students where age>=20 or math>85;

not #非，一边条件成立，取反；一边条件不成立，取反！

select * from students where not (age>=20 or math>85);

排序

select */字段列表 from 数据库表名 [where 条件表达式] [order by 字段名 [asc/desc]];

select * from students where english is not null order by english;

select */字段列表 from 数据库表名 [where 条件表达式] [order by 字段名1 [asc/desc],字段名2 [asc/desc],...,字段名n [asc/desc]];

select * from students where english is not null order by math,english desc;

分组

select */字段列表 from 数据库表名 [group by 分组字段名 [having 条件表达式]];

select sex from students group by sex;

select sex,count(*) from students group by sex;

where #where后面不能用聚合函数

select sex,count(*) from students where math>80 group by sex;

having #having是先分组，在将分组后不符合条件的去掉。

select sex,count(*) as n from students group by sex having n>3; #having n>3 可以写成 having count(*)>3

聚合函数基础

count(字段名)：统计总行数 sum(字段名)：计算列总和 avg(字段名)：求某一列平均值 min(字段名)：求某一列的最小值 max(字段名)：求某一列的最大值

select 聚合函数(字段名) from 数据库表名;

select count(*) from students where age>19;

select sum(english) as 英语总分 from students;

select avg(english) as 英语平均分 from students;

select max(english) as 英语最高分 from students;

select min(english) as 英语最低分 from students;

聚合函数分组

select count(*),sex from students group by sex;

select count(*),sex,sum(english),avg(math) from students group by sex;

select count(*) as n,sex,sum(english),avg(math) from students group by sex having n>3;

聚合函数应用扩展

group_concat #放置每一组的某字段的值的集合。

select count(*),sex,sum(english) as 英语总分,avg(math) as 数学均分,group_concat(math) from students group by sex;

with rollup #在最后新增一行，group by分组字段的基础之上在进行统计数据

select count(*),sex,sum(english),avg(math) from students group by sex with rollup;

limit分页

select */字段列表 from 数据库表名 [limit offset,length];

offset：起始行数，从 0 开始计数，如果省略，默认就是 0

length：返回的行数

DCL

用户管理

查看用户

select * from user;

select user,host,plugin,authentication_string from user;

创建用户

create user '用户名'@'主机名' identified by '密码';

**注意：** **用户名：** 新用户的名字 **主机名：** 指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符% **密码：** 密码可以为空，如果为空则该用户可以不需要密码登陆服务器

删除用户

drop user '用户名'@'主机名';

密码管理

修改用户密码

alter user '用户名'@'主机名' identified by '密码';

设置管理员（root）密码

清空密码

use mysql;

update user set authentication_string='' where user='root';

设置密码

alter user 'root'@'%' identified by 'Root12345';

alter user 'root'@'localhost' identified by 'Root12345';

权限管理

查看用户权限

show grants for '用户名'@'主机名';

授权

grant 权限 1, 权限 2... on 数据库名.表名 to '用户名'@'主机名';

**grant… on …to ：**授权关键字 **权限：** 如select\insert\update\delete等，如果是所有用all **数据库名.表名：** 该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.* **’用户名’@‘主机名’ ：** 注意单引号不能省略

撤销授权

revoke 权限 1, 权限 2... on 数据库名.表名 from '用户名'@'主机名';

**revoke… on …from ：** 撤消授权关键字 **权限：** 如select\insert\update\delete等，如果是所有用all **数据库名.表名：** 该用户可以操作哪个数据库的哪些表。如果要撤消授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.* **’用户名’@‘主机名’ ：** 注意单引号不能省略