MySQL基本操作语句

前言

今天咱不涉及MySQL的理论知识，就从它的增删改查等基本操作谈一谈。因为基本理论知识大家也许都已经较为熟悉了，但往往容易遗忘他的一些操作语句，带大家返璞归真。

1.SQL:

1.Structured Query Language:接构化查询语言，每一种数据库的操作方式存在不一样

2.SQL通用语法：

1.SQL语句可以单行或多行书写，以分号结束。
2.可使用空格和缩进来增强语句的可读性
3.MySql数据库的sql语句不区分大小写，关键字建议大写
4.三种注释：
   单行：--或#
   多行：/*  */

3.SQL分类：

1.DDL(Data Definition Language)数据定义语言
  用来定义数据库对象：数据库，表，列等。关键字：create,drop,alter等
2.DML(Data Manipulation Language)数据操作语言
  用来对数据库中表的数据进行增删该查。关键字：insert delete update等
3.DQL(Data Query Language)数据控制语言
  用来查询数据库中表的记录。关键字：select where等
4.DCL(Data Control Language)数据库控制语言
  用来定义数据库的访问权限核安全级别，及创建用户。关键字:GRANT,REVOKE等

4.DDL:数据定义语言

1.操作数据库:CRUD

   1.C(Create):创建
     *创建数据库：create database 数据库名称;
     *创建指定字符集的数据库：create database 数据库名称 character set 字符集名称(gbk,utf8);

   2.R(Retrieve)查询
     *查询所有数据库名称：show databases;
     *查询某个数据库的字符集：show create database 数据库名称;

   3.U(Update)修改
     *修改数据字符集：alter database 数据库名称 character set 字符集名称;

   4.D(Delete)删除
     *删除数据库：drop database (if exists) 数据库名称;

   5.使用数据库
     *查询当前正在使用的数据库名称：select database();
     *使用数据库：use 数据库名称;

2.操作表

   1.C(Create):创建
     *create table 表名(
         列名1 数据类型，
         列名2 数据类型，
         ...
         列名n 数据类型
         );

      *复制表：create table 表名 like 被复制表名;
      *常用数据类型：
      1.int
      2.double(5,2)共5位，小数点后有2位
      3.date:日期，yyyy-MM-dd
      4.datetime:日期，yyyy-MM-dd HH:mm:ss
      5.timestamp:时间错类型 yyyy-MM-dd HH:mm:ss如果将来不给这个字段复制，或复制为null
       则默认使用系统当前时间，来自动赋值。
      6.varchar:字符串
         varchar(20):表示最大20个字符
      例：
      create table student(
           id int,
           name varchar(32),
           age int,
           score double(4,1),
           birthday date,
           insert_time timestamp
           );
           
		CREATE TABLE SC(
		Sno CHAR(9),
		Cno CHAR(4).
		Grade SMALLINT,
		PRIMARY KEY (Sno,Cno), 	/* 主码由两个属性构成，必须作为表级完整性进行定义*/
		FOREIGN KEY (Sno) REFERENCES Student(Sno),	/*表级完整性约束条件，Sno 是外码，被参照表是Student中*/
		FOREIGN KEY (Cno) REFERENCES Course(Cno)	/*表级完整性约束条件，Cno是外码，被参照表是Course*/
		);


   2.R(Retrieve)查询
     *查询某个数据库中所有表的名称：show tables;
     *查询表结构：desc 表名称;
   3.U(Update)修改
     *修改表名
      alter table 表名 rename to 新的表名;
     *修改表的的字符集
      alter table 表名 character set 字符集名称;
     *添加一列
      alter table 表名 add 列名 数据类型;
     *修改列名称 类型
       alter table 表名 change 列名 新列明 新数据类型;
       alter table 表名 modify 列名 新数据类型;
     *删除列
       alter table 表名 drop 列名;
   4.D(Delete)删除
      * drop table 表名;
      * drop table if exists 表名;

5.DML:增删改表中数据

1.添加数据：

insert into 表名(列名1，列名2，...列名n) values(值1，值2...值n);
insert into 表名 values(值1，值2...值n);

2.删除数据：

  *删除某一字段：alter table 表名 drop column 字段名;
  *删除记录：delete from 表名 where 条件(id=2等);(不加条件将删除所有记录)
  *删除表：truncate table 表名 (删除表，然后再创建一个一模一样的空表)

3.修改数据：

  *update 表名 set 列名1=值1，列名2=值2，列名3=值3 where 条件;

6.DQL:查询表中记录

  select * from 表名;(查询所有记录)

1.语法：

  select
        字段列表
  from
        表名列表
  where
        条件列表
  group by
        分组字段
  heving
        分组自后的条件
  order
        排序
  limit
        分页限定

2.查询基础:

  1.多个字段的查询
   例：查询 姓名和年龄：select name,age from 表名;
  2.去除重复
   例：去除address：select distinct address from 表名;
  3.计算列
   例：计算math和english分数字和：select name,math,english,math+english from 表名;
      如果有null参与的运算，计算结果都为null
      解决办法：
        select name,math,english,ifnull(math,0)+ifnull(english,0) from 表名;
  4.起别名
    select name,math 数学,english 英语,ifnull(math,0)+ifnull(english,0) as 总分 from 表名;

3.条件查询：

 1.where子句后跟条件
 2.运算符
    > < <= >= = <>
    between...and
    in(集合)
    is null
    or 或 ||
    not 或 ！
    例：查询年龄大于20：select * from 表名 where age>20;
       查询年龄不等于20：select * from 表名 where age<>20;
       查询年龄大于20小于30：
       select * from 表名 where age>20 && age<30;
       select * from 表名 where age>20 and age<30;
       select * from 表名 where  age between 20 and 30;
       查询年龄15,17,18岁的信息:
       select * from 表名 where age=15 or age=17 or age=18;
       select * from 表名 where age in(15,17,18);
       查询英语成绩为null:
       select * from 表名 where english=null;这是不正确的
       select * from 表名 where english is null;正确
       查询英语成绩不为null:
       select * from 表名 where english is not null;
      模糊查询：
       查询姓马的有哪些? like
       select * from 表名 where name like '张%';
       查询第二个字是‘小’的人：
       select * from 表名 where name like '_凤%';
       查询姓名是3个字的人：
       select * from 表名 where name like '___';
       查询姓名中包含‘三’的人：
       select * from 表名 where name like '%三%';

4.排序查询：

 order by 排序字段1 排序方式1,排序字段2 排序方式2...
 排序方式：
    ASC:升序,(默认)
    DESC：降序
  例：按数学成绩升序查询：
    select * from 表名 order by math ASC;
    如果数学成绩一样，按照英语成绩排序：
    select * from 表名 order by math ASC,english DESC;
   注：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

5.聚合查询：

将一列数据作为一个整体
  1.count:计算个数
  2.max:计算最大值
  3.min:计算最小值
  4.sum:计算和
  5.avg:计算平均值

6.分组查询：

1.语法:group up 分组字段
  注：分组之后查询的字段：分组字段、聚合函数
   where和having的区别：
   where在分组之前进行限定，如果不满足条件，则不参与分组，having在           分组之   后进行限定，如果不满足结果，则不会被查询
2.例：按性别分组，分别查询男女同学的平均分,人数
     select sex,avg(math),count(id) from 表名 group by sex;
     按性别分组，分别查询男女同学的平均分,人数,要求：分数低于70的人不参与分组。
     select sex,avg(math),count(id) from 表名 where math>70 group by sex;
      按性别分组，分别查询男女同学的平均分,人数,要求：分数低于70的人不参与分组,分组之后人数要大于2
      SELECT sex,AVG(math),COUNT(id) FROM person WHERE math>70 GROUP BY sex HAVING COUNT(id)>=2;
      SELECT sex,AVG(math),COUNT(id) 人数 FROM person WHERE math>70 GROUP BY sex HAVING 人数 >=2;

7.分页查询：

1.语法：limit开始的索引
2.例：
  SELECT * FROM person LIMIT 0,3;#第一页
  SELECT * FROM person LIMIT 3,3;#第二页
3.公式：开始的索引=(当前的页码-1)*每页显示的页数
  分页操作是一种"方言"

中言

显然上面已经列举出了常规操作的语法，但是总觉得有点太基础，那么我们再稍微进阶一下

七种JOIN理论

详情

• MySQL优化学习笔记
• SQL的七种join
• 如何理解sql的七种join

几个高级查询运算词

A： UNION 运算符

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

B： EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

C： INTERSECT 运算符

INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。

记录搜索

TOP关键字在SQL语言中用来限制返回结果集中的记录条数，其使用方法有两种形式，下面做以详细的介绍：

（1）返回确定数目的记录个数

语法格式： SELECT TOP n <列名表> FROM <表名> [查询条件]

其中，n为要返回结果集中的记录条数

（2）返回结果集中指定百分比的记录数

语法格式： SELECT TOP n PERCENT <列名表> FROM <表名> [查询条件]

其中，n为所返回的记录数所占结果集中记录数目的百分比数

举例说明：

假设数据库中有一个表存储的为学生的信息（student）：

（1）SELECT TOP 20 * FROM student --查询前20名学生的信息

（2）SELECT TOP 20 * PERCENT FROM student --查询学生表中前20%的学生信息

开头到N条记录

Select Top N * From 表

N到M条记录(要有主索引ID)

Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID   Desc

N到结尾记录

Select Top N * From 表 Order by ID Desc

案例

例如1：一张表有一万多条记录，表的第一个字段 RecID 是自增长字段， 写一个SQL语句，找出表的第31到第40个记录。

 select top 10 recid from A where recid not  in(select top 30 recid from A)

分析：如果这样写会产生某些问题，如果recid在表中存在逻辑索引。
select top 10 recid from A where……是从索引中查找，而后面的select top 30 recid from A则在数据表中查找，这样由于索引中的顺序有可能和数据表中的不一致，这样就导致查询到的不是本来的欲得到的数据。

解决方案

1， 用order by select top 30 recid from A order by ricid 如果该字段不是自增长，就会出现问题
2， 在那个子查询中也加条件：select top 30 recid from A where recid>-1

其它

主键

说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)

索引

MySQL索引分类：

• 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
• 唯一索引：索引列的值必须唯一，但允许有空值。
• 复合索引：即一个索引包含多个列。
• 基本语法：
  • 创建
    • CREATE [UNIQUE] INDEX indexName ON mytable(columnName(length));
    • ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnName(length));
  • 删除
    • DROP INDEX [indexName] ON mytable;
  • 修改
    • ALTER INDEX [oldIndexName] RENAME TO [newIndexName];
  • 查看
    • SHOW INDEX FROM tableName;
  • 使用alter命令 - 有四种方式来添加数据表的索引
    • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
    • ALTER TABLE tbl name ADD UNIQUE index_name (column_list);：这条语句创建索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)。
    • ALTER TABLE tbl_name ADD INDEX index_name (column_list);：添加普通索引，索引值可出现多次。
    • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);：该语句指定了索引为FULLTEXT，用于全文索引。

注：索引是不可更改的，想更改必须删除重新建。

视图

说明：创建视图：create view viewname as select statement
删除视图：drop view viewname

参考博客

• MySql基本操作语句
• Mysql操作语句大全