MySQL常用语句

DOS中连接：

　登录本机的MYSQL服务器：运行cmd，输入指令：mysql -h localhost -u root -p，然后输入密码，我的本机密码是root。（ps：mysql的默认端口是3306）其他的一些登录指令如下：

                                            -D， --database=name 打开指定数据库

                                            -h， --host=name 服务器名称（可以是本地，也可以是其他）

                                            -p， --password[=name] 密码

                                            -P， --port=# 端口号

                                            -u， --user=name 用户名

                                            -V， --version 输出版本信息并退出

    退出本机的MYSQL服务器：输入exit或者quit。

    此外，登录mysql后使用prompt可以修改mysql的提示符，如：prompt \D（完整日期） \d（当前数据库） \h（服务器名称） \u（当前用户），也可以联合使用，例如：prompt \u@\h:\d>

 

MYSQL常用的变量类型：

    整型类型：    tinyint（1字节）     smallint（2字节）   mediumint（3字节）    int（4字节）     bigint（8字节）

    浮点类型：    float(M,D)（全部位M位，小数点后D位）                  double(M,D)（全部位M位，小数点后D位）

    字符串类型： char(M)（定长字符串，不足M补空格）                  varchar(M)（不定长字符串，最大长度为M个字符）

                            tinytext（不定长文本串，最大长度为2^8个字符）    text（不定长字符串，最大长度为2^16个字符）

                            enum(a, b, c...)（枚举字符串）

 

操作数据库常用的SQL语句：

            create Database  name           创建数据库

            show databases                     显示所有的数据库

            use databases                        选择数据库

            drop database name               删除数据库

            alter database name character set = utf8（或gbk等）    修改数据库的数据编码方式

            show variables like 'storage_engine%'     查询默认的存储引擎（默认InnoDB）

            show indexs from name\G      查看表的索引

 

操作表常用的SQL语句：

　　　create table 表名( id int, name varchar(20), cost double, 属性名 数据类型)            创建表结构

            describe 表名                   查看表结构

            drop table 表名                  删除表

            alter table 表名 rename 新表名        修改表名

            alter table 表名 add/drop/modify 属性名 属性类型 [after/first 某列字段]     新增/删除/修改表列字段 [在某列字段之前或之后]

            insert [into] 表1(字段1, 字段2) values(值1, 值2), (值1, 值2)              将两组行数据插入到表1中

            insert [into] 表1(字段1, 字段2)  select 字段1, 字段2  from 表2           将表2中的两个字段全部数据复制到表1中    

            update 表名 set 字段 = 新值 where 过滤规则                                     更新字段值，在过滤规则约束下

            delete from 表名 where 字段=值                                                     删除字段等于某值的整行

            create index 索引名 on 表名(字段名)                                                创建索引

            select  字段 from 表名                                                     检索表中的字段内容

            select  字段1 from 表名 order by 字段2 asc/desc               按字段2的升序（asc）/降序（desc）排序输出字段1的内容

            select  字段1 from 表名 where 字段2 （= 值/> 值/< 值/between a值 and b值）  按字段2后面的规则过滤字段1的内容

            select  字段1 from 表名 where 字段2 like '通配符规则'        利用通配符过滤，常用通配符有：%（表示任意匹配） _（表示只匹配一个）

            select  【字段1,字段2,字段3】 from 表名 group by 分组字段 [having 过滤规则]       按分组字段进行分组，分组后再用having后的规则进行过滤（ps：having后的过滤规则中包含的字段必须是select检索的字段集合中出现的字段）

            select  表1.字段 表2.字段  FROM 表1 INNER JOIN 表2 ON 字段关联规则              将表1和表2按规则内部关联，输出关联后的两表字段行（ps：多表更新的简单例子：UPDATE tdb_goods INNER JOIN tdb_goods_cate ON tdb_goods.goods_cate = tdb_goods_cate.goods_cate SET tdb_goods.goods_cate = tdb_goods_cate.id）

            select  表1.字段 表2.字段  FROM 表1 LEFT/RIGHT OUTER JOIN 表2 ON 字段关联规则  将表1和表2按规则外部关联，输出关联后的两表字段行，及表1/表2中非关联的字段行

 

操作表常用的SQL函数：

  　　  concat(字段1, 字段2....) as 新字段别名          将多个字段进行拼接，as用于给他起个别名。     

            upper/lower(字段) as 新字段名                    将字段内容转为大写/小写

            length(字段)                                             找出字段串的长度

            locate(字段, 子串)                                      找出字段串的子串                 

            left/right(字段，num)                                取字段的左起/右起num位

            ltrim/rtrim(字段)                                       删除左/右的空格

            replace(字段，s1，s2)                               把字段中的s1替换成s2

            substring(字段，loc，len)                           截取字段从loc位置开始，len长度的子串

            round(字段, 数值num)                                对字段数据进行小数点后取num位

            ceil/floor(字段)                                          对字段数据进行向上/下取整

            power(字段,num)                                      求字段数据的num次幕

            now()                                                      当前日期和时间

            curdate()                                                 当前日期

            curtime()                                                 当前时间

            avg/count/max/min/sum(字段)                  聚集函数：计算字段列的平均值/数量/最大值/最小值/总和

            md5(字段)                                                求字段的md5加密值

 

子查询的操作：

          子查询是指在另一个查询语句中的SELECT子句，当某个查询是另一个查询的条件时，称这个查询子句为子查询。例如：

                   SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

       其中，SELECT * FROM t1 ...称为Outer Query，即：外查询。

                  SELECT column1 FROM t2 称为Sub Query，即：子查询。

       由此可知，子查询嵌套在外查询的内部。事实上，子查询内部也可以再嵌套子查询。此外，子查询必须出现在圆括号内。

       另外，如果子查询返回的结果是多行，则要用ANY、SOME、ALL关键词，将多行结果变成一行，以便外查询进行检索。

 

操作表常用的约束：

    表字段的约束在创建表的时候进行设置，常见的约束如下：

          not null                约束字段的值不能为空

          default 默认值       设置字段的默认值。在之后插入item时，若要用默认值，则插入字段值写为DEFAULT。

          auto_increment    设置字段自动增加，设置为auto_increment的字段必须为主键。在之后插入item时，若要自增，则插入字段值写为NULL或DEFAULT

          unique                 设置字段唯一性约束，即该表中这个字段上的内容不允许重复

          primary key         设置主键约束，即该字段可以唯一地标识所有记录，主键约束保证字段的唯一性和非空性

          foreign key          设置外键约束，即建立两个表的父子关系，该字段在父表中是主键，具有外键约束的子表字段取值范围由父表决定。

    添加约束的语句：

          ALTER TABLE 表名 ADD CONSTRAINT [约束名] PRIMARY KEY(约束字段);

          ALTER TABLE 表名 ADD CONSTRAINT [约束名] UNIQUE KEY(约束字段);

          ALTER TABLE 表名 ADD CONSTRAINT [约束名] FOREIGN KEY(外键列)  REFERENCES 父表(参照列) ON DELETE CASCADE;

    修改约束的语句：

          ALTER TABLE 表名 ALTER 字段名 SET DEFAULT 默认值;

 

外键的使用：

     外键约束的好处：外键约束用于保证数据一致性和完整性，实现数据表一对多的关系，也就是父表的信息被多个子表共享。

     外键约束的要求：（1）父表（参照列所在表）和子表（外键列所在表）存储引擎都是InnoDB；（2）外键列和参照列有相似数据结构；（3）外键列和参照列必须创建索引，其中外键列是mysql自动创建索引，参照列若在主键则自动创建索引否则需要人工创建索引；（4）子表添加行时，如果添加的外键列的值不在父表参照列中，则添加失败。

     外键约束的使用：在创建表时，最后写上： FOREIGN KEY (外键列) REFERENCES 父表(参照列) ON DELETE CASCADE/SET NULL。其中，CASCADE表示父表参照字段更新或删除时，字表外键列也自动变化；SET NULL表示父表参照字段更新或删除时，字表外键列设为NULL。例如：

create table province(
    id tinyint primary key auto_increment,
    name varchar(20) NOT NULL
);
create table users(
   id tinyint primary key auto_increment,
   username varchar(20) not null,
   proid tinyint,
   foreign key(proid) REFERENCES province(id) on delete cascade
);

 

自定义函数的使用：

     mysql中创建函数要包含传入参数和返回值。一个不含有传入参数的例子如下：

CREATE function f1() RETURNS VARCHAR(30)
    return DATA_FORMAT(NOW(),'%Y年%m月%d日 %H点：%i分：%s秒');
SELECT f1();

        一个含有传入参数的例子如下：

CREATE function f2(num1 smallint unsigned, num2 smallint unsigned) RETURNS float(10,2)
   return (num1+num2)/2;
SELECT f2(3,5);