Mysql

最新推荐文章于 2021-06-08 09:01:04 发布

南子的苦逼生活

最新推荐文章于 2021-06-08 09:01:04 发布

阅读量188

点赞数

本文链接：https://blog.csdn.net/weixin_43224949/article/details/84260308

版权

一、数据库相关操作、

数据库：我们可以把数据库看作时一个文件夹（数据行），他下面包含了很多的文件，每个文件又拥有了很多数据行

默认用户为root,权限最高，当然我们可以创建用户

create user "用户名"@"IP地址" identified by "密码"；例如：create user "用户名"@"192.168.1.%" identified by "密码"；create user "用户名"@"%" identified by "密码"；所有IP用户

授权：给谁授权，权限是什么，给谁授权 grant 权限 select(查),insert（写）,uodate(更新) on db1.* (*表示所有文件) to "用户名"@"%";

grant all privileges on db1.t1 to "用户名"@"IP";赋予该人除了grant的所有权限授权一般由DBA来做

create databse 数据库名 default charset utf-8; 默认该数据库编码格式为utf-8

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use mysql;
Database changed
mysql> use mysql; create user "zhezhe"@"%" identified by "123";
Database changed
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on db1.* to "zhezhe"@"%";

数据库主要有四个操作：增、删、改、查（当然这些操作都应该在连接上数据库服务器以后）

查：1、查看当前服务器里面由那些数据库语法：show databases;注意：表名不能重复，且不能更改（在InnoDB）

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

2、添加数据库语法 create database 数据库名；不能创建已存在数据库且数据库命名一般以下划线、数字、字母组合，且不能以数字开头；

mysql> create database db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists
mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

3、同上使用普通用户登陆，需要特定权限才能执行删除、创建数据库，而root用户具有最高权限，可以执行所有操作，语法： drop database 数据库名；

mysql> drop database db1;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db2                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

4、当你连接到MySQL数据库后，可能有多个可以操作的数据库，因此选择你要操作的数据库，便变得非常重要，语法：use 数据库名；

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db2                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use db2;
Database changed

5、查看数据库的属性，当数据库被创建时，会按照默认属性创造，当然我们可以更改，更改文件为my.ini，更改格式参照网上https://my.oschina.net/imecho/blog/1809651；语法：show create database 数据库名；

mysql> show create database db2;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+

mysql> alter database db2 DEFAULT CHARACTER SET gbk;
Query OK, 1 row affected (0.00 sec)

mysql> show create database db2;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+

二、表的基础操作

对表做操作之前，我们应该有限选定数据库；使用use 库名；创建数据库：create table 库名（属性属性类型（长度），......）:表名不能重复，但能够重命名。

ysql> use db2;
Database changed
mysql> create table copy_life(name char(10),age int(5));
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| copy_life     |
+---------------+

查看表：查看已有的表名称 show tables; 查看表结构：desc 表名；查看表下所有信息：select *from 表名；产看指定一个或多个列：select 字段1，字段2，... from 表名；

引擎：INNODB 支持事务即中间任何时刻出了差错，即会回滚到差错前一步 myisam 支持全局索引、速度较快不支持十五

show tables;查看所有表，create table 表名（id int,name char(10)）[default Innodb default utf8]；创建表并设值字段名默认表的编码格式为utf-8 select *from 表名；查看表名 ;insert into 表名(要插入的行，不写的话默认全部) values(值1、值2，..),(值1、值2，..) ;向表里插入数据注意编码问题；

mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| copy_life     |
+---------------+
mysql> desc copy_life;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| age   | int(5)   | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
mysql> create database copy_life;
Query OK, 1 row affected (0.00 sec)

mysql> use copy_life;
Database changed
mysql> create table copy_life(name char(10),age int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into copy_life values("duke",35),("alex",26);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show tables;
+---------------------+
| Tables_in_copy_life |
+---------------------+
| copy_life           |
+---------------------+
1 row in set (0.00 sec)

mysql> desc copy_life;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select *from copy_life;
+------+------+
| name | age  |
+------+------+
| duke |   35 |
| alex |   26 |
+------+------+
2 rows in set (0.00 sec)

mysql> select name,age from copy_life;
+------+------+
| name | age  |
+------+------+
| duke |   35 |
| alex |   26 |
+------+------+

mysql> create table t1(
    -> id int not null primary key auto_increment,
    -> name char(10) not null,
    -> age int null
    -> ) engine=Innodb default charset=utf8;
mysql> insert into t1(name) values("duke"),
    -> ("egon"),
    -> ("沟通");

mysql> select *from t1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | duke   | NULL |
|  2 | egon   | NULL |
|  3 | 沟通   | NULL |
+----+--------+------+

mysql> delete from t1;

mysql> select *from t1;
Empty set (0.00 sec)

mysql> insert into t1(name) values("duke");

mysql> select *from t1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | duke | NULL |
+----+------+------+
mysql> truncate table t1;
mysql> select *from t1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | duke | NULL |
+----+------+------+

mysql> drop table t1;#删除表

mysql> show tables;
+---------------------+
| Tables_in_copy_life |
+---------------------+
| copy_life           |
+---------------------+

四、详细建表语句及数据类型

详细建表语句 create table 表名（字段名数据类型[（长度）约束条件]）[可选内容]

数据类型整型、浮点型、字符型

一、整型

整型分为微整型，小整型，中等整型，整型，大整型

建表后的整型数据默认时无符号的，默认显示宽度及最大十进制数据的长度，若你所输入数字大于整型所限制大小，将会产生错误；若你输入的数字符合格式规范，但是小于默认显示宽度，则按改数字实际宽度录入。

mysql> desc copy_life;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| age   | int(5)   | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
mysql> alter table copy_life change name int_number int;

mysql> alter table copy_life change age tinyint_number tinyint;

mysql> alter table copy_life add small_number smallint;

mysql> alter table copy_life add mediumint_number mediumint;

mysql> alter table copy_life add big_number bigint;

mysql> desc copy_life;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| int_number       | int(11)      | YES  |     | NULL    |       |
| tinyint_number   | tinyint(4)   | YES  |     | NULL    |       |
| small_number     | smallint(6)  | YES  |     | NULL    |       |
| mediumint_number | mediumint(9) | YES  |     | NULL    |       |
| big_number       | bigint(20)   | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

从上面的例子我们可以看出，每一个整型数字都有自己默认的显示宽度，且都是有符号的整型；

mysql> insert into copy_life values(-214748,-12,-3276,-83886,-923337203680);
Query OK, 1 row affected (0.01 sec)

mysql> select *from copy_life;
+------------+----------------+--------------+------------------+---------------+
| int_number | tinyint_number | small_number | mediumint_number | big_number    |
+------------+----------------+--------------+------------------+---------------+
|    -214748 |            -12 |        -3276 |           -83886 | -923337203680 |
+------------+----------------+--------------+------------------+---------------+

当然我们可以设置其显示长度，同时设置其为无符号，例子如下，

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| age   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table t1 modify age int(5) unsigned;#修改其age属性为int显示宽度为5，为无符号整型
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+-----------------+------+-----+---------+-------+
| Field | Type            | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| age   | int(5) unsigned | YES  |     | NULL    |       |
+-------+-----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t1 values(244);#此时类型为整型int无符号
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(523146);#此时类型为整型int无符号，虽然显示宽度是5个n，但是6位数依旧属于int范围，依旧能够正常存入
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values-(-523146);#此时类型为整型int无符号，虽然显示宽度是5个n，不能存入负数
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-(-523146)' at line 1
mysql> desc t1;
+-------+-----------------+------+-----+---------+-------+
| Field | Type            | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| age   | int(5) unsigned | YES  |     | NULL    |       |
+-------+-----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select *from t1;
+--------+
| age    |
+--------+
|    244 |
| 523146 |
+--------+
2 rows in set (0.00 sec)

二、浮点型

浮点型分为三种float、double、decmical三种，三种均可以表示小数，但是三种又各有不同

float:能够表示 235数（小数与整数部分），其中小数部分最多为30位，duble 位数表示同上，但是double的进度高于float。decimal最大位数为60位，小数位为30位，小数位精确表示。小数位数和整数位数可以限制浮点数范围。

mysql> create table money(money1 float(60,30),money2 double(60,30),money3 decimal(60,30));
Query OK, 0 rows affected (0.02 sec)

mysql> desc money;
+--------+----------------+------+-----+---------+-------+
| Field  | Type           | Null | Key | Default | Extra |
+--------+----------------+------+-----+---------+-------+
| money1 | float(60,30)   | YES  |     | NULL    |       |
| money2 | double(60,30)  | YES  |     | NULL    |       |
| money3 | decimal(60,30) | YES  |     | NULL    |       |
+--------+----------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into money values(50.111111111111111111111111111111,50.111111111111111111111111111111,50.111111111111111111111111111111);
Query OK, 1 row affected (0.01 sec)

mysql> select *from money;
+-----------------------------------+-----------------------------------+-----------------------------------+
| money1                            | money2                            | money3                            |
+-----------------------------------+-----------------------------------+-----------------------------------+
| 50.111110687255860000000000000000 | 50.111111111111114000000000000000 | 50.111111111111111111111111111111 |
+-----------------------------------+-----------------------------------+-----------------------------------+

从上个例子中，我们可以得知，输入相同的数据，但是他们的精度时不一样。decimal >double>float。

mysql> alter table money add money4 float(7,5) unsigned;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into money(money4) values(325.002);
ERROR 1264 (22003): Out of range value for column 'money4' at row 1
mysql> insert into money(money4) values(25.02);
Query OK, 1 row affected (0.01 sec)

mysql> select *from money;
+-----------------------------------+-----------------------------------+-----------------------------------+----------+
| money1                            | money2                            | money3                            | money4   |
+-----------------------------------+-----------------------------------+-----------------------------------+----------+
| 50.111110687255860000000000000000 | 50.111111111111114000000000000000 | 50.111111111111111111111111111111 |     NULL |
|                              NULL |                              NULL |                              NULL | 25.02000 |
+-----------------------------------+-----------------------------------+-----------------------------------+----------+

从上例可得；当限定整数部分与小数部分宽度后，则即指定了范围，不可越界，小数部分若位数不足，则向后补0

三、字符串类型

字符串类型：char(m):m表示字符串可输入最大字符长度，若输入字符串不到最大字符串长度，则以空格补齐至m位
                     varchar(m):m表示字符串可输入最大字符长度，若输入字符串不到最大字符串长度，按实际字符长度好   节省空间 速度较慢与char
                     以后创建数据表时，把定常数据列往前放，变长数据列放在最后面，会相对与快一点 相同点 最高都是255字符
                     text:65535 字符 若数据过大，则将文件存在硬盘，将其文件路径存在数据库中
                     上传文件：文件、视频、图片等往往直接存在硬盘上，只将其路径存在数据库中
时间类型：TIME 时分秒
                   DATETIME  年月日时分秒

外键的使用：

外键：外键可以添加多个外键 用逗号隔开 外键一对多
         create table userinfo(
                  uid bigint auto_increment primary key,
                  name varchar(32),
                  department_id int,
                  xx_id int,
                  constraint fk_user_depar foreign key (department_id) references department(id),
                  constraint fk_user_xx foreign key (xx_id) references xx(id)
          )engine=innodb default charset=utf8;

          create table department(
                  id bigint auto_increment primary key,
                  title char(15)
           )engine=innodb default charset=utf8;

作业：

代码：

1、创建班级表
    create table classes(
        id int auto_increment primary key,
        name char(20)
    )engine=innodb default charset=utf8;

2、创建老师表
   create table teachers(
        id int auto_increment primary key,
        name char(20)
    )engine=innodb default charset=utf8;
3、创建学生表外联班级表
    create table students(
        id int auto_increment primary key,
        name char(20),
        gender char(1),
        class_id int,
        constraint fk_user_class foreign key(class_id) references classes(id)    )engine=innodb default charset=utf8;

 4、创建课程表关联老师
 create table courses(
    id int auto_increment primary key,
    cname char(20),
    teacher_id int,
    constraint fk_course_tea foreign key(teacher_id) references teachers(id)
 )engine=innodb default charset=utf8;

 5、创建成绩表关联课程、学生
 create table scores(
    id int auto_increment primary key,
    student_id int,
    course_id int,
    number int,
    constraint fk_score_stu foreign key(student_id) references students(id),
    constraint fk_score_cor foreign key(course_id) references courses(id)

效果：

mysql> select *from teachers;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 老子      |
|  2 | 庄子      |
|  3 | 老夫子    |
+----+-----------+
mysql> select *from students;
+----+-----------+--------+----------+
| id | name      | gender | class_id |
+----+-----------+--------+----------+
|  1 | 杜克      | 男     |        1 |
|  2 | 爱丽丝    | 女     |        2 |
|  3 | 赦罪      | 男     |        3 |
+----+-----------+--------+----------+
mysql> select *from classes;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | 一年级一班      |
|  2 | 二年级二班      |
|  3 | 三年级三班      |
+----+-----------------+
mysql> select *from courses;
+----+--------+------------+
| id | cname  | teacher_id |
+----+--------+------------+
|  1 | 生物   |          1 |
|  2 | 物理   |          2 |
|  3 | 化学   |          2 |
+----+--------+------------+
mysql> select *from scores;
+----+------------+-----------+--------+
| id | student_id | course_id | number |
+----+------------+-----------+--------+
|  1 |          1 |         1 |     98 |
|  2 |          2 |         3 |     21 |
|  3 |          3 |         2 |     86 |
+----+------------+-----------+--------+

南子的苦逼生活

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql

一、数据库相关操作、数据库：我们可以把数据库看作时一个文件夹（数据行），他下面包含了很多的文件，每个文件又拥有了很多数据行默认用户为root,权限最高，当然我们可以创建用户create user "用户名"@"IP地址" identified by "密码"；例如：create user "用户名"@"192.168.1.%" identified by "密码"；cre
复制链接

扫一扫