数据库之mysql基础

DDL语句：数据定义语言

CREATE，DROP，ALTER

DML语句：数据操纵语言

INSERT，DELETE，UPDATE

DQL语句：数据查询语言

SELECT

DCL语句：数据控制语言

GRANT，REVOKE

TCL语句：事务控制语言

COMMIT，ROLLBACK，SAVEPOINT

关系统型数据库相关概念

关系Relational ：关系就是二维表，其中：表中的行、列次序并不重要
行row：表中的每一行，又称为一条记录record
列column：表中的每一列，称为属性，字段，域field
主键Primary key：PK ,一个或多个字段的组合, 用于惟一确定一个记录的字段，一张表只有一个主
键, 主键字段不能为空NULL
唯一键Unique key: 一个或多个字段的组合,用于惟一确定一个记录的字段,一张表可以有多个UK,而
且UK字段可以为NULL
域domain：属性的取值范围，如，性别只能是'男'和'女'两个值，人类的年龄只能0-150

整数型

tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(+-9.22*10的18次方)
上述数据类型，如果加修饰符unsigned后，则最大值翻倍
如：tinyint unsigned的取值范围为(0~255)
int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些
交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是
相同的
BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，非zero值视为真

浮点型(float和double)，近似值

float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位, 注意: 小数点不占用总个数
double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位, 注意: 小数点不占用总个数
设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以
实际为准，即6位

定点数

在数据库中存放的是精确值,存为十进制
格式 decimal(m,d) 表示 最多 m 位数字,其中 d 个小数,小数点不算在长度内
比如: DECIMAL(6,2) 总共能存6位数字，末尾2位是小数，字段最大值 9999.99 （小数点不算在长度内）
参数m<65 是总个数，d<30且 d<m 是小数位
MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。
例如: decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个
字节，小数点后的9个数字用4个字节，小数点本身占1个字节
浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用
8个字节
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal，例如存储
财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

字符串(char,varchar,text)

char(n) 固定长度，最多255个字符,注意不是字节
varchar(n) 可变长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符
BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节
VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节
内建类型：ENUM枚举, SET集合

char和varchar的比较：

1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符
串末尾不能有空格，varchar不限于此
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1
个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节
3.char类型的字符串检索速度要比varchar类型的快
varchar 和 text：
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text
是实际字符数+2个字节。
2.text类型不能有默认值
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text

联系类型

一对一联系(1:1): 在表A或表B中创建一个字段﹐存储另一个表的主键值 如: 一个人只有一个身份
证
一对多联系(1:n)：外键, 如: 部门和员工
多对多联系(m:n)：增加第三张表, 如: 学生和课程

数据约束

约束：constraint，表中的数据在数据类型限定的基础上额外要遵守的限制
常见约束如下:
非空not null:此字段不允许填写空值.
主键primary key ：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；必须提供
数据，即NOT NULL，一个表只能有一个
惟一键unique：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；允许为
NULL，一个表可以存在多个
默认 default：当不填写字段对应的值会使用默认值﹐如果填写时以填写为准.
外键foreign key：一个表中的某字段可填入的数据取决于另一个表的主键或唯一键已有的数据
检查：字段值在一定范围内,如年龄在0到150之间

socket 连接说明

服务器监听的两种 socket 地址：
ip socket: 监听在tcp的3306端口，支持远程通信 ，侦听3306/tcp端口可以在绑定有一个或全部接
口IP上
unix sock: 监听在sock文件上，仅支持本机通信, 如：/var/lib/mysql/mysql.sock)
说明：host为localhost 时自动使用unix sock

关闭mysqld网络连接

只侦听本地客户端， 所有客户端和服务器的交互都通过一个socket文件实现，socket的配置存放
在/var/lib/mysql/mysql.sock） 可在/etc/my.cnf修改

范例：

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
skip-networking=1
bind_address=127.0.0.1

语言规范

在数据库系统中，SQL 语句不区分大小写，建议用大写
SQL语句可单行或多行书写，默认以 " ; " 结尾
关键词不能跨多行或简写
用空格和TAB 缩进来提高语句的可读性
子句通常位于独立行，便于编辑，提高可读性

注释：

#单行注释，注意有空格
-- 注释内容
#多行注释
/*注释内容
注释内容
注释内容*/

命名规则：

必须以字母开头，后续可以包括字母,数字和三个特殊字符（# _ $）
不要使用MySQL的保留字

SQL语句构成

关健字Keyword组成子句clause，多条clause组成语句

示例：

SELECT *           # SELECT子句
FROM products      # FROM子句
WHERE price>666    # WHERE子句

说明：一组SQL语句由三个子句构成，SELECT,FROM和WHERE是关键字

查看数据库

show databases;

创建数据库

create database db1;

创建表

mysql> create table student( id int unsigned auto_increment primary key, name varchar(20) not null, age tinyint unsigned, gender enum('M','F') default 'M' )engine=InnoDB;

查看表数据结构

mysql> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)         | NO   |     | NULL    |                |
| age    | tinyint(3) unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')       | YES  |     | M       |                |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

表中插入数据

mysql> insert student(name,age)values('xiaohong',21);

查看表数据信息

mysql> select * from student;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
|  1 | xiaoming |   21 | M      |
|  2 | xiaohong |   18 | F      |
|  3 | xiaohong |   21 | M      |
+----+----------+------+--------+

改变数据

mysql> update student set name='xiaoqing' where id=3;

删除一条固定数据

mysql> delete from student where id=3;

查询数据

mysql> select * from student;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
|  1 | xiaoming |   21 | M      |
|  2 | xiaohong |   18 | F      |
+----+----------+------+--------+
2 rows in set (0.00 sec)

添加表字段

mysql> alter table student add phone varchar(11);

mysql> select * from student;
+----+----------+------+--------+-------+
| id | name     | age  | gender | phone |
+----+----------+------+--------+-------+
|  1 | xiaoming |   21 | M      | NULL  |
|  2 | xiaohong |   18 | F      | NULL  |
+----+----------+------+--------+-------+
2 rows in set (0.01 sec)

mysql> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)         | NO   |     | NULL    |                |
| age    | tinyint(3) unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')       | YES  |     | M       |                |
| phone  | varchar(11)         | YES  |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

范例：

#修改表名
ALTER TABLE students RENAME s1;
#添加字段
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
#修改字段类型
ALTER TABLE s1 MODIFY phone int;
#修改字段名称和类型
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
#删除字段
ALTER TABLE s1 DROP COLUMN mobile;
#修改字符集
ALTER TABLE s1 character set utf8;
#修改数据类型和字符集
ALTER TABLE s1 change name name varchar(20) character set utf8;
#添加字段
ALTER TABLE students ADD gender ENUM('m','f');
alter table student modify is_del bool default false;
#修改字段名和类型
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
#删除字段
ALTER TABLE students DROP age;
#查看表结构
DESC students;
#新建表无主键，添加和删除主键
CREATE TABLE t1 SELECT * FROM students;
ALTER TABLE t1 add primary key (stuid);
ALTER TABLE t1 drop primary key ;
#添加外键
ALTER TABLE students add foreign key(TeacherID) references teachers(tid);
#删除外键
SHOW CREATE TABLE students #查看外键名
ALTER TABLE students drop foreign key <外键名>;

删除数据库

drop database db1;

删除表

drop table test1;

交叉查询请谨慎使用

一张10w数据的表和另外一张20w数据的表交叉就会变成200亿数据