mysql基本语法

Z时代
2024-01-10
分类：综合

database

建库建表Demo

-- 建库
createDATABASE db_book;
use db_book;
-- 建表
CREATETABLE t_bookType(
    id intprimarykey auto_increment,
    bookTypeName varchar(20),
    bookTypeDesc varchar(200)
);
CREATETABLE t_book(
    id intprimarykey auto_increment,
    bookName varchar(20),
    author varchar(10),
    price decimal(6,2),
    bookTypeId int,
constraint `fk` foreignkey (`bookTypeId`) references `t_bookType`(`id`)
);
-- 查看表结构
desc t_bookType;
-- 查看表ddl（建表语句）
show createtable t_bookType;
-- 重命名表
altertable t_book rename t_book2;

View Code

建立单表

-- 建表
createtable `t_student` (
    `id` double ,
    `stuName` varchar (60),
    `age` double ,
    `sex` varchar (30),
    `gradeName` varchar (60)
); 
-- 插入记录
insertinto `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values("1","张一","23","男","一年级");
insertinto `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values("2","张二","25","男","二年级");
insertinto `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values("3","张三","23","男","一年级");
insertinto `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values("4","张四","22","男","三年级");
insertinto `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values("5","张五","21","女","一年级");
insertinto `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values("6","李一","26","女","二年级");
insertinto `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values("7","李二","20","男","三年级");
insertinto `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values("8","李三","21","女","二年级");
insertinto `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values("9","李四","22","男","一年级");
insertinto `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values("10","李五","25","男","二年级");
insertinto `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values("11","小黑","21",NULL,"二年级");
insertinto `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values("12","小白","23","男","二年级");
insertinto `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 NOTIN (21,23);
-- [21,24]
SELECT*FROM t_student WHERE age BETWEEN21AND24;
SELECT*FROM t_student WHERE age NOTBETWEEN21AND24;
-- 模糊查询
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去重
SELECTDISTINCT gradeName FROM t_student;
-- 升序
SELECT*FROM t_student ORDERBY age ASC;
-- 降序
SELECT*FROM t_student ORDERBY age DESC;
-- 分组查询
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUPBY gradeName;
-- 分页查询(index,size)
SELECT*FROM t_student LIMIT 2,5;

View Code

再建单表

createtable `t_grade` (
    `id` int ,
    `stuName` varchar (60),
    `course` varchar (60),
    `score` int
); 
insertinto `t_grade` (`id`, `stuName`, `course`, `score`) values("1","张三","语文","91");
insertinto `t_grade` (`id`, `stuName`, `course`, `score`) values("2","张三","数学","90");
insertinto `t_grade` (`id`, `stuName`, `course`, `score`) values("3","张三","英语","87");
insertinto `t_grade` (`id`, `stuName`, `course`, `score`) values("4","李四","语文","79");
insertinto `t_grade` (`id`, `stuName`, `course`, `score`) values("5","李四","数学","95");
insertinto `t_grade` (`id`, `stuName`, `course`, `score`) values("6","李四","英语","80");
insertinto `t_grade` (`id`, `stuName`, `course`, `score`) values("7","王五","语文","77");
insertinto `t_grade` (`id`, `stuName`, `course`, `score`) values("8","王五","数学","81");
insertinto `t_grade` (`id`, `stuName`, `course`, `score`) values("9","王五","英语","89");

View Code

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

View Code

建立无外键的俩表

USE `db_book`;
DROPTABLEIFEXISTS `t_book`;
CREATETABLE `t_book` (
  `id` int(11) NOTNULL AUTO_INCREMENT,
  `bookName` varchar(20) DEFAULTNULL,
  `price` decimal(6,2) DEFAULTNULL,
  `author` varchar(20) DEFAULTNULL,
  `bookTypeId` int(11) DEFAULTNULL,
PRIMARYKEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5DEFAULT CHARSET=utf8;
insertinto `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);
DROPTABLEIFEXISTS `t_booktype`;
CREATETABLE `t_booktype` (
  `id` int(11) NOTNULL AUTO_INCREMENT,
  `bookTypeName` varchar(20) DEFAULTNULL,
PRIMARYKEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4DEFAULT CHARSET=utf8;
insertinto `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 LEFTJOIN t_bookType ON t_book.bookTypeId=t_bookType.id;
-- 返回右表所有记录，哪怕左表为空
SELECT*FROM t_book RIGHTJOIN t_bookType ON t_book.bookTypeId=t_bookType.id;
SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb LEFTJOIN 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

建表子查询

createtable `t_pricelevel` (
    `id` int ,
    `priceLevel` int ,
    `price` float ,
    `description` varchar (300)
); 
insertinto `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) values("1","1","80.00","价格贵的书");
insertinto `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) values("2","2","60.00","价格适中的书");
insertinto `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 NOTIN (SELECT id FROM t_booktype);
SELECT*FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);
SELECT*FROM t_book WHEREEXISTS (SELECT*FROM t_booktype);
SELECT*FROM t_book WHERENOTEXISTS (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/

以上是 mysql基本语法的全部内容，来源链接： utcz.com/z/532528.html

mysql基本语法

其他人也看了：