(1) 登录数据库：

mysql -u root（root为用户名） -p

(2) 显示当前时间：

Select now();

(3) 登出(退出)数据库：

Exit / quit / ctr+d

(4) 查看所有数据库 :

Show databases;

(5) 创建数据库 :

Create database students charset=utf8;

(6) 使用数据库 :

Use students;

(7) 查看当前使用的数据库 :

Select database();

(8) 删除数据库 :

Drop database lk;

(9) 查看当前数据库所有表 :

Show tables;

(10) 创建表 :

Create table students(

Id int unsiged primary key auto_increment not null,

Name varchar(10) not null,

Age tinyint default 0,

Gender enum(“男”,”女”) default “女”);

(11) 修改表-添加birthday 字段 :

Alter table students add birthday datetime not null;

(12) 修改表-修改字段类型 :

Alter table students modify birthday date;

(13)修改表-修改字段名和字段类型 :

Alter table students change birthday birth datetime not null;

(14)修改表-删除birthday :

Alter table students drop birth;

(15) 查看表结构 :

Desc students;

(16)查看创库SQL语句 :

Show create database students;

(17)删除表 :

Drop table students;

(18)查询所有列数据 :

Select * from students;

(19) 查询指定列数据:

Select birth from students;

(20)添加数据----全列插入 :

Insert into students values(0,’张三’,18,default);

主键列表插入数据的时候可以指定：0, default, null

(21)添加数据----部分列插入 :

Insert into students(name,age) values(‘guojin’,30);

(22)添加数据—全列多行插入:

Insert into students values(0,’huangrong’,28,’nv’),(0,’黄老邪’,50,default);

(23)添加数据—部分列多行插入:

Insert into students(name,age) values(‘guojin’,30), (‘周伯通’,40);

(24)修改数据:

Update students set age=18,gender=’女’ where id=3;

(25)删除数据:

Delete from students where id=8;

删除数据可以使用逻辑删除，添加一个标识字段

Alter table students add is_del tinyint default 0;

这里删除数据其实修改标识字段

Update students set is_del = 1 where id = 7;

(26)as 关键字，用户给表的字段和表设置别名 :

Select name as n,age as a from students as s;

提示：as 关键字可以省略，也表示设置别名

(27)distinct关键字,用于去除重复的数据行:

Select distinct age,gender from students;

(28) 查询编号大于3的学生:

Select * from students where id > 3;

(29)清屏

System clear;

(30) 查询编号不大于4的学生:

Select * from students where id <=4;

(31)查询姓名不是‘’黄蓉‘’的学生:

Select * from students where name <> ‘黄蓉’;

Select * from students where name != ‘黄蓉’;

(32)查询没被删除的学生:

Select * from students where is_del = 0;

(33)查询编号大于3的女同学:

Select * from students where id > 3 and gender = ‘女’;

(34)查询编号小于4或没被删除的学生:

Select * form students where id < 4 or is_del = 0;

(35) 查询年龄不在10岁到15岁之间的学生 :

Select * from students where not (age>=10 and age<=15);

(36)查询姓黄的学生:

Select * from students where name like ‘黄%’;

(37)查询姓黄并且“”名“”是一个字的学生:

Select * from students where name like ‘黄_’;

%: 表示任意多个字符

_: 表示任意一个字符

(38)查询姓黄或叫靖的学生:

Select * from students where name like ‘黄%’ or name like ‘%靖’；

(39)查询编号为3至8的学生 :

Select * from students where id >= 3 and id <= 8;

Select * from students where id between 3 and 8;

(40)查询编号不是3至8的男生:

Select * from students where not (id between 3 and 8);

(41) 查询编号是3,5,7的学生:

Select * from students where id in (3,5,7);

(42)查询编号不是3,5,7的学生:

Select * from students where id not in (3,5,7);

(43)查询没有填写身高的学生:

Select * from students where height is null;

(44)查询填写身高的学生:

Select * from students where height is not null;

(45)查询未删除男生信息，按学号降序:

Select * from students where is_del = 0 and gender = ‘男’ order by id desc;

显示所有的学生信息，先按照年龄从大到小排序，当年龄相同时 按照身高从高到低排序

Select * from students order by age desc, height desc;

默认是asc 不用指定

(46)查询前三行男生信息:

Select * from students where gender = ‘男’ limit 0,3;

简写方式,第一个参数是开始行索引，默认是0可以不指定，第二个参数是查询条数

Select * from students where gender = ‘男’ limit 3;

(47) 查询学生表，获取第n页数据的SQL语句 :

Select * fromstudents limit (n-1) * m , m;

(48) 查询学生的个数:

Select count(height) from students;

注意点：聚合函数不会对空值进行统计

一般如果要是指定列名，那么就是主键字段

Select count(id) from students;

通用写法

Select count(*) from students;

(49) 查询女生的编号最大值 :

Select max(id) from students where gender = ‘女’;

(50) 查询未删除的学生最小编号 :

Select min(id) from students where is_del = 0;

(51) 查询男生的总身高 :

Select sum(height) from students where gender = ‘男’;

(52) 求男生的平均身高 :

Select sum(height)/count(*) from students where gender = ‘男’;

Select avg(height) from students where gender = ‘男’;

Select avg(ifnull(height,0)) from students where gender = ‘男’;

注意点：聚合函数不会对空值进行统计

Ifnull函数判断指定的字段是否是空值，如果是使用默认值0

(53) 查询性别的种类 :

Select distinct gender from students;

Select gender from students group by gender;

(54) 根据gender字段进行分组,查询每个分组的姓名信息 :

Select gender,group_concat(name) from students group by gender;

group_concat : 统计每个分组指定字段的信息集合，信息之间使用逗号进行分割

(55) 统计不同性别的平均年龄 :

Select gender,avg(age) from students group by gender;

(56) 统计不同性别的人的个数 :

Selectgender,count(*) from students group by gender;

(56) 根据gender字段进行分组，统计分组条数大于2的 :

Select gender,count(*) from students group by gender having count(*) > 2;

注意：对分组数据进行过滤使用having

(57) 根据gender字段进行分组，汇总总人数 :

Select gender,count(*) from students group by gender with rollup;

(58) 根据gender字段进行分组，汇总所有人的年龄 :

Select gender,group_concat(age) from students group by gender with rollup;

(59) 使用内连接查询学生表与班级表 :

Select s.name,c.name from students s inner join classes c on s.c_id = c.id;

(60) 使用左连接查询学生表与班级表 :

Select * from students s left join classes c on s.c_id = c.id;

左连接查询，根据左表查询右表，如果右表数据不存在使用null填充

Left左边是左表，右边是右表

(61) 使用右连接查询学生表与班级表 :

Select * from students s right join classes c on s.c_id = c.id;

右连接查询，根据右表查询左表，如果左表数据不存在使用null填充

right左边是左表，右边是右表

(62) 使用自连接查询省份和城市信息 :

Select c.id,c.title,c.pid,p.title from areas c inner join areas p on c.pid = p.id where p.title = ‘河北省’;

(63) 查询大于平均年龄的学生 :

Select * from students where age > (select avg(age) from students);

(64) 查询学生在班的所有班级名字 :

Select * from classes where id in (select c_id from students where c_id is not null);

(65) 为学生表的cls_id字段添加外键约束 :

Alter table students add foreign key(c_id) references classes(id);

(66) 取消自动提交事务模式 :

Set autocommit =0;

(67) 查看Mysql 数据库支持的表的存储引擎 :

Show engines;

(68) 修改表的存储引擎 :

Alter table students engine = ‘MylSAM’;