数据库的种类

关系型数据库
MySQL:

优点: 免费,适合中小网站,性能非常不错

缺点: 集群，容灾稍微弱一些

Oracle:

银行、大型应用、金融级

性能不错、集群、容灾非常强、价格贵

增删改查

  • 赠: INSERT INTO (字段列表) VALUES (值列表)
  • 删: DELETE FROM 表 WHERE 条件
  • 改: UPDATE 表 SET 字段=值,字段=值,字段=值… WHERE 条件
  • 查: SELECT * FROM 表 WHERE 条件

子句

子句之间有顺序:WHERE->GROUP->ORDER->LIMIT

WHERE GROUP ORDER LIMIT
筛选 合并 排序 限制

WHERE

  • WHERE name=’blue’
  • WHERE age>18
  • WHERE age>18 AND score<60
  • WHERE cach>18 OR score>1000

ORDER

  • ORDER BY age ASC/DESC

    ASC-升序(从小到大)、DESC-降序(从大到小)

    价格(price)升序排序,如果价格相同,再按销量(sales)降序排序

    ORDER BY price ASC, sales DESC

GROUP

  • *统计每个班人数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    ID	class	name
    "1"	"1"	"小明"
    "2"	"2"	"小红"
    "3"	"1"	"小刚"
    "4"	"2"	"小华"
    "5"	"3"	"小强"
    "6"	"3"	"小四"
    "7"	"1"	"小刘"
    "8"	"1"	"小花"

    SELECT * FROM student_table;
    ID	class	name
    "1"	"1"	"小明"
    "2"	"2"	"小红"
    "3"	"1"	"小刚"
    "4"	"2"	"小华"
    "5"	"3"	"小强"
    "6"	"3"	"小四"
    "7"	"1"	"小刘"
    "8"	"1"	"小花"

    SELECT * FROM student_table GROUP BY class;
    ID	class	name
    "1"	"1"	"小明"
    "2"	"2"	"小红"
    "5"	"3"	"小强"

    SELECT class FROM student_table GROUP BY class;
    class
    "1"
    "2"
    "3"

    SELECT class,COUNT(class) FROM student_table GROUP BY class;
    class	COUNT(class)
    1	4
    2	2
    3	2

  • 统计每个班的平均分

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    SELECT * FROM student_table
    ID	class	name	score
    1	1	小明	34
    2	2	小红	98
    3	1	小刚	26
    4	2	小华	99
    5	3	小强	18
    6	3	小四	95
    7	1	小刘	57
    8	1	小花	100
    SELECT * FROM student_table GROUP BY class;
    ID	class	name	score
    1	1	小明	34
    2	2	小红	98
    5	3	小强	18
    SELECT class,AVG(score) FROM student_table GROUP BY class;
    class	score
    1	54.25
    2	98.5
    3	56.5
    每个班级的最高、最低分
    SELECT class,MAX(score),MIN(score) FROM student_table GROUP BY class;
    class	MAX	MIX
    1	80	12
    2	98	30
    3	91	67
    SELECT class,COUNT(class) FROM student_table WHERE score>60 GROUP BY class ORDER BY COUNT(class) DESC LIMIT 2
    class COUNT(class)
    3	3
    1	2

  • *每个人的消费总额

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    ID	name	price
    1	blue	3
    2	blue	5
    3	张三	28000
    4	李四	81000
    5	blue	4
    6	张三	46000
    7	李四	38000
    8	赵六	18
    SELECT name,SUM(price) FROM sales_table GROUP BY name ORDER BY SUM(price) DESC;
    name	SUM(price)
    李四	119000
    张三	74000
    赵六	18
    blue	12