MySQL 基本操作

启动

启动mysql服务

net start mysql

关闭mysql服务

net stop mysql

登录

mysql -u root -p

显示当前时间

select now();

库

退出数据库

exit;quit;

查看所有数据库

show databases;

创建数据库

create database python41 charset=utf8;

保存数据库的路径命令

show global variables like “%datadir%”;

使用数据库

use python41;

查看当前的使用的数据库

select database();

删除数据库

drop database python41;

查看数据库的所有表

show tables;

表

创建表

create table students(

id int unsigned primary key auto_increment not null,

name varchar(20) not null,

age tinyint default 0,

gender enum("男","女") default "男"

);

create table sinanews(
     id int unsigned primary key auto_increment not null,
     update_time datetime default null,
     title varchar(100) not null,
     url varchar(200) not null,
     intro varchar(300) not null,
     media_name varchar(50)
     );

create table details(
id int unsigned primary key auto_increment not null,
update_time datetime default null comment'数据最后更新时间',
province varchar(50) default null comment'省',
city varchar(50) default null comment'市',
confirm int(11) default null comment'累计确诊',
confirm_add int(11) default null comment'新增确诊',
heal int(11) default null comment'累计治愈',
dead int(11) default null comment'累计死亡'
);

create table history(
update_time datetime primary key comment'数据最后更新时间',
localConfirm int(11) default null comment'本土现有确诊',
nowConfirm int(11) default null comment'现有确诊',
confirm int(11) default null comment'累计确诊',
noInfect int(11) default null comment'无症状感染者',
importedCase int(11) default null comment'境外输入',
dead int(11) default null comment'累计死亡'
);

create table hotsearch(
id int unsigned primary key auto_increment not null,
update_time datetime not null,
content varchar(255) not null
);

create table score_c(
    id int unsigned primary key auto_increment not null,
    student_num varchar(100) not null,
    student_name varchar(50) not null,
    pingshi float(3,1) not null,
    qimo float(3,1) not null,
    zongping float(3,1) not null
 );

create table battery_temperature(
     id int unsigned primary key auto_increment not null,
     inspection_date varchar(50) default null,
     temperature_0 varchar(50) not null,
     temperature_8 varchar(50) not null,
     temperature_16 varchar(50) not null
     );

create table battery_humidity(
     id int unsigned primary key auto_increment not null,
     inspection_date varchar(50) default null,
     humidity_0 varchar(50) not null,
     humidity_8 varchar(50) not null,
     humidity_16 varchar(50) not null
     );

create table industry_news(
     id int unsigned primary key auto_increment not null,
     news_title varchar(100) default null,
     news_link varchar(200) not null
     );

create table wind_power_production(
     id int unsigned primary key auto_increment not null,
     year varchar(50) not null,
     electricity_quantity varchar(100) not null
     );

查看表结构

desc students;

修改表-添加birthday字段

alter table students add birthday datetime not null;

修改表-修改字段类型

alter table students modify birthday date;

修改表-修改字段名和字段类型

alter table students change birthday birth datetime not null;

修改表-删除birthday字段

alter table students drop birth;

查看创建表的SQL语句

show create table 表名;

show create table students;

查看创建数据库的SQL语句

show create detabase 数据库名;

show create detabase mytest;

删除表

drop table sinanews;

查询

查询指定列数据

select name,age from students;

查询所有列数据

select * from students;

添加数据-全列插入

insert into students values(0,'张三',18,default);

主键列插入数据的时候可以指定：0，default，null

这里的default表示使用该字段的默认值

insert into students values(default,'曹操',65,default);

insert into students values(null,'欧阳锋',55,default);

添加数据-部分列插入

insert into students(name,age) values('郭靖',30);

添加数据-全列多行插入

insert into students values(0,'黄蓉',28,'女'),(0,'黄老邪',50,default);

添加数据-部分列多行插入

insert into students(name,age) values('杨过',20),('周伯通',55);

修改数据

update students set age = 50 where id = 10;

update students set age = 18, gender = '女' where id = 3;

删除数据

delete from students where id = 8;

实际开发过程中不会物理删除数据，使用逻辑删除，添加一个标识字段

alter table students add is_del tinyint default 0;

这里删除数据其实修改标识字段

update students set is_del = 1 where id = 7;

as关键字（用于给表的字段和表起别名）

select name as 姓名, age as 年龄 from students;

提示：as关键字可以省略，也表示起别名

distinct关键字（去除重复数据行）

select distinct gender from students;

select distinct age, gender from students;

给两列去重

查询编号大于三的学生

select * from students where id > 3;

查询编号不大于四的学生

select * from students where id <= 4;

查询姓名不是黄蓉的学生

select * from students where name != '黄蓉';

或者

select * from students where name <> '黄蓉';

查询没有被删除的学生

select * from students where is_del = 0;

查询编号大于三的女同学

select * from students where id > 3 and gender = '女';

查询编号小于四或者没有被删除的学生

select * from students where id < 4 or is_del = 0;

查询年龄不在10岁到15岁之间的学生

select * from students where not (age >= 10 and age <= 15);

查询编号为3至8的学生

select * from students where id >= 3 and id <= 8;

也可以这样写

select * from students where id between 3 and 8;

查询编号不是3至8的学生

select * from students where not (id between 3 and 8);

查询编号是3、5、7的学生

select * from students where id in(3,5,7);

查询编号不是3、5、7的学生

select * from students where id not in(3,5,7);

查询姓黄的学生

select * from students where name like '黄%';

查询姓黄并且名是一个字的学生

select * from students where name like '黄_';

select * from students where name like '黄__';

%表示任意多个字符

_表示任意一个字符

查询姓黄或叫靖的学生

select * from students where name like '黄%'or name like '%靖';

查询没有填写身高的学生

select * from students where height is null;

查询填写身高的学生

select * from students where height is not null;

MySQL高级查询

排序使用order by

asc升序默认

desc降序

查询未删除男生信息，按照学号降序

select * from students where is_del = 0 and gender = '男' order by id desc;

显示所有的学生信息，先按照年龄从大到小排序，当年龄相同时，按照身高从高到矮进行排序

select * from students order by age desc,height desc;

默认是asc升序

分页查询

查询前三行男生信息

select * from students where gender = '男' limit 0, 3;

第一个参数是开始行索引，默认是0可以不指定，第二个参数是查询的条数

简写

select * from students where gender = '男' limit 3;

查询学生表，获取第n页数据的SQL语句

select * from students limit (n-1)*m m;

聚合函数

count(col)：表示求指定列的总行数

max(col)：表示求指定列的最大值

min(col)：表示求指定列的最小值

sum(col)：表示求指定列的和

avg(col)：表示求指定列的平均值

清屏

linux:system clear

windows:system cls

查询学生个数

select count(height) from students;

注意点：聚合函数不会对空值进行统计

一般如果要是指定列名，那么就是主键字段

select count(id) from students;

通用的写法

select count(*) from students;

查询女生的编号最大值

select max(id) from students where gender = '女';

查询未删除学生的最小编号

select min(id) from students where is_del = 0;

查询男生的总身高

select sum(height) from students where gender = '男';

求男生的平均身高

select sum(height) / count(*) from students where gender = '男';

select avg(ifnull(height,0)) from students where gender = '男';

注意点：聚合函数不对空值进行统计

ifnull函数判断指定的字段是否是空值，如果是空值使用默认值0

分组查询

查询性别的种类

select distinct gender from students;

select gender from students group by gender;

根据name和gender字段进行分组

根据gender字段进行分组，查询每个分组的姓名信息

select gender,group_concat(name) from students group by gender;

group_concat：统计每个分组指定字段的信息集合，信息之间使用逗号分隔

统计不同性别的平均年龄

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

统计不同性别的人的个数

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

根据gender字段进行分组，统计分组条数大于2的

select gender,count(*) from students group by gender having count(*) > 2;

对分组数据进行过滤使用having

根据gender字段进行分组，汇总总人数

select gender,count(*) from students group by gender with rollup;

根据gender字段进行分组，汇总所有人的年龄

select gender,group_concat(age) from students group by gender with rollup;

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

内连接

使用内连接查询学生表与班级表

select s.name,c.name from students s inner join classes c on s.c_id = c.id;

左连接（以左表为主，右表查询不到填充空值）

使用左连接查询学生表与班级表

select * from students s left join classes c on s.c_id = c.id;

left左边是左表，右边是右表

右连接（以右表为主，左表查询不到填充空值）

使用右连接查询学生表与班级表

select * from students s right join classes c on s.c_id = c.id;

自连接

使用自连接查询省份和城市信息

source areas.sql;

source 表示执行的sql文件

select c.id,c.title,c.pid,p.title from areas c inner join areas p on c.pid = p.id where p.title = '河北省'

子查询

查询大于平均年龄的学生

select * from students where age > (select avg(age) from students);

查询学生在班的所有班级名称

select * from classes where id in(select c_id from students where c_id is not null);

查询年龄最大，身高最高的学生

select * from students where age = (select max(age) from students )and height = (select max(height) from students);
或者
select * from 00 where (age,height) = (select max(age),max(height) from students);

子查询是一个完整的查询语句，子查询的执行顺序，先执行子查询然后主查询根据子查询的结果再执行

为学生表得c_id字段添加外键约束

alter table students add foreign key(c_id) references classes(id);

创建学校表

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

创建老师表添加学校外键

 create table teacher(
	id int unsigned not null primary key auto_increment,
	name varchar(20) not null,
	s_id int unsigned,
	foreign key(s_id) references school(id)
);

删除外键

alter table teacher drop foreign key teacher_ibfk_1;

需要先获取外键约束名称，该名称系统会自动生成，可以通过查看表创建语句来获取名称

查看表创建语句来获取名称

show create table teacher;

SQL注释

-- 创建“京东”数据库

查询类型cate_name为“超极本”的商品名称、价格

select name,price from goods where cate_name = '超级本';

显示商品的分类

select distinct cate_name from goods;

select cate_name from goods group by cate_name;

求所有电脑产品的平均价格，并且保留两位小数

select round(avg(price),2) from goods;

显示每种商品的平均价格

select cate_name,avg(price) from goods group by cate_name;

查询每种类型的商品中 最贵、最便宜、平均价、数量

select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name;

查询所有价格大于平均价格的商品，并且按价格降序排序

select * from goods where price > (select avg(price) from goods) order by price desc;

创建商品分类表

将goods表中的分类名称更改为商品分类表中对应的分类id，连接更新表中的某个字段

update goods g inner join goods_cates gs on g.cate_name = gs.name set g.cate_name = gs.id;

查询品牌信息

select brand_name from goods group by brand_name;

通过create table … select来创建商品品牌表并且同时插入数据

create table goods_brand(
	id int unsigned not null primary key auto_increment,
 	name varchar(50) not null) select brand_name as name from goods group by 		brand_name;

注意点：查询表中的名称要和新建表的字段一致

插入双飞燕品牌

insert into goods_brand(name) values('双飞燕');

查看goods表中的商品品牌对应的商品品牌id

select * from goods g inner join goods_brand gs on g.brand_name = gs.name;

将goods表中的品牌更改成品牌表中对应的品牌id，连接更新表中的某个字段

 update goods g inner join goods_brand gs on g.brand_name = gs.name set g.brand_name = gs.id;

int类型可以转换成varchar类型，varchar类型可以转成int类型

修改goods表结构

alter table goods change cate_name cate_id int not null,change brand_name brand_id int not null;

索引

索引可以提高数据的查询速度

主键会自动创建一个索引

外键约束这个字段也会自动创建以个索引

经常查询的字段要建索引

经常更新的表不要创建索引

alter table classes add index(name);

给name字段删除索引

 alter table classes drop index name;

可以节省磁盘空间

创建联合索引

 alter table mytest1 add index(name,age);