mysql的简单基本操作

作者: _琳哥 | 来源:发表于2017-09-22 22:51 被阅读23次

安装

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))

mysql的简单基本操作

安装

获取默认安装的密码

启动服务

停止服务

重启服务

允许数据库远程连接

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

2.登录mysql，运行命令

3.重启mysql

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

约束

使用命令连接数据库

创建数据库

删除数据库

切换数据库

查看当前选择的数据库

表操作

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

创建表

查看表结构

修改表

删除表

更改表名称

查看表的创建语句

数据操作

查询

增加

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

修改

删除

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

备份与恢复

数据备份

进入超级管理员

进入mysql库目录

运行mysqldump命令

数据恢复

连接mysqk，创建数据库

退出连接，执行如下命令

高级查询

基本查询

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

条件

比较运算符

逻辑运算符

模糊查询

范围查询

空判断

优先级

聚合

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

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

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

sum(列)表示求此列的和

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

分组

分组后的数据筛选

对比where与having

排序

获取部分行 (分页)

完整的sql语句

数据库高级部分

关系

外键

连接

自引用

视图

事务

事务语句

索引

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读