数据库基础
mysql数据库基础
1. 数据库的下载与安装
数据库的下载
mysql属于开源软件, 可以在官方随意下载自己想要下载的版本
下载地址: 5.7版本下载位置: https://dev.mysql.com/downloads/mysql/5.7.html#downloads
安装步骤
1. 下载完压缩包后, 解压到指定路径后, 将mysql的bin目录路径添加环境变量
2. 添加完环境变量后, 可以开始启动mysql服务端了, 先输入以下命令
PS C:\WINDOWS\system32> mysqld --initialize-insecure
PS C:\WINDOWS\system32> mysqld
服务端会卡在那运行着...
这时候就可以开始启动客户端了, 另开一个客户端mysql, 初始用户时root , 默认密码为空
PS C:\Users\29137> mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.26 MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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)
3. 这时候虽然可以操作mysql了, 但是要开两个cmd窗口, 就会非常的麻烦, 还需要把mysql配置成服务, 让每次开机都自动启动mysql服务.
PS C:\WINDOWS\system32> mysqld --install Service successfully installed. PS C:\WINDOWS\system32> net start mysql MySQL 服务正在启动 . MySQL 服务无法启动。 服务没有报告任何错误。
这时候如果开始启动错误, 报这个错误的话, 那么可以在任务管理器里查看, 是否mysqld服务端还在运行着, 可以先把这个进程杀死,再启动服务.
PS C:\WINDOWS\system32> net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
可以看到, 服务正常启动了.
停止服务的方式是 net stop mysql
注销服务的方式是 mysqld --remove
3. 修改密码
修改通过mysqladmin命令, 初始密码为空, 以后就要通过-p参数填写原密码修改
PS C:\Users\29137> mysqladmin -uroot -p password 123 Enter password: mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety. PS C:\Users\29137> mysqladmin -uroot -p123 password 12 mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
4. 配置配置文件
进入mysql, 通过\s命令可以看默认编码方式在windows上是gbk, 这些都不符合现在通用的utf-8需求
mysql> \s -------------- D:\Database\mysql\mysql-5.7.26-winx64\bin\mysql.exe Ver 14.14 Distrib 5.7.26, for Win64 (x86_64) Connection id: 3 Current database: Current user: root@localhost SSL: Not in use Using delimiter: ; Server version: 5.7.26 MySQL Community Server (GPL) Protocol version: 10 Connection: localhost via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: gbk Conn. characterset: gbk TCP port: 3306 Uptime: 5 min 45 sec Threads: 1 Questions: 7 Slow queries: 0 Opens: 105 Flush tables: 1 Open tables: 98 Queries per second avg: 0.020
此外, 每次进入mysql都要输入密码很麻烦, 此时可以在mysql的解压目录里添加一个my.ini文件, 添加以下选项
[mysqld] character-set-server=utf8 collation-server=utf8_general_ci [client] default-character-set=utf8 user=root password=12 [mysql] default-character-set=utf8
这时候重启服务后, 再次运行\s命令可以看到默认编码方式都是utf-8了. 并且在终端只需要敲入mysql就可以了.
5. 忘记密码
1. 暴力删库, 重装
2. 跳过认证, 进入修改密码
关闭服务, 用终端启动mysqld服务, 添加跳过授权的参数
PS C:\WINDOWS\system32> mysqld --skip-grant-tables
mysql> use mysql Database changed mysql> update user set authentication_string=password(123) where user='root' and host='localhost'; Query OK, 0 rows affected, 1 warning (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 1
使用上面的方式就可以再次修改密码了.
2. 数据库的初始命令
1. 数据库的增删改查
添加数据库
mysql:(none)> CREATE DATABASE test; Query OK, 1 row affected Time: 0.002s
使用数据库
mysql:(none)> USE test; You are now connected to database "test" as user "root" Time: 0.000s
查询所有数据库
mysql:(none)> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set Time: 0.010s
查看当前数据库
mysql:test> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | test | +------------+ 1 row in set Time: 0.009s
查看数据库的结构
mysql:(none)> SHOW CREATE DATABASE test; +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+ 1 row in set Time: 0.013s
修改数据库
mysql:test> ALTER DATABASE test charset='gbk'; You're about to run a destructive command. Do you want to proceed? (y/n): y Your call! Query OK, 1 row affected Time: 0.003s mysql:test> SHOW CREATE DATABASE test; +----------+--------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+--------------------------------------------------------------+ 1 row in set Time: 0.009s
2. 表的增删改查
创建表, 需要指明表名字段名, 字段类型等等.
mysql:test> USE test; You are now connected to database "test" as user "root" Time: 0.001s mysql:test> CREATE TABLE userinfo( id int(11), name char(10) ); Query OK, 0 rows affected Time: 0.032s
查看表的创建结构, 查看的表的字段类型等等.
mysql:test> SHOW CREATE TABLE userinfo; +----------+-------------------------------------+ | Table | Create Table | +----------+-------------------------------------+ | userinfo | CREATE TABLE `userinfo` ( | | | `id` int(11) DEFAULT NULL, | | | `name` char(10) DEFAULT NULL | | | ) ENGINE=InnoDB DEFAULT CHARSET=gbk | +----------+-------------------------------------+ 1 row in set Time: 0.015s mysql:test> DESC userinfo; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | <null> | | | name | char(10) | YES | | <null> | | +-------+----------+------+-----+---------+-------+ 2 rows in set Time: 0.007s mysql:test> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | userinfo | +----------------+ 1 row in set Time: 0.006s
改表结构
mysql:test> ALTER TABLE userinfo MODIFY name char(20); You're about to run a destructive command. Do you want to proceed? (y/n): y Your call! Query OK, 0 rows affected Time: 0.073s mysql:test> DESC userinfo; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | <null> | | | name | char(20) | YES | | <null> | | +-------+----------+------+-----+---------+-------+ 2 rows in set
删表
mysql:test> DROP TABLE userinfo; You're about to run a destructive command. Do you want to proceed? (y/n): y Your call! Query OK, 0 rows affected Time: 0.014s mysql:test> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ 0 rows in set Time: 0.006s
3. 表记录的增删改查
增加记录
mysql:test> INSERT INTO userinfo(id, name) VALUES(2, 'b'), (3, 'c'), (4, 'c'); Query OK, 3 rows affected Time: 0.007s
查询记录
mysql:test> SELECT * FROM userinfo;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | c |
更新记录
mysql:test> UPDATE userinfo SET name='aaa' WHERE id = 2; Query OK, 2 rows affected Time: 0.008s mysql:test> SELECT * FROM userinfo; +----+------+ | id | name | +----+------+ | 2 | aaa | | 3 | c | | 2 | aaa | | 3 | c | +----+------+
删除记录
mysql:test> DELETE FROM userinfo WHERE id = 2; You're about to run a destructive command. Do you want to proceed? (y/n): y Your call! Query OK, 2 rows affected Time: 0.006s mysql:test> SELECT * FROM userinfo; +----+------+ | id | name | +----+------+ | 3 | c | | 3 | c | +----+------+ 2 rows in set Time: 0.009s
浙公网安备 33010602011771号