MYSQL 总结——1

阅读量：5268 次

发布时间：2019-06-14

本文共 11890 字，大约阅读时间需要 39 分钟。

数据类型

1、数值型

2、日期/时间型

3、文本型

数据库

1、创建数据库

2、删除数据库

数据表

1、创建数据表 create

2、删除数据表 drop

3、修改数据表

4、修改表名

5、drop、delete与truncate的区别

查看数据库、数据表

1、使用/选择数据库 use

2、查看数据库 show

3、查看数据表 show

4、查看表结构 desc/describe/show columns from

5、查看数据库下数据表的信息

6、查看创建数据表的语句

表数据操作

1、插入数据 insert

2、更新数据 update

3、Group by、having

4、Join

5、Union操作符

6、去重操作 distinct/group by

7、聚集函数

8、查询中使用的各种运算符

9、为列名/表名指定别名

10、MySql函数

11、Select语句的一般格式

12、Mysql中字符串使用单引号

正文

1、登录：-h localhost -u root -p root

2、mysql命令行有两种方式结尾：a.分号（;） b. \g

注意： \g 与前面的命令中间有空格，否则会报错，如下图。

参考网址：https://www.cnblogs.com/stono/p/8227394.html

数据类型

1、MySql中为字段选择合适的数据类型是数据库优化的方式之一。

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。

数值型：NUMBER型

这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-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	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	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

Decimal为SQL Server、Mysql等数据库的一种数据类型，不属于浮点数类型，可以在定义时划定整数部分以及小数部分的位数。

使用精确小数类型不仅能够保证数据计算更为精确，还可以节省存储空间，例如百分比使用decimal( 4, 2) 即可。存储数据范围是：-10^38 ~ 10^38-1 的固定精度和小数位的数字。一个decimal类型的数据占用了2~17个字节。

与double相比，decimal类型具有更高的精度和更小的范围，它适合于财务和货币计算。

Decimal数据类型详解，参考网址1：https://blog.csdn.net/weixin_38125277/article/details/80837321

网址2： https://blog.csdn.net/qq_34306360/article/details/79721619

日期/时间型：DATE型

类型	大小 (字节)	范围	格式	用途
DATE	3	'1000-01-01' 到 '9999-12-31'	YYYY-MM-DD	日期值
TIME	3	'-838:59:59' 到 '838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	2位或4位格式的年。4位格式：1901到2155。 2位格式：70到69，表示从1970年到2069年。	YYYY	年份值
DATETIME	8	'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'	YYYY-MM-DD HH:MM:SS	日期和时间值的组合
TIMESTAMP 时间戳	4	'1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07 TIMESTAMP值使用Unix纪元('1970-01-01 00:00:00' UTC)至今的描述来存储。	YYYY-MM-DD HH：MM：SS	日期和时间值的组合，时间戳

说明：

虽然DATETIME和TIMESTAMP返回相同的格式，但它们的工作方式很不同。在INSERT 或 Update查询中，TIMESTAMP自动把自身设置为当前的日期和时间。TimeStamp也接受不同的格式，比如YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

文本型：

类型	大小	用途
CHAR	0-255字节	定长字符串；保存固定长度的字符串，在括号中指定可保存最大字符串的长度。
VARCHAR	0-65535 字节	变长字符串；保存可变长度的字符串，在括号中指定可保存最大字符串的长度。
TINYTEXT	0-255字节	短文本字符串
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
TEXT	0-65 535字节	长文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
BLOB	0-65 535字节	二进制形式的长文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

说明：

a. BINARY 和 VARBINARY 是二进制字符串，即它们包含的是字节字符串而不是字符字符串。

b. char 和 varchar：

(1) char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格，varchar 不限于此。

(2) char(n) 固定长度，char(4) 不管是存入几个字符，都将占用 4 个字节，varchar 是存入的实际字符数 +1 个字节（n<=255）或2个字节(n>255)，所以 varchar(4)存

入3个字符将占用4个字节。

(3) char类型的字符串检索速度要比varchar类型快。

c. varchar 和 text

（1）varchar 可指定 n，text 不能指定，内部存储 varchar 是存入的实际字符数 +1 个字节（n<=255）或 2 个字节(n>255)，text 是实际字符数 +2 个字节。

（2）text 类型不能有默认值。

（3）varchar 可直接创建索引，text 创建索引要指定前多少个字符。varchar 查询速度快于 text, 在都创建索引的情况下，text 的索引似乎不起作用。

d. 二进制数据（_Blob）和 text

（1）存储方式不同，_blob 以二进制方式存储，不区分大小写。 _text 以文本方式存储，英文存储区分大小写。

（2） _blob 存储的数据只能整体读出。

（3） _text 可以指定字符集， _blob 不能指定字符集。

参考网址：http://www.runoob.com/mysql/mysql-data-types.html

数据库

1、创建数据库：create database 数据库名；例: create database info;

说明：创建数据库前先判断是否存在该数据库的语句：create database if not exists info default charset utf8 collate utf8_general_ci;

a. 如果数据库不存在则创建，存在则不创建。 b. 创建数据库Info，并设定编码集为utf8.

2、删除数据库：drop database 数据库名；例： drop database info;

说明：在mysql中执行删除数据库命令后，会弹出一个提示框。

实例：从下图中可以看出，删除数据库时中间" drop test "会报错。

数据表

1、创建基本表： create table 表名(<表名> < 数据类型>)；查看创建表的语句： show create table 表名； show create table course;

说明：a. 在创建数据表时，常常指定数据引擎以及字符集类型，如：Engine=InnoDB DEFAULT CHARSET=utf8;

b. 讲解很详细，参考网址：https://www.cnblogs.com/BeginMan/p/3249472.html

创建数据表的语句和查看创建表时的语句：

2、删除基本表：drop table 表名

说明：a. truncate table 表名；

b. delete from 表名 [where 子句];

注：如果省略where子句，表示删除表中全部元组，但表的定义仍在字典中。也就是说，delete语句删除的是表中的数据，而不是关于表的定义。

c. 删除数据表三种方式的区别：

（1） drop table 表名；删除表全部数据和表结构，立刻释放磁盘空间，InnoDB和MyISAM都是如此。

（2） truncate table 表名；删除表全部数据，保留表结构，立刻释放磁盘空间，InnoDB和MyISAM都是如此。

（3） delete from 表名；删除表全部数据，保留表结构，MyISAM会立刻释放磁盘空间，InnoDB不会释放磁盘空间。

（4）delete from 表名 where xxx；带条件地删除表数据，保留表结构，MyISAM和InnoDB都不会释放磁盘空间。

（5）delete方式，如果想释放磁盘空间，使用 “ optimize table 表名；”，MyISAM和InnoDB都会释放磁盘空间。

delete from student where name= "张三"; optimize table student;

（6）delete from 方式虽然未释放磁盘空间，但是以后添加数据仍然可以使用这部分空间。

实例：

truncate 表名；表结构仍然存在。

delete from 表名 where clause ;

drop table 表名；再次查看表时会报错，提示表不存在

3、修改基本表：使用Alter table语句修改基本表

语法：

Alter table 表名

新增字段： Add column <新列名> <数据类型> [ 完整性约束]

删除字段： Drop column <列名> 注释： column 是可选的，可带、可不带

修改字段类型： Alter column <列名> <数据类型>

说明：

a. 新增字段 add 和删除字段 drop 语句中的column 是可选的，可带，可不带。

b. 删除字段 drop

c. 修改字段 alter, modify, change

Modify：只修改字段类型，不能修改字段名称.

语法： alter table 表名 modify 字段名称字段类型 [约束]; alter table student modify dizhi text;

Change ：可选择是否修改字段类型和字段名称.

change关键字后，要修改的字段名，新字段名及新数据类型，这三项缺一不可。

由于三项必须填写，如果不想修改字段类型和字段名称，那么在新字段名称和新字段类型的位置仍写原字段名称或字段类型，这种方法可行。

语法： alter table 表名 change 字段名称新字段名称新字段类型； alter table student change i j bigint;

Alter ：只能添加、删除默认值，不能修改字段类型，不能修改字段名称。添加默认值时一定要加上关键字set。

修改字段默认值： Alter table student alter i set default 1000;

删除字段默认值： Alter table student alter i drop default;

d. 指定新增字段的位置：first 关键字（设定为第一列）和after关键字（设定位于某个字段之后），这两个关键字只作用于ADD子句。

新增字段：

删除字段：

添加默认值：使用alter 命令

4、修改表名

Alter table 原表名 rename to 新表名；

5、drop、delete和truncate 三种方式详细区别，参考网址：

drop用于删除数据库、数据表等，delete和truncate用于删除表数据

网址1： http://www.runoob.com/mysql/mysql-drop-tables.html

网址2： http://www.runoob.com/mysql/mysql-delete-query.html

网址3： https://blog.csdn.net/fzy198926/article/details/78190633

查看数据库、数据表

1、使用数据库、选择数据库：use 数据库名；

注意：创建和删除数据库、数据表时，中间都要带上database、table，而选择数据库时没有带database.

2、查看有哪些数据库： show databases; 注：结尾要带s

3、当前数据库下有哪些数据表：show tables; 注：结尾有s

4、查看表结构：desc 表名；或 describe 表名；简写/全写两种方式均可或 show columns from 表名；

5、查看某个数据库下所有表的类型、状态： show table status from 数据库名 [ like 'pattern' ]； like ' pattern ' 表示查询哪些表的具体表名。

a. show table status from try; 查询 try 数据库下所有表的信息。

网址： https://www.cnblogs.com/lxwphp/p/8109261.html 讲解了各个字段的含义。

b. 查看数据库 try 下 student 表中的信息。

6、查看创建数据表的语句： show create table 表名； show create table course;

表数据操作

1、向表中插入数据

a. insert into 表名 [（field1, field2 …… field n）]

values (value1, value2 …… value n);

说明：如果INTO子句没有指明（或列出）任何列名，则新插入的元组必须在每个属性列上均有值。

如果指出了新增加的元组在哪些属性列上要赋值，属性的顺序可以与CREATE TABLE中的顺序不一样。

b. 另一种方法：也称为复制另一张表数据

insert into ……select 子句

2、更新表数据：

update 表名 set field1=values1, field2 = values2 [where clause];

insert into 与 update的区别：insert into 是插入一条新记录，update是在原来表的数据上进行更新、修改。

3、Group by子句

Group by子句将查询结果按某一列或多列的值分组，值相等的为一组。

对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果。分组后聚集函数将作用于每一个组，即每一组都有一个函数值。

Having短语：如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则使用having短语指定筛选条件。

说明：

a. where子句与having短语的区别：在于作用对象不同。

where子句：作用于基本表或视图，从中选择满足条件的元组。

having短语：作用于组，从中选择满足条件的组。

b. （新的知识点）with rollup：可以实现在分组统计数据的基础上再进行相同的统计（sum, avg, count ……）

c. （新的知识点）coalesce( )函数：返回参数（列名）中第一个非NULL的字段值，注意不是为空。

该函数的详细介绍网址：https://blog.csdn.net/hackage4619/article/details/58586449

4、Join的使用

在真正的应用中经常需要从多个数据表中读取数据，因此在mysql中使用Join来联合多表查询。

JOIN按照功能大致分为如下四类：

a. Inner join （内连接，或等值连接，可以省略Inner，效果一样）：获取两个表中字段匹配关系的记录。

b. Left join（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

c. Right join（右连接）：获取右表所有记录，即使左表没有对应匹配的记录。

d. Full join （全连接）：只要左表和右表其中一个表中存在匹配，则返回行。full join结合了left join 和 right join的结果。

参考网址1： http://www.runoob.com/mysql/mysql-join.html

参考网址2：https://www.runoob.com/sql/sql-join-full.html

5、Union操作符

功能：用于连接两个以上的select语句的结果，使其组合到一个结果集中。

语法：

SELECT expression1, expression2, ... expression_n

FROM tables

[WHERE conditions]

UNION [ALL | DISTINCT]

SELECT expression1, expression2, ... expression_n

FROM tables

[WHERE conditions];

说明： a. （重要）可以只检索其中几个字段，并不是所有字段。

b. Distinct是默认选项，即默认情况下Union操作符删除了重复的数据。 All, 返回所有结果集，包含重复数据。

6、去重操作

a. distinct 关键字：select distinct age from person;

b. group by ： select age from person group by age;

7、聚集函数（Aggregate functions）

count( [ distinct | all ] * ) 统计元组个数

count( [ distinct | all ] <列名> ) 统计一列中值的个数

sum( [ distinct | all ] <列名>) 计算一列值的总和（此列必须是数值型）

avg( [ distinct | all ] <列名>) 计算一列值的平均值（此列必须是数值型）

max( [ distinct | all ] <列名>) 求一列值中的最大值

min( [ distinct | all ] <列名>) 求一列值中的最小值

说明：

在聚集函数遇到空值时，除count( * ) 外，都跳过空值而只处理非空值。注意, where子句中是不能使用聚集函数作为条件表达式的。

8、常用的查询条件

比较： =，>，<，>=，<=，!=，<>，!>，!< （不等于两种形式： != 和 <>）

确定范围： between and， not between and

确定集合： In, Not in

字符匹配：Like , Not Like

通配符： a. %（百分号）：任意长度的字符串。 b. _（下划线）：任意单个字符。

注：如果匹配串中不含通配符，like效果类似于=（等于）；not like类似于 != 或 <> （不等于）

空值： Is Null, Is not null

说明： a. 处理NULL值时不能使用 = NULL 或！=NULL，需要使用IS NULL和 IS NOT NULL运算符。

b. 不能比较NULL 和 0，它们不是等价的。

多重条件（逻辑运算）：And, Or, Not

9、为表名或列名指定别名（Alias）：select name as a from student as b;

10、MySql函数

数值处理函数：

弧度制：用弧长与半径之比度量对应圆心角角度的方式，叫做弧度制，用符号rad表示，读作弧度。由于圆弧长短与圆半径之比，不因为圆的大小而改变，所以弧度数是一个与圆的半径无关的量。角度以弧度给出时，通常不写弧度单位。弧度制的精髓就在于同意了度量弧与角的单位，从而大大简化了有关公式及运算，尤其在高等数学中，其优点格外明显。

函数名	功能描述	实例
ABS(x)	返回x的绝对值	返回-1的绝对值： Select abs(-1) -- 返回1
Radians(x)	将角度转换为弧度。	180°转换为弧度：select radians(180) -- 3.1415926
Cos(x)	求余弦值（参数x是弧度，而不是度数）	Select cos(2);
Sin(x)	求正弦值（参数x是弧度，而不是度数）	Select sin(radians(30));
Tan(x)	求正切值（参数x是弧度，而不是度数）	Select tan(1.75); -- 5.520379922
PI()	返回圆周率3.141593	Select PI() -- 3.141593
Exp(x)	返回e的x次方	计算e的三次方：select exp(3)
Sqrt(x)	返回x的平方根	25的平方根： Select sqrt(25);
Ceil(x)	返回大于或等于x的最小整数	select ceil(1.5) -- 返回2
Floor(x)	返回小于或等于x的最大整数	select floor(1.5) -- 返回1
Greatest(expr1, expr2, expr3……)	返回列表中的最大值。	select greatest(2, 134, 343, 8, 45) -- 返回343 Select greatest(“Google”,”Runoob”,”Apple”) -- 返回Runoob
Least(expr1, expr2, expr3……)	返回列表中的最小值。	select greatest(2, 134, 343, 8, 45) -- 返回2 Select greatest(“Google”,”Runoob”,”Apple”) -- 返回Apple

字符串函数：len(), substring(), lower(), upper(), left(), right(), Ltrim(), rtrim(), soundex()

函数名	功能描述	实例
Len()	返回文本字段中值的长度	Select len(city) from person
Substring(s,n,length) 同mid(s,n,length)	从字符串s的n位置开始，截取长度为length的子字符串
Lower(s), 同 lcase(s)	将字符串s全部转换为小写字母	Select lower(“RUNOOB”) -- runoob
Upper(s), 同 ucase(s)	将字符串s全部转换为大写字母
Left(s,n)	返回字符串s的前n个字符	Select left(“apple”,2) -- ap
Right(s,n)	返回字符串s的后n个字符	Select right(“runoob”,2) -- ob
Ltrim()	去掉字符串s开始处的空格	Select ltrim(“ runoob”) -- runoob
Rtrim()	去掉字符串s结尾处的空格	Select rtrim(“runoob ”) -- runoob
Soundex()
Space(n)	返回n个空格	Select space(10)
Strcmp(s1,s2)	比较两个字符串	Select strcmp(“apple”,”dog”)

Soundex( )函数的用法，参考网址：https://blog.csdn.net/qq_29741945/article/details/70159705

soundex( )是一个将任何文本串转换为描述其语音表示的字母数字模式的函数。soundex考虑了类似的发音字符和音节，使得对字符串进行发音比较而不是字母比较。虽然，soundex不是SQL概念，但是多数DBMS都提供对soundex的支持。

日期/时间函数

函数名	功能描述	实例
Now()	返回系统当前日期和时间
Date()	从日期或日期表达式中提取日期值	Select date(now()) 或 select date(“2018-12-22”)
Year()	返回年份
Month()	返回月份
Day()	返回日期部分
Hour()	返回时间中的小时部分
Minute()	返回时间中的分钟值
Dayofweek()	返回日期对应的星期几，1是星期日，2是星期一，以此类推
Datediff(d1,d2)	返回日期之间相隔的天数	Select datediff(“2018-11-28”,”2018-12-22”)
Curdate(), 同current_date()	返回当前日期
Curtime() , 同Curren_time()	返回当前时间