MySQL---基础_mysql的stuphone-CSDN博客

本文链接：https://blog.csdn.net/qq_42727102/article/details/96995615
###MySQL
分类：
	DDL：数据定义语言，用来定义数据库对象：数据库，表，列等。关键字：create、drop、alter
	DML：数据操作语言，用来对数据库中表的数据进行增删改。关键字：insert、delete、update等
	DQL：数据查询语言，用来查询数据库中表的记录（数据）。关键字：select，where等
	DCL：数据库控制语言，用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT，REVOKE等
#1.数据库相关
#2.表相关
#3.事务相关
#4.DML操作
#5.块、函数、过程、触发器、视图等高级部分

###################################################################################################

#MySQL:命名规则和规范：字母、数字、_、和$，数字不能在首位，尽量不要使用关键字和$，不区分大小写

#1.数据库相关
#如何创建一个数据库
CREATE DATABASE 数据库名字;
CREATE DATABASE db1;
#如何删除一个数据库
DROP DATABASE 数据库名字;

#2.如何创建一张表
CREATE TABLE 表名
(
 列名 数据类型 [约束],
 列名 数据类型 [约束],
 ...
 列名 数据类型 [约束]
);
#列名：符合MySQL命名规则规范，一般来说MySQL起名字使用_隔开单词
#java：userAge mysql：user_age
#常见类型：
#数值类型：int(整型)、float（小数）、decimal（数值型）
#字符型：char（长度）、varchar（长度）、longtext（长文本）
#日期型：date，time，datetime
#约束：主键约束、外键约束、非空约束、唯一约束、默认值约束和check约束（MySQL不支持）

CREATE TABLE stuinfo
(
	stuid INT PRIMARY KEY, #[auto_increment]:自增（int）#一个表只能有一个主键，作用：标识唯一   主键不允许为空  
	stuname VARCHAR(20) NOT NULL,#varchar（）变长字符串 实际长度是实际放的
	stusex CHAR(1) CHECK(stusex='男' OR stusex='女'),#char（）定长字符串  没放满后面补空格
	stuage DECIMAL(3,0),#3位有效数字，小数点后0位
	stuaddress VARCHAR(30) DEFAULT'陕西省西安市',#mysql5版本 不加（）   8版本DEFAULT（'陕西省西安市'）
	stuphone VARCHAR(15) UNIQUE,#unique允许放null值，可以放多个
	stubri DATE,
	studesc LONGTEXT
);

#删除表
DROP TABLE 表名;
DROP TABLE stuinfo;

#修改表
ALTER TABLE 表名 ADD 列名 类型 [约束];#添加一列
ALTER TABLE 表名 DROP 列名;#删除一列
ALTER TABLE 表名 MODIFY 列名 类型;#修改列的数据类型
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型[约束];#修改列
ALTER TABLE stuinfo DROP studesc;
ALTER TABLE stuinfo ADD studesc LONGTEXT ;

#DDL:create，alter，drop  数据定义语言  操纵的都是结构
#DML：insert，update，delete，select  数据操纵语言 操纵的是数据

##DML：insert、update、delete和select
#1.如何插入一行数据
INSERT [INTO] 表名[(列字段)] VALUES(值列表);
INSERT INTO stuinfo(stuid,stuname,stusex,stuage,stuaddress,stuphone,stubri,studesc)
VALUES(1,'张三','男',23,'陕西省宝鸡市','110','1999-3-3','哦买噶');#日期格式必须这样'1999-3-3'书写
#有自增：1.INSERT INTO stuinfo(stuid,stuname) VALUES(NULL,'张三'); 接着自增
#2.INSERT INTO stuinfo(stuname) VALUES('张三');
#3.INSERT INTO stuinfo(stuid,stuname) VALUES(7,'张三'); 自己输入stuid，接下来自增从7开始

##单行插入的注意事项
#1.如果书写了列字段，列字段顺序可以和物理顺序不一致，那么值列表按照列字段顺序赋值
INSERT INTO stuinfo(stuid,stusex,stuname,stuage,stuaddress,stuphone,stubri,studesc)
VALUES(2,'男','李四',23,'陕西省宝鸡市','111','1999-3-3','哦买噶的')
#2.如果未书写列字段，必须按照物理顺序依次输入值列表，不能省略内容，否则报错
INSERT INTO stuinfo
VALUES(3,'王麻子','男',24,'陕西省宝鸡市','112','1999-3-3','哦买噶啊');
#3.如果插入数据时，值想插入部分列内容，必须保证未赋值列允许为NULL或者有默认值
INSERT INTO stuinfo(stuid,stusex) VALUES(3,'女');#报错
INSERT INTO stuinfo(stuid,stuname) VALUES(4,'张五');#没报错
INSERT INTO stuinfo(stuid,stuname,stuaddress) VALUES(5,'bb',DEFAULT);#没报错

#2.如何修改表数据
UPDATE 表名 SET 列=值,列=值,...,列=值,[WHERE 条件];
UPDATE stuinfo SET stusex='男';#将所有性别改成了‘男’
UPDATE stuinfo SET stuage=19 WHERE stuid=3;#将stuid=3的这一行的stuage改成了19
UPDATE stuinfo SET stuaddress='陕西省宝鸡市',stuphone='116' WHERE stuid=5;
 
#3.如何删除表数据
DELETE FROM 表名 [WHERE 条件];
TRUNCATE table  表名;

DELETE FROM stuinfo WHERE stuid=5;
DELETE FROM stuinfo WHERE stuid=5 OR stuid=6;
DELETE FROM stuinfo;#全表删除
TRUNCATE TABLE stuinfo;#全表删除

##truncate、delete和drop之间的区别？
#1.drop删除的是结构，而truncate和delete删除的是数据
#2.delete可以全删除，也可以部分删除；而truncate只能全删除
#3.delete删除时会写日志，所以能恢复，但是删除慢；而truncate删除时不写日志，所以不能恢复，删除快。

#4.查询表信息
#4.1全表查询
SELECT * FROM 表名;#查询所有行和列  *代表显示所有列，顺序为物理顺序
SELECT * FROM userinfo;
#4.2筛选部分列
SELECT 待显示列 FROM 表名;
SELECT username,usersex FROM userinfo;
#4.3筛选部分行
SELECT * FROM userinfo WHERE 筛选条件;
# =等号 <> !=不等号 > >= < <= and or not !
SELECT * FROM userinfo WHERE userage>28;

#4.4范围查询
SELECT * FROM userinfo WHERE userage BETWEEN 18 AND 40;
SELECT * FROM userinfo WHERE useraddress IN('北京','重庆','西安');#等同于or
SELECT * FROM userinfo WHERE useraddress NOT IN('北京','重庆','西安');
#4.5常量查询
SELECT 8;
SELECT 15,'a';
SELECT *,8 FROM userinfo;
SELECT userid,username,userage+10 FROM userinfo;#所有人年龄+10
#4.6别名查询 重命名查询
SELECT userid AS '编号',userage AS '姓名' FROM userinfo u;
#4.7模糊查询 like 、not like、%
SELECT * FROM userinfo WHERE username LIKE '%张%';#只要名字中有张字
SELECT * FROM userinfo WHERE username LIKE '%成';#名字中最后一个是成字
SELECT * FROM userinfo WHERE username LIKE '张%';#名字中第一个是张字
#4.8加密查询
SELECT MD5('aaaaa');
#4.9拼接行查询  union(拼接，忽略重复行)、union all（拼接，不忽略重复行）
SELECT 8,'a'
UNION
SELECT 8,'a'
UNION
SELECT 9,'b';
#out:8 a
#    9 b

SELECT 8,'a'
UNION ALL
SELECT 8,'a'
UNION ALL
SELECT 9,'b';
#out:8 a
#    8 a
#    9 b
#4.10排序查询 order by，desc（降序）、asc（默认升序）
SELECT * FROM userinfo ORDER BY userage ASC;
SELECT * FROM userinfo ORDER BY userage DESC;
SELECT * FROM userinfo WHERE usersex='男' ORDER BY userage;#先where后order by 即先筛选再排序
SELECT * FROM userinfo ORDER BY userage,userid DESC;#先按userage，遇见相等按userid排序，后面紧跟其排序原则，不写默认asc
#4.11唯一查询
SELECT DISTINCT useraddress FROM userinfo;
#4.12为空查询
SELECT * FROM userinfo WHERE useraddress=NULL;#错误
SELECT * FROM userinfo WHERE useraddress IS NULL;
SELECT * FROM userinfo WHERE useraddress IS NOT NULL;
#4.13部分行查询（分页查询）
SELECT * FROM userinfo LIMIT 0,5;#limit 从第几个开始(不包括当前数据)，显示多少个
page：当前页码
size：每页显示多少条
SELECT * FROM userinfo LIMIT (page-1)*size,size;
#4.14分组（聚合）函数查询
SELECT MAX(userage),MIN(userage),SUM(userage),AVG(userage),COUNT(userage),COUNT(useraddress) FROM userinfo;#count会忽略null(空)行，sum和avg只能是数值类型
#4.15分组查询 
SELECT useraddress FROM userinfo GROUP BY useraddress;
SELECT useraddress,COUNT(*) FROM userinfo GROUP BY useraddress; 
#分组统计年龄大于38岁的每个城市的人数
SELECT userAddress,COUNT(*) FROM userinfo WHERE userage>38 GROUP BY useraddress;
#找出有哪些城市有3人以上都是38岁的
SELECT useraddress,COUNT(*) FROM userinfo WHERE userage>38 GROUP BY useraddress HAVING COUNT(userid)>3;#where分组之前筛选，having分组之后筛选  where-->group by-->having
/*
注意：
1.如果使用了分组查询，那么select和from之间只能显示分组字段和分组（聚合）函数
2.分组是在筛选之后进行的 即先where后group by
*/
#4.16表连接查询
#1.内连接
SELECT 待显示列字段 FROM 表A a INNER JOIN 表B b ON 连接条件 [WHERE 筛选条件];
SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;#将两张表连接起来

SELECT * FROM booksinfo;
SELECT * FROM authorsinfo;
SELECT * FROM bookauthor;
SELECT * FROM booksinfo b INNER JOIN bookauthor ba ON b.book_id=ba.book_id
INNER JOIN authorsinfo a ON ba.authors_id=a.author_id;
#简写
SELECT booksinfo b,bookauthor ba,authorsinfo a WHERE ba.authors_id=a.author_id AND b.book_id=ba.book_id;
#2.左外连接  以左表为主，右表为辅的一种连接方式，如果左表中的数据在右表中没有对应显示，会用NULL补全
SELECT 待显示列字段 FROM 表A a LEFT [OUTER] JOIN 表B b ON 连接条件 [WHERE 筛选条件];
SELECT * FROM emp e LEFT JOIN dept d ON e.`deptno`=d.`deptno`;
#4.17子查询：一个查询的结果是另一个查询的条件，这个查询就是子查询，外边的查询就是父查询
#查询年龄比‘折为成’年龄大的人
SELECT * FROM userinfo WHERE userage>(SELECT userage FROM userinfo WHERE username='折为成');
#查询性别和‘张虎豹’一样的人
SELECT * FROM userinfo WHERE usersex=(SELECT usersex FROM userinfo WHERE username='张虎豹');
#查询地址和‘马果树’，‘杨平’一样的人
SELECT * FROM userinfo WHERE useraddress=(SELECT useraddress FROM userinfo WHERE username='马果树' OR username='杨平');#错误
SELECT * FROM userinfo WHERE useraddress IN(SELECT useraddress FROM userinfo WHERE username='马果树' OR username='杨平');
SELECT * FROM userinfo WHERE useraddress =ANY(SELECT useraddress FROM userinfo WHERE username='马果树' OR username='杨平');
/*
1.如果查询了多行内容，不能使用关系（> >=...）运算符，需要使用范围运算
2.子查询的结果不能有多列，只能返回一列
3.返回结果类型和条件类型一致
*/
#4.18 any和all
SELECT * FROM userinfo WHERE userage>ANY(18,19,28,29);#大于任何一个 即18
SELECT * FROM userinfo WHERE userage>ALL(18,19,28,29);#大于所有的 即29 
#4.19 函数查询
ABS(X) 返回x的绝对值
SELECT ABS(3),ABS(-3);
BIN(X) 返回x的二进制（OCT返回八进制，HEX返回十六进制）
SELECT BIN(4),OCT(9),HEX(4);
SELECT CEILING(5.4),CEILING(5.6),FLOOR(5.4),FLOOR(5.6),ROUND(5.4),ROUND(5.6);
GREATEST(x1,x2,x3...); 返回集合中的最大值
LEAST(x1,x2,x3...);返回集合中的最小值
SELECT GREATEST(5,6,7,8),LEAST(5,6,7,8);
SELECT MOD(6,4);#取余
SELECT RAND(),FLOOR(RAND()*100+1);#随机数

SELECT BIT_LENGTH('a啊bc的'),CHAR_LENGTH('a啊bc的'),LENGTH('a啊bc的');#位 字符 字节
SELECT CONCAT('a','b','c','d'),CONCAT_WS(',','a','b','c','d');#concat_ws以‘,’隔开
SELECT REPLACE('abcdea','a','$'),INSERT('afudsjfhd',2,3,'55555');#两种替换 下标从1开始
SELECT SUBSTR('asfsafasf',3,5);
SELECT INSTR('sfsdgsg','s');#s在字符串中第一次出现的位置
SELECT POSITION('s'IN'sfsdfssf');#同上
SELECT SUBSTR('ahsdhfs',3,5),LEFT('fasfas',3),RIGHT('safsf',1);#截取
SELECT LOWER('AdasSF'),UPPER('asafsaSA');#全小写、全大写
SELECT LTRIM(' a  a '),RTRIM(' a a '),TRIM(' a a ');#去空格 中间空格去不了
SELECT REVERSE('abcd');#逆置字符串

SELECT NOW();#获取当前年月日
SELECT YEAR(NOW());#获取当前年 也可改为月、日
SELECT DATE_ADD(NOW(),INTERVAL 3 YEAR);#三年后  相加
SELECT DATE_ADD(NOW(),INTERVAL -3 YEAR);#三年前

SELECT CAST('029' AS SIGNED INTEGER);#转换


事务:将一个或者一组指令批量依次执行的过程,这些指令要么一起成功，要么一起失败.
ACID:
事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

SET autocommit=0;

SELECT * FROM demo2;

INSERT INTO demo2 VALUES(333);

COMMIT;
ROLLBACK;