mysql 语句总结 1

1、指令集

1.1、数据定义 (DDL)

use、show、create、drop

定义库或表

# 显示
show databases;
show tables;
show columns from 表名;     # 显示表的字段详情
show index from 表名;       # 显示索引、主键

# 使用库
use 库名;

# 新建
create database 库名;
create table 表名;

# 删库、表
drop database 库名;
drop table 表名;

1.2、数据操作 (DML)

insert、delete、update、select

操作一条或多条数据

# 增
insert into 表名(字段,字段) values (值,值);

# 删
delete from 表名 where 条件;

# 改
update 表名 set 字段=值,字段=值;

# 查，where 后面可以加 and、or、like、binary等
select 字段 from 表名 where 条件;   

1.3、其他关键字

union 连接 select

union 连接两个select（仅保留去重复的值）

select 字段 from 表名
union all
select 字段 from 表名

union all 保留所有值

order by 排序

# 升序排序
select 字段 from 表名 order by 字段;   
# 降序排序
select 字段 from 表名 order by 字段 desc;  

group by 分组（一般结合一些函数使用）
group by … WITH ROLLUP; 统计总的个数

# 根据name分组，计算name个数
SELECT name, COUNT(*) FROM 表名 GROUP BY name;
# coalesce()可以代替name中的null值
SELECT coalesce(name, '总数'), SUM(singin) FROM 表名 GROUP BY name WITH ROLLUP;

case 选择

case … when … end 选择语句

case可以当一个字段用

select 字段,case sex 
			when "1" then "男"
			when "2" then "女"
			else "其他" end 
from 表名



select 字段,case
			when sex="1" then "男"
			when sex="2" then "女"
			else "其他" end 
from 表名

binary 区分大小写

select 字段 from 表名 where binary 条件;         # 定义后面的条件是区分大小写的

like 模糊查询

select 字段 from 表名 where 字段 like '%bc%';    # 还可以添加and、or

inner join、left join 两表连接

# inner join  内连接，返回两表都有的部分，INNER JOIN 与 JOIN 是相同的。
SELECT t1.id FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id;

# left join 左连接，左边全部有，右边只有左边出现过的
# 例如教师表(左边)、课程表(右边)，用左连接结果就是：
# 教师A，课1
# 教师A，课2
# 教师B，课3
# 教师C，null
# 教师A、B、C都是教师表里有的，课程表里只有教师A、B

1.4、常用函数

distinct( )                # 查不同种类的
sum( )                     # 相加
count( )                   # 计数
coalesce(name,"其他")      # 替代name的null值
substr("hello",1,3)        # 返回hel
instr("helloworld","l")    # 返回l的位置
IF(条件,v1,v2)             # 如果条件成立，返回结果 v1；否则，返回v2。
ISNULL(条件)               # 判断条件是否为 NULL

SELECT IF(1 > 0,'正确','错误') ;
SELECT ISNULL(NULL);
select distinct(number) from user                    # 查字段name有哪些值
SELECT name, COUNT(*) FROM table1 GROUP BY name;     # 计算同名的个数
SELECT name, sum(age) FROM table1 GROUP BY name;     # 计算同名的年龄和

2、实例

现在有一个科目表subject、成绩表achievement
subject 字段：id、subjectname（课程名字）
achievement 字段：id 、subjectid（课程id）、studentid（学生id）、scroe（分数）

现在要查每个学生每个都有多少分

        SELECT s.subjectname,a.userid,a.score
        FROM subject s LEFT JOIN achievement a ON a.subjectid=s.id
        WHERE a.userid = #{id}
        GROUP BY a.subjectid