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;
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;
update teacher set gender='男' where id=4;
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');
select id,name,city from student;
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;
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;
select * from teacher limit 3;
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;
select s.*,c.name from student as s,class as c where s.class_id=c.id;
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;
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;