MySQL

最新推荐文章于 2022-02-19 11:30:00 发布
最新推荐文章于 2022-02-19 11:30:00 发布
阅读量443 收藏 1
点赞数 1
文章标签： 数据库 java php
原文链接：http://www.cnblogs.com/littlelazy/p/10136412.html
版权
1.初识MySQL

 

#作业 新建MySchool数据库
create database if not exists MySchool;
#作业 新建数据库表grade
#字段 GradeID: int(11) Primary Key(pk)
#字段 GradeName: varchar(50)
use MySchool;
create table grade(
    `GradeID` int(11) Primary key,
    `GradeNmae` varchar(50)
)
View Code

 

2.创建MySQL数据库和表（上） 
#创建数据库Test_DB
CREATE DATABASE IF NOT EXISTS Test_DB;

#查看数据库
SHOW DATABASES;

#使用数据库
USE Test_DB;

#删除数据库
DROP DATABASE IF EXISTS Test_DB;


#创建数据库school
create database if not exists school;

#使用school数据库
use school;

#创建数据库表student
drop table if exists `student`;

create table `student`(
    `StudentNo` int(4) not null comment '学号',
    `LoginPwd` varchar(20) default null,
    `StudentName` varchar(20) default null comment '学生姓名',
    `Sex` tinyint(1) default null comment '性别，取值0或1',
    `GradeId` int(11) default null comment '年级编号',
    `Phone` varchar(50) not null comment '联系电话，允许为空，即可选输入',
    `Address` varchar(255) not null comment '地址，允许为空，即可选输入',
    `BornDate` datetime default null comment '出生事件',
    `Email` varchar(50) not null comment '邮箱编号，允许为空，即可选输入',
    `IdentityCard` varchar(18) default null comment '身份证号',
    primary key(`StudentNo`)
)engine=Innodb default charset=utf8;

#数据表创建语法
drop table if exists `member`;

create table if not exists `member`(
    `id` int(10) unsigned not null auto_increment,
    `userCode` varchar(32) not null comment '账号',
    `userName` varchar(32) not null comment '真实姓名',
    `password` varchar(32) not null comment '密码',
    `birthday` date not null default '0000-00-00' comment '出生日期',
    primary key(id),
    index username(userCode,userName)
)engine=myisam default CHARACTER set utf8 COLLATE utf8_general_ci comment="用户表";

#创建一个test01表格
create table if not exists `test01`(id int(11) not null);
#删除表
drop table `test01`;
#如果表名不存在会报错
# ERROR 1051 (42S02): Unknown table 'test011'
#最好加上 IF 判断
drop table if exists `test01`;

#作业 使用语句新建result表
#字段 `StudentNo` int(4) not null comment '学号'
#字段 `SubjectNo` int(4) not null comment '课程编号'
#字段 `ExamDate` datetime not null comment '考试日期'
#字段 `StudentResult` int(4) not null comment '考试成绩'
create table if not exists result(
    `StudentNo` int(4) not null comment '学号',
    `SubjectNo` int(4) not null comment '课程编号',
    `ExamDate` datetime not null comment '考试日期',
    `StudentResult` int(4) not null comment '考试成绩'
);
View Code

 

3.创建MySQL数据库和表（下）

 

#课堂代码
#查看数据库的定义
show create database school;
#查看数据表的定义
show create table student;
#显示表结构
desc student;

#设置数据库、表、字段的字符集
/*create database `school` default CHARACTER set utf8;

create table `student` (
    `id` int(4) not null auto_increment comment '主键、学号',
    `pwd` varchar(20) not null default '123456' comment '密码',
    `name` varchar(30) not null default '匿名' comment '学生姓名',
    `sex` varchar(2) not null default '男',
    `birthday` datetime default null,
    `address` varchar(100) default null,
    `email` varchar(50) default null,
    primary key(id)
)engine=innodb auto_increment=3 default charset=utf8;

create table a(
    address char(5) character set gbk
);
*/

#创建一个测试表
create table test(
    col1 int(4) zerofill not null
);

#设置严格的检查模式
#Strict Mode功能说明
# 1 不支持对not null字段插入null值
# 2 不支持对自增长字段插入值
# 3 不支持text字段有默认值
# https://blog.csdn.net/fdipzone/article/details/50616247
set sql_mode='strict_trans_tables';

create table tab1(
    id int(4) primary key auto_increment,
    `name` varchar(20) not null
);

#自增列：默认初始值1 步长1
#能够改变自增列的初始值？ 100
#只影响当前表
create table tab1(
    id int(4) primary key auto_increment,
    `name` varchar(20) not null
)auto_increment=100;

#能否改变自增列的步长？   5
#影响所有使用自增列的表
set @@auto_increment_increment=5;

#同一个列可以被多个属性修饰，但是注意属性的顺序
create table tab2(
    id int(4) zerofill not null
)engine=innodb;

#查看mysql所支持的引擎类型（表类型）
show engines;
#查看默认引擎
show variables like 'storage_engine';

create table tab3(
    id int(4) zerofill not null
)engine=myisam;


#重命名表
alter table tab1 rename as table1;
#为表添加列
alter table table1 add address varchar(100) default '中国北京';
#修改表
alter table table1 modify address varchar(500) default '中国北京朝阳区';

alter table table1 change address 地址 varchar(100) default '中国北京朝阳区';

#删除字段
alter table tab4 drop `name`;

create table tab4(
    id int(4) zerofill not null,
    name varchar(50)
)engine=myisam;

#删除表
drop table if exists tab4;

#设置数据表的类型
drop table if exists demo02;
create table if not exists demo02(
    id int(10) not null auto_increment,
    name varchar(32) not null,
    primary key(id)
)engine=myisam;

#或
drop table if exists demo02;
create table if not exists demo02(
    id int(10) not null auto_increment,
    name varchar(32) not null,
    primary key(id)
)engine=innodb;


drop table if exists demo01;
create table if not exists demo01(
    id int(10) not null auto_increment,
    name varchar(32) not null,
    primary key(id)
);

#修改表名
alter table demo01 rename as demo03;
#修改字段
alter table demo03 change name username varchar(32) not null;
#添加字段
alter table demo03 add password varchar(32) not null;
#删除字段
alter table demo03 drop password;

#作业 使用语句新建subject表
# 设置数据表为MyISAM类型
drop table if exists `subject`;
create table if not exists `subject`(
    `SubjectNo` int(11) not null comment '课程编号',
    `SubjectName` varchar(50) default null comment '课程名称',
    `ClassHour` int(4) default null comment '学时',
    `GrandID` int(4) default null comment '年级编号'
)engine=myisam;
View Code

 

4.MySQL数据库数据管理

 

#作业 使用insert语句为课程表subject添加数据
USE school;
ALTER TABLE `subject` CHANGE GrandID GradeID INT(4) DEFAULT NULL COMMENT '年级编号';
INSERT INTO `subject` (SubjectNo,SubjectName,ClassHour,GradeID)
VALUES
(1,'高等数学-1',120,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4);

#创建外键方式一：创建子表的同时创建外键
#年级表（id/年级名称）
CREATE TABLE IF NOT EXISTS grade(
  gradeId INT(10) PRIMARY KEY AUTO_INCREMENT,
  gradeName VARCHAR(50) NOT NULL
);

#学生信息表（学号、姓名、性别年级、手机号、地址、出生日期、邮箱、身份证号）
CREATE TABLE IF NOT EXISTS studet(
  studentNo INT(4) PRIMARY KEY,
  studentName VARCHAR(20) NOT NULL DEFAULT '匿名',
  sex TINYINT(1) DEFAULT 1,
  gradeId INT(10),
  phone VARCHAR(50) NOT NULL,
  address VARCHAR(255),
  bornDate DATETIME,
  email VARCHAR(50),
  identityCard VARCHAR(18) NOT NULL,
  CONSTRAINT FK_gradeId FOREIGN KEY(gradeId) REFERENCES grade(gradeId)
);

#创建外键的方式二：创建子表完毕后，修改子表添加外键
CREATE TABLE student(
  studentNo INT PRIMARY KEY,
  studentName VARCHAR(20) NOT NULL DEFAULT '匿名',
  sex TINYINT(1) DEFAULT 1,
  gradeId INT(10),
  phone VARCHAR(50) NOT NULL,
  address VARCHAR(255),
  bornDate DATETIME,
  email VARCHAR(50),
  identityCard VARCHAR(18) NOT NULL
);

ALTER TABLE student
ADD CONSTRAINT FK_gradeId FOREIGN KEY(gradeId) REFERENCES grade(gradeId);4
#删除具有主外键关系的表时，要先删子表，后删主表

#删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeId;
ALTER TABLE student DROP INDEX FK_gradeid;

#使用语句如何增加数据
INSERT INTO grade(gradename) VALUES('大一');
INSERT INTO grade VALUES(2,'大二');
INSERT INTO grade(gradename) VALUES('大三'),('大四');

#NOW()代表当前时间
INSERT INTO student(studentNO,studentName,sex,gradeId,phone,address,bornDate,email,identityCard)
VALUES(1003,'张三',2,1,'18711765400',NULL,NOW(),NULL,'370123198803040007'),
(1004,'王五',DEFAULT,2,'13611788888','苏州',NOW(),'wangwu@163.com','370123199003040111');

#使用语句修改数据
#将李四的地址修改为中国南京
UPDATE student SET address='中国南京'
WHERE studentNO=1001;

#同时修改多列
UPDATE student SET address='中国南京',email='lisi@163.com'
WHERE studentNo=1001;

#条件可以使用运算符
UPDATE student SET sex=1
WHERE studentNo=1001 OR studentNo=1002 OR studentNO=1003;

UPDATE student SET sex=2
WHERE studentNo>=1001 AND studentNo<=1003;

UPDATE student SET sex=1
WHERE studentNo BETWEEN 1001 AND 1003;

#使用函数
UPDATE student SET studentName=CONCAT("姓名：",studentName);

#删除数据
DELETE FROM grade WHERE gradeId=4;


#创建一个demo_delete表
DROP TABLE IF EXISTS demo_delete;
CREATE TABLE IF NOT EXISTS demo_delete(
  id INT(10) NOT NULL AUTO_INCREMENT,
  title VARCHAR(32) NOT NULL,
  PRIMARY KEY(id)
)AUTO_INCREMENT=5;

INSERT INTO demo_delete (title) VALUES ("aaaaaa"),("bbbbbb"),("cccccc"),("dddddd");

#TRUNCATE清除数据
TRUNCATE TABLE demo_delete;


#与 用 delete 删除数据比较，auto_increment 的值不同
# delete 删除的不会重置该值，从上次插入的主键ID开始累计
DELETE FROM demo_delete;


#创建一个demo_delete表
DROP TABLE IF EXISTS demo_delete;
CREATE TABLE IF NOT EXISTS demo_delete(
  id INT(10) NOT NULL AUTO_INCREMENT,
  title VARCHAR(32) NOT NULL,
  PRIMARY KEY(id)
);

INSERT INTO demo_delete(title) VALUES("aaaaaa"),("bbbbbb"),("cccccc"),("dddddd");

#带where条件的删除数据
DELETE FROM demo_delete WHERE title="aaaaaa";

#不加where条件的删除数据
DELETE FROM demo_delete;

#添加数据表grade数据
INSERT INTO `grade` (`GradeID`,`GradeName`)
VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四');

#添加数据表student数据
INSERT INTO student
(StudentNO,StudentName,Sex,GradeId,Phone,Address,Email,IdentityCard)
VALUES
(1111,"郭靖",1,1,"13500000001","北京海淀区中关村大街1号","guojing@bdqn.cn","450323198612111000"),
(1112,"李文才",1,2,"13500000002","河南洛阳","liwencai@bdqn.cn","450323198512311000"),
(1113,"郭梅",2,3,"13500000015","上海卢湾区","limei@bdqn.cn","450323198412311000");

#修改数据
UPDATE student SET email="student1013@bdqn.cn", LoginPwd="000000" WHERE StudentNo="1013";
UPDATE `subject` SET ClassHour=(ClassHour-10) WHERE ClassHour>110 AND GradeId=1;

#外键：创建表时添加外键约束
#年级表：年级编号、年级名称
CREATE TABLE grade(
  gradeId INT(10) PRIMARY KEY AUTO_INCREMENT,
  gradeName VARCHAR(50) NOT NULL
);

#学生表：学号、姓名、性别、年级、手机号、地址、出生日期、邮箱、身份证
CREATE TABLE student(
  studentNo INT(4) PRIMARY KEY,
  studentName VARCHAR(20) NOT NULL DEFAULT '匿名',
  sex TINYINT(1) DEFAULT 1,
  gradeId INT(10),
  phone VARCHAR(50) NOT NULL,
  address VARCHAR(255),
  bornDate DATETIME,
  email VARCHAR(50),
  identityCard VARCHAR(18) NOT NULL,
  CONSTRAINT FK_gradeId FOREIGN KEY(gradeId) REFERENCES grade(gradeId)
);

#删除表时，先删除子表，再删除主表

#外键：创建表后，添加外键约束
CREATE TABLE grade(
  gradeId INT(10) PRIMARY KEY AUTO_INCREMENT,
  gradeName VARCHAR(50) NOT NULL
);

#学生表：学号、姓名、性别、年级、手机号、地址、出生日期、邮箱、身份证
CREATE TABLE student(
  studentNo INT(4) PRIMARY KEY,
  studentName VARCHAR(20) NOT NULL DEFAULT '匿名',
  sex TINYINT(1) DEFAULT 1,
  gradeId INT(10),
  phone VARCHAR(50) NOT NULL,
  address VARCHAR(255),
  bornDate DATETIME,
  email VARCHAR(50),
  identityCard VARCHAR(18) NOT NULL
);

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;

SHOW CREATE TABLE student;

INSERT INTO grade
VALUES(1,'大一');

INSERT INTO student 
VALUES(1,'zhangsan',1,1,'13109930444','北京',NOW(),'zs@163.com','370111190008074311');
View Code

 

5.使用DQL查询数据（一）

 

 
  
#作业 查询课程表的所有记录，返回如图所示
#要求：1.返回字段名称使用别称 2.返回课程名称（SubjectName）总课时（SubjectHour）
# 3.返回10天上完课程的均课时（ClassHour/10）
USE school;
SELECT * FROM `subject`;
SELECT subjectname '课程名称',classhour '总课时',(classhour/10) '均课时/天'
FROM `subject`;

USE MySchool;
SHOW TABLES;
SELECT * FROM grade;
CREATE TABLE test(
  id INT(4) PRIMARY KEY AUTO_INCREMENT,
  coll VARCHAR(20) NOT NULL
);

INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');
SELECT * FROM test;

#删除表全部数据（不带where条件的delete）
#自增当前值 依然从原来的基础上进行
DELETE FROM test;
INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');
SELECT * FROM test;
#删除表全部数据（truncate）
#自增值恢复到初始值重新开始
TRUNCATE TABLE test;
INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');
SELECT * FROM test;

#创建两个表，分别为InnoDB类型、MyISAM类型
CREATE TABLE tab1(
  id INT(4) PRIMARY KEY AUTO_INCREMENT,
  col1 VARCHAR(20) NOT NULL
)ENGINE=INNODB;

CREATE TABLE tab2(
  id INT(4) PRIMARY KEY AUTO_INCREMENT,
  col1 VARCHAR(20) NOT NULL
)ENGINE=MYISAM;

INSERT INTO tab1(col1) VALUES('row1'),('row2'),('row3');
SELECT * FROM tab1;

INSERT INTO tab2(col1) VALUES('row1'),('row2'),('row3');
SELECT * FROM tab2;


#清空数据
DELETE FROM tab1;
DELETE FROM tab2;

INSERT INTO tab1(col1) VALUES('row1'),('row2'),('row3');
SELECT * FROM tab1;

INSERT INTO tab2(col1) VALUES('row1'),('row2'),('row3');
SELECT * FROM tab2;

TRUNCATE TABLE tab1;
TRUNCATE TABLE tab2;

#重启数据库服务后，tab1：1 2 3；tab2：7 8 9
#同样适用delete from 清空表数据，重启数据库后，对于Innodb表，自增列从初始值重新开始
#而Myisam类型的表，自增列依然从上一个自增数列基础上开始

#启动net start mysql；停止服务 net stop mysql

USE school;
#素材
  
DROP TABLE IF EXISTS `grade`; 

CREATE TABLE `grade` (
  `GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `GradeName` VARCHAR(50) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`GradeID`)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

 

INSERT  INTO `grade`(`GradeID`,`GradeName`) VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
 
 
 
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 '考试成绩',
  KEY `SubjectNo` (`SubjectNo`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
 

INSERT  INTO `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) VALUES (1000,1,'2013-11-11 16:00:00',94),(1000,2,'2012-11-10 10:00:00',75),(1000,3,'2011-12-19 10:00:00',76),(1000,4,'2010-11-18 11:00:00',93),(1000,5,'2013-11-11 14:00:00',97),(1000,6,'2012-09-13 15:00:00',87),(1000,7,'2011-10-16 16:00:00',79),(1000,8,'2010-11-11 16:00:00',74),(1000,9,'2013-11-21 10:00:00',69),(1000,10,'2012-11-11 12:00:00',78),(1000,11,'2011-11-11 14:00:00',66),(1000,12,'2010-11-11 15:00:00',82),(1000,13,'2013-11-11 14:00:00',94),(1000,14,'2012-11-11 15:00:00',98),(1000,15,'2011-12-11 10:00:00',70),(1000,16,'2010-09-11 10:00:00',74),(1001,1,'2013-11-11 16:00:00',76),(1001,2,'2012-11-10 10:00:00',93),(1001,3,'2011-12-19 10:00:00',65),(1001,4,'2010-11-18 11:00:00',71),(1001,5,'2013-11-11 14:00:00',98),(1001,6,'2012-09-13 15:00:00',74),(1001,7,'2011-10-16 16:00:00',85),(1001,8,'2010-11-11 16:00:00',69),(1001,9,'2013-11-21 10:00:00',63),(1001,10,'2012-11-11 12:00:00',70),(1001,11,'2011-11-11 14:00:00',62),(1001,12,'2010-11-11 15:00:00',90),(1001,13,'2013-11-11 14:00:00',97),(1001,14,'2012-11-11 15:00:00',89),(1001,15,'2011-12-11 10:00:00',72),(1001,16,'2010-09-11 10:00:00',90),(1002,1,'2013-11-11 16:00:00',61),(1002,2,'2012-11-10 10:00:00',80),(1002,3,'2011-12-19 10:00:00',89),(1002,4,'2010-11-18 11:00:00',88),(1002,5,'2013-11-11 14:00:00',82),(1002,6,'2012-09-13 15:00:00',91),(1002,7,'2011-10-16 16:00:00',63),(1002,8,'2010-11-11 16:00:00',84),(1002,9,'2013-11-21 10:00:00',60),(1002,10,'2012-11-11 12:00:00',71),(1002,11,'2011-11-11 14:00:00',93),(1002,12,'2010-11-11 15:00:00',96),(1002,13,'2013-11-11 14:00:00',83),(1002,14,'2012-11-11 15:00:00',69),(1002,15,'2011-12-11 10:00:00',89),(1002,16,'2010-09-11 10:00:00',83),(1003,1,'2013-11-11 16:00:00',91),(1003,2,'2012-11-10 10:00:00',75),(1003,3,'2011-12-19 10:00:00',65),(1003,4,'2010-11-18 11:00:00',63),(1003,5,'2013-11-11 14:00:00',90),(1003,6,'2012-09-13 15:00:00',96),(1003,7,'2011-10-16 16:00:00',97),(1003,8,'2010-11-11 16:00:00',77),(1003,9,'2013-11-21 10:00:00',62),(1003,10,'2012-11-11 12:00:00',81),(1003,11,'2011-11-11 14:00:00',76),(1003,12,'2010-11-11 15:00:00',61),(1003,13,'2013-11-11 14:00:00',93),(1003,14,'2012-11-11 15:00:00',79),(1003,15,'2011-12-11 10:00:00',78),(1003,16,'2010-09-11 10:00:00',96),(1004,1,'2013-11-11 16:00:00',84),(1004,2,'2012-11-10 10:00:00',79),(1004,3,'2011-12-19 10:00:00',76),(1004,4,'2010-11-18 11:00:00',78),(1004,5,'2013-11-11 14:00:00',81),(1004,6,'2012-09-13 15:00:00',90),(1004,7,'2011-10-16 16:00:00',63),(1004,8,'2010-11-11 16:00:00',89),(1004,9,'2013-11-21 10:00:00',67),(1004,10,'2012-11-11 12:00:00',100),(1004,11,'2011-11-11 14:00:00',94),(1004,12,'2010-11-11 15:00:00',65),(1004,13,'2013-11-11 14:00:00',86),(1004,14,'2012-11-11 15:00:00',77),(1004,15,'2011-12-11 10:00:00',82),(1004,16,'2010-09-11 10:00:00',87),(1005,1,'2013-11-11 16:00:00',82),(1005,2,'2012-11-10 10:00:00',92),(1005,3,'2011-12-19 10:00:00',80),(1005,4,'2010-11-18 11:00:00',92),(1005,5,'2013-11-11 14:00:00',97),(1005,6,'2012-09-13 15:00:00',72),(1005,7,'2011-10-16 16:00:00',84),(1005,8,'2010-11-11 16:00:00',79),(1005,9,'2013-11-21 10:00:00',76),(1005,10,'2012-11-11 12:00:00',87),(1005,11,'2011-11-11 14:00:00',65),(1005,12,'2010-11-11 15:00:00',67),(1005,13,'2013-11-11 14:00:00',63),(1005,14,'2012-11-11 15:00:00',64),(1005,15,'2011-12-11 10:00:00',99),(1005,16,'2010-09-11 10:00:00',97),(1006,1,'2013-11-11 16:00:00',82),(1006,2,'2012-11-10 10:00:00',73),(1006,3,'2011-12-19 10:00:00',79),(1006,4,'2010-11-18 11:00:00',63),(1006,5,'2013-11-11 14:00:00',97),(1006,6,'2012-09-13 15:00:00',83),(1006,7,'2011-10-16 16:00:00',78),(1006,8,'2010-11-11 16:00:00',88),(1006,9,'2013-11-21 10:00:00',89),(1006,10,'2012-11-11 12:00:00',82),(1006,11,'2011-11-11 14:00:00',70),(1006,12,'2010-11-11 15:00:00',69),(1006,13,'2013-11-11 14:00:00',64),(1006,14,'2012-11-11 15:00:00',80),(1006,15,'2011-12-11 10:00:00',90),(1006,16,'2010-09-11 10:00:00',85),(1007,1,'2013-11-11 16:00:00',87),(1007,2,'2012-11-10 10:00:00',63),(1007,3,'2011-12-19 10:00:00',70),(1007,4,'2010-11-18 11:00:00',74),(1007,5,'2013-11-11 14:00:00',79),(1007,6,'2012-09-13 15:00:00',83),(1007,7,'2011-10-16 16:00:00',86),(1007,8,'2010-11-11 16:00:00',76),(1007,9,'2013-11-21 10:00:00',65),(1007,10,'2012-11-11 12:00:00',87),(1007,11,'2011-11-11 14:00:00',69),(1007,12,'2010-11-11 15:00:00',69),(1007,13,'2013-11-11 14:00:00',90),(1007,14,'2012-11-11 15:00:00',84),(1007,15,'2011-12-11 10:00:00',95),(1007,16,'2010-09-11 10:00:00',92),(1008,1,'2013-11-11 16:00:00',96),(1008,2,'2012-11-10 10:00:00',62),(1008,3,'2011-12-19 10:00:00',97),(1008,4,'2010-11-18 11:00:00',84),(1008,5,'2013-11-11 14:00:00',86),(1008,6,'2012-09-13 15:00:00',72),(1008,7,'2011-10-16 16:00:00',67),(1008,8,'2010-11-11 16:00:00',83),(1008,9,'2013-11-21 10:00:00',86),(1008,10,'2012-11-11 12:00:00',60),(1008,11,'2011-11-11 14:00:00',61),(1008,12,'2010-11-11 15:00:00',68),(1008,13,'2013-11-11 14:00:00',99),(1008,14,'2012-11-11 15:00:00',77),(1008,15,'2011-12-11 10:00:00',73),(1008,16,'2010-09-11 10:00:00',78),(1009,1,'2013-11-11 16:00:00',67),(1009,2,'2012-11-10 10:00:00',70),(1009,3,'2011-12-19 10:00:00',75),(1009,4,'2010-11-18 11:00:00',92),(1009,5,'2013-11-11 14:00:00',76),(1009,6,'2012-09-13 15:00:00',90),(1009,7,'2011-10-16 16:00:00',62),(1009,8,'2010-11-11 16:00:00',68),(1009,9,'2013-11-21 10:00:00',70),(1009,10,'2012-11-11 12:00:00',83),(1009,11,'2011-11-11 14:00:00',88),(1009,12,'2010-11-11 15:00:00',65),(1009,13,'2013-11-11 14:00:00',91),(1009,14,'2012-11-11 15:00:00',99),(1009,15,'2011-12-11 10:00:00',65),(1009,16,'2010-09-11 10:00:00',83),(1010,1,'2013-11-11 16:00:00',83),(1010,2,'2012-11-10 10:00:00',87),(1010,3,'2011-12-19 10:00:00',89),(1010,4,'2010-11-18 11:00:00',99),(1010,5,'2013-11-11 14:00:00',91),(1010,6,'2012-09-13 15:00:00',96),(1010,7,'2011-10-16 16:00:00',72),(1010,8,'2010-11-11 16:00:00',72),(1010,9,'2013-11-21 10:00:00',98),(1010,10,'2012-11-11 12:00:00',73),(1010,11,'2011-11-11 14:00:00',68),(1010,12,'2010-11-11 15:00:00',62),(1010,13,'2013-11-11 14:00:00',67),(1010,14,'2012-11-11 15:00:00',69),(1010,15,'2011-12-11 10:00:00',71),(1010,16,'2010-09-11 10:00:00',66),(1011,1,'2013-11-11 16:00:00',62),(1011,2,'2012-11-10 10:00:00',72),(1011,3,'2011-12-19 10:00:00',96),(1011,4,'2010-11-18 11:00:00',64),(1011,5,'2013-11-11 14:00:00',89),(1011,6,'2012-09-13 15:00:00',91),(1011,7,'2011-10-16 16:00:00',95),(1011,8,'2010-11-11 16:00:00',96),(1011,9,'2013-11-21 10:00:00',89),(1011,10,'2012-11-11 12:00:00',73),(1011,11,'2011-11-11 14:00:00',82),(1011,12,'2010-11-11 15:00:00',98),(1011,13,'2013-11-11 14:00:00',66),(1011,14,'2012-11-11 15:00:00',69),(1011,15,'2011-12-11 10:00:00',91),(1011,16,'2010-09-11 10:00:00',69),(1012,1,'2013-11-11 16:00:00',86),(1012,2,'2012-11-10 10:00:00',66),(1012,3,'2011-12-19 10:00:00',97),(1012,4,'2010-11-18 11:00:00',69),(1012,5,'2013-11-11 14:00:00',70),(1012,6,'2012-09-13 15:00:00',74),(1012,7,'2011-10-16 16:00:00',91),(1012,8,'2010-11-11 16:00:00',97),(1012,9,'2013-11-21 10:00:00',84),(1012,10,'2012-11-11 12:00:00',82),(1012,11,'2011-11-11 14:00:00',90),(1012,12,'2010-11-11 15:00:00',91),(1012,13,'2013-11-11 14:00:00',91),(1012,14,'2012-11-11 15:00:00',97),(1012,15,'2011-12-11 10:00:00',85),(1012,16,'2010-09-11 10:00:00',90),(1013,1,'2013-11-11 16:00:00',73),(1013,2,'2012-11-10 10:00:00',69),(1013,3,'2011-12-19 10:00:00',91),(1013,4,'2010-11-18 11:00:00',72),(1013,5,'2013-11-11 14:00:00',76),(1013,6,'2012-09-13 15:00:00',87),(1013,7,'2011-10-16 16:00:00',61),(1013,8,'2010-11-11 16:00:00',77),(1013,9,'2013-11-21 10:00:00',83),(1013,10,'2012-11-11 12:00:00',99),(1013,11,'2011-11-11 14:00:00',91),(1013,12,'2010-11-11 15:00:00',84),(1013,13,'2013-11-11 14:00:00',98),(1013,14,'2012-11-11 15:00:00',74),(1013,15,'2011-12-11 10:00:00',92),(1013,16,'2010-09-11 10:00:00',90),(1014,1,'2013-11-11 16:00:00',64),(1014,2,'2012-11-10 10:00:00',81),(1014,3,'2011-12-19 10:00:00',79),(1014,4,'2010-11-18 11:00:00',74),(1014,5,'2013-11-11 14:00:00',65),(1014,6,'2012-09-13 15:00:00',88),(1014,7,'2011-10-16 16:00:00',86),(1014,8,'2010-11-11 16:00:00',77),(1014,9,'2013-11-21 10:00:00',86),(1014,10,'2012-11-11 12:00:00',85),(1014,11,'2011-11-11 14:00:00',86),(1014,12,'2010-11-11 15:00:00',75),(1014,13,'2013-11-11 14:00:00',89),(1014,14,'2012-11-11 15:00:00',79),(1014,15,'2011-12-11 10:00:00',73),(1014,16,'2010-09-11 10:00:00',68),(1015,1,'2013-11-11 16:00:00',99),(1015,2,'2012-11-10 10:00:00',60),(1015,3,'2011-12-19 10:00:00',60),(1015,4,'2010-11-18 11:00:00',75),(1015,5,'2013-11-11 14:00:00',78),(1015,6,'2012-09-13 15:00:00',78),(1015,7,'2011-10-16 16:00:00',84),(1015,8,'2010-11-11 16:00:00',95),(1015,9,'2013-11-21 10:00:00',93),(1015,10,'2012-11-11 12:00:00',79),(1015,11,'2011-11-11 14:00:00',74),(1015,12,'2010-11-11 15:00:00',65),(1015,13,'2013-11-11 14:00:00',63),(1015,14,'2012-11-11 15:00:00',74),(1015,15,'2011-12-11 10:00:00',67),(1015,16,'2010-09-11 10:00:00',65),(1016,1,'2013-11-11 16:00:00',97),(1016,2,'2012-11-10 10:00:00',90),(1016,3,'2011-12-19 10:00:00',77),(1016,4,'2010-11-18 11:00:00',75),(1016,5,'2013-11-11 14:00:00',75),(1016,6,'2012-09-13 15:00:00',97),(1016,7,'2011-10-16 16:00:00',96),(1016,8,'2010-11-11 16:00:00',92),(1016,9,'2013-11-21 10:00:00',62),(1016,10,'2012-11-11 12:00:00',83),(1016,11,'2011-11-11 14:00:00',98),(1016,12,'2010-11-11 15:00:00',94),(1016,13,'2013-11-11 14:00:00',62),(1016,14,'2012-11-11 15:00:00',97),(1016,15,'2011-12-11 10:00:00',76),(1016,16,'2010-09-11 10:00:00',82),(1017,1,'2013-11-11 16:00:00',100),(1017,2,'2012-11-10 10:00:00',88),(1017,3,'2011-12-19 10:00:00',86),(1017,4,'2010-11-18 11:00:00',73),(1017,5,'2013-11-11 14:00:00',96),(1017,6,'2012-09-13 15:00:00',64),(1017,7,'2011-10-16 16:00:00',81),(1017,8,'2010-11-11 16:00:00',66),(1017,9,'2013-11-21 10:00:00',76),(1017,10,'2012-11-11 12:00:00',95),(1017,11,'2011-11-11 14:00:00',73),(1017,12,'2010-11-11 15:00:00',82),(1017,13,'2013-11-11 14:00:00',85),(1017,14,'2012-11-11 15:00:00',68),(1017,15,'2011-12-11 10:00:00',99),(1017,16,'2010-09-11 10:00:00',76);

 

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `StudentNo` INT(4) NOT NULL COMMENT '学号',
  `LoginPwd` VARCHAR(20) DEFAULT NULL,
  `StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
  `Sex` TINYINT(1) DEFAULT NULL COMMENT '性别，取值0或1',
  `GradeId` INT(11) DEFAULT NULL COMMENT '年级编号',
  `Phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空，即可选输入',
  `Address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空，即可选输入',
  `BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',
  `Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号，允许为空，即可选输入',
  `IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
  PRIMARY KEY (`StudentNo`),
  UNIQUE KEY `IdentityCard` (`IdentityCard`),
  KEY `Email` (`Email`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;
 
INSERT  INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`) VALUES (1000,'111111','郭靖',1,1,'13500000001','北京海淀区中关村大街1号','1986-12-11 00:00:00','test1@bdqn.cn','450323198612111234'),(1001,'123456','李文才',1,2,'13500000002','河南洛阳','1981-12-31 00:00:00','test1@bdqn.cn','450323198112311234'),(1002,'111111','李斯文',1,1,'13500000003','天津市和平区','1986-11-30 00:00:00','test1@bdqn.cn','450323198611301234'),(1003,'123456','武松',1,3,'13500000004','上海卢湾区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612314234'),(1004,'123456','张三',1,4,'13500000005','北京市通州','1989-12-31 00:00:00','test1@bdqn.cn','450323198612311244'),(1005,'123456','张秋丽 ',2,1,'13500000006','广西桂林市灵川','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311214'),(1006,'123456','肖梅',2,4,'13500000007','地址不详','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311134'),(1007,'111111','欧阳峻峰',1,1,'13500000008','北京东城区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311133'),(1008,'111111','梅超风',1,1,'13500000009','河南洛阳','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311221'),(1009,'123456','刘毅',1,2,'13500000011','安徽','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311231'),(1010,'111111','大凡',1,1,'13500000012','河南洛阳','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311044'),(1011,'111111','奥丹斯',1,1,'13500000013','北京海淀区中关村大街*号','1984-12-31 00:00:00','test1@bdqn.cn','450323198412311234'),(1012,'123456','多伦',2,3,'13500000014','广西南宁中央大街','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311334'),(1013,'123456','李梅',2,1,'13500000015','上海卢湾区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311534'),(1014,'123456','张得',2,4,'13500000016','北京海淀区中关村大街*号','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311264'),(1015,'123456','李东方',1,4,'13500000017','广西桂林市灵川','1976-12-31 00:00:00','test1@bdqn.cn','450323197612311234'),(1016,'111111','刘奋斗',1,1,'13500000018','上海卢湾区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311251'),(1017,'123456','可可',2,3,'13500000019','北京长安街1号','1981-09-10 00:00:00','test1@bdqn.cn','450323198109108311'),(10066,'','Tom',1,1,'13500000000','','0000-00-00 00:00:00','email@22.com','33123123123123123');

DROP TABLE IF EXISTS `subject`;

CREATE TABLE `subject` (
  `SubjectNo` INT(11) 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=18 DEFAULT CHARSET=utf8;
  
INSERT  INTO `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`) VALUES (1,'高等数学-1',110,1),(2,'高等数学-2',110,2),(3,'高等数学-3',100,3),(4,'高等数学-4',130,4),(5,'C语言-1',110,1),(6,'C语言-2',110,2),(7,'C语言-3',100,3),(8,'C语言-4',130,4),(9,'JAVA第一学年',110,1),(10,'JAVA第二学年',110,2),(11,'JAVA第三学年',100,3),(12,'JAVA第四学年',130,4),(13,'数据库结构-1',110,1),(14,'数据库结构-2',110,2),(15,'数据库结构-3',100,3),(16,'数据库结构-4',130,4),(17,'C#基础',130,1);
 
#查询所有学生的信息（所有列，效率低）
SELECT * FROM student;
#查询指定列（学号、姓名）
SELECT studentno,studentname FROM student;
# 为列取别名（as，也可以省略）
SELECT studentno AS 学号, studentname AS 姓名 FROM student;
SELECT studentno 学号, studentname 姓名 FROM student;

#使用as也可以为表取别名
SELECT studentno 学号, studentname 姓名 FROM student AS s;
SELECT studentno 学号, studentname 姓名 FROM student s;

#使用as，为查询结果取一个新名字
SELECT CONCAT('姓名：', studentname) FROM student;
SELECT CONCAT('姓名：', studentname) AS 新姓名 FROM student;

#查看哪些同学参加了考试（学号）-- 去除重复项（distinct，默认all）
SELECT DISTINCT studentno FROM result;

#select查询中可以使用表达式
SELECT @@auto_increment_increment;
SELECT VERSION();
SELECT 100*3-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=1000;
#或者写成&&
SELECT studentno,studentresult
FROM result
WHERE studentresult>=95 && studentresult<=100;

#除了1000号同学，我要其他同学的考试成绩
SELECT studentno,studentresult
FROM result
WHERE studentno!=1000;

SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

#模糊查询 between and\  like \in \null
#查询姓李的同学的学号和姓名
#like结合使用的通配符：%（0到任意个字符） _（一个字符）
SELECT studentno,studentname
FROM student
WHERE studentname 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 '%文%';
View Code

 

6.使用DQL查询数据（二）

 

  
#作业 查询学生表（student）的学号（studentNo），姓名（StudentName）和所在班级（GradeName）
#分别使用：左连接查询方式 右连接查询方式 内连接查询方式
USE school;
SELECT * FROM student;
SELECT * FROM grade;
#左连接
SELECT s.studentno 学号,s.studentname 姓名,g.gradename 所在年级
FROM student s
LEFT JOIN grade g
ON s.gradeid=g.gradeid
#右连接
SELECT s.studentno 学号,s.studentname 姓名,g.gradename 所在年级
FROM student s
RIGHT JOIN grade g
ON s.gradeid=g.gradeid
#内连接
SELECT s.studentno 学号,s.studentname 姓名,g.gradename 所在年级
FROM student s
INNER JOIN grade g
ON s.gradeid=g.gradeid

#模糊查询
SELECT studentno,studentname FROM student;
#like % _
INSERT  INTO `student`
(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`) 
VALUES 
(6666,'6666','郭%',1,1,'13500888001','北京海淀区中关村大街2号','1986-12-11 00:00:00','test888@bdqn.cn','450323198612111888'),
(6667,'6667','李_',1,2,'13500066602','河南洛阳2','1981-12-31 00:00:00','test787@bdqn.cn','450323198178998734');

#查询学员姓名中 有"%" 这个字的同学学号、姓名 转义符
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 ':';

#模糊查询：in    null
SELECT studentno,studentname 
FROM student
WHERE studentno=1000 OR studentno=1001 OR studentno=1002 OR studentno=1003;

SELECT studentno,studentname 
FROM student
WHERE studentno IN (1000,1001,1002,1003);

SELECT studentno,studentname 
FROM student
WHERE address IN ('北京','南京','苏州','扬州');

#null 空
SELECT * FROM student;

INSERT  INTO `student`
(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`) 
VALUES 
(8888,'6666','小红',1,1,'13500885501','北京海淀区中关村大街5号',NULL,'tes5558@bdqn.cn','450323198612114488'),
(8889,'6667','小兰',1,2,'13500066552','河南洛阳5',NULL,'tes5557@bdqn.cn','450323198174448734');

#查询出生日期没有填写的同学 =NULL 是错误的 和null比较必须写 is null
SELECT studentname FROM student
WHERE borndate=NULL;

SELECT studentname FROM student
WHERE borndate IS NULL;

#查询出生日期填写的同学
SELECT studentname FROM student
WHERE borndate IS NOT NULL;

#区别空字符串与null
SELECT * FROM student;
#查询家庭住址没有写的同学
SELECT studentname FROM student WHERE address='' OR address IS NULL;

#连接查询
#内连接 inner join
#外连接 outer join ：左外连接 left join 右外连接 right join
#自连接
#等值连接  非等值连接

#自连接
#等值连接 非等值连接

#查询参加了考试的同学信息（学号、学生姓名、科目编号、分数）
SELECT * FROM student;
SELECT * FROM result;

#思路：1.分析需求，确定查询的列来源于两个表student,result，连接查询
#2.确定使用哪一种连接查询？ ---内连接
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno;

#等值连接
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student s, result r
WHERE s.studentno=r.studentno;

#左连接
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student s
LEFT JOIN result r
ON s.studentno=r.studentno;

#右连接
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student s
RIGHT JOIN result r
ON s.studentno=r.studentno;

#查询出了所有同学，不考试的也查出来
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON r.studentno=s.studentno;

#查一下缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON r.studentno=s.studentno
WHERE studentresult IS NULL;

#非等值连接 左表行数m * 右表行数n 笛卡尔积

#思考题：查询参加了考试的同学信息（学号、学生姓名、科目名称、科目成绩）
SELECT * FROM student;
SELECT * FROM `subject`;
SELECT * FROM result;
SELECT s.studentno 学号,s.studentname 学生姓名,su.subjectname 科目名称,r.studentresult 科目成绩
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` su
ON r.subjectno=su.subjectno;

#自连接

#上次作业问题
SELECT subjectno,classhour,classhour/10 AS '均课时/10' FROM `subject`;
View Code

 7.使用DQL查询数据（三）

#连接查询（自连接）
USE myschool;
CREATE TABLE IF NOT EXISTS category(
  categoryid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  pid INT(10) NOT NULL,
  categoryName VARCHAR(50) NOT NULL,
  PRIMARY KEY(categoryId)
);

INSERT INTO category
VALUES(2,1,"美术设计"),
(3,1,"软件开发"),
(4,3,"数据库基础"),
(5,2,"PS基础"),
(6,2,"色彩搭配"),
(7,3,"PHP基础"),
(8,3,"Java基础");

SELECT * FROM category;

#编写SQL语句，将栏目的父子关系呈现出来，（父栏目名称、子栏目名称）
#父栏目     子栏目
#美术设计   PS基础
#美术设计   色彩搭配
#软件开发   数据库基础
#软件开发   PHP基础
#软件开发   Java基础
#把category表看做两张一模一样的表，然后将这两张表连接查询（自连接）

SELECT a.categoryname AS '父栏目', b.categoryname AS '子栏目'
FROM category AS a,category AS b
WHERE a.categoryid=b.pid
ORDER BY a.categoryname;

#查询参加过考试的同学信息（学号 姓名 科目号 成绩）
USE school;
SELECT * FROM student;
SELECT * FROM result;
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno;

#查询参加过考试的同学信息（学号 姓名 科目名称 成绩）
SELECT * FROM student;
SELECT * FROM result;
SELECT * FROM `subject`;
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno;

#查询学员及所属的年级（学号 学生姓名 年级名） 1 张三 大一
SELECT * FROM student;
SELECT * FROM grade;
SELECT studentno 学号,studentname 学生姓名,gradename 年级名
FROM student s
INNER JOIN grade g
ON s.gradeid=g.gradeid;
#查询科目及所属的年级（科目名称 年级名称） java 大一
SELECT * FROM `subject`;
SELECT * FROM grade;
SELECT subjectname 科目名称,gradename 年级名称
FROM `subject` sub
INNER JOIN grade g
ON sub.gradeid=g.gradeid;

#查询《数据库结构-1》的所有考试结果（学号 学生姓名 科目名称 成绩），按成绩降序排列
SELECT s.studentno 学号,studentname 学生姓名,subjectname 科目名称,studentresult 成绩
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY studentresult DESC, s.studentno; #默认ASC 升序
/*
1.排序要用order by + 排序依据
2.注意order by 在select语句中的顺序
3.默认ASC，降序显示写DESC
4.可以按多个列排序，多个排序条件用逗号分开
5.常见错误：ORDER BY studentresult, s.studentno DESC 正确 成绩升序 学号降序
*/
#分页必须必会用到 -- （用户体验、网络传输、查询压力）
#查询《数据库结构-1》的所有考试结果（学号 学生姓名 科目名称 成绩），按成绩降序排列
#每页显示5条记录出来
SELECT s.studentno 学号,studentname 学生姓名,subjectname 科目名称,studentresult 成绩
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY studentresult DESC
#limit 0,5; #等同于 limit 5 offset 0;
#limit 5 offset 0;
#limit 0,5; #第一页 从哪条记录开始（起始行）(pageno-1)*pagesize 要显示几条pagesize
#limit 5,5; #第二页
#LIMIT 10,5; #第三页
LIMIT 15,5; #第四页
#limit (pageno-1)*pagesize,pagesize  （当前页码-1）*页容量  页容量

#数据库不同 分页的语句不一样

#查询《JAVA第一学年》课程成绩前10名且分数大于80的学生信息（学号、姓名、课程名、分数）
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON sub.subjectno=r.subjectno
WHERE subjectname='JAVA第一学年' AND studentresult>80
ORDER BY studentresult DESC
LIMIT 0,10;
View Code

 

 8.使用DQL查询数据（四）

#作业 按照不同的课程分组，分别算出其平均分，最高分和最低分，对于低于平均分60分的不予显示
SELECT subjectname,AVG(studentresult) 平均分,MAX(studentresult) 最高分,MIN(studentresult) 最低分 
FROM result r INNER JOIN `subject` sub ON r.subjectno=sub.subjectno
GROUP BY sub.subjectno
HAVING 平均分>=60;

#查询《数据库结构-1》的所有考试结果（学号、科目编号、成绩），并按成绩降序排列
#方法一：使用连接查询
SELECT * FROM result;
SELECT * FROM `subject`;
SELECT studentno,r.subjectno,studentresult
FROM result r
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY studentresult DESC;
#方法二：使用子查询
SELECT studentno,subjectno,studentresult
FROM result
#WHERE subjectno = (SELECT subjectno FROM `subject` WHERE subjectname='数据库结构-1')
WHERE subjectno IN (SELECT subjectno FROM `subject` WHERE subjectname='数据库结构-1')
ORDER BY studentresult DESC;

#查询课程为《高等数学-2》且分数不小于80分的学生的学号和姓名
#方法一：使用连接查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno 
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE sub.subjectname='高等数学-2'
AND r.studentresult>=80;
#方法二：使用连接查询+子查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
AND r.subjectno=(SELECT subjectno FROM `subject` WHERE subjectname='高等数学-2');
#方法三：使用子查询
SELECT studentno,studentname FROM student WHERE studentno IN (1001,1002,1003,1004);
SELECT studentno FROM result WHERE studentresult>=80; #1001 1002 1003 1004
SELECT subjectno FROM `subject` WHERE subjectname='高等数学-2'; #2

#分步写简单SQL语句，然后将其嵌套起来
SELECT studentno,studentname
FROM student s
WHERE studentno IN (
SELECT studentno FROM result WHERE studentresult>=80 AND subjectno IN (
SELECT subjectno FROM `subject` WHERE subjectname='高等数学-2'));

#查询《C语言-1》的前5名学生的成绩信息：学号、姓名、分数
#方法一：内连接
SELECT s.studentno,studentname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON sub.subjectno=r.subjectno
WHERE sub.subjectname='C语言-1'
ORDER BY studentresult DESC
LIMIT 0,5;
#方法二：内连接+子查询
SELECT s.studentno,studentname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
WHERE r.subjectno IN (
SELECT subjectno FROM `subject` WHERE subjectname='C语言-1')
ORDER BY studentresult DESC
LIMIT 0,5;

#使用子查询实现：查询郭靖同学所在的年级名称
SELECT gradename FROM grade WHERE gradeid=(SELECT gradeid FROM student WHERE studentname='郭靖');

#数学函数
SELECT ABS(-8); #绝对值
SELECT CEILING(9.8); #大于等于我的最小的整数
SELECT CEILING(9); 
SELECT FLOOR(9.8); #小于等于我的最大的整数
SELECT RAND(); #返回一个0-1之间的随机数
SELECT RAND(10); #以某个数最为种子，返回固定的随机数
SELECT SIGN(1000);
SELECT SIGN(0);
SELECT SIGN(-1000); #符号函数，正数1 负数-1 0返回0

#字符串函数
SELECT CHAR_LENGTH('好好学习数据库'); #返回字符串中包含的字符数
SELECT CONCAT('我','爱','你','课工厂'); #合并字符串
SELECT INSERT('我爱你课工厂',1,3,'很爱'); #替换字符串，从某个位置开始，替换某个长度
SELECT INSERT('我爱你课工厂',10,3,'很爱'); #如果起始位置超过了字符串长度，则返回源字符串
SELECT LOWER('I LOVE YOU'); #变小写
SELECT UPPER('i love you'); #变大写
SELECT LEFT('课工厂欢迎你',3);
SELECT RIGHT('课工厂欢迎你',3);
SELECT REPLACE('课工厂欢迎你','你好','你','你们'); #替换字符串
SELECT SUBSTR('课工厂欢迎你',1,3); #截取,从哪个位置开始截取，截取多长

#日期和时间函数
#获得当前日期
SELECT CURRENT_DATE();
SELECT CURDATE();
#获取当前日期和时间
SELECT NOW();
SELECT LOCALTIME();
SELECT SYSDATE();
#分别获取日期中的某个部分
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

#系统信息
SELECT VERSION();
SELECT USER();

#查询姓李的同学，改成历
SELECT REPLACE(studentname,'李','历') FROM student WHERE studentname LIKE '李%';

#聚合函数
SELECT COUNT(studentname) FROM student; #count()非空值的计数
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;

SELECT SUM(studentresult) AS 总和 FROM result;
SELECT AVG(studentresult) AS 平均分 FROM result;
SELECT MAX(studentresult) AS 最高分 FROM result;
SELECT MIN(studentresult) AS 最低分 FROM result;

SELECT * FROM result;

#查询不同课程的平均分，最高分，最低分
#前提：根据不同科目进行分组
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分 
FROM result r INNER JOIN `subject` sub ON r.subjectno=sub.subjectno
GROUP BY sub.subjectno
HAVING 平均分>80
ORDER BY 平均分 DESC
LIMIT 0,4;
View Code

 

 9.使用MySQL事务和索引

 

#使用事务模拟实现转账
USE shop;

SHOW TABLES;

CREATE TABLE IF NOT EXISTS account(
  id INT(4) PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  cash DECIMAL(9,2) NOT NULL
);

DELETE FROM account;

INSERT INTO account(`name`,cash)
VALUES('A',2000),('B',10000);

SELECT * FROM account;

SET autocommit = 0;

START TRANSACTION;
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
#commit;
ROLLBACK;
SET autocommit = 1;

SELECT * FROM account;

#添加索引方式一：创建表，声明列属性时添加上
CREATE TABLE IF NOT EXISTS test1(
  id INT(4) PRIMARY KEY,
  testno VARCHAR(10) UNIQUE,
  c VARCHAR(50),
  d VARCHAR(20),
  e TEXT,
  INDEX `index_c`(c,d),
  FULLTEXT(e)
)ENGINE=MYISAM;

#添加索引方式二：创建表，将所有列都声明完毕后，再添加索引
CREATE TABLE IF NOT EXISTS test2(
  id INT(4),
  testno VARCHAR(10),
  c VARCHAR(50),
  d VARCHAR(20),
  e TEXT,
  PRIMARY KEY(id),
  UNIQUE KEY(testno),
  INDEX `index_c`(c,d),
  FULLTEXT(e)
)ENGINE=MYISAM;

#添加索引方式三：先创建表，创建表完毕后，修改表添加索引
CREATE TABLE IF NOT EXISTS test3(
  id INT(4),
  testno VARCHAR(10),
  c VARCHAR(50),
  d VARCHAR(20),
  e TEXT
)ENGINE=MYISAM;

ALTER TABLE test3 ADD PRIMARY KEY(id);
ALTER TABLE test3 ADD UNIQUE KEY(testno);
ALTER TABLE test3 ADD INDEX(c,d);
ALTER TABLE test3 ADD FULLTEXT(e);

#全文索引的使用
USE shop;
DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `StudentNo` INT(4) NOT NULL COMMENT '学号',
  `LoginPwd` VARCHAR(20) DEFAULT NULL,
  `StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
  `Sex` TINYINT(1) DEFAULT NULL COMMENT '性别，取值0或1',
  `GradeId` INT(11) DEFAULT NULL COMMENT '年级编号',
  `Phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空，即可选输入',
  `Address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空，即可选输入',
  `BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',
  `Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号，允许为空，即可选输入',
  `IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
  PRIMARY KEY (`StudentNo`),
  UNIQUE KEY `IdentityCard` (`IdentityCard`),
  KEY `Email` (`Email`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;
 
INSERT  INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`) VALUES (1000,'111111','郭靖',1,1,'13500000001','北京海淀区中关村大街1号','1986-12-11 00:00:00','test1@bdqn.cn','450323198612111234'),(1001,'123456','李文才',1,2,'13500000002','河南洛阳','1981-12-31 00:00:00','test1@bdqn.cn','450323198112311234'),(1002,'111111','李斯文',1,1,'13500000003','天津市和平区','1986-11-30 00:00:00','test1@bdqn.cn','450323198611301234'),(1003,'123456','武松',1,3,'13500000004','上海卢湾区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612314234'),(1004,'123456','张三',1,4,'13500000005','北京市通州','1989-12-31 00:00:00','test1@bdqn.cn','450323198612311244'),(1005,'123456','张秋丽 ',2,1,'13500000006','广西桂林市灵川','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311214'),(1006,'123456','肖梅',2,4,'13500000007','地址不详','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311134'),(1007,'111111','欧阳峻峰',1,1,'13500000008','北京东城区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311133'),(1008,'111111','梅超风',1,1,'13500000009','河南洛阳','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311221'),(1009,'123456','刘毅',1,2,'13500000011','安徽','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311231'),(1010,'111111','大凡',1,1,'13500000012','河南洛阳','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311044'),(1011,'111111','奥丹斯',1,1,'13500000013','北京海淀区中关村大街*号','1984-12-31 00:00:00','test1@bdqn.cn','450323198412311234'),(1012,'123456','多伦',2,3,'13500000014','广西南宁中央大街','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311334'),(1013,'123456','李梅',2,1,'13500000015','上海卢湾区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311534'),(1014,'123456','张得',2,4,'13500000016','北京海淀区中关村大街*号','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311264'),(1015,'123456','李东方',1,4,'13500000017','广西桂林市灵川','1976-12-31 00:00:00','test1@bdqn.cn','450323197612311234'),(1016,'111111','刘奋斗',1,1,'13500000018','上海卢湾区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311251'),(1017,'123456','可可',2,3,'13500000019','北京长安街1号','1981-09-10 00:00:00','test1@bdqn.cn','450323198109108311'),(10066,'','Tom',1,1,'13500000000','','0000-00-00 00:00:00','email@22.com','33123123123123123');

SELECT * FROM student;

EXPLAIN SELECT * FROM student WHERE studentno='1000';

ALTER TABLE student ENGINE=MYISAM;
ALTER TABLE student ADD FULLTEXT(studentname);

EXPLAIN SELECT * FROM student WHERE studentname LIKE '李%';

INSERT  INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`) 
VALUES 
(3333,'111111','love you',1,1,'13500000001','北京海淀区中关村大街1号','1986-12-11 00:00:00','test1@bdqn.cn','450323198612000234'),
(4444,'123456','you love',1,2,'13500000002','河南洛阳','1981-12-31 00:00:00','test1@bdqn.cn','450323198100011234');

SELECT * FROM student WHERE MATCH(studentname) AGAINST('love');
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('love');

#删除索引
DROP INDEX testno ON test3;
ALTER TABLE test3 DROP INDEX c;
ALTER TABLE test3 DROP INDEX e;
#删除主键索引
ALTER TABLE test3 DROP PRIMARY KEY;

#显示索引信息
SHOW INDEX FROM student;
View Code

 

 10.MsSQL数据库恢复和备份

 

 

#数据恢复和备份
#1 mysqldump备份
#备份整个数据库
#mysqldump -u root -p school > /root/school.sql
#备份特定的表
#mysqldump -u root -p school grade student > /root/school2.sql
#加参数
#mysqldump -u root -p --skip-add-drop-table school grade student > /root/school3.sql
#查看mysqldump的可选参数
#mysqldump --help|more 
#mysqldump -u root -p --skip-add-drop-table -c school grade student > /root/school4.sql

#source命令恢复
#mysql> create database if not exists test3 character set utf8;
#mysql> use test3;
#mysql> source /root/school.sql

#mysql命令恢复
#mysql -uroot -p test3</root/school.sql

#2 使用SQLyog工具来备份和恢复
#打钩的选项，看清楚

#3 使用SQL命令方式来备份和恢复
# https://blog.csdn.net/qq_31518899/article/details/75662090
SHOW VARIABLES LIKE '%secure%';
#将school数据库student表中的学号、姓名两列备份出去
USE school;

#注意：备份出去的文件不可以提前存在
SELECT studentno,studentname INTO OUTFILE '/root/student.sql' FROM student;
SELECT studentno,studentname INTO OUTFILE '/var/lib/mysql-files/student.sql' FROM student;

#将备份出去的数据恢复到test数据库的stutab表中来
USE test3;
CREATE TABLE stutab(
  id INT(4),
  sname VARCHAR(20)
);
LOAD DATA INFILE '/var/lib/mysql-files/student.sql' INTO TABLE stutab(id,sname);
SELECT * FROM stutab;
#PS：
#myisam frm MYD MYI 三个文件
#innodb frm ib_data1 ib_logfile文件等

#整体回顾
#mysql数据库环境搭建（环境变量 目录）
#mysql命令行使用（开启服务、连接数据库、恢复和备份、SQL语句... ...）
#DDL（create drop）
#DML（insert update delete）
#DQL（select where join group having order limit... ...）
#事务 索引
#数据恢复和备份

#作业：备份MySchool数据库的subject课程表，保存为subject.sql脚本文件
#要求：在每个INSERT语句的列上加上字段名
#mysqldump -u root -p -c school subject > /root/subject.sql
View Code

 

转载于:https://www.cnblogs.com/littlelazy/p/10136412.html

weixin_30687811
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
使用DQL命令查询数据(一)
zhoutao719的博客
08-27 372
使用DQL命令查询数据(一) DQL语言 DQL（Data Query Language）称为数据查询语言 作用： 查询数据库数据，如SELECT语句 简单的单表查询或多表的复杂查询和嵌套查询 数据库语言中最核心、最重要的语句 使用频率最高的语句 SELECT语法 SELECT [ALL | DISTINCT] { * | table.* | [ table.field1 [ as alias1] [, table.field2 [as alias2]][, …]] } FROM table
删除username的索引
weixin_34358092的博客
02-21 110
-- 删除index_name 索引 drop index index_name on user; show index from user \G; -- 创建新索引列组成，index_pinyin为复合索引名称 create index index_pinyin on user(username,pinyin); -- 这里省略主键索引 show index from user \G...
【SQL Server学习笔记】5：使用INSERT语句插入数据
LauZyHou的笔记
09-06 1万+
使用SQL语句的意义是，要开发的DBAS应用程序不能像人一样通过SSMS工具来操作数据，SQL语句是应用程序和数据库通信的桥梁。而且相比手动图形化操作，SQL语句更加方便和强大。SQL(结构化查询语言)的组成①DML(数据操作语言) 插入、删除和修改数据库中的数据INSERT、UPDATE、DELETE等②DCL(数据控制语言) 用来控制存储许可，存取权限等GRANT、REVOKE等③DQL(数
大数据——使用DQL命令查询数据（一）
蜂蜜柚子加苦茶
08-19 602
使用DQL命令查询数据（一）DQL语言SELECT语法（一）指定查询字段查询表中所有的数据列结果，采用“*”符号可指定查询的结果数据列AS子句AS子句作用AS子句用法DISTINCT关键字的使用作用使用表达式的列（1）使用表达式的列（2）在SQL语句中使用表达式SELECT语法（二）where条件语句逻辑操作符比较操作符BETWEEN AND范围查询LIKE模糊查询使用IN进行范围查询NULL空值条件查询SELECT语法（三）连接查询（多表查询）内连接查询（1）内连接查询（2）外连接不同的SQL JOIN对
1.mysql练习（基础理解）
computer408的博客
02-19 814
1.创建表，初始数据 #创建年级表---1 drop table if exists grade; create table grade( gradeid int auto_increment primary key comment '年级id', gradename varchar(10) not null comment '年级名称' ) comment='年级表'; insert into grade(gradename) values('s1');#一次插一条记录`grade` in...
MySql 8.0.37
最新发布
05-30
MySql 8.0.37
mysql8.0升级版本
12-12
MySQL 8.0 升级版本 在本文中，我们将详细介绍 MySQL 8.0 升级版本的步骤和注意事项。MySQL 是一个流行的开源关系数据库管理系统，广泛应用于各种Web 应用程序中。升级 MySQL 版本可以提高数据库性能、修复 bugs 和...
深入浅出MySQL全文
06-29
深入浅出MySQL全文 MySQL 是一种关系型数据库管理系统，广泛应用于各种行业和领域。本资源将从基础篇开始，详细讲解 MySQL 的安装、配置、启动和关闭服务、SQL 基础等知识点。 MySQL 的安装与配置 MySQL 的安装是...
school.sql
05-28
MySQL 练习题部署的环境 SQL文件，下载时请查看我的相关文章，放在文章里篇幅太长了，所以就放在CSDN下载了
号源系统oracle数据库资源自动编号说明
05-07
号源系统 oracle 数据库 资源 自动编号 说明
.分别查询学生表和学生修课表中的全部数据。
12-14
标题 .分别查询学生表和学生修课表中的全部数据。 –1.分别查询学生表和学生修课表中的全部数据。 SELECT * FROM student–查询学生表 SELECT * FROM course–查询课程表 –2.查询成绩在70到80分之间的学生的学号、课程号和成绩。 SELECT Sno 学号,Cno 课程号,Grade 成绩 FROM SC WHERE Grade BETWEEN 70 AND 80 –3．查询C01号课程成绩最高的分数 SELECT TOP 1 Grade FROM SC WHERE Cno=‘C01’ ORDER BY Grade DESC–降序DESC，升序ASC –
MySQL 精简50题练习 school_score.sql
11-14
对MySQL数据分析练习的精简小结，包含：一、数据表信息；二、创建数据库、表、填充信息；三、精简50题 及 参考答案。此外，加入了实际项目中部分的小结。非常适合熟悉MySQL的小伙伴。同时，不需要积分即可下载。是不是很给力！
MySQL5.5.62
11-06
MySQL 5.5.62 是 MySQL 数据库管理系统的一个重要版本，主要针对 Windows 操作系统提供了32位和64位的安装包。MySQL 是一个广泛使用的开源关系型数据库管理系统（RDBMS），由 Oracle 公司维护。它以其高效、稳定和...
mysql 5.7 perl
08-08
离线安装mysql文件包，包含依赖，以及peal全套安装包
北大青鸟MySQL宠物店项目_EpetShop 宠物商店管理系统 北大青鸟阶段项目 mysql的数据库(Beida Jade Bird phases of the project - 下载 - 搜珍网...
weixin_39944944的博客
02-05 595
压缩包 : f301a9e770d4dca47145d0b58494bae.zip 列表EpetShop/EpetShop/.classpathEpetShop/.projectEpetShop/.settings/EpetShop/.settings/org.eclipse.jdt.core.prefsEpetShop/bin/EpetShop/bin/cn/EpetShop/bin/cn/bd...
数据库(增删改查、mysql建课程表)
热门推荐
sxb956347789的博客
10-31 1万+
Oracel 甲骨文公司  SQLSever 微软公司   DB2  IBM公司    MySQL(sun公司，现在是甲骨文)开源的 数据：客观事物的符号表示，包括数值型(1、2、3...)和多媒体(非数值)数据(语音、图片、视频等)。 数据库：长期存储在计算机内的有组织的、可共享的相关数据的集合。 数据库管理系统：维护管理数据集合的软件系统。 数据管理技术：对数据的收集、整理、组织、存储
03.MySQL数据库-new.pptx
06-07
03.MySQL数据库-new.pptx是一份关于MySQL数据库系统的文件，展示了数据库的基本概念和结构。数据库是用来存储和管理数据的仓库，具有存储大量数据、方便检索、保持数据一致性和完整性、安全可共享以及通过组合分析...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值