一、数据库相关操作、
数据库:我们可以把数据库看作时一个文件夹(数据行),他下面包含了很多的文件,每个文件又拥有了很多数据行
默认用户为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 |
+----+------------+-----------+--------+