知识点梳理

数据库、表创建删除 DDL

增、删、改 DML

查询 DQL

  • 内联查询
  • 左右外联查询
  • 子查询

数据库权限管理 DCL

索引

事务

触发器

存储过程

数据库备份

DDL

数据库操作

  • 数据库创建

    create database [if not exists] db_name;
    
  • 数据库删除

    drop database [if not exists] db_nama;
    
  • 数据库使用

    use db_name;
    
  • 查看数据库结构

    show create database db_nama;
    desc db_nama;
    

表操作

  • 表字段常用数据类型

    数值:tinyint、int、double、decimal(高精度字符串型数值)

    字符串:char、varchar(可变字符串)、text(长文本类型)

    日期:time date datetime

    二进制:BLOB、LONGBLOB

    Null:代表未知值,并非空值。

  • 创建语句及字段属性

    #建表规范:
    #1.见表名即可知道表的含义
    #2.使用“_”分割连接两单词
    CREATE TABLE tb_name(
        #primary key 主键:唯一标识,且不能为空。
        #主键建立原则:
        #1.无意义性,采用与业务无关的单独列
        #2.尽量采用整形主键
        #3.减少主键变动
        #4.主键放在表的首列
        u_id int PRIMARY KEY,
        #auto_increment 自增
        c_id int AUTO_INCREMENT,
        #not null 不能为空,不添加默认可以为空
        u_name varchar(22) NOT NULL, 
        #default 设置默认值
        u_tel varchar(11) DEFAULT'无电话',
        #comment 提示
        u_addr varchar(225) COMMENT'地址',
        #外键约束 使此表中的key值只能为另一表中的key值。
        [CONSTRAINT constraint_name] FOREIGN KEY(c_id) REFERENCES tb2_name(c_id)
    ) [ENGINE = InnoDB] [AUTO_INCREMENT = 100003] [CHARACTER SET = utf8] [COLLATE = utf8_general_ci] [ROW_FORMAT = Dynamic];
    
  • 表的修改

    ALTER TABLE tb_name ADD col_name col_property #添加字段
    ALTER TABLE tb_name MODIFY col_name col_property #修改字段
    ALTER TABLE tb_name DROP col_name #删除字段
    ALTER TABLE tb_name CHANGE old_col_name new_col_name new_type #修改字段名称及数据类型
    ALTER TABLE old_tb_name RENAME new_tb_name #修改表名
    
  • 表的删除

    DROP TABLE tb_name;
    
  • 表结构的查看

    DESC tb_name;
    SHOW CREATE TABLE tb_name;
    

DML

  • #单条全字段插入,表结构见上面
    #此处value1为自增属性,可设为null默认自增。
    #此处value2有外键约束,只能插入另一表中有的数据。
    INSERT INTO tb_name values(2,3,"张三","13000000000","上海");
    #单条指定字段插入
    INSERT INTO tb_name(c_id,u_name,u_tel) values(3,"张三","13000000000");
    #多条全字段插入
    INSERT INTO tb_name values(2,3,"张三","13000000000","上海"),(3,4,"李四","18888888888","北京");
    #多条指定字段插入
    INSERT INTO tb_name(c_id,u_name,u_tel) values(3,"张三","13000000000"),(4,"李四","18888888888");
    
    • 根据条件删除数据

      DELETE FROM tb_name [WHERE 子句] [ORDER BY 子句] [LIMIT 子句];
      
    • 删除表中所有数据/清空表

      • delete

        DELETE FROM tb_name;
        
      • truncate

        TRUNCATE TABLE tb_name;
        

两者区别:

  • delete清空表中记录后,自增值如id仍可从删除点继续自增,而truncate会重置自增且表结构不变。
  • 效率上truncate更快,但不会被mysql日志记录,所以不能回复数据!
  • UPDATE tb_name SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
    [ORDER BY 子句] [LIMIT 子句]
    

DQL

MySQL查询语句模板

#内联和外联模版
select [all | distinct]
{*| table.*| table.field1 [as alias] [,table.field2 [as alias]] [,...]}
from table [as alias]
[{left|right|inner} join table [as alias] on ...]
[where ]
[group by ]
[having ]
[order by]
[limit n,m]
#全联模版
(select [all | distinct]
{*| table.*| table.field1 [as alias] [,table.field2 [as alias]] [,...]}
from table [as alias])
union
(select [all | distinct]
{*| table.*| table.field1 [as alias] [,table.field2 [as alias]] [,...]}
from table [as alias])
[where ]
[group by ]
[having ]
[order by]
[limit n,m]

普通查询 SELECT

  • 查询所有字段( “ * ” 为通配符)

    select * from tb_name;
    
  • 查询指定字段

    select field1 [,field2,field3,...] from tb_name;
    
  • 查询过滤重复数据

    select distinct field from tb_name;
    
    
  • 查询临时改变字段值

    select field1+5 from tb_name;
    
    
  • 查询自定字段别名

    select field1 as f1 from tb_name;
    
    

条件查询 WHERE

  • 范围

    select field1 from tb_name where 1 < field2 and field2 < 3;
    
    
  • 固定值

    select field1 from tb_name where field2 = 1;
    
    
  • select field1 from tb_name where field2 = 1 or field3 = 1; 
    
    

模糊查询 LIKE

查询名字中带有君的 
select * from students where name like '%君%';
查找名字叫张x的学生 
select * from students where name like '张_';
查找名字叫张xx的同学 
select * from students where name like '张__';
查找姓张的
select * from students where name like '%张_%';

排序查询 ORDER BY

#desc降序 asc升序
select * from tb_name order by field1 desc;

分组查询 GROUP BY

#where 在分组之前进行过滤,不能使用聚合函数_
#having 在分组之后进行过滤,可以使用聚合函数,having中用到的列,必须在前面使用过才可以用
select field1 from tb_name group by field2;

聚合函数

  • count:统计符合条件的记录的个数。
  • sum:返回对应列数据的总和。
  • max/min/avg:返回数据满足条件的列最大值、最小值、平均值。

关联查询

  • 笛卡尔积

    select * from ta,tb;
    
    
  • 内连接

    #笛卡尔积
    select * from ta a,tb b where a.id=b.id;
    #内连接
    select * from ta a join tb b on a.id=b.id;
    
    
  • 左外链接 [ 看齐左表,正在 ]

    select * from ta a left join tb b on a.id=b.id;
    
    
  • 右外连接 [ 看齐右表 ]

    select * from ta a left join tb b on a.id=b.id;
    
    
  • 全外链接

    #mysql不支持full join全连接
    #select * from ta a full join tb b on a.id=b.id;
    select * from ta left join tb on ta.tb_id = tb.id 
    union
    select * from ta right join tb on ta.tb_id = tb.id;
    
    

事务

MySQL事务处理只支持InnoDB和BDB数据表类型

事务处理概念

将一组sql放在同一批次内去执行,如果一条出错,则该批次内所有语句将被取消执行,且对数据库无影响。

ACID原则

  • 原子性

    要么完整执行,要么干脆不执行,且不执行对数据库无影响。

    原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚,因此事务的操作如果成功就必须要完全应用到数据库,如果操作失败则不能对数据库有任何影响。

  • 一致性

    有无事务,执行结果一致。

    一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态,也就是说一个事务执行之前和执行之后都必须处于一致性状态。   拿转账来说,假设用户A和用户B两者的钱加起来一共是5000,那么不管A和B之间如何转账,转几次账,事务结束后两个用户的钱相加起来应该还得是5000,这就是事务的一致性。

  • 隔离性

    事务与事务之间串行执行。

    隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。 即要达到这么一种效果:对于任意两个并发的事务T1和T2,在事务T1看来,T2要么在T1开始之前就已经结束,要么在T1结束之后才开始,这样每个事务都感觉不到有其他事务在并发地执行。

  • 持久性

    当事务执行完成,在数据库中永久保存。

    持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。   例如我们在使用JDBC操作数据库时,在提交事务方法后,提示用户事务操作完成,当我们程序执行完成直到看到提示后,就可以认定事务以及正确提交, 即使这时候数据库出现了问题,也必须要将我们的事务完全执行完成,否则就会造成我们看到提示事务处理完毕,但是数据库因为故障而没有执行事务的重大错误。

事务语法

SET AUTOCOMMIT = 0;  # 是否开启自动提交模式,0/1:关闭/开启。
# 事务代码块

# 事务结束
commit; # 提交操作,或者rollback回滚
SET AUTOCOMMIT = 1;

索引

作用

提高查询速度,确保数据的唯一性。

分类

  1. 主键索引(PRIMARY KEY)

    • 确保数据记录的唯一性和非空,并作为其他表中外键的参考。
    • 主键只能有一个,但是可以使用联合主键。
    create table tb(
    	id int(6) primary key,
    	#或者
    	#primary key(id)
    )
    
    
  2. 唯一索引(UNIQUE)

    索引列的值必须唯一,但允许为空。

    create table tb(
    	id int(6) unique,
    	#或者
    	#unique key(id)
    )
    
    
  3. 常规索引(INDEX/KEY)

    快速定位特定数据

    create table tb(
    	id int(6) unique,
    	username varchar(16) not null,
    	index [indexName] (username)
    )
    
    
  4. 全文索引(FULLTEXT)

    快速定位指定数据

    • 只能用于MyISAM类型的数据表
    • 只能用于text、char、varchar数据类列

注意

  • 对于那些在查询中很少使用或者参考的列不应该创建索引
  • 对于那 些只有很少数据值的列也不应该增加索引
  • 索引通常取值为int类型,text、image类型不应该添加索引。
  • 超过三个表禁止join,来自阿里巴巴开发手册。
  • 主键和唯一索引的区别

建表后添加索引

# 创建索引
CREATE INDEX indexName ON tb(username(length)); 
# 修改索引
ALTER table tableName ADD INDEX indexName(columnName)

视图

触发器

一)由insert,update, delete触发某种特定的操作

触发器是一种特殊的存储过程,它在插入,删除或修改特定表中的数据时触发执行,它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

二)触发器的作用:

1.安全性。可以基于数据库的值使用户具有操作数据库的某种权利。

可以基于时间限制用户的操作,例如不允许下班后和节假日修改数据库数据。
可以基于数据库中的数据限制用户的操作,例如不允许股票的价格的升幅一次超过10%。

2.审计。可以跟踪用户对数据库的操作。

审计用户操作数据库的语句。
把用户对数据库的更新写入审计表。

3.实现复杂的数据完整性规则

实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同,触发器可以引用列或数据库对象。例如,触发器可回退任何企图吃进超过自己保证金的期货。
提供可变的缺省值。

4.实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。例如,在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。

在修改或删除时级联修改或删除其它表中的与之匹配的行。
在修改或删除时把其它表中的与之匹配的行设成NULL值。
在修改或删除时把其它表中的与之匹配的行级联设成缺省值。
触发器能够拒绝或回退那些破坏相关完整性的变化,取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时,这种触发器会起作用。例如,可以在books.author_code 列上生成一个插入触发器,如果新值与auths.author_code列中的某值不匹配时,插入被回退。

5.同步实时地复制表中的数据。

6.自动计算数据值,如果数据的值达到了一定的要求,则进行特定的处理。例如,如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。

三)语法:

  1. 创建单条执行语句的触发器
create trigger 触发器名 before|after  触发事件
on 表名 for each row 执行语句

例子1:

CREATE DATABASE trigger_db;
USE trigger_db;
#部门表
CREATE TABLE department (
	id INT(5) PRIMARY KEY,
	NAME VARCHAR(20)
);
# 该表记录部门表插入数据的时间
CREATE TABLE depart_trigger_time(
	date_time DATE
);
#创建触发器
CREATE TRIGGER dept_trig BEFORE INSERT
ON department FOR EACH ROW
INSERT INTO depart_trigger_time VALUES(NOW());
#测试 插入数据
 INSERT INTO department VALUES (2,'技术部');
#查看,可以看到部门表插入数据,时间表中也插入操作的时间
SELECT * FROM department;
SELECT * FROM depart_trigger_time;

例子2:

CREATE TABLE note(
	id INT(5) AUTO_INCREMENT PRIMARY KEY,
	author VARCHAR(20) NOT NULL,
	title VARCHAR(20) NOT NULL
);
INSERT INTO note VALUES 
(NULL,'jack','晚上吃大餐'),
(NULL,'lily','新年愿望');

CREATE TABLE del_msg(
	TIME DATETIME,
	id INT(5),
	title VARCHAR(20),
	NAME VARCHAR(20)
);
#触发器   删除note,在msg中添加删除的时间,id,标题,作者
CREATE TRIGGER note_del_trigger BEFORE DELETE 
ON note
FOR EACH ROW 
INSERT INTO del_msg VALUES (NOW(),old.id,old.title,old.author);
#测试  删除,第一条
DELETE FROM note WHERE id = 1;
SELECT * FROM note;
SELECT * FROM del_msg;

  1. 创建多条执行语句的触发器
delimiter $
create trigger  触发器名字 before|after 触发事件
on 表名 for each row
begin
	执行语句
end
$ 
delimiter ;

例子:

CREATE DATABASE IF NOT EXISTS test_db;
USE test_db;
#创建message ,val不能为负数,也不能超过10
DROP TABLE IF EXISTS message;
CREATE TABLE message(
	id INT(5) AUTO_INCREMENT,
	TIME DATETIME,
	val DECIMAL(9,2) NOT NULL,
	PRIMARY KEY(id)
);
#创建触发器 ,当插入val为负数,把插入的值设为0,大于10设置为10
DELIMITER $ 
CREATE TRIGGER message_trigger BEFORE UPDATE
ON message FOR EACH ROW
BEGIN
	IF new.val<0 THEN
	SET new.val=0;
	ELSEIF new.val>10 THEN
	SET new.val=10;
	END IF;
END 
$
DELIMITER ;
#测试
INSERT INTO message VALUES (1,NOW(),3);
SELECT * FROM message;
UPDATE message SET val=-100 WHERE id=1;#设置为-100

存储过程

#模板
DELIMITER // #自定义结束符
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    BEGIN
    	[code]
    END //
DELIMITER ; #将结束符改回;

参数 proc_parameter: [ IN | OUT | INOUT ] param_name type

数据库备份

备份

mysqldump -h 主机名 -u 用户名 -p [options] 数据库名 [table1 table2 ...] > path/filename.sql

还原

#方式1
mysql -h ip -u 用户名 -p 数据库(先创建) < path/filename.sql
#方式2
mysql> source /path/filename.sql

InnDB支持事务,不支持全文索引

MyISAM支持全文索引,不支持事务