mysql 操作语句

00_kawayi
91 阅读5分钟

Mysql

持续更新……

1.  基本操作

启动

mysql -u root -p

1.  数据库

1）创建数据库：create database <数据库名>;

2）查询所有数据库：show databases;

3）删除数据库：drop database <数据库名>;

4）进入数据库：use <数据库名>;

2.  数据表

1）创建表：

CREATE TABLE 表名 (id INT(50) NOT NULL, name VARCHAR(50), PRIMARY KEY (id));

not null字段不能为空；

2）查询数据库下表：show tables;

3）删除表：drop table 表名;

4）清空表中记录：delete from 表名;

5）查询表：select * from 表名;

6）查询表是否存在：SHOW TABLES LIKE 表名;

3.数据操作

1）插入数据

INSERT INTO 表名 (id, name, age) VALUES (1, '小二', 14);

2）修改数据

UPDATE 表名 SET name='小二', age=12 WHERE id=1;

3）删除数据

delete from 表名 where id=1;

4）数据排序

ASC升序 、DESC降序

select * from 表名 ORDER BY id ASC;

1.查询年龄在18到34岁之间的男性，按照年龄从小到大排序
select * from students where (age between 18 and 34) and gender=1;
select * from students where (age between 18 and 34) and gender=1 order by age;
select * from students where (age between 18 and 34) and gender=1 order by age asc;
2.查询年龄在18到34岁之间的女性，身高从高到矮排序
select * from students where (age between18 and 34) and gender=2 order by height desc;

order by多个字段
3.查询年龄在18到34岁之间的女性，身高从高到矮排序，如果身高相同的情况下按照年龄从小到大排序
select * from students where (age between 18 and 34) and gender=2 order by height desc,age asc;
4.查询年龄在18到34岁之间的女性，身高从高到矮排序，如果身高相同的情况下按照年龄从小到大排序，如果年龄也相同那么按照id从大到小排序
select * from students where (age between 18 and 34) and gender=2 order by height desc,age asc, id desc;
5.按照年龄从小到大、身高从高到矮的排序
select * from students order by age asc,height desc;

5）连接查询

外连接

select f1,f2,f3,....
from table1 left/right outer join table2
on 条件;
outer可不写


左连接：左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

select a.id,score
from
  (select id,age from stu where age < 20) a (过滤左表信息)
left join
  (select id, score from result where score < 60) b (过滤右表信息)
on a.id = b.id;


右连接：右外连接就是左表过滤的结果必须全部存在

select a.id,score
from
  (select id,age from stu where age < 20) a (过滤左表信息)
left join
  (select id, score from result where score < 60) b (过滤右表信息)
on a.id = b.id;

内连接

select f1,f2,f3,....
from table1 inter join table2
on 条件;

select a.id,score
from
  (select id,age from stu where age < 20) a (过滤左表信息)
inner join
  (select id, score from result where score < 60) b (过滤右表信息)
on a.id = b.id;
inner join
select … from 表 A inner join表B;
select * from students inner join classes;
查询有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=classes.id;
按照要求显示姓名、班级
select students.*, classes.name from students inner join classes on students.cls_id=classes.id;
select students.name，classes.name from students inner join classes on students.cls_id=classes.id;
给数据表起名字
select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;
查询有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称
select s.*,c.name from students as s inner join classes as c on s.cls_id=c.id;
在以上的查询中,将班级姓名显示在第1列
select c.name，s.* from students as s inner join classes as c on s.cls_id=c.id;
查询有能够对应班级的学生以及班级信息，按照班级进行排序
select c.xxx s.xxx from student as s inner join clssses as c on … order by …;
select c.name，s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;
当时同一个班级的时候，按照学生的id进行从小到大排序
select c.name，s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name,s.id;
left join
查询每位学生对应的班级信息
select * from students as s left join classes as c on s.cls_id=c.id;
查询没有对应班级信息的学生
– select … from xxx as s left join xxx as c on… where …
– select … from xxx as s left join xxx as c on… . … having …
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;
select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;

6）分组查询

group_by, group_concat()

where :是对整个数据表信息的判断;
having:是对于分组后的数据进行判断

group by
1.按照性别分组,查询所有的性别
select gender from students group by gender;
2.计算每种性别中的人数
select gender, count(*) from students group by gender;
where是在group by 前面
3.计算男性的人数
select count(*) from students where gender='男';

group concat(…)
1.查询同种性别中的姓名
select gender,group_concat(name) from students group by gender;

having :having是在group by后面
2.查询平均年龄超过30岁的性别，以及姓名
select gender ,avg(age) from students group by gender having avg(age) > 30;
3.查询每种性别中的人数多于2个的信息
select gender,count(*) from students group by gender having count(*) > 2;
4.查询每组性别的平均年龄
select gender,avg(age) from students group by gender;

mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | signin |
+----+--------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

GROUP BY
mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+
3 rows in set (0.01 sec)

7）分页查询

limit在使用的时候，要放在最后面.

limit start,count   
(start:初始值0，表示从哪─个开始;count:表示数量)
即limit（第N页-1)*每个的个数，每页的个数;

限制查询出来的数据个数
select *from students where age=13 limit 2;
查询前5个数据
select* from students limit 0,5;
查询id6-10(包含)的书序
select * from students limit 5,5;
每页显示2个，第1个页面
select * from students limit 0,2;
每页显示2个，第2个页面
select * from students limit 2,2;
每页显示2个，第3个页面
select * from students limit 4,2;
每页显示2个，第4个页面
select * from students limit 6,2;
每页显示2个，显示第6页的信息,按照年龄从小到大排序
select * from students order by age asc limit 10,2;
– 如果重新排序了,那么会显示第一页
select * from students where gender=2 order by height des limit 0,2;

8）合并查询

  • union : 数据库系统会将所有的查询结果合并到一起，然后去掉相同的记录；
select id from a union select id from b;
  • union all: 使用union all，不会去除掉重复的记录
select id  from a union select id from b;

9）其他查询

标题语法例子
逻辑运算符and, or, not不在18岁以上的女性这个范围内的信息select * from students where not (age>18 and gender=2);
模糊查询like， rlike% 替换1个或者多个 _ 替换1个 查询姓名中 以“小”开始的名字 select name from students where name like"小%";\ 查询姓名中有“小”所有的名字select name from students whece name like "%小%"; \ 查询有2个字的名字select name from students where name like "__"; \ 查询有3个字的名字select name from students where name like "__"; \ 查询至少有2个字的名字 select name fromstudents where name like "__%"; \ rlike正则 查询以周开始的姓名select name from students where name rlike "^周.*"; \ 查询以周开始、伦结尾的姓名select name from students where name rlike "^周.*伦$";
范围查询in，not in，between…and，not between…andin，not in：集合中(不连续)查询；between…and，not between…and：连续范围中查询;年龄不是 18、34岁之间的信息 select name,age from students where age not in (18，34);\ 查询年龄不在在18到34之间的的信息select * from students where age not between 18 and 34;
空判断is null查询身高为空的信息 select *from students where height is null/NULL/Null;\ 判非空is not null select * from students where height is not null;
聚合函数count(), max(), min(), sum(), avg(), round()-count 查询男性有多少人，女性有多少人 select count(*) from students where gender=1;\ -max 查询最大的年龄select max (age) from students;\ -sum 计算所有人的年龄总和 select sum ( age) from students;\ – avg 计算平均年龄 select avg(age) from students\ -round 计算男性的平均身高保留2位小数 select round(avg (height),2) from students where gender=1; select name,round(avg(height)，2) from students where gender=1;
avatar
文章
阅读
粉丝