模糊查询,查询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·update语句报错:Data truncation: Truncated incorrect DOUBLE value: ‘系统开小差啦~请稍后再试‘
MySQL Fix Product Name Format(trim去空格+upper/lower大小写)
mysql pxc LIMITATIONS 8.0.19-10
网友评论