视图的创建和修改

  1. -- 视图创建
  2. create view view_student as select * from student;
  4. create view v_student_info(s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
  5. as select id,name,deptid,age,sex,height,login_date from student
  7. -- 视图查看
  8. select * from view_student;
  10. -- 结构查询
  11. desc view_student;
  13. -- 约束查询
  14. show create view view_student;
  16. -- 修改视图
  17. alter view view_student as select id,name,age from student;
  19. -- 删除视图
  20. drop view if exists view_student;

数据库引擎

  1. -- 查看所有数据库引擎
  2. show engines;
  4. -- 默认存储引擎
  5. show variables like 'default_storage_engine%';
  7. -- 临时修改存储引擎
  8. set default_storage_engine = MyISAM;

创建索引

  1. -- 创建普通索引
  2. create table tb_stu_info(
  3.   id int,
  4.   name char(45),
  5.   dept_id int,
  6.   age int,
  7.   height int,
  8.   index(height)
  9.     );
  11. -- 创建唯一索引
  12. create table tb_stu_info2(
  13.   id int,
  14.   name char(45),
  15.   dept_id int,
  16.   age int,
  17.   height int,
  18.   unique index(height)
  19.     );
  21. -- 删除索引-两种方式
  22. # drop index on
  23. ## alter table drop index
  24. drop index height on tb_stu_info;
  25. alter table tb_stu_info2 drop index height;
  27. -- 修改索引
  30. -- 创建主键索引
  31. create table `table`(
  32.   `id` int(11) not null auto_increment,
  33.   `title` char(25) not null,
  34.   primary key(`id`)
  35. );
  37. -- 创建组合索引
  39. -- 创建全文索引
  41. -- 索引是否有效验证
  42. create table studescribe (
  43.     `stu_id` int(11) not null auto_increment,
  44.   `name` varchar(50) not null,
  45.   `email` varchar(50) not null,
  46.   `phone` varchar(11) not null,
  47.   `create_date` date default null,
  48.   primary key(`stu_id`)
  49.     );
  51. insert into studescribe values('1','adin','454545@qq.com','13548789645','1968-03-26');
  52. insert into studescribe values('2','root','564554@qq.com','16545656562','1969-04-13');
  53. insert into studescribe values('3','adin','132656@qq.com','19874551213','1986-08-23');
  55. create index index_name_email on studescribe(email);
  56. create index index_name_phone on studescribe(phone);
  58. -- 使用下面查询sql
  59. # 使用了索引:主键+索引
  60. explain select * from studescribe where stu_id='1' or phone='13548789645' \G;
  61. # 使用了索引：主键+索引
  62. explain select * from studescribe where stu_id='1' or email='454545@qq.com' \G;
  64. # 删除index_name_phone索引名
  65. drop index index_name_phone on studescribe;
  67. # 没有使用了索引:索引+索引
  68. ## 要都是索引或者主键加索引否则就失效
  69. explain select * from studescribe where phone='13548789645' or email='454545@qq.com' \G;
  70. explain select * from studescribe where stu_id='1' or phone='222' or email='454545@qq.com' \G;
  72. # 使用index_name_email索引
  73. explain select * from studescribe where email like '454545@qq.com%' \G;
  74. # 没有使用index_name_email索引,索引失效
  75. explain select * from studescribe where email like '%454545@qq.com' \G;
  76. # 没有使用index_name_email索引,索引失效
  77. explain select * from studescribe where email like '%454545@qq.com%' \G;

执行SQL文件命令

  1. source filepath

查询记录条数

  1. select count(*) from tbname;