mysql基本语法

建库建表Demo

-- 建库
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;

View Code 建立单表

-- 建表
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,"二年级");

View Code 简单的单表查询

-- 查询
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;

View Code

再建单表

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

View Code

-- 聚合查询，还是分组聚合比较多
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;

View Code 建立无外键的俩表

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,"教育类");

View Code 多表查询（俩表）

-- 笛卡尔积
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;

View Code 建表子查询

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

View Code

博客使用的mysql实例均来自http://www.java1234.com/