mysql常用的基本操作

June 13th, 2021

MySql常用的基本操作
- 创建新的用户
- 数据库常见操作

MySql常用的基本操作

创建新的用户

用户的信息都保存在数据库的库名为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_table和b_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

常用的查询语句

简单查询
无条件查询

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)
查询成绩及格的学生,后面括号里可以放子查询,也可以放已知的数据.

Tech (85)

Demi_YuHongJun

Demi的随笔和技术空间

mysql常用的基本操作