mysql快速入门（mysql学习笔记）

登录MysqlMicrosoft Windows [版本 10.0.19042.746]，下面我们就来说一说关于mysql快速入门?我们一起去了解并探讨一下这个问题吧!

mysql快速入门

登录Mysql

Microsoft Windows [版本 10.0.19042.746]

C:\Users\Administrator>mysql -uroou -p

Enter password: ********* //密码错误，登录失败

ERROR 1045 (28000): Access denied for user 'roou'@'localhost' (using password: YES)

C:\Users\Administrator>mysql -uroot -p

Enter password: *********

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 13

Server version: 8.0.26 MySQL Community Server - GPL

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版本号

mysql> select version();

-----------

| version() |

-----------

| 8.0.26 |

-----------

1 row in set (0.01 sec)

显示数据库

mysql> show databases;

--------------------

| Database |

--------------------

| book |

| information_schema |

| mysql |

| performance_schema |

| sakila |

| sys |

| world |

--------------------

7 rows in set (0.09 sec)

进入book数据库

mysql> use book;

Database changed

查询数据表

mysql> show tables;

----------------

| Tables_in_book |

----------------

| book_a |

| book_ab |

| book_b |

| book_c |

----------------

4 rows in set (0.04 sec)

删除数据表

mysql> drop table book_ab;

Query OK, 0 rows affected (0.08 sec)

再查询数据表，book_ab已被删除

mysql> show tables;

----------------

| Tables_in_book |

----------------

| book_a |

| book_b |

| book_c |

----------------

3 rows in set (0.00 sec)

mysql> show databases;

--------------------

| Database |

--------------------

| book |

| information_schema |

| mysql |

| performance_schema |

| sakila |

| sys |

| world |

--------------------

7 rows in set (0.00 sec)

删除数据库book，其中里面的相关数据表已被删除，不可恢复。

mysql> drop database book;

Query OK, 3 rows affected (0.11 sec)

mysql> show databases;

--------------------

| Database |

--------------------

| information_schema |

| mysql |

| performance_schema |

| sakila |

| sys |

| world |

--------------------

6 rows in set (0.00 sec)

创建数据库库名为book

mysql> create tadabase book;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tadabase book' at line 1

mysql> create database book;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

--------------------

| Database |

--------------------

| book |

| information_schema |

| mysql |

| performance_schema |

| sakila |

| sys |

| world |

--------------------

7 rows in set (0.01 sec)

mysql> use book;

Database changed

mysql> create table book_a(

-> id int(11),

-> name varchar(11),

-> sex varchar(11),

-> habby varchar(11),

-> ID-card int(11),

-> higth float(11)

-> );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-card int(11),

higth float(11)

)' at line 6

创建book_a数据表

mysql> create table book_a(

-> id int(11),

-> name varchar(11),

-> sex varchar(11),

-> habby varchar(11)

-> );

Query OK, 0 rows affected, 1 warning (0.10 sec)

mysql> select id from book_a;

Empty set (0.01 sec)

mysql> use book_a;

ERROR 1049 (42000): Unknown database 'book_a'

mysql> show databases;

--------------------

| Database |

--------------------

| book |

| information_schema |

| mysql |

| performance_schema |

| sakila |

| sys |

| world |

--------------------

7 rows in set (0.00 sec)

mysql> use book_a;

ERROR 1049 (42000): Unknown database 'book_a'

mysql> use book;

Database changed

mysql> carete table book_b(

-> id int(11)primary key,

-> name varcar(11)

-> );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'carete table book_b(

id int(11)primary key,

name varcar(11)

)' at line 1

mysql> create table book_b(

-> id int(1)primary key,

-> name varchar(11)

-> );

Query OK, 0 rows affected, 1 warning (0.05 sec)

显示数据表

mysql> show tables;

----------------

| Tables_in_book |

----------------

| book_a |

| book_b |

----------------

2 rows in set (0.02 sec)

mysql> select id from book_b;

Empty set (0.03 sec)

mysql> describe book_a;

------- ------------- ------ ----- --------- -------

------- ------------- ------ ----- --------- -------

| id | int | YES | | NULL | |

| sex | varchar(11) | YES | | NULL | |

------- ------------- ------ ----- --------- -------

4 rows in set (0.01 sec)

mysql> describe book_b;

------- ------------- ------ ----- --------- -------

------- ------------- ------ ----- --------- -------

| id | int | NO | PRI | NULL | |

------- ------------- ------ ----- --------- -------

2 rows in set (0.00 sec)

mysql> describe book_a;

------- ------------- ------ ----- --------- -------

------- ------------- ------ ----- --------- -------

| id | int | YES | | NULL | |

| sex | varchar(11) | YES | | NULL | |

------- ------------- ------ ----- --------- -------

4 rows in set (0.00 sec)

mysql> describe book_a;

------- ------------- ------ ----- --------- -------

------- ------------- ------ ----- --------- -------

| id | int | YES | | NULL | |

| sex | varchar(11) | YES | | NULL | |

------- ------------- ------ ----- --------- -------

4 rows in set (0.00 sec)

mysql> select id from book_a;

------

| id |

------

| 1 |

| 4 |

------

4 rows in set (0.00 sec)

mysql> select id from book_a;

------

| id |

------

| 2 |

| 4 |

| 5 |

------

5 rows in set (0.00 sec)

mysql> select id from book_a;

------

| id |

------

| 2 |

| 4 |

| 5 |

| NULL |

------

6 rows in set (0.00 sec)

mysql> describe book_a;

------- ------------- ------ ----- --------- -------

------- ------------- ------ ----- --------- -------

| id | int | YES | | NULL | |

| sex | varchar(11) | YES | | NULL | |

------- ------------- ------ ----- --------- -------

4 rows in set (0.00 sec)

mysql> describe book_b;

------- ------------- ------ ----- --------- -------

------- ------------- ------ ----- --------- -------

| id | int | NO | PRI | NULL | |

------- ------------- ------ ----- --------- -------

2 rows in set (0.00 sec)

mysql> select name from book_a;

------

| name |

------

| 小王 |

| NULL |

------

6 rows in set (0.01 sec)

mysql> describe book_a;

------- ------------- ------ ----- --------- -------

------- ------------- ------ ----- --------- -------

| id | int | YES | | NULL | |

| sex | varchar(11) | YES | | NULL | |

------- ------------- ------ ----- --------- -------

4 rows in set (0.00 sec)

mysql> describe book_b：

-> describe book_b；

-> ；

-> ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'describe book_b；

；' at line 2

mysql> describe book_b;

------- ------------- ------ ----- --------- -------

------- ------------- ------ ----- --------- -------

| id | int | NO | PRI | NULL | |

------- ------------- ------ ----- --------- -------

2 rows in set (0.00 sec)

mysql> create table book_c;

ERROR 4028 (HY000): A table must have at least one visible column.

mysql> create table book_c(

-> id name(11) primary key,

-> name varchar(11) not null

-> );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name(11) primary key,

name varchar(11) not null

)' at line 2

mysql> create table book_c(

-> id int(11) primary key,

-> name varchar(11) not null

-> );

Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> describr book_c;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'describr book_c' at line 1

mysql> describe book_c;

------- ------------- ------ ----- --------- -------

------- ------------- ------ ----- --------- -------

| id | int | NO | PRI | NULL | |

------- ------------- ------ ----- --------- -------

2 rows in set (0.02 sec)

mysql> create table book_d(

-> id int(11) primary key auto_increment,

-> name varchar(11) not null

-> );

Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> describe book_d;

------- ------------- ------ ----- --------- ----------------

------- ------------- ------ ----- --------- ----------------

| id | int | NO | PRI | NULL | auto_increment |

------- ------------- ------ ----- --------- ----------------

2 rows in set (0.03 sec)

mysql> insert into book_d(nameid,sex)

-> vulues('lucy',1000),('lura',12000),('even',1500);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'vulues('lucy',1000),('lura',12000),('even',1500)' at line 2

mysql> vulues('lucy',1000),('lura',12000),('even',1500);

mysql> insert into book_d(nameid,sex)

-> values('lucy',1000),('lura',12000),('even',1500);

ERROR 1054 (42S22): Unknown column 'nameid' in 'field list'

mysql> insert into book_d(bameid,sex)

-> values('lusy',100),('cc',1020);

ERROR 1054 (42S22): Unknown column 'bameid' in 'field list'

mysql> describe book_d;

------- ------------- ------ ----- --------- ----------------

------- ------------- ------ ----- --------- ----------------

| id | int | NO | PRI | NULL | auto_increment |

------- ------------- ------ ----- --------- ----------------

2 rows in set (0.00 sec)

mysql> insert into book_d(id,name)

-> insert into book_d(name)

-> values('lucy'),('cc');

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into book_d(name)

values('lucy'),('cc')' at line 2

mysql> insert into book_d(name)

-> values('vv'),('bb');

Query OK, 2 rows affected (0.02 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select from book_d;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from book_d' at line 1

mysql> describe book_d;

------- ------------- ------ ----- --------- ----------------

------- ------------- ------ ----- --------- ----------------

| id | int | NO | PRI | NULL | auto_increment |

------- ------------- ------ ----- --------- ----------------

2 rows in set (0.01 sec)

mysql> select * from book_d;

---- ------

| id | name |

---- ------

| 1 | vv |

| 2 | bb |

---- ------

2 rows in set (0.01 sec)

mysql> select * from book_a;

------ ------ ------ -------

| id | name | sex | habby |

------ ------ ------ -------

| 2 | 小王 | NULL | NULL |

------ ------ ------ -------

6 rows in set (0.01 sec)

mysql> select * from book_d;

---- ------

| id | name |

---- ------

| 1 | vv |

| 2 | bb |

---- ------

2 rows in set (0.00 sec)

mysql> select id from book_d;

----

| id |

----

| 1 |

| 2 |

----

2 rows in set (0.00 sec)

mysql> describe book;

ERROR 1146 (42S02): Table 'book.book' doesn't exist

mysql> describe book_a;

------- ------------- ------ ----- --------- -------

------- ------------- ------ ----- --------- -------

| id | int | YES | | NULL | |

| sex | varchar(11) | YES | | NULL | |

------- ------------- ------ ----- --------- -------

4 rows in set (0.00 sec)

mysql> describe book_b;

------- ------------- ------ ----- --------- -------

------- ------------- ------ ----- --------- -------

| id | int | NO | PRI | NULL | |

------- ------------- ------ ----- --------- -------

2 rows in set (0.00 sec)

mysql> select * from book_a;

------ ------ ------ -------

| id | name | sex | habby |

------ ------ ------ -------

| 2 | 小王 | NULL | NULL |

------ ------ ------ -------

6 rows in set (0.00 sec)

mysql> select id from book_a;

------

| id |

------

| 2 |

| 4 |

| 5 |

| NULL |

------

6 rows in set (0.00 sec)

mysql> describe book_a;

------- ------------- ------ ----- --------- -------

------- ------------- ------ ----- --------- -------

| id | int | YES | | NULL | |

| sex | varchar(11) | YES | | NULL | |

------- ------------- ------ ----- --------- -------

4 rows in set (0.00 sec)

mysql> describe book_b;

------- ------------- ------ ----- --------- -------

------- ------------- ------ ----- --------- -------

| id | int | NO | PRI | NULL | |

------- ------------- ------ ----- --------- -------

2 rows in set (0.00 sec)

mysql> describe book_d;

------- ------------- ------ ----- --------- ----------------

------- ------------- ------ ----- --------- ----------------

| id | int | NO | PRI | NULL | auto_increment |

------- ------------- ------ ----- --------- ----------------

2 rows in set (0.00 sec)

mysql> select * from table book_a;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table book_a' at line 1

mysql> select * from book_a;

------ ------ ------ -------

| id | name | sex | habby |

------ ------ ------ -------

| 2 | 小王 | NULL | NULL |

------ ------ ------ -------

6 rows in set (0.00 sec)

mysql> select * from book_b;

Empty set (0.01 sec)

mysql> select * from book_c;

Empty set (0.01 sec)

mysql> select * from book_d;

---- ------

| id | name |

---- ------

| 1 | vv |

| 2 | bb |

---- ------

2 rows in set (0.00 sec)

mysql> show create table book_d;

-------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| Table | Create Table |

| book_d | CREATE TABLE `book_d` (

`id` int NOT NULL AUTO_INCREMENT,

`name` varchar(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

1 row in set (0.00 sec)

mysql> show create table book_d\G

*************************** 1. row ***************************

Table: book_d

Create Table: CREATE TABLE `book_d` (

`id` int NOT NULL AUTO_INCREMENT,

`name` varchar(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

mysql> show table;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

mysql> show tables;

----------------

| Tables_in_book |

----------------

| book_a |

| book_b |

| book_c |

| book_d |

----------------

4 rows in set (0.00 sec)

mysql> alter table book_d rename to book_e;

Query OK, 0 rows affected (0.07 sec)

mysql> show tables;

----------------

| Tables_in_book |

----------------

| book_a |

| book_b |

| book_c |

| book_e |

----------------

4 rows in set (0.01 sec)

mysql> alter table book_e rename to book_d;

Query OK, 0 rows affected (0.06 sec)

mysql> show tables;

----------------

| Tables_in_book |

----------------

| book_a |

| book_b |

| book_c |

| book_d |

----------------

4 rows in set (0.02 sec)

mysql> select * from book_d;

---- ------

| id | name |

---- ------

| 1 | vv |

| 2 | bb |

---- ------

2 rows in set (0.01 sec)

mysql> describe book_d;

------- ------------- ------ ----- --------- ----------------

------- ------------- ------ ----- --------- ----------------

| id | int | NO | PRI | NULL | auto_increment |

------- ------------- ------ ----- --------- ----------------

2 rows in set (0.01 sec)

mysql> alter table book_d modify name varchar(12);

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe book_d;

------- ------------- ------ ----- --------- ----------------

------- ------------- ------ ----- --------- ----------------

| id | int | NO | PRI | NULL | auto_increment |

------- ------------- ------ ----- --------- ----------------

2 rows in set (0.02 sec)

mysql> desc book_d;

------- ------------- ------ ----- --------- ----------------

------- ------------- ------ ----- --------- ----------------

| id | int | NO | PRI | NULL | auto_increment |

------- ------------- ------ ----- --------- ----------------

2 rows in set (0.00 sec)

mysql> alter table book_d name varchar(13);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name varchar(13)' at line 1

mysql> alter table book_d mod name varchar(13);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mod name varchar(13)' at line 1

mysql> alter table book_d modify name varchar(13);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc book_d;

------- ------------- ------ ----- --------- ----------------

------- ------------- ------ ----- --------- ----------------

| id | int | NO | PRI | NULL | auto_increment |

------- ------------- ------ ----- --------- ----------------

2 rows in set (0.02 sec)

mysql> alter table book_d modify name varchar(14);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc book_d;

------- ------------- ------ ----- --------- ----------------

------- ------------- ------ ----- --------- ----------------

| id | int | NO | PRI | NULL | auto_increment |

------- ------------- ------ ----- --------- ----------------

2 rows in set (0.02 sec)

mysql> alter table book_d rename tu book_e;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'book_e' at line 1

mysql> alter table book_d rename to book_e;

Query OK, 0 rows affected (0.04 sec)

mysql> show tables;

----------------

| Tables_in_book |

----------------

| book_a |

| book_b |

| book_c |

| book_e |

----------------

4 rows in set (0.02 sec)

mysql> alter table book_e rename to book_d;

Query OK, 0 rows affected (0.05 sec)

mysql> show tables;

----------------

| Tables_in_book |

----------------

| book_a |

| book_b |

| book_c |

| book_d |

----------------

4 rows in set (0.00 sec)

mysql> alter table bood_d modify name varchar(10);

ERROR 1146 (42S02): Table 'book.bood_d' doesn't exist

mysql> alter table book_d modify name varchar(10);

Query OK, 2 rows affected (0.11 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> desc book_d;

------- ------------- ------ ----- --------- ----------------

------- ------------- ------ ----- --------- ----------------

| id | int | NO | PRI | NULL | auto_increment |

------- ------------- ------ ----- --------- ----------------

2 rows in set (0.01 sec)

mysql> alter table book_d chage name nameid varchar(11);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'chage name nameid varchar(11)' at line 1

mysql> alter table book_d change name nameid varchar(11);

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc book_d;

-------- ------------- ------ ----- --------- ----------------

-------- ------------- ------ ----- --------- ----------------

| id | int | NO | PRI | NULL | auto_increment |

-------- ------------- ------ ----- --------- ----------------

2 rows in set (0.00 sec)

mysql>