当前位置: 澳门新濠3559 > 数据库 > 正文

2、从其它表中查询出数据,在表tb中有一个键d

时间:2019-11-29 07:15来源:数据库
索引由数据表中的一列或多列组合而成,创建索引的目的是为了优化数据库的查询速度。其中用户创建的索引指向数据库中具体数据的位置。当用户通过索引查询数据库中的数据时 不需

索引由数据表中的一列或多列组合而成,创建索引的目的是为了优化数据库的查询速度。其中用户创建的索引指向数据库中具体数据的位置。当用户通过索引查询数据库中的数据时
不需要遍历所有数据库中的数据,提高了查询效率,索引是一种将数据库中的单列或多列的值排序的结构
  用户通过索引查询数据,系统可以不必遍历数据表中的所有记录,而是查询索引列,一般的过程的数据查询是通过遍历全部数据,并寻找数据库中的匹配记录而实现的

MySQL课堂总结随笔,mysql课堂随笔

第一章:数据类型
数值类型(整数类型、浮点数类型)
日期时间类型
字符串和二进制类型
其他数据类型

*数值类型包括整数型和浮点型。
整数型数据:只能存储整数!最常用的整型数据类型是INT型。
浮点型数据:可以存储整数和小数,但都带有小数点。最常用的浮点型数据类型是FLOAT型。
*日期时间类型:
日期型:用于保存日期。DATE
时间性:用于保存时间。TIME
日期时间型:用于保存日期与时间。DATETIME
注意:
日期与时间之间,需要使用空格分开。
日期型、时间型、日期时间型的数据,需要使用单引号括起来。
*字符串型:
CHAR类型:固定长度的字符串 当实际字符长度小于类型其长度时,后面用空格补全。
VARCHAR类型:可变长度的字符串 保存的是实际字符串的长度。
2、从其它表中查询出数据,在表tb中有一个键deptld与这个id关联。CHAR与VARCHAR长度范围都是0~255之间的大小。
注意:
当字符串长度超过类型长度时,超出的字符将被删除。(没有了)

 

第二章、相关操作
mysql程序常用命令
选定默认数据库:use dbname;
显示所有数据库:show databases;
显示默认数据库中所有表:show tables;
放弃正在输入的命令:c
显示命令清单:h
退出mysql程序:q
查看MySQL服务器状态信息:s

处理SQL文件
mysql命令
处理存放在文件里的SQL命令
格式:mysql [option] dbname < file.sql
mysql –u root –p --default-character-set=latin1 dbname < backup.sql
注:latin1是MySQL4.0的默认字符集或者utf-8是MySQL5.0的默认字符集,如果不知道使用什么字符集的时候,可以选用这两个其中一个

mysqladmin命令
完成许多系统管理任务,如创建或删除一个数据库、修改密码等
格式:mysqladmin [option] admincommand
创建数据库:mysqladmin –u root –p create newdb
删除数据库:mysqladmin –u root –p drop newdb
更改密码:mysqladmin –u root –p password “newpasswd”

mysqldump命令
用于为数据库创建备份
格式:mysqldump [option] dbname > bak.sql
mysqldump –u root –p tmpdb > backuptmp.sql
备份出来的是一个文本文件,默认为utf-8字符集,如果想使用另外一种字符集,必须使用 --default-character-set=gbk选项来设置

附加工具
MySQL CC OS X 的版本现在也有
DBTOOL专业管理器,来自
注意:
不要直接删除磁盘中的数据库文件!否则会导致服务器出错!
此外,数据库文件不能直接双击打开,也不能直接访问。
该如何访问数据库中的数据?
使用数据库服务器(DBMS)进行访问。

 

第三章、数据库操作
查看当前服务器中的所有数据库(名称):show databases;
查看数据库的信息(创建语句):show create database 数据库名称;
修改数据库的基本语法格式(修改默认字符集,即编码):
ALTER database 数据库名称
default character set 编码方式
collate 编码方式_bin;
查看当前支持的字符集:show character set ;
删除数据库(包括该数据库中所有信息):drop database 数据库名称;
小结
查看:show
show databases 查看当前服务器中所有数据库的名称
show create database 查看指定数据库的创建语句
show character set 查看所有字符集
show collation 查看所有校对规则
创建:create
修改:alter
删除:drop
数据库:database

 

第四章、数据表操作
查看与删除数据表的操作
查看数据表的建立语句:SHOW CREATE table 表名;
查看当前数据库中所有表:SHOW tables;
查看表中的字段信息:DESCRIBE 表名;可简写为:DESC 表名;
删除数据表:DROP table 表名;
注意:要创建表格,必须先选定数据库
修改数据表
修改数据表的语法,与修改数据库的语法类似:
ALTER database mydb
default character set gbk
collate gbk_bin;
ALTER table 表名
具体要修改的选项;
注意:修改数据表是指,修改表的结构,而不是修改表中的数据。
1)修改表名称:
ALTER table 表名
rename [TO] 新表名;
注:[TO]为可选项,即可加可不加
2)修改字段名:
ALTER table 表名
change 旧字段名 新字段名 新(原)数据类型;
3)修改字段的数据类型:
ALTER table 表名
modify 字段名 新数据类型;
4)添加字段:
ALTER table 表名
add 新字段名 数据类型;
5)删除字段:
ALTER table 表名
drop 字段名 ;
6)修改字段的排列位置:
ALTER table 表名
modify 字段名1 数据类型 FIRST | AFTER 字段名2 ;
小结
注意:修改数据表是指,修改表的结构,而不是修改表中的数据
1)修改表名 rename
2)修改字段名(也可同时修改字段的数据类型) change
3)修改字段的数据类型 modify
4)添加字段 add
5)删除字段 drop
6)修改字段的排列位置 modify

 

第五章、约束
约束——用于实现数据完整性,数据完整性理解为:数据的有效性(正确性)。
数据库完整性概述:
(1)实体完整性(行与行) 主键约束、外键约束
(2)域(字段)完整性(各个字段数值) 非空约束、默认值约束、检查
(3)参照完整性(表与表) 外键约束
(4)用户(自定义)完整性
约束的类型:
(1)主键约束
(2)外键约束
(3)非空约束
(4)唯一约束
(5)默认值约束
(6)检查约束

1)主键约束(PRIMARY KEY)
用来唯一标识表中的一个列,一个表中主键约束最多只能有一个,不同的行上,主键值不能相等。
在创建表时设置联合主键约束。联合主键,即多个列联合作为主键。这是,主键列的值不能同时都相等。
*修改表时添加主键约束
语法格式:
ALTER TABLE 表名
ADD CONSTRAINT 约束名 PRIMARY KEY(列名);
表名:要添加约束的表
约束名:由用户指定,用于标识约束
列名:要添加主键约束的列
*修改表时删除主键约束
语法格式:
ALTER TABLE 表名
DROP PRIMARY KEY;
注意:由于主键约束在一个表中只能有一个,因此不需要指定主键名就可以删除。

2)外键约束( FOREIGN KEY )
外键是指引用另一个表中的一列或多列,被引用的列应该具有主键约束或唯一约束。
外键用于建立和加强两个表数据之间的连接。
外键用于建立和加强两个表数据之间的连接。
外键用于建立多个表之间的关系。
语法格式:
添加外键
ALTER TABLE 表名
ADD CONSTRAINT 约束名
FOREIGN KEY (外键字段名) REFERENCES 外键表名(列名);
删除外键
ALTER TABLE 表名
DROP FOREIGN KEY 外键名;
EG:ALTER TABLE student
ADD CONSTRAINT FK_ID
FOREIGEN KEY (gid) REFERENCES grade(id);
含义:在student表的gid列上建立一个外键约束,该约束是参照grade表的id字段。
对表添加数据时有什么影响?
对于被参照的表grade:添加数据时无任何影响。
对于外键表student上的外键列gid:该列上的数据只能选择被参照表grade上被参照列id中的数据。
简单的说:student表上gid列中的数据,只能是grade表上id列中已有的数据。即,student表的gid外键依赖于grade表的id。

3)非空约束(NOT NULL)
用来限定数据表中的列必须输入值。
*修改表时添加非空约束
语法格式:
ALTER TABLE 表名
MODIFY 列名 该列数据类型 NOT NULL ;

4)唯一约束(UNIQUE)
也是用来唯一标识表中列的,不同的行上,唯一键的值也不能相等与主键约束不同的是,在一张数据表中可以有多个唯一约束。
*修改表时添加唯一约束
语法格式:
ALTER TABLE 表名
ADD CONSTRAINT 约束名 UNIQUE(列名);
*删除唯一约束
语法格式:
DROP INDEX 约束名 ON 表名

5)默认值约束(DEFAULT)
用来当不给表的列输入值时,自动为该列指定一个值。
*修改表时添加默认值约束
语法格式:
ALTER TABLE 表名
ALTER 列名 SET DEFAULT 默认值 ;
*删除默认值约束
语法格式:
ALTER TABLE 表名
ALTER 列名 DROP DEFAULT ;

小结
表创建后,再添加约束
主键约束
ALTER TABLE 表名
ADD CONSTRAINT 约束名 PRIMARY KEY(列名);
唯一约束
ALTER TABLE 表名
ADD CONSTRAINT 约束名 UNIQUE(列名);
非空约束
ALTER TABLE 表名
MODIFY 列名 该列数据类型 NOT NULL ;
默认约束
ALTER TABLE 表名
ALTER 列名 SET DEFAULT 默认值 ;
外键约束
ALTER TABLE 表名
ADD CONSTRAINT 约束名
FOREIGN KEY (外键字段名) REFERENCES 外键表名(列名);
注意:约束名称可以使用SHOW CREATE TABLE 表名;来查询。
删除约束
主键约束
ALTER TABLE 表名
DROP PRIMARY KEY;
唯一约束
DROP INDEX 约束名 ON 表名
非空约束
ALTER TABLE 表名
MODIFY 列名 该列数据类型 ;
默认约束
ALTER TABLE 表名
ALTER 列名 DROP DEFAULT ;
外键约束
ALTER TABLE 表名
DROP FOREIGN KEY 外键名;

 

 

第六章、索引
索引主要内容
1)索引的概念
数据库的索引好比新华字典的音序表,它是对数据表中一列或多列的值进行排序后的一种结构,其作用就是提高表中数据的查询速度。
2)索引的分类
(1)普通索引:基本索引类型
(2)唯一性索引:该索引所在字段的值必须是唯一的
(3)全文索引:定义在字符串类型上的索引
(6)空间索引:只能创建在空间数据类型上
(4)单列索引:在单个字段上创建的索引
(5)多列索引:在多个字段上创建的索引
3)创建索引
*创建表时创建索引
基本语法格式:
CREATE TABLE 表名称
(
字段名 数据类型 [完整性约束条件],
字段名 数据类型 [完整性约束条件],
…………
字段名 数据类型 ,
[ UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名 [(长度)] [ASC | DESC] )
);
UNIQUE :唯一索引
FULLTEXT:全文索引
SPATIAL :空间索引
INDEX | KEY :表示索引,二选一即可
字段名:要添加索引的字段
长度:索引的长度
ASC | DESC :ASC为升序排序,DESC为降序排序,默认是ASC升序排序
*使用CREATE INDEX语句 在已有表上创建索引
基本语法格式:
CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名 [(长度)] [ASC | DESC] ) ] ;
*使用ALTER TABLE语句 在已有表上创建索引
基本语法格式:
ALTER TABLE 表名
ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
(字段名 [(长度)] [ASC | DESC] ) ]
4)删除索引
*使用ALTER TABLE语句 在删除索引
基本语法格式:
ALTER TABLE 表名
DROP INDEX 索引名;
*使用DROP INDEX语句 在删除索引
基本语法格式:
DROP INDEX 索引名 ON 表名 ;
5)查看表中的索引
SHOW INDEX FROM 表名;

 

第七章、数据的添加、更新和删除
1)添加数据
*为表中添加记录(数据)INSERT
语法:*INSERT [INTO] 表名 [(字段列表)]
VALUES (值列表)
*INSERT [INTO] 表名
SET 字段名1 = 值 ,字段名2 = 值 , …………
同时添加多行数据
*INSERT [INTO] 表名 [(字段列表)]
VALUES (值列表),(值列表),…………

注意:如果表名后面没写字段名,则默认是向所有字段添加值,另外字符串值应该用单引号‘ ’ 括起来。
如何知道数据是否已经插入到表中?使用查询语句:select * from 表名;
如果表名后面没写字段名,则默认是向所有字段添加值,添加值的顺序必须和字段在表中定义的顺序相同。
2)更新数据
注意:更新数据时指对表中存在的记录进行修改
语法:UPDATE 表名
SET 字段名1 = 更新值,字段名2 = 更新值,…………
[WHERE 更新条件]
叙述:
where 子句是判断语句,用来设定条件,限制只更新满足条件的行,如果不带where子句,则更新所有行数据。可以一次修改多个行,多个列的数据。
3)删除数据
语法:DELETE FROM 表名
[WHERE 删除条件]
叙述:此语句删除表中的行,如果不带where子句,则删除整个表中的记录,但是表(表结构)不会被删除。
*DELETE语句与TRUNCATE语句的区别
DELETE语句与TRUNCATE语句都能实现删除表中所有数据的功能。
区别:
(1)DELETE语句是DML(数据操纵)语句,TRUNCATE语句是DDL(数据定义)语句。
(2)DELETE语句可以使用where子句来设定只删除表中指定的数据,TRUNCATE语句只能用于删除表中所有记录(数据)。
(3)使用TRUNCATE语句删除表中所有数据后,自动增加字段( AUTO_INCREMENT )的默认初始值重新由1开始,而使用DELETE语句删除表中所有记录后,再次添加记录时,自动增加字段的值为删除时该字段的最大值加1。
(4)重要:TRUNCATE语句在本质上直接清空数据表,清空后数据不能恢复,DELETE语句在删除数据前,会将删除操作写入操作日志,数据可以恢复(在满足某些条件时)。

 

第八章、单表查询
查询产生一个虚拟表,看到的是表形式显示的结果,但结果并不真正存储,每次执行查询只是现从数据表中提取数据,并按照表的形式显示出来
1)简单条件查询
SELECT语句的语法
SELECT [DISTINCT | DISTINCTROW | ALL] * | 字段列表
[FROM 表名
[WHERE 查询条件]
[GROUP BY 分组字段名 [HAVING 分组过滤条件] ]
[ORDER BY 排序字段名 [ASC | DESC] ]
[LIMIT [偏移量,] 记录数]
[PROCEDURE procedure_name]
]
[DISTINCT | DISTINCTROW | ALL] :用于剔除重复的记录
"*| 字段列表":需要查询的字段,“ * ”代表表中所有字段
FROM 表名 :需要查询的表名
[WHERE 查询条件] :查询条件
GROUP BY 分组字段名 :用于将查询结果按指定字段进行分组统计
HAVING 分组过滤条件:对分组统计结果进行过滤的条件
[ORDER BY 排序字段名 [ASC | DESC] ] :对查询结果按照指定的字段进行排序
[LIMIT [偏移量,] 记录数] :用于限制查询结果的数量(记录数),[偏移量,] 可以设置查询结果忽略(跳过)的记录数
[PROCEDURE procedure_name]:查询存储过程返回的结果集数据
* 关系运算符
= :等于
> :大于
>= :大于或等于
< :小于
<= :小于或等于
<> :不等于
带IN关键字的查询
IN关键字用于判断某个字段的值是否在指定的集合中
集合中的数据使用小括号“ ( ) ”括起来
带BETWEEN …AND关键字的查询
BETWEEN AND用于判断某个字段的值是否在指定的范围内
空值查询
空值(NULL)不同于0,也不同于空字符串!
where ... is (not) NULL;
带DISTINCT关键字的查询
表中的某些字段的数据可能存在重复的值,在查询结果中也有可能有重复的值,从而导致出现重复的数据行
带LINK关键字的查询
使用LIKE可以进行模糊查询。
通配符:下划线 “ _ ”:代表一个任意字符
百分号 “ % ”:代表0个或多个任意字符
注意:通配符必须配合LIKE使用
如果在like模糊查询中,字符串中需要使用_和%字符,则需要使用转义字符,即在_和%字符加上反斜杠“ ” eg: where ... like ‘%%%’ ;(包含%)

where ... like '';(*%,%*,*%*,%*% ->*代字母,字母开头,字母结尾,开头结尾,包含字母)
带AND,OR关键字的多条件查询
AND:而且(多个条件需要同时满足,表达式才成立)
OR:或者(多个条件只需要满足一个,表达式就成立)
提示:多个查询条件,每个条件最好使用小括号括起来

2)高级查询
聚合函数
COUNT():返回某列的行数
SUM():返回某列的和
AVG():返回某列的平均值
MAX():返回某列的最大值
MIN():返回某列的最小值
上面的函数对一组(一列)数值进行统计,并返回唯一的一个数值(结果),所以这些函数被称为聚合函数。
*COUNT()函数——计数
select COUNT( * )
from ...
where ...;
*SUM()函数——求和
select SUM( 字段名)
from 表名 ;
*MAX()函数——求最大值
select MAX( 字段名 )
from 表名 ;
*MIM()函数——求最小值
select MIN( 字段名)
from 表名 ;
对查询结果排序
SELECT [DISTINCT | DISTINCTROW | ALL] * | 字段列表
[FROM 表名
[WHERE 查询条件]
[ORDER BY 排序字段名 [ASC | DESC] ]
]
[ORDER BY 排序字段名 [ASC | DESC] ] :对查询结果按照指定的字段进行排序。
[ASC | DESC] :排序方式ASC 为升序, DESC为降序。默认是ASC 升序。
注意:ORDER BY在WHERE子句的后面
分组查询
SELECT [DISTINCT | DISTINCTROW | ALL] * | 字段列表
[FROM 表名
[WHERE 查询条件]
[GROUP BY 分组字段名 [HAVING 分组过滤条件] ]
[ORDER BY 排序字段名 [ASC | DESC] ]
]
[GROUP BY 分组字段名 [HAVING 分组过滤条件] ]
注意:各个子句的先后次序!
WHERE
GROUP BY
ORDER BY
使用LIMIT限制查询结果的数量
查询数据时,可能会返回很多条记录,用户可以使用LIMIT来限制查询结果显示的行数。例如分页功能,也可以使用LIMIT来实现。
SELECT语句的语法
SELECT [DISTINCT | DISTINCTROW | ALL] * | 字段列表
[FROM 表名
[WHERE 查询条件]
[GROUP BY 分组字段名 [HAVING 分组过滤条件] ]
[ORDER BY 排序字段名 [ASC | DESC] ]
[LIMIT [偏移量,] 记录数]
[PROCEDURE procedure_name]
]
[DISTINCT | DISTINCTROW | ALL] :用于剔除重复的记录
* | 字段列表 :需要查询的字段,“ * ”代表表中所有字段
FROM 表名 :需要查询的表名
[WHERE 查询条件] :查询条件
GROUP BY 分组字段名 :用于将查询结果按指定字段进行分组统计
HAVING 分组过滤条件:对分组统计结果进行过滤的条件
[ORDER BY 排序字段名 [ASC | DESC] ] :对查询结果按照指定的字段进行排序
[LIMIT [偏移量,] 记录数] :用于限制查询结果的数量(记录数),[偏移量,] 可以设置查询结果忽略(跳过)的记录数
[PROCEDURE procedure_name]:查询存储过程返回的结果集数据
注意:LIMIT同样适用于UPDATE与DELETE语句

 

第九章、多表操作
*了解什么是外键,会为表添加外键约束和删除外键约束
*了解三种关联关系,会向关联表中添加和删除数据
*学会使用交叉连接、内连接、外连接及复合条件连接查询多表中的数据
*掌握子查询,会使用IN、EXISTS、ANY、ALL关键字及比较运算符查询多表中的数据

1)外键约束( FOREIGN KEY )
外键是指引用另一个表中的一列或多列,被引用的列应该具有主键约束或唯一约束。
外键用于建立和加强两个表数据之间的连接。
外键用于建立和加强两个表数据之间的连接。
外键用于建立多个表之间的关系。
语法格式:
添加外键
ALTER TABLE 表名
ADD CONSTRAINT 约束名
FOREIGN KEY (外键字段名) REFERENCES 外键表名(列名);
删除外键
ALTER TABLE 表名
DROP FOREIGN KEY 外键名;
EG:ALTER TABLE student
ADD CONSTRAINT FK_ID
FOREIGEN KEY (gid) REFERENCES grade(id);
* 含义:在student表的gid列上建立一个外键约束,该约束是参照grade表的id字段。
*对表添加数据时有什么影响?
对于被参照的表grade:添加数据时无任何影响。
对于外键表student上的外键列gid:该列上的数据只能选择被参照表grade上被参照列id中的数据。
简单的说:student表上gid列中的数据,只能是grade表上id列中已有的数据。即,student表的gid外键依赖于grade表的id。
*对表删除数据时有什么影响?
对于外键表(子表)student:删除数据无任何影响。
对于被参照表(父表)grade:被删除的数据不能是已经被student表参照(引用)的数据。
为表添加外键时,需要注意的问题:
(1)建立外键的表以及被参照的表的表类型必须是InnoDB型。
(2)定义外键名时,不能加引号。见书本P124。
注意:MySQL创建表时默认的表类型为MYISAM!
多学一招:添加外键约束的参数说明
P125,表5-1(所谓的关联操作)
CASCADE:删除包含与删除键值有参照关系的所有记录
SET NULL:修改包含与删除键值有参照关系的所有记录,删除时修改为NULL
NO ACTION:不进行任何操作
RESTRICT:拒绝主表删除或修改外键关联列

2)操作关联表
关联关系:
*多对一
最常见的关系。例如:一个班对于多名同学
*多对多
例如:多名学生,多门课程, 一个学生可以选修多门课程,一门课程也可以被多名学生选修。
*一对一
(1)添加数据

(2)删除数据

3)连接查询
(1)交叉连接 CROSS JOIN
select * from 表1 CROSS JOIN 表2
交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔积。
(2)内连接 INNER JOIN
内连接(inner join)又称简单连接或自然连接,是最常见的连接查询。
内连接只列出与连接条件匹配的数据行。
内连接的两个表可以是同一个表,这种连接称为自连接
简单地说,内连接查询的结果是对交叉连接的结果按连接条件进行过滤后,得到的结果。
select * from 表1 [INNER] JOIN 表2 ON 连接条件
eg select * from department INNER JOIN employee
ON department.did = employee.did ;
eg select department.did, department.dname, employee.name
from department INNER JOIN employee
ON department.did = employee.did ;
eg select department.did, department.dname, employee.name
from department , employee
WHERE department.did = employee.did ;
*为表指定别名:
eg select p2.did , p2.name , p1.dname
from department as p1 JOIN employee as p2
ON p1.did = p2.did ;
eg select p2.did , p2.name , p1.dname
from department p1, employee p2
WHERE p1.did = p2.did ;
eg 为内连接添加查询条件:查询王红的所有个人资料和部门资料
select p1.* , p2.*
from department p1 JOIN employee p2 ON p1.did = p2.did
WHERE p2.name = '王红';
eg 找出王红所在的部门的所有员工的资料
select p1.*
from employee p1 JOIN employee p2 ON p1.did = p2.did
WHERE p2.name = '王红';
(3)外连接
外连接分两种:
a.LEFT JOIN(左连接):在内连接的基础上,返回左表中不符合连接条件的记录(即返回左表中所有的记录)
b.RIGHT JOIN(右连接):在内连接的基础上,返回右表中不符合连接条件的记录(即返回右表中所有的记录)
注意:多表查询时,应该明确指定每个字段所在的表。格式为:表名.字段名
*左连接LEFT JOIN
*右连接REGHT JOIN
*全连接FULL JOIN
*外连接必定是两个表的连接查询,这两个表分别为左表与右表
*外连接的结果不仅包含符合连接条件的数据,还允许查询结果包含不符合连接条件的数据。
*简单地说,外连接的查询结果就是在内连接查询结果的基础上,加上左表或右表中不符合连接条件的数据。
select * from 表1 LEFT JOIN 表2 ON 连接条件
select * from 表1 RIGHT JOIN 表2 ON 连接条件
(4)复合条件连接查询
使用order by 子句对连接查询结果排序

4)子查询
子查询是指一个查询语句嵌套在另一个查询语句内部的查询。
一个查询可以嵌套在SELECT、SELECT……INTO、INSERT……INTO等语句中。
IN:集合判断运算符
EXISTS:存在测试运算符
ANY:部分满足
ALL:全部满足
带IN关键字的子查询
内层(嵌套)的子查询仅返回一个字段的数据,这些数据可以看成是一个集合。
IN关键字就是判断某个字段(did)的值是否在这个集合中
带EXISTS关键字的子查询
EXISTS(单目运算符),测试后面的子查询是否存在数据。
TRUE:子查询的查询结果存在数据
FALSE:子查询的查询结果不存在数据,即查询结果为空集
带ANY关键字的子查询
ANY关键字表示结果集中任意一个数据满足条件即代表条件成立
带ALL关键字的子查询
ALL关键字表示结果集中所有数据都满足条件,条件才算成立
*eg: 例5-8 查询存在年龄为20岁的员工的部门
select * from department
where did IN ( select did from employee where age = 20 );
*eg: 例5-10 查询employee表中是否存在龄大于21岁的员工,如果存在,则查询department表中的所有记录
select * from department
where EXISTS ( select did from employee where age > 21 );
*eg: 例5-11
select * from department
where did >ANY ( select did from employee);

select * from employee
where age = ANY ( select age from employee where did=1);

*eg: 例5-12
select * from department
where did >ALL ( select did from employee);
*eg: 查询出所有年龄比员工的平均年龄小的员工信息。
select * from employee
where age > ( select avg(age) from employee);
如何知道平均年龄?select avg(age) from employee;
如何组合?
例如:要查询出所有年龄小于21的员工信息,怎么做?
select * from employee
where age < 21;
*eg: 查询出年龄最小(大)的员工的信息。
最小年龄:select min(age) from employee;
最大年龄:select max(age) from employee;
select * from employee
where age= ( select max(age) from employee);

 

第十章、多表查询
函数(列表)
数学函数
ABS(x):返回x的绝对值
SQRT(x):返回x的平方根
ROUND(x,y):对x进行四舍五入,保留y为小数
CONCAT(…):将各个字段使用下划线连接起来

为表和字段取别名
查询数据时,可以为表和字段取别名。
为表取别名:
select * from 表名 AS 别名
为字段取别名:
select 字段名 [AS] 别名 from 表名 AS 别名
字段的别名可以直接显示在查询结果中

 

第十一章、视图
*了解视图的概念,能够简述视图的特点
*掌握视图的创建方式,学会在单表和多表上创建视图
*掌握视图数据的查看、修改、更新以及删除

1)视图概述
视图是从一个或多个基本表中导出来的表,它是一种虚拟存在的表,并且表的结构和数据依赖于基本表。
通过视图不仅可以看到存放在基本表中的数据,并且还可以像操作基本表一样,对视图中存放的数据进行查询、修改和删除。
与直接操作基本表相比,视图具有以下优点:
(1)简化查询语句
(2)安全性
(3)逻辑数据独立性
视图是虚拟的表!
(1)使用上与表基本一样
(2)但视图并不真正存储数据,视图的中数据归根结底是存储在对应的基本表中的。
2)视图管理
创建视图的语法:
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS
SELECT查询语句
[ WITH [CASCADED | LOCAL] CHECK OPTION ];
注:SELECT查询语句 的结果,就是该视图中的内容(包括视图的结构和数据)
创建视图的基本语法:
CREATE VIEW 视图名称
AS SELECT查询语句 ;
注:SELECT查询语句 的结果,就是该视图中的内容(包括视图的结构和数据)
提示:创建视图的其他选项一般使用默认值即可。
*eg: 例7-1
create view view_stu
as select math, chinese, math+chinese from student;
创建视图时为视图指定字段名的基本语法:
CREATE VIEW 视图名称(视图的字段名)
AS SELECT查询语句 ;
*eg: 例7-2 为视图自定义字段名称
create view view_stu2 (math , chin , sum)
as select math, chinese, math+chinese from student;
*eg: 例7-3 在多表上创建视图
create view view_class (id , name , class)
as select student.s_id, student.name , stu_info.class
from student INNER JOIN stu_info
ON student.s_id = stu_info.s_id;
select * from view_class;
查看视图
* NULL:表示该列是否允许存储NULL值
* Key:表示该列是否带有索引
* Default:表示该列是否带有的默认值
* Extra:表示该列的附加信息
查看表的结构:DESCRIBE 表名称
查看视图的结构:DESCRIBE 视图名称 可简写为:DESC 视图名称
*eg: DESC view_stu
查看表的基本信息:SHOW TABLE STATUS LIKE '表名称'G
查看视图的基本信息:SHOW TABLE STATUS LIKE '视图名称'G
*eg: 例7-5 查看stu_classs视图的基本信息
SHOW TABLE STATUS LIKE ' stu_classs 'G
查看表的创建语句:SHOW CREATE TABLE 表名称
查看视图的创建语句:SHOW CREATE VIEW 视图名称
*eg: 例7-6 查看stu_classs视图的基本信息
SHOW CREATE VIEW stu_classs
修改视图结构
修改视图的基本语法:
CREATE OR REPLACE VIEW 视图名称
AS SELECT查询语句 ;
OR REPLACE :表示新建的视图替换已有的视图
注意:修改视图结构时,所使用的SELECT查询语句,必须是完整的查询语句!
提示:修改后的视图,其内容可以与原有的视图没有任何关联。
*eg: 创建一个视图myview,内容包含student表中所有同学的学号与姓名。
CREATE VIEW myview
AS select s_id , name from student;
修改视图的 另一种语法:
ALTER VIEW 视图名称
AS SELECT查询语句 ;
更新视图 (对视图中的数据进行修改)
可以对视图中的数据进行更新,其操作包括修改、插入、删除数据。
对视图数据的操作与对表数据的操作基本一样。
因为视图是一个虚拟的表,并不会保存数据,所以,更新视图中的数据,归根结底就是更新其基本表中的数据进行更新!
既然视图中没有保存数据,那么视图中保存的是什么?
视图中只保存了该视图的定义,即这个视图对应的查询语句。
当用户访问一个视图时,MySQL服务器会根据这个视图的定义(其查询语句)实时地去查询基本表,得到相应的结果集,然后再将结果集呈现给用户。
更新视图时,需要注意,并不是所有的视图都能执行更新视图的操作!
当视图中包含如下内容时,视图的更新操作将不能被执行:
(1)视图中包含基本表中被定义为非空的列(不能添加、修改)
(2)视图的数据为数学表达式的结果(不能添加、修改)
(3)视图中的数据使用了聚合函数(不能添加、修改、删除)
(4)定义视图的SELECT语句中使用DISTINCT,UNION,TOP,GROUP BY 或HAVING子句。(不能添加、修改、删除)
删除视图
删除视图的基本语法:
DROP VIEW [IF EXISTS] 视图名称;
IF EXISTS:加入视图存在的话,则删除;如果不存在,则什么都不做。
*eg: 例7-12 删除view_stu2视图
DROP VIEW view_stu2;
使用IF EXISTS则不会因为视图已经删除,而出现错误
DROP VIEW IF EXISTS view_stu2;

 

第十二章、事务与存储过程
*了解事务的概念,会开启、提交和回滚事务
*掌握事务的4种隔离级别
*学会创建存储过程
*掌握调用、查看、修改和删除存储过程
1)事务
(1)事务概念
事务处理机制在程序开发过程中有着非常重要的作用,它可以使整个系统更加安全,保证在同一个事务中的操作具有同步性。
所谓事务就是针对数据库的一组操作,它可以由一条或多条SQL语句组成,同一个事务的操作具备同步的特点,如果其中有一条语句无法执行,那么所有的语句都不会执行,也就是说,事务中的语句要么都执行,有么都不执行。
(2)事务的操作:
开启事务:START TRANSACTION
提交事务:COMMIT
回滚事务:ROLLBACK
注意:MySQL中直接书写的语句都是自动提交的,而事务中的操作语句都需要使用COMMIT语句手动提交。
ROLLBACK语句只能针对未提交的事务执行回滚操作,已提交的事务是不能回滚的。
事务的4个特性:
*原子性
一个事务必须被视为一个不可分割的最小工作单元。(事务中的操作,要么全部执行,要么全部不执行)
*一致性
事务将数据库从一种状态转变为下一种一致的状态。
*隔离性
还称为并发控制、可串行化、锁等。
当多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离
*持久性
事务一旦提交,其所做的修改就会永久保存到数据库中,即使数据库发生故障也不应该对其有任何影响。
事务的提交:事务中的操作,需要提交后才能执行成功。提交事务:COMMIT
事务的回滚:在操作一个事务时,如果发现当前事务中的操作是不合理的,就可以通过回滚来取消当前事务。回滚事务:ROLLBACK
(3)事务的隔离级别:数据库是多线程并发访问的,所以很容易出现多个线程同时开启事务的情况,这样就会出现脏读、重复读以及幻读的情况,为了避免这种情况的发生,就需要为事务设置隔离级别。
MySQL中,事务有4中隔离级别:
READ UNCOMMITTED(读未提交)
事务中的最低级别,该级别的事务可以读取另一个事务中未提交的数据,也被称为脏读,这是相对危险的。
READ COMMITTED(读提交)
大多数的数据库管理系统的默认隔离级别,该级别下的事务只能读取其他事务已经提交的内容。
REPEATABLE READ(可重复读)
是MySQL默认的事务隔离级别,可以避免脏读、不可重复读的问题,确保同一事务的多个实例在并发读取数据时,会看到同样的数据。
SERIALIZABLE(可串行化)
是事务的最高隔离级别,它会强制对事务进行排序,使之不会发生冲突,从而解决脏读、幻读、重复读的问题。

2)存储过程
(1)存储过程的创建
在开发过程中,经常会遇到重复使用某一功能的情况,为此,MySQL引入了存储过程。
存储过程就是一条或多条SQL语句的集合,当对数据库进行一系列复杂操作时,存储过程可以将这些复杂操作封装成一个代码块,以便重复使用,大大减少数据库开发人员的工作量。
(2)创建存储过程
创建存储过程的基本语法:
CREATE PROCEDURE 存储过程名称 (参数列表)
BEGIN
存储过程内容
END
参数可以分三种:
IN:输入参数
OUT:输出参数
INOUT:输入输出参数
由于存储过程会包含多条SQL语句,而SQL语句默认是以分号“;”结束,所以,创建存储过程的结束语句不能是分号,在创建存储过程之前,必须先重新定义MySQL的结束符。

例:将两个斜杠“//”定义为MySQL语句的结束符
DELIMITER //

注意:定义完后,直接输入的MySQL语句,都需要使用“//”作为结束符,而不再是分号“;”
可以使用以下语句将结束符重新设置为分号“;”
DELIMITER ;
注意:分号前需要添加空格!
例6-2 创建一个查看student表所有内容的存储过程

delimiter //
create procedure Proc()
begin
select * from student;
end //

delimiter ;

例6-12 创建一个存储过程,查看student表中指定性别同学的人数
delimiter //
create procedure CountProc1( IN s_gender varchar(50),
OUT num int)
begin
select count(*) into num
from student
where gender = s_gender;
end //

delimiter ;
(3)变量的使用
存储过程中可以定义变量,基本语法是:
DECLARE 变量名称 变量数据类型 [DEFAULT 初始值 ]

例:定义一个整数(INT)变量,名称为myvariable,初始值为100
DECLARE myvariable INT DEFAULT 100
a.变量的赋值:
SET 变量名称 = 数值

例:设置myvariable的值为150
SET myvariable = 150

例:
DECLARE var1, var2 , var3 INT;
SET var1 = 10 , var2 = 20;
SET var3 = var1 + var2;

创建一个存储过程,计算两个整数的和,并将结果由参数返回
delimiter //
create procedure Proc1( IN a int , IN b int , OUT c int)
begin
SET c = a + b;
end //

delimiter ;

调用存储过程计算5,22的和:
call Proc1(5, 22,@x);
查看结果:
select @x;

3)流程控制语句的使用
分支语句:
IF
THEN
ELSE
END IF;
循环语句:
WHILE
END WHILE;

创建一个存储过程,计算比较两个整数的大小,并将较大的数由参数返回
delimiter //
create procedure Proc2( IN a int , IN b int , OUT c int)
Begin

end //
调用存储过程:
call Proc2(15, 33,@x);
查看结果:
select @x;

delimiter //
create procedure Proc2( IN a int , IN b int , OUT c int)
Begin
IF a >= b
THEN SET c = a;
ELSE SET c = b;
END IF;
end //
调用存储过程:
call Proc2(15, 33,@x);
查看结果:
select @x;

创建一个存储过程,计算计算两个整数的范围内,所有整数的和,并将结果由参数返回
create procedure Proc3( IN a int , IN b int , OUT c int)
Begin

 

end //
调用存储过程:
call Proc3(1, 100,@x);
查看结果:
select @x;

create procedure Proc3( IN a int , IN b int , OUT c int)
Begin
DECLARE s INT DEFAULT 0;
DECLARE i INT ;
SET i = a;
WHILE i <= b DO
SET s = s + i;
SET i = i + 1;
END WHILE;
SET c = s;
end //
调用存储过程:
call Proc3(1, 100,@x);
查看结果:
select @x;

4)修改存储过程
修改存储过程的基本语法:
ALTER PROCEDURE 存储过程名称
存储过程属性 ;
注意:MySQL不能修改存储过程的代码,只能修改存储过程的权限等属性。如果一定要修改存储过程代码,必须先将存储过程删除之后,再重新创建一个新的存储过程。

5)删除存储过程
删除存储过程的基本语法:
DROP PROCEDURE 存储过程名称 ;
注意:MySQL不能修改存储过程的代码,只能修改存储过程的权限等属性。如果一定要修改存储过程代码,必须先将存储过程删除之后,再重新创建一个新的存储过程。

第十三章、数据库的高级操作
*学会对数据库中的数据进行备份和还原操作
*学会在数据库中创建、删除用户
*学会对数据库中的权限进行授予、查看和收回操作
1)数据备份
使用mysqldump命令可以对数据库进行备份。
可以备份单个数据库、多个数据库或者所有数据库。
注意:mysqldump是系统命令,并非MySQL语句,所以并非在MySQL控制台中执行,而是在系统
命令提示符窗口中执行。
mysqldump命令备份数据表的基本语法:
mysqldump -u用户名 –p密码 数据库名称 [表名称] >文件名称

mysqldump命令备份数据库的基本语法:
mysqldump -u用户名 –p密码 --database 数据库名称 >文件名称

mysqldump命令备份服务器中所有数据库的基本语法:
mysqldump -u用户名 –p密码 --all-databases >文件名称

例8-1 将数据库chapter08中的所有数据表备份到文件myfile.sql中,用户名为root,密码为itcast。
mysqldump –uroot –pitcast chapter08 > myfile.sql
mysqldump –uroot –pitcast chapter08 > c:myfile.sql
注意: -uroot 的-u与用户名、 –pitcast 的-p与密码,之间都不需要加空格

例 将数据库chapter08的整个数据库数据备份到文件myfile.sql中,用户名为root,密码为itcast。
mysqldump -uroot –pitcast --database chapter08 >myfile.sql
注意: -uroot 的-u与用户名、 –pitcast 的-p与密码,之间都不需要加空格。
如果是空密码或者是不立即输入密码,-p后面不需要任何内容。

例 将服务器中所有数据库备份到文件myfile.sql中,用户名为root,密码为itcast。
mysqldump -uroot -pitcast --all-databases >myfile.sql

2)数据还原
mysql命令还原数据的基本语法:
mysql -u用户名 –p密码 [数据库名称] <文件名称

若已使用下列语句备份了chapter08数据库中所有的表:
mysqldump -u root–pitcast chapter08 > myfile.sql

例 利用文件myfile.sql还原数据库chapter08中的表,用户名为root,密码为itcast。
mysql -uroot –pitcast chapter08 < myfile.sql

mysql命令还原整个或者多个数据库的基本语法:
mysql -u用户名 –p密码 <文件名称
注意:还原整个数据库或者多个数据库不需要注明数据库名称。

若已使用下列语句备份了整个chapter08数据库:
mysqldump -uroot –pitcast --database chapter08 >myfile.sql

利用文件myfile.sql还原整个数据库chapter08,用户名为root,密码为itcast。
mysql -uroot –pitcast < myfile.sql

另一种还原方法,登陆到mysql服务器,使用source语句进行还原。
source语句命令还原数据的基本语法:
source 文件名称

利用文件myfile.sql还原数据
source myfile.sql

3)用户管理
每个软件都会对用户信息进行管理。
MySQL中的用户分为root用户和普通用户,root用户为超级管理员,具有所有权限,如创建用户,删除用户,管理用户等,而普通用户只拥有被赋予的某些权限。
MySQL安装完成后,服务器中会有一个系统数据库——mysql。该数据库保存着所有的系统数据。
该数据库中的user表是最重要的一个表,记录了允许连接到服务器的账号(用户)信息以及一些全局级的权限信息,通过操作该表就可以对这些信息进行修改。
User表共有42个字段,这些字段大致可分如下4类:
1.用户列
包括Host、User、Password字段,分别代表主机名、用户名和密码
2.权限列
包括Select_priv 、 Insert_priv、 Update_priv等以priv结尾的字段,这些字段决定了用户对应的权限。
3.安全列
用于管理用户的安全信息。例如,是否支持加密等。
4.资源控制列
用户限制用户使用的资源。例如,每小时操作的次数。

A.创建普通用户
1.使用GRANT语句创建用户
GRANT语句不仅可以创建用户,还可以对用户进行授权,该语句会自动加载权限表,不需要手动刷新,而且安全、准确、错误少,使用GRANT语句创建用户是最常用的方法。
GRANT的语法格式:
GRANT 权限 ON 表名
TO 用户 [IDENTIFIED BY [密码] ]

例如:创建一个新用户,用户名为user1,密码为123,并授予该用户对chapter08.student表的查询权限
GRANT select ON chapter08.student
TO 'user1'@'localhost' IDENTIFIED BY '123‘

注意: ‘user1’@‘localhost’表示该用户可以通过本地站台(localhost)进行登陆,并进行操作。
创建用户后,可以查询mysql数据库中的user表验证用户信息是否已经加入。注意:密码是加密保存的字符串。

2.使用CREATE USER语句创建用户
使用CREATE USER语句创建用户是,服务器会自动修改相应的授权表,但需要注意的是,该语句创建的用户是没有任何权限的。

CREATE USER的语法格式:
CREATE USER 用户名 [IDENTIFIED BY [密码] ]

例如:创建一个新用户,用户名为user2,密码为123
CREATE USER 'user2'@'localhost' IDENTIFIED BY '123'

注意: ‘user2’@‘localhost’表示该用户可以通过本地站台(localhost)进行登陆,并进行操作

3.使用INSERT 语句创建用户(不推荐使用)
其实,创建用户在本质上就是在user表中添加一条记录,所以,也可以使用INSERT语句直接在表中添加一条记录,也可以达到添加用户的目的。

INSERT语句的基本语法:
INSERT INTO 表名(需指定内容的字段列表)
VALUES (数值列表);

例如:创建一个新用户,用户名为user3,密码为123
INSERT INTO mysql.user (Host , User ,Password ,ssl_cipher ,x509_issuer ,x509_subject)
VALUES (‘localhost’ ,’user3’,PASSWORD(‘123’),’’,’’,’’);

注意:PASSWORD是一个系统函数,PASSWORD(‘123’)的作用是将密码’123‘进行加密。因为保存在user表中的密码必须经过加密,所以添加的密码必须经过加密,否则该用户的密码将无法正确保存。

B.删除普通用户
1.使用DROP USER语句删除用户
DROP USER语句的基本语法:
DROP USER 用户名

例如:删除用户user1
DROP USER ’user1’@’localhost’

2.使用DELETE语句删除用户(不推荐使用)
DELETE FROM 表名
WHERE 要删除的用户名

例如:删除用户user2
DELETE FROM mysql.user
WHERE host = ‘localhost’ AND user =‘user2’

C.修改用户密码
1.修改root用户的密码
2.root用户修改普通用户的密码
3.普通用户修改密码

1.修改root用户的密码
使用系统命令mysqladmin
mysqladmin -u 用户名 [-h 主机名] –p password 新密码

使用UPDATE语句
UPDATE mysql.user
SET password = PASSWORD(‘新密码’)
WHERE user = ’root‘ AND host = ’localhost‘;

使用SET语句
SET PASSWORD = PASSWORD(‘新密码’)

  1. root用户修改普通用户的密码
    使用GRANT语句
    GRANT USAFE ON *.* TO ‘用户名’@‘主机名’
    IDENTIFIED BY [password] ‘新密码’;

使用UPDATE语句
UPDATE mysql.user
SET password = PASSWORD(‘新密码’)
WHERE user = ’root‘ AND host = ’localhost‘;

使用SET语句
SET PASSWORD FOR ‘用户名‘@’主机名‘ = PASSWORD(‘新密码’)

3.普通用户修改密码
使用SET语句
SET PASSWORD = PASSWORD(‘新密码’)

4)权限管理
1.mysql的权限
参见P215,表8-2。

2.授予权限
GRANT 权限 ON 对象 TO ‘用户名’@‘主机名’

3.查看权限
SHOW GRANTS FOR ‘用户名’@‘主机名’

4.收回权限
REVOKE 权限 ON 对象 FROM ‘用户名’@‘主机名’

 

 

 

 

一、创建数据库
创建数据库
create database mydb
default character set gbk
collate 编码方式_bin;

 

二、创建表

每个列的属性之间,用“,”(逗号)分隔。
注意:是分隔,即最后一列后面,不用添加。

创建表
CREATE TABLE produtinfo
( id int,
proname varchar(20),
proprice float(5,2),
prodate date,
propic varchar(20),
proremarks varchar(50)
);

auto_increment自动编号

约束
创建表时设置主键约束 PRIMARY KEY

CREATE TABLE produtinfo
(
id int PRIMARY KEY,
proname varchar(20),
proprice float(5,2),
prodate datetime,
propic varchar(20),
proremarks varchar(50)
)

CREATE TABLE produtinfo
(
id int,
proname varchar(20),
proprice float(5,2),
prodate datetime,
propic varchar(20),
proremarks varchar(50),
PRIMARY KEY (id)
)
创建表时设置唯一约束 UNIQUE

CREATE TABLE 班级资料表
(
班号 int PRIMARY KEY,
专业 varchar(10),
班长 varchar(20),
班主任 varchar(20),
UNIQUE (专业)
);

创建表时设置非空约束 NOT NULL

CREATE TABLE 学生资料表
(
学号 int PRIMARY KEY,
姓名 varchar(20) NOT NULL,
性别 varchar(1) DEFAULT ‘男’ ,
出生日期 datetime,
籍贯 varchar(10),
班级 int
);

创建表时设置默认值约束 DEFAULT

CREATE TABLE 学生资料表
(
学号 int PRIMARY KEY,
姓名 varchar(20),
性别 varchar(1) DEFAULT ‘男’ ,
出生日期 datetime,
籍贯 varchar(10),
班级 int DEFAULT 201301
);

添加约束

主键约束 语法格式:
ALTER TABLE 表名
ADD CONSTRAINT 约束名 PRIMARY KEY(列名);
例子:
ALTER TABLE produtinfo
ADD CONSTRAINT PK_mypk PRIMARY KEY( id );
ALTER TABLE produtinfo
ADD CONSTRAINT PK_mypk PRIMARY KEY (id , proname );

唯一约束 语法格式:
ALTER TABLE 表名
ADD CONSTRAINT 约束名 UNIQUE(列名);
例子:
ALTER TABLE produtinfo
ADD CONSTRAINT UQ_myuq UNIQUE ( 专业 );

非空约束 语法格式:
ALTER TABLE 表名
MODIFY 列名 该列数据类型 NOT NULL ;
例子:
ALTER TABLE 学生资料表
MODIFY 性别 varchar(1) NOT NULL ;

默认值约束 语法格式:
ALTER TABLE 表名
ALTER 列名 SET DEFAULT 默认值 ;
例子:
ALTER TABLE 学生资料表
ALTER 性别 SET DEFAULT ‘男’ ;

查看当前支持的字符集 show character set ;

查看数据表的建立语句: SHOW CREATE table 表名;

查看当前数据库中所有表: SHOW tables;

查看表中的字段信息: DESCRIBE 表名;可简写为: DESC 表名;

查询表中的所有数据: select * from 表名;

删除数据库(包括该数据库中所有信息):
drop database 数据库名称;

删除数据表:
DROP table 表名;

注意:要创建表格,必须先选定数据库(USE 库名)

删除主键约束
语法格式:
ALTER TABLE 表名
DROP PRIMARY KEY;

例子:
ALTER TABLE produtinfo
DROP PRIMARY KEY;
由于主键约束在一个表中只能有一个,因此不需要指定主键名就可以删除。
删除唯一约束
语法格式:
DROP INDEX 约束名 ON 表名

例子:
DROP INDEX UQ_zy ON 班级资料表

删除默认值约束
语法格式:
ALTER TABLE 表名
ALTER 列名 DROP DEFAULT ;

例子:
ALTER TABLE 学生资料表
ALTER 性别 DROP DEFAULT ;

 

修改数据表

1)修改表名称: ALTER table 表名 rename [TO] 新表名;
注:[TO]为可选项,即可加可不加

例如:将表mytable的名称改为my_table
ALTER table mytable
rename my_table ;

2)修改字段名:ALTER table 表名
change 旧字段名 新字段名 新(原)数据类型;

例如:将mytable的字段name改为stuname,该字段原来的数据类型为CHAR(20)
ALTER table mytable
change name stuname CHAR(20);
如需在修改字段名称的同时,将该字段的数据类型改为CHAR(12 )
ALTER table mytable
change name stuname CHAR(12);

3)修改字段的数据类型:
ALTER table 表名
modify 字段名 新数据类型;

例如:将mytable表的字段name的数据类型改为CHAR(50)
ALTER table mytable
modify name CHAR(50);

4)添加字段:
ALTER table 表名
add 新字段名 数据类型;

例如:在mytable表中添加字段new的数据类型改为VARCHAR(50)
ALTER table mytable
add new CHAR(50);

5)删除字段:
ALTER table 表名
drop 字段名 ;

例如:将mytable表中的new字段删除
ALTER table mytable
drop new ;

6)修改字段的排列位置:
ALTER table 表名
modify 字段名1 数据类型 FIRST | AFTER 字段名2 ;

例如:将mytable表中的name字段修改为第一个字段,即将其位置排在第一,
name字段的数据类型为CHAR(20)
ALTER table mytable
modify new CHAR(20) first ;

例如:将mytable表中的name字段的位置移动到字段sex后面,name字段的数据类型为CHAR(20)
ALTER table mytable
modify new CHAR(20) after sex;

 

 

三、添加、更新与删除数据
1)
为表中添加记录(数据)INSERT
语法:INSERT [INTO] 表名 [(字段列表)]
VALUES (值列表)
举例1:向student表中添加一条学生记录(学生信息):
insert into student( id , name , grade)
values(1 , ’zhangsan’ , 98.5 ) ;
举例2:再向student表中添加一条学生记录(学生信息):
insert into student( name , grade , id )
values( ’lisi’ , 95 , 2 ) ;

为表中添加记录时不指定字段名
语法:INSERT [INTO] 表名 [(字段列表)]
VALUES (值列表)
举例3:再向student表中添加一条学生记录(学生信息):
如果表名后面没写字段名,则默认是向所有字段添加值,
添加值的顺序必须和字段在表中定义的顺序相同。
insert into student
values( 3, ’wangwu’ , 61.5 ) ;

为表中指定的部分字段添加记录
语法:INSERT [INTO] 表名 [(字段列表)]
VALUES (值列表)
举例4:再向student表中添加一条学生记录(学生信息):
insert into student( id , name)
values( 4, ’zhaoliu’ ) ;

INSERT的其他写法
语法:INSERT [INTO] 表名
SET 字段名1 = 值 ,字段名2 = 值 , …………
举例7:再向student表中添加一条学生记录(学生信息):
insert into student
set id = 5 , name = ‘boya’ , grade = 99 ;

INSERT的其他写法:同时添加多行数据
语法:INSERT [INTO] 表名 [(字段列表)]
VALUES (值列表),(值列表),…………
举例8:向student表一次添加三条学生记录(学生信息):
insert into student
values ( 6, ’lilei’ , 99 ) ,
( 7, ’hanmeimei’ , 100 ) ,
( 8, ’poly’ , 40.5 ) ;

举例9:向student表一次添加三条学生记录(指定字段):
insert into student ( id , name )
values ( 9, ’liubei’ ) ,
( 10, ’guanyu’ ) ,
( 11, ’zhangsan’ ) ;

2)
更改记录操作
语法:UPDATE 表名
SET 字段名1 = 更新值,字段名2 = 更新值,…………
[WHERE 更新条件]
叙述:
where 子句是判断语句,用来设定条件,限制只更新满足条件的行,如果不带where子句,则更新所有行数据。
可以一次修改多个行,多个列的数据。
举例:
将student表中的所有学生名称为"Alex"的改为"Tom":
update student
set name=‘Tom’
where name= ‘Alex’;
例3-10:更改student表中id为1的记录的name字段与grade字段的值:

UPDATE student
SET name = ‘caocao’, grade = 50
WHERE id = 1;

 

第一章:数据类型 数值类型(整数类型、浮点数类型) 日期时间类型 字符串和二进制类型 其他数据类型...

11.删除数据表

drop table 表1 表2 。。。

删除被其他表关联的主表:先删除字表,再删除主表(或取消外键约束,删除主表)

删除索引:
    DROP INDEX index_name ON tbl_name   

8.查看数据表的结构

查看表基本结构:describe 表名 或 desc 表名

mysql> desc tb_employee8;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(25) | NO   |     | NULL    |                |
| deptld | int(11)     | YES  |     | NULL    |                |
| salary | float       | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

(1)NULL:表示该列是否可以存储null值

(2)key:表示该列是否已经编制索引。pri为该列主键的一部分;uni表示该列是unique索引的一部分;mul表示在列中某个给定值允许出现多次。

 

删除表:        
DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]    

6.默认约束:指定某列的默认值。如男性同学较多,性别就可以默认为“男”。如果插入一条新的记录时没有为这个字段赋值,那么系统自动会为这个字段赋值为男。

语法规则:字段名 数据类型 default 默认值

例:mysql> create table tb_employee7(id int(11)primary key,name varchar(25)not null,deptld int(11)default 1111,salary float);

 

       create  index 索引名  on 表名 (字段名(长度)     aes|desc)
      创建索引时 字段名(长度)  中"(长度)"表示只索引最左边的几个字符

2.主键约束:主键又称主码,是表中一列或多列的组合。主键约束要求主键列的数据唯一,并且不能为空。主键能够唯一标识表中的一条记录,可以结合外键来定义不同数据表之间的关系,并且可以加快数据库查询速度。主键分为两种类型:单字段主键和多字段联合主键。

(1)单字段主键

mysql> create table tb_employee1(id int(11)primary key,name varchar(25),depld int(11),salary float);
mysql> create table tb_employee1(id int(11),name varchar(25),depld int(11),salary float,primary key(id));

(2)多字段联合主键

mysql> create table tb_employee1(id int(11),name varchar(25),depld int(11),salary float,primary key(id,name));

 

1.  
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]
2.
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement
通过select查询语句创建表时,创建的表可能和被查询表的格式定义不太一样

3.
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) } 

10.修改数据表:修改数据库中已经存在的数据表结构。常用的修改表的操作有:修改表名,修改字段数据类型或字段名,增加和删除字段,修改字段的排列位置,更改表的存储引擎,删除表的外键约束等。

(1)修改表名

alter table 旧表名 rename 新表名
例:mysql> alter table tb_dept3 rename tb_deptment3;

(2)修改字段数据类型

alter table 表名 modify 字段名 数据类型
例:mysql> alter table tb_dept1 modify name varchar(30);

(3)添加字段

alter table 表名 add 新字段名 数据类型
例1:mysql> alter table tb_dept1 add managerld int(20);(没有完整性约束条件的字段)
例2:mysql> alter table tb_dept1 add column1 varchar(12) not null;(添加有约束条件)
例3:mysql> alter table tb_dept1 add column2 int(11) first;(添加到第一列)
例4:mysql> alter table tb_dept1 add column3 int(11) after name;(添加到name后一列)

(4)删除字段

alter table 表名 drop 字段名
例:mysql> alter table tb_dept1 drop column3;

(5)修改字段排序

alter table 表名 modify 字段1 数据类型 first after 字段2
例1:mysql> alter table tb_dept1 modify id int(11) first;
例2:mysql> alter table tb_dept1 modify column2 int(11) after column1;

(6)更改表的数据引擎

alter table 表名 engine=更改后的存储引擎
例:mysql> alter table tb_dept1 engine=innodb;

(7)删除表的外键约束

alter table 表名 drop foreign key 外键约束名

(8)修改字段名

mysql> alter table customers change c_contact c_phone varchar(50);

 

InnoDB支持外键
键也称作约束,可用作索引,属于特殊索引,BTree

5.唯一性约束:要求该列唯一,允许为空,但只能出现一个空值。唯一性约束可以确保一列或者多列都不能出现重复值。

(1)在定义完列之后直接指定唯一约束

语法规则:字段名 数据类型 unique

mysql> create table tb_dept2(id int(11)primary key,name varchar(22)unique,location varchar(50));

(2)在定义完所有列之后指定唯一约束

语法规则:constraint 约束名 unique(字段名)

mysql> create table tb_dept3(id int(11)primary key,name varchar(22),location varchar(50),constraint sth unique(name));

unique和primary key的区别:一个表中可以有多个字段声明为unique,但只能有一个primary key声明;声明为primary key的列不允许有空值,但是声明为unique的字段允许空值的存在。

 

SHOW {INDEX | INDEXES | KEYS} FROM tb_name:显示指定表上的索引
DESC table_name  显示表结构

索引创建:

    CREATE INDEX index_name ON tb_name (col,...);
   col_name [(length)] [ASC | DESC]
    

7.设置数据表的属性值自动增加:在数据库应用中,经常希望在每次插入新数据时,系统会自动生成字段的主键值。可以为表主键添加auto_increment关键字来实现。在mysql中,默认情况下auto_increment初始值为1,每新增一条记录,字段自动加1.一个表只能有一个字段使用auto_increment约束,且每个字段必须为主键的一部分。auto_increment约束的字段可以是任何整数类型(tinyint/samllint/int/bigint)。

语法规则:字段名 数据类型 auto_increment

例:定义数据表tb_employee8,指定员工标号自动增加

mysql> create table tb_employee8(id int(11)primary key auto_increment,name varchar(25)not null,deptld int(11),salary float);

 

mysql> insert into tb_employee8(name,salary) values('lucy',1000),('lii',800),('cai',20000);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tb_employee8;
+----+------+--------+--------+
| id | name | deptld | salary |
+----+------+--------+--------+
|  1 | lucy |   NULL |   1000 |
|  2 | lii  |   NULL |    800 |
|  3 | cai  |   NULL |  20000 |
+----+------+--------+--------+
3 rows in set (0.00 sec)

 

创建表: 
1、直接定义一张空表; 
2、从其它表中查询出数据,并以之创建新表; 
3、以其它表为模板创建一个空表;

3.外键约束:外键用来在两个表之间建立联系,它可以是一列或者多列。一个表可以有一个或者多个外键。外键对应的是参照完整性,一个表的外键可以是空值,若不为空值,则每一个外键必须等于另一个表中主键的某个值。

下面介绍几个概念。是表中的一个字段,它可以不是本表的主键,但对应另外一个表的主键。外键的主要作用是保证数据引用的完整性,定义外键后,不允许删除在另一个表中具有关联关系的主键。例如:部分表的主键id,在表tb中有一个键deptld与这个id关联。

主表(父表):对于两个具有关联关系的表而言,相关字段中主键所在的那个表就是主表。

从表(自表):对于两个具有关联关系的表而言,相关字段中外键所在的那个表就是从表。

例:定义数据表tb_employee5,并且在该表中创建外键约束

创建一个部门表tb_dept1,表结构如下表所示

字段名称

数据类型

备注

id

int(11)

部门编号

name

varchar(22)

部门名称

location

varchar(50)

部门位置

mysql> create table tb_dept1(id int(11)primary key,name varchar(22),location varchar(50));
定义数据表tb_employee5,让它的deptld字段作为外键关联到tb_dept1的主键id:
mysql> create table tb_employee5(id int(11)primary key,name varchar(25),deptld int(11),salary float,constraint fk_emp_dept1 foreign key(deptld) references tb_dept1(id));

 

 

1.创建表:之前需要use database database_name 然后create table 表名();

例:创建员工表tb_employee1,结构如下表所示

字段名称

数据类型

备注

id

int(11)

员工编号

name

varchar(25)

员工名称

depld

int(11)

所在部门编号

salary

float

工资

mysql> create database aa;
mysql> use aa;
mysql> create table tb_employee1(id int(11),name varchar(25),depld int(11),salary float);

 

修改表定义: 
ALTER TABLE 
添加、删除、修改字段 
添加、删除、修改索引 
改表名 
修改表属性

4.非空约束(not null):是指字段的值不能为空。对于使用了非空约束的字段,如果用户在添加数据时没有指定值,数据库系统会报错。

创建数据库:
    CREATE DATABASE|SCHEMA [IF NOT EXISTS] db_name [CHARACTER SET=] [COLLATE=]  

删除数据库:
    DROP {DATABASE | SCHEMA} [IF EXISTS] db_name    

9.查看表详细结构

语法规则:show create table 表名G

mysql> show create table tb_employee8G
*************************** 1. row ***************************
       Table: tb_employee8
Create Table: CREATE TABLE `tb_employee8` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `deptld` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

 

 

语法规则:字段名 数据类型 not null

例:定义数据表tb_employee6,指定员工的名称不能为空

mysql> create table tb_employee6(id int(11)primary key,name varchar(25)not null,deptld int(11),salary float);

 

原文来自  

MySQL管理表和索引

  创建表时定义其中的字段类型时为数值时 加  unisined表示无符号数值类型(只有正数)。
               例如create table 表名 ( id int unisgned ) ; id列的值只能为正数

编辑:数据库 本文来源:2、从其它表中查询出数据,在表tb中有一个键d

关键词: