数据库入门1
基础知识 MySQL
MySQL
发布日期:   2018-10-01
文章字数:   3.1k
阅读时长:   16 分
阅读次数:  

基本概念

什么是数据库？

数据库(database) 保存有组织的数据的容器(通常是一个文 件或一组文件)。数据库软件称为DBMS(数据库管理系统)。数据库 是通过DBMS创建和操纵的容器。

什么是SQL?

SQL(发音为字母S-Q-L或sequel)是结构化查询语言(Structured Query Language)的缩写。SQL是一种专门用来与数据库通信的语言。

什么是非关系型数据库NoSQL？

非关系型数据库NoSQL是通过键值对存储的，每个键值对可以有不同的结构和字段，相对灵活。无需经过SQL层的解析，性能非常高。由于是基于键值对，数据没有耦合性，非常容易水平拓展。

什么是关系型数据库？

关系型数据库可以简单理解为二维表格及其之间的联系所组成的数据组织。关系型数据库可以在一个或多个表之间做复杂的查询。同时也支持事务，对于安全性能要求较高的应用，可以保证安全。

数据库的基本操作

登录：
mysql -u root -p 
mysql -uroot -proot
//直接使用某个数据库
mysql -uroot -proot lx
查看所有数据库
show databases;
查看数据库的引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
创建数据库
create database lx_databse charset utf8;
查看创建的数据库
show create database lx_database;
删除数据库
drop database lxx_database;
选择数据库
use  lxx_database;

注意：如果要修改数据库，不能修改数据库名称，只能修改字符集和校对集。

表操作

创建表方式1：
create table stu (
id int not null auto_increment primary key comment'id 学号',
name varchar(10) not null default'' comment'姓名',
age int not null default 0 comment'年龄' ,
sex varchar(5) not null default'男' comment'性别'
)charset=utf8 engine=Myisam;
创建表方式2：
create table user(
id int unsigned not null auto_increment,
name varchar(8) not null,
primary key(id)
);
创建表(单字段主键)并指定引擎为myisam：
create table member(
id int unsigned auto_increment primary key,
username char(20) not null default '',
gender char(1) not null default '',
weight tinyint unsigned not null default 0,
birth date not null default '0000-01-01',
salary decimal(8,2) not null default 0.00,
lastlogin int unsigned not null default 0
) engine myisam charset utf8;
创建表(多字段联合主键)并指定引擎为myisam：
create table student(
id int unsigned auto_increment,
username char(20) not null default '',
gender char(1) not null default '',
weight tinyint unsigned not null default 0,
birth date not null default '0000-01-01',
salary decimal(8,2) not null default 0.00,
lastlogin int unsigned not null default 0,
primary key(id,username)
);
删除表
drop table stu；
查看表
show tables；
查看表结构
desc  stu；
show columns from user;
查看创建该表时使用的语句
show create table stu;
修改表名
alter table stu rename to student;
修改表引擎
mysql> show create  table  tb_edu\G;
*************************** 1. row ***************************
       Table: tb_edu
Create Table: CREATE TABLE `tb_edu` (
  `id` int(10) unsigned NOT NULL,
  `edu` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> alter table tb_edu engine=InnoDB;
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> show create  table  tb_edu\G;
*************************** 1. row ***************************
       Table: tb_edu
Create Table: CREATE TABLE `tb_edu` (
  `id` int(10) unsigned NOT NULL,
  `edu` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified
添加新的字段到该表
alter table student add address varchar(200) not null default'' comment'住址' ;
添加新的字段到该表的指定字段后面
alter table stu add address varchar(200) not null default'' comment'住址' after age;
修改表中的字段名(改名 改属性)
alter table student change address tel char(11) default'18210823650' comment'电话';
修改表中的字段(只改属性)
alter table student modify  tel int comment'电话';
修改表中的字段的位置
alter table stu modify  sex  varchar(5) after name;

alter table stu modify  name varchar(10)  First;
删除表中的字段
alter table student drop  tel;
为表中的字段添加普通索引

原来的表结构：

mysql> desc user;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)     | YES  |     | NULL    |                |
| age   | int(11)          | YES  |     | NULL    |                |
| email | varchar(255)     | YES  |     | NULL    |                |
| sign  | int(11)          | YES  |     | NULL    |                |
| grad  | varchar(255)     | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

添加索引（name的key变成了MUL）：

mysql> alter table user add index i_name(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)     | YES  | MUL | NULL    |                |
| age   | int(11)          | YES  |     | NULL    |                |
| email | varchar(255)     | YES  |     | NULL    |                |
| sign  | int(11)          | YES  |     | NULL    |                |
| grad  | varchar(255)     | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
删除属性为普通索引的字段
mysql> alter table user drop index i_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)     | YES  |     | NULL    |                |
| age   | int(11)          | YES  |     | NULL    |                |
| email | varchar(255)     | YES  |     | NULL    |                |
| sign  | int(11)          | YES  |     | NULL    |                |
| grad  | varchar(255)     | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
删除表中的主键

删除表中的主键时比较特殊，id是自增类型，需要先修改id为非自增才能删除。直接删除会报错：

mysql> desc user;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)     | YES  |     | NULL    |                |
| age   | int(11)          | YES  |     | NULL    |                |
| email | varchar(255)     | YES  |     | NULL    |                |
| sign  | int(11)          | YES  |     | NULL    |                |
| grad  | varchar(255)     | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> alter table user drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> show create table user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `sign` int(11) DEFAULT NULL,
  `grad` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> alter table user modify id int(10) unsigned not null;
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> desc user;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(255)     | YES  |     | NULL    |       |
| age   | int(11)          | YES  |     | NULL    |       |
| email | varchar(255)     | YES  |     | NULL    |       |
| sign  | int(11)          | YES  |     | NULL    |       |
| grad  | varchar(255)     | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table user drop primary key;
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> desc user;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   |     | NULL    |       |
| name  | varchar(255)     | YES  |     | NULL    |       |
| age   | int(11)          | YES  |     | NULL    |       |
| email | varchar(255)     | YES  |     | NULL    |       |
| sign  | int(11)          | YES  |     | NULL    |       |
| grad  | varchar(255)     | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

外键约束（FOREIGN KEY）

基本概念：

外键约束（FOREIGN KEY）用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。外键是表的一个字段，不是本表的主键，但对应另一个表的主键。定义外键后，不允许删除另一个表中具有关联关系的行。外键的主要作用是保持数据的一致性、完整性.主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。

注意：应该先创建主表，而且两个关联表必须为innodb，不然会报错：

ERROR 1824 (HY000): Failed to open the referenced table 'tb_edu'

完整测试小案例：

创建主表person_edu：

create table person_edu(
    id int unsigned primary key,
    edu char(20) not null default '');
mysql> desc person_edu;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| edu   | char(20)         | NO   |     |         |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

为person_edu表中的edu字段增加唯一性约束：

mysql> alter table person_edu add unique(edu);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc person_edu;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| edu   | char(20)         | NO   | UNI |         |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

插入数据：

INSERT INTO `db_edu`.`person_edu`(`id`, `edu`) VALUES
(1, '小学'),
(2, '初中'),
(3, '高中'),
(4, '本科'),
(5, '硕士'),
(6, '博士');

创建从表（从表person的edu_id成为 person_edu的id字段的外键）：

create table person(
id int unsigned auto_increment,
name char(20) not null default '',
age tinyint not null default 0,
edu_id int unsigned not null default 1,
email char(40) not null default '',
primary key(id,name),
constraint FK_edu foreign key(edu_id) references person_edu(id)
);

查看表的表述：

mysql> desc person;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | char(20)         | NO   | PRI |         |                |
| age    | tinyint(4)       | NO   |     | 0       |                |
| edu_id | int(10) unsigned | NO   | MUL | 1       |                |
| email  | char(40)         | NO   |     |         |                |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> show create table person\G;
*************************** 1. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `age` tinyint(4) NOT NULL DEFAULT '0',
  `edu_id` int(10) unsigned NOT NULL DEFAULT '1',
  `email` char(40) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`name`),
  KEY `FK_edu` (`edu_id`),
  CONSTRAINT `FK_edu` FOREIGN KEY (`edu_id`) REFERENCES `person_edu` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

为从表插入数据：

INSERT INTO `db_edu`.`person`( `name`, `age`, `edu_id`, `email`) VALUES ('wang', 26, 3, 'li@qq.com'),
('zhao', 25, 2, 'li3@qq.com'),
 ('qian', 32, 6, 'lfi@qq.com'),
 ('sun', 42, 1, 'lfafi@qq.com'),
 ('zhou', 34, 3, 'lfa2i@qq.com'),
 ('wu', 53, 5, 'lgfi@qq.com'),
 ('zheng', 64, 4, 'l532i@qq.com'),
 ('feng', 67, 1, 'lddi@qq.com'),
 ('chen', 25, 3, 'lgi@qq.com'),
 ('chu', 72, 4, 'lfasi@qq.com'),
 ('wei', 81, 5, 'lhasi@qq.com'),
 ('yang', 19, 6, 'lfai@qq.com');

删除从表外键

mysql> alter table person drop foreign key FK_edu;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table person\G;
*************************** 1. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `age` tinyint(4) NOT NULL DEFAULT '0',
  `edu_id` int(10) unsigned NOT NULL DEFAULT '1',
  `email` char(40) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`name`),
  KEY `FK_edu` (`edu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

ERROR:
No query specified

通过alter方式为现有的表增加外键

mysql> alter table person add constraint FK_edu foreign key(edu_id) references person_edu(id);
Query OK, 12 rows affected (0.06 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> show create table person\G;
*************************** 1. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `age` tinyint(4) NOT NULL DEFAULT '0',
  `edu_id` int(10) unsigned NOT NULL DEFAULT '1',
  `email` char(40) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`name`),
  KEY `FK_edu` (`edu_id`),
  CONSTRAINT `FK_edu` FOREIGN KEY (`edu_id`) REFERENCES `person_edu` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

ERROR:
No query specified

删除没有关联的表：

drop table if exists user_copy;

删除有关联的表

如果直接删除主表，则会显示失败。原因是会破坏表的完整性。如果必须要删除，则可以先删除与它关联的子表，再删除主表。但是，有的情况下可能需要保留子表而只删除父表，可以通过取消关联的外键，就可已删除父表了。

mysql> drop table if exists person_edu;
ERROR 3730 (HY000): Cannot drop table 'person_edu' referenced by a foreign key constraint 'FK_edu' on table 'person'.
mysql>

更新数据

插入数据

指定字段插入：
insert  into member (user_name,gender,weight,birth) values
('lgj','1',55.5,'1963-11-22'),
('wsl','0',52.5,'1965-08-27');
对于自增的主键id，赋值时直接给null：
insert into student (id,name,age,sex) values(null,'刘星星',25,'男');
插入数据时可以省略字段列表 但必须要按顺序给值
insert into student values(null,'刘国杰',54,'男');
插入多条数据
insert into student (id,name,age,sex) values(null,'李梦强',20,'男'),(null,'段亚辉',21,'男'),(null,'王水兰',52,''女');

删除数据

delete from student where id >= 7;
查看删除该数据时数据库操作的描述（只是产看，并没有删除）
desc delete from user where id=12\G;
注意：普通删除后会保留删除的id号，不再让数据使用。truncate会删除表内容，重新建立表。
truncate  student;

修改数据

update student set age=28,sex='女' where id=1;

update student set age=26,name='段LL' where name='鲁大师';

   转载规则


《数据库入门1》 由 刘星星 采用 知识共享署名 4.0 国际许可协议 进行许可。
 上一篇
数据库入门2 数据库入门2
检索数据无查询条件的查询基本查询：select *from student ;倒序查询5条数据：select *from student order by id desc limit 0,5 ;基础查询：查询数据量（设置别名： 字段名
2018-10-02 MySQL
MySQL 检索数据
下一篇 
iOS中的内存管理总结 iOS中的内存管理总结
内存管理iOS通过以下三种方式来管理内存方案： TaggedPointer //非指针型的isa NONPOINTER_ISA //包含了引用计数表 和 弱引用表 散列表TaggedPointerTaggedPointer对象是为了
2018-08-27 iOS
iOS 总结提高 内存管理
  目录