MySQL基础之数据管理与常用函数

2018-10-05 本文已影响0人磊_5d71

记录操作

添加记录

INSERT [INTO] tbl_name[(col_name,...)] {VALUE|VALUES}(VALUES...);
不指定字段名称 INSERT tbl_name VALUE(value...) 需要按照建表时的字段顺序给每一个字段赋值
一次插入多条记录 INSERT tbl_name[(字段名称,...)] VALUES(值,...),(值,....),(值,...)0
INSERT ... SET的形式 INSERT tbl_name SET 字段名称=值,...;
INSERT ... SELECT INSERT tbl_name[(字段名称...)] SELECT 字段名称,... FROM tbl_name [WHERE 条件]

修改记录

UPDATE tbl_name SET 字段名称 = 值，字段名称 = 值[WHERE条件] 如果不添加条件，整个表中的记录都会被更新

删除记录

DELETE FROM tbl_name[WHERE 条件] 如果不添加条件，所有记录都被删除
DELETE 清空数据表时不会重置AUTO_INCREMENT的值，可以通过ALTER语句将其设置为1 ALTER TABLE tbl_name AUTO_INCREMENT = 1;
彻底清空数据表 TRUNCATE [TABLE] tbl_name
1、清楚表中所有记录
2、重置AUTO_INCREMENT的值

查询记录

SELECT select_expr,... FROM tbl_name
[WHERE 条件]
[GROUP BY{col_name|position} HAVING 二次筛选]
[ORDER BY{col_name|postion|expr}[ASC|DESC]]
[LIMIT 限制结果集显示条数]

数据库名.表名查询 select * from mysql.user;
给字段起别名 SELECT 字段名称 [AS] 别名名称,... FROM db_name.tbl_name;
给数据表起别名 SELECT 字段名称 ,... FROM tbl_name [AS] 别名;

WHERE条件筛选记录

NULL 查询要使用<=>或者IS NULL SELECT * FROM user where userDesc<=>NULL;
指定范围 [NOT]BETWEEN AND
指定集合 [NOT] IN(值,...)
逻辑运算符 AND 逻辑与 OR 逻辑或
匹配字符 [NOT]LIKE 一般和通配符一起使用
1、% 任意长度的字符串
2、_ 任意一个字符

-- 用户名长度为三位的用户(三个下划线)
SELECT * FROM user WHERE username like '___';

GROUP BY 分组

把值相同放到一个组中，最终查询出的结果只会显示组中的一条记录
分组配合GROUP_CONCAT()查看组中的某个字段的详细信息

-- 按性别分组，查看组中的用户名有哪些
SELECT GROUP_CONCAT(username),sex FROM user1 
GROUP BY sex;

配合聚合函数使用
1、COUNT() 其中COUNT(*)统计时候包括NULL COUNT(id) 统计时候不包括NULL的条数
2、SUM()
3、MAX()
4、MIN()
5、AVG()
配合WITH ROLLUP关键字使用显示总信息
HAVING子句对分组结果进行二次筛选

-- 测试查询操作
CREATE TABLE user1(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号',
age TINYINT UNSIGNED NOT NULL DEFAULT 18  COMMENT '年龄',
sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
addr VARCHAR(20) NOT NULL DEFAULT '北京',
married TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未结婚，1代表已婚',
salary FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
)ENGINE=INNODB CHARSET=UTF8;
INSERT user1 VALUES(1,'king',23,'男','北京',1,50000);
INSERT user1(username,age,sex,addr,married,salary) VALUES('queen',27,'女','上海',0,25000);
INSERT user1 SET username='imooc',age=31,sex='女',addr='北京',salary=40000;
INSERT user1 VALUES(NULL,'张三',38,'男','上海',0,15000),
(NULL,'张三风',38,'男','上海',0,15000),
(NULL,'张子怡',39,'女','北京',1,85000),
(NULL,'汪峰',42,'男','深圳',1,95000),
(NULL,'刘德华',58,'男','广州',0,115000),
(NULL,'吴亦凡',28,'男','北京',0,75000),
(NULL,'奶茶妹',18,'女','北京',1,65000),
(NULL,'刘嘉玲',36,'女','广州',0,15000);

-- GROUP BY
SELECT * FROM user1 GROUP BY sex;

-- 按性别分组，查看组中的用户名有哪些
SELECT GROUP_CONCAT(username),sex FROM user1 
GROUP BY sex;

-- 按照sex分组，得到用户名详情，并且显示分组中的总人数
SELECT sex,GROUP_CONCAT(username),count(*) AS totalUsers  FROM user1  GROUP BY sex;

-- 按照addr分组，得到用户名的详情，总人数，得到组中年龄的总和，年龄最大值、最小值、平均值
SELECT addr,
GROUP_CONCAT(username) AS userDetail,
COUNT(*) AS totalUser,
SUM(age),
MAX(age),
MIN(age),
AVG(age)
FROM user1
GROUP BY addr;

-- WITH ROLLUP后面跟总条数
SELECT GROUP_CONCAT(username),
COUNT(*)
FROM user1
GROUP BY sex
WITH ROLLUP;

-- 按照字段位置进行分组
SELECT addr,
GROUP_CONCAT(username) AS userDetail,
COUNT(*) AS totalUser,
SUM(age),
MAX(age),
MIN(age),
AVG(age)
FROM user1
GROUP BY 1;

-- 按照addr分组，统计总人数
SELECT COUNT(*),addr,
GROUP_CONCAT(username)
FROM user1
GROUP BY addr;

-- 对于分组结果进行二次筛选，条件是组中总人数>=3
SELECT COUNT(*),addr,
GROUP_CONCAT(username)
FROM user1
GROUP BY addr 
HAVING COUNT(*) >=3;

ORDER BY 实现排序效果

-- 按照多个字段排序
SELECT id,username,age
FROM user1
ORDER BY age ASC, id ASC;

-- 实现随机记录
SELECT id,username,age
FROM user1
ORDER BY RAND();

LIMIT限制结果集的显示条数

LIMIT 值：显示结果集的前几条记录
LIMIT offset,row_count 从offset开始，显示几条记录

-- 显示前3条记录
SELECT id,username,age
FROM user1
LIMIT 0,3;

-- 显示接下来的3条
SELECT id,username,age
FROM user1
LIMIT 3,3;

高级操作

多表联查

笛卡尔积的形式
内连接的形式
SELECT 字段名称,... FROM tbl_name1
INNER JOIN tbl_name2
ON 连接条件
外连接的形式
1、左外连接：先显示左表中的全部记录，再去右表中查询复合条件的记录，不符合的以NULL代替
SELECT 字段名称,... FROM tbl_name1
LEFT [OUTER] JOIN tbl_name2
ON 条件;
2、右外连接：先显示右表中的全部记录，再去左表中查询复合条件的记录，不符合的以NULL代替
SELECT 字段名称,... FROM tbl_name1
RIGHT [OUTER] JOIN tbl_name2
ON 条件;

CREATE DATABASE IF NOT EXISTS test2 DEFAULT CHARACTER SET 'UTF8';
USE test2;
CREATE TABLE emp(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号',
age TINYINT UNSIGNED NOT NULL DEFAULT 18  COMMENT '年龄',
sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
addr VARCHAR(20) NOT NULL DEFAULT '北京',
depId TINYINT UNSIGNED NOT NULL COMMENT '部门对应的编号'
)ENGINE=INNODB CHARSET=UTF8;

INSERT emp(username,age,depId) VALUES('king',24,1),
('queen',25,2),
('imooc',26,1),
('lily',27,1),
('rose',28,3),
('john',29,3);

INSERT emp(username,age,depId)  VALUES('测试用户',39,6);

CREATE TABLE dep(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(50) NOT NULL UNIQUE,
depDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE=INNODB CHARSET=UTF8;
INSERT dep(depName,depDesc) VALUES('PHP教学部','研发PHP课件'),
('JAVA教学部','研发JAVA课件'),
('WEB前端教学部','研发WEB前端课件'),
('IOS教学部','研发IOS课件');


-- 查询 emp id username age  部门名称 dep depName

SELECT emp.id,emp.username,emp.age,dep.depName FROM emp,dep;

SELECT e.id,e.username,e.age,d.depName
FROM emp AS e
INNER JOIN dep AS d
ON e.depId=d.id;

-- 查询emp id username age addr dep id depName depDesc
SELECT e.id,e.username,e.age,e.addr,
d.id,d.depName,d.depDesc
FROM dep AS d
JOIN emp AS e
ON d.id=e.depId;

-- 测试左外连接
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
LEFT OUTER JOIN dep AS d
ON e.depId=d.id;

-- 测试右外连接
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
RIGHT JOIN dep AS d
ON e.depId=d.id;

外键约束的使用

只有innodb存储引擎支持外键
[CONSTRAINT 外键名称 ]FOREIGN KEY(字段名称) REFERENCES 主表(字段名称)
子表的外键字段和主表的主键字段类型要相似；如果是数值型要求一致，并且无符号也要一致；如果是字符型，要求类型一致，长度可以不同
如果外键字段没有创建索引，MySQL会自动帮我们添加索引
子表的外键关联的必须是父表的主键
外键约束的参照操作
1、CASCADE 从父表删除或更新，子表也跟着删除或者更新，级联的操作
2、SET NULL 从父表删除或者更新记录，并设置子表的外键列为NULL。
3、NO ACTION | RESTRICT 拒绝对父表做更新或者删除操作

-- 指定级联操作 DELETE CASCADE UPDATE CASCADE 更新父表相关子表也会更新
ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id)
ON DELETE CASCADE ON UPDATE CASCADE;

-- 添加外键名称

-- 新闻分类表 news_cate
CREATE TABLE news_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE=INNODB;

-- 新闻表 news
CREATE TABLE news(
id INT UNSIGNED AUTO_INCREMENT KEY,
title VARCHAR(100) NOT NULL UNIQUE,
content VARCHAR(1000) NOT NULL,
cateId TINYINT UNSIGNED NOT NULL,
FOREIGN KEY(cateId) REFERENCES news_cate(id)
)ENGINE=INNODB;

-- SHOW CREATE TABEL news,自动创建了cateId的索引
| news  | CREATE TABLE `news` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `content` varchar(1000) NOT NULL,
  `cateId` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  KEY `cateId` (`cateId`),
  CONSTRAINT `news_ibfk_1` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

-- 测试cateID外键对应的id不存在，是否可以插入
INSERT INTO news(title,content,cateId) VALUES('A6','SSAW33',45);

-- 测试删除父表中的记录，和删除父表
DELETE FROM news_cate WHERE id =1;

动态创建和删除外键

-- 动态删除外键 ALTER TABLE tbl_name DROP FOREIGN KEY fk_name;
-- 动态添加外键
1、 ALTER TABLE tbl_name
[CONSTRAINT 外键名称] ADD FOREIGN KEY(外键字段) REFERENCES 主表(主键字段);
2、动态添加外键之前表中的记录一定合法的记录，没有脏值，否则外键添加不成功

-- 动态删除外键,删除了外键，当初创建外键自动产生的索引还在
ALTER TABLE news
DROP FOREIGN KEY news_ibfk_1;

-- 添加外键
ALTER TABLE news
ADD FOREIGN KEY (cateId) REFERENCES news_cate(id);

子查询

SELECT 字段名称 FROM tbl_name WHERE col_name=(SELECT col_name FROM tbl_name)
内层语句查询的结果可以做为外层语句查询的条件
由IN引发的子查询
由比较运算符引出的子查询
由EXISTS引发的子查询
复制表CREATE TABLE tbl_name LIKE tbl_name;

-- 测试子查询

-- 测试由IN引发的子查询
SELECT *  FROM emp where depId IN(SELECT id from dep);

SELECT *  FROM emp where depId NOT IN(SELECT id from dep);

-- 学员stu
CREATE TABLE stu(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
score TINYINT UNSIGNED NOT NULL
);
INSERT stu(username,score) VALUES('king',95),
('queen',75),
('zhangsan',69),
('lisi',78),
('wangwu',87),
('zhaoliu',88),
('tianqi',98),
('ceshi',99),
('tiancai',50);

-- 分数级别level
CREATE TABLE level(
id tinyint UNSIGNED AUTO_INCREMENT KEY,
score TINYINT UNSIGNED COMMENT '分数'
);
INSERT level(score) VALUES(90),(80),(70);

-- 由比较运算符引出的子查询
-- 查询出成绩优秀的学员
SELECT * FROM stu WHERE score >= (SELECT score FROM level where id =1);

-- 由EXISTS引发的子查询
SELECT * FROM emp WHERE EXISTS(SELECT depName FROM dep WHERE id = 10);

ANY SOME ALL

图片.png

-- 带有ANY SOME ALL关键字的子查询
SELECT * FROM stu 
WHERE score >= ANY(SELECT score FROM level);

SELECT * FROM stu 
WHERE score >= SOME(SELECT score FROM level);

SELECT * FROM stu 
WHERE score >= ALL(SELECT score FROM level);

联合查询的使用

UNION 重复部分会合并到一起
SELECT 字段名称,... FROM tbl_name1
UNION
SELECT 字段名称... FROM tbl_name2;
UNION ALL 简单的合并两张表的所有数据
SELECT 字段名称,... FROM tbl_name1
UNION ALL
SELECT 字段名称... FROM tbl_name2;

-- 创建一个user1表，id username
CREATE TABLE user1(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20)
)SELECT id,username FROM emp;

--  将user表中的用户名写入到user1表中
INSERT user1(username) SELECT username FROM user;

-- 去掉字段重复值
SELECT DISTINCT(username) FROM user2;

无限级分类数据表的设计与实现

自身连接查询

-- 测试自身连接
CREATE TABLE cate(
    id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
    cateName VARCHAR(100) NOT NULL UNIQUE,
    pId SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
INSERT cate(cateName,pId) VALUES('服装',0);
INSERT cate(cateName,pId) VALUES('数码',0);
INSERT cate(cateName,pId) VALUES('箱包',0);

INSERT cate(cateName,pId) VALUES('男装',1);
INSERT cate(cateName,pId) VALUES('女装',1);
INSERT cate(cateName,pId) VALUES('内衣',1);

INSERT cate(cateName,pId) VALUES('电视',2);
INSERT cate(cateName,pId) VALUES('冰箱',2);
INSERT cate(cateName,pId) VALUES('洗衣机',2);

INSERT cate(cateName,pId) VALUES('爱马仕',3);
INSERT cate(cateName,pId) VALUES('LV',3);
INSERT cate(cateName,pId) VALUES('GUCCI',3);

INSERT cate(cateName,pId) VALUES('夹克',4);
INSERT cate(cateName,pId) VALUES('衬衫',4);
INSERT cate(cateName,pId) VALUES('裤子',4);

INSERT cate(cateName,pId) VALUES('液晶电视',10);
INSERT cate(cateName,pId) VALUES('等离子电视',10);
INSERT cate(cateName,pId) VALUES('背投电视',10);

-- 查询所有的分类信息，并且得到其父分类
SELECT s.id ,s.cateName AS sCateName,p.cateName AS pCateName
FROM cate AS s
LEFT JOIN cate AS p
ON s.pId = p.id;

-- 查询所有分类及其子分类
SELECT p.id ,p.cateName AS pCateName,s.cateName AS sCateName
FROM cate AS s
LEFT JOIN cate AS p
ON s.pId = p.id;



sCate 表
id | cateName        | pId |
+----+-----------------+-----+
|  1 | 服装            |   0 |
|  2 | 数码            |   0 |
|  3 | 玩具            |   0 |
|  4 | 男装            |   1 |
|  5 | 女装            |   1 |
|  6 | 内衣            |   1 |
| 10 | 电视            |   2 |
| 11 | 冰箱            |   2 |
| 12 | 洗衣机          |   2 |
| 13 | 爱马仕          |   3 |
| 14 | LV              |   3 |
| 15 | GUCCI           |   3 |
| 16 | 夹克            |   4 |
| 17 | 衬衫            |   4 |
| 18 | 裤子            |   4 |
| 19 | 液晶电视        |  10 |
| 20 | 等离子电视      |  10 |
| 21 | 背投电视        |  10

pCate
id | cateName        | pId |
+----+-----------------+-----+
|  1 | 服装            |   0 |
|  2 | 数码            |   0 |
|  3 | 玩具            |   0 |
|  4 | 男装            |   1 |
|  5 | 女装            |   1 |
|  6 | 内衣            |   1 |
| 10 | 电视            |   2 |
| 11 | 冰箱            |   2 |
| 12 | 洗衣机          |   2 |
| 13 | 爱马仕          |   3 |
| 14 | LV              |   3 |
| 15 | GUCCI           |   3 |
| 16 | 夹克            |   4 |
| 17 | 衬衫            |   4 |
| 18 | 裤子            |   4 |
| 19 | 液晶电视        |  10 |
| 20 | 等离子电视      |  10 |
| 21 | 背投电视        |  10

MySQL常用函数

数学函数的使用

CEIL() 进一取整
FLOOR() 舍掉小数部分
ROUND() 四舍五入
TRUNCATE() 截取小数点后几位
MOD() 取余数
ABS() 取绝对值
POWER() 幂运算
PI() 圆周率
RAND()或者RAND(X) 0~1之间的随机数
SIGN(X) 得到数字符号
EXP(X) 计算e的x次方

字符串函数

CHAR_LENGTH(); 得到字符串的字符数
LENGTH(); 得到字符串的长度
CONCAT(s1,s2,....); 将字符串合并为一个字符串,包含null返回结果就是null
CONCAT_WS(x,s1,s2,....); 以指定分隔符拼接字符串
REVERSE() 字符串反转
LEFT()|RIGHT(); 返回字符串的前几个字符或者后几个字符
LPAD()|RPAD();用字符串填充到指定长度
TRIM()|LTRIM()|RTRIM() 去掉字符串两端的空格
REPEAT():重复指定的次数
REPLACE():字符串
SUBSTRING();截取字符串SUBSTRING 起始位置从1开始
STRCMP();比较字符串，按照ASCII

-- 测试字符串函数

-- CHAR_LENGTH(); 得到字符串的字符数
SELECT CHAR_LENGTH('SAHHIA');

-- 以下汉字显示3个字符
SELECT CHAR_LENGTH('你好啊');

-- LENGTH(); 得到字符串的长度，一个中文在utf-8占3位
SELECT LENGTH('你好啊');

-- CONCAT(s1,s2,....); 将字符串合并为一个字符串
SELECT CONCAT('A','B','C');

-- 有null的情况结果就为null
SELECT CONCAT('A','B','C',null);

-- CONCAT_WS(x,s1,s2,....); 以指定分隔符拼接字符串
SELECT CONCAT_WS('-','A','B','C');
SELECT CONCAT_WS('-','A','B','C',NULL);
SELECT CONCAT_WS(NULL,'A','B','C');

-- REVERSE() 字符串反转
SELECT REVERSE('ABCDEFG');

--  LEFT()|RIGHT(); 返回字符串的前几个字符或者后几个字符
SELECT LEFT('HELLO',2),RIGHT('HELLO',2);

-- LPAD()|RPAD();用字符串填充到指定长度
SELECT LPAD('ABC',10,'?');
SELECT RPAD('ABC',10,'!');

-- 去掉字符串两端的空格TRIM()|LTRIM()|RTRIM():

SELECT CONCAT('*',TRIM(' abc '),'*'),CONCAT('*',LTRIM(' abc '),'*'),CONCAT('*',RTRIM(' abc '),'*');

-- REPEAT():重复指定的次数
SELECT REPEAT('HELLO',4);

-- REPLACE():字符串
SELECT REPLACE('HELLO KING','KING','QUEEN');

-- SUBSTRING();截取字符串SUBSTRING 从1开始截取
SELECT SUBSTRING('ABCDEF',1,4);

-- STRCMP();比较字符串
SELECT STRCMP('A','B');

日期时间常用函数

返回当前日期
SELECT CURDATE() ,CURRENT_DATE();
返回当前时间
SELECT CURTIME(),CURRENT_TIME();
返回当前日期时间
SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE();
返回月份、月份名称
SELECT MONTH(CURDATE()),MONTHNAME(CURDATE());
返回星期几
SELECT DAYNAME(NOW());
返回一周内的第几天
SELECT DAYOFWEEK(NOW());
返回一年内的第多少个星期
SELECT WEEK(NOW());
返回年月日、小时、分钟、秒
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
DATEDIFF()计算两个日期相差的天数
SELECT DATEDIFF('2018-10-06','2018-03-02');

其他常用函数

返回数据库版本
SELECT VERSION();
返回连接数
SELECT CONNECTION_ID();
返回当前连接的数据库
SELECT DATABASE();
返回当前用户
SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER();
得到上一步插入产生AUTO_INCREMENT的值
SELECT LAST_INSERT_ID();
MD5加密
SELECT MD5('king');
password密码加密算法
SELECT PASSWORD('root');