Mysql常用基础语句

achong
2022-03-23 / 0 评论 / 46 阅读 / 正在检测是否收录...

持续更新......

选择某表;

USE test01;

创建表后,添加主键约束;

ALTER TABLE `user` ADD CONSTRAINT pk_id PRIMARY KEY(`id`);

删除主键约束,不需要指定字段;

ALTER TABLE `person1` DROP PRIMARY KEY;

添加联合主键;

ALTER TABLE `person1` ADD PRIMARY KEY (`uid`,`pid`);

创建表后,添加外键约束;

alter table 从表 add [constraint] 外键名称 foreign key (从表外键字段名) references 主表(主表的主键);

ALTER TABLE `userInfo` ADD CONSTRAINT userInfo_user_fk FOREIGN KEY(`id`) REFERENCES `user`(`id`);

删除外键约束,需要指定外键名称;

ALTER TABLE `userInfo` DROP FOREIGN KEY userInfo_user_fk;

增---------------------------------------------------------------------;

创建表;

CREATE TABLE person1(
`uid` CHAR(12) NOT NULL,
`pid` CHAR(12) NOT NULL,
`text` CHAR(50)
);

插入数据;

INSERT INTO user1(NO,NAME) VALUE ("125","achong");

插入多行数据;

INSERT INTO user1(NO,NAME) VALUE ("125","achong1"),("126","achong2"),("127","achong3");

INSERT INTO 
userInfo(id, address, phone) 
VALUE 
("125","广东省清远市111",10001),
("126","广东省清远市222","10002"),
("127","广东省清远市333","10003");

添加字段;

ALTER TABLE user1 ADD `phone`CHAR(11);

创建数据库

create database userdb;

删--------------------------------------------------------------------;

删除某表;

DROP TABLE user22;

删除某行;

DELETE FROM person1 WHERE `text`= "qwe00";

删除表中所有行

对比delete,truncate table的执行熟读更快,使用的系统资源和事务日志资源更少,并且删除数据后的标识列会重新开始编号。

truncate table t_user;

改--------------------------------------------------------------------;

修改表名;

ALTER TABLE `user1` RENAME `user`;

修改字段,连同字段的属性都需要修改;

ALTER TABLE user1 CHANGE `phone` `sex`CHAR(1);

更新表数据

update dept set loc='SH' where deptno=30;

查--------------------------------------------------------------------;

查询所有数据库;

SHOW DATABASES; 

查看所有表;

SHOW TABLES;

查看表结构;

DESC `userInfo`;

查询表数据;

SELECT * FROM `userInfo`;
SELECT * FROM `person1`;

0

评论 (0)

取消
粤ICP备18061175号-3