Linux下mysql学习笔记一

最新推荐文章于 2024-10-11 07:45:00 发布

Julinus

最新推荐文章于 2024-10-11 07:45:00 发布

阅读量1.2k

点赞数 2

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_35396127/article/details/80443220

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Ubuntu 下 Mysql 安装配置

        Mysql 服务器安装： apt-get install mysql-server
        Mysql 客户端安装： apt-getinstallmysql-client
        Mysql 开发包安装： apt-getinstalllibmysqlclient-dev
        启动： sudo /etc/init.d/mysql start
        停止： sudo /etc/init.d/mysql stop
        重启： sudo /etc/init.d/mysql restart

Mysql 的基本使用

        mysql [-h 主机] -u 用户 -p ;
        说明：
                1. 如果没有写 -h 127.0.0.1 默认是连接本地
                2. 如果需要登录到另外一个mysql，则需要修改配置，一般情况不让远程登录。

库的基本操作

创建数据库

create database 库名；

使用库

use 库名；

创建数据表

create table 表名(

字段1 列类型1

字段2 列类型2

........

字段 n 列类型n

);

        向表中插入数据：
              insert into 表名 (字段1，字段2，......) values ( values1,values2 ,.....);
        查看表中数据：

select *from 表名；

SQL 分类：

（1）、DDL 数据定义语言，用来维护存储数据的结构

代表指令：create , drop , alter;

（2）、DML 数据操纵语言，用来对数据进行操作（表中的内容）

代表指令：insert，delete，update；

DML 中有分出了一个 DQL

数据插叙语言：select

（3）、 DCL数据控制语言，主要负责权限管理和事务

代表指令：grant，revoke，commit；

数据库的操作

语法： create database [if not exists] db_name [create_specification [, create_specification] ...]
说明：
            大写的表示关键字
            [] 是可选项
            character set: 指定数据库采用的字符集
            collte : 指定数据库字符集的比较方式（默认utf8_ general_ ci,一般不指定）

查看系统默认字符集以及校验规则的命令为：

show variables like 'character_set_database';
show variables like 'collation_database';

        案例:
                1. 创建名为company1的数据库。
                        mysql> create database test1 Query OK, 1 row affected (0.00 sec) 说明：当我们创建数据库没有指
定字符集和校验规则时，系统使用默认，字符集：utf8，校验规则是：utf8_ general_ ci

2. 创建一个使用utf8字符集的 test2 数据库

create database company2 charset=utf8; Query OK, 1 row affected (0.00 sec);

3. 创建一个使用utf字符集，并带校对规则的company3数据库。
create database company3 charset=utf8 collate utf8_general_ci;

4. 查看数据

show databases;

                5. 显示数据库创建语句
                      show create database 数据库名；

                    说明：
                            1) MySQL 建议我们关键字使用大写，但是不是必须的。
                            2) 数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字
                            3) /*!40100 default.... */ 这个不是注释，表示当前mysql 版本大于4.01版本，就执行这句话。

             6. 数据库删除
                          drop database [ if exists ] db_ name;
              7. 查看当前MySQL数据库的连接情况
                            show processlist;

                        可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是你正常登陆的，很有可能你的数
据库被人入侵了。以后大家发现自己数据库比较慢时，可以用这个指令来看看数据库连接情况。

8. 备份和恢复数据库

mysqldump -u root -p密码数据库名 > 数据库存放路径。示例： mysql -u root -p gcdb > ./mysql.sql;

要恢复数据库，必须先创建一个空的数据库。最好和以前的数据库名字一致。 create databsase gcdb; source /home/arrayli/mysql.sql;

如果备份数据库其中的一张表：mysqldump -u root -p密码数据库名表名1 表名2 > ./mytables.bk ；恢复指定的表：mysql> source 备份的文件路径。

                         在使用 source 恢复数据时，保证 use 对应的数据库同时备份多个数据库 [arrayli@ubuntu ]~$ mysqldump -u root -p 密码 -B 数据库名 1 数据库名 2... > 数据库存放路径.
                        同时恢复多个数据库 mysql > source 备份的文件。如果我们备份一个数据库时，也带上 -B 参数，在恢复数据库时，不需要再创建空数据库。

9. 修改数据库
语法： alter database [ if exists ] db_name [alter_spacification [,alter_spacification]...]

10. 删除数据库

drop database 数据库名字 ;
表的操作

1. 语法：

                      create table_name (
                                     field1 datatype,
                                     field2 datatype,
                                  field3 datatype

                        ) character set 字符集 collate 校验规则 engine 存储引擎 ;
             2. 说明：
                    field 表示列名
                    datatype 表示列的类型
                    character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准。
                    collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准。
              3. 创建一张表 :
                        create table users (

                                 id int,
                              name varchar(20) comment ' 用户名 ',
                                 password char(32) comment ' 密码是 32 位的 md5 值 ',
                                 birthday date comment ' 生日 '
                        ) character set utf8 engine MyISAM;
              4. 查看表的结构：

                     desc 表名 ;
              5. 修改表：
                     在项目实际开发中，经常修改某个表的结构，比如字段名字，字段大小，字段类型，表的字符集类型，表的存储引
擎等等。我们还有需求，添加字段，删除字段等等。这时我们就需要修改表。
              alter table tablename add (column datatype [default expr][,column datatype]...);
                  alter table tablename modify (column datatype [default expr][,column datatype]...);
                  alter table tablename drop (column);

数据类型

1. 数值类型

Mysql支持所有标准SQL中的数值类型，其中包括严格数据类型(INTEGER,SMALLINT,DECIMAL,NUMBERIC)，以及近似数值数据类型(FLOAT,REAL,DOUBLE PRESISION),并在此基础上进行扩展。

扩展后增加了TINYINT,MEDIUMINT,BIGINT这3种长度不同的整形，并增加了BIT类型，用来存放位数据。

整数类型字节范围（有符号）范围（无符号）用途

TINYINT 1字节 (-128，127) (0，255) 小整数值

SMALLINT 2字节 (-32 768，32 767) (0，65 535) 大整数值

MEDIUMINT 3字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值

INT或INTEGER 4字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值

BIGINT 8字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值

FLOAT 4字节 (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度浮点数值

DOUBLE 8字节 (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数值

DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值依赖于M和D的值小数值

2. 字符串类型

　MySQL 提供了8个基本的字符串类型,分别:CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 各SET等多种字符串类型。

可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据。

字符串类型字节大小描述及存储需求

CHAR 0-255字节定长字符串

VARCHAR 0-255字节变长字符串

TINYBLOB 0-255字节不超过 255 个字符的二进制字符串

TINYTEXT 0-255字节短文本字符串

BLOB 0-65535字节二进制形式的长文本数据

TEXT 0-65535字节长文本数据

MEDIUMBLOB 0-16 777 215字节二进制形式的中等长度文本数据

MEDIUMTEXT 0-16 777 215字节中等长度文本数据

LOGNGBLOB 0-4 294 967 295字节二进制形式的极大文本数据

LONGTEXT 0-4 294 967 295字节极大文本数据

VARBINARY(M) 允许长度0-M个字节的定长字节符串，值的长度+1个字节

BINARY(M) M 允许长度0-M个字节的定长字节符串

3. 日期和时间类型

　在处理日期和时间类型的值时，MySQL 带有 5 个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型，和混合日期、时间类型。

根据要求的精度，子类型在每个分类型中都可以使用，并且 MySQL 带有内置功能可以把多样化的输入格式变为一个标准格式。

类型大小(字节) 范围格式用途

DATE 4 1000-01-01/9999-12-31 YYYY-MM-DD 日期值

TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间

YEAR 1 1901/2155 YYYY 年份值

DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值

TIMESTAMP 4 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

4. 复合类型

　　MySQL 还支持两种复合数据类型 ENUM 和 SET，它们扩展了 SQL 规范。虽然这些类型在技术上是字符串类型，但是可以被视为不同的数据类型。一个 ENUM 类型只允许从一个集合中取得一个值；而 SET 类型允许从一个集合中取得任意多个值。

枚举，其实就是“单选”类型，对应界面或表单中的“单选项”的数据值；enum('选项1','选项2','选项3',...); 该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且，处于效率考虑，这些值实际存储的是“数字”，因为这些选项，每个选项值，一次对应如下数字：1,2,3,....最多65535个；当我们添加枚举值时，也可以添加对应的数字编号。

set就是“多选”类型，对应于界面或表单的“多选项”的数据值。set('选项值1','选项值2','选项值3', ...);该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了任何其中的多个值；而且，处于效率考虑，这些实际存储的是“数字”，因为这些选项，每个选项值，一次对应如下数字：1,2,4,8,16,32，....最多64个。

表的约束
1、真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业
务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。表的约束很多，这里主要介绍如下几个，其余的以后讲解。null/not null,default, comment, zerofill，primary
key，auto_increment，unique。
2、空属性

(1)、两个值：null（默认的）和not null(不为空)
(2)、数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。

3、默认值
(1)、默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。

              (2)、默认值的生效：数据在插入的时候不给该字段赋值，就使用默认值
              (3)、注意： set和enum不能设置默认值。
      4、列描述

                comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了
解。
                mysql> create table tt12 (
                        -> name varchar(20) not null comment '姓名',
                        -> age tinyint unsigned default 0 comment '年龄',
                        -> sex char(2) default '男' comment '性别'
                        -> );
                  通过desc查看不到注释信息

    5、zerofill
              mysql> show create table tt3\G
                ***************** 1. row *****************
                Table: tt3
                            Create Table: CREATE TABLE `tt3` (
                            `a` int(10) unsigned DEFAULT NULL,
                            `b` int(10) unsigned DEFAULT NULL
                            ) ENGINE=MyISAM DEFAULT CHARSET=gbk 1 row in set (0.00 sec)
        可以看到int(10),这个代表什么意思呢？整型不是4字节码？这个10又代表什么呢？其实没有zerofill这个属性，括号内的数字是毫无意义的。a和b列就是前面插入的数据，如下：
            mysql> select * from tt3;
                +------+------+
                    | a | b |
                +------+------+
                    | 1 | 2 |
              +------+------+
        但是对列添加了zerofill属性后，显示的结果就有所不同了。修改tt3表的属性：mysql> alter table tt3 change a a int(5) unsigned zerofill;对a列添加了zerofill属性，再进行查找，返回如下结果：
            mysql> select * from tt3;
            +-------+------+
                | a | b |
            +-------+------+
            | 00001 | 2 |
            +-------+------+
        这次可以看到a的值由原来的1变成00001，这就是zerofill属性的作用，如果宽度小于设定的宽度（这里设置的是
5），自动填充0。要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是1。为什么是这样呢？我们可
以用hex函数来证明。
mysql> select a, hex(a) from tt3;
+-------+--------+
| a | hex(a) |
+-------+--------+
| 00001 | 1 |
+-------+--------+
可以看出数据库内部存储的还是1,00001只是设置了zerofill属性后的一种格式化输出而已
      6、主键

          (1)、主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，主键所在的列是整数类型。
一张表中最多只能有一个主键。创建表的时候直接在字段上指定主键
        mysql> create table tt13 (
-> id int unsigned primary key comment '学号不能为空',
-> name varchar(20) not null);
Query OK, 0 rows affected (0.00 sec)
          (2)、在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为
主键，可以使用复合主键。

(3) 当表创建好以后，可以再次追加主键。alter table 表名 add primary key(字段列表) 主键约束：主键对应的字段中不能重复，一旦重复，操作失败。

(4)、删除主键 alter table 表名 drop primary key;
7、自增长

            auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，
得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。
            自增长的特点:
                    任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
                    自增长字段必须是整数
                    一张表最多只能有一个自增长
      8、唯一键

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有
多个字段需要唯一性约束的问题。唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

表的增删查改
1、增加：

                insert into table_name[(column[,column...])] values (value [,value...]);
        示例：
          (1). 创建一张商品表
                    mysql> create table goods( -> id int unsigned primary key, -> goods_name varchar(50) not null default '',
                                                            -> price float not null default 0.0 -> ); Query OK, 0 rows affected (0.01 sec)
          (2). 插入两条记录
                    mysql> insert into goods values(100, '牛排', 78.5); Query OK, 1 row affected (0.00 sec)
      2、使用添加语句注意的细节：

          (1)、插入的数据应与字段的数据类型相同。比如，将‘abc'插入到id列就不行：
          (2)、数据的大小应在规定的范围内，例如：不能将一个长度为80的字符串插入到长度为40的列中。
            (3)、在values中列出的数据位置必须与被加入的列位置相对应
            (4)、字符和日期类型应该包含在单引号中。
            (5)、插入空值，不指定或insert into table values(null)
            (6)、insert into table values(),(),() 一次性添加多条记录
            (7)、如果给表中的所有字段添加数据，可以不写前面的字段名称
            (8)、如果你只给表的某几个字段赋值，则需要制定字段名
3、增加进阶
                    在数据插入的时候，假设主键对应的值已经存在：插入失败！当主键存在冲突的时候(duplicate key),可以选择性的进行处理:
              (1). 更新操作
                        insert into 表名(字段列表) values(值列表) on duplicate key update 字段=新值; mysql> insert into goods
values(101, 'ccc', 20.5) on duplicate key update goods_name='ccc', price=20.5;
                (2).替换：主键如果没有冲突，就直接插入。 replace into 表名(包含字段) values(值列表)；
      4、修改
                更新表中的数据，语法：update tbl_name set col_name1=expr1, [, col_name2=expr2 ...] [where conditon]
            update使用细节：
                    update 语法可以用心值更新原有表中的各列
                    set子句指示要修改哪些列和要给予哪些值
                    where子句指定应更新哪些行。如果没有where子句，则更新所有行
                     如果需要更新多个字段，可以通过 set 字段1=值1，字段2=值2...
            更新还可以限制更新数量：update 表名 set 字段=值 [where 条件] [limit 更新数量];
      5、删除

删除表中的数据，语法：delete from tbl_name [where condition]
删除表中的所有记录

                            mysql> delete from goods; --删除整个表的数据，但是表的结构还存在
                使用truncate删除表中的记录
                            mysql> truncate table goods; --这个指令也把整个表记录删除
                上述两种删除整表的区别:
                             效果一样，truncate速度快
                              delete可以带where条件，删除更加灵活
                                delete可以返回被删除的记录数，而truncate返回0
                        推荐使用delete
                        delete使用细节：
                            如果不适用where子句，将删除整个表中所有数据
                            delete语句不能删除某一列的值（可以用update置null）
                            使用delete语句仅删除记录，不删除表本身（drop table）
select
      1、语法：select [distinct] *| {column1,column2,...} from tbl_name;
      2、*号表示查询所有列（星号效率很低，用哪些字段就取哪些字段）
                select * from student;
      3、distinct 如果结果中有完全相同的行，就去除重复行。 mysql> select distinct math from student;
      4、在select语句中可以使用表达式对查询的列进行运算
        5、select语句中可以使用as起别名。select column as 别名 from 表；
      6、select的where子句
                使用where子句，进行查询过滤。在where子句中经常使用的运算符

      7、select的order by 子句
                order by子句排序查询结果
                select column1,column2,... from table order by column asc|desc,...;  order by 指定排序的列，排序的列可以使表中的列名，也可以是select语句后指定的别名。asc升序(默认)，desc降序。order by 子句应该位于select语句的结尾。

8、count
count(列名)返回某一列，行的总数。select count(*)|count(列名) from tbl_name where condition

            示例：统计一个班级共有多少学生 mysql> select count(*) from student;
        9、sum
                sum函数返回满足where条件的行的和。select sum(列名） {，sum(列名)...} from tbl_name [where condition] 。注意：sum仅对数值起作用，否则结果无意义。

            示例：统计一个班级数学总成绩    mysql> select sum(math) from student;
      10、avg
                agv函数返回满足where条件的一列的平均值。select avg(列名) [,avg(列名),...] from tbl_name [where condition];

             示例：求一个班级的数学平均分 mysql> select avg(math) from student;
      11、max/min
                max/min函数返回满足where条件的一列的最大/最小值。select max(列名) from tbl_name [where condition]

             示例：求班级最高和最低分  mysql> select max(chinese+english+math), min(chinese+english+math) from student;
      12、group by子句的使用
                group by 子句对列进行分组。select column1, column2, .. from table group by column;

函数
          1、常用日期函数

            获得年月日：    mysql> select current_date();
            获得时分秒：   mysql> select current_time();
            获得时间戳： mysql> select current_timestamp();
            在日期的基础上加日期：            mysql> select date_add('2017-10-28', interval 10 day);
            在日期的基础上减去时间：         mysql> select date_sub('2017-10-1', interval 2 day);
            计算两个日期之间相差多少天：   mysql> select datediff('2017-10-10', '2016-9-1');
            日期函数使用细节：
                    date_ add()和date_ sub()中的interval后面可以使 year minute second day
                    datediff(date1, date2)得到的是天数，二期是date1-date2的天数，因此可以使负数
      2、字符串函数

3、数学函数

            绝对值                    mysql> select abs(-100.2);
            向上取整                  mysql> select ceiling(23.04);
            向下取整                mysql> select floor(23.7);
            保留2位小数位数（小数四舍五入)        mysql> select format(12.3456, 2);
            产生随机数 [0,1]       mysql> select rand();
            其他常用函数            user() 查询当前用户         md5（str）可以对一个字符串进行md5加密，加密后得到一个32位字符串

            创建一张表，存用户名和密码
                            mysql> create table users(name varchar(30), passwd char(32));
            插入数据，密码用md5加密
                            mysql> insert into users values('zhangsan', md5('123456'));
            database() 显示当前正在使用的数据库
                            mysql> select database();
            password()函数，对mysql用户加密。