当前位置：文档库 › 数据库笔记大全

数据库笔记大全

Sql国际标准管理数据库的语言
1：92标准 99标准
2：Sql语言分类
DDL（数据定义语言）用来创建删除存储数据用的数据库以及数据库中的表等对象。包含以下几种指令：
CREATE 创建数据库和表等对象
DROP 删除数据库和表等对象
ALTER 修改数据库和表等对象的结构
DML（数据操作语言）用来查询或者变更表中的记录。包含以下以下几种指令：
SELECT 查询表中的数据
INSERT 向表中插入新的数据
UPDATE 变更表中的数据
DELETE 删除表中的数据
DCL（数据控制语言）用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对RDBMS的用户是否有权限操作数据库中的对象（数据库表）进行设定。包含以下几种指令：
COMMIT 确认对数据库中的数据进行的变更
ROLLBACK 取消对数据库中的数据进行的变更
3:相关命令
启动mysql服务器 net start mysql
进入服务器：mysql -u root -p
创建数据库：create database 数据库名
使用/更换数据库：use 数据库名
查看数据库：show databases;
查看数据据里面所建的表：show tables;
删除数据库：drop database 数据库名;
创建表：create table 表名(字段名字段类型，字段名字段类型);
删除表：drop table 表名
查看表的创建的列：SHOW COLUMNS FROM 表名;
SHOW CREATE TABLE 表名;
修改表名：rename table 旧表名 to 新表名
向表中插入数据：insert into 表名(列1，列2) values(值1，值2);
查看数据：select 列1，列2 from 表
修改表结构：alter
alter table 表名 drop 列
alter table 表名 add 列类型；
alter table 表名 modify 列新类型
修改表中的列的名字和类型
alter table 表名 change 旧列名字新列名列类型;

4 数据类型

字符串类型：char varchar
Char(10)定长的，表示该字段的值都是长度为10的
Varchar(10)不定长的，表示值最多是10的长度，但是不够10，按本身长度

布尔值：boolean
整数：interger(p)
小数：DECIMAL(p,s) p: 证书精度 s:小数位
时间：date time

别名

字段和表格都可以起别名，别名可以在前面加as,也可以不加一旦表格起了别名，字段就必须用别名

SELECT id AS "学号"， https://www.wendangku.net/doc/a77605564.html, AS "姓名" FROM aa;
SELECT a.id AS "学号"， https://www.wendangku.net/doc/a77605564.html, AS "姓名" FROM aa a;

常数
Select 常数 as 别名 from 表名；

运算符
+ - * /
SELECT id+5 FROM aaa;
所有包含有NULL的计算，结果肯定是NULL

去重 distinct
1：去重是去除所有列查询结果重复的
SELECT DISTINCT 列名1，列名2...... FROM 表名；
2：去重关键字distinct不能写在一个列之后

Where 查询条件
= > < !=(<>) >= <=
SELECT * FROM aa WHERE id>=2;
判断null
is null is not null
SELECT * FROM aa WHERE age IS NOT NULL;

3:NOT

将查询结果反向

4：and并且 or 或者
#查询成绩都及格
SELECT * FROM aa WHERE chinesscore >=60 AND mathscore >=60;
-- 查询语文在80分或数学在80分以上的并且总成绩在 170以上
SELECT * FROM aa WHERE chinesscore >=80 OR mathscore>=80 AND (chinesscore+mathscore)>170 ;

聚合函数：

多行查询记录结果的运算

常用函数： count(列)sum() avg() max() min()

计算时会忽略null值

去重计算 count（distinct 列）去重关键字不能写在函数外面

Group by
分组查询分组进行聚合函数运算
Group by关键字必须是放置where语句之后的
Group by后面可以放多个列，多列两两结合去分组计算

-- 查询每个班级的学生人数
SELECT COUNT(*),class FROM aa GROUP BY class

-- 查询每个班语文及格的学生人数
SELECT COUNT(*),id,sex,class FROM aa WHERE chsc>=60 GROUP BY class,sex
Having

分组计算之后，进行条件筛选
Having后面可以跟的条件1）常数 2）聚合函数 3）group by 后面跟的列的条件筛选

-- 查询2班以外其他班级的学生人数
SELECT COUNT(*),class FROM aa WHERE class<>1 GROUP BY class;

-- 查询结果出错，因为group by后面只有class列，不能对sex进行筛选
SELECT COUNT(*) FROM aa GROUP BY class HAVING sex="男";

SELECT COUNT(*) ,class FROM aa GROUP BY class HAVING 3 ;
排序 order by
1: order by 列asc desc（倒叙）
2：可以根据多列进行排序，排序是先根据第一列排序，如果值相同，再根据下一个列排序
3：多列倒叙，一个倒叙添加一个关键字desc
4：位置在where和group by 之后
-- 根据成绩排序
SELECT * FROM aa ORDER BY chsc DESC,masc DESC;

-- 大于30岁的学生，进行成绩排序
SELECT * FROM aa WHERE age>=25 ORDER BY chsc;

-- 每个班级人数，并且排序
SELECT COUNT(*)AS c, SUM(age),class,id FROM aa GROUP BY class ORDER BY class

增删改
-- 插入数据
INSERT INTO b(id,age,NAME) VALUES(1,20,'tom');
INSERT INTO b(id,NAME) VALUES(1,'tom1');
-- 插入全部列的数据
INSERT INTO b VALUES(2,"tom2",10);
-- 插入其他表格中的数据
INSERT INTO b(id,age,NAME) SELECT id,age,NAME FROM aa

-- 创建并将查询结果存储到一个新表中
CREATE TABLE test AS SELECT * FROM aa WHERE class=1;
-- 创建一个新表，空表，它的表结构和aa相同
CREATE TABLE test1 AS SELECT * FROM aa WHERE 1=2;

-- 删除表格 ddl
DROP TABLE test1
-- 删除所有数据
DELETE FROM b WHERE id=4

-- 更新
UPDATE b SET NAME="tom1" WHERE id=1

三大范式：
关系型数据库的创建表格关系的规则规范
第一范式：每一行只包含一个实例的信息，每个字段只能包含一个值
第二范式：在第一范式的基础上，每个表格都有自己的唯一表示列，这个列叫主键列，这个列可以是多列组合而成的，叫复合主键
第三范式：在前两范式的基础上，为了减少数据冗余，

可以设置外键
约束：
-- 创建表时设置约束
CREATE TABLE stu(
-- 主键
id INT PRIMARY KEY AUTO_INCREMENT,
-- 非空
NAME VARCHAR(20) NOT NULL,
-- 唯一
idcard VARCHAR(30) UNIQUE,
-- 默认值
sex VARCHAR(6) DEFAULT "男",
-- 外键
cid INT REFERENCES class(cid)
);
追加和删除约束
主键约束：非空+唯一
-- 删除
ALTER TABLE stu DROP PRIMARY KEY;
-- 追加
ALTER TABLE stu ADD CONSTRAINT stu_pk PRIMARY KEY(id);
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
非空约束
追加不为空
ALTER TABLE 表名 MODIFY 列名列类型 NOT NULL;

删除不为空
ALTER TABLE 表名 MODIFY 列名列类型
唯一约束
-- 添加
ALTER TABLE stu MODIFY idcard VARCHAR(30) UNIQUE
-- 删除唯一
ALTER TABLE stu MODIFY idcard VARCHAR(30)
ALTER TABLE stu DROP INDEX idcard
外键约束
-- 添加外键
ALTER TABLE stu ADD CONSTRAINT tid_fk FOREIGN KEY(tid) REFERENCES teacher(tid)
alter table 外键表名 add constraint 约束名称 foreign key (外键字段) references 主键表名(约束列名)
如果表A中的Ids是主键，要约束表B中得Aid列，那么语句应该是：

alter table B add constraint A_B_Ids foreign key(Aid) references A(Ids)
-- 外键删除
ALTER TABLE stu DROP FOREIGN KEY tid_fk
默认值（不是约束）
ALTER TABLE stu MODIFY sex VARCHAR(20) DEFAULT "男"
ALTER TABLE stu MODIFY sex VARCHAR(20)
Mysql自增长
ALTER TABLE stu MODIFY id INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE stu MODIFY id INT
事务

1事务就是需要在同一个处理单元中执行的一系列更新处理的集、合。
2在mysql 中需要执行代码开启事务，一旦提交或回滚，表示事物结束
3在oracle数据库中不需要手动开启事务，因为在Oracle中，事务一直是开启状态
-- 开启事物
START TRANSACTION;
COMMIT;-- 提交
ROLLBACK;-- 回滚
事务的ACID特性
原子性：要么都成功，要么都不成功
一致性：遵循约束
隔离性：不同用户互补干扰，在事务提交之前，其他用户看不到更新数据
持久性：一旦提交事务，数据将永久保存

视图

1.一个查询结果集组成的表
2.视图中存储的是查询语句
3.视图是将查询结果作为集合保存起来，这样在项目中多次用到该查询，就不用再写查询命令了，视图的作用是主要是查看结果集
4.在视图中可以进行增删改，增删改后的数据和表格中的数据一致
5.但并不是所有的视图都可以增删改
6.创建视图
create view 视图名称(列名1，列名2......) AS SELECT 语句。
create view v_aa AS SELECT id,NAME,age FROM aa;
create view v_aa2(a_i0
d,NAME,age)AS SELECT id,NAME,age FROM aa;
show table status like
7.删除视图
Drop view 视图名

子查询
1查询结果作为一个列（查询结果只能是一列一行）
SELECT 列名1，列名2，(SELECT AVG(列名) FROM 表名) AS 表别名 FROM

表名；
2查询结果作为查询条件，（注意表达式如果是= > <等，查询结果只能是一列一行）
SELECT 列名1 FROM 表名1 AS 表1 WHERE 列名表达式（
SELECT 列名 FROM 表名2 WHERE 列名表达式表1.列名）
3查询结果作为一个表格
Select 列名1 from (select * from 表名)

-- 查询叫lucy的老师所带的所有学生信息
SELECT s.* FROM stu s WHERE tid=(SELECT tid FROM teacher WHERE tname="lucy")
-- 查询学生id，姓名，班级语文成绩平均值
SELECT id,NAME, (SELECT AVG(chsc) FROM aa) AS avg_chsc FROM aa

-- 班级人数最多是多少
SELECT MAX(ccid.coun) FROM (SELECT COUNT(*) AS coun,cid FROM stu GROUP BY cid) ccid

谓词
In
SELECT 列名1，列名2... FROM 表名 WHERE 列名 IN （值1，值2......）
-- 年龄是20,24 34的人信息
SELECT * FROM aa WHERE age IN(20,24,34)
-- aa中和stu中班级相同的所有人信息
SELECT * FROM aa WHERE class IN (SELECT cid FROM stu)
Between and
相当于：
SELECT 列名1，列名2.... FROM 表名 WHERE 列名 >= 值1 AND 列名<=值2.......

-- 年龄范围在20-30之间的所有人信息
SELECT * FROM aa WHERE age BETWEEN 20 AND 30
Like 模糊查询
通配符
_ 一个占位符
% 0-多个占位

-- 名字中有含有to的
SELECT * FROM aa WHERE NAME LIKE "%to%"
-- 名字中开头是to
SELECT * FROM aa WHERE NAME LIKE "to%"
-- 名字中第二个字母是to
SELECT * FROM aa WHERE NAME LIKE "_to%"
EXISTS
判断是否有子查询结果，如果没有为false，一旦有结果，无论结果有几个，都为true，该关键字不能用于增删改
-- 一个班中有不及格的，就将所有学生的成绩+10
SELECT * FROM stu WHERE EXISTS(SELECT score FROM stu WHERE score <60)
Any
All

-- 和老师年龄-10相同的学生信息
SELECT * FROM stu WHERE age = ANY(SELECT tage-10 FROM teacher)

-- 比学生年龄大的老师信息
SELECT * FROM teacher WHERE tage >= ALL(SELECT age FROM stu)

级联查询
多个表之间进行查询，查询结果行数是相乘之后的数
92标准 99标准
92标准
-- 查询学生信息和相关的班级信息
SELECT s.id,https://www.wendangku.net/doc/a77605564.html,ame,https://www.wendangku.net/doc/a77605564.html, ,s.age FROM stu s,class c WHERE s.cid=c.cid
-- 查询比mandy的学生，成绩高的所有学生
SELECT s.id ,https://www.wendangku.net/doc/a77605564.html,,s.score,https://www.wendangku.net/doc/a77605564.html,,s1.score FROM stu s ,stu s1 WHERE https://www.wendangku.net/doc/a77605564.html,="mandy" AND s.score<=s1.score AND https://www.wendangku.net/doc/a77605564.html, <> https://www.wendangku.net/doc/a77605564.html,;

-- 99标准
SELECT s.id,https://www.wendangku.net/doc/a77605564.html,ame,https://www.wendangku.net/doc/a77605564.html, ,s.age FROM stu s JOIN class c ON s.cid=c.cid;

SELECT s.id ,https://www.wendangku.net/doc/a77605564.html,,s.score,https://www.wendangku.net/doc/a77605564.html,,s1.score FROM stu s inner JOIN stu s1 ON s.score<=s1.score AND https://www.wendangku.net/doc/a77605564.html, <> https://www.wendangku.net/doc/a77605564.html, WHERE https://www.wendangku.net/doc/a77605564.html,="mandy";

SELECT s.id,https://www.wendangku.net/doc/a77605564.html,ame,https://www.wendangku.net/doc/a77605564.html, ,s.age,t.tname FROM stu s
INNER JOIN class c ON s.cid=c.cid
JOIN teacher t ON s.tid=t.tid

-- 外级联（有一方级联关系中是null值）
-- 查询学生和相关的老师信息（包括了没有老师的学生信息）
SELECT s.id,https://www.wendangku.net/doc/a77605564.html,,s.tid ,t.tname

FROM stu s LEFT OUTER JOIN teacher t ON s.tid= t.tid;
-- 查询老师和他相关的学生信息（包括了目前没有学生的老师信息）
SELECT s.id,https://www.wendangku.net/doc/a77605564.html,,t.tname,t.tid FROM stu s RIGHT OUTER JOIN teacher t ON s.tid= t.tid;

笛卡尔集
没有级联条件的
SELECT s.id,https://www.wendangku.net/doc/a77605564.html,ame,https://www.wendangku.net/doc/a77605564.html, ,s.age FROM stu s,class c
SELECT s.id,https://www.wendangku.net/doc/a77605564.html,ame,https://www.wendangku.net/doc/a77605564.html, ,s.age FROM stu s JOIN class c
Having 分组查询计算之后的条件 on 多张表之间的级联条件 where 条件查询

组合查询
Select 查询语句1
UNION ALL
Select 查询语句2
Union all
Select 查询语句3
查询结果的列数必须一致

函数
SELECT ABS(-9)
SELECT ABS(age) FROM aa
SELECT MOD(9.0,8.1)
SELECT ROUND(34.456,2)
拼接
SELECT CONCAT('aa',NAME),id,NAME FROM aa
SELECT CONCAT(CONCAT('aa',NAME),'bb'),id,NAME FROM aa
取长度
SELECT LENGTH(NAME),NAME FROM aa
大写转换
SELECT UPPER(NAME),NAME FROM aa
截取
SELECT SUBSTRING(NAME FROM 1 FOR 2),NAME FROM aa

-- 日期
SELECT CURRENT_DATE
SELECT CURRENT_TIME
SELECT CURRENT_TIMESTAMP
INSERT INTO aa(DATE) VALUES(CURRENT_DATE)
UPDATE aa SET DATE=CURRENT_DATE WHERE id=3;
CAST（类型转换）

数值转为字符串
SELECT CAST( 123 AS CHAR(20)) AS 别名；

字符串转换日期
SELECT CAST(“2001-1-1” AS DATE) AS 别名；

COALESCE（NULL转换其他）

将NULL转为其他值: 取参数中第一个非null值
SELECT COALESCE（NULL，1） AS 别名；
SELECT COALESCE（NULL，“demo”，NULL） AS 别名；
SELECT COALESCE（NULL，NULL，“1991-1-1”） AS 别名；

Case
等值
Select case sex
When “1” then “男”
When “2” then “女”
Else “其他” end as ‘sex’
From stu;

不等值
Select case
When age<=18 then “未成年”
When age<=30 then “青年”
When age<=60 then “壮年”
Else “其他” end as “age”
From stu;

ALTER TABLE aa DROP test

ALTER TABLE aa ADD test INT AFTER age
ALTER TABLE aa MODIFY test INT AFTER NAME

SELECT * FROM v_aa
-- 视图
DESCRIBE v_aa
SHOW TABLE STATUS LIKE "v_aa"
SHOW CREATE VIEW v_aa
SELECT * FROM information_schema.views ;

-- 修改视图
CREATE OR REPLACE VIEW v_aa AS SELECT id,NAME FROM aa
ALTER VIEW v_aa AS SELECT NAME FROM aa

引擎
查看所有引擎
Show engines;
查看当前引擎
SHOW VARIABLES LIKE '%storage_engine%';
设置引擎
set default_storage_engine=InnoDB

常用引擎
1）INNODB 默认引擎提供了事务，主键自增长
2）Myisam 将表存储在三个文件中
后缀为frm为扩展名的文件存储表的结构
MYD为扩展名的文件存储数据，其是MYData的缩写；
MYI为扩展名的文件存储索引，其是MYIndex的缩写
Memory 将数据存储在硬盘文件中，可以删除文件来删除数据，不通过表去删除
Eg:注意添加时不能用first 列，但是可以用first和after列
ALTER TABLE aa ADD test INT AFTER age

索引

可以用来快速查询数据库表中的特定记录
索引由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度，缺点是会降低增删改效率
创建索引
1）创建表时创建索引
创建唯一索引（主键和唯一约束）
Create table 表名（id int primary key，name varch ar(20) unique）
2)在已经存在的表上创建索引
普通索引多行、单行索引
CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (属性名 [ (长度) ] [ ASC | DESC] );
CREATE INDEX in_age ON aa(age) -- 单行
CREATE INDEX in_name ON aa(NAME,teacher) -- 多行
CREATE INDEX in_age ON aa(age DESC)
3）用ALTER TABLE语句来创建索引
ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名（属性名 [ (长度) ] [ ASC | DESC]）;
ALTER TABLE aa ADD INDEX in_age(age)

删除索引
DROP INDEX 索引名ON 表名
验证索引效率
EXPLAIN SELECT * FROM student WHERE age>40
FULLTEXT索引仅可用于 MyISAM 引擎下

分页查询
LIMIT ：后面两个参数，第一个参数：起始查询记录的行数，第一行为0，第二个参数：查询显示的行数
SELECT * FROM student LIMIT 2,3

DROP PROCEDURE IF EXISTS pr_test;
DELIMITER //
CREATE PROCEDURE pr_test(IN id INT,OUT t_age INT,OUT t_teacher VARCHAR(20))
BEGIN
SELECT age FROM aa WHERE aa.id=id INTO t_age;
-- 变量进行赋值
SET t_teacher="tom";
END //
DELIMITER ;

-- 调用存储过程
SET @id=5;
CALL pr_test(@id,@age,@teacher);
SELECT @age,@teacher;

DROP PROCEDURE IF EXISTS pr_test;
DELIMITER //
CREATE PROCEDURE pr_test()
BEGIN
DECLARE num INT;
SET num=2;
WHILE num<100 DO
INSERT INTO b VALUES(num,20+num,CONCAT("tom",num));
SET num=num+1;
END WHILE ;
END //
DELIMITER ;

分支语句：
If
If 条件 then
Elseif 条件 then
Else
End if;

Case
Case 变量
When 值1 then 。。。
When 值2 then 。。。
Else 。。。
End case;

Case
When 条件 then。。。
When 条件 then。。。
Else 。。。
End case;
Loop:
add_num: LOOP
SET @count=@count+1;
IF @count=100 THEN
LEAVE add_num ;
END LOOP add_num ;

ITERATE语句是跳出本次循环，然后直接进入下一次循环。ITERATE语句是跳出本次循环，然后直接进入下一次循环。

REPEAT语句
REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下：

[begin_label:] REPEAT
statement_list
UNTIL 结束条件
END REPEAT [end_label]

While
WHILE @count<100 DO
SET @count=@count+1;
END WHILE ;

函数
DROP FUNCTION IF EXISTS funtest2;
DELIMITER //
CREATE FUNCTION funtest2(id INT)
RETURNS VARCHAR(20)
BEGIN
DECLARE s_name VARCHAR(20);
SELECT https://www.wendangku.net/doc/a77605564.html, INTO s_name FROM aa a WHERE a.id=id;
RETURN s_name;
END//

SELECT funtest2(3);

备份
备份数据库

中（数据库中的某些表或全部表）
mysqldump -u username -p dbname table1 table2 ...> BackupName.sql
还原
mysql -u root -p [dbname] < sql文件的路径
注意，一定要在cmd 中执行。

备份表格
mysqldump -u root -pPassword -T目标目录 dbname table [option] ;
例如：mysqldump -u root -p123456 -T "d:/cc" stu student
还原
mysqlimport -u root -pPassword [--LOCAL] dbname file [OPTION]
Eg:
mysqlimport -u root -p123456 test2 "d:\Test\score.txt" 只能导入结果集
需要有相对应的表格即test2 .score，表名和文件名相对性

表的查询结果的备份
备份
1）SELECT id,NAME,age FROM aa INTO OUTFILE "d:/test1.txt"
2）mysql -u root -pPassword -e "SELECT语句" dbname> C:/name.txt ;
还原
LOAD DATA INFILE "d:/test2.txt" INTO TABLE b