MySQL笔记

登陆本地MySQL:mysql -u root -p密码
数据库的相关概念:
1.DB:数据库,保存一组有组织的数据的容器
2.DBMS:数据库管理系统,又称为数据库软件(产品),用于管理DB中的数据
3.SQL:结构化查询语言,用于和DBMS通信的语言

数据库特点:
1.将数据放到表中,表再放到库中
2.一个数据库中可以有多个表,每个表都有一个名字,用来标识自己,表名具有唯一性
3.表具有一些特性,这些特性定义了数据在表中如何存储,类似java中类的设计
4.表由列组成,我们也称为字段。所有表都是由一个或者多个列组成,每一列类似java中的属性
5.表中的数据是按行存储的,每一行类似java中的对象

MySQL下载:https://www.mysql.com

创建数据库:CREATE DATABASE 数据库名称 [库选项];
CREATE DATABASE表示创建数据库;数据库名称可以是字母、数字和下划线组成的任意字符串;库选项用于设置此数据库的相关特性,如字符集CHARSET,校对集COLLATE.
例:CREATE DATABASE mydb;
如果创建的数据库已经存在,则程序会报错,在创建数据库时可以在数据库名称前添加 IF NOT EXISTS;
例:CREATE DATABASE IF NOT EXISTS mydb;

查看MySQL中已经存在的数据库:SHOW DATABASES;
在完成创建数据库后,若要查看该数据库中的信息:SHOW CREATE DATABASE 数据库名称;
选择数据库:USE 数据库名称;
删除数据库:DROP DATABASE 数据库名称;
在使用DROP DATABASE删除数据库时,若待删除数据库不存在,MySQL服务器会报错,可以使用DROP DATABASE IF EXISTS 数据库名称; 来删除数据库

在MySQL数据库中,所有的数据都储存在数据表中,若要对数据执行增删改查等操作,首先需要在指定的数据库中准备一张表
创建数据表:CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名(
字段名 字段类型 [字段属性]…) [表选项]
可选项TEMPORARY 表示临时表,仅在当前会话中可见,并且在会话关闭时自动关闭。字段名指的是数据表的列名;字段类型设置字段中保存的数据类型,如时间日期类型等;
可选项“字段属性”指的是指的是字段的某些特殊约束条件。可选的“表选项”用于设置表的相关属性,如储存引擎(ENGINE)、字符集(CHARSET)和校对集(COLLATE)
在操作数据表前需要先用 USE 数据库 选择数据库

例:#创建mydb数据库
CREATE DATABASE mydb;
#选择数据库
USE mydb;
#创建goods数据表
CREATE TABLE goods(
id INT COMMENT ‘编号’,
name VARCHAR(32) COMMENT ‘商品名’,
price INT COMMENT ‘价格’,
description VARCHAR(255) COMMENT ‘商品描述’
);
INT用于设置字段数据类型是整型;VARCHAR(L)表示可变长度的字符串,L表示字符串数,COMMENT用于在创建表时添加注释内容,并将其保存到表结构中。

查看数据表:SHOW TABLES [LIKE匹配模式];
若不添加可选项LIKE匹配模式,表示查看当前数据库中的所有数据表;匹配模式符有两种,分别为 % 和 _ 。
前者表示匹配一个或多个字符,代表任意长度的字符串,长度也可以为0,后者仅可以匹配一个字符串。

查看数据库的相关信息(名称、创建时间、存储引擎):SHOW TABLE STATUS [FROM 数据库名] [LIKE匹配模式];

修改数据表名称:
1:ALTER TABLE 旧表名 RENAME [TO|AS] 新表名;
2:RENAME TABLE 旧表名1 TO 新表名1[,旧表名2 TO 新表名2]…

修改表选项:数据表中的表选项字符集、储存引擎以及校对集也可以通过ALTER TABLE 修改:ALTER TABLE 表选项 [=] 值;
例:ALTER TABLE my_goods CHARSET = utf8;修改数据表的字符集

查看数据表的字段信息:MySQL提供的DESCRIBE语句可以查看数据表中所有字段或指定字段信息,包括字段名、字段类型等。
查看所有字段的信息:{ DESCRIBE | DESC } 数据表名;
查看指定字段的信息:{ DESCRIBE | DESC } 数据表名 字段名;

查看创建数据表的具体SQL语句以及表的字符编码,可以使用:SHOW CREATE TABLE 表名;

查看数据表结构:
1:SHOW [FULL] COLUMNS FROM 数据表名 [FROM 数据库名];
2:SHOW [FULL] COLUMNS FROM 数据库名.数据表名;
SHOW FULL COLUMN语句除与 DESC 语句查询出的相同字段外,还包括Collation (校对集)字段、Privileges(权限)字段和Comment(注释)字段。

修改字段名:ALTER TABLE 数据表名 CHANGE [COLUMN] 旧字段名 新字段名 字段类型 [字段属性];

修改字段的位置:
数据表在创建时,字段编写的先后顺序就是其在数据库中储存的顺序,若需要调整某个字段的位置,也可以使用MODIFY实现
ALTER TABLE 数据表名 MODIFY [COLUMN] 字段名1 数据类型 [字段属性] [ FIRST | AFTER 字段名2]
修改字段的位置就是在修改字段类型的后面添加 FIRST 或 AFTER 字段名2。前者表示将‘字段名1’调整为数据表的第1个字段,后者表示将‘字段名1’插入到‘字段名2’的后面

新增字段:
新增一个字段,并可以指定其位置:ALTER TABLE 数据表名 ADD [COLUMN] 新字段名 字段类型 [ FIRST | AFTER 字段名]
同时新增多个字段:ALTER TABLE 数据表名 ADD [COLUMN] (新字段名1 字段类型1,新字段名2 字段类型2,…)
在不指定位置的情况下,新增的字段默认添加到表的最后。

删除字段:ALTER TABLE 数据表名 DROP [COLUMN] 字段名;

删除数据表:DROP [TEMPORARY] TABLE [IF EXISTS] 数据表1[,数据表2]…;
删除数据表操作指的是删除数据库中已经存在的表

为数据表中的所有字段添加数据:INSERT [INTO] 数据表名 {VALUES | VALUE}(值1,[,值2]…);
例:mysql>INSERT INTO goods
->VALUES(1,‘notebook’,4998,‘High cost performance’);

通常需要在创建数据库时添加表选项,设置数据表的字符集,例:
CREATE TABLE goods(
字段…
)DEFAULT CHARSET utf8;
对于已经添加数据的数据表,则可以通过ALTER TABLE … CHANGE/MODIFY完成对表字段字符集的设置,例:
mysql>ALTER TABLE goods
->MODIFY name VARCHAR(32) CHARACTER SET utf8,
->MODIFY description VARCHAR(255) CHARACTER SET utf8;

除了为数据表中所有字段添加数据外,还能通过指定字段名的方式增加数据。指定的字段名可以是数据表中全部的字段,也可以是部分的字段
INSERT [INTO] 数据表名 (字段名1[,字段名2]…)
{VALUE | VALUE}(值1[,值2]);
例:将编号为3的Mobile phone 插入到goods数据表中
mysql>INERT INTO goods (id,name) VALUE (3,‘Mobile phone’);

MySQL中还提供了另外一种使用INSERT语句为指定字段添加数据的方式:
INSERT [INTO] 数据表名
SET 字段名1 = 值1[,字段名2=值2]…
例:INSERT INTO goods SET id=3,name=‘Mobile phone’;

一次添加多行数据:
INSERT [INTO] 数据表名 [(字段列表)]
{VALUES|VALUE}(值列表)[,(值列表)]…;
多个值列表之间使用逗号分隔,其中,字段列表在省略时,插入的数据需严格按照数据表创建的顺序插入,否则‘值列表’插入的数据仅需与字段列表中的字段对应即可
mysql>INSERT INTO goods VALUES
->(1,‘notebook’,4998,‘High cost performance’),
->(2,‘笔记本’,9998,‘续航时间超过10小时’),
->(3,‘Mobile phone’,NULL,NULL);

查询表中全部数据:SELECT * FROM 数据表名;
指定字段查询数据:SELECT {字段名1,字段名2,字段名3,…} FROM 数据表名;
查询数据时,若想要查询出符合条件的相关数据记录时,可以使用WHERE实现
SELECT * |{字段名1,字段名2,字段名3,…} FROM 数据表名 WHERE 字段名 =值;

修改数据:
UPDATE 数据表名
SET 字段名1=值1[,字段名2=值2,…]
[where 条件表达式]
例:将goods中编号为2的商品价格改为5899元
mysql>UPDATE goods SET price=5899 WHERE id=2;

删除数据:DELETE FROM 数据表名 [WHERE 条件表达式];
例:DELETE FROM goods WHERE id=3;

例:电子杂志订阅表的操作:
1.选择数据库
mysql>CREATE DATABASE IF NOT EXISTS mydb;
mysql>use mydb;
2.创建电子杂志订阅表
mysql>CREATE TABLE subscribe(
->id INT COMMENT ‘编号’,
->email VARCHAR(60) COMMENT ‘邮件订阅的邮箱地址’,
->status INT COMMENT ‘是否确认,0未确认,1已确认’,
->code VARCHAR(10) COMMENT ‘邮箱确认的验证码’
->)DEFAULT CHARSET=utf8;
3.操作电子杂志订阅表的数据
(1):添加数据
mysql>INSERT INTO subscribe VALUES
->(1,‘tom123@163.com’,1,‘TRBXPO’),
->(2,‘lucy123@163.com’,1,‘LOICPE’),
->(3,‘lily123@163.com’,0,‘JIXDAMI’),
->(4,‘jimmy123@163.com’,0,‘QKOLPH’),
->(5,‘joy123@163.com’,1,‘JSMWNL’);
(2):查询数据
mysql>SELECT * FROM subscribe WHERE statue=1;
(3):修改数据
mysql>UPDATE subscribe SET status=1 WHERE id=4;
(4):删除数据
mysql>DELETE FROM subscribe WHERE id=5;

数据类型:MySQL数据库提供了多种数据类型,其中包括数字类型、时间和日期类型、字符串类型。

数字类型:
1.整数类型
MySQL中根据取值范围的不同,整数类型可以分为5种,分别是TINYINT SMALLINT MEDIUMINT BIGINT
为字段设置零填充(ZEROFILL)时,若数值宽度小于显示宽度,会在左侧填充0,。
例:
mysql>CREATE TABLE my int2(
->int_1 INT(3) ZEROFILL,
->int_2 INT(6) ZEROFILL
);

浮点数类型:
在MySQL中,储存的小数都是使用浮点数或定点数来表示的。浮点数的类型有两种,分别是单精度浮点数(FLOAT)和双精度浮点数(DOUBLE),当浮点数使用UNSIGNED修饰为无符号时,取值范围将不包含负数。

时间和日期类型:
为了方便在数据库中储存日期和时间,MySQL提供了表示日期和时间的数据类型,分别是YEAR DATE TIME TIMESTAMP

1.YEAR类型用于表示年份
CREATE TABLE my_year(y YEAR); #设置y字段的数据类型为YEAR
INSERT INTO my_year VALUES(2020); #插入年份数据,2020

2.DATE类型
DATE类型用于表示日期值,不包含时间部分
CREATE TABLE my_date (d DATE); #设置d字段的数据类型为DATE
INSERT INTO my_date VALUES(‘2020-01-21’); #插入日期数据
INSERT INTO my_date VALUES(CURRENT_DATE); #插入当前系统日期
INSERT INTO my_date VALUES(NOW()); #插入当前系统日期

3.TIME类型
TIME类型用于表示时间值,它的显示形式一般为HH:MM:SS HH表示小时 MM表示分 SS表示秒

4.DATETIME类型
DATETIME用于类型用于表示日期和时间,它的显示形式为’YYYY-MM-DD HH:MM:SS’ YYYY表示年,MM表示月,DD表示日,HH表示小时,MM表示分,SS表示秒
使用NOW()来输入当前系统的日期和时间。

5.TIMESTAMP类型:
TIMESTAMP(时间戳)类型用于表示日期和时间,它的显示形式与DATETIME相同,但取值范围比DATETIME小

(1):NOT NULL表示非空约束,该字段将不允许保存NULL值
(2):DEFAULT表示默认约束,当字段无任何输入时,自动设置某个值作为默认值。此处设为CURRENT_TIMESTAMP表示使用系统当前日期和时间作为默认值

字符串类型:
CHAR 固定长度字符串
VARCHAR 可变长度字符串
TEXT 大文本数据
ENUM 枚举类型
SET 字符串对象
BINARY 固定长度的二进制数据
VARBINARY 可变长度的二进制数据
BLOB 二进制大对象

ENUM类型:
ENUM类型又称为枚举类型,定义ENUM类型的方式:ENUM(‘值1’,‘值2’,‘值3’,…)
(‘值1’,‘值2’,‘值3’,…)称为枚举列表,ENUM类型的数据只能从枚举列表中取,并且只能取一个
例:
#创建表
mysql>CREATE TABLE my_enum (gender ENUM(‘male’,‘female’));
#插入两条测试记录:
mysql>INSERT INTO my_enum VALUES(‘male’),(‘female’);
#查询记录,查询结果为"female"
mysql>SELECT * FROM my_enum WHERE gender=‘female’;
#插入枚举列表中没有的测试值 结果会报错
mysql>INSERT INTO my_enum VALUES(‘m’);

SET类型:
SET类型用于保存字符串对象,其定义格式与ENUM类型类似
SET(‘值1’,‘值2’,‘值3’,…)
SET类型与ENUM的区别在于,它可以从列表中选择一个或多个值来保存,多个值之间用逗号分隔
例:
#创建表
mysql>CREATE TABLE my_set (hobby SET(‘book’,‘game’,‘code’));
#插入3条测试记录
mysql>INSERT INTO my_set VALUES(’’),(‘book’),(‘book,code’);
#查询记录,查询结果为’book,code’
mysql>SELECT * FROM my_set WHERE hobby=‘book,code’;

ENUM类型类似于单选框,SET类型类似于复选框
ENUM和SET类型列表中的值都可以使用中文,但必须设置支持中文的字符集,例如CREATE TABLE my_enum(gender ENUM(‘男’,‘女’)) CHARSET=GBK;

BINARY和VARBINARY类型:
BINARY类型的长度是固定的,如果数据的长度不足最大长度,将在数据的后面用"\0"补齐,最终达到指定长度.

BLOB类型:
BLOB类型用于保存数据量很大的二进制数据,如图片、PDF文档
BLOB类型与TEXT类型很相似,但BLOB类型数据是根据二进制编码进行比较和排序,而TEXT类型数据是根据文本模式进行比较和排序

JSON数据类型:
#JSON数组:
[“abc”,10,null,true,false]
#JSON对象
{“k1”:“value”,“k2”:10}
JSON数组中保存的数据可以是任意类型。JSON数组使用[]实现,多个值之间使用逗号分隔,JSON对象使用{}实现,保存的数据是一组键值对.
例:
#创建表
mysql>CREATE TABLE my_json(j1 JSON,J2 JSON);
mysql>INSERT INTO my_json VALUES
->(’{“k1”:“value”,“k2”:10}’,’[“run”,“sing”]’);
#查询记录
mysql>SELECT * FROM my_json;

表的约束:
默认约束:默认约束用于为数据表中的字段指定默认值,即当在表中插入一条新记录时,如果没有给这个字段赋值,数据库系统会自动为这个字段插入默认值。
定义默认值:字段名 数据类型 DEFAULT 默认值;
BLOB、TEXT数据类型不支持默认约束
例:
mysql>CREATE TABLEB my_default(
->name VARCHAR(10),
->age INT UNSIGNED DEFAULT 18
);

删除默认约束:
mysql>ALTER TABLE my_default MODIFY age INT UNSIGEND;
添加默认约束:
mysql>ALTER TABLE my_default MODIFY age INT UNSIGNED DEFAULT 18;

非空约束:
非空约束指的是字段的值不能为NULL,在MySQL中,非空约束是通过NOT NULL定义的
字段名 数据类型 NOT NULL;

唯一约束:
唯一约束用于保证数据表中字段的唯一性,即表中字段的值不能重复出现。唯一约束是通过UNIQUE定义的。
#列级约束
字段名 数据类型 UNIQUE;
#表级约束
UNIQUE(字段名1,字段名2,…);
例:
(1):创建表,分别通过列级约束和表级约束的方式增加唯一性约束
#列级约束
mysql>CREATE TABLE my_unique_1(
->id INT UNSIGNED UNIQUE,
->usernmae VARCHAR(10) UNIQUE
);
#表级约束
mysql>CREATE TABLE my_unique_2(
->id INT UNSIGNED,
->username VARCHAR(10),
->UNIQUE(id),
->UNIQUE(username)
->);
(2):为含唯一约束的字段插入记录
mysql>INSERT INTO my_unique_1 (id) VALUES(1);
mysql>INSERT INTO my_unique_1 (id) VALUES(2);
#插入重复记录,插入失败
mysql>INSERT INTO my_unique_1 (id) VALUES(1);

(3):添加和删除唯一性约束
#创建测试表
mysql>CREATE TABLE my_unique_3 (id INT);
#添加唯一性约束
mysql>ALTER TABLE my_unique_3 ADD UNIQUE(id);
#删除唯一性约束
mysql>ALTER TABLE my_unique_3 DROP INDEX id;

主键约束:
在MySQL中,为了快速查找表中的信息,可以设置主键来实现。
#列级约束
字段名 数据类型 PRIMARY KEY
#表级约束
PRIMARY KEY(字段名1,字段名2,…)
例:
(1)创建my_primary表,为id字段添加主键约束
mysql>CREATE TABLE my_primary(
->id INT UNSIGNED PRIMARY KEY,
->username VARCHAR(20)
->);

添加主键约束后,插入重复值或NULL值会失败
(2)为一个表添加或删除主键约束
#删除主键约束
mysql>ALTER TABLE my_primary DROP PRIMARY KEY;
#删除id字段的非空约束
mysql>ALTER TABLE my_primary MODIFY id INT UNSIGNED;
#添加主键约束
mysql>ALTER TABLE my_primary ADD PRIMARY KEY (id);

自动增长:
在为数据表设置主键约束后,每次插入记录时,都需要检查主键的值,防止插入的值重复导致插入失败,这回给数据库的使用带来很多麻烦,
为此可以利用MySQL提供的自动增长功能来自动生成主键的值
基本语法:字段名 数据类型 AUTO_INCREMENT
注意点:
1.一个表只能有一个自动增长字段,该字段的数据类型是整数类型,且必须定义主键,如UNIQUE KEY , PRIMARY KEY
2.若为自动增长字段插入NULL、0、DEFAULT或在插入时省略该字段,则该字段就会使用自动增长值;若插入的值是一个具体的值,则不会使用自动增长值
3.自动增长值从1开始自增,每次加1.若插入的值大于自动增长的值,则下次插入的自动增长值会自动使用最大值加1;若插入的值小于自动增长值,则不会对自动增长值产生影响
4.使用DELETE删除记录时,自动增长值不会减少或填补空缺
例:
(1):创建my_auto表,为id字段添加自动增长
mysql>CREATE TABLE my_auto(
->id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
->username VARCHAR(20)
->);
(2):插入记录进行测试
#插入时省略id字段,将会自动使用自动增长值
mysql>INSERT INTO my_auto (username) VALUES(‘a’);
#为id字段插入NULL,将会使用自动增长值
mysql>INSERT INTO my_auto VALUES(NULL,‘b’);
#为id字段插入具体值6
mysql>INSERT INTO my_auto VALUES(6,‘c’);
#为id字段插入0,使用自动增长值
mysql>INSERT INTO my_auto VALUES(0,‘d’);

(3):为现有的表修改或删除自动增长
#修改自动增长值
mysql>ALTER TABLE my_auto AUTO_INCREMENT=10;
#删除自动增长
mysql>ALTER TABLE my_auto MODIFY id INT UNSIGNED;
#重新为id添加自动增长
mysql>ALTER TABLE my_auto MODIFY id INT UNSIGNED AUTO_INCREMENT;

例:设计用户表
1.创建用户表
mysql>CREATE TABLE mydb.user(
->id INT USIGNED PRIMARY KEY AUTO_INCREMENT COMMENT ‘用户id’,
->username VARCHAR(20) UNIQUE NOT NULL COMMENT ‘用户名’,
->mobile CHAR(11) NOT NULL COMMENT ‘手机号码’,
->gender ENUM(‘男’,‘女’,‘保密’) NOT NULL COMMENT ‘性别’,
->reg_time TIMESTAMP DEFAULE CURRENT_TIMESTAMP COMMENT ‘注册时间’,
->level TINYINT UNSIGNED NOT NULL COMMENT ‘会员等级’
->)DEFAULT CHARSET=utf8;
2.添加测试记录
mysql>INSERT INTO mydb.user VALUES(
->NULL,‘小明’,
->‘12311111111’,‘男’,
->‘2018-01-01 11:11:11’,1
->);

查询基础:
语法:select 查询列表 from 表名
查询列表可以是:表中的字段、常量、表达式、函数
查询的结果是一个虚拟的表格

例:
查询表中的单个字段:
select last_name from employees;
查询表中的多个字段:
select last_name,salary from employees;
查询表中的所有字段:
select * from employees;
查询表达式:
select 100*98;
查询函数:
select version();

变量,在这个符号当中的变量为非关键字
查询常量值不需要加from

起别名:
方式一:
select 100%98 as 结果;
select last_name as 姓,first_name as 名 from employees;

方式二:
select last_name 姓,first_name 名 from employees;

案例:查询salary,显示结果为out put
select salary as “out put” from employees;
此时的out是关键字,空格是特殊字符,需要使用双引号括起来

去重(distinct):
案例:查询员工表中涉及到的所有的部门编号
select distinct department_id from employees;

+号的作用 :
java中的+号:
1.运算符,两个操作数都为数值型
2.连接符,只要有一个操作数为字符串

mysql中的+号:仅仅有一个功能:运算符

select 100+98;两个操作数都为数值型,则做加法运算
select ‘123’+90;其中一方为字符型,试图将字符型数值转换成数值型,如果转化成功,则继续做加法运算,如果转换失败,则将字符型数值转换成0
select null+10;只要有一方结果为null,则结果肯定为null

连接符号(concat):
select concat(‘a’,‘b’,‘c’) as 结果;
案例:查询员工名和姓连接成一个字段,并显示为 姓名
select concat(last_name,first_name) as 姓名 from 表;

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

分类:
一、按条件表达式筛选
条件运算符:> < = != <> >= <=
二.按逻辑表达式筛选
逻辑运算符:&& || !
三.模糊查询
like between and in (is null)

例1:查询工资大于12000的员工信息
select * from employees where salary>12000;

例2:查询部门编号不等于90号的员工名和部门编号
select last_name,department_id from employees where department_id!=90;

二、按逻辑表达式筛选
select last_name,salary,commission_pct from employees where salary>=10000 and salary<=20000

1.like:
特点:1.一般和通配符搭配使用
通配符:
% 任意多个字符,包含0个字符
_ 任意单个字符

例1:查询员工名中包含字符a的员工信息
select * from employees where last_name like ‘%a%’;
例2:查询员工名中第三个字符为e,第五个字符为a的员工名和工资
select last_name,salary from employees where last_name like ‘__n_l%’;

MySQL中的转义字符为\

2.between and
between and包含临界值
例:查询id在100到120之间的员工信息
select * from employees where employee_id between 100 and 120;

3.in关键字
含义:1.判断某字段的值是否属于in列表中的某一项
2.in列表的值类型必须一致或兼容
案例:查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
select last_name,job_id from employees where job_id in(‘IT_PROT’,‘AD_VP’,‘AD_PRES’);

4.is null
=不能用于判断null值
案例1:查询没有奖金的员工名和奖金率
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;
<=>既可以判断null值,又可以判断普通的数值
<>代表不等于

排序查询:
语法:select 查询列表 from [where 筛选条件] order by 排序列表 [asc|desc];
asc代表是升序,desc代表的是降序,如果不写,默认是升序
案例:查询员工信息,要求工资从高到低
select * from employees order by salary desc;

查询部门编号>=90的员工信息,按入职时间的先后进行排序
select * where department_id>=90 order by hiredate asc;

例:查询员工的姓名和部门号和年薪,按年薪降序 按姓名升序
select last_name,department_id,salary*12 年薪 from employees order by 年薪 desc,last_name asc;

例:选择工资不在8000到17000的员工的姓名和工资,按工资降序
select last_name,salary from employees where salary not between 800 and 17000 order by salary desc;

查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
select * from employees where email like ‘%e%’ order by length(email) desc,department_id asc;

常见函数:
概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:1.隐藏了实现细节 2.提高代码的重用性
调用:select 函数名(实参列表) [from 表];
特点:
1.叫什么(函数名)
2.干什么(函数功能)
分类:
1.单行函数:concat、length、ifnull等
2.分组函数
功能:做统计使用,又称为统计函数、聚合函数、组函数

一、字符函数:
1.length:获取参数值的字节个数
例:select length(‘jhon’)

2.concat 拼接字符串
select concat(last_name,’_’,first_name) from employees;

3.upper、lower:将字符串转换为大写或者小写
select upper(‘john’);
select lower(‘JOHN’);
例:将姓变大写;名变小写,然后拼接
select concat(upper(last_name),lower(first_name)) 姓名 from employees;

4.substr、substring
例:截取从指定索引处指定字符长度的字符
select substr(‘李莫愁爱上了陆展元’,1,3) out_put from employees;
结果为:李莫愁

5.instr
select instr(‘杨不悔爱上了殷六侠’,‘殷六侠’) as out_put;
返回子字符串在父字符串第一次出现的索引,如果找不到返回0

6.trim
select trim(‘a’ from ‘aaaaaaa张aaaaaa翠山’) as out_put;
结果为:张aaaaaa翠山

7.lpad 用指定的字符串实现左填充达到指定长度
例:select lpad(‘殷素素’,10,’*’) as out_put;

8.rpad 用指定的字符串实现左填充达到指定长度

9.replace替换(替换全部)
select replace(‘张无忌爱上了周芷若’,‘周芷若’,‘赵敏’) as out_put;

二、数学函数
1.round 四舍五入
select round(1.45);
select round(1.567,2);//四舍五入并且保留两位小数

2.ceil 向上取整,返回>=该参数的最小整数
select ceil(1.001);

3.floor:向下取整,返回<=该参数的最大整数

4.truncate 截断
select truncate(1.65,1);结果为1.6

5.#mod取余
select mod(10,3);

三、日期函数
now 返回当前系统日期+时间
select now();

#curdate 返回当前系统日期,不包含时间
select curdate();

#可以获取指定的部分,年、月、日、小时、分钟、秒
select year(now()) 年;
select month(now()) 月;

四、其他函数
select version();
select database();
select user();

五.流程控制函数
1.if函数: if else 的效果
select if(10>5,‘大’,‘小’);

2.case函数的使用一:
case 要判断的字符或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;

else 要显示的值n或语句n;
end

例:查询员工的工资,要求部门号=30,显示的工资为1.1倍,部门号=40,显示的工资为1.2倍,部门号=50,显示的工资为1.3倍,其他部门显示的工资为原工资

select salary 原始工资,department_id,
case department_id
when 30 then salary1.1
when 40 then salary
1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;

3.case 函数的使用二:类似于 多重if:
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2

else 要显示的值n或语句n
end
例:查询员工的工资情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示c级别
否则,显示D级别

select salary,
case
when salary>20000 then ‘A’
when salary>15000 then ‘B’
when salary>10000 then ‘C’
else ‘D’
end as 工资级别 from employees;

二、分组函数
功能:用作统计使用,又称为聚合函数或统计函数或组函数
分类:
sun 求和、avg 平均值、max 最大值、min 最小值、count 计算个数

特点:
1.sum、avg一般用于处理数值型,max、min、count可以处理任何类型
2.以上分组函数都忽略null值
3.可以和distinct搭配实现去重的运算

1.简单的使用 例:
select sum(salary) from employees;
select sum(salary) 和,avg(salary) 平均,max(salary) 最大值

select sount(*) from employees;:可以统计表的行数

例1.:查询公司员工工资的最大值,最小值,平均值,总和
select max(salary) mx_sal,min(salary) mi_sal,avg(salary) ag_sal,sum(salary) sm_sal from employees;
例2:查询部门编号为90的员工个数
select count(*) from employees where department_id = 90;

进阶5:分组查询
引入:查询每个部门的平均工资
分组数据:group by 子句语法
可以使用group by子句将表中的数据分成若干组
语法:
select 分组函数,列(要求出现在group by的后面)
from 表
[where 筛选条件]
group by 分组列表
[order by 子句]
注意:
查询列表必须特殊,要求是分组函数和group by后出现的字段

例1:查询每个工种的最高工资
select max(salary),job_id
from employees
group by job_id;

例2:查询邮箱中包含a字符的,每个部门的平局工资
select avg(salary),department_id
from employees
where email like ‘%a%’
group by department_id;

案例2:查询有奖金的每个领导手下员工的最高工资
select max(salary),manager_id
from employees
where commission_pct is not null
group by manager_id;

例:查询哪个部门的员工个数>2
(1):查询每个部门的员工个数
select count(*),department_id
from employees
group by department_id;

(2):根据1的结果进行筛选,查询哪个部门的员工个数>2
select count(),department_id
from employees
group by departemnt_id
having count(
)>2(分组后的条件);

例:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select max(salary),job_id
from employees
where commission_pct is not null
having max(salary)>12000;

案例:查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示
select avg(salary) a,department_id,job_id
from employees
where department_id is not null
group by department_id,job_id
having a>10000
order by a desc;

连接查询:
含义:又称多表查询,当查询的字段来自多个表时,就会用到连接查询
笛卡尔乘积现象:表1 有m行,表2有n行,结果=m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件

例:
select name,bodyName from boys,beauty
where beauty.boyfriend_id=boys.id;

例2:查询员工名和对应的部门名
select last_name,department_name
from employees,departments
where employees.department_id=departments.department_id;

2.为表起别名
1.提高语句的简洁度
2.区分多个重名的字段
注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定

1.多表等值连接的结果为多表的交集部分
2.n表连接,至少需要n-1个连接条件

查询员工名、工种号、工种名
select e.last_name,e.job_id,j.job_title
from employees e,jobs j
where e.job_id=j.job_id;

案例:查询有奖金的员工名、部门名
select last_name,department_name,commission_pct
from employees e,departments d
where e.department_id=d.department_id
and e.commission_pct is not null;

案例2:查询城市名中第二个字符为o的部门名和城市名
select department_name,city
from departments d,locations l
where d.location_id=l.location_id
and city like ‘_o%’;

查询每个城市的部门个数
select count(*) 个数,city
from departments d,locations l
where d.location_id=l.location_id
group by city;

查询每个工种的工种名和员工的个数,并且按员工个数降序
select job_title,count()
from employees e,jobs j
where e.job_id=j.job_id
group by job_title
order by count(
) desc;

7.实现三表连接
案例:查询员工名、部门名和所在城市,城市以s开头
select last_name,department_name,city
from employees e,departments d,locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
and city like ‘s%’;

自连接:自己连接自己,把原来这张表当成两张表或多张表使用
例:查询员工名和上级的名称
select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees e,employees m
where e.manger_id=m.employee_id;

已知表student,里面有id(学号),name,gradeId(年级编号)
已知表grade里面有id(年级编号),name(年级名)
已知表result,里面有id,score,studentNO(学号)
要求查询姓名、年级名、成绩
select s.name,g.name,r.score
from student s,grade g,result r
where s.id=r.studentNo
and g.id=s.gradeid;

其他函数:
version:当前数据库服务器的版本
database:当前打开的数据库
user:当前用户
password(‘字符’):返回该字符的密码形式
md5(‘字符’):返回该字符的md5加密形式

流程控制函数:
1.if(条件表达式,表达式1,表达式2):如果条件表达式成立,返回表达式1,否则返回表达式2

2.case情况1
case 变量或表达式或字段
when 变量1 then 值1
when 变量2 then 值2

else 值n
end

2.case情况2
case
when 条件1 then 值1
when 条件2 then 值2

else 值n
end

二、sql99语法
语法:
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
连接类型分类:
内连接:inner
外连接:
左外:left 【outer】
右外:right 【outer】
全外:full 【outer】
交叉连接:cross

1.内连接:
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

1.等值连接:
案例1.查询员工名、部门名
select last_name,department_name
from employees e
inner join departments d
on e.department_id=d.department_id;

案例2:查询名字中包含e的员工名和工种名(添加筛选)
select last_name,job_title
from employees e
inner join jobs j
on e.last_name like ‘%e%’;

案例3:查询部门个数>3的城市名和部门个数(添加分组+筛选)
#1.查询每个城市的部门个数
#2.在1的结果上筛选满足条件的
select city,count() 部门个数
from departements d
inner join locations
on d.location_id=l.location_id
group by city
having count(
)>3;

  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
CSDN是一个技术交流平台,里面有许多关于各种编程语言和数据库的学习资料和笔记。而MySQL是其一种常用的关系型数据库管理系统,也是开放源代码软件之一。 在CSND上,MySQL笔记是指关于MySQL数据库的学习和使用的笔记和教程。这些笔记包含了MySQL数据库的基本概念、安装配置、SQL语句的使用、数据表的设计和管理、索引使用、数据备份和恢复等方面的知识。学习MySQL笔记可以帮助开发者更好地理解和应用MySQL数据库MySQL笔记主要可以分为以下几个方面来介绍和学习: 1. 数据库基础知识:学习数据库的基本概念和原理,了解关系型数据库的特点以及MySQL的特点。 2. 安装和配置:学习如何在不同操作系统上安装和配置MySQL数据库,包括设置用户名、密码和端口等。 3. SQL语句的使用:学习SQL语句的基本语法和常用命令,包括查询、插入、更新、删除等操作。 4. 数据表的设计和管理:学习如何设计和创建数据表,包括选择适当的数据类型、设置主键和外键等。 5. 索引的使用:学习如何创建和使用索引来提高查询效率和数据访问速度。 6. 数据备份和恢复:学习如何进行MySQL数据库的数据备份和恢复,包括全量备份和增量备份等。 通过学习MySQL笔记,开发者可以掌握MySQL数据库的基本操作和高级功能,提高数据管理和查询的效率。此外,还可以了解MySQL数据库的优化技巧和性能调优方法,提升数据库的性能和稳定性。 总之,通过CSDN上的MySQL笔记,开发者可以系统地学习和掌握MySQL数据库的相关知识,从而更好地应用于实际的项目开发

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值