mysql tdb数据库_MYSQL数据库基础

最新推荐文章于 2022-01-14 11:59:31 发布

慕北颖

最新推荐文章于 2022-01-14 11:59:31 发布

阅读量1.5k

点赞数

文章标签： mysql tdb数据库

本文链接：https://blog.csdn.net/weixin_29189987/article/details/113435372

版权

数据库基础

DBMS(数据库管理系统)

DBMS 是“数据库管理系统”的简称(全称 DataBase Management System)。实际上它可以对多个数据库进行管理，所以你可以理解为 DBMS = 多个数据库(DB) + 管理程序.

Oracle,MySQL都是数据库管理系统，即DBMS

常见DBMS

DBMS类型

关系型数据库

关系型这是最常见的经典的数据库模式。关系数据库管理系统(RDBMS)，是基于集合理论的系统，实现方式是具有行和列的二维表

键值型数据库

KV将简单的键映射到(可能)更复杂的值，就像一个巨大的哈希表。由于它们相对简单，因此这种类型的数据库实现起来最灵活。哈希查找速度快，在Redis的例子中就是这样，速度是其主要的关注。哈希查找也容易分布化，所以Riak利用这一事实，侧重于简单管理的集群。

列型数据库

不同于前两个数据库，列型数据库按列存储类似的数据，而不是按行存储数据。列的添加很容易，版本控制是小菜一碟，并且对于空值没有存储成本。我们看到了HBase是对这一类型的经典实现

文档型数据库

文档数据库适合于涉及高度可变领域的问题。当你事先不知道你的数据看起来究竟像什么样子，文档型数据库是一个不错的选择

SQL语句

结构化查询语言(sql)

sql语句三种类型

DDL：数据定义语言

Create

drop

Alter等

DML：数据操纵语言(增删改查等)

update(改)

insert(增)

select(查)

delete(删)

DCL：数据控制语言(权限等)

grant(授权)

revoke(取消授权)

关系型数据库的常见组件

数据库：database

表table:(行，列)

索引：index

视图：view

用户：user

权限：privilege

存储过程: procedure

触发器: trigger

安装及初始化mariadb

[root@localhost ~]# yum install -y mariadb mariadb-common mariadb-devel meriadb-server

[root@localhost ~]# systemctl enable --now mariadb

[root@localhost ~]# mysql -uroot -p #root账户登录

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

......

设置密码

MariaDB [(none)]> set password = password('123456');

Query OK, 0 rows affected (0.000 sec) # password()是加密函数，可以让密码进行加密。

mysql服务的组成

客户端

mysql：交互式客户端程序

mysql_secure_installation: 安全初始化

mysqldump：备份工具

navicat 导航猫，图形化客户端工具

workbench 跨平台图形化客户端工具

服务端

mysqld/mariadb

mysql交互工具使用

mysql

-u：指定用户名，默认是root

-p：指定密码

-V：显示版本号

-e：不登陆的情况下执行sql语句后退出

-h：指定服务器主机。默认localhost.

DDL操作

创建数据库

CREATE DATABASE student;

IF NOT EXISTS：如果存在就创建。不存在给予警告。不会ERROR。

MariaDB [(none)]> create database sawyer;

Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> create database if not exists sawyer;

Query OK, 0 rows affected, 1 warning (0.000 sec) #db_sawyer已经存在，但是加了ifnotexists后并不会报错

查看数据库

SHOW DATABASES;

删除数据库

DROP DATABASE student;

IF EXISTS：如果存在删除，如果不存在也不会ERROR。

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sawyer |

| sy |

+--------------------+

5 rows in set (0.000 sec)

进入数据库

USE databasename;

MariaDB [(none)]> use sawyer;

Database changed

MariaDB [sawyer]>

创建表

CREATE TABLE tablename(columnname1 datastructure1(长度) not null,columname2 ......)；

create table student(id int(11) not null primary key auto_increment,name varchar(100) not null,age tinyint(4))

MariaDB [sawyer]> desc student;

+-------+--------------+------+-----+---------+----------------+

+-------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | tinyint(4) | YES | | NULL | |

+-------+--------------+------+-----+---------+----------------+

3 rows in set (0.001 sec)

数据库常见数据结构

数据结构名

举例

字符型数据

char,varchar,text

日期时间数据

datatime,smalldatatime

整数型数据

int,tinyint(0-255)

精确小数型数据

decimal,numeric

近似数值类型

float,real

位类型数据

常用于性别等

删除表

DROP TABLE tablename;

MariaDB [sy]> drop table student; # 删除sy下的student这张表

Query OK, 0 rows affected (0.003 sec)

查看数据库内所有表清单

SHOW TABLES;

MariaDB [sawyer]> show tables;

+------------------+

| Tables_in_sawyer |

+------------------+

| student |

+------------------+

1 row in set (0.000 sec)

查看表结构

DESC tablename;

MariaDB [sawyer]> desc student;

+-------+--------------+------+-----+---------+----------------+

+-------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | tinyint(4) | YES | | NULL | |

+-------+--------------+------+-----+---------+----------------+

3 rows in set (0.001 sec)

注：\G代替；来更换查看方式

查看创建表的过程

SHOW CREATE TABLE databasename.tablename;

MariaDB [sawyer]> show create table student\G

*************************** 1. row ***************************

Table: student

Create Table: CREATE TABLE `student` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(100) NOT NULL,

`age` tinyint(4) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.000 sec)

查看表的状态

SHOW TABLE STATUS LIKE 'tablename'\G

MariaDB [sawyer]> show table status like 'student'\G

*************************** 1. row ***************************

Name: student

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 0

Avg_row_length: 0

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: 1

Create_time: 2020-10-20 12:25:23

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

Max_index_length: 0

Temporary: N

1 row in set (0.001 sec)

通配符

like 'table%' ：匹配table开头，其后任意长度字符

like 'table_':匹配table开头，其后一个长度字符

修改表的结构

ALTER TABLE tablename add/drop/modify column 数据类型选项;

MariaDB [sawyer]> alter table student modify age tinyint(5);

Query OK, 0 rows affected (0.001 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [sawyer]> desc student;

+-------+--------------+------+-----+---------+----------------+

+-------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | tinyint(5) | YES | | NULL | |

+-------+--------------+------+-----+---------+----------------+

3 rows in set (0.001 sec)

用户的创建/删除

格式：username@客户端ip或%或_

表示只能从指定客户端Ip通过username远程登录到数据库服务器中。%表示允许所有主机。

例子：CREATE/DROP USER 'username@ip' [identified by '123456']

MariaDB [(none)]> create user 'test@192.168.197.133';

Query OK, 0 rows affected (0.000 sec)

##客户端登录

[root@localhost ~]# mysql -u'test@192.168.197.133' -h192.168.197.132

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 19

Server version: 10.3.17-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

DCL操作

授权grant

指定操作对象：

表示方式

意义

*.*

所有库的所有表

db_name

指定库的所有表

db_name.tablename

指定库的指定表

权限类型：

名称|含义|

ALL|所有的权限|

INSERT|新增的权限|

DELETE|删除内容的权限|

UPDATE|修改内容的权限|

SELECT|查看内容的权限|

GRANT ALL ON *.* TO 'test@localhost' identified by '123456';

#test账户默认查看不到数据库信息

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

+--------------------+

1 row in set (0.001 sec)

## 客户端给test@192.168.197.133增加student表的查询权限

MariaDB [(none)]> grant select on sawyer.student to 'test@192.168.197.133';

Query OK, 0 rows affected (0.000 sec)

## 刷新权限

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.000 sec)

## test再次查看

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| sawyer |

+--------------------+

2 rows in set (0.001 sec)

查看权限

show grants;(默认查看当前用户的权限)

show grants for tom@ip;(查看指定用户的权限)

## root登录，所以默认查看root的权限。

MariaDB [(none)]> show grants;

+----------------------------------------------------------------------------------------------------------------------------------------+

| Grants for root@localhost |

+----------------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |

| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |

+----------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.000 sec)

## 查看指定用户的权限

MariaDB [(none)]> show grants for 'test@192.168.197.133';

+------------------------------------------------------------------+

| Grants for test@192.168.197.133@% |

+------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'test@192.168.197.133'@'%' |

| GRANT SELECT ON `sawyer`.`student` TO 'test@192.168.197.133'@'%' |

+------------------------------------------------------------------+

2 rows in set (0.000 sec)

移除权限(revoke)

revoke select on databasename.tablename from username@ip;

MariaDB [(none)]> revoke select on sawyer.student from 'test@192.168.197.133';

Query OK, 0 rows affected (0.001 sec)

DML操作

insert(新增)

格式一：添加单行内容

insert tablename value(value1,value2,value3);

格式二：添加多行内容

insert tablename values(value1,value2,value3),(value1,value2,value3),......;

格式三: 添加部分字段的值，其余默认为null。

insert tablename(column1,column2) value(value1,value2);

delete(删除)

语法：

DELETE FROM tablename [WHERE column1=value];

update(修改)

语法：

UPDATE tablename SET column1=value [WHERE clause]

实例

1.创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段(id，name，age)。

MariaDB [sawyer]> create table student(id int(11) not null primary key auto_increment,name varchar(100) not null,age tinyint(4));

Query OK, 0 rows affected (0.006 sec)

## 查看

MariaDB [sawyer]> desc student;

+-------+--------------+------+-----+---------+----------------+

+-------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | tinyint(4) | YES | | NULL | |

+-------+--------------+------+-----+---------+----------------+

3 rows in set (0.001 sec)

2.往新建的student表中插入数据

MariaDB [sawyer]> insert student values(1,'tom',20),(2,'jerry',23),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(6,'zhangshan',20),(7,'lisi',null),(8,'chenshuo',10),(9,'wangwu',3),(10,'qiuyi',15),(11,'qiuxiaotian',20);

Query OK, 11 rows affected (0.001 sec)

Records: 11 Duplicates: 0 Warnings: 0

#查看效果

MariaDB [sawyer]> select * from student;

+----+-------------+------+

| id | name | age |

+----+-------------+------+

| 1 | tom | 20 |

| 2 | jerry | 23 |

| 3 | wangqing | 25 |

| 4 | sean | 28 |

| 5 | zhangshan | 26 |

| 6 | zhangshan | 20 |

| 7 | lisi | NULL |

| 8 | chenshuo | 10 |

| 9 | wangwu | 3 |

| 10 | qiuyi | 15 |

| 11 | qiuxiaotian | 20 |

+----+-------------+------+

11 rows in set (0.000 sec)

3.修改lisi的年龄为50

MariaDB [sawyer]> update student set age=50 where name='lisi';

Query OK, 1 row affected (0.001 sec)

Rows matched: 1 Changed: 1 Warnings: 0

#查看

MariaDB [sawyer]> select * from student;

+----+-------------+------+

| id | name | age |

+----+-------------+------+

| 1 | tom | 20 |

| 2 | jerry | 23 |

| 3 | wangqing | 25 |

| 4 | sean | 28 |

| 5 | zhangshan | 26 |

| 6 | zhangshan | 20 |

| 7 | lisi | 50 |

| 8 | chenshuo | 10 |

| 9 | wangwu | 3 |

| 10 | qiuyi | 15 |

| 11 | qiuxiaotian | 20 |

+----+-------------+------+

4.查询student表中名字叫zhangshan的记录

MariaDB [sawyer]> select * from student where name='zhangshan';

+----+-----------+------+

| id | name | age |

+----+-----------+------+

| 5 | zhangshan | 26 |

| 6 | zhangshan | 20 |

+----+-----------+------+

2 rows in set (0.000 sec)

5.查询student表中名字叫zhangshan且年龄大于20岁的记录

MariaDB [sawyer]> select * from student where name='zhangshan' and age>20;

+----+-----------+------+

| id | name | age |

+----+-----------+------+

| 5 | zhangshan | 26 |

+----+-----------+------+

1 row in set (0.001 sec)

6.查询student表中年龄在23到30之间的记录

MariaDB [sawyer]> select * from student where age between 23 and 30;

+----+-----------+------+

| id | name | age |

+----+-----------+------+

| 2 | jerry | 23 |

| 3 | wangqing | 25 |

| 4 | sean | 28 |

| 5 | zhangshan | 26 |

+----+-----------+------+

4 rows in set (0.000 sec)

7.修改wangwu的年龄为100

MariaDB [sawyer]> update student set age=100 where name='wangwu';

Query OK, 1 row affected (0.001 sec)

Rows matched: 1 Changed: 1 Warnings: 0

# 查看

MariaDB [sawyer]> select * from student;

+----+-------------+------+

| id | name | age |

+----+-------------+------+

| 1 | tom | 20 |

| 2 | jerry | 23 |

| 3 | wangqing | 25 |

| 4 | sean | 28 |

| 5 | zhangshan | 26 |

| 6 | zhangshan | 20 |

| 7 | lisi | 50 |

| 8 | chenshuo | 10 |

| 9 | wangwu | 100 |

| 10 | qiuyi | 15 |

| 11 | qiuxiaotian | 20 |

+----+-------------+------+

11 rows in set (0.000 sec)

8.删除student中名字叫zhangshan且年龄小于等于20的记录

MariaDB [sawyer]> delete from student where name='zhangshan' and age<=20;

Query OK, 1 row affected (0.001 sec)

MariaDB [sawyer]> select * from student;

+----+-------------+------+

| id | name | age |

+----+-------------+------+

| 1 | tom | 20 |

| 2 | jerry | 23 |

| 3 | wangqing | 25 |

| 4 | sean | 28 |

| 5 | zhangshan | 26 |

| 7 | lisi | 50 |

| 8 | chenshuo | 10 |

| 9 | wangwu | 100 |

| 10 | qiuyi | 15 |

| 11 | qiuxiaotian | 20 |

+----+-------------+------+

10 rows in set (0.000 sec)

慕北颖

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql tdb数据库_MYSQL数据库基础

数据库基础DBMS(数据库管理系统)DBMS 是“数据库管理系统”的简称(全称 DataBase Management System)。实际上它可以对多个数据库进行管理，所以你可以理解为 DBMS = 多个数据库(DB) + 管理程序.Oracle,MySQL都是数据库管理系统，即DBMS常见DBMSDBMS类型关系型数据库关系型这是最常见的经典的数据库模式。关系数据库管理系统(RDBMS)，是基...
复制链接

扫一扫

mysql tdb数据库_MYSQL数据库基础

“相关推荐”对你有帮助么？