SQL高级(一)

修改表

修改表名

alter table <旧表名> rename [TO] <新表名>;

添加字段

alter table 表名 add 字段名 数据类型 [属性];

修改字段

alter table 表名 change 原字段名 新字段名 数据类型;

删除表中的字段

alter table 表名 drop 字段名;

添加主键

给表添加主键

alter table 表名 add constraint 主键名

primary key 表名 (主键字段)

查看所有修改表结构的SQL语句

help alter table

添加外键

给表添加外键

alter table 表名 add constraint 外键名

foreign key(外键字段) references 关联表名(关联字段)

提示

MySQL中，MyISAM存储类型的表不支持外键。因此，可以通过建立逻辑关联的方式保证数据的完整性和一致性

数据操纵语言

DML(Data Manipulation Language)

DML是对数据库中表记录的执行：

插入(INSERT)

插入单行数据

插入多行数据

将查询结果插入到新表

更新(UPDATE)

删除(DELETE)

软件开发1人员日常使用最频繁的操作

必备技能！！！

一次向表中插入一条记录

INSERT INTO 表名[(字段名列表)] values(值列表)

注意

字段名是可选的，如省略，则依次插入所有字段

多个列表和多个值之间使用逗号分隔

值列表和字段名列表一一对应

如果插入表中的部分字段数据，则字段名列表必填

一次向表中插入多条记录

INSERT INTO 表名 [(字段名列表)]

values(值列表1),(值列表2),.....(值列表n)

为避免表结构发生变化引发的错误，建议插入数据时写明具体字段

更新数据

update 表名

set 字段1 = 值1，字段2 = 值2 ,...字段n = 值 n

[where 条件]

删除数据

delete from 表名 [where条件];

truncate table 表名;

使用TRUNCATE语句删除表中记录后,将重置自增列,表结构及其字段、约束、索引保持不变，执行速度比DELETE语句快

如果要删除表，使用DROP语句

如果要保留表但删除表中的所有数据，如果与事务无关，可以使用TRUNCATE语句；如果与事务有关，则使用DELETE语句

数据查询语言

DQL(Data Query Language)

用于查询数据库的表中数据

是数据库中最为核心的语言，使用频率最高

语法

select <字段名列表>

from <表名或视图>

where <查询条件>

group by <分组的字段名>

having <条件>

order by <排序的字段名>[ASC或DESC]

为了提高语句的执行效率,建议：编写查询语句时，采用

select 字段列表 from 表名 where 条件表达式；

常用函数

SQL中将一些常用的数据处理操作封装起来，作为函数提供给程序员使用，可以提高程序员开发效率

MySQL支持的常用函数

字符串函数

时间日期函数

聚合函数

数学函数

字符串函数

函 数 名	作 用	举 例
CONCAT( str1,str1,…，strn)	连接字符串str1、str2、……、strn为一个完整字符串	SELECT CONCAT( 'MySQL',' is powerful.');返回：MySQL is powerful.
LOWER(str)	将字符串str中所有字符变为小写	SELECT LOWER( 'MySQL is powerful.');返回：mysql is powerful.
UPPER(str)	将字符串str中所有字符变为大写	SELECT UPPER( 'MySQL is powerful.');返回：MYSQL IS POWERFUL.
SUBSTRING( str,num,len)	返回字符串str的第num个位置开始长度为len的子字符串	SELECT SUBSTRING( 'MySQL is powerful.',10,8);返回：powerful
INSERT( str,pos,len,newstr)	将字符串str从pos位置开始，len个字符长的子串替换为字符串newstr	SELECT INSERT( 'MySQL is powerful.',10,0,'very ');返回：MySQL is very powerful.

时间日期函数

函数名	作用	举例**（部分结果与当前日期有关）**
CURDATE()	获取当前日期	SELECT CURDATE();返回：2020-08-03
CURTIME()	获取当前时间	SELECT CURTIME();返回：16:54:40
NOW()	获取当前日期和时间	SELECT NOW();返回：2020-08-03 16:55:00
WEEK(date)	返回日期date为一年中的第几周	SELECT WEEK(NOW());返回：31
YEAR(date)	返回日期date的年份	SELECT YEAR(NOW());返回：2020
HOUR(time)	返回时间time的小时值	SELECT HOUR(NOW());返回：16
MINUTE(time)	返回时间time的分钟值	SELECT MINUTE(NOW());返回：56
DATEDIFF(date1,date2)	返回日期参数date1和date2之间相隔的天数	SELECT DATEDIFF(NOW(), '2019-8-8');返回：361
ADDDATE(date,n)	计算日期参数date加上n天后的日期	SELECT ADDDATE(NOW(),5);返回：2020-08-07 16:57:28
UNIX_TIMESTAMP(date)	将日期转换成时间戳	SELECT UNIX_TIMESTAMP( "2020-9-1");返回：1598889600

聚合函数

函数名	作用
COUNT()	返回某字段的行数
MAX()	返回某字段的最大值
MIN()	返回某字段的最小值
SUM()	返回某字段的和
AVG()	返回某字段的平均值

数学函数

函数名	作 用	举 例
CEIL(x)	返回大于或等于数值x的最小整数	SELECT CEIL(-2.1);返回：32
FLOOR(x)	返回小于或等于数值x的最大整数	SELECT FLOOR(-2.1);返回：-3
RAND()	返回0~1间的随机数	SELECT RAND();返回：0.15013303621684485

分页查询

语法：

select<字段名列表> from<表名或视图> [where<查询条件>] [group by <分组的字段名>] [having <条件>] [order by <排序的字段名> [ASC 或 DESC]] [LIMIT [位置偏移量，]行数];

使用LIMIT时，第1条记录的位置是0！

LIMIT子句经常和ORDER BY 字句一起使用，即先对查询结果进行排序，再根据LIMIT子句的参数返回指定的数据

子查询

子查询是一个嵌套在SELECT、INSERT、UPDATE或DELETE语句或其他子查询中的查询

子查询在WHERE语句中的一般用法

语法：

SELECT......FROM 表1

WHERE 字段1 比较运算符（子查询）；

先执行子查询，返回所有来自子查询的结果，再执行外围的父查询，返回查询的最终结果

将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个

SQL语句中指定别名的方法

使用AS关键字，符合ANSI标准

SELECT 字段列表 FROM 表名 AS 表的别名

使用空格，简便的方法

SELECT 字段列表 FROM 表名 表的别名

可以为表、字段、查询结果指定别名

为某个表命名了别名后，在SELECT语句中出现该表的字段需要指定表名时，就必须统一使用该表的别名；否则将产生语法错误