MySQL实战2
MySQL核心语句实践
- 通过多种客户端读写mysql
- mysql命令
- navicat工具使用
- php
- Python
- 不同的业务、给与不通的权限
- 又见王者荣耀数据库
- 数据表的设计，创建
- 修改表的数据
- 索引创建
- 删除数据条目
- 数据插入
- 数据查询
- 修改表数据
Grant语句的实践
开发测试需要连接数据库
开通远程连接的账号，读写数据库
#给测试，开发开通一个连接数据库的账号，且只能够进行读写操作的用户
# create user
# grant 设置权限
#grant语句可以直接创建，且赋值权限，给一个用户
grant insert,select,update,delete on 数据库.数据表 to 用户@'%' identified by '密码';
#创建用户且授权
mysql> grant select,create,update,delete on *.* to xyp@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
#查看用户信息
mysql> select user,host,password from mysql.user;
+---------------+-----------------------+-------------------------------------------+
| user | host | password |
+---------------+-----------------------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| zhengzheng | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| zhengxinqiang | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| xyp | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+-----------------------+-------------------------------------------+
6 rows in set (0.00 sec)
#刷新权限
#一定要记住，修改了user表，创建，修改用户后，立即刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
授权的几大特性,网络安全，服务器安全理念与实践
- 权限，轻而易举，不要使用
all privileges，尽量指定某一个权限，某几个也行 - 主机范围也是，尽量不要给
%，限制一个IP，以及mysql也要更换端口- root密码登入复杂
- 更改ssh端口
- 禁止root用户登入，只允许普通用户登入
- 只用普通用户+公钥的形式
- 修改默认软件的端口，如3306，6379等
- grant 授权对数据库的选择，也尽量别用*,而指定数据库以及数据表
通过后端代码，远程连接mysql
php读写mysql
shell
#安装php，依赖
#Centos种安装php，以及php读写mysql的一个驱动
yum -y install php-mysqlnd php
已安装:
php.x86_64 0:5.4.16-48.el7 php-mysqlnd.x86_64 0:5.4.16-48.el7
作为依赖被安装:
libzip.x86_64 0:0.10.1-8.el7 php-cli.x86_64 0:5.4.16-48.el7 php-common.x86_64 0:5.4.16-48.el7
php-pdo.x86_64 0:5.4.16-48.el7
完毕！
读取mysql数据的php脚本
<?php
#定义数据库的连接信息
$servername = "192.168.20.227";
$username = "root";
$password = "123456";
$dbname = "kings";
//创建链接，括号里面是需要传递的参数
$conn = mysqli_connect($servername, $username, $password, $dbname);
#设置php正确读取中文
mysqli_query($conn,"SET NAMES UTF8");
#Check Connection
#如果数据库连接失败，那么执行异常抛出，程序会主动的报错
#如果数据库连接正常，不执行
if(!$conn) {
die("连接失败：" . mysqli_connect_error());
}
#php连接mysql后执行的SQL语句
$sql = "select * from kings.tanks";
#通过一个php函数，连接，且执行sql语句
//该函数是php在mysql中执行SQL的函数
#对执行的结果，会放在这个变量里面，$result
$result = mysqli_query($conn, $sql);
#对执行结果判断，大于0则表示执行正确
#判断，上述执行的SQL语句的执行结果，影响mysql数据的行数，是否大于0
if (mysqli_num_rows($result) > 0 ) {
//循环获取数据
while ($row = mysqli_fetch_assoc($result)) {
//这里的$row表示每一行数据$row["id"]，表示数据库中的字段
echo "id: " . $row["id"]. " - Name: " . $row["name"]. " " . $row["skills"]. "\n";
}
} else {
echo "--------没有结果--------";
}
#关闭数据库连接，防止内存泄漏
mysqli_close($conn);
?>
执行结果如下
[root@localhost opt]# vim mysql_read.php
[root@localhost opt]# php mysql_read.php
id: 1 - Name: 程咬金 大招回血，血量越低，伤害越高
id: 2 - Name: 大鱼庄周 免疫所有队友的负面控制
php插入数据
<?php
#定义数据库的连接信息
$servername = "192.168.20.227";
$username = "zhengxinqiang";
$password = "123456";
$dbname = "kings";
//创建链接，括号里面是需要传递的参数
$conn = mysqli_connect($servername, $username, $password, $dbname);
#设置php正确读取中文
mysqli_query($conn,"SET NAMES UTF8");
#Check Connection
#如果数据库连接失败，那么执行异常抛出，程序会主动的报错
#如果数据库连接正常，不执行
if(!$conn) {
die("连接失败：" . mysqli_connect_error());
}
#php连接mysql后执行的SQL语句
#自己修改插入表
$sql = "insert into kings.tanks(id,name,skills,price) values(3,'吕布','真实伤害爆棚',18888),(4,'关羽','跑起来贼快','18888')";
#通过一个php函数，连接，且执行sql语句
//该函数是php在mysql中执行SQL的函数
#对执行的结果，会放在这个变量里面，$result
if (mysqli_query($conn, $sql)){
echo "新纪录插入成功";
} else{
echo "Error: " . $sql . "<br>" . mysqli_error($conn) ;
}
#关闭数据库连接，防止内存泄漏
mysqli_close($conn);
?>
执行结果如下
[root@localhost opt]# vim mysql_write.php
[root@localhost opt]# php mysql_write.php
新纪录插入成功
[root@localhost opt]#
[root@localhost opt]# mysql -S /my_mysql/3306/mysql.sock -p123456
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 24
Server version: 5.6.50-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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> select * from kings.tanks;
+----+--------------+--------------------------------------------+-------+
| id | name | skills | price |
+----+--------------+--------------------------------------------+-------+
| 1 | 程咬金 | 大招回血，血量越低，伤害越高 | 8888 |
| 2 | 大鱼庄周 | 免疫所有队友的负面控制 | 2888 |
| 3 | 吕布 | 真实伤害爆棚 | 18888 |
| 4 | 关羽 | 跑起来贼快 | 18888 |
+----+--------------+--------------------------------------------+-------+
4 rows in set (0.00 sec)
mysql>
Python读写mysql
安装python3
#安装解释器
[root@localhost opt]# yum -y install python3 python3-devel
#开发python3脚本即可
#python3读写mysql，就有一个桥梁，有一个驱动
#让python3可以正确的链接mysql
#通过如下命令安装驱动
#安装python3之后自动有一个pip3出现
[root@localhost opt]# which pip3
/usr/bin/pip3
#桥梁已经安装好
[root@localhost opt]# pip3 install pymysql
WARNING: Running pip install with root privileges is generally not a good idea. Try `pip3 install --user` instead.
Collecting pymysql
Downloading https://files.pythonhosted.org/packages/4f/52/a115fe175028b058df353c5a3d5290b71514a83f67078a6482cff24d6137/PyMySQL-1.0.2-py3-none-any.whl (43kB)
100% |████████████████████████████████| 51kB 273kB/s
Installing collected packages: pymysql
Successfully installed pymysql-1.0.2
开发python3读写mysql的脚本
vim python3_mysql_read_write.py
#导入驱动，下载导入并且使用它
import pymysql
#连接
#使用的数据库，连接数据库写入数据
conn = pymysql.Connect(host="192.168.20.227", port=3306, user="zhengxinqiang", passwd="123456", db="kings")
#创建游标,理解为在python中有一双手，可以执行sql语句
cursor = conn.cursor()
#插入数据
sql="insert into tanks(id,name,skills,price) values(5,'项羽','皮糙肉厚，贼抗打，超级坦克','6888')"
#执行sql，并且返回执行的受影响的行数
rows = cursor.execute(sql)
print(f"插入了{rows}行数据")
sql2="select * from tanks;"
#执行SQL查询
cursor.execute(sql2)
print(f"查出的结果是：{cursor.fetchall()}")
#提交事务
conn.commit()
#关闭游标
cursor.close()
#关闭连接
conn.close()
执行py脚本
python3 python3_mysql_read_write.py
[root@localhost opt]# vim python3_mysql_read_write.py
[root@localhost opt]# python3 python3_mysql_read_write.py
插入了1行数据
查出的结果是：((1, '程咬金', '大招回血，血量越低，伤害越高', 8888), (2, '大鱼庄周', '免疫所有队友的负面控制', 2888), (3, '吕布', '真实伤害爆棚', 18888), (4, '关羽', '跑起来贼快', 18888), (5, '项羽', '皮糙肉厚，贼抗打，超级坦克', 6888))
[root@localhost opt]#
mysql> select * from kings.tanks;
+----+--------------+--------------------------------------------+-------+
| id | name | skills | price |
+----+--------------+--------------------------------------------+-------+
| 1 | 程咬金 | 大招回血，血量越低，伤害越高 | 8888 |
| 2 | 大鱼庄周 | 免疫所有队友的负面控制 | 2888 |
| 3 | 吕布 | 真实伤害爆棚 | 18888 |
| 4 | 关羽 | 跑起来贼快 | 18888 |
| 5 | 项羽 | 皮糙肉厚，贼抗打，超级坦克 | 6888 |
+----+--------------+--------------------------------------------+-------+
5 rows in set (0.00 sec)
修改tanks坦克表
alter
修改数据表的名字
#rename 指令 ，语法是
rename table 旧的表名字 to 新的表名字
mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| tanks |
+-----------------+
1 row in set (0.00 sec)
mysql> rename table tanks to Tanks;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| Tanks |
+-----------------+
1 row in set (0.00 sec)
mysql>
alter命令修改数据表的名字
#语法
alter table 当前表名 rename to 新的表名字
mysql> alter table Tanks rename to TANKS;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| TANKS |
+-----------------+
1 row in set (0.00 sec)
mysql>
修改数据表的字段
查看表的具体信息
mysql> desc tanks;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(100) | NO | | NULL | |
| skills | varchar(255) | NO | | NULL | |
| price | int(11) | NO | | NULL | |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#建表语句
mysql> show create table tanks;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tanks | CREATE TABLE `tanks` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(100) NOT NULL,
`skills` varchar(255) NOT NULL,
`price` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
添加字段
Introduction 具体信息介绍
alter指定
#语法
alter table 表名字 add 字段名 字段数据类型（长度） 额外的字段属性；
alter table tanks add introduction varchar(255) not null;
mysql> alter table tanks add introduction varchar(255) not null;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tanks;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(100) | NO | | NULL | |
| skills | varchar(255) | NO | | NULL | |
| price | int(11) | NO | | NULL | |
| introduction | varchar(255) | NO | | NULL | |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> show create table tanks;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tanks | CREATE TABLE `tanks` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(100) NOT NULL,
`skills` varchar(255) NOT NULL,
`price` int(11) NOT NULL,
`introduction` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
添加指定位置的字段
召唤师技能，闪现，点燃等等
枚举类型，二选一字段
加在skills字段后面
# summoner_skills ENUM('flush','ghost') not null defalut 'flush' # flush:闪现；ghost 幽灵疾步；
#插入到数据表中
#添加after 指定插入到哪个字段的后面
alter table tanks add summoner_skills ENUM('flush','ghost') not null default 'flush' after skills;
mysql> alter table tanks add summoner_skills ENUM('flush','ghost') not null default 'flush' after skills;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tanks;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(100) | NO | | NULL | |
| skills | varchar(255) | NO | | NULL | |
| price | int(11) | NO | | NULL | |
| introduction | varchar(255) | NO | | NULL | |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table tanks add summoner_skills ENUM('flush','ghost') not null default 'flush' after skills;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
一次性添加多个字段
#英雄的阵容，英雄图像
camp
pic
#修改表字段语句,并且指定的位置插入字段
#希望的顺序是id name skills summoner_skills camp pic introduction
alter table tanks add camp varchar(50) after price, add pic varchar(255) after camp;
mysql> alter table tanks add summoner_skills ENUM('flush','ghost') not null default 'flush' after skills;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table tanks add camp varchar(50) after price, add pic varchar(255) after camp;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tanks
-> ;
+-----------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(100) | NO | | NULL | |
| skills | varchar(255) | NO | | NULL | |
| summoner_skills | enum('flush','ghost') | NO | | flush | |
| price | int(11) | NO | | NULL | |
| camp | varchar(50) | YES | | NULL | |
| pic | varchar(255) | YES | | NULL | |
| introduction | varchar(255) | NO | | NULL | |
+-----------------+-----------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
删除table的字段
#alter结合drop删除字段
alter table 表名字 drop 字段
mysql> desc tanks;
+-----------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(100) | NO | | NULL | |
| skills | varchar(255) | NO | | NULL | |
| price | int(11) | NO | | NULL | |
| introduction | varchar(255) | NO | | NULL | |
| summoner_skills | enum('flush','ghost') | NO | | flush | |
+-----------------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table tanks drop summoner_skills;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tanks;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(100) | NO | | NULL | |
| skills | varchar(255) | NO | | NULL | |
| price | int(11) | NO | | NULL | |
| introduction | varchar(255) | NO | | NULL | |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
修改表字段的数据类型
修改table字段的数据类型
#语句 pic 的varchar 改为char类型
alter table 表名字 change 旧字段名 新字段名 新数据类型
mysql> alter table tanks change pic pic_url char(200);
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc tanks;
+-----------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(100) | NO | | NULL | |
| skills | varchar(255) | NO | | NULL | |
| summoner_skills | enum('flush','ghost') | NO | | flush | |
| price | int(11) | NO | | NULL | |
| camp | varchar(50) | YES | | NULL | |
| pic_url | char(200) | YES | | NULL | |
| introduction | varchar(255) | NO | | NULL | |
+-----------------+-----------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql数据表的索引的创建
索引的查看与添加，与删除
PRIMARY是一个主键，也是index的一个索引
#查看数据表的索引信息
mysql> show index from mysql.user\G
*************************** 1. row ***************************
Table: user
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: Host
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: user
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: User
Collation: A
Cardinality: 6
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
#查看坦克表的默认索引
mysql> show index from kings.tanks\G
*************************** 1. row ***************************
Table: tanks
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
添加索引
给name字段，添加索引
#语法 alter table 数据表 add index 索引的名字（哪一个字段）
mysql> alter table tanks add index name_index(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from kings.tanks\G
*************************** 1. row ***************************
Table: tanks
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: tanks
Non_unique: 1
Key_name: name_index
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
删除索引
#语法
alter table 表名字 drop index 索引名字；
mysql> alter table tanks drop index name_index;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from kings.tanks\G
*************************** 1. row ***************************
Table: tanks
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
数据表的删除
删除数据库的语法
#语法
#删除数据库
drop database 数据库名字；
#删除数据库表语法
drop table 表名字；
删除数据表之delete语句
语法
delete from 删除数据表的内容
delete from 可以结合where 条件语句，进行数据的指定删除，判断为删除
delete 是一行行的删除数据的，大于大容量的数据删除，效率低下
delete 对于删除有自增id的列，数据删除后，会保留其id的位置
#指定条件where删除
mysql> delete from tanks where id=3;
Query OK, 1 row affected (0.01 sec)
数据源，坦克表的内容
mysql> select * from kings.tanks;
+----+--------------+--------------------------------------------+-----------------+-------+------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+--------------+--------------------------------------------+-----------------+-------+------+---------+--------------+
| 1 | 程咬金 | 大招回血，血量越低，伤害越高 | flush | 8888 | NULL | NULL | |
| 2 | 大鱼庄周 | 免疫所有队友的负面控制 | flush | 2888 | NULL | NULL | |
| 3 | 吕布 | 真实伤害爆棚 | flush | 18888 | NULL | NULL | |
| 4 | 关羽 | 跑起来贼快 | flush | 18888 | NULL | NULL | |
| 5 | 项羽 | 皮糙肉厚，贼抗打，超级坦克 | flush | 6888 | NULL | NULL | |
+----+--------------+--------------------------------------------+-----------------+-------+------+---------+--------------+
5 rows in set (0.00 sec)
delete对于有自增列id的列，数据删除之后，会保留其ID的位置
清空tanks
mysql> select * from kings.tanks;
+----+--------------+--------------------------------------------+-----------------+-------+------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+--------------+--------------------------------------------+-----------------+-------+------+---------+--------------+
| 1 | 程咬金 | 大招回血，血量越低，伤害越高 | flush | 8888 | NULL | NULL | |
| 2 | 大鱼庄周 | 免疫所有队友的负面控制 | flush | 2888 | NULL | NULL | |
| 4 | 关羽 | 跑起来贼快 | flush | 18888 | NULL | NULL | |
| 5 | 项羽 | 皮糙肉厚，贼抗打，超级坦克 | flush | 6888 | NULL | NULL | |
+----+--------------+--------------------------------------------+-----------------+-------+------+---------+--------------+
4 rows in set (0.00 sec)
mysql> delete from tanks;
Query OK, 4 rows affected (0.01 sec)
mysql> select * from kings.tanks;
Empty set (0.00 sec)
mysql> desc tanks;
+-----------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(100) | NO | | NULL | |
| skills | varchar(255) | NO | | NULL | |
| summoner_skills | enum('flush','ghost') | NO | | flush | |
| price | int(11) | NO | | NULL | |
| camp | varchar(50) | YES | | NULL | |
| pic_url | char(200) | YES | | NULL | |
| introduction | varchar(255) | NO | | NULL | |
+-----------------+-----------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql>
#尝试插入数据，注意，此时的数据表，只有主键的属性，（非空且唯一），没有自增的属性；
mysql> insert into tanks(name,skills) values('程咬金','大招贼能回血，还好肉，打不动');
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> select * from tanks;
+----+-----------+--------------------------------------------+-----------------+-------+------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+-----------+--------------------------------------------+-----------------+-------+------+---------+--------------+
| 0 | 程咬金 | 大招贼能回血，还好肉，打不动 | flush | 0 | NULL | NULL | |
+----+-----------+--------------------------------------------+-----------------+-------+------+---------+--------------+
1 row in set (0.00 sec)
#再次插入数据，如果不指定ID，还是插入在0，条目就重复了，不唯一会报错
mysql> insert into tanks(name,skills) values('吕布','团战性大招，也是特别远');
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
给坦克表的id添加自增属性modify
给ID列添加自增属性之后，再来查看，数据的删除，插入，结果是怎样的。
#修改指令，修改字段的数据类型，额外属性用modify
#自增属性的意思就是每一个数据插入之后，自动的id+1的操作
mysql> select * from tanks;
Empty set (0.00 sec)
#主键重复
mysql> alter table tanks modify id int(11) primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined
#把主键去了就行
mysql> alter table tanks modify id int(11) auto_increment;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tanks;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| skills | varchar(255) | NO | | NULL | |
| summoner_skills | enum('flush','ghost') | NO | | flush | |
| price | int(11) | NO | | NULL | |
| camp | varchar(50) | YES | | NULL | |
| pic_url | char(200) | YES | | NULL | |
| introduction | varchar(255) | NO | | NULL | |
+-----------------+-----------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
#插入数据
mysql> insert into tanks(name,skills) values('程咬金','大招回血');
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> insert into tanks(name,skills) values('凯','单挑王');
Query OK, 1 row affected, 2 warnings (0.01 sec)
mysql> insert into tanks(name,skills) values('老夫子','反弹伤害');
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> select * from tanks;
+----+-----------+--------------+-----------------+-------+------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+-----------+--------------+-----------------+-------+------+---------+--------------+
| 1 | 程咬金 | 大招回血 | flush | 0 | NULL | NULL | |
| 2 | 凯 | 单挑王 | flush | 0 | NULL | NULL | |
| 3 | 老夫子 | 反弹伤害 | flush | 0 | NULL | NULL | |
+----+-----------+--------------+-----------------+-------+------+---------+--------------+
3 rows in set (0.01 sec)
#演示数据删除，再次插入数据
mysql> delete from tanks;
Query OK, 3 rows affected (0.00 sec)
mysql> select * from tanks;
Empty set (0.00 sec)
mysql> insert into tanks(name,skills) values('花木兰','切换武器形态');
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> insert into tanks(name,skills) values('刘禅','可以攻击防御塔');
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> select * from tanks;
+----+-----------+-----------------------+-----------------+-------+------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+-----------+-----------------------+-----------------+-------+------+---------+--------------+
| 4 | 花木兰 | 切换武器形态 | flush | 0 | NULL | NULL | |
| 5 | 刘禅 | 可以攻击防御塔 | flush | 0 | NULL | NULL | |
+----+-----------+-----------------------+-----------------+-------+------+---------+--------------+
2 rows in set (0.00 sec)
删除之truncate语法
truncate语句直接清空数据表，重新建立一个表
不会出现自增id的问题，重新计算id的位置
mysql> truncate table tanks;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from tanks;
Empty set (0.00 sec)
mysql> insert into tanks(name,skills,price) values('鲁班','二技能全图，被动扫射',5888),('甄姬','技能会冰冻敌人','2888');
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> select * from tanks;
+----+--------+--------------------------------+-----------------+-------+------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+--------+--------------------------------+-----------------+-------+------+---------+--------------+
| 1 | 鲁班 | 二技能全图，被动扫射 | flush | 5888 | NULL | NULL | |
| 2 | 甄姬 | 技能会冰冻敌人 | flush | 2888 | NULL | NULL | |
+----+--------+--------------------------------+-----------------+-------+------+---------+--------------+
2 rows in set (0.00 sec)
坦克表数据查询
方法1：通过*查询
mysql> select * from tanks;
+----+--------+--------------------------------+-----------------+-------+------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+--------+--------------------------------+-----------------+-------+------+---------+--------------+
| 1 | 鲁班 | 二技能全图，被动扫射 | flush | 5888 | NULL | NULL | |
| 2 | 甄姬 | 技能会冰冻敌人 | flush | 2888 | NULL | NULL | |
+----+--------+--------------------------------+-----------------+-------+------+---------+--------------+
2 rows in set (0.00 sec)
通过指定字段查询
只查看鲁班的技能和价格
mysql> select skills,price from tanks where name='鲁班';
+--------------------------------+-------+
| skills | price |
+--------------------------------+-------+
| 二技能全图，被动扫射 | 5888 |
+--------------------------------+-------+
1 row in set (0.00 sec)
插入测试数据
#注意，批量插入数据的话，要求这部分的数据，有共同的属性
insert into tanks(name,skills,price,summoner_skills,camp) values('阿古朵','其在一个球上','13888','ghost','野兽'),('鲁班大师','可以把队友吸过来','13888','flush','机器'),('猪八戒','攻击敌人回血','8888','ghost','野兽'),('雅典娜','被打死之后可以全图跑动','18888','ghost','神仙派');
mysql> insert into tanks(name,skills,price,summoner_skills,camp) values('阿古朵','其在一个球上','13888','ghost','野兽'),('鲁班大师','可以把队友吸过来','13888','flush','机器'),('猪八戒','攻击敌人回血','8888','ghost','野兽'),('雅典娜','被打死之后可以全图跑动','18888','ghost','神仙派');
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 1
mysql> select * from tanks;
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
| 1 | 鲁班 | 二技能全图，被动扫射 | flush | 5888 | NULL | NULL | |
| 2 | 甄姬 | 技能会冰冻敌人 | flush | 2888 | NULL | NULL | |
| 3 | 阿古朵 | 其在一个球上 | ghost | 13888 | 野兽 | NULL | |
| 4 | 鲁班大师 | 可以把队友吸过来 | flush | 13888 | 机器 | NULL | |
| 5 | 猪八戒 | 攻击敌人回血 | ghost | 8888 | 野兽 | NULL | |
| 6 | 雅典娜 | 被打死之后可以全图跑动 | ghost | 18888 | 神仙派 | NULL | |
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
6 rows in set (0.00 sec)
mysql>
limit限制条目
限制只看2条数据
mysql> select * from tanks where price > 3000 ;
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
| 1 | 鲁班 | 二技能全图，被动扫射 | flush | 5888 | NULL | NULL | |
| 3 | 阿古朵 | 其在一个球上 | ghost | 13888 | 野兽 | NULL | |
| 4 | 鲁班大师 | 可以把队友吸过来 | flush | 13888 | 机器 | NULL | |
| 5 | 猪八戒 | 攻击敌人回血 | ghost | 8888 | 野兽 | NULL | |
| 6 | 雅典娜 | 被打死之后可以全图跑动 | ghost | 18888 | 神仙派 | NULL | |
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
5 rows in set (0.00 sec)
mysql> select * from tanks where price > 3000 limit 2 ;
+----+-----------+--------------------------------+-----------------+-------+--------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+-----------+--------------------------------+-----------------+-------+--------+---------+--------------+
| 1 | 鲁班 | 二技能全图，被动扫射 | flush | 5888 | NULL | NULL | |
| 3 | 阿古朵 | 其在一个球上 | ghost | 13888 | 野兽 | NULL | |
+----+-----------+--------------------------------+-----------------+-------+--------+---------+--------------+
2 rows in set (0.00 sec)
limit语法
limit 2
limit 2,3（从第二个开始，后面三行显示）
mysql> select * from tanks where price > 3000 limit 2 ;
+----+-----------+--------------------------------+-----------------+-------+--------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+-----------+--------------------------------+-----------------+-------+--------+---------+--------------+
| 1 | 鲁班 | 二技能全图，被动扫射 | flush | 5888 | NULL | NULL | |
| 3 | 阿古朵 | 其在一个球上 | ghost | 13888 | 野兽 | NULL | |
+----+-----------+--------------------------------+-----------------+-------+--------+---------+--------------+
2 rows in set (0.00 sec)
mysql> select * from tanks where price > 3000 limit 2,3;
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
| 4 | 鲁班大师 | 可以把队友吸过来 | flush | 13888 | 机器 | NULL | |
| 5 | 猪八戒 | 攻击敌人回血 | ghost | 8888 | 野兽 | NULL | |
| 6 | 雅典娜 | 被打死之后可以全图跑动 | ghost | 18888 | 神仙派 | NULL | |
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
3 rows in set (0.00 sec)
指定条件
#查询某一个数据
#找到ID为5的数据
mysql> select * from tanks where id=5;
+----+-----------+--------------------+-----------------+-------+--------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+-----------+--------------------+-----------------+-------+--------+---------+--------------+
| 5 | 猪八戒 | 攻击敌人回血 | ghost | 8888 | 野兽 | NULL | |
+----+-----------+--------------------+-----------------+-------+--------+---------+--------------+
1 row in set (0.00 sec)
#找出ID大于4并且价格大于8000的记录
mysql> select * from tanks where id > 4 and price >8000;
+----+-----------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+-----------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
| 5 | 猪八戒 | 攻击敌人回血 | ghost | 8888 | 野兽 | NULL | |
| 6 | 雅典娜 | 被打死之后可以全图跑动 | ghost | 18888 | 神仙派 | NULL | |
+----+-----------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
2 rows in set (0.00 sec)
#找出格大于8000的记录且属于野兽的
mysql> select * from tanks where price > 3000 and camp = '野兽';
+----+-----------+--------------------+-----------------+-------+--------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+-----------+--------------------+-----------------+-------+--------+---------+--------------+
| 3 | 阿古朵 | 其在一个球上 | ghost | 13888 | 野兽 | NULL | |
| 5 | 猪八戒 | 攻击敌人回血 | ghost | 8888 | 野兽 | NULL | |
+----+-----------+--------------------+-----------------+-------+--------+---------+--------------+
2 rows in set (0.00 sec)
结果排序
mysql> insert into tanks(name,skills,price,camp) values('钟馗','钩子特别恶心','15555','灵魂'),('钟馗','钩子特别恶心','15555','灵魂'),('梦奇','泡泡无限护盾','7555','灵魂'),('刘邦',.'大招全图支援','8555','神仙派'),('亚瑟','人在塔在','9225','神仙派');
Query OK, 5 rows affected, 1 warning (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 1
mysql> select * from tanks;
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
| 1 | 鲁班 | 二技能全图，被动扫射 | flush | 5888 | NULL | NULL | |
| 2 | 甄姬 | 技能会冰冻敌人 | flush | 2888 | NULL | NULL | |
| 3 | 阿古朵 | 其在一个球上 | ghost | 13888 | 野兽 | NULL | |
| 4 | 鲁班大师 | 可以把队友吸过来 | flush | 13888 | 机器 | NULL | |
| 5 | 猪八戒 | 攻击敌人回血 | ghost | 8888 | 野兽 | NULL | |
| 6 | 雅典娜 | 被打死之后可以全图跑动 | ghost | 18888 | 神仙派 | NULL | |
| 7 | 钟馗 | 钩子特别恶心 | flush | 15555 | 灵魂 | NULL | |
| 8 | 钟馗 | 钩子特别恶心 | flush | 15555 | 灵魂 | NULL | |
| 9 | 梦奇 | 泡泡无限护盾 | flush | 7555 | 灵魂 | NULL | |
| 10 | 刘邦 | 大招全图支援 | flush | 8555 | 神仙派 | NULL | |
| 11 | 亚瑟 | 人在塔在 | flush | 9225 | 神仙派 | NULL | |
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
11 rows in set (0.00 sec)
排序结果
默认是升序，asc排序规则，从小到大
mysql> select id,name,skills,price,camp from tanks where price > 4000 order by price ;
+----+--------------+-----------------------------------+-------+-----------+
| id | name | skills | price | camp |
+----+--------------+-----------------------------------+-------+-----------+
| 1 | 鲁班 | 二技能全图，被动扫射 | 5888 | NULL |
| 9 | 梦奇 | 泡泡无限护盾 | 7555 | 灵魂 |
| 10 | 刘邦 | 大招全图支援 | 8555 | 神仙派 |
| 5 | 猪八戒 | 攻击敌人回血 | 8888 | 野兽 |
| 11 | 亚瑟 | 人在塔在 | 9225 | 神仙派 |
| 3 | 阿古朵 | 其在一个球上 | 13888 | 野兽 |
| 4 | 鲁班大师 | 可以把队友吸过来 | 13888 | 机器 |
| 7 | 钟馗 | 钩子特别恶心 | 15555 | 灵魂 |
| 8 | 钟馗 | 钩子特别恶心 | 15555 | 灵魂 |
| 6 | 雅典娜 | 被打死之后可以全图跑动 | 18888 | 神仙派 |
+----+--------------+-----------------------------------+-------+-----------+
10 rows in set (0.00 sec)
#降序排序
mysql> select id,name,skills,price,camp from tanks where price > 4000 order by price desc ;
+----+--------------+-----------------------------------+-------+-----------+
| id | name | skills | price | camp |
+----+--------------+-----------------------------------+-------+-----------+
| 6 | 雅典娜 | 被打死之后可以全图跑动 | 18888 | 神仙派 |
| 7 | 钟馗 | 钩子特别恶心 | 15555 | 灵魂 |
| 8 | 钟馗 | 钩子特别恶心 | 15555 | 灵魂 |
| 3 | 阿古朵 | 其在一个球上 | 13888 | 野兽 |
| 4 | 鲁班大师 | 可以把队友吸过来 | 13888 | 机器 |
| 11 | 亚瑟 | 人在塔在 | 9225 | 神仙派 |
| 5 | 猪八戒 | 攻击敌人回血 | 8888 | 野兽 |
| 10 | 刘邦 | 大招全图支援 | 8555 | 神仙派 |
| 9 | 梦奇 | 泡泡无限护盾 | 7555 | 灵魂 |
| 1 | 鲁班 | 二技能全图，被动扫射 | 5888 | NULL |
+----+--------------+-----------------------------------+-------+-----------+
10 rows in set (0.00 sec)
修改表数据update
update 数据库.表名字 set 字段=字段新内容 where 查询条件
update 数据库.表名字 set 字段=字段新内容,字段2=字段2新内容 where 查询条件
mysql> select * from tanks;
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
| 5 | 猪八戒 | 攻击敌人回血 | ghost | 8888 | 野兽 | NULL | |
#更改一个字段
mysql> update kings.tanks set price=7999 where name='猪八戒';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tanks;
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------
| 5 | 猪八戒 | 攻击敌人回血 | ghost | 7999 | 野兽 | NULL | |
11 rows in set (0.00 sec)
#修改多个字段
mysql> update kings.tanks set price=19999,summoner_skills='flush' where name='猪八戒';
Query OK, 1 row affected (0.39 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tanks where id=5;
+----+-----------+--------------------+-----------------+-------+--------+---------+--------------+
| id | name | skills | summoner_skills | price | camp | pic_url | introduction |
+----+-----------+--------------------+-----------------+-------+--------+---------+--------------+
| 5 | 猪八戒 | 攻击敌人回血 | flush | 19999 | 野兽 | NULL | |
+----+-----------+--------------------+-----------------+-------+--------+---------+--------------+
1 row in set (0.00 sec)
mysql>
浙公网安备 33010602011771号