MYSQL

模糊查询

模糊查询，查询name 以张开头的数据
```
select *from t1 where name like '张%'； 
```

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

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

查询姓名以‘刚’结尾的两个字符的名字
```
select *from t1 where name like '_刚'； 
```
查询头两条记录
```
 select *from t1 limit 2; 
```
查询索引从1开头的3条记录
```
 select *from t1 limit 1,3; 
```
使用offset，索引从1开始的3条记录
```
 select *from t1 limit 3 offset 1; 
```

统计每个年龄有多少人

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

按年龄排序，升序
```
 select *from t1 order by age; 
```
按年龄排序，降序
```
 select *from t1 order by age desc; 
```

别名

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

多表查询

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

子查询

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

视图

创建视图

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

创建视图

 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;

修改视图

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

查看视图
```
 show tables;
 show table status; 
```

5 查看创建视图的信息

 show create view view_student;

执行事务

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

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

MYSQL

模糊查询

视图

索引

主键

相关推荐