mariadb数据类型分为数字、日期、时间以及字符串值。
适用类型原则:够用就行,尽量使用范围小的,而不用大的
常用数据类型:
1、整数 int,bit #例如 年纪 适用于必须是整数的
2、小数 decimal # 例如 身高、收入 #decimal(5,2) 5位数保留小数点后两位 decimal(7,1)7位数保留小数点后两位
3、字符串 char,varchar (可定义字符) #例如 姓名 、班级、籍贯等等,也可以整数、小数等数据类型 但是不能函数运算
4、日期时间 date,time,datetime
5、枚举类型 enum #可指定的类型 例如 性别
约束:
主键primary key:物理上存储的顺序
非空not null:此字段不能为空
唯一unique:此字段不允许重复
默认default:当不填写此值时会使用默认值,如果填写则已填写为准
外键foreign key:对关系字段进行约束,当为关系字段填写值时,会到关联的表中查询此值是否存在,如果存在则填写成功,如果不存在则填写失败并抛出异常
MariaDB [testdb]> create table class(id int primary key not null auto_increment,name varchar(20), age tinyint unsigned);
Query OK, 0 rows affected (0.00 sec)
MariaDB [testdb]> insert into class(name,age) values('李志锋',29);
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> select * from class;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | 李志锋 | 29 |
+----+-----------+------+
1 row in set (0.00 sec)
MariaDB [testdb]> desc class;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
MariaDB [testdb]> alter table class add gender enum ('男','女','保密');
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testdb]> desc class;
+--------+----------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('男','女','保密') | YES | | NULL | |
+--------+----------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
MariaDB [testdb]> alter table class modify gender enum('男','女','保密') default'保密';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testdb]> desc class;
+----------+----------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('男','女','保密') | YES | | 保密 | |
| birthday | datetime | YES | | NULL | |
+----------+----------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
MariaDB [testdb]> alter table class change birthday birth date;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [testdb]> desc class;
+--------+----------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('男','女','保密') | YES | | 保密 | |
| birth | date | YES | | NULL | |
+--------+----------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
MariaDB [testdb]> alter table class drop gender;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testdb]> desc class;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| birth | date | YES | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
--update 表名 set 列1=值1, 列2=值2… where 条件;
例: 将class表中的绿帽子改成红帽
MariaDB [testdb]> select * from class;
+----+-----------+------+-------+------+
| id | name | age | birth | high |
+----+-----------+------+-------+------+
| 1 | 李志锋 | 29 | NULL | NULL |
| 2 | 绿帽子 | 19 | NULL | NULL |
| 3 | 王晶 | 29 | NULL | NULL |
+----+-----------+------+-------+------+
3 rows in set (0.00 sec)
MariaDB [testdb]> update class set name=('红帽') where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [testdb]> select * from class;
+----+-----------+------+-------+------+
| id | name | age | birth | high |
+----+-----------+------+-------+------+
| 1 | 李志锋 | 29 | NULL | NULL |
| 2 | 红帽 | 19 | NULL | NULL |
| 3 | 王晶 | 29 | NULL | NULL |
+----+-----------+------+-------+------+
3 rows in set (0.00 sec)
delete from 表名 where 条件
例:将class表中的红帽删除
MariaDB [testdb]> delete from class where name=('红帽');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> select * from class;
+----+-----------+------+-------+------+
| id | name | age | birth | high |
+----+-----------+------+-------+------+
| 1 | 李志锋 | 29 | NULL | NULL |
| 3 | 王晶 | 29 | NULL | NULL |
+----+-----------+------+-------+------+
2 rows in set (0.00 sec)
手机扫一扫
移动阅读更方便
你可能感兴趣的文章