mysqlsql语法及其使用.ppt

《mysqlsql语法及其使用.ppt》由会员分享，可在线阅读，更多相关《mysqlsql语法及其使用.ppt（70页珍藏版）》请在三一办公上搜索。

1、MySQL SQL语法及其使用,MySQL 支持的SQL语句的分类,数据库的选取、创建、丢弃和变更USECREATE DATABASEDROP DATABASEALTER DATABASE,数据表及索引的创建、变更和丢弃CREATE TABLEDROP TABLECREATE INDEXDROP INDEXALTER TABLE,获得关于数据库和数据表的信息DESCRIBESHOW,从数据表中检索信息SELECT UNION,事务处理BEGINCOMMITROLLBACKSET AUTOCOMMIT,对数据表里的信息进行检索DELETEINSERTLOAD DATAREPLACEUPDATE,

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

3、数据库的变更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_idINT UNSIGNED NOT NULL,event_id INT UNSIGNED NO

4、T 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 NULL,dateDATE NOT NULL,PRIMARY KEY(student_id,date);,数据表的丢弃 drop t

5、able 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 tbl_name(index_columns);create fulltext index index_name on tbl_name(index_

6、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);alter table tbl_name add fulltext(index_columns);,索引的丢弃 drop index index_name on tbl_name;a

7、lter 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;列出当前或指定数据库里的数据表；show tables;show tables from db_name;显示有关数据表中的数据列或索引信息show columns from tbl_name;show index from

8、 tbl_name;显示关于当前数据库或指定数据库中数据表的描述信息show table status;show table status from db_name;显示与数据表的当前结构相对应的create table语句show create table tbl_name;,3.4 插入、删除、修改数据记录,插入数据记录可以一次性列出全部的数据insert into tbl_name values(value1,value2,);可以用一条insert语句将多个数据行插入到数据表中去insert into tbl_name values(),(),;可以直接对数据列进行赋值，先给出数据列的

9、名字，再列出它的值insert into tbl_name(col_name1,col_name2,)values(value1,value2,);删除数据记录delete from tbl_name where where_expr;修改数据记录update tbl_nameset which columns to changewhere which record to update;,3.5 数据查询,SELECT selcet_options select_list FROM tbl_list WHERE where_expr ORDER BY col_name ASC|DESC HAV

10、ING where_expr ORDER BY col_name ASC|DESC LIMIT skip_count,SHOW_COUNT 注意：（1）可以生成统计信息（2）通配符号_匹配单个的任意字符%能匹配任何一个字符序列（包括空序列在内）。,涉及多个数据表的查询操作 SELECT语句的基本语法 SELECT selection_list#选择哪些数据列 FROM table_list 从哪些数据表选择数据行 WHERE primary_constraint 数据行必须满足哪些条件 GROUP BY grouping_colums 如何对结果分组 ORDER BY sorting_colu

11、mns 如何对结果进行排序 HAVING secondary_constraint 数据行必须满足的次要条件 LIMIT count;对结果个数的限制正确写出一个查询的关键是怎样才能把个有关数据表按正确的顺序关联起来。,单关联查询只设计一个数据表mysqlSELECT*FROM student;全关联在SELECT语句的FROM子句给出了多个数据表的名字关联操作 JOIN或“,”SELECT student_id,date,score,typeFROM event join scoreWHERE date=20020906 AND type=Q AND event.event_id=score

12、.event_id;结果示例SELECT student.name,event.date,score.score,event.typeFROM event,score,studentWHERE event.date=20020916 AND event.event_id=score.event_id AND score.student_id=student.student_id;结果示例,课内练习：查询缺勤学生的姓名和缺勤次数SELECT student.student_id,student.name,count(absence.date)as absencesFROM student,abs

13、enceWHERE student.student_id=absence.student_idGROUP BY student.student_id;结果示例,左关联（右关联）LEFT JOIN的工作情况：首先，要指定一个或多个用来匹配的两个数据表里的数据行的数据列。当左数据表里的某个数据行与右数据表里的某个数据行相匹配时，这两个数据行的内容将被选取为一个输出行。当左数据表里的某个数据行在右数据表里没有找到任何匹配时，它也将被选取为一个输出行，但这时它将与右数据表里的一个“伪造”数据行（这个“伪造”数据行的所有数据列都将被设置为NULL）进行组合。DELETE FROM score WHERE

14、 student_id 19;SELECT student.*,score.*FROM student LEFT JOIN scoreON student.student_id=score.student_id;结果示例,左关联能够将左数据表有的，而在右数据表中没有匹配的数据行找出来SELECT student.*FROM student LEFT JOIN scoreON student.student_id=score.student_idWHERE score.score IS NULL;结果示例,课堂练习：,找出考试缺勤的同学，以便安排补考SELECT student.student_

15、id,student.name,event.event_id,event.date,event.typeFROM student,event LEFT JOIN score ON student.student_id=score.student_id AND event.event_id=score.event_idWHERE score.score IS NULLORDER BY student.student_id,event.event_id;结果示例,使用子选择（从MySQL 4.1 版本才开始支持）允许将一个SELECT查询嵌套在另一个SELECT查询当中SELECT*FROM sc

16、oreWHERE event_id IN(SELECT event_id FROM event WHERE type=Q)；结果示例使用子选择来生成一个参考值 由内层的SELECT语句来检索出一个数据值，然后把这个数据值用在外层SELECT语句的比较操作中。SELECT*FROM score WHERE event_id=(SELECT event_id FROM event WHERE date=20020923 AND type=Q)；结果示例EXISTS 和 NOT EXISTSIN 和 NOT IN,EXISTS 和 NOT EXISTS子选择 这两种形式的子选择将把由外层查询检索到的

17、数据值传递给内部 查询，看它们是否满足内层查询所给出的匹配条件。EXISTS 和 NOT EXISTS子选择非常适合用来检索某个数据表在另一个数据表里有或者没有匹配的记录。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子

18、选择 在IN 和 NOT IN子选择里，内层SELECT语句应该返回且只返回一个数据列，这个数据列的值将由外层SELECT语句中的比较操作进行求值。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 F

19、ROM table2 WHERE column2b=value)改写为 SELECT table1.*FROM table1,table2 WHERE table1.column1=table2.column2a AND table2.column2b=value);,SELECT*FROM scoreWHERE event_id IN(SELECT event_id FROM event WHERE type=T);结果示例改为SELECT score.*FROM score,eventWHERE score.event_id=event.event_id AND event.type=T;

20、结果示例,（2）非匹配（即缺失）型子选择查询的改写 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 studentWHERE student_id NOT IN(SELECT student_id FROM absence);结果示例改写为SELECT student

21、.*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结果示例UNIONUNION语句的特点：（1）在UNION查询的结果集中，输出列的名字和类型将由UNION查询中的第一个SELECT所选取的数据列的名字和类型来决定。UNION查询的第二个以及以后的SELECT所选取的数据列的个数必须和第一

22、个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差不多。（5）UNION查询可以利用不同的筛选条件去选取同

23、一个数据表的不同子集。涉及多个数据表的删除和修改操作 将所涉及的数据表列举在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 FROM student,score WHERE student.stu

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

25、过程中没有发生错误，用COMMIT语句来提交事务并使修改生效，否则发出ROLLBACK语句来撤销那些修改。CREATE TABLE books(id VARCHAR(8)PRIMARY KEY,title VARCHAR(96),price FLOAT,description VARCHAR(30)type=innodb;示例,外键与引用完整性外键关系允许把某一个数据表里的一个索引声明为与另一个数据表里的一个索引有关联关系，还允许对外键所在的数据表设置一些操作处理方面的约束条件。数据库将根据外键关系所定义的规则来维护引用完整性。MySQL里的外键支持是由InnoDB数据表处理程序提供的。父表：

26、是包含着原始键值的数据表。子表：是引用父表中键值的数据表，这种引用使它和父表构成依赖关系 父表和子表是通过父表中的键值而关联在一起的，具体的说，就是子表中的索引将引用父表中的索引，它的值或者必须与父表中的某个值相匹配，或者将被设置为NULL，以表明父表中没有与之对应的记录。外键可以设置为不能为NULL的情况，此时，每一个键值都必须与父表中的某个值相匹配。语法FOREIGN KEY index_name(index_columns)REFERENCES tbl_name(index_columns)ON DELETE action,FOREIGN KEY后面的index_columns部分列出了

27、构成子表中的这个索引的各有关数据列，它们必须和父表中的索引值相匹配REFERENCES子句中的tbl_name 和 index_columns 分别给出了父表的名字和与子表中的外键相对应的父表索引数据列的名字。REFERENCES子句里的index_columns 部分必须与关键字FOREIGN KEY后面的index_columns 部分有相同数目的数据列。ON DELETE 用来设定在父表记录被删除时子表将发生什么情况。ON DELETE CASCADE 级联删除 ON DELETE SET NULL 设置为NULL注意事项：（1）构成外键关系的父表数据列和子表数据列在它们各自所在的数据表

28、中都必须被索引。（2）父表索引和子表索引中相互对应的数据列必须是兼容的类型,管理MySQL用户帐户 指定哪些用户可以连接到MySQL服务器？它们可以从哪些地方去连接MySQL服务器？连接上MySQL服务器后能进行怎样的操作？这些信息存放在mysql数据库中的各个权限表中。GRANT：创建MySQL帐户并设定它的权限。REVOKE：收回现有的MySQL帐户的权限。GRANT和REVOKE语句会影响到的四个权限表,（1）创建MySQL用户帐户并进行授权GRANT privileges privileges(columns)ON what TO account IDENTIFIED BY passw

29、od；privileges 授予有关帐户的权限。,columns 有关权限将作用于哪些数据列上（只有在需要设置数据列级权限时才必须给出这条语句）。what 有关权限的级别。最高级别是全局级，即给定权限将作用于所有的数据库和所有的数据表；可以将全局级权限视为超级用户权限。有关权限还可以被设定为数据库级、数据表级或者数据列级（给出一条columns子句时）。,account 被授有关权限的帐户。account 值以user_namehost_name格式给出的一个用户名和一个主机名构成。passwod 有关帐户的口令。示例从某一台主机访问GRANT ALL ON ksjf.*TO heyanloc

30、alhost IDENTIFIED BY 12345;GRANT ALL ON ksjf.*TO IDENTIFIED BY 12345;GRANT ALL ON ksjf.*TO heyan192.168.128.3 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.*TO heyan192.168.128.%IDENTIFIED BY 123

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

32、 ksjf.*TO heyan192.168.128.%IDENTIFIED BY 12345;示例2GRANT SELECT ON ksjf.score TO assistantlocalhost IDENTIFIED BY 12345;GRANT UPDATE(score)ON ksjf.score TO assistantlocalhost;,注意：权限表里的记录不“追随”更名操作。例如，如果更改了某个数据表的名字，关联在该数据表上的所有权限将全部失效。这一原则适用于数据库级、数据表级和数据列级的各种权限。（2）收回权限和删除用户REVOKE语句和GRANT语句在语法上非常相似REVOK

33、E privileges(columns)ON what FROM account;示例GRANT ALL ON ksjf.*TO heyanlocalhost IDENTIFIED BY 12345;REVOKE DELETE,UPDATE ON ksjf.*FROM heyanlocalhost;REVOKE语句会收回某个帐户的权限，但不会删除这个帐户；要想真正的删除某个帐户，必须明确的使用DELETE语句把它在user权限表中的记录项删除掉。C:mysql-u root-pmysqlUSE mysql;mysqlDELETE FROM user WHERE User=heyan AND

34、Host=localhost;mysqlFLUSH PRIVILEGES;/*通知MySQL服务器重新加载权限表*/,（3）修改口令或重新设置丢失的口令利用主机名Host和用户名User值从user权限表里查出有关帐户的记录项并修改它的Password值mysqlUPDATE SET Password=PASSWORD(network)-WHERE User=heyan AND Host=localhost;mysqlFLUSH PRIVILEGES;/*通知MySQL服务器重新加载权限表*/SET PASSWORD FOR heyanlocalhost=PASSWORD(network);GRANT USAGE ON ksjf.*TO heyanlocalhost IDENTIFIED BY network;课后自学熟练掌握SQL查询语言,