MYSQL基本操作

2019年06月09日

用户管理:



    1.登陆

        mysql //登录默认用户root 没密码

        mysql -uroot -p1234        

        mysql -h host_name -u user_name –ppassword

        mysql --user root --password --host localhost

        mysql -uroot -p1234 -h192.168.1.9;   //登陆远程服务器

    2.创建用户

        命令:

            CREATE USER 'username'@'host' IDENTIFIED BY 'password';

        

        说明:username - 你将创建的用户名, 

        host - 指定该用户在哪个主机上可以登陆,

        如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%. 

        password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.

 

 

        create user root identified by '1234';

        

        insert into mysql.user(Host,User,Password) values("localhost","xt",password("1234"));

            

        mysql>flush privileges;//刷新系统权限表

        这样就创建了一个名为:xt  密码为:1234  的用户。



    

    3.受权

        命令:

            GRANT privileges ON databasename.tablename TO 'username'@'host'

        说明: privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等.

        如果要授予所的权限则使用ALL.;databasename - 数据库名,tablename-表名,

        如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 如*.*

        

        grant all privileges on stud.* to xt@localhost identified by '1234';

        // xt本地用户受权为 all所有权限,操作 stud数据库的所有表

        

        mysql>flush privileges;//刷新系统权限表

        

        如果想指定部分权限给一用户,可以这样来写:

        mysql>grant select,update on stdu.* to xt@localhost identified by '1234';

        //刷新系统权限表。

        mysql>flush privileges;





        grant all on *.* to 'root'@'%' with grant option;

        //all所有权限, *.* 数据库名.表名 

        // '%'这里指这可以使用该用户访问的主机ip,'%'表为所有主机都可以用该帐号访问

        

    4.设置与更改用户密码

    

        命令:

            SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

        如果是当前登陆用户用:

            SET PASSWORD = PASSWORD("newpassword");

 

        例子: SET PASSWORD FOR 'xt'@'%' = PASSWORD("123456");

 

    

    

        更新用户密码

        update user set password=password('1234');

        //更新所有用户的密码为1234

        //password('1234');表示对密码加密

        //对密码的更新要重启服务才能生效

        --

    5.修改密码    

        mysql>update mysql.user set password=password('新密码') where User="xt" and Host="localhost";

        mysql>flush privileges; 



        

    6.删除名称为空的用户

        delete from user where user='';

        

    7.删除用户

        mysql>DELETE FROM user WHERE User="xt" and Host="localhost";

        mysql>flush privileges;

        

        或: drop user xt;

        

        //删除用户的数据库

        mysql>drop database stud;

        

    

    8.增加用户可以选程访问,mysql.user表:

        create user root identified by '1234';   --创建用户 root 密码1234

        Grant all on *.* to 'root'@'%' with grant option; 授权'%'表示所有主机都可以使用该帐号登陆





    9.撤销用户权限

         命令: 

            REVOKE privilege ON databasename.tablename FROM 'username'@'host';

 

        说明: privilege, databasename, tablename - 同授权部分.

 

        例子: REVOKE SELECT ON *.* FROM 'xt'@'%';

 

    10.显示数据库所有变量设置

        show variables; 

        

    11. 显示数据库所用的码表;

        show variables like 'char%';

        

    12. 设置编码

        set character_set_client=UTF8;

    13. 同时设置client,connection,database,results编码;(统一编码)

        set names utf8;

        

    14.修改用户可登陆主机 (可阻止其他IP登陆)

        update user set host='192.168.1.3' where host='%';

----------------------------------------------------------------------------

备分数据库――》

            mysqldump -u 用户名 -p 数据库名 > 文件名.sql

恢复数据库――》

            Source 文件名.sql        

        

-----------------------------------------------------------------------------        



    1.显示所有数据库

        show databases;

    

    2.创建数据库

        create database xtk;

        create database xtk character set utf8;//创建支持中文编码的数据库

        create database if not exists xtk; //如果xtk 不存在就创建;

    3.选择使用数据库

        use xtk;

    

    4.创建表

        create table stud(

            id int,

            name varchar(30),

            age int

            );

    

    5.显示默认数据库中所有表

        show tables;



        或:

        select * from tables ;

        select * from tables where table_schema='xt';

        //查寻表中有那些字段

        select * from columns where table_schema='xt' and table_name='stud';

        

    6.查寻表

        select * from stud;

        Select * from user;    //看一个某个表中的数据:

        Select 字段 from 表名;

        select host,user,password from user; 

        SELECT * FROM users WHERE sex='1' AND (age>30 OR addr='NY');

        

        SELECT * FROM users WHERE age IN(25,65,35,15);/*in就是或的关系*/

        select * from test.text where id in(1,3,5) /*指找出ID为1 3 5的 not in()则相反   */

        

        SELECT * FROM users WHERE  age>=20 AND age<=30;

        -

        SELECT * FROM users WHERE age BETWEEN 20 AND 40;/*and且用的是大小就可以转成between*/

        select * from test.text where id not between 1 and 4   /*指ID不在1-4里面的 出来 */

        

        **子查询**

        子查询是指在一个查询中包含别一个查询语句 ,在服务器解析带有子查询的语句时,会先执行子查询。再执行外部查询:



           查询一下,年龄最大的人是哪一个人:

        SELECT * FROM users WHERE age=(SELECT MAX(age) FROM users);





        查询一下,年龄最小的人是哪一个人:

        SELECT * FROM users WHERE age=(SELECT MIN(age) FROM users);

        

        /*没有车的人*/

        SELECT DISTINCT users.name FROM users,car WHERE users.id NOT IN (SELECT car.userid FROM car);

    

    7.插入记录

        insert into stud values(1,'xtksfm',160);

    

        insert into stud(id,name) values(1,’jack’);// 指定列

        

        

        insert into stud values(1,'xtksfm',160),(2,'sssm',110),(3,'xdddm',80);



    8.放弃正在输入的命令

        \c

        clear;

    9.显示命令清单

        \h

        help

    10.退出mysql程序:

        \q

        exit

    11.查看mysql服务器状态信息:

        \s

        

    12.显示系统设置信息

        show VARIABLES;    



    13.显示表结构

        desc 表名;

    

    14.删除表中数据

        delete from 表名;

        delete from stud;

        delete from stud where id=1; /*删除指定的行 */

        

        delete from stud where id>5;

        

        也可以使用

        TRUNCATE TABLE stud; //它与delete的区别是不记录操作日志,即无法恢复数据。



    15.删除表

        drop table 表名;

        drop table stud;



    16.删除数库库

        drop database 库名;





    17.修改表结构

        添加字段:

            alter table stud

            add column age int; //给stud添加例 age

        

        删除字段:

            alter table stud

            drop column name; 

        

        在字段的上面添加一个新的字段。需要关键字 first :

            mysql > alter table passwd add id int(3) not null auto_increment primary  key not null first ;

      

        在指定的位置添加一个新的字段:

            mysql >alter table passwd add date year after id ;

        

        默认添加在最后面:

            mysql >alter table passwd add QQ int(16) ;

        

        删除表结构字段:

            mysql >alter table passwd drop date ;

        

        修改字段类型:

            mysql >alter table passwd modify QQ int(11) not null ;   修改passwd表的QQ字段。    

        

        修改字段名:

            mysql> alter table passwd change QQ qq int(11) not null ;  修改QQ字段为qq,用到change关键字。 需要将权限类型写上,也可以更改类型。        

    18. 修改(设置)数据

        update stud set age=90; //给stud表的所有age字段设置值;

        

        update stud set age=160

        where name='ksfm';        //给stud表name='ksfm'的age字段设置值;



        update stud set name='Rose',id=22 where id=1; //同时修改两个值

    

        在原值上面进行累加操作:

        Update product set price=price*1.1  -所有商品的价格上涨10%



    

    19. 排序

        排序:ORDER BY要出现在其他关键字的后面

        

        /*DISTINCT 去除相同*/



        /*ORDER BY 从小到大排序*/

        SELECT DISTINCT age FROM stud ORDER BY age; //默认从小到大;

        SELECT DISTINCT age FROM stud ORDER BY age  ASC;

        

        /*ORDER BY  DESC从大到小排序*/

        SELECT DISTINCT age FROM stud ORDER BY age DESC;



    20. 自动增长主键

        

        CREATE TABLE b(

            id INT PRIMARY KEY AUTO_INCREMENT,

            NAME VARCHAR(32)

        );

        

    21.    添加主键//person_pk为自定义主键名(用于索引)

        ALTER TABLE person

            ADD CONSTRAINT person_pk PRIMARY KEY (id);

        

    22.    删除主键:

        alter table tablename drop primary key ;

        

    23.    外键约束

        create table car(

            id   varchar(32) primary key,

            name varchar(30),

            price Numeric(10,2),

            pid  varchar(32),

            CONSTRAINT car_fk foreign key(pid) references person(id)

        );    

        

        ALTER TABLE sb

           ADD CONSTRAINT sb_fk1 FOREIGN KEY(sb_studid) REFERENCES stud(stud_id);

    24.    别名

        xs.age 这种引用方式可以在子查询中使用,如果要在主查询中使用 ,就要用 别名.age的形式:

        SELECT aa.id,aa.name,aa.age FROM (SELECT * FROM stud) AS aa WHERE age>20;



        SELECT xx.id,xx.name,xx.age FROM stud AS xx;

        

        

-----------------------------------------------------------------------------------        



    limit 限制



    select *from stud limit 0,2;   //大于0行的, 查询2行;

        

        

------------------------------------------------------------------------------------

    where子句:

    

    1.    SELECT * FROM stud WHERE age>80;

        



    2.    /*使用between*/ 在两者之间

        SELECT * FROM stud WHERE age BETWEEN 29 AND 44;

        

    3.    /*and or */

        SELECT * FROM stud WHERE age>80 AND age<600;

        SELECT * FROM stud WHERE age=160 OR age=150;



    4.    /*用in来换掉or*/ 表示在之内的

        SELECT * FROM stud WHERE age IN(5,55,15);



    5.    /*模糊查询like,%(任意匹配),_(匹配一个)*/

        SELECT * FROM stud WHERE NAME LIKE '大__';

        SELECT * FROM stud WHERE NAME LIKE '%o%' OR age=44;



        

    6.    /*查询没有设置年龄的人*/

        SELECT * FROM stud WHERE age IS NULL;

        UPDATE stud SET age=100 WHERE age IS NULL;



-----------------------------------------------------------------------------

    view 视图

    视图是保存在数据库中的选择查询,相当于从一个或多个数据表中派生出来的虚拟表。

    它兼有查询和表的双重功能。



    view 相当于一个查寻出来的虚拟表

    

    /*创建一个view*/

    CREATE VIEW ss AS SELECT * FROM stud WHERE age>9;

    查寻view

    SELECT * FROM ss ;



-------------------------------------------------------------------------------



    聚合函数:

    

    1.Count计数――》

        Count(*)行数量—不包含空行 null 



        SELECT COUNT(*) FROM users; //计数并查寻出来

        SELECT COUNT(1) FROM users;

        COUNT不会包含NULL的记录的行

        SELECT COUNT(age) FROM users;



        /*使用count进行数量计算,使用as设置一个别名

          使用*或是1是计算所有。

          使用某个名,是指不包含null的列的数量 。

        */

        SELECT COUNT(*) AS num FROM stud;   //AS num 别名



        SELECT COUNT(age) FROM stud;





    2.avg平均――》

        /*计算age整型的平均值*/

        SELECT AVG(age) FROM stud;

        

        /*计算age平均值ROUND取整*/

        SELECT ROUND(AVG(age)) AS 平均值 FROM stud;

        

    3.Max最大――》

        SELECT MAX(age) FROM stud;

        SELECT NAME,MAX(age) FROM stud;

        /*查寻年龄最大的人的名子*/

        SELECT * FROM stud WHERE age =(SELECT MAX(age) FROM stud);

        SELECT * FROM stud WHERE age in(SELECT MAX(age) FROM stud);

    

    4.Min最小――》

        /*选择年龄最小的那个人的名称*/

        SELECT * FROM stud WHERE age=(SELECT MIN(age) FROM stud);

    

    5.Sum求和――》

        SELECT SUM(age) FROM stud;

        

    6.Distinct――》去除相同的信息

        /*查寻出age字段 不包含age相同的记录*/

        SELECT DISTINCT age FROM stud;

        

    

    排序:ORDER BY要出现在其他关键字的后面



        /*ORDER BY 从小到大排序*/



        SELECT DISTINCT age FROM stud ORDER BY age; //默认从小到大;

        SELECT DISTINCT age FROM stud ORDER BY age  ASC;

        

        /*ORDER BY  DESC从大到小排序*/

        SELECT DISTINCT age FROM stud ORDER BY age DESC;

        

    

    7.Exists存在判断――》

        /*exists只判断是否存在*/

        SELECT * FROM stud WHERE EXISTS (SELECT * FROM stud WHERE age=100);



-------------------------------------------------------------------------

    

    group by对数据进行分组:

    

    /*根据名称进行分组计算*/

    /*查寻出name 和 age 两个字段 , 并根据name分组 ,所有name 相同的为一行记录 */

    SELECT NAME,SUM(age) FROM stud GROUP BY NAME;



    /* sum(age)要想出现在 where 后那么必须 在前面查寻使用过sum(age)*/

    /* 对分组后的数据要筛选不用where 而是having关键字 */

    SELECT NAME,SUM(age) FROM stud GROUP BY NAME HAVING SUM(age)>20;



--------------------------------------------------------------------------

    

    Having:

    

    Having关键字的做用是对已经使用了聚合函数的查询再进行过虑:

    如:根据角色ID计算数量,然后只选择数据大于4的记录:

        SELECT COUNT(*),func_role FROM func GROUP BY func_role

        HAVING COUNT(*) >4



    

---------------------------------------------------------------------------

    

    字符串处理函数:

    1.

    Length(str) - 求字符串长度

    

    2.

    TRIM(str)

        SELECT * FROM stud WHERE TRIM(NAME)='xt';

    

    Ltrim(str) - 去掉左边的空格

        

    Rtrim(str) - 去掉右边的空格

        SELECT * FROM stud WHERE RTRIM(LTRIM(NAME))='AAA';

    

    

    Left(str,len); - 从左边取出n个字符

        SELECT LEFT(TRIM(NAME),2) FROM stud;

        

    Right(str,len); - 从右边取出n个字符

    Substring(str,begin,end) -返回子串

    Reverse(str) –返回颠倒的字符串

        SELECT REVERSE(NAME) FROM stud;

        

    Lower(str) - 转成小写

    Upper(str) - 转成大写

    Concat(Str,str…..)串联字符串。

        SELECT CONCAT(NAME,age,id) FROM stud;

    Instr(str,s) – 返回s在str中出面的位置,没有则返回0

        SELECT NAME,INSTR(NAME,'o') FROM stud;

    

-----------------------------------------------------------------------------

**    数学相关函数**

    ABS (number2 )                    绝对值

    BIN (decimal_number )            十进制转二进制

    CEILING (number2 )                向上取整

    CONV(number2,from_base,to_base)    进制转换

    FLOOR (number2 )                向下取整

    FORMAT (number,decimal_places )    保留小数位数

    HEX (DecimalNumber )            转十六进制

    LEAST (number , number2? [,..])    求最小值

    MOD (numerator ,denominator )    求余

    RAND([seed])                    RAND([seed])    

------------------------------------------------------------------------------



    关联查询和子查询:

    

    当一个表的数据不能满足我们的需要时,我们就要从多个表中查询数据。

    此时必须使用关联查询:

    inner join – 内关联,双方必须都要存在。 

    left join -左关联。以左边数据为准。

    right join - 右关联。

    

    /*查寻年龄相同的所有人*/ (已知有age 23 或 21的)

    select * from stud where age=23 or age=21;





    /*查寻年龄相同的所有人*/ (不知age都是什么值)

    //分析:根据年龄计数并分组,然后再筛选>=2的

    select count(age),age from stud group by age having count(age)>=2;

    

    1.无关子查询

    

        /*无关子查询*/--->>是指子查询语句中不包含主语句中的表

        select * from stud where age in(

            select age from stud group by age having count(age)>=2

        );

    

    

    2.相关子查询

    

        /*查寻年龄相同并且age>20的所有人*/ (不知age都是什么值) 



        /*相关子查询*/--->>是指子查询语句中包含主语句中的表

        SELECT * FROM stud AS xs WHERE age IN(

            SELECT age FROM stud WHERE xs.age>20 GROUP BY age HAVING COUNT(age)>=2

        );



        

    3.注意:

        xs.age 这种引用方式可以在子查询中使用,如果要在主查询中使用 ,就要用 别名.age的形式:

        例:

            SELECT aa.id,aa.name,aa.age FROM (SELECT * FROM stud) AS aa WHERE age>20;



            SELECT xx.id,xx.name,xx.age FROM stud AS xx;

            

    

    4.关联查询join ---->>指有关联关系的表之间的查询

        两个表之间有 一对多 ,多对多, 一对一 的关系:

        那么两个表之间就有 :    

            关键字 join 相当于逻辑判断作用

            /*内连接的连接查询结果集中仅包含满足条件的行*/

            /*外连接的连接查询结果集中既包含那些满足条件的行,还包含其中某个表的全部行*/

            

            inner join – 内关联,查询时两个表必须都要存在。 相于逻辑 AND

    -->>示例:

        users            car

        |-id-主键        |-id-主键

        |-name            |-name

        |-sex            |-price

                        |-userid-外键

        CREATE TABLE users(

            id VARCHAR(32) PRIMARY KEY,

            NAME VARCHAR(32),

            sex CHAR(1)

        );

        

        CREATE TABLE car(

            id VARCHAR(32) PRIMARY KEY,

            NAME VARCHAR(32),

            price NUMERIC(10,2),

            userid VARCHAR(32)

        );

        

        

        /*查询一个某人拥有什么车*/

        SELECT users.name,car.name FROM users,car WHERE users.id=car.userid;

        /*查询一个某人拥有什么车 关联关系查询*/

        SELECT users.name,car.name FROM users INNER JOIN car ON users.id=car.userid;//inner join 指两个表都必须相时存在

                

        

        /*没有车的人*/

        SELECT DISTINCT users.name FROM users,car WHERE users.id NOT IN (SELECT car.userid FROM car);

        

        /*没有车的人 用关联关系查询    left join -左关联。查询时以左边数据为准。*/

        SELECT person.name,car.name FROM 

        person LEFT JOIN car ON person.id=car.pid //只要左表存在就列出,右表不满足的列null

        WHERE car.name IS NULL            

        

        /*查询xt有什么车*/

        SELECT person.name,car.name FROM person,car

        WHERE person.id=car.pid AND person.name='xt';

        

        SELECT car.name FROM car WHERE car.userid=(SELECT users.id FROM users WHERE users.name="xt");                

    

        /*查询有2车以上的人的ID*/

        SELECT COUNT(pid),pid FROM car GROUP BY pid HAVING COUNT(pid)>=2;



        SELECT * FROM person

        WHERE id IN (SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2);

    

            right join - 右关联。

------------------------------------------------------------------------------

     多对多

        

         stud                     book

    --------------         ------------------

        S001 xt                B001 Java

        S002 yy                B002 Oracle

        S003 YL                B003 .Net

        

                

                中间表

            -------------------

外键<---    StudID        BookID--->外键 foreign key

            S001        B001

            S001        B003

            S003        B001

            ________________

                    |

                联合主键-->> 不同出现两个字段都一样的数据

                primary key

    

        /*创建stud表*/

        CREATE TABLE stud(

         stud_id VARCHAR(32) PRIMARY KEY,

         sdub_name VARCHAR(32)

         );

        

        /*创建book表*/

        CREATE TABLE book(

         book_id VARCHAR(32) PRIMARY KEY,

         book_name VARCHAR(32)

         );

        

        /*创建sb中间表*/

        CREATE TABLE sb(

         sb_studid VARCHAR(32) NOT NULL,

         sb_bookid VARCHAR(32) NOT NULL

         );

        

        /*给中间表设置联合主键*/

         ALTER TABLE sb

           ADD CONSTRAINT sb_pk PRIMARY KEY(sb_studid,sb_bookid);

        

        /*给中间表设置外键*/

         ALTER TABLE sb

           ADD CONSTRAINT sb_fk1 FOREIGN KEY(sb_studid) REFERENCES stud(stud_id);

        

        /*给中间表设置外键*/

         ALTER TABLE sb

           ADD CONSTRAINT sb_fk2 FOREIGN KEY(sb_bookid) REFERENCES book(book_id);

 

        

        /*查询所有人选择的所有书*/(SQL 92标准 联合查询)

        SELECT stud.sdub_name,book.book_name FROM stud,book,sb

        WHERE stud.stud_id=sb.sb_studid AND book.book_id=sb.sb_bookid;

        

        /*查询所有人选择的所有书*/(SQL 96标准使用join)

        SELECT stud.sdub_name,book.book_name

        FROM stud INNER JOIN sb ON stud.stud_id=sb.sb_studid

              INNER JOIN book ON sb.sb_bookid=book.book_id;

    

        

        /*查询所有没有选择书的人*/

        SELECT stud.sdub_name,book.book_name

        FROM stud LEFT JOIN sb ON stud.stud_id=sb.sb_studid

              LEFT JOIN book ON sb.sb_bookid=book.book_id

        WHERE book.book_name IS NULL;

        

        /*查询没人先的书*/

        SELECT stud.sdub_name,book.book_name

        FROM stud RIGHT JOIN sb ON stud.stud_id=sb.sb_studid

              RIGHT JOIN book ON sb.sb_bookid=book.book_id

        WHERE stud.sdub_name IS NULL;

        

------------------------------------------------------------------------------

    表约束操作:

    

    1.主键约束

        添加主键的两种方法:

            (1)    /*创建表时定义约束*/

                CREATE TABLE car(

                    id INT PRIMARY KEY,

                    NAME VARCHAR(30),

                    NO INT

                );

                

            (2)    /*创建表后 再修改添加主键约束*/

                推荐使用第二种方法,因为可以指定主键名;

                CREATE TABLE person(

                    id INT NOT NULL, //要加非空约束

                    NAME VARCHAR(32),

                    age INT

                );

                ALTER TABLE person

                ADD CONSTRAINT person_pk PRIMARY KEY (id);

                //person_pk为自定义主键名(用于索引)

    

    

    2. 唯一约束

         CREATE TABLE b(

            id INT UNIQUE,

            NAME VARCHAR(32)

         );

    

    

    3.创建自动增长的主键

        注意:自动增长的主键,在有记录删除后主键id 出现断号,而且oracle不支持;

        CREATE TABLE b(

            id INT PRIMARY KEY AUTO_INCREMENT,

            NAME VARCHAR(32)

        );

    

    

    

    

    

    

    4.默认约束

        例: sex字段的默认值为0

        CREATE TABLE s2(

            id INT,

            sex CHAR(1) DEFAULT '0'

        );

        INSERT INTO s2(id) VALUE(10);

    

    

    5.外键约束

        FOREIGN KEY

        

        

        create tabl person(

                    id   varchar(32) primary key,

                    name varchar(30),

                    sex  char(1)

                );

        create table car(

            id   varchar(32) primary key,

            name varchar(30),

            price Numeric(10,2),

            pid  varchar(32),

            CONSTRAINT car_fk foreign key(pid) references person(id)

        );

        

        /*查询什么人有什么车*/

        SELECT person.name,car.name FROM person,car

        WHERE person.id=car.pid;

        

*

关系查询:



        /*查询什么人有什么车 用关联关系查询*/

        SELECT person.name,car.name FROM 

        person INNER JOIN car ON person.id=car.pid;//inner join 指两个表都必须相时存在



        

        /*没有车的人*/

        SELECT person.name,car.name FROM 

        person LEFT JOIN car ON person.id=car.pid //只要左表存在就列出

        WHERE car.name IS NULL                      //再条件 car.name是 NULL的

        

        

        

        /*查询xt有什么车*/

        SELECT person.name,car.name FROM person,car

        WHERE person.id=car.pid AND person.name='xt';

        

        

        /*查询有2车以上的人的ID*/

        SELECT COUNT(pid),pid FROM car GROUP BY pid HAVING COUNT(pid)>=2;



        SELECT * FROM person

        WHERE id IN (SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2);

-------------------------------------------------------------------------------------

    if  和 case 控制流程函数



    

    1. if        

        

        IF(expr1,expr2,expr3)

        //判断expr1如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),

        则 IF()的返回值为expr2; 

        否则返回值则为 expr3。

        IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。

        

        SELECT IF(1>2,2,3); 

        结果3

        

        SELECT IF(1<2,'yes ','no');

        结果'yes'



        SELECT IF(STRCMP('test','test1'),'no','yes');

        结果'no'

    

    

    2. ifnull    

        

        IFNULL(expr1,expr2)

        //如果expr1 不为 NULL,则 IFNULL() 的返回值为 expr1;

        否则其返回值为 expr2;

        IFNULL()的返回值是数字或是字符串,具体情况取决于其所使用的语境。

        

        SELECT IFNULL(1,0);

        结果1

        

        SELECT IFNULL(NULL,10);

        结果10

        

        SELECT IFNULL(1/0,'yes');

        结果'yes'

        

    

    3.NULLIF

    

        NULLIF(expr1,expr2)

        //如果expr1 = expr2 成立,那么返回值为NULL,否则返回值为 expr1。

        这和CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END相同。

        

        SELECT NULLIF(1,1);

        结果NULL

        

        SELECT NULLIF(1,2);

        结果1

        



        

    4.CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

        

        

        SELECT CASE 1 WHEN 1 THEN 'one'

        -> WHEN 2 THEN 'two' ELSE 'more' END;

        结果'one'



        

        SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;

        结果'true'



        SELECT CASE BINARY 'B'

        -> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;

        结果NULL



        

        create table ss(

            id int,

            sex char(1) default '0',

            birth varchar(19)

        );

        INSERT INTO ss VALUES(10,'1','2011-09-08 09:23:27');

        select id,(case sex when '0' then '女' else '男' end) as 性别,birth from ss;

        SELECT id,(CASE sex WHEN '0' THEN '女' WHEN sex='1' THEN '男' END) AS 性别,birth FROM ss;



-------------------------------------------------------------------------------------------------------



    存储过程:

    

    1.    

        DELIMITER &&                //改变Mysql语句结束符 为"&&"

        

        carete procdure p2()

            BEGIN

                select * from lk;

            END&&

            

        DELIMITER ;                    //创建存储过程完成后,改回 ";"

        

        //调用

        CALL p2();

            

    2. 声明变量



        @为用户自定义变量 

        @@为系统变量

            

            

            例:@abc

               select @abc;

                

               select @@character_set_database;

               

----------------------------------------------------------------------------------------------

    事物操作:

    

    1.开启一个事物

        start transaction;

    

    2. 用户的一次操作(执行SQL语句)

            

            

    3) 提交 commit; 或 撤销rollback;        

            

    ------------------------------------

    隔离级别操作:

    

    注意:隔离级别操作,影响的是在开启一个事物之内的操作;

    

    .    什么是脏读? 

        读取别人未提交的数据,未保存的数据就叫脏读;  

     

     

     隔离级别分为 四种级别:

    Transaction_read_uncommited=1 (读未提交)可读到别人未提交的数据

    Transaction_read_commited = 2(读已经提交)只能读到别人已经提交的数据

    Transaction_repeatable_read=4(可重复读-默认)永远读取的是自己已经读取到的数据。不受别的连接的影响。

    Transaction_serializable =8(串行化-最高级别) 相当于同步(当A用户在操作,那么B用户只能处理等待状态;



    1.查看隔离级别:

        select @@tx_isolation;

        

    

    2.设置隔离级别:(在纪色的Mysql5.5以上)

        //设置为可读到别人未提交的数据;

        set session transaction isolation level read uncommitted; 

        



        //设置为只能读到别人已经提交的数据;

        set session transaction isolation level read committed; 



        //设置为串行化-最高级别 相当于同步;

        set session transaction isolation level serializable; 

--------------------- 

作者:shisongsong 

来源:CSDN 

原文:https://blog.csdn.net/shisongsong/article/details/70218273 

版权声明:本文为博主原创文章,转载请附上博文链接!


分类:技术文章 | 标签: mysql | 查看:551
网站数据定期备份的重要性MySQL的information_schema介绍

发表评论: