MySQL之DML

MySQL之DML

#添加表字段

MariaDB [test]> alter table student add mobile char(11) not null default '123456789'; Query OK, 0 rows affected (0.002 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test]> select * from student; +----+----------+------+--------+-----------+ | id | name | age | gender | mobile | +----+----------+------+--------+-----------+ | 10 | xiaoming | 20 | M | 123456789 | | 13 | xiaohong | 20 | F | 123456789 | | 23 | A | 20 | F | 123456789 | | 33 | B | 20 | F | 123456789 | +----+----------+------+--------+-----------+

#修改表字段

MariaDB [test]> alter table student modify mobile char(12); Query OK, 4 rows affected (0.007 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [test]> desc students; ERROR 1146 (42S02): Table 'test.students' doesn't exist MariaDB [test]> desc student; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | | mobile | char(12) | YES | | NULL | | +--------+---------------------+------+-----+---------+----------------+

#删除表字段

MariaDB [test]> alter table student drop column mobile; Query OK, 0 rows affected (0.002 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test]> desc student; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ MariaDB [test]> select * from student; +----+----------+------+--------+ | id | name | age | gender | +----+----------+------+--------+ | 10 | xiaoming | 20 | M | | 13 | xiaohong | 20 | F | | 23 | A | 20 | F | | 33 | B | 20 | F | +----+----------+------+--------+ 4 rows in set (0.000 sec)

#表里面添加字段

MariaDB [test]> insert student value (1,'wang',20,'M'); Query OK, 1 row affected (0.001 sec) MariaDB [test]> select * from student; +----+----------+------+--------+ | id | name | age | gender | +----+----------+------+--------+ | 1 | wang | 20 | M | | 10 | xiaoming | 20 | M | | 13 | xiaohong | 20 | F | | 23 | A | 20 | F | | 33 | B | 20 | F | +----+----------+------+--------+

#一次性添加多个字段

MariaDB [test]> insert student (name,gender,age) value ('F','M',22),('D','F',23),('E','M',24); Query OK, 3 rows affected (0.001 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [test]> select * from student; +----+----------+------+--------+ | id | name | age | gender | +----+----------+------+--------+ | 1 | wang | 20 | M | | 10 | xiaoming | 20 | M | | 13 | xiaohong | 20 | F | | 23 | A | 20 | F | | 33 | B | 20 | F | | 43 | C | 22 | M | | 44 | F | 22 | M | | 45 | D | 23 | F | | 46 | E | 24 | M | +----+----------+------+--------+ 9 rows in set (0.000 sec)

#复制旧表结构批量导入旧表数据

MariaDB [test]> create table student2 like student; Query OK, 0 rows affected (0.003 sec) MariaDB [test]> desc student2; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.001 sec) MariaDB [test]> select * from student2; Empty set (0.000 sec) #此时为空，添加内容： MariaDB [test]> insert student2 select * from student; Query OK, 9 rows affected (0.001 sec) Records: 9 Duplicates: 0 Warnings: 0 MariaDB [test]> select * from student2; +----+----------+------+--------+ | id | name | age | gender | +----+----------+------+--------+ | 1 | wang | 20 | M | | 10 | xiaoming | 20 | M | | 13 | xiaohong | 20 | F | | 23 | A | 20 | F | | 33 | B | 20 | F | | 43 | C | 22 | M | | 44 | F | 22 | M | | 45 | D | 23 | F | | 46 | E | 24 | M | +----+----------+------+--------+ 9 rows in set (0.000 sec)

set 添加表里面内容的方法

MariaDB [test]> insert student set name='hao',age=22,gender='M'; Query OK, 1 row affected (0.001 sec) MariaDB [test]> select * from student; +----+----------+------+--------+ | id | name | age | gender | +----+----------+------+--------+ | 1 | wang | 20 | M | | 10 | xiaoming | 20 | M | | 13 | xiaohong | 20 | F | | 23 | A | 20 | F | | 33 | B | 20 | F | | 43 | C | 22 | M | | 44 | F | 22 | M | | 45 | D | 23 | F | | 46 | E | 24 | M | | 47 | hao | 22 | M | +----+----------+------+--------+ 10 rows in set (0.000 sec)

#update ...... where..... 修改表内容

MariaDB [test]> update student set age=18 where id=46; MariaDB [test]> select * from student; +----+----------+------+--------+ | id | name | age | gender | +----+----------+------+--------+ | 1 | wang | 20 | M | | 10 | xiaoming | 20 | M | | 13 | xiaohong | 20 | F | | 23 | A | 20 | F | | 33 | B | 20 | F | | 43 | C | 22 | M | | 44 | F | 22 | M | | 45 | D | 23 | F | | 46 | E | 18 | M | | 47 | hao | 22 | M | +----+----------+------+--------+ 10 rows in set (0.000 sec)

#delete

#将45号删除： MariaDB [test]> delete from student where id=45; Query OK, 1 row affected (0.001 sec) MariaDB [test]> select * from student; +----+----------+------+--------+ | id | name | age | gender | +----+----------+------+--------+ | 1 | wang | 20 | M | | 10 | xiaoming | 20 | M | | 13 | xiaohong | 20 | F | | 23 | A | 20 | F | | 33 | B | 20 | F | | 43 | C | 22 | M | | 44 | F | 22 | M | | 46 | E | 18 | M | | 47 | hao | 22 | M | +----+----------+------+--------+ 9 rows in set (0.000 sec) #快速清空表里面的内容： MariaDB [test]> truncate table student; Query OK, 0 rows affected (0.009 sec) MariaDB [test]> select * from student; Empty set (0.000 sec) MariaDB [test]> desc student; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.001 sec)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。