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