# ##############创建hero表并插入数据##########
DROP TABLE IF EXISTS `hero`;
CREATE TABLE `hero` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`price` int(11) DEFAULT NULL,
`type_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
INSERT INTO `hero`
(`id`,`name`,`age`,`sex`,`price`,`type_id`)
VALUES
(1,'李白',18,'男',18888,2),
(2,'孙尚香',17,'女',6888,4),
(3,'韩信',15,'男',NULL,2),
(4,'小乔',5,'女',NULL,3),
(5,'西施',5,'女',13888,3),
(7,'张飞',1,'男',13888,5),
(8,'花木兰',1,'女',NULL,1),
(9,'虞姬',16,'女',13888,4),
(10,'凯',NULL,NULL,13888,1),
(11,'孙策',NULL,NULL,NULL,NULL),
(12,'曹操',30,'男',NULL,4),
(13,'孙膑',5,NULL,NULL,5);
# ##################创表插入数据完成##############
INSERT INTO hero VALUES(14,'杨玉环',18,'女',13888,3);
INSERT INTO hero (name,price,type_id) VALUES('马可波罗',13888,4);
INSERT INTO hero
VALUES
(16,'露娜',18,'女',NULL,2),
(17,'张良',20,'男',NULL,3);
DELETE FROM hero WHERE`name`='张良';
DELETE FROM hero ;
DELETE FROM hero WHERE 1=1 ;
DELETE FROM hero WHERE TRUE ;
UPDATE hero SET `name`='百里守约' WHERE `name`='马可波罗';
SELECT * FROM hero ;
SELECT * FROM hero WHERE sex = '男' ;
SELECT name,id,price ,sex FROM hero WHERE sex = '男';
SELECT * FROM hero WHERE `name`='百里守约';
SELECT * FROM hero WHERE name='百里守约';
SELECT * FROM hero WHERE NAME='百里守约';
SELECT * FROM hero WHERE age>5 AND age<20;
SELECT * FROM hero WHERE age BETWEEN 5 AND 20;
SELECT * FROM hero WHERE age IN (5,16,17);
SELECT MIN(age) FROM hero ;
SELECT * FROM hero WHERE age = (SELECT MIN(age) FROM hero) ;
SELECT SUM(age) FROM hero;
SELECT COUNT(*) FROM hero;
SELECT AVG(age) FROM hero WHERE sex = '男';
SELECT * FROM hero ORDER BY age;
SELECT * FROM hero ORDER BY age DESC;
SELECT * FROM hero WHERE `name` LIKE '___';
SELECT * FROM hero WHERE `name` LIKE '李%';
SELECT * FROM hero WHERE `name` LIKE '___%';
SELECT * FROM hero LIMIT 3,2 ;
# ################################新建type表 ,插入五条数据################################
DROP TABLE IF EXISTS `tpye`;
CREATE TABLE `type` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(20) DEFAULT NULL,
`route` varchar(50) DEFAULT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO
`type`(`tid`,`tname`,`route`)
VALUES
(1,'战士','上单'),
(2,'刺客','打野'),
(3,'法师','中路'),
(4,'射手','下路'),
(5,'辅助','下路');
# ############################################建表操作完成####################################
SELECT * FROM hero h, type t ;
SELECT * FROM hero h INNER JOIN type t ;
SELECT * FROM hero h, type t WHERE h.type_id = t.tid ;
SELECT * FROM hero h INNER JOIN type t ON h.type_id = t.tid ;
# #########向type表中插入一行数据,方便观察##############
INSERT INTO type VALUES(6,'新插入的','方便外连接观察');
# ##############插入完毕#######################
SELECT * FROM hero h LEFT JOIN type t ON h.type_id = t.tid;
SELECT * FROM hero h RIGHT JOIN type t ON h.type_id = t.tid;
SELECT * FROM hero h LEFT JOIN type t ON h.type_id = t.tid
UNION
SELECT * FROM hero h RIGHT JOIN type t ON h.type_id = t.tid;