MySQL操作数据表

本文主要是介绍MySQL操作数据表，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

操作数据表

1. 修改表名：

   ALTER TABLE 旧表名 RENAME AS 新表名

   ALTER TABLE teacher RENAME AS teacher1
   

   ​

2. 向表中添加字段：

   ALTER TABLE 表名 ADD 字段名 列属性

   ALTER TABLE teacher1 ADD age INT(3)
   

   ​

3. 修改表的字段（修改约束）：

   ALTER TABLE 表名 MODIFY 字段名 新列属性

   ALTER TABLE teacher1 MODIFY age VARCHAR(3)
   

​ 修改表的字段（重命名）：

​ ALTER TABLE 表名 CHANGE 旧字段名 新字段名[新字段属性]

ALTER TABLE teacher1 CHANGE age sex VARCHAR(1)

4. 删除表的字段：

   ALTER TABLE 表名 DROP 字段名

   ALTER TABLE teacher1 DROP sex
   

5. 删除数据表：

   DROP TABLE 表名

   DROP TABLE IF EXISTS teacher1
   

   ​

注意：

• 所有的创建和删除尽量加上判断，以免报错
• 创建表时，表名和字段名尽量使用反引号括起来
• SQL关键字不敏感，建议小写

MySQL数据管理

1、外键

1.1在创建表时添加外键约束：

先创建一张grade数据表：

CREATE TABLE  IF  NOT EXISTS`grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(10) NOT NULL COMMENT '年级名称',
	PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的gradeid字段 要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） reference 引用
CREATE TABLE IF NOT EXISTS `student` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '12345' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `gradeid` INT(10) NOT NULL  COMMENT '学生年级',
  `address` VARCHAR(190) DEFAULT NULL COMMENT '家庭住址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (gradeid),
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表时，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

1.2在创建表时无外键关系

ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的字段) REFERENCE 外键引用表名（表内的哪个字段）

ALTER TABLE `student` ADD
CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`);

以上创建的外键都是物理外键，数据库级别的外键，不建议使用，避免数据库过多造成困扰

建议：

• 数据库就是单纯的表，只用来存数据，只有行（记录）和列（字段）
• 想使用多张表的数据时用程序去实现外键

2、DML语言

数据库操作语言

• insert
• updata
• delete

2.1、添加

INSERT INTO 表名 ([字段名1],[字段名2],[字段名3]) VALUES ([值1],[值2],[值3],...)

INSERT INTO `grade` (`gradename`) VALUES ('大一') 
--由于主键自增可以省略主键，但是不写表的字段就会一一匹配出错

INSERT INTO `grade` (`gradename`) VALUES ('大二'),('大三'),('大四')

一般写插入语句要保证一定要数据和字段一一对应

INSERT	INTO `student` (`name`,`pwd`,`sex`) VALUES ('原语','abcdef','男')

INSERT	INTO `student` (`name`,`pwd`,`sex`) VALUES ('原语2','abcdef','男'),('原语3','abcdef','男'),('原语4','abcdef','男')

双击左侧列表数据表栏位中的字段名可以直接填入括号

2.2、修改

UPDATE `表名` SET column = value [,column = value,...] WHERE 条件

UPDATE `student` SET `name`='上弦'  WHERE `id`=1 

UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `id`=1

where必须在set后面否则报错

如果没加where条件则该列字段对应的所有值会全部更改 ：

UPDATE `student` SET `name`='小明'

条件：

操作符会返回布尔值

2.3、删除

DELETE FROM `表名` WHERE 条件

DELETE FROM `student` WHERE `id`=1

若不加where条件则表示删除整张表（不建议使用）

TRUNCATE 清空数据库表（表的结构和索引约束都不会变）：

TRUNCATE `student`

detete 与 truncate 区别：

• 相同：都能删除数据，都不会删除表结构
• 不同：
  • truncate会重新设置自增列 计数器会归零
  • truncate不会影响事务

delete的删除问题：（当重启数据库时）

• Innodb 自增列会重新从1开始，因为存在内存中，断电即失
• MyISAM 继续从上一个自增量开始，因为存在于文件中，不会丢失

3、DQL语言

数据库查询语言：

• 用于查询操作
• 数据库最核心的语言，最重的语句
• 是使用频率最高的语句

数据库中的表达式：

• 文本值
• 列
• Null
• 函数
• 计算表达式
• 系统变量
• ...

SELECT 表达式 FROM 表

SELECT * FROM `student`

SELECT `StudentNo`,`LoginPwd` FROM `student`

查询结果并别名：

SELECT 字段 AS 别名 FROM `表名` AS 别名

SELECT `StudentNo` AS 学号,`LoginPwd` AS 密码 FROM `student`

查询结果并用函数

CONCAT(A,B)连接函数

SELECT CONCAT('姓名:',`StudentName`) AS 新名字 FROM `student`

去重（distinct）

去除select语句中查询出来的重复数据，重复的数据只显示一条

SELECT DISTINCT `StudentNo` FROM `result`

select其他作用：

select用于查询版本号：(函数)

SELECT VERSION()

select用于计算： （表达式）

SELECT 3*100-1 AS 计算结果

select用于查询自增步长： （变量）

SELECT @@auto_increment_increment

select可以让查询结果值改变（对表中原数据不造成影响）：

SELECT `StudentNo`,`StudentResult`+1 AS 新成绩 FROM`result`

4、where条件字句

作用：检索中数据中符合条件的值

搜索条件由一个或者多个表达式组成，结果返回布尔值

尽量使用英文字母表示

SELECT `StudentNo`,`StudentResult` FROM`result` WHERE `StudentResult`>=95 AND `StudentResult`<=100
--  即
SELECT `StudentNo`,`StudentResult` FROM `result` WHERE `StudentResult` BETWEEN 95 AND 100

模糊查询

like与%（其后有0至任意字符）_(其后只有一个字符)

SELECT `StudentNo`,`StudentName` FROM `student`WHERE `StudentName` LIKE 'M%'

SELECT `StudentNo`,`StudentName` FROM `student`WHERE `StudentName` LIKE '%T%'

in其后是具体的一个或多个值 ，不能用% _

SELECT `StudentNo`,`StudentName` FROM `student` WHERE `StudentNo` IN (1,2,3)

5、联表查询(join)

三种查询（本质）：

七种连接查询：

SELECT student.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult` FROM `student` INNER JOIN`result` WHERE student.`StudentNo`=result.`StudentNo`

SELECT student.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult` FROM `student` LEFT JOIN`result` WHERE student.`StudentNo`=result.`StudentNo`

SELECT student.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult` FROM `student` RIGHT JOIN`result` WHERE student.`StudentNo`=result.`StudentNo`

开始select的StudentNo没有添加表名，造成字段模糊错误

join（连接的表） on（判断的条件） 连接查询

where 等值查询

-- 查询参加了考试的同学的学号、姓名、科目名、分数
SELECT student.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult` FROM `student` RIGHT JOIN`result` ON student.`StudentNo`=result.`StudentNo` INNER JOIN `subject` ON result.`SubjectNo`=subject.`SubjectNo`

思路：

1. 要查询哪些数据 select
2. 要从哪几张表中查询 from 表 join 连接的表 on 交叉条件
3. 假设存在一种多表查询，先查询两张表然后再慢慢增加

6、自连接及联表查询

自连接：自己的表和自己的表连接，即：一张表拆成两张一样的表即可

CREATE TABLE `category`(
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT'主题id',
`pid` INT(10) NOT NULL COMMENT'父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT'主题名字',
PRIMARY KEY(`categoryid`)
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8


INSERT INTO `category` VALUES (2,1,'信息技术'),(3,1,'软件开发'),(4,3,'数据库'),(5,1,'美术设计'),(6,3,'web开发'),(7,5,'PS技术'),(8,2,'办公信息')

父类：

子类：

操作：查询父类对应的子类关系

SELECT a.`categoryName` AS 父栏目 , b.`categoryName` AS 子栏目 FROM `category` AS a,`category` AS b WHERE a.`categoryid`=b.`pid`

这篇关于MySQL操作数据表的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！