对如下表操作
mysql> desc t_user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | tno | int(10) | YES | | NULL | | | tname | varchar(32) | YES | | NULL | | | tsex | char(1) | YES | | m | | +-------+-------------+------+-----+---------+-------+
给t_user表添加一个联系电话字段tel
mysql> alter table t_user add tel varchar(10);
给t_user表中的tel字段长度扩展到20个长度
mysql> alter table t_user modify tel varchar(20);
将t_user表中的tel字段删除
mysql> alter table t_user drop tel;
insert语句语法格式(字段和值必须一一对应,个数相同,数据类型相同)
insert into tablename(columname1,columname2..) values(value1,value2..);向t_user表中插入数据
mysql> insert into t_user(tno,tname,tsex) values(1,'xlj','m'); Query OK, 1 row affected (0.00 sec) mysql> select * from t_user; +------+-------+------+ | tno | tname | tsex | +------+-------+------+ | 1 | xlj | m | +------+-------+------+ 1 row in set (0.00 sec)
将查询结果插入到某张表中
insert into emp_bak select * from emp where sal=3000;
注意:若插入数据的时候没有给字段指定任何数据,默认插入null
(*)insert语句中字段名称可以省略嘛?
可以!(但如果省略字段名,values必须含有所有字段的值)
mysql> insert into t_user values(2,'jay','m');建议:不要将前面的字段省略,如果省略则表示将表中所有的字段全部加上,省略之后程序不健壮,无法修改表结构,修改表结构可能导致insert执行失败
插入中文——因为cmd命令下是gbk编码,而数据库是utf8,所以建议安装MySQL-front(当然办法还有很多的)
mysql> update t_user set tname='carry' where tno=2; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t_user; +------+-------+------+ | tno | tname | tsex | +------+-------+------+ | 1 | xlj | m | | 2 | carry | m | +------+-------+------+ 2 rows in set (0.00 sec)
delete语句的语法格式
delete from tablename where 条件; 注意:若没有条件限制,这张表的所以记录全部删除删除tno=2的学生
mysql> delete from t_user where tno=2; Query OK, 1 row affected (0.07 sec) mysql> select * from t_user; +------+-------+------+ | tno | tname | tsex | +------+-------+------+ | 1 | xlj | m | +------+-------+------+ 1 row in set (0.00 sec)
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2018-03-21 21:01:45 | +---------------------+ 1 row in set (0.05 sec)
语法结构(将查询结果当做一张表创建)
- create table tablename as select columname... from tablename_o;
mysql> create table t_user1 as select * from t_user; mysql> select * from t_user1; +------+-------+------+ | tno | tname | tsex | +------+-------+------+ | 1 | xlj | m | +------+-------+------+ 1 row in set (0.00 sec)
mysql> create table t_user2 as select tno,tname from t_user; mysql> select * from t_user2; +------+-------+ | tno | tname | +------+-------+ | 1 | xlj | +------+-------+ 1 row in set (0.00 sec)