create database test00 default character set utf8mb4；

/*create database test00 default character set utf8mb4;
/使用数据库/
USE test01;
/删除数据库/
/DROP DATABASE IF EXISTS test01;/

/创建表/
USE test;
CREATE TABLE student
(
stu_id INT PRIMARY KEY AUTO_INCREMENT,
stu_name VARCHAR(15) NOT NULL,
stu_age INT NOT NULL,
stu_gender VARCHAR(1) NOT NULL,
stu_address VARCHAR(50)
);
/删除表/
DROP TABLE IF EXISTS student;
/添加人/
INSERT INTO student(stu_name,stu_age,stu_gender,stu_address)
VALUES(‘张三’,18,‘男’,‘广州现代信息’),
(‘李四’,19,‘女’,‘广州现代信息’),
(‘王五’,17,‘男’,‘广州现代信息’);

DROP TABLE IF EXISTS fish;
/创建fish表/
USE test01;
CREATE TABLE fish
(
fish_id INT PRIMARY KEY AUTO_INCREMENT,
fish_name VARCHAR(15) NOT NULL,
fish_address VARCHAR(50),
ownner_id INT NOT NULL
);
INSERT INTO fish(fish_name,fish_address,ownner_id)
VALUES(‘银龙鱼’,‘鱼缸’,1),
(‘清道夫’,‘鱼缸’,2),
(‘金龙鱼’,‘游览馆’,3),
(‘鲫鱼’,‘餐厅’,4),
(‘鲤鱼’,‘小鱼缸’,5);
/添加属性/
ALTER TABLE fish ADD COLUMN fish_age INT;
/删除属性/
ALTER TABLE fish DROP COLUMN fish_age;

DROP TABLE IF EXISTS car;
CREATE DATABASE car DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE car;
CREATE TABLE car
(
car_id INT PRIMARY KEY AUTO_INCREMENT,
/牌照/
car_no INT NOT NULL,
/品牌/
car_brand VARCHAR(15) NOT NULL,
/类型/
car_type VARCHAR(15) NOT NULL,
/马力/
car_power VARCHAR(15) NOT NULL,
/重量/
car_weight VARCHAR(15) NOT NULL,
/颜色/
car_color VARCHAR(15) NOT NULL
);
/添加字段，出厂日期/
ALTER TABLE car ADD COLUMN car_date DATETIME;
INSERT INTO car(car_no,car_brand,car_type,car_power,car_weight,car_color,car_date)
VALUES(1001,‘啊’,‘啊啊’,‘安阿伯’,‘安保’,‘嗷嗷’,‘2024-03-14 15:30:08’);
/update 表名 set列=值 where条件/
UPDATE car SET car_color=‘白色’ WHERE car_id = 1;

DROP TABLE IF EXISTS emp;
CREATE DATABASE emp DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE emp;
CREATE TABLE emp
(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15) NOT NULL,
emp_age INT NOT NULL,
emp_sex VARCHAR(15) NOT NULL,
emp_wages INT NOT NULL,
emp_department VARCHAR(15) NOT NULL
);
INSERT INTO emp(emp_name,emp_age,emp_sex,emp_wages,emp_department)
VALUES(‘张三’,24,‘男’,3000,‘技术部门’),
(‘李四’,31,‘女’,4000,‘技术部门’),
(‘王五’,27,‘女’,3500,‘后勤部门’),
(‘老六’,23,‘男’,4100,‘后端’),
(‘小七’,21,‘男’,2500,‘前端’),
(‘赵八’,33,‘女’,4500,‘后端’),
(‘钱九’,29,‘男’,3100,‘校对’),
(‘小一’,27,‘男’,3000,‘后端’),
(‘小二’,33,‘男’,2500,‘技术部门’),
(‘小三’,34,‘女’,3700,‘后勤部门’),
(‘小四’,38,‘女’,2500,‘前端’),
(‘小五’,41,‘女’,5000,‘技术部门’),
(‘小六’,24,‘男’,4100,‘后端’),
(‘小七’,26,‘女’,3300,‘技术部门’),
(‘小八’,28,‘女’,3000,‘后勤部门’);

/所有女员工工资+200/
update emp set emp_wages=emp_wages+200 WHERE emp_sex=‘女’;
/删除id为9的员工/
delete from emp where emp_id = 9;
/查询年龄在20到30之间的开发部的男员工/
select * from emp where emp_age > 20 AND emp_age < 30 AND emp_sex=‘男’;
/查询前端或技术部门的30岁下的女员工/
select * from emp where emp_age < 30 AND emp_sex = ‘女’ AND (emp_department=‘前端’ OR emp_department=‘技术部门’);
/查询后勤的姓小的员工/
select * from emp where emp_name LIKE ‘小%’ AND emp_department = “后勤部门”;
/查找技术部门工资最高的两个员工/
select * from emp WHERE emp_department=‘技术部门’ ORDER by emp_wages desc limit 2;
/查找年龄最小的女员工/
select * from emp WHERE emp_sex=‘女’ ORDER by emp_age asc limit 1;
/每页5条，分页查询/
select * from emp limit 0,5;
select * from emp limit 5,5;
select * from emp limit 10,5;

/单条件查询/
SELECT * FROM student WHERE stu_id=3;
SELECT stu_name FROM student WHERE stu_id=3;
/查所有年龄大于18/
SELECT * FROM student WHERE stu_age>18;
/多条件查询or/
SELECT * FROM student WHERE stu_address=‘广东’ OR stu_address=‘北京’;
/查询所有成年学生，或者地址是北京的/
SELECT * FROM student WHERE stu_address=‘北京’ OR stu_age>=18;
/查询所以成年学生并且是湖南的and(并且)/
SELECT * FROM student WHERE stu_address=‘湖南’ AND stu_age>=18;
/in条件(只要满足其中一个条件就符合)/
SELECT * FROM student WHERE stu_address in(‘湖南’,‘河北’,‘北京’);
/between xx and xx(选择区间)/
SELECT * FROM student WHERE stu_age BETWEEN 18 and 27;
/not 取反/
SELECT * FROM student WHERE stu_age not BETWEEN 18 and 27;
/整体取反/
SELECT * FROM student WHERE NOT (stu_address=‘广东’ OR stu_address=‘北京’);
/模糊查询like %代表任意长度的字符串/
SELECT * FROM student WHERE stu_name LIKE ‘张%’;
/每一个下划线匹配一个/
SELECT * FROM student WHERE stu_name LIKE ‘张_’;
/找出所有姓王的/
SELECT * FROM student WHERE stu_name LIKE ‘王%’;
/找出名字里面有老字的/
SELECT * FROM student WHERE stu_name LIKE ‘%王%’;
/找出姓王的，并且名字里面没有九字的/
SELECT * FROM student WHERE stu_name LIKE ‘王%’ AND not stu_name like ‘%九%’;

– 删除数据库
drop database if exists testdb;
– 创建数据库
create database testdb
default character set utf8mb4 collate utf8mb4_general_ci;
– 使用数据库
use testdb;

/update 表名 set 列 = 值，列 = 值，列 = 值，where 条件(修改表格)/
UPDATE student set stu_name=‘张小二’,stu_age=19 where stu_id=2;

/创建一张表/
DROP table if exists class;
create table class(
cl_id int primary key auto_increment,
cl_name VARCHAR(250),
cl_number INT
);
/添加数据/
insert into class(cl_name,cl_number)
VALUES
(‘1班’,40),
(‘2班’,48),
(‘3班’,39),
(‘4班’,38),
(‘5班’,42);
/添加字段/
alter table student add column stu_cl int;
/将所有学生的班级id更新上去/
UPDATE student set stu_cl=1 where stu_id=1;
UPDATE student set stu_cl=2 where stu_id=2;
UPDATE student set stu_cl=4 where stu_id=3;
UPDATE student set stu_cl=3 where stu_id=4;
/多条件查询/
SELECT * from student where stu_name=‘张三’ and stu_address=‘广州’;
/查找所有广州男生/
SELECT * from student where stu_gender=‘男’ and stu_address=‘广州’;
/查找不是广州的/
SELECT * FROM student where not stu_address=‘广州’;
/查找年纪最小的所有的学生/
SELECT * from student where stu_age=(SELECT MIN(stu_age) from student);
/count仅用于计数/
SELECT count(*),stu_age,sum(stu_age) from student GROUP BY stu_age;
/查找平均年龄最大的班级/
SELECT avg(stu_age) from student GROUP BY stu_cl ORDER BY avg(stu_age) desc LIMIT 1;
/**/