sql数据库

-- 一、数据库的操作

    -- 1、连接数据库------------------------------------

    mysql -h127.0.0.1 -P3306 -uroot -proot  -- 明文

    -- 如果连接本地数据库 -h可以省略 如果服务器端口是3306，-P端口号也可以省略

    mysql -uroot -proot	-- 明文

    -- 密文
    mysql -uroot -p  
    Enter password: ****



    -- 2、退出数据库------------------------------------
   
    EXIT    --方法一
    
    quit    --方法二
    
    q       --方法三
    
    ctrl+c  --方法四

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



    -- 3、数据库其他命令

    select version();   -- 显示数据库版本

     select now();      -- 显示时间

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



    -- 4、创建数据库----------------------------------
    
    -- 语法：create database 数据库名 charset=utf8;

    create database Threekingdomes;
    create database Threekingdomes charset=utf8;



    -- 5、查看创建数据库的语句------------------------

    -- 语法：show crate database ....

    show create database Threekingdomes;


    --6、查看当前使用的数据库-------------------------
    
    select database();


   -- 8、使用数据库-----------------------------------

    -- 语法： use 数据库的名字

    use Threekingdomes;


    -- 9、删除数据库----------------------------------
        
    -- drop database 数据库名;

    drop database Threekingdomes;


    -------------------------END----------------------------


-- 二、数据表的操作

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

    show tables;




    -- 2、创建表----------------------------------

    -- auto_increment表示自动增长
    -- not null 表示不能为空
    -- primary key 表示主键
    -- default 默认值

    -- 语法：create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);

    create table class(id  int, us_name varchar(30));

    -- 加约束
    create table class2(id  int primary key not null auto_increment, us_name varchar(30));

    -- 换行操作
       create table class3(
           id, int primary key not null auto_increment, 
           us_name varchar(30)
           );




    -- 3、查看表结构-----------------------------

    -- desc 数据表的名字; 

    desc class;



    -- 4、创建class4表(id、name、age、high、gender、cls_id)-----------------------------

    create table class4(
        id int unsigned primary key not null auto_increment,
        name varchar(30),
        age tinyint unsigned ,
        high decimal(5,2),
        gerder enum("男","女","保密") DEFAULT "保密",
        cls_id int unsigned
    );

    insert into class4 VALUES(0,"小明",18,188.88,"男",1);



    -- 5、查看表的创建语句-----------------------------

    -- 语法：show create table 表名字;

        show create table students;



    -- 6、修改表-添加字段-----------------------------

    -- 语法：alter table 表名 add 列名 类型;

        alter table class4 add birthday datetime;



    -- 7、修改表-修改字段：不重命名版-----------------------------

    -- 语法：alter table 表名 modify 列名 类型及约束;

        alter table class4 modify birthday date



    -- 8、修改表-修改字段：重命名版-----------------------------

    -- 语法：alter table 表名 change 原名 新名 类型及约束;

        alter table class4 change birthday birth date default "2000-01-01";



    -- 9、修改表-删除字段-----------------------------

    -- 语法：alter table 表名 drop 列名;

        alter table class4 drop high;



    -- 10、删除表----------------------------

    -- 语法：drop table 表名;
    -- 语法：drop database 数据库;
    -- 语法：drop table 数据表;

         drop table xxxxx;

----------------------------END------------------------------





-- 三、增删改查(curd)

    -- 1、 增加-----------------------------

        -- 全列插入
        -- 语法：insert [into] 表名 values(...)
        -- 主键字段 可以用 0  null   default 来占位
        -- 向classes表中插入 一个班级

        insert into classes values(0, "菜鸟班");


        +--------+-------------------------------------+------+-----+------------+----------------+
        | Field  | Type                                | Null | Key | Default    | Extra          |
        +--------+-------------------------------------+------+-----+------------+----------------+
        | id     | int(10) unsigned                    | NO   | PRI | NULL       | auto_increment |
        | name   | varchar(30)                         | YES  |     | NULL       |                |
        | age    | tinyint(3) unsigned                 | YES  |     | 0          |                |
        | gender | enum('男','女','中性','保密')       | YES  |     | 保密       |                |
        | cls_id | int(10) unsigned                    | YES  |     | NULL       |                |
        | birth  | date                                | YES  |     | 2000-01-01 |                |
        +--------+-------------------------------------+------+-----+------------+----------------+



    -- 1.1、向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");

        -- 枚举中 的 下标从1 开始 1---“男” 2--->"女"....-----------------------------

        insert into students values(default, "小李飞刀", 20, 1, 1, "1990-02-01");
    

        -- 部分插入-----------------------------

        -- insert into 表名(列1,...) values(值1,...)
        insert into students (name, gender) values ("小乔", 2);
    

         -- 多行插入-----------------------------

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



    -- 2、修改。-----------------------------
    -- 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=3; -- 只要id为3的 进行修改



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

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



-- 4、查询。-----------------------------

    -- 4.1、简单查询-------------------------

    -- 4.1.1  查询所有字段
    -- 语法：SELECT 字段名1，字段名2，…FROM 表名

    -- 任务：查询student2表中的所有记录
    --方法一
        SELECT id,name,grade ,gender  FROM student; 
    --方法二
        select * from student;


    -- 4.1.2  查询指定的部分字段-------------

    -- 语法：SELECT 字段名1，字段名2，… FROM 表名；

    -- 任务：查询student表中的name字段和gender字段
        SELECT name,gender FROM student;

    -- 注意：字段顺序可以更改（则显示的结果也会作出对应的调整。）如：
        SELECT id,grade,gender ,name FROM student;




    -- 4.2、按条件查询------------------------

    -- 4.2.1  带关系运算符的查询

    -- 语法：SELECT 字段名1，字段名2，…FROM 表名 WHERE 条件表达式
    -- 在WHERE子句中可以使用如下关系运算符：

    -- 任务：查询student表中id为4的人的id和name字段

        SELECT id,name FROM student2  WHERE id=4;



    -- 4.2.2  带 IN 关键字的查询

    -- IN关键字用于判断某个字段的值是否在指定集合中，若在，则该字段所在的记录将会被查询出来.
    -- 语法：SELECT * 字段名1，字段名2，…FROM 表名 WHERE 字段名 [ NOT ]  IN （元素1，元素2，…）

    -- 任务：查询student表中id值为1,2,3的记录
        SELECT * FROM student2 WHERE  id IN (1,2,3);


    -- 4.2.3  带 BETWEEN AND  关键字的查询

    -- BETWEEN AND 用于判断某个字段的值是否在指定范围之内，若在，则该字段所在的记录会被查询出来，反之不会。
    -- 语法：BETWEEN AND 用于判断某个字段的值是否在指定范围之内，若在，则该字段所在的记录会被查询出来，反之不会。

    -- 任务：查询student表中id值在2~5之间的人的id和name
        SELECT id,name FROM student WHERE id BETWEEN 2 AND 5;


    -- 4.2.4  空值查询

    -- 在数据表中有些值可能为空值（NULL），空值不同于0，也不同于空字符串，需要使用 IS NULL 来判断字段的值是否为空值。
    -- 语法：SELECT *  字段名1，字段名2，…FROM 表名 WHERE 字段名 IS [ NOT ] NULL

    -- 任务：查询student表中gender值为空值的记录。
    -- 注意：IS NOT NULL 关键字用来查询字段不为空值的记录。
        SELECT * FROM student WHERE gender IS NULL;


    -- 4.2.5  带 DISTINCT 关键字的查询

    -- 很多表中某些字段的数据存在重复的值，可以使用DISTINCT关键字来过滤重复的值，只保留一个值。
    -- 语法：SELECT DISTINCT 字段名 FROM 表名；

    -- 任务：查询student2表中gender字段的值，结果中不允许出行重复的值。
        SELECT DISTINCT gender FROM student2;


    -- 4.2.6  带 LIKE 关键字的查询

    -- 语法：SELECT * | 字段名1，字段名2，…FROM 表名 WHERE 字段名 [ NOT ] LIKE ‘匹配字符串’;

    -- （1）百分号（%）通配符
    -- 匹配任意长度的字符串，包括空字符串。例如，字符串“ l% ”匹配以字符 l 开始，任意长度的字符串，如“ ct  ”，“ cut ”，“ current ”等；字符串“ c%g ”表示以字符 c 开始，以 g 结尾的字符串；字符串“ %y% ”表示包含字符“ y ”的字符串，无论“ y ”在字符串的什么位置。
    -- 任务：查询student2表中name字段以字符“ s ”开头的人的id,name
        SELECT id,name FROM student2  WHERE name LIKE "l%"; 


    -- （2）下划线（_）通配符
    -- 下划线通配符只匹配单个字符，若要匹配多个字符，需要使用多个下划线通配符。例如，字符串“ cu_ ”匹配以字符串“ cu ”开始，长度为3的字符，如“ cut ”，“ cup ”；字符串“ c_ _l”匹配在“ c ”和“ l ”之间包含两个字符的字符串，如“ cool ”。需要注意的是，连续的“_”之间不能有空格，例如“M_ _QL”只能匹配“MySQL”，不能匹配“My SQL”。
    -- 任务：查询在student2表中name字段值以“ wu ”开始，以“ ong ”结束，并且中间只有一个字符的记录。
        SELECT * FROM student2 WHERE name LIKE 'wu_ong';

    --注意：若要查询的字段值本来就含有“ % ”或者“ _ ”,则要用“ \ ”进行转义，如要查询本身含有“ % ”的字符串，命令应改为 “ %\%%”。



    -- 4.2.7  带 AND 关键字的多条件查询

    -- 在使用SELECT语句查询数据时，优势为了使查询结果更加精确，可以使用多个查询条件，如使用 AND 关键字可以连接两个或多个查询条件。
    -- 语法：SELECT * | 字段名1，字段名2，…FROM 表名 WHERE 条件表达式1 AND 条件表达式2 [ … AND 条件表达式 n ];

    -- 任务：查询student2表中 id 字段小于5，并且 gender 字段值为“ 女 ”的人的id和name
        SELECT id,name FROM student WHERE id<5 AND gender='女';


   -- 4.2.8  带 OR 关键字的多条件查询

    -- 与 AND 关键字不同，OR 关键字只要满足任意一个条件就会被查询出来
    -- 语法：SELECT * | 字段名1，字段名2，…FROM 表名 WHERE 条件表达式1 OR 条件表达式2 [ … OR 条件表达式 n ];

    -- 任务：查询student2表中 id 字段小于5，并且 gender 字段值为“ 女 ”的人的id和name
        SELECT id,name ,gender FROM student2 WHERE id<3 OR gender='女';


 -- 4.2.9   OR 和 AND 一起使用的情况

    -- OR 和 AND 一起使用的时候，AND 的优先级高于 OR，因此二者一起使用时，会先运算 AND 两边的表达式，再运算 OR 两边的表达式。
   
    -- 任务：查询student2表中gender值为“女”或者gender值为“男”并且grade字段值为100的人的记录
        SELECT * FROM student2 WHERE gender='女' OR gender='男'  AND grade=100;



-- 聚合函数
    /* 
    MAX() MIN() AVG() SUM（） count()

    步骤一：创建一个数据库
    步骤二：创建一个表
    步骤三：表中插入多个值

    */
    USE class; -- 进入 class 库中

    CREATE TABLE students(usid INT(4),age INT(4),username VARCHAR(25)); -- 创建一个 students 的表。

    INSERT INTO students(usid,age,username,gender) VALUES (1,30,'刘备','男');
    INSERT INTO students(usid,age,username,gender) VALUES (2,30,'孙尚香','女');
    INSERT INTO students(usid,age,username,gender) VALUES (3,34,'吕布','男');
    INSERT INTO students(usid,age,username,gender) VALUES (4,34,'貂蝉','女');
    INSERT INTO students(usid,age,username,gender) VALUES (5,38,'周瑜','男');
    INSERT INTO students(usid,age,username,gender) VALUES (6,38,'小乔','女');

    -- MAX()可以求出同一列中最大的值

    SELECT MAX(age) FROM students;
    SELECT * FROM students;

    -- MIN()可以求出同一列中最小的值

    SELECT MIN(age) FROM students;
    SELECT * FROM students;

    -- AVG()可以求出同一列中平均数；

    SELECT AVG(age) FROM students;


    -- SUM()可以求出同一列中和；

    SELECT SUM(age) FROM students;

    -- COUNT()可以统计当前表中一共有多少条数据；

    SELECT COUNT(*) FROM students;
    SELECT COUNT(1) FROM students;
    SELECT COUNT(100) FROM students;


    # 注意：我们的聚合函数不对 null 进行操作。

    INSERT INTO students(usid,username) VALUES (7,'周瑜');

    SELECT COUNT(IFNULL(age,0)) FROM students;

    /*
    对查询结果进行排序

    order by:

    语法：SELECT 字段名1，字段名2，…FROM 表名
    ORDER BY 字段名1 [ ASC | DESC ],字段名2 [ ASC(升序) | DESC（降序） ]…

    默认是升序
    */
    -- 查询表中所有的人物信息，按照年龄排序；
    SELECT * FROM students ORDER BY age;
    SELECT * FROM students;
-------------------------------------------------------------------------------------
-- 分组查询

在对表中数据进行统计的时候，可以使用GROUP BY 
按某个字段或者多个字段进行分组，字段中值相同的为一组，
如男生分为一组，女生分为一组。

语法：SELECT  字段名1，字段名2，…FROM 表名
GROUP BY 字段名1，字段名2，… [ HAVING 条件表达式 ];

*/

/*
（1）单独使用 GROUP BY 进行分组

单独使用GROUP BY 关键字，查询的是每个分组中的一条记录
*/
-- 查询student表中的数据，按照gender字段进行分组。

SELECT * FROM students GROUP BY gender; -- 查询的是每个分组中的一条记录
SELECT * FROM students;

/*
（2）GROUP BY 和聚合函数一起使用

GROUP BY 和聚合函数一起使用，
可以统计出某个或者某些字段在一个分组中的最大值、最小值、平均值等。

*/
-- 将students表按照gender字段进行分组查询，计算出每组共有多少个人。

SELECT gender FROM students;  -- 查询 students 表中 gender 字段所有的值
SELECT gender FROM students GROUP BY gender; -- 给 gender 分组
SELECT COUNT(*),gender FROM students GROUP BY gender;-- 计算出每组共有多少个人


/*
（3）GROUP BY 和 HAVING 关键字一起使用

HAVING关键字和WHERE关键字的作用相同，区别在于HAVING 关键字可以跟聚合函数，
而WHERE 关键字不能。通常HAVING 关键字都和GROUP BY一起使用，用于对分组后的结果进行过滤。

*/
-- 将students表按照age字段进行分组查询，查询出 age 字段值之和小于300的分组

SELECT age FROM students; -- 查询 表中 students 所有的 age
SELECT SUM(age) FROM students; -- age 字段值之和  
SELECT SUM(age),age FROM students GROUP BY age; -- 查询 students 表中 age 字段每组之和
SELECT SUM(age),age FROM students GROUP BY age HAVING SUM(age) < 300;-- 查询出grade字段值之和小于300的分组
SELECT * FROM students;
--------------------------------------------------------------------------------------------
/*
使用 LIMIT（限制） 限制查询结果的数量

语法：SELECT 字段名1，字段名2，…FROM 表名
LIMIT 记录数 [ OFFSET ] 记录数   LIMIT Y OFFSET X 向后偏移 x位，

在此语法中，LIMIT 后面可以跟两个参数，第一个参数“ OFFSET ”表示偏移量，如果偏移量为0，
则从查询结果的第一条记录开始，偏移量为1则从查询结果中的第二条记录开始，以此类推。
OFFSET为可选值，默认值为0，第二个参数“记录数”表示指定返回查询记录的条数。

*/
-- 查询 students 表中的前四条记录。
SELECT * FROM students;
SELECT * FROM students LIMIT 4;

-- 查询students表中age字段 第2位后面第3位人的数据（从高到低），后面的1代表取一条数据。
SELECT * FROM students ORDER BY age DESC; -- 以 age字段 为条件降序排列
SELECT * FROM students ORDER BY age DESC  LIMIT 2,1;
/*
为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。
经常用到在数据库中查询中间几条数据的需求
比如下面的sql语句：
selete * from testtable limit 2,1;
selete * from testtable limit 2 offset 1;
注意：
1.数据库数据计算是从0开始的
2.offset X是跳过X个数据，limit Y是选取Y个数据
3.limit  X,Y  中X表示跳过X个数据，读取Y个数据
这两个都是能完成需要，但是他们之间是有区别的：
（1）是从数据库中第三条开始查询，取一条数据，即第三条数据读取，一二条跳过
（2）是从数据库中的第二条数据开始查询两条数据，即第二条和第三条。
*/
---------------------------------------------------------------------------------------------
/*
为表和字段取别名

为表取别名

在进行查询操作时，如果表名很长使用起来不方便，可以为表取一个别名来代替表的名称。

语法：SELECT * FROM 表名 [ AS ] 别名；

*/
-- 为students表起一个别名s，并查询students表中gender字段值为“女”的记录
SELECT * FROM students AS s WHERE s.gender='女';
-------------------------------------------------------------------------------------------
/*
为字段取别名

语法：SELECT 字段名 [ AS ] 别名 [ ,字段名 [AS] 别名，…]  FROM 表名 ；

*/
-- 查询students表中的所有记录的username和gender字段值,并为这两个字段起别名stu_username和stu_gender
SELECT username AS stu_username,gender AS stu_gender FROM students;
SELECT * FROM students;
-------------------------------------------------------------------------------------------
/*
子查询

*/
SELECT * FROM students;
SELECT username FROM students WHERE usid < (SELECT age FROM students WHERE username='小乔');
-- 语句拆解
SELECT age FROM students WHERE username='小乔';-- 查询 students表中 小乔的年龄
SELECT username FROM students WHERE usid < 40; -- 查询 students表中 字段usid 小于40 的人物名称。
-------------------------------------------------------------------------------------------
/*
联合查询(union)

*/
-- 查询数据库版本和数据库用户
SELECT VERSION() UNION SELECT USER();

-------------------------------------------------------------------------------------------
/*
嵌套查询

*/
-- 
SELECT * FROM students;
SELECT COUNT(*) FROM students WHERE usid=(SELECT usid FROM students WHERE usid=1 AND username=(SELECT username FROM students WHERE address='China' LIMIT 1));
-- 语句拆解
SELECT username FROM students WHERE address='China'  LIMIT 1; -- 查询 students 表中 地址为 'China'的人名称。
SELECT usid FROM students WHERE usid=1 AND username=(SELECT username FROM students WHERE address='China' LIMIT 1);

-------------------------------------------------------------------------------------------
/*
嵌套查询

*/

-- 查询数据库：
SELECT schema_name FROM information_schema.schemata;

-- 查询表名：
SELECT table_name FROM information_schema.tables;
-- 查询列名：
SELECT column_name FROM information_schema.columns;
-- 查询数据：
SELECT * FROM students;

另一种：

SELECT table_name FROM mysql.innodb_table_stats WHERE database_name = DATABASE();

SELECT table_name FROM mysql.innodb_index_stats WHERE database_name = DATABASE();

-------------------------------------------------------------------------------------------