欢迎投稿

今日深度:

mysql基本语法,

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/

www.htsjk.Com true http://www.htsjk.com/Mysql/42302.html NewsArticle 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...
相关文章
    暂无相关文章
评论暂时关闭