MYSQL学习笔记（持更中）

MySQL学习笔记

一 SQL语言

1.1SQL概述

SQL是一个综合的、通用的、功能强大的关系数据库语言，它具有四个方面的功能：数据定义、数据查询、数据操纵、数据控制。

SQL的特点：

1. 综合统一
   集DDL、DML、DCL功能于一体。
   可以独立完成数据库生命中的全部活动
   （1）建立数据库、定义关系与关系模式、插入数据；
   （2）对数据库中的数据进行查询和更新；
   （3）数据库重构和维护；
   （4）数据库安全性、完整性控制等；
2. 高度非过程化
   用户只需提出做什么，无需告知怎么做，不必了解存储路径，存储路径的选择由系统自动完成
3. 面向集合的操作方式
4. 语言简洁，易学易用，SQL完成核心功能只用9个动词

SQL功能	操作符
数据查询	select
数据定义	create、drop、alter
数据操纵	insert、update、delete
数据控制	grant、revoke

1.2数据定义

	创建	删除	修改
表	create table	drop table	alter table
视图	create view	drop veiw
索引	create index	drop index
数据库	create database	drop database	alter database

1. 创建数据库
   create database 数据库名；

create database test

2. 打开数据库
   use 数据库名

use test

在指定数据库中建表或者进行其他操作时，得先打开这个数据库，才能在进行操作

3. 删除数据库
   drop database 数据库名

drop database test

注意：不能删除一个打开了的数据库

4. 数据类型

数据类型	大小	范围
TINYINT	1byte	0~255
SMALLINT	2byte	-2^15 ~2^15-1
INT	4byte	-2^31 ~2^31-1
BIGINT	8yte	-2^63 ~2^63-1
REAL	4byte	单精度浮点型 -3.40E+38~3.40E+38
FLOAT	8byte	双精度浮点型 -1.79E+308~1.79E+308

其他数据类型：

• decimal（p,s）:p表示可以存储的最大十进制位数，s表示小数点右边可以存储的最大十进制位数，如decimal（6，3）表示的是-999.999~999.999之间的数。
• char（n）：长度固定为n字符的字符串类型，其中n最大为8000，如果用户定义的数据类型为char（5），而给的值是’abc’,则系统自动在后面补空格。
• varchar（n）：长度最大为n字符的变长字符串类型。
• smalldatetime：4byte，从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。
• datetime：8byte，从1753年1月1日到9999年12月31日的日期和时间数据，精确到百分之三秒。

5. 基本表的建立

use test
create table student
(
sname varchar(30) not null,         /*姓名*/
sno char(20) constraint PK_student_sno primary key, /*学号*/
sex char(2),     /*性别*/
sage smallint,     /*年龄*/
sdept varchar(10)    /*所属院系*/
);

create table course
(
cname varchar(30) not null,         /*课程名*/
cno char(20) constraint PK_student_sno primary key, /*课程号*/
cpno char(20),     /*先修课*/
ccredit float     /*学分*/
);

create table sc
(
cno char(20) not null,         /*课程号*/
sno char(20)  /*学号*/
grade float   /*成绩*/
);

属性姓名后面的not null表示该属性值不为空，若没有加not null则默认为null，可以为空。学号属性被设置为名为PK_student_sno的主码，设置主键是数据库完整性约束的一种，常用完整性约束及命名规则如下：

• 主码约束 命名：constraint PK_表名_属性名 primary key
• 唯一性约束 命名：constraint UQ_表名_属性名 unique
• 默认值约束 命名：constraint DF_表名_属性名 default(值)
• 外键约束 命名：constraint FK_表名_属性名 foreign key references 表名（属性名）
• check约束 命名：constraint CK_表名_属性名 check(规则)

6. 基本表的修改
   (1)add方式：用于增加新列和完整性约束。
   (2)alter方式：只能用于修改列的定义，不能用来修改约束，约束只能增减或者删除。
   (3)drop方式：用于删除某些列或者约束

例：用add方式增加home属性到表student中，把表student中的sage设置为默认值25，表sc中的sno设置为外键，设定表sc中的属性grade只能在0到100之间。

alter table student add home varchar(50) null
alter table student add constraint DF_student_sage default(25) for sage
alter table sc add constraint FK_sc_sno foreign key(sno)references student(sno)
alter table sc add constraint CK_sc_grade check(grade>=0 and grade<=100)
/*上面一行代码的另外一种表示：alter table sc add constraint CK_sc_grade check(grade between 0 and 100) */

例：用alter方式把表student中的sdept属性，由原来的10拓宽到40，把表student中的sex设置为不许有空值

alter table student alter column sdept varchar(50)
alter table student alter column sex not null

例：用drop方式删除表student中的sdept属性，删除sc表中属性sno的外码约束

alter table student drop column sdept
alter table sc drop constraint FK_sc_sno

7. 删除基本表
   drop table 表名
   注意：在sqlserver中删除基本表后，基本表的定义、表中数据、索引、约束都将被删除。由此表导出的视图仍然存在，但用户引用会出错。如果有另外一个表的外码参照这个基本表的主码，则不允许删除这个表。

8. 索引的建立和删除
   索引是数据库随机检索的常用手段，它实际上就是记录关键字与其物理地址的对应关系，建立索引的目的是为了加快查询速度。索引的种类：

• 聚集索引：记录的物理顺序要按照索引项的顺序重排，且一个表只能有一个。
• 非聚集索引：记录的物理顺序不按照索引项的顺序重排。
• 唯一索引：每一个索引值只对应唯一的数据记录。

例：为student按学号sno升序建立唯一索引

create unique index ID_sno on student(sno ASC);

例：为course按课程号cno降序建立唯一索引

create unique index ID_cno on course(cno DESC);

例：为sc按学号sno升序课程号cno降序建立唯一索引

create unique index ID_sno_cno on course(sno ASC cno DESC);

例：删除student的ID_sno索引

drop index student.ID_sno;

当表中某列被设置为主键时，SQL server会在该列上自动建立一个聚集唯一索引，而某列被设置为unique约束时，自动建立一个非聚集的唯一索引。

1.3数据查询

单表查询

1. select语句
   例：查询全体学生的学号和姓名，查询学生的所有信息

select sno,sname from student
select sno,sname,sdept,sex,sage from student
/*上一行代码也可表示为：select * from student*/

select语句后面可以接算数表达式、函数、字符串常量
例：查询所有学生名字，在名字后面加一个“hello”；查询所有学生的姓名加出生年份；查询所有学生的姓名，出生年份和所属分院，要求分院字母小写

select sname