常用SQL语句

一、DDL数据定义语言

-- 创建库
create database db1;  
-- 创建库是否存在，不存在则创建
create database if not exists db1;
-- 查看所有数据库
show databases;
-- 查看某个数据库的定义信息 
show create database db1; 
-- 修改数据库字符信息
alter database db1 character set utf8; 
-- 删除数据库
drop database db1; 

--创建表
create table student(
    id int,
    name varchar(32),
    age int ,
    score double(4,1),
    birthday date,
    insert_time timestamp
);

--备份表 常用
DROP TABLE IF EXISTs s_dict_backup;
CREATE TABLE s_dict_backup LIKE s_dict;
INSERT INTO s_dict_backup SELECT * FROM s_dict;

-- 查看表结构
desc 表名;
-- 查看创建表的SQL语句
show create table 表名;
-- 修改表名
alter table 表名 rename to 新的表名;
-- 添加一列
alter table 表名 add 列名 数据类型;
-- 删除列
alter table 表名 drop 列名;
-- 删除表
drop table 表名;
drop table  if exists 表名;

-- 建表模板

DROP TABLE IF EXISTS tbl_student_class;
CREATE TABLE tbl_student_class (
id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
sno varchar(12) NOT NULL COMMENT '学号',
cno varchar(5) NOT NULL COMMENT '班级号',
cname varchar(50) NOT NULL COMMENT '班级名',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生班级表';

二、增删改

-- 增
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
insert into 表名 values(值1,值2,...值n);
insert into 表名(列名1,列名2) values(值1,值2);

-- 删
-- 删除表中数据
delete from 表名 where 列名 = 值;
-- 删除表中所有数据
delete from 表名;
-- 删除表中所有数据（高效 先删除表，然后再创建一张一样的表。）
truncate table 表名;

-- 改
update 表名 set 列名 = 值 where 列名=值;

三、DQL数据查询语言

主要关键字

-- BETWEEN AND
-- 查询年龄大于等于20 小于等于30				
SELECT * FROM student WHERE age >= 20 &&  age <=30;
SELECT * FROM student WHERE age >= 20 AND  age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;

-- in
-- 查询年龄22岁，18岁，25岁的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM student WHERE age IN (22,18,25);

-- IS NULL
SELECT * FROM student WHERE Eglish  IS NOT NULL;

-- 模糊查询
 _:单个任意字符
 %：0或多个任意字符
-- 查询姓周的有哪些? like
SELECT * FROM student WHERE NAME LIKE '周%'
-- 查询姓名第二个字是杰的人		
SELECT * FROM student WHERE NAME LIKE "_杰%"
-- 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE '___';	
-- 查询姓名中包含林的人
SELECT * FROM student WHERE NAME LIKE '%林%';
 
-- DISTINCT 用于返回唯一不同的值。
SELECT DISTINCT NAME FROM  student ;

-- 分组GROUP BY 具体可参考https://blog.csdn.net/qq_37634156/article/details/120055284
SELECT cno, COUNT(*) nums FROM tbl_student_class GROUP BY cno HAVING COUNT(*) = 3;
having能否单独使用：经过测试是跟数据库有关系，测试了常用的三个数据库，结果是Oracle和postgresql不能，mysql可以

-- 分页查询
SELECT * FROM student LIMIT 6,3; -- 第3页 pageSize为3

-- 内连接
-- 查询员工表的名称，性别。部门表的名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;  
-- 外连接

-- 左外连接 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
-- 右外连接
SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;

-- 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

-- CASE WHEN用法
SELECT
    STUDENT_NAME,
    (CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        WHEN score >= 80 THEN '优秀'
        ELSE '异常' END) AS REMARK
FROM
    TABLE

四、事务管理

//开启事务
START TRANSACTION;
//
xxx
//失败回滚
ROLLBACK;
//成功提交
COMMIT;

待更新…