1.創(chuàng)建數(shù)據(jù)庫
create database student character set utf8;
show student;
drop database student; //刪除
2.使用數(shù)據(jù)庫
use student;

3. 創(chuàng)建表
   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. 插入數(shù)據(jù)
   insert into student_t(name,age) values('hanyan',25);
5. 修改數(shù)據(jù)
   update student_t set age=26 where name='hanyan';
6. 刪除數(shù)據(jù)
   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條數(shù)據(jù)
    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