Mysql数据库复习-白红宇

Mysql数据库复习

阅读量：4161 次

发布时间：2019-05-26

本文共 7025 字，大约阅读时间需要 23 分钟。

文章目录

数据库

概念:长期存放在计算机内，有组织、可共享的大量数据的集合，是一个数据“仓库”

作用:保存、管理数据

关系型数据库（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为0SELECT 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)	#截取字符串起始位置为1SELECT 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);