sql语句案例

操作数据库

-- 创建数据库
-- character set：字符集
-- collate：字符集校对规则
create database 数据库名称 character set utf8 collate utf8_bin;
-- 查看数据库
show databases;
--查看某个数据库的定义信息
show create database 数据库名称;
-- 修改数据库
alter database 数据库名称 character set 字符集 collate 校对规则;
-- 删除数据库
drop database 数据库名称;
-- 切换数据库
use 数据库名称;
-- 查看当前使用的数据库
select database();

操作表

-- 查看所有表
show tables;

-- 查看表的结构信息
desc 表名;

-- 创建表
create table 表名(
  字段名 字段类型 约束,
  字段名 字段类型 约束,
  ...
)

-- 删除表
drop table 表名;

-- 添加列
alter table 表名 add 列名 类型(长度) comment '注释';

-- 修改列类型，长度和约束
alter table 表名 modify 列名 类型(长度) 约束;

-- 删除列
alter table 表名 drop 列名

-- 修改列名称
alter table 表名 change 旧列名 新列名 类型(长度) 约束;

-- 修改表名
rename table 表名 to 新的表名;

-- 修改表字符集
alter table 表名 character set 字符集;

操作表记录

-- 添加表记录
insert into tab values(xxx,xxx,xxx);

-- 删除表记录
delete from tab where tab.id=1;
-- 删除所有记录
delete from tab;
  -- 属于DML（操作）语言，一条记录一条记录删，可以事务回滚
truncate table tab;
  -- 属于DDL（定义）语言，将表删除，然后创建一个一模一样的表，不能事务回滚

-- 修改表记录
update tab tab.sss="ss",tab.ssss="sss" where tab.id=1;

查询表记录

-- 查询全部记录
select * from tab;

-- 查询单个记录
select xxx from tab;

-- 带条件查询
select xxx from tab where xxx = xxx;

-- 别名查询
select xxx as xx from tab;

-- 三表连接
/* 
    查询所有在课程＂S0002＂或课程＂S0003＂考试中缺考的学生名单，
    并标明这个学生是在缺考哪门课程。（缺考成绩为0）
*/
select Student.Sname as 学生姓名,Course.Cname as 科目名称 
    from SCore inner 
    join Student on SCore.SCsid = Student.Sid inner 
    join Course on Course.Cid = SCore.SCcid 
    where SCore.SCscore=0;

-- 平均值
-- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select Student.Sid as 学生编号,Student.Sname as 学生姓名,AVG(SCore.SCscore) as 平均成绩
    FROM SCore inner 
  join Student on SCore.SCsid = Student.Sid 
  GROUP BY Student.Sid 
  HAVING AVG(SCore.SCscore) >= 60;

-- 模糊查询
-- 查询「李」姓老师的数量
select COUNT(*) as 李姓老师数量 
    FROM Teacher 
  WHERE Tname 
  LIKE '李%';

-- 分页查询
-- 表示从第五行开始返回五行数据
select * from tab limit 4,5;

-- 去重
select distinct xxx from tab;

-- 聚合函数
-- 总行数
select count(*) from tab;
-- 求和
select sum(age) from tab;
-- 最大值
select max(age) from tab;
-- 最小值
select min(age) from tab;
-- 平均值
select avg(age) from tab;
-- 判断空
-- age为空时，赋值为0
select ifnull(age, 0) from tab;

-- 排序
/*
  desc：降序
  asc：升序（默认）
*/
select * from tab order by age desc/asc;
select * from tab where name = "李%" order by age desc;

-- 分组查询
select count(*) from tab group by name having sum(age) > 18 order by age desc;
select count(*) from tab where age > 18 group by name order by age desc;

-- 连接查询
-- 交叉连接
select * from tab1 cross join tab2;
select * from tab1,tab2;
-- 内连接
-- 显示内连接
select * from tab1 inner join tab2 on tab1.id = tab2.id;
-- 隐式内连接
select * from tab1,tab2 where tab1.id = tab2.id;
-- 外连接
-- 左外连接
select * from tab1 left join tab2 on tab1.id = tab2.id;
-- 右外连接
select * from tab1 right join tab2 on tab1.id = tab2.id;

-- 子查询
-- in关键字
select * from tab1 where tab1.id in (select tab2.id from tab2 where createdate < '2001-01-01');
-- exists关键字
-- 如果子查询语句没有结果就不执行exists前的SQL
select * from tab1 where exists (select tab2.id from tab2 where createdate < '2001-01-01');
-- any关键字
-- any(sql) 取SQL查询结果中任意一个值
select * from tab1 where tab1.id > any(select tab2.id from tab2);
-- all关键字
-- all(sql) 取SQL查询结果中所有值
select * from tab1 where tab1.id > all(select tab2.id from tab2);

操作事务

/*
  隔离级别：
    read uncommitted：未提交读
    read committed：提交读
    repeatable read：重复读
    serializable：串行化
*/
-- 设置事务隔离级别：
set session transaction isolation level 隔离级别;
-- 查看事务隔离级别：
select @@tx_isolation;