我要投稿

MYSQL单表操作学习之DDL、DML及DQL语句示例

admin Mysql

2024-04-16 0 518

1、创建数据表——DLL语句

CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT \’编号\’,
`ceateDate` datetime DEFAULT NULL COMMENT \’创建时间\’,
`userName` varchar(20) COLLATE utf8_icelandic_ci DEFAULT NULL COMMENT \’姓名\’,
`pwd` varchar(36) COLLATE utf8_icelandic_ci DEFAULT NULL COMMENT \’密码\’,
`phone` varchar(11) COLLATE utf8_icelandic_ci DEFAULT NULL COMMENT \’手机号\’,
`age` tinyint(3) DEFAULT NULL COMMENT \’年龄\’,
`sex` char(2) COLLATE utf8_icelandic_ci DEFAULT NULL COMMENT \’性别\’,
`introduce` varchar(255) COLLATE utf8_icelandic_ci DEFAULT NULL COMMENT \’简介\’,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_icelandic_ci;

2、插入数据——DML语句

insert into student values (0,\’2022-02-02 10:10:10\’,\’葫芦娃爷爷\’,\’123123\’,\’1234567690\’,69,
\’男\’,\’葫芦娃爷爷，在穿山甲的帮助下找到了葫芦籽\’);
insert into student values (0,\’2022-02-02 10:10:10\’,\’蛇精\’,\’123123\’,\’1234566890\’,66,
\’女\’,\’那个坏女人，绑架了葫芦娃\’);
insert into student values (0,\’2022-02-02 10:10:10\’,\’蜈蚣精\’,\’123123\’,\’1234567860\’,67,
\’男\’,\’那个坏男人，绑架了葫芦娃们\’);
insert into student values (0,\’2022-02-02 10:10:10\’,\’一娃\’,\’123123\’,\’1634567890\’,23,
\’男\’,\’金刚娃\’);
insert into student values (0,\’2022-02-02 10:10:10\’,\’二娃\’,\’123123\’,\’1236567890\’,26,
\’男\’,\’水娃\’);
insert into student values (0,\’2022-02-02 10:10:10\’,\’三娃\’,\’123123\’,\’1234567896\’,24,
\’男\’,\’拿个破葫芦\’);

insert into student (userName,age,introduce) VALUES(\’山神\’,25,\’孕育了葫芦娃兄弟\’)
select * from student

3、编辑语句——DQL

SELECT userName as \’用户名\’,age 年龄,sex\’性别\’,introduce\’简介\’ from student
where pwd is not 123123;
SELECT distinct sex \’性别类型\’ from student;
SELECT * from student order by userName desc;
SELECT * from student limit 5,3;