mysql 一些常用指令

登陆：

1 mysql -u root -p //登陆，输入root密码

退出登陆

1	`mysql>exit;`

mysql 为所有ip授权

1	`mysql>` `GRANT` `ALL` `PRIVILEGES` `ON` `.` `TO` `'root'@'%'` `IDENTIFIED` `BY` `'123456'` `WITH` `GRANT` `OPTION;`

为单一IP授权 xxx.xxx.xxx.xxx就是想要授权的IP，其实就是把%替换成想要授权的IP

1	`mysql>` `GRANT` `ALL` `PRIVILEGES` `ON` `.` `TO` `'root'@'xxx.xxx.xxx.xxx'` `IDENTIFIED` `BY` `'123456'` `WITH` `GRANT` `OPTION;`

查看SQL下有哪些数据库

mysql> show databases;

切换数据库

mysql> use 你的数据库名字;

显示当前数据库下所有的表

mysql> show tables;

查询数据库的编码格式

mysql> show create database banzhan_tplayer;//banzhan_tplayer数据库名

查询数据表的编码格式

mysql> show create table t_player;//t_player数据表名

查询数据表每一列的格式

mysql> show full columns from t_player;

自增字段必须是主键，否则建表报错

显示mysql本地配置选项的值：show global variables like 'wait_timeout'; 显示wait_timeout选项的值

/**SQL99标准的四大分类
    DDL语言：数据定义语言(用于定义数据的表结构)Data Definition Language
        创建数据表：create table 表名
        修改数据表：alter table 表名
        删除数据表：drop table 表名
    DML语言：数据操纵语言(用于操作数据表中的数据)DML - Data Mainpulation Language
        添加数据：insert
        修改数据：update
        删除数据：delete
    DQL语言：数据查询语言(专门用于数据的查询)DQL - Data Query Language
        查询数据：select
    DCL语言：数据控制语言（Data Control Language)
        grant 权限 to scott, remove 权限 from scott

*/


#查看
    SHOW DATABASES;        #显示有哪些数据库
    SHOW TABLES;            #查看当前数据库有哪些表
    SHOW CREATE TABLE studentinfo; #显示建表语句
    DESC studentinfo; #查看表结构
    
#切换到数据库mytest2
    USE tuling;

#删除
    DROP DATABASE IF EXISTS tuling; # 删除数据库
    DROP TABLE IF EXISTS usedel; # 删除表
    DELETE FROM usedel WHERE id=1; # 删除数据    
    
#创建
    /**创建数据库
    *    1. 数据库名不要使用中文，
    * 2. 由于数据库将来存储⼀些⾮ascii码的字符，所以务必指定字符编码，⼀般都是指定utf-8编码
    * 3. charset指定utf8 COLLATE 指定utf8_general_ci            
    *    4. mysql中⽂字符集是utf8,不是utf-8
    */
    CREATE DATABASE IF NOT EXISTS tuling DEFAULT charset utf8 COLLATE utf8_general_ci;
    
    /**创建表
    * int :整型；
    * float/double:浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值：999.99
    * decimal:浮点型，不会出现精度缺失问题，比如金钱
    * char:固定长度字符串类型，最大长度：char(255)
    * varchar:可变长度字符串类型，最大长度:varchar(65535)
    * text(clob):字符串类型，存储超大文本
    * blob:字节类型，最大4G
    * date:日期类型；格式：yyyy-MM-dd
    * time:时间类型；格式：hh:mm:ss
    * timestamp:时间戳
    * datetime:
    * 
    * 约束：
    *     默认值：default
    *   非空约束：not null
    *     唯一约束：unique
    *     主键约束（非空+唯一）：primary key
    *     外键约束：foreign key
    */
    CREATE TABLE IF NOT EXISTS studentinfo (
        id INT(4) PRIMARY KEY auto_increment,
        name VARCHAR(32),
        sex VARCHAR(4) DEFAULT '男',
        age INT(4) DEFAULT 20,
        ys CHAR(16) DEFAULT '',
        class char(8),
        date DATE,
        tel CHAR(16),
        jg CHAR(32)
    );
    
    #创建表
    CREATE TABLE IF NOT EXISTS usedel(
        id INT(4) PRIMARY KEY auto_increment,
        name VARCHAR(16),
        sex VARCHAR(8),
        age FLOAT(4,1)
    );
    
#修改表结构(ALTER:改变 变更)
    # 添加表字段
    ALTER TABLE usedel ADD(
        birthday DATE
    );
    #添加主键
    ALTER TABLE usedel ADD PRIMARY KEY (id);
    /**ALTER 添加、删除约束
        1.主键约束
            添加:alter table  table_name add primary key (字段)
            删除:alter table table_name drop primary key
        2.非空约束
            添加:alter  table table_name modify 列名 数据类型  not null 
            删除:alter table table_name modify 列名 数据类型 null
        3.唯一约束
            添加:alter table table_name add unique 约束名（字段）
            删除:alter table table_name drop key 约束名
        4.自动增长
            添加:alter table table_name  modify 列名 int  auto_increment
            删除:alter table table_name modify 列名 int  
        5.外键约束
            添加:alter table table_name add constraint 约束名 foreign key(外键列) references 主键表（主键列）
            删除:
                第一步:删除外键
                    alter table table_name drop foreign key 约束名
                第二步:删除索引
                    alter  table table_name drop index 索引名
                    约束名和索引名一样
        6.默认值
            添加:alter table table_name alter 列名  set default '值'
    */
    
    /** 修改表字段
    * 并不能随意的更改已有列的数据类型。尤其是表中已经有数据了
    * A：兼容类型：⻓度可以从⼩到⼤，不能已有的数据越界。
    * B：不兼容类型：varchar-->int，更改失败
    */
    ALTER TABLE usedel MODIFY name VARCHAR(32);

    # 删除表字段
    ALTER TABLE usedel DROP birthday;
    # 删除主键
    ALTER TABLE usedel DROP PRIMARY KEY;

    
    
#插入
    /**
    * 全列插入：如果有所有列都要插入数据，那么可以省略列的名字
    * 对自动增长的数据，在全列插入的时候需要占位处理，一般使用0来占位，但是最终的值以实际为准；
    * 缺省插入：如果有某一个或一些字段没有数值，那么就要写清楚列名和值。
    * 插入的时候，not null和primary key的列必须赋值，其他的列根据情况来赋值，如果没有赋值则会使用默认值
    */
    INSERT INTO studentinfo(name,sex,age,ys,class,date,tel,jg) VALUES 
        ('张晓晴','女',20,'广告','0801','2007-12-5', '13245678954','内蒙古'),
        ('于洋','男',22,'软工','0708','2007-7-15', '13145378954','辽宁'),
        ('余婷','女',21,'环艺','0709','2007-9-3', '15945623954','江西'),
        ('任亮','男',22,'英语','0802','2008-2-22', '17545378954','山东'),
        ('高亮','男',22,'软工','0709','2007-8-26', '15043678254','山东'),
        ('龚建刚','男',23,'软工','0803','2008-2-26', '13143673944','山西');
        
#修改
        /**where后是修改条件：为true，才会修改数据。
        * 1. 条件必须是boolean类型的值或表达式；
        * 2.运算符：
        *         =，数值相等
        *         !=，<>，数值不等
        *         between ... and，区间
        *         >
        *         <
        *         >=
        *         <=
        *         or
        *         and
        *         in(值1，值2，值3.。)
        *         is null
        *         is not null
        * 3.is null不是=null(永远是false)
        *         #select * from student where age=NULL; #age=null 永远是false 应改为 'age IS NULL'
        */
        UPDATE studentinfo SET age=25, ys='数学' WHERE id=3;
        
#查询
        #简单查询
                #全部查询
                SELECT * FROM studentinfo;
                #查询指定列
                SELECT name,sex,age FROM studentinfo;
                
                #起别名
                SELECT name as '姓名', sex as '性别', age as '年龄' FROM studentinfo;
                SELECT name '姓名', sex '性别', age '年龄' FROM studentinfo; #省略 as
                SELECT s.`name`, s.sex, s.age FROM studentinfo s; #给表名起别名
                
                
                #字符串类型可以做连续运算
                SELECT CONCAT('我的名字是：',s.`name`,'  性别：',s.sex) FROM studentinfo s;
                
                #去重 [DISTINCT]:distinct 列名1，列名2，⼀⾏数据都相同，才会被认为是重复的数据，去除。
                SELECT DISTINCT s.sex FROM studentinfo s;
                SELECT DISTINCT sex, age FROM studentinfo;
        
        #条件查询
                /**
                * 比较运算符：=，!=,<>,>,<,>=,<=
                * 逻辑运算符：and ，or， not
                * 范围：between and，in,not in
                * null：is null，is not null
                */
                SELECT * FROM studentinfo WHERE ys='软工';
                SELECT * FROM studentinfo WHERE age>20;
                SELECT * FROM studentinfo WHERE date BETWEEN '2007-9-1' and '2008-9-1';
                SELECT * FROM studentinfo WHERE sex='男' and jg='山东';
        
        #模糊查询
            /**
            * %：匹配0-多个任意的字符
            * _：匹配1个任意字符
            */
                SELECT * FROM studentinfo WHERE ys LIKE '%工';
                SELECT * FROM studentinfo WHERE tel LIKE '__1%';# 手机号第三位是'1'
    
        #排序：ORDER BY
            # ASC 升序（默认）
            # DESC 降序
            SELECT * FROM studentinfo ORDER BY tel;
            SELECT * FROM studentinfo ORDER BY tel DESC;
    
        #统计函数：也叫聚合函数，通常⽤于求整个表中某列的数据的：sum()总和，avg()平均值，max()最大值，min()最小值, count(*/主键)
            SELECT count(*) '男生总数' FROM studentinfo WHERE sex='男';
            SELECT max(age) '最大年龄' FROM studentinfo;
            SELECT min(age) '最小年龄' FROM studentinfo;
            SELECT AVG(age) '平均年龄' FROM studentinfo;
    
        #分组：[GROUP BY 列名]，按照指定的列进行分组，值相同的会分在一组。
            SELECT class,count(*) from studentinfo GROUP BY class;
            #group by 后面也可以跟多个列进行分组，表示这些列都相同的时候在一组
            SELECT sex,jg, COUNT(*) FROM studentinfo GROUP BY sex,jg;
    
            /**having⼦句和where ⼦句：都是⽤于限定条件
            * 对比：
            *     1. where 和having后⾯都是跟条件
            *        2. where是对表中数据进行原始筛选
            *        3. having是对group by 的结果的二次筛选
            *        4. having必须配合group by使用，一般也会跟着聚合函数一起使用
            *        5. 可以先有where,后面跟着group by和having
            *    区别和结论：
            *        1. 语法上：在having中使⽤组函数（max,min,avg,count...),而where后不能用组函数。
            *        2. 执⾏上：where是先过滤再分组。having是先分组再过滤。
            */
            #分组后限定查询：having
            SELECT sex,jg, COUNT(*) FROM studentinfo GROUP BY sex,jg HAVING sex='男';
            SELECT sex,jg, COUNT(*) FROM studentinfo GROUP BY sex,jg HAVING sex='男' and COUNT(*)=1;
    
        #分页 [LIMIT start, count] 是mysql特有的方言
            SELECT * FROM studentinfo LIMIT 0,3;
    
        #多表联查
            # 两张表在连接查询的时候，如果没有连接条件，那么会产⽣笛卡尔积（冗余数据）
            SELECT student.*, studentinfo.* FROM student,studentinfo;
    
            #内连接
                #方言内联
                SELECT * FROM student s, studentinfo si WHERE s.age=si.age;
                #标准内联
                SELECT * FROM student s INNER JOIN studentinfo si ON s.age=si.age;
    
            #左外连接:left join 是left outer join的简写 
                /**
                * 因为内连接的查询结果，并不是所有的数据，⽽是满⾜规则的数据。
                * 左外链接，右外连接是为了补充内连接的查询结果的。
                * 左表记录无论是否满足条件都会查询出来，⽽右表只有满足条件才能查询出来。
                * 左表中不满足条件的记录，右表部分都为NULL
                */
                SELECT s.*, si.*  FROM student s LEFT JOIN studentinfo si ON s.age=si.age;
                SELECT * FROM student s LEFT OUTER JOIN studentinfo si ON s.age=si.age;
            #右外连接
                SELECT * FROM student s RIGHT JOIN studentinfo si ON s.age=si.age;
    
        #子查询
            /**注意事项
            *    1，子查询必须在()里
            *    2，在子查询中不能使用order by子句
            *    3，子查询可以再嵌套子查询，最多不能超过255层
            * 子查询：单行子查询，多行子查询
            *        1. 单行子查询
            *            子查询的结果是单行数据
            *            在where条件后，需要配合单行运算符：>,<,>=,<=,!=,=
            *        2. 多行子查询
            *            子查询的结果是多行数据
            */
            
            # 比任亮岁数大的员工信息
            #SELECT age FROM studentinfo WHERE name='任亮';
            SELECT * FROM studentinfo WHERE age > (SELECT age FROM studentinfo WHERE name='任亮');
    
            #SELECT MIN(age) FROM studentinfo;
            #SELECT MAX(age) FROM studentinfo; 
            SELECT * FROM studentinfo WHERE age>(SELECT MIN(age) FROM studentinfo) AND age<(SELECT MAX(age) FROM studentinfo);
    
            #山东岁数最大的人的信息
            #SELECT MAX(age) FROM studentinfo WHERE jg='山东';
            SELECT * FROM studentinfo WHERE age=(SELECT MAX(age) FROM studentinfo WHERE jg='山东');
            
    
    
#外键约束：保证数据的完整性和有效性    
        #建表语法：CONSTRAINT 约束名 FOREIGN KEY 子表名 (子表字段名) REFERENCES 父表名 (父表唯一字段名)
        
        #创建class 主表
        CREATE TABLE class(
            classno INT(4) PRIMARY KEY,
            classname VARCHAR(16)
        );
        INSERT INTO class(classno, classname) VALUES(1,'java'),(2,'python'),(3,'html5');
        
        #创建class 子表
        CREATE TABLE student(
            sid INT(4) PRIMARY KEY auto_increment,
            sname VARCHAR(32),
            age INT(3),
            sex VARCHAR(3),
            classno INT(4)
            
            #创建时添加表级外键约束
            #CONSTRAINT fk_classno FOREIGN KEY (classno) REFERENCES class (classno)
        );
        
        #删除student表
        DROP TABLE student;
        
      #外键设置语法(CONSTRAINT:限制，约束)(REFERENCES:参考)
        ALTER TABLE student ADD CONSTRAINT fk_classno FOREIGN KEY student (classno) REFERENCES class (classno);

        #插入student表数据
        INSERT INTO student(sid,sname, age, classno) VALUES 
        (1,'张三',20,1),
        (2,'李四',34,2),
        (3,'王五',32,3);
    
        #插入语句会有外键报错，因为主表 classno没有4
        INSERT INTO student(sid,sname, age, classno) VALUES (4,'张三3',20,4);
        #删除父表中的数据也是会报错的，因为子表中有外键关联@1
        DELETE FROM class WHERE classno=1;
        
        #要解决@1的由于子类外键约束导致无法删除问题，两个外键策略：
        #1.手动置空
            UPDATE student SET classno=null WHERE classno = 1;#先将子表关联数据设置为null
            DELETE FROM class WHERE classno=1;#再删除
        
        #2.级联操作 on update on delete (CASCADE:是选项)
            #开启级联后，更新/删除一个主表的主键值（唯一字段），系统会相应的更新/删除所有从表匹配的外键值。
            ALTER TABLE student DROP FOREIGN KEY fk_classno;
            ALTER TABLE student ADD CONSTRAINT fk_classno FOREIGN KEY student(classno) REFERENCES class(classno) ON UPDATE CASCADE ON DELETE CASCADE;
            DELETE FROM class WHERE classno=2;#此时删除父表classno=2的项，子表对应也会删除