mysql基本操作

1、散列表（Hash table，也叫哈希表），是根据关键码值(Key value)而直接进行访问的数据结构。也就是说，它通过把关键码值映射到表中一个位置来访问记录，以加快查找的速度。给定表M，存在函数f(key)，对任意给定的关键字值key，代入函数后若能得到包含该关键字的记录在表中的地址，则称表M为哈希(Hash）表，函数f(key)为哈希(Hash) 函数。

1、select version; 获取版本号

2、select connection_id（）；查看当前用户的连接数

3、show processlist; 输出当前用户的连接信息

4、select user(),curent_user(),system_user(); 获取当前登录用户名

----------------------MySQL不区分大小写

1、dos命令行进入mysql：MySQL  -h  localhost  -u  root  -p

                      进入mysql的samp_db库：MySQL  -h  localhost  -u  root  -p  samp_db     

            若同时再执行一条查询语句，则                    …………                     samp_db   -e   "desc products"

2、where下与串类型的列比较要括号，与数值不需要。

3、where  A1  like  'jet%';    #找出A1 下所有以 jet 开头的产品。

4、where A1 regexp '1000';  #正则表达式，包含文本1000的所有行；‘1000|2000’,包含1000或2000。

    where A1 regexp '[123] Ton'；意思是匹配1 ton、2 ton、3 ton，是【1|2|3】Ton 的缩写。也可[1-3] Ton。

    where A1 regexp '\\.';  包含字符  ‘.’ 的值。以\\为前导，\\-表示查找，\\.表示查找 . 。

5、\\f 换页    \\n 换行   \\r回车  \\t 制表   \\v 纵向制表    \\\ 匹配 \

6、show warnings； 查看错误信息

7、delete from A;  删除表中数据

     delete from A where a1=0;

8、show table status like  'view_t' \G   查看视图信息

   show create view view_t \G  查看视图的详细定义

  select * from A.view; 查看表A 下的视图

9、select  last_insert_id;  查看最后插入的ID

10、分组查询 group by ...having....   用having过滤     group by ...  with rollup; 增加一条记录，记录总和     和集合函数一起使用

11、排序 order by 默认升序，降序DESC

12、limit 4；前4行     limit  4 offset 3；从第5行开始后面的3条记录

13、集合函数 count()、sum()、avg()、max()、min()

14、select num1 from A where a1 > any(select  num2  from B);或者用some，作用一样。与大于B中num2下的任何1个数即提出，

                                                       all （...）则全部

15、 where exists （）；not exists（）；   IN ()

16、正则表达式  REGEXP

        where A1 REGEXP  '^b'; 以字母b开头的记录； ' y$' :以y结尾的记录；'a.g'： ‘.‘匹配任一字符，如 aog；

        ‘ ^ba* ’ ：以b开头，后面包含a，a没出现也满足； ‘ ^ba+ ’ ：以b开头，后面包含a，a至少出现1次

        regexp  ‘on’；匹配指定字符串，只要有这两个字母的都可，如lemon；‘on|ap’：出现 on 或 ap 的。

        regexp 'x{2,4}'; 表示字母x至少连续出现2次，最多不超过4次

      区别：where A1 like ‘on’；只含字母on的

1、alter  table A modify  A1 INT(10) NOT NULL; 修改A1为 int(10),非空

     alter tabel A drop A1; 删除A1列

     alter table A add A1 VARCHAR(50) NOT NULL;添加A1列

2、show create table A \G;  查看表结构

3、alter  table A  ENGINE=MyISAM;     修改表的存储引擎为MyISAM  ,   引擎还有InnoDB

time

1、create table  tmp3( t  time);    年份（y  year）

2、insert into tmp3 values (current_time),(now());   插入系统当前时间

    note: '1112'和1112表示00:11:12，‘11:12’表示11:12:00 ； A:B:C ，A,B,C都不能超过60

date

1、create table tmp4(d date);

2、insert into tmp4 values(current_date),now(),curdate(),curdate+0);    curdate+0 将日起转换为数值型 2011-12-23 → 20111223

datetime

1、create table tmp5(dt datetime);

2、insert into tmp5 values( ' 17-05-04  08:08:08 ' ),('990909090909');  插入当前日期 insert....vulues( now());

任何标点符号都可以用作日起部分或时间部分的间隔符。

timestamp 以UTC（世界标准时间）格式保存 YYYY-MM-DD  HH:MM:SS

    note： current_timestamp()、timestamp()、localtime()、now()、sysdate()  作用相同

3、UTC_DATE()返回当前的UCT日期值，UTC_TIME()

month(date)   select month('2012-12-12');    12

mothname(date)    select monthname('2012-12-12');  December

dayname(d)、dayname( '2013-02-13'),wednesday

dayofweek(d)  一周中的第几天   1

weekday（d） 0~6表周一至周日   6

week(d) 计算日期d是一年中的第几周

dayofyear(d) 计算日期d是一年中的第几天

dayofmouth(d) 计算日期d是一个月中的第几天

quarter（）获取指定日期对应的季度

DATE_FORMAT(date,format)   以format指定的格式显示date值    

       eg:  select date_format( '2017-05-05','%W %D %M %Y);   输出：Friday 5th May 2017

字符串类型

char(M)固定长度字符串。0~255   对于‘ab   ’,尾部空格将被删除

varchar(M)可变字符串，0~65535   ，保留尾部空格

text类型

tinytext  255    text 65535    mediumtest 16 777 215  longtext 4GB

ENUM 、SET类型

BIT 位字段类型

1、create table tmp6(b bit());

2、insert into tmp6 values (2),(8);

3、select  bin( b+0)  from tmp6;

      b+0表示将二进制结果转换为对应的数字的值，bin（）函数将数字转换为二进制形式，返回 10  1000

逻辑运算符

NOT 或者！ ，AND 或者&&， OR或者 || ,  XOR逻辑异或

！ 1+1相当于（！1）+1 为1  优先级

位运算符

| 位或  eg：10 |15,10,15的二进制分别是1010,1111，按位或运算之后，为1111，即整数15

& 位与    ^位异或    <<位左移    >>位右移    - 位取反

正则表达式

REGEXP

1、SELECT note， note regexp ‘[gm]’ from tmp15;  判断note字段是否包含字母g或者m

 CREATE TABLE test_table2
 (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        firstname CHAR(100) NOT NULL,
        middlename CHAR(100) NOT NULL,
        birth DATE NOT NULL,
        title CHAR(100) NULL
 ) ENGINE=MyISAM;

数学函数

1、平方根函数 SQRT(x)，求余函数 MOD(x,y)

2、CEIL（x），CEILING（x），向上取整数，3.5则4，FLOOR（x）向下取整数，,3.5则3

3、RAND()产生随机数

     ROUND(x)对 x 四舍五入，ROUND(x，y） ，对x四舍五入，并保留到小数点右面y位，y为负，小数点左边的相应位数保存为0，不进行四舍五入；

      eg：round（232.38，-1）→230 ，round（232.38，-2）→200，round（232.38，0）→232

4、SIGN(x) 符号函数，由负、0、正数分别返回 -1,0,1

5、POW(x,y)、POWER(x,y)返回  x的 y 次方x^y

     EXP(3)→e^3

6、log（x），log10（x）以10为底的对数

7、角度弧度转换 radians（x）和degrees（x）

8、正弦SIN(x）、反正弦ASIN(x)；cos（x），acos（x），tan(x),atan(x），cot（x）

字符串函数

1、char_length(x) 计算字符串长度

2、concat（s1,s2）合并字符串函数，select concat('jiang','wenwu');→ jiangwenwu  

                                还可拼接两个列，concat（a1,'(,'a2')'）→ a1(a2)

     concat_ws(x,s1,s2)  其中 x 为分隔符 ，select concat_ws( '-'，'1993'，'12' ); →  1993-12

3、insert(s1,x,len,s2)替换字符串函数，select insert（‘Quest’，‘2’，'4'，‘what’）;→Qwhat，   

                                                                                 （‘Quest’，‘2’，'100'，‘what’）;→Qwhat

4、upper（x）、lower（x）

5、获取指定长度的字符串 LEFT(s,n)、RIGHT(s,n)；select  LEFT( 'footbal',4); → foot

6、删除空格函数 LTRIM(s)、RTRIM(s)、TRIM(s)    eg:select concat(TRIM(A1),  '(' , A2 , ')' );→A1(A2)

7、TRIM（s1,FROM,s）删除字符串 s 中两端的所有字符串 s1

8、REPEAT(s,n) , select repeat('my',3); →mymymy

9、space（n）空格函数，n个空格

      replace（s，s1，s2）用字符串 s2 替代 s 中的所有字符串 s1

10、STRCMP（s1，s2）比较。相同返1，s1小于s2，返-1，否则返1

11、reverse（s）字符串逆序

存储过程和函数

存储过程就是一条或多条SQL语句的集合。

1、创建存储过程  delimiter //   create produce  A(...,...)  begin...end //   delimiter;  调用：call A(a，b)； 查看结果：select @a,@b;

2、创建存储函数 delimiter //

                              create function A( id INT)

                              returns char(50)

                              return (select ......);  //   调用存储函数： select A(100)；     在命令行 select A(100) //

     show procedure status like 'A%' \G

     show  create   {procedure | function}  A;

3、定义变量  declare a1 INT DEFAULT 100;  设定默认值a1=100

4、光标只能在存储过程和函数中使用

5、流程控制：if、case...then...else...end、loop

      使用LOOP语句进行循环操作：

       my_loop：loop

       SET id=id+1;

             IF id<10  THEN   ITERATE  my_loop;       iterate 语句将执行顺序转到语句段的开头

            ELSEIF id>=10  THEN  LEAVE  my_loop;      leave语句退出循环

            END IF;

       END LOOP  my_loop;

6、declare  id INT DEFAULT 0；

    repeat

              set id=id+1;

              until  id>=10

     end repeat；

7、while  i<10  do

              set i=i+1;

     end while;

8、修改存储过程A 的定义，将读写权限改为 MODIFIES SQL DATA，并指明调用者可以执行：

ALTER  PROCEDURE A

             MODIFIES  SQL  DATA

             SQL   SECURITY   INVOKER;

9、修改存储过程A 的定义，将读写权限改为 READS  SQL DATA，并加上注释信息  FIND NAME：

ALTER  PROCEDURE A

             MODIFIES  SQL  DATA

             COMMENT  'FIND NAME';