mysql语法总结及例子

1. DDL相关

-- 查询所有数据库
show databases;
-- 删除数据库
drop database ladeng;

-- use `数据库名称`;  表示使用此数据库
use ladeng;

-- 查看表结构
show create table student;

d. 往表中添加新字段

ALTER TABLE `report_user_manage` ADD is_delete tinyint(2) NOT NULL COMMENT '是否删除 0-未删除 1-已删除';
ALTER TABLE `report_user_manage` ADD creator varchar(50) NOT NULL COMMENT '创建人';
ALTER TABLE `report_user_manage` ADD gmt_create datetime NOT NULL COMMENT '创建时间';
-- 当数据新插入时更新时间默认为null, 当数据修改时更新时间会自动变化
ALTER TABLE `report_user_manage` ADD gmt_modified datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间';

-- 如果源字段值是其它类型如:vachar且有值, 修改为新字段bigint类型,则会报报,需要先清空这个字段值才能修改字段名称; 如果源字段值都为null或者类型也为bigint则直接修改不会报错
ALTER TABLE 表名 CHANGE  `源字段名称` `目标字段名称`  bigint(20)  DEFAULT NULL COMMENT '逾期已还用户数';

2. 查询表总共有多少条数据, 一般有如下3中写法, 推荐优先使用 count(1) 或 count(id)

-- count(1):性能高
select count(1) from student;
-- count(带索引的字段):性能高, count(未带索引的字段):性能一般, count(字段)时需要注意,如果该字段值存在有null则统计有误差.

select count(字段名称) from student; 如: select count(id) from student; -- count(*): * 会扫描所有列,故性能比前面几种低 select count(*) from student;

3. 单条插入

-- 如果选中了数据库则数据库名称可以省略; 
-- 如果数据库名称或表名称是中文名或者字段名称是关键字，则尽量加上 `` 包裹起来
-- 如果id是自增,则id默认一般不能插入，除非自己修改数据库配置允许插入id;
-- 所有字段值都可以用单引号包围, 如果字段类型不是varchar类型,也能使用''包裹字段值, 底层做了类型强制转换成varchar
INSERT INTO 数据库名称.表名称 (字段名称1, 字段名称2) VALUES('字段1值', '字段2值');
INSERT INTO `ladeng`.`student` (`name`, `age`, `score`, `courseId`) VALUES ('小明', '19', '100', '3');

4. 批量插入(插入条数100条至10000条以内性能高, 具体插入条数看插入的字段个数和字段值的字符数, 推荐1000条)

--  方式一(多条插入语句中间用分号; 分隔): 如: 下面这2条同时执行
INSERT INTO `ladeng`.`student` (`name`, `age`, `score`, `courseId`) VALUES ('小红', '18', '15', '3');
INSERT INTO `ladeng`.`student` (`name`, `age`, `score`, `courseId`) VALUES ('小丽', '22', '25', '3');

-- 方式二: values后面跟多条数据，中间用顿号、分隔
INSERT INTO `ladeng`.`student` (`name`, `age`, `score`, `courseId`) VALUES ('小红', '18', '15', '3'),('小丽', '22', '25', '3');

5. 单条更新语句

UPDATE `ladeng`.`student` SET `score`='79', age = 19  WHERE `id` = '1';

6. 批量更新语句: 参照批量插入的第一种方式

7. 删除语句

delete from student where id in (14, 15);

8. between ... and ... 在什么之间, 如: 查询年龄>= 19并且年龄<=22数据

select * from student where age between 19 and 22  等价于
select * from student where age >=19 and age <= 22

9. 聚焦函数(count, sum, max, min, group by), 将多行数据聚焦成一个值

-- 查询总条数, 如果不存在id>2的数据则count(1)会返回0,而不是返回null
select count(1) from student where id > 2
-- 查询所有学生所有科目总分数, 如果表中没有数据则sum(score)会返回null，而不是返回0
select sum(score) from student 
-- 查询所有学生所有科目总分数, 如果没有数据就默认返回0; ifNull是用来判断是否为空,为空则赋值一个默认值
select ifNull(sum(score), 0) from student
-- 查询每个学生所有科目分数, 按照学生名字分组; group by 作用是分组,如果需要对多个字段分组，则 group by 字段1,字段2,...
select `name`, sum(score) from student group by `name`;

10.

-- case when then end的第一种用法; 类似于java中的if..   else if..  else.. 语句
select 
    score,
    case 
        when score < 60 then '不及格'
        when score >= 60 and score < 80 then '中等'
        when score >= 80 then '优秀'
    end as score_zh
from student;

b. sql中的3种判断语句

下面那些sql执行的结果都等于这张图片

第一种用法(推荐使用这种,没有局限性,): case when 条件表达式1 then 值1 when 条件表达式2 then 值2 ... else 默认值 end as `字段别名`

-- 这3条sql意思都一样,结果如下图 (else可以省略); 等价于java中的if语句
select  
  is_delete,
  case 
    when is_delete = 0 then '未删除'
    else '已删除'
  end as is_delete_zh
from student 

select  
  is_delete,
  case 
    when is_delete = 0 then '未删除'
    when is_delete = 1 then '已删除'
  end as is_delete_zh
from student 

select  
  is_delete,
  case 
    when is_delete = 0 then '未删除'
    when is_delete = 1 then '已删除'
  else '' end as is_delete_zh
from student

第二种用法;

-- 这2条sql意思一样，结果如下图(else可以省略); 等价于java中的 switch语句
select 
  is_delete,
  case is_delete
    when 0 then '未删除'
    when 1 then '已删除'
  end as is_delete_zh
from student 

-- 等价于java中的 switch语句.
select 
  is_delete,
  case is_delete
       when 0 then '未删除'
       else '已删除'
  end as is_delete_zh
from student

第三种用法:

-- 等价于java中的三目运算符
select is_delete, if(is_delete = 0, '未删除', '已删除') as is_delete_zh from student;

JAVA学习网

分类

mysql语法总结及例子