MYSQL基本操作

数据库介绍

MYSQL

采用c/s的客户端服务器模型，客户端通过连接服务器进行数据的增删改查操作，服务端方便进行集群化处理，主从复制；

MYSQL服务端模型采用IO复用+线程池来实现；

DDL：数据定义语言

常用关键字：create，drop，alter等操作

DML：数据操作语言

常用关键字：insert，delete，update，select等

DCL（Data Control Language）：数据控制语言,用户控制不同数据段直接许可和访问级别的语句，定义库，表，字段，用户访问权限，安全级别

常用关键字：grant，revoke

数据库范式：

好处：

1. 减少数据冗余；（主要特点）

2. 消除异常；（插入异常，更新异常，删除异常）

3. 让数据库更加和谐；

范式介绍：

第一范式（1NF）：每一列保持原子特性，列是基本数据项，不能进行分割，否则要设置成一对多的关系 ；

注意：不符合第一范式不能称为关系型数据库

例子：学生（姓名，年龄，专业，地址，电话，邮箱，简介）

         Student(name  age  major addressID tel  email intro)

地址是可以分割，新建表进行 关联

  address（addressID  province  city town）

第二范式（2NF）：属性完全依赖于主键（码）===》消除部份依赖

非主属性完全依赖于主关键字，如果不依赖主键，应该拆分成新的主体，设计成一对多的关系

 

码（具有唯一性的属性），一个表里存在多个码，选取一个称为主键，其他为候选键

有多个主键，非主属性只依赖其中部分主键，不符合第二条，属于部份依赖。

 示例：选课关系表（学号，姓名，年龄，课程名称，成绩，学分）

主键：学号 ---》联合主键：（学号，课程名称）  

   学分和学号不存在依赖关系，不满足第二范式，设计成一对多的关系

拆分：课程（）选课关系（）

第三范式（3NF）：属性不依赖于其他非主属性 ---》消除传递依赖

要求一个数据表中的不包含已在其他表中已包含的非主关键属性

示例：Student(学号 姓名   所在学院  学院地点   学院电话)

主键：学号，所在学院依赖于学号，但学院地点和电话依赖于所在学院，属于传递依赖，不符合第三范式，应该拆分成学生表和学院表。

学生（学号  姓名  学院）

学院（学院号  学院地点  学院电话）

注意：

范式越高，拆分的表会越多，表越多带来的问题是：

1. 查询时需要连接的多个表，增加了查询的复杂度；

2. 查询时需要连接多个表，降低了查询的性能；

基本数据类型

整型：

浮点型与定点数：

时间日期：

字符串类型：char,varchar,Text,Enum,Set等类型。

二进制类型：

SQL语句

库操作：

连接数据库：

命令：mysql -uXXX -pXXXX

MySQL  代表客户端命令  -u 后跟用户名   -p 后跟密码

断开连接：exit；

查看数据库：show databases;

创建数据库：create database 库名；

选择数据库：use 数据库名；

查看数据下的表：show tables;

删除数据库：drop 库名；

表操作

1、创建表

create table table_name(

属性名 类型 [完整性约束],

属性名 类型 [完整性约束],

...

属性名 类型[完整性约束]

);

注意：创建表的时候选取合适的数据类型

给定字段完整性约束，比如：主键、外键、非空

-------------------------------------

约束条件       |   说明

PRIMARY KEY   |修饰的属性是该表的主键

FOREIGN KEY   |修饰的属性为该表的外键（另一个表的主键，两个表的连接）

NOT NULL      |表示该属性不能为空

UNIQUE        |表示该属性的值具有唯一性

AUTO_INCREMENT|表示该属性的值是自增的，整数类型（MYSQL特色）

 

DEFAULT       |给属性设置默认值

-------------------------------------

eg:学生表

   Student(SID,Sname,Sage,Ssex)

   --SID 学生编号,Sname 学生姓名,Sage 年龄,Ssex 学生性别

  create table Student(

  SID INT primary key unique,

  Sname varchar(25) not null,

  Sage  INT,

  Ssex  char(4) default 'nan'

  );

2、查看表

2.1、desc table_name;  

通过desc关键字查看表的字段名称、类型、是否为null、约束条件，默认值、备注信息

2.2、show create table table_name;

  展示创建表的SQL语句，并且打印了存储引擎以及字符集编码（字符集编码默认拉丁码，使用中文改成"utf-8"）

3、删除表

drop table table_name;

4、插入数据

insert into table_name(属性1、属性2...属性n) values

(v1,v2...vn);  

eg:insert into Student(SID,Sname,Sage,Ssex) values

(1,'zhan san',12,'nan');

insert into Student(SID,Sname,Sage,Ssex) values

(1,' ',4,' ');

5、删除数据

delete from table_name [where 属性名 = values];

6、修改表

在使用过程中不满足使用情况下修改，用alter命令修改

6.1、修改表名

alter table  old_table_name RENAME new_table_name;

eg:alter table Student rename gdz;

6.2、修改属性类型  

alter table table_name MODIFY 属性名 属性类型;

eg:alter table Student modify Ssex varchar(4);

6.3、修改属性名

alter table table_name CHANGE 旧属性名 新属性名 数据类型;

eg:alter table Student change Ssex Stest varchar(4);

6.4、增加字段

alter table table_name ADD 属性名 属性类型 [完整性约束];

eg:alter table Student add Stest varchar(10) default 'hello';

6.5、删除字段

alter table table_name DROP 属性名;

eg:alter table Student drop Stest;

6.6、修改属性排列位置

alter table table_name MODIFY 属性名 属性类型 FIRST; //将当前属性放到第一个位置

alter table table_name MODIFY 属性名1 属性类型 AFTER 属性名2;// 属性1排列在属性2后面

eg:alter table Student modify Ssex varchar(4) after Sname;

6.7、修改表的存储引擎

alter table table_name ENGINE = INNODB|MYISAM;

7.查询表

select 属性列表 from 表名 [where 条件表达式1]

[group by 属性名1[having 条件表达式]]

[order by 属性名2 [asc | desc]]

7.1、带in子查询

[not] in (元素1 ，元素2... 元素3);

eg:select * from Student where SID in(2,3,4);

7.2、带between and 的范围查询

[not]between 取值1 and 取值2; 

eg:select * from Student where Sage between 20 and 21;

7.3、带like的通配符匹配字符串

[not] like '字符串';

通配符：

%:表示0个或者任意长度的字符串

_:只能表示1个字符

eg:select * from Student where Sname like '%l%';

     select * from Student where Sname like '%l';  //以l结尾的名字

    select * from Student where Sname like '_l%';//第二个字符以l结尾的名字

7.4、空值查询

is [not] null;

eg:select * from Student  where Sname is not null;

7.5、带and的多条件查询

条件表达式1 and 条件表达式2 [ ... and 条件表达式2]

eg:select * from Student where SID >2 and Ssex='nv';

7.6、带or的多条件查询

条件表达式1 or 条件表达式2 [ ... or 条件表达式2]

eg:select * from Student where SID > 2 or Ssex='nv';

7.7、去重复查询 

select distinct 属性名

eg:select distinct Ssex from Student;

7.8、用group by 属性名[having 表达式];

select * from Student group by Ssex;

7.9、对结果排序

order by 属性名[asc | desc];

asc:升序排列，数据越来越大，默认是升序

desc:降序排列，数据越来越小

eg:select * from Student order by Sage asc;

7.10、union 联合查询

select 属性列表 from table_name1 [where 条件表达式] union

select 属性列表 from table_name2 [where 条件表达式]

联合查询默认的结果数据进行了去重，distinct 使用All 可以展示不去重的结果

eg:select SID from Student union all  select SID from SC;

7.11、limit 分页查询

select * from Student limit 90 ;一个参数指从起始0号位置开始读取指定长度的数据

 select * from Student limit200,9 ; 第一个位置值得是起始位置，第二个参数指的是读取数据的长度（个数）

7.12、联合查询

示例：有两个表（学生信息表Student和成绩表SC）

Student:

+-----+----------+------+------+

| SID | Sname    | Ssex | Sage |

+-----+----------+------+------+

|   1 | zhaolei  | nan  |   20 |

|   2 | qiandian | nan  |   20 |

|   3 | sunfen   | nan  |   21 |

|   4 | wulan    | nv   |   18 |

+-----+----------+------+------+

SC:

+------+------+-------+

| SID  | CID  | score |

+------+------+-------+

|    1 |    1 |    80 |

|    1 |    3 |    87 |

|    2 |    2 |    89 |

|    6 |    1 |    66 |

+------+------+-------+

左连接：select * from Student a left join SC b on a.SID = b.SID;

左连接：返回左边表的所有数据以及右边表和左边表相同的数据

+-----+----------+------+------+------+------+-------+

| SID | Sname    | Ssex | Sage | SID  | CID  | score |

+-----+----------+------+------+------+------+-------+

|   1 | zhaolei  | nan  |   20 |    1 |    1 |    80 |

|   1 | zhaolei  | nan  |   20 |    1 |    3 |    87 |

|   2 | qiandian | nan  |   20 |    2 |    2 |    89 |

|   3 | sunfen   | nan  |   21 | NULL | NULL |  NULL |

|   4 | wulan    | nv   |   18 | NULL | NULL |  NULL |

+-----+----------+------+------+------+------+-------+

内连接：select * from Student a inner join SC b on a.SID = b.SID;

内连接：返回的是左右表都同时存在的数据

+-----+----------+------+------+------+------+-------+

| SID | Sname    | Ssex | Sage | SID  | CID  | score |

+-----+----------+------+------+------+------+-------+

|   1 | zhaolei  | nan  |   20 |    1 |    1 |    80 |

|   1 | zhaolei  | nan  |   20 |    1 |    3 |    87 |

|   2 | qiandian | nan  |   20 |    2 |    2 |    89 |

+-----+----------+------+------+------+------+-------+

右连接：select * from Student a right join SC b on a.SID = b.SID;

右连接：返回的是右边表里所有的数据以及左边和右边相同的数据

+------+----------+------+------+------+------+-------+

| SID  | Sname    | Ssex | Sage | SID  | CID  | score |

+------+----------+------+------+------+------+-------+

|    1 | zhaolei  | nan  |   20 |    1 |    1 |    80 |

|    1 | zhaolei  | nan  |   20 |    1 |    3 |    87 |

|    2 | qiandian | nan  |   20 |    2 |    2 |    89 |

| NULL | NULL     | NULL | NULL |    6 |    1 |    66 |

+------+----------+------+------+------+------+-------+

别名

表的别名  table_name 别名

属性的别名 属性 [as] 别名

内连接：

select a.属性,...,a.属性,b.属性,...,b.属性 from table_name1 a ,table_name2 b on a.id = b.id where a.属性名 | b.属性名 满足条件

select a.属性,...,a.属性,b.属性,...,b.属性 from table_name1 a inner join table_name2 b on a.id = b.id where a.属性名 | b.属性名 满足条件

打印已存在的学生已有成绩的信息，包括：学号、姓名、成绩

eg:select a.SID,a.Sname,b.score from Student a inner join SC b on a.SID = b.SID;

外链接：

[左外连接]

select a.属性,...,a.属性,b.属性,...,b.属性 from table_name1 a left join table_name2 b on a.id = b.id where a.属性名 | b.属性名 满足条件

[右外连接]

select a.属性,...,a.属性,b.属性,...,b.属性 from table_name1 a right join table_name2 b on a.id = b.id where a.属性名 | b.属性名 满足条件

eg:select * from Student where SID in(select SID from Student where Sage >20);

问题1：distinct 和group by 区别？

distinct只是将重复的行从结果中出去； group by是按指定的列分组，一般这时在select中会用到聚合函数。

distinct是把不同的记录显示出来。 group by是在查询时先把纪录按照类别分出来再查询。

group by 必须在查询结果中包含一个聚集函数，而distinct不用。

问题2：连表查询及子表查询那个效率高？

事实上，子表查询（in）效率低，由于生成了一个中间表，中间表的创建和销毁都需要时间，（生成子表的过程可以通过explain语句查看），所以连接查询（join）的效率较高。