Mysql命令
MySql数据库学习笔记：
注意:所有的数据库名，表名，表字段都是区分大小写的。所以你在使用SQL命令时需要输入正确的变量名称。
SQL语句中的单行注释使用 -- 多行注释采用 /*…*/
1.创建数据库：
create database 数据库名；
2.删除数据库：
drop database 数据库名;
3.选中数据库：
use 数据库名;
4.查看数据库中表：
show tables;
5.创建表：
create table name(
id int(10) NOT NULL AUTO_INCREMENT,
title varchar(20) ,
data varchar(40),
primary key(id)
);
6.查看数据表的结构：
describe 数据表名;(desc 数据表名;)
7.删除表：
drop table 表名;
8.插入数据
insert into name
(id,title,data)
values
(3,"boy",89);
9.读取数据表：
select * from runoob_tbl;
SELECT 列名,列名
FROM 表名
[WHERE 条件]
[LIMIT N][ OFFSET M]
10.插入多行数据：
insert into name
(id,title,data)
values
(7,"boy",90),
(8,"boy",90),
(9,"boy",90),
(10,"boy",90),
(11,"boy",90),
(12,"boy",90),
(13,"boy",90);
insert into name
(id,title,data)
values
(14,"ttt",22),
(15,"ttt",22),
(16,"ttt",22);
11.like子句来读取条件数据
select *from name where data like "%0";
insert into tablename
(id,title,data)
values
(1,"ttt",22),
(2,"ttt",22),
(3,"ttt",22);
12.联合查询：
select id from name
union all
select name from tablename
order by id;
13.排序：
select *from name order by id asc; 顺序
select *from name order by id desc; 倒叙排列
14.交集、补集：
select * from name as a inner join tablename b on a.id=b.id; 交集inner可以省略
select * from name as a left join tablename b on a.id=b.id; 取得name表的数据
select * from name as a right join tablename b on a.id=b.id; 取得tablename表的数据
交集：
mysql> select * from name as a join tablename b on a.id=b.id;
+----+-------+------+----+------+
| id | title | data | id | name |
+----+-------+------+----+------+
| 1 | boy | 80 | 1 | a |
| 2 | boy | 90 | 2 | b |
| 3 | boy | 90 | 3 | c |
| 4 | girl | 91 | 4 | d |
| 5 | girl | NULL | 5 | e |
| 6 | boy | 90 | 6 | f |
+----+-------+------+----+------+
取得name表的数据 6 rows in set (0.00 sec)
mysql> select * from name as a left join tablename b on a.id=b.id;
+----+-------+------+------+------+
| id | title | data | id | name |
+----+-------+------+------+------+
| 1 | boy | 80 | 1 | a |
| 2 | boy | 90 | 2 | b |
| 3 | boy | 90 | 3 | c |
| 4 | girl | 91 | 4 | d |
| 5 | girl | NULL | 5 | e |
| 6 | boy | 90 | 6 | f |
| 7 | boy | 90 | NULL | NULL |
| 8 | boy | 90 | NULL | NULL |
| 9 | boy | 90 | NULL | NULL |
| 10 | boy | 90 | NULL | NULL |
| 11 | boy | 90 | NULL | NULL |
| 12 | boy | 90 | NULL | NULL |
| 13 | boy | 90 | NULL | NULL |
| 15 | ttt | 22 | NULL | NULL |
| 14 | ttt | 22 | NULL | NULL |
| 16 | ttt | 22 | NULL | NULL |
+----+-------+------+------+------+
16 rows in set (0.00 sec)
mysql> select * from name as a right join tablename b on a.id=b.id;
+------+-------+------+----+------+
| id | title | data | id | name |
+------+-------+------+----+------+
| 1 | boy | 80 | 1 | a |
| 2 | boy | 90 | 2 | b |
| 3 | boy | 90 | 3 | c |
| 4 | girl | 91 | 4 | d |
| 5 | girl | NULL | 5 | e |
| 6 | boy | 90 | 6 | f |
+------+-------+------+----+------+
6 rows in set (0.00 sec)
15.查询内容为null值的方法：
select * from name where data is null; 空值的查询
select * from name where data is not null; 非空值的查询
16.正则表达式（regexp）：
select * from name where title regexp '^b'; title字段中以“b”开头的所有数据
select * from name where title regexp "boy$"; 以“boy”为结尾的所有数据
select * from name where title regexp "bo"; 包含“o”字符串
17.事物控制： begin commit
18.修改字段命令：alter命令
alter table name add i int; 添加新字段
alter table name add j int(8) after data; 添加新字段j 类型为int（8）位置在data之后
alter table name drop j; 删除字段
alter table name change i j int(8); 修改字段名和字段类型
default
19.添加索引：
create index jjj on name(data); 添加索引jjj
alter table name add index iii(id); 添加索引iii
show index from name； 显示索引
alter table name drop index jjj; 删除索引
20.导出数据表：
select * from name into outfile "D:\Program Files\phpStudy_2016.11.03\MySQL\data\dump.txt";
select * from name into outfile "C:\Users\lenovo\Desktop\dump.txt";
21.函数使用：
SELECT ABS(-1) --- 求绝对值，返回1
SQL注入学习：
order by 来判断字段
select * from user order by 3; 正确执行
select * from user order by 4; 错误执行，说明字段为3
22.SQL注入，以SQL-labs为靶场环境：
原理：select * from users where id = '1' and 1=1 --+; //+号解释为空格 也可以为" #"或者%20.
23.SQL-lasbs Less-1：
1、添加两行显示代码，便于调试：
echo "有效输入：".$id."<br>";
echo "执行的MySQL语言为:"."$sql"."<br>";
2、
