MYSQL语法（模糊查询，视图，索引）

MYSQL

模糊查询

1. 模糊查询，查询name 以张开头的数据

   select *from t1 where name like '张%'； 

2. 查询姓名包含’三’的记录

   select *from t1 where name like '%三%'; 

3. 查询姓名以‘刚’结尾的两个字符的名字

   select *from t1 where name like '_刚'； 

4. 查询头两条记录

    select *from t1 limit 2; 

5. 查询索引从1开头的3条记录

    select *from t1 limit 1,3; 

6. 使用offset，索引从1开始的3条记录

    select *from t1 limit 3 offset 1; 

7. 统计每个年龄有多少人

    select  age,count(name) as mucount from t1 group by age; 

8. 按年龄排序，升序

    select *from t1 order by age; 

9. 按年龄排序，降序

    select *from t1 order by age desc; 

10. 别名

     select t.id, t.name as myname,t.age as myage from t1 as t; 

11. 多表查询

     select s.id,s.name,s.tel,s.classid,c.cname from students as s,class_info as c where s.classid=c.classid; 

12. 子查询

     select *from students where classid =(select classid from class_info where name ='软件6班'); 

视图

1. 创建视图

   create view view_student as select id,name,birthday,sex from students; 

2. 创建视图

    create view view_student_classinfo as select s.*,c.name as myname from students as s join class_info as c on s.classid=c.classid;
   select *from view_student_classinfo; 

3. 修改视图

   altel view view_student as select id,name,birthday from students; 

4. 查看视图

    show tables;
    show table status; 

5 查看创建视图的信息

 show create view view_student; 

6. 执行事务

   begin;
   delete from class_info where class id=6;
   delete from students where id=1;
   commit; 

索引

• 创建索引

   create table t2(id int not null,username varchar(16) not null,index(username)); 

• 显示索引

   show  index from t2; 

• 删除索引

   alter table t1 drop index username; 

• 创建唯一约束

   create table persons(id int not null ,name varchar(20),address varchar(40)),phone varchar(11) not null unique); 

主键

• 创建主键表

   create table city(id int primary key,name varchar(20) not null);
   insert into city values(1,'北京'),(2,'哈尔滨'),(3,'上海'); 

• 创建外键表student1

   create table student1(id int primary key auto_increment,cityid int,foreign key(cityid) references city(id));
   insert into student1 values(2,3); 

本文地址：https://blog.csdn.net/weixin_47440383/article/details/107920440