mysqlsql⾼级语法
mysql语法
1 create table
1.1 普通创建
创建表时多字段指定索引，在字段后加索引选项
create table courses ( id int unsigned auto_increment not null, name char(10) not null,primary key (id,name))；创建表时单字段指定索引，直接在字段⾥加索引选项
create table courses ( id int unsigned auto_increment not null permary key, name char(10) not null);
显⽰表状态
show table status like "courses"\G
更改表引擎
alter table courses engine myisam
查看表的索引
show index from courses;
1.2 引⽤其他表数据创建select
create table courseBak select * from courses where id<5;
1.3 引⽤其他表的表结构创建空表
create table test like courses;
2 修改表结构alter
增加索引
alter table test add unique (name);
修改字段
alter table test change name names varchar(20) not null;
增加字段
alter table test add starttime date not null default "2020-4-13";
修改表名
alter table test rename to testcourses;
rename table testcourses to test;
增加外键约束
alter table student add foreign key foreign_1 (id) references courses (id);
删除外键约束
show create table student; #获取fk_symbol
alter table student drop foreign key fk_symbol;
3 select 单表查询
3.1 单表查询
select sid,name,id from student where id=3;
select * from student;
条件查询 = != < > <= >= <=>
select * from students where age>=20;
<=> 可以与空值⽐较
select * from students where TID<=>NULL;
运算符查询，但是该查询不⽀持索引
select * from students where age+5 > 30;
逻辑关系查询and or not
select * from students where not (SID>4 or gender="F");
通配符查，使⽤like
查看mysql索引
%代表任意字符
_代表单个任意字符
select * from students where name like "y%"
正则表达式查 rlike
select * from students where name rlike ".*$";
匹配列表，使⽤in指定列表内容
select * from students where age IN (18,20) and gender="F";
匹配null，is 或者 is not
select * from students where TID is not NULL;
相同的值只显⽰⼀次，distinct⽤法
select distinct gender from students;
根据排序按顺序读取位置的⾏，使⽤limit选项
select * from students where SID < 8 limit 2,3;
3.2 查询后进⾏排序
使⽤order by选项，后跟字段名称默认为升序ASC DESC为降序select * from students where TID is not NULL order by CID1 DESC；字段别名
select name as test from students
3.3 聚合运算 avg sum max min count
Select avg(age) from students where denger=”F”;
分组聚合运算 group by
select avg(age) from students group by gender;
分组后条件判定，使⽤having having只能与group by 联合使⽤，做过滤
select count(CID2),CID2 from students group by CID2 having count(CID2) >= 2;
4 多表查询
连接⽅式：
交叉连接：笛卡尔乘积
select sid,name,course from student,courses；
⾃然连接：等值⽐较后连接
select sid,name,course from student,courses where student.id=courses.id;
外连接：
左外连接：左边查询的条件右边没有值，⽤NULL显⽰
… left join … on
select SID,name,CID1,course from students left join courses on students.CID1=courses.id;
右外连接：右边查询的条件左边没有值，⽤NULL显⽰
…right join … on
select * from students right join courses on students.CID1=courses.id;
⾃连接:⾃⼰表复⽤⾃⼰字段，必须借⽤别⽤，做表中的字段区分
select s.name as student,c.name as teacher from students as s,students as c  where s.TID=c.SID;
⼦查询：
⽤（）做嵌套，嵌套select只能返回单值
select name,age from students where age> (select avg(age) from students);
在in中使⽤⼦查询
select name,CID1,CID2 from students where CID1 in (select CID2 from courses);
三表查询
select students.urse,students.ame from courses,students,tutors where students.CID1=courses.TID AND courses.TID=tutors.TID;
联合查询，使⽤union选项
(select * from courses) union (select CID1,name FROM students);
⼦查
NOT IN couuses.CID2 不在students.TID中的⾏
select CID2 from courses CID2 not in (select TID from students WHERE TID IS NOT NULL);
创建索引
create index name_index_test on student (id);
补充说明
索引的length指定索引时⽐较的位数，⽤sub_part表⽰
ASC 升序
DESC 降序
1. ydm

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。