MySql基本操作

一、数据库相关介绍

 数据库（database）是用来组织、存储和管理数据的仓库

数据库管理系统（DBMS）：是实现对数据有效组织，管理和存取的系统软件。

数据的建立和维护功能，数据定义功能，数据操纵功能，数据库的运行管理功能，通信功能 

数据库系统是一个人机系统，由硬件，os，数据库，DBMS ，应用软件和数据库用户组成。

用户可以通过DBMS 或则 应用程序来操作数据库 。

数据库主要分两大类：  关系型数据库 SQL      非关系型数据库 NoSQL

关系型数据库：

关系数据库的存储结构是二维表格 ，表是有行和列。行称为记录，是用来描述一个对象的信息。列称为字段，是用来描述对象的一个属性
存储结构：二维表格  

存储的数据：结构化数据    

使用场景：存储业务数据、账户信息等
对象： 库 ->  表 -> 二维表格形式的结构化数据     行（记录）：用来描述一个对象的信息
                                                                               列（字段）：用来描述对象的一个属性

关系性数据库常见的有：MYSQL ，Oracle，SQL-Server，MariaDB， postgreSQL 

  非关系型数据库：

非关系型数据库（NoSQL）  存储结构：非二维表格结构，不同类型的NoSQL采用不同的存储结构，比如 键值对、文档、索引、时间序列 等
缓存型   Redis   Memcached
文档型   MongoDB
索引型   Elasticsearch
时序型   Prometheus   InfluxDB
列式型   HBase

二、关系数据库的常用操作

1、安装mysql

将安装mysql 所需软件包传到/opt目录下
mysql-5.7.17.tar.gz
boost_1_59_0.tar.gz		

 
确认安装 gcc 、 gcc-c++ 、make、cmake 
ncurses-devel、（字符终端屏幕控制基本库）
bison、（语法分析器）
libaio-devel （支持同步I/O）的软件包
yum -y install gcc gcc-c++ make ncurses ncurses-devel bison cmake libaio-devel
yum install autoconf -y     ####安装5.6版本是需要安装autoconf库
 
---------------添 加mysql用户并加入到mysql组----------------
 
useradd  -s /sbin/nologin mysql
mkdir -p /usr/local/mysql
 
tar zxvf mysql-5.5.24.tar.gz -C /opt/
 
cd   mysql-5.5.24
 
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
 -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_DATADIR=/home/mysql \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306                               
 
指定安装路径
指定家目录  sock 数据库连接文件
指定字符集
指定字符编码
扩展性的字符集 支持其他的字符集
开启各种功能的存储引擎机
存储引擎机
存储引擎机
支持readline程序平台 读取数据按行读取，一行是一个对象
本地写入文件，方便导入导出
数据库的家目录  数据库存放的路径
指定管理用户和端口号
 
make -j 2 && make install  
 
------如果报错，清空缓存文件-如果正常可忽略-------
find -name CMakeCache.txt
rm -f ./CMakeCache.txt                          
---------------------------------
 
chown -R mysql:mysql /usr/local/mysql     
 
 
 
export PATH=$PATH:/usr/local/mysql/bin/   \*开机时刷新*\          
 
或者可选择vi /etc/profile 在最后一行加入后 运行source /etc/profile 
 
cp support-files/my-medium.cnf /etc/my.cnf                  
或
cp support-files/my-default.cnf /etc/my.cnf
 
cp support-files/mysql.server /etc/init.d/mysqld              
 
chmod 755 /etc/init.d/mysqld                             
chkconfig --add /etc/init.d/mysqld                      
chkconfig mysqld --level 35 on                                  
 
--------------初始化数据库------------------------------------------------
/usr/local/mysql/scripts/mysql_install_db \
--user=mysql \
--ldata=/var/lib/mysql \
--basedir=/usr/local/mysql \
--datadir=/home/mysql
 
ln -s /var/lib/mysql/mysql.sock /home/mysql/mysql.sock           /*直接建立软连接*/
 
vi /etc/init.d/mysqld      #####46行和47行添加
basedir=/usr/local/mysql
datadir=/home/mysql
 
service mysqld start
netstat -tnl 3306
mysql    #####初始登录mysql
grant all privileges on *.* to 'root'@'%' identified by 'abc123';    ### ##授予root用户可以在所有终端远程登录，使用的密码是abc123，并对所有数据库和所有表有操作权限。
 
 
#数据库密码设置
mysqladmin -u root password "abc123"
---------------------------------------------------------------
mysql -u root  -p  
abc123
mysql>show databases;

2、SQL语句  关系型数据库专用的操作管理语句 

用于管理数据对象（库、表、索引 等）

1、创建及删除数据库和表

查看当前的 MySQL 版本信息及连接用户名

SELECT VERSION();
SELECT USER();

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.44    |
+-----------+
1 row in set (0.00 sec)

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)

查看当前服务器中的数据库

SHOW DATABASES;                        #大小写不区分，分号“;”表示结束

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

 MySQL会附带4个系统数据库：
information_schema：主要存储系统中的一些数据库对象信息，如数据库名，数据库的表，表栏的数据类型与访问权限等
performance_schema：主要存储数据库服务器性能参数信息
mysql：主要存储系统的用户权限信息
sys：MySQL5.7之后引入的一个新的 sys 数据库，sys 库里面的表、视图、函数以及存储过程可以让用户快速了解MySQL的一些信息，它的数据来源于performance_schema

查看数据库中表信息

USE 数据库名;            //先切换到数据库
SHOW TABLES;         //再查看表

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)



mysql> show tables from mysql;  #效果同上

查看表的结构

USE 数据库名;
DESCRIBE [数据库名.]表名;
可缩写成：DESC 表名;

创建新的数据库

CREATE DATABASE 数据库名;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database student_information;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| mysql               |
| performance_schema  |
| student_information |
| sys                 |
+---------------------+
5 rows in set (0.00 sec)

创建新的表

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。

mysql> create table aaa(id int, name varchar(20), sex varchar(2), age int, primary key(id));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------------------+
| Tables_in_student_information |
+-------------------------------+
| aaa                           |
+-------------------------------+
1 row in set (0.00 sec)

mysql> desc aaa;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(2)  | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

2、管理表中的数据记录

删除指定的数据表

DROP TABLE [数据库名.]表名;                #如不用USE进入库中，则需加上数据库名

删除指定的数据库

DROP DATABASE 数据库名;

向数据表中插入新的数据记录

INSERT INTO 表名(字段1,字段2[,...]) VALUES(字段1的值,字段2的值,...);

mysql> select * from aaa;
Empty set (0.00 sec)

mysql> insert into student_information.aaa(id, name, sex, age) values(1, 'zhangsan', '男', 28);
Query OK, 1 row affected (0.00 sec)

mysql> select * from aaa;
+----+----------+------+------+
| id | name     | sex  | age  |
+----+----------+------+------+
|  1 | zhangsan | 男   |   28 |
+----+----------+------+------+
1 row in set (0.00 sec)

mysql> 

查询语句

SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];

mysql> select * from aaa;
+----+----------+------+------+
| id | name     | sex  | age  |
+----+----------+------+------+
|  1 | zhangsan | 男   |   28 |
|  2 | 王五     | 男   |   29 |
|  3 | 李四     | 男   |   29 |
|  4 | a        | 女   |   19 |
|  5 | b        | 女   |   18 |
+----+----------+------+------+
5 rows in set (0.00 sec)

mysql> select * from aaa where sex='女';
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  4 | a    | 女   |   19 |
|  5 | b    | 女   |   18 |
+----+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from aaa where id=4;
+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  4 | a    | 女   |   19 |
+----+------+------+------+
1 row in set (0.00 sec)

mysql> select * from aaa where id>2 and id <=5;
+----+--------+------+------+
| id | name   | sex  | age  |
+----+--------+------+------+
|  3 | 李四   | 男   |   29 |
|  4 | a      | 女   |   19 |
|  5 | b      | 女   |   18 |
+----+--------+------+------+
3 rows in set (0.00 sec)

横向按行显示数据 

mysql> select * from aaa limit 1,3;  //查询从第一行后一行开始的三行内容，即2~4行
+----+--------+------+------+
| id | name   | sex  | age  |
+----+--------+------+------+
|  2 | 王五   | 男   |   29 |
|  3 | 李四   | 男   |   29 |
|  4 | a      | 女   |   19 |
+----+--------+------+------+
3 rows in set (0.00 sec)

纵向按列显示数据

mysql> select * from aaa limit 1,3\G;
*************************** 1. row ***************************
  id: 2
name: 王五
 sex: 男
 age: 29
*************************** 2. row ***************************
  id: 3
name: 李四
 sex: 男
 age: 29
*************************** 3. row ***************************
  id: 4
name: a
 sex: 女
 age: 19
3 rows in set (0.00 sec)

修改、更新数据表中的数据记录

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];

mysql> select * from aaa;
+----+----------+------+------+
| id | name     | sex  | age  |
+----+----------+------+------+
|  1 | zhangsan | 男   |   28 |
|  2 | 王五     | 男   |   29 |
|  3 | 李四     | 男   |   29 |
|  4 | a        | 女   |   19 |
|  5 | b        | 女   |   18 |
+----+----------+------+------+
5 rows in set (0.00 sec)

mysql> update aaa set sex='女' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from aaa;
+----+----------+------+------+
| id | name     | sex  | age  |
+----+----------+------+------+
|  1 | zhangsan | 女   |   28 |
|  2 | 王五     | 男   |   29 |
|  3 | 李四     | 男   |   29 |
|  4 | a        | 女   |   19 |
|  5 | b        | 女   |   18 |
+----+----------+------+------+
5 rows in set (0.00 sec)

在数据表中删除指定的数据记录

DELETE FROM 表名 [WHERE 条件表达式];

mysql> select * from aaa;
+----+----------+------+------+
| id | name     | sex  | age  |
+----+----------+------+------+
|  1 | zhangsan | 女   |   28 |
|  2 | 王五     | 男   |   29 |
|  3 | 李四     | 男   |   29 |
|  4 | a        | 女   |   19 |
|  5 | b        | 女   |   18 |
+----+----------+------+------+
5 rows in set (0.00 sec)

mysql> delete from aaa where id=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from aaa;
+----+----------+------+------+
| id | name     | sex  | age  |
+----+----------+------+------+
|  1 | zhangsan | 女   |   28 |
|  3 | 李四     | 男   |   29 |
|  4 | a        | 女   |   19 |
|  5 | b        | 女   |   18 |
+----+----------+------+------+
4 rows in set (0.00 sec)

3、修改表名和表结构

修改表名
ALTER TABLE 旧表名 RENAME 新表名;

mysql> alter table aaa rename bbb;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------------------+
| Tables_in_student_information |
+-------------------------------+
| bbb                           |
+-------------------------------+
1 row in set (0.00 sec)

扩展表结构（增加字段）

ALTER TABLE 表名 ADD address varchar(50) default '地址不详';
#default '地址不详'：表示此字段设置默认值 地址不详；可与 NOT NULL 配合使用

mysql> alter table bbb add class varchar(20) default '三班';
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc bbb;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(2)  | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| class | varchar(20) | YES  |     | 三班    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select * from bbb;
+----+----------+------+------+--------+
| id | name     | sex  | age  | class  |
+----+----------+------+------+--------+
|  1 | zhangsan | 女   |   28 | 三班   |
|  3 | 李四     | 男   |   29 | 三班   |
|  4 | a        | 女   |   19 | 三班   |
|  5 | b        | 女   |   18 | 三班   |
+----+----------+------+------+--------+
4 rows in set (0.00 sec)

修改字段（列）名，添加唯一键

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];

mysql> alter table bbb change class stu_class varchar(20) unique key;
ERROR 1062 (23000): Duplicate entry '三班' for key 'stu_class'
mysql> alter table bbb change id b_id varchar(20) unique key;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from bbb;
+------+----------+------+------+--------+
| b_id | name     | sex  | age  | class  |
+------+----------+------+------+--------+
| 1    | zhangsan | 女   |   28 | 三班   |
| 3    | 李四     | 男   |   29 | 三班   |
| 4    | a        | 女   |   19 | 三班   |
| 5    | b        | 女   |   18 | 三班   |
+------+----------+------+------+--------+
4 rows in set (0.00 sec)

删除字段

ALTER TABLE 表名 DROP 字段名;

mysql> select * from bbb;
+------+----------+------+------+--------+
| b_id | name     | sex  | age  | class  |
+------+----------+------+------+--------+
| 1    | zhangsan | 女   |   28 | 三班   |
| 3    | 李四     | 男   |   29 | 三班   |
| 4    | a        | 女   |   19 | 三班   |
| 5    | b        | 女   |   18 | 三班   |
+------+----------+------+------+--------+
4 rows in set (0.00 sec)

mysql> alter table bbb drop class;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from bbb;
+------+----------+------+------+
| b_id | name     | sex  | age  |
+------+----------+------+------+
| 1    | zhangsan | 女   |   28 |
| 3    | 李四     | 男   |   29 |
| 4    | a        | 女   |   19 |
| 5    | b        | 女   |   18 |
+------+----------+------+------+
4 rows in set (0.00 sec)

给表中添加主键（一个表中只有一个主键）

mysql> desc ccc;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table ccc add primary key(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc ccc;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除主键

mysql> alter table ccc drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用MySQL的六大约束属性

主键约束    primary key     字段的值不能重复，且不能为NULL，一个表只能有一个主键
唯一键约束  unique key      字段的值不能重复，但可以为NULL，一个表可以有多个唯一键
非空约束    not null        字段的值不能为NULL
默认值约束  default 'XXX'   字段的值如果没有设置值则使用默认值自动填充
自增约束    auto_increment  字段的值如果没有设置值会默认从开始每行自动递增 1 ，要求设置自增的字段必须为主键字段
外键约束    foreign key     用来保证外键表和主键表相关联数据的完整性和一致性

mysql> create table ddd(id int primary key auto_increment, name varchar(20) not null, sex char(2), age int);
Query OK, 0 rows affected (0.01 sec)

mysql> desc ddd
    -> desc ddd;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc ddd' at line 2
mysql> desc ddd;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| sex   | char(2)     | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)