文章目录
一、数据库的概述
- 数据库作用：数据持久化保存，高可靠，高可用，数据的快速提取。
- 数据库存储类型：关系型数据库 非关系型数据库。
- 数据保存在内存
- 优点：存取速度快
- 缺点：数据不能永久保存
1.关系型数据库
- 其存储结构是二维表格，反映事物及其联系的数据是以表格形式保存的
- 在每个二维表中，每一行称为一条记录，用来描述一个对象的信息，每一列称为一个字段，用来描述对象的一个属性。
1.2、非关系型数据库
- 非关系型数据库存储方式
- 键-值方式(key–value),以键为依据存储、删、改数据
- 列存储(Column-oriented), 将相关的数据存储在列族中
- 文档的方式，数据库由-系列数据项组成，每个数据项都 有名称与对应的值
非关系型数据库(统称NOSQL)：
- redis(内存数据库/缓存数据库)： K-V键值对 key-value 变量名-值
- memcache (内存数据库/缓存数据库) ：K-V键值对
非关系型数据库的优点：
- 数据库高并发读写的需求
- 对海量数据高效率存储与访问
- 数据库的高扩展性与高可用性的需求
二：MySQL
2.1、登录数据库
- 注意MySQL的初始密码为空，通过mysql -u root -p进入回车，输入两次新密码即可设置新密码。
- 登录数据库
- 格式：mysql -h主机地址 -u用户名 -p用户密码
- 注：u与root可以不加空格
[root@server3 ~]# mysql -u root -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
2.2：查看数据库
show datebases;
2.4：查看数据库中的数据表信息
use 数据库名 #切换到库
show tables 查看库中的表
2.5：显示数据表的结构
describe [数据库名.]表名
例如：
describe mysql.time_zone;
或者
先切换到mysql库，再显示表结构。
mysql> describe mysql.time_zone;
+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| Time_zone_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Use_leap_seconds | enum('Y','N') | NO | | N | |
+------------------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
三、SQL语句
3.1、SQL语言分类
1、SQL语言
- 是Structured Query Language的缩写，即结构化查询语言
- 是关系型数据库的标准语言
- 用于维护管理数据库,如数据查询、数据更新、访问控制、对象管理等功能
2、SQL分类
- DDL:数据定义语言
- DML:数据操纵语言
- DQL:数据查询语言
- DCL:数据控制语言
3.2、DDL语句
1、使用DDL语句新建库、表
创建数据库
创建数据表：CREATE TABLE 表名(字段定义…）
CREATE TABLE 表名（字段定义...）
create table 表名（字段01名称 字段01类型 字段01约束，字段02名 字段02类型 字段02约束）存储引擎 字符集
【多个字段用，隔开】
字段01名称：属性名称，自定义
字段01类型：
- int （5） 整型 00000-99999
- double 浮点型
- decimal（5,2） 有效数字是5位，小数点后面保留2位100.00 088.45
- fioat 单精度浮点 4字节
- varchar（50） 可变长度字符串
- char（10） 固定长度字符串
字段01约束：
- 非空约束：内容不允许为空
- 主键约束：非空且唯一 典型的标识
- 默认值：假如没有填数据，默认预先设定的值
- 自增特：id 1 2 3 自动帮你去填
存数引擎：myisam innodb
字符集：UTF-8
mysql> create table info (id int(3) not null primary key,name char(10) not null,address varchar(50) default 'nj');
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| info |
+-------------------+
1 row in set (0.00 sec)
#查看表结构
mysql> describe info;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(3) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| address | varchar(50) | YES | | nj | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
删除指定的数据库：DROP DATABASE 数据库名
mysql> create database tom;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
| tom |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database tom;
Query OK, 0 rows affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student | #tom已删除
| sys |
+--------------------+
5 rows in set (0.00 sec)
3.2：管理表中的数据
- INSERT:插入新数据
- UPDATE：更新原有数据
- DELETE：删除不不需要的数据
3.3：向数据表中插入新的数据记录（DML）
#第一种插入记录的方法：
insert into info (id,name,address) values (2,'lisi','上海'),(3,'wangwu','北京');
#第二种插入记录方式：
insert into info values (4,'yiyi','北京');
mysql> describe info;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(3) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| address | varchar(50) | YES | | nj | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into info (id,name,address) values (2,'lisi','上海'),(3,'tom','杭州');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from info;
+----+------+---------+
| id | name | address |
+----+------+---------+
| 2 | lisi | 上海 |
| 3 | tom | 杭州 |
+----+------+---------+
2 rows in set (0.00 sec)
#第二种方法
mysql> insert into info values (1,'潘玉彬','广州');
Query OK, 1 row affected (0.01 sec)
mysql> select * from info;
+----+-----------+---------+
| id | name | address |
+----+-----------+---------+
| 1 | 潘玉彬 | 广州 |
| 2 | lisi | 上海 |
| 3 | tom | 杭州 |
+----+-----------+---------+
3 rows in set (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| info |
| tom |
+-------------------+
2 rows in set (0.00 sec)
mysql> describe tom;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 未知 | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into tom (id,name,score,address) values (1,'pyb',90.2,'hangzhou');
mysql> select * from tom;
+----+------+-------+----------+
| id | name | score | address |
+----+------+-------+----------+
| 1 | pyb | 90.20 | hangzhou |
+----+------+-------+----------+
这边不加字段名称也可直接添加字段的值（默认是所有字段）
mysql> insert into tom values (2,'lisi',88,'shanghai');
mysql> select * from tom;
+----+------+-------+----------+
| id | name | score | address |
+----+------+-------+----------+
| 1 | pyb | 90.20 | hangzhou |
| 2 | lisi | 88.00 | shanghai |
+----+------+-------+----------+
2 rows in set (0.00 sec)
#default定义未知
mysql> insert into tom (name,score,address) values ('luoli',77,'shanxi'),('meimei',55,default);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from tom;
+----+--------+-------+----------+
| id | name | score | address |
+----+--------+-------+----------+
| 1 | pyb | 90.20 | hangzhou |
| 2 | lisi | 88.00 | shanghai |
| 3 | luoli | 77.00 | shanxi |
| 4 | meimei | 55.00 | 未知 |
+----+--------+-------+----------+
mysql> insert into tom values (5,'wudi',75,'suzhou');
Query OK, 1 row affected (0.01 sec)
mysql> select * from tom;
+----+--------+-------+----------+
| id | name | score | address |
+----+--------+-------+----------+
| 1 | pyb | 90.20 | hangzhou |
| 2 | lisi | 88.00 | shanghai |
| 3 | luoli | 77.00 | shanxi |
| 4 | meimei | 55.00 | 未知 |
| 5 | wudi | 75.00 | suzhou |
+----+--------+-------+----------+
5 rows in set (0.00 sec)
#筛选大于75的信息
mysql> select * from tom where score > 75;
+----+-------+-------+----------+
| id | name | score | address |
+----+-------+-------+----------+
| 1 | pyb | 90.20 | hangzhou |
| 2 | lisi | 88.00 | shanghai |
| 3 | luoli | 77.00 | shanxi |
+----+-------+-------+----------+
3 rows in set (0.00 sec)
#将tom表中大于80的数据，放入新建的jerry表
mysql> create table jerry as select * from tom where score > 80;
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from jerry;
+----+------+-------+----------+
| id | name | score | address |
+----+------+-------+----------+
| 1 | pyb | 90.20 | hangzhou |
| 2 | lisi | 88.00 | shanghai |
+----+------+-------+----------+
2 rows in set (0.00 sec)
#查看jerry表数据 已经克隆过来了
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| jerry |
| tom |
+-------------------+
2 rows in set (0.00 sec)
3.4：修改、更新数据表中的数据记录
update stu set address='杭州' where id=4;
- 3、在数据表中删除指定的数据记录
- delete from 表名 where 条件表达式
delete from stu where name='wangwu';
3.5：设置用户权限的命令
- 若是用户已存在，则会更改用户密码
- 若是用户不存在，则是新建用户
- GRANT 权限列表 ON 数据库名
#创建用户
mysql> grant all privileges on *.* to 'liu'@'loaclhost' identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.06 sec)
#mysql数据库的内置库提取到内存里
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
#登录用户成功
mysql> select user from user;
+---------------+
| user |
+---------------+
| bbsuser |
| root |
| liu |
| bbsuser |
| mysql.session |
| mysql.sys |
| root |
| root |
+---------------+
8 rows in set (0.00 sec)
mysql> grant all privileges on *.* to 'liu'@'localhost' identified by '123123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
#mysql数据库的内置库提取到内存里
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#登录用户成功
[root@server3 ~]# mysql -u liu -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
3.6：在数据库表中删除指定的数据记录
DELETE FPOM 表名 WHERE 条件表达式
mysql> select * from tom;
+----+--------+-------+----------+
| id | name | score | address |
+----+--------+-------+----------+
| 1 | pyb | 55.00 | hangzhou |
| 2 | lisi | 55.00 | shanghai |
| 3 | luoli | 77.00 | shanxi |
| 4 | meimei | 55.00 | 未知 |
| 5 | wudi | 75.00 | suzhou |
+----+--------+-------+----------+
5 rows in set (0.00 sec)
mysql> delete from tom where score <=60;
Query OK, 3 rows affected (0.01 sec)
mysql> select * from tom;
+----+-------+-------+---------+
| id | name | score | address |
+----+-------+-------+---------+
| 3 | luoli | 77.00 | shanxi |
| 5 | wudi | 75.00 | suzhou |
+----+-------+-------+---------+
2 rows in set (0.00 sec)
#真谨慎操作，删除全部内容
mysql> delete from tom;
Query OK, 0 rows affected (0.00 sec）
mysql> select * from tom;
Empty set (0.00 sec)
3.7：DQL是数据查询语句，只有SELECT
用于从数据表中查找符合条件的数据记录
查询时不指定条件
#SELECT 字段名1，字段名2....FROM表名 WHERE 条件表达式
mysql> select name,score from jerry where address='hangzhou';
+------+-------+
| name | score |
+------+-------+
| pyb | 90.20 |
+------+-------+
1 row in set (0.00 sec)
3.8：清空表
- DELETE FROM tablename
- TRUNCATE TABLE tablename
- truncate与drop区别
drop是删除表 truncate 是情况里面的数据
mysql> truncate table jerry;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from jerry;
Empty set (0.00 sec)
mysql>
3.9：临时表
临时建立的表，用于保存一些临时数据，不会长期存在
mysql> create temporary table cdc (id int(3) not null auto_increment,name varchar(10) not null,hobby varchar(10) not null,primary key(id))engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> describe cdc;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| hobby | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into cdc (name,hobby) values ('boy','dog');
Query OK, 1 row affected (0.00 sec)
mysql> select * from cdc;
+----+------+-------+
| id | name | hobby |
+----+------+-------+
| 1 | boy | dog |
+----+------+-------+
1 row in set (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| info |
| jerry |
| tom |
+-------------------+
3 rows in set (0.00 sec)
#不在硬盘上，在内存上
测试临时表断开连接会自动删除
[root@server3 ~]# mysql -u liu -p123321
……省略
mysql> use cdc;
ERROR 1049 (42000): Unknown database 'cdc'
mysql> select * from cdc;
ERROR 1046 (3D000): No database selected
3.10：临时表
3.11：create方法
#查看hei表信息结构
mysql> select * from hei;
+----+--------+-------+-----------+
| id | name | score | address |
+----+--------+-------+-----------+
| 1 | lisi | 44.50 | doongjing |
| 2 | wangwu | 77.00 | xiamen |
+----+--------+-------+-----------+
2 rows in set (0.00 sec)
#查询的语句生成一张新表hh
mysql> create table hh as select * from hei;
#已经生成
mysql> select * from xiao;
+----+------+-------+
| id | name | hobby |
+----+------+-------+
| 1 | q | run |
| 2 | x | fly |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| hai |
| info |
| jerry |
| tom |
| xiao |
+-------------------+
5 rows in set (0.00 sec)
mysql> select xiao;
ERROR 1054 (42S22): Unknown column 'xiao' in 'field list'
mysql> select * from xiao;
+----+------+-------+
| id | name | hobby |
+----+------+-------+
| 1 | q | run |
| 2 | x | fly |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> create table hai as select * from xiao;
mysql> select * from hai;
+----+------+-------+
| id | name | hobby |
+----+------+-------+
| 1 | q | run |
| 2 | x | fly |
+----+------+-------+
2 rows in set (0.00 sec)
#查看hai表结构 可以看出跟xiao表一样
mysql> desc hai;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(3) | NO | | 0 | |
| name | varchar(10) | NO | | NULL | |
| hobby | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
克隆也可分为两部分：第一步复制结构生成表 （结构没有数据）第二步 导入数据
3.12：LIKE方法
从hei表完整复制结构生成gg表
mysql> create table jerry like tom;
Query OK, 0 rows affected (0.00 sec)
#查看所有表
mysql> show tables;
+---------------+
| Tables_in_mei |
+---------------+
| jerry |
| tom |
+---------------+
2 rows in set (0.00 sec)
#新表jerry是没有数据的
mysql> select * from jerry;
Empty set (0.00 sec)
四：数据库用户授权
4.1：查看用户的权限
mysql> show grants for 'root'@'%';
+-------------------------------------------+
| Grants for root@% |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' | # 拥有所有权限
+-------------------------------------------+
1 row in set (0.00 sec)
查看数据库有哪些用户
mysql> select user from mysql.user;
+---------------+
| user |
+---------------+
| bbsuser |
| root |
| liu |
| tom |
| bbsuser |
| liu |
| mysql.session |
| mysql.sys |
| root |
| root |
+---------------+
10 rows in set (0.00 sec)
4.2：撤销用户权限的命令
REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址
mysql> revoke all privileges on *.* from 'root'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'root'@'%';
+----------------------------------+
| Grants for root@% |
+----------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%' |
+----------------------------------+
1 row in set (0.00 sec)
698
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
