MySQL下的sql示例.docx

1、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 def

2、ault 男,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),

3、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;desc student;show columns from course;desc course;show columns from sc;desc sc;例4：修改基本表的结构向student表的最后中插入一个字段alter

4、table student add address varchar(64);查看student表结构所发生的变化desc student;向student表的第1列前插入一个字段alter table student add IDNum char(18) not null first;查看student表结构所发生的变化desc student;向student表的sage列后插入一个字段alter table studentadd birthday date after sage;查看student表结构所发生的变化desc student;删除新增加的三个字段alter table stu

5、dent drop IDNum;alter table student drop address;alter table student drop birthday;查看student表结构所发生的变化desc student;将Sdept由char(2)修改为char(8)alter table student change Sdept Sdept char(8);查看student表结构所发生的变化desc student;将Sage由tinyint(2)修改为int(6),并不允许为空alter table student change Sage Sage int(6) not null

6、;查看student表结构所发生的变化desc student;将Ssex由char(2)修改为int(1),缺省为0alter table student change Ssex Ssex int(1) default 0;查看student表结构所发生的变化desc student;将Sno改名为Snum，由char(9)修改为int(6),且为主键alter table student change Sno Snum int(6) primary key;查看student表结构所发生的变化desc student;(也可以先删除Sno，再增加Snum)例5：删除基本表删除Student

7、表drop table student;查看demo库中基本表的数量show tables;删除Course表/SC表drop table course;drop table SC;查看demo库中基本表的数量show tables;例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),Pr

8、imary key(sno),index s1(sname asc),unique s2(sage desc);查看数据表的结构desc 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);alt

9、er table student add primary key(sno);alter table student add index s1(sname asc);alter table student add unique s2(sage desc);查看数据表的结构desc Student;查看索引show index from Student;例8：先建立数据表，再直接创建索引(普通/唯一性索引)如果Student表已存在，则先删除drop tables student;primay key建主索引create table Student (Sno char(9) not null pr

10、imary 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);查看数据表的结构desc Student;查看索引show index from Student;例9：通过修改属性的方式删除Student表的索引alter table student drop index s1;alter table st

11、udent drop index s2;alter table student drop primary key;查看索引show index from Student;例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;查看索引show index from Student;例11：按表结构对应插入数据清除

12、前面索引的影响，重新建立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(张三,20

13、0215999,男,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);正确查看数

14、据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 * fr

15、om 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;删除学号

16、为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,

17、张立,男,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

18、 into studentvalues(200215125,张立,男,19,IS);删除性别为男或者年龄=18的学生delete from student where ssex=男 or sage=18;查看数据select * from Student;例14：修改记录 先将数据恢复为4条记录delete from student;insert into studentvalues(200215121,李勇,男,20,CS);insert into studentvalues(200215122,刘晨,女,19,CS);insert into studentvalues(200215123,

19、王敏,女,18,MA);insert into studentvalues(200215125,张立,男,19,IS);查看数据select * from Student;将李勇的年龄修改为21岁update student set sage=21 where sname=李勇;查看数据select * from Student;将所有男生的年龄加倍update student set sage=sage*2 where ssex=男;查看数据select * from Student;将所有男生的年龄增加15% update student set sage=sage*1.15 where

20、ssex=男;查看数据(注意，结果仍为整数)select * from Student;将学号为200215121的学生的年龄修改为21，系部为ISupdate studentset sage=21,sdept=IS where sno=200215121;查看数据select * from Student;将学号为200215121的学生的学号修改为200215122update studentset sno=200215122 where sno=200215121;错误，主键重复查看数据select * from Student;将学号为200215121的学生的学号修改为2002151

21、24update studentset sno=200215124 where sno=200215121;正确查看数据select * from Student;将学号为200215122的学生的姓名清空update student set sname=NULL where sno=200215122;错误，sname不允许为空查看数据select * from Student;先修改属性，允许为空alter table student change sname sname char(8);查看结构desc student;再将学号为200215122的学生的姓名清空update stude

22、nt set sname=NULL where sno=200215122;正确查看数据select * from Student;将学号为200215123的学生的年龄改为zz update student set sage=zz where sno=200215123;错误，sage为整型查看数据select * from Student;例15：带外键的数据表的创建建立三张无外键关系的表，顺序任意如果Student、course、SC表已存在，则先删除drop tables student;drop tables course;drop tables sc;建立学生选课表create t

23、able SC (Sno char(9),Cno char(1),Grade tinyint(3),primary key(Sno,Cno);建立学生表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 table Course (Cno char(1) not null primary key,Cname char(12) not null,C

24、pno char(1),Ccredit tinyint(1);建立Student与SC存在外键关系的表，则必须先student,后sc表如果Student、course、SC表已存在，则先删除drop tables student;drop tables course;drop tables sc;建立学生选课表create table SC (Sno char(9),创建不成功Cno char(1),Grade tinyint(3),primary key(Sno, Cno),foreign key(Sno) references Student(sno);先建立学生表（不带主键）creat

25、e 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 SC (Sno char(9),Cno char(1),Grade tinyint(3),primary key(Sno, Cno),foreign key(Sno) references Student(sno);创建不成功，因为Studnet(sno)不是主键删除学生表drop table student;

26、建立学生表（带主键）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 table SC (Sno char(9),Cno char(1),Grade tinyint(3),primary key(Sno, Cno),foreign key(Sno) references Student(sno);创建成功创建有外键关系的三个表，则sc必须

27、最后建立如果Student、course、SC表已存在，则先删除drop tables sc;drop tables course;drop tables 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);建立课程表create table Course (Cno char(1) not null primary key,Cname char

28、(12) not null,Cpno char(1),Ccredit tinyint(1),foreign key(Cpno) references Course(Cno);建立学生选课表create table SC (Sno char(9),Cno char(1),Grade tinyint(3),primary key(Sno, Cno),foreign key(Sno) references Student(sno),foreign key(Cno) references Course(Cno);查看表结构show tables;desc Student;desc Course;des

29、c SC;例16：带外键数据表的删除drop table Course;删除不成功，要删除course，必须先删除scdrop table SC;drop table course;drop table student;例17：带外键数据表的记录的插入先把三张表补齐建立课程表create table Course (Cno char(1) not null primary key,Cname char(12) not null,Cpno char(1),Ccredit tinyint(1),foreign key(Cpno) references Course(Cno);建立学生表create table Student (Sno char(9) not null primary key,Sname char(8) not null,Ssex char(2) not null def