分页查询
语法
SELECT 查询的东西
FROM 表
【WHERE 条件】
【GROUP BY 字段】
【HAVING 条件】
【ORDER BY 排序的字段】
LIMIT 条目起始索引【,条目数】
特点
1.写法
写法一:
LIMIT 条目数
意思:从第一条开始,显示指定条目数的数据
写法二:
LIMIT 起始条目索引,条目数
意思:从指定的起始索引的条目开始,显示指定条目数的数据
2.起始索引从0开始
案例1:查询员工表的中前五行
select * from rmployees limit 5;
案例2:查询 员工表 的第11条到第20条
select * from employees limit 10,10;
案例3:查询员工表的第15条到30条
select * from employees limit 14,16;
函数
函数类似于java的方法
分类:
分组函数:将一组值进行统计,得出一个值
单行函数:将一个值进行处理,得出一个值
调用语法:
select 函数名(实参列表);
单行函数:
- 字符函数
- 数学函数
- 日期函数
- 流程控制函数
1.字符函数
length 获取字节长度
unicode:固定字节,一个字母和一个汉字都占2个字节
gbk:一个字母占一个字节,一个汉字占2个字节
utf8:一个字母占一个字节,一个汉字占3个字节
concat 拼接字符
SELECT CONCAT('hello',',',last_name) out_put
FROM employees
upper 转换成大写,lower 转换成小写
SELECT UPPER('ABC');
SELECT LOWER('ABC');
案例:将姓转大写,名转小写,然后拼接
SELECT CONCAT(UPPER(last_name),' ',LOWER(first_name)) 姓名
FROM employees;
instr 获取子串第一次出现的索引,索引从1开始,如果找不到返回0
SELECT INSTR('张三丰爱上了三丰的郭襄','郭襄');
substr
SELECT SUBSTR('郭襄爱上了杨过',6);
SELECT SUBSTR('郭襄爱上了杨过',1,2);
lpad左填充、rpad右填充
SELECT LPAD('尹志平爱上了',5,'*');
trim 去前后空格或特殊字符
SELECT LENGTH(TRIM(' 小 龙 女 ')) 美女;
SELECT TRIM('a' FROM 'aaaaaaaqqqqqq小qq龙q女qqqqqqqqqqqqqq') 美女;
2.数字函数
CEIL向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.0);
FLOOR 向下取整,返回<=该参数的最大整数
SELECT FLOOR(-1.0);
ROUND 四舍五入,先按绝对值四舍五入,然后加正负号
SELECT ROUND(-1.56);
SELECT ROUND(1.537,1);
RAND 随机数,返回0-1之间的小数,不包含1
SELECT RAND();
截断 TRUNCATE
SELECT TRUNCATE(1.99,1); // 1 代表保留一位小数
3.日期函数
获取当前日期 now
SELECT NOW();
获取日期,没有时间 CURDATE
SELECT CURDATE();
获取时间,没有日期 CURTIME
SELECT CURTIME();
获取日期的指定部分
SELECT YEAR(NOW()); //年
SELECT MONTH(NOW()); // 月
SELECT MONTHNAME(NOW()); // 月英文全名
SELECT DAY(NOW()); // 日
SELECT CONCAT(YEAR(NOW()),'年',MONTH(NOW()),'月',DAY(NOW()),'日') 日期;
判断两个日期的差别天数,前者比后者大,返回正数,小返回负数,相等返回0.
SELECT DATEDIFF(NOW(),'2022-8-8');
SELECT DATEDIFF('2017-9-25',NOW());
4.流程控制函数
1.if函数
SELECT IF(100>9,'不错呦','不够大') 备注;
2.case语句
CASE
WHEN 条件1 THEN 显示的值1
WHEN 条件2 THEN 显示的值2
...
ELSE 显示的值n
END
案例:
SELECT salary,
CASE
WHEN salary>20000 THEN '购买汽车'
WHEN salary>10000 THEN '购买手机'
WHEN salary>5000 THEN '购买pad'
ELSE '购买棒棒糖'
END 购买资格
FROM employees;
案例:查询部门和对应的级别
SELECT department_id,
CASE department_id
WHEN 10 THEN 'A'
WHEN 20 THEN 'B'
WHEN 30 THEN 'C'
WHEN 50 THEN 'D'
END 级别
FROM employees;
DML:数据操纵语言
insert、update、delete
插入数据
语法:
INSERT INTO beauty(字段1,字段2,...)
VALUES(值1,值2,...);
特点:
1、字段和值列表必须一一对应
2、字符型和日期型必须用单引号引起来
3、字段的顺序可以和表中字段的顺序不一致
4、不可以为NULL的字段,必须插入值,比如说主键id;
可以为NULL的字段,可以不用插入值,使用NULL来代替,或字段名和值都不写
5、字段和值的个数必须一致
6、字段列表可以省略,默认所有列 ,而且顺序和表中的列的顺序一致
插入一条数据
语法:
INSERT INTO beauty(字段1,字段2,...)
VALUES(值1,值2,...);
特点:
1、字段和值列表必须一一对应
2、字符型和日期型必须用单引号引起来
3、字段的顺序可以和表中字段的顺序不一致
4、不可以为NULL的字段,必须插入值,比如说主键id;
可以为NULL的字段,可以不用插入值,使用NULL来代替,或字段名和值都不写
5、字段和值的个数必须一致
6、字段列表可以省略,默认所有列 ,而且顺序和表中的列的顺序一致
案例1:传统的插入
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(100,'赵丽颖','女','1986-1-1','15811111111',NULL,10);
- 字段的插入顺序要和表属性的顺序一致
- 不可以为 null 的字段要手动填充 null 值
- 可以为 null 的字段可以不用手动填充
- 字段可以省略但可读性差
插入多条数据
方式一:
insert into...values
案例:
INSERT INTO beauty (id,NAME,phone,boyfriend_id)
VALUES(106,'古力娜扎','119',1),
(107,'范冰冰','112',1),
(108,'吉克隽逸','888',1);
方式二:
insert into...select
案例:
INSERT INTO beauty(NAME,phone)
SELECT '唐艺昕2','999' UNION
SELECT '李沁2','888' UNION
SELECT '董洁2','777'
方式三
连续插入一条数据一块运行
数据更新
语法:(更新一条数据)
UPDATE 表名 SET 字段名=新值,字段名=新值
[WHERE 条件];
案例1:将赵丽蓉更新为 关晓彤
UPDATE beauty SET NAME='关晓彤'
WHERE NAME='赵丽蓉';
案例2:将11号的女神,更改为名字是杨紫,生日1998-1-1
UPDATE beauty SET NAME = '杨紫',borndate='1998-1-1'
WHERE id=11;
语法(更新多表数据)
UPDATE 表1 别名1,表2 别名2
SET 字段1=新值,字段2=新值
WHERE 连接条件
AND 筛选条件;
案例:将张无忌的女朋友的电话都更改为88888
UPDATE beauty b,boys bo
SET phone='88888'
WHERE b.`boyfriend_id`=bo.`id`
AND bo.`boyName`='张无忌'
数据删除
删除的方式一:
语法
DELETE FROM 表 [WHERE 条件]
案例1:删除电话号码的长度小于11位的女神信息
DELETE FROM beauty
WHERE LENGTH(phone)<11;
案例2:将没有男朋友的女神删除
DELETE b FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL
删除的方式二:
语法:
TRUNCATE TABLE 表名
案例1:删除beauty表的记录
TRUNCATE TABLE beauty
二种删除方式的区别【面试题】 ☆☆☆☆☆
1、TRUNCATE不能加WHERE条件,而DELETE可以加WHERE
2、TRUNCATE效率较高
3、TRUNCATE不能删除多表,而DELETE可以
4、删除带标识列字段的表时,TRUNCATE 删除后再插入,标识列的值从1开始DELETE删除后再插入,标识列的值从断点处开始
5、TRUNCATE删除后,不能回滚;DELETE删除可以回滚
库和表的管理
DDL数据定义语言
关键词
create、alter、drop
库的操作:创建库(★)、删除库
表的操作:创建表(★)、修改表、删除表(★)、复制表
一、库的操作
1.创建库
CREATE DATABASE student;
CREATE DATABASE IF NOT EXISTS student;
2.删除库
DROP DATABASE student;
DROP DATABASE IF EXISTS student;
二、表的操作
1.创建表
案例:创建学员信息表
#学号、姓名、性别、邮箱、生日
CREATE TABLE stuinfo(
stuNo INT,#学号
stuName VARCHAR(20),#姓名
gender CHAR(1),#性别
email VARCHAR(50),#邮箱
borndate DATETIME #生日
)
2.修改表
①修改表名
ALTER TABLE stuinfo RENAME TO student;
ALTER TABLE student RENAME stuinfo;
②修改列名,不加类型 报错,需要加类型
ALTER TABLE stuinfo CHANGE COLUMN borndate birthday DATETIME;
③修改列的类型
ALTER TABLE stuinfo MODIFY COLUMN gender VARCHAR(2);
④添加新列
ALTER TABLE stuinfo ADD COLUMN phone VARCHAR(11);
⑤删除列
ALTER TABLE stuinfo DROP COLUMN phone;
3.删除表
drop table if exists stuinfo;
4.表的复制
①仅仅复制表的结构
CREATE TABLE newTable2 LIKE stuinfo;
②复制表的结构+数据
CREATE TABLE newTable3
SELECT * FROM stuinfo;
CREATE TABLE newTable4
SELECT stuno,stuname
FROM stuinfo WHERE stuno=1;
常见的数据类型概述
1.整型
整数类型 | 占字节数 | 范围 |
---|---|---|
Tinyint | 1 | -128~127 |
Smallint | 2 | 很大 |
Mediumint | 3 | 很大 |
Int、integer | 4 | 很大 |
Bigint | 8 | 很大 |
utf-8 英语 1个字节 中文是占 3个字节
2.小数
小数类型 | 占字节数 |
---|---|
float | 4 |
double | 8 |
定点数类型 | 字节 |
DEC/DECIMAL | M+2 |
3.位类型
Bit : 1~8字节
4.char和varchar类型
字符串类型 | 字符串类型 | 描述及需求 |
---|---|---|
char(M) | M | M为0~255之间的整数 |
varchar(M) | M | M为0~65535之间的整数 |
5、binary和varbinary类型
说明:类似于char和varchar,不同的是它们包含二进制字符串而不包含非二进制字符串。
6、Enum类型
7、Set类型
8、时间类型
日期和时间类型 | 字节 |
---|---|
date | 4 |
datetime | 8 |
timestamp | 4 |
time | 3 |
year | 1 |
9、datetime和timestamp的区别
(1)Timestamp支持的时间范围较小,取值范围
19700101080001——2038年的某个时间
Datetime的取值范围:1000-01-01 ——9999—12-31
(2)timestamp和实际时区有关,更能反映实际的日期,而datetime则只能反映出插入时的当地时区
(3)timestamp的属性受Mysql版本和SQLMode的影响很大
10.整型(详解)
特点
1.根据所占字节或保存的数值范围分类
tinyint、smallint、mediumint、int/integer、bigint
2.默认为有符号整型
3.如果超过类型的范围,会报警告,插入临界值(最大值或最小值)
4.如果想设置为无符号整型,则需要加上关键字unsigned
5.每个整型可以设置显示的宽度,int默认为11.但只能加上zerofill才能有效果
而且加上zerofill,默认为无符号整型
11.浮点型和定点型(详解)
原则:
1、选择能存储数值的越小类型越好
2、类型越简单越好
1.分类
浮点型:
float(M,D)
double(M,D)
定点型:
decimal(M,D)
2. D和M 参数
D表示小数位数,如果不够用0补齐,如果超过则四舍五入
M表示整数位数+小数位数,整数部分如果超过,则报错,显示为临界值
M和D 对于浮点型或定点型都可以省略
但浮点型默认的精度为插入数值的精度,而定点型默认的精度为M=10,D=0
3. 定点型和浮点型区别
定点型的精度表示更高,比较适合处理货币运算
12.字符型(详解)
1、char(M)和varchar(M):
M表示的是最多字符个数
char(M):字符个数可以省略,默认M=1
varchar(M):字符个数不可以省略
2、比较
所占的空间 效率 使用场景
char 固定的,有点浪费 高 一般存储字段的值的个数固定情况
varchar 可变的,根据值的长度 低 一般存储字段的值的个数变化较大
13.日期型(详解)
1.分类:
date:只保存日期不保存时间 。‘1998-1-1’
time:只保存时间不保存日期
year:只保存年
datetime:日期+时间
timestamp:日期+时间
2.datetime和timestamp的区别
字节 时间范围 是否受时区和版本等影响
datetime 8 1000——9999 不受
timestamp 4 1970-2038 受