MySQL数据库基本操作

本是古典何须时尚 2023-10-05 16:18 28阅读 0赞

#### 文章目录 ####

*  MySQL数据库基本操作
 *  一、SQL语句概述
 *   *  1.1 SQL语言
     *  1.2 SQL分类
 *  二、具体操作
 *   *  说明：
     *  2.1 DDL
     *   *  2.1.1 DDL语句创建库，表的命令
         *  2.1.2 DDL语句删除表库命令
         *  2.1.3 修改表的属性
     *  2.2 DML
     *   *  2.2.1 DML语句使用
         *  2.2.2 向数据表中插入新的数据记录
         *  2.2.3 修改，更新数据表中的数据记录
         *  2.2.4 在数据表中删除指定的数据记录语句
     *  2.2.5：清空表
     *  2.3 DQL操作命令
     *   *  2.3.1 DQL语句的作用
         *  2.3.2 不指定条件查询语句
         *  2.3.3 指定条件查询的语句
     *  2.4 DCL
     *   *  2.4.1 DCL语句使用
         *  2.4.2 设置用户权限的命令
         *  2.4.3 查看用户权限的命令
         *  2.4.4 撤销用户权限的命令
     *  2.5 创建临时表
     *  2.6 克隆表
     *  三、创建、删除用户
     *  3.1 创建用户
     *  3.2 删除用户

## MySQL数据库基本操作 ##

## 一、SQL语句概述 ##

### 1.1 SQL语言 ###

Structured Query Language，即结构化查询语言  
关系型数据库标准语言  
用来维护和管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能

### 1.2 SQL分类 ###

1.  DDL：数据定义语言 （Data Definition Language)  
    如：CREATE、ALTER、DROP。
2.  DML：数据操纵语言 （Data Manipulation Language)  
    如：INSERT、UPDATA、DELETE。
3.  DQL：数据查询语言 （Data Query Language)  
    如：SELECT
4.  DCL：数据控制语言 （Data Control Language)  
    如：COMMIT、ROLLBACK、GRANT、REVOKE。

## 二、具体操作 ##

### 说明： ###

1.  char的长度是不可变的，而varchar的长度是可变的  
    字段b:类型char(10), 值为：abc, 存储为：abc （abc+7个空格）  
    字段d：类型varchar(10), 值为：abc, 存储为：abc(自动变为3个长度）
2.  var和varchar都超出长度自动截取
3.  var(10)和varchar(10)都表示能存10个字符，无论是数字，字母还是汉字。
4.  char最多可以存放255个字符，varchar的最大长度为65535个字节。

### 2.1 DDL ###

#### 2.1.1 DDL语句创建库，表的命令 ####

*  创建数据库：create database 数据库名
 *  创建数据表：create table 表名（字段定义…）
 *  create table 表名 (字段01名称 字段01类型 字段01约束,字段02名称 字段02类型 字段02约束,…)存储引擎,字符集

mysql> create database school;
    mysql> use school;
    mysql> create table giao (id int(4) not null primary key auto_increment, name varchar(10) not null, age int(3) not null );

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NDgwMDIw_size_1_color_FFFFFF_t_70]

**注意**: int (4) 整型 0000-9999

double 浮点型 8字节

decimal(5,2) 有效数字是5位,小数点后面保留两位 100.00 099.50

float 单精度浮点 4字节

char (10) 固定长度字符串

varchar (50) 可变长度字符串

非空约束: 内容不允许为空

主键约束: 非空且唯一 标识

默认值: 假如没有填数据,默认预先设定的值填写

自增特性: auto\_increment

存储引擎: engine myisam innodb

字符集: UTF8

#### 2.1.2 DDL语句删除表库命令 ####

删除指定的数据表：drop table \[数据库名.\]表名

删除指定的数据库：drop database 数据库名

mysql> drop table school.giao;	'//删除表'

mysql> drop database school;	'//删除库'

#### 2.1.3 修改表的属性 ####

alter table：  修改表的属性
    alter table t2 add id int first;          增加一列成为第一列 al
    alter table t2 add id2 int after id;        在id后面增加一列叫id2

![在这里插入图片描述][2020122318525611.png]

alter table t2 drop id2;    删除id2这个列 
    alter table t2 change id ID bigint;    修改列名和数据类型 
    alter table t2 modify ID int;    修改列的数据类型

![在这里插入图片描述][20201223185649388.png]

show engines;    查看数据库有哪些存储引擎 
    alter table t20 engine MyISAM;    修改表的存储引擎 
    show create table t20;    查看修改存储引擎是否成功 
    alter table t20 default charset=utf8;    修改表的语言编码

### 2.2 DML ###

#### 2.2.1 DML语句使用 ####

DML语句用于对表中的数据进行管理  
包括以下操作

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

#### 2.2.2 向数据表中插入新的数据记录 ####

insert into 表名（字段1，字段2，…）values（字段1的值，字段2的值，…）
    mysql> create table t3(id int(4) not null primary key auto_increment, name varchar(10) not null, age int(4) not null);
    mysql> insert into t3(name,age) values('zhangsan',18),('lisi',28);

![在这里插入图片描述][20201223191418518.png]

#### 2.2.3 修改，更新数据表中的数据记录 ####

update 表名 set 字段名 1=值1[，字段名2=值2] where条件表达式
    mysql> select * from t3;
    +----+----------+-----+
    | id | name     | age |
    +----+----------+-----+
    |  1 | zhangsan |  18 |
    |  2 | lisi     |  28 |
    +----+----------+-----+
    2 rows in set (0.00 sec)
    mysql> update t3 set name='wangwu' where id=2;
    Query OK, 1 row affected (0.06 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from t3;
    +----+----------+-----+
    | id | name     | age |
    +----+----------+-----+
    |  1 | zhangsan |  18 |
    |  2 | wangwu   |  28 |
    +----+----------+-----+
    2 rows in set (0.01 sec)

#### 2.2.4 在数据表中删除指定的数据记录语句 ####

delete from 表名 where条件表达式
    mysql> delete from kgc where id=2;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from kgc;
    +----+----------+-----+
    | id | name     | age |
    +----+----------+-----+
    |  1 | zhangsan |  18 |
    +----+----------+-----+
    1 row in set (0.00 sec)

不带where条件的语句表示删除表中所有记录（谨慎操作）

mysql> delete from kgc;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from kgc;
    Empty set (0.00 sec)

### 2.2.5：清空表 ###

drop 库.表;					//删除表,删除记录同时删除表结构
    delete from tablename;      //删除表的记录
    truncate table tmp;			//删除所有记录,保留表的结构

### 2.3 DQL操作命令 ###

#### 2.3.1 DQL语句的作用 ####

DQL是数据查询语句，只有一条：SELECT  
用于从数据表中查找符合条件的**数据记录**

#### 2.3.2 不指定条件查询语句 ####

SELECT字段名1，字段名2…FROM表名

mysql> select id,name from t3;

+----+----------+
    | id | name     |
    +----+----------+
    |  1 | zhangsan |
    |  2 | wangwu   |
    +----+----------+
    2 rows in set (0.00 sec)

#### 2.3.3 指定条件查询的语句 ####

SELECT字段名1，字段名2…FROM表名 WHERE条件表达式

mysql> select * from t3 where id=1;
    +----+----------+-----+
    | id | name     | age |
    +----+----------+-----+
    |  1 | zhangsan |  18 |
    +----+----------+-----+
    1 row in set (0.00 sec)

### 2.4 DCL ###

#### 2.4.1 DCL语句使用 ####

设置或查看用户的权限，或者创建用户

#### 2.4.2 设置用户权限的命令 ####

*  若用户已存在，则更改用户密码
 *  若用户不存在，则新建用户
 *  GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 \[IDENTIFIED BY ‘密码′ \]

mysql> grant all privileges on *.* to 'root'@'%' identified by 'abc123' with grant option;
    '//all privileges：所有权限，%：remote终端'
    Query OK, 0 rows affected, 1 warning (0.00 sec)

#### 2.4.3 查看用户权限的命令 ####

SHOW GRANTS FOR 用户名@来源地址

mysql> show grants for 'root'@'%';
    +-------------------------------------------------------------+
    | Grants for root@%                                           |
    +-------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
    +-------------------------------------------------------------+
    1 row in set (0.00 sec)

#### 2.4.4 撤销用户权限的命令 ####

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址

mysql> revoke all privileges on *.* from 'root'@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show grants for 'root'@'%';
    +----------------------------------------------------+
    | Grants for root@%                                  |
    +----------------------------------------------------+
    | GRANT USAGE ON *.* TO 'root'@'%' WITH GRANT OPTION |
    +----------------------------------------------------+
    1 row in set (0.00 sec)

### 2.5 创建临时表 ###

mysql> create temporary table temp_kgc (id int(4) not null auto_increment,name varchar(10) not null,hobby varchar(10) not null, primary key(id))engine=innodb default charset=utf8;

mysql> insert into temp_t3 (name,hobby) values ('tom','cat');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show tables;            //看不到临时表,其保存在硬盘内
    +------------------+
    | Tables_in_school |
    +------------------+
    | test              |
    +------------------+
    mysql> select * from temp_t3;   //能够查看临时表的内容
    +----+------+-------+
    | id | name | hobby |
    +----+------+-------+
    |  1 | tom  | cat   |
    +----+------+-------+
    mysql> exit
    Bye
    [root@mysql ~]# mysql -uroot -pabc123
    mysql> use school;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> select * from temp_t3;   //重新连接数据库,临时表丢失
    ERROR 1146 (42S02): Table 'test.temp_t3' doesn't exist
    mysql>

### 2.6 克隆表 ###

1.  like方法

从kgc完整复制表结构生成test表,再导入数据

mysql> create table duplication like t3;  //复制表结构
    mysql> insert into duplication select * from t3;	//插入数据

![在这里插入图片描述][2020122319484650.png]

1.  show create table 方法

先查看kgc表完整结构,根据此结构创建名字不同结构相同的表test ,再插入数据

mysql> show create table t3\G         '\G代表竖行显示'
    mysq*************************** 1. row ***************************
           Table: t3
    Create Table: CREATE TABLE "t3" (
      "id" int(4) NOT NULL AUTO_INCREMENT,
      "name" varchar(10) NOT NULL,
      "age" int(4) NOT NULL,
      PRIMARY KEY ("id")
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
    1 row in set (0.01 sec)
    mysql> create table duplication2 (id int(4) not null primary key auto_increment,name varchar(10) not null,age int(5) not null );
    
    mysql> insert into duplication2 (id,name,age) values (2,'wangwu','28'),('1','zhangsan','18');
    
    mysql> select * from duplication2;
    +----+----------+-----+
    | id | name     | age |
    +----+----------+-----+
    |  1 | zhangsan |  18 |
    |  2 | wangwu   |  28 |
    +----+----------+-----+
    2 rows in set (0.00 sec)

### 三、创建、删除用户 ###

### 3.1 创建用户 ###

create user user@host identified by '123456'; @'%' @ip @'network' 
    
    //说明：用户的信息保存在mysql数据库中的user表中，验证用户是否创建成功如下： 
    select user,host,'123456' from mysql.user;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NDgwMDIw_size_1_color_FFFFFF_t_70 1]

### 3.2 删除用户 ###

mysql> drop user user01@'localhost'; 删除用户
    mysql> select user from mysql.user where user='user01'; 验证用户是否删除成功
    mysql> drop user user; 默认删除该用户从任意主机登陆
    mysql> rename user u01@'instructor.example.com' to u001@'localhost'; 重命名用户名 
    mysql> show grants; 查看用户权限 
    mysql> show grants for user02@'%'; 查看指定用户的权限 
    mysql> drop user ''@'rhel6.example.com'; 删除一个匿名用户 
    mysql> delete from mysql.user where user=''; 删除mysql中的匿名用户 
    mysql> delete from mysql.user where user='root' and host='::1'; 
    mysql> flush privileges;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NDgwMDIw_size_1_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2020122318333293.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NDgwMDIw,size_1,color_FFFFFF,t_70
[2020122318525611.png]: https://img-blog.csdnimg.cn/2020122318525611.png
[20201223185649388.png]: https://img-blog.csdnimg.cn/20201223185649388.png
[20201223191418518.png]: https://img-blog.csdnimg.cn/20201223191418518.png
[2020122319484650.png]: https://img-blog.csdnimg.cn/2020122319484650.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NDgwMDIw_size_1_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201223201111566.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NDgwMDIw,size_1,color_FFFFFF,t_70