编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE student (
    id int,
    name varchar(255),
	address varchar(255),
	city varchar(255),
    class_id int
);

INSERT INTO student VALUES (1, '刘一', '郑十家隔壁', '河南',1);
INSERT INTO student VALUES (2, '陈二',  '李四家隔壁', '安徽',3);
INSERT INTO student VALUES (3, '张三',  '白娘子家隔壁', '杭州',2);
INSERT INTO student VALUES (4, '李四',  '许仙家隔壁', '杭州',2);
INSERT INTO student VALUES (5, '王五',  '李四家隔壁', '杭州',3);
INSERT INTO student VALUES (6, '赵六',  '赵六家隔壁', '杭州',1);
INSERT INTO student VALUES (7, '孙七',  '张三家隔壁', '杭州',2);
INSERT INTO student VALUES (8, '周八',  '雷峰塔附近', '杭州',3);
INSERT INTO student VALUES (9, '吴九',  '孙七家隔壁', '杭州',1);
INSERT INTO student VALUES (10, '郑十',  '周八家隔壁', '杭州',1);
insert into student values (11,'王二','河北廊坊','河北',2);
insert into student values (11,'王二','河北廊坊','河北',8);
SELECT * FROM student\G;
show databases;

show tables in test;
-- 注释:SQL语句;
# 我是注释;
/*
我
是
注释
*/
use test;
select database();
create database wqy charset utf8;
show databases;
drop database wqy;
show databases;

use sys;
show tables;
select * from session\G;

use test;
create table teacher (
    id int,
    name varchar(10) not null,
    gender varchar(1),
    age int,
    salary decimal(8,2),
    event_date date,
    event_timestamp timestamp
);

insert into teacher values(1,'张婉仪','女',24,180000.00,'2025-07-01','2025-07-05 10:00:00');
insert into teacher values(2,'曾舜晞','男',30,600000.99,'2021-08-12','2025-07-05 10:00:00');
insert into teacher values(3,'白露','女',28,800000.99,'2023-08-12','2025-07-05 10:00:00');
select * from teacher;

insert into teacher values(4,'张静怡','女',24,480000.00,'2025-07-01','2025-07-05 10:00:00'),(5,'贺军翔','男',32,500000.99,'2020-08-12','2025-07-05 10:00:00');
select * from teacher;

delete from teacher where id=1;
select * from teacher;
delete from teacher where id>4;
select * from teacher;
use test;
show tables;

#  修改id为1的性别为男
update teacher set gender='男' where id=4;
#  修改全部的年龄为30
update teacher set age=30;
select * from teacher;

insert into teacher values(1,'张婉仪','女',24,180000.00,'2025-07-01','2025-07-05 10:00:00');
insert into teacher values(2,'曾舜晞','男',30,600000.99,'2021-08-12','2025-07-05 10:00:00');
insert into teacher values(3,'白露','女',28,800000.99,'2023-08-12','2025-07-05 10:00:00');

#  查询语句DQL 分组聚合 排序分页
#  从表里查询某几列
select id,name,city from student;

#  select 字段|聚合函数 from 表名 [where 条件] group by 列 order by 列 [asc desc]    asc从小到大,默认升序,可不写
select * from teacher where age>24 ;
select * from teacher where gender='男';
select age from teacher where age>24 group by age order by age desc;

#  统计不同性别的老师的平均年龄  count(列|*),写随便什么列或*,结果都一样,*就是数有几条数据,count(age)就是有几个有年纪的
select gender,avg(age),sum(age),min(age),max(age),count(*) from teacher group by gender;

#  统计男老师和女老师的人数
select gender,count(name) from teacher group by gender;

#  使用limit关键字对结果进行数量限制或分页显示  limit n[,m]   limit 10,5   跳过前10条,向后取5条
select * from teacher limit 3;


#  查询语句DQL 关联查询
use test;
create table class(
    id int,
    name varchar(255)
);
insert into class values(1,'三年级1班');
insert into class values(2,'三年级2班');
insert into class values(3,'三年级3班');
insert into class values(5,'三年级5班');

select student.*,class.name from student,class where student.class_id=class.id;
#  用as可以给表起别名
select s.*,c.name from student as s,class as c where s.class_id=c.id;
#  用内关联 [inner]是可以省略的
select * from student as s inner join class as c on s.class_id=c.id;
#  左外关联
select * from student as s left join class as c on s.class_id=c.id;
#  右外关联
select * from student as s right join class as c on s.class_id=c.id;







#  ID(自增主键) auto_increment primary key
#  orders表中的user_id字段定义了外键,关联到users表的user_id主键,就建立了两个表之间的关系
use test;
create table users(
    user_id int auto_increment primary key,
    username varchar(10) not null
);
create table orders(
    order_id int auto_increment primary key,
    user_id int,
    order_date date,
    foreign key(user_id) references users (user_id)
);
insert into users values(1,'张三');
insert into orders values(1001,1,'2025-07-6');
select * from users
    right join orders
    on users.user_id=orders.user_id;