Mysql笔记

这是我在极客学院看视频记录下的笔记：

SQL功能：
 DDL-定义语言
 DML--操作语言
 DCL--控制语言

执行方式：
   交互式SQL---如shell、PLUS
   嵌入式SQL

数据定义：
CREATE TABLE           创建表
DROP  TABLE              删除表
ALTER TABLE             修改表
CREATE INDEX          创建索引
DROP INDEX              删除索引
CREATE PROCUDURE 创建存储过程
DROP PROCUDURE     删除存储过程


数据操作
insert
delete
update
select

权限控制
GRANT 用于授予用户权限
REVOKE 解除用户权限

事务控制:
COMMIT 用于提交事务
ROLLBACK 用于回滚事务

下载workbench，在浏览器中输入wb.mysql.com---download now--download

XAMPP---开启mysql----workbench操作数据库


更新数据失败？
 workbench--prefrences--sql editor--取消forbid。。。复选框--重新连接数据库

修改定义;
新增字段: alter table tb_user add email varchar(50);
删除字段：alter table tb_user drop email;
更改字段：alter table tb_user change age user_age int;()
更改字段类型：alter table tb_user change user_age user_age tinyint(1) not null;
更改表明：alter table tb_user rename user_tb;
删除表：drop table user_tb;


mysql通过shell连接到数据库

 如果启动的shell已经配置好了路径，只需输入：mysql --version（查询版本号）


 如果启动的shell没有配置好路径，则需输入/Applications/XAMPP/bin/mysql -h localhost -u root -p
    
 （-h是表示连接哪个服务器，localhost是本地，-u是用户名，密码是默认为空，直接回车即可）

查看数据库：show databases;
查看帮助文档：mysql --help

MYSQL的数据类型：
主要的类型有----

 1.数值类型：整数类型和浮点数类型

  1.1整数类TINYINT ,SMALLINT,MEDIUMINT,INT,BIGINT

  tinyint只占1个字节，范围是-128到127
  smallint只占2个字节，范围是到32767
  MEDIUMINT只占3个字节,范围是到8388507
  INT只占4个字节
  BIGINT只占8个字节


  alter table user_tb add intsix0 int(6);
  alter table user_tb add intsix1 int(6) zerofill;---六位，不够就用0填充；
  如插入123，则intsix0 字段显示123，而intsix1字段显示000123

1.2浮点型
 FLOAT(4个字节),DOUBLE(8个字节),DECIMAL

 alter table user_tb add intsix2 float(5,2);表示浮点数最多有5位且小数点后最多有2位

bool:真1假0

2.字符类型：
  char  是定长的，代表此字段最多为10个，超过或截取前十个；
  varchar
  text
  blob

a.字段charstring 如果类型是char(10)，则字段是不区分大小写的
b.字段charstring 如果类型是char(10) binary，则是二进制的，是区分大小写的；


insert into table tablb_String values('HELLO!');


第一种情况where charstring =‘hello’,是可以查出来的，第二种情况则查不出来，只能是HELLO!


存储稍微大块的内容：

  text不区分大小写

   blob区分大小写

ENUM,SET

alter table tablb_String  add gender enum('M','N');

则插入的时候，只能单个插入：
 
insert into tablb_String   values('Hello!','M');且只能是M或N ，如果是其他的话，数据库插入null


alter table tablb_String  add setcol set('A','B','CD','E');


INSERT INTO tablb_String   values('Hello!','A,B,CD');




日期和时间类型：


DATE  YYYY-MM-DD 范围1000.1.1-9999.12.31 ，3个字节
TIME  HH:MM:SS
YEAR  YYYY 范围1901.1.1-2155.12.31
DATETIME YYYY-MM-DD  HH:MM:SS范围1000.1.1-9999.12.31
TIMESTAMP 1970.1.1-2031


create table table_date (birthday DATE);


插入值可以是‘2015-12-25’ 也可以是20150101数值型，此方法同样适用于datetime和timestamp


insert into table_date  values('2015-03-10')  或者 insert into table_date  values(20150101) 


alter table table_date  add datetimecol DATETIME(2);默认是空的，2代表在秒后加00，
 
  例如新增值为20150203，则在数据库中的体现值是20150203 00:00:00.00（注意后面的.00）


alter table table_date  add datetime2 TIMESTAMP; 新增第一个此类型字段时数据库中默认的是当前时间，2015-12-25 15;34:20 ,
                                 
                                                 新增第二个此类型的字段时数据库默认为0000-00-00 00:00:00


主键自增长，auto_increment


alter table users add unique(name);说明name是唯一的，如果插入重复则会报错




delete from users;//当执行此删除的时候，会报错，因为有可能此表处于被保护的阶段，怎么办？
   
    可以查询出这个保护安全性的变量字段：
    
      执行sql语句：show variables like ‘%sql_safe%’在数据库中会显示出sql_safe_updates字段是true;
  
      要想删除这个表，应该把这个字段值改为false;
 
      set sql_safe_updates =0;数据库就会变成false，这样就可以继续执行删除表操作；
    
    删除后ID 不会被重置，再插入一条数据时，ID 会接着走；
   
 trunkcate table users;//删除了整个表，他的ID自增长会重置，然后从1开始；  




=============================
  
              ------------运算符------------


  
-----算数运算符----


 +、-、*、/、%


 例子：select 1+2；


        有null参与的计算结果都是null,例1+null 结果为null
        字符之间也是可以进行运算的，
   
             ‘9’+‘6’，结果为15，
            
              ‘20aa’*5结果为100（可以转化为20）


               ‘aa20’*5结果为0（可以转化为0）




-----比较运算符----


= 、<>、!=、>、<=>、between、in、is null、like、regexp


除了<=>之外，其他的跟null进行比较结果都是null，无法比较； 


    null<=>null结果是1
  
    0<=>null结果是0


    'tabl'='TABL'结果是1，不区分大小写，如果前后有空格可以忽略的，如 'tabl'='TABL   '结果还是1，如果前面加binary ,就开始区分大小写了，如binary  'tabl'='TABL',结果是0
   
   select 5 in(1,2,3,5,6)结果是1，select null in (1,23,4)结果是null；select null in(1,2,null)结果还是null,所以null是无法使用in方法的；


    like:%，_等等


    _下划线是表示单个字符，‘jikexueyuan’ like 'j_ke%'结果是1


-----逻辑运算符----


 not或者!、AND或者&&、OR或者||、XOR（此运算代表亦或，前后两个相同则是假，相反则是真）


-----位运算符----


=====================================Mysql连接和联合====================================================


交叉连接cross join


内连接  inner join


交叉连接、内连接都是可以用逗号“,”代替的，但是交叉连接没有where，而内连接可以有where


左连接 left join  ....on ...


右连接 right join ....on....


其中on可以用using()代替，
  
   例如：select * from user u left join user_group ug on user.userid = ug.userid


        等价于select * from user u left join user_group ug using（userid）


自连接：把一张表看成两张表


联合：union 多个表查询的列相同，可以联合在同一张表中，如果有重复，会自动去重


      union all不会自动去重，会全部显示






=====================================Mysql函数=======================================




==============数学函数==========


ABS(value1)    取绝对值
 
CEILING(value1)最高限度,取比当前值大的、相邻的一个整数，ceiling(4.6)=5


FLOOR(value1)  取比当前值小的、相邻的一个整数，floor(3.7)=3


GREATEST(value1,value2...)取当前组数据中最大的一个


LEAST(value1,value2...)取当前组数据中最小的一个


MOD（value1,value2）取两个值的余数


PI() ，值为3.141593


RAND(),产生0到1的随机数


ROUND(X)四舍五入取整数，如round(9.2)=9


ROUND(X,M),四舍五入,X是数值，M是取小数点几位，如round(10.234,2)=10.23


TRUNCATE(X,M);截取Ｍ位小数点，没有四舍五入


SIGN(M)获取布尔值标志，结果只有-1，0，1，正数返回1，负数返回-1，例如sign(-23.4)=-1,sign(4)=1,sign(0)=0


POWER(X,M)或POW(X,M)表示X 的 M次方，例如：power(2,3)=8


BIN(X)将X转换为二进制的数字


OCT(X)将X转换为八进制的数字


HEX(X)将X转换为十六进制的数字


=================聚合函数===================


对一组数据集中起来概括的函数


AVG()求平均值


MAX()最大值


MIN()最小值


COUNT()数量


SUM()求和




=================字符串函数===================


LENGTH(X) 求长度


LCASE(X)、LOWER（X）将字符串变为小写


UCASE(X)、UPPER(X)将字符串变为大写


STRCMP(X,M)比较字符串大小，结果为-1，0，1


    例如STRCMP('hello','yes')=-1因为h比y靠前


        STRCMP('yes','yes')=0   因为俩个字母一样


POSITION(substr IN str)     返回字符串在另一个字符串的位置，从1开始查起，例如 select POSITION('d' IN 'dfg')=1


REPLACE(str,from_str,to_str)将字符串中的from_str字符串换成to_str，例如select REPLACE('hrllo','r','o')=hollo


INSERT(str,pos,len,newstr)  将字符串的第pos个位置开始查len个，替换成newstr，INSERT('kello',3,4,'hh') = kehh
  
CONCAT(value1,value2)合并函数,将value1和value2组合合并


CONCAT_WS(separator,str1,str2,...)合并函数加符号CONCAT_WS(';','hello','world')=hello;world


LEFT(str,len)  截取从左边开始算的len个字符，如left('wegfd',2)=we


RIGHT(str,len)  截取从右边开始算的len个字符，如right('wegfd',2)=fd


LPAD(str,len,padstr) 为str在左边填充padstr,填充后总数量为len，例如lpad('lo',5,'hel')=hello


rpad  在右边填充


ltrim 去掉左边的空格


rtrim 去掉右边的空格


trim  将两边的空格去掉


SUBSTRING(str,pos,len)取字符串


ASCII取阿斯克马函数


=================日期和时间函数===================
now() 获取当天日期和时间


curtime获取当天时间


curdate 获取当天日期


year('20151229') 获取年=2015


month('20151229') 获取月份=12


monthname('20151229') 月份名称


dayofyear是当年的第几天


dayofweek()是这个星期中的第几天  ，注意第一天是从星期天还是算的


dayname 当天的名字，是星期几，如果是第三天，就是星期二


week这一年的第几周


hour()可以取一个具体时间的小时


minute()可以取一个具体时间的分钟


second()可以取一个具体时间的秒


DATE_ADD(date,INTERVAL expr unit)可以对年、月、日进行相加


date_sub()可以对年、月、日进行相减




=========================数据加密函数================================


password()加密，不可逆，区分大小写


encrypt()


encode("secrect","abc") 加密，secrect是被加密的值，abc是加密的键值，
  
 insert into users(uname) values(encode("secrect","abc"));


decode("","")解密
 
  select decode(uname,"abc") from users;


二者的加密级别略低，当无法正确解密的时候，显示为空


===============================
aes_encrypt("secrect","abc") 加密，secrect是被加密的值，abc是加密的键值，
  
 insert into users(uname) values(aes_encrypt("secrect","abc"));


aes_decrypt("","")解密
 
  select aes_decrypt(uname,"abc") from users;


二者的加密级别略高，当无法正确解密的时候，显示为null


====
MD5(x)可以直接查询md5加密结果


SHA(x)


============================================控制流函数==================================================


IF(1>2,3,4);如果1>2成立的话，返回第一个参数3，否则返回4


ifnull()


nullif()