以MySQL为例
数据库—>表—>数据
目录
使用命令行窗口连接MySQL数据库实例：mysql –u用户名 –p密码
特殊数据类型：Enum，Set，Text，Blob，TIMESTAMP ，默认值
字符和日期型数据应包含在单引号中。
| Database |
+---------------------------+
| information_schema | -- mysql元数据，基础数据
| mysql | --mysq配置数据库，其中包含用户信息。（用户名和密码，权限管理）
| performance_schema | --mysql数据库软件的运行数据，日志信息，性能数据
| test | --测试数据库。空的
+---------------------------+
SQL语句的分类：
DDL： 数据定义语言【创建/ 删除 / 更改】
create / drop / alter
DML：数据操作语句 【增删改】
insert / delete /update /truncate
DQL： 数据查询语言 【查】
select / show
delete from：可以全表删除 ；可以带条件删除 ；不能删除表的约束；使用delete from删除的数据可以回滚（事务）
truncate table：可以全表删除 ；不能带条件删除 ；可以删除表的约束 ；使用truncate table删除的数据不能回滚 (不产生回滚记录不用回滚空间,所以删除更快)
管理数据库：
增加： create database 数据库 default character utf8;
删除： drop database 数据库;
修改： alter database 数据库 default character gbk;
查询： show databases / show create database 数据库;
表空间:
创建表空间: create tablespace 表空间名 datafile '数据文件名' size 表空间大小
设置为默认表空间, 更改表空间状态, 重命名, 删除, 维护...
管理表：
选择数据库：use 数据库;
增加： create table 表(字段名1 字段类型,字段名2 字段类型......);
级联删除： drop table 表 cascade constraints;
修改：
添加字段： alter table 表 add [column] 字段名 字段类型;
删除字段: alter table 表 drop [column] 字段名;
修改字段类型： alter table 表 modify 字段名 新的字段类型;
修改字段名称 ： alter table 表 change 旧字段名 新字段名 字段类型;
修改表名称： alter table 表 rename [to] 新表名;
查询： show tables / desc student;
查询其他用户的存储过程: select * from DBA_objects where object='PROCEDURE' and owner='用户'
管理数据：
增加： insert into 表(字段1，字段2,。。。) values(值1，值2.。。。。);
删除： delete from 表 where 条件;
修改： update 表 set字段1=值1，字段2=值2...... where 条件;
查询：
--------------------------------------------------------------------------------------------------------------
SELECT语句格式：{}必选；[]可选
SELECT { [all | distinct(去重) ] 列 | *}
[ INTO 新表名]
FROM {表名 | 视图 | 其他查询}
[WHERE筛选条件表达式 ]
[GROUP BY 分组字段名列表 ]
[HAVING 分组条件表达式]
[ORDER BY 排序字段名列表 [ASC | DESC] ]
[COMPUTE 集合函数(列名1) [ BY 列名2] [...n]]
---------------------------------------------------------------------------------------------------------------
查询案例：
所有字段： select * from 表;
指定字段： select 字段1,字段2.... from 表;
指定别名： select 字段 as 别名 from 表;
合并列： select (字段1+字段2) from 表; 注：合并列只能合并数值类型的字段
去重： select distinct 字段 from 表;
去重统计:select count(distinct 字段) from table
条件查询：where
a）逻辑条件 ：and(&&) or（||） not（!）
select * from 表 where 条件1 and/or 条件2b）比较条件： > < >= <= = <> ；
BETWEEN ...AND... 在某一区间的值；
IN(set) 在in列表中的值，如：in(100,200)；
select * from 表 where servlet>=90;c）判空条件：
判断null： is null / is not null
判断空字符串： ='' / <>''d）
LIKE %(n个字符) _(一个字符)
IN (值1,值2,值3,...)
BETWEEN 值1 AND 值2
聚合查询（使用聚合函数的查询）：SELECT sum(字段)/ avg()/ max()/ min()/ count() FROM 表
分页查询：【查询语句】+ limit 起始行,查询行数
排序：【查询语句】+ order by 字段asc/desc
asc: 正序，顺序desc：反序，倒序
分组查询 就是按照字段值相同的 把所有条数据汇总成一条. having对group by 相当于where对select
分组查询：【查询语句】+ group by 字段
分组后筛选： having 条件
关联查询(多表查询)
多表查询规则：1）确定查询哪些表 2）确定哪些字段 3）表与表之间连接条件 (规律：连接条件数量是表数量-1)
1.交叉连接查询 不推荐，产生笛卡尔乘积现象
2.内连接查询：【只有满足条件的结果才会显示】
方法1
SELECT 表一字段,表二字段
FROM 表一,表二
WHERE 表与表之间连接条件
方法2
SELECT 表一字段,表二字段
FROM 表一 别名 --这里别名可省略AS
INNER JOIN 表二
ON 连接条件
3.左[外]连接查询：【左表数据全部保留】
FROM 左表
LEFT OUTER JOIN 右表
4.右[外]连接查询：【右表数据全部保留】
RIGHT OUTER JOIN
5.自连接查询
时间日期相关函数：
CURRENT_DATE () 当前日期
CURRENT_TIME() 当前时间
CURRENT_TIMESTAMP () 当前时间戳
DATE (datetime ) 返回datetime的日期部分
字符串相关函数：
待补
数学相关函数：
待补
数据约束：
对用户操作表的数据进行约束
默认值： DEFAULT
非空： NOT NULL
唯一： UNIQUE
主键（非空+唯一）：PRIMARY KEY
自增长： ZEROFILL PRIMARY KEY AUTO_INCREMENT --零填充+主键+自增长
外键（约束两种表的数据）：外键设置在副表上，通常参考主表的主键
CONSTRAINT 外键名称FOREIGN KEY(外键) REFERENCES 参考表(参考字段)
添加数据： 先添加主表，再添加副表
修改数据： 先修改副表，再修改主表
删除数据： 先删除副表，再删除主表
级联操作：有了外键约束后，希望直接修改或删除主表数据，从而影响副表数据。就要使用级联操作
级联修改： ON UPDATE CASCADE
级联删除： ON DELETE CASCADE
CONSTRAINT 外键名称 FOREIGN KEY(外键) REFERENCES 参考表(参考字段) ON UPDATE CASCADE ON DELETE CASCADE
索引
索引结构
B树， B+树和Hash
B树的所有节点既存放 键(key) 也存放 数据(data);
B+树只有叶子节点存放 key 和 data，其他内节点只存放key
索引分类
- 主键索引(Primary Key) 主键一定是唯一性索引，唯一性索引并不一定就是主键。
- 二级索引 / 辅助索引二级索引的叶子节点存储的数据是主键，通过二级索引，可以定位主键的位置
- 唯一索引(Unique Key) 不能出现重复的数据，但是允许数据为NULL， 可以有多个
- 普通索引(Index) 为了快速查询数据，可以有多个
- 前缀索引(Prefix) 只对文本的前几个字符创建索引，只适用于字符串类型
- 全文索引(Full Text) 检索大文本数据中的关键字的信息
聚集索引
索引结构和数据一起存放的索引。查询速度非常的快，更新代价大
主键索引属于聚集索引
非聚集索引
索引结构和数据分开存放的索引。
可能会二次查询(回表)： 叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。
二级索引属于非聚集索引。
覆盖索引
需要查询的字段正好是索引的字段，直接根据该索引查到数据， 而无需回表查询。
创建普通索引、UNIQUE索引或PRIMARY KEY索引
添加PRIMARY KEY（主键索引）
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`)
添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE (`column`)
添加INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name (`column`)
添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
使用CREATE 语句对表增加普通索引或UNIQUE索引
CREATE INDEX index_name ON `table_name`(`column`)
CREATE UNIQUE INDEX index_name ON `table_name`(`column`)
CREATE PRIMARY KEY INDEX index_name ON `table_name`(`column`) ;
删除索引
DROP INDEX index_name ON `table_name`
ALTER TABLE `table_name` DROP INDEX index_name
ALTER TABLE `table_name` DROP PRIMARY KEY
最左前缀原则
创建的联合索引由三个字段组成:
ALTER TABLE table ADD INDEX index_name (num,name,age)当查询的条件有为: num / (num AND name) / (num AND name AND age) 时，索引才生效。
有些Mysql版本好像不遵守最左前缀原则，索引也会生效。
存储过程
带有逻辑的sql语句
特点
1）执行效率非常快！存储过程是在数据库的服务器端执行的！！！
2）移植性很差！不同数据库的存储过程是不能移植。
-- 创建存储过程
DELIMITER $ -- 声明存储过程的结束符，遇到$就结束
CREATE PROCEDURE pro_test() --存储过程名称(参数列表)
BEGIN -- 开始
-- 可以写多个sql语句;
-- sql语句+流程控制
END $ -- 结束 结束符
-- 执行存储过程
CALL pro_test(); -- CALL 存储过程名称(参数);
参数：
IN： 表示输入参数，可以携带数据带存储过程中
OUT： 表示输出参数，可以从存储过程中返回结果
INOUT： 表示输入输出参数，既可以输入功能，也可以输出功能
-- 传入参数id，查询信息
DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT) -- IN: 输入参数
BEGIN
SELECT * FROM user WHERE id=eid;
END $
-- 调用
CALL pro_findById(4);
-- 带有输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20)) -- OUT：输出参数
BEGIN
SET str='helljava';-- 给参数赋值
END $
条件判断
IF ···THEN
ELSEIF ···THEN
ELSE ···
END IF;
循环
-- DECLARE 定义局部变量i,vsum
DECLARE i INT DEFAULT 1;
DECLARE vsum INT DEFAULT 0;
WHILE i<=num DO
SET vsum = vsum+i;
SET i=i+1;
END WHILE;
-- ***mysql的变量******
-- 全局变量（内置变量）：mysql数据库内置的变量 （所有连接都起作用）
-- 查看所有全局变量： show variables
-- 查看某个全局变量： select @@变量名
-- 修改全局变量： set 变量名=新值
-- character_set_client: mysql服务器的接收数据的编码
-- character_set_results：mysql服务器输出数据的编码
-- 会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中（如果连接断开，那么会话变量全部丢失！）
-- 定义会话变量: set @变量=值
-- 查看会话变量: select @变量
-- 局部变量： 在存储过程中使用的变量就叫局部变量（只要存储过程执行完毕，局部变量就丢失！！）
触发器
触发器建在永久表上。当操作了某张表时，希望同时触发一些动作/行为，可以使用触发器完成
create trigger 触发器名称
after/before[触发时间] insert/update/delete[监视事件] on 表名[监视地点]
for each row
begin
sql语句; [触发事件]
end;
当【监视地点】发生【监视事件】就在【触发时间】执行【触发事件】。触发器执行过程中有可能产生new表，old表（new是新插入的数据，old是原来的数据）
insert只会有new，代表着要插入的新记录
delete只会有old，代表着要删除的记录
update由于执行的是先删除旧的记录，再插入新的记录，因此new和old都会有
-- 创建触发器(添加)
CREATE TRIGGER 触发器名称 AFTER INSERT ON 监听表 FOR EACH ROW
begin
insert into 触发表 ( id, name)
values( new.userid, new.username); -- 当往监听表插入一条记录时，将nwe表中userid与username添加到触发表的id、name字段
end;
-- 创建触发器(修改)
CREATE TRIGGER 触发器名称 AFTER UPDATE ON 表 FOR EACH ROW 要触发的sql语句; -- 当往表修改一条记录时···
-- 创建触发器(删除)
CREATE TRIGGER 触发器名称 AFTER DELETE ON 表 FOR EACH ROW 要触发的sql语句; -- 当往表删除一条记录时···
-- 分配权限账户
GRANT 权限 ON 数据库.表 TO '用户名'@'账户类型'IDENTIFIED BY '密码';
-- 权限：SELECT 允许访问；DELETE 允许删除
备份
mysqldump –u root -p 数据库> c:/bak.sql
恢复
mysql –u root -p 数据库 < c:/back.sql
1289
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
