一、数据库定义语言（DDL）：
创建以下操作：
1、添加数据库：create database 库名；
mysql> create database student;
Query OK, 1 row affected (0.16 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| student |
+--------------------+
5 rows in set (0.01 sec)
2、删除数据库：drop database 库名；
mysql> drop database student;
Query OK, 0 rows affected (0.16 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
3、创建mydbtest数据库并修改字符集：
-- 创建一个使用utf8字符集的mydb2数据库。
create database mydbtest character set gbk;
Query OK, 1 row affected (0.33 sec)
mysql> show create database mydbtest;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| mydbtest | CREATE DATABASE `mydbtest` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------------+
1 row in set (0.02 sec)
-- 修改mydb2字符集为uft8;
alter database mydbtest character set utf8;
Query OK, 1 row affected, 1 warning (0.13 sec)
mysql> show create database mydbtest;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| mydbtest | CREATE DATABASE `mydbtest` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
4、创建表t_student 并查看该表的字段和结构：
-- 1.选中数据库
mysql> use mydbtest;
Database changed
-- 2.创建数据表
create table t_student(
id bigint primary key auto_increment,
name varchar(12) not null,
age int
);
Query OK, 0 rows affected (0.53 sec)
-- 3.查看所创建的表
mysql> show tables;
+--------------------+
| Tables_in_mydbtest |
+--------------------+
| t_student |
+--------------------+
1 row in set (0.00 sec)
-- 4.查看表中的字段
mysql> desc t_student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(12) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
-- 5.查看表的结构
mysql> show create table t_student;
+-----------+---------------+
| Table | Create Table
----------------------------+
| t_student | CREATE TABLE `t_student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(12) NOT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
-----------------------------------------------
1 row in set (0.05 sec)
5.对数据表的结构的修改：
-- 1、表名改为t_user。
rename table t_student to t_user;
Query OK, 0 rows affected (0.20 sec)
mysql> show tables;
+--------------------+
| Tables_in_mydbtest |
+--------------------+
| t_user |
+--------------------+
1 row in set (0.00 sec)
-- 2、在上面员工表的基本上增加一个password列。
mysql> alter table t_student add password varchar(20);
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(12) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
-- 3、修改age列，修改为varchar类型。
mysql> alter table t_student modify age varchar(80);
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(12) | NO | | NULL | |
| age | varchar(80) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
-- 4、列名name修改为username
mysql> alter table t_student change name username varchar(20) not null;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| age | varchar(80) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
-- 5、删除password列。
mysql> alter table t_student drop password;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| age | varchar(80) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)
-- 6、修改表的字符集为gbk
mysql> alter table t_student character set gbk;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t_student;
| Table | Create Table
| t_student | CREATE TABLE `t_student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(20) CHARACTER SET utf8 NOT NULL,
`age` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
1 row in set (0.00 sec)
6.主键和外键约束：
-- 1.创建一个t_class班级表，
create table t_class(
cid bigint primary key auto_increment,
cname varchar(20)
);
Query OK, 0 rows affected (0.16 sec)
mysql> desc t_class;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| cid | bigint(20) | NO | PRI | NULL | auto_increment |
| cname | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
-- 2.增加t_student学生表cid字段
alter table t_student add cid bigint not null;
mysql> desc t_student;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| age | varchar(80) | YES | | NULL | |
| cid | bigint(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
-- 3.增加学生表外键约束
添加外键约束语法：alter table 从表 add constraint 外键（形如：FK_从表_主表） foreign key (从表外键字段) references 主表(主键字段);
mysql> alter table t_student add constraint fk_t_student_t_class foreign key(cid) ref
rences t_class(cid);
Query OK, 0 rows affected (0.37 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t_student;
-------------------------------------------------------------------+
| Table | Create Table
-------------------------------------------------------------------+
| student | CREATE TABLE `t_student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(20) CHARACTER SET utf8 NOT NULL,
`age` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
`cid` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_t_student_class` (`cid`),
CONSTRAINT `fk_student_class` FOREIGN KEY (`cid`) REFERENCES `t_class` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
-------------------------------------------------------------------+
1 row in set (0.01 sec)
二、MYSQL权限管理
1.用户管理
1.1.使用create命令创建用户
---------------------------------------
语法：create user 'user'@'host' identified by 'password';
user：表示用户名
host：允许用户登录的主机名称。
(1)可以是:localhost 仅限本机访问，或是一个IP地址：172.20.15.19 ,
(2)还可以用通配置符：如果172.20.15.% ,（默认%）表示所有主机都可以访问。
password：用户密码。还可以用password（）函数加密密码。
例1：create user 'radius'@'localhost' identified by password 'radius' ;
用password(）函数加密radius。
1.2.直接修改mysql数据库中的user表中的用户：
---------------------------------------
用SQL语句修改user表后，需要flush privileges 刷新一下，重新加载用户权限表。这种方式也可以修改其他普通用户的密码。例2：insert into user(user,password,host) values('mysqltest',password('123456'),'172.20.%');
1.3.删除用户drop命令
-----------------------------------
格式: drop user 'user'@'host'drop user 'mysqltest'@'172.20.%' ;
1.4.SQL语句删除user表中的用户
-----------------------------------delete from user where user='mysqltest' and host='172.20.%';
1.5.修改密码：update更改user表
----------------------------------mysql> update mysql.user set password=password('mysql') where user='root' and host='%' ;
直接修改mysql数据库中的user表中的root用户。update之后还需要flush privileges 刷新一下，重新加载用户权限表。这种方式也可以修改其他普通用户的密码。
1.6.修改密码：set ,alter命令方式
-----------------------------------set password for 'root'@'%'=password('mysql');
alter user 'root'@'localhost' indentified by password('mysql');
1.7.重置root密码：mysqladmin
-----------------------------------
没有登录mysql之前，使用mysqladmin命令重置root密码mysqladmin -u root -h localhost -p password "123456" ;
1.8.MYSQL 8.0修改密码
-----------------------------------
mysql8.0 user表中没有了password字段，用plugin和authentication_string这两个字段
MySQL8.0之前的版本密码加密规则：mysql_native_password，
MySQL8.0密码加密规则：caching_sha2_password
plugin:默认方式caching_sha2_password ，要想远程登录必需将对应的用户plugin字段改成：mysql_native_password
authentication_string：密码
ALTER命令方式
====================ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
SQL语句
====================
update user set plugin='mysql_native_password' ,authentication_string=password('123456') where user='root' and host='localhost' ;
1.9.root用户忘记密码
------------------------------------
(1)停止mysql服务，
service mysql stop
或
systemctl status mysqld.service
(2) 输入命令
window下： mysqld --skip-grant-tables ;
linux下： mysqld_safe --skip-grant-tables user=mysql;
(3)用root用户进入mysql不需要密码：
mysql -u root ;
(4)修改密码：
update mysql.user set Password=password('password') where User='user';
(5)刷新加载权限表
flush privileges;
(6)重启mysql服务
service mysql start
或
systemctl start mysqld.service
2.权限管理
2.1.grant命令授权
--------------------------------
语法：grant privileges on db.table to 'user'@'host';
grant：授权命令
privileges: 权限,all表示所有权限，所可以指定具体的如：select_priv,insert_priv,delete_priv等等
有哪些权限可以在mysql数据库中的user表中查询
db：数据库名，如：radius.radacct表示radius库radacct表，也可用通配符*：radius.* 或 *.*表示所有库和表
user：用户名称。
host：主机
2.2.revoke 命令权限收回
--------------------------------
语法：revoke privileges on db.table to 'user'@'host';
revoke ：权限收回
privileges: 权限,all表示所有权限，所可以指定具体的如：select,insert,delete等等
有哪些权限可以在mysql数据库中的user表中查询
db：数据库名，如：radius.radacct表示radius库radacct表，也可用通配符*：radius.* 或 *.*表示所有库和表
user：用户名称。
host：主机
2.3.直接修改user表中的权限字段
--------------------------------
update user set select_priv='y',insert_priv='y',delete_priv='y' where user='root' and host='localhost';