MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一，该笔记用于生产环境快速查阅.
数据库与表 ◆数据库相关命令◆ 创建数据库： 创建数据库可以使用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` | +
查询数据库： 查询数据库可以使用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` | +
删除数据库： 手动删除数据库 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 | | | 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 `) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) MariaDB [lyshark]> alter table tb_emp drop foreign key fk_empdept; MariaDB [lyshark]> show create table tb_emp \G *************************** 1. row *************************** Table: tb_emp KEY `fk_empdept` (`deptid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 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字节存储空间.
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列.65535(2^16-1)字符的TEXT列.16777215(2^24-1)字符的TEXT列.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.
创建一个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通配符:
%:匹配任意字符,贪婪匹配
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)
参考文献 《MySQL5.7从入门到精通》 - 刘增杰