1. 背景
* CURD 操作通常是使用关系型数据库系统中的结构化查询语言(Structured Query Language,SQL)完成的
* CURD 定义了用于处理数据的基本原子操作
* CURD 代表创建(Create)、更新(Update)、读取(Retrieve)和删除(Delete)操作。
2. 创建表操作
* 创建数据库(DB) mytest
CHARACTER SET: 设置字符集
mysql> CREATE DATABASE mytest CHARACTER SET utf8mb4;
Query OK,1 row affected (0.00 sec)* 在数据库中创建表(table)
ENGINE=INNODB 指定Innodb 存储引擎
CHARSET=utf8mb4 设置表字符集
mysql> CREATE TABLE users(-> id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,-> name VARCHAR(32) NOT NULL,-> sex ENUM('M','F') NOT NULL,-> age INT NOT NULL
->)ENGINE=INNODB CHARSET=utf8mb4;
Query OK,0 rows affected (0.03 sec)3. 插入数据操作
* select 插入单条数据
mysql> INSERT INTO users SELECT NULL,'tom','M',29;
Query OK,1 row affected (0.01 sec)
Records:1 Duplicates:0 Warnings:0
mysql> select * from users;+----+------+-----+-----+| id | name | sex | age |+----+------+-----+-----+|1| tom | M |29|+----+------+-----+-----+1 row in set (0.00 sec)* values 插入单条数据
mysql> INSERT INTO users VALUES (NULL,'jak','F',33);
Query OK,1 row affected (0.01 sec)
mysql> select * from users;+----+------+-----+-----+| id | name | sex | age |+----+------+-----+-----+|1| tom | M |29||2| jak | F |33|+----+------+-----+-----+2 rows in set (0.00 sec)* select 指定列插入 [ id列会自增 ]
mysql> INSERT INTO users(name, sex, age) SELECT 'sea','M','26';
Query OK,1 row affected (0.01 sec)
Records:1 Duplicates:0 Warnings:0
mysql> select * from users;+----+------+-----+-----+| id | name | sex | age |+----+------+-----+-----+|1| tom | M |29||2| jak | F |33||3| sea | M |26|+----+------+-----+-----+3 rows in set (0.01 sec)* values 指定列插入
mysql> INSERT INTO users(name, sex, age) VALUES ('hai','F','18');
Query OK,1 row affected (0.02 sec)
mysql> select * from users;+----+------+-----+-----+| id | name | sex | age |+----+------+-----+-----+|1| tom | M |29||2| jak | F |33||3| sea | M |26||4| hai | F |18|+----+------+-----+-----+4 rows in set (0.00 sec)* values 插入多条数据
mysql> INSERT INTO users VALUES (null,'test1','F',23),(null,'test2','M',34);
Query OK,2 rows affected (0.01 sec)
Records:2 Duplicates:0 Warnings:0
mysql> select * from users;+----+-------+-----+-----+| id | name | sex | age |+----+-------+-----+-----+|1| tom | M |29||2| jak | F |33||3| sea | M |26||4| hai | F |18||5| test1 | F |23||6| test2 | M |34|+----+-------+-----+-----+6 rows in set (0.00 sec)* values 指定列插入多条数据
mysql> INSERT INTO users(name, sex, age) VALUES ('user1','F',23),('user2','M',34);
Query OK,2 rows affected (0.01 sec)
Records:2 Duplicates:0 Warnings:0
mysql> select * from users;+----+-------+-----+-----+| id | name | sex | age |+----+-------+-----+-----+|1| tom | M |29||2| jak | F |33||3| sea | M |26||4| hai | F |18||5| test1 | F |23||6| test2 | M |34||7| user1 | F |23||8| user2 | M |34|+----+-------+-----+-----+8 rows in set (0.00 sec)4. 修改数据操作
update <table_name>
set column = val,....
where 条件语句 (没有写条件语句会修改表中所有数据)* 一次修改单列数据操作
mysql> select * from users where id =1;+----+------+-----+-----+| id | name | sex | age |+----+------+-----+-----+|1| tom | M |29|+----+------+-----+-----+1 row in set (0.00 sec)
mysql> UPDATE users set name='lisea' where id =1;
Query OK,1 row affected (0.02 sec)
Rows matched:1 Changed:1 Warnings:0
mysql> select * from users where id =1;+----+-------+-----+-----+| id | name | sex | age |+----+-------+-----+-----+|1| lisea | M |29|+----+-------+-----+-----+1 row in set (0.00 sec)* 一次修改多列数据操作
mysql> select * from users where id =1;+----+-------+-----+-----+| id | name | sex | age |+----+-------+-----+-----+|1| lisea | M |29|+----+-------+-----+-----+1 row in set (0.01 sec)
mysql> UPDATE users set sex='F',age=33 where id =1;
Query OK,1 row affected (0.01 sec)
Rows matched:1 Changed:1 Warnings:0
mysql> select * from users where id =1;+----+-------+-----+-----+| id | name | sex | age |+----+-------+-----+-----+|1| lisea | F |33|+----+-------+-----+-----+1 row in set (0.00 sec)5. 删除数据操作
delete from <table_name>
where 条件语句 [ 没有写条件语句会修改表中所有数据 ]
mysql> select * from users;+----+-------+-----+-----+| id | name | sex | age |+----+-------+-----+-----+|1| lisea | F |33||2| jak | F |33||3| sea | M |26||4| hai | F |18||5| test1 | F |23||6| test2 | M |34||7| user1 | F |23||8| user2 | M |34|+----+-------+-----+-----+8 rows in set (0.00 sec)
mysql> DELETE FROM users WHERE id =1;
Query OK,1 row affected (0.02 sec)
mysql> select * from users;+----+-------+-----+-----+| id | name | sex | age |+----+-------+-----+-----+|2| jak | F |33||3| sea | M |26||4| hai | F |18||5| test1 | F |23||6| test2 | M |34||7| user1 | F |23||8| user2 | M |34|+----+-------+-----+-----+7 rows in set (0.00 sec)
mysql> DELETE FROM users WHERE id in (2,4);
Query OK,2 rows affected (0.01 sec)
mysql> select * from users;+----+-------+-----+-----+| id | name | sex | age |+----+-------+-----+-----+|3| sea | M |26||5| test1 | F |23||6| test2 | M |34||7| user1 | F |23||8| user2 | M |34|+----+-------+-----+-----+5 rows in set (0.00 sec)
mysql> DELETE FROM users WHERE id >=7;
Query OK,2 rows affected (0.01 sec)
mysql> select * from users;+----+-------+-----+-----+| id | name | sex | age |+----+-------+-----+-----+|3| sea | M |26||5| test1 | F |23||6| test2 | M |34|+----+-------+-----+-----+3 rows in set (0.00 sec)