mysql的简单基本操作

安装

sudo apt-get install mysql-server mysql-client

获取默认安装的密码

sudo grep mysql_root_passwd /root/env.txt

启动服务

service mysql start

停止服务

service mysql stop

重启服务

service mysql restart

允许数据库远程连接

1.找到mysql配置文件并修改

 sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
 将bind-address=127.0.0.1注释

2.登录mysql，运行命令

grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option;
flush privileges;

3.重启mysql

在mysql中包含的数据类型很多，这里主要列出来常用的几种

数字：int,decimal
字符串：varchar,text
日期：datetime
布尔：bit

约束

主键 primary key
非空 not null
惟一 unique
默认 default
外键 foreign key

使用命令连接数据库

mysql -uroot -p

查看版本：select version();

创建数据库

mysql> create database 数据库名 charset=utf8;

删除数据库

mysql> drop database 数据库名;

切换数据库

mysql> use 数据库名;

查看当前选择的数据库

mysql> show databases;

表操作

显示当前数据库中的所有表

mysql> show tables;

创建表

auto_increment表示自动增长

create table 表名(列及类型);如：
mysql> create table students( id int auto_increment primary key not null, name varchar(10) not null, gender bit default 1, birthday datetime);

查看表结构

desc 表名; 例如：
mysql> desc students;

修改表

alter table 表名 add|change|drop 列名 类型;
如：
mysql> alter table students add isDelete bit default 0;

删除表

drop table 表名;

更改表名称

rename table 原表名 to 新表名;

查看表的创建语句

show create table 表名;
例如：mysql> show create table students;

数据操作

查询

select * from 表名

增加

全列插入：insert into 表名 values(...)
例如： mysql> insert into students values(0,'张三',1,'1990-7-7',0);

缺省插入：insert into 表名(列1,...) values(值1,...)
例如：mysql> insert into students(name) values('李四');
      mysql> insert into students(gender,name) values(0,'小龙女');

同时插入多条数据：insert into 表名 values(...),(...)...;
例如：mysql> insert into students(name) values('王五'),('董永');
  或insert into 表名(列1,...) values(值1,...),(值1,...)...;

主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功后以实际数据为准

修改

update 表名 set 列1=值1,... where 条件
例如：mysql> update students set birthday='1990-2-2' where id=2;
      mysql> update students set gender=0,birthday='2017-9-21' where id=4;

删除

delete from 表名 where 条件
例如：mysql> delete from students where id=5;

truncate table 表名  (这种方式删除数据后 重新插入的数据id从1开始)

逻辑删除，本质就是修改操作update

如果需要删除则

update students isdelete=1 where ...;
例如 ： mysql> update students set isDelete= 1 where id=4;
  mysql> select * from students where isDelete=0;   通过添加条件查找没有删除项

备份与恢复

数据备份

进入超级管理员

sudo -s

进入mysql库目录

cd /var/lib/mysql

运行mysqldump命令

mysqldump –uroot –p 数据库名 > ~/Desktop/备份文件.sql;
按提示输入mysql的密码
例如： root@ubuntu:/var/lib/mysql# mysqldump -uroot -p python3 > ~/Desktop/bak.sql

数据恢复

连接mysqk，创建数据库

退出连接，执行如下命令

mysql -uroot –p 数据库名 < ~/Desktop/备份文件.sql
根据提示输入mysql密码
例如：lin@ubuntu:~/Desktop$ mysql -uroot -p py31 < bak.sql

高级查询

基本查询

select * from 表名
例如：mysql> select * from students;
或者查询部分：
mysql> select id,name from students;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张三      |
|  2 | 小龙女    |
|  3 | 王五      |
|  4 | 董永      |
|  5 | 张三      |
+----+-----------+
5 rows in set (0.01 sec)

去除重复行 (distinct 比较的是相同行)

mysql> select distinct name from students;
+-----------+
| name      |
+-----------+
| 张三      |
| 小龙女    |
| 王五      |
| 董永      |
+-----------+
4 rows in set (0.00 sec)

mysql> select distinct id,name from students;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张三      |
|  2 | 小龙女    |
|  3 | 王五      |
|  4 | 董永      |
|  5 | 张三      |
+----+-----------+
5 rows in set (0.00 sec)

条件

select * from 表名 where 条件; (条件结果为true的行才会出现在结果集中)

比较运算符

等于=
大于>
大于等于>=
小于<
小于等于<=
不等于!=或<>
查询编号大于3的学生

mysql> select id,name from students where id<3;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张三      |
|  2 | 小龙女    |
+----+-----------+
2 rows in set (0.02 sec)

逻辑运算符

mysql> select id,name from students where id<3 and gender=0;
+----+-----------+
| id | name      |
+----+-----------+
|  2 | 小龙女    |
+----+-----------+
1 row in set (0.00 sec)

模糊查询

like
%表示任意多个任意字符
_表示一个任意字符

mysql> select id,name from students;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张三      |
|  2 | 小龙女    |
|  3 | 王五      |
|  4 | 董永      |
|  6 | 张世界    |
|  7 | 张五      |
+----+-----------+
6 rows in set (0.00 sec)

mysql> select id,name from students where name like '张%';
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张三      |
|  6 | 张世界    |
|  7 | 张五      |
+----+-----------+
3 rows in set (0.00 sec)

mysql> select id,name from students where name like '张_';
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  7 | 张五   |
+----+--------+
2 rows in set (0.00 sec)

范围查询

in表示在一个非连续的范围内

mysql> select id,name from students where id in(1,3,6);
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张三      |
|  3 | 王五      |
|  6 | 张世界    |
+----+-----------+
3 rows in set (0.00 sec)

between ... and ...表示在一个连续的范围内

mysql> select id,name from students where id between 2 and 4;
+----+-----------+
| id | name      |
+----+-----------+
|  2 | 小龙女    |
|  3 | 王五      |
|  4 | 董永      |
+----+-----------+
3 rows in set (0.00 sec)

空判断

注意：null与''是不同的
判空 is null

mysql> select id,name,birthday from students where birthday is null;
+----+-----------+----------+
| id | name      | birthday |
+----+-----------+----------+
|  2 | 小龙女    | NULL     |
|  3 | 王五      | NULL     |
|  4 | 董永      | NULL     |
+----+-----------+----------+
3 rows in set (0.00 sec)

mysql> select id,name,birthday from students where birthday is not null;
+----+-----------+---------------------+
| id | name      | birthday            |
+----+-----------+---------------------+
|  1 | 张三      | 1990-01-01 00:00:00 |
|  6 | 张世界    | 1990-01-02 00:00:00 |
|  7 | 张五      | 1990-01-02 00:00:00 |
+----+-----------+---------------------+
3 rows in set (0.00 sec)

优先级

小括号，not，比较运算符，逻辑运算符
and比or先运算，如果同时出现并希望先算or，需要结合()使用

聚合

为了快速得到统计数据，提供了5个聚合函数

count(*)表示计算总行数，括号中写星与列名，结果是相同的

mysql> select count(*) from students;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.01 sec)

max(列)表示求此列的最大值

mysql> select max(id) from students where gender=0;
+---------+
| max(id) |
+---------+
|       7 |
+---------+
1 row in set (0.00 sec)

min(列)表示求此列的最小值

mysql> select min(id) from students where gender=0;
+---------+
| min(id) |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

sum(列)表示求此列的和

mysql> select sum(id) from students where gender=1;
+---------+
| sum(id) |
+---------+
|       8 |
+---------+
1 row in set (0.00 sec)

avg(列)表示求此列的平均值

mysql> select avg(id) from students where gender=0;
+---------+
| avg(id) |
+---------+
|  5.0000 |
+---------+
1 row in set (0.00 sec)

分组

按照字段分组，表示此字段相同的数据会被放到一个组中
分组后，只能查询出相同的数据列，对于有差异的数据列无法出现在结果集中
可以对分组后的数据进行统计，做聚合运算

select 列1,列2,聚合... from 表名 group by 列1,列2,列3...

mysql> select * from students;
+----+-----------+--------+---------------------+----------+
| id | name      | gender | birthday            | isDelete |
+----+-----------+--------+---------------------+----------+
|  1 | 张三      | 1      | 1990-07-07 00:00:00 |          |
|  2 | 李四      | 1      | 1990-02-02 00:00:00 |          |
|  3 | 小龙女    |        | NULL                |          |
|  4 | 王五      |        | 2017-09-21 00:00:00 | 1        |
+----+-----------+--------+---------------------+----------+
4 rows in set (0.00 sec)

mysql> select gender as  gender,count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
|        |        2 |
| 1      |        2 |
+--------+----------+
2 rows in set (0.04 sec)

分组后的数据筛选

select 列1,列2,聚合... from 表名
group by 列1,列2,列3...
having 列1,...聚合...

mysql> select gender,count(*) from students group by gender having gender=1;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 1      |        2 |
+--------+----------+
1 row in set (0.00 sec)

对比where与having

where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
having是对group by的结果进行筛选

排序

select * from 表名
order by 列1 asc|desc,列2 asc|desc,...

将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
默认按照列值从小到大排列
asc从小到大排列，即升序
desc从大到小排序，即降序

按id升序
mysql> select id,name from students order by id;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张三      |
|  2 | 李四      |
|  3 | 小龙女    |
|  4 | 王五      |
+----+-----------+
4 rows in set (0.00 sec)

按男生学号降序
mysql> select id,name from students where gender = 1 order by id desc;  
+----+--------+
| id | name   |
+----+--------+
|  2 | 李四   |
|  1 | 张三   |
+----+--------+
2 rows in set (0.00 sec)

获取部分行 (分页)

select * from 表名 limit start,count
从start开始，获取count条数据
start索引从0开始

mysql> select id,name from students limit 2,1;
+----+-----------+
| id | name      |
+----+-----------+
|  3 | 小龙女    |
+----+-----------+
1 row in set (0.00 sec)

分页
select * from students
where isdelete=0
limit (n-1)*m,m

完整的sql语句

select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit star,count

执行顺序为：
from 表名
where ....
group by ...
select distinct *
having ...
order by ...
limit star,count

数据库高级部分

关系

外键

创建成绩表
mysql> create table scores(
    -> id int primary key auto_increment not null,
    -> score decimal(4,1),
    -> stuid int,
    -> subid int,
    -> foreign key(stuid) references students(id),
    -> foreign key(subid) references subjects(id));

级联操作的类型包括：

restrict（限制）：默认值，抛异常
cascade（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删除
set null：将外键设置为空
no action：什么都不做

连接

连接查询分类如下：

表A inner join 表B：表A与表B匹配的行会出现在结果中
表A left join 表B：表A与表B匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充
表A right join 表B：表A与表B匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充
在查询或条件中推荐使用“表名.列名”的语法
如果多个表中列名不重复可以省略“表名.”部分
如果表的名称太长，可以在表名后面使用' as 简写名'或' 简写名'，为表起个临时的简写名称

mysql> select students.name, subjects.title,scores.score 
from scores 
inner join students on scores.stuid=students.id 
inner join subjects on scores.subid=subjects.id;

+-----------+--------+-------+
| name      | title  | score |
+-----------+--------+-------+
| 张三      | python | 100.0 |
| 李四      | python |  99.0 |
| 小龙女    | python |  71.0 |
| 张三      | linux  |  90.0 |
| 李四      | linux  |  79.0 |
| 小龙女    | linux  |  89.0 |
| 张三      | java   |  93.0 |
| 李四      | java   |  91.0 |
| 小龙女    | java   |  95.0 |
+-----------+--------+-------+
9 rows in set (0.01 sec)

mysql> select students.name,subjects.title,scores.score from students 
    -> inner join scores on students.id=scores.stuid
    -> inner join subjects on scores.subid=students.id;
+-----------+--------+-------+
| name      | title  | score |
+-----------+--------+-------+
| 张三      | python | 100.0 |
| 张三      | linux  | 100.0 |
| 张三      | java   | 100.0 |
| 张三      | redis  | 100.0 |
| 李四      | python |  79.0 |
| 李四      | linux  |  79.0 |
| 李四      | java   |  79.0 |
| 李四      | redis  |  79.0 |
| 小龙女    | python |  95.0 |
| 小龙女    | linux  |  95.0 |
| 小龙女    | java   |  95.0 |
| 小龙女    | redis  |  95.0 |
+-----------+--------+-------+
12 rows in set (0.00 sec)

自引用

mysql> create table areas(id int primary key auto_increment not null,
    -> title varchar(20),pid int,
    -> foreign key(pid) references areas(id));

视图

mysql> create view v_1 as 
    -> select stu.*,sco.score,sub.title from scores as sco
    -> inner join students as stu on sco.stuid=stu.id 
    -> inner join subjects as sub on sco.subid=sub.id;

视图的用途就是查询

mysql> select * from v_1;
+----+-----------+--------+---------------------+-------+--------+
| id | name      | gender | birthday            | score | title  |
+----+-----------+--------+---------------------+-------+--------+
|  1 | 张三      | 1      | 1990-01-01 00:00:00 | 100.0 | python |
|  2 | 小龙女    |        | NULL                |  90.0 | python |
|  3 | 王五      | 1      | NULL                |  85.0 | python |
|  1 | 张三      | 1      | 1990-01-01 00:00:00 |  99.0 | kotlin |
|  2 | 小龙女    |        | NULL                |  93.0 | kotlin |
|  3 | 王五      | 1      | NULL                |  81.0 | kotlin |
|  1 | 张三      | 1      | 1990-01-01 00:00:00 |  71.0 | php    |
|  2 | 小龙女    |        | NULL                |  95.0 | php    |
|  3 | 王五      | 1      | NULL                |  79.0 | php    |
+----+-----------+--------+---------------------+-------+--------+

事务

当一个业务逻辑需要多个sql完成时，如果其中某条sql语句出错，则希望整个操作都退回
使用事务可以完成退回的功能，保证业务逻辑的正确性
事务四大特性(简称ACID)
- 2.1 原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行
- 2-2 一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致
- 2-3 隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的
- 2-4 持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障
要求：表的类型必须是innodb或bdb类型，才可以对此表使用事务

事务语句

开启begin;
提交commit;
回滚rollback;

索引

create index 索引名 on 表名(列名(length))

最后编辑于：2017.12.21 18:33:18

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,772评论 6赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,458评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,610评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,640评论 1赞 276
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,657评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,590评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,962评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,631评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,870评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,611评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,704评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,386评论 4赞 319
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,969评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,944评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,179评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 44,742评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,440评论 2赞 342

mysql的简单基本操作

安装

获取默认安装的密码

启动服务

停止服务

重启服务

允许数据库远程连接

1.找到mysql配置文件并修改

2.登录mysql，运行命令

3.重启mysql

在mysql中包含的数据类型很多，这里主要列出来常用的几种

约束

使用命令连接数据库

创建数据库

删除数据库

切换数据库

查看当前选择的数据库

表操作

显示当前数据库中的所有表

创建表

查看表结构

修改表

删除表

更改表名称

查看表的创建语句

数据操作

查询

增加

主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功后以实际数据为准

修改

删除

逻辑删除，本质就是修改操作update

备份与恢复

数据备份

进入超级管理员

进入mysql库目录

运行mysqldump命令

数据恢复

连接mysqk，创建数据库

退出连接，执行如下命令

高级查询

基本查询

去除重复行 (distinct 比较的是相同行)

条件

比较运算符

逻辑运算符

模糊查询

范围查询

空判断

优先级

聚合

count(*)表示计算总行数，括号中写星与列名，结果是相同的

max(列)表示求此列的最大值

min(列)表示求此列的最小值

sum(列)表示求此列的和

avg(列)表示求此列的平均值

分组

分组后的数据筛选

对比where与having

排序

获取部分行 (分页)

完整的sql语句

数据库高级部分

关系

外键

连接

自引用

视图

事务

事务语句

索引

推荐阅读更多精彩内容