数据库复习-2

数据库复习-2

SQL语法学习

创建表

--字符串使用用''


create table if not exists `student`(
	`id` int(4) not null auto_increment comment '学号',
	`name` varchar(30) not null default '匿名' comment '姓名',
    `pwd`  varchar(20) not null default '123456' comment '密码',
    `sex`  varchar(2) not null default '女' comment '性别',
    `birthday` datetime default null comment  '出生日期',
    `address`  varchar(100) default null comment  '地址',
    `email`   varchar(100) default null comment  '邮箱',
    primary key(`id`)
    
) ENGINE=INNODB DEFAULT CHARSET=utf8

--show create table student查看创表语句
--desc student 显示表结构

数据库引擎

MyISAM和InnoDB(默认使用)的区别

	MyISAM	InnoDB
事务支持	no	yes
数据行锁定	no	yes
外键约束	no	yes
全文索引	yes	no
表空间大小	较小	较大约为MyISAM两倍

常规操作

MyISAM：节约空间 速度快

InnoDB ：安全性高 事务的处理 多表多用户

在物理的存储位置

所有数据库文件都在data目录下 一个文件夹就对应一个数据库

本质还是文件存储！

Mysql引擎在物理文件上的区别

​ InnoDB 在数据表中只有一个*.frm文件，以及上级目录下的ibdata1文件

​ MyISAM对应的文件 *.frm --表结构的定义文件

​ *.MYD --数据文件

​ *.MYI --索引文件

设置数据库表的字符集编码

CHARSET=utf8

不设置会出问题，它默认的Latin1是不支持中文

修改和删除表

ALTER TABLE  student RENAME  student1 --修改表名
ALTER TABLE  student1 add age INT(11) --添加表字段
ALTER TABLE  student1 MODIFY age VARCHAR(11) --修改约束
ALTER TABLE  student1 CHANGE age age1 INT(11) --重命名


ALTER TABLE  student1 DROP age1 --删除字段

drop table if exists student1 --删除表

mysql数据管理

外键

最佳实践

数据库就是单纯的表，只用来存数据，其他实现用程序实现

以下操作是物理外键，数据库级别的外键，不建议使用

一、基本概念

1、MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。

2、外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接。

3、如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innoDB类型。

4、外键的使用条件

① 两个表必须是InnoDB表，MyISAM表暂时不支持外键

② 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；

③ 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

5、外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作

mysql> CREATE TABLE tb_dept1
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) NOT NULL,
    -> location VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.37 sec)


mysql> CREATE TABLE tb_emp6
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CONSTRAINT fk_emp_dept1
    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );
Query OK, 0 rows affected (0.37 sec)

mysql> DESC tb_emp6;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  | MUL | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (1.33 sec)

DML语言

数据操作语言：insert update delete

添加

mysql> use RUNOOB;
Database changed
mysql> INSERT INTO runoob_tbl 
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 PHP", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 MySQL", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("JAVA 教程", "RUNOOB.COM", '2016-05-06');
Query OK, 1 rows affected (0.00 sec)
--数据和字段要一一对应
--主键自增可以省略 如果不写表的字段他就会一一匹配
--字段和字段之间要用逗号隔开

修改

--在 tb_courses_new 表中，更新所有行的 course_grade 字段值为 4
mysql> UPDATE tb_courses_new
    -> SET course_grade=4;
--在 tb_courses 表中，更新 course_id 值为 2 的记录，将 course_grade 字段值改为 3.5，将 course_name 字段值改为“DB”
mysql> UPDATE tb_courses_new
    -> SET course_name='DB',course_grade=3.5
    -> WHERE course_id=2;

删除

--删除 tb_courses_new 表中的全部数据
DELETE FROM tb_courses_new;
--在 tb_courses_new 表中，删除 course_id 为 4 的记录、
mysql> DELETE FROM tb_courses
    -> WHERE course_id=4;

TRUNCATE

完全清空一个数据表数据，表的结构和约束不会变

TRUNCATE   `student`

DELETE和TRUNCATE区别

相同点：都能删除数据，都不会删除表结构

不同：

​ TRUNCATE 重新设置自增列 计数器会归零

​ TRUNCATE不会影响事务

DELETE删除的问题，重启数据库，现象

​ InnoDB 自增会从1开始（存在内存中断电即失）

​ MyISAM 继续上一个增量开始 （存在文件中不会丢失）