MySql数据库常用查询语句的练习

1. 在stu数据库下创建表studens及表中的数据

create database stu;

use stu;

create table students;

2.添加age字段（删除字段）及增加修改删除数据

alter table students add age int (约束条件）;

alter table students drop age;

insert into students(class,name,gender,age)/values/(“一班”,”张三”,”男”,”21”);

update students set age = values where id = values;

delete from students where id=values;

3.查询基本语句

select id,name,age from students;

select id,name,age from students where age>18 order by age desc;

4.表单查询

使用“*”查询所有字段 select * from students;

查看数据库中的表结构  desc 表名； describe

查询指定字段:select 字段名1，字段名2 from 表名；

select name,age from students;

去除重复数值：select distinct 字段名 from 表名；#字段名（要去除重复数值的列）

select distinct age from students;

5.where子句

查询指定记录：Where 条件表达式

select id,name,age,sex from students where id=1;

IN关键字查询：[NOT] IN (元素1,元素2....元素n)

select id,name,age from students where age in (17,18);

Between查询范围：判读字段的值是否在指定范围

语法规则如下：[NOT] BETWEEN 取值1 AND 取值2 

取值1：大于等于这个值   取值2：小于等于这个值

select id,name,age from students where age between 18 and 20;

Like匹配查询：匹配字符串是否相等，语法规则如下：

[NOT] like ‘字符串’   

此处LIKE与等于号（=）是等价的

select id,name,age from students where name like '王五';

select id,name,age from students where name = '王五';

Like匹配查询：使用like匹配带有通配符‘%’的字符串‘王%’

字符串中包含通配符时，‘=’不能代替like

select id,name,age from students where name like '李%';

select id,name,age from students where name = '李%';

Like匹配查询 ：使用like匹配带有通配符‘_’的字符串‘王_’ 匹配单个字符

下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符

select id,name,age from students where name like '李_';

AND多条件查询 等同于&&

联合多个条件进行查询，语法规则如下：

条件表达式1 AND 条件表达式2

查询表中age为20且name为李开头的记录

select id,name,age from students where age = 20 and name like '李%';

OR多条件查询 等同于||

只要满足几个查询条件中的其中一个，记录就会被查询出来，语法规则如下：

条件表达式1 OR 条件表达式2

select id,name,age from students where age > 20 or name like '李四';

AND与OR

当AND与OR一起使用时，AND要比OR先运算

select id,name,age from students where id > 5 and id in(1,2,3) or age = 18;

select id,name,age from students where age > 18 and id in(1,2,3) or id > 5;

LIMIT限制查询结果的数量

LIMIT不指定初始位置，从第一条记录开始

显示。语法规则如下：

LIMIT 记录数 

limit n,m

n：开始位置

m：要检索的行数

行0 ：检索出来的第一行为行0，而不是行1， 因此，limit 1,1将检索出第二行而不是第一行

select * from students limit 3;

select * from students limit 5;

select * from students limit 0,2;

select * from students limit 1,2;

对查询结果排序

ORDER BY 对记录进行排序。语法规则如下：

ORDER BY 属性名 [ASC | DESC]

ASC：升序（默认升序）

DESC：降序

可以给多个列排序，只需要指定列名，列名之 间用逗号分开即可

select * from students order by age;

select * from students order by age asc;

select * from students order by age desc;

ORDER BY 和 LIMIT组合

能够找出一个列中最高或最低的值

select 字段名 from 表名

order by 字段名 DESC|ASC

LIMIT 1;

select id,name,age from students order by age desc limit 1;

select id,name,age from students order by age asc limit 1;

分组查询

GROUP BY 将查询结果按某个字段或多个字段进行分组。字段中的值相等的为一组。

语法规则如下：

GROUP BY 属性名 [having 条件表达式]

select gender from students group by gender;

分组查询

使查询结果按照某一列或多列值进行分组,值相等的为一组。

GROUP BY [ALL] GROUP_BY_EXPRESSION HAVING

对分组后的结果按条件进行筛选，只能在Group by子句后使用，不能单独使用，只能对分组计算的结果进行筛选，不能使用别名

select 字段名 from 表名 group by 字段名 having 等式；

1. 集合函数

COUNT() ：用来统计记录的条数

select count(*) from students;

select age,count(*) from students group by age;

SUM() ：用来计算字段的值的总和

select sum(age) from students;

select age,sum(id) from students group by age;

AVG() ：用来计算字段的值的平均值

select avg(age) from students;

MAX() ：用来查询字段的最大值

select max(age) from students;

select gender,max(age) from students group by gender;

MIN() ：用来查询字段的最小值

select min(age) from students;

select gender,min(age) from students group by gender;

1. 正则表达式查询

  正则表达式：属性名 REGEXP '匹配方式’

“^”查询以特定字符或者字符串开头的记录

select * from students where name regexp '^刘';

“$”查询以特定字符或者字符串结尾的记录

select * from students where name regexp '五$';

“.”匹配字符串中的任意一个字符

select * from students where name regexp '刘.';

注：“.”和“_”的区别

两个都可以代替任意一个字符进行查询

“.”只能用于regexp关键字进行匹配

select * from students where name regexp '李_天';

select * from students where name like '李_天';

select * from students where name like '李..';

[..]字符集合。匹配所包含的任意一个字符

[]可以指定集合的区间。

如:[a-z],[1-9]

select * from students where phone regexp '[6-8]';

“[^字符集合]” ：匹配指定字符以外的字符

select * from students where phone regexp '[^0-9]';

select * from students where phone regexp '[^0-9]';

匹配指定字符串

正则表达式可以匹配字符串。当表中的记录包含这个字符串时， 就可以将该记录查询出来。如果指定多个字符串时，需要用符号“|”隔开。只要匹配这些字符串中的任意一个即可

select * from students where name regexp '王|李';

“*”和“+”

匹配多个字符，*和+都可以匹配多个该符号之前的字符。

“+”：至少表示一个字符

“*”：可以表示0个字符

select * from students where phone regexp '0*9';

‘{m}’或者’{m,n}’来指定字符串连续出现的次数 

{m}：m是一个非负整数。匹配确定的 m 次

{m,n}：m 和 n 均为非负整数，其中m <= n

select * from students where phone regexp '5{1}';

select * from students where phone regexp '5{2,5}';

1. 运算符

+ 加法

- 减法 mod(m,n)

* 乘法 n%m  n除m取余数

/ 或 DIV 除法 n div m  n除m取结果的整数

%或MOD 取余

算术运算符：加减乘除求余，主要是用在数值计算上

select 5+5,10-5,10/2,10 div 2,mod(10,3);

MySQL 注释符号有三种，分别如下所示。

1. #...

2. "-- "（注意："--" 后面有一个空格)

3. /*...*/

1、“#”，表示单行注释，语法“#注释内容”

2、“--”，表示单行注释，语法“-- 注释内容”

3、“/**/”，表示多行注释，语法“/*注释内容*/”

select * from students;#12313242

select * from students where age = 17 -- limit 1;

1. 查询函数

联合查询 : UNION 

并操作 union、交操作 intersect、差操作 except

UNION用于合并具有相同字段结构的两个表的内容，主要用在一个结果中集中显示不同表的内容

select * from venus1

union

select * from venus2

union查询默认不返回重复记录

union查询的表的字段必须一样

union查询的数据类型必须能兼容

#union查询结果只增加了行数而列数不变

union操作符用于合并两个或多个select语句的结果集

union所查询的列数、列的顺序必须相同，数据类型必须兼容

select 1 union select 2 union select 3;

id = 1 order by 4 -- + 正常

id = 1 order by 5 -- + 异常

结论：当前语句查询了四列

当前语句查询了7列

嵌套查询 : [NOT]IN、ANY、ALL、[NOT]EXISTS

1.子查询

2.子查询可以继续嵌套

3.子查询中不可以使用order by子句，只对最后结果排序

4.子查询要用括号括起来

子查询结果为集合时可用如下关键字判断

[NOT]IN [不]包含其中

ANY//ALL 任何一个//所有的

[NOT]EXISTS [不]存在

系统用户名：system_user()

select system_user();

select user from mysql.user

用户名：user()  

select user();

当前用户名：current_user()

select current_user();

当前日期：current_date()

select current_date()；

连接数据库用户名：session_user()

select session_user();

数据库名：database()  

select database();

数据库版本：version()

select version();

数据库读取路径：@@basedir

show variables like '%basedir%';

MYSQL安装路径：@@char

show variables like"%char%";

查看当前系统版本：@@version_compile_os

select @@version_compile_os;

判断当前用户的第一字符：

select left(user(),1)=’r’;

判断当前用户的最后一个字符：

select right(user(),1)=’r’;

截取函数：

截取当前数据第二个字符是否为r

select substr(database(),2,1)='r';

select mid(database(),2,1)=’r’;

#正确返回1，错误返回0