sql语句

1.创建数据库
create database student character set utf8;
show student;
drop database student; //删除
2.使用数据库
use student;

3. 创建表
   create table student_t(
   id int auto_increment,
   name char(20),
   age int,
   grade char(20),
   index(id)
   );
   desc studen_t;
   show create table student_t\G
4. 插入数据
   insert into student_t(name,age) values('hanyan',25);
5. 修改数据
   update student_t set age=26 where name='hanyan';
6. 删除数据
   delete from student_t where name='hanyan';
7. 修改表结构
   alter table student_t change name sname char(25);
   alter table student_t modify name sname;
8. 删除表列
   alter table student_t drop age;
9. 新增表列
   alter table student_t add sex enum('male','female');
10. 新增主键
    alter table student_t add primary key(id);
11. 新增外键
    alter table student_t add foreign key(id) references class(class_id);
12. 无条件查询
    select * from student_t;
13. 条件查询
    select * from student_t where name='hanyan';
14. 模糊查询
    select * from student_t where name='%yan';
15. 排序查询
    select * from student_t order by age desc/asc;
16. 分组查询
    查询每个年级年龄最大的学生：
    select name, max(age) from student_t group by grade;
    查询每个年级年龄大于20的学生：
    select name from student_t group by grade having age>20;
17. 分页
    limit n,m 表示从n+1开始取m条数据
    select * from student_t limit 1;
    select * from student_t limit 1, 2;
18. 多表查询
    select name from student_t, grade_t where student_t.grade = grade_t.grade;
19. 子查询 where, from
    select * from student_t where score in (select score from grade_t where score>60);

select name from student_t,(select name grade_t where score>=60) as gra where student_t.id = gra.id

20. 连接查询
    left join, right join, inner join(并集)

https://www.nowcoder.com/practice/c63c5b54d86e4c6d880e4834bfd70c3b?tpId=82&tqId=29755&rp=1&ru=%2Factivity%2Foj&qru=%2Fta%2Fsql%2Fquestion-ranking&tab=answerKey
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

CREATE TABLE dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
请你查找各个部门领导薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列

select
salaries.*, dept_manager.dept_no
from
salaries left join dept_manager
on salaries.emp_no = dept_manager.emp_no
where
dept_manager.to_date = '9999-01-01'
order by salaries.emp_no

image.png

附：https://www.cnblogs.com/ywxbbbbb/p/10180905.html