MySQL 基本操作：增删改查

MySQL 基本操作：增删改查

-- 查看数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE student;

-- 调用数据库
USE student;

-- 查看当前数据库
SELECT DATABASE();

-- 创建数据表
CREATE TABLE score(
    name VARCHAR(20),
    id int,
    sex CHAR(1),
    data DATE
    );
    
-- 查看表结构
DESC score;

-- 插入数据
INSERT INTO score VALUES ("ming",20021,"f","1999-10-8");

-- 查询表
SELECT *FROM score where id = 20001;

-- 修改表数据
UPDATE score SET name= "huang" where id = 20001;

-- 删除表数据
DELETE FROM score where id = 20002;

-- 删除表
DROP TABLE pet;

-- 删除数据库
CREATE DATABASE test;
DROP DATABASE test;

MySQL插入、更新与删除数据 实例

利用MySQL实现下面操作，（Mysql作业，很简单的实例，具体语句在最后。）

新建食品表

1.Food表的定义

2.将下边的记录插入到food表中

3.将“CC牛奶厂”的厂址（address）改为“内蒙古”，并且将价格改为3.2
4.将厂址在北京的公司保质期（validity_time）都改为5年
5.删除过期食品的记录。若当前时间生产年份（produce_time）>保质期（validity_time），则视为过期食品
6.删除厂址为北京的食品的记录

新建教师表

1.teacher表的定义

2.向teacher表中插入数据

3.更新id为1的记录，将生日（birthday）改为“198-11-08”

4.将性别（sex）为“男”的记录的家庭住址（address）都变为“北京市朝阳区”

5.删除教工号（num）为1002的记录删除

SQL

CREATE DATABASE xuxing;
USE xuxing;
CREATE TABLE Food_db(
    id INT(10) PRIMARY KEY NOT NULL UNIQUE auto_increment,
    name VARCHAR(20) NOT NULL,
    company VARCHAR(30) NOT NULL,
    price FLOAT NOT NULL,
    produce_time YEAR,
    validity_time INT(4),
    address VARCHAR(50)
);

INSERT INTO Food_db VALUES(1,"AA饼干","AA饼干厂",2.5,2008,3,"北京");
INSERT INTO Food_db VALUES(2,"CC牛奶","CC牛奶厂",3.5,2009,3,"河北");
INSERT INTO Food_db VALUES(3,"EE果冻","EE果冻厂",1.5,2007,3,"北京");
INSERT INTO Food_db VALUES(4,"FF咖啡","FF咖啡厂",20,2002,3,"天津");
INSERT INTO Food_db VALUES(5,"GG奶糖","GG奶糖厂",14,2003,3,"广东");

UPDATE Food_db SET address = "内蒙古" WHERE name = "CC牛奶厂";
UPDATE Food_db SET validity_time = 5 WHERE addres = "北京";

DELETE FROM Food_db WHERE validity_time < (year(curdate())-produce_time);
DELETE FROM Food_db WHERE address = "北京";


CREATE TABLE teacher_db(
    id INT(4) PRIMARY KEY NOT NULL UNIQUE auto_increment,
    num INT(10) NOT NULL UNIQUE,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(4) NOT NULL,
    birthday DATETIME,
    address VARCHAR(50)
);

INSERT INTO teacher_db VALUES(1,1001,"张三","男","1984-11-08","北京市昌平区");
INSERT INTO teacher_db VALUES(2,1002,"李四","女","1970-01-21","北京市海淀区");
INSERT INTO teacher_db VALUES(3,1003,"王五","男","1976-10-30","北京市昌平区");
INSERT INTO teacher_db VALUES(4,1004,"赵六","男","1980-06-05","北京市顺义区");

UPDATE teacher_db SET birthday="198-11-08" WHERE id = 1;
UPDATE teacher_db SET address="北京市朝阳区" WHERE sex = "男";

DELETE FROM teacher_db WHERE num = 1002;