常使用SQL语句

梦里梦外; 2022-05-11 02:52 245阅读 0赞

## 操作数据库 ##

–创建数据库  
create database dbname;

–删除数据库  
drop database dbname;

## 操作表 ##

–新建表：  
create table table1( id varchar(300) primary key, name varchar(200) not null);

–插入数据  
insert into table1 (id,name) values (‘aa’,‘bb’);

–更新数据  
update table1 set id = ‘bb’ where id=‘cc’;

–删除数据  
delete from table1 where id =‘cc’;

–删除表  
drop table table1;

–修改表名：  
alter table table1 rename to table2;

–表数据复制：  
insert into table1 (select \* from table2);

–复制表结构：  
create table table1 select \* from table2 where 1>1;

–复制表结构和数据：  
create table table1 select \* from table2;

–复制指定字段：  
create table table1 as select id, name from table2 where 1>1;

## 查询语句汇总 ##

–查询所有字段  
select \* from table1

–查询指定字段  
select name from table1

–where查询指定数据  
select \* from table1 where name = ‘李四’;

–带in关键字的查询  
select \* from table1 where age in (17,18,19);

–带between and查询  
select \* from table1 where age between 20 and 30;

–带like的模糊查询（’%‘代表任意个,’*'代表一个）  
select \* from table1 where name like ‘张%’;  
select \* from table1 where name like '李斯*’;

–空值查询  
select \* from table1 where tel is not null;

–带and的多条件查询  
select \* from table1 where name = ‘王欢’ and sex =‘男’;

–带or的多条件查找  
select \* from table1 where name = ‘王欢’ or age = 15;

–distinct去重复查询  
select name from table1;//不去重复，相同的值也会显示出来  
select distinct name from table1 ;//去除重复，相同的值只显示一次

–对查询结果排序（order by）  
select \* from table1 order by age;

–group by 分组查询  
与group\_concat()函数一起使用  
select bookTypeId,group\_concat(author) from t\_book group by bookTypeId;  
与聚合函数一起使用  
select bookTypeId,count(bookName) from t\_book group by bookTypeId;  
与having函数一起使用（限制输出的结果）  
select bookTypeId,count(author) from t\_book group by bookTypeId having count(author)❤️;  
与with rollup一起使用（最后一行会输出统计后的记录数据）  
select bookTypeId,count(bookName) from t\_book group by bookTypeId with rollup;  
select bookTypeId,group\_concat(author) from t\_book group by bookTypeId with rollup;

–limit 分页查询  
select \* from t\_book limit 0,3；//显示第一页三条记录