mysql常用命令

基础命令

• 登录数据库

mysql -u root -p

• 显示当前时间

select now();

• 退出(登出)数据库

exit();

• 查看所有数据

show databases;

• 创建数据库(test为数据库名)

create database test charset=utf8;

• 删除数据库(慎用)

drop database test;

• 使用(选择)数据库

use test;

• 查看当前使用的数据库

select database();

• 查看当前数据库中所有数据表

show tables;

表结构操作命令

• 创建表(表名为studens,包含id、name、gender、height、age)

create table students(
id int unsigned not null primary key auto_increment,
name varchar(20) not null,
gender enum("男","女"),
height decimal(3,2),
age tinyint default 0
);

##### 常见数据类型:
整数：int，bit
小数：decimal
字符串：varchar,char
日期时间: date, time, datetime
枚举类型:enum
##### 常见的约束:
主键 primary key: 物理上存储的顺序. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned
非空 not null: 此字段不允许填写空值.
惟一 unique: 此字段的值不允许重复.
默认 default: 当不填写字段对应的值会使用默认值，如果填写时以填写为准.
外键 foreign key: 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.

• 删除表

drop table students;

• 修改表-添加字段(添加birthday字段)

alter table students add birthday datatime;

• 修改表-仅修改字段类型

alter table students modify birthday data;

• 修改表-修改字段名和字段类型(修改birthday字段为birth)

alter table students change birthday birth datatime not null;

• 修改表-删除字段(删除birth字段)

alter table students drop birth;

• 查看表结构

desc students;

数据库表基本增、删、改、查命令

• 查询所有数据

select * from students;

• 查询指定列数据

select name,age from students;

• 添加数据-全列插入

insert into students values(0,"小明","男",1.75,18);

• 添加数据-部分列插入

insert into students(name,gender,age) values("小红","女","17");

• 添加数据-全列多行插入

insert into students values (0,"小张","男",1.75,18),(null,"小王","男",1.68,17);

• 添加数据-部分多行插入

insert into students(name,gender,age) values("小花","女","17"),("小黑","男","19");

• 修改数据

update students set name = "张三" where id = 2;

• 删除数据

-- 永久删除:
delete from students where id = 1;
-- 标记式删除:
alter table students add is_delete bit not null default 0;
update students set is_delete = 1 where id = 2;

条件查询

• as 别名

select name as 姓名 from students;

• distinct 去重

select distinct name from students;

• desc降序 \ asc升序
• where条件查询

--查询年龄大于18的学生
select * from students where age>18;
--查询id在2和4之间的学生(包含)
select * from students where id between 2 and 4;
--查询id在（3，5，7）内的学生
select * from students where id in (3,5,7);
--查询姓名最后一个字为"红"的学生
select * from students where name like ‘%红‘;
--查询名字是两个字且以"小"开头的学生
select * from students where name like ‘小_‘;
--查询未删除男生信息，按学号降序
 select * from students where is_delete = 0 and gender = ‘男‘ order by id desc;
--查询前3行男生信息
 select * from students where gender = ‘男‘  limit 3;

聚合函数查询(聚合函数不统计null值)

• 查询学生个数

select count(*) from students;

• 查询男生标号的最值

-- 查询男生的编号最大值
select max(id) from students where gender="男";
-- 查询男生的编号最小值
select min(id) from students where gender="男";

• 求女生的平均身高

select avg(height) from students where gender="女";

分组查询

• 按gender字段分组

select gender from students group by gender;

• 根据name和gender字段进行分组

select gender,name from students group by gender,name;

• 根据gender字段进行分组， 查询每个分组的姓名信息

-- group_concat显示每个分组的指定字段的信息
select gender,group_concat(name) from students group by gender;

• 统计不同性别的平均年龄,并保留两位小数

select gender,round(avg(age),2) from students group by gender;

• 根据gender字段进行分组，统计分组条数大于2的

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

• 根据gender字段进行分组,行末汇总所有人的年龄(rollup 在行末显示总汇总信息)

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

多表连接查询

inner/left/right on :连接查询关键字
on:连接查询条件
先创建班级表:

create table classes(id int unsigned not null primary key auto_increment,name varchar(20) not null);

插入3条班级数据:

insert into classes values(0,"一班"),(null,"二班"),(null,"三班");

students 表增加c_id字段,并赋值

• 内连接(以两表指定字段的交集连接)

select * from students as s inner join classes as c on s.c_id=c.id

• 左连接(以左表为基准数查询右表数据,右表数据不存在时显示null)

select * from students as s right join classes as c on s.c_id = c.id

• 右连接(与左连接相反)

select * from students as s right join classes as c on s.c_id = c.id

• 自连接(左表右表是同一个数据源表,自连接必须对表取不同别名)

select c.id, c.title, c.pid, p.title from areas c inner join areas p on c.pid = p.id;

子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询.
子查询是可以独立使用的查询语句,辅助主查询充当数据源.

• 查询大于平均年龄的学生

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

• 查找年龄最大,身高最高的学生

select * from students where (age,height) = (select max(age),max(height) from students);

添加/删除外键约束

• 添加外键约束

1. 创建表时添加:

 create table school(
id int not null primary key auto_increment,
name varchar(30)
);

create table teacher(
id int not null primary key auto_increment,
name varchar(30), 
s_id int, 
foreign key (s_id) references school(id)
);

2. 为已存在的表添加:

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

• 删除外键

alter table drop foreign key teacher_ibfk_1;
(*外键名使用"show create table teacher"命令查找*)

Mysql高级使用 - 将查询结果插入到其他表中

已存在商品表goods,包含字段:种类cate_name,商品名name,价格price

-- 先创建单独的商品分类表,仅包含id和商品名name
create table goods_cate(
id int not null primary key auto_increment, name varchar(30) not null
);

-- 查询goods表中商品的分类信息
select cate_name from goods group by cate_name;

-- 将查询结果插入到good_cates表中
insert into goods_cate(name) select cate_name from goods group by cate_name;

-- 查看goods表中的商品分类名称对应的商品分类id
select * from goods g inner join goods_cate gc on g.cate_name=gc.name;

-- 将goods表中的分类名称更改成商品分类表中对应的分类id，连接更新表中的某个字段

updata goods inner join goods_cate on goods.cate_name=goods_cate.name set goods.cate_name=goods_cate.id;