MySQL连接
1:主机IP
2:主机PORT
3:数据库用户及密码
4:使用MySQL不需要申请表空间,但需要schema
linux通过MySQL客户端连接数据库
mysql -h127.0.0.1 -P3316 -umysql -pmysql
schema
1:亦可以叫做database,库、数据库,是MySQL中组织和管理表的一个结构。
2:进行创建表及相关curd操作,必须先有schema
3:MySQL默认包含4个schema,test用户可以操作,其余3个系统使用
MySQL使用
1:创建数据库 create database 数据库名;
2:查看数据库 show databases;
3:选择数据库 use 数据库名;
4:删除数据库 drop database 数据库名;
5:查看建表语句 show create table 表名;
6:mysql支持标准SQL
MySQL基本操作
1:查看表结构 desc tablename
2:复制表 create table 新表名 like 源表名
3:删除表 drop table 表名1[,表名2...]
4:修改表 alter table 表名 更改动作1[,更改动作2...]
-- alter table student add addr(25) --增加表字段
-- alter table student changes addr text --更改表字段名和字段类型
-- alter table student drop addr --删除表字段
-- alter table student rename to stu --更改表名称
limit
MySQL使用limit n,m分页,limit位于select整个查询语句之后
limit n,m 表示从第n行开始取m行数据,n可省略,默认从0开始
事务控制
1:设置事务提交方式
set autocommit = 0|1 --自动提交:1(mysql默认自动提交) --自行控制提交:0
2:开始事务 start transaction|begin
3:提交事务 commit
4:回滚事务 rollback
MySQL存储引擎
1:MySQL支持多种存储引擎,在处理不同的应用时,可以通过选择不同的存储引擎来提高应用效率,灵活存储
2:MySQL存储引擎包括:InnoDB、MyISAM、BDB、MEMORY、等,其中只有InnoDB和BDB提供事务安全表,其它存储
引擎都是非事务安全表
3:创建表时可以指定表使用的存储引擎和字符集
-- create table mydb(id int,name char(20)) engine=InnoDB charset=utf8
4:InnoDB存储引擎
-- MySQL默认存储引擎
-- 支持事务
-- 行锁设计,提供高并发性
-- 支持外键,确保数据完整
-- 独立的ibd文件存储表结构和数据
-- 支持全文索引
适用场景:强调可靠性和需要支持事务的处理
5:MyISAM存储引擎
-- 不支持事务(指的是对数据不能进行回滚这些,并不是部分成功部分失败,数据库本身会保证原子性)
-- 表锁设计
-- 不支持外键
-- 支持全文索引
-- 使用数据压缩和索引优化
-- 特点:可靠性高、适用范围广、检索速度快
适用场景:强调数据检索速度,读写并发不多场景,写少读多
MySQL支持的基本数据类型
-- Numeric类型
-- data and time 类型
-- String类型
选择原则:根据选定的存储引擎,确定合适的数据类型
1:按照存储引擎选择数据类型
InnoDB存储引擎和数据列,建议使用varchar类型
--对于InnoDB数据表,内部的行存储格式没有区分固定长度和可变长度列(所有的数据行都指向数据列值
的头指针)因此在本质上,使用固定长度的char列不一定比使用可变长度varchar列简单。因而,主要
性能的因素是数据行使用的存储总量。由于char占用的平均空间多余varchar,因此使用varchar来最
化需要处理的数据行。
5:MyISAM存储引擎和数据列
-- MyISAM数据表,最好使用固定长度的数据列代替可变长度的数据列
2:数据类型选择
1:数字型,字符串
优先使用数字类型,占用空间少,处理速度快
2:优先使用ENUM或SET
字符串,固定选项值
-- `sex` enum('F','M') comment '性别'
3:避免使用null字段
-- 很难进行查询优化
-- null列加索引,需要额外空间,需要避免
-- 含null复合索引无效
4:少用并拆分text/blob
-- 很难进行查询优化
-- 强制生成硬盘临时表,浪费更多空间
-- 若必须使用则拆分成单独的表
MySQL的索引
1:MySQL表所有列都可以建索引
2:在MySQL5.6中,对于InnoDB表,整字段索引最大支持767字节,前缀最大可以支持3072字节长。
索引的建立
1:搜索的所以列,不一定是要选择的列,最适合索引的列是出现在where字句中的列
2:使用唯一索引,考虑某列中值的分布
3:使用短索引,如果对串列进行索引,应该指定一个前缀长度。
4:使用利用最左前缀
SQL语句优化
1:sql要尽可能简单,复杂sql要尽量拆成小的sql
2:事务要简单,整个事务的时间长度不要太长
3:避免使用触发器、函数、存储过程
4:降低业务耦合度,业务各自使用独立的库、表
5:避免在数据库中进行数字运算
6:不要用select*,查询那几个字段就select这几个字段即可
7:sql中使用or的改写为in(),因为or的效率没有in高,in里边的数字个数建议控制1000以内
8:limit分页注意效率,limit越大,效率越低
9:使用union all 代替 union
10:避免使用大表的join
11:使用group by 分组排序
12:在sql语句中,尽量避免前缀是%的like
13:SQL语句中不可以出现隐式转换,比如select id from stu where id='1' 会把id隐式转换为字符
Oracle与MySQL的一些差异
1:自动增长的数据类型处理
MySQL有自动增长数据类型,插入数据时不用操作此字段,会自动获得数据值。
Oracle没有此类型
MySQL自增列的定义:
CREATE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (c1)
) ENGINE=InnoDB;
默认自增长从1开始增长;
2:单引号处理
MySQL里可以用单引号、双引号包起字符串,Oracle里只可以用单引号包起字符串。
在插入和修改字符串前必须做单引号的替换:把所有出现的一个单引号替换成两个单引号。
3:翻页的sql语句处理
MySQL处理翻页的SQL语句比较简单,用LIMIT 开始位置, 记录个数;Oracle处理翻页的SQL语句就比较
繁琐了。每个结果集只有一个ROWNUM字段标明它的位置。
-- MySQL:select * from test limit 1,10;
4:日期字段处理
MySQL日期时间字段分DATE、DATETIME、TIMESTAMP、TIME、YEAR,
Oracle日期字段只有DATE,包含年月日时分秒信息,用当前数据库的系统时间为SYSDATE, 精确到秒,
或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’) 年-月-日 24小时:分钟:秒
的格式YYYY-MM-DD HH24:MI:SS TO_DATE()日期型字段转换成字符串函数TO_CHAR(‘2001-08-01’,’YYYY-MM-DD
HH24:MI:SS’) 日期字段的数学运算公式有很大的不同。MySQL找到离当前时间7天用 DATE_FIELD_NAME > SUBDATE((NOW(),INTERVAL 7
DAY)Oracle找到离当前时间7天用 DATE_FIELD_NAME >SYSDATE - 7;
5:空字符的处理
MySQL的非空字段也有空的内容,Oracle里定义了非空字段就不容许有空的内容。
按MySQL的NOT NULL来定义Oracle表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断,
如果为NULL或空字符,需要把它改成一个空格的字符串。