mysql 高级语法_mysql sql高级语法

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

%代表任意字符

_代表单个任意字符

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.name,courses.course,students.SID,tutors.tname 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 INcouuses.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 降序

ydm ↩︎