网站开发需要学多久,后台网站模板 html,废品回收在哪个网站做效果好,比较好的软件开发公司存储在系统中的数据是数据库管理系统(DBMS)的核心#xff0c;数据库被设计用来管理数据的存储、访问和维护数据的完整性。MySQL中提供丰富的数据管理语句#xff0c;包括插入数据的INSERT、更新数据的UPDATE以及删除数据的DELETE语句。7.1、插入数据为表的所有字段插入数据使…存储在系统中的数据是数据库管理系统(DBMS)的核心数据库被设计用来管理数据的存储、访问和维护数据的完整性。MySQL中提供丰富的数据管理语句包括插入数据的INSERT、更新数据的UPDATE以及删除数据的DELETE语句。7.1、插入数据为表的所有字段插入数据使用基本的INSERT语句插入数据要求指定表名称和插入到新纪录中的值其基本语法为INSERT INTO tbl_name (column_list) VALUES (value_list)在插入数据前首先创建一张表mysql CREATE TABLE person- (- id INT UNSIGNED NOT NULL AUTO_INCREMENT,- name CHAR(40) NOT NULL DEFAULT ,- age INT NOT NULL DEFAULT 0,- info CHAR(50) NULL,- PRIMARY KEY (id)- );Query OK, 0 rows affected (0.03 sec)在person表中插入一条新记录id值为1name值为Greenage值为21info值为Lawyermysql INSERT INTO person (id ,name, age , info)- VALUES (1,Green, 21, Lawyer);Query OK, 1 row affected (0.02 sec)mysql SELECT * FROM person;------------------------| id | name | age | info |------------------------| 1 | Green | 21 | Lawyer |------------------------1 row in set (0.00 sec)在person表中插入一条新记录id值为2name值为Suseage值为22info值为dancermysql INSERT INTO person (age ,name, id , info)- VALUES (22, Suse, 2, dancer);Query OK, 1 row affected (0.02 sec)mysql SELECT * FROM person;------------------------| id | name | age | info |------------------------| 1 | Green | 21 | Lawyer || 2 | Suse | 22 | dancer |------------------------2 rows in set (0.00 sec)为表的指定字段插入数据在person表中插入一条新记录name值为Willamage值为20info值为sports manmysql INSERT INTO person (name, age,info)- VALUES(Willam, 20, sports man);Query OK, 1 row affected (0.02 sec)mysql SELECT * FROM person;-----------------------------| id | name | age | info |-----------------------------| 1 | Green | 21 | Lawyer || 2 | Suse | 22 | dancer || 3 | Willam | 20 | sports man |-----------------------------3 rows in set (0.00 sec)在person表中插入一条新记录name值为lauraage值为25mysql INSERT INTO person (name, age ) VALUES (Laura, 25);Query OK, 1 row affected (0.01 sec)mysql SELECT * FROM person;-----------------------------| id | name | age | info |-----------------------------| 1 | Green | 21 | Lawyer || 2 | Suse | 22 | dancer || 3 | Willam | 20 | sports man || 4 | Laura | 25 | NULL |-----------------------------4 rows in set (0.00 sec)可以发现id字段在插入数据后没有赋值时自动增加在这里id字段为表的主键不能为空紫铜会自动为字段插入自增的序列值。同时插入多条记录INSERT语句可以同时相数据表中插入多条记录插入时指定多个值列表每个值列表之间用逗号分隔开基本语法为INSERT INTO tbl_name (column_list)VALUES (value_list1), (value_list2),(value_list3);在person表中在name、age和info字段指定插入值同时插入3条新记录mysql INSERT INTO person(name, age, info)- VALUES (Evans,27, secretary),- (Dale,22, cook),- (Edison,28, singer);Query OK, 3 rows affected (0.02 sec)Records: 3 Duplicates: 0 Warnings: 0mysql SELECT * FROM person;-----------------------------| id | name | age | info |-----------------------------| 1 | Green | 21 | Lawyer || 2 | Suse | 22 | dancer || 3 | Willam | 20 | sports man || 4 | Laura | 25 | NULL || 5 | Evans | 27 | secretary || 6 | Dale | 22 | cook || 7 | Edison | 28 | singer |-----------------------------7 rows in set (0.00 sec)在person表中不指定插入列表同时插入2条新记录mysql INSERT INTO person- VALUES (9,Harry,21, magician),- (NULL,Harriet,19, pianist);Query OK, 2 rows affected (0.02 sec)Records: 2 Duplicates: 0 Warnings: 0mysql SELECT * FROM person;------------------------------| id | name | age | info |------------------------------| 1 | Green | 21 | Lawyer || 2 | Suse | 22 | dancer || 3 | Willam | 20 | sports man || 4 | Laura | 25 | NULL || 5 | Evans | 27 | secretary || 6 | Dale | 22 | cook || 7 | Edison | 28 | singer || 9 | Harry | 21 | magician || 10 | Harriet | 19 | pianist |------------------------------9 rows in set (0.00 sec)将查询结果插入数据INSERT语句用来给数据表插入记录时指定插入记录的列值。INSERT还可以将SELECT语句查询的结果插入到列表中其基本语法为INSERT INTO tbl_name1 (column_list1)SELECT (column_list2) FROM table_name2 WHERE (condition)从person_old表中查询所有的记录并将其插入到person表中首先创建一个名为person_old的数据表其表结构与person结构相同mysql CREATE TABLE person_old- (- id INT UNSIGNED NOT NULL AUTO_INCREMENT,- name CHAR(40) NOT NULL DEFAULT ,- age INT NOT NULL DEFAULT 0,- info CHAR(50) NULL,- PRIMARY KEY (id)- );Query OK, 0 rows affected (0.11 sec)向person_old表中添加两条记录mysql INSERT INTO person_old- VALUES (11,Harry,20, student), (12,Beckham,31, police);Query OK, 2 rows affected (0.20 sec)Records: 2 Duplicates: 0 Warnings: 0mysql SELECT * FROM person_old;---------------------------| id | name | age | info |---------------------------| 11 | Harry | 20 | student || 12 | Beckham | 31 | police |---------------------------2 rows in set (0.00 sec)插入数据到person表中mysql INSERT INTO person(id, name, age, info)- SELECT id, name, age, info FROM person_old;Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0mysql SELECT * FROM person;------------------------------| id | name | age | info |------------------------------| 1 | Green | 21 | Lawyer || 2 | Suse | 22 | dancer || 3 | Willam | 20 | sports man || 4 | Laura | 25 | NULL || 5 | Evans | 27 | secretary || 6 | Dale | 22 | cook || 7 | Edison | 28 | singer || 9 | Harry | 21 | magician || 10 | Harriet | 19 | pianist || 11 | Harry | 20 | student || 12 | Beckham | 31 | police |------------------------------11 rows in set (0.00 sec)7.2、更新数据表中有数据之后可以对数据进行更新其基本语法为UPDATE table_nameSET col_name1value1,col_name2value2,...,WHERE where_condition在person表中更新id值为11的记录将age字段值改为15将name字段值改为LiMingmysql UPDATE person SET age 15, nameLiMing WHERE id 11;Query OK, 1 row affected (0.02 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql SELECT * FROM person WHERE id11;--------------------------| id | name | age | info |--------------------------| 11 | LiMing | 15 | student |--------------------------1 row in set (0.00 sec)在person表中更新age值为19~22的记录将info字段值都改为studentmysql UPDATE person SET infostudent WHERE age BETWEEN 19 AND 22;Query OK, 6 rows affected (0.02 sec)Rows matched: 6 Changed: 6 Warnings: 0mysql SELECT * FROM person WHERE age BETWEEN 19 AND 22;---------------------------| id | name | age | info |---------------------------| 1 | Green | 21 | student || 2 | Suse | 22 | student || 3 | Willam | 20 | student || 6 | Dale | 22 | student || 9 | Harry | 21 | student || 10 | Harriet | 19 | student |---------------------------6 rows in set (0.00 sec)7.3、删除数据从数据表中删除数据使用DELETE语句其基本语法为DELETE FROMN table_name [WHERE ]在person表中删除id等于11的记录执行删除操作前使用SELECT语句查看当前id11的记录mysql SELECT * FROM person WHERE id11;--------------------------| id | name | age | info |--------------------------| 11 | LiMing | 15 | student |--------------------------1 row in set (0.00 sec)使用DELETE语句删除该记录mysql DELETE FROM person WHERE id 11;Query OK, 1 row affected (0.02 sec)语句执行完毕查看执行结果mysql SELECT * FROM person WHERE id11;Empty set (0.00 sec)在person表中使用DELETE语句同时删除多条记录删除age字段在19-22的记录执行删除操作前使用SELECT语句查看当前的数据mysql SELECT * FROM person WHERE age BETWEEN 19 AND 22;---------------------------| id | name | age | info |---------------------------| 1 | Green | 21 | student || 2 | Suse | 22 | student || 3 | Willam | 20 | student || 6 | Dale | 22 | student || 9 | Harry | 21 | student || 10 | Harriet | 19 | student |---------------------------6 rows in set (0.00 sec)DELETE删除这些记录mysql DELETE FROM person WHERE age BETWEEN 19 AND 22;Query OK, 6 rows affected (0.01 sec)查看执行结果mysql SELECT * FROM person WHERE age BETWEEN 19 AND 22;Empty set (0.00 sec)删除person表中所有记录SQL语句如下执行删除操作前使用SELECT语句查看当前的数据mysql SELECT * FROM person;-----------------------------| id | name | age | info |-----------------------------| 4 | Laura | 25 | NULL || 5 | Evans | 27 | secretary || 7 | Edison | 28 | singer || 12 | Beckham | 31 | police |-----------------------------4 rows in set (0.00 sec)执行DELETE语句删除这4条记录mysql DELETE FROM person;Query OK, 4 rows affected (0.01 sec)查看执行结果mysql SELECT * FROM person;Empty set (0.00 sec)如果想删除表中的所有记录还可以使用TRUNCATE TABLE语句TRUNCATE将直接删除原来的表并重新创建一个表其语法结构为TRUNCATE TABLE table_name。TRUNCATE直接删除表而不是删除记录因此执行速度比DELETE快。
