MySQL数据库

标签：username where 数据库查询 MySQL nbiao id select

每日格言：你对这行的兴趣，决定你在这行的成就！

一、数据库基本操作

1、登录MySQL

本地登录

mysql -uroot -proot -P3306 -h127.0.0.1

设置远程登陆

GRANT ALL ON *.* TO root@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

这句话意思：允许任何IP地址（上面的 % 就是这个意思）的电脑用root帐户和密码（root）来访问这个MySQL Server，当然如果改成admin就只允许admin用户登录，root就无法远程登录。

2、基础命令

2.1 MySQL三种退出方式

exit;
quit;
\q;

2.2 查看所有数据库及所在文件夹

show databases; // 查看所有数据库
use mysql; // 进入数据库
select database(); // 查看当前所在位置

2.3 MySQL注释符

#注释内容，表示单行注释
"-- 注释内容（注：--后面有一个空格）"
/*注释内容*/

2.4 MySQL增删数据库

create database kalpa; // 增加数据库
drop database kalpa; // 删除数据库

2.5 改数据库值

slter database kalpa charset utf8; // 非重点，知道就行

2.6 查看数据表和表资源信息

表的内容包含：表结构，字段类型，主键，是否为空等属性。

show tables; // 查看数据列表信息
desc biao; // 查看数据表内容
show status; // 显示一些系统特定资源的信息，例如，正在运行的线程数量

2.7 筛查数据表用户密码

mysql> select * from user;  # 查看user表
+----+------+----------+
| ID | name | password |
+----+------+----------+
|  1 | root | toor     |
+----+------+----------+
1 row in set (0.00 sec)

mysql> select password from user where name='root';  # 筛查数据表用户密码
+----------+
| password |
+----------+
| toor     |
+----------+
1 row in set (0.00 sec)

二、数据库表的操作

1、table表的操作

1.1 简单创建table数据表

create table users(id int(7));  # 创建table表，给予长度7

1.2 创建完整表

create table biao(id int(7)auto_increment,username char(100) not null,password char(100) not null,primary key(id))engine=innodb default charset=utf8;

create table biao(id int(7))  # 创建表
auto_increment  # 标识该属性的值是自动增加，这是MySQL的SQL语句的特色
username char(100) not null  # 字段、数据类型和长度值、不能为空
primary key(id)  # 标识该属性为该表的主键，可以唯一的标识对应的元组
engine=innodb  # 设置表的引擎和默认的字符类型，常见的数据库引擎InnoDB myisam
default  # 为该属性设置默认值

常见约束条件：
PRIMARY KEY     # 标识该属性为该表的主键，可以唯一的标识对应的元组
FOREIGN KEY     # 标识该属性为该表的外键，是与之联系某表的主键
NOT NULL        # 标识该属性不能为空
UNIQUE          # 标识该属性的值是唯一的
AUTO_INCREMENT  # 标识该属性的值是自动增加，这是MySQL的SQL语句的特色
DEFAULT         # 为该属性设置默认值

2、表格基础操作

2.1 修改表名

# ALTER TABLE 旧表名 RENAME 新表名;
alter table biao rename nbiao;

2.2 修改字段数据类型

# ALTER TABLE 表名 MODIFY 属性名 数据类型;
alter table nbiao modify username varchar(155);

2.3 修改字段名

# ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;
alter table nbiao change username nname char(111);

2.4 增加字段名

# ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];
alter table nbiao add shell char(100) not null;

# 批量添加多个字段
alter table nbiao add(s1 char(10) not null,s2 char(10) not null);

2.5 删除字段

# ALTER TABLE 表名 DROP 属性名;
alter table nbiao drop shell;

2.6 修改储存表引擎

 # ALTER TABLE 表名 ENGINE = 存储引擎名;
 alter table nbiao engine=myisam;

三、数据库增删改查

1、表中所有字段添加数据

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| username | varchar(255) | YES  |     | NULL    |                |
| password | varchar(255) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

# INSERT语句中指定所有字段名
insert into nbiao(id,username,password)values(1,'kalpa','root');

# INSERT语句中不指定字段名
insert into nbiao values(null,'test','123456');

2、为表中的指定字段添加数据

# 指定字段添加数据递增状态
insert into nbiao (username)values('user');

# 增加多条数据1
insert into nbiao (id,username,password) values (null,'u1','abc'),(null,'u2','xyz');

# 增加多条数据2
insert into nbiao values(null,'u3','efg'),(null,'u4','opq');

3、在表中删除数据

# 指定删除，这种方法删除数据后，增加数据id会从后开始增添
delete from nbiao where id=5;

# 全部删除，这种方法删除数据后，增加数据id=1开始算
truncate table nbiao;

# 全部删除，这种方法删除数据后，增加数据id会从后开始增添
delete from nbiao;

4、更改表中数据

更新数据指对表中现存的数据进行更改

# 修改单个数据
update nbiao set username='root' where id=2;

# 修改多种类型数据
update nbiao set username='test',password='tset' where id=1;

#修改所有类型数据
update nbiao set password='test';

四、查询数据

1、表单查询

# 查询所有数据
select * from biao;

# 查询单个类型数据
select username from biao;

# 查询多个类型数据
select username,password from biao;

2、按条件查询

# 条件查询
select * from biao where id=1;

# 按指定条件查询
select * from biao where id>=1;

关系运算符说明：
=   等于
<>  不等于
!=  不等于< 小于
<=  小于等于
>   大于
>=  大于等于

3、带IN关键字的查询

select * from nbiao where id in(1,3);

select * from nbiao where id not in(2);

4、空值查询

# 空值查询
select * from nbiao where id is null;

# 非空查询
select * from nbiao where id is not null;

5、去掉重复查询

select distinct password from nbiao;

6、带LIKE关键字进行模糊查询

6.1 常用百分号通配符

select * from nbiao where username like "%e";

select * from nbiao where username like "%e%";

6.2 下划线通配符

# 一个数值加一个_
select * from nbiao where username like "u___";

select * from nbiao where username like "use_";

7、带AND和OR关键字的多条件查询

7.1 带AND关键字的多条件查询

# AND左右都成立
select * from nbiao where id<9 and username='user';

7.2 带OR关键字的多条件查询

# OR左右两侧满足一个条件即可
select * from nbiao where id=9 or username='user';

7.3 OR和AND一起使用

select * from nbiao where id>1 and password='pass' or username='root';

select * from nbiao where id>1 and password='pass' and username='root';

注：AND的优先级高于OR

OR和AND一起使用的时候，会先运算AND两边的表达式，再运算OR两边的表达式。

五、高级查询数据

1、聚合查询

# 关键字说明：
COUNT()     返回某列的行数
SUM()       返回某列值的和
AVG()       返回某列的平均值
MAX()       返回某列的最大值
MIN()       返回某列的最小值


# 返回id列的行数
select count(id) from nbiao;
# 返回表的行数
select count(*) from nbiao;

# 返回id列值的和
select sum(id) from nbiao;

# 返回id列的平均值
select avg(id) from nbiao;

# 返回id列的最大值
select max(id) from nbiao;

# 返回id列的最小值
select min(id) from nbiao;

2、分组查询

2.1 单独使用 GROUP BY 进行分组

# 对username去重查询整个表
select * from nbiao group by username;

# 对password去重查询整个表
select * from nbiao group by password;

2.2 GROUP BY 和 HAVING 关键字一起使用

# 查询nbiao对username去重后输出username所对应的id列最大的行值
select max(id) from nbiao group by username;

# 查询nbiao对username去重后输出username所对应的id列最小的行值
select min(id) from nbiao group by username;

# 查询nbiao对username去重后依次输出username重复行的数量
select count(id) from nbiao group by username;

# 查询nbiao对username相同的id值加和
select sum(id) from nbiao group by username;

HAVING关键字和WHERE关键字的作用相同，区别在于HAVING关键字可以跟聚合函数，而WHERE关键字不能。通常HAVING关键字都和GROUP BY一起使用，用于对分组后的结果进行过滤

# 查询sum函数求和中小于6的数据返回
select * from nbiao group by username having sum(id)<6;

3、使用 LIMIT 限制查询结果的数量

# 查看表中三行数据
select * from nbiao limit 3;

# 查看表中从第2条开始（不包含第二条）显示后一条数据
select * from nbiao limit 2,1;

4、为表和字段取别名

4.1 表取别名

# 数据量非常大的情况下，用as定义类型别名查询
select * from nbiao as u where u.id=3;

4.2 字段取别名

# 字段名username变成myname
select username as myname from nbiao;

六、MySQL的子查询

1、where型子查询

# "*"类型需要选择查询类型（把内层查询结果当作外层查询的比较条件）
select * from "*" where id in (select * from "*" where id>1);

select * from nbiao where id in (select id from nbiao where id>1);

2、from型子查询

# 把内层的查询结果供外层再次查询

select * from (select * from nbiao where id<3) as age;
# (select * from nbiao where id<3)查询出来的是一个集合别名为age，与 select * from age 内容一样

3、exists型子查询

# 把外层查询结果拿到内层，看内层的查询是否成立。假如()内返回成立，则输出所有，返回不成立则无。

select * from nbiao where exists(select * from nbiao where id>3);

4、联合查询（两个表的查询）

select * from nbiao union select *,0 from biao;
# *,0 是指两个表列不一致的情况，添加可相匹配列后返回查询数据

七、数据库内、左和右连接操作

1、利用Navicat或者Workbench操作数据库

1.1 登录数据库

1.2 创建a_new表

1.3 创建b_new表

1.4 查询表

2、数据库内连接

关键字：inner join on

# 说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分

select * from a_new a inner join b_new b on a.class_id=b.user_id;

3、数据库左连接

关键字：left join on / left outer join on

# 说明：left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。
# 左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录，右表记录不足的地方均为NULL。

select * from a_new a left join b_new b on a.class_id=b.user_id;

4、数据库右连接

关键字：right join on / right outer join on

# 说明：right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。
# 与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来，左表记录不足的地方均为NULL。

select * from a_new a right join b_new b on a.class_id=b.user_id;

八、IFORMATION_SCHEMA详解

1、跨库查询

跨库查询是SQL注入的一种，information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。

元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等（“数据词典”和“系统目录”）。

MySQL中把 information_schema 看作是一个信息数据库。其中保存着关于MySQL服务器所维护的所有其它数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。

INFORMATION_SCHEMA中有数个只读表。它们实际上是视图，而不是基本表，因此无法看到与之相关的任何文件。

2、information_schema数据库表

2.1 information_schema数据库表说明

SCHEMATA表：提供MySQL中所有数据库的信息，show databases的结果取之此表。
TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema、表类型、表引擎、创建时间等信息，show tables from schemaname的结果取之此表。
COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息，show columns from schemaname.tablename的结果取之此表。
STATISTICS表：提供了关于表索引的信息，show index from schemaname.tablename的结果取之此表。
USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表，是非标准表。
SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表，是非标准表。
TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表，是非标准表。
COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表，是非标准表。
CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息，SHOW CHARACTER SET结果集取之此表。
COLLATIONS表：提供了关于各字符集的对照信息。
COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。
TABLE_CONSTRAINTS表：描述了存在约束的表，以及表的约束类型。
KEY_COLUMN_USAGE表：描述了具有约束的键列。
ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。
VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。
TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表。

3、information_schema数据库-三种表

3.1 三种重要表格介绍

SCHEMATA        #包含了所有数据库名、类型、字符集等
TABLES          #新建所有表的信息
COLUMNS         #列的类型

# 开始进行联动查询
select * from information_schema.SCHEMATA

3.2 查询SCHEMATA库

# 查询出库中第一个表里的第一行数据
select schema_name from information_schema.schemata limit 0,1;

# 查询出库中第二个表里的第一行数据，下面数据以此类推
select schema_name from information_schema.schemata limit 1,1;

3.3 查询库中TABLES表

# 查询出表中第一个数据信息
select * from information_schema.tables where table_schema='kalpa' limit 0,1;

在平常SQL注入中，不会直接写账号，需要转换值：

select HEX('kalpa');
6B616C7061

# 可以通过转值查询，该思路更好理解SQL注入
select * from information_schema.tables where table_schema=0x6B616C7061 limit 0,1;

3.4 查询COLUMNS列

3.4.1 方法一

select column_name from information_schema.columns where table_schema=0x6B616C7061 and table_name='nbiao';

3.4.2 方法二

select column_name from information_schema.columns where table_name='nbiao';

3.5 综合查询

# 通过information_schema系统库里面表来查询其它库表的数据，跨库查询需要比较大的权限才可以
select id,username,password from kalpa.nbiao;

标签：username,where,数据库,查询,MySQL,nbiao,id,select
From： https://www.cnblogs.com/HKalpa/p/16738684.html