一. ubuntu下常用
1.显示现有的数据库
show databases;
2.显示当前所在的所在的数据库
select database();
3.使用xxx数据库
use xxx;
4.进入xxx数据库后,查看所有表
show tables;
二. 数据库操作
1.创建xxx数据库
create database xxx;
2.删除xxx数据库
drop database xxx charset=utf8;
3.切换到xxx数据库
use xxx;
4.查看当前选择的数据库
select database();
三. 表操作
1.常用的的数据类型
数字:int,decimal
字符串:varchar,text
日期:datetime
布尔:bit
2.约束条件
主键primary key
非空not null
惟一unique
默认default
外键foreign key
3.显示当前数据库中的所有表
show tables;
4.创建一个human表,包含ID,名字,性别,年龄,生日
create table human(
id int auto_increment primary key,
name varchar(10) not null,
sex bit default 1,
age int not null,
birthday datetime
);
5.显示当前创建human表所有字段对应的信息
desc human;
//输出的信息如下:
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| sex | bit(1) | YES | | b'1' | |
| age | int(11) | NO | | NULL | |
| birthday | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
6.显示当前yyy表所有的数据
select * from yyy;
7.删除当前yyy表
drop table yyy;
8.把当前yyy表改成zzz表
alter table yyy rename zzz;
9.向当前human表插入字段,方式1
insert into human values(0,'吕布',1,58,'235-5-2');
//一次插入多条
insert into human values(0,'吕布',1,58,'235-5-2'),(0,'刘备',1,68,'232-5-6');
10.向当前human表插入字段,方式2
insert into human(name,age,weight) values('张飞',47,66.7);
11.human添加表列,名为weight,约束为不为空
alter table human add weight float not null;
12.human删除weight表列
alter table human drop weight;
13.human修改name表列类型
alter table human change name man char(20);
14.根据条件删除,删除age<60
delete from human where age<60;
15.通常删除并不是真正的删除,而是设置一个标志位isDelete,逻辑上删除
alter table human add isDelete bit default 0;
//当isDelete为1是默认是删除的,用户查询数据时过滤为1的,比如:
select * from human where isDelete=0;
16.update更新
update human set name="曹操" where id=1;
四. 备份与恢复
1.数据备份
//1. 进入超级管理员
sudo -s
//2. 进入mysql库目录
cd /var/lib/mysql
//3. 运行mysqldump命令
mysqldump –uroot –p 数据库名 > ~/Desktop/备份文件.sql;
按提示输入mysql的密码
- 数据恢复
//1. 连接mysql,创建数据库
//2. 退出连接,执行如下命令(下面的数据库名就是步骤1创建的数据库名)
mysql -uroot –p 数据库名 < ~/Desktop/备份文件.sql
根据提示输入mysql密码
五. 查询
根据这个表中的数据来讲解:
mysql> desc human;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| sex | bit(1) | YES | | b'1' | |
| age | int(11) | NO | | NULL | |
| birthday | datetime | YES | | NULL | |
| isDelete | bit(1) | YES | | b'0' | |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
1.查询xx表所有数据
select * from human;
//显示现有的数据:
mysql> select * from human;
+----+---------+------+-----+---------------------+----------+
| id | name | sex | age | birthday | isDelete |
+----+---------+------+-----+---------------------+----------+
| 4 | 刘备 | 1 | 68 | 0232-05-06 00:00:00 | |
| 5 | 吕布 | 1 | 58 | 0235-05-02 00:00:00 | |
| 6 | 刘备 | 1 | 68 | 0232-05-06 00:00:00 | |
+----+---------+------+-----+---------------------+----------+
2.查询时消除重复行,sex为xx表中的性别属性0/1
select distinct sex from human;
3.根据条件来查询
select * from human where 条件;
3.1 比较运算符,> < = 或组合
select * from human where id<5;
select * from human where name!="吕布";
3.2 逻辑运算符,and or not
select * from human where id>4 and isdelete=0;
3.3 模糊查询,like % _ 或组合
//1. 查找姓吕的
select * from human where name like '吕%';
//2. 查找含有“备”字的:
select * from human where name like '%备%';
//3. 查询姓吕并且名字是一个字的人:
select * from human where name like '吕_';
3.4 范围查询,in between … and …
select * from human where id in(1,3,8);
select * from human where id between 3 and 8;
select * from human where id between 3 and 8 and sex=1;
3.5 空判断 is (not)null
select * from human where birthday is not null;
3.6 优先级 小括号>not>比较运算符>逻辑运算符
4.聚合为查询结果显示
4.1 查询总数
select count(*) from human;
select count(name) from human;
4.2 查询最大值
select max(age) from human;
4.3 查询最小值
select min(id) from human where isdelete=0;
4.4 查询总和
select avg(age) from human where isdelete=0 and sex=0;
4.5 查询平均值
select avg(age) from human where isdelete=0 and sex=1;
5.分组查询
按照字段分组,表示此字段相同的数据会被放到一个组中;分组后,只能查询出相同的数据列,对于有差异的数据列无法出现在结果集中;可以对分组后的数据进行统计,做聚合运算
select 列1,列2,聚合... from 表名 group by 列1,列2,列3...
5.1查询男女生总数,as 性别–>别名
select sex as 性别,count(*)
from human
group by sex;
5.2 having后面的条件运算符与where的相同,查询男生总人数
//方案一
select count(*)
from human
where sex=1;
//方案二:
select sex as 性别,count(*)
from human
group by sex
having sex=1;
6.排序 asc升序,desc降序:
select * from human
where sex=1 and isdelete=0
order by id desc;
7.分页获取, 从下标0开始,获取3条数据
select * from human limit 0,3;
六. 多表操作
1.创建三个表,students,subjects,scores
//学生
create table students(
id int auto_increment primary key not null,
name varchar(10) not null,
sex bit default 1
);
//学科
create table subjects(
id int auto_increment primary key not null,
name varchar(20) not null
);
//分数,最后一条为关联外键的写法
create table scores(
id int auto_increment primary key not null,
score decimal(5,2) not null,
stuid int,
subid int,
foreign key(stuid) references students(id)
);
2.动态插入外键
alter table scores add foreign key(subid) references subjects(id);
//添加外键名字sco_sub,可以根据名字把外键删除掉,通常不删
alter table scores add constraint sco_sub foreign key(subid) references subjects(id);
3.插入多条数据
//students
insert into students values(0,"张三",1),(0,"李四",1),(0,"王五",1),(0,"赵柳",0);
//subjects
insert into subjects(name) values("语文"),("数学"),("英语");
//scores, (0,89.5,1,1)->(id,score,stuid=students(id),subid=sujects(id)),stuid要students中存在的id,subid同理
insert into scores values(0,89.5,1,1),(0,96,1,2),(0,85,1,3),
(0,93.5,2,1),(0,88,2,2),(0,95,2,3),
(0,79,3,1),(0,66,3,2),(0,77,3,3),
(0,55.5,4,1),(0,58,4,2),(0,65,4,3);
3.查询
//1. 查询全部
select * from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;
//2. 根据字段查询,
select students.name,subjects.name,score
from scores
inner join students on students.id=scores.stuid
inner join subjects on subjects.id=scores.subid;
//3. 聚合分组查询,查平均分
select students.name,avg(score)
from scores
inner join students on scores.stuid=students.id
group by students.name;
//4. 聚合分组+条件查询,男的总分; 此处scores.score可以省略scores,因为后面查询的是from scores
select students.name,sum(scores.score)
from scores
inner join students on scores.stuid=students.id
where students.sex=1
group by students.name;
4.视图, 用于结合多表后查询
//1. 创建, 因为students.*里面的字段包含(id,name,sex)和subjects.name重名了,所以要起别名
create view v_1 as
select students.*,subjects.name as title,scores.score from scores
inner join students on students.id=scores.stuid
inner join subjects on subjects.id=scores.subid;
//起别名写法, 表名 as 表别名
create view v_1 as
select stu.*,sub.name as title,sco.score from scores as sco
inner join students as stu on stu.id=sco.stuid
inner join subjects as sub on sub.id=sco.subid;
//2. 查询全部
select * from v_1;
//3. 查询视图中姓名和性别
select name,sex from v_1 where score>70;
5.业务,银行存取,支付等
//1. 开始事物
begin;
//2. 操作过程,比如更新/插入字段: 执行后内存发生变化,但硬件内存没有生效
update students set name='陈二' where id=1;
//3. 提交(或回退=取消)
commit;
//回退
rollback;
6.自关联(多表做成一张表,减少多表的开销)
//1. 创建
create table areas(
id int primary key,
atitle varchar(20),
pid int,
foreign key(pid) references areas(id)
);
//2. 从sql文件中导入数据
source areas.sql;
//3. 查询一共有多少个省,查询省的名称为“山西省”的所有城市
select city.* from areas as city
inner join areas as province on city.pid=province.id
where province.atitle='山西省';
//4. 查询市的名称为“广州市”的所有区县
select dis.*,dis2.* from areas as dis
inner join areas as city on city.id=dis.pid
left join areas as dis2 on dis.id=dis2.pid
where city.atitle='广州市';
七. 服务器上MySQL
1.安装
sudo apt-get install mysql-server mysql-client
2.管理服务
//1. 启动
service mysql start
//2. 停止
service mysql stop
//3. 重启
service mysql restart
3.允许远程连接服务
//1. 找到mysql配置文件, 将bind-address=127.0.0.1注释
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
//2. 登录mysql
mysql -uroot -p
请输入密码:mysql
//3. 执行以下两条命令
grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option;
flush privileges;
//4. 重启MySQL