【若泽大数据第五天】基础-MySql数据库的使用基础

MySql基础命令

• database及数据库用户的创建与删除

1. 创建database

[[email protected] ~]# su - mysqladmin	# 切换用户
Last login: Sat Feb  9 20:03:56 CST 2019 on pts/0
hadoop614:mysqladmin:/usr/local/mysql:>mysql -uroot -p 	# root用户登录数据库
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
······
mysql> 

查看当前所有database

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

##  创建一个database，命名为bigdata
ysql> create database bigdata;
Query OK, 1 row affected (0.00 sec)

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

2. 创建数据库用户
   查看当前数据库用户

mysql> use mysql	# 切换database
Database changed
mysql> select distinct user from user;
+------+
| user |
+------+
| root |
+------+
1 row in set (0.00 sec)

创建数据库用户

mysql> grant all privileges on bigdata.* to [email protected]'%' identified by '你的密码';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

使用d614用户登录数据库

hadoop614:mysqladmin:/usr/local/mysql:>mysql -ud614 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
......
mysql> use mysql
ERROR 1044 (42000): Access denied for user 'd614'@'%' to database 'mysql' ## 没有权限访问
mysql> use bigdata
Database changed	# 切换成功
mysql> 

删除database

hadoop614:mysqladmin:/usr/local/mysql:>mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
.......
mysql> drop database bigdata;
Query OK, 0 rows affected (0.00 sec)

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

删除数据库用户

mysql> use mysql
Database changed
mysql> delete from user where user='d614';
Query OK, 1 row affected (0.00 sec)

mysql> select distinct user from user;
+------+
| user |
+------+
| root |
+------+
1 row in set (0.00 sec)

• 查询当前数据库连接访问数

mysql> show processlist;
+-----+------+-------------------+---------+---------+------+-------+------------------+
| Id  | User | Host              | db      | Command | Time | State | Info             |
+-----+------+-------------------+---------+---------+------+-------+------------------+
| 190 | d614 | localhost         | bigdata | Sleep   |  987 |       | NULL             |
| 191 | root | localhost         | mysql   | Query   |    0 | init  | show processlist |
| 195 | d614 | 42.101.110.3:5856 | bigdata | Sleep   |  105 |       | NULL             |
| 196 | d614 | 42.101.110.3:5857 | bigdata | Sleep   |   78 |       | NULL             |
+-----+------+-------------------+---------+---------+------+-------+------------------+
4 rows in set (0.00 sec)

杀死所有非root用户的连接

mysql> kill 190;
Query OK, 0 rows affected (0.00 sec)

mysql> kill 195;
Query OK, 0 rows affected (0.00 sec)

mysql> kill 196;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+-----+------+-----------+-------+---------+------+-------+------------------+
| Id  | User | Host      | db    | Command | Time | State | Info             |
+-----+------+-----------+-------+---------+------+-------+------------------+
| 191 | root | localhost | mysql | Query   |    0 | init  | show processlist |
+-----+------+-----------+-------+---------+------+-------+------------------+
1 row in set (0.00 sec)

• 使用dbeaver.exe工具访问MySQL数据库

注：在使用dbeaver工具前，须在Windows本地安装好Java JDK，并设置环境变量；最好是使用JDK1.8及一下的版本，1.8以上版本目前调试相对麻烦

查看本地Java JDK

• 使用dbeaver工具登录MySQL数据库
  
  配置好后进行连接测试
  
  测试成功后一直下一步，直至完成
  
  

• 数据库SQL基础语句

数据类型

数据类型	关键字
整数类型	bit、bool、tiny int、small int、medium int、int、big int
浮点数类型	float、double、decimal
字符串类型	char、varchar、tiny text、text、medium text、longtext、tiny blob、blob、long blob
日期类型	date、time、datetime、timestamp、year
其他数据类型	binary、varbinary、enum、set、geometry、point、multipoint、linestring、multilinestring、polygon、geometrycollection等

• mysql数据库语言

1.数据定义语言DDL(Data Definition Language)： create、drop、use、show、alter、desc
2.数据操纵语言DML(Data Manipulation Language)： insert、update、delete、truncate
3.数据查询语言DQL(Data Query Language)：select
4.数据控制语言DCL(Data Control Language) ： commit、用户，权限，事务。

• SQL语句示例
  创建数据库表

CREATE TABLE `rzdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_num` int(11) DEFAULT NULL,
  `stu_name` varchar(100) DEFAULT NULL,
  `stu_age` int(11) DEFAULT NULL,
  `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createuser` varchar(100) DEFAULT NULL,
  `updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updateuser` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `rzdata_test` (
  `stu_num` int(11) DEFAULT NULL,
  `stu_name` varchar(100) DEFAULT NULL,
  `stu_age` int(11) DEFAULT NULL,
  `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createuser` varchar(100) DEFAULT NULL,
  `updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updateuser` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`stu_num`,`stu_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

写入数据

INSERT INTO bigdata.rzdata
(stu_num, stu_name, stu_age, createtime, createuser, updatetime, updateuser)
VALUES(0, '', 0, CURRENT_TIMESTAMP, '', CURRENT_TIMESTAMP, '');

查询数据及输出结果

更新数据并查询

update bigdata.rzdata t set t.stu_name ='d614' where t.stu_num='0';

删除数据并查询

delete from bigdata.rzdata where stu_name ='d614' ;