mysql命令速查手册

数据准备

-- 创建数据库
create database qianduan_test charset=utf8;

-- 使用数据库
use qianduan_test;

-- 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);

-- 向classes表中插入数据
insert into classes values (0, "qianduan_01期"), (0, "qianduan_02期");

--01 mysql 数据库的操作

    -- 链接数据库
    mysql -uroot -p   回车
 
    
    -- 退出数据库
    --quit/exit

    

    -- sql语句最后需要有分号;结尾
    -- 显示数据库版本 version
    -- select version();
    
    
    
    -- 查看当前使用的数据库
    -- select database();
    
    

    -- 查看所有数据库
    show databases;

    

    -- 创建数据库
    -- create database 数据库名 charset=utf8;
    create database qd22 charset=utf8;
    

    -- 查看创建数据库的语句
    -- show create database 数据库名
    show create database qd22;
     

    -- 使用数据库
    -- use 数据库的名字
    use qd22;
    

    -- 删除数据库
    -- drop database 数据库名;
    drop database test;
    


--02 数据表的操作

    -- 查看当前数据库中所有表
    show tables;
    

    -- 创建表
    -- int unsigned 无符号整形
    -- auto_increment 表示自动增长跟主键在一起
    -- not null 表示不能为空
    -- primary key 表示主键
    -- default 默认值
    -- create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);     可选
    create table test(name varchar(30) not null, age int unsigned);


    

    -- 查看表结构(查看表的字段信息)
    -- desc 数据表的名字;
    desc test;

    
   
    -- 创建 classes 表(有id、name两个字段)
    create table classes(id int unsigned primary key auto_increment, name varchar(30) not null);

    
    
    
    -- 创建 students 表(有id、name、age、high (decimal)、gender (enum)、cls_id这些字段)
    create table students(
        id int unsigned primary key auto_increment, 
        name varchar(30) not null, 
        age int unsigned, 
        high decimal(3, 2), 
        gender enum("男", "女", "保密", "中性") default "保密", 
        cls_id int unsigned
    );


    -- 查看表的创建语句
    -- show create table 表名字;
    show create table students;
    


    -- 修改表-添加字段 mascot (吉祥物)
    -- alter table 表名 add 列名 类型;   给classes表添加mascot字段
    alter table classes add mascot varchar(50);

   

    -- 修改表-修改字段：不重命名版
    -- alter table 表名 modify 列名 类型及约束;
    alter table classes modify mascot varchar(100);
   
    


    -- 修改表-修改字段：重命名版
    -- alter table 表名 change 原名 新名 类型及约束;
    alter table classes change mascot jxw int unsigned;
    


    -- 修改表-删除字段
    -- alter table 表名 drop 列名;
    alter table classes drop jxw;
    


    -- 删除表
    -- drop table 表名;
    -- drop database 数据库;
    drop table test;
   

    
--03 增删改查(curd)

    -- 增加
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
        -- 全列插入
        -- insert into 表名(字段1,字段2) values(值1，值2);
        -- 主键字段 可以用 0  null   default 来占位
        -- 向classes表中插入 一个班级

        insert into classes(id, name) values(1, "qd22");
        insert into classes(id, name) values(3, "qd23");
        insert into classes(id, name) values(0, "qd24");
        insert into classes(name) values("qd25");


        -- 向students表插入 一个学生信息
+--------+-------------------------------------+------+-----+---------+----------------+
| Field  | Type                                | Null | Key | Default | Extra          |
+--------+-------------------------------------+------+-----+---------+----------------+
| id     | int(10) unsigned                    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)                         | NO   |     | NULL    |                |
| age    | int(10) unsigned                    | YES  |     | NULL    |                |
| high   | decimal(3,2)                        | YES  |     | NULL    |                |
| gender | enum('男','女','中性','保密')       | YES  |     | 保密    |                |
| cls_id | int(11)                             | YES  |     | NULL    |                |
+--------+-------------------------------------+------+-----+---------+----------------+
        -- 全部插入
        -- insert into 表名 values(值1,值2,值3...)
        insert into students values(1, "张三", 18, 1.83, '男', 1);

        -- 部分插入
        -- insert into 表名(列1,...) values(值1,...)
        insert into students(name) values("李四");
        insert into students(name,age) values("王五", 20);

        -- 多行插入
        -- insert into 表名(列1) values(值), (值);
        insert into students(name) values("赵六"),("李七");


    -- 修改
    -- update 表名 set 列1=值1,列2=值2... where 条件;
        -- 全部修改
        update students set age = 16;

        
        -- 按条件修改
        update students set age = 20 where id=3;
        update students set age = 18 where name="赵六";
        
        
        -- 按条件修改多个值
        -- update students set gender ="",age = "" where name="xxx";
        update students set age = 15, high=1.80 where id=2;
        
        
    -- 查询基本使用
        -- 查询所有列
        -- select * from 表名;
        -- 查询students表中的所有数据信息
        select * from students;

        ---定条件查询  
        --查询id为1的学生所有信息  
        select * from students where id = 1;
        
        
        -- 查询指定列
        -- select 列1,列2,... from 表名;
        select name, age from students;
        select name, age from students where id = 1;
        
    
        -- 可以使用as为列或表指定别名
        -- select 字段[as 别名] , 字段[as 别名] from 数据表;
        select name as "姓名", age as "年龄" from students;
        select name as n, age as a from students;
        

        -- 字段的顺序
        -- select age, name from students;
        select age, name from students;

    -- 删除
        -- 物理删除
        -- delete from 表名 where 条件
        delete from students where id = 4;
        
      

        -- 逻辑删除
        -- 用一个字段来表示 这条信息是否已经不能再使用了
        -- 给students表添加一个 is_delete 字段 bit 类型
        --alter table 表名 add 字段 类型 default 默认值;
        alter table students add is_delete bit default 0;
        --update 表名 set is_delete=1 where id=;
        update students set is_delete=1 where id=1;
        
 

--数据库操作前的准备

-- 查询练习
	-- 查询所有字段
	-- select * from 表名;
	 select * from students;
 


	-- 查询指定字段
	-- select 列1,列2,... from 表名;
	select name, age from students;

	
	-- 使用 as 给字段起别名
	-- select 字段 as 名字.... from 表名;
	select name as n, age as a 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 gender from students;
	select distinct gender from students;   --  使用distinct关键字的做法清除重复的记录
	select gender from students group by gender;   --  使用分组的做法清除重复的记录


-- 条件查询
	-- 比较运算符
		-- 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;
		select * from students where age <> 18;
		

	-- 逻辑运算符
		-- and  /  between .. and ..
		-- 18和28之间的所有学生信息
		select * from students where age > 18 and age < 28;  --不包含18 和28
		select * from students where age >= 18 and age <= 28;  --包含18 和28

		select * from students where age between 18 and 28;  --包含18 和28


		-- 18岁以上的女性的学生信息
		select * from students where age > 18 and gender="女";
		select * from students where age > 18 and gender=2;   --枚举的数据类型可以用数字来表示，数字从1开始


		-- or
		-- 18以上或者身高高过180(包含)以上的学生信息
		select * from students where age > 18 or height >= 180.00;
		

		-- not
		-- 不在 18岁以上的女性 这个范围内的信息
		-- select * from students where not (age>18 and gender=2);
		select * from students where not (age > 18 and gender=2); 


	-- 模糊查询(where name like 要查询的数据)
		-- like 
		-- % 替换任意个
		-- _ 替换1个
		-- 查询姓名中 以 "小" 开始的名字的学生信息
		select * from students where 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 * from students where age = 18 or age = 34;
		select * from students where age in (18, 34);  -- (18, 34)不是区间，是确切的值

		

		-- 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; 
		
		

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

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


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


-- 排序
	-- order by 字段
	-- asc
	-- asc从小到大排列，即升序
	-- desc
	-- desc从大到小排序，即降序
	-- 查询年龄在18到34岁之间的男性，按照年龄从小到大到排序

	select * from students where gender=1 and age between 18 and 34 order by age asc;

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

	-- order by 多个字段
	-- 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序
	
	select * from students where gender=2 and age between 18 and 34 order by height desc, age asc;

	
    -- 如果年龄也相同那么按照id从大到小排序
    select * from students where gender=2 and age between 18 and 34 order by height desc, age asc, id desc;


-- 聚合函数
	-- 总数
	-- count
	-- 查询男性有多少人

	select count(*) from students where gender = 1;
	
	-- 最大值
	-- max
	-- 查询最大的年龄
	select max(age) from students;
	
	
	-- 查询女性的最高 身高
	select max(height) from students where gender = 2; 
	


	-- 最小值
	-- min
	select min(height) from students where gender = 2; 
	

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

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


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


	-- 四舍五入 round(123.23 , 1) 保留1位小数
	-- 计算所有人的平均年龄，保留2位小数
	select round(avg(age), 2) from students;
	


	-- 计算男性的平均身高 保留2位小数
	select round(avg(height), 2) from students where gender = 1;   -- 没有算null这种情况  /4
	select round(sum(height)/count(*), 2) from students where gender = 1;   -- 把null算进去了    /5


	# 聚合函数计算的时候不会把null计算进去
	
-- 分组

	-- 分组的口诀
	-- select 分组的字段  from 表名 group by 分组字段;

	-- group by
	-- 按照性别分组,查询所有的性别
	select gender from students group by gender;



	--# 每种、每类是分组的关键字眼， 以什么为分组，最好在展示的时候，把这个字段放在第一列
	-- 计算每种性别中的人数
	select gender, count(*) from students group by gender;



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


	
	-- 查询每组性别的平均年龄
	select gender, avg(age) from students group by gender;


	-- having(注意having和group by 连用 having后通常也要跟 聚合函数)
	-- 注意： 聚合函数如果做为条件出现，只能和having配合。不能和where配合使用。
	-- 查询平均年龄超过30岁的性别，以及姓名
	select gender, avg(age), group_concat(name) from students group by gender having avg(age) > 30;
	

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


-- 分页
	-- limit start, count
	-- limit 放在最后面(注意)
	-- 公式：limit (要显示第几页-1) * 每页分多少个, 每页分多少个;
	-- 限制查询出来的数据个数
	-- 查询前5个数据
	select * from students limit 5;

	-- 每页分2个，要显示第1页  (查询前2个数据) 
	select * from students limit 2;

	--通用写法：
	select * from students limit 0, 2;

	-- 每页分2个，要显示第2页
	select * from students limit 2, 2;
	
	-- 每页分2个，要显示第3页
	select * from students limit 4, 2;

	-- 每页分2个，要显示第4页
	select * from students limit 6, 2;

	-- 每页分2个，显示第6页的信息, 按照年龄从小到大排序
	-- 先整体排序，再进行分页！
	select * from students order by age asc limit 10, 2;
	

	
	
	 
-- select * from students where 
-- select 分组 from students group by 分组字段 having 条件查询
-- select * from 表a inner join 表b on 连接的条件

-- 连接查询   (表与表之间的链接， 为了更好的查出有效数据)
	-- inner join ... on
	-- select ... from 表A inner join 表B;
	-- 查询 有能够对应班级的学生以及班级信息
	select * from studetns 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.name as "姓名", c.name as "所在班级" from students as s inner join classes as c on s.cls_id=c.id;


	-- 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息 students.*，只显示班级名称 classes.name.

	select s.*, c.name from students as s inner join classes as c on s.cls_id = c.id;
	

	-- 在以上的查询中，将班级名显示在第1列
	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 c.name;

	
	
	-- 当时同一个班级的时候，按照学生的id进行从小到大排序
	select c.name,s.* from students as s inner join classes as c on s.cls_id = c.id order by c.name, s.id asc;


	

-- 子查询  (一个查询的结果作为另一个查询的一部分， 会把前者的查询称为子查询)
	-- 标量子查询: 子查询返回的结果是一个数据(一行一列)
	-- 列子查询: 返回的结果是一列(一列多行)
	-- 行子查询: 返回的结果是一行(一行多列)
	
	-- 查询出高于平均身高的信息(height)

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

	



	-- 查询学生的班级号能够对应的 学生名字
	-- select * from students where cls_id in(1,2);
	-- select id from classes;

	select students.name from students where cls_id = 1 or cls_id = 2;
	select students.name from students where cls_id in (1, 2);
	select students.name from students where cls_id in (select id from classes);