MySQL 数据库、表的基本操作

深藏阁楼爱情的钟 2024-03-23 17:47 21阅读 0赞

**目录**

数据库

关系数据库SQL

关系数据库常用词汇

常用命令语句

数据库操作

查看数据库

创建数据库

修改数据库编码

删除数据库

数据表操作

查看数据表

创建数据表

表中数据操作

增

删

改

查

--------------------

###### 数据库 ######

> 数据库是在数据管理和程序开发过程中，一种非常重要的数据管理方法，通过数据库，可以非常方便的对数据进行管理操作  
> 数据库是数据的仓库，数据库按照一定的数据格式，结构来存储数据，方便数据和操作和管理。

###### **关系数据库****SQL**  ######

> 关系数据库，是建立在关系数据库模型基础上的数据库，借助于集合代数等概念和方法来处理数据库中的数据，同时也是一个被组织成一组拥有正式描述性的表格，该形式的表格作用的实质是装载着数据项的特殊收集体，这些表格中的数据能以许多不同的方式被存取或重新召集而不需要重新组织数据库表格。关系数据库的定义造成元数据的一张表格或造成表格、列、范围和约束的正式描述。每个表格（有时被称为一个关系）包含用列表示的一个或更多的数据种类。 每行包含一个唯一的数据实体，这些数据是被列定义的种类。当创造一个关系数据库的时候，你能定义数据列的可能值的范围和可能应用于那个数据值的进一步约束。而 
> 
> SQL 语言是标准用户和应用程序到关系数据库的接口。其优势是容易扩充，且在最初的数据库创造之后，一个新的数据种类能被添加而不需要修改所有的现有应用软件。主流的关系数据库有oracle 、 db2 、sqlserver、 sybase 、 mysql 、 postgresql 、 mysql 分支等。

###### **关系数据库常用词汇** ######

*  SQL 结构化查询语言（Structured Query Language）简称SQL 
 *  DBA 数据库管理员（Database Administrator，简称DBA） 
 *  DB 数据库 
 *  DBMS 数据库管理系统 
 *  DBS 数据库系统 
 *  DDL 关系数据库 数据定义语言 语言DDL(Data Definition Language) 
 *  DML（data manipulation language）：它们是SELECT、UPDATE、INSERT、DELETE，就像它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言 
 *  DDL（data definition language）：DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用 
 *  DCL（Data Control Language）：是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db\_owner或db\_securityadmin等人员才有权力执行DCL 
 *  RDBMS：关系数据库管理系统 Relational Database Management System

###### 常用命令语句 ######

1.  查询 MySQL 版本号； MySQL 安装目录路径； MySQL 数据文件所在目录路径；MySQL 端口号； MySQL 主机名称。
2.  查询用户所属主机名称；用户名称；用户密码哈希值（不显示明文）；用户使用的插件名称。
3.  查询当前使用的数据库名称；MySQL 版本号；当前日期时间；当前用户名称；当前日期；当前时间；当前日期时间的 Unix 时间戳格式。
4.  查询 MySQL 支持的存储引擎列表。
5.  查询 MySQL 支持的字符集列表。
6.  查询 MySQL 设置的与字符相关的变量。
7.  查看当前连接人数。
8.  查询当前正在休眠或关闭的线程数量。
9.  查看当前各用户连接数据库服务器的数量。
10. 查询当前各个用户连接到 MySQL 服务器的线程数量，并按照线程数从大到小排序，限制输出前 20 条记录。

SELECT @@VERSION, @@BASEDIR, @@DATADIR, @@PORT, @@HOSTNAME;
    -- SELECT @@VERSION: 查询 MySQL 版本号；
    -- SELECT @@BASEDIR: 查询 MySQL 安装目录路径；
    -- SELECT @@DATADIR: 查询 MySQL 数据文件所在目录路径；
    -- SELECT @@PORT: 查询 MySQL 端口号；
    -- SELECT @@HOSTNAME: 查询 MySQL 主机名称。
    
    
    SELECT HOST, USER, AUTHENTICATION_STRING, PLUGIN FROM mysql.USER;
    -- SELECT HOST: 查询用户所属主机名称；
    -- SELECT USER: 查询用户名称；
    -- SELECT AUTHENTICATION_STRING: 查询用户密码哈希值（不显示明文）；
    -- SELECT PLUGIN: 查询用户使用的插件名称。
    
    
    SELECT DATABASE(), VERSION(), NOW(), CURRENT_USER(), CURDATE(), CURTIME(), UNIX_TIMESTAMP();
    -- SELECT DATABASE(): 查询当前使用的数据库名称；
    -- SELECT VERSION(): 查询 MySQL 版本号；
    -- SELECT NOW(): 查询当前日期时间；
    -- SELECT CURRENT_USER(): 查询当前用户名称；
    -- SELECT CURDATE(): 查询当前日期；
    -- SELECT CURTIME(): 查询当前时间；
    -- SELECT UNIX_TIMESTAMP(): 查询当前日期时间的 Unix 时间戳格式。
    
    
    SHOW ENGINES;
    -- 查询 MySQL 支持的存储引擎列表。
    
    
    SHOW CHARSET;
    -- 查询 MySQL 支持的字符集列表。
    
    
    SHOW VARIABLES LIKE '%CHAR%';
    -- 查询 MySQL 设置的与字符相关的变量。
    
    
    show status like 'threads%';
    -- 查看当前连接人数
    
    SELECT COUNT(\*) AS `num` FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE = 'sleep' OR state='closing';
    -- 查询当前正在休眠或关闭的线程数量。
    
    select user,count(*) from information_schema.processlist group by user;
    -- 查看当前各用户连接数据库服务器的数量
    
    SELECT user, count(*) as num FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY user ORDER BY num DESC LIMIT 20;
    -- 查询当前各个用户连接到 MySQL 服务器的线程数量，并按照线程数从大到小排序，限制输出前 20 条记录。

###### 数据库操作 ######

###### 查看数据库 ######

显示所有数据库

show databases;
    -- 同义词
    show schemas;
    --模糊查询 like后面可以加速特征%
    show databases like 'db%';

使用/切换数据库

use 数据库名
    use 数据库名;
    >>>use employees;
    #  “;”不做强制要求

显示当前使用的数据库

select database();

###### 创建数据库 ######

创建

create database 数据库名;
    >>>create database testdb;
    
    create database 数据库名 charset 编码格式;
    >>> create database testdb charset utf8;
    
    create database 数据库名 character set 编码格式;
    >>> create database testdb character set utf8;

查看创建数据库的信息

show create database 数据库名;
    >>>show create database employees;

###### 修改数据库编码 ######

使用alter命令

alter database 数据库名 character set 编码格式;
    >>>alter database employees character set utf8;

###### 删除数据库 ######

drop database 数据库名;
    >>>drop database testdb;

###### 数据表操作 ######

###### 查看数据表 ######

查看所选数据库中所有的表

show tables;

显示表结构

desc 表名;
    >>>desc student;

###### 创建数据表 ######

创建新的表

create table 表名(
      字段1 字段类型,
      字段2 字段类型,
      字段3 字段类型……);
    >>>create table student(id int, name char(20), age int);

显示创建表时的信息

show create table 表名;
    >>>show create table student;  #表格显示信息
    >>>show create table student \G;  # 垂直显示信息

修改表结构

alter table 表名 modify 字段名 类型;
    >>>alter table student modify name varchar(20);

增加字段

alter table 表名 add 字段名 数据类型
    >>>alter table student add gender char(4);

删除字段

alter table 表名 drop 字段名;
    >>> alter table student drop gender;

修改字段类型

alter table 表名 modify 字段名 类型;
    >>>alter table student modify name varchar(20);

修改字段类型并命名

alter table 表名 change 原字段名 新字段名 类型;
    >>>alter table student change id number smallint;

###### 表中数据操作 ######

###### 增 ######

插入数据

insert into 表名 [(字段名列表, ...)] values(值列表, ...)
    >>>insert into student values(1,'tom',20);
    
    # 插入指定字段: 可以插入指定的字段，指定字段顺序和列中顺序可以不同，值顺序要和指定的列顺序相同。
    >>>insert into student(age,name) values(20,'jack');
    
    # 插入多条数据
    >>>insert into student values(2,'rose',20),(3,'tony',22);

###### **删** ######

删除数据

# 删除全部数据 
    truncate 表名;
    >>>truncate stu;
    
    # 删除满足条件的数据
    delete from 表名 where 字段名 = 数据;
    >>>delete from stu where snumber = 1;

###### 改 ######

修改数据

update 表名 set 字段=值 [条件]
    
    # 更新所有的数据
    >>>update student set age=25;
    
    # 更新满足条件的数据
    >>>update student set name='mike' where name='tony';

###### 查 ######

数据库的查询命令较多，后续将在另一篇文章更新。