SQL语句
大纲
-
表操作
-
查看表
SHOW TABLES;
show tables; -
查看表接口
DESC [tabelName];
desc [tableName]; -
创建表
CREATE TABLE [tableName] ( [fieldName] [dataType] [dataConstraints] ); -
修改表-添加字段
ALERT TABLE [tableName] ADD [fieldName] [dataType];
alter table [tableName] add [fieldName] [dataType]; -
修改表-修改字段类型与约束
ALERT TABLE [tableName] MODIFY [fieldName] [dataType] [dataConstraints];
alert table [tableName] modify [fieldName] [dataType] [dataConstraints]; -
修改表-修改字段名
ALERT TABLE [tableName] CHANGE [oldFieldName] [newFieldName] [dataType] [dataConstraints];
alert table [tableName] change [oldFieldName] [newFieldName] [dataType] [dataConstraints]; -
修改表-删除字段
ALERT TABLE [tableName] DROP [fieldName];
alert table [tableName] drop [fieldName]; -
删除表
DROP TABLE [tableName];
drop table [tableName];
-
-
数据操作
-
查询数据
SELECT [fieldName or *] FROM [tableName];
select [fieldName or *] from [tableName]; -
插入数据
INSERT INTO [tableName] [([fieldName])] values([datas]);
insert into [tableName] [([fieldName])] values([datas]); -
修改数据
UPDATE [tableName] SET [fieldName]=[date] … WHERE [dataConditions]; -
删除数据
DELETE FROM [tableName] WHERE [dataConditions];
-
-
聚合函数
-
总数
计算总行数,写字段名和通配符结果相同
SELECT count([fieldName or *]) FROM [tableName];
select count([fieldName or *]) from [tableName]; -
最大值
获取字段的最大值
SELECT max([fieldName]) FROM [tableName];
select max([fieldName]) from [tableName]; -
最小值
获取字段的最小值
SELECT min([fieldName]) FROM [tableName];
select min([fieldName]) from [tableName]; -
求和
计算字段内数据的合
SELECT sum([fieldName]) FROM [tableName];
select sum([fieldName]) from [tableName]; -
平均值
计算字段的平均值
SELECT avg([fieldName]) FROM [tableName];
select avg([fieldName]) from [tableName];
-
-
分组
-
group by
SELECT [fieldName or *] FROM [tableName] GROUP BY [fieldName];
select [fieldName or *] from [tableName] group by [fieldName];
将以 group by 后的字段进行分组 -
group by + group_concat()
使用group_concat()来查询每一个分组中某字段的集合
SELECT [fieldName or *], group_concat([fieldName]) FROM [tableName] GROUP BY [fieldName];
select [fieldName or *], group_concat([fieldName]) from [tableName] group by [fieldName];
-