mysql数据库基操，都坐下！

数据库的操作

链接数据库

 mysql -uroot -p
 mysql -uroot -p[password]

退出数据库

exit/quit/ctrl+d

sql语句最后需要有分号;结尾

显示数据库版本

 select version();

显示时间

 select now();

查看所有数据库

 show databases;

创建数据库

 create database 数据库名 [charset = utf8];
 create database python04;
 create database python04new charset=utf8;

查看创建数据库的语句

 show create database ....
 show create database python04;

查看当前使用的数据库

 select database();

使用数据库

 use 数据库的名字
 use python04new;

删除数据库

 drop database 数据库名;
 drop database python04;

数据表的操作

查看当前数据库当中所有的表

 show tables;

创建表

 auto_increment表示自动增长
 not null 表示不能为空
 primary key 表示主键
 default 默认值
 create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);
 创建classes表(id, name)

 create table xxxx(id int, name varchar(30));
 create table yyyy(id int primary key not null auto_increment, name varchar(30));
 create table zzzz(
     id int primary key not null auto_increment, 
     name varchar(30)
 );

查看表的信息

 desc 表的名字;
 desc xxxx;

创建student表(id, name, age, high, gender, cls_id)

 create table students(
     id int unsigned auto_increment primary key not null,
     name varchar(30),
     age tinyint unsigned default 0,
     high decimal(5,2), 
     gender enum("男", "女", "中性", "保密") default "保密",
     cls_id int unsigned
 );

 insert into students values(0, "laowang", 18, 188.50, "男", 0);
 select * from students;

创建classes表(id, name)

 create table classes(
     id int unsigned not null primary key auto_increment, 
     name varchar(30)
 );

查看表的创建语句

 show create table 表名字;
 show create table students;

修改表-添加字段

 alter table 表名 add 列名 类型及约束;
 alter table students add birthday datetime;

修改表-修改字段:不重命名版

 alter table 表名 modify 列名 类型及约束
 alter table students modify birthday date;

修改表-修改字段:重命名版

 alter table 表名 change 原列名 新列名 类型及约束;
 alter table students change birthday birth date default "2000-01-01";

修改表-删除字段

 alter table 表名 drop 列名;
 alter table students drop high;

删除表

 drop database 数据库;
 drop table 数据表;
 drop table xxxx;

增删改查(curd)

增加

 全列插入
 insert [into] 表名 values(...)
 主键字段 可以用 0 null default 来占位

 向classes表中插入一个班级
 insert into classes values(0, "菜鸟班");


 +--------+-------------------------------------+------+-----+------------+----------------+
 | Field  | Type                                | Null | Key | Default    | Extra          |
 +--------+-------------------------------------+------+-----+------------+----------------+
 | id     | int unsigned                        | NO   | PRI | NULL       | auto_increment |
 | name   | varchar(30)                         | YES  |     | NULL       |                |
 | age    | tinyint unsigned                    | YES  |     | 0          |                |
 | gender | enum('男','女','中性','保密')        | YES  |     | 保密       |                |
 | cls_id | int unsigned                        | YES  |     | NULL       |                |
 | birth  | date                                | YES  |     | 2000-01-01 |                |
 +--------+-------------------------------------+------+-----+------------+--------------
 
 向students插入一个学生信息
 insert into students values(0, "小李飞刀", 20, "女", 1, "1990-01-01");
 insert into students values(null, "小李飞刀", 20, "女", 1, "1990-01-01");
 insert into students values(default, "小李飞刀", 20, "女", 1, "1990-01-01");

 失败: insert into students values(default, "小李飞刀", 20, "第4性别", 1, "1990-01-01");

 枚举中的下标从1 开始 1---"男" 2---"女" 3---"保密"
 insert into students values(default, "小李飞刀", 20, 1, 1, "1990-01-01");

 部分插入
 insert into 表名(列1,...) values(值1,...);
 insert into students(name, gender) values("小乔", "女");

 多行插入
 insert into students(name, gender) values("大乔", "女"), ("貂蝉", "女");
 insert into students values(default, "西施", 20, "女", 1, "1990-01-01"), (default, "王昭君", 20, "女", 1, "1990-01-01");

修改

 update 表名 set 列1=值1, 列2=值2... where 条件;
 update students set gender=1; 全部都改
 update students set gender=1 where name="小李飞刀"; 只要name是小李飞刀的全部修改
 update students set gender=1 where id=3; 只要id为3的就修改
 update students set age=22, gender=1 where id=4; 只要id为4的，同时修改两处

删除

 物理删除
 delete from 表名 where 条件;
 delete from students; 整个数据表中所有数据全部删除
 delete from students where name="小李飞刀";

 逻辑删除
 用一个字段表示，这条信息已经不能再使用了
 给students表添加一个is_delete字段 bit类型
 alter table students add is_delete bit default 0;

查询基本使用

 查询所有列
 select * from 表名;
 select * from students;

 定条件查询
 select * from students where name="小李飞刀"; 查询name为小李飞刀的所有信息
 select * from students where id>3; 查询id>3的信息

 查询指定列
 select name, gender from students;


 可以用as为列或表指定别名
 select 字段(as 别名) , 字段(as 别名) from 数据表 where...;
 select name as 姓名, gender as 性别 from students;

 字段的顺序
 select id as 序号, gender as 性别, name as 姓名 from students;

数据的准备

创建一个数据库

 create database python_test charset=utf8;

使用一个数据库

 use python_test;

显示当前使用的数据库

 select database();

创建数据表

 students表
 create table students(
     id int unsigned primary key not null auto_increment,
     name varchar(20) default "",
     age tinyint unsigned default 0,
     height decimal(5,2),
     gender enum("男", "女", "保密") default "保密",
     cls_id int unsigned default 0,
     is_delete bit default 0 
 );

 classes表
 create table classes(
     id int unsigned primary key not null auto_increment,
     name varchar(20) not null
 );

数据准备

 insert into students values
 (0, '小明',   18,180.00,2,1,0),
 (0, '小月月', 18,180.00,2,2,1),
 (0, '彭于晏', 29,185.00,1,1,0),
 (0, '刘德华', 59,175.00,1,2,1),
 (0, '黄蓉',   38,160.00,2,1,0),
 (0, '凤姐' ,  28,150.00,4,2,1),
 (0, '王祖贤', 18,172.00,2,1,1),
 (0, '周杰伦', 36,  NULL,1,1,0),
 (0, '程坤',   27,181.00,1,2,0),
 (0, '刘亦菲', 25,166.00,2,2,0),
 (0, '金星',   33,162.00,3,3,1),
 (0, '静香',   12,180.00,2,4,0),
 (0, '郭靖',   12,170.00,1,4,0),
 (0, '周杰',   34,176.00,2,5,0);

 insert into classes values
 (0, "python_01期"),
 (0, "python_02期"),
 (0, "python_04期");

查询

查询所有字段

 select * from students;
 select * from classes;

查询指定字段

 select 列1, 列2 from 表名;
 select name, age from students;

使用as给字段起别名

 select 字段 as 名字... from 表名;
 select name as 姓名, age as 年龄 from students;

 select 表名.字段 .... from 表名;
 select students.name , students.age from students;

可以通过as给表起别名

 select 别名.字段 .... from 表名 as 别名;
 select s.name , s.age from students as s;

消除重复行

 distinct 字段
 select distinct gender from students;

条件查询

比较运算符

 select ... from 表名 where ....;

 >
 查询大于18岁的信息
 select * from students where age>18;
 select id, name, gender from students where age>18;

 <
 查询小于18岁的信息
 select * from students where age<18;

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

 =
 查询年龄为18岁的所有学生的名字
 select * from students where age=18;

逻辑运算符

 and
 18 到 28之间的所有学生信息
 select * from students where age>18 and age<28;

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

 or
 18岁以上或身高超过180以上(包含)
 select * from students where age>18 or height >= 180;

 not 
 不在18以上的女性 这个范围内的信息
 select * from students where gender=2 and not age > 18;
 加括号可以改变优先级
 select * from students where not (age > 18 and gender=2);

 年龄不是小于或者等于18 并且是女性
 select * from students where (not age<=18) and gender=2;

模糊查询

 like
 % 替换零个，一个或多个
 _ 替换一个
 查询姓名中 以"小" 开始的名字
 select name from students where name like "小%";

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

 查询有2个字的名字
 select name from students where name like "__";
 
 查询有3个字的名字
 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 "^周.*伦$";

范围查询

 in (1, 3, 8) 表示在一个非连续的范围内
 查询年龄为 18， 34 的姓名
 select name, age from students where age = 18 or age = 34;
 select name, age from students where age = 18 or age = 34 or age=12;
 select name, age from students where age in (12, 18, 34);

 not in 不在非连续的范围之内
 年龄不是 18， 34岁之间的信息
 select name, age from students where age not in (18, 34);

 bteween ... and ... 表示在一个连续的范围之内 (左闭右闭)
 查询 年龄在18到34之间的信息
 select name, age from students where age between 18 and 34;

 not between ... and ... 表示不在一个连续的范围之内
 查询 年龄不在18到34岁之间的信息
 select * from students where age not between 18 and 34; (记这种用法)

 错误！！---> **select * from students where age not (between 18 and 34); 因为not between是一个整体**

 正确的用法是---> **select * from students where not age between 18 and 34;**

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

 判非空
 is not null;
 select * from students where height is not null;

排序

order by 字段

 asc从小到大排列，即升序(默认)
 desc 从大到小排列，即降序

 查询年龄在18 到 34岁之间的男性，按照年龄从小到大排序
 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;

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

order by 多个字段

 若第一个字段相同，按第二个字段，若第二个也相同，按第三个，以此类推
 查询年龄在18到34岁之间的女性，身高从高到低排序，若身高相同，年龄从小到大
 select * from students where (age between 18 and 34) and gender=2 order by height desc, age asc;

 查询年龄在18到34岁之间的女性，身高从高到低排序，若身高相同，年龄从小到大，年龄相同id从大到小排
 select * from students where (age between 18 and 34) and gender=2 order by height desc, age asc, id desc;

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

聚合函数

总数

 count
 查询男性有多少人，女性有多少人
 select * from students where gender=1;
 select count(*) from students where gender=1;
 select count(*) as 男性人数 from students where gender=1;

 select count(*) as 女性人数 from students where gender=2;

最大值

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

 查询女性的最高身高
 select max(height) as 女性最高身高 from students where gender=2;

最小值

 min

求和

 sum
 计算所有人的年龄总和
 select sum(age) as 年龄总和 from students;

平均值

 avg
 计算平均年龄
 select avg(age) as 年龄平均 from students;

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

四舍五入 round(123.1, 1)保留一位小数

 计算所有人的平均年龄，保留2位小数
 select round(sum(age)/sum(*), 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 name from students group by gender;
 失败--->select * from students group by gender;
 select gender from students group by gender;

 计算每种性别当中的人数
 select gender, count(*) from students group by gender;

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

group_concat(…)

 查询同种性别中的姓名
 select gender, group_concat(name) from students where gender=1 group by gender;
 select gender, group_concat(name,age,id) from students where gender=1 group by gender;
 select gender, group_concat(name," ",age," ",id) from students where gender=1 group by gender;

having (对分组进行条件判断)

 查询平均年龄超过30岁的性别，以及姓名 having avg(age) > 30
 select gender, group_concat(name), avg(age) from students group by gender having avg(age) > 30;

 查询所有性别中的人数多于2个的信息
 select gender, group_concat(name) from students group by gender having count(*)>2;

分页

limit start, count; (limit放在最后)

 限制查询出来的数据个数
 select * from students limit 2;

 查询5个数据
 select * from students limit 0, 5;

 查询id 6-10(包含) 的分页
 select * from students limit 5, 5;

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

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

 每页显示两个，第3个页面
 select * from students limit 4, 2;

 每页显示两个，第4个页面
 select * from students limit 6, 2; --->limit (第N页-1)*每页的个数，每页的个数

 每页显示两个，显示第6页的信息， 按照年龄从小到大排序
 失败--->select * from students limit 2*(6-1), 2;
 失败--->select * from students limit 10, 2 order by age;
 select * from students order by age limit 10, 2;

连接查询(mysql支持内连接和外连接，其中外连接分为左连接和右连接)

inner join … on

 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;

 在以上查询中，将班级名字放在第一列
 select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id;

 查询 有能够对应班级的学生以及班级信息,按照班级进行排序
 select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by s.cls_id;

 当同一个班级的时候，按照学生的id进行从小到大排序
 select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by s.cls_id, 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;(多个表的集使用having)
 select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;(一般原表使用where)

right join

 将数据表名字互换位置，用left join完成

自关联

 通俗理解：一张表里面的某两个或者几个字段之间有关系，简称自关联(我关联我自己)

 创建表
 create table areas(
     aid int primary key not null,
     atitle varchar(20) default null,
     pid int default null
 );

 所需数据下载
 https://pan.baidu.com/s/1Rk5-Xm5a4Xtckc3RmotYnA 提取码: yn78

 在mysql内使用命令 source xxx.sql(需要在文件当前路径)即可执行文件内所有命令添加数据

 查询出山东省有哪些市
 select * from areas as province inner join areas as city on province.aid=city.pid having province.atitle="山东省";
 select province.atitle, city.atitle from areas as province inner join areas as city on province.aid=city.pid having province.atitle="山东省";

子查询

 子查询通俗来讲，就是一个select里面还包含有另一个子查询的信息，相当于函数的结果作为另一个函数的参数一样
 标量子查询
 查询出高于平均身高的信息
 select * from students where height>(select avg(height) from students);

 查询最高的男生信息
 select * from students where height = (select max(height) from students);

 列级子查询
 查询学生的班级号能对应的学生信息
 select * from students where cls_id in (select id from classes);

关注我的公众号【阳仔不想当码农】， 给你更多愉快体验！