- RDBMS（Relational Database Management System）关系型数据库
表里面套用表 -
SQL（Structured Query Language）结构化查询语言
Navicat
-
Navicat连接：
新建数据库：
新建表：
数据类型
MySQL
数据库的操作：
# 连接数据库
mysql -uroot -p
mysql -uroot -p密码
# 退出数据库
exit / quit / control+d
# sql语句最后需要加分号;
# 显示数据库版本
select version();
# 显示时间
select now();
# 查看所有数据库
show databases;
# 创建数据库
create database 数据库名; (默认charset Latin，无法显示中文)
create database 数据库名 charset=utf8;
# 查看创建数据库
show create database 数据库名;
# 删除数据库
drop database 数据库名;
# 如果数据库名里有-,用``包起来，如
drop database `test-01`;
# 使用数据库
use 数据库名;
# 查看当前使用的数据库
select database();
数据表的操作：
// 查看当前数据库中的所有表
show tables;
// 创建表
// primary key 表示主键
// not null 表示不能为空
// auto_increment表示自动增长
// default 默认值
create table tablename( //语句太长可以换行，可以在sublime中写完后copy到MySQL中执行
id int primary key not null auto_increment,
name varchar(30)
);
//例子
create table students(
id int unsigned not null auto_increment primary key,
name varchar(30),
age tinyint unsigned default 0,
height decimal(5,2),
gender enum("男", "女"),
cls_id int unsigned
);
insert into students values(0, "老王", 18, 188.88, "男", 0);
select * from students
//展示table
desc tablename;
//修改表 alter
//add 添加字段
alter table students add birthday datetime;
//modify 修改字段——不重命名
alter table students modify birthday date;
//change 修改字段——重命名
//alter table students change 原名 新名 约束和类型;
alter table students change birthday birth date;
//drop 删除字段
alter table students drop height;
//drop 删除表
drop table students;
增删改查curd：
create update retrieve delete
//增加 insert values
//insert into tablename values(...);
//主键字段可以用0 null default 来占位，会自动auto_increment
insert into students values(0, "小李", 20, "女", 1, "1990-01-01");
insert into students values(null, "小李", 20, "女", 1, "1990-01-01");
insert into students values(default, "小李", 20, "女", 1, "1990-01-01");
//枚举中的内容可以用数字下标代替，按定义时的顺序从1开始
insert into students values(0, "小李", 20, 2, 1, "1990-01-01");
//部分插入
insert into students (name, gender) values ("小乔", 2); //剩余的用default值或者null自动填充
//多行插入
insert into students (name, gender) values("小乔", 2), ("大乔", 2);
//向students表中插入多个数据
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'刘亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'静香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0);
//删除
//物理删除delete
delete from students; //删除所有数据
delete from students where name="小李";
//逻辑删除
//给students添加一个is_delete字段 bit类型（8个bit是一个字节）
alter table students add is_delete bit default 0;
update students set is_delete=1 where id=6;
//修改 update set
update students set gender=1; //表里所有项都改成gender1
update students set gender=1 where name="小李"; //name是小李的改
update students set gender=1 where id=3; //id是3的改，唯一
update students set age=22,gender=1 where id=3; //改多个
//查询 select
select * from students; //查询所有
select * from students where name="小李";
select * from students where id>3;
//查询指定列
select name,gender from students ;
//as别名
select name as 姓名, gender as 性别 from students;
//去重查询 distinct
select distinct gender from students;
//条件查询
//比较运算符 > < >= <= = !=
select * from students where age=18;
//逻辑运算符 与或非 and or not
select * from students where age>18 and age<28;
select * from students where age>18 and gender="男";
select * from students where age>18 or height>180;
select * from students where not age>18 and gender="男"; //和下面的语句效果相同
select * from students where (not age>18) and gender="男";
select * from students where not (age>18 and gender="男");
//tip：利用()解决优先级，以不变应万变，不需要记优先级
//模糊查询
//like
//% 代表0个或多个字符
//_ 代表1个字符
select * from students where name like "小%"; //名字以小开头
select * from students where name like "%小%"; //名字包含小
select * from students where name like "__"; //两个字的名字
select * from students where name like "__%"; //至少两个字的名字
//rlike 正则
select * from students where name rlike "^周.*"; //以周开头
select * from students where name rlike "^周.*伦%" //以周开头，伦结尾
//范围查询
//in
select * from students where age in (12, 18, 34); //年龄=12or18or34的
//not in
select * from students where age not in (12, 18, 34); //年龄不=12or18or34的
//between x and y 包括x和y
select * from students where age between 18 and 34; //年龄在18-34的
// not between x and y
select * from students where age not between 18 and 34; //年龄不在18-34的
select * from students where not age between 18 and 34; //这种写法也可以，但不推荐
//空判断
//is null/NULL/Null 条件为空
select * from students where height is null;
//is not null 条件不为空
select * from students where height is not null;
//排序
//order by asc升序/desc降序
select * from students where (age between 18 and 34) and gender=1 order by age; //()可写可不写
select * from students where (age between 18 and 34) and gender=1 order by age asc; //asc为默认，可不写
select * from students where (age between 18 and 34) and gender=1 order by age desc;
//order by 多个字段
select * from students where (age between 18 and 34) and gender=1 order by age asc, height desc; //按age升序排序后，如果有age相同的按照height降序排
//聚合函数
//总数 count
select count(*) as "男性人数" from students where gender=1; //男性总数
//最大值 max
select max(height) from students where gender=2; //最高女性身高
//最小值 min
//求和 sum
select sum(age) from students; //年龄总和
//平均值 avg
select avg(age) from students; //平均年龄
select sum(age)/count(*) from students; //=avg(age) select后可以跟表达式
//四舍五入 round
round(123.193, 1) //四舍五入保留一位小数 123.2
select round(avg(age), 2) from students;
//分组 group by (分组和聚合一起使用才有意义，单独使用就是distinct去重)
select gender from students group by gender;
select gender, count(*) from students group by gender; //计算每种性别的人数
select gender, count(*) from students where gender=1 group by gender; //计算男性人数
//group_concat() 查询
select gender, group_concat(name, "_", age, "", id) from students where gender=1 group by gender; //查询男性的姓名&年龄&id
//having 在group by 结果中筛选
select gender, group_concat(name), avg(age) from students group by gender having avg(age)>30; //查询按性别分组且平均年龄>30的分组
//分页 limit start, count
select * from students limit 2; //显示两个
select * from students limit 0, 2； //每页显示两个，第一个页面
select * from students limit 2, 2； //每页显示两个，第二个页面
select * from students limit 4, 2； //每页显示两个，第三个页面
//第n页，limit (n-1)*每页个数，每页个数
//limit必须放在最后（where在前面）
select * from students where gender=1 order by height desc limit 2, 2;
//连接查询
//inner join --> table1 inner join table2 on
select * from students inner join classes on students.cls_id=classes.id;
//按需求显示
select students.*, classes.name from students inner join classes on students.cls_id=classes.id;
//给数据表起名字
select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;
//排序
select s.*, c.name from students as s inner join classes as c on s.cls_id=c.id order by c.name;
select s.*, c.name from students as s inner join classes as c on s.cls_id=c.id order by c.name, s.id;
//left join --> table1 left join table2 on 以左边的table为准，右边table没有对应数据的用null补全
select * from students as s left join classes as c on s.cls_id=c.id;
//查询没有对应班级信息的学生
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;
//这里用where也可以，但where一般用于表内数据筛选，having用于查询出来的数据的筛选
//right join 一般不用，将left join左右调换就行
create table areas(
aid int primary key,
atitle varchar(30),
pid int
);
//从SQL文件中导入数据
cd进入areas.sql所在目录 -> 启动MySQL -> use database; -> source areas.sql;
//查询所有山东省的城市
select * from areas as province inner join areas as city on province.aid=city.pid having province.atitle="山东省";
select province.atitle, city.atitle from areas as province inner join areas as city on province.aid=city.pid having province.atitle="山东省";
//查询所有青岛市的区
select * from areas as province inner join areas as city on province.aid=city.pid having province.atitle="青岛市";
子查询：（子查询速度更慢一些）
//查询身高最高的男生信息
select * from students where height = (select max(height) from students);
//子查询完成查询山东省的城市
select * from areas where pid = (select aid from areas where atitle="山东省");
tip：
数据库设计
ER模型：
网友评论