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依赖于主键的情况。