MariaDB数据库（二）

1. MariaDB数据类型

　　MariaDB数据类型可以分为数字，日期和时间以及字符串值。

　　使用数据类型的原则：够用就行，尽量使用范围小的，而不用大的。

1.1 常用的数据类型

　　整数：int，bit（位，0和1）；bit（1）有两种可能，0或1，bit（2）有四种可能，00、01、10、11。

　　小数：decimal；　　 #decimal(5,2)

　　字符串：varchar，char　　

　　日期时间：date，time，datetime

　　枚举类型(enum)，比如gender类型有男、女等。

1.2 约束

　　1）主键primary key：物理上存储的顺序，有索引功能，不为空，不能重复，按序排序；

　　2）非空not null：此字段不能为空；

　　3）唯一unique：此字段不允许重复；

　　4）默认default：当不填写此值时会使用默认值，如果填写则已填写为准；

　　5）外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常。外键设置原则：谁的范围小给谁设置外键。

数值类型	类型	字节大小	有符号范围（Signed）	无符号范围（Unsigned）
	TINYINT	1	-128~127	0~256
	SMALINT	2	-32768~32767	0~65535
	MEDIUINT	3	-8388608~8388607	0~16777215
	INT/INTEGER	4
	BIGINT	8
字符串	类型	字节大小	示例
	CHAR	0~255	CHAR（20）不管写多少，占用均为20字符
	VARCHAR	0~255	可伸缩，VARCHAR（22）最多22个字符，写多少占用多少，查询效率比CHAR差
	TEXT	0~65535
日期时间类型	类型	字节大小	示例
	DATE	4	只有年月日，2019-5-15
	TIME	3	只有时分秒，14:56:36
	DATETIME	8	年月日时分秒
	YEAR	1	年 2019
	TIMESTAP	4	带有时区的详细信息，如‘2019-5-15 14:56:36’UTC

2. SQL语句增删改

进入数据库

[root@localhost ~]# mysql -uroot -p123

1> 查看

　　查看数据库

MariaDB [(none)]> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| testbase           |

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

　　创建数据库testdb用作实验

MariaDB [(none)]> create database testdb character set utf8;    #支持中文

MariaDB [(none)]> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| testdb             |

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

　　使用数据库testdb

MariaDB [(none)]> use testdb;

MariaDB [testdb]> show tables;                        #查看表

Empty set (0.000 sec)                                 #空表

　　创建一个classes表，有ip、name两个字段（列），ip为无符号的整型数，设置有主键，name为VARCHAR类型的字符串

MariaDB [testdb]> create table classes (id tinyint unsigned primary key,name varchar());

Query OK,  rows affected (0.005 sec)

MariaDB [testdb]> show tables;

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

| Tables_in_testdb  |

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

| classes           |

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

　　查看表的字段

MariaDB [testdb]> desc classes;    #describe，描述

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

| Field | Type                | Null | Key | Default | Extra|

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

| id    | tinyint() unsigned | NO   | PRI | NULL    |      |

| name  | varchar()         | YES  |     | NULL    |      |

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

 #定义     类型                是否为空 是否主键  默认值    扩展

　　linux当中的注释用#，而数据库中的注释用--

　　创建一个复杂的students表，包含字段id、name、age、high、gender、cls_id

MariaDB [testdb]> create table students (id tinyint unsigned primary key auto_increment,

    -> name varchar(),

    -> age tinyint unsigned,

　　-> gender enum('男', '女', '人妖','保密')not null default '人妖',   #枚举，默认为‘人妖’

    -> cls_id tinyint unsigned

　　-> );

MariaDB [testdb]> show tables;

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

| Tables_in_testdb |

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

| classes          |

| students         |

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

MariaDB [testdb]> desc students;

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

| Field  | Type                        | Null | Key | Default | Extra          |

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

| id     | tinyint() unsigned         | NO   | PRI | NULL    | auto_increment |

| name  | varchar()                  | YES  |     | NULL    |                |

| age   | tinyint() unsigned          | YES  |     | NULL    |                |

| gender | enum('男','女','人妖','保密') | NO   |     | 人妖    |                |

| cls_id | tinyint() unsigned         | YES  |     | NULL    |                |

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

　　查看表的创建

MariaDB [testdb]> show create table students;

……..                                                  #列出语法

| students | CREATE TABLE `students` (

  `id` tinyint() unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar() DEFAULT NULL,

  `age` tinyint() unsigned DEFAULT NULL,

  `gender` enum('男','女','人妖','保密') NOT NULL DEFAULT '人妖',

  `cls_id` tinyint() unsigned DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8                  #默认引擎为InnoDB

………

MariaDB处理引擎有InnoDB、MyISAM、blackhole等，最主要的区别在于InnoDB支持事务处理与外键和行级锁。

2> 改字段

　　修改表：修改用命令alter

　　添加一个brithday字段，类型为date

MariaDB [testdb]> alter table students add brithday date;

MariaDB [testdb]> desc students;

+----------+--------------------------------------+-----+---------+----------------+
| Field    | Type                          | Null | Key | Default | Extra          |

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

| id       | tinyint() unsigned           | NO   | PRI | NULL    | auto_increment |

| name     | varchar()                   | YES  |     | NULL    |                |

| age      | tinyint() unsigned           | YES  |     | NULL    |                |

| gender   | enum('男','女','人妖','保密')   | NO   |人妖 |         |                |

| cls_id   | tinyint() unsigned    YES    |      |NULL |         |                |

| brithday | date                          | YES  |     | NULL    |                |

　　更改字段名

MariaDB [testdb]> alter table students change brithday birthday date;

　　更改表的类型

MariaDB [testdb]> alter table students modify birthday datetime;

　　删除字段

MariaDB [testdb]> alter table students drop birthday;

　　删除表

MariaDB [testdb]> drop table students;

3> 给字段插入数据

　　向students表插入数据，value接值，ip为1，name为zxj，字符串类型用单引号引起，age为250，high为250.253，gender为人妖id_cls为1

MariaDB [testdb]> insert into students values (,'zxj',250.253,'人妖',);

　　查看插入的字段

MariaDB [testdb]> select * from students;

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

| id | name | age  | gender | cls_id |

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

|   | zxj  |   | 人妖    |       |

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

 row in set (0.000 sec)

　　由于id设置的是自增，因此每次插入时id都是增加的，为体现效果，手动插入id为4的一条数据

MariaDB [testdb]> select * from students;

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

| id | name | age  | gender | cls_id |

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

|   | zxj  |   | 人妖    |       |

|   | zxj  |   | 人妖    |       |

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

MariaDB [testdb]> insert into students values (,'zxj',250.253,'人妖',);

Query OK,  row affected (0.001 sec)

MariaDB [testdb]> select * from students;

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

| id | name | age  | gender | cls_id |

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

|   | zxj  |   | 人妖    |    1   |

|   | zxj  |   | 人妖    |       |

|   | zxj  |   | 人妖    |       |

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

 rows in set (0.000 sec)

MariaDB [testdb]> show create table students;

….

) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8       #当前默认值为为6，因此下一次默认添加数据时自增为6

　　对部分插入，比如仅对name、age添加数据

MariaDB [testdb]> insert into students (name,age) values ('wrl' ,);

MariaDB [testdb]> select * from students;

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

| id | name | age  | gender | cls_id |

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

|   | zxj  |   | 人妖   |       |

|   | zxj  |   | 人妖   |       |

|   | zxj  |   | 人妖   |       |

|   | wrl  |    | 人妖   |  NULL  |                        #gender设置了默认值为‘人妖’

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

　　删除一条数据

MariaDB [testdb]> delete from students where id=;           #条件表达式where

MariaDB [testdb]> select * from students;

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

| id | name | age  | gender | cls_id |

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

|   | zxj  |   | 人妖    |       |

|   | zxj  |   | 人妖    |       |

|   | zxj  |   | 人妖    |       |

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

　　插入多条数据

MariaDB [testdb]> insert into students (name,age) values ('wrl' ,),('my',);   #多条数据以逗号隔开，字符串加单引号

MariaDB [testdb]> select * from students;

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

| id | name | age  | gender | cls_id |

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

|   | zxj  |   | 人妖   |       |

|   | zxj  |   | 人妖   |       |

|   | zxj  |   | 人妖   |       |

|   | wrl  |    | 人妖   |  NULL  |

|   | my   |    | 人妖   |  NULL  |

4> 修改字段数据，用命令update

　　修改id>=7的数据的gender为‘保密’

MariaDB [testdb]> update students set gender='保密' where id>=;

MariaDB [testdb]> select * from students;

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

| id | name | age  |  gender  |cls_id |

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

|   | zxj  |   |   人妖   |       |

|   | zxj  |   |   人妖   |       |

|   | zxj  |   |   人妖   |       |

|   | wrl  |    |   保密   |  NULL  |

|   | my   |    |   保密   |  NULL  |

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

　　由于gender设置是枚举，选项有四个，因此可以用1、2、3、4来代替男、女、人妖、保密四个选项。

MariaDB [testdb]> update students set gender= where id=;

MariaDB [testdb]> update students set gender= where id=;

MariaDB [testdb]> update students set gender= where name='zxj';

MariaDB [testdb]> select * from  students;

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

| id | name | age  | gender  | cls_id   |

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

|   | zxj  |   |  保密   |          |

|   | zxj  |   |  保密   |          |

|   | zxj  |   |  保密   |          |

|   | wrl  |    |  男     |      NULL |

|   | my   |    |  女     |      NULL |

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

　　还可以修改多条数据的多个字段（列），如

MariaDB [testdb]> update students set gender=,age= where name='zxj';

Query OK,  rows affected (0.001 sec)

Rows matched:   Changed:   Warnings:

MariaDB [testdb]> select * from students;

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

| id | name | age  | gender | cls_id |

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

|   | zxj  |    | 女     |       |

|   | zxj  |    | 女     |       |

|   | zxj  |    | 女     |    1   |

|   | wrl  |    | 男     |NULL    |

|   | my   |    | 女     |NULL    |

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

 rows in set (0.000 sec)

5> 删除

　　删除students表中id为4、5的数据

MariaDB [testdb]> delete from students where id=;

MariaDB [testdb]> delete from students where id=;

MariaDB [testdb]> select * from students;

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

| id | name | age  | gender | cls_id |

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

|   | zxj  |  23  | 女     |       |

|   | wrl  |    | 男     | NULL   |

|   | my   |  23  | 女     | NULL   |

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

　　清空表。清空表的内容不要随便用，会清除所有数据。

MariaDB [testdb]> delete from students;

面试：用delete并不会清除自增AUTO_UNCREMENT的值，若要清空表后重置自增值为1，用truncate命令，truncate会清空表的所有内容且不可恢复（不能回滚）。

MariaDB [testdb]> truncate table students；

6> 逻辑删除　

　　用一条字段来表示这条信息是否已经不能在使用了，比如游戏企业通过设置一个字段来表示哪些用户活跃，哪些用户不活跃等。一般企业都将逻辑删除标记为bit（0和1）。逻辑删除并不会删除磁盘数据。

　　先在students表中添加一个逻辑删除标记ls_delete，bit类型，默认为0

MariaDB [testdb]> alter table students add is_delete  bit default ;

MariaDB [testdb]> desc students;

………

| is_delete | bit()                              | YES  |     | b''    |

MariaDB [testdb]> insert into students (name,age,is_delete) values ('jony',,);

MariaDB [testdb]> select * from students;

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

| id | name | age  | gender | cls_id | is_delete |

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

|   | zxj  |    | 女     |       |           |        #bit=0数值较小，不显示不代表没有

|   | wrl  |    | 男     |   NULL |           ||   | my   |    | 女     |   NULL |           |

|   | jony |    | 人妖   |   NULL | 口         |        #bit=1

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

 rows in set (0.000 sec)

　　用is_delete来表明删除项，如筛选bit不为0的用户

MariaDB [testdb]> select * from students where is_delete=;

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

| id | name | age  | gender | cls_id | is_delete |

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

|   | zxj  |    | 女     |       |           |

|   | wrl  |    | 男     | NULL   |           |

|   | my   |    | 女     | NULL   |           |

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

巴特西

MariaDB数据库（二）

1. MariaDB数据类型

1.1 常用的数据类型

1.2 约束

2. SQL语句增删改

最新文章

热门文章