基本语句

表的创建与删除

  1. -- 创建表,并且添加主键约束,和外键约束
  2. create table 表名(
  3.     `字段名` 类型名(大小) 约束,
  4.     primary key(`字段名`),
  5.     foreign key(`字段名`) references 表名(`字段名`)
  6. )engine = innodb default charset = utf8
  7. -- 删除表
  8. drop table 表名;
  9. -- 展示表的结构
  10. desc(describe) 表名;

表结构的修改

-- 重命名表(数据库不重命名)
alter table `旧名字` rename `新名字`;
-- 字段类型的修改
alter table `表名` modify `字段名` 字段类型(int(10));
-- 字段名的修改(change) 
alter table  `表名` change  `字段名` `新名字` 字段类型(int(10));

-- 添加字段
alter table `表名` add `字段名` `字段类型` [first/last/after `字段名`];
-- 添加主键约束
alter table `表名` add primary key(字段);

rename: 可以用来修改表的名称
**change**:关键字可以修改表的字段的名称,不能修改表的类型
**modify**:关键字可以修改表的字段类型,但不能修改字段的名称

表数据的增删查改

-- 插入语句
insert into `表名` values ("","",0),("","",0);
insert into `表名`(字段,字段,字段) values ("","",0),("","",0);

-- 查询语句
select * from `表名` where 条件表达式 group by 字段 having 条件表达式 order by 字段 [asc/desc];
-- 查询全部信息并去重
select distinct * from `表名`;

-- 更新语句
update `表名` set 字段=数值 where 条件表达式;

-- 删除语句
delete from `表名` where 条件表达式; -- delete 使用后自增索引可能会出问题
truncate table `表名`;  -- 删除表重新创建,清空全部数据

基本语句的实战

表的创建

1.创建学生表名为student，包含5个属性：

CREATE TABLE student(
sno char(5),
sname char(8),
sdept char(2) not null,
sclass char(2) not NULL,
sage numeric(2),
PRIMARY KEY(sno)
)engine=INNODB DEFAULT CHARSET=utf8

2.创建课程表：

create table course(
 cno char(3),
 cname char(16) UNIQUE,
 ccredit numeric(2),
 PRIMARY KEY(cno)
)engine = innodb default charset = utf8

3.创建分数表：

CREATE table score (
sno char(5),
cno char(3),
score NUMERIC(5,2),
PRIMARY KEY(sno,cno),
FOREIGN KEY(sno) REFERENCES student(sno),
FOREIGN KEY(cno) REFERENCES course(cno)
)engine = innodb default charset = utf8

4.给学生表的sdept添加索引：

alter table student add index(sdept);

5.给表student添加字段ssex性别字段并且默认为男：

alter table student add ssex char(1) default '男';

插入数据

1.给表student插入数据：

insert into student VALUES("96001","马小燕","CS","01",21,"女"),
("96002","黎明","CS","01",18,"男"),
("96003","刘东明","MA","01",18,"男"),
("97001","马蓉","MA","02",19,"女"),
("97002","李成功","CS","01",20,"男"),
("97003","黎明","IS","03",19,"女"),
("97004","李丽","CS","02",19,"女"),
("96005","司马志明","CS","02",18,"男");

2.给表course插入数据：

INSERT INTO `course` VALUES ('001', '数学分析', 8),
 ('002', '普通物理', 8),
 ('003', '微机原理', 4),
 ('004', '数据结构', 4),
 ('005', '操作系统', 4),
 ('006', '数据库原理', 4),
 ('007', '编译原理', 3),
 ('008', '程序设计', 2);

3.给表score插入数据：

INSERT INTO `score` VALUES ('96001', '001', 77.00);
('96001', '003', 89.00),
('96001', '004', 86.00),
('96001', '005', 82.00),
('96002', '001', 88.00),
('96002', '003', 92.00),
('96002', '006', 90.00),
('96005', '004', 92.00),
('96005', '005', 90.00),
('96005', '006', 89.00);

数据的查询

-- 求全体学生的学号、姓名、性别和年龄。
select sno,sname,ssex,sage from student;
-- 求选修了课程的学生学号。(应该是有分数的人才是有选修课的人)
select distinct sno from score;
-- 求年龄在19岁与22岁(含20岁和22岁)之间的学生的学号和年龄。
select sno,sage from student where sage >=19 and sage<=22;
select sno,sage from student where sage between 19 and 22;
-- 求各门课程的平均成绩与总成绩。(应该是有人选没人选都求出分数来)
select course.cname,ifnull(avg(score),0),ifnull(sum(score),0) sum from course left join score on course.cno=score.cno group by course.cno;
-- 求姓名是以“李”打头的学生。(模糊查询/正则表达式)
select sname from student where sname like '李%';
select sname from student where sname rlike '^李';
-- 求选修了课程名为 ’数据结构’ 的学生的学号和姓名。（分别用连接和子查询做）
select sno,student from student where sno in (select sno from score where score.con in (select cno from course where course.cname='数据结构'));
select student.sno,student.sname from student,score,course where student.sno=score.sno and score.cno=course.cno and course.cname='数据结构';
-- 求学生人数不足3人的系及其相应的学生数。
select sdept,count(*) as people from  student group by sdept having people<3;

数据的更新

update 表名 set where 条件表达式;

数据的删除

drop table 表名;

创建视图并操作

创建视图create view 视图名称 as (select * from 表名);

--  (建立视图)  建立计算机系的学生的视图STUDENT_CS。
create view STUDENT_CS as select * from student;
-- (建立视图)  建立由学号和平均成绩两个字段的视图STUDENT_GR。
select student.sno,ifnull(avg(score),0) from student left join score on student.sno=score.sno group by student.sno;
--(视图查询)利用视图STUDENT_GR求平均成绩为88分以上的学生学号和平均成绩。
select * from STUDENT_GR where `avg(s.score)`>88;
--(视图更新)利视图STUDENT_CS，增加学生( ‘96006’, ‘张然’, ‘CS’, ‘02’, ‘男’, 19 )。
insert into STUDENT_GB(sno, sname, sdept, sclass, sage, ssex) values ("96006","张然", "CS", "02", 19, "男");
--（视图更新)利用视图STUDENT_CS，将学生年龄增加1岁。观察其结果。
update student_gb set sage=sage+1;