mysql命令语句大全_mysql增删改查语句

SQL语句基本操作之增删改查

　　SQL

　　1.SQL通用语法

　　#单行注释，/**/多行注释

　　不区分大小写

　　不识别换行符

　　语句以分号结尾

　　2.SQL分类

　　3.SQL数据类型

　　为什么要掌握各种数据类型？

　　——数据库由各种数据组成灵活使用各种数据类型对优化数据库是非常重要的

　　MySQL有哪些数据类型？

　　一共有三个大类的数据类型：数值、日期/时间和字符串(字符)类型。三个大类下有更细致的分类。

　　一、数值类型

　　MySQL支持所有标准SQL数值数据类型。

　　这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

　　关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

　　BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

　　作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

　　类型大小范围（有符号）范围（无符号）用途TINYINT1 字节(-128，127)(0，255)小整数值SMALLINT2 字节(-32 768，32 767)(0，65 535)大整数值MEDIUMINT3 字节(-8 388 608，8 388 607)(0，16 777 215)大整数值INT或INTEGER4 字节(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值BIGINT8 字节(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)极大整数值FLOAT4 字节(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度 浮点数值DOUBLE（REAL同义）8 字节(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度 浮点数值DECIMAL(NUMBERIC同义)对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2，其中M为总位数，D为小数点后的位数依赖于M和D的值依赖于M和D的值小数值

　　DECIMAL与FLOAT、DOUBLE的比较：

　　1、Decimal为专门为财务相关问题设计的数据类型。与double相比，decimal 类型具有更高的精度和更小的范围，它适合于财务和货币计算。

　　2、float 和 double 在存储的时候，存在精度损失的问题，但是decimal没有或者很少有

　　二、日期和时间类型

　　表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

　　每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

　　TIMESTAMP类型有专有的自动更新特性。

　　类型大小 (字节)范围格式用途DATE31000-01-01/9999-12-31YYYY-MM-DD日期值TIME3'-838:59:59'/'838:59:59'HH:MM:SS时间值或持续时间YEAR11901/2155YYYY年份值DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值TIMESTAMP41970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值，时间戳

　　DATETIME与TIMESTAMP的比较：

　　1.占用空间类型占据字节表示形式datetime8 字节yyyy-mm-dd hh:mm:sstimestamp4 字节yyyy-mm-dd hh:mm:ss

　　2.表示范围：类型表示范围datetime'1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'timestamp'1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'

　　翻译为汉语即"时间戳"，它是当前时间到 Unix元年(1970 年 1 月 1 日 0 时 0 分 0 秒)的秒数。对于某些时间的计算，如果是以 的形式会比较困难，假如我是 出生，现在的时间是 ，那么要计算我活了多少秒钟用 还需要函数进行转换，但是 直接相减就行。

　　3.时区：

　　只占 4 个字节，而且是以的格式储存， 它会自动检索当前时区并进行转换。

　　以 8 个字节储存，不会进行时区的检索.

　　也就是说，对于来说，如果储存时的时区和检索时的时区不一样，那么拿出来的数据也不一样。对于来说，存什么拿到的就是什么。

　　还有一个区别就是如果存进去的是，会自动储存当前时间，而 会储存 。

　　三、字符串类型

　　字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

　　类型大小用途CHAR0-255字节定长字符串VARCHAR0-65535 字节变长字符串TINYBLOB0-255字节不超过 255 个字符的二进制字符串TINYTEXT0-255字节短文本字符串BLOB0-65 535字节二进制形式的长文本数据TEXT0-65 535字节长文本数据MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据MEDIUMTEXT0-16 777 215字节中等长度文本数据LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据LONGTEXT0-4 294 967 295字节极大文本数据

　　CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

　　BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

　　BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

　　有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

　　CHAR与VARCHAR的比较：

　　1.char的长度是不可变的，而varchar的长度是可变的，也就是说，定义一个char[10]和varchar[10],如果存进去的是‘csdn’,那么char所占的长度依然为10，除了字符‘csdn’外，后面跟六个空格，而varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的。

　　2.char的存取数度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率，而varchar是以空间效率为首位的。

　　3.char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节。

　　四、缺省

　　创建表时，在数据类型后面加上DEFAULT '默认值' 即可使用缺省功能。

　　值得注意的是mysql 5.0 以上的版本中text类型是默认不支持有缺省值的，如果要使用需要对配置文件my.ini进行调整。具体方法是将sql_mode的值去掉。然后重启MySQL服务即可

　　4.DDL语句

　　/\反引号、单引号和双引号

　　参考：

　　MySQL中单引号和反引号的区别是什么？ - 蒋川的回答 - 知乎 https://www.zhihu.com/question/39853371/answer/2215848391单引号( ' )和双引号( " ) 用于字符串和日期，作用相同。反引号( ` )用于表名和列名。数字作为数值时，不要用任何引号，保留符、关键字不要用任何引号。

　　MySQL / MariaDB 使用单引号、双引号、反引号、不加引号的各类情况汇总。

　　我们可在这张图中，概览 MySQL / MariaDB 符号使用的全局规律。

　　单引号、双引号

　　单引号( ' ) 用于 VALUES() 里包含的字符串和日期。

　　当然 MySQL 也支持双引号括字符串，它与单引号可以互换使用，不过如果没有什么特殊原因，还是尽量选择用单引号。双引号很有可能引起未知错误。

　　MySQL 里的 DATE 和 DATETIME 也需要使用单引号括起来作为字符串，比如'1970-01-01 00:00:00' 。当然，某些版本「时间」你不用单引号也能 INSERT INTO，不过劝你不要这么做，都括起来养成良好的编程习惯，安全又省心。

　　反引号

　　反引号（`）[反引号在键盘的左上角]，用于标记「表名」和「列名」，通常情况加不加都行。但如果「表名」或「列名」为 MySQL 保留符，那么一定要加反引号作为区分。

　　不过建议尽量避免使用保留符作为表名和列名，以免引起未知错误。

　　不用引号的情况

　　MySQL 关键字、保留字、函数，比如 NULL 和 NOW() 都不能使用引号括起来。如果使用单引号'NOW()'把函数括起来，那么对于 MySQL 来说，它就变成了字符串，失去了函数的功能。

　　总结

　　一定要掌握好如何在 MySQL / MariaDB 中，使用或不使用引号的规律，乱用引号是新手最容易出现的问题，特别是因为引号引起的错误，报错中很难提示出来。这种错误很可能让你要花好久时间，才能排查出来。而实际上引号的使用并没有多复杂。你可以保存本教程中的概览，待以后备查和复习。

　　1.数据库操作

　　1.查询

　　show databases ;查询所有数据库

　　select database();查询当前数据库

　　2.创建

　　create database [if not exists] 数据库名称 [default charset设置默认字符集] [collate 排序规则];

　　遇到了这个问题

　　注意纯数字字符需要加上反引号（即键盘第一列上数第二个键）

　　创建名为114514的数据库

　　3.删除

　　drop database [if exists] 数据库名；

　　4.使用

　　use 数据库名;

　　2.表操作

　　使用use指令进入数据库

　　1.查询表

　　查询所有对象：show tables;

　　查询表结构：desc 表名称;

　　查询指定表的建表语句 show create table 表名;

　　2.创建表

　　create table 表名(

　　字段1 字段1类型 [comment] 注释,

　　字段2 字段2类型 （同上）

　　)comment 表注释;

　　3.添加字段

　　alter table 表名 add 字段名 类型（长度）comment 注释 约束；

　　4.修改字段

　　修改字段名和类型：alter table 表名 change 旧字段名 新字段名 类型（长度）comment 注释 约束；

　　仅修改数据类型：alter table 表名 modify 字段名 新数据类型（长度）comment 注释 约束；

　　5.删除字段

　　alter table 表名 drop 字段名；

　　6.修改表名

　　alter table 表名 rename to 新表名；

　　7.删除表、删除表内容

　　删除表（表会全部消失）

　　drop table (if exists) 表名；

　　删除表内容 （删除并重新创建空表）

　　truncate table 表名；

　　5.DML、DCL语句

　　1.DML语句

　　1.向字段中添加数据

　　向指定字段添加数据：insert into 表名 （字段名1，字段名2，...）values（值1，值2，...）；

　　值与字段顺序一一对应，且值的格式必须符合字段的格式

　　向全部字段中添加数据 ：insert into 表名 values（值1，值2，...）；

　　批量添加数据：insert into 表名 字段名1，字段名2，...）values （值1，值2，...），（值1，值2，...），....；

　　2.修改数据

　　update 表名 set 字段名1=值1，字段名2=值2，...[where 条件];

　　3.删除数据

　　delete from 表名 where 条件；

　　2.DCL 语句

　　dcl用来管理控制数据库的查看和访问权限

　　1.用户管理

　　create user ‘用户名’@% identified by ‘密码’；创建一个可以在任意主机上访问的用户

　　2.权限控制

　　常见用户权限如下：

　　撤销所有权限：revoke all

　　6.DQL语句

　　单表查询 :select from where group by having order by limit

　　1.基本查询

　　select 字段1【as 别名1】,... 表名；*代表所有的字段

　　查询去重：select distinct 字段列表 from 表名；查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。SELECT 命令可以读取一条或者多条记录。你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据你可以使用 WHERE 语句来包含任何条件。你可以使用 LIMIT 属性来设定返回的记录数。你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

　　2.where关键词

　　where关键词后可以跟如下条件：

　　3.DQL聚合函数

　　聚合函数作用于表的某一列数据，常见的聚合函数如下：

　　select 聚合函数（字段列表）from 表名；

　　4.分组查询

　　select 字段列表 from 表名 【where 条件】group by 分组字段名 【having 分组后过滤条件】；

　　where 用于分组前筛选，having用于分组后过滤

　　where中不能使用聚合函数判断，而having可以

　　如查询至少被5名学生选中的课程：

　　select class from Courses group by class having count(distinct student)>=5;

　　5.排序查询

　　select 字段列表 from 表名 order by 字段1 排序方式1 ，字段2 排序方式2；

　　字段的顺序决定了多重排序的优先级

　　升序：asc（默认）， 降序：desc

　　6.分页查询

　　select 字段列表 from 表名 limit 起始索引，查询记录数；

　　“方言”，即在SQL语言下不同数据库使用的不同关键词实现相同的功能

　　举个例子： select * from tb limit 10,10；表示从第二页开始查询，每页返回前十个数据