MySQL-sql语句练习.doc

MySQL中模式就是数据库SHOW DATABASES;show databases;罗列所有数据库名称CREATE DATABASE create database TEST;创建名为TEST的数据库DROP DATABASE drop database TEST;删除名为TEST的数据库USE use TEST;使用名为TEST的数据库SHOW TABLESshow tables;显示当前数据库所有表格SHOW FULL COLUMNS FROM show full columns from 在MySQL数据库中显示表的结构,如果加上full则更加详细DESC desc TEST;查看表TEST的结构，同show columns from TESTSHOW CREATE TABLE show create table TEST;查看表TEST的所有信息，包括建表语句创建基本表:CREATE TABLE ( 列级完整性约束条件, 列级完整性约束条件,);create table STUDENT(studentId int(30) primary key,name varchar(255),address varchar(255);创建名为STUDENT的表格，有属性studentId,name,address,其中studentId为主键create table TEACHER(teacherId int(30),name varchar(255),age int(20),studentId int(30),primary key (teacherId),foreign key (studentId) references STUDENT(studentId);创建名为TEACHER的表格，其中teacherId为主键，studentId为外键，引用STUDENT表的主键studentId修改基本表:ALTER TABLE RENAME alter table TEACHER rename S;将TEACHER表的表名改为SALTER TABLE ADD COLUMN alter table TEACHER add column ADDRESS varchar(255);在表名为TEACHER的表中加入名为ADDRESS的列ALTER TABLE CHANGE COLUMN alter table TEACHER change column ADDRESS address varchar(230);修改TEACHER表中的列，将ADDRESS的列名改为addressALTER TABLE DROP COLUMN alter table TEACHER drop column address;删除列名为address的列,column可有可无删除基本表：DROP TABLE RESTRICT|CASCADE删除表格drop table STUDENT restrict;删除STUDENT表。受限制的删除，欲删除的基本表不能被其他表的约束所引用(如check,foreign key等约束)，不能有视图，不能有触发器，不能有存储过程或函数等。drop table STUDENT cascade;若选择cascade，则该表删除没有限制。在删除基本表的同时，相关的依赖对象，例如视图，都将被一起删除。但是我在MySQL测试的时候给错误提示Cannot delete or update a parent row: a foreign key constraint fails，不予以删除，不知道是什么原因。关于完整约束性：参考文章：完整性约束的SQL定义ALTER TABLE ADD CONSTRAINT alter table teacher add constraint pk_teacher_id primary key teacher(id);在teacher表中增加名为pk_teacher_id的主键约束。ALTER TABLE DROP alter table teacher drop primary key;删除teacher表的主键约束。alter table student add constraint fk_student_teacher foreign key student(teacherId) references teacher(id);在student表中增加名为fk_student_teacher的约束条件，约束条件为外键约束。索引的建立与删除:索引的建立：CREATE UNIQUE|CLUSTER INDEX ON ( 次序, 次序);UNIQUE 表明此索引的每一个索引值只对应唯一的数据记录。CLUSTER 表示要建立的索引是聚簇索引。create unique index id_index on teacher(id asc);对teacher表的id列建立unique索引，索引名为id_index索引的删除：DROP INDEX ON drop index id_index on teacher;在teacher表中删除索引，索引名为id_index另外的方法：新建索引：ALTER TABLE ADD UNIQUE|CLUSTER INDEX ( , )alter table teacher add unique index id_index(id asc);在teacher表中对id列升序建立unique索引，索引的名字为id_index删除索引：ALTER TABLE DROP INDEX alter table teacher drop index id_index;删除teacher表名为id_index的索引数据库索引的建立有利也有弊，参考文章：数据库索引的作用和优点缺点（一）数据库索引的作用和优点缺点（二）数据库建立索引的原则数据查询：SELECT ALL|DISTINCT ,FROM WHERE GROUP BY HAVING ORDER BY ASC|DESC , ASC|DESC；查询经过计算的值：select teacherId as id,salary - 100 as S from teacher;查询经过计算的值，从teacher表中查询出teacherId字段，别名为id,并且查询出salary字段减去100后的字段，别名为S使用函数和字符串：select teacherid as id,birth,salary - 20 as SA, lower(name) from teacher;可以是字符串常量和函数等,birth 为字符串常量，lower(name)为函数，将name字段以小写字母形式输出消除取值重复的行：select distinct name from teacher;如果没有指定DISTINCT关键词，则缺省为ALL.查询满足条件的元组：WHERE子句常用的查询条件:查询条件谓词比较=, , =, =, !=, , !, ! 500;select * from teacher where salary 500;(2)确定范围：select * from teacher where salary between 300 and 1000；select * from teacher where salary not between 500 and 1000(3)确定集合select * from teacher where name in(test,test2);select * from teacher where name not in(test,test2);(4)字符匹配:NOT LIKE ESCAPE 可以是一个完整的字符串，也可以含有通配符%和_%代表任意长度(长度可以是0)的字符。例如a%b表示以a开头，以b结尾的任意长度的字符串。如acb,addgb,ab_代表任意单个字符。例如a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb,afb等都满足该匹配串。select * from teacher where name like %2%；select * from teacher where name like _e%d；注意一个汉字要占两个字符的位置。(5)涉及空值查询：select * from teacher where name is null;select * from teacher where name is not null;注意这里的is不能用符号(=)代替。(6)多重条件查询：select * from teacher where name = test and salary between 400 and 800;select * from teacher where name like %s% or salary = 500;ORDER BY子句：ORDER BY 子句对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列，缺省值为(ASC)select salary from teacher order by salary asc;select * from teacher order by name desc,salary asc;聚集函数(aggregate functions):COUNT(DISTINCT|ALL*) 统计元组个数COUNT(DISTINCT|ALL) 统计一列值的个数SUM(DISTINCT|ALL) 计算一列值的总和AVG(DISTINCT|ALL) 计算一列值的平均值MAX(DISTINCT|ALL) 求一列值中的最大值MIN(DISTINCT|ALL) 求一列值中的最小值缺省值为ALLselect count(distinct name) from teacher;查询没有重复的名字的个数select count(*) from teacher;查询teacher表格总记录数select sum(salary) from teacher;查询teacher表的salary字段的总和select avg(salary) from teacher;查询teacher表的salary字段的平均值select max(salary) from teacher;查询teacher表的salary字段的最大值select min(salary) from teacher;查询teacher表的salary字段的最小值GROUP BY子句：GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组。对查询结果分组的目的是为了细化聚集函数的作用对象。分组后聚集函数将作用于每一个组，即每一组都有一个函数值。select cno,count(*) from teacher group by cno;对teacher表格按照cno分组，并算出每组里面有多少个元素如果分组后还要按照一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING语句指定筛选条件。select cno,count(*) from teacher group by cno having count(*) = 4;对teacher表格按照cno分组，并算出每组里面有多少个元素，得到元素个数大于等于4的值连接查询：连接查询是关系数据库中最主要的的查询，包括等值连接查询，自然连接查询，非等值连接查询，自身连接查询，外连接查询和复合条件连接查询等。等值与非等值连接查询：连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词，格式为：. .其中比较运算符主要有：=，, =, =, !=(或)等select s.*,t.* from student as s,teacher as t where s.teacherid = t.teacherid;等值连接查询，将student表和teacher的信息连接查询出来，连接条件是s.teacherid = t.teacherid自身连接：一个表与自身进行连接，称为自身连接select , from people as teacher,people as student where = student.teacher;自身连接查询，在people表里有两种角色，一种是教师，一种是学生，利用自身连接查询，得到name字段和teacher字段相等的元组外连接：左外连接列出左边关系中所有元组，右外连接列出右边关系中所有元组。左外连接：SELECT , FROM LEFT OUTER JOIN ON 右外连接：SELECT , FROM RIGHT OUTER JOIN ON select s.sno,,o, from student as s left outer join class as c on (o = o);student表和class表进行左外连接，连接条件是o=oselect o,,s.sno, from student as s right outer join class as c on (o = o);student表和class表进行右外连接，连接条件为o=ostudent表数据：+-+-+-+| sno | cno | name |+-+-+-+| 1 | 1 | 地心 | 2 | 2 | 华雄 | 3 | 1 | 孝慈 | 4 | 3 | 必须 |+-+-+-+class表数据：+-+-+-+| cid | cno | name |+-+-+-+| 1 | 1 | 化学 | 2 | 2 | 物理 | 3 | 3 | 政治 |+-+-+-+左外连接效果：+-+-+-+-+| sno | name | cno | name |+-+-+-+-+| 1 | 地心 | 1 | 化学 | 2 | 华雄 | 2 | 物理 | 3 | 孝慈 | 1 | 化学 | 4 | 必须 | 3 | 政治 |+-+-+-+-+右外连接效果：+-+-+-+-+| cno | name | sno | name |+-+-+-+-+| 1 | 化学 | 1 | 地心 | 1 | 化学 | 3 | 孝慈 | 2 | 物理 | 2 | 华雄 | 3 | 政治 | 4 | 必须 |+-+-+-+-+MySQL不支持全外连接！复合条件连接：WHERE子句中可以有多个连接条件，称为复合条件连接select s.sno,,,s.score from student s,class c where o = o and s.score (select avg(score) from student s2where = );查询学生的大于各科平均成绩的科目以上是相关子查询。带有ANY(SOME)或ALL谓词的子查询子查询返回单值时可以用比较运算符，但返回多值时要用ANY(有的系统用SOME)或ALL谓词修饰。使用ALL或ALL谓词时必须使用比较运算符。ANY 大于子查询结果的某个值ALL 大于子查询结果的所有值ANY 小于子查询结果的某个值=ANY 大于等于子查询结果的某个值=ALL 大于等于子查询结果的所有值=ANY 小于等于子查询结果的某个值=ALL 小于等于子查询结果的所有值=ANY 等于子查询结果的某个值=ALL 等于子查询结果的所有值(通常没有实际意义)!=(或)ANY 不等于子查询结果的某个值!=(或)ALL 不等于子查询结果的任何一个值select name,score from student where score = all (select score from student);查询成绩最小的学生姓名和成绩集合查询：SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。集合操作主要包括并操作(UNION),交操作(INTERSECT),差操作(EXCEPT)。参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。MySQL数据库不支持INTERSECT和EXCEPT操作！select * from student where cno=1unionselect * from student where cno=2;查询班级1和班级2所有学生信息数据更新：插入数据：插入元组：INSERTINTO (),VALUES (,)；例子：insert into student (cno,name,score) values (2,横切,85);插入子查询结果：INSERTINTO (,)子查询;例子：insert into studentcopy select * from student;将student表的信息全部复制到studentcopy表中修改数据：UPDATE SET =,=WHERE 修改某一元组的值：update studentcopy set score=80 where sno=1;修改多个元组的值:update studentcopy set score=score-20;删除数据：DELETEFROM WHERE ;删除某一元组：delete from studentcopy where sno=1;删除多个元组：delete from studentcopy;带子查询的删除语句：delete from studentcopy where cno in (select cno from student