1、数据库
show databases; #在Mysql中查数据库:
use 数据库名称; #使用一个数据库:
create database 数据库名称; #新建一个数据库:
2、表
show tables; #在数据库中查表
create tables 表名(列名1 数据格式,
列名2 数据格式
列名3 数据格式); #在数据库中建表
drop table 表名 #删除表
desc 表名 #查看表中所有列名的设计信息
describe 表名 #查看表中所有列名的设计信息
ALTER TABLE 表名 add 列名 数值格式 #在表中添加列
ALTER TABLE 表名 change 列名 新列名 数值格式; #重命名列
3、记录
select * from 表名 #在表中查找所有记录
insert into 表名(列名1,列名2,列名3)values(值1,值2,'字符'3); #在表中插入新的一条记录
update 表名 set 列名1=值1,列2=值2,列3=值3 where id='10002'; #在表中修改一条特定记录.这里没有TABLE和FROM!!!
delete from 表名 where id='10001'; #在表中删除一条特定记录
select 列名2,列名5 from 表名; #在表中查询某些列
select 列名2,列名5 from 表名 where age>21; #在表中查询某些列的特定记录
4、运算符
select * from customer where salary >=2000 and salary<=3000;
select * from customer where salary between 2000 and 3000;
select * from customer where salary=1000 or salary=2000 or salary=3100;
select * from customer where salary in (1000,2000,3100);
select name from customer where name like '%o%'; #查询name中有o的人的名字
select name from customer where name like '__o%; #查询name中第3个字母是o的人的名字
select * from customer where email is null; #查询email为空的所有人的信息
select * from customer where email is not full; #查询email不为空的所有人的信息
select * from customer order by salary; #查询所有客户信息,且按salary排序
select * from customer order by salary desc; #查询所有客户信息,且按salary降序排序
select version(); #查询Mysql的版本
5、注释
单行注释: #注释
-- 注释
多行数据: /*注释*/
6、字符串
字符串可以是SELECT列表中的一个字符,数字,日期。
日期和字符只能在单引号中出现。
7、SELECT 的辅助功能
SELECT 数值; #直接显示出某个值
SELECT '字符'; #直接显示出某个字符
SELECT 表达式; #计算出某个表达式
SELECT 函数(); #查询函数返回值
SELECT VERSION();
8、起别名
好处 ⑴提高可读性便于理解
⑵如果要查询的字段有重名情况,使用别名区分开来
方式一:使用AS
SELECT 100%98 AS 结果; #取余数
SELECT last_name AS 姓,first_name AS 名 FROM employees;
方式二:使用空格
SELECT last_name 姓,first_name 名 FROM employees;
注意:若别名中有特殊符号,例如#或者空格,则需要加双引号或者单引号
9、去重
SELECT department_id FROM employees;>>SELECT DESTINCT department_id FROM EMPOYEES;
10、+的作用
仅仅一个功能:运算符
select 100+90; #两个操作数都为数值型,则做加法运算
select '123'+90; #其中一方为字符型,试图将字符型数值转换成数值型
#如果转换成功,则继续做加法运算
#如果转换失败,则将字符数值转换成0
select null+10; #只要其中一方为null,则结果为null.
11、拼接函数
SELECT CONCAT(字符串1,字符串2) FROM; #字符串不能为null,否则输出为null。
12、IFNULL函数
判断某列单元格是否为Null,若不为Null,则返回该单元格的数值,若为null,则返回设置的数值)
IFNULL(列名,为NULL时的返回值)
13、条件查询
语法:select 查询列表
from 表名
where 筛选条件;
筛选条件分类:
一、按条件表达式筛选
简单条件运算符: >,<,=,!=,<>,>=,<=
注意:不能判断null值,能判断数值和'字符'.
二、按逻辑表达式筛选
逻辑运算符:用于连接条件表达式 and,or,not
例子:查询部门编号不是在90到110之间的员工信息
SELECT *
FROM employees
WHERE NOT(department_id>=90 and department_id<=100);
三、模糊查询
like,(not)between and,in,is (not) null
1)like
like一般和通配符使用,不能判断Null值
通配符:
%:代表任意个字符,包含0个字符
_: 任意单个字符
例题:查询员工名中第二个字符为_的员工名 (为转义字符)
SELECT last_name
FROM employees
WHERE lase_name like '__%';
或者重新定义一个新的转义字符,例如$: ESCAPE '$'
SELECT last_name
FROM employees
WHERE lase_name like '_$_%' ESCAPE '$';
例题:
ID name dep
1 a 财务
2 b 财务
3 c 信息
4 d null
5 e null
如上表,在SQL数据库中,字段有null的值做like的模糊查询,总是得不到想要的结果
select * from tb where name like '%' and dep like '%'
这样写,就只能取出没有null的记录
ID name dep
1 a 财务
2 b 财务
3 c 信息
select * from tb
这样写,或者
select * from tb where name like '%' or dep like '%'
这样写,或者
select * from tb where name like '%' and (dep like '%' or dep is null)
这样写,在没有输入查询条件时可以得到全部的值
ID name dep
1 a 财务
2 b 财务
3 c 信息
4 d null
5 e null
2)(not) between and 或者 not(列名 between and)
可以简化条件运算符的使用,提高效率,等价于>=和<=。
3)in('','','')
判断某字段的值是否属于in列表中的某一项
使用in比or方便,提高简洁度
in列表的值类型必须一致或兼容
不能和通配符使用,因为in是多个“=”的缩写,而通配符和like一起使用。
4)is null
例题:查询没有(有)奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS (not) NULL;
例题:查询员工号为176的员工姓名和部门号和年薪,(commision_pct可能为null值)
SELECT
last_name,
department_id,
salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
employees;
5)is
is不等价于“=”,只能和(NOT) NULL搭配
6)安全等于<=>
和IS用法一样,可以判断(not) null值,也可以判断是否“=”,缺点是可读性差,容易和不等于“<>”弄混
14、排序查询
语法:
select 查询列表
from 表
where 筛选条件
order by 排序列表 (asc/desc)
排序列表
第一种:order by 列名
第二种:order by 别名
第三种:order by 表达式(简单运算或者函数)
例题:按姓名的长度显示员工的姓名和工资
select length(last_name) as 字节长度,last_name,salary
from employees
order by length(last_name) DESC;
order by还支持按多个字段排序,先按第一个字段排序,当第一个字段的两个或多个值相等时再按第二个字段排序
例题:查询员工信息,要求先按工资排序,再按员工编号排序
select employees
order by salary ASC,employee_id DESC;
15、常见函数
特点:隐藏了实现细节;提高代码的重用性
类型:单行函数(一对一),如concat、length、ifnull等
分组函数(多对一),作统计使用
1)单行函数:
字符函数
数学函数
日期函数
其他函数
流程控制函数
一、字符函数
#length
select length('john');
select length('张三丰hahaha');
#concat
select concat(last_name,'_',first_name) from employees;
#upper、lower 大小写变换
select upper('john');
select lower('joHn');
#substr、substring 截取函数
select substr('李莫愁爱上了陆展元了’,7,3); 陆展元
#instr 与excel中find函数对应,返回子串第一次出现的索引,如果找不到则返回0
select instr('杨不悔爱上了殷六侠','殷六侠'') from employees; 7
#trim 去掉前后空格输出 或者 去掉前后的特定字符
select trim(' 张翠山 '); 张翠山
select trim('a' from 'aaaaaaaaa张aaa翠aaaaaaaaa山'); 张aaa翠
#lpad 用指定的字符实现左填充指定长度
select lpad('殷素素',10,'*'); *******殷素素
select lpad('殷素素',2,'*'); 殷素
#rpad 用指定算符实现右填充指定长度
select lpad('殷素素‘,12,’ab'); 殷素素ababababa
#repalce 替换
select replace('张无忌爱上了周芷若','周芷若','赵敏'); 张无忌爱上了赵敏
二、数学函数
#round
select round(1.65); 2
select round(1.45); 1
select round(-1.45); -1
select round(-1.65); -2
select round(1.567,2); 1.57
#ceil 向上取整,返回>=该参数的最小整数
select ceil(1.52); 2
select ceil(1.002); 2
select ceil(1.00); 1
select ceil(-1.02); -1
#floor 像下取整,返回<=该参数的最大整数
select floor(9.99); 9
select floor(-9.99) -10
#truncate 截断,小数点第几位截断
select truncate(1.6666,1); 1.6
#mod 取余 被除数为正,结果为正,被除数为负,结果为负
mod(a,b): a-a/b*b
select mod(10,3); 1
select 10%3; 1
select mod(-10,-3) -1
select mod(-10,3) -1
select mod(10,-3) 1
三、日期函数
#now 返回当前系统日期+时间
select now();
#curdate 返回当前系统日期,不包含时间
select curdate();
#curtime 返回当前时间,不包含日期
select curtime();
#可以获取指定的部分,年、月、日、小时、分钟、秒
select year(now());
select year('1998-1-1');
select year(hiredate) from employees;
select month(now()); #显示数字
select monthname(now()); #显示英文
#str_to_date:将日期格式的字符转换成指定格式的日期
str_to_date:(9-13-1999','%m-%d-%Y'); 1999-09-13
select date(STR_TO_DATE('2014-12-06 02','%Y-%m-%d %H') ); 2014-12-06 02
#date_format:将日期转换成字符
date_format('2018/6/6','%Y年%m月%d日‘); 2018年06月06日
四、其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
五、流量控制函数
#if函数 if else的效果
SELECT IF(表达式,‘返回值1','返回值2');
#case函数
使用一:
case 要判断的表达式或字段
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
……
else 要显示的值n或语句n;
end