数据库-从入门到跑路 .1 (DDL、DQL)
数据库学习笔记——从入门到跑路
第一节 数据类型
1.整数类型
了解即可，不用背
2. 浮点数类型和定点数类型
注意： M 表示：数据的总长度(不包括小数点)；
D 表示：小数位；
例如 decimal(5,2) 123.45
存入数据的时候，按四舍五入计算
3. 日期与时间类型
4.字符串类型
第二节 数据库定义操作DDL
数据库基本操作
show databases; //显示所有数据库
create database 数据库的名称; //创建数据库
drop database 数据库的名称; //删除数据库
**/创建一个表 t_bookType，
id 为 int 主键，自动增加，
booktypename varchar（20）型，
booktypedesc 为varchar（200）型
/
CREATE TABLE t_bookType(
id int primary key auto_increment,
bookTypeName varchar(20),
bookTypeDesc varchar(200)
);
**/创建一个表t_book
id为int主键，自动增加，
bookName 为 varchar(20),
author 为 varchar(10),
price 为 decimal(6,2),
bookTypeId 为 int,
constraint fk foreign key (bookTypeId) references t_bookType(id)
/
CREATE TABLE t_book(
id int primary key auto_increment,
bookName varchar(20),
author varchar(10),
price decimal(6,2),
bookTypeId int,
constraint fk foreign key (bookTypeId) references t_bookType(id)
);
查看操作
desc t_bookType; //查看表结构
show create table t_bookType; //查看表详细结构
更改操作
alter table t_book rename t_book2; //更改表名
alter table t_book change bookName bookName2 varchar(20);//更改字段（列名），并更改类型
alter table t_book add testField int first ; //增加字段，在第一个字段增加
alter table t_book add testField int after 字段2; //在字段2前加一个字段
删除操作
alter table t_book drop testField; //删除字段
drop table t_bookType; //删除表
drop table t_book;
第三节.数据库查询语句DQL
1.单表查询
库函数/*
SQLyog 企业版 - MySQL GUI v8.14
MySQL - 5.1.49-community
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
create table `t_student` (
`id` double ,
`stuName` varchar (60),
`age` double ,
`sex` varchar (30),
`gradeName` varchar (60)
);
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('1','张三','23','男','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('2','张三丰','25','男','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('3','李四','23','男','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('4','王五','22','男','三年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('5','珍妮','21','女','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('6','李娜','26','女','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('7','王峰','20','男','三年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('8','梦娜','21','女','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('9','小黑','22','男','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('10','追风','25','男','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('11','小小张三','21',NULL,'二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('12','小张三','23','男','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('13','张三锋小','24',NULL,'二年级');
常用操作
SELECT id,stuName,age,sex,gradeName FROM t_student ; //查询表中选中的的字段
SELECT stuName,id,age,sex,gradeName FROM t_student ;
SELECT * FROM t_student; //查询表中所有的字段
SELECT stuName,gradeName FROM t_student; //查询表中指定的字段
SELECT * FROM t_student WHERE id=1; //where条件查询
SELECT * FROM t_student WHERE age>22;
/IN关键字查询，（）输入元素，查询符合条件的学生信息/
SELECT * FROM t_student WHERE age IN (21,23);
SELECT * FROM t_student WHERE age NOT IN (21,23);
/* BETWEEN AND 范围查询*/
SELECT * FROM t_student WHERE age BETWEEN 21 AND 24;
SELECT * FROM t_student WHERE age NOT BETWEEN 21 AND 24;
/* like 模糊查询 %表示任意多个字符，_表示单个字符*/
SELECT * FROM t_student WHERE stuName LIKE '张三';
SELECT * FROM t_student WHERE stuName LIKE '张三%';
SELECT * FROM t_student WHERE stuName LIKE '张三__';
SELECT * FROM t_student WHERE stuName LIKE '%张三%';
/* 空值查询*/
SELECT * FROM t_student WHERE sex IS NULL;
SELECT * FROM t_student WHERE sex IS NOT NULL;
/* 多条件查询，满足多个条件的查询方式 and表示全部满足，or表示或*/
SELECT * FROM t_student WHERE gradeName='一年级' AND age=23
SELECT * FROM t_student WHERE gradeName='一年级' OR age=23
/* 去重复查询，在需要查询的字段前，加DISTINCT */
SELECT DISTINCT gradeName FROM t_student;
/* 对查询结果排序，在结尾加上 ORDER BY 属性名（以该属性排序） asc|desc */
SELECT * FROM t_student ORDER BY age ASC;
SELECT * FROM t_student ORDER BY age DESC;
/*分组查询
1，单独使用(毫无意义)；
2，与GROUP_CONCAT()函数一起使用；
3，与聚合函数一起使用；
4，与HAVING 一起使用(限制输出的结果)；
5，与WITH ROLLUP 一起使用(最后加入一个总和行)； */
/* 直接使用 /
SELECT * FROM t_student GROUP BY gradeName;
/ */
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;
/* LIMIT 分页查询 */
SELECT 字段1，字段2...FROM 表名LIMIT 初始位置，记录数；
SELECT * FROM t_student LIMIT 0,5;
SELECT * FROM t_student LIMIT 5,5;
SELECT * FROM t_student LIMIT 10,5;
2.聚合函数
库函数t_grade
/*
SQLyog 企业版 - MySQL GUI v8.14
MySQL - 5.1.49-community
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
create table `t_grade` (
`id` int ,
`stuName` varchar (60),
`course` varchar (60),
`score` int
);
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('1','张三','语文','91');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('2','张三','数学','90');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('3','张三','英语','87');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('4','李四','语文','79');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('5','李四','数学','95');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('6','李四','英语','80');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('7','王五','语文','77');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('8','王五','数学','81');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('9','王五','英语','89');
/*COUNT()函数
1.COUNT()函数用来统计记录的条数
2.与GOUPE BY 关键字一起使用；
*/
SELECT COUNT(*) FROM t_grade; //计算这个表里的所有行数
SELECT COUNT(*) AS total FROM t_grade;//as 别名，是为计数起一个别名
SELECT stuName,COUNT(*) FROM t_grade GROUP BY stuName;
/SUM()函数
1，SUM()函数是求和函数；
2，与GOUPE BY 关键字一起使用；/
SELECT stuName,SUM(score) FROM t_grade WHERE stuName="张三";
//查找名字是张三的同学的成绩总分，显示为2列
SELECT stuName,SUM(score) FROM t_grade GROUP BY stuName;
/AVG(字段名)，求平均值/
SELECT stuName,AVG(score) FROM t_grade WHERE stuName="张三";
SELECT stuName,AVG(score) FROM t_grade GROUP BY stuName;
/MAX(字段名)，求最大值最小值/
SELECT stuName,course,MAX(score) FROM t_grade WHERE stuName="张三";
SELECT stuName,MAX(score) FROM t_grade GROUP BY stuName;
SELECT stuName,course,MIN(score) FROM t_grade WHERE stuName="张三";
SELECT stuName,MIN(score) FROM t_grade GROUP BY stuName;
3.多表查询
db_book 库
/*
SQLyog 企业版 - MySQL GUI v8.14
MySQL - 5.1.49-community : Database - db_book
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`db_book` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `db_book`;
/*Table structure for table `t_book` */
DROP TABLE IF EXISTS `t_book`;
CREATE TABLE `t_book` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bookName` varchar(20) DEFAULT NULL,
`price` decimal(6,2) DEFAULT NULL,
`author` varchar(20) DEFAULT NULL,
`bookTypeId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*Data for the table `t_book` */
insert into `t_book`(`id`,`bookName`,`price`,`author`,`bookTypeId`) values (1,'Java编程思想','100.00','埃史尔',1),(2,'Java从入门到精通','80.00','李钟尉',1),(3,'三剑客','70.00','大仲马',2),(4,'生理学(第二版)','24.00','刘先国',4);
/*Table structure for table `t_booktype` */
DROP TABLE IF EXISTS `t_booktype`;
CREATE TABLE `t_booktype` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bookTypeName` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*Data for the table `t_booktype` */
insert into `t_booktype`(`id`,`bookTypeName`) values (1,'计算机类'),(2,'文学类'),(3,'教育类');
/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40111 SET [email protected]_SQL_NOTES */;
多表查询采用连接查询
把多个表的条件连接起来
分为内连接和外连接查询
内连接查询
SELECT * FROM t_book,t_bookType; //把2个表直接外连接，取笛卡尔积
SELECT * FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id; //where条件查询
SELECT bookName,author,bookTypeName FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;
//查询2个表的部分字段
SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id;
//注意：该命令中from后面加上了别名，as省略了，在select后的tb和tby都是别名
外连接查询:左连接 和 右连接
外连接可以查出某一张表的所有信息，格式如下
SELECT 属性名列表 FROM 表名1 LEFT|RIGHT JOIN 表名2 ON 表名1.属性名1=表名2.属性名2；
SELECT * FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId = t_bookType.id;
//左连接可以查询出“表名1”的所有记录，而“表名2”中，只能查询出匹配的记录；
SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb LEFT JOIN t_bookType tby ON tb.bookTypeId=tby.id;
SELECT * FROM t_book RIGHT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;
//右连接可以查询出“表名2”的所有记录，而“表名1”中，只能查询出匹配的记录
SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb RIGHT JOIN t_bookType tby ON tb.bookTypeId=tby.id;
多条件查询（一般不用）
SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id AND tb.price>70;
// 查找了tb.bookName,tb.author,tby.bookTypeName这几个字段中满足tb.bookTypeId=tby.id AND tb.price>70条件的行
3.子查询
库函数t_pricelevel
/*
SQLyog 企业版 - MySQL GUI v8.14
MySQL - 5.1.49-community
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
create table `t_pricelevel` (
`id` int ,
`priceLevel` int ,
`price` float ,
`description` varchar (300)
);
insert into `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) values('1','1','80.00','价格贵的书');
insert into `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) values('2','2','60.00','价格适中的书');
insert into `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) values('3','3','40.00','价格便宜的书');
子查询：一个查询语句的条件可能落在另一个SELECT 语句的查询结果中。
/*带in关键字的子查询*/
SELECT * FROM t_book WHERE booktypeId IN (SELECT id FROM t_booktype);
SELECT * FROM t_book WHERE booktypeId NOT IN (SELECT id FROM t_booktype);
//查询了t_booktype表中的id，作为查询t_book表的条件
/*带比较大小运算符的子查询*/
SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);
//查询t_book表中价格大于（）中的数字的行
/*Exists，如果子查询存在，则执行外部查询*/
SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);
SELECT * FROM t_book WHERE NOT EXISTS (SELECT * FROM t_booktype);
/*ANY 关键字表示满足其中任一条件；*/
SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);
/*ALL 关键字表示满足所有条件；*/
SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);
4.合并查询
SELECT id FROM t_book;
//查询t_book中所有的ID
SELECT id FROM t_booktype;
//查询t_booktype中所有的ID
SELECT id FROM t_book UNION SELECT id FROM t_booktype;
//查询t_booktype和表t_book中所有不重复的的ID
SELECT id FROM t_book UNION ALL SELECT id FROM t_booktype;
//查询t_booktype和表t_book中的的ID（包括重复）
5.表与字段的别名取法
SELECT * FROM t_book WHERE id=1;
//查询id=1的书籍信息
SELECT * FROM t_book t WHERE t.id=1;
//查询id=1的书籍信息
SELECT t.bookName FROM t_book t WHERE t.id=1;
//查询id=1的书籍name
SELECT t.bookName AS bName FROM t_book t WHERE t.id=1;
SELECT t.bookName bName FROM t_book t WHERE t.id=1;
//查询查询id=1的书籍name，并重命名为bname