数据库操作基础.ppt

第12章 数据库操作基础,一、mysql概述,作为一款优秀的开源软件，MySQL的功能不比那些价格昂贵的数据库差，是一个真正的多用户、多线程SQL数据库服务器，是比较流行的关系数据库。相对其他数据库而言，可以用16个字来概括MySQL，“短小精悍、功能齐全、运行极快、完全免费”。在重要的安全问题上，从Linux系统上移植过来的MySQL 更具备了其他数据库所无法比拟的优势。使用上也越来越人性化，有多款图形界面的支持，操作越来越简单。在中小型企业网、门户网的应用中占据了相当大的优势，甚至在大型项目的开发中，MySQL也能应对自如。,MySQL的特点（1）MySQL是一个关系数据库管理系统，把数据存储在表格中，使用标准的结构化查询语言SQL进行访问数据库。（2）MySQL是完全免费的，在网上可以任意下载，并且可以查看到它的源文件，进行必要的修改。（3）MySQL服务器的功能齐全，运行的速度极快，十分可靠，有很好安全性。（4）MySQL服务器在客户、服务器或嵌入系统中使用，是一个客户机服务器系统，能够支持多线程，支持多个不同的客户程序和管理工具。SQL和MySQL的关系SQL（Structured Query Language，结构化查询语言），与其说是一门语言，倒不如说是一种标准，数据库系统的工业标准。大多数的RDBMS 开发商的SQL都基于这个标准，虽然在有些地方并不是完全一样的，但这并不妨碍对SQL的学习和使用。,下表给出SQL标准的关键字及其功能：,在MySQL中，不仅支持SQL标准，而且还对其进行了扩展，使得它能够支持更为强大的功能。下表介绍MySQL支持的SQL语句：,mysql中有三种注释方式：l 以“#”号开头直到行尾的所有内容都是注释。2以“-”号开头直到行尾的所有内容都是注释，注意在“-”后面还有一个空格。3以“/*”开始，以“*/”结束的所有内容都是注释，可以对多行进行注释。,二、如何启动、关闭mysql服务器,启动、停止MySQL服务器启动、停止MySQL服务器的方法有两种：系统服务器和 命令提示符(dos):1.通过系统服务器启动、停止MySQL从“控制面板”“管理工具”中找到“服务”选项，在“名称”列中找到“MySQL”服务，单击右键，选择启动如下图所示,2.在命令提示符下启动MySQL选择“开始”/“运行”选项，输入“cmd”，进入DOS窗口，在命令提示符下输入如下指令：net start MySQL(启动)、net stop MySQL(停止),3.如何连接MySQL服务器第一种方法直接启动mysql,在启动后的窗口中输入密码即可,第二种方法：采用命令首先设置环境变量：在命令提示符中设置或者在windows的path中设置,其次启动mysql（可省略）,采用这种方式就不需要每次都在启动和连接数据库之前在命令行窗口中执行命令设置path环境变量了,最后连接MYSQL 格式：mysql-h主机地址-u用户名-p用户密码 连接远程机器：mysql-h10.4.3.188-uptsdb-p 等价写法 mysql-host=10.4.3.188-user=ptsdb-password 连接本地机器：mysql-uroot-p 等价写法 mysql-user=root-p（注:u与root可以不用加空格，其它也一样）下面对以上参数进行解释说明。-u：用户名。-h：MySQL所在服务器地址。-p：用户密码。输入完命令语句后，按下Enter键就进入到了MySQL数据库中。,第三种打开命令提示符，切换到C:Program FilesMySQLMySQL Server 5.0bin,注意用户在连接数据库时,如果只输入mysql也可以进入，但是这样用的是一个匿名用户登陆，虽然无需用户名、密码，就可以直接登录，但是非常不安全。最好使用文中提到的连接格式，养成良好的书写习惯。如果密码在“-p”后直接给出，那么密码就是明文显示出来的，例如：MySQL u root h127.0.0.1 p 123456为了安全，我们可以输入p后就按Enter键,然后在一下行中再输入密码，这样输入的就是密文密码。,三、修改连接mysql的密码,1.通过修改MYSQL数据库中MYSQL库的USER表就用普通的UPDATE语句就可以mysql 状态下输入 usemysqlupdate user set password=password(新密码)where user=root;回显 Query OK,0 rows affected(0.00 sec)Rows matched:2 Changed:0 Warnings:0 mysql 状态下输入 FLUSH PRIVILEGES;回显Query OK,0 rows affected(0.00 sec)mysql 状态下输入 quit 或exit退出 sql,注意每个命令后都要加上一个分号;mysql 才开始执行该行命令而FLUSH PRIVILEGES指令会让已载入记忆体的 mysql 系统资料库更新 重起 mysql.在更新 root 密码后，日后要与 MySQL 连线的方法为：mysql-uroot-p新密码,2.在命令行中使用如下命令（注意不是mysql提示符下）C:Program FilesMySQLMySQL Server 5.0binmysqladmin-u root-p password 123456接下来会提示 Enter password:如果你是第一次登陆还没修改过密码，直接回车就可以了（以前初始密码是root的话输入root）。这是 root 密码就修改成了 123456。不要使用下面这种格式，否则密码就修改成了 123456 这个8位字符，而不是6位的了。C:Program FilesMySQLMySQL Server 5.0binmysqladmin-u root-p password 123456,忘记MYSQL密码的解决方案 1、net stop mysql 停止mysql服务2、执行mysqld-nt-skip-grant-tables这样启动，不需要密码就可以进入MySQL了3、此时打开一个新的命令窗口，执行mysql，进入MySQL4、修改root密码use mysqlupdate user set password=password(NEW PASSWORD)where user=root;flush privileges;5、在任务管理器里kill掉mysqld-nt进程6、net start mysql 重启MySQL即可,四、Mysql数据库管理的基本操作,常用操作将带您掌握以下知识：1.创建、删除、选择数据库2.MySQL数据库中的一些字段类型解释3.创建、删除、修改数据表4.添加、删除、修改数据,1.创建、删除、选择数据库,创建数据库使用MySQL创建数据库，格式如下：CREATE DATABASE db_cn_web;其中，db_cn_web是要创建的数据库名称，该名称必须是合法的，对于合法的数据库名称有如下一些规则。（1）不能与其他数据库重名。（2）名称可以是任意应为字母、阿拉伯数字，下划线（_）或者“$”组成，可以使用上述的任意字符开头，但不能使用单独的数字，那样会造成它与数值相混淆。（3）名称最长可为64个字符组成（还包括表、列和索引的命名），而别名最多可长达256个字符。（4）不能使用MySQL关键字作为数据库、表名。,下面通过CREATE DATABASE语句创建了一个名称为db_cn_web的数据库，并且通过SHOW DATABASES语句将MySQL服务器中的所有数据库名称都显示出来，代码如下。MySQL CREATE DATABASE db_cn_web;Query OK,1 row affected(0.01 sec)MySQLSHOW DATABASES;+-+|Database|+-+|information_schema|db_cn_web|MySQL+-+3 rows in set(0.00 sec),选择数据库USE语句用于选择一个数据库，使其成为当前默认数据库。格式如下。USE db_cn_web;例如选择名称为db_cn_web的数据库，然后查询该数据库中的表信息，代码如下。MySQL USE db_cn_web;Database changedMySQL SHOW TABLES;Empty set(0.00 sec)这时已经进入到db_cn_web的数据库中了，我们可以在这个库中建表等操作。,删除数据库删除数据库的操作可以使用DROP DATABASE语句，格式如下。DROP DATABASE db_cn_web;下面，通过DROP DATABASE语句删除名称为db_cn_web的数据库，代码如下。MySQL DROP DATABASE db_cn_web;Query OK,0 rows affected(0.00 sec)MySQL SHOW DATABASE;,显示MySQL数据库的版本：mysqlselect version();显示数据库列表:mysqlshow databases;显示表格 mysqlshow tables from mydb(数据库名);显示表结构:mysqldesc mydb.tablename;等价于mysqlshow columns from mydb.tablename;,数值类型(例如:Float,Int,Double 等)日期和时间类型(例如:Date,Time等)String类型(例如:Char,Varchar),Mysql常用的数据类型：,MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER(Integer)、SMALLINT(SmallInt)等以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。,数值类型,数值类型,作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT,Float(同义real)小(单精度)浮点数。允许的值是-3.402823466E+38到-1.175494351E-38、0和1.175494351E-38到3.402823466E+38。Double(同义DOUBLE PRECISION)普通大小(双精度)浮点数。允许的值是-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到 1.7976931348623157E+308,日期和时间类型,表示时间值的DATE和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。TIMESTAMP类型有专有的自动更新特性,日期和时间类型-Date,DATE日期。支持的范围为1000-01-01到9999-12-31。MySQL以YYYY-MM-DD格式显示DATE值，但允许使用字符串或数字为DATE列分配值。,日期和时间类型-Datetime,DATETIME日期和时间的组合。支持的范围是1000-01-01 00:00:00到9999-12-31 23:59:59。MySQL以YYYY-MM-DD HH:MM:SS格式显示DATETIME值，但允许使用字符串或数字为DATETIME列分配值。,日期和时间类型-TIMESTAMP,TIMESTAMP(Mysql 4.1版本以下）时间戳。范围是1970-01-01 00:00:00到2037年。TIMESTAMP列用于INSERT或UPDATE操作时记录日期和时间。如果你不分配一个值，表中的第一个TIMESTAMP列自动设置为最近操作的日期和时间。也可以通过分配一个NULL值，将TIMESTAMP列设置为当前的日期和时间。TIMESTAMP值返回后显示为YYYY-MM-DD HH:MM:SS格式的字符串，显示宽度固定为19个字符,日期和时间类型-Time,TIME时间。范围是-838:59:59到838:59:59。MySQL以HH:MM:SS格式显示TIME值，但允许使用字符串或数字为TIME列分配值。,日期和时间类型-Year,YEAR(2|4)两位或四位格式的年。默认是四位格式。在四位格式中，允许的值是1901到2155和0000。在两位格式中，允许的值是70到69，表示从1970年到2069年。MySQL以YYYY 格式显示YEAR值，但允许使用字符串或数字为YEAR列分配值。,日期和时间类型,下面的表显示了各类“零”值的格式。请注意如果启用NO_ZERO_DATE SQL模式，使用这些值会产生警告。,可以使用任何常见格式指定DATETIME、DATE和TIMESTAMP值：,YYYY-MM-DD HH:MM:SS或YY-MM-DD HH:MM:SS格式的字符串。允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间割符。例如，98-12-31 11:30:45、98.12.31 11+30+45、98/12/31 11*30*45和981231 113045是等价的。,YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。例如，和970523091528被解释为1997-05-23 09:15:28，但971122129015是不合法的(它有一个没有意义的分钟部分)，将变为0000-00-00 00:00:00。,YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字，假定数字对于日期类型是有意义的。例如，和830905132800被解释为 1983-09-05 13:28:00。,YYYY-MM-DD或YY-MM-DD格式的字符串。这里也允许使用“不严格的”语法。例如，98-12-31、98/12/31和981231是等价的。YYYYMMDD或YYMMDD格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。例如，19970523和970523被解释为 1997-05-23，但971332是不合法的(它有一个没有意义的月和日部分)，将变为0000-00-00。,YYYYMMDD或YYMMDD格式的数字，假定数字对于日期类型是有意义的。例如，19830905和830905被解释为1983-09-05。,无效DATETIME、DATE或者TIMESTAMP值被转换为相应类型的“零”值(0000-00-00 00:00:00、0000-00-00或者00000000000000)。,String类型,字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM等。,String类型-CHAR和VARCHAR类型,CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。例如，CHAR(30)可以占用30个字符。CHAR列的长度固定为创建表时声明的长度。最大长度为 8,000 个字符。VARCHAR列中的值为可变长字符串。最大长度为 8,000 个字符。,下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别：,char是一种固定长度的类型，varchar则是一种可变长度的类型 char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足（在检索操作中那些填补出来的空格字符将被去掉）在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）。char 固定长度，所以在处理速度上要比varchar快速很多，但是对费存储空间，所以对存储不大，但在速度上有要求的可以使用char类型，反之可以用varchar类型来实现,String类型-BINARY和VARBINARY类型,BINARY和VARBINARY类类似于CHAR和VARCHAR 它们包含字节字符串而不是字符字符串。BINARY和VARBINARY允许的最大长度一样，如同CHAR和VARCHAR，不同的是BINARY和VARBINARY的长度是字节长度而不是字符长度。,String类型-BLOB和TEXT类型,可以容纳可变数量的数据 BLOB 列被视为二进制字符串(字节字符串)。TEXT列被视为非二进制字符串(字符字符串)。Text数据类型的列可用于存储大于8kb的字符。,String类型-ENUM类型,ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。ENUM(value1,value2,.)ENUM列最多可以有65,535个截然不同的值.,SQL主要语句语法,MySQL实用工具语句（DESCRIBE语法，USE语法）数据定义语句（创建，删除，修改数据库和表）数据操作语句（对表中的数据进行查询，修改，删除等操作）,SQL主要语句语法MySQL实用工具语句,USE语法 USE db_name USE db_name语句可以通告MySQL把db_name数据库作为默认（当前）数据库使用，用于后续语句。该数据库保持为默认数据库，直到语段的结尾，或者直到发布一个不同的USE语句：,SQL主要语句语法MySQL实用工具语句,DESCRIBE语法（获取有关列的信息）DESCRIBE可以提供有关表中各列的信息。也就是创建的表中各个属性的名字和数据类型等信息,SQL主要语句语法数据定义语句,CREATE DATABASE语法CREATE TABLE语法 CREATE INDEX语法 DROP DATABASE语法 DROP INDEX语法 DROP TABLE语法 ALTER TABLE语法 RENAME TABLE语法,CREATE DATABASE语法,CREATE DATABASE用于创建数据库，并进行命名。如果要使用CREATE DATABASE，您需要获得数据库CREATE权限。CREATE DATABASE db_name；例如：Create Database test1;,CREATE TABLE语法,CREATE TABLE(列级完整性约束条件,列级完整性约束条件),)；,注：列级约束条件有两个任选项：NOT NULL，表示此列不得置NULL，在其后还可加UNIQUE任选项，表示列值不得重复。DEFAULT，当此列的值空缺时，填以缺省值例如：stu_id char(20)not null,price float default 0.01,表级完整性约束条件,主关键字约束指定表的一列或几列的组合的值在表中具有惟一性，即能惟一地指定一行记录。每个表中只能有一列被指定为主关键字，且IMAGE 和TEXT 类型的列不能被指定为主关键字，也不允许指定主关键字列有NULL 属性。语法如下：,CONSTRAINT constraint_namePRIMARY KEY（column_name）各参数说明如下：constraint_name指定约束的名称约束的名称。在数据库中应是惟一的。如果不指定，则系统会自动生成一个约束名。column_name指定组成主关键字的列名。,Create table products(id char(20)not null,price float default 0.01,constraint primary key(id);,定义外关键字约束的语法如下：CONSTRAINT FOREIGN KEY（column_name1,column_name2,column_name16）REFERENCES ref_table（ref_column1,ref_column2,ref_column16）ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION 各参数说明如下：REFERENCES指定要建立关联的表的信息。ref_table指定要建立关联的表的名称。ref_column指定要建立关联的表中的相关列的名称。,ON DELETE NO ACTION|RESTRICT指定在删除表中数据时，对关联表所做的相关操作。在子表中有数据行与父表中的对应数据行相关联的情况下，NO ACTION 是缺省值.RESTRICT：凡被基表引用的主键，不得删除ON UPDATE NO ACTION|RESTRICT 指定在更新表中数据时，对关联表所做的相关操作。在子表中有数据行与父表中的对应数据行相关联的情况下,NO ACTION 是缺省值.RESTRICT：凡被基表引用的主键，不得删除,create table orders(order_id char(8),p_id char(8),p_name char(10),constraint primary key(order_id),foreign key(p_id)references products(p_id)on delete restrict);,CREATE INDEX语法,CREATE INDEX index_name ON tbl_name(index_col_name,.)注：index_col_name:col_name(length)ASC|DESC 例如：create index stu_age on student(age ASC);,DROP DATABASE语法,DROP DATABASE db_name 删除已有的数据库,DROP INDEX语法,DROP INDEX index_name ON tbl_name 删除表中的索引,DROP TABLE语法,删除表Drop table table_name;,ALTER TABLE语法,ALTER TABLE tbl_name ADD完整性约束 DROP列名 MODIFY；ADD用于增加新列和新的完整性约束条件，新增加的列一律为空值。(index,primary key等）DROP用于删除指定的完整性约束条件（index,primary key,foreign key)MODIFY用于修改原有的列定义,Alter table aa add primary key(id);Alter table aa add name char(20);Alter table aa drop primary key;Alter table aa modify id char(100)not null;,RENAME TABLE语法,RENAME TABLE tbl_name TO new_tbl_name 对一个表重命名例如：rename table student to ss;,创建，删除视图(view),创建视图语法：Create view(,.)As select查询语句删除视图语法：Drop view 视图名,Create view stu_test(id,name)As select stu_id,name from student;Drop view stu_test;,SQL主要语句语法-数据操作语句,SelectUpdateInsertDelete,Select查询语句,SELECT ALL|DISTINCT,FROM,WHERE GROUP BY HAVING ORDER BYASC|DESC；,Select查询语句,SELECT指明需要查询的项目，一般是列名，也可以是表达式，查询表中未存储但可导出的结果。WHERE用来说明查询的条件DISTINCT任选项用来消除查询结果的重复项GROUP用来将结果按列名1的值进行分组，该属性列值相等的元组为一组ORDER将结果表按列名2的值升序或降序排序,where,查询满足条件的元组（where 子句）比较大小确定范围（BETWEENAND/Not Beteween.and）确定集合（IN,NOT IN）字符匹配（LIKE,NOT LIKE）可适用通配符%和 _。%:代表任意长度（长度可以为0）的字符串:a%_：代表任意单个字符:a_,Select*from student;Select*from student where age not between 18 and 19;Select*from student where name like a%;,Select student.name,teacher.name from student,teacher where student.select_course_id=teacher.teach_course_id;,Update修改语法,一般格式：INSERT INTO(,)VALUES(,)功能是将新元组插入指定的表中INTO中没有出现的属性列，新纪录在其上取空值属性列省略则按已有关系模式顺序插入，且其在每个属性列上均有值。Insert into student values(1,aa,asdf,1980-12-09);Insert into student(stu_id,name)values(2,bb);,INSERT INTO(,)子查询；,Insert 插入语法,一般格式：UPDATE SET=,=WHERE;功能是修改指定表中满足条件的元组SET子句给出 的值用于取代相应的属性列值。省略WHERE 子句，表示修改表中所有元组例如：Update student set address=asdf,birthday=1977-11-3 where stu_id=2;,Delete删除语法,DELETE FROM WHERE;功能是从指定表中删除满足条件的所有元组如果省略WHERE子句，表时删除表中全部元组，但表的定义仍在数据字典中。即：只删除数据。例如：Delete from student where name=“aa”;,从Mysql中导入和导出数据库结构,导入数据库 常用source 命令 进入mysql数据库控制台，如mysql-u root-p mysqluse 数据库 然后使用source命令，后面参数为脚本文件（如这里用到的.sql）mysqlsource d:/test_db.sql,使用mysqldump:(mysqldump命令位于mysql/bin/目录中)几个常用用例：1.导出整个数据库mysqldump-u 用户名-p 数据库名 导出的文件名mysqldump-u root-p dataname dataname.sql这个时候会提示要你输入root用户名的密码,输入密码后dataname数据库就成功备份在mysql/bin/目录中.,2.导出一个表mysqldump-u 用户名-p 数据库名 表名 导出的文件名mysqldump-u root-p dataname users dataname_users.sql3.导出一个数据库结构mysqldump-u wcnc-p-d-add-drop-table dataname d:wcnc_db.sql-d 没有数据-add-drop-table 在每个create语句之前增加一个drop table,