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/
http://www.htsjk.com/Mysql/42302.html
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...
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。