MySQL学习笔记（⼀）
MySQL学习笔记（⼀）
数据库学习
⼀、数据库基础知识
1.1 查看数据库版本号：
C:\Users\Crush>mysql --version
mysql Ver 8.0.26 for Win64 on x86_64 (MySQL Community Server - GPL)
1.2 MySQL的登录
1.2.1 登录⽅式1：MySQL⾃带客户端
开始菜单 → 所有程序 → MySQL → MySQL 8.0 Command Line Client
说明：仅限于root⽤户
1.2.2 登录⽅式2：windows命令⾏
格式：
mysql -h 主机名 -P 端⼝号 -u ⽤户名 -p密码
举例：
mysql -h localhost -P 3306 -u root -pabc123 # 这⾥我设置的root⽤户的密码是abc123
sql容易学吗注意：
（1）-p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格。如：
mysql -hlocalhost -P3306 -uroot -pabc123
（2）密码建议在下⼀⾏输⼊，保证安全
mysql -h localhost -P 3306-u root -p
Enter password:****
（3）客户端和服务器在同⼀台机器上，所以输⼊localhost或者IP地址127.0.0.1。同时，因为是连接本机：-hlocalhost就可以省略，如果端⼝号没有修改：-P3306也可以省略
简写成：
mysql -u root -p
Enter password:****
连接成功后，有关于MySQL Server服务版本的信息，还有第⼏次连接的id标识。
也可以在命令⾏通过以下⽅式获取MySQL Server服务版本的信息：
c:\> mysql -V
c:\> mysql --version
或登录后，通过以下⽅式查看当前版本信息：
mysql> select version();
1.2.3 退出登录
exit
或
quit
1.2.4 启动数据库8.0并退出
C:\Users\Crush>mysql -uroot -p123456 -hlocalhost -P3307
mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.26 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> quit
Bye
1.2.5 启动数据库5.7并退出
C:\Users\Crush>mysql -uroot -p123456 -hlocalhost -P3306
mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 239
Server version: 5.7.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> quit
Bye
1.3 建议启动数据库的安全⽅式
C:\Users\Crush>mysql -u root -p -hlocalhost -P3307
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.26 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> quit
Bye
1.4 查看字符集
mysql> show variables like 'character_%';
+--------------------------+---------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | D:\SoftProject\MySQL\MySQL Server 8.0\install\share\charsets\ |
+--------------------------+---------------------------------------------------------------+
8 rows in set, 1 warning (0.18 sec)
1.5 MySQL的编码设置
1.5.1 MySQL5.7中
问题再现：命令⾏操作sql乱码问题
mysql> INSERT INTO t_stu VALUES(1,'张三','男');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at row 1
问题解决
步骤1：查看编码命令
show variables like 'character_%';
show variables like 'collation_%';
步骤2：修改mysql的数据⽬录下的my.ini配置⽂件
[mysql] #⼤概在63⾏左右，在其下添加
.
..
default-character-set=utf8 #默认字符集
[mysqld] # ⼤概在76⾏左右，在其下添加
...
character-set-server=utf8
collation-server=utf8_general_ci
注意：建议修改配置⽂件使⽤notepad++等⾼级⽂本编辑器，使⽤记事本等软件打开修改后可能会导致⽂件编码修改为“含BOM 头”的编码，从⽽服务重启失败。
步骤3：重启服务
步骤4：查看编码命令
show variables like'character_%';
show variables like'collation_%';
1.5.2 MySQL8.0中
在MySQL 8.0版本之前，默认字符集为latin1，utf8字符集指向的是utf8mb3。⽹站开发⼈员在数据库设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从MySQL 8.0开始，数据库的默认编码改为utf8mb4，从⽽避免了上述的乱码问题。
⼆、数据库操作
2.1 显⽰所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.13 sec)
2.2 创建数据库并显⽰所有
mysql> create database test01;
Query OK, 1 row affected (0.14 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test01 |
+--------------------+
5 rows in set (0.03 sec)
2.3 使⽤数据库，创建表，显⽰表
mysql> use test01;
Database changed
mysql> create table employee(id int primary key auto_increment,name varchar(15));
Query OK, 0 rows affected (1.54 sec)
mysql> show tables;
+------------------+
| Tables_in_test01 |
+------------------+
| employee |
+------------------+
1 row in set (0.09 sec)
2.4 显⽰表结构
mysql> show create table employee;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(15) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1004 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.5 插⼊语句并查询数据
mysql> insert into employee values(1001,'Tom');
Query OK, 1 row affected (0.08 sec)
mysql> insert into employee values(1002,'Jane');
Query OK, 1 row affected (0.10 sec)
mysql> insert into employee values(1003,'Mark');
Query OK, 1 row affected (0.09 sec)
mysql> select * from employee;
+------+------+
| id | name |
+------+------+
| 1001 | Tom |
| 1002 | Jane |
| 1003 | Mark |
+------+------+
3 rows in set (0.01 sec)
2.6 显⽰所有表
mysql> show tables;
+------------------+
| Tables_in_test01 |
+------------------+
| employee |
+------------------+
1 row in set (0.00 sec)
mysql>
2.7 删除表
mysql> drop table employee;
Query OK, 0 rows affected (1.56 sec)
mysql> show tables;
Empty set (0.00 sec)
2.8 显⽰所有数据库
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论