数据库操作(二)

最新推荐文章于 2022-06-19 14:15:26 发布

ོ執棋者

最新推荐文章于 2022-06-19 14:15:26 发布

阅读量356

点赞数 1

文章标签：数据库 mysql mysql数据库表操作

本文链接：https://blog.csdn.net/Fzicoo/article/details/100567109

版权

创建学员信息表

/*
  列名：
     stuNo   学号 非空
     stuName 姓名 非空
     stuAge  年龄 非空
     stuId   身份证号码
     stuSeat 座位号
     stuClass 班级
*/

CREATE TABLE  stuInfo(
 stuNo   CHAR(6)  NOT  NULL,   #学号，非空（必填）
 stuName  VARCHAR(20)  NOT  NULL ,  #学员姓名，非空（必填）
 stuAge  INT NOT NULL,  #年龄，非空（必填）
 stuID  VARCHAR(18),     #身份证号，18位或15位
 stuSeat INT,    #座位号
 stuClass VARCHAR(20)
);

修改表结构:

/* 
   记特殊的：
   alter table 表名 ADD 列名 类型 [约束]
   alter table 表名 MODIFY 列名 类型 [约束]
   alter table 表名 DROP 列名
*/

添加列:

/*
  语法：alter table 表名 add 列名 类型 [约束];
  语法：alter table <表名> add 列名1 类型1 约束1,add 列名2 类型2 约束2,...;
  语法：alter table <表名> add (列名1 类型1 约束1,add 列名2 类型2 约束2,...);
*/
# 修改学员信息表，添加电话和地址2个字段
ALTER TABLE stuInfo
	ADD (stuTelePhone VARCHAR(13),address VARCHAR(200));
ALTER TABLE stuInfo
	ADD stuTelePhone VARCHAR(13),
	ADD address VARCHAR(200);	
           
# 修改学员信息表，添加性别字段
ALTER TABLE stuInfo ADD sex CHAR(2) DEFAULT '男';
INSERT INTO stuInfo(stuNo,stuName,stuAge) VALUES('s001','张三',18);
INSERT INTO stuInfo(stuNo,stuName,stuAge,sex) VALUES('s002','李四',28,'女');

修改列:

/*
  语法：alter table 表名 modify 列名 类型 [约束];
  语法：alter table <表名> MODIFY 列名1 类型1 约束1,MODIFY 列名2 类型2 约束2,...; 
*/                  
# 修改学员信息表，学员姓名长度为25，添加非空约束
ALTER TABLE stuInfo MODIFY stuName VARCHAR(25) NOT NULL;
# INSERT INTO stuInfo(stuNo,stuAge) VALUES('s001',18);# 报错

# 修改学员信息表，学员年龄默认为20,姓名长度为30
ALTER TABLE stuInfo 
	MODIFY stuAge INT DEFAULT 20,
	MODIFY stuName VARCHAR(30);

删除列:

/*
  语法：alter table 表名 drop 列名;
  语法：alter table 表名 drop 列名1,drop 列名2,...;
*/
# 修改学员信息表，删除电话字段stutel_no
ALTER TABLE stuInfo DROP stuTelePhone;
# 修改学员信息表，删除地址address和座位号字段stuSeat
ALTER TABLE stuInfo DROP stuSeat, DROP address;

修改表名:

# 语法：rename table 旧表名 to 新表名;
RENAME TABLE student1 TO student;

添加约束:

/*
  语法：ALTER TABLE <表名>
             ADD CONSTRAINT 约束名 约束类型 [具体的约束说明]
*/
# 添加主键约束（stuNo作为主键）
ALTER TABLE stuInfo 
	ADD CONSTRAINT pk_stuInfo_stuNo PRIMARY KEY(stuNo);


# 为stuScore添加主键约束（stuNo作为主键）
ALTER TABLE stuScore 
	ADD CONSTRAINT pk_stuScore_stuNo PRIMARY KEY(stuNo);
# 创建成绩表
CREATE TABLE stuScore(
   stuNo CHAR(6),
   cname VARCHAR(20),
   score FLOAT
);

# 添加唯一约束（身份证号唯一，因为每人的身份证号全国唯一）
ALTER TABLE stuInfo
	ADD CONSTRAINT uq_stuInfo_stuId UNIQUE(stuID);	

# 添加外键约束（主表stuInfo和从表stuScore建立关系，关联字段为stuNo）
# FOREIGN KEY(stu)：本表的哪个字段创建外键约束
# REFERENCES stuInfo(stuNo)：哪个主表的哪个字段关联
ALTER TABLE stuScore
	ADD CONSTRAINT fk_stuScore_stuNo FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo);

删除约束:

# 语法：ALTER TABLE 表名 DROP CONSTRAINT 约束名
# alter table stuInfo drop constraint uq_stuInfo_stuId;

查询约束:

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'stuinfo';
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'stuscore';

表操作语句:

# CREATE(创建)、UPDATE(更新)、READ(读取)、DELETE(删除)
# 重点要掌握的4种SQL：INSERT/UPDATE/DELETE/SELECT(重中之重)

INSERT(插入):

# 语法：
# 一行数据：INSERT INTO <表名>[(列名1,列名2,...] VALUES(值1,值2,...);
# 多行数据：INSERT INTO <表名>[(列名1,列名2,...] VALUES(值1,值2,...)[,(值1,值2,...),...];
/*
  查询数据后插入：INSERT INTO <表名>[(列名1,列名2,...] SELECT 列名1,列名2 from <表名>
    注意：SELECT 后面的列名的个数和类型要与前面的列名的个数与类型要对应，否则报错
*/  
# 第一种：
# 插入的列顺序、个数和类型要与建表的顺序、个数、类型要对应。
INSERT INTO stuInfo VALUES('s003','jack',20,'111','大一','男');
# 第二种：
INSERT INTO stuInfo(stuNo,stuName) VALUES('s004','tom');
# 第三种：多条数据插入
INSERT INTO stuInfo(stuNo,stuName)
	VALUES('s005','tony')
	     ,('s006','mary')
	     ,('s007','jerry')
	     ,('s008','yy');
# 第四种：多条数据插入：主键自增
CREATE TABLE test1(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20));
# 主键自增长，所以插入数据时可以不设置	 
INSERT INTO test1(NAME) VALUES('tony'),('mary'); 
# 根据查询的结果集插入表中
INSERT INTO test1(NAME) SELECT NAME FROM test1;

UPDATE(更新):

# 语法：
# 更新数据：UPDATE <表名> SET 列名1=值1[,列名2=值2,...] [WHERE 条件表达式];
# UPDATE单列
# 将学号为s001的学生的年级改为大二
UPDATE stuInfo SET stuClass='大二';# 修改所有数据的年级为大二
UPDATE stuInfo SET stuClass='大二' WHERE stuNo='s001';
# UPDATE多列
UPDATE stuInfo SET stuClass='大三',stuId='222' WHERE stuNo='s001';

# DELETE(删除)
# 语法：
# 删除数据：DELETE FROM <表名> [WHERE 条件表达式];
# 删除学号为s008的学生记录
DELETE FROM stuInfo WHERE stuNo='s008';
# 删除姓名为tony或年龄为21岁的学生
DELETE FROM stuInfo WHERE stuName='tony' OR stuAge=21;
# 删除性别为男且年龄为20岁的学生
DELETE FROM stuInfo WHERE sex='男' AND stuAge=20;
# 删除所有的数据
DELETE FROM stuInfo;

SELECT(查询):

/*
   SELECT *|列名1 [AS] 别名1,列名2 [AS] 别名2,... 
   FROM <表名1|视图名1> [AS] 别名1(表),<表名2|视图名2> [AS] 别名2(表),...
   [WHERE 条件表达式]
   [ORDER BY 列名1 [asc/desc],列名2 [asc/desc],...]
   [GROUP BY 列名1,列名2,...]
   [HAVING 条件表达式]
   描述：
     1) SELECT *|列名1 [as] 别名1,列名2 [as] 别名2,...  ：表达要查询的数据
             *：表示所有列的数据
             列名1,列名2,...：查询你设置的列的数据
             AS：设置别名，可写可不写
     2) FROM <表名1|视图名1> [AS] 别名1(表),<表名2|视图名2> [AS] 别名2(表),...：
        查询的表名或视图名  
     3) [WHERE 条件表达式]：对查询的数据的过滤
     4) [ORDER BY 列名1 [asc/desc],列名2 [asc/desc],...]：对数据的排序操作
            asc：表示升序，默认值
            desc：表示降序，如果要表示它就必须写
     5) [GROUP BY 列名1,列名2,...]：分组 
     6) [HAVING 条件表达式]：依赖于GROUP BY 存在，对分组后的结果的条件过滤     	
*/

# 查询所有学生信息
SELECT * FROM stuInfo;
# 查询学号为s001的学生信息
SELECT * FROM stuInfo WHERE stuNo='s001'; 
# 查询所有学生的学号和姓名列
SELECT stuNo,stuName FROM stuInfo;
# 如果列名输出时要用别名
# 将stuNo显示为"学号"、stuName显示为"姓 名"
# AS 设置别名
SELECT stuNo AS 学号,stuName 姓名 FROM stuInfo;
#SELECT stuNo AS 学号,stuName 姓 名 FROM stuInfo;#这一种会报错，因为姓名中间有空格，语法错误
SELECT stuNo AS 学号,stuName "姓 名" FROM stuInfo;

# 查询去重后的学生性别
# DISTINCT：去除重复的数据
SELECT sex FROM stuInfo;#8条
SELECT DISTINCT sex FROM stuInfo;#2条

# 查询性别为男或女的学生信息（几种）
SELECT * FROM stuInfo WHERE sex='男' OR sex='女';
# IN：属于  
#    sex只要符合括号内的其中的任意一个值就满足条件
# NOT IN：不属于
SELECT * FROM stuInfo WHERE sex IN ('男','女');
SELECT * FROM stuInfo WHERE sex NOT IN ('男','女');

# 按年龄降序，学号升序
SELECT * FROM stuInfo ORDER BY stuAge DESC,stuNo ASC;
SELECT * FROM stuInfo ORDER BY stuAge DESC,stuNo;

# 将学号和姓名合并后显示
# select stuNo||stuName from stuInfo;

# 查询年龄在20岁一下学生的姓名
SELECT stuName FROM stuInfo WHERE stuAge<20;

# 查询年龄在20岁-30岁之间学生的姓名
SELECT stuName FROM stuInfo WHERE stuAge>=20 AND stuAge<=30;
# between L and H
# L：范围的最小值
# H：范围的最大值
SELECT stuName FROM stuInfo WHERE stuAge BETWEEN 20 AND 30;
# 查询姓张的学生信息
# like：模糊查询
# 有2种通配符：%和?
# %：表示0到多个字符
# ?：表示0到1个字符

SELECT * FROM stuInfo WHERE stuName LIKE 't%';

# 查询姓名中包含"o"的学生信息
SELECT * FROM stuInfo WHERE stuName LIKE '%o%';

# limit 关键字 做分页
# limit n 取n条数据 1 ~ n
# limit n[,m] n是从第几条记录开始取（注意：行号从0开始），m表示取几条记录
SELECT * FROM stuInfo LIMIT 5,10;    #返回第6-15行数据 
SELECT * FROM stuInfo LIMIT 5;      #返回前5行 
SELECT * FROM stuInfo LIMIT 0,5;    #返回前5行
# 查询学生年龄最大的3名学生信息

# 统计学生表中的总人数
# 求某门课程的总分、最高分、最低分、平均分
# 统计每个学生的平均成绩

# 子查询
# 查询与张三在同一个班级的学生
         
# 查询学生成绩大于80分的学生姓名
              
# 左外连接：左表有的数据全都显示，右表如果数据不存在则显示 null
# 查询学生表的学号、姓名、成绩

# 右外连接：右表有的数据全都显示，左表如果数据不存在则显示 null  
# 查询学生表的学号、姓名、成绩

ོ執棋者

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库操作(二)

创建学员信息表/* 列名： stuNo 学号非空 stuName 姓名非空 stuAge 年龄非空 stuId 身份证号码 stuSeat 座位号 stuClass 班级*/CREATE TABLE stuInfo( stuNo CHAR(6) NOT NULL, #学号，非空（必填） stu...
复制链接

扫一扫