mysql学习下

1：添加数据

1.1为表中所有字段添加数据

1.1.1NSERT 语句中指定所有字段名

语法：INSERT INTO 表名(字段名1，字段名2，…)VALUES(值1，值2，…);

例题：向student表中插⼊（id为1，姓名：xiaoqiao，年龄：18，性别：woman，⽣⽇：2002）数据。

insert into student(id,name,age,sex,birthday) values(1,"xiaoqiao",18,"woman","2002");

1.1.2 INSERT语句中不指定字段名语法：

INSERT INTO 表名 VALUES(值11，值2，…);

例题：向student表中插⼊（id为2，姓名：daqiao，年龄：20，性别：woman，⽣⽇：2004）数据。

insert into student values(2,"daqiao",20,"woman","2000");

1.1.3为表的指定字段添加数据

为指定字段添加数据，即只向部分字段添加值，⽽其它字段的值为表定义时的默认值。

语法：INSERT INTO 表名（字段1，字段2，…）VALUES(值1，值2，…);

例题：向student表中插⼊（id为3，姓名：zhouyu，年龄：18，）数据。

insert into student(id,name,age) values(3,"xaoqiao",18);

1.1.4同时添加多条数据

语法：INSERT INTO 表名(字段名1，字段名2，…) VALUES （值1，值2，…),(值1，值2);

例题：向student表中插⼊（id为4，姓名：sunce，年龄：20，性别：man，⽣⽇：2004）和（id为5，姓名：caocao，年龄：20，性别：man，⽣⽇：2004）数据

insert into student values(4,"sunce",20,"man","2000"),(5,"caocao",20,"man","2000");

2：删除数据

2.1删除部分数据

即删除指定的部分数据，需要使⽤WHERE⼦句来指定删除记录的条件。

语法：DELETE FROM 表名 WHERE 条件表达式；

例题：向student表中删除id为1的数据。

delete from student where id=1

2.2删除全部数据（千万不要）

若 DELETE 语句中没有使⽤WHERE语句，则会将表中所有记录都删除。

语法：DELETE FROM 表名;

例题：向student表中删除所有的数据。

delete from student ;

3：修改数据

3.1UPDATE 更新部分数据

指更新指定表中的指定记录，使⽤WHERE ⼦句来指定

语法：UPDATE 表名 SET 字段名1=值1，[ ，字段名2=值2，…] [ WHERE 条件表达式

例题：向student表中id为1的数据中字段age改为16，字段birthday改为2004

update student set age=16,birthday=2004 where id=1;

3.2UPDATE 更新全部数据

若 UPDATE 语句中没有使⽤WHERE语句，则会将表中所有记录都删除。

语法：UPDATE FROM 表名;

例题：向student表中将age字段都该为17。

update student set age=17;

4：查询数据

4.1简单查询

1.查询所有字段

语法：SELECT 字段名1，字段名2，…FROM 表名；

例题：查询student表中所有数据。

SELECT id,name,age ,sex,birthday FROM student;

select * from student;

2查询指定的部分字段

语法：SELECT 字段名1，字段名2，… FROM 表名;

例题：查询student表中所有的name字段和sex字段。

SELECT name,sex FROM student;

注意：字段顺序可以更改，则显示的结果也会作出对应的调整。

4.2按条件查询

1.带关系运算符的查询

语法：SELECT 字段名1，字段名2，…FROM 表名 WHERE 条件表达式；

例题：查询student表中id为4的⼈的id和name字段。

SELECT id,name FROM student WHERE id=4;

2.带 IN 关键字的查询

语法：SELECT * 字段名1,字段名2,…FROM 表名 WHERE 字段名 [ NOT ] IN （元素1，元素2，…）；

解释：IN关键字⽤于判断某个字段的值是否在指定集合中，若在，则该字段所在的记录将会被查询出来

例题：查询student表中id值为1,2,3的记录。

SELECT * FROM student WHERE id IN (1,2,3);

3.带 BETWEEN AND 关键字的查询

语法：SELECT * { 字段名1,字段名2,… FROM 表名WHERE 字段名 [ NOT ]BETWEEN 值1 AND 值2；

解释：BETWEEN AND ⽤于判断某个字段的值是否在指定范围之内，若在，则该字段所在的记录会被 查询出来，反之不会。

例题：查询student表中id值在2~5之间的⼈的id和name。

SELECT id,name FROM student WHERE id BETWEEN 2 AND 5;

4.空值查询

语法：SELECT * 字段名1，字段名2，…FROM 表名 WHERE 字段名 IS [ NOT ] NULL

解释：在数据表中有些值可能为空值（NULL），空值不同于0，也不同于空字符串，需要使⽤ IS NULL 来判断字段的值是否为空值。

例题：查询student表中sex值为空值的记录。

SELECT * FROM student WHERE sex IS NULL;

注意：IS NOT NULL 关键字⽤来查询字段不为空值的记录。

5. 带 LIKE 关键字的查询

百分号（%）通配符语法：SELECT * | 字段名1，字段名2，…FROM 表名 WHERE 字段名 [ NOT ]LIKE ‘匹配字符串’;

解释：匹配任意⻓度的字符串，包括空字符串。例如，字符串“c% ”匹配以字符 C开始，任意⻓度的字符串，如“ ct ”，“ cut ”，“ current ”等；字符串“ c%g ”表示以字符 c 开始，以 g 结尾的字符串；字符串“ %y% ”表示包含字符“ y ”的字符串，⽆论“ y”在字符串的什么位置。

例题：查询student表中name字段以字符“ s ”开头的⼈的id,name。

SELECT id,name FROM student WHERE name LIKE "s%";

6.带 AND 关键字的多条件查询

语法：SELECT * | 字段名1，字段名2，…FROM 表名;

解释：在使⽤SELECT语句查询数据时，优势为了使查询结果更加精确，可以使⽤多个查询条件，如使⽤ AND 关键字可以连接两个或多个查询条件。

例题：查询student表中 id 字段⼩于5，并且 sex 字段值为“ woman ”的⼈的id和name

SELECT id,name FROM student WHERE id

7.带 OR 关键字的多条件查询

语法：SELECT * | 字段名1，字段名2，…FROM 表名 WHERE 条件表达式1 OR 条件表达式2 [ … OR 条件表达式 n ];

解释：与 AND 关键字不同，OR 关键字只要满⾜任意⼀个条件就会被查询出来。

例题：查询student表中 id 字段⼩于5，或者 sex 字段值为“ woman ”的⼈的id和name

SELECT id,name FROM student WHERE id

8.OR 和 AND ⼀起使⽤的情况

语法：SELECT * | 字段名1，字段名2，…FROM 表名;

解释：OR 和 AND ⼀起使⽤的时候，AND 的优先级⾼于 OR，因此⼆者⼀起使⽤时，会先运算 AND 两边的表达式，再运算 OR 两边的表达式。

例题：查询student表中sex值为“woman”或者sex值为“man”并且age字段值为16的⼈的记录 SELECT * FROM student WHERE sex='woman' OR sex='man' AND age=16;

5.聚合函数

COUNT()函数

统计记录的条数语法：SELECT COUNT(*) FROM 表名;

例题：查询student表中⼀共有多少条记录。

SELECT COUNT(*) FROM student;

SUM()函数

求出表中某个字段所有值的总和语法：SELECT SUM(字段名) FROM 表名；

例题：查询student表中 age 字段的总和

SELECT SUM(age) FROM student;

AVG()函数

求出表中某个字段所有值的平均值语法：SELECT AVG(字段名) FROM 表名；

例题：查询student表中表中age字段的平均值。

SELECT AVG(age) FROM student;

MAX()函数

求出表中某个字段所有值的最⼤值语法：SELECT MAX(字段名) FROM 表名；

例题：查询student表中 age 字段的最⼤值

SELECT MAX(age) FROM student;

MIN()函数

求出表中某个字段所有值的最⼩值语法：SELECT MIN(字段名) FROM 表名；

例题：查询student表中 grade字段的最⼩值

SELECT MIN(age) FROM student;

6.对查询结果进⾏排序

1.带 ORDER BY 关键字的排序

语法：SELECT 字段名1，字段名2，…FROM 表名 ORDER BY 字段名1 [ ASC | DESC],字段名2 [ ASC | DESC ]…;

解释：在该语法中指定的字段名是对查询结果进⾏排序的依据，ASC表示升序排列，DESC 表示降序排列，默认情况是升序排列。

例题：查询student表中的所有记录，并按照age字段进⾏升序排序

SELECT * FROM student ORDER BY age;

2.带 GROUP BY 关键字的分组查询

语法：SELECT 字段名1，字段名2，…FROM 表名 GROUP BY 字段名1，字段名2，… [ HAVING 条件表达式 ];

解释：在对表中数据进⾏统计的时候，可以使⽤GROUP BY 按某个字段或者多个字段进⾏分组，字段中值相同的为⼀组，如男⽣分为⼀组，⼥⽣分为⼀组。

注意点使⽤*⽆法查询解决⽅法：

1.关闭 ONLY_FULL_GROUP_BY

查询：SELECT @@sql_mode;

关闭：SET SESSION sql_mode=(SELECTREPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''))

2.1单独使⽤ GROUP BY 进⾏分组

解释：单独使⽤GROUP BY 关键字，查询的是每个分组中的⼀条记录。

例题：查询student表中的数据，按照sex字段进⾏分组

SELECT * FROM student GROUP BY sex;

2.2GROUP BY 和聚合函数⼀起使⽤

解释：GROUP BY 和聚合函数⼀起使⽤，可以统计出某个或者某些字段在⼀个分组中的最⼤值、最⼩值、平均值等。

例题：查询student表中按照age字段进⾏分组查询，计算出每组共有多少个⼈。

SELECT COUNT(*) ,age FROM student GROUP BY age;

2.3GROUP BY 和 HAVING 关键字⼀起使⽤

解释：关键HAVING字和WHERE关键字的作⽤相同，区别在于HAVING 关键字可以跟聚合函数，⽽WHERE 关键字不能。通常HAVING 关键字都和GROUP BY⼀起使⽤，⽤于对分组后的结果进⾏过滤。

例题：查询student表中按照sex字段进⾏分组查询，查询出age字段值之和⼩于300的分组

SELECT sum(age),sex FROM student GROUP BY sex HAVING SUM(age)

3.使⽤ LIMIT 限制查询结果的数量

语法：SELECT 字段名2，字段名2，…FROM 表名 LIMIT 记录数 [ OFFSET ] 记录数LIMIT Y OFFSET X 向后偏移 x位。

解释：在此语法中，LIMIT 后⾯可以跟两个参数，第⼀个参数“ OFFSET ”表示偏移量，如果偏移量为0，则从查询结果的第⼀条记录开始，偏移量为1则从查询结果中的第⼆条记录开始，以此类推。OFFSET为可选值，默认值为0，第⼆个参数“记录数”表示指定返回查询记录的条数。

例题：查询tudent表中的前四条记录

SELECT * FROM student LIMIT 4;

例题：查询student表中从0开始查询4条记录

SELECT * FROM student LIMIT 0,4;

7.为表和字段取别名

1.为表取别名

语法：SELECT * FROM 表名 [ AS ] 别名；

解释：在进⾏查询操作时，如果表名很⻓使⽤起来不⽅便，可以为表取⼀个别名来代替表的名称。

例题：为student表起⼀个别名s，并查询student表中sex字段值为“woman”的记录

SELECT * FROM student AS s WHERE s.sex='woman';

2.为字段取别名

语法：SELECT 字段名 [ AS ] 别名 [ ,字段名 [AS] 别名，…] FROM 表名 ；

例题：查询student表中的所有记录的name和sex字段值，并为这两个字段起别名stu_name和stu_sex

SELECT name AS stu_name,sex AS stu_gender FROM student;

8.⼦查询

select name from student where id < (select age from student wherename='sunce');

9.联合查询(union)

select version() union select user();

10.MySQL内置库

在MySQL>5.0的版本中，把information_schema看作是⼀个⾃带的内置数据库，确切说是信息数据库。它保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。

重点学习下schemata、tables、columns以及相关的字段名。

schemata表

这⾥重点关注schema_name这个字段，它存放着所有数据库库名，在SQL注⼊中我们可以使⽤它来进⾏来查询所有数据库，然后进⾏⾼权限的跨库查询。

tables表

tables表⽤来存储所有数据库⾥的表名等信息,其中table_schema列⽤来存放所有数据库的库名,table_name⽤来存放MySQL数据库中的所有表名

columns表

columns表⽤来存放数据库⾥的所有字段信息，其中table_schema列⽤来存放数据库库名，table_name列⽤来存放所有数据库⾥的所有表名，column_name列⽤来存放所有的字段/内容(值)

11.SQL中tips

1.符号运⽤

2.常⽤注释符

在MySQL中，常⻅的注释符有以下⼏种：

#

--空格

/**/

内联注释符及特性：

/*！code */

内联注释符是⼀种基于注块注释衍⽣出来的注释⻛格，它可以⽤于整个SQL语句中，⽤来执⾏SQL语句，内联注释有个特殊点，和MySQL版本息息相关，也就是：当内联注释中出现⼩于或等于当前版本号的时候，内联注释符⾥的⼦句会被执⾏，⼤于时则不会被执⾏，注意版本的写法，例如，5.5.34，在内联注释中需要写为50534，版本号第⼆位加0，然后注意版本号和内联注释中间不能有空格。

3.常⻅的空格符号

两个空格代替⼀个空格，⽤Tab代替空格，%a0=空格

%20(空格) %09(tab) %0a(回⻋符) %0b %0c %0d %a0(空格) %00 /**//*!*/

4.内联注释

select user()/*version()*/

12.注⼊中常⻅的sql函数

1.rand()函数

//⽣成⼀个0~1之间的随机数

语句：select rand();

2.floor()函数

//向下取整

语句：select floor(rand()*2);

3.concat()函数

//将符合条件的同⼀列中的不同⾏数据进⾏拼接，选择好数据库

语句：select concat(0x3a,database(),0x3a)；0x3a为:号

4.concat_ws()函数

//第⼀个参数是其它参数的分隔符。分隔符的位置在连接的两个字符串之间。

语句：select concat_ws('!',database(),'haha');

5.group_concat

语句：select group_concat(user,'~',password) from users;

6.sleep()函数

//对数据库进⾏延时的函数 语法：后⾯直接跟需要延时的时间即可，默认单位为s

语句：select sleep(5);

7.updatexml()

//语法updatexml(⽬标xml⽂档，xml路径，更新的内容

语句：select updatexml('anything',concat('~',(select version())),'xxx');

8.length()函数

//返回参数的⻓度

语句：select length(user());

9.substr()函数

//从字符串 s 的 start 位置截取⻓度为 length 的⼦字符串,selectsubstr(user(),1,2)

语句：select substr(user(),1,2);

10.substring()函数，left() ,mid()函数

//同substr

11.ascii()、ord()函数

//返回字符的ascii码

语句：select ascii(mid(user(),1));

语句：select ord(mid(user(),1));

12.strcmp()函数

//strcmp函数是string compare(字符串⽐较)的缩写，⽤于⽐较两个字符串并根据⽐较结果返回整数。基本形式为strcmp(str1,str2)，若str1=str2，则返回零；若str1str2，则返回正数

语句：select strcmp(version(),user());

13.if(a,b,c)

//如果a为真，则返回b，否则返回c

语句：select if(length(database())=5,1,0);

14.locate()函数

//locate(substr,str)返回substr字符串在str中出现的位置，没有返回0

语句：select locate('o',user());

15.position()函数

//position(substr in str)返回substr字符串在str中出现的位置，没有返回0

语句：select position('@' in user());

16.instr()函数

//instr(str,substr)返回substr字符串在str中出现的位置,与locate相同，只是参数顺序相反

语句：select instr('qweasdzXC','w');

17.hex()&unhex()函数

渗透中有些注⼊点因为数据库中对数据字段的类型定义，可能不⽀持union来显示某些不同类型的内容，所以使⽤hex对数据进⾏⼗六进制编码；

hex参数可⽤于任何参数外⾯

select hex(concat(user(),1));

select hex(user()),hex(database())；

unhex()则是⽤来将⼗六进制的内容转换回⼗进制

select unhex('726F6F74406C6F63616C686F737431');