MySQL数据库基本操作

597人浏览 2023-10-23 15:28:35

一、SQL语句 (mysql 数据库中的语言)

show databases;查看数据库
use "database_name" ;进入数据库
show tables; 查看当前数据库中有哪些表
select * from "table_name";查询数据表中的所有内容
describe "table_name"; 查看表结构
desc "table_name";

类比excel表格

简写

filed 字段名

二、DDL

1.DDL语句

用于创建数据库对象(库、表、索引等)

(1)创建新的数据库

create database 数据库名;

(2)创建新的表

create table 表名(字段1 数据类型,字段2 数据类型[, ...] [, primary key (主键名)]);
主键一般选择能代表唯一性的字段不允许取空值(NULL) ，一个表只能有一个主键。

create database 数据库名;
use 数据库名;
create table 表名 (id int not null, name char(10) not null, score decimal (5,2) ,passwd char (48) defalt' ',primary  key (id)) ;
 desc 表名;
 not null        不允许为空值
 default ' '      默认值为空
 primary key ：   主键一般选择没有重复并且不为空值的字段
 例子
create table 表名 (id int(10) not null primary key, name varchar(40) ,age int(3));
create table food (id int(3) , name varchar (40) ,money decimal (3,1) ,primary key (id));

2.删除数据库和表

删除指定的数据表

drop   删除表内容(数据)和表结构
 
use  数据库名
drop table 表名 
drop table [数据库名.] 表名;
如不用use进入库中，则需加上数据库名
删除指定的数据库
drop database 数据库名;

三、DML

管理表中的数据记录

insert: 插入新数据update: 更新原有数据delete: 删除不需要的数据

1.insert插入新数据

格式:
 insert into 表名(字段1,字段2[,...]) values (字段1的值，字段2的值，...);
 
例子：
insert into 表名 (id,name,score,passwd) values (1,'自定义',70.5,passwd('123456')) ;
 passwd('123456') :查询数据记录时，密码字串以加密形式显示:若不使用passwd()， 查询时以明文显示。
 密码复杂性验证
 insert into 表名 values(2,'自定义',90.5, 654321) ;
select * from 表名 ;      查询表的数据记录
 insert插入表数据
 在此之前需要进行查看desc table_name; 来查看表结构(有哪些字段，有无主键，主键是哪个字段，type，是否允许为空，是否有默认值)
 使用insert into table_name进行插入，是根据查看到的表结构来判断，可以怎么写

2.update更新原有数据

修改、更新数据表中的数据记录

格式:
update 表名 set 字段名1=字段值1[,字段名2=字段值2] [where 条件表达式];
 
例子:
update 表名 set passwd=PASSWORD('') where name='自定义';
update 表名 set name= '自定义' , passwd='' where id=3;

3.delete: 删除不需要的数据(表内容)

在数据表中删除指定的数据记录(行)

格式:. delete from 表名 [where 条件表达式]; 
例子: delete from 表名 where id=4;

四、DQL查询数据记录

select
 
格式:
 
seleect 字段名1,字段名2[,...] from 表名[where 条件表达式];
 
例子:
seleect * from 表名;
seleect id, name from 表名;
seleect id, name, score from 表名 where id=2;
 
select name from 表名\G          以列表方式竖向显示
select * from info limit 2;      只显示前3行
select * from info limit 2,3;    显示第3行后的前3行

类比excel表格

五、DCL

1.alter 修改表名和表结构(表结构)

alter table 旧表名 rename 新表名;
 
扩展表结构(增加字段)
alter table 表名  add address varchar(50) default '地址不详' ;
default ' 地址不详':表示此字段设置默认值为地址不详，可与not null配合使用
alter table 表名 add address varchar(50) not null default '地址不详' ;
 
修改字段(列)名，添加唯一键(唯一性约束)
alter table 表名 change 旧列名 新列名 数据类型 [unique key] ;
unique key:唯一键(特性:唯一， 但可以为空，空值只允许出现一次)
primary key (主键) :唯一且非空
alter table 表名 change name user_name varchar(10) unique key;
change可修改字段名、数据类型、约束等所有项。
 
删除字段
格式:
alter table 表名 drop 字段名;

2.扩展

use 数据库名;
 
create table if not exists info (id int(4) zerofill primary key auto_ increment(指定主键的第:二种方式.),name varchar(10) not null,cardid int(18) not null unique key,hobby varchar(50) ) ;
 
if not exists:   表示检测要创建的表是否已存在，如果不存在就继续创建
 
int(4) zerofill: 表示若数值不满4位数，则前面用"0"填充，例0001
 
auto_increment: 表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增;
    自增长字段数据不可以重复;自增长字段必须是主键;如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
 
unique key: 表示此字段唯一键约束，此字段数据不可以重复:一张表中只能有一个主键，但是一张表中可以有多个唯一键
 
not null:表示此字段不允许为NULL

3.高级操作

use 数据库名;
create table if not exists info (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cardid int(18) not null unique key, 
hobby varchar (50));
 
if not exists:表示检测要创建的表是否已存在，如果不存在就继续创建
 
int(4) zerofill: 表示若数值不满4位数，则前面用"0"填充，例0001
 
auto_ increment: 表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增; 自增长字段数据不可以重复;自增长字段必须是主键;如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
 
funique key: 表示此字段唯一键约束， 此字段数据不可以重复:一张表中只能有一个主键，但是一张表中可以有多个唯一键
 
not null: 表示此字段不允许为NULL

4. 数据表入门级操作

create table test1 like test;
复制格式，通过like方法，复制数据库里的表结构生成另一个表
 
insert into test1 select * from test;
备份内容
克隆表，将数据表的数据记录生成到新的表中
 
create table test02 (select * from test);  复制test表数据到test02中
 
show create table test02\G
获取数据表的表结构、索引等信息
 
select * from test02;

六、清空表，删除表内的所有数据

1.方法一

delete. from test01;
 delete清空表后，返回的结果内有删除的记录条目;
 delete工作时是一行一行的删除记录数据的;如果表中有自增长字段，使用delete from 删除
所有记录后，再次新添加的记录会从原水最大的记录ID后面继续自增写入记录。
 delete from teat01 不会删除表的结构

2. 方法二

truncate table test01; 
 truncate清空表后，没有返回被刚除的条目:truncate工作时是将表结构按原样重新建立，因此在速度上truncateE会比DELETE清空表快;使用truncate table 清空表内数据后，ID会从1开始重新记录

3. 删除类型

drop table table_name
 
1)属于DDL:
2)不可回滚(无法恢复)
3)不可带where
4)表内容和结构删除
5)删除速度快
truncate table table_name 
 
1)属于DDL
2)不可回滚
3)不可带where
4)表内容删除
 
PS: truncate 操作过程是:先保留要删除的表的表结构，然后删除表，再重建这张表的表结构
所以，例如auto_ increment 自增长的这种属性，会从头开始重新记录
 
5)删除速度快
delete from table_name
 
1)属于DML
2)可回滚(可恢复)
3)可带where
4)表结构在，表内容要看where执行的情况(执行更为麻烦)
5) 删除速度慢,需要逐行删除

不再需要一张表的时候，用drop.

想删除部分数据行时候，用delete，并且带上where子句

保留表而删除所有数据的时候用truncate

速度

drop > trungate > delete

安全性

delete 最好

七、创建临时表

临时表创建成功之后，使用show tables命令是看不到创建的临时表的，临时表会在连接退出后被销毁。

如果在退出连接之前，也可以可执行增删改查等操作，比如使用DROPTABLE语句手动直接删除临时表。

无法创建外键
 
create temporary table 表名 (字段1数据类型,字段2数据类型[，...]
[，primary key (主键名) ] );
 
sql 写入→数据表中→先把数据保存在内存中→写入到磁盘
insert into info→info这张表， 会先复制一份表数据到内存里面，给我们进行修改→敲完回车之后，确定提交了，才会写入数据表中--》再保存在磁盘里面
 
create table test01→只会保存在内存中，在数据库退出连接之前的所有操作，都是在内存中
进行的，不会保存在磁盘里面，退出连接后，临时表会释放掉
 
例子:
create temporary table test03 (
id int(4) zerofill primary key auto_increment,
name varchar (10) not null,
cardid int(18) not null unique key,
hobby varchar(50))
insert into test03 values (1,'zhangsan',123456,'running');
select * from test03;
show tables; 
quit
mysql -u -P
use 数据库名
select * from test03;

增、删、改、查操作:
增: create 、alter table table_name add 、insert、show create table 表名
删: drop、 delete、 truncate
改: alter、 update .
查: select show desc
    auto_ increment

八、MySQL中6种常见的约束/规则

(1)主键约束(primary key) :具有唯一且非空

(2)外键约束(foreign key)

(3)非空约束(not null)：不可为空

(4)唯一性约束(unique [keylindex]) :唯一，可为空，但空值只允许出现一次

(5)默认值约束(default) : create (address varchar (50) default ' 魔都') ;

(6)自增约束(auto_ increment) :随着记录增加，基于最新的记录的id进行+1的自增长

1.外键的定义

如果同一个属性字段x在表一中是主键，而在表二中不是主键，则字段x称为表二的外键。

创建外键约束作用(误删，修改)，保证数据的完整性和一致性。

2.主键表和外键表

(1)以公共关键字作主键的表为主键表(父表、主表)

(2)以公共关键字作外键的表为外键表(从表、外表)

注意:与外键关联的主表的字段必须设置为主键。要求从表不能是临时表,

主表外键字段和从表的字段具备相同的数据类型、字符长度和约束(不包括主键约束)。

创建主表test01
create table test01 (hobid int(4),hobname varchar (50));
 
创建从表test02
create table test02 (id int(4) primary key auto_increment,name varchar(10),age int(3),hobid int(4));
 
为主表test01添加一个主键约束。主键名建议以"PK_”开头。
alter table test01 add constraint PK_hobid primary key (hobid);
 
为从表test02表添加外键，并将test02表的hobid字段和test01表的hobid字段建立外键关联。
外键名建议以"FK_”开头。
alter table test02 add constraint FK_hobid foreign key (hobid) references test01 (hobid);
references :引用
 
可以使用查询表语句结构命令查看外键关联
show create table test02;
 
desc test01;
desc test02;
 
Fk:foreign key 外键约束
PK:primary key 主键约束

插入新的数据记录时，要先主表再从表
insert into test01 values(1,'runing');
insert into test02 values (1,'zhangsan',18,1);
 
关联性：关联两张表
 
删数数据记录时，要先从表再主表，也就是说删除主键表时必须先删除其他与之关联的表。
drop tables test05;
drop tables test04;
 
查看和删除外键约束
如果要删除外键约束字段先删除外键约束，再删除外键名
show create table test05;
alter table test05 drop foreign key FK_hob;
alter table test05 drop key FK_hob;
desc test05;
 
外键约束:外键约束的规则和一个外键名称。

九、数据库用户管理(库、表)

1.新建用户


create user '用户名'@'来源地址' [identified by [password] '密码'];
 
'用户名':指定将创建的用户名.
'来源地址':指定新创建的用户可在哪些主机上登录，可使用IP地址、网段(IP地址/24) 、主机名的形式(localhost) ，本地用户可用localhost,允许任意主机登录，可用通配符%
'密码': 若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密;若使用加密密码，需要先使用select password('密码'); 获取密文，再在语句中添password '密文';若省略“identified by"部分，则用户的密码将为空(不建议使用)
create user 'user1 '@' localhost' identified by '自定义' ;
select password('自定义') ;
create user 'user2'@'localhost' identified by password 
' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' ;

2.管理用户

(1)查看用户信息
 
创建后的用户保存在mysql数据库的user表里.
 
use mysql;
select user, authentication string, Host from mysql.user;
 
(2)重命名指定
rename user '自定义'@'localhost' to '自定义'@'localhost';
 
(3)删除用户
drop user '自定义'@' localhost' ;

3.修改密码


(1)修改当前登陆的用户的密码
 
set password = password('自定义') ;
flush privilege: 
 
(2)修改其他用户密码
set password for 'userl '@' localhost' = password(' abc123') ;
 
(3)忘记root密码的解决办法安全性保障→简单密码→被审计→责令修改密码复杂性
 
修改/etc/my.cnf 配置文件，免密登陆mysql
vim /etc/my.cnf
[mysqld]
skip-grant-tables     添加，使登录mysql不使用授权表
 
systemctl restart mysqld
mysql                  直接登录
 
然后使用SQL语句修改密码
update mysql.user set authentication_string =password('自定义') where user='root';
 
flush privileges;
quit
mysql -u root -p密码
 
PS:最后再把/etc/my.cnf配置文件里的skip-grant-tables删除，并重启mysq1服务，然后尝试使用新密码登陆

4. 数据库用户授权


授予权限
 
grant提权
 
grant 权限列表on数据库名.表名to '用户名'@'来源地址' [identified by '密码'];
 
示例:grant all on *.* to 'skyuser'@'' identified by ' admin123' ;
grant all on *.*
 
权限列表:用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select， insert, alter、show、update"。 使用"all"表示所有权限，可授权执行任何操作。
 
数据库名.表名:用于指定授权操作的数据库和表的名称，其中可以使用通配符"*"。
例如，使用"kgc.*"表示授权操作的对象为kgc数据库中的所有表。
 
'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.kgc.com"、“192.168.226.%”等。
 
identified by：用于设置用户连接数据库时所使用的密码字符串。
在新建用户时，若省略“identified by"部分，则用户的密码将为空。
 
允许用户zhangsan在本地查询school数据库中所有表的数据记录，
但禁止查询其他数据库中的表的记录。
grant select on school.* to 'zhangsan'@'localhost' identified by 'abc123';
 
允许用户lisi在所有终端远程连接mysql，并拥有所有权限。
grant all [privileges] on *.* to 'lisi'@'%' identified by '123456';
 
flush privileges;    #刷新权限（mysql.user 权限规则表）
quit
 
mysql -u zhangsan -pabc123
use xyw;
show tables; .
select * from YYY;
 
查看权限
mysql -u root -pabc123
show grants for用户名@来源地址;
show grants for '自定义'@'%' ;
 
撤销权限
revoke 权限列表on数据库名.表名from用户名@来源地址;
 
revoke all on *.* from '自定义'@'%' ;
 
show grants for '自定义 '@'%';
usage权限只能用于数据库登陆，不能执行任何操作; usage权限不能被回收，即revoke不能删除用户。
flush privileges;