mysql 增删改数据 dml 1207 0310

# 插入数据
INSERT INTO student(id,sname,gender) VALUES(3,"小强",0);

反应

(1 row(s) affected)
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

INSERT INTO student VALUES(6,"吕小强",1)

INSERT INTO student VALUES(7,"吕小强",1),(9,"刘小备",0),(12,"aaa",1);

非全列的添加数据

insert into 表名（列1，列2，列n) values(值1，值2，值n);

全列添加数据

insert into 表名 values(值1，值2，值n);

添加多个数据

insert into 表名 values(值1，值2，值n),(值1，值2，值n),.....;

DELETe FROM student WHERe id=9;

一行一行的删除数据

delete from 表名

一次性删掉整个表 然后重建一个同样的表

truncate table 表名

UPDATe student SET sname = "大装" WHERe id = 3;

格式

update 表名 set 列1=值1,列n=值n where 条件

# 新建数据库
CREATE DATAbase hpSchool CHARACTER SET utf8;

# 使用数据库
USE hpSchool;

# 创建老师表
CREATE TABLE teacher(
	id INT PRIMARY KEY AUTO_INCREMENT,
	tname VARCHAR(5) NOT NULL,
	skill VARCHAR(20),
	joinDate DATETIME
);

# 查看老师表的表结构
DESC teacher;

# 添加几个老师的信息
INSERT INTO teacher VALUES(NULL,"卡卡西","雷切","2020-1-2");

# 查看老师表的数据
SELECT * FROM teacher;

# 查看所有的数据表
SHOW TABLES;


# 创建学生表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(5) NOT NULL,
	place VARCHAR(10) NOT NULL,
	gender INT DEFAULT 1,
	score INT,
	birthday DATETIME,
	registTime TIMESTAMP,
	tid INT,
	ConSTRAINT sb1234 FOREIGN KEY (tid) REFERENCES teacher(id)
);


# 查看一下学生表的表结构
DESC student;


# 往学生表中添加数据
INSERT INTO student(sname,place,tid) VALUES("小强","武汉",1);


# 查询学生数据
SELECt * FROM student;

# 查所有老师的数据
SELECt * FROM teacher;

# 查询自己想要的列
SELECt id,tname,skill FROM teacher;

# 查询学生都来自哪里的
SELECt DISTINCT place FROM student;

# 算术运算功能，让一行数据的一个值与另一个值算术运算
SELECt id,IFNULL(score,60),IFNULL(score,60)+id AS total FROM student;