SQL语句大全

--登录数据库
mysql -uroot -p  
解释说明: -u表示用户名, -p：密码

--显示当前时间
select now();

--登出(退出)数据库
exit/quit/ctr+d

--查看所有数据库
show databases;

--创建数据库
create database py40 charset=utf8;

--使用数据库
use py40;
--查看数据库里面的所有表
show tables;

--查看当前使用的数据库
select database();

--删除数据库-慎重
drop database py40

--查看当前数据库中所有表
show tables;

--创建表
create table students(id int unsigned not null primary key auto_increment,name varchar(10) not null, age tinyint default 0, height decimal(3,2), gender enum('男', '女') default '女');


--修改表-添加birthday字段
alter table students add birthday date;

--修改表-修改字段类型
alter table students modify birthday datetime not null;

--修改表-修改字段名和字段类型
alter table students change birthday birth date;

--修改表-删除birthday字段
alter table students drop birth;


--查看表结构
desc students

--查看创表SQL语句
show create table students;

--查看创库SQL语句
show create database python40;

--删除表
drop table students;

--查询所有列数据
select * from students;
--查询指定列数据
select name,age from students;

--添加数据--全列插入
insert into students values(null, '黄蓉', 18, 1.55, '女')
提示: 主键自增列可以使用0或者null或者default

--添加数据--部分列插入
insert into students(name, age) values('黄老邪',60);  
--添加数据--全列多行插入
insert into students values(null, '黄蓉', 18, 1.55, '女'),(null, '郭靖', 18, 1.55, '女');

--添加数据--部分列多行插入
insert into students(name, age) values('黄老邪',60),('欧阳锋',60);  

--修改数据
update students set height = 1.8, gender='男' where id = 8;

--删除数据
delete from students where id = 9;
--逻辑删除，添加表示字段，删除数据其实是对标识字段的修改
alter table students add is_del int not null default 0;

update students set is_del = 1 where id = 10;

--as关键字
select name as n, age as a from students s;
提示: as可以省略表示也是设置别名

--distinct关键字,去除重复
select distinct height, gender from students;


--查询编号大于3的学生
select * from students where id > 3;

--查询编号不大于4的学生
select * from students where id <= 4;

--查询姓名不是“黄蓉”的学生
select * from students where name != '黄蓉';
select * from students where name <> '黄蓉';

--查询没被删除的学生
select * from students where is_del = 0;

--查询编号大于3的女同学
select * from students where id > 3 and gender = '女';

--查询编号小于4或没被删除的学生
select * from students where id < 4 or is_del = 0;

--查询年龄不在10岁到15岁之间的学生
select * from students where not (age >= 10 and age <= 15);


--查询姓黄的学生
select * from students where name like '黄%';

% 表示任意多个字符

--查询姓黄并且“名”是一个字的学生
select * from students where name like '黄_';

_ 表示任意一个字符

--查询姓黄或叫靖的学生
select * from students where name like '黄%' or name like '%靖';

--查询编号为3至8的学生
select * froms students where id >= 3 and id <= 8;
select * from students where id between 3 and 8;

--查询编号不是3至8的男生
select * from students where not (id between 3 and 8);

--查询编号是3、5、7的学生
select * from students where id in (3, 5, 7);

--查询编号不是3、5、7的学生
select * from students where id not in (3, 5, 7);

--查询没有填写身高的学生
select * from students where height is null;
--查询身高不为空的学生
select * from students where height is not null;

--查询未删除男生信息，按学号降序
select * from students where is_del = 0 and gender='男' order by id desc;

--显示所有的学生信息，先按照年龄从大-->小排序，当年龄相同时 按照身高从高-->矮排序
select * from students order by age desc, height desc;

--查询前3行男生信息，limit后面的参数，从第一个参数开始，往后查询第二个参数个。
select * from students where gender = '男' limit 0, 3;
简写
select * from students where gender = '男' limit 3;

limit是分页查询的关键字，第一个参数表示开始行索引， 第二个参数是查询的条数

--查询学生表，获取第n页数据的SQL语句

提示： 求第n页数据，其实先把开始行索引计算出来即可
select * from student limit (n-1)*10, 10

-- 统计学生表里面的总人数
select count(id) from students;
select count(height) from students;  注意点聚合函数不统计NULL值
提示: 如果要是必须指定某一个字段，那么这个字段应该是主键
如果不想指定字段而且统计的数据不出现问题，可以使用*
select count(*) from students;
-- 获取年龄最大的学生
select max(age) from students;
-- 查询女生的编号最大值
select max(age) from students where gender = '女';
-- 查询未删除的学生最小编号
select min(id) from students where is_del = 0;
-- 查询男生的总身高
select sum(height) from students where gender = '男'
-- 求平均值
select sum(height)/count(*) from students where gender = '男'
-- 不统计null的平均值
select avg(height) from  students where gender='男';
-- 统计null的平均值
select avg(ifnull(height, 0)) from students where gender='男';
-- 根据gender字段来分组
select gender from students group by gender;

-- 根据name和gender字段进行分组
select gender,name from students group by gender, name;

-- 根据gender字段进行分组， 查询gender字段和分组的name字段信息
select gender, group_concat(name) from students group by gender;

-- 提示: 如果给表进行分组，那么查询的字段只能是指定分组的字段， 其它字段要想查询可以使用聚合函数
-- 统计不同性别的人的平均年龄
select gender, avg(age) from students group by gender;
-- 统计不同性别的人的个数
select gender, count(*) from students group by gender;
-- 根据gender字段进行分组，统计分组条数大于6的
select gender, count(*) from students group by gender having count(*) > 5;
-- 根据gender字段进行分组，汇总总人数
select gender, count(*) from students group by gender with rollup;

-- 根据gender字段进行分组，汇总所有人的年龄
select gender, group_concat(age) from students group by gender with rollup;

-- 使用内连接查询学生表与班级表
select s.name, c.name from students s inner join classes c on s.c_id = c.id;

提示: 做连接查询的时候可以给表设置别名，使用更加方便
‘
-- 使用左连接查询学生表与班级表
select s.name, c.name from students s left join classes c on s.c_id = c.id;

左连接查询以左表为主，查询右表数据，右表不存在使用null
left关键字左边的表是左表，left右边的表使用右表

-- 使用右连接查询学生表与班级表
select s.name, c.name from students s right join classes c on s.c_id = c.id;
右连接查询以右表为主，查询左表数据，左表不存在使用null
right关键字左边的表是左表，right右边的表使用右表

-- 使用自连接查询省份和城市信息
select c.id, c.title, c.pid, p.title from areas c inner join areas p on c.pid = p.id where p.title='山西省';

-- 查询大于平均年龄的学生
select * from students where age > (select avg(age) from students);

子查询其实就是一条完整的select查询语句，先执行子查询，然后在执行主查询

-- 查询学生在班的所有班级名字
select * from classes where id in (select c_id from students where c_id is not null);

-- 查找年龄最大,身高最高的学生

 select * from students where age = (select max(age) from students) and height = (select max(height) from students);

简写：
 select * from students where (age, height) = (select max(age), max(height) from students);
 
-- 为学生表的c_id字段添加外键约束（如果）
外键约束:对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性
alter table students add foreign key(c_id) references classes(id);

-- 创建学校表
create table school( id int unsigned not null primary key auto_increment, name varchar(50) not null );


-- 创建老师表添加学校外键
create table teacher( id int unsigned not null primary key auto_increment, name varchar(30), sid int unsigned, foreign key(sid) references school(id) );
-- 删除外键
alter table teacher drop foreign key teacher_ibfk_1

-- 查询类型cate_name为 '超级本' 的商品名称、价格
select name, price from goods where cate_name = '超级本';

-- 显示商品的分类
select distinct cate_name from goods
select cate_name from goods group by cate_name;
select cate_name, group_concat(name) from goods group by cate_name;select distinct cate_name from goods

-- 求所有电脑产品的平均价格,并且保留两位小数
 select round(avg(price),2) from goods;

-- 显示每种商品的平均价格
select cate_name, avg(price) from goods group by cate_name;

select cate_name, round(avg(price),1) from goods group by cate_name;

-- 查询每种类型的商品中 最贵、最便宜、平均价、数量

select cate_name, max(price), min(price), avg(price), count(*) from goods group by cate_name;

-- 查询所有价格大于平均价格的商品，并且按价格降序排序
select * from goods where price > (select avg(price) from goods) order by price desc;

-- 创建商品分类表
 create table goods_cates(
    id int unsigned not null primary key auto_increment,
    name varchar(50)
 );

-- 查询goods表中商品的分类信息
 select cate_name from goods group by cate_name;

-- 将查询结果插入到good_cates表中
insert into goods_cates(name) select cate_name from goods group by cate_name;

-- 添加移动设备分类信息
insert into goods_cates(name) values('移动设备');

-- 查看goods表中的商品分类名称对应的商品分类id
select g.cate_name, gc.id from goods g inner join goods_cates gc on g.cate_name = gc.name;


-- 将goods表中的分类名称更改成商品分类表中对应的分类id，连接更新表中的某个字段

update  goods g inner join goods_cates gc on g.cate_name = gc.name set g.cate_name = gc.id;

update 表名 set 列名=列值

-- 查询品牌信息
select brand_name from goods group by brand_name;

-- 通过create table ...select来创建商品品牌表并且同时插入数据
create table goods_brands( id int unsigned not null primary key auto_increment, name varchar(30)) select brand_name as name from goods group by brand_name;

insert into goods_brands(name) select brand_name from goods group by brand_name;

-- 插入双飞燕品牌
insert into goods_brands(name) values('双飞燕');

-- 查看goods表中的商品品牌对应的商品品牌id
select g.brand_name, gs.id from goods g inner join goods_brands gs on g.brand_name = gs.name

-- 将goods表中的品牌更改成品牌表中对应的品牌id，连接更新表中的某个字段
update goods g inner join goods_brands gs on g.brand_name = gs.name set g.brand_name = gs.id;

-- 通过alter table语句修改表结构,把cate_name改成cate_id，把brand_name改成brand_id
alter table goods change cate_name cate_id int not null, change brand_name brand_id int not null;