MySQL详细语法

一、数据库操作

1.登录MySQL

mysql -u用户名 -p密码
mysql -uroot -proot666

2.退出MySQL

exit

3.新建数据库

create database 数据库名 charset=utf8;              charset=utf8: 写入中文
create database Python1 charset=utf8;

4.使用数据库

use 数据库的名字;
use Python1;

5.查看所有数据库

show databases;

6.删除数据库

drop database 数据库名;
drop database Python1;

二、数据表操作

1.查看当前数据库中所有表

show tables;

2.创建表

-- create table 表名(字段名 数据类型 约束条件)
	-- 创建classes表（id、name）
	create table classes (
		id int primary key auto_increment,
		name varchar(20)
	);

	-- 创建students表（id, name, age, height, gender）
	create table students (
		id int primary key auto_increment,
		name varchar(20),
		age int,
		height int,
		gender enum('男', '女')
	);

3.修改表

-- 修改表-新增字段                                    alter -- 修改   
	-- alter table 表名 add 列名 类型及约束;
		-- 1、添加birth字段到最后                       最后--默认
		alter table students add birth date;

		-- 2、添加score字段到第1列                       首列--first
		alter table students add score int first;

		-- 3、添加phone字段到name的后面                   name的后面 -- after name
		alter table students add phone varchar(20) after name;


-- 修改表-修改字段： 
		-- 不修改列名: alter table 表名 modify 列名 类型及约束;
		alter table students modify phone int;
	
		--  修改列名: alter table 表名 change 原名 新名 类型及约束;
		alter table students change gender sex enum('男', '女');

4.删除表

-- 删除字段
	-- alter table 表名 drop 列名;
	alter table students drop score;

-- 删除表
	-- drop table 表名;
	drop table students;

三、数据的增删改查(CRUD)

1.增加

1.1全字段插入

-- insert into 表名 values (...);     
	-- 向classes表中插入一个班级
	insert into classes values (0, 'Python1班');
	insert into classes values (0, 'Python2班');
	insert into classes values (0, 'Python3班');

	-- 向students表中插入一个学生信息            枚举下标从1开始
	-- 主键字段 可以用0: 占位符 
	insert into students values (0, '张三', 18, 180, '男');
	insert into students values (0, '李四', 19, 175, '女');
	insert into students values (0, '王五', 20, 170, 3);
	insert into students values (0, '赵六', 21, 165, 1);

1.2多行插入

insert into students values 
	(0, '小明', 18, 180, 2), 
	(0, '小月', 18, 180, 2), 
	(0, '小花', 29, 185, 1),
	(0, '小红', 59, 175, 1), 
	(0, '小张', 38, 160, 2),
	(0, '小李', 38, 160, 2);

2.修改

-- update 表名 set 列1=值1, 列2=值2, 列3=值3 ... where 条件判断;
	-- 所有学生的性别修改成男
	update students set gender='男';

	-- 将id为3的学生性别修改成女
	update students set gender=2 where id=3;

	-- 将id为4的学生名字修改成小小
	update students set name='小小' where id=4;

	-- 将id为5的学生，名字修改成小小，性别修改成男
	update students set name='小小', gender='男' where id=5;

3.删除

-- delete from 表名 where 条件;
-- 删除名字是张三的学生
delete from students where name='张三';

-- 删除id<4的学生
delete from students where id<4;

4.查询

4.1基本查询

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

-- 查询指定列 -- id, age, name
select id, name, age from students;

-- 可以用as设置别名         
-- select 字段1 as 别名1, 字段2 as 别名2 from 数据表名 where ...;
select id as 学号, name as 姓名, age as 年龄 from students;

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

4.2条件查询

-- 比较运算符
		-- 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;

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

		-- 18岁以上的女生信息
		select * from students where age>18 and gender='女';

		-- or 
		-- 查看18岁以上 或者 身高大于170的男生
		select * from students where age>18 or (height>170 and gender='男');

		-- not 
		-- 不在18岁以上的女生的信息
		select * from students where  not (age>18 and gender='女');

4.3范围查询

-- in(1, 3, 8)表示在一个非连续的范围内            --离散  
	-- 查询 年龄为18, 27, 34的同学信息
	select * from students where age=18 or age=27 or age=34;
	select * from students where age in(18, 27, 34);

	-- not in 不连续的范围之内
	-- 查询 年龄不在18, 27, 34的同学信息
	select * from students where age not in(18, 27, 34);

	-- between ... and ... 表示在一个连续的范围内    -- 连续  
	-- 查询 年龄是18~34之间的同学信息
	select * from students where age between 18 and 34;

	-- not between ... and ... 表示不在一个连续的范围内
	-- 查询 年龄不是18~34之间的同学信息
	select * from students where age not between 18 and 34;

4.4排序查询

-- order by 单个字段（默认：从小到大排序, 省略了asc）  asc--从小到大  desc--从大到小
	-- 查询年龄在18~34之间的男性，年龄从小到大排序
	select * from students where (age between 18 and 34) and gender='男' order by age;

	-- 查询年龄在18~34之间的女性，身高从大到小排序
	select * from students where (age between 18 and 34) and gender='女' order by height desc;

	-- order by 多个字段    -- 往后写就对了
	-- 查询年龄在18到34岁之间的女性，身高从大到小排序，如果身高相同情况下按照ID大小排序
	select * from students where (age between 18 and 34) and gender='女' order by height desc, id desc, age desc;

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

4.5聚合查询

-- count 总数
	-- 查询有多少男生，多少女生
	select count(*) as 男生人数 from students where gender='男';
	select count(*) as 女生人数 from students where gender='女';

	-- 查询学生总数
	select count(*) from students
	select count(*) as 学生总数 from students;

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

	-- 查询男生年龄最大的	
	select max(age) as 男生最大年龄 from students where gender='男';
	select max(age) as 女生最大年龄 from students where gender='女';

-- min 最小值
	-- 查询最小的年龄
	select min(age) as 班级最小年龄 from students;

-- sum 求和
	-- 计算所有男生的年龄和
	select sum(age) as 男生年龄和 from students where gender='男';

-- avg 平均值
	-- 计算男生平均年龄
	select avg(age) as 男生平均年龄 from students where gender='男';

-- round(num, 2) 保留2位小数
	-- 计算女生平均年龄，保留2位小数
	select round(avg(age), 2) as 女生平均年龄 from students where gender='女';

4.6分组查询

-- group by 
	-- 按照性别分组
	select gender, count(*) from students group by gender;

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

	-- 查询每种性别中最大的年龄
	select gender as 性别, max(age) as 最大年龄 from students group by gender;

	-- 查询每种性别的平均年龄，并且保存2位小数
	select gender as 性别, round(avg(age), 2) as 平均年龄 from students group by gender;

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

-- group_concat(...)
	-- 查询同种性别中的学生姓名
	select gender as 性别, group_concat(name) as 名字 from students group by gender;

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

	-- 查询性别人数超过2人得性别，并显示其名字
	select gender, group_concat(name)  from students group by gender having count(*)>2;

4.7分页查询

-- limit start【起始下标】, count【个数】 限制查询出来数据的个数      下标从0开始
 
	-- 查询前5个数据
	select * from students limit 0, 5;

	-- 查询id=(6~10)的排序  5, 5 
	select * from students limit 5, 5;

4.8子查询

-- 查询出高于平均身高的信息
select * from students where height>(select avg(height) from students);

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