sql语句整理

-- 创建表
-- create table teacher (
--     id int(11) not null auto_increment,
--     teaname varchar(10) not null,
--     sex varchar(10) not null,
--     course varchar(10) not null,
--     primary key (id)
-- )ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4

-- 删除表
-- drop table teacher;

-- 添加表字段
--  alter table teacher add column inteme datetime;

-- 删除表字段
-- alter table  teacher drop column age;

-- 修改表字段

-- alter table teacher modify column age varchar(10);

-- 插入语句
--  insert into teacher (teaname,sex,course,age,inteme) values ('张三',1,'计算机',32,);

-- 查询语句
-- select * from teacher where teaname='张三';

-- 删除语句
-- delete from teacher where teaname = '张三';

-- 日期格式化函数
-- SELECT FROM_UNIXTIME(1344887103);-- 将毫秒数转化为日期格式

-- select NOW() as time --获取当前时间

-- 嵌套查询
-- select * from teacher where id in (select id from teacher where teaname='张三');

-- 求和函数
-- select sum(id) from teacher;

-- 内部链接
-- select student.*,teacher.* 
-- from student
-- inner join teacher on student.`id`=teacher.`id`;

-- 左链接
-- select student.*,teacher.*
-- from student
-- left join teacher on student.`id`= teacher.`id`;

-- 右链接
-- select student.*,teacher.*
-- from student
-- right join teacher on student.`id` = teacher.`id`;

-- 笛卡尔积
-- select student.*,teacher.*
-- from student, teacher ;

-- 索引的使用
-- 单列索引
-- create index stu on student(stuname);

-- 唯一索引
-- create unique index id on student(id);

-- 聚簇索引
-- create index stus on student(id,stuname);

-- 删除索引
-- drop index stus on student