mysql数据库命令操作详解及sql语句操作

mysql数据库基本操作

注：所有sql语句后面都要跟英文分号，在一个表中行又叫记录，列又叫字段。如果在创建表中不小心按下了回车，也不要紧，继续写，mysql中遇见英文分号才结束，如下图：

1.登录mysql数据库(三种方式)：
(1)mysql -u root -p
  输入密码：xxx
(2)mysql -u root -pmysql(mysql为数据库密码);
(3)mysql -u root -pmysql -h 127.0.0.1;
2.退出mysql(数据库三种方法)：
(1)quit 
(2)exit
(3)ctrl+D
2.查看时间:
select now();
3.查看数据库版本信息：
select version();
4.查看所有数据库:
show databases;
5.使用test数据库：
use test;
6.查看当前使用的数据库：
select database();
7.查看当前数据库中所有表：
show tables;
8.创建python_test数据库:
create database python_test;(如果有特殊的符号使用反单引号将数据库名括起来,tab键上面)
9.删除python数据库：(了解即可，千万别删，否则跑路)
drop database python;
10.查看创建数据库的信息：
show create database python_test;
11.创建数据库python_test,并指定编码为utf-8:
create database python_test charset=utf8;(注意是utf8，不带杠)
12.创建表student(auto_increment 表示自动增长，enum表示枚举,decimal(5,2)代表180.56小数点后两位数字):
create table python_test.students(
id int unsigned primary key auto_increment not null,
name varchar(20) default "",
age tinyint unsigned default 0,
height decimal(5,2),
gender enum("男","女","中性","保密"), 
cls_id int unsigned default 0
);
13.查看students表结构：
desc students;

**增加**
11.向表中插入数据:
**全列插入**：insert into students values("null","Jack",18,178.45,12);
**全列多行插入**：insert into students values("null","Jack",18,178.45,12),("default","tony",19,180.76,13);
**部分列插入**：insert into students(name,age) values("tom",21);

**修改**
update students set gender=0,age=23 where id=2;
**若不指定，则会修改全表中的数据，会造成数据的污染。**

**删除**
delete from students where id=1;

**逻辑删除**(**向表中添加一个字段用来标记该条信息不能再使用了，避免真对表进行物理上的删除)：**
向表中添加一个字段进行标记：
**alter table students add status bit default 0;(bit代表两个数0和1,0默认不删除)**

update students set status=1 where id=2;(逻辑删除id为2的学生的信息)

查询正常的数据：
select * from students  where status=0;

查询逻辑上已删除的数据;
select * from students where status=1;

**向表中添加字段**
15.为studnet表添加字段/列：
alter table students add birthday datetimes;

16.修改student表中字段：
重命名版：alter table students change birthday birth datatime not null;
不重命名版：alter table students modify birth date not null;

17.删除表中字段birthday：
alter table students drop birthday;

18.删除student表：
drop table students；

**查询**
1.查询student表中所有信息：
select * from students;

2.查询指定字段：
select id,name,gender from students;

3.创建表(数据库名.表名)
create table database.class (
id int unsigned primary key auto_increment,
name varvhar(2) not null
);

查询所有学生信息：
select * from students;

消除重复行：
select distinct gender from students;

表名.字段名:
select students.id,students.name,student.gender from students;

给表起别名：
select s.id,s.name,s.gender from students as s;

给字段或列起别名:(as也可不加):
select id as 序号,name as 名字,gender as 性别 from students; 

**条件查询**
**---比较运算符**
查询大于18岁的学生信息：
select * from students where age>18;

查询小于或者等于18岁的学生信息：
select * from students where age<=18;

**---逻辑运算符**
查询18到28之间的所有学生信息：
select * from students where age>18 and age<28;

查询18岁以上的女性：
select * from students where age>18 and gender="女";
select * from students where age>18 and gender=2;

查询18岁以上或者身高高于180(包含180)以上：
select * fronm students where age>18 or height>=180;

查询年龄不在(18岁以上的女性)：
select * from students where not (age>18 and gender=2); 

年龄不在18岁并且是女性：
select * from students where (not age<=18) and gender=2; 

**---模糊查询**
查询名字中以小开头的名字：
select name from students where name like "小%";

查询姓名当中有"小"的所有名字：
select name from students where like "%小%";

查询有2个字的名字：
select name from students where name like "__";

查询有三个字的名字：
select name from students where name like "___";

查询至少两个字的名字：
select name from students where name like "__%";

---rlike 正则表达式
查询以周开头的姓名：
select name from students where name rlike "^周.*";

查询以周开头，以伦结尾的姓名：
select name from students where name rlike "^周.*伦$";

**----范围查询**
查询年龄为18,34岁的名字：
select name,age from students where age=18 or age=34;
select name,age from students where age in(18,34);

查询年龄不是18,34岁的名字：
select name,age from students where age not in(18,34);

查询年龄在18到34之间的名字：
select name,age from students where age between 18 and 34;

查询年龄不在18到34之间的名字(注：not后面不要加括号)：
select name,age from students where age not between 18 and 34;

**----判断空**
查询身高为空的信息：
select * from students where height is null;

查询身高信息非空:
select * from students where height is not null;

**排序**
**---order by asc(升序)**
查询年龄在18到34之间的男性信息年龄按照按照从大到小排序：
select * from students where (age between 18 and 34) and gender=1 order by age desc;

查询年龄在18到34之间的女性信息按照身高从低到高排序：
select * from students where (age between 18 and 34) and gender=2 order by height asc;

**---order by 多个字段 desc(降序)**
如果身高相同，按id从小到大排：
select * from students where (age between 18 and 34) and gender=2 order by height asc,id asc;

按照年龄从小到大，身高从高到低排：
select * from students order by age asc,height desc;

**聚合函数**
查询男性人数：
select count(*) as "男性人数" from students where gender=1;

查询最大的年龄：
select max(age) from students ;

查询女性的最低身高：
select min(height) from students where gender=2;

查询所有人年龄总和：
select sum(age) from students;

计算平均年龄：
select avg(age) from students;
select sum(age)/count(*) from students;

**四舍五入round(x,2) x保留两位小数：**
计算所有人平均年龄，保留两位小数:
select round(sum(age)/count(*),2) from students;

计算男性的平均身高，保留两位小数：
select round(avg(height),2) from students where gender=1;
select name,round(avg(height),2) from students where gender=1;**是错误的**

**分组group by(一般和聚合函数一起用)**
按照性别分组，查询所有的性别：
select gender from students group by gender;(先分组，再从组里取数据,若使用*是错误的)

计算每种性别中的人数：
select gender,count(*) from students group by gender;(count(*)是对分组的结果进行计数)

查看分组后每组中的名字,age：
select gender,group_concat(name,age) from students where gender=1 group by gender;
select gender,group_concat(name," ",age," ") from students where gender=1 group by gender;

计算男性的人数：
select gender,count(*) from students where gender=1 group by gender;

**having 是对分组后得到结果进行筛选**
查询平均年龄超过30岁的性别，以及姓名
select gender,group_concat(name),avg(age) from students group by gender having avg(age)>30;

---where是对原表结果进行判断，having是对分组的结果进行判断
查询每种性别中人数多于2的学生信息：
select gender,group_concat(name),count(*) from students group by gender having count(*)>2;

**分页**
限制查询出来的数据个数：
select * from students  limit 5;

从第一条数据开始，查询5条数据：
select * from studentslimit 0,5;

每页显示2个，第一个页面：
select * from students limit 0,2;

每页显示2个，第二个页面：
select * from students limit 2,2;

每页显示2个，第N个页面：
select * from students limit (N-1)*2,2;

**连接查询**
**---内连接：查询的结果为两个表中匹配的数据**
select * from students inner join classes;(相当于两个表相乘，又叫笛卡尔积,数据毫无意义)

查询student表中学生信息以及对应所属班级：
select * fron 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 order by c.name,s.id;
**----外连接**
**左连接(以左边的表为基准，找到就显示，找不到默认显示null)**
查询每位学生对应的班级信息：
select * from students as s left join classes as c on s.cls_id=c.id ；

**右连接：一般不用(因为只需将表位置交换即为左连接)**
查询没有对应班级信息的学生：
select s.*,c.name from students as s left join classes as c on s.cls_id=c.id where c.id is null;
select s.*,c.name from students as s left join classes as c on s.cls_id=c.id having c.id is null;

**自关联**
**windows下mysql数据库支持utf8编码命令；
chcp 65001**
**将含有大量insert语句的areas.sql脚本插入areas表中:
source areas.sql**

查询出山东省的所有市
关联查询：select * from areas as province inner join areas as city on city.pid=province.aid having province.atitle="山东省";

子查询：select * from areas where pid=(select aid from areas where atitle="河南省");

**子查询(查询中嵌套着查询)
标量子查询：子查询返回的结果是一个数据(一行一列)**
查询班级学生的平均身高：
如：select * from students where age>(select avg(age) from students);

**列级子查询：返回的的结果是一列(一列多行**）
查询还有学生在班的所有班级名字：
select name from classes where id  in (select cls_id from students);

**行级子查询：将多个字段合成一个行元素,返回的结果是一行(一行多列)**
如：查询班级中年龄最大、身高最高的人的信息
select * from students where (height,age)=(select max(height),max(age) from students);