SQL基础-操纵表及插入、查询-白红宇

SQL基础-操纵表及插入、查询

阅读量：5220 次

发布时间：2019-06-14

本文共 5926 字，大约阅读时间需要 19 分钟。

一、操纵表

1、表的关键信息

2、更新表名

更新表名：使用RENAME TABLE关键字。语法如下：    RENAME TABLE 旧表名 TO 新表名;比如，生产环境投产前备份teacher表，使用如下语句：    RENAME TABLE teacher TO teacher_bak;

3、更新字段名

更新字段名：使用ALTER TABLE关键字。语法如下：    ALTER TABLE 表名 RENAME COLUMN 旧字段名 TO 新字段名;比如，修改teacher_name的字段名为teacher_nm，使用如下语句：    ALTER TABLE teacher RENAME COLUMN teacher_name TO teacher_nm;

4、更新字段类型

更新字段类型：使用ALTER TABLE关键字。语法如下：    ALTER TABLE 表名 ALTER COLUMN 字段名 新字段类型;比如，修改teacher_name的类型为VARCHAR(100)，使用如下语句：    ALTER TABLE teacher ALTER COLUMN teacher_name varchar(100);在更改类型的时候，更改前的类型和更改后的类型一定要能显示转换，否则会造成数据丢失。

5、更新字段NULL值

更新字段类型时，可同时设置字段NULL值、默认值，也是使用ALTER TABLE关键字,语法是一样的。比如，修改teacher_name的类型为VARCHAR(100) NULL，使用如下语句：    ALTER TABLE teacher ALTER COLUMN teacher_name varchar(100) null DEFAULT ‘’;

6、更新字段默认值

更新字段类型时，也可同时设置字段默认值，语法是一样的。比如，修改teacher_name的类型为VARCHAR(100) ，默认值为’’，使用如下语句：    ALTER TABLE teacher ALTER COLUMN teacher_name varchar(100) default ‘’;更新字段默认值：也可使用ALTER TABLE关键字。语法如下：    ALTER TABLE 表名 ALTER COLUMN 字段名 SET DEFAULT 默认值;比如，修改teacher_name的默认值为’未知’，使用如下语句：    ALTER TABLE teacher ALTER COLUMN teacher_name set default ‘未知’;删除字段默认值：使用ALTER TABLE关键字。语法如下：    ALTER TABLE 表名 ALTER COLUMN 字段名 DROP DEFAULT;比如，删除teacher_name的默认值，使用如下语句：    ALTER TABLE teacher ALTER COLUMN teacher_name drop default;

7、更新主键字段

设置主键：如果表已存在，但没有定义主键字段，可使用ALTER TABLE关键字设置主键。语法如下：    ALTER TABLE 表名 ADD PRIMARY KEY （主键字段列表）;比如，将teacher表的teacher_name和gender两个字段设置为主键，使用如下语句：    ALTER TABLE teacher ADD PRIMARY KEY （teacher_name,gender）;删除主键字段：如果表中已定义主键字段，可使用ALTER TABLE关键字删除主键。语法如下：    ALTER TABLE 表名 DROP PRIMARY KEY;比如，将teacher表的主键删除，使用如下语句：    ALTER TABLE teacher DROP PRIMARY KEY;更新主键字段：如果表中已定义主键字段，但想修改主键字段，比如说假如现在teache表的主键字段是teacher_name和gender，现在想修改主键字段为teacher_id，也是可以的。

8、新增、删除字段

新增字段：使用ALTER TABLE关键字。语法如下：    ALTER TABLE 表名 ADD COLUMN 新字段名 字段类型及其他属性;比如，在teache表中新增年龄字段age，使用如下语句：    ALTER TABLE teacher ADD COLUMN age int not null default 0;删除字段：使用ALTER TABLE关键字。语法如下：    ALTER TABLE 表名 DROP COLUMN 字段名;比如，在teache表中删除年龄字段age，使用如下语句：    ALTER TABLE teacher DROP COLUMN age;

9、删除表

删除表：使用DROP TABLE关键字。语法如下：    DROP TABLE 表名;比如，删除teacher表，使用如下语句：    DROP TABLE teacher;

二、插入数据

1、建表

CREATE TABLE teacher (    teacher_id VARCHAR(50) NOT NULL,    teacher_name VARCHAR(100) NOT NULL DEFAULT '未知',    gender VARCHAR(10),    PRIMARY KEY ( teacher_id));

2、插入整行字段

插入整行字段：关键字INSERT INTOINSERT INTO 表名 VALUES (?,?,?,?);假如需要向teacher表中插入两条数据：    #值的顺序与表中的字段顺序必须一致！不太安全；    INSERT INTO teacher VALUES ('T0001','高齐妍','男');    INSERT INTO teacher VALUES ('T0002','李红','女');    #安全    INSERT INTO teacher(teacher_id,teacher_name,gender) VALUES ('T0001','高齐妍','男');

3、插入部分字段

INSERT INTO teacher(teacher_id,teacher_name,gender) VALUES ('T0003', '李一萱',NULL);#这种要求性别字段可为NULLINSERT INTO teacher(teacher_id,teacher_name) VALUES ('T0003', '李一萱');#这要求teacher_name字段设置了默认值INSERT INTO teacher(teacher_id) VALUES ('T0004');

4、一次插入多行

INSERT INTO teacher VALUES ('T0001','高齐妍','男'),('T0002','李红','女');注意： 建议一条SQL语句插入的条数在1000条以下； 还受限于数据库支持的单条SQL语句的长度；

5、借助其他表数据插入

INSERT INTO teacher SELECT * FROM teacher;克隆表：SELECT * INTO teacher_bak FROM teacher;CREATE TABLE teacher_bak AS SELECT * FROM teacher;

三、查询

1、建表

##建表CREATE TABLE `student` (  `student_id` varchar(50) NOT NULL COMMENT '学生编号',  `student_name` varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',  `gender` varchar(10) NOT NULL DEFAULT '' COMMENT '性别',  `birth_day` date NOT NULL COMMENT '生日',  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',  `class_id` varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',  `score` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',  `teacher_id` varchar(20) DEFAULT NULL COMMENT '老师编号') ENGINE=MyISAM DEFAULT CHARSET=gbk COMMENT='学生';##插入部分数据INSERT INTO `student` VALUES ('S20180001','方东美','女','2006-02-04',12,'G0101',80.65,'T0003'),('S20180002','方香','女','2008-09-28',10,'G0101',75.48,NULL),('S20180003','高紫菡','男','2006-07-22',12,'G0101',74.46,'T0003'),('S20180004','胡未迟','男','2007-07-25',11,'G0101',51.27,'T0003'),('S20180005','李咏颐','男','2007-03-16',11,'G0101',88.84,'T0003'),('S20180006','吴灏潇','男','2008-04-19',10,'G0101',69.93,NULL),('S20180007','吴明鸿','男','2007-11-18',11,'G0101',63.65,'T0003'),('S20180008','吴鹏宇','男','2007-08-24',11,'G0101',84.69,'T0003'),('S20180009','吴少雄','男','2007-08-04',11,'G0101',76.36,'T0003'),('S20180010','习芬飘','女','2005-01-27',13,'G0101',83.42,'T0003'),('S20180011','俞倚琳','女','2007-07-07',11,'G0101',97.38,'T0003'),('S20180012','张琼雪','女','2006-06-12',12,'G0101',81.01,'T0003');

2、查询部分列

SELECT 字段列表 FROM 表名;SELECT student_id,student_name,gender FROM student;

3、查询所有列

第一种写法(推荐)，字段顺序可以变换：    SELECT teacher_id,teacher_name,gender,age,class_id FROM student;第二种写法：    SELECT * FROM student;

4、排序返回结果

将查询结果排序后展示：关键字ORDER BY … [ASC/DESC]SELECT 字段列表 FROM 表名ORDER BY 字段1 [ASC/DESC],字段2 [ASC/DESC]…;SELECT student_id,student_name,age FROM student ORDER BY age ASC;            | | (等价)SELECT student_id,student_name,age FROM student ORDER BY age;##可以指定多个字段排序SELECT student_id,student_name,age FROM student ORDER BY age ASC,student_name ASC;##按列位置排序SELECT student_id,student_name,age FROM student ORDER BY age ASC,student_name ASC;            | | (等价)SELECT student_id,student_name,age FROM student ORDER BY 3 ASC,2 ASC;

5、限制返回结果

###返回前几行：    关键字TOP、LIMITmysql没有TOP两种写法： SELECT TOP 10 字段列表 FROM 表名; SELECT 字段列表 FROM 表名 LIMIT 10;一般在两种情况下会使用： 表数据太多，想随便查看一下表中的数据； 一般与ORDER BY关键字配合使用，返回按某些字段排序后的前几行；###返回中间几行：    关键字LIMIT M OFFSET N（从第N行开始，返回M行记录）两种写法： SELECT 字段列表 FROM 表名 LIMIT M OFFSET N; SELECT 字段列表 FROM 表名 LIMIT N,M;这里的第N行，是从0开始算起的；一般与ORDER BY关键字配合使用，按M行为一页，返回某一个分页的记录；###返回中间几行：    关键字LIMIT M OFFSET N（从第N行开始，返回M行记录）SELECT 字段列表 FROM 表名 LIMIT 0,10;          | | (等价)SELECT 字段列表 FROM 表名 LIMIT 10;###返回后几行：     关键字TOP、LIMIT、ORDER BYSQL语句中，没有返回后几行的专用写法，一般转换为按返回前几行的相反方式排序后，再返回前几行。使用这种方式变相的返回后几行。第1步：先按学生编号倒序排序；第2步：返回排序后的前5行；第3步：将前5行数据升序排序；SELECT * FROM(SELECT TOP 5 *FROM studentORDER BY student_id DESC)aORDER BY student_id ASC

转载于:https://www.cnblogs.com/weiyiming007/p/11425148.html

你可能感兴趣的文章

2017西安区域赛A / UVALive - 8512 线段树维护线性基合并