【数据库学习】sql基本语法

定义：数据定义语言 -> 操作数据库，和数据表结构

-- 创建数据库
create database if not exists java2206;
-- 查看数据库
show databases;
-- 使用数据库
use java2206;
-- 删除数据库, 就会把数据库中的表一起删除(无法撤销)
drop database if exists test;
-- (在数据库中)创建数据表 -> 必须在数据库内部使用
create table stu (
    -- 表头: 字段名 字段类型
    id int);

-- 建表 (参考java中类的声明)
create table if not exists `student` (
    -- comment: 字段含义
    `id` int(4) comment '学生编号',
    `gender` char(1),
    `username` varchar(20),
    `hiredate` date);
-- 查看表的结构
desc student;
-- 查看所有的表
show tables;
-- 删除表: drop是不可以撤销的
drop table stu;
-- 修改表的结构  alter-- 添加新的字段
alter table student add `email` varchar(20);
-- 修改字段(类型)
alter table student modify `id` varchar(20);
-- 删除字段(慎重使用)
alter table student drop email;
-- 重命名表
rename table stu to student;
-- 修改字段名 ``区分关键字
alter table student change hiredate `date` date;
-- 查看当前数据库字符集 latin
show variables like '%char%';

定义：数据操纵语言, 表数据的操作

-- 一次插入所有的字段, values(值的顺序, 和数据类型, 个数) 必须和表中的定义顺序是一致的
insert into student values(1001, '男', 'tom', '2022-07-20');
insert into student values(1001, '男', 'tom', null);
insert into student values(1001, '男', '', '2022-07-20');
insert into student values(1001, '男', null, '2022-07-20');

-- 不要求顺序, 不要求个数, 没有插入的列就是 null
insert into student(id, username) values(1002, 'jack');

-- 设置安全模式
SET SQL_SAFE_UPDATES = 0;
-- 删除所有
delete from student;
-- 删除id是1001的(所有)用户
delete from student where id = 1001 ;
-- 删除所有 (不可撤销 drop + create)
truncate table student;

-- * 表示所有字段, * 查询时,会先解析成字段, 效率低
select * from student;
-- 手动指定所有字段的查询
select deptno, dname, loc from dept;

条件查询 where

-- = : 字符串, 数字, 时间
select * from emp where empno = 7369;
-- > >= < <= : 数字, 字符串, 时间
-- 查询工资小于3000的人
select * from emp where sal < 3000;
select * from emp where ename > 'JONES';
select * from emp where hiredate > '1981-12-17'

组合条件

-- 查询empno 是 7379, 7876, 7788
-- or: 或者 , and: 并且
select * from emp where empno = 7369 or empno = 7876 or empno=7788;
-- in (集合)  =xx1 or =xx2 or =xx3: 子查询常用
select * from emp where empno in (7369, 7876, 7788);

范围查询

-- 查询工资 在 950 ~ 1300 
select * from emp where sal >= 950 and sal <= 1300;
-- between num1 and num2, 等同于 >= num1 and <= num2
select * from emp where sal between 950 and 1300;

去除重复

-- 员工表中, 都在哪些部门里, 只需要deptno (去除重复)
select distinct deptno from emp;

运算

-- 查询每个员工的姓名, 和他们的年薪 sal * 12
-- 查询结果可以直接运算, as 添加别名,可以省略
select ename name, sal*12 as '年薪' from emp as e;
select ename, sal*12 '年薪' from emp e;
select emp.ename, sal*12 '年薪' from emp;
-- 表添加别名后, 只能用别名
select e.ename, sal*12 '年薪' from emp e;

like 模糊查询

-- 查询姓名中首字母是 A 的
-- like模糊查询, 必须要借助占位符 ->  _ 表示一个字符;  % 表示任意字符
select ename from emp where ename LIKE 'A%';
-- 查询名字的第二个字母是A
select ename from emp where ename LIKE '_A%';

is

-- 查询有奖金的员工姓名+奖金金额
select ename, comm from emp where comm > 0;
-- 查询没有上司的员工姓名 null: 不能直接比较, 无限大或者无限小, 不能直接运算
select ename from emp where mgr is null;
-- 查询员工的年薪 = 月薪 * 12 + 奖金-- null值的函数 : ifNull(字段, 值)   ifNull(comm, 0, 10)
select ename, sal*12+ifNull(comm, 0) '年薪' from emp e;
-- 查询1981年入职的员工姓名和入职时间
select ename, hiredate from emp where year(hiredate)=1981;

1. concat

   -- 11. 连接职员表中的职员名字、职位、薪水，列之间用逗号连接，列头显示成 OUT_PUT
   select concat(ename,',',job,',',sal) as OUT_PUT from emp;
   

2. round

   -- 12. 查询职员表 emp 中员工号、姓名、工资，以及工资提高百分之 20%后的结果，元为单位进行四舍五入
   select empno,ename,sal,concat(round(sal*1.2),'元') as 'out' from emp;
   

3. 不等于 != <>

   -- 14. 列出部门表中除了 ACCOUNTING 部门还有哪些部门。
   select dname from dept where dname <> 'ACCOUNTING';
   

4. length

   -- 16. 将员工的姓名按首字母排序，并列出姓名的长度（length）
   select ename, length(ename) from emp order by ename;
   

5. timestampdiff

   -- 17. 查询各员工的姓名 ename，并显示出各员工在公司工作的月份数 (即：和当前日期比较，该员工已经工作了几个月,  用整数表示)。
   select ename, timestampdiff(month, hiredate, now()) from emp;
   

6. date_format，now

   -- 19. 按照"2009年4月11日 20-35-10"格式显示系统时间。
   select date_format(now(), '%Y年%m月%d日 %H-%i-%s') ;
   

7. lpad

   -- 22. 查询员工的姓名和工资，按下面的形式显示：（提示：使用 lpad 函数）
   select ename, lpad(sal, 15, '$')  salary from emp;
   

8. format

   -- 23. 查询薪水大于 1600 元的员工的姓名和薪水，薪水值显示为"$5000.00"这种形式，并对查询结果按薪水的降序方式进行排列；
   select ename, concat('$',format(sal, 2)) from emp where sal > 1600
   order by sal desc;