索引演示表一

  1. DROP TABLE IF EXISTS `actor`;
  2. CREATE TABLE `actor` (
  3.     `id` INT ( 11 ) NOT NULL,
  4.     `name` VARCHAR ( 45 ) DEFAULT NULL,
  5.     `update_time` datetime DEFAULT NULL,
  6.     PRIMARY KEY ( `id` )
  7. ) ENGINE = INNODB DEFAULT CHARSET = utf8;
  9. INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1, 'a', NOW());
  10. INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (2, 'b', NOW());
  11. INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (3, 'c', NOW());
  14. DROP TABLE IF EXISTS `film`;
  15. CREATE TABLE `film` (
  16.     `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
  17.     `name` VARCHAR ( 10 ) DEFAULT NULL,
  18.     PRIMARY KEY ( `id` ),
  19.     KEY `idx_name` ( `name` )
  20. ) ENGINE = INNODB DEFAULT CHARSET = utf8;
  22. INSERT INTO `film` (`id`, `name`) VALUES (3, 'film0');
  23. INSERT INTO `film` (`id`, `name`) VALUES (1, 'film1');
  24. INSERT INTO `film` (`id`, `name`) VALUES (2, 'film2');
  27. DROP TABLE IF EXISTS `film_actor`;
  28. CREATE TABLE `film_actor` (
  29.     `id` INT ( 11 ) NOT NULL,
  30.     `film_id` INT ( 11 ) NOT NULL,
  31.     `actor_id` INT ( 11 ) NOT NULL,
  32.     `remark` VARCHAR ( 255 ) DEFAULT NULL,
  33.     PRIMARY KEY ( `id` ),
  34.     KEY `idx_film_actor_id` ( `film_id`, `actor_id` )
  35. ) ENGINE = INNODB DEFAULT CHARSET = utf8;
  36. INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`, `remark`) VALUES (1, 1, 1, NULL);
  37. INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`, `remark`) VALUES (2, 1, 2, NULL);
  38. INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`, `remark`) VALUES (3, 2, 1, NULL);

索引演示表二

  1. DROP TABLE IF EXISTS `employees`;
  2. CREATE TABLE `employees` (
  3.     `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
  4.     `name` VARCHAR ( 24 ) NOT NULL DEFAULT '' COMMENT '姓名',
  5.     `age` INT ( 11 ) NOT NULL DEFAULT '0' COMMENT '年龄',
  6.     `position` VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '职位',
  7.     `hire_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  8.     PRIMARY KEY ( `id` ),
  9.     KEY `idx_name_age_position` ( `name`, `age`, `position` ) USING BTREE 
  10. ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COMMENT = '员工记录表';
  12. INSERT INTO `employees` (`id`, `name`, `age`, `position`, `hire_time`) VALUES (1, 'LiLei', 22, 'manager', NOW());
  13. INSERT INTO `employees` (`id`, `name`, `age`, `position`, `hire_time`) VALUES (2, 'HanMeimei', 23, 'dev', NOW());
  14. INSERT INTO `employees` (`id`, `name`, `age`, `position`, `hire_time`) VALUES (3, 'Lucy', 23, 'dev', NOW());
  17. DROP PROCEDURE IF EXISTS insert_emp;
  18. delimiter $$
  19. CREATE PROCEDURE insert_emp () 
  20. BEGIN
  21.     DECLARE i INT;
  22.     SET i = 1;
  23.     WHILE(i <= 100000) DO
  24.             INSERT INTO employees (`name`, `age`, `position`) VALUES (CONCAT( 'zhuge', i ), i, 'dev');
  25.             SET i = i + 1;
  26.     END WHILE;
  27. END $$
  28. delimiter ;
  29. CALL insert_emp ();

连接查询演示表

  1. DROP TABLE IF EXISTS `t1`;
  2. CREATE TABLE `t1` (
  3.     `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
  4.     `a` INT ( 11 ) DEFAULT NULL,
  5.     `b` INT ( 11 ) DEFAULT NULL,
  6.     PRIMARY KEY ( `id` ),
  7.     KEY `idx_a` ( `a` ) 
  8. ) ENGINE = INNODB DEFAULT CHARSET = utf8;
  11. DROP TABLE IF EXISTS `t2`;
  12. CREATE TABLE t2 LIKE t1;
  15. DROP PROCEDURE IF EXISTS insert_t1;
  16. delimiter $$
  17. CREATE PROCEDURE insert_t1 () 
  18. BEGIN
  19.     DECLARE i INT;
  20.     SET i = 1;
  21.     WHILE(i <= 10000) DO
  22.             INSERT INTO t1 (`a`, `b`) VALUES (i, i);
  23.             SET i = i + 1;
  24.     END WHILE;
  25. END $$
  26. delimiter ;
  27. CALL insert_t1 ();
  30. DROP PROCEDURE IF EXISTS insert_t2;
  31. delimiter $$
  32. CREATE PROCEDURE insert_t2 () 
  33. BEGIN
  34.     DECLARE i INT;
  35.     SET i = 1;
  36.     WHILE(i <= 100) DO
  37.             INSERT INTO t2 (`a`, `b`) VALUES (i, i);
  38.             SET i = i + 1;
  39.     END WHILE;
  40. END $$
  41. delimiter ;
  42. CALL insert_t2 ();

锁演示表

  1. DROP TABLE IF EXISTS `mylock`;
  2. CREATE TABLE `mylock` (
  3.   `id` int(11) NOT NULL AUTO_INCREMENT,
  4.   `NAME` varchar(20) DEFAULT NULL,
  5.   PRIMARY KEY (`id`)
  6. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  8. INSERT INTO `mylock` (`id`, `NAME`) VALUES (1, 'a');
  9. INSERT INTO `mylock` (`id`, `NAME`) VALUES (2, 'b');
  10. INSERT INTO `mylock` (`id`, `NAME`) VALUES (3, 'c');
  11. INSERT INTO `mylock` (`id`, `NAME`) VALUES (4, 'd');

事务演示表

  1. DROP TABLE IF EXISTS `account`;
  2. CREATE TABLE `account` (
  3.   `id` int(11) NOT NULL AUTO_INCREMENT,
  4.   `name` varchar(255) DEFAULT NULL,
  5.   `balance` int(11) DEFAULT NULL,
  6.   PRIMARY KEY (`id`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  9. INSERT INTO `account` (`id`, `name`, `balance`) VALUES (1, 'lilei', 450);
  10. INSERT INTO `account` (`id`, `name`, `balance`) VALUES (2, 'hanmei', 16000);
  11. INSERT INTO `account` (`id`, `name`, `balance`) VALUES (3, 'lucy', 2400);