文章目录

• 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;

1. 执行事务

   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);