初学数据库整理

数据库查询
–模糊查询

select * from 表名 where 字段 like "*%"；

–in 等值判断

select * from 表名 where 字段 in(70,60,55)；

–链接查询

select r.*,s.字段名 from 表名 result r, student s;

–内连接

select r.*,s.字段名 from 表名 result r, student s
    where r.字段名=s.字段名;
select r.*,s.字段名 from 表名 result r
    join student s on r.字段名=s.字段名;    

//满足条件才进行关联
左连接是以左表为主，右表匹配不上的置空。
–自连接

SELECT c1.`categoryName` 父栏目,c2.`categoryName` 子栏目
FROM category c1,category c2
WHERE c1.`categoryId`=c2.`pid`；

数据库
概念:长期存放在计算机内，有组织、可共享的大量数据的集合，是一个数据“仓库”
作用:保存、管理数据
关系型数据库（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语句中的注释符，也可以用 /该处为注释/

查询最基础最常用: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
从右表中返回所有的记录，即便在左表中没有匹配的行
自连接
使用前提:一个表的某个字段被多次使用,并且这多次使用有关系

常见的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的个数,效率高
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）

触发器
语法：create trigger 触发器名 before|after 事件 on 表名 for each row 触发器语句;
例子
DELIMITER KaTeX parse error: Expected 'EOF', got '#' at position 1: #̲改变语句结束符 …
DELIMITER ;
/*
*注意触发器里面触发的内容在建立触发器时候不会检查语句错误,只有当触发的时候才会检查语句是否有问题,
*且当触发语句出错时,触发该触发器的语句也会被取消执行
*/

视图(实际是一个临时表)
语法: create view 视图名 as select查询语句;
/*
*注意mysql视图不支持子查询,创建一个视图作为表查询即可
*/

存储过程(注重过程)
创建存储过程语法:
DELIMITER $$CREATEprocedure‘存储过程名‘(参数列表)-->in代表入参,out代表出参参数列表格式:in或out参数名参数类型beginsql语句end$$
DELIMITER ;
使用存储过程的关键字 call
例子:
DELIMITER $$CREATEPROCEDUREtes{t}_p(INnum1INT,INnum2INT,OUTsum{1}_{2}INT,OUTminus{1}_{2}INT)BEGINSETsum{1}_2=num1+num2;SETminus{1}_2=num1-num2;END$$
DELIMITER ;
CALL test_p(10,20,@a,@b); – 需要用会话变量保存返回值
SELECT @a,@b;
函数(注重结果)
创建函数语法:
DELIMITER KaTeX parse error: Expected 'EOF', got '#' at position 54: …URNS 返回值类型 #̲(参数列表格式:参数名 参数类…
DELIMITER ;
SELECT 函数名(实参);#函数可直接使用

MySQL 流程控制语句
1)if语句语法
if 条件1 then
执行语句1;
else 条件2 then
执行语句2;
else 条件3 then
执行语句3;
…
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;