当前位置: 首页>数据库>正文

mariadb rownum函数 mariadb数据类型

数据库简介

数据库--即电子文件柜,用户可以对文件中的数据进行增,删,改,查等操作。

数据库分类

  1. 关系型数据库
    关系型数据库管理系统(Relational Database Management System,RDBMS)
  2. 非关系型数据库
    noSQL(Not Only SQL )

关系型数据库与非关系型数据库的区别

关系型数据库

  1. 数据以二维表格的形式存储,可以通过外键关联,一致性强;
  2. 数据存储在磁盘中,每次读取数据都是一次I/O请求,数度慢,系统开销大,高并发的情况下很影响性能
  3. 使用方便,易于维护

非关系型数据库

  1. 数据以key-value形式存储,只适合存储以一些简单的数据;不适合持久存储海量数据;
  2. 数据存储在内存中,关联性弱,读取速度快;

所以什么时候选型要用关系型数据库呢?

  1. 需要事务数据库
  2. 有复杂查询的数据库

常见关系型数据库管理系统

  • Oracle
  • Mysql
  • MariaDB
  • Microsoft SQL Server
  • db2
  • .......

常见非关系型数据库

  • Redis
  • MongoDB
  • Amazon
  • .......

MariaDB

MariaDB 数据库管理系统是Mysql的一个分支,是RDMS,主要由开源社区来维护;由于SUN被甲骨文公司收购,Mysql的所有权归oracle所有,不再开源。MySQL之父----Monty在2009年发起了MariaDB 开源项目。

MariaDB安装

MariaDB 10.3版本安装源

[mariadb]
name = MariaDB
baseurl = http://mirrors.ustc.edu.cn/mariadb/yum/10.3/centos7-amd64/
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

MariaDB数据类型

常用数据类型

  • 整数型:int,bit
  • 小数型:decimal #decimal(5,2)
  • 字符串:varchar,char
  • 时间:date,time,datetime
  • 枚举类型:enum

约束

  • primary key:主键
  • not null:不为空
  • unique:字段不重复
  • default:默认
  • foreign key:外键,对关系字段进行约束,当关系字段填写值时,会到关联的表中查询此值是否存在,如果存在则填写成功,如果不存在则填写失败并抛出异常。

数值类型常用

类型

字节大小

有符号范围(signed)

无符号范围(unsigned)

tinyint

1

-128~127

0~255

smallint

2

-32768~32767

0~65535

mediumint

3

-8388608~8388607

0~16777215

int/integer

4

-2147483648~2147483647

0~4294967295

bigint

8

-9223372036854775808~

9223372036854775807

0~18446744073709551615

字符串

类型

字节大小

示例

char

0~255

不能伸缩(定多少我就取多少)

varchar

0~255

可以伸缩(要浪费一个字节指定字符长度)

text

0~65535

大文本

日期时间类型

类型

字节大小

示例

date

4

'2019-01-01'

time

3

'12:30:30'

datetime

8

'2019-01-01 12:30:30'

timestamp

4

'1970-01-01 00:00:01'UTC~'2038-01-01 00:00:01'UTC

SQL语句(命令)

数据库管理

  • 忘记数据库的root密码怎么办?
vim /etc/my.conf.d/server.conf

[server]
skip-grant-tables   #添加



#重启数据库

#无密码登陆数据库
  • 创建或修改密码初始化数据库
╭─root@localhost.localdomain ~  
╰─➤  mysql_secure_installation
  • 进入RDBMS
╭─root@localhost.localdomain ~  
╰─➤  mysql -uroot -p123456     #SQLuser和passward
  • 退出RDBMS
MariaDB [mysql]> quit
Bye
╭─root@localhost.localdomain ~  
╰─➤
  • 查看所有数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
  • 创建数据库
MariaDB [(none)]> create database test1;
Query OK, 1 row affected (0.001 sec)

#指定字符编码
MariaDB [(none)]> create database test2 character set utf8;
Query OK, 1 row affected (0.003 sec)
  • 设置与更改用户密码
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword')

#如果是当前登陆用户
SET PASSWORD = PASSWORD("newpassword");
  • 查看创建数据库的状态
MariaDB [test1]> show create database test1;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
  • 修改指定数据库的字符编码
MariaDB [test1]> alter database test1 default character set=utf8;
Query OK, 1 row affected (0.002 sec)
  • 使用数据库
MariaDB [(none)]> use test1;
Database changed
MariaDB [test1]>
  • 删除数据库
MariaDB [test1]> drop database test2;
Query OK, 0 rows affected (0.005 sec)

用户管理

  • 查看当前用户
MariaDB [test1]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.002 sec)
  • 查看用户信息

用户信息储存在mysql数据库的user表

MariaDB [mysql]> select Host,User,Password from user;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1       | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
  • 在localhost主机上所有库所有表上给user250用户所有权限,认证密码'123456'
MariaDB [mysql]>-- grant 权限 on 库名.表 to 用户@主机 identified  by  '密码';

MariaDB [mysql]> grant all privileges on *.* to user250@localhost identified by '123456';
Query OK, 0 rows affected (0.003 sec)
  • 给root用户在所有主机上的所有权限(用于第三方登录数据库)(端口号:3306)
MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by '123456';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> use mysql;
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
MariaDB [mysql]> select host,user,password from user;
+-----------+---------+-------------------------------------------+
| host      | user    | password                                  |
+-----------+---------+-------------------------------------------+
| localhost | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1       | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| %         | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | user250 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------+-------------------------------------------+
  • 给user250用户添加 查询 的权限
MariaDB [mysql]> grant select on *.* to user250@localhost ;
Query OK, 0 rows affected (0.000 sec)

select:查询权限
create:创建权限
update:更新权限
delete:删除权限

  • 查看user250的权限
MariaDB [(none)]>-- show grants for 用户@主机;

MariaDB [(none)]> show grants for user250@localhost;
  • 收回user250用户的所有权限
MariaDB [(none)]> revoke all on *.* from user250@localhost;
Query OK, 0 rows affected (0.001 sec)

表结构操作

  • 显示当前时间
MariaDB [(none)]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-07-03 07:00:39 |
+---------------------+
  • 创建葫芦娃表
MariaDB [test1]> create table huluwa (
    -> id int unsigned auto_increment primary key,
    -> name varchar(10),
    -> age tinyint unsigned,
    -> high decimal(5,2),
    -> gender enum('boy','girl','unknow')default 'unknow'
    -> );
Query OK, 0 rows affected (0.206 sec)
  • 查看所有表
MariaDB [test1]> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| huluwa          |
+-----------------+
  • 查看表结构
MariaDB [test1]> desc huluwa;
+--------+-----------------------------+------+-----+---------+----------------+
| Field  | Type                        | Null | Key | Default | Extra          |
+--------+-----------------------------+------+-----+---------+----------------+
| id     | int(10) unsigned            | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10)                 | YES  |     | NULL    |                |
| age    | tinyint(3) unsigned         | YES  |     | NULL    |                |
| high   | decimal(5,2)                | YES  |     | NULL    |                |
| gender | enum('boy','girl','unknow') | YES  |     | unknow  |                |
+--------+-----------------------------+------+-----+---------+----------------+
  • 给表添加字段
MariaDB [test1]>-- alter table 表名 add 列名 类型;

MariaDB [test1]> alter table huluwa add color varchar(10);
Query OK, 0 rows affected (0.406 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • 修改表字段;不重命名(modify)
MariaDB [test1]>-- alter table 表名 modify 列名 类型及约束;

MariaDB [test1]> alter table huluwa modify name varchar(20);
Query OK, 0 rows affected (0.005 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • 修改表字段;重命名
MariaDB [test1]>-- alter table 表名 change 原名 新名 类型及约束;

MariaDB [test1]> alter table huluwa change age bir_day date;
Query OK, 0 rows affected (0.426 sec)              
Records: 0  Duplicates: 0  Warnings: 0
  • 删除字段
MariaDB [test1]>-- alter table 表名 drop 列名;

MariaDB [test1]> alter table huluwa drop color;
Query OK, 0 rows affected (0.018 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • 删除表
MariaDB [test1]>-- drop table 表名;

MariaDB [test1]> drop table huluwa;
Query OK, 0 rows affected (0.010 sec)

表数据 增,删,改

注意:bit 型数据有值只是终端显示不明显

  • 创建表
MariaDB [test]> create table huluwa (
    ->  id int unsigned auto_increment primary key,
    ->  name varchar(10),
    ->  age tinyint unsigned,
    ->  high decimal(5,2),
    ->  gender enum('boy','girl','unknow')default 'unknow',
    ->  lost bit(1));
  • 表结构
MariaDB [test1]> desc huluwa;
+--------+-----------------------------+------+-----+---------+----------------+
| Field  | Type                        | Null | Key | Default | Extra          |
+--------+-----------------------------+------+-----+---------+----------------+
| id     | int(10) unsigned            | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10)                 | YES  |     | NULL    |                |
| age    | tinyint(3) unsigned         | YES  |     | NULL    |                |
| high   | decimal(5,2)                | YES  |     | NULL    |                |
| gender | enum('boy','girl','unknow') | YES  |     | unknow  |                |
| lost   | bit(1)                      | YES  |     | NULL    |                |
+--------+-----------------------------+------+-----+---------+----------------+
  • 查询表创建状态
MariaDB [test1]> show create table huluwa;
  • 增加:全列插入(大娃出生了)
MariaDB [test1]>-- insert into 表名 values (...);

MariaDB [test1]> insert into huluwa values (0,'大娃',1,1.324,'boy',0);
Query OK, 1 row affected, 1 warning (0.003 sec)

MariaDB [test1]> select * from huluwa;
+----+--------+------+------+--------+------+
| id | name   | age  | high | gender | lost |
+----+--------+------+------+--------+------+
|  1 | 大娃   |    1 | 1.32 | boy    |      |
+----+--------+------+------+--------+------+
  • 修改:(大娃丢了,lost列变为1)
MariaDB [test1]>-- update 表名 set 列=值... where 过滤条件;

MariaDB [test1]> update huluwa set lost=1 where name='大娃';
Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test1]> select * from huluwa;
+----+--------+------+------+--------+------+
| id | name   | age  | high | gender | lost |
+----+--------+------+------+--------+------+
|  1 | 大娃   |    1 | 1.32 | boy    |     |
+----+--------+------+------+--------+------+
  • 数字类型数据可以使用运算符
  • in(' ',' ',' ')语法的使用
update huluwa set age=age+1 where name in('大娃','二娃');
  • 增加:部分插入(二娃出生了)
MariaDB [test1]>-- insert into 表名(字段) valuse(...);

MariaDB [test1]> insert into huluwa(id, name,lost) values(0,'二娃',0);
Query OK, 1 row affected (0.003 sec)

MariaDB [test1]> select * from huluwa;
+----+--------+------+------+--------+------+
| id | name   | age  | high | gender | lost |
+----+--------+------+------+--------+------+
|  1 | 大娃   |    1 | 1.32 | boy    |     |
|  2 | 二娃   | NULL | NULL | unknow |      |
+----+--------+------+------+--------+------+
  • 增加:插入多条(三娃和四娃一起出生了)
MariaDB [test1]>-- insert into 表名 valuse (...),(...),...;

MariaDB [test1]> insert into huluwa(id, name,lost) values(0,'三娃',0),(0,'四娃',0);
Query OK, 2 rows affected (0.003 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test1]> select * from huluwa;
+----+--------+------+------+--------+------+
| id | name   | age  | high | gender | lost |
+----+--------+------+------+--------+------+
|  1 | 大娃   |    1 | 1.32 | boy    |     |
|  2 | 二娃   | NULL | NULL | unknow |      |
|  3 | 三娃   | NULL | NULL | unknow |      |
|  4 | 四娃   | NULL | NULL | unknow |      |
+----+--------+------+------+--------+------+
  • 删除(delete / truncate)(删除丢了的葫芦娃)
MariaDB [test1]>-- delete from 表名 where 条件;

MariaDB [test1]> delete from huluwa where lost=1;

MariaDB [test1]> select * from huluwa;
+----+--------+------+------+--------+------+
| id | name   | age  | high | gender | lost |
+----+--------+------+------+--------+------+
|  2 | 二娃   | NULL | NULL | unknow |      |
|  3 | 三娃   | NULL | NULL | unknow |      |
|  4 | 四娃   | NULL | NULL | unknow |      |
+----+--------+------+------+--------+------+
MariaDB [test1]> truncate table huluwa;
Query OK, 0 rows affected (0.040 sec)

MariaDB [test1]> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| huluwa          |
+-----------------+
1 row in set (0.000 sec)

MariaDB [test1]> select * from huluwa;
Empty set (0.000 sec)

mariadb rownum函数 mariadb数据类型,mariadb rownum函数 mariadb数据类型_数据库,第1张




https://www.xamrdz.com/database/6g31962871.html

相关文章: