MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一，该笔记用于生产环境快速查阅.
该笔记文字描述部分整理于《MySQL 5.7从入门到精通》其目的是总结通用知识点，学习时总结的笔记，以及常用SQL语句的写法模板，方便后期查阅与工作时使用。
数据库与表
◆数据库相关命令◆
创建数据库： 创建数据库可以使用Create database命令，创建一个lyshark数据库，并查看。
MariaDB [(none)]> create database lyshark charset utf8;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show create database lysharkG
+----------+------------------------------------------------------------------+
| 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类型是一个定长,二进制字节字符串类型,在字段不足制定字节是会自动在后面填 .
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)