【笔记归档】mysql学习笔记

    mysql -uXXX -pXXX   #其中XXX为用户名和密码
                        #成功进入后提示符变为： mysql>
                        #可输入第一个例子  version(),current_date();  
                       【注意一定要在结尾有分号;】
                       【多行输入时在最后一行要有分号作为输入结束标志】
                       【SQL关键字大小写不敏感】
    mysql>exit          #关闭
    mysql>quit          #关闭
         
         >help          #帮助
    
    配置： 
          which mysqld  查看路径
          配置文件  ：   /etc/my.cnf

    use 库名            #mysql安装完成后默认建立mysql库
                         use mysql;     #可不加分号，use语句必须在一个单行上给出
                         输出：
                        +---------------------------+
                        | Tables_in_mysql           |
                        +---------------------------+
                        | columns_priv              |
                        | db                        |
                        | event                     |
                        | func                      |
                        | ...              |
                        | help_category             |
                        | help_keyword              |

     show databases;
     show tables;   
     
     select database();   # 找出当前选了哪个数据库
     
     select version();    # 产看mysql版本
     
     #查看mysql的连接权限
     select host,user from mysql.user order by user;
     #查看某主机用户有哪些权限
     show grants for bbpk@10.xx.xx.xx;
     # 查看mysql 当前连接
     > show processlist;

     DESC tablename；
     show columns from tablename;
     describe tablename;
     show create table d_fun.t_char_data;  #查看建表语句
     ##语法顺序
       select|distinct > from > where > group by > having > union > order by

    创建数据库
        mysql> CREATE DATABASE menagerie;  【Unix下，数据库名称是区分大小写的】
    使用数据库
        mysql> use     menagerie
        
    登陆时直接选择数据库
        shell> mysql -h host -u user -p menage    【menage为库名】 
                                             #远程访问mysql需要有权限
        Enter password: ********
        【注：也可在-p后直接写输入密码，不过这样不安全】
        
    授权：GRANT ALL PRIVILEGES ON `db%`.* TO 'conf'@'localhost' IDENTIFIED BY 'xxxxxxx';
          flush PRIVILEGES ;

    mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
        -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
    显示表结构
    mysql> DESCRIBE pet;
           DESC pet;

    创建一个文本文件“pet.txt”，每行包含一个记录，用定位符(tab)把值分开，每行数据顺序必须与表的列对应。
    空值可用NULL或 “\N”表示。
    将文本文件“pet.txt”装载到pet表中，使用这个命令：
    mysql> LOAD DATA LOCAL INFILE '/path/xxx.txt' INTO TABLE pet;
    
    请注意如果用Windows中的编辑器（使用\r\n做为行的结束符）创建文件，应使用：
    mysql> LOAD DATA LOCAL INFILE '/path/text.txt' INTO TABLE pet
        -> LINES TERMINATED BY '\r\n';

    mysql> insert into pet
        -> values('pp','diane','hamster','f','2006-03-30',NULL);

     select * from table where id>5 and id<10;
     select name from table where id=3 or id =9;  
     
     select DISTINCT name from pet;            #去重
     
     select name,birth from pet ORDER BY birth;      #分类，默认升序
     select name,birth from pet ORDER BY birth DESC;       #降序排列
     
     select * from basss limit 1\G    # \G  单列显示数据

     select name,CURDATE(),YEAR(CURDATE()),MONTH(CURDATE()),RIGHT(CURDATE(),5) 
     from pet ORDER BY name;
            # CURDATE() 当前日期 XXXX-XX-XX
            # RIGHT(str,num) 取str右边num个字符
            # MONTH(time) 取出月份
            # UNIX_TIMESTAMP('xxxx-xx-xx') 获取UNIX时间
            # CURTIME()  当前时间
            # NOW()   当前日期加时间  xxxx-xx-xx xx:xx:xx
            # DATE_FORMAT(date,format) 将日期转为特定格式
            # FROM_UNIXTIME(unix_timestamp) 将unix时间转为xxxx-xx-xx xx:xx:xx

     select from_unixtime(unix_timestamp(),"%Y-%m-%d %H:%i:%s");
                           #  %Y 年 xxxx
                              %y 年 xx
                              %m 月 数字形式  01..12
                              %M 月 名称形式 January,Decembe
                              %d 日 数字形式
                              %H 时 00..23
                              %h 时 01..12  结合 %p AM/PM
                              %i 分 00..60
                              %s 秒 

        select * from pat where name like 'b%';    # 用百分号%做匹配 表示任意个字符
        select * from pat where name like '___'    # 一个下划线’_‘ 表示一个字符
      
        select * from pat where name REGEXP '^b'   # 正则匹配 ，以b开头
        select * from pat where name REGEXP BINARY '^b'  #只匹配小写
        
        select * from pet where name REGEXP '^.{5}$'  # 匹配5个字符
        select iUin,max(dtEventTime) time from RoleLogin group by iUin having time <'2013-07-28';
                                         # having 写在group by之后

        select count(*) from pet;        # 取出表记录个数
        
        select owner,count(*) from pet GROUP BY owner;   #查看每个owner有多少个记录
        select owner,sex,count(*) from pet where sex IS NOT NULL GROUP BY owner,sex;  #按owner和sex分组统计

        select pet.name,(year(date)-year(birth)-(right(date,5)<right(birth,5))) AS age,remark
        from pet,event                                                            # AS 别名 如果忽略AS，该别名仍然成立
        where pet.name=event.name

         select name,score
         from student
         where score=(select max(score) from student);

         select name,score
         from student
         where score in (select score from stu_info where age < 12); 

         ###【注意】 in 中不支持limit,即上面括号的语句中不能有 limit
        #其他方法
        select name,score from student
        order by score DESC   #降序
        LIMIT 1;              #只取第一行

             # operand comparison_operator ANY (subquery)   #返回子查询中任一满足比较操作的值
            select s1 from t1 where s1 > ANY (select s1 from t2);

            # IN 其实是 = ANY 的别名
            # IN 中不能用LIMIT
            # operand IN (subquery)
            # 下面两句功能相同
            select s1 from t1 where s1 = ANY (select s1 from t2);
            select s1 from t1 where s1 IN (select s1 from t2);

            # operand comparison_operator SOME (subquery)
            # SOME 是ANY 的别名
            # 下面两句等同
            select s1 from t1 where s1 <> ANY (select s1 from t2);
            select s1 from t1 where s1 <> SOME (select s1 from t2);

            # operand comparison_operator ALL (subquery)
            # ALL必须接在一个比较操作符的后面，对于子查询返回的所有值，如果比较结果为真，则返回TURE
            select s1 from t1 where s1 > ALL (select s1 from t2);

        在SQL中 , where 关键字无法与某些函数一起使用， 因此用 HAVING语句对函数结果进行判断
        select Customer,SUM(OrderPrice) from Orders
        where Customer='Bush' OR Customer='Adams'
        having SUM(OrderPrice)>1500

       drop database if exists drop_database;

删除取到的第一个
       delete from Db.table
       where user='123'
       order by time
       limit 1;        

       truncate table FightTower;

       alter table tbPveAttack drop partition p_20140321;alter table tbPveAttack add  partition (partition  p_20140321 VALUES IN (735678));

       drop table if exists table_name;

        SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
        FROM information_schema.tables
        WHERE table_schema = 'YourDatabaseName';

        USE mpboy_web;
        SET FOREIGN_KEY_CHECKS = 0;
        DROP table if exists auth_group;
        DROP table if exists south_migrationhistory;
        SET FOREIGN_KEY_CHECKS = 1;

    update t_char_data set created_world_id=1 where aid=16268 or aid=136168;

      $ mysql < batch-file  # 基本结构 batch-file为mysql脚本
      $ mysql -h host -u user -pxxx <batch-file > mysql.out       # 加入参数，将结果输出到文件中
      $ mysql -u root -p xxx -N -e "select name from db.table";      # -e 或 --execute
        for i in $(ls);do mysql -ufun -pxxx d_fun -h gamedb.1.Qyouji.db -P10001 --default-character-set=utf8<$i; done                                                          #  -N skip-colum-names
      mysql -uxxx -pxx d_fun -h gamedb.1.Qyouji.db -P10001 --default-character-set=utf8 <reset.sql
      #注：批处理模式下的默认输出不包含列名

    :=                                   #冒号前变量为空或不存在，将等号后内容赋给它
    ||,OR,XOR
    &&,AND
    NOT
    BETWEEN,CASE,WHEN,THEN,ELSE
    =,<=>,>=,<=,<>,!=,IS,LIKE,REGEXP,IN  # <=> NULL-safe-equal   空值进行比较时给出正确结果
                                           select NULL<=>NULL;   结果为1
                                         # <>  不等于
                                           select '0.01'<>'.01'; 结果为1
    |
    &
    <<,>>
    -，+
    &，/,DIV,%,MOD
    ^
    -,~
    ！
    BINARY,COLLATE
    

    mysqldump -uxx -pxx --default-character-set=*** --single-transaction --skip-opt --create-option -R -q -B db_QQyxsq_logtool_1 >db_QQyxsq_logtool_1.sql
    mysqldump -uxx -pxx --default-character-set=*** --single-transaction --skip-opt --create-option -R -q -d -B db_QQyxsq_logdb_1 >db_QQyxsq_logtdb_1.schema
    mysqldump -uxx -pxxx -h www.chengxu.kr.db -P10000 db_kr_ptdl --skip-lock-tables
    格式：
          mysqldump [options] db_name [tbl_name ...]
          mysqldump [options] --databases db_name ...
          mysqldump [options] --all-databases
    帮助：
          mysqldump --help
    选项：
          --default-character-set=charset_name  选择字符集
          
          --add-drop-database   Add a DROP DATABASE statement before each CREATE DATABASE statement.
          
          --add-drop-table      Add a DROP TABLE statement before each CREATE TABLE statement.
          
          --all-databases, -A   Dump all tables in all databases.
          
          --skip-opt         跳过 opt的选项

          --databases, -B     导出某个库
          
          --ignore-table=db_name.tbl_name  忽略某个表
          
           --single-transaction  在执行导出前向服务器发送一个开始事务声明（START TRANSACTION SQL statement），仅对Innodb引擎的DB有效
          
          --opt  等于同时选  --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset
          
           --quick, -q   快速模式，用于导出数据量很大的表。
           It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set 
           and buffering it in memory before writing it out.
          
           --replace  Write REPLACE statements rather than INSERT statements. This option was added in MySQL 5.1.3.

           --routines, -R   Dump stored routines (functions and procedures) from the dumped databases. 
           
           --create-options   Include all MySQL-specific table options in the CREATE TABLE statements.           
          
           --no-data   -d

    安装好的Mysql首次登录后，
    mysql> update mysql.user 
        -> set password = password('密码') where User = 'root';
        -> flush privileges;