柳意梧情

心在哪里收获就在哪里
加载
首页 » MySQL笔记 » 数据库语句整理

数据库语句整理

#创建数据库study
CREATE DATABASE IF NOT EXISTS study;

#使用数据库study
USE study;

#删除数据库(study)
DROP DATABASE IF EXISTS study;

#查看数据库(study)的定义
SHOW CREATE DATABASE study;
#查看数据表(student)的定义
SHOW CREATE TABLE student;
#显示表(student)结构
DESC student;

#MySQL的数据表的类型:MyISAM InnoDB HEAP BOB CSV等
#查看MySQL所支持的引擎类型(表类型)
SHOW ENGINES;

#设置严格检查模式
SET sql_mode='strict_trans_tables';

#创建表stu(字段,列名)
#学号(id) int型、姓名(name) varchar型、密码(pwd) varchar型、出生日期(birthday) datetime型、性别(sex) varchar型、地址(address) varchar型、邮箱(email) varchar型
CREATE TABLE IF NOT EXISTS stu(
	`id` INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT '学号,主键,自增',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名,不能为空,默认匿名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码,不能为空,默认123456',
	`birthday` DATETIME,
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别,不能为空,默认男',
	`address` VARCHAR(100) COMMENT '地址',
	`email` VARCHAR(50) COMMENT '邮箱'
);
#创建一个测试表
CREATE TABLE test(
	`id` INT(4) PRIMARY KEY AUTO_INCREMENT
	);
	
#DML
#重命名表
ALTER TABLE test RENAME AS testTable;
#为表添加列
ALTER TABLE testTable ADD `name` VARCHAR(30) DEFAULT '匿名';
#修改表
ALTER TABLE testTable MODIFY `name` VARCHAR(30) DEFAULT '请填写姓名';
ALTER TABLE testTable CHANGE `name` 姓名 VARCHAR(30);

#删除字段
ALTER TABLE testTable DROP 姓名;
#删除表
DROP TABLE IF EXISTS test;

#年纪表(grade)
CREATE TABLE IF NOT EXISTS grade(
	`gradeID` INT(4) PRIMARY KEY AUTO_INCREMENT,
	`gradeName` VARCHAR(50) NOT NULL COMMENT '年级名称'
);

#学生表(student)
CREATE TABLE IF NOT EXISTS student(
	`studentno` INT(4) PRIMARY KEY AUTO_INCREMENT,
	`gradeID` INT(10) COMMENT '年级编号',
	`studentName` VARCHAR(50) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`sex` VARCHAR(2) DEFAULT '男' COMMENT '性别',
	`phone` INT(15) COMMENT '手机号',
	`address` VARCHAR(100) COMMENT '地址',
	`email` VARCHAR(50) COMMENT '邮箱',
	`brithday` DATETIME,
	#添加外键(添加外键方法一:创建表的时候同时添加外键)
	CONSTRAINT fk_gradeID FOREIGN KEY(`gradeID`) REFERENCES grade(`gradeID`)
);

#添加外键方法二:创建子表完毕后,修改子表添加外键
ALTER TABLE student
ADD CONSTRAINT fk_gradeID FOREIGN KEY(`gradeID`) REFERENCES grade(`gradeID`);

#删除具有主外键关系的表时,要先删除子表,后删主表
#删除外键
ALTER TABLE student DROP FOREIGN KEY fk_gradeID;
#删除索引
ALTER TABLE student DROP INDEX fk_gradeID;

#插入数据
#插入一列数据
INSERT INTO grade(`gradeName`) VALUES('一年级');
#插入多列数据
INSERT INTO student(`gradeID`,`studentName`,`sex`,`phone`,`address`,`email`,`brithday`)
VALUES(1,'张三','男',1111111,'安德新寓','1111@qq.com','2019-05-26');
#插入多行数据
INSERT INTO student(`gradeID`,`studentName`,`sex`,`phone`,`address`,`email`,`brithday`)
VALUES(1,'李四','男',2222222,'中华门','2222@qq.com','2000-05-26'),
(1,'王五',DEFAULT,3333333,'安德门',NULL,'2011-05-26'),
(1,'赵六','女',4444444,'夫子庙','4444@qq.com',NOW());	#NOW()当前时间

#修改数据
UPDATE student SET `address`='安德门'
WHERE `studentno`=1;
#修改多列数据
UPDATE student SET `phone`=12345678,`email`='123@qq.com',`brithday`='1999-05-26'
WHERE `studentno`=1;

#条件使用运算符
UPDATE student SET `sex`='保密'
WHERE `studentno`=1 OR `studentno`=2 OR `studentno`=3;

UPDATE student SET `sex`='男'
WHERE `studentno`>=1 AND `studentno`<=3;

UPDATE student SET `sex`='女'
WHERE `studentno` BETWEEN 1 AND 3;

#使用函数
#NOW()当前时间
UPDATE student SET `brithday`=NOW()
WHERE `studentno`=1;
#CONCAT()连接字符串
UPDATE student SET `studentName`=CONCAT('姓名:',`studentName`);

#删除数据
DELETE FROM student WHERE `studentno`=4;
#删除表全部数据(不带where条件的delete)
#自增当前值依然从原来的基础上继续增长
DELETE FROM student;
#删除表全部数据(truncate)
#自增值恢复到初始值重新开始,当前表结构相当于重建
TRUNCATE TABLE student;

#创建两个表,分别为InnoDb、MyISAM类型
CREATE TABLE tab1(
		`id` INT(4) PRIMARY KEY AUTO_INCREMENT,
		`coll` VARCHAR(20) NOT NULL
	)ENGINE=INNODB;
CREATE TABLE tab2(
		`id` INT(4) PRIMARY KEY AUTO_INCREMENT,
		`coll` VARCHAR(20) NOT NULL
	)ENGINE=MYISAM;
INSERT INTO tab1(`coll`) VALUES('row1'),('row2'),('row3');
INSERT INTO tab2(`coll`) VALUES('row1'),('row2'),('row3');
#清空表数据
DELETE FROM tab1;
DELETE FROM tab2;
#重启数据库服务后,tab1:1 2 3	tab2:4 5 6
#InnoDb和MyISAM类型同样适用delete from清空数据,对于InnoDb类型的表,自增列从初始值重新开始,而MyISA类型的表,自增列依然从上一个自增数据的基础上开始


#DQL
#创建school库、grade表、result表、student表、subject表

CREATE DATABASE IF NOT EXISTS `school`;

USE `school`;

/*Table structure for table `grade` */

DROP TABLE IF EXISTS `grade`;

CREATE TABLE `grade` (
  `gradeid` INT(4) NOT NULL AUTO_INCREMENT,
  `gradename` VARCHAR(32) NOT NULL,
  PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `grade` */

INSERT  INTO `grade`(`gradeid`,`gradename`) VALUES (1,'S1'),(2,'S2'),(3,'S3');

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `studentNo` INT(4) NOT NULL COMMENT '学号',
  `loginPwd` VARCHAR(20) NOT NULL COMMENT '密码',
  `studentName` VARCHAR(50) NOT NULL COMMENT '学生姓名',
  `sex` CHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `gradeId` INT(4) DEFAULT NULL COMMENT '年级编号',
  `phone` VARCHAR(50) DEFAULT NULL COMMENT '联系电话',
  `address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
  `bornDate` DATETIME DEFAULT NULL COMMENT '出生时间',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮件账号',
  `identityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号码',
  PRIMARY KEY (`studentNo`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

/*Data for the table `student` */

INSERT  INTO `student`(`studentNo`,`loginPwd`,`studentName`,`sex`,`gradeId`,`phone`,`address`,`bornDate`,`email`,`identityCard`)
VALUES (10000,'123','郭靖','男',1,'13645667783','天津市河西区','1990-09-08 00:00:00',NULL,NULL),
(10001,'123','李文才','男',1,'13645667890','地址不详','1994-04-12 00:00:00',NULL,NULL),
(10002,'123','李斯文','男',1,'13645556793','河南洛阳','1993-07-23 00:00:00',NULL,NULL),
(10003,'123','张萍','女',1,'13642345112','地址不详','1995-06-10 00:00:00',NULL,NULL),
(10004,'123','韩秋洁','女',1,'13812344566','北京市海淀区','1995-07-15 00:00:00',NULL,NULL),
(10005,'123','张秋丽','女',1,'13567893246','北京市东城区','1994-01-17 00:00:00',NULL,NULL),
(10006,'123','肖梅','女',1,'13563456721','河北省石家庄市','1991-02-17 00:00:00',NULL,NULL),
(10007,'123','秦洋','男',1,'13056434411','上海市卢湾区','1992-04-18 00:00:00',NULL,NULL),
(10008,'123','何睛睛','女',1,'13053445221','广州市天河区','1997-07-23 00:00:00',NULL,NULL),
(20000,'123','王宝宝','男',2,'15076552323','地址不详','1996-06-05 00:00:00',NULL,NULL),
(20010,'123','何小华','女',2,'13318877954','地址不详','1995-09-10 00:00:00',NULL,NULL),
(30011,'123','陈志强','男',3,'13689965430','地址不详','1994-09-27 00:00:00',NULL,NULL),
(30012,'123','李%','女',3,'13685678854','地址不详','1992-09-27 00:00:00',NULL,NULL);

/*Table structure for table `subject` */

DROP TABLE IF EXISTS `subject`;

CREATE TABLE `subject` (
  `subjectNo` INT(4) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
  `subjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
  `classHour` INT(4) DEFAULT NULL COMMENT '学时',
  `gradeID` INT(4) DEFAULT NULL COMMENT '年级编号',
  PRIMARY KEY (`subjectNo`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='课程表';

/*Data for the table `subject` */

INSERT  INTO `subject`(`subjectNo`,`subjectName`,`classHour`,`gradeID`)
VALUES (1,'Login Java',220,1),
(2,'HTML',160,1),
(3,'Java OOP',230,2);

/*Table structure for table `result` */

DROP TABLE IF EXISTS `result`;

CREATE TABLE `result` (
  `studentNo` INT(4) NOT NULL COMMENT '学号',
  `subjectNo` INT(4) NOT NULL COMMENT '课程编号',
  `examDate` DATETIME NOT NULL COMMENT '考试日期',
  `studentResult` INT(4) NOT NULL COMMENT '考试成绩',
  PRIMARY KEY (`studentNo`,`subjectNo`,`examDate`),
  CONSTRAINT `FK_result_student` FOREIGN KEY (`studentNo`) REFERENCES `student` (`studentNo`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

/*Data for the table `result` */

INSERT  INTO `result`(`studentNo`,`subjectNo`,`examDate`,`studentResult`)
VALUES (10000,1,'2016-02-15 00:00:00',71),
(10000,1,'2016-02-17 00:00:00',60),
(10001,1,'2016-02-17 00:00:00',46),
(10002,1,'2016-02-17 00:00:00',83),
(10003,1,'2016-02-17 00:00:00',60),
(10004,1,'2016-02-17 00:00:00',60),
(10005,1,'2016-02-17 00:00:00',95),
(10006,1,'2016-02-17 00:00:00',93),
(10007,1,'2016-02-17 00:00:00',23);

#插入数据完毕后查询

#查询所有学生信息(所有列,效率低)
SELECT * FROM `student`;
#查询指定列(学号 姓名)
SELECT `studentNo`,`studentName` FROM `student`;
#取别名(AS可以省略)
SELECT `studentNo` AS "学号",`studentName` "姓名" FROM `student`;
#为表取别名(AS可以省略)
SELECT s.`studentNo` AS "学号",s.`studentName` "姓名" FROM `student` s;
#使用AS为查询结果取别名
SELECT CONCAT('姓名:',`studentName`) AS "学生姓名" FROM `student`;
#查询参加考试的学生学号(使用DISTINCT去重复)
SELECT DISTINCT `studentNo` FROM `result`;

#select查询中可以使用表达式
#查自增步长
SELECT @@auto_increment_increment
#查MySQL版本号
SELECT VERSION();
#select计算
SELECT 100*1 AS 计算结果;

#学生考试成绩集体加1分
SELECT `studentNo`,`studentResult`+1 AS "加分后" FROM `result`;

#满足条件的查询(where条件)

#查询成绩95-100
SELECT `studentNo`,`studentResult`
FROM `result`
WHERE `studentResult`>=95 AND `studentResult`<=100;
#模糊查询
SELECT `studentNo`,`studentResult`
FROM `result`
WHERE `studentResult` BETWEEN 95 AND 100;
#精确查询
SELECT `studentNo`,`studentResult`
FROM `result`
WHERE `studentNo`=10000;
#模糊查询(like %)
#查询名字中有“文”的同学
SELECT `studentNo`,`studentName`
FROM `student`
WHERE `studentName` LIKE ("%文%");
#查询名字中有“%”的同学(转义符 \)
SELECT `studentNo`,`studentName`
FROM `student`
WHERE `studentName` LIKE ("%\%%");
#使用自己自定义的转义符(:)
SELECT `studentNo`,`studentName`
FROM `student`
WHERE `studentName` LIKE ("%\:%%") ESCAPE ':';
#使用or连接多个条件
SELECT `studentNo`,`studentName`
FROM `student`
WHERE `studentNo`=10000 OR `studentNo`=10001 OR `studentNo`=10002;
#使用in连接多个条件
SELECT `studentNo`,`studentName`
FROM `student`
WHERE `studentNo` IN(10000,10001,10002);
收录详情:百度已收录
版权声明:本文内容源自互联网,由(柳意梧情)整编。
文章标题:数据库语句整理
本文地址:https://blog.liuyiwuqing.cn/MySQL/118.html
本文最后更新于2019-6-3,已超过半年没有更新,如果文章内容或图片资源失效,请留言反馈,我们会及时处理,谢谢!
分享到:
版权所有,转载注意明处:柳意梧情博客 » 数据库语句整理

WRITTEN BY:柳意梧情

avatar

评论

游客

切换注册

登录

您也可以使用第三方帐号快捷登录

切换登录

注册

2988 sitemap