MySQL -2 指令

客户端SQL指令记录： -- 针对数据库和针对数据表

（一）数据库

1. 查看当前所有数据库：show databases;

2. 创建数据库：create database 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

3. 删除数据库：drop database 数据库名;

4. 进入数据库：use 数据库; 》》 show tables;

python 代码远程发送SQL指令给MySQL

英语单词：

collate: vt.核对，校对；校勘 ==> 字符集的设定，需要校对

cursor : 游标；n.光标；（计算尺的）[计] 游标，指针

commit: 委托，==》 connect 对象类似于一个中间人，每次需要execute SQL指令，都需要中介人委托一下；而要求的SQL指令会记录到游标中进行传递信息；

fetch: v.（去）拿来，（去）找来

import pymysql

# 连接MySQL
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset='utf8')
cursor = conn.cursor()

#发送指令
cursor.execute("show databases")  #会有返回值 5，意思是有五个数据库
result = cursor.fetchall()
print(result)  #(('information_schema',), ('db2',), ('mysql',), ('performance_schema',), ('sys',))

cursor.execute("create database db3 default charset utf8 collate utf8_general_ci")
conn.commit()

cursor.execute("show databases")
result=cursor.fetchall()
print(result)

cursor.execute("drop database db3")
conn.commit()

cursor.execute("show databases")
result=cursor.fetchall()
print(result)

cursor.execute("use mysql")
cursor.execute("show tables") # 返回值31
result = cursor.fetchall()
print(result)

cursor.close()
conn.close()

Python 3.8.8 (tags/v3.8.8:024d805, Feb 19 2021, 13:18:16) [MSC v.1928 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license()" for more information.
>>> import pymysql
>>> conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset='utf8')
>>> cursor = conn.cursor()
>>> cursor.execute("show databases")
5
>>> result = cursor.fetchall()
>>> print(result)
(('information_schema',), ('db2',), ('mysql',), ('performance_schema',), ('sys',))
>>> cursor.execute("create database db3 default charset utf8 collate utf8_general_ci")
1
>>> conn.commit()
>>> cursor.execute("show databases")
6
>>> result=cursor.fetchall()
>>> print(result)
(('information_schema',), ('db2',), ('db3',), ('mysql',), ('performance_schema',), ('sys',))
>>> cursor.execute("drop database db3")
0
>>> conn.commit()
>>> cursor.execute("show databases")
5
>>> result=cursor.fetchall()
>>> print(result)
(('information_schema',), ('db2',), ('mysql',), ('performance_schema',), ('sys',))
>>> cursor.execute("use mysql")
0
>>> cursor.execute("show tables")
31
>>> result = cursor.fetchall()
>>> print(result)
(('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',))
>>>

（二）数据表

enter database : use databasename;

check all tables: show tables:

create table structure:

create table tablename(

columname type,

columname type,

columname type

)default charset=utf8;

可以为空，不可以为空，默认值，主键等特性，以及表的增删改查等等看下图

英语词汇：

increment ：自增，稳固增长

alter ：改变，改动

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db2                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.06 sec)

mysql> use db2
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table tb1(
    -> id int,
    -> name varchar(16)
    -> )default charset=utf8;
Query OK, 0 rows affected (0.11 sec)

mysql> create table tb2(
    -> id int,
    -> name varchar(16) not null,
    -> email varchar(32) null,
    -> age int
    -> )default charset=utf8;
Query OK, 0 rows affected (0.07 sec)

mysql> create table tb3(
    -> id int,
    -> name varchar(16) not null,
    -> email varchar(32) null,
    -> age int default 3
    -> )default charset=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> create table tb4(
    -> id int primary key,
    -> name varchar(16) not null,
    -> email varchar(32) null,
    -> age int default 3
    -> )default charset=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> create table tb5(
    -> id int not null auto_increment primary key,
    -> name varchar(16) not null,
    -> email varchar(32) null,
    -> age int default 3
    -> )default charset=utf8;
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| tb1           |
| tb2           |
| tb3           |
| tb4           |
| tb5           |
+---------------+
5 rows in set (0.00 sec)

mysql>

修改的操作：增删改查

查看表：desc tb2;