重命名,将表格
ALTER TABLE name RENAME TO name1;
ALTER TABLE name RENAME AS name1;
ALTER TABLE name RENAME name1;
删除表 DROP name;
DROP user,user1;
添加字段
ALTER TABLE uesr(表格名字) ADD(后面添加内容) card CHAR(18) + 位置;
一个表添加多段
ALTER TABLE user
ADD …………,
ADD………;
删除字段
ALTER TABLE user DROP test;
删除表多段字
ALTER TABLE user
DROP test,
DROP test1;
修改字段内容 MODIFY
ALTER TABLE user MODIFY email ;
将car字段移动到test字段之后
ALTER TABLE user MODIFY card CHAR(18) AFTER test;
将test字段改为test1
ALTER TABLE user CHANGE test test1;
删除默认值
ALTER TABLE user AFTER email DROP DEFAULT;
添加主键
ALTER TABLE user ADD PRIMARY KEY(id,name);
删除主键
ALTER TABLE user DROP PRIMARY KEY;
或, 当有自增长,删不掉主键,可以通过一下方式
ALTER TABLE user MODIFY id INT,
ALTER TABLE user DROP PRIMARY KEY;
添加唯一
ALTER TABLE user ADD UNIQUE(name);
删除唯一
ALTER TABLE user DROP INDEX name;
修改表的存储引擎
ALTER TABLE user ENGINE=MYISAM;
修改自增长的值
ALTER TABLE user AUTO_INCREMENT=100;
插入表记录的操作
INSERT INTO user VALUES(1,’king’,’12312’);
INSERT user VALUE(1,’king’,’12313’);
INSERT user(id,name) VALUES(1,’king’);
INSERT user VALUES(1,’king’),
(2,’king2’),
(3,’king3’);
以INSERT SET形式插入
INSERT user SET id=8,username=’king’;
将查询结果插入表
INSERT user SELECT id,username FROM user1;
将用户表中所有的年龄更新为15
UPDATE user SET age=15;
UPDATE user SET age=12,email=’123@q.com’;
将第一个记录的password,email,age
UPDATE user SET password=1123,email=’2@.amm’,age=12
WHERE id=1;
UPDATE user SET age=age-5 WHERE id>=3;
删除user表中的记录
DELETE FROM user;
删除user表中id为1的用户
DELETE FROM user WHERE id=1;
彻底清空uesr表
TRUNCATE TABLE user;