MySQL数据库SQL语句基本操作

一、用户管理：

创建用户：

create user '用户名'@'IP地址' identified by '密码';

删除用户：

drop user '用户名'@'IP地址';

修改用户：

rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';

修改密码：

set password for '用户名'@'IP地址' = Password('新密码')

 

二、库相关操作

查看所有数据库： 

SHOW DATABASES;

创建数据库 ：

CREATE DATABASE cnblog（数据库名称,下同） CHARSET=UTF8; 

删除数据库 ：

DROP DATABASE cnblog;

切换数据库 ：

USE cnblog；

查看当前数据库 ：

SELECT DATABASE();

 

图例：

 

 

三、表相关操作

查看数据库中的所有表：SHOW TABLES;

创建表：

格式（中括号中可以不填）：CREATE TABLE 表名（列名 数据类型 是否可以为空）[ ENGINE= INNODB DEFAULT CHARSET=UTF8 ]

1.数字
     int[unsigned]  可以保存2的32次方大小的数，若有正负号，表示正负2的31次方
    bigint可以表示很大的数
2.字符串
    char(10）定长 若数据没有10长度会用空格补缺
    varchar（10）变长  数据是多少就是多少
    两者都只能表示255字节长度的字符串，
3.时间
    一般用DATATIME 显示XXXX-MM-DD H-i-s
4.枚举
    enum 插入数据时只能从Enum选项中选取
 CREATE TABLE shirts (
                    name VARCHAR(40),
                    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
                );
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small')
    set    插入值只能是set选项中的组合
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
5.文件
如果文件过长，或者是图片，存取的是文件的url位置

            NOT NULL    -不能为空
            NULL           -可为空

    CREATE TABLE 表名(
         num INT NOT NULL DEFAULT 6,
        ) ;     
     # 插入的这个数默认为6

1. 自增列为主键的情况    
CREATE TABLE 表名（
           id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        ）    
2. 自增列为索引
CREATE TABLE 表名（
           id  INT NOT NULL AUTO_INCREMENT,
           index（id）      
        ）     

1.主键可以为单个字段
CREATE TABLE 表名（
           id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        ） 

2.主键可以为组合
CREATE TABLE 表名（
           firstname  VARCHAR(20) NOT NULL,
           lastname  VARCHAR(20) NOT NULL,
           PRIMARY KEY (firstname,lastname)
        ） 

creat table color(
                nid int not null primary key,
                name char(16) not null
            )

create table fruit(
                nid int not null primary key,
                smt char(32) null ,
                color_id int not null,
                constraint fk_cc foreign key (color_id) references color(nid)
            )

SHOW CREATE TABLE 表名

DESC 表名

 

 

修改表：

添加列：ALTER TABLE 表名 ADD 列名 类型
删除列：ALTER TABLE 表名 DROP COLUMN 列名
修改列：ALTER TABLE 表名 MODIFY COLUMN 列名 类型;  -- 类型
修改外键：ALTER TABLE 从表 ADD CONSTRAINT 外键名称（形如：FK_从表_主表）FOREIGN KEY 从表(外键字段) REFERENCES 主表(主键字段);
删除外键：ALTER TABLE 表名 DROP FOREIGN KEY 外键名称

 

 INSERT INTO 表名(列名，列名) VALUES (值，值)；
eg: INSERT INTO student(name,tel) VALUES(‘小明’,‘13111311313’)；

DELETE FROM 表名 WHERE 条件；
eg：DELETE FROM STUDENT WHERE sid=‘1001’ AND class=‘二班’

UPDATE 表名 SET 列=值 WHERE 条件
eg：UPDATE student SET points = 80 WHERE class=‘三班’

SELECT * FROM 表名
eg：SELECT * FROM STUDENT WHERE id>1
      SELECT id,gender FROM student WHERE points>80

ALTER TABLE 表名 AUTO_INCREMENT=10
表示主键的值从10开始递增

 添加一个条件语句（语法case when ...then.... else .... end）

select max(num),min(num) case when min(num)<60 then 不及格 else min(num) end from score

最低分低于60分的会显示不及格，如果高于60分会显示最低是多少。

按条件查询（关键词where）：
SELECT * FROM students WHERE age>10;
SELECT * FROM students WHERE id in (1,3,5);
SELECT * FROM students WHERE id BETWEEN 5 AND 16；(这是个闭区间)
SELECT * FROM students WHERE age=30 AND firstname=‘张'；
SELECT * FROM students WHERE id in (SELECT id FROM male_students)(先从第二张表取id，再用此id查第一张表数据)

通配符查询（关键词like）：
SELECT * FROM students WHERE name  like '%平’；（查找名字以平结尾的）
SELECT * FROM students WHERE name  like '_平’；（查找名字以平结尾的,只能为两个字）
SELECT * FROM students WHERE name  like '%平%’；（查找名字中含有平的）

分页查找（关键字limit）:
SELECT * FROM students limit 10；      取10条数据查看
SELECT * FROM students limit 20,10    从第20条开始向后取10条
SELECT * FROM students limit 5 offset 7     从第7条开始向后取5条

排序 （关键词 ORDER BY）
SELECT * FROM students ORDER BY id DESC；降序排列
SELECT * FROM students ORDER BY id ASC；升序排列

分组（关键字group by 和 having):
SELECT count(id) as mount, class_id FROM students GROUP BY cl

ass_id HAVING count(id) > 20;(注意：where语句不能接聚合函数的，如果要对分组后的结果进行二次筛选要用having)

连表操作（关键词LEFT JOIN ...ON）:
SELECT student.name FROM student LEFT JOIN class ON student.cid = class.id 

多张表相连：
SELECT student.name，teacher.id FROM student 
LEFT JOIN class ON student.cid = class.id 
LEFT JOIN teacher on class.teacher = teacher.id

LEFT JOIN 与 RIGHT JOIN区别：会以LEFT或RIGHT关联的表作为主显示表，从表没有数据会显示NULL

INNER JOIN 则会隐藏NULL的字段

注意：多张表关联时要引用字段，字段所在的表必须是关联好的不能凭空引入一个字段

 

索引：帮助我们快速的找到数据

索引文件存储的某种格式：

1.hash， 单值取值快， 但取多项数据时速度较慢

2.btree，单值没有hash速度快，但单次取数据查询次数少，对于多项数据速度较快

常见索引：
普通索引：加速查找
唯一索引：加速查找 + 不能重复
主键索引：加速查找 + 不能为空 + 不能重复
联合索引：
        - 联合主键索引
        - 联合唯一索引
        - 联合普通索引

普通索引：

生成表时即创建普通索引
CREATE TABLE index_1(
    nid int not null auto_increment primary key,
    name char(32) not null,
    email char(32) not null,
    extra text,
    index ix_name(name)
)

SQL语句创建索引
CREATE INDEX 索引名 on 表名（字段）

删除索引
DROP 索引名 on 表名；

查看索引
show index from 表名；

索引的缺点：
1.需要保存额外的索引文件
2.更新修改新建速度慢
3.要命中索引
    - SELECT * FROM student WHERE name='一休', 命中
    - SELECT * FROM student WHERE name LIKE '一休', 未命中

唯一索引：
创建表生成唯一索引
CREATE TABLE index_2(
        nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)  -- 将name字段设置为唯一索引
)


创建唯一索引
CREATE unique INDEX 索引名 on 表名（字段）；

删除唯一索引
DROP unique INDEX 索引名 on 表名

主键索引
create table index_3(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

OR

create table index_4(
    nid int not null auto_increment,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    primary key(ni1),
    index ix_name (name)
)


创建主键
ALTER TABLE 表名 add PRIMARY KEY(列名)

删除主键
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;


联合索引：
创建联合索引

create table index_5(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text
)
create index ix_name_email on in3(name,email);


删除联合索引
- drop unique index 索引名称 on 表名

在索引文件中直接获得数据
SELECT name FROM tb WHERE name='zhang'

把多个单列索引合并使用
SELECT * FROM tb WHERE name=‘xxx’ AND id=3；

在组合索引中，遵循最左前缀原则，比如索引为（name,class,tel）
查询条件为name时走索引，(name,class),(name,tel)都会走索引
但是如果是class或tel开头不走索引

1.like
select * from tb1 where name like '%cn';

2.使用函数
SELECT * FROM tb WHERE  reverse(name) = 'zhang'

3.or
SELECT * FROM tb WHERE nid=1 or email='nn@qq.com'
注意：当or条件未建立索引才会失效或者后面接and条件是索引，也会忽略or

4.类型不一致
SELECT * FROM tb WHERE sid='999';(int类型却用字符串查找)

5.！=
SELECT * FROM tb WHERE sid != 99;
注意：如果条件是主键依然走索引

6.>
SELECT * FROM tb WHERE sid>99;
注意：如果条件是主键或整数类型会走索引

7.order by
SELECT name FROM tb order by sid;
注意：如果sid是索引 name非索引 则不走索引
如果order by接主键依然走索引

8.最左前缀匹配

1.避免使用SELECT *
2.创建表时尽量用char代替varchar
3.建表时固定长度的字段往前排
4.索引散列（重复少）不适合建索引（性别）
5.尽量使用短索引（text字段类型格式：text（18）以前18个字符建索引）
6.连表操作是条件类型需一直

作用：不执行sql语句，MySQL预估SQL语句执行时间
 id
        查询顺序标识
            如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;
            +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
            | id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
            +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
            |  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    9 | NULL        |
            |  2 | DERIVED     | tb1        | range | PRIMARY       | PRIMARY | 8       | NULL |    9 | Using where |
            +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
        特别的：如果使用union连接气值可能为null


    select_type
        查询类型
            SIMPLE          简单查询
            PRIMARY         最外层查询
            SUBQUERY        映射为子查询
            DERIVED         子查询
            UNION           联合
            UNION RESULT    使用联合的结果
            ...
    table
        正在访问的表名


    type
        查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
            ALL             全表扫描，对于数据表从头到尾找一遍
                            select * from tb1;
                            特别的：如果有limit限制，则找到之后就不在继续向下扫描
                                   select * from tb1 where email = 'seven@live.com'
                                   select * from tb1 where email = 'seven@live.com' limit 1;
                                   虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。

            INDEX           全索引扫描，对索引从头到尾找一遍
                            select nid from tb1;

            RANGE          对索引列进行范围查找
                            select *  from tb1 where name < 'alex';
                            PS:
                                between and
                                in
                                >   >=  <   <=  操作
                                注意：!= 和 > 符号


            INDEX_MERGE     合并索引，使用多个单列索引搜索
                            select *  from tb1 where name = 'alex' or nid in (11,22,33);

            REF             根据索引查找一个或多个值
                            select *  from tb1 where name = 'seven';

            EQ_REF          连接时使用primary key 或 unique类型
                            select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;



            CONST           常量
                            表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
                            select nid from tb1 where nid = 2 ;

            SYSTEM          系统
                            表仅有一行(=系统表)。这是const联接类型的一个特例。
                            select * from (select nid from tb1 where nid = 1) as A;
    possible_keys
        可能使用的索引

    key
        真实使用的

    key_len
        MySQL中使用索引字节长度

    rows
        mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值

    extra
        该列包含MySQL解决查询的详细信息
        “Using index”
            此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
        “Using where”
            这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
        “Using temporary”
            这意味着mysql在对查询结果排序时会使用一个临时表。
        “Using filesort”
            这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
        “Range checked for each record(index map: N)”
            这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。

分页：
最常见的分页基于 SELECT * FROM text_tb limit 0,10
这样的分页当数据量很大时（例如千万级别的数据量），很耗费时间，解决方法有以下几点：
1. 只给浏览用户展示固定页数，比如200页，300页，减轻mysql检索压力
2. 用索引表（覆盖索引）
    SELECT * FROM text_tb WHERE id in(SELECT id FROM text_tb LIMIT 1000000,10)
3.记录当前页最小id（比如150000）和最大id（150010）
    下一页: SELECT * FROM text_tb WHERE id > max_id LIMIT 10;
    上一页：SELECT * FROM text_tb WHERE id < min_id ORDER BY id DESC LIMIT 10;
4.给URL上的页数信息加密，无法直接访问到较大页数

注意：
1.id 不一定是连续的，所以不能用id bewteen xx and xx 来进行检索
2.中间页数的思路，根据前页的id 计算要跳到的页数的id，取到为止，倒序排列，并取头10个作为limit
select * from userinfo where id in (
    select id from (select id from userinfo where id > max_id limit 30) as N order by N.id desc limit 10
)