SQL语句,增删改查

2019-10-27 0 条评论 457 次阅读 0 人点赞

SQL语言全称,结构化查询语言,是数据库通用的数据库语言,各个数据库厂商基于SQL标准开发了各自的SQL语言,他们的语法既有共同之处,也各自在一些小的地方有各自不同的之处,这篇文章主要介绍了MySQL/MariaDB使用的SQL语言,且在该文章中由于本人脑子有炮,所以在没有特别申明的情况下MySQL和MariaDB会不加区分的使用

语言规范

不区分大小写
字符串可能会区分大小写
可以单行也可以多行,
以分号结束
关键词不能跨行写
可以使用缩进提高阅读性

注释

/ contents/:多行注释
-- 注释内容:单行注释--后面有空格
# MySQL中的注释

数据库对象命名规则

必须以字母开头
可以包括数字和三个特殊符号,# _ $
不要使用MySQL的保留字
同一个database中对象不能同名,即使不是同一类型

SQL语句分类:

这个分类并不是一个标准的分类,所以不用纠结

  1. DDL(Data Defination Language):数据定义语言,常用的SQL关键字有create,drop,alter
  2. DML(Date Manipulation Language):数据操作语言,常用的SQL关键字有insert delet,update
  3. DCL(Data Control Language):数据控制语言,主要用来操作权限,grant,revoke
  4. DQL(Data Query Language):数据库查询语言 select

数据库操作

  • 创建

    CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [ CHARACTER SET [=] charset_name ]
    [ COLLATE [=] collation_name ]

    datebase和schema可以不加区分的使用,if not exists从字面也可以看出,如果这个数据库不存在name就创建它,后面的character set可以设置该数据库默认的字符集,collate可以设置默认的排序规则,

    获取MySQL支持的字符集:

    > show charset;

    msyql会显示一个列表给你,有以下字段

    Charset:字符集的名称
    Description:描述信息
    Default_collation:该字符集使用的默认排序方式
    Maxlen:单个字符所占的最大字节数

    获取所有的排序方式:

    > show collation;

    示例:

    > create database blog character set "utf8mb4" collate "utf8mb4_general_ci"

    创建一个名为blog的数据库,默认字符集为utf8mb4,排序规则为utf8mb4_general_ci

  • 删除

    > DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

    这个没有什么需要解释的了,看到删除很多人又激动了,我只想说这是一个老梗了,老用没意思

表操作

  • 创建表

    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
      (create_definition,...)
      [table_options]
      [partition_options]
    Or:
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
      [(create_definition,...)]
      [table_options]
      [partition_options]
      select_statement
    Or:
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
      { LIKE old_tbl_name | (LIKE old_tbl_name) }
    

    有三种创建方式,第一种手动创建,第二种依照某个现有表创建,且可能会将表中的数据复制过来,第三种也是根据现有表创建,不过只创建表结构

    第一种:

    temporary表示创建的是临时表,小括号中定义了表结构,多个字段用逗号分隔,每个字段都由字段名开头,然后是数据类型,再然后后跟若干的该字段的属性

    数据类型,注意,定长数据类型也可以指定长度,不过并不会实际存储的大小,只是显示宽度,在数据类型的选择上如果追求性能可以尽量使用定长的数据类型,如果需要节省空间,则可以考虑变长的数据类型
    字符型:
    CHAR,BINARY(区分大小写),定长数据类型
    VARCHAR,VARBINARY,变长数据类型,
    TEXT,TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT,不区分大小写
    BLOB,TINYBLOB,MDEIDUMBLOB,LONGBLOB
    ENUM,SET
    数值型:
    精确数值型
    整型:TINYINT,SMALLIN,MEDIUMINT,INT,BIGINT
    十进制型:DECIMAL
    近似数值型
    浮点型:
    FLOAT:
    DOUBLE
    BIT
    日期时间型:
    DATE(3B)
    TIME(3B)
    DATETIME(8B)
    TIMESTAMP
    YEAR(2),YEAR(4)

    常用的属性:属性可以给每个字段单独设定,也可以在字段定义完成之后使用:属性()在括号中指定拥有该属性的字段比如 primary key(id)

    ​ unsigned:无符号,要仅挨着数据类型
    not null:不能为空
    primary key:主键,一张表只能有一个
    auto_increment:自增长字段
    default "value":默认值
    unique key:唯一键

    表选项:table_option:这项可以指定的有很多,这里仅介绍几个

    ​ engine=eng_name:设置存储引擎
    default charset:设置默认字符集
    comment:表描述信息

    示例

    create table weibo (
        weibo_id int  not null auto_increment,
        user_id int not null,
        weibo_content text(800) not null,
        img_paths text(65535),
        thum_img_path text(65535),
        weibo_date date not null,
        weibo_time time not null,
        weibo_stat int(2) default 1,
        primary key(weibo_id)
    )auto_increment=1 engine=innodb default charset=utf8;

    第二种

    这种方式主要是使用一个查询语句然后会创建一个和查询到的表的结构一样的表,且数据也会被复制过来

    示例

    [blog]> create table user (select user,host,password from mysql.user); 

    这会创建一个user表,该表是仿照mysql数据库中的user表创建的,且创建后数据库中会包含内容,这和括号中的select语句有关,之后还会详细的考察select语句,这里不做过多解释

    第三种:

    这种方式和上一种一相似,都是根据现有的其他表创建一个新表,只不过他只创建出表,而不会去复制表中的内容

    示例

    [blog]> create table student like hellodb.students;
    查看表结构
    desc table_name
    show table status like "table_name" -- 查看某个表的表信息
    show table status from "DB_NAME"    -- 查看某个库中所有表的结构
    show create table tb_name:          -- 查看表定义
    show columns from "table_name"
  • 修改表结构

    ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

    示例

    [blog]> ALTER TABLE comment ADD group_id INT NOT NULL; -- 添加一列
    [blog]> ALTER TABLE comment RENAME TO comments; -- 修改表名
    [blog]> ALTER TABLE comments MODIFY group_id int NOT NULL; -- 修改列属性,注意属性要写全,否则可能会丢失以前的属性
    [blog]> alter table weibo delete weibo_stat; -- 删除一列

    这里只是列了一些常用的例子,可以仿照例子,进行使用

  • 删除表

    ROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name]
  • 插入数据

    INSERT INTO tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    Or:
    INSERT INTO tbl_name SET col_name={expr | DEFAULT}, ...
    Or:
    INSERT INTO tbl_name [(col_name,...)]
      SELECT ...

    插入数据也有三种写法:

    第一种

    这种方式比较常用,value|values前的括号中指定要向哪个字段中添加数据,其后面的括号中的内容指定要对应前面指定字段添加的值,注意value后只能跟一组值,values后可以跟多组

    示例

    [blog]> insert into weibo (user_id,weibo_content,weibo_date,weibo_time) value 
    (2,"hello world",sysdate(),now());
    [blog]> insert into weibo (user_id,weibo_content,weibo_date,weibo_time) values
    (2,"hello world",sysdate(),now()),
    (3,"hello MariaDB",sysdate(),now());
    第二种

    这种方式有点啰嗦,他是使用set 然后指定字段名等于某个值,因此,稍微会有点麻烦,不过可读性还是不错的

    示例

    [blog]> insert into weibo set 
    user_id=5,
    weibo_content="hello linux",
    weibo_date=sysdate(),
    weibo_time=now();
    第三种

    这种方式比较有意思,插入的内容可以是使用select查询到的结果,因此可以批量的插入,前面的写法和第一种一样,只不过在指定完字段名之后,直接写select语句就可以了,需要注意的是select查询到的数据,各字段的数据类型必须和要插入的表的数据类型一致,否则不会成功

    [blog]> insert into weibo (user_id,weibo_content,weibo_date,weibo_time) 
    select user_id,weibo_content,weibo_date,weibo_time from weibo;
  • 更新表

    -- 单表语法
    UPDATE [LOW_PRIORITY] [IGNORE] table_reference
      SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
      [WHERE where_condition]
      [ORDER BY ...]
      [LIMIT row_count]
    -- 多表语法
    UPDATE [LOW_PRIORITY] [IGNORE] table_references
      SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
      [WHERE where_condition]

    这里仅讨论单表语法,多表我也没玩过,这里需要提醒一下,更新表如果出错和删库没有什么区别,所以要格外小心,最大的问题就是不指定条件,如果不指定条件会将表中的该列所有数据进行更新,当然为了防止这种悲剧的发生MySQL还是做了工作的,你可以在配置文件中客户端块中添加safe-update配置,这样当你的更新语句没有条件指定的时候mysql是不会给你更新的,当然如果你是通过编程语言调用的api就要格外注意你用的编程语言调用库有没有给你做这种限制,上面的where,order by,limit就是做限制的,这些内容会放在后面的select中介绍

    示例

    [blog]> UPDATE comments SET group_id=1 WHERE id=25;
  • 删除表中的数据

    -- 单表操作
    DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
      [WHERE where_condition]
      [ORDER BY ...]
      [LIMIT row_count]
    -- 多表操作
    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
      tbl_name[.*] [, tbl_name[.*]] ...
      FROM table_references
      [WHERE where_condition]
    Or:
    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
      FROM tbl_name[.*] [, tbl_name[.*]] ...
      USING table_references
      [WHERE where_condition]
    

    删除表和更新表一样,也可以使用safe_update,如果不指定条件也是悲剧

    示例

    [blog]> delete from comments where id=235;

select语句:

SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

上面的内容可能有点乱,我简单解释一下他的语法格式,最简单的select 要查询的字段 from 要从哪个表里面查询,然后在这个基础的格式上做扩展,对查询的结果进行筛选,排序,甚至将查到的结果作为一个临时表,交给另外一个select进行再次查询

示例

[blog]> select * from weibo;  -- *代表所有字段
[blog]> select weibo_id,user_id,weibo_contents from weibo;

下面就来一点点的扩展:

where子句:主要作用是对查到的结果进行过滤,其后面可以写一系列的条件

​ and:并且
​ or:或者
​ =,!=,<>,<=,>=,<,>:等于,不等于,不等于,小于等于,大于等于,小于,大于
​ is NULL:是NULL
​ is no null 不是NULL
​ between ... and ... 从某个小值到某个大值
​ not:取反
​ xor:异或

示例

[blog]> select * from weibo; where weibo_id = 5 
[blog]> select weibo_id,user_id,weibo_contents from weibo where weibo_idid > 3 and user_id < 5 ;

like:模糊搜索,_代表单个任意字符,%代表任意个任意字符,注意like必须放在where之后

示例:

[blog]> select * from weibo weibo where weibo_content like "%linux%";

group by:

分组,这个可能稍微有点难理解,在需要统计功能的时候可以使用,最长用的方法就是在select的时候使用某个函数进行统计某个字段的信息,然后,使用group by对这个字段进行分组,且分组的字段必须是select的字段中的一个

常用的函数

​ min():求这组值中的最小值
​ max():求最大值
​ avg():求平均值
​ sum():求和
​ count():统计个数

了解了这几个常用函数在使用分组时就简单多了,我们可以根据某一个字段进行分组,然后对该字段中相同的内容进行统计

示例

[hellodb]> select ClassID,avg(age) from students group by ClassID;

order by

根据指定字段排序,order by [-]col_name {[asc]|desc}:排序,默认升序,asc正序,可以不写,desc,倒叙,-主要控制NULL排在前还是后

示例

[hellodb]> select * from students order by Age;
[hellodb]> select * from students where classid = 1 order by Age;

limit:

limit的主要作用是取数据库中的某一部分,比如说前5行或者8-16行,有两种写法 limit 2表明取前两行,limit 2,3表明从第2行开始取,一共取3行,注意,这里的行是从0 开始的

示例:

[hellodb]> select * from students limit 6; 
[hellodb]> select * from students order by Age limit 6; 
[hellodb]> select * from students order by Age limit 2 10; 
多表查询

横向连接:将两个表连接后需要指定where条件,也就是第一张表中的某个字段等于另外一个表的字段连接起来,这样就能将其对应起来了,否则会做笛卡尔乘积,也就是小学学的乘法分配律,注意多表操作的时候要给每个表起别名,在名后使用 as 为其设置别名

连接类型

​ inner join ... on:内连接,两张表取交集
cross join ... on:交叉连接,笛卡尔乘积
left join ... on:左外连接,取左边的表的全部内容和两张表的交集,右表没有的为NULL
right join ... on:右外连接,取右边的表的全部内容和两张表的交集,左表没有的为NULL

另外在其他数据库中还有全外连接,但是mysql并没有实现这种操作,因此,此处不作解释

示例:

[hellodb]> select stu.Name,sco.CourseID from students as stu,scores as sco where stu.StuID=sco.StuID and sco.CourseID in (1,2,4,7);
[hellodb]> select stu.Name,stu.Age from students as stu where stu.age>(select avg(age) from students) group by ClassID;

纵向连接,将两个select语句连接起来,让其显示到同一张表中,注意两个select语句的字段数必须一致

且字段的数据类型也应该一致,union取的是不同的内容,如果想取全部内容可以使用union all

[blog]> select user,password,host from blog.user 
union 
select user,password,host from mysql.user;
[blog]> select user,password,host from blog.user 
union all 
select user,password,host from mysql.user;

视图:view,虚表,保存有实表查询到的部分结果,视图本身并不存放数据,其实和别名差不多,只不过是在任何一条SQL语句中都可以使用,其他数据库也有将视图中的数据保存到磁盘中的,但是MySQL并没有
CREATE VIEW view_name AS SELECT ...

select执行顺序:from --> where --> group by --> having --> order by --> select --> limit

相关文档

mysql文档
mysql5.1中文文档官方下载

bighero

这个人太懒什么东西都没留下

文章评论(0)