mysql的简单基本操作
2017-09-22 本文已影响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)
逻辑运算符
- and
- or
- not
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))
