mysql 基础的sql

下面是常用的sql的写法：

-- 创建表
create table stud(
	sno varchar(15) primary key,
	sname varchar(15) not null,
	age int,
	saddress varchar(15)
);

-- 添加数据
insert into stud values('1001','JACK',20,'纽约');
insert into stud values('1002','Tom',24,'湖南益阳');
insert into stud values('1003','张三',24,'患难长沙');
insert into stud values('1004','李四',15,'患难益阳');
insert into stud values('1005','张三丰',80,'武侠');
insert into stud values('1006','郭嚷',75,'武侠');
insert into stud values('1007','灭绝师太',10,'武侠');

-- 查询所有
select * from stud;


-- 取列别名
select sno as 编号,sname as 姓名,age as 年龄,saddress as 地址
from stud;

-- select复杂查询
-- 使用>= 和<=：
select * from stud where age>=20 and age<=30;
-- 使用between 实现范围查询：
select * from stud where age BETWEEN 20 and 30;
-- 查询年龄等于20或者年龄等于30的数据:
select * from stud where age=20 or age=30;
-- in 关键查询
select * from stud where age in(20,30);
-- not in关键字查询
select * from stud where age not in(20,30);
-- like关键字模糊查询，'%'模糊匹配
select * from stud where sname like '张%';
-- like关键字模糊查询，'_'占位符查询，查询姓为张的，且有两位的
select * from stud where sname like '张_';
-- 查询名字中含有三的
select * from stud where sname like '%三%';
-- 查询名字中含有'三'的且年龄大于30的
select * from stud where sname like '%三%' and age>30;


-- 修改表格的结构
-- 添加列
alter table stud add column sex char(1) default '1';
select * from stud;
-- 删除列
alter table stud drop column sex;
select * from stud;

-- 修改表的数据
insert into stud values('1009','蓝图',null,'江苏南通');
select * from stud;
update stud set age=20 where age is null;
select * from stud;
update stud set sname='蓝图',saddress='无锡'
where sno='1002';


-- 删除数据
delete from stud where sname='蓝图';


-- 创建视图
create view myview
as 
select * from stud where age>20;
select sname,sno,age from myview where age<40;

-- 聚合函数(*号和1 代表只要表中一行有非null的列数据，这一行就是非null)
select count(1) as total from stud;
-- 统计age不为空的列
select count(age) from stud;
-- 显示出stud表中所有age的平均值
select avg(age) as averageage from stud;
-- 将显示出来的数据四舍五入
select floor(avg(age)) as average from stud;
-- SUM(expr)
select sum(age) from stud;
-- MAX(expr)
select max(age) from stud;
-- MIN(expr)
select min(age) from stud;
-- 查询年龄最小的那个人的名字和年龄
select sname,age from stud where age=(
	select min(age) from stud
);
select sname,age from stud where age in(
	select min(age) from stud
);
-- 再次创建一个年龄为10的人
insert into stud values('1010','李白',10,'无锡');
-- 再次查询
select age from stud where age in(
	select min(age) from stud
);
-- DISTINCT
select distinct age from stud where age in(
	select min(age) from stud
);

-- 排序
-- 升序
select * from stud order by age asc;
-- 降序
select * from stud order by age desc;


-- EXISTS存在性判断exists (select * from stud where age=20) ---只要存在age=20的，就返回true、
-- 也就是exists(...) 是判断括号内的表达式是不是null的，如果是null则返回false，否则返回true；
-- 此句因为stud存在age=20的行，所以会输出所有的sname，age。
select sname,age from stud where EXISTS(
	select age from stud where age>20
);

-- 分组
-- 使用平均函数
select saddress,avg(age) as 平均年龄
from stud
group by saddress;
-- 使用sum函数
select saddress,sum(age) as 年龄总和
from stud
group by saddress;
-- 使用having语句
select saddress,sum(age) as 年龄总和 
from stud group by saddress
having sum(age)>30


-- 字符串处理函数
-- Ltrim(str)	去掉左边的空格
-- Rtrim(str) 去掉右边的空格
-- trim(str) 	去掉两边的空格
-- Left(str,n)从左边取出n个字符
-- Right(Str,n)从右边取出n个字符
-- Substring(str,begin,end) 返回子字符串
-- Reverse(str) 返回颠倒字符串
-- Lower(str) 转换为小写
-- Upper(str) 转换为大写
-- CONCAT(str1,str2,...) 字符串连接函数
-- INSTR(str,substr) 返回substr在s的位置，没有返回0


-- left
select * from stud where left(saddress,2)='湖南'
-- 串联
select concat(sno,'-->',sname,'-->',age,'-->',saddress) as 拼接的结果 from stud

-- INSTR(str,substr):从1开始计数
select sname,INSTR(sname,'三') from stud