mysql基础（一）

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;