MYSQL(一) 创建用户-创建数据库-创建表-插入数据

MYSQL从创建用户-创建数据库-创建表-插入数据

#创建新用户peter
mysql> create user 'peter'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.15 sec)
#授予权限
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON mysql.*
    -> TO 'peter'@'localhost';
Query OK, 0 rows affected (0.17 sec)
#使用新用户登录
PS D:\mysql-8.0.19-winx64\bin> ./mysql -u peter -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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

mysql> use mysql
Database changed

#创建数据库
mysql> CREATE DATABASE PETZHANG;
Query OK, 1 row affected (0.10 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| petzhang           |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> USE PETZHANG;
Database changed

#创建表
mysql> CREATE TABLE IF NOT EXISTS `RUNOOB_TBL2`(
    -> `RUNOOB_ID` INT UNSIGNED AUTO_INCREMENT,
    -> `RUNOOB_TITLE` VARCHAR(100) NOT NULL,
    -> `RUNOOB_AUTHOR` VARCHAR(40) NOT NULL,
    -> `SUBMISSION_DATE` DATE,
    -> PRIMARY KEY(`RUNOOB_ID`))ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.36 sec)

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

mysql> desc runoob_tbl;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| runoob_id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| runoob_title    | varchar(100) | NO   |     | NULL    |                |
| runoob_author   | varchar(40)  | NO   |     | NULL    |                |
| submission_date | date         | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

#插入数据
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date) values
    -> ("学习 PHP", "菜鸟教程", NOW());
Query OK, 1 row affected, 1 warning (0.22 sec)

mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date) values
    -> ("学习 MySQL", "菜鸟教程", NOW());
Query OK, 1 row affected, 1 warning (0.11 sec)

mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date) values
    -> ("JAVA 教程", "RUNOOB.COM", '2016-05-06');
Query OK, 1 row affected (0.09 sec)

mysql> select * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 PHP     | 菜鸟教程      | 2020-03-05      |
|         2 | 学习 MySQL   | 菜鸟教程      | 2020-03-05      |
|         3 | JAVA 教程    | RUNOOB.COM    | 2016-05-06      |
+-----------+--------------+---------------+-----------------+
3 rows in set (0.00 sec)