MySQL单表操作学习DDL_DML_DQL语句

数据库名称可以为【schoolDB】,字符集【utf8】,排序规则【utf8_general_ci】。

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

CREATE TABLE `lzy` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `creatdate` datetime DEFAULT NULL COMMENT '创建时间',
  `username` varchar(20) DEFAULT NULL COMMENT '姓名',
  `pwd` varchar(36) DEFAULT NULL COMMENT '密码',
  `phone` varchar(11) DEFAULT NULL COMMENT '手机号',
  `age` tinyint(3) DEFAULT NULL COMMENT '年龄',
  `sex` char(2) DEFAULT '男' COMMENT '性别',
  `introduce` varchar(255) DEFAULT NULL COMMENT '简介',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;

2、插入数据——DML语句

insert into lzy values(0,'2022-02-02 10:00:00','开心超人','123454',
'12323456789',16,'男','开心铁拳。');
insert into lzy values(0,'2022-02-02 10:00:00','花心超人','123454',
'12323456789',15,'男','花心磁力链。');
insert into lzy values(0,'2022-02-02 10:00:00','甜心超人','123454',
'12323456789',14,'女','甜心保护罩。');
insert into lzy values(0,'2022-02-02 10:00:00','粗心超人','123454',
'12323456789',13,'男','粗心飞弹。');
insert into lzy values(0,'2022-02-02 10:00:00','小心超人','123454',
'12323456789',12,'男','小心分身。');
insert into lzy (username,age,introduce) values('宅博士',255,'超人他们爹');
select * from lzy;

3、基础查询语句——DQL

select username as '用户名',age 年龄,sex '性 别',introduce '简介' 
from lzy where pwd is not null;
select distinct sex '性别类型' from lzy;
select * from lzy order by username desc ;
select * from lzy limit 6,3;