创建数据库 删除数据库 表的创建 删除表
cmd打开命令行窗口:
因为链接的是自己的数据库,并且采用的是默认端口号3306
简化为: mysql -u 用户名 -p 密码
安装mysql 后,内置一个超级用户root ,密码为空
mysqld: 启动mysql服务的命令
mysql(必须配置环境变量)链接客户端的命令
退出mysql
连接别人的mysql
mysql -u 用户名 -p 密码 - h 主机号
修改root用户 支持远程连接
select host,user from mysql.user
update mysql.user set host = '%' where user = 'root'
这样支持别人远程连接
root 为超级用户 mysql需要重启 别人才能够成功连接
net stop mysql
这样关不了
启动mysql net start mysql
5、 数据库: database
查看当前用户下的数据库(需要启动数据库) 加分号 代表结束
show databases;
创建一个数据库
命令: create database py1905;
查看查看数据库
删除一个数据库
drop database py1905;
切换到指定数据库
use 数据库名
6、表 table: 作用 : 存储数据
表是 由 表头 和 内容 组成
column : 一个字段对应一个column
1、查看当前数据库下有多少张表
show tables;
2、创建一张表
数据库中 常见的数据类型:
int (整数) ===> 4个字节 ,1个字节 8位
-2^31 ===> 2^31-1
long (长整数) 8字节 64 位
-2^63 ===> 2^63-1
double 双精度,存储小说
varchar (可变字符串)
char(定长字符串)
date(日期类型,包含年月日)
datetime(精确到 时分秒)
text : (存储新闻、帖子、小说等)
longblob:(存储音频、图片、视频)
注意: 表名 字段 不能使用 中文
创建一张用户表、存储用户信息
create table py1905 (
name varchar(50),
gender char(2),
age int,
tel varchar(11),
address varchar(200),
birth date,
ID varchar(18),
photo longblob
);
多个字段用逗号分隔,最后一个没有逗号
删除表
drop table 表名;
Windows PowerShell
版权所有 (C) Microsoft Corporation。保留所有权利。
尝试新的跨平台 PowerShell https://aka.ms/pscore6
PS C:\WINDOWS\system32> net start mysql
请求的服务已经启动。
请键入 NET HELPMSG 2182 以获得更多的帮助。
PS C:\WINDOWS\system32> mysql -u -p -h 192.168.11.174
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.11.174' (10060)
PS C:\WINDOWS\system32> net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。
PS C:\WINDOWS\system32> net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
PS C:\WINDOWS\system32> mysql -u -p -h 192.168.11.174
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.11.174' (10060)
PS C:\WINDOWS\system32> show databases
show : 无法将“show”项识别为 cmdlet、函数、脚本文件或可运行程序的名称。请检查名称的拼写,如果包括路径,请确保路径正确
,然后再试一次。
所在位置 行:1 字符: 1
+ show databases
+ ~~~~
+ CategoryInfo : ObjectNotFound: (show:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
PS C:\WINDOWS\system32> net start mysql
请求的服务已经启动。
请键入 NET HELPMSG 2182 以获得更多的帮助。
PS C:\WINDOWS\system32> root -u -p
root : 无法将“root”项识别为 cmdlet、函数、脚本文件或可运行程序的名称。请检查名称的拼写,如果包括路径,请确保路径正确
,然后再试一次。
所在位置 行:1 字符: 1
+ root -u -p
+ ~~~~
+ CategoryInfo : ObjectNotFound: (root:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
PS C:\WINDOWS\system32> root -uroot -p
root : 无法将“root”项识别为 cmdlet、函数、脚本文件或可运行程序的名称。请检查名称的拼写,如果包括路径,请确保路径正确
,然后再试一次。
所在位置 行:1 字符: 1
+ root -uroot -p
+ ~~~~
+ CategoryInfo : ObjectNotFound: (root:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
PS C:\WINDOWS\system32> mysql root -uroot -p
Enter password:
ERROR 1049 (42000): Unknown database 'root'
PS C:\WINDOWS\system32> mysql root -uroot -p
Enter password:
ERROR 1049 (42000): Unknown database 'root'
PS C:\WINDOWS\system32> mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
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:
-> show databases;
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 ':
show databases' at line 1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| progect1 |
| sys |
+--------------------+
5 rows in set (0.02 sec)
mysql> create database py1905;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| progect1 |
| py1905 |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database py1905;
Query OK, 0 rows affected (0.07 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| progect1 |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database py1905;
Query OK, 1 row affected (0.00 sec)
mysql> use py1905
Database changed
mysql> show tables;
Empty set (0.01 sec)
mysql> use mysql
Database changed
mysql> show tables
-> ;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> create table user (
-> name varchar(50),
-> gender char(2),
-> age int,
-> tel varchar(11),
-> address varchar(200),
-> birth date,
-> ID varchar(18),
-> photo longblob
-> );
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(50),
gender char(2),
age int,
tel varchar(11),
address varcha' at line 1
mysql> create table user (
-> name varchar(50),
-> gender char(2),
-> age int,
-> tel varchar(11),
-> address varchar(200),
-> birth date,
-> ID varchar(18),
-> photo longblob
-> );
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(50),
gender char(2),
age int,
tel varchar(11),
address varchar(' at line 1
mysql> create table user (
-> name varchar(50),
-> gender char(2),
-> age int,
-> tel varchar(11),
-> address varchar(200),
-> birth date,
-> ID varchar(18),
-> photo longblob
-> );
ERROR 1050 (42S01): Table 'user' already exists
mysql> create table py1905 (
-> name varchar(50),
-> gender char(2),
-> age int,
-> tel varchar(11),
-> address varchar(200),
-> birth date,
-> ID varchar(18),
-> photo longblob
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> show table py1905
-> ;
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 'py1905' at line 1
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| py1905 |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
32 rows in set (0.00 sec)
mysql> use py1905
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table py1905 (
-> name varchar(50),
-> gender char(2),
-> age int,
-> tel varchar(11),
-> address varchar(200),
-> birth date,
-> ID varchar(18),
-> photo longblob
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> show tables;
+------------------+
| Tables_in_py1905 |
+------------------+
| py1905 |
+------------------+
1 row in set (0.00 sec)
mysql> drop table py1905;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql>