文章目录
数据库
概念:长期存放在计算机内,有组织、可共享的大量数据的集合,是一个数据“仓库”
作用:保存、管理数据
关系型数据库(SQL)
MySQL、Oracle、SQL Server、SQLite、DB2,Hive
非关系型数据库(NOSQL)
Redis、MongoDB,Hbase
数据库管理系统(Database Management System)DBMS–>服务
数据管理软件,科学组织和存储数据、高效地获取和维护数据
DDL(数据定义语言) 定义和管理数据对象,如数据库,数据表等 CREATE、DROP、ALTER
DML(数据操作语言) 用于操作数据库对象中所包含的数据 INSERT、UPDATE、DELETE
DQL(数据查询语言) 用于查询数据库数据 SELECT
DCL(数据控制语言) 用来管理数据库的语言,包括管理权限及数据更改 GRANT、COMMIT、ROLLBACK
创建数据库–>CREATE [IF NOT EXISTS] 库名称;
查看数据库–>SHOW DATABASES;
删除数据库–>DROP DATABASE [IF EXISTS] 库名称;
使用指定的数据库–>USE 库名称;
创建表
CREATE TABLE [IF NOT EXISTS] subject
( --如果表名是关键字加反引号
subject_no INT(11) PRIMARY KEY, --列名 列类型(列长度) 参数,
列数据类型:字符串类型(char varchar text) 日期和时间型(DATETIME DATE TIMESTAMP) 数值类型(int double decimal(m–>全部长度,d–>小数长度)) NULL值,用null算术运算结果仍为null
subject_name VARCHAR(50), …
subject_hour INT(4), …
grade_id INT(4) --最后一列不要有逗号
)[ 表类型 ] [ 表字符集 ] [注释] ;
查看表结构–>desc 表名;
查看表数据–>select * from 表名;
插入表数据–>insert into表名 values(2,‘mysql’,50,2);(注意:括号里的值根据数据类型需相匹配)
删除表–>DROP TABLE 表名;
更改表名–>ALTER TABLE 原表名 RENAME TO 新表名;
显示表创建语句–>SHOW CREATE TABLE subject1
;
alter修改表结构–>
ALTER TABLE 表名 ADD 列名 列类型(长度) 列属性;-- >加入列
ALTER TABLE 表名 MODIFY 列名 列类型(长度) 列属性;–> 修改列
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型(长度) 列属性;–> 改列名和列属性
列属性
UNSIGNED -->非负
ZEROFILL -->0填充
AUTO_INCREMENT -->自增
NULL 和 NOT NULL–>空/非空
DEFAULT -->默认值
外键管理
– >对于公共关键字,有外键的表叫子表 有主键的叫主表
添加外键
1.建表时建外键(正常情况用的多)
语法:constraint 外键名 foreign key(子表外键列) references 主表(主表主键列)
2.建表后添加外键–>前提条件子表不能有不符合外键约束的数据
语法:alter table 子表 add constraint 外键名 foreign key(子表外键列) references 主表名(主表主键列)
DML数据操作语言
1.insert添加数据
语法:INSERT INTO 表名 [ ( 字段1, 字段2, 字段3, … ) ] VALUES ( ‘值1’, ‘值2’, ‘值3’, …)
注意字段顺序可与建表时不一样,但是后面的值要与字段顺序和类型匹配
2.update修改数据
语法:UPDATE 表名 SET 列名1=值1 [ ,列名2=值2 … ][ WHERE 条件判断 ];
between and 在数据量大时效率会高得多
3.delete删除数据
#1)DELETE FROM 表名 [ WHERE 条件判断]; -->不会清空自增序号
#2)truncate table 表名;–>不可带条件,会清空自增序号,效率更高,使用TRUNCATE TABLE不会对事务有影响
DQL数据查询语言
语法:
SELECT [ALL | DISTINCT]
{ * | table.* | [ table.field1 [ as alias1] [, table.field2 [as alias2]][, …]] }
FROM table_name [ as table_ alias ]
[ left|out|inner join table_name2 ] #联合查询
[ WHERE … ] #指定结果需满足的条件
[ GROUP BY …] #指定结果按照哪几个字段来分组
[ HAVING …] #过滤分组的记录必须满足的次要条件
[ ORDER BY… ] #指定查询记录按一个或者多个条件排序 -->关键字asc升序 desc降序(每列需要有关键字修饰排序,默认升序)
[ LIMIT { [ offset,] row_count | row_count OFFSET offset }] ; #指定查询的记录从哪条至哪条(分页) 分页规律–>每页条数*(当前页数-1),每页条数
[] 括号代表可选的;
{} 括号代表必须的;
两个- #是MySQL语句中的单行注释符,也可以用 JAVA的多行注释
查询最基础最常用:select * from 表名;
AS子句作用(也可省略不写):
可给数据列取一个新别名
可给表取一个新别名
可把经计算或总结的结果用另外一个新名称来代替
DISTINCT关键字的使用
作用–>去掉SELECT查询返回的记录结果中重复的记录(所有返回列的值都相同),只返回一条
模糊查询
like:结合通配符(%和_)使用:%代表0~任意字符 _代表一个字符
SELECT * FROM student WHERE StudentName LIKE '%梅';
SELECT * FROM student WHERE StudentName LIKE '_梅';
in:–>等值判断
SELECT * FROM result WHERE StudentResult IN (60,80,90,99);
联合查询
内连接inner join(inner可省略):等值 非等值 自连接
SELECT r.*,s.`StudentName` FROM result r INNER JOIN student s ON r.`StudentNo`=s.`StudentNo`;
外连接-->以谁为主问题,左连接左表为主右边匹配不上的置空,右连接右表为主左边匹配不上的置空
左外连接:left join
从左表中返回所有的记录,即便在右表中没有匹配的行
右外连接:right join
从右表中返回所有的记录,即便在左表中没有匹配的行
自连接
使用前提:一个表的某个字段被多次使用,并且这多次使用有关系
全连接 union
常见的mysql函数
#mysql函数
-- 1)数学函数
SELECT ABS(-8); #绝对值
SELECT CEILING(9.8);#大于等于我最小整数
SELECT FLOOR(9.3); #小于等于我的最大整数
SELECT RAND();#返回0-1之间的随机数
SELECT RAND(10);#种子确定,数就确定
SELECT SIGN(-9); #符号函数-->负数-1 正数1 0为0
SELECT MOD(3,2);#第一个数对第二个数取余
SELECT (5 DIV 2);#第一个数对第二个数取商-->div是取商的关键字不是函数
-- 2)字符串函数
SELECT CHAR_LENGTH("好好学习,数据库");#返回字符串的字符数
SELECT CONCAT('我','哎'); #合并字符串
SELECT INSERT( '我爱你课工场',1,3,'很爱'); #替换字符串,位置从1开始从某个位置开始,替换某个长度 若起始位置超过长度返回原字符串
SELECT LOWER(' I LOVE YOU'); #变小写
SELECT UPPER('i love you'); #变大写
SELECT LEFT('课工场欢迎你',3); #左边截取几个长度字符串
SELECT RIGHT('课工场欢迎你',3); #右边截取几个长度字符串
SELECT REPLACE('课工场欢迎你,你好','你','你们');
SELECT SUBSTRING('课工场欢迎你',1,3) #截取字符串起始位置为1
SELECT REVERSE('课工场欢迎你'); #反转字符串
-- 3)日期和时间函数
#日期和时间
SELECT NOW();
SELECT LOCALTIME();
SELECT SYSDATE();
#日期
SELECT CURRENT_DATE();
SELECT CURDATE();
#具体
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
#系统信息函数
SELECT VERSION();
SELECT USER();
#统计函数
COUNT();#统计行数
-- >参数情况
1)表的列 需要计数非空列,一般会计数主键列
2)* 效率比较低
3)1 参数为1表示增加一列填充1去数1的个数,效率高
SUM();#数值求和
AVG();#数值平均值
MAX();#最大值
MIN();#最小值
事务
事务就是将一组SQL语句放在同一批次内去执行,如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
事务必须具备以下四个属性,简称ACID 属性
原子性(Atomicity)一致性(Consistency)隔离性(Isolation)持久性(Durability)
步骤
SET AUTOCOMMIT = 0; 关闭MySQL数据库的自动提交
START TRANSACTION 开始一个事务,标记事务的起始点
COMMIT 提交一个事务给数据库
ROLLBACK 将事务回滚,数据回到本次事务的初始状态
SET AUTOCOMMIT = 1; 开启MySQL数据库的自动提交
索引
作用
提高查询速度
确保数据的唯一性
可以加速表和表之间的连接,实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时,可以显著减少分组和排序的时间
全文检索字段进行搜索优化
分类
主键索引(PRIMARY KEY)
唯一索引(UNIQUE)
常规索引(INDEX)
全文索引(FULLTEXT)
查看索引
SHOW KEYS FROM 表名;或者SHOW INDEX FROM表名;
触发器
语法:create trigger 触发器名 before|after 事件 on 表名 for each row 触发器语句;
例子:
DELIMITER $$#改变语句结束符
CREATE TRIGGER trig_g_s
AFTER INSERT
ON grade FOR EACH ROW
BEGIN
INSERT INTO student(studentno,studentname,sex,gradeid) VALUES(1745,'西巴',0,new.GradeId);#old 和 new关联表数据
INSERT INTO `subject` VALUES(18,'语文',80,new.GradeId);
END $$
DELIMITER ;
/*
*注意触发器里面触发的内容在建立触发器时候不会检查语句错误,只有当触发的时候才会检查语句是否有问题,
*且当触发语句出错时,触发该触发器的语句也会被取消执行
*/
视图(实际是一个临时表)
语法: create view 视图名 as select查询语句;
/*
*注意mysql视图不支持子查询,创建一个视图作为表查询即可
*/
存储过程(注重过程)
创建存储过程语法:
DELIMITER $$
CREATE procedure `存储过程名` (参数列表) -->in代表入参,out代表出参
参数列表格式:in或out 参数名 参数类型
begin
sql语句
end $$
DELIMITER ;
使用存储过程的关键字 call
例子:
DELIMITER $$
CREATE PROCEDURE test_p(IN num1 INT ,IN num2 INT ,OUT sum1_2 INT,OUT minus1_2 INT)
BEGIN
SET sum1_2=num1+num2;
SET minus1_2=num1-num2;
END $$
DELIMITER ;
CALL test_p(10,20,@a,@b); -- 需要用会话变量保存返回值
SELECT @a,@b;
函数(注重结果)
创建函数语法:
DELIMITER $$
CREATE FUNCTION 函数名(参数列表) RETURNS 返回值类型 #(参数列表格式:参数名 参数类型)-->注意varchar类型参数要加长度
BEGIN
sql语句
RETURN 一个值;
END $$
DELIMITER ;
SELECT 函数名(实参);#函数可直接使用
MySQL 流程控制语句
1)if语句语法
if 条件1 then
执行语句1;
else 条件2 then
执行语句2;
else 条件3 then
执行语句3;
else
执行最后语句;
...
end if;
2)case语句(多分支语句结构):首先从WHEN后的VALUE中查找与CASE后的VALUE相等的值,如果查找到则执行该分支的内容,否则执行ELSE后的内容
case 变量或者值可省略
when value1 then
执行语句1;
when value2 then
执行语句2;
...
else
执行语句n;
end case;
3)while循环语句:首先判断条件是否为真,如果是则执行循环体,否则退出循环
while 条件 do
...
end while;
4)repeat循环(相当于Java中的do while)先执行一次循环体,之后判断条件是否为真,若为真则退出循环,若为假否则继续执行循环(使用的是until语句判断)
repeat
...
until 条件
end repeat;
控制循环语句(while repeat loop)的关键字:
leave为退出当前循环(java中的break)
iterate为重新开始一次循环(Java中的continue)
不同的是mysql中这俩个关键字后面需要跟一个循环的名称
声明变量
1)@符号声明变量
这种方式定义的变量,定义同时赋值,语句的位置可以任意出现,不需要带上参数类型,但定义的变量是会话变量,如果想定义局部变量,那么变量名加上'@'。
例如:set @aa='XXX';
2)declare
语句的位置必须在begin下(变量声明全部在首段),同时要带上参数类型
例如:declare tname varchar(50);
使用序号
① := 与 = 的区别
:= 赋值的意思 在set update select 中表示赋值的意思,用的比较少一般都用=,但是在用变量实现行号时(比如获取排列序号)一定要用:=。
= 等于的意思 只有当set 和 update时,和:=的意思是一样的,表示赋值,其余情况都是等于的意思。
② 用户变量 @
@rank 是对一个叫rank的参数进行赋值。
注意:对用户变量赋值有两种方式,一种直接用"=“另一种用”:="。其区别在于使用set命令对用户变量赋值时,两种方式都可以使用;当使用select语句对用户变量进行赋值时,只能使用“:=”方式,因为在select语句中,“=”被看做是比较操作符。
例如:
SET @a=0;
SELECT c.card
, @a:=@a+1
FROM tb_card
c;
也可:
SELECT c.card
,@b:=@b+1
FROM tb_card c,(SELECT @b:=0) b;
关于分组之后各分组的排名问题(不考虑并列)这个作者写的比较详细可参照:https://www.jianshu.com/p/fff5d1f71c0f