文章目录
  1. 1. 基本数据查询
  2. 2. 数据更新语句
  3. 3. MySQL注释
  4. 4. others

基本数据查询

1.创建CREATE

1
CREATE TABLE tablename (field type [(size)] [NOT NULL]{, field type [(size)] [NOT NULL]...}[,PRIMARY KEY(field{,field...})]);

2.选择SELECT

1
SELECT * FROM students WHERE id < 20 AND age > 25;

3.范围BETWEEN..AND

1
SELECT * FROM students WHERE id BETWEEN 1 AND 10;

4.模糊查询LIKE

1
SELECT * FROM students WHERE name LIKE '%son';  #比如找一个名字后缀为'son'的人

LIKE通配符:

%   0或者更多字符
_   任意一个字符
[]  指定范围内的任意单个字符
[^] 不再指定范围内的任意单个字符

Example:

Like"[CK]%" 返回以 C 或者 K 开始的任意字符串
Like"[S-V]ing" 返回长为 4 个字符的字符串,结尾是"ing",开始是从 S 到 V
Like"M[^c]%" 返回以"M"开始且第二个字符不是"c"的任意字符串

5.创建昵称AS

1
SELECT name AS 姓名 FROM students ;

6.查询结果排序ORDER BY ASC升序DESC降序

1
SELECT * FROM students WHERE name LIKE '%son' ORDER BY age ASC,id DESC;#age优先排列

7.统计COUNT

1
SELECT COUNT(id) FROM students; #查询id字段的行数

8.分组GROUP BY

1
SELECT * FROM students GROUP BY age (HAVING id > 5);  #括号可略,添加附加条件

9.联合查询UNION

1
2
3
4
-- 前提是两个表字段一样--
SELECT * FROM students UNION SELECT * FROM students_bak; #合并两个表的查询记录,Veen图的合并
-- 默认记录不重复,不过可以使用UNION ALL显示所有记录
SELECT * FROM students UNION ALL SELECT * FROM students_bak;

10.子查询/嵌套查询

1
2
-- 该查询ORDER BY无效--
SELECT * FROM students WHERE name = (SELECT age FROM WHERE age = 20);

例1
例2

数据更新语句

  1. INSERT
    –> VALUES结构(插入单条记录)

    1
    2
    3
    4
    5
    6
    7
    INSERT INTO tablename [(colname{,colname...})]{VALUES (expr|NULL{,expr|NULL....})|Subquery}
    -- Example:
    INSERT INTO school.stuents('s_name','s_age')
    VALUES ('xiaobai','20');
    -- OR
    INSERT INTO school.students('s_id','s_name','s_age')
    VALUES (NULL, 'laoli', '10');

    –>子查询Subquery(可插入多条记录)

    1
    2
    3
    INSERT INTO newStudens
    SELECT * FROM students WHERE name = 'laowang';
    -- 往newStudents表中插入students的一个字段(改为age就能插入多个了..)
  2. UPDATE
    –> grammar

    1
    2
    3
    4
    5
    6
    7
    8
    UPDATE tablename
    SET colname= {expr|NULL|(subquery)}
    {, colname= {expr|NULL|(subquery).....}}
    [WH ERE search_condition] -- 子查询
    -- ->Example:
    UPDATE students
    SET age = age + 10
    WHERE name = 'doubi';
  3. DELETE

    1
    2
    3
    4
    5
    DELETE FROM tablename
    [WHERE search_condition]
    -- ->Example:
    DELETE FROM students
    WHERE neme = 'doubi';

MySQL注释

  1. #...
  2. -- .. 有个空格
  3. /*...*/

others

不等于 <>

文章目录
  1. 1. 基本数据查询
  2. 2. 数据更新语句
  3. 3. MySQL注释
  4. 4. others