MySQL基础总结

• mysql中的操作语句不区分大小写
• 创建数据库
  • CREATE DATABASE IF NOT EXISTS test01
• 删除数据库
  • CREATE DATABASE IF EXISTS test01
• 使用数据库
  • USE test02，
  • 如果你的表名或者字段名是特殊字符
    • USE `user`
• 查看数据库
  • SHOW DATABASES ---查看所有数据库

 

• 数据库的列类型
  • 数值
    • tinyint 十分小的数据 1个字节
    • smallint 较小的数据 2个字节
    • mediumint 中等大小的数据 3个字节
    • int 标准的整数 4个字节 常用
    • bigint 较大的数据 8个字节
    • float 浮点数 4字节
    • double 浮点数 8字节
    • decimal 字符串形式的浮点数 涉及精度问题用它
  • 字符串
    • char 固定大小的字符串 0-255
    • varchar 可变字符串 0-65535
    • tinytext 微型文本 2^8-1
    • text 文本串 2^16-1
  • 时间日期
    • date YYYY-MM-DD, 日期
    • time： HH：mm:ss 时间格式
    • datetime YYYY-MM-DD HHmm:ss
    • timestamp 时间戳， 1970.1.1 到现在的毫秒数 唯一
    • year 年份表示
  • null
    • 没有值，未知
    • 不要使用NULL进行运算，结果为NULL，没有任何意义

 

• 数据库的字段属性
  • Unsigned:
    • 无符号的整数
    • 该列不能声明为负数
  • zerofill：
    • 0 填充的
    • 不足的位数，使用0来填充
      • int(3), 5 --- 005
  • 自增
    • 自动在上一条记录的基础上+1（默认）
    • 通常用来设计唯一的逐渐 ，必须是整数类型
    • 可以自定义设置起始值，以及步长
  • 非空
    • NULL
    • not null
      • 如果设置为not null ,如果不给赋值，就会报错。
      • null: 如果不填写值，默认就是null
  • 默认：
    • 设置默认的值

 

• 创建数据表

    CREATE TABLE IF NOT EXISTS `student` (
        `id`  INT(4)  NOT NULL AUTO_INCREMENT  COMMENT  '学号',
        `name` VARCHAR(30)  NOT NULL DEFAULT '匿名'  COMMENT '姓名',
        `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
        `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
        `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
        PRTMARY KEY(`id`)
    )ENGINE=INNODB DEFAULT CHARSET=utf8

• 格式

CREATE TABLE [IF NOT EXISTS] `表名` ( `字段名` 列类型 [属性] [索引] [注释], ... `字段名` 列类型 [属性] [索引] [注释] )[表类型][字符集设置][注释]

 

• 查看创建数据库的语句
  • SHOW CREATE DATABASE school
• 查看创建表的语句
  • SHOW CREATE TABLE student
• 查看表的具体结构
  • DESC student

 

• 数据表的类型
  • InnoDB mysql默认使用
    • 事务支持 支持
    • 数据行锁定 支持
    • 外键约束 支持
    • 全文索引 不支持
    • 表空间大小 较大 约为MyISAM的两倍
    • 安全性高，支持事务的处理，多表多用户操作
  • MyISAM
    • 事务支持 不支持
    • 数据行锁定 不支持
    • 外键约束 不支持
    • 全文索引 支持
    • 表空间大小 较小
    • 节约空间，速度较快

 

• 所有的数据文件都存在data目录下，本质还是文件的存储，一个文件夹就对应一个数据库
• MySQL引擎在物理文件上的区别
  • InnoDB 在数据表中只有一个*.frm文件，以及上级目录下的 ibdata1文件
  • MyISAM 对应的文件
    • *.frm -表结构的定义的文件
    • *.MYD 数据文件（data）
    • *.MYI 索引文件（index）

 

• 设置数据库表的字符集编码
  • CHARSET=utf8
  • 不设置的话，会是默认的字符集编码，不支持中文
  • 或者在my.ini文件中加上
    • character-set-server=utf8

 

• 修改表名
  • ALTER TABLE teacher RENAME AS teacher1
• 增加表的字段
  • ALTER TABLE teacher1 ADD age INT(11)
• 修改表的字段
  • 重命名
    • ALTER TABLE teacher1 CHANGE age age1 INT(10)
  • 修改约束
    • ALTER TABLE teacher1 MODIFY age VARCHAR(5)
  • change 用来字段重命名，不能修改字段类型和约束
  • modify不能用来字段重命名，只能修改字段类型和约束
• 删除表的字段
  • ALTER TABLE teacher1 DROP age1
• 删除表
  • DROP TABLE IF EXISITS teacher1

 

• MySQL数据管理
  • 外键
    • FOREIGN KEY （`id`) REFERENCES
    • ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY （作为外键的列) REFERENCES 那个表（哪个字段)
  • DML语言
    • insert
    • update
    • delete
  • 添加
    • INSERT INTO 表名（[字段名1,字段名2,字段名3]）VALUES('值1'，'值2','值3')
  • 修改
    • UPDATE 表名 SET 原来的值 = 新值 WHERE 条件
  • 删除
    • DELETE FROM 表名 WHERE 条件
  • 清空一个数据库表，表的结构和索引约束不变
    • TRUNCATE 表名
      • TRUNCATE 重新设置自增列 计数器归零
      • TRUNCATE 不会影响事务

 

• DQL 数据查询语言
  • 拼接字符串 Concat（a,b)
    • SELECT CONCAT('姓名：' , studentName) AS 新名字 FROM student
  • 去重复 DISTINCT
    • SELECT DISTINCT studnetNo FROM result
  • 查询系统版本
    • SELECT VERSION()
  • 模糊查询
    • like 结合 %（代表0到任意字符) _ (一个字符）
  • 连接查询
    • inner join
      • 如果表中至少有一个匹配就返回行
    • left join
      • 会从左表中返回所有的值，即使右表中没有匹配
    • right join
      • 会从右表中返回所有的值，即使左表中没有匹配
  • 分页 limit
    •  
  • 排序 order by
    • 升序 ASC
    • 降序 DESC
  • 数值函数
    • 绝对值 ABS
    • 向上取整 CEILING(9.4)
    • 向下取整 FLOOR()
    • 返回一个0~1之间的随机数 RAND()
    • SIGN(10) 判断一个数的符号 负数返回-1 正数返回1
  • 字符串函数
    • CHAR_LENGTH 字符串长度
    • CONCAT 拼接字符串
    • INSERT 替换字符串
    • LOWER 转小写字母
    • UPPER 转大写字母
    • INSTR 返回第一次出现的字串的索引
    • REPLACE 替换出现的指定字符串
    •  
• 事务Transaction
  • 原子性
    • 要么都成功，要么都失败
  • 一致性
    • 事务前后的数据完整性要保持一致
  • 隔离性
    • 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其它事务的操作数据所干预，事务之间要隔离
  • 持久性
    • 事务一旦提交则不可逆，被之持久化滑到数据库中
  • 脏读
    • 指一个事务读取了另一个事务未提交的数据
  • 不可重复读
    • 在一个事务内读取表中的某一行数据，多次读取结果不同。（这不一定是错误的，只是某些场合不对）
  • 幻读
    • 是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
• mysql 默认开启事务自动提交的
  • SET autocommit = 0； // 关闭事务
  • 事务开启
    • START TRANSACTION
    • COMMIT // 提交
    • ROLLBACK // 回滚
• 分析SQL执行的状态
  • EXPLAIN
• 增加一个全文索引
  • ALTER TABLE school.student ADD FULLTEXT INDEX `studentName` (`studentName`);
• 显示索引信息
  • SHOW INDEX FROM student
• 创建索引
  • CREATE INDEX 索引名 ON 表（字段）
  • 索引在小数量的时候，没多大区别，适用于大数据量，区别就出来了
• 索引原则
  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表不需要加索引
  • 索引一般加在常用来查询的字段上

 

• MYSQL备份
  • Mysqldump
    • mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘中的位置/文件名

 

• 数据库的三大范式
  • 第一范式：保证每一列都不可再分
  • 第二范式：先满足第一范式的前提下，每张表只描述一件事情
  • 第三范式：满足第二范式的前提下，却表数据表中的每一列数据都和主键直接相关，而不能间接相关
• 规范性 和 性能的问题
  • 关联查询的表 不得超过三张表
    • 考虑商业化的需求和目标，成本，用户体验 ，数据库的性能更加重要
    • 在规范性能问题的时候，需要适当的考虑一下规范性
    • 故意给某些表增加一些冗余的字段，从多表查询中变为单表查询
    • 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）