MySQL 数据库基础知识 - 数字知识商城-小可爱商城

MySQL 是最流行的关系型数据库管理系统

数据库与表

◆数据库相关命令◆

创建数据库：创建数据库可以使用Create database命令，创建一个lyshark数据库，并查看。

MariaDB [(none)]> create database lyshark charset utf8; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show create database lyshark\G +----------+------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------+ | lyshark | CREATE DATABASE `lyshark` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+------------------------------------------------------------------+

查询数据库：查询数据库可以使用Show databases命令，也可以通过like限定查询结果。

MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | lyshark | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]> show databases like 'lys%'; +-----------------+ | Database (lys%) | +-----------------+ | lyshark | +-----------------+ 1 row in set (0.00 sec)

更新数据库：将数据库的字符集从 utf8 修改为gbk格式。

MariaDB [(none)]> alter database lyshark charset gbk; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show create database lyshark; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | lyshark | CREATE DATABASE `lyshark` /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+-----------------------------------------------------------------+

删除数据库：手动删除数据库 lyshark并查询。

MariaDB [(none)]> drop database lyshark; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec)

◆数据表相关命令◆

创建数据表：创建lyshark库,用来存储表结构,并指定采用utf8编码,在该数据库中创建tb_user表.

MariaDB [(none)]> create database lyshark charset utf8; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> use lyshark Database changed

MariaDB [lyshark]> create table tb_user -> ( -> id int(11), -> name varchar(25), -> deptId int(11), -> salary float -> ); Query OK, 0 rows affected (0.01 sec)

查询表结构：在MySQL中,查看表结构可以使用describe和show create table语句.

MariaDB [lyshark]> describe lyshark; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) MariaDB [lyshark]> show create table lyshark \G *************************** 1. row *************************** Table: lyshark Create Table: CREATE TABLE `lyshark` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `salary` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

定义单字段主键：创建表tab_1将id字段定义为primary key主键,其SQL语句的写法如下.

MariaDB [lyshark]> create table tab_1 -> ( -> id int(10), -> name varchar(20), -> deptid int(10), -> salary float, -> primary key(id) -> ); Query OK, 0 rows affected (0.01 sec)

定义多字段主键：创建表tab_2,将name字段与deptid字段组合在一起,成为tab_2的多字段联合主键.

MariaDB [lyshark]> create table tab_2 -> ( -> name varchar(20), -> deptid int(10), -> salary float, -> primary key(name,deptid) -> ); Query OK, 0 rows affected (0.00 sec)

使用非空约束：创建表tab_3,指定name字段不能为空,为空则报错误.

MariaDB [lyshark]> create table tab_4 -> ( -> id int(10) primary key, -> name varchar(20) not null, -> salary float -> ); Query OK, 0 rows affected (0.00 sec)

使用默认约束：创建表tab_4,指定salary字段自动默认工资为500,如不填写此项默认是500.

MariaDB [lyshark]> create table tab_4 -> ( -> id int(10) primary key, -> name varchar(20) not null, -> salary float default 500 -> ); Query OK, 0 rows affected (0.00 sec)

使用唯一约束：创建表tab_5,并指定字段name列唯一,允许为空,但只能出现一个空值,唯一约束可以确保数据不重复.

MariaDB [lyshark]> create table tab_5 -> ( -> id int(10) primary key, -> name varchar(20), -> location varchar(50), -> constraint sth unique(name) -> ); Query OK, 0 rows affected (0.00 sec)

注意：unique和primary key的区别，一个表可以有多个字段声明成unique,但只能有一个primary key声明.

设置表自增长：创建表tab_6指定id员工编号为自动增长模式,并插入数据,省略ID编号这一栏即可.

MariaDB [lyshark]> create table tab_6 -> ( -> id int(10) primary key auto_increment, -> name varchar(20) not null, -> salary float -> ); Query OK, 0 rows affected (0.01 sec) MariaDB [lyshark]> insert into tab_8(name,salary) values('lyshark',1000),('admin',200); MariaDB [lyshark]> select * from tab_8; +----+---------+--------+ | id | name | salary | +----+---------+--------+ | 1 | lyshark | 1000 | | 2 | admin | 200 | +----+---------+--------+ 2 rows in set (0.00 sec)

使用外键约束：外键用来在两个表的数据之间建立连接,每个外键值必须等于另一个表中主键的某个值.

1.创建一个tb_dept并指定为主表,把tb_emp指定为从表,将两表指定字段相关联.

MariaDB [lyshark]> create table tb_dept -> ( -> id int(11) primary key, -> name varchar(22) NOT NULL, -> location varchar(50) -> ); Query OK, 0 rows affected (0.01 sec)

2.接着创建数据表tb_emp,让它的deptid字段,作为外键关联到tb_dept的主键id字段上.

MariaDB [lyshark]> create table tb_emp -> ( -> id int(10) primary key, -> name varchar(25), -> deptid int(10), -> salary float, -> constraint fk_empdept foreign key(deptid) references tb_dept(id) -> ); Query OK, 0 rows affected (0.00 sec)

3.以上语句执行,在表tb_emp上添加了名称为fk_empdept的外键约束,外键字段为deptid,其依赖于tb_dept表中的,主键id.

MariaDB [lyshark]> desc tb_dept; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(22) | NO | | NULL | | | location | varchar(50) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) MariaDB [lyshark]> desc tb_emp; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(10) | NO | PRI | NULL | | | name | varchar(25) | YES | | NULL | | | deptid | int(10) | YES | MUL | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)

修改字段与数据

◆操作表中字段◆

修改表名称：通过alter table语句,将数据表tab_1,改名成lyshark.

MariaDB [lyshark]> show tables; +-------------------+ | Tables_in_lyshark | +-------------------+ | tab_1 | +-------------------+
1 rows in set (0.00 sec) MariaDB [lyshark]> alter table tab rename to lyshark; Query OK, 0 rows affected (0.00 sec) MariaDB [lyshark]> show tables; +-------------------+ | Tables_in_lyshark | +-------------------+ | lyshark | +-------------------+
1 rows in set (0.00 sec)

修改字段名：将表中tab_test的salary字段名改为lyshark并修改数据类型为varchar(30).

MariaDB [lyshark]> desc tab_test; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) MariaDB [lyshark]> alter table tab_test change salary lyshark varchar(30); Query OK, 4 rows affected (0.00 sec) MariaDB [lyshark]> desc tab_test; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | lyshark | varchar(30) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)

只修改字段类型：将表tab_test的name字段数据类型由varchar(20)修改为varchar(40).

MariaDB [lyshark]> desc tab_test; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) MariaDB [lyshark]> alter table tab_test modify name varchar(40); Query OK, 4 rows affected (0.01 sec) MariaDB [lyshark]> desc tab_test; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)

在末尾添加字段：在tab_test表,结尾添加clound字段,类型为varchar(20),并具有not null属性.

MariaDB [lyshark]> desc tab_test; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | lyshark | varchar(30) | YES | | NULL | | | manager | int(10) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) MariaDB [lyshark]> alter table tab_test add clound varchar(20) not null; Query OK, 4 rows affected (0.01 sec) MariaDB [lyshark]> desc tab_test; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | lyshark | varchar(30) | YES | | NULL | | | manager | int(10) | YES | | NULL | | | clound | varchar(20) | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)

在开头添加字段：在tab_test表的第一列添加一个新字段,字段名wang类型int(4).

MariaDB [lyshark]> desc tab_test; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | lyshark | varchar(30) | YES | | NULL | | | manager | int(10) | YES | | NULL | | | clound | varchar(20) | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) MariaDB [lyshark]> alter table tab_test add wang int(4) first; Query OK, 4 rows affected (0.00 sec) MariaDB [lyshark]> desc tab_test; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | wang | int(4) | YES | | NULL | | | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | lyshark | varchar(30) | YES | | NULL | | | manager | int(10) | YES | | NULL | | | clound | varchar(20) | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)

在指定位置添加字段：在tab_test表的指定位置添加一个字段,在name列的后面插入一个xxxx字段类型为int.

MariaDB [lyshark]> desc tab_test; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | wang | int(4) | YES | | NULL | | | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | lyshark | varchar(30) | YES | | NULL | | | manager | int(10) | YES | | NULL | | | clound | varchar(20) | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) MariaDB [lyshark]> alter table tab_test add xxxx int(4) after name; Query OK, 4 rows affected (0.01 sec) MariaDB [lyshark]> desc tab_test; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | wang | int(4) | YES | | NULL | | | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | xxxx | int(4) | YES | | NULL | | | lyshark | varchar(30) | YES | | NULL | | | manager | int(10) | YES | | NULL | | | clound | varchar(20) | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec)

删除表中指定字段：使用alert table drop命令,删除tab_test表中的clound字段.

MariaDB [lyshark]> desc tab_test; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | wang | int(4) | YES | | NULL | | | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | xxxx | int(4) | YES | | NULL | | | lyshark | varchar(30) | YES | | NULL | | | manager | int(10) | YES | | NULL | | | clound | varchar(20) | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec) MariaDB [lyshark]> alter table tab_test drop clound; Query OK, 4 rows affected (0.01 sec) MariaDB [lyshark]> desc tab_test; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | wang | int(4) | YES | | NULL | | | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | xxxx | int(4) | YES | | NULL | | | lyshark | varchar(30) | YES | | NULL | | | manager | int(10) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)

修改字段排列到第一列：将tab_test表中的lyshark字段移动到第1列.

MariaDB [lyshark]> desc tab_test; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | wang | int(4) | YES | | NULL | | | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | xxxx | int(4) | YES | | NULL | | | lyshark | varchar(30) | YES | | NULL | | | manager | int(10) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) MariaDB [lyshark]> alter table tab_test modify lyshark varchar(30) first; Query OK, 4 rows affected (0.01 sec) MariaDB [lyshark]> desc tab_test; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | lyshark | varchar(30) | YES | | NULL | | | wang | int(4) | YES | | NULL | | | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | xxxx | int(4) | YES | | NULL | | | manager | int(10) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ 6 rows in set (0.01 sec)

修改指定字段到任意位置: 把tab_test表中的manager字段放到lyshark字段的后面.

MariaDB [lyshark]> desc tab_test; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | lyshark | varchar(30) | YES | | NULL | | | wang | int(4) | YES | | NULL | | | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | xxxx | int(4) | YES | | NULL | | | manager | int(10) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) MariaDB [lyshark]> alter table tab_test modify manager int(10) after lyshark; Query OK, 4 rows affected (0.01 sec) MariaDB [lyshark]> desc tab_test; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | lyshark | varchar(30) | YES | | NULL | | | manager | int(10) | YES | | NULL | | | wang | int(4) | YES | | NULL | | | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | xxxx | int(4) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)

修改表的存储引擎：使用show create table查看引擎,并修改tab_test表的默认存储引擎为MyISAM.

MariaDB [lyshark]> show create table tab_test \G
MariaDB [lyshark]> alter table tab_test engine=MyISAM; MariaDB [lyshark]> show create table tab_test \G *************************** 1. row *************************** Table: tab_test Create Table: CREATE TABLE `tab_test` ( `lyshark` varchar(30) DEFAULT NULL, `manager` int(10) DEFAULT NULL, `wang` int(4) DEFAULT NULL, `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(40) DEFAULT NULL, `xxxx` int(4) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 #可以看到已经改变了 1 row in set (0.00 sec)

MySQL中主要存储引擎有:MyISAM、InnoDB、MEMORY、BDB、FEDERATED等.

删除表的外键约束：使用drop foreign key命令删除外键，删除tb_emp的外键约束

MariaDB [lyshark]> desc tb_emp; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(10) | NO | PRI | NULL | | | name | varchar(25) | YES | | NULL | | | deptid | int(10) | YES | MUL | NULL | | #外键标识MUL | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec) MariaDB [lyshark]> show create table tb_emp \G
*************************** 1. row ***************************
       Table: tb_emp
  CONSTRAINT `fk_empdept` FOREIGN KEY (`deptid`) REFERENCES `tb_dept` (`id`) #foreign key指定了外键 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec) MariaDB [lyshark]> alter table tb_emp drop foreign key fk_empdept; #删除tb_emp表中的外键fk_empdept MariaDB [lyshark]> show create table tb_emp \G
*************************** 1. row ***************************
       Table: tb_emp
  KEY `fk_empdept` (`deptid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 #此时已经没有foreign key 这一项了，说明删除成功 1 row in set (0.00 sec)

删除指定表：删除lyshark数据库中的tab_test表结构.

MariaDB [lyshark]> drop table if exists tab_test; Query OK, 0 rows affected (0.00 sec) MariaDB [lyshark]> show tables; +-------------------+ | Tables_in_lyshark | +-------------------+ +-------------------+ 10 rows in set (0.01 sec)

◆操作表中数据◆

创建一个数据表：为了方便后续的练习,我们先来创建一个表结构,SQL语句如下:

MariaDB [lyshark]> create table person -> ( -> id int unsigned not null auto_increment, -> name char(50) not null default '', -> age int not null default 0, -> info char(50) null, -> primary key(id) -> ); Query OK, 0 rows affected (0.00 sec)

在所有字段插入数据：在person表中,插入一条新记录id=1,name=LyShark,age=22,info=Lawyer,SQL语句如下:

MariaDB [lyshark]> select * from person; Empty set (0.00 sec) MariaDB [lyshark]> insert into person(id,name,age,info) values(1,'LyShark',22,'Lawyer'); Query OK, 1 row affected (0.00 sec) MariaDB [lyshark]> select * from person; +----+---------+-----+--------+ | id | name | age | info | +----+---------+-----+--------+ | 1 | LyShark | 22 | Lawyer | +----+---------+-----+--------+ 1 row in set (0.00 sec)

在指定字段插入数据：在person表中,插入一条新记录,name=Willam,age=18,info=sports,我们不给其指定ID,SQL语句如下:

MariaDB [lyshark]> desc person; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | char(50) | NO | | | | | age | int(11) | NO | | 0 | | | info | char(50) | YES | | NULL | | +-------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) MariaDB [lyshark]> insert into person(name,age,info) values('Willam',18,'sports man'); Query OK, 1 row affected (0.04 sec) MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | +----+---------+-----+------------+ 2 rows in set (0.00 sec)

同时为表插入多条记录：在person表中,同时插入3条新记录,有多条只需要在每一条的后面加,即可,SQL语句如下:

MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | +----+---------+-----+------------+ 2 rows in set (0.00 sec) MariaDB [lyshark]> insert into person(name,age,info) values('Evans',27,'secretary'), -> ('Dale',22,'cook'), -> ('Edison',28,'singer'); Query OK, 3 rows affected (0.01 sec) MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | | 3 | Evans | 27 | secretary | | 4 | Dale | 22 | cook | | 5 | Edison | 28 | singer | +----+---------+-----+------------+ 5 rows in set (0.00 sec)

将查询结果插入到表中：新建一个person_old表,其表结构和person相同,我们将person_old表中的内容全部迁移到person中去,SQL语句如下:

1.创建一个person_old表,并插入测试字段:

MariaDB [lyshark]> create table person_old -> ( -> id int unsigned not null auto_increment, -> name char(50) not null default '', -> age int not null default 0, -> info char(50) null, -> primary key(id) -> ); Query OK, 0 rows affected (0.01 sec) MariaDB [lyshark]> insert into person_old -> values(11,'harry',20,'student'),(12,'Beckham',33,'police'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0

2.接下来我们将person_old表中的内容迁移到person中去

MariaDB [lyshark]> select * from person_old; +----+---------+-----+---------+ | id | name | age | info | +----+---------+-----+---------+ | 11 | harry | 20 | student | | 12 | Beckham | 33 | police | +----+---------+-----+---------+ 2 rows in set (0.00 sec) MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | | 3 | Evans | 27 | secretary | | 4 | Dale | 22 | cook | | 5 | Edison | 28 | singer | +----+---------+-----+------------+ 5 rows in set (0.00 sec) MariaDB [lyshark]> insert into person(id,name,age,info) -> select id,name,age,info from person_old; Query OK, 2 rows affected (0.00 sec) MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | | 3 | Evans | 27 | secretary | | 4 | Dale | 22 | cook | | 5 | Edison | 28 | singer | | 11 | harry | 20 | student | | 12 | Beckham | 33 | police | +----+---------+-----+------------+ 7 rows in set (0.00 sec)

更新表中指定字段：修改person表中数据,将id=11的name字段的值改为xxxx,age字段改为200,SQL语句如下:

MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | | 3 | Evans | 27 | secretary | | 4 | Dale | 22 | cook | | 5 | Edison | 28 | singer | | 11 | harry | 20 | student | | 12 | Beckham | 33 | police | +----+---------+-----+------------+ 7 rows in set (0.00 sec) MariaDB [lyshark]> update person set age=200,name='xxxx' where id=11; #更新单个字段 Query OK, 1 row affected (0.00 sec) MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | | 3 | Evans | 27 | secretary | | 4 | Dale | 22 | cook | | 5 | Edison | 28 | singer | | 11 | xxxx | 200 | student | | 12 | Beckham | 33 | police | +----+---------+-----+------------+ 7 rows in set (0.00 sec)

更新表的一个范围：更新person表中的记录,将1-12的info字段全部改为lyshark blog,SQL语句如下:

MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | | 3 | Evans | 27 | secretary | | 4 | Dale | 22 | cook | | 5 | Edison | 28 | singer | | 11 | xxxx | 200 | student | | 12 | Beckham | 33 | police | +----+---------+-----+------------+ 7 rows in set (0.00 sec) MariaDB [lyshark]> update person set info='lyshark blog' where age between 1 and 200; #指定修改的字段 Query OK, 7 rows affected (0.00 sec) MariaDB [lyshark]> select * from person; +----+---------+-----+--------------+ | id | name | age | info | +----+---------+-----+--------------+ | 1 | LyShark | 22 | lyshark blog | | 2 | Willam | 18 | lyshark blog | | 3 | Evans | 27 | lyshark blog | | 4 | Dale | 22 | lyshark blog | | 5 | Edison | 28 | lyshark blog | | 11 | xxxx | 200 | lyshark blog | | 12 | Beckham | 33 | lyshark blog | +----+---------+-----+--------------+ 7 rows in set (0.00 sec)

删除表中指定记录：通过id号,删除表中指定列,此处删除第id=12号,这条记录,SQL语句如下:

MariaDB [lyshark]> select * from person; +----+---------+-----+--------------+ | id | name | age | info | +----+---------+-----+--------------+ | 1 | LyShark | 22 | lyshark blog | | 2 | Willam | 18 | lyshark blog | | 3 | Evans | 27 | lyshark blog | | 4 | Dale | 22 | lyshark blog | | 5 | Edison | 28 | lyshark blog | | 11 | xxxx | 200 | lyshark blog | | 12 | Beckham | 33 | lyshark blog | +----+---------+-----+--------------+ 7 rows in set (0.00 sec) MariaDB [lyshark]> delete from person where id=12; #通过id号，删除表中指定列 Query OK, 1 row affected (0.05 sec) MariaDB [lyshark]> select * from person; +----+---------+-----+--------------+ | id | name | age | info | +----+---------+-----+--------------+ | 1 | LyShark | 22 | lyshark blog | | 2 | Willam | 18 | lyshark blog | | 3 | Evans | 27 | lyshark blog | | 4 | Dale | 22 | lyshark blog | | 5 | Edison | 28 | lyshark blog | | 11 | xxxx | 200 | lyshark blog | +----+---------+-----+--------------+ 6 rows in set (0.00 sec)

删除表的一个范围：在person表中,删除age字段值在19-22的记录,SQL语句如下:

MariaDB [lyshark]> select * from person; +----+---------+-----+--------------+ | id | name | age | info | +----+---------+-----+--------------+ | 1 | LyShark | 22 | lyshark blog | | 2 | Willam | 18 | lyshark blog | | 3 | Evans | 27 | lyshark blog | | 4 | Dale | 22 | lyshark blog | | 5 | Edison | 28 | lyshark blog | | 11 | xxxx | 200 | lyshark blog | +----+---------+-----+--------------+ 6 rows in set (0.00 sec) MariaDB [lyshark]> delete from person where age between 19 and 22; #指定范围删除 Query OK, 2 rows affected (0.00 sec) MariaDB [lyshark]> select * from person; +----+--------+-----+--------------+ | id | name | age | info | +----+--------+-----+--------------+ | 2 | Willam | 18 | lyshark blog | | 3 | Evans | 27 | lyshark blog | | 5 | Edison | 28 | lyshark blog | | 11 | xxxx | 200 | lyshark blog | +----+--------+-----+--------------+ 4 rows in set (0.00 sec)

清空表中所有记录：

MariaDB [lyshark]> select * from person; +----+--------+-----+--------------+ | id | name | age | info | +----+--------+-----+--------------+ | 2 | Willam | 18 | lyshark blog | | 3 | Evans | 27 | lyshark blog | | 5 | Edison | 28 | lyshark blog | | 11 | xxxx | 200 | lyshark blog | +----+--------+-----+--------------+ 4 rows in set (0.00 sec) MariaDB [lyshark]> delete from person; #清空表中所有记录 Query OK, 4 rows affected (0.00 sec) MariaDB [lyshark]> select * from person; Empty set (0.00 sec)

数据类型相关

◆整数数据类型◆

数值型类型主要用来存储数字,MySQL提供了多种数值数据类型,不同的数据类型提供不同的取值范围,可以存储的值范围越大,其所需要的存储空间也会越大,MySQL主要提供的整形有:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT,整数类型的属性字段可以添加AUTO_INCREMENT自增长约束条件,如下表所示:

类型名称	说明信息	存储占比
TINYINT	很小的整数	1个字节
SMALLINT	小的整数	2个字节
MEDIUMINT	中等大小整数	3个字节
INT	普通大小整数	4个字节
BIGINT	大整数	8个字节

上表可看出,不同类型的数据字节是不同的,整数类型的取值范围也是固定的,基本上分为有符号和无符号型,下表就是他们的相应取值范围,仅供参考:

数据类型	有符号	无符号
TINYINT	-128-127	0-255
SMALLINT	32768-32767	0-65535
MEDIUMINT	-8388608-8388607	0-16777215
INT	-2147483648-2147483647	0-4294967295
BIGINT	这个范围不多说,(大)	0-无法形容的大

实例1: 创建一个整数类型的表.

MariaDB [lyshark]> create table myint -> ( -> uid int(10), -> name varchar(20) -> ); Query OK, 0 rows affected (0.00 sec)

以上是uid就是一个整数类型的字段,注意后面的(10)意思是指定能够显示的数值中数字的个数.

实例2: 分别创建整形的数据类型字段看看.

MariaDB [lyshark]> create table temp -> ( -> a tinyint, -> b smallint, -> c mediumint, -> d int, -> e bigint -> ); Query OK, 0 rows affected (0.00 sec) MariaDB [lyshark]> desc temp; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | a | tinyint(4) | YES | | NULL | | | b | smallint(6) | YES | | NULL | | | c | mediumint(9) | YES | | NULL | | | d | int(11) | YES | | NULL | | | e | bigint(20) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)

◆浮点数和定点数◆

在MySQL中浮点数和定点数都是用来表示小数的,浮点数类型有两种:单精度浮点数(FLOAT)和双精度浮点数(DOUBLE),定点类型的话只有一种(DECIMAL),下表是这几个数值的说明信息:

类型名称	说明信息	存储占比
FLOAT	单精度浮点数	4个字节
DOUBLE	双精度浮点数	8个字节
DECIMAL	压缩的定点数	M+2个字节

实例: 创建temp表,其中字段x,y,z数据类型分别是 float(5.1) double(5.1) decimal(5.1)并向表中插入一些数据.

MariaDB [lyshark]> create table temp -> ( -> x float(5,1), -> y double(5,1), -> z decimal(5,1) -> ); Query OK, 0 rows affected (0.00 sec)

向表中插入数据，并查看结果,MySQL默认自动截断小数点后面的数据,具体截断位数由计算机硬件和操作系统决定.

MariaDB [lyshark]> insert into temp values(5.12,5.22,5.123); Query OK, 1 row affected, 1 warning (0.01 sec) MariaDB [lyshark]> select * from temp1; +------+------+------+ | x | y | z | +------+------+------+ | 5.1 | 5.2 | 5.1 | +------+------+------+ 1 row in set (0.00 sec) MariaDB [lyshark]>

◆日期与时间类型◆

MySQL中有多种表示日期的数据类型,主要有LDATETIME、DATE、TIME和YEAR.例如,当只记录年信息的时候,可以只使用 YEAR类型而没有必要使用DATE,每一个类型都有合法的取值范围,当指定确实不合法的值时系统将"0"值插入到数据库中,下面先来看一下他的类型说明吧:

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901-2155	1字节
TIME	HH:MM:SS	-838:59:59-838:59:59	3字节
DATE	YYYY-MM-DD	1000-01-01-9999-12-3	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00-9999:12-31 23:59:59	8字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC-2038-01-19 03:14:07 UTC	4字节

YEAR类型: 主要用于存储一个年份,例如：1997 2018

1.创建temp表,定义数据类型为year的字段x,并向表中插入数据.

MariaDB [lyshark]> create table temp(x year); #创建一个year类型的字段 Query OK, 0 rows affected (0.00 sec) MariaDB [lyshark]> desc temp; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | x | year(4) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) MariaDB [lyshark]> insert into temp values(2018),("2020"); #插入一些数据：注意必须是1901-2155之间的数 Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [lyshark]> select * from temp; +------+ | x | +------+ | 2018 | | 2020 | +------+ 2 rows in set (0.00 sec)

TIME类型:主要用于存储时间,例如：12:12:21

1.创建temp1表,定义数据类型为time的字段x,并向表中插入数据.

MariaDB [lyshark]> create table temp1( #创建一个time类型的字段 -> x time -> ); Query OK, 0 rows affected (0.01 sec) MariaDB [lyshark]> desc temp1; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | x | time | YES | | NULL | | +-------+------+------+-----+---------+-------+ 1 row in set (0.00 sec) MariaDB [lyshark]> insert into temp1 values('11:22:05'),('23:23'),('20'); #分别插入数据：注意（%HH-%MM-%SS） Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [lyshark]> select * from temp1; +----------+ | x | +----------+ | 11:22:05 | | 23:23:00 | | 00:00:20 | +----------+ 3 rows in set (0.00 sec)

2.当然啦我们可以简写省略冒号.

MariaDB [lyshark]> create table temp1(x time); Query OK, 0 rows affected (0.01 sec) MariaDB [lyshark]> insert into temp1 values('102231'); Query OK, 1 row affected (0.00 sec) MariaDB [lyshark]> select * from temp1; +----------+ | x | +----------+ | 10:22:31 | +----------+ 1 row in set (0.00 sec)

3.向temp表中的x字段插入当前系统运行时间,通过函数(CURRENT_TIME),(NOW()取出.

MariaDB [lyshark]> select * from temp; Empty set (0.00 sec) MariaDB [lyshark]> insert into temp values (CURRENT_TIME),(NOW()); Query OK, 2 rows affected, 1 warning (0.00 sec) Records: 2 Duplicates: 0 Warnings: 1 MariaDB [lyshark]> select * from temp; +----------+ | x | +----------+ | 21:27:43 | | 21:27:43 | +----------+ 2 rows in set (0.00 sec)

DATE类型:Date类型主要用于存储年月日,例如：1997-10-05

1.创建temp表,表中是date类型的x字段,并插入一条数据.

MariaDB [lyshark]> create table temp(x date); #创建一个date类型的字段 Query OK, 0 rows affected (0.00 sec) MariaDB [lyshark]> desc temp; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | x | date | YES | | NULL | | +-------+------+------+-----+---------+-------+ 1 row in set (0.00 sec) MariaDB [lyshark]> insert into temp values('1997-10-05'),('20180523'); #插入一些数据 Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [lyshark]> select * from temp; +------------+ | x | +------------+ | 1997-10-05 | | 2018-05-23 | +------------+ 2 rows in set (0.00 sec)

2.向temp表中插入系统当前日期,通过函数(CURRENT_DATE()),(NOW())取出系统日期.

MariaDB [lyshark]> select * from temp; +------------+ | x | +------------+ | 1997-10-05 | | 2018-05-23 | +------------+ 2 rows in set (0.00 sec) MariaDB [lyshark]> insert into temp values(CURRENT_DATE()),(NOW()); #取出系统当前日期并插入 Query OK, 2 rows affected, 1 warning (0.00 sec) Records: 2 Duplicates: 0 Warnings: 1 MariaDB [lyshark]> select * from temp; +------------+ | x | +------------+ | 1997-10-05 | | 2018-05-23 | | 2018-06-16 | | 2018-06-16 | +------------+ 4 rows in set (0.00 sec)

DATATIME:DateTime类型用于存储日期和时间,例如：2018-01-24 22:12:24

1.创建temp表dt字段类型为datetime,并插入一条数据.

MariaDB [lyshark]> create table temp(dt datetime); Query OK, 0 rows affected (0.00 sec) MariaDB [lyshark]> desc temp; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | dt | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 1 row in set (0.00 sec) MariaDB [lyshark]> insert into temp values('1997-05-10 10:22:14'),('20180616220101'); #插入日期时间 Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [lyshark]> select * from temp; +---------------------+ | dt | +---------------------+ | 1997-05-10 10:22:14 | | 2018-06-16 22:01:01 | +---------------------+ 2 rows in set (0.00 sec)

2.取系统当前日期并插入temp表的dt字段.

MariaDB [lyshark]> select * from temp; +---------------------+ | dt | +---------------------+ | 1997-05-10 10:22:14 | | 2018-06-16 22:01:01 | +---------------------+ 2 rows in set (0.00 sec) MariaDB [lyshark]> insert into temp values(now()); #取系统日期插入temp表的dt字段 Query OK, 1 row affected (0.00 sec) MariaDB [lyshark]> select * from temp; +---------------------+ | dt | +---------------------+ | 1997-05-10 10:22:14 | | 2018-06-16 22:01:01 | | 2018-06-16 22:03:39 | +---------------------+ 3 rows in set (0.00 sec) MariaDB [lyshark]>

TIMESTAMP类型:TimeStamp与DateTime相同,但是TimeStamp是使用的UTC(世界标准时间)

1.创建temp表并插入timestamp类型的x字段,插入一条数据.

MariaDB [lyshark]> create table temp(x timestamp); #创建一个timestamp类型的字段 Query OK, 0 rows affected (0.00 sec) MariaDB [lyshark]> desc temp; +-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | x | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------+ 1 row in set (0.00 sec) MariaDB [lyshark]> insert into temp values('2018-06-16 22:24:00'); #插入一条时间记录 Query OK, 1 row affected (0.01 sec) MariaDB [lyshark]> select *from temp; +---------------------+ | x | +---------------------+ | 2018-06-16 22:24:00 | +---------------------+ 1 row in set (0.00 sec) MariaDB [lyshark]> set time_zone='+12:00' #将时间上调12小时 -> ; Query OK, 0 rows affected (0.00 sec) MariaDB [lyshark]> select * from temp; #再次查询已经是第二天了 +---------------------+ | x | +---------------------+ | 2018-06-17 02:24:00 | +---------------------+ 1 row in set (0.00 sec)

◆文本字符串类型◆

字符串类型用来存储字符串数据,除了可以存储字符串数据之外,还可以存储其他数据,比如图片和声音的二进制数据.MySQL支持两类字符型数据:文本字符串和二进制字符串,本小节主要介绍文本字符串类型,文本字符串可以进行区分或者不区分大小写的串比较,另外还可以进行模式匹配查找.MysQL中文本字符串类型指CHAR,VARCHAR,TEXT,ENUM和SET,如下表所示.

类型名称	说明信息	存储需求
CHAR	固定长度非二进制字符串	M字节,1<=M<=255
VARCHAR	变长非二进制字符串	L+1字节
TIMYTEXT	非常小的非二进制字符串	L+1字节
TEXT	小的非二进制字符串	L+2字节
MEDIUMTEXT	中等非二进制字符串	L+3字节
LONGTEXT	大的非二进制字符串	L+4字节
ENUM	枚举类型	l或2个字节
SET	SET成员类型	1,2,3,4或8个字节

CHAR和VARCHAR:定长和不定长字符串类型

CHAR和VARCHAR的长度区别:

● CHAR是一种定长字符串,它的长度在初始化时就被固定比如说：char(10)则固定分配10个字符的长度,如果使用了CHAR类型,不论你的数据填充多少都会消耗4字节存储空间.
● VARCHAR是一种不定长字符串,它的长度取决于你输入的字符数,使用VARCHAR的话,它会动态的分配空间大小,但最大也不能超过定义的长度

1.定义一个temp表,里面有两个字段分别是ch,vch类型是char(4)和varchar(4)插入数据查看区别.

MariaDB [lyshark]> create table temp -> ( -> ch char(4), -> vch varchar(4) -> ); Query OK, 0 rows affected (0.00 sec) MariaDB [lyshark]> desc temp; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | ch | char(4) | YES | | NULL | | | vch | varchar(4) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) MariaDB [lyshark]> insert into temp values('xy ','xy '); Query OK, 1 row affected (0.01 sec) MariaDB [lyshark]> select *from temp; +------+------+ | ch | vch | +------+------+ | xy | xy | +------+------+ 1 row in set (0.00 sec)

TEXT类型:用于保存非二进制字符串,如文章内容评论内容等,当保存或查询text列的值时,不删除尾部空格.

关于TEXT类型的取值范围:

● TINYTEXT 最大长度为255(2^8-1)字符的TEXT列.
● TEXT 最大长度为65535(2^16-1)字符的TEXT列.
● MEDIUMTEXT 最大长度为16777215(2^24-1)字符的TEXT列.
● LONGTEXT 最大长度为4294967295字符的TEXT列.

1.创建一个表temp1,并创建text字段,写入一段话看看.

MariaDB [lyshark]> create table temp1(x text); Query OK, 0 rows affected (0.02 sec) MariaDB [lyshark]> desc temp1; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | x | text | YES | | NULL | | +-------+------+------+-----+---------+-------+ 1 row in set (0.00 sec) MariaDB [lyshark]> insert into temp1 values('hello lyshark') -> ; Query OK, 1 row affected (0.00 sec) MariaDB [lyshark]> select * from temp1; +---------------+ | x | +---------------+ | hello lyshark | +---------------+ 1 row in set (0.00 sec)

ENUM枚举类型:enum的值根据列索引顺序排列,并且空字符串排在非空字符串前,NULL值排在其他所有的枚举值前面

1.来看一个枚举的小例子,注意:枚举默认标号从1开始.

MariaDB [lyshark]> create table temp2(enm enum('first','second','thire')); Query OK, 0 rows affected (0.00 sec) MariaDB [lyshark]> desc temp2; +-------+--------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------------------------+------+-----+---------+-------+ | enm | enum('first','second','thire') | YES | | NULL | | +-------+--------------------------------+------+-----+---------+-------+ 1 row in set (0.00 sec) MariaDB [lyshark]> insert into temp2 values('1'),('2'),('3'),(NULL); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [lyshark]> select * from temp2; +--------+ | enm | +--------+ | first | | second | | thire | | NULL | +--------+ 4 rows in set (0.00 sec) MariaDB [lyshark]>

set集合:但在声明成集合时,其取值就已经固定了

MariaDB [lyshark]> create table temp3(s set('a','b','c','d')); #首先定义了一个集合，元素有abcd Query OK, 0 rows affected (0.01 sec) MariaDB [lyshark]> desc temp3; +-------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------------+------+-----+---------+-------+ | s | set('a','b','c','d') | YES | | NULL | | +-------+----------------------+------+-----+---------+-------+ 1 row in set (0.00 sec) MariaDB [lyshark]> MariaDB [lyshark]> insert into temp3 values('a'),('a,b,c'),('a,b,c,d'); #分别插入3个不同的集合，看看 Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [lyshark]> select * from temp3; +---------+ | s | +---------+ | a | | a,b,c | | a,b,c,d | +---------+ 3 rows in set (0.00 sec) MariaDB [lyshark]> insert into temp3 values('a,'f''); #在插入f时报错，因为集合中定义是没有f ERROR 1064 (42000): You have an error in your SQL syntax;

◆二进制字串类型◆

在MySQL中的二进制数据类型有:BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB,LONGBLOB,老样子,看下面的表格,就清晰啦.

类型名称	说明信息	存储需求
BIT	位字段类型	(M+7/8)个字节
BINARY	固定长度二进制字符串	M个字节
VARBINARY	可变长二进制字符串	M+1字节
TINYBLOB	非常小的BLOB	L+1字节
BLOB	小BLOB	L+2字节
MEDIUMBLOB	中等大小的BLOB	L+3字节
LONGBLOB	非常大的BLOB	L+4字节

bit类型:位字段类型,也就是说插入的数据会被转换成101011011这样的格式

1.定义并插入数据测试,x+0表示将二进制结果转换为对应的数字的值,bin()函数将数字转换为2进制.

MariaDB [lyshark]> create table temp5(x bit(4)); Query OK, 0 rows affected (0.00 sec) MariaDB [lyshark]> desc temp5; +-------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------+------+-----+---------+-------+ | x | bit(4) | YES | | NULL | | +-------+--------+------+-----+---------+-------+ 1 row in set (0.00 sec) MariaDB [lyshark]> insert into temp5 values(100),(115),(10); Query OK, 3 rows affected, 2 warnings (0.00 sec) Records: 3 Duplicates: 0 Warnings: 2 MariaDB [lyshark]> select BIN(x+0) from temp5; +----------+ | BIN(x+0) | +----------+ | 1111 | | 1111 | | 1010 | +----------+ 3 rows in set (0.00 sec)

BINARY和VARBINARY类型: 定长与不定长二进制字符串类型.

1.binary类型是一个定长,二进制字节字符串类型,在字段不足制定字节是会自动在后面填0.
2.varbinary类型是一个可变长,二进制字节字符串类型,而vb字段不会填充.

创建一个temp10,分别有两个字段b,vb类型分别是binary(3)和varbinary(30)

MariaDB [lyshark]> create table temp10( -> b binary(3), -> vb varbinary(30) -> ); Query OK, 0 rows affected (0.00 sec) MariaDB [lyshark]> desc temp10; +-------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+-------+ | b | binary(3) | YES | | NULL | | | vb | varbinary(30) | YES | | NULL | | +-------+---------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) MariaDB [lyshark]> insert into temp10 values(5,5); Query OK, 1 row affected (0.01 sec) MariaDB [lyshark]> select length(b),length(vb) from temp10; #可以看到b占用3字节，而vb是只占用1字节 +-----------+------------+ | length(b) | length(vb) | +-----------+------------+ | 3 | 1 | +-----------+------------+ 1 row in set (0.00 sec)

运算符类型

运算符链接表达式中各个操作数,其作用是用来指明对操作数所进行的运算,运用运算符可以更加灵活的使用表中的数据,常见的运算符有:算术运算,比较运算,逻辑运算,位运算等,下面我们将依次介绍这几种运算符的运用.

◆算术运算符◆

运算符	作用
+	加法运算
-	减法运算
*	乘法运算
/	除法运算
%	求余运算

加法运算(+)

MariaDB [lyshark]> select * from temp; +------+ | num | +------+ | 100 | +------+ 1 row in set (0.00 sec) MariaDB [lyshark]> select num,num+10 from temp; +------+--------+ | num | num+10 | +------+--------+ | 100 | 110 | +------+--------+ 1 row in set (0.00 sec)

减法运算(-)

MariaDB [lyshark]> select * from temp; +------+ | num | +------+ | 100 | +------+ 1 row in set (0.01 sec) MariaDB [lyshark]> select num,num-10 from temp; +------+--------+ | num | num-10 | +------+--------+ | 100 | 90 | +------+--------+ 1 row in set (0.00 sec)

乘法运算(*)

MariaDB [lyshark]> select * from temp; +------+ | num | +------+ | 100 | +------+ 1 row in set (0.00 sec) MariaDB [lyshark]> select num,num*10 from temp; +------+--------+ | num | num*10 | +------+--------+ | 100 | 1000 | +------+--------+ 1 row in set (0.00 sec)

除法运算(/)

MariaDB [lyshark]> select * from temp; +------+ | num | +------+ | 100 | +------+ 1 row in set (0.00 sec) MariaDB [lyshark]> select num,num/10 from temp; +------+---------+ | num | num/10 | +------+---------+ | 100 | 10.0000 | +------+---------+ 1 row in set (0.00 sec)

取余数运算(%)

MariaDB [lyshark]> select * from temp; +------+ | num | +------+ | 100 | +------+ 1 row in set (0.00 sec) MariaDB [lyshark]> select num,num%10 from temp; +------+--------+ | num | num%10 | +------+--------+ | 100 | 0 | +------+--------+ 1 row in set (0.00 sec)

◆比较运算符◆

运算符	作用
=	等于
<=>	安全的等于
<>(!=)	不等于
<=	小于等于
>=	大于等于
>	大于
IS NULL	判断一个值是否为NULL
IS NOT NULL	判断一个值是否不为NULL
LEAST	在有两个或多个参数时,返回最小值
GREATEST	当有两个或多个参数时,返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
ISNULL	与IS NULL作用相同
IN	判断一个值是IN列表中的任意一个值
NOT IN	判断一个值不是IN列表中的任意一个值
LIKE	通配符匹配
REGEXP	正则表达式匹配

等于运算符(=): 使用等于运算符进行相等判断

MariaDB [lyshark]> select 1=1 , 1=0 , '1'=1 , '0.01'=0 , 'a'='a' , (1+1)=(2+2) , NULL=NULL; +-----+-----+-------+----------+---------+-------------+-----------+ | 1=1 | 1=0 | '1'=1 | '0.01'=0 | 'a'='a' | (1+1)=(2+2) | NULL=NULL | +-----+-----+-------+----------+---------+-------------+-----------+ | 1 | 0 | 1 | 0 | 1 | 0 | NULL | +-----+-----+-------+----------+---------+-------------+-----------+ 1 row in set (0.00 sec)

全等于(<=>): 这个运算符和=功能相同,但是全等于可以用来判断NULL值,而等于是不能的

MariaDB [lyshark]> select 1<=>0 , 1<=>1 , '1'<=>1 , '0.01' <=> 0 , 'a' <=> 'a' , (10+10)<=>(20+20) , NULL<=>NULL ; +-------+-------+---------+--------------+-------------+-------------------+-------------+ | 1<=>0 | 1<=>1 | '1'<=>1 | '0.01' <=> 0 | 'a' <=> 'a' | (10+10)<=>(20+20) | NULL<=>NULL | +-------+-------+---------+--------------+-------------+-------------------+-------------+ | 0 | 1 | 1 | 0 | 1 | 0 | 1 | +-------+-------+---------+--------------+-------------+-------------------+-------------+ row in set (0.00 sec)

不等于(<>或!=): 俩数不相等返回1,相等返回0

MariaDB [lyshark]> select 'lyshark' != 'admin' , 1!=2 , 1 <> 1 , (10+10)<>(10+10) , NULL!=NULL; +----------------------+------+--------+------------------+------------+ | 'lyshark' != 'admin' | 1!=2 | 1 <> 1 | (10+10)<>(10+10) | NULL!=NULL | +----------------------+------+--------+------------------+------------+ | 1 | 1 | 0 | 0 | NULL | +----------------------+------+--------+------------------+------------+ 1 row in set (0.00 sec)

小于运算符(<): 两数相比较,左边小于右边返回1,否则返回0

MariaDB [lyshark]> select 'xxx' < 'xxxx' , 1<2 , 1<1 , 5.5<5 , (1+1)<(10-10) , NULL <NULL ; +----------------+-----+-----+-------+---------------+------------+ | 'xxx' < 'xxxx' | 1<2 | 1<1 | 5.5<5 | (1+1)<(10-10) | NULL <NULL | +----------------+-----+-----+-------+---------------+------------+ | 1 | 1 | 0 | 0 | 0 | NULL | +----------------+-----+-----+-------+---------------+------------+ 1 row in set (0.00 sec)

小于等于(<=): 两数相比较,左边小于或者等于右边返回1,否则返回0

MariaDB [lyshark]> select 'xxxx' <= 'xxxx' , 1<=1 , 1<=2 , 5.5<=5 , NULL<=NULL; +------------------+------+------+--------+------------+ | 'xxxx' <= 'xxxx' | 1<=1 | 1<=2 | 5.5<=5 | NULL<=NULL | +------------------+------+------+--------+------------+ | 1 | 1 | 1 | 0 | NULL | +------------------+------+------+--------+------------+ 1 row in set (0.00 sec)

大于运算符(>): 两数相比较,左边大于右边返回1,否则返回0

MariaDB [lyshark]> select 'xxxx' > 'xxx' , 5>1 , 10>10 , NULL > NULL; +----------------+-----+-------+-------------+ | 'xxxx' > 'xxx' | 5>1 | 10>10 | NULL > NULL | +----------------+-----+-------+-------------+ | 1 | 1 | 0 | NULL | +----------------+-----+-------+-------------+ 1 row in set (0.00 sec)

大于等于(>=): 两数相比较,左边大于或者等于右边返回1,否则返回0

MariaDB [lyshark]> select 'xxxx' >= 'xxxx' , 1>=1 , 1>=10 , NULL>=NULL; +------------------+------+-------+------------+ | 'xxxx' >= 'xxxx' | 1>=1 | 1>=10 | NULL>=NULL | +------------------+------+-------+------------+ | 1 | 1 | 0 | NULL | +------------------+------+-------+------------+ 1 row in set (0.00 sec)

IS NULL运算符(ISNULL)和IS NOT NULL运算符(ISNOTNULL): is null如果为NULL返回1否则返回0,而is not null则相反.

MariaDB [lyshark]> select null is null , isnull(null) , isnull(1) , 1 is not null; +--------------+--------------+-----------+---------------+ | null is null | isnull(null) | isnull(1) | 1 is not null | +--------------+--------------+-----------+---------------+ | 1 | 1 | 0 | 1 | +--------------+--------------+-----------+---------------+ 1 row in set (0.00 sec)

between and 运算符(expr BETWEEN min AND max): 假如expr大于或等于min并且小于或等于max,则beetween返回1,否则返回0

MariaDB [lyshark]> select 4 between 2 and 5 , 4 between 4 and 6 , 20 between 5 and 10; +-------------------+-------------------+---------------------+ | 4 between 2 and 5 | 4 between 4 and 6 | 20 between 5 and 10 | +-------------------+-------------------+---------------------+ | 1 | 1 | 0 | +-------------------+-------------------+---------------------+ 1 row in set (0.00 sec)

letsa运算符(least 值1,值2.....值n): 在定义的数值列表中返回最小的那个元素的数值

MariaDB [lyshark]> select least(10,0) , least(1,2,3,4,5,6,7,8,9) , least('a','b','c') , least(10,null); +-------------+--------------------------+--------------------+----------------+ | least(10,0) | least(1,2,3,4,5,6,7,8,9) | least('a','b','c') | least(10,null) | +-------------+--------------------------+--------------------+----------------+ | 0 | 1 | a | NULL | +-------------+--------------------------+--------------------+----------------+ 1 row in set (0.00 sec)

greatest运算符(greatest 值1,值2....值n): 在定义的数值列表中返回最大的那个元素的数值

MariaDB [lyshark]> select greatest(10,0) , greatest(1,2,3,4,5,6,7,8,9) , greatest('a','b','c') , greatest(10,null); +----------------+-----------------------------+-----------------------+-------------------+ | greatest(10,0) | greatest(1,2,3,4,5,6,7,8,9) | greatest('a','b','c') | greatest(10,null) | +----------------+-----------------------------+-----------------------+-------------------+ | 10 | 9 | c | NULL | +----------------+-----------------------------+-----------------------+-------------------+ 1 row in set (0.00 sec)

IN 和NOT IN 运算符(值1 IN (值1,值2.....值n)): in运算符判断指定数值是否在指定的一个列表里,有则返回1无则返回0,而not in运算符恰恰相反.

MariaDB [lyshark]> select 1 in (1,2,3,4,5) , 'lyshark' in ('root','admin','lyshark'); +------------------+-----------------------------------------+ | 1 in (1,2,3,4,5) | 'lyshark' in ('root','admin','lyshark') | +------------------+-----------------------------------------+ | 1 | 1 | +------------------+-----------------------------------------+ 1 row in set (0.00 sec) MariaDB [lyshark]> select 10 not in (1,2,3,4,5) , 'lyshark' not in ('root','admin','lyshark'); +-----------------------+---------------------------------------------+ | 10 not in (1,2,3,4,5) | 'lyshark' not in ('root','admin','lyshark') | +-----------------------+---------------------------------------------+ | 1 | 0 | +-----------------------+---------------------------------------------+ 1 row in set (0.00 sec)

LIKE匹配运算符(expr LIKE 匹配条件): like运算符用来匹配字符串,如果expr满足条件则返回1否则返回0,若expr或匹配条件中任何一个为NULL则结果为NULL.

LIKE通配符:

%:匹配任意字符,贪婪匹配
_:只匹配一个字符
t__:表示匹配以t开头,长度为2个字符的字符串
%d:表示匹配以字母d结尾的字符串

MariaDB [lyshark]> select 'lyshark' like 'lyshark' , 'lyshark' like '%k' , 'lyshark' like 'ly_____'; +--------------------------+---------------------+--------------------------+ | 'lyshark' like 'lyshark' | 'lyshark' like '%k' | 'lyshark' like 'ly_____' | +--------------------------+---------------------+--------------------------+ | 1 | 1 | 1 | +--------------------------+---------------------+--------------------------+ 1 row in set (0.00 sec)

regexp字符串匹配运算符(expr regexp 匹配条件): regexp运算符能够更加精确的匹配,如果expr满足条件则返回1否则返回0,若expr或匹配条件中任何一个为NULL则结果为NULL.

REGEXP通配符：

^：匹配以该字符后面的字符开头的字符串
$：匹配以该字符后面的字符结尾的字符串
.：匹配任意一个单一字符
[...]：匹配在方括号内的任意字符

MariaDB [lyshark]> select 'lyshark' regexp '^l' , 'lyshark' regexp 'k$' , 'lyshark' regexp '..shark' , 'lyshark' regexp '[lyak]'; +-----------------------+-----------------------+----------------------------+---------------------------+ | 'lyshark' regexp '^l' | 'lyshark' regexp 'k$' | 'lyshark' regexp '..shark' | 'lyshark' regexp '[lyak]' | +-----------------------+-----------------------+----------------------------+---------------------------+ | 1 | 1 | 1 | 1 | +-----------------------+-----------------------+----------------------------+---------------------------+ 1 row in set (0.00 sec)

◆逻辑运算符◆

运算符	作用
NOT 或 !	逻辑非
AND 或 &&	逻辑与
OR	逻辑或
XOR	逻辑异或

NOT逻辑非: not或!逻辑非运算符,当操作数为0时返回1,当操作为1时返回0,当操作数为NULL时,返回NULL

MariaDB [lyshark]> select not 1 , not(1-1) , not -10 , not NULL; +-------+----------+---------+----------+ | not 1 | not(1-1) | not -10 | not NULL | +-------+----------+---------+----------+ | 0 | 1 | 0 | NULL | +-------+----------+---------+----------+ 1 row in set (0.00 sec)

AND逻辑与: and是逻辑与运算符,当两边都为真是结果为1,否则结果为0

MariaDB [lyshark]> select 1 and -1 , 1 and 0 , 1 and NULL , 0 and NULL; +----------+---------+------------+------------+ | 1 and -1 | 1 and 0 | 1 and NULL | 0 and NULL | +----------+---------+------------+------------+ | 1 | 0 | NULL | 0 | +----------+---------+------------+------------+ 1 row in set (0.00 sec)

OR逻辑或: or是逻辑或运算符,两边的结果如果有一边为真,则返回1否则返回0

MariaDB [lyshark]> select 1 or 1 , 1 or 0 , 1 or -1 , 1 or NULL; +--------+--------+---------+-----------+ | 1 or 1 | 1 or 0 | 1 or -1 | 1 or NULL | +--------+--------+---------+-----------+ | 1 | 1 | 1 | 1 | +--------+--------+---------+-----------+ 1 row in set (0.00 sec)

XOR异或: xor逻辑异或运算符,当任意一个操作数为null时返回null,如果两边都为0则返回1否则返回0

MariaDB [lyshark]> select 1 xor 1 , 0 xor 0 , 1 xor 0 , 1 xor null; +---------+---------+---------+------------+ | 1 xor 1 | 0 xor 0 | 1 xor 0 | 1 xor null | +---------+---------+---------+------------+ | 0 | 0 | 1 | NULL | +---------+---------+---------+------------+ 1 row in set (0.00 sec)

◆移位运算符◆

运算符	作用
\	位或
&	位与
^	位异或
<<	位左移
>>	位右移
~	位取反

位或(|): 位或运算符,按照提供数据的二进制形式依次或运算,最后输出结果

MariaDB [lyshark]> select 10 |15 , 9|4|2 ; +--------+-------+ | 10 |15 | 9|4|2 | +--------+-------+ | 15 | 15 | +--------+-------+ 1 row in set (0.00 sec)

位与(&): 位与运算符,按照提供数据的二进制形式依次与运算,最后输出结果

MariaDB [lyshark]> select 10 & 15 ,9&4&2 ; +---------+-------+ | 10 & 15 | 9&4&2 | +---------+-------+ | 10 | 0 | +---------+-------+ 1 row in set (0.00 sec)

位异或(^): 将指定数据的二进制形式,逐一按位或运算

MariaDB [lyshark]> select 10 ^ 15 , 1^0 , 1^1; +---------+-----+-----+ | 10 ^ 15 | 1^0 | 1^1 | +---------+-----+-----+ | 5 | 1 | 0 | +---------+-----+-----+ 1 row in set (0.00 sec)

按位左移(expr<<需要左移的位数): 将指定数据expr,的二进制形式,按位左移

MariaDB [lyshark]> select 4 <<2; +-------+ | 4 <<2 | +-------+ | 16 | +-------+ 1 row in set (0.00 sec)

按位右移(expr>>需要右移的位数): 将指定数据expr,的二进制形式,按位右移

MariaDB [lyshark]> select 16 >>2; +--------+ | 16 >>2 | +--------+ | 4 | +--------+ 1 row in set (0.00 sec)

按位取反(~): 将相应位数的二进制形式,逐位反转

MariaDB [lyshark]> select 5 & ~1 ; +--------+ | 5 & ~1 | +--------+ | 4 | +--------+ 1 row in set (0.00 sec)