MySQL数据的增删改操作

MySQL数据的增删改操作

1、查看表的属性

show columns from 表名   ——显示列的属性名字等等

information数据库存储着所表的信息，其中的table_constraints存储着约束信息

SELECT * FROM table_constraints
WHERE table_name= 表名 ——查询该表的所有约束

例如：

SELECT * FROM table_constraints
WHERE table_name= 'student' 

3、插入操作

[]里的语句是可选项也可以不选用。

写法一：insert [low_priority 降低优先级| delayed 延迟 | hig_priority 提高优先级（只对insert 和select有效） ] [ignore 忽略警报] into 表名（列名，列名。。。。）

values(…),(…),(…)； -----向表格一次加入多条信息

写法二：insert [low_priority 降低优先级| delayed 延迟 | hig_priority 提高优先级（只对insert 和select有效） ] [ignore 忽略警报] into 表名

​ set 列名=…，列名=…; ————向指定的列加入信息，其他没有写的列默认为空

写法三：insert [low_priority 降低优先级| delayed 延迟 | hig_priority 提高优先级（只对insert 和select有效） ] [ignore 忽略警报] into 表名

​ select 列名 from 表名 ; ————向表格加入来自其他表的数据

例如：

inser into s

select * from student;

4、修改操作

update [low_peiority] [ignore] 数据表名 set 字段1=值1 [,字段2=值2]

[where ]

[order by 字段 asc/desc ]

[limit 行数]

例如：修改产品表，找到单价最贵的三种产品降价5%

UPDATE products SET Price=Price*0.95
ORDER BY Price DESC
LIMIT 3

5、删除操作

delete [low_prioity] from 表名

[where ]

[order by 字段 asc/desc ]

[limit 行数]

两种不同的删除操作：

delete from 表名 ——删除数据

truncate table 表名 ——清空数据，后drop再create 就是进行重建

SELECT books.name FROM
books JOIN (SELECT rid,COUNT(*)`cou`FROM borrows WHERE valid=1 GROUP BY rid) t1
ON books.id=t1.rid
WHERE quantity<=cou

CREATE DATABASE db1;
USE db1;

db1

EXPLAIN
SELECT * FROM student
WHERE sname='小明'


sales


SELECT *
FROM productnotes
WHERE MATCH(note_text) AGAINST('teacher');

SELECT *
FROM productnotes
WHERE note_text LIKE '%teacher%'
SELECT * FROM table_constraints
WHERE table_name= 'student'    ——查询该表的所有约束



CREATE TABLE s
LIKE student 
UPDATE products SET Price=Price*0.95
ORDER BY Price DESC
LIMIT 3







CREATE DATABASE BookManage

USE BookManage

CREATE TABLE bookTypes
(
id INT PRIMARY KEY,
NAME VARCHAR(20) NOT NULL
);

CREATE TABLE readers
(
id INT PRIMARY KEY,
NAME VARCHAR(20) NOT NULL
);

CREATE TABLE books
(
id INT PRIMARY KEY,
NAME VARCHAR(20) NOT NULL,
price DECIMAL(18,2),
quantity INT,
btype INT REFERENCES booktypes(id)
);

CREATE TABLE borrows
(
rid INT ,
bid INT REFERENCES books(id),
valid BIT(1) NOT NULL,
PRIMARY KEY(rid,bid), 
FOREIGN KEY(rid)REFERENCES readers(id)
);



INSERT INTO booktypes(id,NAME)
 VALUES(1,'历史'),(2,'计算机');

INSERT INTO readers(id,NAME)
 VALUES(1,'张三'),(2,'李四');
 INSERT INTO books(id,NAME,price,quantity,btype)
 VALUES(1,'史记',56.8,2,1),(2,'数据库原理',47.2,3,2);
INSERT INTO borrows
VALUES((SELECT id FROM readers WHERE NAME='张三'),(SELECT id FROM books WHERE NAME='史记'),1),
((SELECT id FROM readers WHERE NAME='李四'),(SELECT id FROM books WHERE NAME='史记'),1);
SELECT * FROM borrows;
SELECT rid,bid ,BIN(valid+0) FROM borrows;

UPDATE borrows
SET valid = 0
WHERE rid=(SELECT id FROM readers WHERE NAME='张三') AND bid=(SELECT id FROM books WHERE NAME='史记' );
VALUES((SELECT id FROM readers WHERE NAME='张三'),(SELECT id FROM books WHERE NAME='数据库原理'),1);

DELETE FROM borrows WHERE rid=(SELECT id FROM readers WHERE NAME='张三');
DELETE FROM readers WHERE NAME ='张三';



SELECT NAME FROM books
INNER JOIN borrows
WHERE (books.quantity- COUNT(valid))<=0
ORDER BY borrows.bid;