一：数据库的特点：
1：持久化存储
2：读写速度极快
3：保证数据有效性。
数据库是来干啥的？？
存储和管理数据。
二：数据库的分类：
1：关系型数据库：MySQL ，SQL Server， Oracle,SQLlite。
2：非关系型数据库：MongoDB ，Redis
三: SQL 语言：
DQL：数据查询语言，用于对数据进行查询，如select
DML：数据操作语言，对数据进行增加、修改、删除，如/insert、update、delete
TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
DCL：数据控制语言，进行授权与权限回收，如grant、revoke
DDL：数据定义语言，进行数据库、表的管理等，如create、drop
四：MYSQL数据库：
1：登录数据库：
sudo mysql -u用户名 -p密码
sudo mysql -uroot -p123456
2:退出数据库：
exit
quit
CTRL + d
3：查询系统时间：
select now()
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-08-14 18:13:34 |
+---------------------+
1 row in set (0.00 sec)
4:查看所有的数据库:
show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Test |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
5:数据库的操作：
创建数据库：create database 数据库名 字符编码;
使用数据库 ：use 数据库名;
查看当前使用的数据库： select database();
删除数据库：drop database 数据库名；
mysql> create database student charset=utf8;
Query OK, 1 row affected (0.00 sec)
mysql> use student;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| student |
+------------+
1 row in set (0.00 sec)
mysql> drop database student;
Query OK, 0 rows affected (0.00 sec)
6：数据库表的所有操作：
创建数据的表：create table 表名（字段名 类型 约束,字段名 类型 约束 …）
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) not null,
age tinyint unsigned default 0,
high decimal(5,2),
gender enum('男','女'),
cls_id int unsigned
);
查看表结构：desc 表名
mysql> create table students(
-> id int unsigned primary key auto_increment not null,
-> name varchar(20) not null,
-> age tinyint unsigned default 0,
-> high decimal(5,2),
-> gender enum('男','女'),
-> cls_id int unsigned
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> desc students;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
| cls_id | int(10) unsigned | YES | | NULL | |
+--------+---------------------+------+-----+---------+----------------+
6 rows in set (0.04 sec)
修改表–给表添加列：alter table 表名 add 列名 类型；
mysql> alter table students add birthday datetime;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
| cls_id | int(10) unsigned | YES | | NULL | |
| birthday | datetime | YES | | NULL | |
+----------+---------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
修改表–不重命名列：
alter table 表名 modify 列名 类型及约束。
mysql> alter table students modify birthday date;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
| cls_id | int(10) unsigned | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+---------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
修改表—重命名列：
alter table 表名 change 原名 新名 类型和约束。
mysql> alter table students change birthday birth date;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
| cls_id | int(10) unsigned | YES | | NULL | |
| birth | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
修改表—删除列：
alter table 表名 drop 列名
mysql> alter table students drop birth;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
| cls_id | int(10) unsigned | YES | | NULL | |
+--------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
查看表的创建语句：
mysql> show create table students;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` tinyint(3) unsigned DEFAULT '0',
`high` decimal(5,2) DEFAULT NULL,
`gender` enum('男','女') DEFAULT NULL,
`cls_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看库的创建语句：
mysql> show create database student;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| student | CREATE DATABASE `student` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
删除表和删除数据库：
drop table 表名
drop database 数据库名
