2. -- DDL
    4. USE db1;
    6. SHOW TABLES;
    8. # 添加字段 
    9. ALTER TABLE students ADD brithday DATETIME DEFAULT "2020-07-21";
    10. ALTER TABLE students ADD id_2 INT;
    12. # 修改字段
    13. ALTER TABLE students MODIFY brithday DATE DEFAULT "2020-07-21";
    15. # 修改字段名
    16. ALTER TABLE students CHANGE brithday birth DATE DEFAULT "2020-07-21";
    18. # 删除字段
    19. ALTER TABLE students DROP id_2;
    22. -- DML 表数据操作
    24. # 添加整行数据
    25. INSERT INTO students VALUE(10, "lily", 20, "2000-1-1");
    27. # 同时添加多行数据
    28. INSERT INTO  students(NAME, age, birth, gender) VALUES("lucy", 21, "1999-01-01", "male"),
    29. ("jerry", 25, "1995-01-01", "male"),
    30. ("ermao", 10, "2010-01-01", "female");
    32. # 添加name，gender两个字段的数据
    33. ALTER TABLE students ADD  gender ENUM("male","female");
    35. # 将姓名为jacky全部修改为jack
    36. UPDATE students SET NAME="jack" WHERE NAME="jacky";
    38. # 将性别为女的名字修改为rose
    39. UPDATE students SET NAME="rose" WHERE gender="female";
    42. # 将姓名为jack的数据 物理删除
    43. DELETE FROM students WHERE NAME="jack";
    46. -- 查询语句
    47. # 查询Student表所有数据
    48. SELECT * FROM students;
    50. # 询Student表中name与gender字段的数据
    51. SELECT NAME, gender FROM students;
    53. # 查询Student表中name字段的数据并且去重
    54. SELECT DISTINCT NAME FROM students;
    56. -- 比较运算: =  >  >=  <  <=  <> !=
    57. # 查询id大于11的数据
    58. SELECT * FROM students WHERE id>11;
    60. # 查询年龄大于18岁的信息
    61. SELECT * FROM students WHERE age>18;
    63. # 查询姓名不是lily的数据
    64. SELECT * FROM students WHERE NAME!="lily";
    67. -- 逻辑运算： and or not
    68. # 查询18~22之间的所有学生信息
    69. SELECT * FROM students WHERE age>=18 AND age<=22;
    71. # 查询id大于3的女同学
    72. SELECT * FROM students WHERE id>3 AND gender="female";
    74. # 查询id小于12或者id大于13的学生信息
    75. SELECT * FROM students WHERE id<12 OR id>13
    77. # 查询年龄不是18的女同学
    78. SELECT * FROM students WHERE (NOT age=18) AND gender="female";
    81. -- like % _
    82. # 查询名字以l开始的学生信息
    83. SELECT * FROM students WHERE NAME LIKE("l%");
    85. # 查询名字含有i的学生信息
    86. SELECT * FROM students WHERE NAME LIKE("%i%");
    88. # 查询名字仅有2个字符的学生信息
    89. INSERT INTO students(NAME, age) VALUES("wb", 11);
    90. SELECT * FROM students WHERE NAME LIKE("__");
    92. # 查询名字至少有2个字符的学生信息
    93. INSERT INTO students(NAME, age) VALUE("o", 111);
    94. SELECT * FROM students WHERE NAME LIKE("__%");
    97. -- in 和 between and
    98. # 查询id是1或者11或者13的学生信息
    99. SELECT * FROM students WHERE id IN(1,11,13);
    101. # 查询年龄不是18,20的学生信息
    102. SELECT * FROM students WHERE age NOT IN(18,20);
    104. # 查询id是2至4的学生信息
    105. SELECT * FROM students WHERE id BETWEEN 2 AND 4;
    108. # 查询id是3-5的男同学信息
    109. SELECT * FROM students WHERE (id BETWEEN 10 AND 15) AND gender="male";
    112. # 查询年龄不在18至20之间的学生信息
    113. SELECT * FROM students WHERE age NOT BETWEEN 18 AND 20;
    115. -- is null 和 is not null
    117. # 查询没有填写性别的学生
    118. SELECT * FROM students WHERE gender IS NULL;
    119. SELECT * FROM students WHERE gender IS NOT NULL;
    122. -- 聚合函数
    123. # 查询最大的年龄
    124. SELECT MAX(age) AS "最大年龄" FROM students;
    127. # 女性最大的id
    128. SELECT MAX(id) FROM students WHERE gender="female";
    131. # 查询未删除的学生的最小编号
    132. ALTER TABLE students DROP is_delete;
    133. ALTER TABLE students ADD is_delete ENUM ("0","1") DEFAULT "1";
    134. SELECT MIN(id) FROM students WHERE is_delete="0";
    136. # 查询男生年龄和
    137. SELECT SUM(age) FROM students WHERE gender="male";
    140. # 查询未删除女生的年龄的平均值 保留一位小数
    141. SELECT ROUND(AVG(age), 1) FROM students WHERE gender="female"  AND is_delete="1";
    144. # 计算男性的平均年龄，保留2位小数
    145. SELECT ROUND(AVG(age), 2) FROM students WHERE gender="female";