mysql数据库之必备知识

mysql核心知识之服务管理

查看mysql的服务进程： ps -ef | grep mysql

service服务管理： cp -a mysql.server /etc/rc.d/init.d/mysql

启动命令： service mysql start or systemctl mysql start

关闭命令 service mysql stop  or systemctl mysql stop

重启命令： service mysql restart or system mysql restart

查看状态命令： service mysql status

登录管理  ln -s /usr/local/mysql/bin/*  /bin

登录命令： mysql -u root -p

默认端口： 3306

配置文件： /etc/my.cnf

mysql 的库表深入解析

• （1） 什么是库

数据仓库

mysql数据库：本质上是一个关系型数据服务管理系统

• （2） 什么是表？

数据库： database

数据表： table

字段（列）：column

行: row

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
 
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> 
mysql> 
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> 

mysql的sql各类语句精讲

DDL 数据定义语言（Data Definition Language）

DDL数据操作语言

DQL数据查询语句

DCL数据控制语言

mysql 核心知识之DDL

mysql 数据库必备知识之创建、查看以及使用/切换

/*mysql数据库必备知识之创建、查看以及使用、切换*/
 
（1） 直接创建数据库 db1
CREATE database db1;
 
(2) 判断是否存在，如果不存在则创建数据库 db2
CREATE database IF NOT EXISTS db2;
 
 
(3) 创建数据库并指定字符集为gbk
CREATE database db3 DEFAULT CHARACTER SET gbk;
 
(4) 查看某个库是什么字符集；
 
SHOW CREATE DATABASE XD;
 
(5) 查看当前mysql 使用的字符集
 
show variables LIKE 'character%';
 
（6） 查看当前数据库
SELECT database();
 
（7） 进入库名
use 库名;

[root@mysqlmaster mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> create database xiaoming;
Query OK, 1 row affected (0.01 sec)

mysql> create database xiaoming;
ERROR 1007 (HY000): Can't create database 'xiaoming'; database exists
mysql>
mysql> create database if not exists xiaoming;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> create database db3 default character set gbk;
Query OK, 1 row affected (0.00 sec)

mysql> show create database xiaoming;
+----------+---------------------------------------------------------------------+
| Database | Create Database                                                     |
+----------+---------------------------------------------------------------------+
| xiaoming | CREATE DATABASE `xiaoming` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> show create database db3;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| db3      | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

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

mysql> show create database xiaohong;
+----------+---------------------------------------------------------------------+
| Database | Create Database                                                     |
+----------+---------------------------------------------------------------------+
| xiaohong | CREATE DATABASE `xiaohong` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show variables LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.12 sec)

mysql>

mysql 创建表之常用数据类型

（1） 数据类型是什么？

数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。有一些数据是要存储为数字的，数字当中有些是要存储为整数、小数、日期型等

（2）mysql常见数据类型

（1） 整数型               

（2） 浮点型

（3） 定点型

（4）字符串型

（5）时间类型

mysql数据库必备知识值创建表

语法：

CREATE TABLE table_name(
column1 type1 CONSTRAINT1 description1,
column2 type2 CONSTRAINT2 description2,
column3 type3 CONSTRAINT3 description3,
);
 
CONSTRAINT CONDITION :
 
comment --- description
NOT NULL   -- NOT NULL
DEFAULT   -- DEFAULT value
unsigned   -- unsigned (int)
auto_increment  -- increase automatically
zerofill  ---  fillment automatically
UNIQUE KEY  --- UNIQUE value

mysql数据库必备知识之查看

查看数据库中的所有表 ： show tables;

查看表结构： DESC table_name;

查看创建表的sql语句：
show CREATE TABLE table_name;
\G : 有结束sql语句的作用，也有显示数据纵向旋转90度
\g : 有结束sql语句的作用

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db3                |
| mysql              |
| performance_schema |
| sys                |
| xiaohong           |
| xiaoming           |
+--------------------+
7 rows in set (0.00 sec)
 
mysql> use xiaohong
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> show tables;
+--------------------+
| Tables_in_xiaohong |
+--------------------+
| student            |
+--------------------+
1 row in set (0.00 sec)
 
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | tinyint(4)  | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | tinyint(4)  | NO   |     | NULL    |       |
| class | varchar(20) | NO   |     | NULL    |       |
| sex   | char(5)     | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.03 sec)
 
mysql> show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` tinyint(4) NOT NULL COMMENT 'student number',
  `name` varchar(20) NOT NULL COMMENT 'student name',
  `age` tinyint(4) NOT NULL COMMENT 'student age',
  `class` varchar(20) NOT NULL COMMENT 'student class',
  `sex` char(5) NOT NULL COMMENT 'student sex'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
 
mysql> CREATE TABLE teacher AS SELECT * FROM student WHERE 1=2;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show tables;
+--------------------+
| Tables_in_xiaohong |
+--------------------+
| student            |
| teacher            |
+--------------------+
2 rows in set (0.01 sec)
 
mysql> desc teacher;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | tinyint(4)  | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | tinyint(4)  | NO   |     | NULL    |       |
| class | varchar(20) | NO   |     | NULL    |       |
| sex   | char(5)     | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
 
mysql> show create table teacher\G
*************************** 1. row ***************************
       Table: teacher
Create Table: CREATE TABLE `teacher` (
  `id` tinyint(4) NOT NULL COMMENT 'student number',
  `name` varchar(20) NOT NULL COMMENT 'student name',
  `age` tinyint(4) NOT NULL COMMENT 'student age',
  `class` varchar(20) NOT NULL COMMENT 'student class',
  `sex` char(5) NOT NULL COMMENT 'student sex'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
 
mysql> 

mysql数据库必备知识之表结构维护与删除

（1） CHANGE TABLE name
RENAME TABLE old_tab_name TO new_tab_name;
 
(2) ADD COLUMN
 
ADD a COLUMN : 
 
ALTER TABLE tab_name ADD column_name TYPE;
ALTER TABLE ADD COLUMN_name TYPE comment 'description';
 
ADD a COLUMN IN TABLE FIRST:
ALTER TABLE tab_name ADD column_name TYPE FIRST;
 
ADD a COLUMN IN TABLE LAST:
ALTER TABLE tab_name ADD column_name TYPE AFTER column_name;
 
(3) MODIFY COLUMN TYPE
ALTER TABLE tab_name MODIFY column_name NEW_type;
 
(4) MODIFY COLUMN name
ALTER TABLE tab_name CHANGE OLD_column_name new_column_name TYPE;
 
(5) DELETE COLUMN name
ALTER TABLE tab_name DROP column_name;
 
(6) MODIFY CHARACTER collection
ALTER TABLE tab_name CHARACTER SET character_collection;
 
(7) mysql tables delete
drop table tab_name;
DROP TABLE IF EXISTS tab_name;