MySql--SQL语言

目录

SQl---DDL  结构定义

创建、删除 数据库

代码

 运行

设计表

数据类型

整数

浮点数

主键

约束

主键自增长

默认值

字段注释

创建、删除 表

代码

运行

代码

代码

运行 

SQL---DML  数据操纵

插入数据

代码 

运行 

代码 

运行 

代码 

运行 

代码 

运行

修改数据

代码 

运行 

删除数据

代码  

运行

SQL---DQL  数据查询

基础查询

特定列查询，全部列查询

代码  

运行

代码  

运行 

算数运算符 + - * /

代码  

运行 

排除重复行

代码  

运行 

条件查询

代码  

运行 

模糊查询

like

代码  

运行 

between and 

代码  

运行 

in

代码  

运行 

is null /is not null

代码  

运行 

union 

代码  

运行 

union all

代码  

运行 

排序

代码  

运行 

数量限制

代码  

运行 

分组查询

代码  

运行

代码  

运行

代码  

运行 

函数  查询结果处理

单行函数 

字符函数

代码 

运行

代码 

运行

代码 

运行

代码 

运行

代码 

运行

代码 

运行

代码 

运行

代码 

运行

代码 

运行

代码 

运行 

逻辑处理

代码  

运行

代码  

运行

代码  

运行 

数学函数

代码  

运行

代码  

运行

代码  

运行 

日期函数 

代码  

运行

代码  

运行

​编辑

代码  

运行

代码  

运行

​编辑

代码   

运行 

分组函数

代码  

运行

---

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;

代码  

 SELECTNAME,gender,(CASE WHEN height>=2 THEN '高个子' ELSE '正常身高' END) AS height
FROMstudent

SELECTNAME,gender,(CASE WHEN height>=2 THEN 'A'WHEN height>=1.8 THEN 'B'ELSE 'C' END) AS height
FROMstudent

运行

-- ifnull(列名,'为空时默认信息')

代码  

SELECTNAME,gender,IFNULL(reg_time,'暂未录入') AS reg_time
FROMstudent

运行

-- if(条件,'结果1','结果2')

代码  

SELECTNAME,gender,IF(height>=2,"高个子","正常") AS height   
FROMstudent

运行 

数学函数

-- 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

运行