MySQL下的sql示例

例1：建立数据库查看现有数据库show databases;建立demo数据库create database demo;查看现有数据库show databases;删除数据库drop database demo;查看现有数据库show databases;例2：建立基本表如果demo数据库未建立，则先建立数据库create database demo;不选择缺省数据库的情况下建立学生表错误的create table Student (Sno char(9) not null,Sname char(8) not null,Ssex char(2) not null default 男,Sage tinyint(2),Sdept char(2);create table demo.Student (Sno char(9) not null,Sname char(8) not null,Ssex char(2) not null default 男,Sage tinyint(2),Sdept char(2);选择demo为缺省的数据库Use demo;建立课程表不用再demo.SC不用再demo.Coursecreate table Course (Cno char(1) not null,Cname char(12) not null,Cpno char(1),Ccredit tinyint(1);建立学生选课表create table SC (Sno char(9),Cno char(1),Grade tinyint(3);查看demo库中基本表的数量show tables;show tables from demo; (如果demo不是当前数据库)例3：查看基本表的结构show columns from student;show columns from course;show columns from sc;例4：修改基本表的结构向student表的最后中插入一个字段alter table student add address varchar(64);查看student表结构所发生的变化向student表的第1列前插入一个字段alter table student add IDNum char(18) not null first;查看student表结构所发生的变化向student表的sage列后插入一个字段alter table studentadd birthday date after sage;查看student表结构所发生的变化删除新增加的三个字段alter table student drop IDNum;alter table student drop address;alter table student drop birthday;查看student表结构所发生的变化将Sdept由char(2)修改为char(8)alter table studentchange Sdept Sdept char(8);查看student表结构所发生的变化将Sage由tinyint(2)修改为int(6),并不允许为空alter table studentchange Sage Sage int(6) not null;查看student表结构所发生的变化将Ssex由char(2)修改为int(1),缺省为0alter table studentchange Ssex Ssex int(1) default 0;查看student表结构所发生的变化将Sno改名为Snum，由char(9)修改为int(6),且为主键alter table studentchange Sno Snum int(6) primary key;查看student表结构所发生的变化(也可以先删除Sno，再增加Snum)例5：删除基本表删除Student表drop table student;查看demo库中基本表的数量删除Course表/SC表drop table course;drop table SC;例6：创建数据库的时候建立索引如果Student表已存在，则先删除drop tables student;create table Student (Sno char(9) not null,Sname char(8) not null,Ssex char(2) not null default 男,Sage tinyint(2),Sdept char(2),Primary key(sno),index s1(sname asc),unique s2(sage desc);查看数据表的结构show columns from Student;查看索引show index from Student;例7：先建立数据表，再通过修改数据表的属性来建立索引如果Student表已存在，则先删除drop tables student;create table Student (Sno char(9) not null,Sname char(8) not null,Ssex char(2) not null default 男,Sage tinyint(2),Sdept char(2);alter table student add primary key(sno);alter table student add index s1(sname asc);alter table student add unique s2(sage desc);查看数据表的结构show columns from Student;查看索引show index from Student;例8：先建立数据表，再直接创建索引(普通/唯一性索引)如果Student表已存在，则先删除drop tables student;primay key建主索引create table Student (Sno char(9) not null primary key,Sname char(8) not null,Ssex char(2) not null default 男,Sage tinyint(2),Sdept char(2);create index s1 on student(sname asc);create unique index s2 on student(sage desc);查看数据表的结构show columns from Student;查看索引show index from Student;例9：通过修改属性的方式删除Student表的索引alter table student drop index s1;alter table student drop index s2;alter table student drop primary key;例10：直接删除索引(普通/唯一性索引)因为前面例9已删除，要先建立create index s1 on student(sname asc);create unique index s2 on student(sage desc);删除索引drop index s1 on student;drop index s2 on student;例11：按表结构对应插入数据清除前面索引的影响，重新建立Student表drop table student;create table Student (Sno char(9) not null primary key,Sname char(8) not null,Ssex char(2) not null default 男,Sage tinyint(2),Sdept char(2);在Student中插入数据insert into student values(200215121,李勇,男,20,CS);正确查看数据select * from Student;insert into studentvalues(张三,200215999,男,20,CS);不正确，200215999当做姓名，超宽查看数据select * from Student;insert into studentvalues(张三,200215,男,20,CS);正确，张三当做学号，200215当做姓名查看数据select * from Student;insert into studentvalues(200215128,NULL,女,19,CS);错误，sname不能为空查看数据select * from Student;insert into studentvalues(200215128, 张三,女,NULL,NULL);正确查看数据select * from Student;insert into studentvalues(200215129, 李四,男);错误，给出的数据列（3）必须与数据表的列（5）相同查看数据select * from Student;例12：按字段名对应插入数据insert into student (sname,sno)values(李四,200215129);正确，带缺省值及空字段查看数据select * from Student;insert into student (ssex,sno)values(女,200215130);错误，sname非空且无缺省值查看数据select * from Student;例13：删除数据清空全部数据delete from student;查看数据select * from Student;再增加4条记录insert into studentvalues(200215121,李勇,男,20,CS);insert into studentvalues(200215122,刘晨,女,19,CS);insert into studentvalues(200215123,王敏,女,18,MA);insert into studentvalues(200215125,张立,男,19,IS);查看数据select * from Student;删除学号为200215122的记录delete from student where sno=200215122;查看数据select * from Student;删除年龄大于等于19的学生delete from student where sage=19;查看数据select * from Student;重新插入3条记录，恢复为4条记录insert into studentvalues(200215121,李勇,男,20,CS);insert into studentvalues(200215122,刘晨,女,19,CS);insert into studentvalues(200215125,张立,男,19,IS);删除性别为男的学生delete from student where ssex=男;查看数据select * from Student;重新插入2条记录，恢复为4条记录insert into studentvalues(200215121,李勇,男,20,CS);insert into studentvalues(200215125,张立,男,19,IS);删除性别为男且年龄=19的学生delete from student where ssex=男 and sage=19;查看数据select * from Student;重新插入1条记录，恢复为4条记录insert into studentvalues(200215125,张立,男,19,IS);删除性别为男或者年龄=18的学生delete from student where ssex=男 or sage=20;select * from student where ssex=男; 单字段条件比较(not与比较运算符合用 非必须)select * from student where not sage=20;select * from student where not ssex=男; 多个单字段比较进行逻辑运算(优先级NOT AND OR)select * from student where sage=19 and ssex=男;select * from student where sage=19 or ssex=男; 用betweenand表示范围(非必须)select * from student where sage between 18 and 19;select * from student where sage between 19 and 18; 结果为空，因为between有大小要求 用in来确定集合范围(非必须)select * from student where sdept in (CS, MA);例23：通过字符串匹配来选择行首先插入一条新的记录insert into student values(20021_121,李勇军,男,21,IS);查看全部数据select * from student;=与like在匹配全字符串时一样select * from student where sname=李勇;select * from student where sname like 李勇;在like中使用%与_select * from student where sname like 李勇%;select * from student where sname like 李勇_;在=中使用%与_（无效）select * from student where sname = 李勇%;select * from student where sname = 李勇_;本身有_或%时的转义符匹配select * from student where sno like 20021_121;select * from student where sno like 20021_121;例24：涉及空值时的查询先插入2条记录insert into student(sno,sname)values(2008001,张三);insert into student (sno,sname)values(2008002,李四);查看全部数据select * from student;查询空值select * from student where sage is null;select * from student where sdept is not null;空值与的区别再插入2条记录insert into student(sno,sname,sdept)values(2008003,王五,);insert into student (sno,sname,sdept)values(2008004,赵六, );两个空格查看全部数据select * from student;查询空值、和 select * from student where sdept is null;select * from student where sdept=;select * from student where sdept= ; 一个空格select * from student where sdept= ; 两个空格例25：查询结果排序首先删除废数据delete from student where sno=20021_121;delete from student where sno=2008001;delete from student where sno=2008002;delete from student where sno=2008003;delete from student where sno=2008004;查询结果排序select * from student order by sage;select * from student order by sage desc;select * from student order by sage, sdept;select * from student order by sage, sdept desc;例26：集函数的使用先插入两条记录insert into student values(200215130,张三,男,19,IS);insert into student values(200215131,李四,男,19,IS);查看全部数据select * from student; count：求指定列的行的数量select count(*) from student;select count(*) from student where ssex=男;select count(sdept) from student;select count(distinct sdept) from student;select count(sdept) from student where ssex=男;select count(distinct sdept) from student where ssex=男; sum：求指定数值列的总和select sum(sage) from student;select sum(sage) from student where ssex=男;select sum(distinct sage) from student where ssex=男; avg：求指定列的平均值select avg(sage) from student;select avg(sage) from student where ssex=男;select avg(distinct sage) from student where ssex=男; max：求指定列的最大值select max(sage) from student;select max(sage) from student where ssex=男;select max(distinct sage) from student where ssex=男; min：求指定列的最小值select min(sage) from student;select min(sage) from student where ssex=男;select min(distinct sage) from student where ssex=男;例27：用group by进行分组select * from student group by sage;select sage,count(*) from student group by sage;select sdept,count(*) from student group by sdept;select sage,count(*) from student where ssex=男 group by sage;select * from sc group by cno;select cno,count(*) from sc group by cno;select cno,avg(grade) from sc group by cno;select cno,max(grade) from sc group by cno;例28：用having子句在分组后进行筛选select cno, count(*) from sc group by cno;select cno, count(*) from sc group by cno having count(*)=2;例29：等值与非等值连接查询删废数据delete from student where sname=张三;delete from student where sname=李四;无意义的笛卡尔积select * from student,sc;select * from student,course,sc;等值连接select * from student,sc where student.sno=sc.sno;select * from student,course,sc where student.sno=sc.sno and o=o;自己组织输出表列：select sno,sname,ssex,cno,grade from student,sc where student.sno=sc.sno;错,sno无法区分属于哪个数据表select student.sno,sname,ssex,cno,grade from student,sc where student.sno=sc.sno; 正确select student.sno,sname,ssex,sdept,o,cname,grade from student,course,sc where student.sno=sc.sno and o=o;正确例30：表的自连接查询每一门课的间接先修课（先修的先修）select o,second.cpnofrom course first, course second where first.cpno=s