MySQL学习笔记

标题 MySQL学习笔记

一、基础知识

数据库的好处

1、可以持久化数据到本地。
2、结构化查询

数据库常见概念

1、DB：数据库，存储数据的容器
2、DBMS：数据库管理系统，又称数据库软件或数据库产品，用于创建或管理数据库
3、SQL：结构化查询语言，用于和数据库通讯的语言，不是木个人数据库软件所特有的，而是几乎所有主流数据库软件所特有的。

数据库存储数据的特点

1、数据存放到表中，然后表在存放到库中。
2、一个库中可以有多张表，每张表具有唯一的表名用来标识自己
3、表中有一个或多个列，列又称为“字段”，相当于Java中的“属性”
4、表中的每一行数据相当于Java中的“对象”

二、常用命令

1、查看当前所有数据库

show databases;

2、打开指定的库

use 库名;

3、查看当前库的所有表

show tables;

4、查看其他库的所有表

show tables from 库名;

5、创建表

create table 表名（
                     列名 列类型,
                     列名 列类型,
                   );

6、查看表结构

desc 表名

7、查看服务器版本

1）登录到MySQL服务器端

select version();

2)没有登录到MySQL服务器端

mysql--version;
或
mysql--V

三、MySQL语法规范

• 1、不区分大小写，但建议关键字大写，表名、列名小写
• 2、每条命令用分号结尾
• 3、每条命令根据需要可以进行缩进或换行
• 4、注释
  单行注释 ： #注释文字
  多行注释：/* 注释文字 */

四、DQL语言

基础查询

首先要导入库 use 库名；
语法：select 查询列表 from 表名；
查询列表可以是：表中的字段、常量值、表达式、函数
查询的结果是一个虚拟的表格

1、查询表中的单个字段

select 列名 from 表名

2、查询表中的多个字段

select 列名1，列名2，列名3 from 表名

3、查询表中的所有字段

select * from 表名

4、查询常量值

select 100;
select 'name';# 字符型货日期型必须用单引号引起来

5、查询表达式

select 100*98-2;

6、查询函数

select 函数名 (实参列表) ;

7、起别名

用 AS 或 空格
1）便于理解
2）如果要查询的字段有重名的情况，使用别名可以区分开来

select 100*98 as 结果;
select last_name as 姓,first_name as 名 from 表名;
select last_name  姓,first_name   名 from 表名;

8、去重

select distinct 列名 from 表名;

9、加号的作用

作用：运算符
若两个都是数值型，则做加法运算；
若一方是字符一方是数值型，把字符转换成数值运算。若转换不成功，字符按0运算。
若一方为null，则结果肯定为null；

10、拼接多个字段 concat

例子：把表中的姓和名连接起来

select concat(last_name,first_name) as 姓名;

11、IFNULL

功能：如果某一列为空，还要进行运算时可以把 null变为0

select ifnull(列名，0) from 表名;

12、ISNULL

功能：查询表中的值，如果是NULL返回1，不是NULL返回0

条件查询

语法：

			select 
						查询列表
			from
						表名
			where 
						筛选条件;

注意：执行顺序：表名——筛选条件——查询列表
分类：
1）按条件表达式筛选
条件运算符：>、 <、 =、 >< 、>=、<=、<=>
2）按逻辑表达式查询
逻辑运算符：&&、||、！、and 、or、 not
3）模糊查询
like、between and、in 、is null

1、按条件表达式查询

• 案例1：查询工资大于12000的员工信息

select
		*
from
		表名
where
		salary>12000;

• 案例2：查询部门编号不等于90的员工名和部门编号

select
		last_name,
		department_id
from
		表名
where
		department_id><90;

2、按逻辑表达式查询

• 案例1：查询员工工资在10000——20000之间的员工名、工资、以及奖金
  AND \ OR\ NOT

select
		last_name,
		salary,
		commission_pct
from
		表名
where
		salary>=10000 AND salary<=20000;

• 案例2：查询部门标号不是在90-110之间，或者工资高于15000的员工信息

select
		*
from
		表名
where
		NOT (department_id>=90 AND department_id<=100) OR salary>=15000 ;

3、模糊查询

1）LIKE

一般和通配符（%任意多个字符 包含0个字符 ；_ 任意单个字符）搭配使用
可以是字符型，也可以是数值型

• 案例一：查询员工名包含字符a的员工信息

select
		*
from
		表名
where
		last_name 	LIKE '%a%';

• 案例二：查询员工名第二个字符为a，第五个字符为b

select
		*
from
		表名
where
		last_name 	LIKE '_a_b%';

• 案例三：员工名中第二个字符是下划线的

select
		*
from
		表名
where
		#或 last_name 	LIKE '_\_%';   #加转义字符
		last_name 	LIKE '_$_%' escape '$'; #escape加任意转义字符

2）between and

可以提高简洁度；包含临界值；完全等价于>=a,<=b

• 案例一：查询员工编号在100——200之间的信息

select
		*
from
		表名
where
		# department_id >=100 AND salary<=200;
		department_id between 100 AND 200;

3）in

使用 IN 比使用 OR提高了简洁度
IN列表的数据类型必须一致或兼容

• 案例一：查询员工工种编号是“AD-VP”'AD-PRES”的员工名和工种编号

select 
		last_name,
		job_id
from
		表名
where
		# job_id='AD-VP' OR job_id='AD-PRES' ;
		job_id IN ('AD-VP','AD-PRES');

4）is null

由于空不能和“=”一起使用，所以查询空的信息是用 IS NULL

• 案例一：查询奖金率为0的员工信息

select 
		*
from
		表名
where
		commission_pct IS NULL ;
		commission_pct  <=> NULL ;#安全等于 判断是否等于

• “IS NULL " VS " 安全等于 <=>”
  IS NULL :仅仅可以判断null值，可读性较高，建议使用
  <=> :既可以判断null值，也可以判断普通树脂，可读性差。

排序查询

语法：
select 查询列表
from 表名
【where 筛选条件】
order by 排序列表 [asc | desc]
特点：
1）asc 升序 desc 降序 默认升序
2）order by 可以支持单个字段、多个字段、表达式、函数、别名查询
3）order by 一般放在查询语句最后面，limit字句除外

• 案例一：查询员工信息，要求工资从高到低

select  *
from  表名
ORDER BY salary  DESC ;

• 案例二：查询部门编号>=90的员工信息 ，以入职时间先后排序【添加筛选条件】

select 	*
from  表名
where  department_id >=90
ORDER BY hirdate ASC ;

• 案例三：按年薪高低显示员工信息 和年薪【按表达式排序】

select  * ,salary*12*(1+IFNULL (commission_pact,0)) AS 年薪
from  	表名
ORDER BY salary*12*(1+IFNULL (commission_pact,0)) DESC ;

• 案例四：按年薪高低显示员工信息 和年薪【按别名排序】

select  * ,salary*12*(1+IFNULL (commission_pact,0)) AS 年薪
from  	表名
ORDER BY 年薪 DESC ;

• 案例五：按姓名的长度显示员工的姓名和工资【按函数排序】

select LENGTH (last_name) as 字节长度 ,last_name,salary
from 表名
ORDER BY LENGTH (last_name) DESC;

• 案例六：先按工资升序，再按员工编号降序【按多个字段排序】

select *
from 表名
ORDER BY salary ASC ,employee DESC;

常见函数学习

功能：类似于Java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：提高代码重用型；隐藏实现细节。
调用：select 函数名（实参列表）【from 表】
特点：叫什么（函数名）干什么（功能）
分类：单行函数（如concat , length , ifnull） 分组函数（又叫组函数 、统计函数 ）

单行函数

1、字符函数

• length 获取参数值的字节数

select LENGTH ('job');

• concat 拼接字符数

select CONCAT (last_name,'_',first_name) 姓名 from 表名

• upper 、lower 变大写 ，变小写

select UPPER ('john');
select LOWER ('JUho');

#案例姓变大写，名变小写然后拼接起来

select CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 from 表名 ;

• substr substring 截取索引
  注意：索引从1开始
  1）截取从指定索引处后面所有字符

select SUBSTR ('祝你好运！',3) out_put ; #好运

2）截取从指定索引处后面所有字符

select SUBSTR ('祝你好运！',1,,2) out_put ; #祝你

3）案例：姓名中首字符大写，其他字符小写，然后用下划线拼接

select CONCAT(UPPER (SUBSTR(last_name,1,1)),'_',LOWER (SUBSTR(last_name,2)))  out_put;

• instr 返回在字符串中第一次出现的索引，如果没有，返回0

select INSTR ('你真好看！','好看') AS out_put; # 结果是3

• trim 去前后空格，只能是前后

select TRIM ('      你  真好看！   ') AS out_put;  #结果：你  真好看！
select TRIM ('a' from'aaaa你aaaa真aaaaa好看！aaaa') AS out_put;  #结果：你aaaa真aaaaa好看！

• lpad 用指定的字符实现指定长度的左填充，如果长度太短会从右侧截断

select LPAD ('祝你好运！',10,'*') AS out_put;  #结果 *****祝你好运！
select LPAD ('祝你好运！',2,'*') AS out_put;  #结果 祝你

• rpad 右填充 同上

select RPAD ('祝你好运！',10,'*') AS out_put; #结果 祝你好运！*****

• replace 替换

select REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put; #结果张无忌爱上了赵敏

2、数学函数

• round 四舍五入

select round (-1.55);  #结果-2
select round (-1.345,2);  #结果 -1.34  小数点后保留两位

• ceil 向上取整 返回 >=该参数的最小整数

select ceil (-1.20);  #结果-1

• floor 向下取整 返回 <=该参数的最大正整数

 select floor (9.99); # 结果9
 select floor (-9.99); #结果-10

• truncate 截断 小数点后保留几位

select truncate (1.3999229,2);  # 结果 1.39

• mod 取余 相当于% mod（a,b）= a-a/b-b
  结果的正负与被除数相同

select mod （10，-3）; #结果 -1 

3、日期函数

• now 返回当前系统日期和时间

select now();

• curdate 返回当前系统日期，不包含时间

select curdate();

• curtime 返回当前时间，不包含日期

select curtime();

• 还可以获取指定部分的年、月、日、小时、分钟、秒

select year(now());

• str_to_date 通过指定格式将字符转换成日期
  

select str_to_date('1997-3-4',%y-%m-%d); #结果 97-03-04

• date_format 将日期转换成字符

select date_format (now(), '%m月 %d日 %年');

4、其他函数

• version（） 查看函数版本
• database（）查看数据库

5、流程控制函数

• if（）

select if(10>5,'大','小');  #大