MySQL基础

MySQL数据库的基本操作：
创建数据库：
create database class;
显示数据库：
show databases;
选择数据库：
use class;
创建表：
create table namelist(
number int,
name char(10),
age int,
gender char(5),
birthday date,
job varchar(50));
显示数据库中所有的表：
show tables;
显示某一个表的结构：
show create table namelist;
向表中添加数据：
insert into namelist values (1,'taiyang',24,'boy','1993-5-8','teacher');
insert into namelist values (2,'xingyun',22,'girl','1995-8-2','teacher');
insert into namelist values (3,'haifeng',15,'boy','2002-2-19','student');
insert into namelist values (4,'baiyun',17,'girl','2000-9-11','student');
insert into namelist values (5,'heitu',14,'boy','2003-11-7','student');
insert into namelist values (6,'xiaoxi',14,'girl','2003-4-27','student');
insert into namelist values (7,'mingyue',15,'girl','2002-3-27','student');
1.显示表的全部内容：
select * from namelist;
2.查询number=1的全部信息
select * from namelist where number = 1;
3.通过别名的方式查询number为1的name和job
select name,job from namelist where number = 1;
4.查询job为teacher的姓名
select name from namelist where job = 'teacher';
5.查询性别为女和指定生日的学生全部信息
select * from namelist where gender = 'girl' and birthday ='2000-9-11';
6.查询gender为男或者number大于3的人
select * from namelist where gender = 'boy' or number > 3;
7.查询name最后一个字符为指定字符的人的姓名全称
select name from namelist where name like'%n';
8.查询name以指定字符开头的人的全部信息
select * from namelist where name like'x%';
9.查询name中包含指定字符的人的全部信息
select * from namelist where name like'%x%'
10.查询全部信息，并按指定条件排序
select * from namelist order by number desc;
select * from namelist order by number asc;
11.多个排序条件：当第一个条件相同时，以第二个条件排序
select * from namelist order by number,age asc;
12.按性别分组，查询男女的人数
select gender,count(*) from namelist group by gender;
13.按性别分组，查询出gender为女的人数
select gender,count(*) from namelist where gender ='girl';
14.查询表的总记录数
select count(*) from namelist;
15.查询全部记录的前三条数据或者指定范围记录
select * from namelist limit 0,3;