完整MySQL入门指令练习，有插入表格数据
欢迎关注微.信公众号：软件集合
// 完整MySQL入门训练，有插入数据
-- <01 mysql数据库的操作>
-- 连接数据库
mysql -uroot -pmysql
-- mysql: [Warning] Using a password on the command line interface can be insecure.
mysql -uroot -p
-- 退出数据库连接
quit/exit/ctrl z
--书写sql语句的基本语法
--1. sql语句不区分大小写
--2. sql语句书写完，必须以分号结尾（;）
--3. 书写sql语句可以换行
-- 显示数据库版本 version
select version();
-- 显示时间
select now();
-- 查看当前使用的数据库
select database();
-- 查看所有数据库
--这些数据库是mysql系统的数据库，不要操作
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Test |
| mysql |
| performance_schema |
| sys |
+--------------------+
-- 创建数据库
-- create database 数据库名 charset=utf8;
-- 数据库名随意
create database python729 charset=utf8;
-- 查看创建数据库的语句
-- show create database 数据库名
show create database python729;
-- 使用数据库
-- use 数据库的名字
use python729;
-- 删除数据库
-- drop database 数据库名;
drop database python729;
------------------------------------------------
--<02 数据表的操作>
-- 查看当前数据库中所有表
-- 注意：要查看所有的表，首先得选择一个数据库
use python729;
show tables;
-- 创建表
-- create table 数据表名字 (字段名 类型 约束[, 字段 类型 约束]);
-- 数据表命名：字符+数字+_,不能以数字开始，最后都用小写字母，单词之间可以用_拼接。
-- int unsigned 无符号整形
-- auto_increment 表示自动增长
-- not null 表示不能为空
-- primary key 表示主键
-- default 默认值
-- 注意： 约束条件要写在类型后面，约束条件中的unsigned要跟在类型的后面。
-- 创建 classes 表(id、name)
create table classes(
id int(11) unsigned primary key auto_increment,
name varchar(3) not null unique
);
-- 创建 students 表(id、name、age、high (decimal)、gender (enum)、cls_id)
-- id int unsigned primary key not null auto_increment(自动增长)
-- name varchar(20) not null
-- age tinyint default 0
-- height decimal(5,2)
-- gender enum('男', '女', '保密')
-- cls_id int(11)
create table students(
id int(11) unsigned primary key auto_increment not null,
name varchar(20) not null,
age tinyint default 0,
height decimal(5, 2),
gender enum('男', '女', '保密'),
cls_id int
);
-- 查看表结构
-- desc 数据表的名字;
desc classes;
-- 查看表的创建语句
-- show create table 表名字;
show create table classes;
+--------+----------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(4) | YES | | 0 | |
| height | decimal(5,2) | YES | | NULL | |
| gender | enum('男','女','保密') | YES | | NULL | |
| cls_id | int(11) | YES | | NULL | |
+--------+----------------------------+------+-----+---------+----------------+
-- 修改表-添加字段 mascot (吉祥物)
-- alter table 表名 add 列名 类型 约束;
alter table students add mascot varchar(11) not null;
-- 修改表-修改字段：不重命名版
-- alter table 表名 modify 列名 类型及约束;
alter table students modify mascot char(11) null;
-- 修改表-修改字段：重命名版
-- alter table 表名 change 原名 新名 类型及约束;
alter table students change mascot new_mascot varchar(3);
-- 修改表-删除字段
-- alter table 表名 drop 列名;
alter table students drop new_mascot;
-- 删除表
-- drop table 表名;
drop table classes;
-- drop database 数据库;
drop database demo;
--03 增删改查(curd)(重点)
-- 增加
-- 全列(字段)插入
-- insert into 表名 values(数据, 数据, 数据, ...) 有多少个字段,就书写多少个数据
-- 主键字段 可以用 0 null default 来占位
-- 对于枚举类型来说,可以使用数字(下标,从 1 开始)代替具体的字符串
-- 向classes表中插入 一个班级
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(3) | NO | UNI | NULL | |
+-------+------------------+------+-----+---------+----------------+
-- insert into classes values(0, '第一班级');
-- ERROR 1406 (22001): Data too long for column 'name' at row 1（超过约束条件）
insert into classes values(0, '第一班');
insert into classes values(0, 't11');
--提前补充：select * from 表名;
select * from classes;
+--------+----------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(4) | YES | | 0 | |
| height | decimal(5,2) | YES | | NULL | |
| gender | enum('男','女','保密') | YES | | NULL | |
| cls_id | int(11) | YES | | NULL | |
+--------+----------------------------+------+-----+---------+----------------+
-- 向students表插入 一个学生信息
insert into students values(null, 'hello', null, 200, 1, 2);
insert into students values(null, 'good', 0, 200, 1, 2);
-- 插入指定列数据
-- insert into 表名(字段, 字段, ...) values (数据, 数据, ...);
insert into students(name, gender, cls_id) values('tina', 2, 1);
-- 插入多行数据
-- insert into 表名 values(数据, 数据，数据), (), ...; # 全字段的多行插入
insert into students values(default, 'hello1', 0, 180, 3, 2), (default, 'hello2', 0, 190, 1, 3), (default, 'hello3', 0, 190, 2, 4);
-- insert into 表名(字段, 字段,...) values (), (), ...; # 指定字段的多行插入
insert into students(name) values('good1'), ('good2'), ('good3'), ('good4');
-- 查询基本使用
-- 查询所有列
-- select * from 表名;
select * from students;
-- 查询指定列
-- select 列1,列2,... from 表名;
select name,age from students;
-- 修改
-- update 表名 set 列1=值1,列2=值2... [where 条件];
-- 全部修改
update students set age=18;
update students set age=23, height=180;
-- 按条件修改
-- where后面会详细讲解
update students set age=28 where name='hello';
-- 删除
-- 物理删除
-- delete from 表名 where 条件
delete from students where id=10;
-- 逻辑删除
-- 用一个字段来表示 这条信息是否已经不能再使用了
-- 给students表添加一个 is_delete 字段 bit 类型
alter table students add is_delete bit default 0;
update students set is_delete=1 where id=1;
--查询没有被删除的所有数据is_delete=0， is_delete=1就是被逻辑删除的数据
select * from students where is_delete=0;
-- 数据库备份与恢复(了解)
-- 注意：以下指令在终端下使用,不是在mysql命令行客户端使用
-- 备份
-- mysqldump –uroot –p 数据库名 > 文件名.sql;
mysqldump -uroot -p python46 > python46.sql
-- 恢复
-- mysql -uroot –p 新数据库名 < 文件名.sql;
mysql -uroot -p python46_bak < python46.sql
-- as关键字使用， 起别名(字段，表)
-- select 字段[as 别名] , 字段[as 别名] from 数据表;
select height as 身高, gender as 性别 from students;
select height 身高, gender 性别 from students;
-- select 别名.字段 .... from 表名 as 别名;
select students.height, students.gender from students;
select s.height, s.gender from students as s;
select s.height, s.gender from students s;
--指定别名，在使用的时候必须使用别名
-- select students.height, students.gender from students as s; 错误用法
-- distinct关键字 消除重复行
-- select distinct 字段名 from 表名;
select distinct gender from students;
select distinct cls_id from students;
--第二部分，表结构要和插入的数据匹配
--删除students和classes表
drop table students;
drop table classes;
--新建students和classes表
-- students表
create table 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('男','女','中性','保密') default '保密',
cls_id int unsigned default 0,
is_delete bit default 0
);
-- classes表
create table classes (
id int unsigned auto_increment primary key not null,
name varchar(30) not null
);
-- 向students表中插入数据
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),
(0,'凌小小',28,180.00,2,1,0),
(0,'司马二狗',28,120.00,1,1,0);
-- 向classes表中插入数据
insert into classes values (0, "python_01期"), (0, "python_02期"),(0,'Python_03期');
-- where条件查询(students表)
-- 基础的where查询
-- 查询id为5的这条数据
select * from students where id=5;
-- 比较运算符
-- select .... from 表名 where .....
-- >
-- 查询大于18岁的信息
select * 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;
-- =
-- 查询年龄为18岁的所有学生的名字
select * from students where age=18;
-- != 或者 <>
-- 查询年龄不为18的所有学生的id和name和age
select id, name, age from students where age != 18;
-- 逻辑运算符
-- and
-- 18和28之间的所有学生信息
select * from students where age >= 18 and age <= 28;
-- 错误写法：select * from students where age >= 18 and <= 28;
-- 错误写法： select * from students where 18 <= age <= 28;
-- 18岁以上的女性
select * from students where age > 18 and gender=2;
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 not (age > 18 and gender = 2)
-- 模糊查询(where name like 要查询的数据)
-- like
-- % 替换任意个
-- _ 替换1个
-- 查询姓名中 以 "小" 开始的名字 name like '小%'
select * from students where name like '小%';
-- 查询姓名中 有 "小" 所有的名字 name like '%小%'
select * from students where name like '%小%';
-- 查询有2个字的名字
select * from students where name like '__';
-- 查询有3个字的名字
select * from students where name like '___';
-- 查询至少有2个字的名字
select * from students where name like '__%';
-- 范围查询
-- in (1, 3, 8)表示在一个非连续的范围内
-- 查询 年龄为18、34的姓名
select name from students where age = 18 or age = 34 or age = 28;
select name from students where age in (18, 34, 28);
-- not in 不非连续的范围之内
-- 年龄不是 18、34岁的信息
select * from students where age not in (18, 34);
-- between ... and ...表示在一个连续的范围内
-- 查询 年龄在18到34之间的的信息
select * from students where age between 18 and 34;
select * from students where age >= 18 and age <= 34;
-- not between ... and ...表示不在一个连续的范围内
-- 查询 年龄不在在18到34之间的的信息
select * from students where age not between 18 and 34;
select * from students where not (age >= 18 and age <= 34);
-- 空判断
-- 判空is null
-- 查询身高为空的信息
select * from students where height is null;
-- 判非空is not null
select * from students where height is not null;
-- 排序
-- order by 字段 asc|desc asc默认升序
-- select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
-- asc从小到大排列，即升序
-- desc从大到小排序，即降序
-- 查询年龄在18到34岁之间的男性，按照年龄从小到大到排序(默认是asc升序)
select * from students where age between 18 and 34 and gender=1 order by age;
-- 查询年龄在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;
-- 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序, 如果年龄也相同那么按照id从大到小排序
select * from students where age between 18 and 34 and gender=2 order by height desc,age,id desc;
-- 分页
-- select * from 表名 limit start,count
-- star 开始查询的索引，默认是0
-- count 查询的条数
-- 限制查询出来的数据个数
-- 查询前5个学生
-- 注意： limit 要放在sql语句的最后
--查询前3个女生
--如果start为0，可以省略不写
select * from students where gender=2 limit 0, 3;
select * from students where gender=2 limit 3;
-- 年龄最大的三个人 (先根据年龄排序,再使用 limit)
select * from students order by age desc limit 3;
-- 分页使用
-- 实现分页确定2个要素就可以了 当前第几页 每页显示3条
-- 当前第n页 limit start 查询的数据
-- 1 0 0 1 2
-- 2 3 3 4 5
-- 3 6 6 7 8
-- 4 9 9 10 11
-- start = (当前页-1) * 每页显示的条数
-- 当前页=n， 每页显示m条
-- select * from students limit (n-1)*m, m;
select * from students limit 0, 3;
select * from students limit 3, 3;
select * from students limit 6, 3;
-- 聚合函数
-- 总数
-- count
-- 查询全部学生有多少人
-- 基础语法 select 聚合函数(字段) from 表名;
select count(*) from students;
select count(id) from students;
select count(height) from students;
-- 聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算，必须使用ifnull函数对null值做替换。
select count(ifnull(height, 0)) from students;
-- 可以给count起别名
select count(*) as '学生总数' from students;
select count(*) students_count from students;
-- count会返回非null数据的总和
select count(height) from students;
-- 查询男性有多少人，女性有多少人
select count(*) from students where gender='男';
select count(*) from students where gender=2;
--欢迎关注微.信公众号：软件集合
-- 最大值
-- max
-- 查询最大的年龄
select max(age) from students;
-- 查询女性的最高 身高
select max(height) from students where gender=2;
-- 最小值
-- min
-- 查询最小年龄
select min(age) from students;
-- 求和
-- sum
-- 计算所有人的年龄总和
-- sum(age)里面是字段 求得就是字段的数据总和
select sum(age) from students;
-- sum(age<15)里面是表达式 求得就是满足条件的数据的总个数
select sum(age<15) from students;
-- 平均值
-- avg
-- 计算平均年龄
select avg(age) from students;
-- 计算平均年龄 sum(age)/count(*)
select sum(age)/count(*) from students;
-- 四舍五入 round(123.23 , 1) 保留1位小数
-- 计算所有人的平均年龄，保留2位小数
select avg(age) from students;
select round(avg(age), 2) from students;
-- 计算男性的平均身高 保留2位小数
select round(avg(height), 2) from students where gender
-- 分组(重点)
-- group by 字段
-- 按照性别分组,查询所有的性别
select gender from students group by gender;
-- 注意： 如果使用了group by,那么查询的字段只能是group by后的分组字段或者聚合函数字段。
-- 错误写法：select name, gender from students group by gender;
-- 计算每种性别中的人数
select gender, count(gender) from students group by gender;
-- group_concat(...)
-- 查询同种性别中的姓名
select gender, group_concat(name) from students group by gender;
-- 查询同种性别中有哪些年龄
select gender, group_concat(age) from students group by gender;
-- group_concat('字段1', '字段2', ...)
-- 查询同种性别中的人的姓名和年龄
select gender, group_concat(name, ':', age)
from students
group by gender;
-- 查询每组性别的平均年龄
select gender, avg(age) from students group by gender;
-- 查询平均年龄超过30岁的性别，以及姓名 having avg(age) > 30(重点)
select gender, group_concat(name)
from students
group by gender
having avg(age) > 30;
-- having是对每个组中的数据进行筛选和group by联合使用
-- where是对所有的数据进行筛选
-- 查询每种性别的平均年龄和名字
select gender, avg(age), group_concat(name)
from students
group by gender;
-- 查询每种性别中的人数多于2个的性别和姓名（重点）
select gender, group_concat(name)
from students
group by gender
having count(*) > 2;
-- with rollup 汇总的作用(了解)
-- 查询每个性别的人数
select gender, count(*) from students group by gender with rollup;
-- 查询每个性别的平均年龄
select gender, avg(age) from students group by gender with rollup;
-- 连接查询(重点)
-- 内连接
-- inner join ... on
-- select ... from 表A inner join 表B on 条件;
-- 查询 有能够对应班级的学生以及班级信息
-- 在关联查询中，如果使用字段，要指明字段所属的表： 表名.字段名
select * from students inner join classes on students.cls_id = classes.id;
-- 按照要求显示姓名、班级
select students.name, classes.name, students.height
from students
inner join classes
on students.cls_id = classes.id;
-- 给数据表起名字
-- 注意：表起了别名，在后面使用的时候，就一定要用别名，否则报错
select s.name, c.name
from students s
inner join classes c
on s.cls_id = c.id;
-- 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息 students.*，只显示班级名称 classes.name.
select students.*, classes.name
from students
inner join classes
on students.cls_id = classes.id;
-- 在以上的查询中，将班级姓名显示在第1列
select classes.name class_name, students.*
from students
inner join classes
on students.cls_id = classes.id;
-- 查询 有能够对应班级的学生以及班级信息, 按照班级进行排序
-- select c.xxx s.xxx from students as s inner join clssses as c on .... order by ....;
select c.id, c.name, s.id, s.name
from students s inner join classes c
on s.cls_id = c.id
order by c.id;
-- 当时同一个班级的时候，按照学生的id进行从小到大排序
select c.id, c.name, s.id, s.name
from students s inner join classes c
on s.cls_id = c.id
order by c.id, s.id;
-- 连接查询-内连接其他写法
-- 第一种写法，可以把inner去掉
select students.name, classes.name
from students join classes
on students.cls_id = classes.id;
-- 第二种写法
select students.name,classes.name
from students, classes
where students.cls_id = classes.id;
-- left join
-- 查询每位学生对应的班级信息
select * from
students left join classes
on students.cls_id = classes.id;
-- 补充：查询没有对应班级信息的学生
-- select ... from xxx as s left join xxx as c on..... where .....
select * from
students left join classes
on students.cls_id = classes.id
where clssses.id is null;
-- right join on
-- 将数据表名字互换位置，用left join完成
-- 查询每位学生对应的班级信息
select * from classes right join students on students.cls_id = classes.id;
-- 聚合函数
-- 总数
-- count
-- 查询全部学生有多少人
-- 基础语法 select 聚合函数(字段) from 表名;
select count(*) from students;
select count(id) from students;
select count(height) from students;
-- 聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算，必须使用ifnull函数对null值做替换。
select count(ifnull(height, 0)) from students;
-- 可以给count起别名
select count(*) as '学生总数' from students;
select count(*) students_count from students;
-- count会返回非null数据的总和
select count(height) from students;
-- 查询男性有多少人，女性有多少人
select count(*) from students where gender='男';
select count(*) from students where gender=2;
-- 最大值
-- max
-- 查询最大的年龄
select max(age) from students;
-- 查询女性的最高 身高
select max(height) from students where gender=2;
-- 最小值
-- min
-- 查询最小年龄
select min(age) from students;
-- 求和
-- sum
-- 计算所有人的年龄总和
-- sum(age)里面是字段 求得就是字段的数据总和
select sum(age) from students;
-- sum(age<15)里面是表达式 求得就是满足条件的数据的总个数
select sum(age<15) from students;
-- 平均值
-- avg
-- 计算平均年龄
select avg(age) from students;
-- 计算平均年龄 sum(age)/count(*)
select sum(age)/count(*) from students;
-- 四舍五入 round(123.23 , 1) 保留1位小数
-- 计算所有人的平均年龄，保留2位小数
select avg(age) from students;
select round(avg(age), 2) from students;
-- 计算男性的平均身高 保留2位小数
select round(avg(height), 2) from students where gender
-- 分组(重点)
-- group by 字段
-- 按照性别分组,查询所有的性别
select gender from students group by gender;
-- 注意： 如果使用了group by,那么查询的字段只能是group by后的分组字段或者聚合函数字段。
-- 错误写法：select name, gender from students group by gender;
-- 计算每种性别中的人数
select gender, count(gender) from students group by gender;
-- group_concat(...)
-- 查询同种性别中的姓名
select gender, group_concat(name) from students group by gender;
-- 查询同种性别中有哪些年龄
select gender, group_concat(age) from students group by gender;
-- group_concat('字段1', '字段2', ...)
-- 查询同种性别中的人的姓名和年龄
select gender, group_concat(name, ':', age)
from students
group by gender;
-- 查询每组性别的平均年龄
select gender, avg(age) from students group by gender;
-- 查询平均年龄超过30岁的性别，以及姓名 having avg(age) > 30(重点)
select gender, group_concat(name)
from students
group by gender
having avg(age) > 30;
-- having是对每个组中的数据进行筛选和group by联合使用
-- where是对所有的数据进行筛选
-- 查询每种性别的平均年龄和名字
select gender, avg(age), group_concat(name)
from students
group by gender;
-- 查询每种性别中的人数多于2个的性别和姓名（重点）
select gender, group_concat(name)
from students
group by gender
having count(*) > 2;
-- with rollup 汇总的作用(了解)
-- 查询每个性别的人数
select gender, count(*) from students group by gender with rollup;
-- 查询每个性别的平均年龄
select gender, avg(age) from students group by gender with rollup;
-- 连接查询(重点)
-- 内连接
-- inner join ... on
-- select ... from 表A inner join 表B on 条件;
-- 查询 有能够对应班级的学生以及班级信息
-- 在关联查询中，如果使用字段，要指明字段所属的表： 表名.字段名
select * from students inner join classes on students.cls_id = classes.id;
-- 按照要求显示姓名、班级
select students.name, classes.name, students.height
from students
inner join classes
on students.cls_id = classes.id;
-- 给数据表起名字
-- 注意：表起了别名，在后面使用的时候，就一定要用别名，否则报错
select s.name, c.name
from students s
inner join classes c
on s.cls_id = c.id;
-- 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息 students.*，只显示班级名称 classes.name.
select students.*, classes.name
from students
inner join classes
on students.cls_id = classes.id;
-- 在以上的查询中，将班级姓名显示在第1列
select classes.name class_name, students.*
from students
inner join classes
on students.cls_id = classes.id;
-- 查询 有能够对应班级的学生以及班级信息, 按照班级进行排序
-- select c.xxx s.xxx from students as s inner join clssses as c on .... order by ....;
select c.id, c.name, s.id, s.name
from students s inner join classes c
on s.cls_id = c.id
order by c.id;
-- 当时同一个班级的时候，按照学生的id进行从小到大排序
select c.id, c.name, s.id, s.name
from students s inner join classes c
on s.cls_id = c.id
order by c.id, s.id;
-- 连接查询-内连接其他写法
-- 第一种写法，可以把inner去掉
select students.name, classes.name
from students join classes
on students.cls_id = classes.id;
-- 第二种写法
select students.name,classes.name
from students, classes
where students.cls_id = classes.id;
-- left join
-- 查询每位学生对应的班级信息
select * from
students left join classes
on students.cls_id = classes.id;
-- 补充：查询没有对应班级信息的学生
-- select ... from xxx as s left join xxx as c on..... where .....
select * from
students left join classes
on students.cls_id = classes.id
where clssses.id is null;
-- right join on
-- 将数据表名字互换位置，用left join完成
-- 查询每位学生对应的班级信息
select * from classes right join students on students.cls_id = classes.id;
--欢迎关注微.信公众号：软件集合
欢迎关注微.信公.众号：软件集合
扫一扫
4796
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
