MySQL的增删改查，索引创建与存储引擎修改

最新推荐文章于 2024-09-14 15:36:45 发布

peihhua

最新推荐文章于 2024-09-14 15:36:45 发布

阅读量5.6k

点赞数 2

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_48192495/article/details/108536904

版权

一. MySQL的结构

数据以表格的形式出现，每行为单独的一条记录，每列为一个单独的字段，许多的记录和字段组成一张表单（table）若干的表单组成库（database）。

1.1 记录（一条数据）

在数据库当中，表当中行称之为记录.

1.2 字段

在数据库当中，表当中的列称之为字段。（id name …)

1.3 MySQL数据类型

数据类型用于指定特定字段所包含数据的规则，它决定了数据保存在字段里的方式，包括分配给字段的宽度，以及值是否可以是字母、数字、日期和时间等。任何数据或数据的组合都有对应的数据类型，用于存储字母、数字、日期和时间、图像、二进制数据等。数据类型是数据本身的特征，其特
性被设置到表里的字段。

MySQL常见基础数据类型:

字符串类型（CHAR（O-255固定长度）,VARCHAR（O-255可变长度））
数值类型（INT（整数型）、FLOAT（浮点型）)
日期和时间类型（DATE（年月日）、TIME（时分秒））

1.4 MySQL约束类型

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

主键约束 primary key:
主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也
不允许出现空值。每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。
外键约束foreign key:
外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的
两个字段或是两个表的两个字段之间的参照关系。
唯一约束unique:
唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。唯一约束不允许出现重复的值，但是可以为多个null。同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。
非空约束not null与默认值default:
非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。Null类型特征:所有的类型的值都可以是null，包括int、float等数据类型。

1.5 MySQL锁

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

1.6 MySQL的存储引擎

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

MYISAM: 默认引擎、插入和查询速度较快，支持全文索引，不支持事务、行级锁和外键约束等功能。
INNODB: 支持事务、行级锁和外键约束等功能。
MEMORY: 工作在内存中，通过散列字段保存数据，速度快、不能永久保存数据。

1.6 事务

可以把一系列要执行的操作称为事务，而事务管理就是管理这些操作要么完全执行，要么完全不执行。

经典案例:银行转账工作，从一个账号扣款并使另一个账号增款，这两个操作要么都执行，要么都不执行。所以，应该把它们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束
时，都能保持数据一致性。

注意: mysql中并不是所有的数据引擎都支持事务管理的，只有innodb支持事务管理。

二. MySQL管理命令

2.1 MySQL增删改查的实现命令

MySQL登录及退出命令:
设置密码: mysqladmin -uroot password’123456’

登录:mysql -u用户名 -p密码 -P端口 -S套接字文件
-p 用户密码.
-h 登陆位置（主机名或ip地址）
-P 端口号(3306改了就不是了）
-S 套接字文件（/var/lib/mysql/mysql.sock)
退出命令:exit或ctrl+d

1．创建登录用户

mysql>create user zhangsan@'%'identified by'123456';
%:指任意的远程终端

2.测试用户登录

mysql -uzhangsan -p123456 -h 192.168.88.10

3．用户为自己更改密码

mysql>set password=password ('123456') ;

4 root用户为其他用户找回密码

mysql>set password for zhangsan'%'=password('123123');

5.root 找回自己的密码并修改

关闭数据库，修改主配置文件(/etc/my.cnf）添加: skip-grant-tables
 #vim /etc/my.cnf
skip-grant-tables

启动数据库，空密码登录并修改密码
update mysql.user set password=password(‘新密码’) where user='root';
或者 update mysql.user set authentication_string=password('123') where user='root';  ###centos7
删除skip-grant-tables,重启数据库验证新密码

6.创建查询数据

mysql>create database web;
mysql>show databases;

7．创建数据表

Mysql>use web;              #选择要使用的数据库
Mysql>create table user1 (id int ,name char(30));       #创建user1表，并添加id和name字段以及类型
Mysql>describe user1;                            #查看表结构（字段）

复杂一点的.
Mysql>create table a2(
->id int unsigned not null auto_increment，#字段要求为正数、且自增长、主键
->name char(30) not null default‘',        #字符型长度30字节，默认值为空格
->age int not null default 0,                  #字段默认值为0.
->primary key (id));                              #设置id为主键      
Mysql> describe a2;

8．插入数据

Mysql>insert into a2(id, name, age) values(1,‘zhangsan’,21);   #指明插入字段和数据
Mysql>select * from a2; 
Mysql>insert into a2 values (2,‘lisi’,20);           #按顺序插入指定字段。
Mysql>insert into a2 values (3,‘wangwu’);        #未声明年龄
Mysql>insert into a2 values(4,‘zhao’,19),(5,‘sun’,25);     #插入多条数据。

9．将表a2的数据复制到表a1

Mysql>select * from al ; 
Mysql>insert into al (id, name)  select id, name from a2;
#查询a2值，并写入到al.
Mysql>select * from al;

10．删除数据库

Mysql>drop database abc; 
Mysql>show databases;

11．删除数据表

Mysql>drop table a1;
Mysql>show table;

12．删除表里的数据记录

Mysql>delete from a2 where id=5;          #删除id=5的记录
Mysql>delete from a2 where age between 23 and 25;     #删除年龄在23-25之间的

注:库和表的删除用drop,记录删除用delete

13．修改表中的数据

Mysql>update a2 set age=21 where id=3;

14.修改数据表的名称

Mysql>alter table a2 rename a1;

15.修改数据表的字段类型

Mysql>describe a1; 
Mysql>alter table a1 modify name char(50); 
Mysql>describe a1;

16.修改数据表的字段类型详情

Mysql>describe a1;
Mysql>alter table a1 change name username char(50) not null default '';
Mysql>describe a1;

17．添加字段

Mysql>describe a1;
Mysql>alter table a1 add time datetime; 
Mysql>describe a1;
#添加位置默认在末尾

Mysql>alter table a1 add birthday year first;     #添加字段到第一列。
Mysql>alter table a1 add sex nchar(1) after id;    #添加到指定字段后

18．删除字段

Mysql>alter table a1 drop birthday;

19.Mysql用户授权
授予用户全部权限

Mysql>select user from mysql.user ; 
Mysql>grant all on aa.a1 to lisi@‘%’;          #给已存在用户授权。
Mysql>grant all on aa.a1 to abc@‘%’ identified by ‘123456’;        #创建用户并授权

取消abc用户的删除库、表、表中数据的权限

Mysql>revoke drop, delete on aa.a1 from abc@‘%’;      #取消删除权限（登录abc测试）
Mysql>show grants for abc@‘%’;      #查看指定用户的授权。
Mysql>show grants for lisi@‘%’;

三. MySQL索引创建

注意:
创建普通的索引，字段里的数据是可以重复的。
创建唯一索引，字段里的数据不可重复，有重复会发生报错，删除重复字段才可创建唯一索引。

3.1 创建普通索引

####用create方法创建索引####
mysql> create index index_age on info (age);      ###针对info表中，age创建索引

 ####用alter方法创建普通索引####
mysql> alter table info add index index_age (age);

####创建表的时候创建普通索引##
mysql> create table user ( \
    id int(4) not null primary key auto_increment, \
    name varchar(10) not null, \
    score decimal not null, \
    hobby int(2) not null default '1', \
    index index_scrore (score));

mysql> show index from info;    ###查看索引
mysql> show keys from info;     #查看索引
mysql> drop index index_age on info;   #删除索引

3.2 创建唯一索引

###用create unique方法，创建唯一索引###
mysql> create unique index unique_name on info (name);

###使用alter table方法创建唯一索引###
mysql> alter table info add unique index_name (name);

###使用新建表的方法创建唯一索引###
mysql> create table user1 (
    -> id int(4) not null primary key auto_increment,
    -> name varchar(10) not null,
    -> score decimal not null,
    -> hobby int(2) not null default '1',
    -> index index_scrore (score));

3.3 创建主键索引

###主键索引###
mysql> create table user2 ( \
    id int(4) not null  auto_increment, \
    name varchar(10) not null, \
    age int(3) not null, \
    primary key (id));
    
#查看主键索引###
mysql> show index from user2;

3.4 创建组合索引

mysql> create table user3 (
    -> name varchar(10) not null,
    -> age int(3) not null,
    -> sex tinyint(1) not null,
    -> index user3(name,age,sex));

####查看组合索引，发现keyname统统是一样的####
mysql> show keys from user3;

3.4 创建全文索引

##创建表方法创建全文索引##
mysql> create table user4 (
    -> id int(11) not null auto_increment,
    -> tile char(255) character set utf8 collate utf8_general_ci not null,
    -> content text character set utf8 collate utf8_general_ci not null,
    -> primary key (`id`),
    -> fulltext (content));
###alter方法创建全文索引###
mysql>alter table user4 add fulltext index_content(content);

###create方法创建全文索引###
mysql>create fulltext index index_content on user4(content);

##查看全文索引##
mysql> show keys from user4;

##删除全文索引
mysql> drop index content on user4;

四. MySQL事务

mysql>begin;   ###开始事务
mysql> insert into info (name,address,age) values ('wangwu','beijing',15);
mysql> select * from info;
mysql> savepoint a;
mysql> insert into info (name,address,age) values ('zhaoliu','beijing',16);
mysql> savepoint b;
mysql> select * from info;
mysql> rollback to a;
mysql> select * from info;
mysql> rollback to b;  ###回滚不回去
mysql>commit;   ###提交事务

五. 存储引擎

5.1 修改表的存储引擎

mysql> show engines;

###修改表的存储引擎###
mysql> alter table info engine=MyISAM;

###查看表存储引擎###
mysql> show create table info \G

5.2 修改默认存储

vi /etc/my.cnf
default-storage-engine=MyISAM

systemctl restart mysqld

mysql> show variables like '%storage_engine%';