mysql基本语法

-- 建库
create DATABASE db_book;
use db_book;
-- 建表
CREATE TABLE t_bookType(
    id int primary key auto_increment,
    bookTypeName varchar(20),
    bookTypeDesc varchar(200)
);
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;
-- 查看表ddl（建表语句）
show create table t_bookType;
-- 重命名表
alter table t_book rename t_book2;

-- 建表
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 * FROM t_student;
SELECT * FROM t_student WHERE id=1;
SELECT * FROM t_student WHERE age>22;
-- in 相当于集合吧，别和between混淆
SELECT * FROM t_student WHERE age IN (21,22,23);
SELECT * FROM t_student WHERE age NOT IN (21,23);
-- [21,24]
SELECT * FROM t_student WHERE age BETWEEN 21 AND 24;
SELECT * FROM t_student WHERE age NOT BETWEEN 21 AND 24;
-- 模糊查询
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 gradeName=‘一年级‘ AND age=23;
-- 并集
SELECT * FROM t_student WHERE gradeName=‘一年级‘ OR age=23;
-- DISTINCT去重
SELECT DISTINCT gradeName FROM t_student;
-- 升序
SELECT * FROM t_student ORDER BY age ASC;
-- 降序
SELECT * FROM t_student ORDER BY age DESC;
-- 分组查询
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;
-- 分页查询(index,size)
SELECT * FROM t_student LIMIT 2,5;

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‘);

-- 聚合查询，还是分组聚合比较多
SELECT COUNT(*) FROM t_grade;
SELECT stuName,COUNT(*) FROM t_grade GROUP BY stuName;
SELECT stuName,SUM(score) FROM t_grade GROUP BY stuName;
SELECT stuName,AVG(score) FROM t_grade WHERE stuName="张三";
SELECT stuName,AVG(score) FROM t_grade GROUP BY stuName;

USE `db_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;
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);
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;

insert  into `t_booktype`(`id`,`bookTypeName`) values (1,‘计算机类‘),(2,‘文学类‘),(3,‘教育类‘);

-- 笛卡尔积
SELECT * FROM t_book,t_bookType;
SELECT * FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;
SELECT bookName,author,bookTypeName FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;
SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id;
-- 返回左表所有记录，哪怕右表为空
SELECT * FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;
-- 返回右表所有记录，哪怕左表为空
SELECT * FROM t_book RIGHT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;
SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb LEFT 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;

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 * FROM t_book WHERE booktypeId IN (SELECT id FROM t_booktype);
SELECT * FROM t_book WHERE booktypeId NOT IN (SELECT id FROM t_booktype);
SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);
SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);
SELECT * FROM t_book WHERE NOT EXISTS (SELECT * FROM t_booktype);
SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);
SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);