基础
安装 MySQL
brew install MySQL
查看 MySQL 安装路径
ps -ef|grep MySQL
查看 MySQL 的配置文件
MySQL --verbose --help | grep my.cnf
启动
MySQL.server start
登录 MySQL, 可以在 my.cnf 的配置文件中加入 skip-grant-table
MySQL -u root -p
当前时间
select now();
显示版本
select version();
查询数据库
show databases;
创建数据库
- create database test_db;
- create database test_db charset=utf8;// 设置数据库的编码格式
- create database test_db character set utf8;
显示创建的语句
show create database test_db;
删除数据库
drop database test_db;
切换数据库
use test_db;
当前选择的数据库
select database();
查看当前数据库的表
show tables;
创建一个数据表 primary key 主键 not null 不能为空 auto_increment 自动增长
Create table students(id int unsigned not null auto_increment primary key,name varchar(20),age tinyint unsigned,hight decimal(5,2),gender enum('男','女','未知') default '未知');
删除表
drop table test;
给表添加字段
alter table students add birthday datetime;
修改表的字段类型
alter table students modify birthday date;
修改表的字段名字和类型
alter table students change birthday birth date default "1990-01-01";
删除一个字段
alter table students drop birth;
查看数据表结构
desc students;
插入数据
- insert into students values (0,'刘',18,1.72,'男');
- insert into students values (0,'刘',18,1.72,'男'),(0,'刘',18,1.72,'男');
- insert into students (name,hight) values ("测试",1.77);
- insert into students (name,hight) values ("测试",1.77),("测试",1.77);
查询数据
- select * from students;
- select name,age from students;
select name as 姓名, age as 年龄 from students;
select s.name,s.age from students as s;
修改数据
- update students set age = 30,name = "老王" where id = 1;
- update students set hight = hight +1 where id = 30;
删除数据
delete from students where id = 6
进阶
查询去重
select distinct gender from students;
where 使用
- select * from students where gender <> "男";
- select * from students where id> 3;
- select * from students where id = 3;
- select * from students where age>18 and age<40;
- select * from students where age>30 or age <20;
- select * from students where not (age> 30);
- select * from students where (not age> 30) and name="刘";
- select * from students where name like "测 %";// 查询以测开头的
- select * from students where name like "__";// 查询名字有两个字符的
- select * from students where name rlike "^ 测.*";// 正则
- select * from students where age in (30,18);// 年龄 30, 或者 18 的
- select * from students where age not in (30,18);//
- select * from students where age between 18 and 30;// 年龄 18<=age<=30
- select * from students where age not between 18 and 30;// 年龄 18<=age<=30
- select * from students where age is NULL;
- select * from students where age is not NULL;
排序
- select * from students order by age;
- select * from students where age is not NULL order by age;//asc 升序
- select * from students where age is not NULL order by age desc;// 降序
分组, 统计
select count(*) from students where age = 18;// 统计 18 岁的人数
select count(*) as 18 岁人数 from students where age = 18
- select max(age) from students ;// 最大年龄
- select min(age) from students ;// 最小年龄
- select sum(age) from students;// 所有年龄总和
- select avg(age) from students;// 平均年龄
- select sum(age)/count(*) from students;// 平均年龄
- select round( sum(age)/count(*),2) from students;//2 代表保留两位小数
- select gender from students group by gender;// 分组
- select gender, count(*) from students group by gender;// 分组统计
- select gender,group_concat(name) from students group by gender;// 根据性别统计姓名
- select gender,group_concat(name,age) from students where gender="女";
- select gender,group_concat(name,"_",age) from students where gender="女";
- select gender,group_concat(name) from students group by gender having count(*)>3;// 分组的数大于 3 条记录
- select gender,group_concat(name) from students group by gender having avg(age)>20;// 分组的平均年龄大于 20
分页
- select * from students limit 2;// 限制查询出来的个数, 最多两条
- select * from students limit 0,5;// 从数据里面第 0 条开始查 5 条
- select * from students limit 5,5;// 从第五条开始查 5 条
- select * from students limit 5,5;// 从第五条开始查 5 条
- select name from students where gender="未知" limit 2;
联合查询
if not exists 的使用
- Create table cls(id int unsigned not null auto_increment primary key,name varchar(20));
- alter table students add cls_id int default 0;
- Create table if not exists cls(id int unsigned not null auto_increment primary key,name varchar(20));
内连接, 取交集
- select * from students inner join cls ;// 结果是 count(students)*count(cls)
- select * from students inner join cls on students.cls_id = cls.id;// 查出学生所在的班级对应的班级
- select students.id,students.name,cls.name from students inner join cls on students.cls_id = cls.id;// 显示指定的列
- select s.*,c.name from students as s inner join cls as c on s.cls_id = c.id;// 显示学生所有字段, 给表设置别名
- select s.*,c.name from students as s inner join cls as c on s.cls_id = c.id order by c.name,s.id;// 排序
- select * from students as s left join cls as c on s.cls_id = c.id ;// 用 students 为基础, 查 cls, 等价下面
- select * from cls as c right join students as s on s.cls_id = c.id ;
- select * from students as s left join cls as c on s.cls_id = c.id having c.name is null;// 查询没有班级的学生
- select * from students as s left join cls as c on s.cls_id = c.id where c.name is null;// 这个不建议
- select s.id,s.name,c.name from students as s,cls as c where s.cls_id = c.id;// 不建议
自关联, 需要的数据
- create table areas(aid int primary key,atitle varchar(20),pid int);
- source aaa.sql;// 如果 aaa.sql 是一个 sql 文件, 可以使用这个, 前提是 ls 必须路径里面必须有 aaa.sql
- Select areas.atitle from areas inner join areas as city on city.pid = areas.aid having areas.atitle="北京市区";
子查询
- select * from students where hight = (select max(hight) from students where gender='男');// 查询最高的男生信息
- select * from students inner join (select max(hight) as hight from students group by cls_id) as stu on students.hight = stu.hight;// 查询每个班最高的学生信息
- select * from (select cls_id, max(hight) as hight from students group by cls_id) as stu left join students as s on stu.cls_id = s.cls_id and stu.hight = s.hight;// 同上
- select * from students inner join (select max(hight) as hight from students group by cls_id) as stu on students.hight = stu.hight having students.gender="女";// 查询每个班最高的女学生信息
- select * from students left join (select max(hight) as hight from students group by cls_id) as stu on students.hight = stu.hight having students.gender="女";// 同上
- // 视图, 主要是为了方便查询
- select s.id,s.name,s.age,s.hight,s.gender,c.name from students as s inner join cls as c on s.cls_id = c.id;
删除
drop view students_cls_view;
事务:
- start transaction;// 或者 begin
- update students set name="好人 3" where id = 28;
- commit;// 提交 rollback;// 可以回滚
索引
- set profiling=1;// 开启时间运行监测
- select * from students;
- show profiles;// 查看查询的时间
- set profiling=1;
- create index id_index on students(id);// 如果是字符串, 需要把 id 改成 name(10) 删除 drop index id_index
- show profiles;
来源: http://www.bubuko.com/infodetail-3385349.html