1.创建数据库
创建一个名称为chendaben的数据库:
create database chendaben default charset utf8 collate utf8_gengersal_ci;
使用chendaben数据库:
use chendaben;
查看当前使用的数据库:
select database();
2.创建表
create table t_student
(
id
double ,
stuName
varchar (60),
age
double ,
sex
varchar (30),
gradeName
varchar (60)
);
3.查看表定义
describe 表名
列如:
describe t_student;
也可以简写成:
desc t_student;
4.查看表结构的详细定义(创建时的sql语句)
show create table t_student;
5.插入数据
insert into t_student
(id
, stuName
, age
, sex
, gradeName
) values(‘1’,’张三’,’23’,’男’,’一年级’);
insert into t_student
(id
, stuName
, age
, sex
, gradeName
) values(‘2’,’张三丰’,’25’,’男’,’二年级’);
insert into t_student
(id
, stuName
, age
, sex
, gradeName
) values(‘3’,’李四’,’23’,’男’,’一年级’);
insert into t_student
(id
, stuName
, age
, sex
, gradeName
) values(‘4’,’王五’,’22’,’男’,’三年级’);
insert into t_student
(id
, stuName
, age
, sex
, gradeName
) values(‘5’,’珍妮’,’21’,’女’,’一年级’);
insert into t_student
(id
, stuName
, age
, sex
, gradeName
) values(‘6’,’李娜’,’26’,’女’,’二年级’);
insert into t_student
(id
, stuName
, age
, sex
, gradeName
) values(‘7’,’王峰’,’20’,’男’,’三年级’);
insert into t_student
(id
, stuName
, age
, sex
, gradeName
) values(‘8’,’梦娜’,’21’,’女’,’二年级’);
insert into t_student
(id
, stuName
, age
, sex
, gradeName
) values(‘9’,’小黑’,’22’,’男’,’一年级’);
insert into t_student
(id
, stuName
, age
, sex
, gradeName
) values(‘10’,’追风’,’25’,’男’,’二年级’);
insert into t_student
(id
, stuName
, age
, sex
, gradeName
) values(‘11’,’小小张三’,’21’,NULL,’二年级’);
insert into t_student
(id
, stuName
, age
, sex
, gradeName
) values(‘12’,’小张三’,’23’,’男’,’二年级’);
insert into t_student
(id
, stuName
, age
, sex
, gradeName
) values(‘13’,’张三锋小’,’24’,NULL,’二年级’);
6.查询
– 指定年龄
*SELECT FROM t_student WHERE t_student.age in (21,23,25);**
– 年龄范围
*SELECT FROM t_student WHERE age NOT BETWEEN 21 AND 2**3;
– 模糊搜索
– %全匹配
SELECT * FROM t_student WHERE stuName LIKE “小%”;
– _代表一个字符
SELECT * FROM t_student WHERE stuName LIKE “小_”;
– 且
SELECT * FROM t_student WHERE t_student.sex IS NULL AND age=21;
– 或
SELECT * FROM t_student WHERE t_student.sex IS NULL OR age=21;
– 去重复查询
SELECT DISTINCT gradeName FROM t_student;
– 降序排列
SELECT * FROM t_student ORDER BY age DESC;
– 升序排列
SELECT * FROM t_student ORDER BY age ASC;
– 分组查询
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;
– GROUP BY 与聚合函数一起使用
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;
– 各年级的平均年龄
SELECT gradeName,AVG(age) FROM t_student GROUP BY gradeName;
– 与having一起使用,限制输出条件
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING gradeName NOT in (“一年级”);
– 加一个总和行
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;
– LIMIT 分页查询(索引从0开始,查询5条记录)
SELECT * FROM t_student LIMIT 0,5;