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 "[1].*$";
匹配列表，使用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 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 降序
-
ydm ??