目录
SQl---DDL 结构定义
创建、删除 数据库
代码
-- 创建数据库
CREATE DATABASE IF NOT EXISTS schooldb CHARSET utf8
-- 删除数据库
DROP DATABASE IF EXISTS schooldb 运行
-- mysql中数据库一旦创建,名字不能修改
-- 修改数据库字符集
ALTER DATABASE schooldb CHARSET utf8设计表
数据类型
char(n) 可变长度的字符串 n是最大长度 如果只存储2个字符,实际占有2个空间位置
varchar(n) 可变长度的字符串 n是最大长度 如果只存储2个字符,实际占有2个空间位置
data 日期，包含年，月，日
datetime 年月日，时分秒
整数
signed 有符号，默认为无符号
unsigned 无符号
浮点数
decimal（M，D）
M:精度，数据的总长度；
D:标度，小数点后的长度。
主键
在一张表中代表唯一的一条记录，不能为空且不能重复
约束
主键约束 PRIMARY KEY
不能为空约束 NOT NULL
唯一性约束 UNIQUE
主键自增长
主键自增长，设置主键自增长后，只能为整数类型
AUTO_INCREMENT
默认值
DEFAULT default_value
字段注释
comment'注释'
创建、删除 表
-- 创建表
-- 确定表名 学生信息表
-- 确定表中字段(列) 学号,姓名,性别,生日,电话,身高,注册时间
-- 确定字段(列)的数据类型 以及长度
-- 确定列的约束
-- 创建表语法
代码
CREATE TABLE student(
number INT,
NAME VARCHAR(6),
gender CHAR(1),
birthday DATE,
phone VARCHAR(11),
height DECIMAL(3,2),
reg_time DATETIME
)运行
-- 删除表结构，删除后表中数据就不存在了
代码
DROP TABLE student-- 创建表 并添加约束规则
-- PRIMARY KEY 设置主键约束 ,添加主键约束后，值不能重复,一个表中只能有一个列是主键
-- 主键列可以唯一
-- mysql中主键可以设置为自动增长 AUTO_INCREMENT
-- NOT NULL 不能为空约束
-- UNIQUE 唯一性约束
-- 检查约束 设置条件
代码
CREATE TABLE student(
number INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号主键',
NAME VARCHAR(6) NOT NULL COMMENT '姓名',
gender CHAR(1) NOT NULL COMMENT '性别',
birthday DATE NOT NULL,
phone VARCHAR(11) NOT NULL UNIQUE,
height DECIMAL(3,2) CHECK(height<3),
reg_time DATETIME
)运行
SQL---DML 数据操纵
常用语句：insert into，update，delete
插入数据
方式1: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n);
代码
INSERT INTO student(NAME,gender,birthday,phone,height,reg_time)VALUES('杜兰特','男','2019-6-8','12345678913',1.98,NOW())
INSERT INTO student(NAME,gender,birthday,phone,height,reg_time)VALUES('科比','男','2019-6-8','12345678912',1.98,NOW())运行
方式2: INSERT INTO 表名 set 列名1=值1,..列名n=值n;
代码
INSERT INTO student SET NAME='詹姆斯',gender='男',birthday='1986-2-3',phone='12345678911'
运行
方式3: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n),(值1,值2…..,值n);
代码
INSERT INTO student(NAME,gender,birthday,phone,height,reg_time)
VALUES('科比','男','1988-6-8','12345678914',1.88,NOW()),
('库里','男','1987-6-8','12345678915',2.12,NOW())运行
方式4:INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配。
代码
-- 复制表结构
CREATE TABLE stu LIKE student
INSERT INTO stu(number,NAME,gender,birthday,phone,height,reg_time)SELECT * FROM student运行
修改数据
-- 修改记录 修改时一定注意条件
UPDATE 表名 SET 列名 = ‘新值’WHERE 条件
代码
UPDATE stu SET NAME='c罗',height=1.70 WHERE number = 1运行
删除数据
DELETE FROM 表名 WHERE 条件
TRUNCATE TABLE 表名;清空整张表
代码
-- 删除
DELETE FROM student WHERE number = 4-- 修改表名
RENAME TABLE student TO stu
RENAME TABLE stu TO student
-- 复制表结构
CREATE TABLE stu LIKE student
运行
SQL---DQL 数据查询
-- select 结果 from 表名
基础查询
特定列查询，全部列查询
select column1,column2 from table
代码
-- 查询指定的列
SELECT NAME,gender,birthday,phone FROM student运行
select * from table
代码
-- 查询所有的列 *-表示所有的列 查询的结果是虚拟的表格，不能操作，是只读的，不能写入
SELECT * FROM student运行
算数运算符 + - * /
代码
-- 查询结果进行算术运算 + - * /
SELECT NAME,height+1 FROM student运行
排除重复行
select distinct column1,column2 from table
代码
-- 去除重复数据 查询结果中所有的列都相同，就视为重复数据
-- DISTINCT关键字去除重复数据
SELECT DISTINCT NAME,gender FROM student运行
条件查询
select 结果 from 表 where 条件 【group by 条件 order by 列 limit】
-- select * from 表名 where 条件
-- =
-- and 并且
-- >
-- or 多个条件只需要满足一个即可
-- !=
-- <>
代码
SELECT * FROM student WHERE gender = '男' AND height > 1.90
SELECT * FROM student WHERE gender = '男' OR height > 1.70
SELECT * FROM student WHERE gender != '男'
SELECT * FROM student WHERE gender <> '女运行
模糊查询
like
是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值 或数值型.
通配符： % 任意多个字符
代码
-- like '库%' 模糊匹配
SELECT * FROM student WHERE NAME LIKE '库%'运行
between and
两者之间,包含临界值；
代码
-- between 1.65 and 1.98 两者之间
SELECT * FROM student WHERE height>=1.65 AND height<=1.98
SELECT * FROM student WHERE height BETWEEN 1.65 AND 1.98 运行
in
判断某字段的值是否属于in列表中的某一项
代码
-- in(值1，值2，值n) 在给定的值中
SELECT * FROM student WHERE height =1.78 OR height =1.88 OR height =1.98
SELECT * FROM student WHERE height NOT IN(1.78,1.88,1.98)运行
is null /is not null
为空的/不为空的
代码
-- 查询身高为null的数据
SELECT * FROM student WHERE height IS NULL
SELECT * FROM student WHERE height IS NOT NULL运行
union
mysql 会把结果集中重复的记录删掉
代码
-- union 将多个查询的结果合并，合并时注意列数一致，会去除重复数据
SELECT NAME,gender FROM student WHERE gender = '男'
UNION
SELECT NAME,gender FROM student WHERE number>1 运行
union all
mysql 会把所有的记录返回，且效率高于union
代码
-- union all 只是将查询的多个结果合并，不会去除重复数据
SELECT NAME,gender FROM student WHERE gender = '男'
UNION ALL
SELECT NAME,gender FROM student WHERE number>1运行
排序
order by 排序列
asc代表的是升序，desc代表的是降序，如果不写，默认是升序
代码
-- order by 列 asc(升序) desc(降序)
SELECT * FROM student WHERE number>0 ORDER BY number ASC
SELECT * FROM student WHERE number>0 ORDER BY number DESC
SELECT * FROM student WHERE number>0 ORDER BY height DESC
SELECT * FROM student WHERE number>0 ORDER BY reg_time ASC
SELECT * FROM student WHERE number>0 ORDER BY height ASC,reg_time DESC运行
数量限制
limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)
代码
-- 数量限制 实现分页查询
-- limit 开始位置，每次查询的数量
SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 0,3
SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 3,3
SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 6,3运行
分组查询
-- 分组--在一些统计场景中，按照某一个条件进行区分（分组）
-- 统计时，按照性别，班级，年级，部门，年份，月份
-- 把满足某一个条件的数据分到同一个组中，每一个组内单独进行处理
select 分组函数，列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[having 分组后的筛选]
[order by 子句]
-- 查询男生，女生各有多少人 每个年级各有多少人 每个部门各有多少人
-- 分组统计 男生组统计一次，女生组统计一次
代码
SELECT COUNT(*),gender FROM student GROUP BY gender
SELECT MAX(height),gender FROM student GROUP BY gender
SELECT COUNT(*),DATE_FORMAT(reg_time,'%Y-%m') FROM student GROUP BY DATE_FORMAT(reg_time,'%Y-%m')运行
-- 统计男生女生各多少人
代码
SELECT COUNT(*),gender FROM student GROUP BY gender运行
代码
-- 男生和女生谁的人数大于2
-- having count(*)>2 having 条件 是对分组后的结果进行条件筛选
SELECT COUNT(*),gender FROM student GROUP BY gender HAVING COUNT(*)>2运行
函数 查询结果处理
-- 单行函数：会对查询中的每一行数据进行处理
-- 分组函数 聚合函数(查询多行，返回一行) 统计函数
单行函数
字符函数
-- length（列名） 以字节为单位
代码
SELECT LENGTH(NAME),NAME,gender FROM student运行
-- char_length（列名） 以字符为单位
代码
SELECT CHAR_LENGTH(NAME),NAME,gender FROM student运行
-- concat(str1,str2,str3) 连接多个字符串 as 别名
代码
SELECT CONCAT(NAME,":",CHAR_LENGTH(NAME)) AS NAME,gender FROM student运行
-- upper() 转大写 lower() 转小写
代码
SELECT UPPER(NAME),LOWER(NAME) FROM student运行
-- substring(字符串,开始位置,截取长度) 开始位置从1开始
代码
SELECT SUBSTRING(NAME,1,2) FROM student运行
-- instr(字符串，指定的字符)获取指定字符首次出现位置，找不到返回0
代码
SELECT INSTR(NAME,'o') FROM student运行
-- trim(字符串) 默认是去掉字符串前后空格的
代码
SELECT TRIM(NAME),NAME FROM student运行
-- trim(指定的子串 from 字符串) 可以去掉前后指定的子串
代码
SELECT TRIM('a' FROM NAME),NAME FROM student运行
-- 左填充 右填充到指定长度
代码
SELECT LPAD(NAME,5,'a'),RPAD(NAME,5,'b') FROM student运行
-- replace(列,'old','new')字符串的替换
代码
SELECT REPLACE(NAME,'o','O') FROM student运行
逻辑处理
-- case when 条件 then 条件成立结果 else 不成立结果 end;
代码
SELECT
NAME,
gender,
(CASE WHEN height>=2 THEN '高个子' ELSE '正常身高' END) AS height
FROM
studentSELECT
NAME,
gender,
(CASE WHEN height>=2 THEN 'A'
WHEN height>=1.8 THEN 'B'
ELSE 'C' END) AS height
FROM
student运行
-- ifnull(列名,'为空时默认信息')
代码
SELECT
NAME,
gender,
IFNULL(reg_time,'暂未录入') AS reg_time
FROM
student运行
-- if(条件,'结果1','结果2')
代码
SELECT
NAME,
gender,
IF(height>=2,"高个子","正常") AS height
FROM
student运行
数学函数
-- round(数值) 四舍五入
-- ceil(数值) 向上取整，返回>=该参数的最小整数
-- floor(数值) 向下取整，返回<=该参数的最大整数
代码
SELECT NAME,gender,ROUND(height),CEIL(1.1),FLOOR(1.9) FROM student运行
-- mod(被除数,除数) 取余，被除数为正，则为正；被除数为负，则为负
代码
SELECT height,number,MOD(height,number) FROM student运行
-- truncate(数值,保留小数的位数) 截断，小数点后截断到几位
-- rand() 获取随机数，返回0-1之间的小数
代码
SELECT NAME,gender,TRUNCATE(height,1),height,RAND() FROM student运行
日期函数
-- now() 年月日 时分秒 获取系统当前时间
-- curdate() 年月日
-- curtime() 时分秒
代码
SELECT NOW(),CURDATE(),CURTIME() FROM student
运行
-- year(日期) 将日期格式化为年
-- month(日期) 将日期格式化为月
代码
SELECT YEAR(birthday),MONTH(birthday) FROM student
SELECT * FROM student WHERE MONTH(birthday) = 6
SELECT COUNT(*),YEAR(birthday) FROM student GROUP BY YEAR(birthday)运行
-- str_to_date('2022-3-6','%Y-%m-%d') 将字符串日期格式化为日期类型
代码
SELECT STR_TO_DATE('2022-3-6','%Y-%m-%d') FROM student运行
-- date_format(birthday,'%Y-%m') 将日期格式化为指定的字符串
代码
SELECT DATE_FORMAT(birthday,'%Y-%m') FROM student
SELECT COUNT(*),DATE_FORMAT(reg_time,'%Y-%m') FROM student GROUP BY DATE_FORMAT(reg_time,'%Y-%m')运行
- datediff(CURDATE(),birthday) 计算两个日期之间相差的天数
代码
SELECT DATEDIFF(CURDATE(),birthday) FROM student运行
分组函数
-- sum() 求和
-- avg() 平均值
-- max() 最大值
-- min() 最小值
-- count() 统计个数
代码
SELECT SUM(height) FROM student
SELECT AVG(height) FROM student
SELECT MAX(height) FROM student
SELECT MIN(height) FROM student
SELECT COUNT(*) FROM student运行
