MySQL基础

MySQL基础

一、MySQL-DDL

1、使用DDL语句创建表

CREATE TABLE 表名(列名 类型,列名 类型......);

2.使用DDL语句删除表

DROP TABLE 表名;

3、使用DDL语句修改表

ALTER TABLE 旧表名 RENAME 新表名;

4、使用DDL语句修改列名

ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列 名 类型;

5、使用DDL语句修改列类型

ALTER TABLE 表名 MODIFY 列名 新类型;

6、使用DDL语句添加新列

ALTER TABLE 表名 ADD COLUMN 新列名 类型;

7、使用DDL语句删除指定的列

ALTER TABLE 表名 DROP COLUMN 列名;

二、MySQL-约束

2.1、约束概述

数据库约束是对表中的数据进行进一步的限制，保证数据的正确 性、有效性和完整性。

• 主键约束(Primary Key) PK

  ​ 主键约束是使用最频繁的约束。在设计数据表时，一般情况 下，都会要求表中设置一个主键。 主键是表的一个特殊字段，该字段能唯一标识该表中的每条 信息。例如，学生信息表中的学号是唯一的。

• 外键约束(Foreign Key) FK

  外键约束经常和主键约束一起使用，用来确保数据的一致 性。

• 唯一性约束(Unique)

  唯一约束与主键约束有一个相似的地方，就是它们都能够确 保列的唯一性。与主键约束不同的是，唯一约束在一个表中 可以有多个，并且设置唯一约束的列是允许有空值的。

• 非空约束(Not Null)

  非空约束用来约束表中的字段不能为空。

• 检查约束(Check)

​ 检查约束也叫用户自定义约束，是用来检查数据表中，字段 值是否有效的一个手段，但目前 MySQL 数据库不支持检查 约束。

2.2、主键约束(Primary Key)

• 单一主键 使用一个列作为主键列，当该列的值有重复时，则违反唯一约 束。
• 联合主键 使用多个列作为主键列，当多个列的值都相同时，则违反唯一约 束。

2.2.1、修改表添加主键约束

ALTER TABLE 表名 ADD PRIMARY KEY(列名);

2.2.2、主键自增长

• 一个表中只能有一个列为自动增长。

• 自动增长的列的类型必须是整数类型。

• 自动增长只能添加到具备主键约束与唯一性约束的列上。

• 删除主键约束或唯一性约束，如果该列

• 拥有自动增长能力，则需要先去掉自动增长然 后在删 除约束。

  alter table 表名 modify 主键 类型 auto_increment;
  

2.2.3、删除主键

使用DDL语句删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

2.3.外键约束(Foreign Key)

2.3.1、添加外键约束

使用DDL语句添加外键约束

ALTER  TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN  KEY( 列 名 ) REFERENCES 参照的表名 (参照的列名);

2.3.2、删除外键约束

使用DDL语句删除外键约束。

ALTER TABLE 表名 DROP FOREIGN KEY 约束名;

2.4、唯一约束（Unique）

2.4.1、添加唯一性约束

使用DDL语句添加唯一性约束

ALTER TABLE  表名 ADD CONSTRAINT  约束名 UNIQUE(列名);

2.4.2、删除唯一性约束

使用DDL语句删除唯一性约束

ALTER TABLE  表名 DROP KEY  约束名;

2.5、非空约束(Not Null)

2.5.1、添加非空约束

使用DDL语句添加非空约束。

ALTER TABLE  表名 MODIFY  列名 类型 NOT NULL;

2.5.2、删除非空约束

使用DDL语句删除非空约束。

ALTER TABLE  表名 MODIFY  列名 类型 NULL;

2.6、创建表时添加约束

查询表中的约束信息：

SHOW KEYS FROM  表名;

例子：

create table depts(department_id int primary key auto_increment,department_name varchar(30) unique,location_id int not null);

三、MySQL-DML

3.1、添加数据(INSERT)

3.1.1、选择插入

INSERT INTO  表名(列名 1 ，列名 2 ，列名 3.....) VALUES(值 1 ，值 2 ，值 3......);

3.1.2、完全插入

INSERT INTO  表名 VALUES(值 1 ，值 2 ，值 3......);

注意：如果主键是自动增长，需要使用 default 或者 null 或者 0 占位。

3.2、默认值处理(DEFAULT)

在 MySQL 中可以使用 DEFAULT 为列设定一个默认值。如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。

3.2.1、创建表时指定列的默认值

CREATE TABLE 表名(列名 类型 default 默认值,......);

3.2.2、修改表添加新列并指定默认值

ALTER TABLE 表名 ADD COLUMN 列名 类型 DEFAULT 默认值;

3.2.3、插入数据时的默认值处理

如果在插入数据时并未指定该列的值，那么MySQL 会将默认值添加到该列中。如果是 完全项插入需要使用 default 来占位。

3.3、更新数据(UPDATE)

UPDATE 表名 SET  列名=值，列名=值 WHERE 条件;

注意：更新语句中一定要给定更新条件，否则表中的所有数据都会被更新。

3.4、删除数据

DELETE FROM  表名 WHERE 条件;

注意：在DELETE语句中，如果没有给定删除条件则会删除表中的所有数据。

3.5、TRUNCATE清空表

TRUNCATE TABLE  表名;

3.5.1、清空表时DELETE与 TRUNCATE 区别

1. truncate 是整体删除(速度较快)， delete 是逐条删除(速度较慢)；
2. truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete 高的原因；
3. truncate 是会重置自增值，相当于自增列会被置为初始值，又重新从 1 开始记录，而 不是接着原来的值。而 delete 删除以后， 自增值仍然会继续累加。

3.6、查询数据

3.6.1、语句的功能

SELECT 语句从数据库中返回信息。使用一个 SELECT 语句，可以做下面的事：

• **列选择：**能够使用 SELECT 语句的列选择功能选择表中的列，这些列是想

  要用查询返回的。当查询时，能够返回列中的数据。

• **行选择：**能够使用 SELECT 语句的行选择功能选择表中的行，这些行是想

  要用查询返回的。能够使用不同的标准限制看见的行。

• **连接：**能够使用 SELECT 语句的连接功能来集合数据，这些数据被存储在不

  同的表中，在它们之间可以创建连接，查询出我们所关心的数据。

3.6.2、SELECT基本语法

基本 SELECT 语句

在最简单的形式中，SELECT 语句必须包含下面的内容：

• 一个 SELECT 子句，指定被显示的列
• 一个 FROM 子句，指定表，该表包含 SELECT 子句中的字段列表

在语法中：

语句	含义
SELECT	是一个或多个字段的列表
*	选择所有的列
DISTINCT	禁止重复
column | expression	选择指定的字段或表达式
alias	给所选择的列不同的标题
FROM table	指定包含列的表

3.6.3、列的选择

选择所有列：

用跟在 SELECT 关键字后面的星号 (*)，你能够显示表中数据的所有列。

select * from departments;

选择指定列:

能够用 SELECT 语句来显示表的指定列，指定列名之间用逗号分隔。

3.6.4、算数表达式

需要修改数据显示方式，如执行计算，或者作假定推测，这些都可能用到算术表达式。一个算术表达式可以包含列名、固定的数字值和算术运算符。

运算符的优先级：

1. 乘法和除法比加法和减法的优先级高
2. 相同优先级的运算符从左到右计算
3. 圆括号用于强制优先计算，并且使语句更清晰

3.6.5、MySQL中定义空值

1. null是一个未分配的、未知的，或不适用的值
2. null不是0，也不是空格

如果一行中的某个列缺少数据值，该值被置为 null， 或者说包含一个空。空是一个难以获得的、未分配的、未知的，或不适用的值。空和 0 或者空格不相同。 0 是一个数字，而空格是一个字符。

注意：

包含空值的算术表达式计算结果为空。

3.6.6、MySQL中的别名

使用列别名

SELECT  列名 AS  列别名 FROM  表名 WHERE  条件;

使用列别名

SELECT  表别名.列名 FROM  表名 as 表别名 WHERE  条件;

3.6.7、MySQL中去除重复

除去相同的行

SELECT DISTINCT 列名 FROM 表名;

3.6.8、查询中的行选择

用 WHERE 子句限制从查询返回的行。一个 WHERE 子句包含一个必须满足的条件，WHERE 子句紧跟着 FROM 子句。如果条件是 true，返回满足条件的行。

在语法中：

WHERE 限制查询满足条件的行

condition 由列名、表达式、常数和比较操作组成.

SELECT * |  投影列 FROM  表名 WHERE  选择条件;

3.6.9、MySQL中的比较条件

运算符	含义
大于	>
小于	<
等于	=
大于等于	>=
小于等于	<=
不等于	<>
between and	在什么之间
like	像
in/is null	在什么里面/是否为空

1. 可以用 BETWEEN 范围条件显示基于一个值范围的行。指定的范围包含一个下限和一个上限。
2. 使用工IN成员条件测试在列表中的值
3. 使用工IKE 条件执行有效搜索串值的通配符扌搜索条件既可以包含文字也可以包含数字:
   %_表示零个或多个字符
   _表示一个字符
4. NULL 条件，包括 IS NULL 条件和 IS NOT NULL 条件。IS NULL 条件用于空值测试。空值的意思是难以获得的、未指定的、未知的或者不适用的。因此，你不能用 = ，因为 null 不能等于或不等于任何值。

3.6.10、逻辑条件

运算符	含义
AND	如果两个组成部分的条件都为真，返回TRUE
OR	如果两个组成部分中的任一个条件为真，返回TRUE
NOT	如果跟随的条件为假，返回TRUE

逻辑条件组合两个比较条件的结果来产生一个基于这些条件的单个的结果，或者逆

转一个单个条件的结果。当所有条件的结果为真时，返回行。

SQL 的三个逻辑运算符是：

• AND
• OR
• NOT

可以在 WHERE 子句中用 AND 和 OR 运算符使用多个条件。

3.7、使用 ORDER BY 排序

用ORDER BY子句排序行

默认：

-ASC:升序排序，

-DESC:降序排序

注意：

ORDERBY子句在SELECT语句的最后。

在一个不明确的查询结果中排序返回的行。ORDER BY 子句用于排序。如果使用了 ORDER BY 子句，它必须位于 SQL 语句的最后。

SELECT 语句的执行顺序如下：

• FROM 子句
• WHERE 子句
• SELECT 子句
• ORDER BY 子句

3.8、SQL函数

3.8.1、函数简介

函数是 SQL 的一个非常强有力的特性，函数能够用于下面的目的：

• 执行数据计算
• 修改单个数据项
• 操纵输出进行行分组
• 格式化显示的日期和数字
• 转换列数据类型

SQL 函数有输入参数，并且总有一个返回值。

3.8.2、函数分类

1. 单行函数

   单行函数仅对单个行进行运算，并且每行返回一个结果。

   常见的函数类型：

   • 字符
   • 数字
   • 日期
   • 转换

   单行函数:

   操纵数据项
   接受多个参数并返回一个值

   作用于每一个返回行，每行返回一个结果

   可以修改数据类型可以嵌套
   接受多个参数，参数可以是一个列或者一个表达式

2. 多行函数

多行函数能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数。

3.8.3、字符函数

大小写处理函数

函数	描述	实例
LOWER(s)|LCASE(s)	将字符串 s 转换 为小写	将字符串 OLDLU转换为小写： SELECT LOWER(“OLDLU”); – oldlu
UPPER(s)|UCASE(s)	将字符串s转换为 大写	将字符串 oldlu转换为大写： SELECT UPPER(“oldlu”); – OLDLU

字符处理函数

函数	描述	实例
LENGTH(s)	返回字符串 s 的长度	返回字符串oldlu的字符数 SELECT LENGTH(“oldlu”); --5;
CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合 并为一个字符串	合并多个字符串 SELECT CONCAT("sxt ", "teacher ", “oldlu”); --sxt teacher oldlu;
LPAD(s1,len,s2)	在字符串 s1 的开始处填充字 符串 s2，使字符串长度达到 len	将字符串 x 填充到 oldlu字符串的开始处：SELECT LPAD(‘oldlu’,8,‘x’); – xxxoldlu
LTRIM(s)	去掉字符串 s 开始处的空格	去掉字符串 oldlu开始处的空格： SELECT LTRIM(" oldlu") ;-- oldlu
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中 的字符串 s1	将字符串 oldlu 中的字符 o 替换为字符 O：SELECT REPLACE(‘oldlu’,‘o’,‘O’); --Oldlu
REVERSE(s)	将字符串s的顺序反过来	将字符串 abc 的顺序反过来： SELECT REVERSE(‘abc’); – cba
RPAD(s1,len,s2)	在字符串 s1 的结尾处添加字 符串 s2，使字符串的长度达 到 len	将字符串 xx填充到 oldlu字符串的结尾处：SELECT RPAD(‘oldlu’,8,‘x’); – oldluxxx
RTRIM(s)	去掉字符串 s 结尾处的空格	去掉字符串 oldlu 的末尾空格： SELECT RTRIM("oldlu "); – oldlu
SUBSTR(s, start, length)	从字符串 s 的 start 位置截取 长度为 length 的子字符串	从字符串 OLDLU中的第 2 个位置截取 3个 字符： SELECT SUBSTR(“OLDLU”, 2, 3); – LDL
SUBSTRING(s,start, length)	从字符串 s 的 start 位置截取 长度为 length 的子字符串	从字符串 OLDLU中的第 2 个位置截取 3个 字符： SELECT SUBSTRING(“OLDLU”, 2, 3); – LDL
TRIM(s)	去掉字符串 s 开始和结尾处的 空格	去掉字符串 oldlu 的首尾空格： SELECT TRIM(’ oldlu ');–oldlu

3.8.4、数字函数

函数名	描述	实例
ABS(x)	返回 x 的绝对值	返回 -1 的绝对值： SELECT ABS(-1) – 返回1
ACOS(x)	求 x 的反余弦值(参数是弧度)	SELECT ACOS(0.25);
ASIN(x)	求反正弦值(参数是弧度)	SELECT ASIN(0.25);
ATAN(x)	求反正切值(参数是弧度)	SELECT ATAN(2.5);
ATAN2(n, m)	求反正切值(参数是弧度)	SELECT ATAN2(-0.8, 2);
AVG(expression)	返回一个表达式的平均值，expression 是一个字段	返回 Products 表中Price 字段的平均值： SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(x)	返回大于或等于 x 的最小整数	SELECT CEIL(1.5) – 返回2
CEILING(x)	返回大于或等于 x 的最小整数	SELECT CEILING(1.5); – 返回2
COS(x)	求余弦值(参数是弧度)	SELECT COS(2);
COT(x)	求余切值(参数是弧度)	SELECT COT(6);
COUNT(expression)	返回查询的记录总数， expression 参数是一个字段或 者 * 号	返回 Products 表中 products 字段总共有多少条记录： SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
DEGREES(x)	将弧度转换为角度	SELECT DEGREES(3.1415926535898) – 180
n DIV m	整除，n 为被除数，m 为除数	计算 10 除于 5： SELECT 10 DIV 5; – 2
EXP(x)	返回 e 的 x 次方	计算 e 的三次方： SELECT EXP(3) – 20.085536923188
FLOOR(x)	返回小于或等于 x 的最大整数	小于或等于 1.5 的整数： SELECT FLOOR(1.5) – 返回1
GREATEST(expr1,expr2, expr3, …)	返回列表中的最大值	返回以下数字列表中的最大值： SELECT GREATEST(3, 12, 34, 8,25); – 34 返回以下字符串列表中的最大值： SELECT GREATEST(“Google”, “Runoob”, “Apple”); – Runoob
LEAST(expr1, expr2, expr3, …)	返回列表中的最小值	返回以下数字列表中的最小值： SELECT LEAST(3, 12, 34, 8, 25);-- 3 返回以下字符串列表中的最小值： SELECT LEAST(“Google”, “Runoob”, “Apple”); – Apple
LN	返回数字的自然对数，以 e 为 底。	返回 2 的自然对数： SELECT LN(2); – 0.6931471805599453
LOG(x) 或LOG(base, x)	返回自然对数(以 e 为底的对 数)，如果带有 base 参数，则 base 为指定带底数。	SELECT LOG(20.085536923188) – 3 SELECT LOG(2, 4); – 2
LOG10(x)	返回以 10 为底的对数	SELECT LOG10(100) – 2
LOG2(x)	返回以 2 为底的对数	返回以 2 为底 6 的对数： SELECT LOG2(6); --2.584962500721156
MAX(expression)	返回字段 expression 中的最 大值	返回数据表 Products 中字段 Price 的最大值： SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression)	返回字段 expression 中的最 小值	返回数据表 Products 中字段 Price 的最小值： SELECT MIN(Price) AS MinPrice FROM Products;
MOD(x,y)	返回 x 除以 y 以后的余数	5 除于 2 的余数： SELECT MOD(5,2) – 1
PI()	返回圆周率(3.141593）	SELECT PI() --3.141593
POW(x,y)	返回 x 的 y 次方	2 的 3 次方： SELECT POW(2,3) – 8
POWER(x,y)	返回 x 的 y 次方	2 的 3 次方： SELECT POWER(2,3) – 8
RADIANS(x)	将角度转换为弧度	180 度转换为弧度： SELECT RADIANS(180) --3.1415926535898
RAND()	返回 0 到 1 的随机数	SELECT RAND() --0.93099315644334
ROUND(x)	返回离 x 最近的整数	SELECT ROUND(1.23456) --1
SIGN(x)	返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1	SELECT SIGN(-10) – (-1)

函数名	描述	实例
SIN(x)	求正弦值(参数是弧度)	SELECT SIN(RADIANS(30)) – 0.5
SQRT(x)	返回x的平方根	25 的平方根： SELECT SQRT(25) – 5
SUM(expression)	返回指定字段的总和	计算 OrderDetails 表中字段 Quantity 的总和： SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
TAN(x)	求正切值(参数是弧度)	SELECT TAN(1.75); – -5.52037992250933
TRUNCATE(x,y)	返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区 别是不会进行四舍五入）	SELECT TRUNCATE(1.23456,3) – 1.234

ROUND 函数四舍五入列、表达式或者 n 位小数的值。如果第二个 参数是 0 或者缺少，值被四舍五入为整数。如果第二个参数是 2值 被四舍五入为两位小数。如果第二个参数是–2，值被四舍五入到小 数点左边两位。

TRUNCATE函数的作用类似于 ROUND 函数。如果第二个参数是 0 或者缺少，值被截断为整数。如果第二个参数是 2，值被截断为两 位小数。如果第二个参数是–2，值被截断到小数点左边两位。与 ROUND 最大的区别是不会进行四舍五入。

3.8.5、日期函数

在MySQL中允许直接使用字符串表示日期，但是要求字符串的日期格式必须为：‘YYYY-MM-DD HH:MI:SS’ 或者‘YYYY/MM/DD HH:MI:SS’;

*函数名*	*描述*	*实例*
CURDATE()	返回当前日期	SELECT CURDATE(); -> 2018-09-19
CURTIME()	返回当前时间	SELECT CURTIME(); -> 19:59:02
CURRENT_DATE()	返回当前日期	SELECT CURRENT_DATE(); -> 2018-09-19
CURRENT_TIME()	返回当前时间	SELECT CURRENT_TIME(); -> 19:59:02
DATE()	从日期或日期时间表达式中提取日期值	SELECT DATE(“2017-06-15”); -> 2017-06-15
DATEDIFF(d1,d2)	计算日期 d1->d2 之间相隔的天数	SELECT DATEDIFF(‘2001-01-01’,‘2001-02-02’) ->-32
DAY(d)	返回日期值 d 的日期部分	SELECT DAY(“2017-06-15”); -> 15
DAYNAME(d)	返回日期 d 是星期几，如Monday,Tuesday	SELECT DAYNAME(‘2011-11-11 11:11:11’) ->Friday
DAYOFMONTH(d)	计算日期 d 是本月的第几天	SELECT DAYOFMONTH(‘2011-11-11 11:11:11’) ->11
DAYOFWEEK(d)	日期 d 今天是星期几，1 星期日，2 星期 一，以此类推	SELECT DAYOFWEEK(‘2011-11-11 11:11:11’) ->6
DAYOFYEAR(d)	计算日期 d 是本年的第几天	SELECT DAYOFYEAR(‘2011-11-11 11:11:11’) ->315
HOUR(t)	返回 t 中的小时值	SELECT HOUR(‘1:2:3’) -> 1
LAST_DAY(d)	返回给给定日期的那一月份的最后一天	SELECT LAST_DAY(“2017-06-20”); -> 2017-06-30
MONTHNAME(d)	返回日期当中的月份名称，如 November	SELECT MONTHNAME(‘2011-11-11 11:11:11’) -> November
MONTH(d)	返回日期d中的月份值，1 到 12	SELECT MONTH(‘2011-11-11 11:11:11’) ->11
NOW()	返回当前日期和时间	SELECT NOW() -> 2018-09-19 20:57:43
SECOND(t)	返回 t 中的秒钟值	SELECT SECOND(‘1:2:3’) -> 3
SYSDATE()	返回当前日期和时间	SELECT SYSDATE() -> 2018-09-19 20:57:43
TIMEDIFF(time1,time2)	计算时间差值	SELECT TIMEDIFF(“13:10:11”, “13:10:10”); -> 00:00:01
TO_DAYS(d)	计算日期 d 距离 0000 年 1 月 1 日的天数	SELECT TO_DAYS(‘0001-01-01 01:01:01’) -> 366
WEEK(d)	计算日期 d 是本年的第几个星期，范围是0 到 53	SELECT WEEK(‘2011-11-11 11:11:11’) -> 45
WEEKDAY(d)	日期 d 是星期几，0 表示星期一，1 表示星 期二	SELECT WEEKDAY(“2017-06-15”); -> 3
WEEKOFYEAR(d)	计算日期 d 是本年的第几个星期，范围是0 到 53	SELECT WEEKOFYEAR(‘2011-11-11 11:11:11’) -> 45
YEAR(d)	返回年份	SELECT YEAR(“2017-06-15”); -> 2017

3.8.6、转换函数

隐式数据类型转换

隐式数据类型转换是指MySQL服务器能够自动地进行类型转换。如：可以将标准格式的字串日期自动转换为日期类型。

MySQL字符串日期格式为：‘YYYY-MM-DD HH:MI:SS’ 或 ‘YYYY/MM/DD HH:MI:SS’;

显示数据类型转换

显示数据类型转换是指需要依赖转换函数来完成相关类型的转换。

如：

• DATE_FORMAT(date,format) 将日期转换成字符串;
• STR_TO_DATE(str,format) 将字符串转换成日期;

3.8.7、通用函数

函数名	描述
IF(expr,v1,v2)	如果表达式 expr 成立，返回结果 v1；否 则，返回结果 v2。
IFNULL(v1,v2)	如果 v1 的值不为 NULL，则返回 v1，否则返 回 v2。
ISNULL(expression)	判断表达式是否为 NULL
NULLIF(expr1, expr2)	比较两个参数是否相同，如果参数 expr1 与expr2 相等 返回 NULL，否则返回 expr1
COALESCE(expr1, expr2,	返回参数中的第一个非空表达式（从左向 右）
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 … WHEN conditionN THEN resultN ELSE result END	CASE 表示函数开始，END 表示函数结束。 如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不 成立则返回 result，而当有一个成立之后， 后面的就不执行了。