SQL语句

SQL语句总结

DDL操作数据库和数据表

查询数据表

• 查询所有的数据表

show tables;

• 查询表结构

desc 表名;

• 查询表字符集

show table status 库名 like '表名';

• 使用数据库

use 数据库名;

创建数据表

• 创建数据表

create table 表名(
	列名 数据类型 约束,
	列名 数据类型 约束,
	...
	列名 数据类型 约束
);
# 示例
create table product(
	id int,
	name varchar,
	price double,
	stock int
);

修改数据表

• 修改表名

alter table 表名 rename to 新表名;

• 修改表的字符集

alter table 表名 character set 字符集名称;

• 单独添加一列

alter table 表名 add 列名 数据类型;

• 修改某列的数据类型

alter table 表名 modify 列名 新数据类型;

• 修改列名和数据类型

alter table 表名 change 列名 新列名 新数据类型;

• 删除某一列

alter table 表名 drop 列名;

删除数据表

# 如果存在则删除
drop table if exists 表名;

DML增删改数据表

添加数据时，列名、值、数据类型要一一对应，除了数字类型，其他数据类型的数据都需要加引号(推荐单引)

• 给指定列添加数据

insert into 表名(列名1,列名2,..) values(值1,值2,...);
# 示例
insert into product(id, name,price,stock) 
values(1,'电脑',3880,11);

# 示例
insert into product(id, name,price,stock) 
values(2,'手机',1999.99,25);

• 给全部列添加数据

insert into 表名 values(值1,值2,...);
# 示例
insert into product
values(1,'手机',1999.99,25);

• 批量添加数据

insert into 表名(列名1,列名2,..) ,(值1,值2...)
values(值1,值2,...),
insert into 表名
 values(值1,值2,...),(值1,值2...),
...;
# 示例
insert into product values(1,'手机',1999.99,25),
(2,'鼠标',669.99,5),
(3,'电脑',5999,55);

修改表中数据时，最好添加筛选条件,不然会修改全部数据

• 修改表中数据

update product set price=3500 where name= '手机';
update product set price=1800 where name='电脑';

删除表中数据时，最好添加筛选条件,不然会删除全部数据

• 删除表中数据

delete from product where name='电脑';

DQL查询数据表

执行顺序

select
	字段列表									6
from
	表名列表									1
[inner] join
	表2     on  连接条件					2
where
	条件列表(分组前过滤条件)		3
group by
	分组字段									4
having
	分组后过滤条件						5
order by
	排序											7
limit
	分页											8

1. 查询表中的单个字段

select 
  last_name 
from
  employees ;

2. 查询表中的多个字段

select
  last_name,
  salary,
  email 
from
  employees ;

3. 查询表中的所有字段

select
  * 
  from
    employees ;

4. 查询常量值

select100;
select'john';

5. 查询表达式

select100%98;

6. 查询函数

selectVERSION();

7. 起别名 查询字段有重名的情况，使用别名可以加以区分开来
   使用AS

select 100% 98 as 结果;
select
  last_name as姓,
  first_name as名 formatemployees ;

使用空格

select
  last_name 姓,
  first_name 名 
from
  employees ;

使用别名出错时候，对别名加以引号

select 
  last_name 姓,
  first_name 名 
from
  employees ;

8. 去重
   使用distinct

select distinct
  department_id 
from
  employees ;

9. 加号的作用
   select 100+90; 若两个操作数都为数值型，则做加法运算
   select'123' + 90; 若其中一方为字符型，则试图将字符型数值转换成数值型

如果成功，则继续做加法运算
如果失败，则将字符型数值转换成0，进行运算
select null + 10 任何一方为null，则结果为null
案例：查询员工名和姓连接成一个字段，并显示为姓名

select 
  last_name + first_name as姓名 
from
  employees ;

10. concat连接

select
  CONCAT('a', 'b', 'c') as 结果 
from
  employees ;
#-------------
select
  CONCAT(last_name, first_name) as结果 
from
  employees ;

11. 条件查询（where）
    语法：

    select
    查询列表 3
    from
    表名 2
    where
    筛选条件; 1
    分类：

• 按条件表达式筛选

    条件运算符：> < =  !=  <>(不等于)
  

• 按逻辑表达式筛选

    逻辑运算符: && || !  或者 and  or  not (推荐)
  

# 案例1： 查询工资> 12000的员工信息
SELECT * FROM employees WHERE salary > 12000;
# 案例2：查询部门编号不等于90号的员工名和部门编号
SELECT last_name, department_id FROM employees WHERE department_id <> 90;
# 案例3：查询工资在10000到20000直接的员工名、工资以及奖金
SELECT last_name, salary, commission_pct FROM employees WHERE salary >= 10000 && salary <= 20000;
# 案例4：查询部门编号不是在90到110直接，或工资高于15000的员工信息
SELECT * FROM employees WHERE NOT(department_id >= 90 && department_id <= 110);

• 模糊查询
  like
  • 一般和通配符搭配使用

#  查询员工名中第三个字符为e，第五个字符为a的员工信息
SELECT 
  last_name 
FROM
  employees 
WHERE last_name LIKE '__e_a%' ;

`% `
- 任意多个字符

`_ `
- 任意单个字符

`between and`
- 使用between and 可以提高语句的简介度
	包含临界值
	临界值不可用交换顺序

# 查询员工名中包含字符a的员工信息
SELECT 
  * 
FROM
  employees 
WHERE last_name LIKE '%a%' ;
# 查询员工名中第二个字符为_的员工名
SELECT 
  last_name 
FROM
  employees 
WHERE last_name LIKE '_\_%' ;

#  查询员工名中第二个字符为_的员工名
SELECT 
  last_name 
FROM
  employees 
WHERE last_name LIKE '_$_%' ESCAPE '$' ;
 # escape 标识为转义字符

`in `

- 判断某字段属于in列表的某一项
	in列表的值类型必须一致或者兼容
	可以提高语句的简洁性
	
	> 等值号判断，不能使用通配符

#  查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的员工名和工种编号
SELECT 
  last_name,
  job_id 
FROM
  employees 
WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES') ;

`is null`

> 不能判断null值
	安全等于 （<=>）
		判断是否等于，等于返回true
		但是可读性比较差
	比较：最好用is null 可读性好，安全性高

# 查询没有奖金的员工名和奖金率
SELECT 
  last_name,
  commission_pct 
FROM
  employees 
WHERE commission_pct IS NULL ;

# 查询有奖金的员工名和奖金率
SELECT 
  last_name,
  commission_pct 
FROM
  employees 
WHERE commission_pct IS NOT NULL ;

12. 排序查询（order by）
    

• asc 升序查询（默认）
• desc 降序

# 选择按月薪降序且月薪不在8000到17000的员工姓名、月薪
SELECT 
  last_name,
  salary 
FROM
  employees 
WHERE NOT (salary BETWEEN 8000 
    AND 17000) 
ORDER BY salary DESC ;

13. 分组查询 （group by）

一般与having 搭配使

# 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT 
  job_id,
  MAX(salary) mx_sal 
FROM
  employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id 
HAVING MAX(salary) > 12000 ;

# 查询领导编号>105的每个领导收下的最低工资>5000的领导编号及最低工资
SELECT 
  manager_id,
  MIN(salary) 
FROM
  employees 
WHERE manager_id > 102 
GROUP BY manager_id 
HAVING MIN(salary) > 5000 ;