mysql

MYSQL

1.安装与卸载

(1)安装:mysql的安装

     注意点： 选择字符集 UTF8

              选中 root remote machine

  安装完成后 检测 window服务中 是否存 "Mysql"

  添加环境变量 Path=%PATH%;%MySql_HOME%/bin

  使用命令连接  mysql -u用户名 -p密码 -hip地址 -P端口

  常用的客户端  mysql.exe(dos客户端)

                sqlyog (类似 plsql)

                Sqlfront

(2)卸载:

1.在”开始”-->控制面板-->添加或删除程序-->mysql5.1

2.删除安装到本机的程序文件:c:\program files\mysql

3.删除安装后本机的应用数据:

c:\Documents and settions\Alll users\Application Data\mysql

2.Mysql和oracle数据体系对比:

 Oracle 每一个【用户】 有一个方案 方案下存在 表数据

 Mysql 每一个【数据库】 就存在表数据库 用户控制权限

3.常用的命令

use 数据库名 #进入输入的数据库

    desc 表名 #显示表结构  等价于 SHOW FULL FIELDS FROM user;

    show charset; #mysql支持的所有字符集

SHOW COLLATION; #字符串的校对规则（是否区分大小写）

Show table:查看指定的某个数据库名下有哪些数据表

Show databases:查看数据库中有哪些数据库

Show variables like ‘%character%’:查看包好指定名称的变量字符编码

Select version():查看函数的操作结果

Select user():查看当前登录的用户

Select now():查看当前系统时间

Select database():获取当前数据库名

4.用户管理

增加用户:create user 用户名 identified by 密码;密码要加单引

 在mysql中 用户名包括两部分  用户名@'连接权限'  

      #该用户只允许192.168.1.1@ip的机器连接当前数据库

      create user student@'192.168.1.1' identied by '密码'

      create user student@'192.168.1.2' identied by '密码'

      create user student@'%' identified by '密码'

查:  select host,user,password from mysql.user;

      修改密码

         1  直接通过修改密码表

            use mysql;

            update user set password=password('654321') where user='student';

            flush privileges;刷新更新结果

         2 直接修改当前用户的密码

            set password=password('123456');

         3 忘记密码

           找到 mysql_home/my.ini

               在[mysqld] 添加 skip-grant-tables

               重启 mysql服务

                  net stop mysql;

          net start mysql;

               在dos中敲入 mysql -uroot

               通过第1步骤的修改密码

 

      #删除用户

Drop user 用户名

        drop user student #默认是%

        drop user student@''

      #用户添加权限

        grant all on *.* to 用户名@'主机名'   --用户权限

        grant all on mysql.* to 用户名@'主机名'  --数据库权限

        grant all on mysql.db to 用户名@'主机名' --对象权限

5.授予权限

Grant  insert/select/update/delete/all  on  数据库名.对象  to  用户

Grant  all  on  *.*   to  用户名  (相当于orcal中grant  dba  用户)

 

grant [all privileges|all] on *.* to 用户名 [with grant option];  --只有本地DBA登录用户才能授所有权限给其它用户[该权限可以传递].

grant 具体权限 on 数据库名.对象名(表名，视图) to 用户名  [with grant option];

Grant all privileges on *.* to ‘用户名’@’%’ Identified by 密码 with grant  option;

远程用户也可以授权。

6.更新变量值：

set  变量=变量值;

 

set @a = 1;  ##设置a变量值为1;

 

set [@@]全局变量名=变量值; ##set @@a = 1; a一定要是全局变量名(show variables命令中得到.)  

 

select [@@全局变量名],@局部变量;  ##查询指定变量名的对应值。

 

设置编码字符集：

 

show variables like '%character%';  ##查出所有设置字符的变量

set 变量名=utf8;                    ##将指定的变量值设置为utf8的方式。--->latin1编码格式/

charset utf8;                       ##设置mysql连接字符集

 

my.ini文件：(控制台默认的编码方式为gbk编码|cp830)  mysql5.6 ----my_default.ini

[client]   ----   设置客户端的字符集

default-character-set=latin1  

 

[mysql]    ----   设置连接的字符集

default-character-set=utf8

 

[mysqld]   ----   设置的数据库，服务的字符编码集

default-character-set=utf8

 

help  命令名;   ##打开帮助文档  

 

系统表：information_schema.user_privileges,table_privileges  #当前用户的权限表，表权限表.

mysql.db  ,host, user;   #当前用户的数据库，主机，用户

 

7.忘记密码:

在my.ini文件中 [mysqlid] 下加 skip-grant-tables 跳过权限

修改:update mysql.user set password=password

刷新,注释(skip-grant=tables)

 

8.mysql数据类型与数据类型对应函数操作。

a.数据类型：

 

数值：    int  \integer   decimal(m,d)---double类型   (有符号数和无符号数)  --BigDecimal

 

字符：     varchar    变长字符串

char       定长字符串.

 

日期：    datetime,date,timestamp

 

布尔类型： bool   true|false  1|0  ---------只用一个二进制位来存储数据。

 

大对象：  

blob  二进制组成的大数据类型  ，  

text   由字符组成的大数据类型

 

b. 操作符：

 

 

圆括号()-- 最优先运算--改变优先级。

 

取反，负号: ~，-   数值运算后结果显示为bigint （8位字节来表示数据）

select ~-1,~1;

 

算术 ： + - */，%（取余),mod ,div(取整)

select 1+1,10/3,10%3,10 mod 3,10 div 3;

 

位运算(按位操作数据)：   &(位与）,|（位或）,^（位异或）,>>算术右移，<<算术 左移. (<<<逻辑左移 JAVA语言支持 )                       

比较： >,<,=,!=, 比较运算返回值为boolean,(true,false ,null),而且比较运算只要有一 方为null,则结果为null.

 

等价于<=>(比较两边都空值时,返回结果为1,有一方为null时，返回结果为0),

 

##true -->1 ,false ---->0, null--->null

is,like ,in  ,between .. and.. ,>=,<=

逻辑:    and   or   not ,!   ,&&,||

 

赋值运算: :=       select @t:=0;     @表示为局部变量，@@表为全局变量(系 统变量 show variables )

select @t:=(@r:=11)+(@g:=8) 运算并赋值。

 

c.类型运算：

abs(x);

ceil,floor,sqrt,tan,sin,cos

round(x,y):四舍五入   

truncate(x,n);--截断。如果n是负数，则截整数位数，如果n是正数时，将指 定n位后小数位除掉。---trunc(oracle)

Floor(x):返回不大于x的最大正数

Ceiling(x):返回不小于x的最小正数

SQRT(x):返回非负数的x的二次方根

 

字符类型 ：  

select 'a'+'b';   --是数值相加。

select concat(str1,str2,..);  --是将多个字符串连接。

SUBSTRING(s,n,len):返回一个长度同len字符相同的子字符串，"a.java"   ---select substring("a.java",-4,5);

REPLACE(s,s1,s2):使用字符串s2替代字符串s中所有的字符串s1.

strcmp(s1,s2);//字符串比较

Char_length():获取字符的长度

Length():获取长度

Trim():去空格

Ltrim():取左空格

Rtrim():取右空格

Replace(字符串,被替换的,要替换的):替换字符串

Instr(“fdfdsa”,”dfd”):找字符串的位置

Concat(‘c’,’a’,’b’):链接字符串

Upper():转大写

Lower():转小写

Asscii():转asc码

 

日期类型：

now() , --datetime,当前日期

curdate(),current_date(),current_date,current_timestamp

date     ----->   curdate(), current_date()

time    ------>  curtime(),current_time()

timestamp--> current_timestamp()

 

 

year(d),month(d),day(d)

 

select date_add(current_timestamp(),interval 1 second); --增加1秒

select current_timestamp(), date_add(current_timestamp(),interval 1 second),date_sub(current_date(),interval 1 month);

--增加1秒，减少一个月

datediff(date1,date2):返回起始时间date1和结束时间date2之间的天数。

例如：select datediff(now(),date_add(now(),interval 30 day));

其它函数：

ifnull(x,v)  --当前X为空，则显示V.  条件函数

user():显示当前用户

Select LAST_INSERT_ID():最后插入的主键

AUTO_INCREMENT:自增

Show collation:查看排序

Group_concat():聚合成一列 相当于Oracle中的wm_concat

 

转换函数:

Cast(1 as char):将数字转换为char

9.引擎

不同引擎是表示数据存储的格式不一致,同时支持功能不一致

InnoDB:支持acid事务,支持主键,自增长,支持锁

CSV:使用CSV格式存储(excel) 不支持事务,不支持自动增长,不支持主键,不支持锁.不允许插 入空的列

BLACKHOLE:黑洞数据写入后都会消失,一般用于记录日志,不同于存储数据

MEMORY:内存模式 数据写入了内存当电脑关机后数据丢失

10.DDL SQL语句

(1)切换数据库模式

user 数据库名:切换到指定的数据库

Select  database():查看当前数据库

(2)列出数库的表

Show tables();

(3)查看表结构

Desc  columns;

(4)创建数据库模式

Create database 数据库名

(5)删除数据库模式

Drop database 数据库名

(6)修改数据库模式

Alter database 数据库名  charcter set utf8

(7)创建表

Create temporary table 表名(

列名   类型,

...

列名   类型

 

)

注:temporary 可以创建临时表,临时表是指创建后只在当前数据库内有效,断开数据库连接自动删除

(8)删除表

Drop table 表名

(9)修改表

修改表名:rename 旧表名 to 新表名

修改列名:alter table 表名 rename column 旧列名 to 新列名

修改列类型:alter table 表名 modify 列名 类型

添加列:alter table 表名 add 列名 类型 default 默认值

删除列:alter table 表名 drop column 列名

查询表结构: desc 表名(命令下)

Select * from all_tab_columns where table_name=”表名”

表注释:comment on table 表名 is ‘注释’

列注释:comment on column 表名.列名 is ‘注释’

11. DML语句

(1) 插入数据

Insert into 表名 (列名,列名1...) values (‘值’,’值1’...)

(2) 查询数据

Select * from 表名

Select 列名,列名1 from 表名

(3) 删除数据

Delete from 表名 where 键=值;

(4) 修改数据

Update 表名 set 列名=值 where 键=值

12. 校对规则

以bin结尾的校对规则区分大小写,以ci结尾的不区分大小写

13. 过程

创建过程中要使用delimiter//来改变语句结束符号

类型(变量定义后使用)

数值,日期,字符,布尔类型

@变量名 类型[default  默认值]:与当前的过程相关联,只能在当前过程中定义使用

Declare 吧 into  default 0 //mysql赋初始值

存储过程的定义

DELIMITER //
DROP PROCEDURE IF EXISTS prg_select;
//
CREATE PROCEDURE 存储过程名()
BEGIN
  SELECT  ‘hello’;
END;
//
CALL 存储过程名();
//

14语句

If语句

DELIMITER //
DROP PROCEDURE IF EXISTS prg_if;
//
CREATE PROCEDURE prg_if()
BEGIN
  DECLARE i INT DEFAULT 101;
  IF(i%2=0) THEN
    SELECT '偶数';
    ELSEIF(i%2!=0) THEN
      SELECT '奇数';
  END IF;
  
END
//
CALL prg_if();
//

loop循环

DELIMITER //
DROP PROCEDURE IF EXISTS prg_loop;
//
CREATE PROCEDURE prg_loop()
BEGIN
  DECLARE i INT DEFAULT(初始值) 1;
  DECLARE mystr VARCHAR(200) DEFAULT '';
  myl:LOOP
    SET mystr=CONCAT(mystr,' ',i);
    IF(i=10) THEN
        LEAVE(退出条件) myl;
    END IF;
    SET i=i+1;(赋值)
  END LOOP myl;
  SELECT mystr;
END
//
CALL prg_loop();
//

REPEAT语句

DELIMITER //
DROP PROCEDURE IF EXISTS prg_repeat;
//
CREATE PROCEDURE prg_repeat()
BEGIN	
   DECLARE i INT DEFAULT 1;
   DECLARE mystr VARCHAR(200) DEFAULT '';
   REPEAT
	SET mystr=CONCAT(mystr,' ',i);
	SET i=i+1;
   UNTIL i=11(退出条件)
   END REPEAT;
   SELECT mystr;
END 
//
CALL prg_repeat();
//

While循环

DELIMITER //
DROP PROCEDURE IF EXISTS prg_while;
//
CREATE PROCEDURE prg_while()
BEGIN
   DECLARE i INT DEFAULT 1;
   DECLARE mystr VARCHAR(200) DEFAULT '';
   WHILE(i<=10) DO
	SET mystr=CONCAT(mystr,' ',i);
	SET i=i+1;
   END WHILE;
   SELECT mystr;
END 
//
CALL prg_while();
//

动态sql(prepare)

DELIMITER //
DROP PROCEDURE IF EXISTS prg_prepare;
//
CREATE PROCEDURE prg_prepare()
BEGIN
   SET @mySQL='select * from userinfo where userid=1';
   PREPARE stmt_name FROM @mySQL;
   EXECUTE stmt_name;
   DEALLOCATE PREPARE stmt_name;
END
//
CALL prg_prepare();
//

光标(相当于Oracle 中的游标)

DELIMITER //
DROP PROCEDURE IF EXISTS prg_cursor;
//
CREATE PROCEDURE prg_cursor()
BEGIN
   DECLARE done INT DEFAULT 0;
   DECLARE usernamevar VARCHAR(200);
   DECLARE cur1 CURSOR FOR SELECT username FROM userinfo;
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done =1;
   OPEN cur1;
     WHILE(done=0) DO
       FETCH cur1 INTO usernamevar;
       IF(done=0) THEN
		SELECT usernamevar;
       END IF;
     END WHILE;
     CLOSE cur1;
END
//
CALL prg_cursor();
//

分页:

Select * from 表名 limit 开始位置,多少条

删除重复的数据

Delete from 表名 where 主键 in(select a.主键 from (select 主键 from 表名 where 主键 not in (select min(主键) from 表名 group by 非主键的所有列名)) a)

15约束

主键约束:

Primary key 不允许为空,不允许重复

删除主键: alter table tablename drop primary key

主键自动增长  auto_increment

唯一约束:

Uqique

定义非空约束:notnull

16. 数据库的备份与恢复

备份:在cmd窗口下 mysqldump -u 用户名 -p 数据库名 >c://sql

恢复:方法1 在cmd窗口下 mysql -u 用户名 -数据库名 <c://sql

方法2 在mysql命令下 source c://sql

 

17. mysql权限体系

 

Mysql 的权限体系大致分为5个层级

全局层级

全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限。

数据库层级
数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。

表层级
表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。

列层级
列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。

子程序层级
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。