sql 列转行_SQL 语句（一）

DML 表查询

针对 表 中的 数据行 进行的增、删、改、查

增 insert into ...

-- 标准 insert into st(id,name,age,gender,address,intime,cardnum,num)values(1,'张三',18,'m','北京','2020-04-27','666666',10); select * from st;

-- 部分列录入insert into st(name,intime,num)values('李四',NOW(),1111);select * from st;

update 更新数据行的值

update st set name='张六'  where id=4;select * from st; update st set name='张qi' , age=21  where id=4;select * from st;

delete 删除数据行

delete from st  where id=5;select * from st;

伪删除：update 替代 delete ，添加状态列，1带表存在，0代表删除

添加状态列 alter table st add column state tinyint not null default 1 comment '状态列,0是删除,1是存在'; 使用update 替换 delete 原： delete from st where id=4修改后： update st set state=0 where id=4;

面试题 drop　table t1 ,truncate table t1 , delete from t1 区别 ？

# drop　table t1;      作用：1. 删除所有表数据，删除整个表段(rm ibd  ),属于物理性质，会释放磁盘空间。       2. 删除表定义 (rm  frm , 元数据也会被删除)# truncate table t1 ;  作用：保留表结构，清空表段中的数据页。属于物理删除，会释放磁盘空间。 # delete from t1;   作用: 删除数据行。逐行删除。保留表结构，属于逻辑性质删除。只是标记删除，不会立即释放磁盘空间。  所以delete 操作会产生碎片。

DQL 数据查询语句

select 独立使用(MySQL 独有)

-- 查询数据库服务器配置参数select @@port;select @@server_id;select @@basedir;select @@datadir;select @@socket;select @@innodb_flush_log_at_trx_commit;

-- 替代方法：show variables;show variables like '%trx%';

查询内置函数

select DATABASE();select NOW();select USER();select CONCAT("hello world");select user,host from mysql.user;select CONCAT("数据库用户:",user,"@",host,plugin,";") from mysql.user;

其他数据库

select NOW() from dual;

select 通用使用方法

select 多子句执行顺序(单表)select     列   from       表  where      条件  group by   列 having     条件 order by   列 limit      条件

生产中熟悉业务：

1. comment

2. desc ，简单查询表中数据，猜

3. E-R关系图

select 配合 from 子句使用

 查询表中所有数据(小表)use world;select id,name,countrycode ,district,population from city;或者select id,name,countrycode ,district,population from world.city;

select + from + where 子句使用

查询city表中，所有中国的城市信息。select *  from city where countrycode = 'CHN'; 查询人口数小于100人城市信息select * from city where population<100;

where 配合逻辑连接符(and, or , between and)，实现多条件过滤

查询人口数为100w-200w(包括两头)城市信息select * from city where population >= 1000000 and population <= 2000000  查询中国或美国的城市信息。 select * from city where countrycode='CHN'  or countrycode='USA' ; 查询中国或美国，人口数大于500w的城市 select * from world.city where countrycode in ('CHN','USA') and population >= 5000000;

select + from + group by + 聚合函数

--- 聚合函数：count()            统计数量sum()              求和avg()              平均数max()              最大值min()              最小值group_concat()     列转行

-- group  by  分组功能原理1. 按照分组条件进行排序2. 进行分组列的去重复3. 聚合函数将其他列的结果进行聚合。--- SQL_MODE=only_full_group 保证group by 语句 准确性

统计中国城市的个数select COUNT(*) from city where countrycode='CHN'; 统计中国的总人口数。select SUM(population) from city where countrycode='CHN'; 统计中国每个省的城市个数及城市名列表mysql> select district, COUNT(name),name    -> from city     -> where countrycode='CHN'  group by district     -> ;ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

报错说明：5.7 之后的SQL对于group by语句的限制。

1. 没有在group by 后

2. 同时没有在函数中聚合操作违反了SQL_MODE=only_full_group_by

统计中国每个省的城市个数及城市名列表。select district, COUNT(name),GROUP_CONCAT(name)from city where countrycode='CHN'  group by district

having 子句应用

后判断，主要应用在group by之后需要判断。--- 统计每个国家的总人口数，只显示总人口超过1亿人的信息select countrycode,SUM(population)  from city group by countrycode having  SUM(population)>100000000;

order by 语句应用

查询中国所有的城市信息，并按照人口数从大到小排序输出select * from city where countrycode = 'CHN' order by population desc ; 每个国家的总人口数，总人口超过5000w的信息,并按总人口数从大到小排序输出select countrycode,SUM(population)  from city group by countrycode having  SUM(population)>50000000order by SUM(population) desc ;

limit 分页查询

一般配合order by 使用

查询中国所有的城市信息，并按照人口数从大到小排序输出，只显示前十名。select * from city where countrycode = 'CHN' order by population desc limit 10 ; 6-10 名 select * from city where countrycode = 'CHN' order by population desc limit 5,5

送个福利