MySQL常用命令总结

MySQL服务

查看MySQL服务状态
sudo service mysql status

停止MySQL服务
sudo service mysql stop

启动MySQL服务
sudo service mysql start

重启MySQL服务
sudo service mysql restart

MySQL客户端连接MySQL服务端命令
mysql -uroot -p

quit或者exit或者ctr + d表示退出

显示当前时间
select now()

数据约束

数据约束：约束是指数据在数据类型限定的基础上额外增加的要求.
常见的约束如下:
主键 primary key: 物理上存储的顺序. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned.
非空 not null: 此字段不允许填写空值.
惟一 unique: 此字段的值不允许重复.
默认 default: 当不填写字段对应的值会使用默认值，如果填写时以填写为准.
外键 foreign key: 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.

添加外键约束: alter table 从表 add foreign key(外键字段) references 主表(主键字段);
为cls_id字段添加外键约束：
alter table students add foreign key(cls_id) references classes(id);
删除外键约束:需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称show create table teacher;
获取名称之后就可以根据名称来删除外键约束alter table teacher drop foreign key 外键名;

在创建数据表时设置外键约束
创建学生表
create table school(
id int not null primary key auto_increment,
name varchar(10)
);
创建老师表
create table teacher(
id int not null primary key auto_increament,
name varchar(10),
s_id int not null,
foreign key(s_id) reference school(id)
);

数据类型

常用的数据类型:
整数：int，bit
小数：decimal
字符串：varchar,char
日期时间: date, time, datetime
枚举类型(enum)
常见的约束:
主键约束 primary key
非空约束 not null
惟一约束 unique
默认约束 default
外键约束 foreign key
数据类型和约束保证了表中数据的准确性和完整性

数据库相关

查看所有数据库
show databases;

创建数据库
create database 数据库名 charset=utf8;

使用数据库
use 数据库名;

查看当前使用的数据库
select database();
删除数据库-慎重
drop database 数据库名;

查看当前数据库中所有表
show tables;

表相关

创建表
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) not null,
age tinyint unsigned default 0,
height decimal(5,2),
gender enum(‘男’,’女’,’人妖’,’保密’)
);

修改表-添加字段
alter table 表名 add 列名 类型 约束;

修改表-修改字段类型
alter table 表名 modify 列名 类型 约束;

修改表-修改字段名和字段类型
alter table 表名 change 原名 新名 类型及约束;

修改表-删除字段
alter table 表名 drop 列名;

查看创表SQL语句
show create table 表名;

查看创库SQL语句
show create database 数据库名;

删除表
drop table 表名;

查询数据
查询所有列 select * from 表名;
查询指定列 select 列1,列2,… from 表名;

添加数据
全列插入 值的顺序与表结构字段的顺序完全一一对应insert into 表名 values (…)
部分列插入 值的顺序与给出的列顺序对应insert into 表名 (列1,…) values(值1,…)
全列多行插入insert into 表名 values(…),(…)…;
部分列多行插入insert into 表名(列1,…) values(值1,…),(值1,…)…;

修改数据
update 表名 set 列1=值1,列2=值2,… where 条件;

删除数据
delete from 表名 where 条件;

逻辑删除
添加删除表示字段，0表示未删除 1表示删除
alter table student add isdelete bit default 0;
逻辑删除数据 update students set isdelete=1 where id=8;

使用as给字段起别名
select id as 序号, name as 名字, gender as 性别 from students;

通过 as 给表起别名
select s.id,s.name,s.gender from students as s;

distinct可以去除重复数据行
select distinct name, gender from students;

查询

where条件查询
select * from 表名 where 条件;

比较运算符查询
= > >= < <= != <>

逻辑运算符查询
and or not
select * from students where not (age >= 10 and age <= 15);

模糊查询
查询姓黄的学生 select * from students where name like ‘黄%’;
查询姓黄并且“名”是一个字的学生 select * from students where name like ‘黄_’;
查询姓黄或叫靖的学生 select * from students where name like ‘黄%’ or name like ‘%靖’;

范围查询
between。。and。。select * from students where id between 3 and 8;
in

空判断查询
is null select * from students where height is null;
is not null
排序查询语法
select * from 表名 order by 列1[,列2 asc|desc,…] asc|desc;
select * from students where gender=1 and is_delete=0 order by id desc;
select * from students order by age desc,height desc;

分页查询
select * from 表名 limit start,count;
select * from students where gender=1 limit 0,3;
select * from students where gender=1 limit 3;

已知每页显示m条数据，求第n页显示的数据
select * from students limit (n-1)*m,m;

聚合函数

聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组（group by）来使用，用于统计和计算分组数据。
count(col): 表示求指定列的总行数
max(col): 表示求指定列的最大值
min(col): 表示求指定列的最小值
sum(col): 表示求指定列的和
avg(col): 表示求指定列的平均值

求总行数
返回非NULL数据的总行数 select count(height) from students;
返回总行数，包含NULL记录 select count(*) from students;

求最大值
select max(id) from students where gender=2;

求最小值
select min(id) from students where is_delete=0;

求和
查询男生的总身高 select sum(height) from students where gender=1;
平均身高 select sum(height)/count(*) from students where gender=1;

求平均值
不统计NULL select avg(height) from students where gender=1;
用自己提供的值替换NULL select avg(ifnull(height,0)) from students where gender=1;

分组查询

将查询结果按照指定字段进行分组，字段中数据相等的分为一组
group by 列名 [having 条件表达式] [with rollup];

group by
根据gender字段来分组 select gender from students group by gender;
根据name和gender分组 select name, gender from students group by name, gender;

group by + group_concat()
group_concat(字段名)：统计每个分组指定字段的信息集合，每个信息之间用逗号分隔
select gender,group_concat(name) from students group by gender;

group by + 聚合函数的使用
select gender,avg(age) from students group by gender;
select gender,count(*) from students group by gender;

group by + having的使用
having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group
select gender,count() from students group by gender having count()>2;

group by + with rollup的使用
在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果
select gender,count(*) from students group by gender with rollup;
select gender,group_concat(age) from students group by gender with rollop;

select coalesce(a,b,c)
如果a == null则选择b；如果b ==null，则选择c；如果都为null，则返回null
select coalesce(name,’总数’),sum(singin) as count from tbl group by name with rollup

连接查询

可以实现多个表的查询，当查询的字段数据来自不同的表就可以使用连接查询完成
内连接查询，左连接查询，右连接查询，自连接查询

内连接查询
查询两个表中符合条件的共有记录
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2;
select * from students as s inner join classes as c on s.cls_id = c.id;

左连接查询
以左表为主条件查询右表数据，根据条件查询如果数据不存在使用null值填充
select 字段,字段n… from 表1 left join 表2 on 表1.字段1 = 表2.字段2;
select * from students as s left join classes as c on s.cls_id =c.id;

右连接查询
以右表为主条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充
select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2;
select * from students as s right join classes as c on s.cls_id = c.id;

自连接查询
自连接是一种特殊的连接方式，连接的表还是本身这张表。把一张表模拟成左右两张表，然后进行连表查询，自连接查询必须对表起别名。
select c.id, c.title, c.pid, p.title from areas as c inner join areas as p on c.pid = p.id
where p.title = ‘山西省’;

子查询
在一个select语句中，嵌入了另一个select语句，那么被嵌入的select语句称之为子查询语句，外部那个select语句则称为主查询。子查询是嵌入到主查询中，子查询是辅助主查询的，要么充当条件，要么充当数据源，子查询是可以独立存在的语句，是一条完整的select语句。
select * from students where age > (select avg(age) from students);
select name from classes where id in (select cls_id from students where cls_id is not null);
select * from students where (age,height) = (select max(age),max(height) from students);

数据库设计之三范式

范式：对设计数据库提出的一些规范，目前有迹可循的共有8种，一般遵守3范式即可
第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。
第二范式（2NF）：满足1NF，另外包含两部分内容，一是表必须有一个主键；二是非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分。
第三范式（3NF）：满足2NF，另外非主键列必须直接依赖于主键，不能存在传递关系。即不能存在：非主键列A依赖于非主键B，非主键B依赖于主键的情况。