MySQL-SQL语法及其使用ppt课件

1、MySQL SQL语法及其使用,MySQL 支持的SQL语句的分类,数据库的选取、创建、丢弃和变更 USE CREATE DATABASE DROP DATABASE ALTER DATABASE,数据表及索引的创建、变更和丢弃 CREATE TABLE DROP TABLE CREATE INDEX DROP INDEX ALTER TABLE,获得关于数据库和数据表的信息 DESCRIBE SHOW,从数据表中检索信息 SELECT UNION,事务处理 BEGIN COMMIT ROLLBACK SET AUTOCOMMIT,对数据表里的信息进行检索 DELETE INSERT LOAD

2、 DATA REPLACE UPDATE,管理性语句 FLUSH GRANT REVOKE,考试计分项目,student数据表,score数据表,event数据表,absence数据表,3.1 数据库的选定、创建、丢弃和变更,数据库的选定 use db_name; use语句选定一个数据库并将它当作MySQL指定服务器连接上的默认（当前）数据库。 即使已经将某个数据库选定为当前的默认数据库，也可以通过 db_name.tbl_name形式的名字去访问其它数据库中的数据表。 select * from db_name.tbl_name; 数据库的创建 create database db_nam

3、e,数据库的丢弃 drop database db_name; 数据库的变更 alter database db_name default character set charset,3.2 数据表的创建、丢弃、索引和变更 数据表的创建 CREATE TABLE student ( name VARCHAR(20) NOT NULL, sex ENUM(F,M) NOT NULL, student_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (student_id) ); CREATE TABLE score ( student_i

4、dINT UNSIGNED NOT NULL, event_id INT UNSIGNED NOT NULL, PRIMARY KEY (event_id, student_id), scoreINT NOT NULL,CREATE TABLE event ( dateDATE NOT NULL, typeENUM (T,Q) NOT NULL, event_idINT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (event_id) ); CREATE TABLE absence ( student_idINT UNSIGNED NOT NUL

5、L, dateDATE NOT NULL, PRIMARY KEY (student_id, date),数据表的丢弃 drop table tbl_name; 数据表的索引 MySQL能够创建以下几种类型的索引： 1 普通索引 2 唯一化索引 3 供全文本使用的FULLTEXT索引 创建索引的方法 1 使用create table 语句在创建一个数据表的同时创建索引 2 使用create index 语句创建索引 create index index_name on tbl_name ( index_columns); create unique index index_name on tb

6、l_name ( index_columns); create fulltext index index_name on tbl_name ( index_columns); 3 使用alter table 语句将索引添加到现在的数据表上去 alter table tbl_name add index index_name ( index_columns); alter table tbl_name add unique index_name ( index_columns); alter table tbl_name add priamry key( index_columns); alte

7、r table tbl_name add fulltext( index_columns,索引的丢弃 drop index index_name on tbl_name; alter table tbl_name drop index index_name; 变更数据表的结构 重命名数据表 alter table tbl_name rename to new_tbl_name; 改变数据列类型 alter table tbl_name modify columns,3.3 获取关于数据库和数据表的信息 列出服务器管理着的数据库 show databases; 列出当前或指定数据库里的数据表；

8、show tables; show tables from db_name; 显示有关数据表中的数据列或索引信息 show columns from tbl_name; show index from tbl_name; 显示关于当前数据库或指定数据库中数据表的描述信息 show table status; show table status from db_name; 显示与数据表的当前结构相对应的create table语句 show create table tbl_name,3.4 插入、删除、修改数据记录,插入数据记录 可以一次性列出全部的数据 insert into tbl_nam

9、e values(value1,value2,); 可以用一条insert语句将多个数据行插入到数据表中去 insert into tbl_name values(),(),; 可以直接对数据列进行赋值，先给出数据列的名字，再列出它的值 insert into tbl_name (col_name1,col_name2,) values(value1,value2,); 删除数据记录 delete from tbl_name where where_expr; 修改数据记录 update tbl_name set which columns to change where which reco

10、rd to update,3.5 数据查询,SELECT selcet_options select_list FROM tbl_list WHERE where_expr ORDER BY col_name ASC | DESC HAVING where_expr ORDER BY col_name ASC | DESC LIMIT skip_count, SHOW_COUNT 注意： （1）可以生成统计信息 （2）通配符号 _匹配单个的任意字符 %能匹配任何一个字符序列（包括空序列在内,涉及多个数据表的查询操作 SELECT语句的基本语法 SELECT selection_list #选择

11、哪些数据列 FROM table_list 从哪些数据表选择数据行 WHERE primary_constraint 数据行必须满足哪些条件 GROUP BY grouping_colums 如何对结果分组 ORDER BY sorting_columns 如何对结果进行排序 HAVING secondary_constraint 数据行必须满足的次要条件 LIMIT count ; 对结果个数的限制 正确写出一个查询的关键是怎样才能把个有关数据表按正确的顺序关联起来,单关联 查询只设计一个数据表 mysqlSELECT * FROM student ; 全关联 在SELECT语句的FROM子

12、句给出了多个数据表的名字 关联操作 JOIN或 “ , ” SELECT student_id,date,score,type FROM event join score WHERE date = 20020906 AND type = Q AND event.event_id = score.event_id; 结果示例 SELECT ,event.date,score.score,event.type FROM event,score,student WHERE event.date=20020916 AND event.event_id=score.event_i

13、d AND score.student_id=student.student_id; 结果示例,课内练习： 查询缺勤学生的姓名和缺勤次数 SELECT student.student_id,, count(absence.date) as absences FROM student,absence WHERE student.student_id = absence.student_id GROUP BY student.student_id; 结果示例,左关联（右关联） LEFT JOIN的工作情况： 首先，要指定一个或多个用来匹配的两个数据表里的数据行的数据列。当左

14、数据表里的某个数据行与右数据表里的某个数据行相匹配时，这两个数据行的内容将被选取为一个输出行。当左数据表里的某个数据行在右数据表里没有找到任何匹配时，它也将被选取为一个输出行，但这时它将与右数据表里的一个“伪造”数据行（这个“伪造”数据行的所有数据列都将被设置为NULL）进行组合。 DELETE FROM score WHERE student_id 19 ; SELECT student.* ,score.* FROM student LEFT JOIN score ON student.student_id = score.student_id ; 结果示例,左关联能够将左数据表有的，而在

15、右数据表中没有匹配的数据行找出来 SELECT student.* FROM student LEFT JOIN score ON student.student_id = score.student_id WHERE score.score IS NULL; 结果示例,课堂练习,找出考试缺勤的同学，以便安排补考 SELECT student.student_id,,event.event_id,event.date,event.type FROM student,event LEFT JOIN score ON student.student_id = score.s

16、tudent_id AND event.event_id = score.event_id WHERE score.score IS NULL ORDER BY student.student_id,event.event_id ; 结果示例,使用子选择（从MySQL 4.1 版本才开始支持） 允许将一个SELECT查询嵌套在另一个SELECT查询当中 SELECT * FROM score WHERE event_id IN ( SELECT event_id FROM event WHERE type=Q ) ； 结果示例 使用子选择来生成一个参考值 由内层的SELECT语句来检索出一个数

17、据值，然后把这个数据值用在外层SELECT语句的比较操作中。 SELECT * FROM score WHERE event_id = ( SELECT event_id FROM event WHERE date =20020923 AND type=Q ) ； 结果示例 EXISTS 和 NOT EXISTS IN 和 NOT IN,EXISTS 和 NOT EXISTS子选择 这两种形式的子选择将把由外层查询检索到的数据值传递给内部 查询，看它们是否满足内层查询所给出的匹配条件。 EXISTS 和 NOT EXISTS子选择非常适合用来检索某个数据表在另一个数据表里有或者没有匹配的记录。

18、 SELECT * FROM student WHERE EXISTS ( SELECT * FROM score WHERE student.student_id = score.student_id ) ; 结果示例 SELECT * FROM student WHERE NOT EXISTS ( SELECT * FROM score WHERE student.student_id = score.student_id ) ; 结果示例,IN 和 NOT IN子选择 在IN 和 NOT IN子选择里，内层SELECT语句应该返回且只返回一个数据列，这个数据列的值将由外层SELECT语句

19、中的比较操作进行求值。 SELECT * FROM student WHERE student_id IN ( SELECT student_id FROM score ); SELECT * FROM student WHERE student_id NOT IN ( SELECT student_id FROM score ) ; 可以将子选择改写为关联查询： （1）匹配型子选择查询的改写 SELECT * FROM table1 WHERE column1 IN ( SELECT column2a FROM table2 WHERE column2b = value ) 改写为 SELE

20、CT table1. * FROM table1,table2 WHERE table1.column1 = table2.column2a AND table2.column2b = value ),SELECT * FROM score WHERE event_id IN ( SELECT event_id FROM event WHERE type = T ) ; 结果示例 改为 SELECT score.* FROM score,event WHERE score.event_id = event.event_id AND event.type = T ; 结果示例,2）非匹配（即缺失

21、）型子选择查询的改写 SELECT * FROM table1 WHERE column1 NOT IN ( SELECT column2 FROM table2 ）; 改写为 SELECT table1. * FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column2 WHERE table2.column2 IS NULL ; SELECT * FROM student WHERE student_id NOT IN ( SELECT student_id FROM absence ); 结果示例 改写为 SELECT s

22、tudent. * FROM student LEFT JOIN absence ON student.student_id = absence.student_id WHERE absence.student_id IS NULL ; 结果示例,涉及多个表的UNION查询 依次从多个数据表选取记录 SELECT * FROM student UNION select * FROM score 结果示例 UNION UNION语句的特点： （1）在UNION查询的结果集中，输出列的名字和类型将由UNION查询中的第一个SELECT所选取的数据列的名字和类型来决定。UNION查询的第二个以及以后

23、的SELECT所选取的数据列的个数必须和第一个SELECT相同，但它们的名字和类型却不必相同 （2）在默认的情况下，UNION会把结果集中的重复项去掉，如果想保留重复项，在第一个UNION的后面加上关键字ALL。 （3）如果想对UNION结果进行排序，可以在最后一个SELECT语句的后面加上一个ORDER BY子句，这个ORDER BY子句将作用于UNION查询的最终结果。UNION查询中的各个SELECT语句也允许有它自己的ORDER BY子句。在这样做时必须把SELECT语句和它的ORDER BY子句括起来,4）LIMIT语句也能用在UNION查询中，具体用法于ORDER BY差不多。 （

24、5）UNION查询可以利用不同的筛选条件去选取同一个数据表的不同子集。 涉及多个数据表的删除和修改操作 将所涉及的数据表列举在FORM子句里，再把用来找出待删除记录的匹配条件写在WHERE子句中。 DELETE table1 FROM table1,table2 WHERE table1.id = table2.id ; 涉及多个数据表的DELETE语句还允许用同一条语句从多个数据表里同时把有关的记录都删除掉。 DELETE table1,table2 FROM table1,table2 WHERE table1.id = table2.id ; DELETE student,score F

25、ROM student,score WHERE student.student_id = score.student_id AND student.student_id = 1 ; 结果示例 涉及多个数据表的删除操作还可以利用外键机制来实现,事务处理 事务是一组SQL语句，在执行时被视为一个不可中断的逻辑单元。 事务处理的四个特征： （1）原子性。组成事务的语句形成一个逻辑单元，不能只执行其中的一部分。 （2）一致性。在事务处理执行的前后，数据库是一致的。 （3）隔离性。一个事务处理对另一个事务处理没有影响。 （4）持续性。当事务处理成功执行结束后，其效果在数据库中被永久的记录下来。 进行事务

26、处理的步骤：先使用BEGIN语句来禁止自动提交模式，然后依次执行事务的各条语句，最后如果事务处理过程中没有发生错误，用COMMIT语句来提交事务并使修改生效，否则发出ROLLBACK语句来撤销那些修改。 CREATE TABLE books ( id VARCHAR(8) PRIMARY KEY, title VARCHAR(96), price FLOAT, description VARCHAR(30) ) type = innodb; 示例,外键与引用完整性 外键关系允许把某一个数据表里的一个索引声明为与另一个数据表里的一个索引有关联关系，还允许对外键所在的数据表设置一些操作处理方面的约

27、束条件。数据库将根据外键关系所定义的规则来维护引用完整性。 MySQL里的外键支持是由InnoDB数据表处理程序提供的。 父表：是包含着原始键值的数据表。 子表：是引用父表中键值的数据表，这种引用使它和父表构成依赖关系 父表和子表是通过父表中的键值而关联在一起的，具体的说，就是子表中的索引将引用父表中的索引，它的值或者必须与父表中的某个值相匹配，或者将被设置为NULL，以表明父表中没有与之对应的记录。 外键可以设置为不能为NULL的情况，此时，每一个键值都必须与父表中的某个值相匹配。 语法 FOREIGN KEY index_name ( index_columns ) REFERENCES

28、tbl_name ( index_columns ) ON DELETE action,FOREIGN KEY后面的index_columns部分列出了构成子表中的这个索引的各有关数据列，它们必须和父表中的索引值相匹配 REFERENCES子句中的tbl_name 和 index_columns 分别给出了父表的名字和与子表中的外键相对应的父表索引数据列的名字。 REFERENCES子句里的index_columns 部分必须与关键字FOREIGN KEY后面的index_columns 部分有相同数目的数据列。 ON DELETE 用来设定在父表记录被删除时子表将发生什么情况。 ON DEL

29、ETE CASCADE 级联删除 ON DELETE SET NULL 设置为NULL 注意事项： （1）构成外键关系的父表数据列和子表数据列在它们各自所在的数据表中都必须被索引。 （2）父表索引和子表索引中相互对应的数据列必须是兼容的类型,管理MySQL用户帐户 指定哪些用户可以连接到MySQL服务器？ 它们可以从哪些地方去连接MySQL服务器？ 连接上MySQL服务器后能进行怎样的操作？ 这些信息存放在mysql数据库中的各个权限表中。 GRANT： 创建MySQL帐户并设定它的权限。 REVOKE：收回现有的MySQL帐户的权限。 GRANT和REVOKE语句会影响到的四个权限表,1）创

30、建MySQL用户帐户并进行授权 GRANT privileges privileges ( columns ) ON what TO account IDENTIFIED BY passwod； privileges 授予有关帐户的权限,columns 有关权限将作用于哪些数据列上（只有在需要设置数据列级权限时才必须给出这条语句）。 what 有关权限的级别。最高级别是全局级，即给定权限将作用于所有的数据库和所有的数据表；可以将全局级权限视为超级用户权限。有关权限还可以被设定为数据库级、数据表级或者数据列级（给出一条columns子句时,account 被授有关权限的帐户。 account 值

31、以user_namehost_name格式给出的一个用户名和一个主机名构成。 passwod 有关帐户的口令。 示例 从某一台主机访问 GRANT ALL ON ksjf.* TO heyanlocalhost IDENTIFIED BY 12345; GRANT ALL ON ksjf.* TO IDENTIFIED BY 12345; GRANT ALL ON ksjf.* TO heyan IDENTIFIED BY 12345; 从任意主机访问 GRANT ALL ON ksjf.* TO heyan IDENTIFIED BY 12345; 从一组有限的主机

32、访问 GRANT ALL ON ksjf.* TO heyan. IDENTIFIED BY 12345; GRANT ALL ON ksjf.* TO heyan192.168.128.% IDENTIFIED BY 12345,示例1 从某一台主机访问 GRANT ALL ON ksjf.* TO heyanlocalhost IDENTIFIED BY 12345; GRANT ALL ON ksjf.* TO IDENTIFIED BY 12345; GRANT ALL ON ksjf.* TO heyan IDENTIFIED BY 12345; 从任意主机访问 GRANT ALL ON ksjf.* TO heyan IDENTIFIED BY 12345; 从一组有限的主机访问 GRANT ALL ON ksjf.* T