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>

四时宝库

程序员的知识宝库

mysql学习笔记（史上最全的mysql基础教程）