MySQL CRUD ::: info 概述
CRUD 由英文单词 C reate, R ead, U pdate, D elete 的首字母组成,即增删改查 。
本文通过介绍基本的 MySQL CRUD 方法,向读者呈现如何访问 MySQL 数据。
扩展阅读:SQL 语法必知必会
:::
MySQL 连接 1 2 3 4 5 6 7 mysql - u < user > - p mysql [db_name] mysql - h < host> - P < port> - u < user > - p [db_name] mysql - h < host> - u < user > - p [db_name] exit 或 \q
进程管理 1 2 3 4 show processlist;kill pid;
MySQL 备份恢复 1 2 3 4 5 6 7 8 mysqldump - u user - p db_name > db.sql mysqldump - u user - p db_name mysql - u user - p db_name < db.sql
DDL 数据库管理 1 2 3 4 5 6 7 8 CREATE DATABASE db;DROP DATABASE db;SHOW DATABASES;USE db;
表管理 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 SHOW TABLES;SHOW FIELDS FROM t;DESC t;SHOW CREATE TABLE t;TRUNCATE TABLE t;CREATE TABLE t ( id INT , name VARCHAR DEFAULT NOT NULL , price INT DEFAULT 0 PRIMARY KEY (id) ); DROP TABLE t;ALTER TABLE t ADD column ;ALTER TABLE t DROP COLUMN c;ALTER TABLE t ADD constraint ;ALTER TABLE t DROP constraint ;ALTER TABLE t1 RENAME TO t2;ALTER TABLE t1 RENAME c1 TO c2;
约束 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 CREATE TABLE t( c1 INT , c2 INT , c3 VARCHAR , PRIMARY KEY (c1,c2) ); CREATE TABLE t1( c1 INT PRIMARY KEY , c2 INT , FOREIGN KEY (c2) REFERENCES t2(c2) ); CREATE TABLE t( c1 INT , c1 INT , UNIQUE (c2,c3) ); CREATE TABLE t( c1 INT , c2 INT , CHECK (c1> 0 AND c1 >= c2) ); CREATE TABLE t( c1 INT PRIMARY KEY , c2 VARCHAR NOT NULL );
索引管理 1 2 3 4 5 6 7 8 9 10 CREATE INDEX idx_nameON t(c1,c2);CREATE UNIQUE INDEX idx_nameON t(c3,c4)DROP INDEX idx_name;
增删改查 插入 1 2 3 4 5 6 7 8 9 10 11 12 13 INSERT INTO t(column_list)VALUES (value_list);INSERT INTO t(column_list)VALUES (value_list), (value_list), …; INSERT INTO t1(column_list)SELECT column_listFROM t2;
更新 1 2 3 4 5 6 7 8 9 10 UPDATE tSET c1 = new_value;Update values in the column c1, c2 that match the condition UPDATE tSET c1 = new_value, c2 = new_value WHERE condition ;
删除 1 2 3 4 5 6 DELETE FROM t;DELETE FROM tWHERE condition ;
查询 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 SELECT c1, c2 FROM tSELECT * FROM tSELECT c1, c2 FROM tWHERE condition SELECT DISTINCT c1 FROM tWHERE condition SELECT c1, c2 FROM tORDER BY c1 ASC [DESC ]Skip offset of rows and return the next n rows SELECT c1, c2 FROM tORDER BY c1LIMIT n OFFSET offset Group rows using an aggregate function SELECT c1, aggregate(c2)FROM tGROUP BY c1SELECT c1, aggregate(c2)FROM tGROUP BY c1HAVING condition
参考资料