美文网首页
mysql常用的基本操作

mysql常用的基本操作

作者: 莫辜负自己的一世韶光 | 来源:发表于2019-03-15 12:07 被阅读0次

    创建新的用户

    用户的信息都保存在数据库的库名为mysql的user表中.而root和123456是默认创建的用户.
    如果我们要创建新的用户,也会保存到当前的user表中.


    数据库常见操作

    数据库中常见的数据类型

    bit[(M)]
                二进制位(101001),m表示二进制位的长度(1-64),默认m=1
    
            tinyint[(m)] [unsigned] [zerofill]
    
                小整数,数据类型用于保存一些范围的整数数值范围:
                有符号:
                    -128 ~ 127.
                无符号:
    ~ 255
    
                特别的: MySQL中无布尔值,使用tinyint(1)构造。
    
            int[(m)][unsigned][zerofill]
    
                整数,数据类型用于保存一些范围的整数数值范围:
                    有符号:
                        -2147483648 ~ 2147483647
                    无符号:
    ~ 4294967295
    
                特别的:整数类型中的m仅用于显示,对存储范围无限制。例如: int(5),当插入数据2时,select 时数据显示为: 00002
    
            bigint[(m)][unsigned][zerofill]
                大整数,数据类型用于保存一些范围的整数数值范围:
                    有符号:
                        -9223372036854775808 ~ 9223372036854775807
                    无符号:
     ~  18446744073709551615
    
            decimal[(m[,d])] [unsigned] [zerofill]
                准确的小数值,m是数字总个数(负号不算),d是小数点后个数。 m最大值为65,d最大值为30。
    
                特别的:对于精确数值计算时需要用此类型
                       decaimal能够存储精确值的原因在于其内部按照字符串存储。
    
            FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
                单精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。
                    无符号:
                        -3.402823466E+38 to -1.175494351E-38,
                        1.175494351E-38 to 3.402823466E+38
                    有符号:
                        1.175494351E-38 to 3.402823466E+38
    
                **** 数值越大,越不准确 ****
    
            DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
                双精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。
    
                    无符号:
                        -1.7976931348623157E+308 to -2.2250738585072014E-308
                        2.2250738585072014E-308 to 1.7976931348623157E+308
                    有符号:
                        2.2250738585072014E-308 to 1.7976931348623157E+308
                **** 数值越大,越不准确 ****
    
    
            char (m)
                char数据类型用于表示固定长度的字符串,可以包含最多达255个字符。其中m代表字符串的长度。
                PS: 即使数据小于m长度,也会占用m长度
                varchar(m)
                varchars数据类型用于变长的字符串,可以包含最多达255个字符。其中m代表该数据类型所允许保存的
                字符串的最大长度,只要长度小于该最大值的字符串都可以被保存在该数据类型中。
    
                注:虽然varchar使用起来较为灵活,但是从整个系统的性能角度来说,char数据类型的处理速度更快,
                      有时甚至可以超出varchar处理速度的50%。因此,用户在设计数据库时应当综合考虑各方面的因素,
                      以求达到最佳的平衡
    
            text
                text数据类型用于保存变长的大字符串,可以组多到65535 (2**16 − 1)个字符。
    
            mediumtext
                A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
    
            longtext
                A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.
    
    
            enum
                枚举类型,
                An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
                示例:
                    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
                集合类型
                A SET column can have a maximum of 64 distinct members.
                示例:
                    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');
    
            DATE
                YYYY-MM-DD(1000-01-01/9999-12-31)
    
            TIME
                HH:MM:SS('-838:59:59'/'838:59:59')
    
            YEAR
                YYYY(1901/2155)
    
            DATETIME
    
                YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59    Y)
    
            TIMESTAMP
    
                YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 年某时)
    

    修改表的列(字段)

            修改表(修改列,以及列的类型和约束):
                1. 添加列:
                       alter(不是alert) table 表名  add  列名 类型;
                       alter(不是alert) table t1 add  age int default 0;(默认添加到最后一列)
                       alter(不是alert) table t1 add  address char(12) first; (添加到第一列)
                       alter(不是alert) table t1 add  phone after id; (在某个列的后面添加一列)
    
                2. 删除列
                       alter table 表名 drop 列名;
                       alter table t1 drop phone;
    
                3. 修改列
                        alter table 表名 modify column 列名 类型; # 只修改类型,使用modify'
                        alter table 表名 change column 原来的列名 新列名 类型; # 列名和类型同时修改使用change
                        alter table t1 change column name my_name varchar(20);
                4. 添加主键
                        alter table 表名 add primary key(列名)
                        alter table t1 add primary key(id);
    
                5.删除主键
                        alter table 表名 drop primary key(id);
                        alter table 表名 modify 列名 int ,drop primary key;
    
                        alter table t1 drop primary key ;
                        alter table t1 modify id int ,drop primary key;
                6.添加外键
                        alter table 从表  add constraint 从键名称 foreign_key 从表(外键字段) references 主表(主键字段);
                  删除外键:
                        alter table 表名 drop foreign_key 外键名称
    
                7.修改默认值:
                    alter table t1 alter age set default 1000;
                  删除默认值
                    alter table t1 alter age drop default;
    

    表内容操作

    1. 插入数据(insert into 表名(字段,字段) values(值,值))
            insert into tb1(nid,name) values(1,'fioman');
    
            2. 查询操作(select 字段,字段 表名(字段,字段) values(值,值))
            select * from tb1;
    
            3. 增
            insert into 表名(列名,列名) values(值,值)
            insert into 表名(列名,列名) values(值,值),(值,值)
            insert into 表名(列名,列名) select (列名,列名) from 表
    
            4. 删
            delete from 表
            delete from 表 where id = 1 and name = 'alex'
    
            5. 改
            update 表 set name = 'jingjing' where id > 1;
    
            6. 条件
            select * from 表 where id > 1 and name != 'alex' and age = 18;
            select * from 表 where id between 5 and 16;
            select * from 表 where id not in (11,22,33)
            select * from 表 where id in (select nid from 表2)
    
            7.通配符
            select * from 表 where name like 'ale%' ale开头的所有的字符串(%表示任意多个字符)
            select * from 表 where name like 'ale_' ale开头的后面加一个字符(一个字符)
    
            8.限制
            select * from 表 limit 5;  前5行
            select * from 表 limit 4,5; 从第四行开始(不包括) 5行到9行的数据
            select * from 表 limit 5 offset 4  - 从第四行开始的5行
    
            9.排序
            select * from 表 order by 列 asc  升序
            select * from 表 order by 列 desc 降序
            select * from 表 order by 列1 desc,列2 asc
    
            10.分组
            select num from 表 group by num;
            select num ,nid from 表 group by num,nid;
            select num nid from 表 where nid > 10 group by num,nid order nid desc;
            select num nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid;
    
            select num from 表 group by num having max(id) > 10
            特别的group by 必须在where之后,order by 之前
    
            11.连表
                无对应关系则不显示
                select A.num ,A.name B.name from A,B where A.nid=B.nid
    
                无对应关系则不显示
                select A.num,A.name,B.name from A inner join B on A.nid=B.nid
    
                A表所有显示,如果B中午对应关系,则值为null
                select A.num,A.name,B.name from A left join B on A.nid = B.nid
    
                B表所有显示,如果B中午对应关系,则值为null
                select A.num,A.name,B.name from A right join B on A.nid = B.nid
    
            12.组合
                组合,自动处理重合
                select nickname from A union select name from B
                组合,不处理重合
                select nickname from A union all select name from B
    

    理解MySql的内连接,左外连接,右外连接,全连接

    首先创建两张表a_tableb_table,关联字段a_talbe.a_id和b_table.b_id

    CREATE TABLE `a_table` (
      `a_id` int(11) DEFAULT NULL,
      `a_name` varchar(10) DEFAULT NULL,
      `a_part` varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    
    
    CREATE TABLE `b_table` (
      `b_id` int(11) DEFAULT NULL,
      `b_name` varchar(10) DEFAULT NULL,
      `b_part` varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    

    表测试数据


    内连接
    关键字: inner join on 是显示的内连接,同时用where子句的隐私的内连接.它的重点是只查找匹配的行.
    语句

    select * from a_table a inner join b_table b where a.a_id=b.b_id

    左连接(左外连接)
    关键字: left join 表 on 条件/ left outer join on
    语句

    select * from a_table a left join b_table on a.a_id=b.b_id


    说明: 左外连接左表中的内容会全部显示出来,而右表中的内容只显示匹配的内容,而至于没有匹配的内容一律用null来填充

    右连接(右外连接)
    关键字:right join 表 on 条件/rigth outer join 表 on
    语句:

    select * from a_table a right join b_table b on a.a_id=b.b_id;


    right join是right outer join的简写,它的全称是右外连接,是外连接中的一种。
    与左(外)连接相反,右(外)连接,左表(a_table)只会显示符合搜索条件的记录,而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

    交叉连接

    SQL语句查询的基本原理

    单表查询
    根据where条件过滤掉表中的记录,形成中间表(这个中间表用户是不可见的).然后根据select的选择列进行返回最终结果.

    两表连接查询
    对两表求积(笛卡尔积)并用ON条件和连接连接类型进行过滤形成中间表;然后根据WHERE条件过滤中间表的记录,并根据SELECT指定的列返回查询结果。

    多表连接查询
    先对第一个和第二个表按照两表连接做查询,然后用查询结果和第三个表做连接查询,以此类推,直到所有的表都连接上为止,最终形成一个中间的结果表,然后根据WHERE条件过滤中间表的记录,并根据SELECT指定的列返回查询结果。
    理解SQL查询的过程是进行SQL优化的理论依据。

    on后面的条件和where条件的区别

    ON条件:是过滤两个链接表笛卡尔积形成中间表的约束条件。
    WHERE条件:在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中,是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
    从这里可以看出,将WHERE条件移入ON后面是不恰当的。推荐的做法是:
    ON只进行连接操作,WHERE只过滤中间表的记录。

    数据库常用的操作:
    1.创建数据库:

    create database db_name;

    2.创建并设置字符编码

    create database db_name character set utf8;

    3.查看数据库的字符集编码:

    show variables like 'character_set_database'

    4.修改字符集编码

    alter db_name character set utf8

    数据表常见的操作:
    1.创建一个表

    create table tb_name(id int,name varchar(20)) ;
    注意在创建表的时候,不要添加记录,添加记录用insert into

    2.删除表

    drop table tb_name;

    3插入数据到表中

    insert into tb_name(id,name) values(1,'a'),(2,'b')

    查询表

    select * from tb_name where 查询条件

    添加一个字段(列)

    alter table tb_name add age int not null default 10;

    删除列

    alter table tb_name drop column age;

    修改某一列的字段类型

    alter table tb_name modify age varchar(50)

    更新某一列的数据

    update table tb_name set age = 100 where id =1;

    约束
    1) 约束的种类
    primary key(主键约束 唯一且不为null)
    default(默认值约束)
    not null (非空约束)
    unique(唯一约束,可以为null值)
    foreign key(外键约束)
    check(检查约束)

    2) 添加约束
    alter table tb_name add primary key(id)
    alter table tb_name add unique(test_student)
    alter table tb_name add foreign key(id)

    3)删除约束

    alter table tb_name drop primary key

    常用的查询语句

    1.简单查询
    无条件查询

    select * from tb_name; select col1,col2,... from tb_name

    条件查询

    select * from tb_name where 查询条件;

    排序查询

    select * from tb_name where 条件 order by 字段 asc/desc desc降序, asc升序.默认是升序asc

    例如:查询出及格的学生,并按照从高到低进行排序:

    select * from students where score >= 60 order_by score desc;

    模糊查询
    使用关键字like主要使用%,下划线,[]三个字符. % 表示匹配0个或多个字符(通配符),下划线表示匹配一个字符.[]匹配它里面的一个,类似正则.

    例一: 查询名字中姓张的

    select * from person where name like '张%';

    例二:查询姓张的且只有两个字的

    select * from person where name like '张_';

    例三:查询姓氏为张,王,李的人

    select * from person where name like '[张王李]%'

    分组查询:

    select * from tb_name group by 列名.关键字group by将统计列中相同的数据进行分组.

    例一:查询学生中的每个分数有多少人,就是按分数进行分组.有相同的分数将进行合并

    select score,count(*) from student group by score;

    分组常用的函数
    1)max:求最大值 例: select s_name,max(math_score) from student group by s_name 查询数学成绩最高的学生姓名
    select s_name,max(math_score) from student group by s_name
    (2)min:求最小值 例: select s_name,min(math_score) from student group by s_name 查询数学成绩最低的学生姓名
    select s_name,min(math_score) from student group by s_name
    (3)avg:求平均值 例: select class_id,avg(math_score) from student group by class_id 查询每个班的平均数学成绩
    select class_id,avg(math_score) from student group by class_id
    (4)sum:求总数和  例: select sum(s_id) from student 查询表中一共有多少学生
    select sum(s_id) from student
    (5)count:求总行数

    having的用法:筛选成组后的各种数据,它可以筛选出表中没有的数据作为查询条件
    比如:

    select s_name,sum(s_socre) from student group_by s_name having sum(s_socre) > 600

    查询总成绩大于600分的学生,当我们表没有总分这个记录.
    只有每科的成绩,这时就可以使用having了,where就不能来筛选总成绩大于600的学生了.

    having和where的区别?
    having: 是对查询的结果中的列发挥作用,一般配合group by来使用,并且这个列可以不是表中的字段.
    where: 是针对表中的列进行查询,直接在原表上进行操作.而having是对查询的结果进行判断,筛选出满足条件的数据记录.

    limit:
    主要用来分页,limit n,m表示从n+1开始取m条数据
    比如:从去结果中第三条到低5条数据
    select * from tb_name limit 2,5

    简单的多表查询

    select table1.* ,table2.* from table1,table2 where 查询条件.

    子查询和连接查询
    1.where子查询:
    select s_name,s_score from student where s_score in (select s_score from student where s_score >= 60)
    查询成绩及格的学生,后面括号里可以放子查询,也可以放已知的数据.

    相关文章

      网友评论

          本文标题:mysql常用的基本操作

          本文链接:https://www.haomeiwen.com/subject/jooruqtx.html