Mysql必知必会

标签：Mysql

1.mysql独有语句

mysql -u root-ppassword

show databases;

create database database1;

use database1;

select database();

select version();

show tables;

source ...

use table1;

desc table1;

select * from table1;//不建议，效率低；

select * from tablename join tablename on contidition where group by having order by desc limit//limit startindex，length;

show create table table1;

exit;

2.SQL规范→

2.0 不区分大小写;结尾，字符串建议用''；重命名方式：字段加 别名

2.1 DQL [数据查询语言]select XX，XX from //单表查询

2.2 DML[数据操纵语言] insert update delete

2.3 DDL[数据定义语言] create drop alter

2.4 TCL[Transaction control language] 事务控制语言 rollback，commit

2.5 DCL[data control language] 数据控制语言 grant和revoke

3.子句

3.1 where→null用is判断，其他用=；where后不可加上分组函数；

辅助：is，is not ，in，between and，like

3.2 order by→多字段排序，前优先极高

3.3 分组函数→count，max，min，avg，sum

特点：自动忽略null所在值，有null即为null→ifnull（字段名，想要的值）

count（*）→有多少行记录；count（字段名）→有多少该字段，忽略null

3.4 group by→分组

3.4.1 不写默认使用一张表的方式进行分组；

3.4.2 常常与分组函数联合使用，分组后使用分组函数，而分组在where执行后；

3.5 having子句→过滤

3.5.1 与group by连用

3.5.2 一般情况首选where子句，效率高，非必要不使用having

3.5.2 group by分组后select后面的字段必须是只有参与分组的字段和分组函数

3.5.3 多字段联合分组

3.6 select 顺序→select→5 from→1 where→2 group by→3 having→4 order by→6

3.7 去重→distinct

3.7.1 用于select后的字段，进行过滤；

3.7.2 distinct用于字段前表示所有字段都会过滤

3.7.3 distinct只能出现在select第一个字段，否则数量无法匹配

4. 多表查询

4.1 多表原因：减少冗余，但速度减少了

4.2 查询分类：

分类1：sql92和sql99；

分类2：内外全连接

内连接：等值连接、非等值连接、自连接

外连接：左外连接、右外连接

全连接：既左又右连接

4.3 笛卡尔积现象→两表查询无限制则数量为乘积

4.3.1 改进的查询方法即加条件的方法并不能减少匹配次数，因为匹配过程是撞击→测试

4.3.2 内连接

等值连接：连接条件是等号

select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

//表连接用on使得表的连接和结果过滤结构清晰

非等值连接：连接条件不是等号

内连接→自己查自己

思想：可以看成两张表，复制一份去一个别名

4.3.3 外连接

思想：在内连接地位平等的基础上引入主从表，即使附表字段值无法匹配主表也要返回主表查询结果，并代之以null；

分类：左连接、右连接

经验：外连接较为常用，符合实际场景

多表连接：思路同上

select e.ename,d.dname,s.grade

from emp e

left join dept d

 on e.deptno = d.deptno;

  //连接第二张表；

left join dept s

 on e.sal between s.loscal and s.hisal;

5. 子查询

5.1 select子查询→不常用

5.2 from子查询

5.3 where子查询

select * from emp where sal > (select avg(sal) from emp);

【注意点】在利用嵌套查询时，一定要把分组函数取别名

5.4 结果相加→union→用于连接两张不相干的表

select ename,job from emp where job ='salesman'

​union

select ename,job from emp where job ='manager';

6.DML语言→增删查改

6.1 insert插入数据

6.1.1 语法：insert into tablename(XX) values(XX);

//xx要匹配，可以写一部分→其他字段取null；

6.1.2 插入多行

insert into tablename  values(XX),values(XX2);

6.2 复制表

create table table2 as (select  ename,deptno from table1);

insert into name (select * from table);

6.3 update修改

用法：update table1 set 字段1=值1，字段2=值2 where 条件；

【注意】没有条件的话全局更新

6.4 delete删除

delete from table1 where 条件；

delete from table1；

【大表删除】truncate table name//表被截断，不可回滚

【delete删除原理】试卷用橡皮擦擦掉

7. 表结构的修改

7.1 增 create

create table tablename(字段名 类型，字段名 类型);

//类型：int，bigint，float，double，char→定长字符串→String，varcahr→不定长字符串→StringBuffer，date→日期类型，即java.sql.date，blob→二进制大对象，如图片、视频等，clob→字符大对象，较大的文本，大字符串；

//定长选char，否则选varchar

//表明以t_或者tbl_开头；

//char和varchar在建表中要加上限度

7.2 alter，drop，

7.3 一般实际开发中用工具即可，因为基本不会 表结构进行变动；

7.4 crud：create retrieve update delete

8. 约束

8.1 定义：给字段加上一些限制以保证数据安全、准确和有效性；

8.2 约束分类：

非空约束：不为空→not null

唯一约束：不重复→unique，此时可以为null，且null不是值！

主键约束：不空也不重复，并且唯一标识记录

     主键分类：单一【建议】、复合、自然【建议】、业务

     主键自增：自然约束的代表，加上auto_increment后自动对主键进行编号；

外键约束：多表连接字段，以避免冗余

     父子表：从a去找b，则a为子，b为父

     规范：新增操作要求先父后子，删除操作先子后父

     要求：子表外键字段必须是主键的唯一约束字段，外键值可为null；

     create table classtable(

cno int primary key,

    cname varchar(255)

);

create table studenttable(

       sno int primary key,

    sname varchar(200);

   foreigner key(classno) reference classtable(sno)

);//外键约束；

insert into calsstable values(1,'zhagnsan');

insert into calsstable values(200,'zhagnsan',1);

8.3 约束规范

表级约束：最后对某些/个字段加上约束【非空约束不支持】

列级约束：单个字段后加上约束【四个字段都支持】

9. 存储引擎

9.1 数据库的存储方式，不同引擎对应不同的存储方式

9.2 show crate table table1；→innodb default charset=utf-8；

9.3 完整建表语句：*  CREATE TABLE     `t_t1` (

   `id` bigint(20) DEFAULT NULL,

   `name` varchar(10) NOT NULL,

   `sex` char(1) DEFAULT '1',

   `classnum` int(11) DEFAULT NULL

   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

9.4 建表时可以指定存储引擎和字符集

9.5 mysql默认存储引擎是innoDB

9.6 show engines\G

9.7 常见引擎：

myisam：三张表【结构、数据、所索引】，不支持事务，可被压缩，效率高

innodb：安全、崩溃自动回恢复，支持事务，行级锁和外键；支持级联删除；

memory：快

10.事务

10.1 事务是一个完整的业务逻辑单元，不可再分，用于保证业务的同步；

10.2 只有insert delete update语句支持事务，保证数据的完整和安全

10.3 事务原理：

a.开始事务机制即相当于把接下来顶点命令放在缓存区，等到提交/回滚才会写入文件

b.要么都成功，要么都失败

10.4 commit→事务提交，rollback→事务回滚

10.5 mysql默认自动提交，写一句执行一句；

10.6 关闭事务：start transaction

10.7  事务特性→ACID

原子、一致性、隔离性、持久性【数据必须写入到硬盘才算成功】

10.8 隔离性

a.类似多线程并发

b.级别：

级别1：read uncommited→读缓冲区视图

级别2：read commited→读已提交，不可重复读；

级别3：repeated read→读开始的备份文件

级别4：serializable→串行，效率低；

11.索引

11.1 类似于目录，加快查找速度

11.2 创建和删除索引

create index 名 on 表名(字段);

drop index 索引名 on 表名；

11.3 索引添加时机：

数据量庞大，数据量有很少的DML操作，经常需要在where子句后查询该字段；

11.4 自动添加索引

主键和unique字段自动添加索引

11.5 命令

查看sql语句的执行计划：explain select ename,sal from table1 where sal = 6000；

11.6 底层原理：B+树，加快查找速度

11.7 索引分类：

单一索引

复合索引

主键索引

唯一索引

11.8 索引失效

有like模糊匹配时失效，无法精确匹配；

12. 视图→不同的表看待方式

12.1 创建视图

create view myview as（select * from emp）;

12.2 删除视图

drop view myview ;

12.3 对视图的操作会影响到原表的数据

12.4 要求：只有SQL语句才能以视图对象创建，对视图也可以CRUD;

12.5 作用：隐藏表的实现细节，对视图进行crud并不会提高效率。

13.DBA命令

13.1 在cmd执行，而不是mysql执行

13.2 导入→source

create database mydb；

* use mydb；

* source mydb.sql的路径;

13.3 导出→

* 导出整个表：mysqldump bjpowernode >D:\bjpowernode.sql -uroot -p333

* 导出指定库指定表：mysqldump bjpowernode  emp>D:\bjpowernode.sql -uroot -p333

14.数据库三范式

14.1 范式：设计表的规范/规则，不出现冗余

14.2 第一范式：表有唯一主键，并且各字段不可再分

14.3 第二范式：非主键字段必须完全依赖于主键，不可部分依赖→复合主键

14.4 第三范式：非主键必须直接依赖于主键，不可传递依赖

14.5 建表规范：

a.多对多的关系如何设计表？

三张表，关系表两个外键

b.一对多的关系如何设计表？

两张表，多的表加外键

c.一对一的关系如何设计表？

两张表，外键唯一/主键共享