进击のpython
数据库——初识sql语句
前面提到了说，数据库管理系统就像我们曾经做过的输入命令返回结果的socket通信差不多
那既然提到了命令，在MySQL中，有一些基本的语句，就应该被知道
刚开始学的时候可能觉得这些这么多的英语单词，我记不住怎么办？
其实你会发现，好多的语句其实跟我们说话的方式很像（我会在介绍语句的时候说），记忆起来就不是很困难
如果是单词的问题，就只能建议你多敲多联系了，“无他，唯手熟尔”
数据库本质也就只是个文件对吧，那是个文件就有：增 查 改 删
所以我们会针对库，表，记录的这四个方法来进行介绍，一定注意，多敲！
库（操作文件夹）
增
create database db1 charset utf8;
（创建 数据库 db1 编码设置 utf8）
mysql> create database db1 charset utf8;
Query OK, 1 row affected (0.05 sec)
这样就相当于建一个文件夹，其实也真的建立了一个文件夹
现在我们是在本机的数据库操作，所以数据都存放在了本地，那我们看看我们新建的数据库文件
找到你的数据库安装目录，能看到一个叫做data的文件夹，看名字也知道这个名字是数据的意思
我们打开它，是不是发现了我们新建的库：db1，而且是文件夹的形式吧！
数据库的命名也是有规范的：
可以由字母、数字、下划线、＠、＃、＄区分大小写唯一性不能使用关键字如 create select不能单独使用数字最长128位
表也可以复制
复制表结构＋记录 （key不会复制: 主键、外键和索引）
create table new_service select * from t1;
mysql> create table new_service select * from t1;
Query OK, 2 rows affected (1.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show
-> tables;
+---------------+
| Tables_in_db1 |
+---------------+
| new_service |
| t1 |
+---------------+
2 rows in set (0.00 sec)
mysql> desc new_service;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num | char(1) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| hobby | char(1) | YES | | NULL | |
| job | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> select * from new_service;
+------+------+------+------+-------+------+
| num | id | name | sex | hobby | job |
+------+------+------+------+-------+------+
| NULL | 1 | 1 | 1 | 男 | 无 |
| NULL | 1 | 1 | 1 | 男 | 无 |
+------+------+------+------+-------+------+
2 rows in set (0.00 sec)
只复制表结构
create table t2 select * from t1 where 1=2; //条件为假，查不到任何记录
mysql> create table t2 select * from t1 where 1=2;
Query OK, 0 rows affected (1.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| new_service |
| t1 |
| t2 |
+---------------+
3 rows in set (0.00 sec)
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num | char(1) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| hobby | char(1) | YES | | NULL | |
| job | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> select * from t2;
Empty set (0.00 sec)
还有种简单的：
create table t3 like t1;
mysql> create table t3 like t1;
Query OK, 0 rows affected (1.03 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| new_service |
| t1 |
| t2 |
| t3 |
+---------------+
4 rows in set (0.00 sec)
mysql> desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num | char(1) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| hobby | char(1) | YES | | NULL | |
| job | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> select * from t3;
Empty set (0.00 sec)
查
show create database db1;
(展示 创建 数据库 db1)
mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
show databases;
(展示 所有数据库)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
改
改其实无非就是改编码格式
alter database db1 charset gbk;
（修改 数据库 db1 编码格式 gbk）
mysql> alter database db1 charset gbk;
Query OK, 1 row affected (0.34 sec)
mysql> show create database db1;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
是不是编码格式被修改了！
删
drop database db1;
(删掉 数据库 db1)
mysql> drop database db1;
Query OK, 0 rows affected (0.41 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
这回所有的数据库中就不再有db1了
那你猜猜在data文件夹里还有db1这个文件夹了吗？？
表（文件）
首先表（文件）必须要在库（文件夹）下，所以操作之前先建一个库db1（文件夹）
然后切换到这个文件夹下：
use db1;
（使用 db1）
mysql> use db1;
Database changed
其实这里可以提一句，你可以通过select database();来看你目前在哪个数据库下（文件夹里）
mysql> select database();
+------------+
| database() |
+------------+
| db1 |
+------------+
1 row in set (0.00 sec)
增
要创建一个表，是不是应该有相应字段（比如前一节例子的 姓名，性别之类的）,同时要给每一个字段的类型
create table t1(id int,name char,age int,sex char,hobby char,job char);
(创建 表 t1(id int型，name char型，... ...))
mysql> create table t1(id int,name char,age int,sex char,hobby char,job char);
Query OK, 0 rows affected (1.17 sec)
那我们去找一下db1文件夹，看看里面是不是新建了表（自己去看）
那总结起来，其实就是如下的格式：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
注意：
- 在同一张表中，字段名是不能相同
- 宽度和约束条件可选
- 字段名和类型是必须的
- 最后一个数据之后不要加'',''
aletr table t1 add times char;
(修改 表 t1 加 times 字符串类型)
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| hobby | char(1) | YES | | NULL | |
| job | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> alter table t1
-> add times char;
Query OK, 0 rows affected (1.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| hobby | char(1) | YES | | NULL | |
| job | char(1) | YES | | NULL | |
| times | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
6 rows in set (0.01 sec)
默认添加在最后,当然你也可以添加到开头
aletr table t1 add times char first;
mysql> alter table t1
-> add num char first;
Query OK, 0 rows affected (1.45 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num | char(1) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| hobby | char(1) | YES | | NULL | |
| job | char(1) | YES | | NULL | |
| times | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
7 rows in set (0.01 sec)
查
show create table t1;
（展示 创建 表 t1）
mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(1) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`hobby` char(1) DEFAULT NULL,
`job` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可能是会拿到这种乱七八糟的，就是因为他想给你拼个表格出来，但是太多了，就拼错了
但是关键的信息我们还是能看到的对吧
show tables;
(展示 所有表)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
desc t1;
(查看列和数据类型 t1)
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num | char(1) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| hobby | char(1) | YES | | NULL | |
| job | char(1) | YES | | NULL | |
| times | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
6 rows in set (0.35 sec)
看着舒服点了吧~~~
改
alter table t1 rename t2;
(修改 表 t1 重命名 t2)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.06 sec)
mysql> alter table t1
-> rename t2;
Query OK, 0 rows affected (0.57 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t2 |
+---------------+
1 row in set (0.00 sec)
alter table t1 modify name char(5);
（修改 表 t1 修改 name字段 char（5））
mysql> alter table t1 modify name char(5);
Query OK, 0 rows affected (1.57 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num | char(1) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| hobby | char(1) | YES | | NULL | |
| job | char(1) | YES | | NULL | |
| times | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
6 rows in set (0.01 sec)
除了改字符长度，还可以修改字段
alter table t1 change name NAME char(5);
(修改 表 t1 改变 name 为 NAME char(5))
mysql> alter table t1 change name NAME char(5);
Query OK, 0 rows affected (0.51 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num | char(1) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| NAME | char(5) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| hobby | char(1) | YES | | NULL | |
| job | char(1) | YES | | NULL | |
| times | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
6 rows in set (0.01 sec)
删
drop table t1;
（删掉 表 t1）
mysql> drop table t1;
Query OK, 0 rows affected (0.55 sec)
mysql> show tables;
Empty set (0.00 sec)
看看你的data文件夹下的db1还有文件了吗？
alter table t1 drop times;
(修改 表 t1 删掉 times)
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num | char(1) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| hobby | char(1) | YES | | NULL | |
| job | char(1) | YES | | NULL | |
| times | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
7 rows in set (0.01 sec)
mysql> alter table t1
-> drop times;
Query OK, 0 rows affected (1.48 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num | char(1) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| hobby | char(1) | YES | | NULL | |
| job | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
6 rows in set (0.01 sec)
接下来的这些写法,你现在可能不知道什么意思,但是要记下来
1.修改存储引擎
mysql> alter table t1
他
-> engine=innodb;
2.添加stu_num到name字段之后
mysql> alter table t1
-> add stu_num int after name;
3.修改字段类型
mysql> alter table t1
-> modify age int(3);
4.增加约束（针对已有的主键增加auto_increment）
mysql> alter table t1 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined
mysql> alter table t1 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
5.对已经存在的表增加复合主键
mysql> alter table t1
-> add primary key(host_ip,port);
6.增加主键
mysql> alter table t1
-> modify name char primary key;
7.增加主键和自动增长
mysql> alter table t1
-> modify id int primary key auto_increment;
8.删除主键
mysql> alter table t1 modify id int(11) not null; #删除自增约束
mysql> alter table t1
-> drop primary key; #删除主键
记录
对记录的操作首先要有库和表对吧
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| hobby | char(1) | YES | | NULL | |
| job | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
5 rows in set (0.44 sec)
增
insert t1(id,name,sex,hobby,job)values(1,'张三',18,'男','无业');
(插入 t1... ...)
mysql> insert t1(id,name,sex,hobby,job)values(1,'张三',18,'男','无业');
Query OK, 1 row affected, 3 warnings (0.44 sec)
多个添加：
insert t1(id,name,sex,hobby,job)values(1,'张三',18,'男','无业'),(2,'李四',20,'女','学生');
mysql> insert t1(id,name,sex,hobby,job)values(1,'张三',18,'男','无业'),(2,'李四',20,'女','学生');
Query OK, 2 rows affected, 6 warnings (0.38 sec)
Records: 2 Duplicates: 0 Warnings: 6
mysql> select * from t1;
+------+------+------+-------+------+
| id | name | sex | hobby | job |
+------+------+------+-------+------+
| 1 | 1 | 1 | 男 | 无 |
| 1 | 张 | 1 | 男 | 无 |
| 2 | 李 | 2 | 女 | 学 |
+------+------+------+-------+------+
3 rows in set (0.00 sec)
查
select * from t1;
(查找 所有 从 t1)
mysql> select * from t1;
+------+------+------+-------+------+
| id | name | sex | hobby | job |
+------+------+------+-------+------+
| 1 | 张 | 1 | 男 | 无 |
+------+------+------+-------+------+
1 row in set (0.00 sec)
这么写的前提是你已经在db1下面了，如果不在 后面的t1 就改写成db1.t1就好了
如果我只查姓名，就把*改成字段name 如果查姓名年龄，就改成 name，age
然后你就会发现，少字了
其实到这就该跟你说，char（数字）代表着这个字段容纳的字符个数，因为都是1，所以只留一个
我们可以修改一下！
改
update db1.t1 set name = '1';
(更新 db1里面的t1表 设置 name 为 1)
一旦是这么写的会发生什么呢？
mysql> update db1.t1 set name = '1';
Query OK, 2 rows affected (0.38 sec)
Rows matched: 3 Changed: 2 Warnings: 0
mysql> select * from t1;
+------+------+------+-------+------+
| id | name | sex | hobby | job |
+------+------+------+-------+------+
| 1 | 1 | 1 | 男 | 无 |
| 1 | 1 | 1 | 男 | 无 |
| 2 | 1 | 2 | 女 | 学 |
+------+------+------+-------+------+
3 rows in set (0.00 sec)
我会发现我把所有的name都改掉了，那我要是只想改id=2的name呢？
update db1.t1 set name = '1' where id = 2;
mysql> update db1.t1 set name = 'haha' where id = 2;
Query OK, 1 row affected, 1 warning (0.38 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> select * from t1;
+------+------+------+-------+------+
| id | name | sex | hobby | job |
+------+------+------+-------+------+
| 1 | 1 | 1 | 男 | 无 |
| 1 | 1 | 1 | 男 | 无 |
| 2 | h | 2 | 女 | 学 |
+------+------+------+-------+------+
3 rows in set (0.00 sec)
删
delete from t1 where id = 2;
(删除 从t1 ... ...)
mysql> delete from t1 where id = 2;
Query OK, 1 row affected (0.38 sec)
mysql> select * from t1;
+------+------+------+-------+------+
| id | name | sex | hobby | job |
+------+------+------+-------+------+
| 1 | 1 | 1 | 男 | 无 |
| 1 | 1 | 1 | 男 | 无 |
+------+------+------+-------+------+
2 rows in set (0.00 sec)