第一部分:【基础篇】2.2.1:SQL分类1)DDL(Data Definition Languages): 数据定义语言
常用语句:create,drop,alter 等2)DML(Data Manipulation Language):数据操作语句
常用语句:insert,delete,update和select等3)DCL(Data Control Language):数据控制语句,定义数据库,表,字段,用户的访问权限和安全级别
常用语句:grant,revoke
DDL:
tableName=emp
desc:查看表结构语句
例: desc emp;
alter:1)修改字段类型
alter table emp modify column ename varchar(20);2)新增字段:默认加在最后,可以使用alter,first 控制字段位置
alter table emp add column ageint(2)after ename;3)删除表字段
aler table emp drop column age;4)字段改名
alter table emp change age ageint(10);5)修改表明
alter table emp rename emp_new;
注意:change 和 modify 都可以修改表的定义,change可以修改列名,modify则不能;建议记住change用法:
DML:1.limit:结合order by 语句可以实现分页
select* from emp order by ename limit 2,3;2.表连接:内连接和外连接1)内连接:只是显示两张表匹配的内容;
select* from emp,dept where emp.deptId =dept.deptId2)外连接:会把未匹配的内容也显示出来
左连接:显示所有左边表的记录,甚至是右边表中没有和它匹配
右连接:显示所有右边表的记录,甚至是左边表中没有和它匹配
左:
select ename,deptname from emp left join dept on emp.deptId=dept.deptId;3)union,unionall 记录联合
select deptno from emp union select deptno from dept;
DCL:
用户名:z1,密码:123,数据库:sakila
赋予用户名在z1对数据库sakila下所有表都可以进行select,insert
grant select,insert on sakila.* to 'z1'@'localhost' identified by '123';
撤销用户民z1对数据库sakila下所有表的insert权限
revoke insert on sakila.* from 'z1'@'localhost';
帮助文档查看:
?contents"? 类型别名"例如查看数据库支持的数据类型:"? data types"
"? create table"查看数据库创建语句"? show"show命令都可以查看什么东东3.1数据类型
浮点数:float(M,D)(单精度) double(M,D)(双精度)
有精度误差:float f = 7.22f;float c = 7.0f;f - c = 0.21999979;
定点数:decimal(M,D) 适合用来表示货币等精度高的数据
M:代表一共显示M位数字;
D:代表小数位数,超过小数位会按照四舍五入保存3.2日期时间类型
DATE:年月日
TIME:时分秒
DATETIME:年月日时分秒
TIMESRAMP:如果需要经常插入日期或者更新当前日期使用;缺点:表示范围比较小(区分时区时间使用);
表中的第一个TIMESTAMP列自动设置为系统时间,如果在一个TIMESTAMP列中插入NULL,则该列值将自动设置为当前日期和时间3.3.1CHAR 和 VARCHAR类型
CHAR:定长字符(会自动删除字符串后面的空格)
VARCHAR:可变的字符,会根据实际字符长度进行调整;(保留原始字符,不做任何处理)
第二部分:【开发篇】1.MySQL存储引擎:MyISAM,InnoDB,BDB,MEMORY,MERGE,NDB Cluster,ARCHIVE,CSV ...
提供事物安全表:InnoDB,BDB;其他都为非事物
引擎优缺点:1)MyISAM:
优点:访问速度快
缺点:不支持事物,也不支持外键2)InnoDB:(mysql默认存储引擎)
优点:支持事物,支持外键
缺点:写的效率低,并且会占用更多的磁盘空间以保留数据和索引3)MEMORY: 基于内存来创建表
优点:读写速度快
缺点:不安全,数据量大小有限制(数据容易丢失)4)MEGER: 存储引擎是一组MyISAM表的组合,顾名思义就是对多个表进行整合8.2TEXT 与 BLOB(保存较大文本时需要使用)1)TEXT:只能存储字符,不能存储二进制2)BLOB:可以存储二进制;比如:照片,视频3)对大文本进行检索时,可以使用合成的(Synthetic)来提高文本的查询性能;(即:对文本内容建立散列码,并把该字段存储到数据表中,查询时通过散列码查询)
例:create table t(id varchar(100),context blob,hash_value varchar(40));
insert into t(1,repeat('bj',2),md5(context));
hash_value字段即为散列码;
注意:在使用blob,text类型进行频繁删除操作时,需要及时进行碎片清理;optimize table tableName;10.1索引的介绍1.索引的分类:1)btree2)hash3)r-tree(空间索引),一般很少用到4)fulltext(全文索引) 仅支持varchar, char,text类型2.存储引擎支持的索引:1)MyISAM存储引擎:btree(默认),fulltext,R-tree(空间索引)2)innodb存储引擎:btree(默认)3)memory存储引擎:hash(默认),btree3.索引创建语句
create index index_name on tablename (indexname);10.2索引的设计原则1)索引列要使用在条件查询列中(where 后面的列),不应该使用在select 列中2)索引唯一性,这样能提高索引效率(索引基数越大,效果越好)
例如:记录性别的列只有:M,F,对此列创建索引时不管搜索哪个值,都会得出大约一半的列,索引用处不大(基数小)3)索引简短性,较小的值减少磁盘io,较短的值比较更快,索引高速缓冲区能容纳更多的索引键值;
如果一个比较长的列要建立索引:要考虑是否可以建立前缀索引(要保证大多数的唯一性),以达到简短性
前缀索引:列类型为char(200),可以取前10到20个字符作为索引;4)不要过渡使用索引,过渡使用会造成性能问题;因为更新数据的同时,也要对索引维护更新;5)利用最左索引,最左前缀是用在复合索引上的;
例如:创建索引name_age,mysql会对name进行排序,name相等时进行age排序,即order by name,age原则;
语句1:select* from student where name = "wg" and age = 28;
语句2:select* from student where age = 28 and name = "wg";
最左索引说的就是条件查询时先使用最左边的索引字段,例如:语句1,但是语句1和语句2的查询效果是一样的,那是因为mysql优化器对sql进行了优化,语句2优化后就是语句1;10.3btree与hash索引比较
hash: 只能进行关键字查询,即=或<=>;优化器不能使用hash索引来加速order by 操作;
btree:适合like,between,>= 或<=操作时都能使用到索引
下列查询适合btree和hash
select* from key_col = 1 or key_col in(2,5,6) ;
只适合btree
select* from key_col >= 1 and key_col <= 6;
select* from key_col2 like 'txt%';14.1lock tables 与 unlock tables
当一个表被一个线程lock 时,其他线程可以进行select 操作,插入,修改操作会等待,直到锁表线程执行unlock tables;
当锁表线程执行另一个lock tables 或者关闭与服务器关闭连接时,会隐式解锁锁定的表
例子:
线程1:lock table emp read;
线程2:select*from emp; (可以查询)
线程2:insert into emp values('1111','2018-02-07',5000,2);(会等待)
线程2:update emp set ename= '2222' where ename = '2222';(会等待)
当线程1执行 unlock tables;或执行另一个lock table 时(包含 lock table emp),或线程关闭连接时, 线程2:执行插入更新操作;
例子:
线程1:lock table emp write;
线程2:select*from emp;(可以查询)
线程2:select* from emp where ename = '111';(会等待)
总结:读锁定会阻塞写操作,同理写操作会阻塞读操作14.2事物控制
mysql事物默认是自动提交的,即set autocommit= 1;1)通过设置set autocommit = 0;可以改变提交模式,之后所有的事物都需要明确的命令(commit)提交;2)如果只是某些语句需要手动提交,可以使用 start transaction 开启事物,执行sql操作...,commit;提交;
start transactio 语句会开启一个事物,会切换到手动提交模式;直到执行commit命令,会再次切换到自动提交模式;3)commit and chain;提交之后会再次开启一个事物;4)rollback 结合savepoint使用;