mysql基本内容学习过程
1， 数据库的登录：mysql -u 用户名(root) -p密码 -P (端口) -h服务器名（本地表示:127.0.0.1）
2. 更改数据库显示：mysql -u root -p --prompt \u@\h \d>
--prompt: 使用--prompt命令进行修改；
\h：表示本机(localhost)
\u:当前登录用户(用户名)
\d:当前操作数据库（database name）
3. 数据库的命令操作：
1），查询所有数据库命令： root@localhost (none)>SHOW DATABASES;
2) ，使用某个数据库：
root@localhost (none)>USE table2
Database changed
root@localhost table2>
3），创建一个数据库：CREATE DATABASE 数据库名称：
root@localhost table2>SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| t2 |
| table1 |
| table2 |
| tables3 |
| test |
+--------------------+
8 rows in set (0.00 sec)
root@localhost table2>SHOW CREATE DATABASE table3;
ERROR 1049 (42000): Unknown database ‘table3‘
查看某一个数据库：
root@localhost table2>SHOW CREATE DATABASE tables3;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| tables3 | CREATE DATABASE `tables3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
更新一个数据库：使用ALTER命令：
root@localhost table2>SHOW CREATE DATABASE tables3;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| tables3 | CREATE DATABASE `tables3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
更改数据库的编码格式：
root@localhost table2>ALTER DATABASE tables3 CHARACTER SET = gbk;
Query OK, 1 row affected (0.00 sec)
root@localhost table2>show create database tables3;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| tables3 | CREATE DATABASE `tables3` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
root@localhost table2>
删除数据库：DROP TABLENAME;
root@localhost table2>SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| t2 |
| table1 |
| table2 |
| tables3 |
| test |
+--------------------+
8 rows in set (0.00 sec)
root@localhost table2>DROP DATABASE tables3;
Query OK, 0 rows affected (0.00 sec)
root@localhost table2>SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| t2 |
| table1 |
| table2 |
| test |
+--------------------+
7 rows in set (0.00 sec)
root@localhost table2>
一， 数据库的数据类型:
整形：
1. YINYINT
2. SMALLINT
3, MEDIUMINT
4. INT
5. BIGINT
浮点型：
1， FLOAT[M,D]
2，DOUBLE[M,D]
日期：
1. YEAR
2. TIME
3， DATE
4， DATETIME
5， TIMESTAMP
字符型：
1， CHAR(M)
2， VARCHAR(M)
3, TINYTEXT
4, TEXT
5, DEDIUMTEXT
6, LONGTEXT
7, ENUM 枚举值
8. SET
二， 创建数据库表：CREATE TABLE(行和数据类型)
root@localhost test>CREATE TABLE tb3(
-> username VARCHAR(20),
-> age TINYINT UNSIGNED,
-> salary FLOAT(8,2) UNSIGNED
-> );
Query OK, 0 rows affected (0.09 sec)
查看数据表是否存在：
root@localhost test>SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| tb3 |
+----------------+
1 row in set (0.00 sec)
查看当前数据库中的某一个库的表：
root@localhost test>SHOW TABLES FROM mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
查看某一张表的结构：
root@localhost test>SHOW COLUMNS FROM tb3;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(8,2) unsigned | YES | | NULL | |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
root@localhost test>
为表中添加数据：INSERT [INTO] TABLE_NAME(字段信息) VALUE(字段值)；
root@localhost test>INSERT tb3 VALUES(‘WUYANLONG‘);
ERROR 1136 (21S01): Column count doesn‘t match value count at row 1
root@localhost test>insert tb3(username,age) VALUES(‘luoting‘,21);
Query OK, 1 row affected (0.04 sec)
root@localhost test>insert tb3(username,age) VALUES(‘tom‘,22);
Query OK, 1 row affected (0.03 sec)
root@localhost test>SHOW TABLSE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresp
onds to your MySQL server version for the right syntax to use near ‘TABLSE‘ at line 1
root@localhost test>SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| tb1 |
| tb3 |
+----------------+
2 rows in set (0.00 sec)
root@localhost test>SELECT * FROM tb3;
+-----------+------+----------+
| username | age | salary |
+-----------+------+----------+
| WUYANLONG | 25 | 78563.21 |
| luoting | 21 | NULL |
| tom | 22 | NULL |
+-----------+------+----------+
3 rows in set (0.00 sec)
数据库表中的字段允许为空和非空（NULL, NOT NULL）;
root@localhost test>CREATE TABLE tb2(
-> username VARCHAR(20) not null,
-> age tinyint unsigned NULL
-> );
1. 数据库中的自动编号： AUTO_INCREMENT属性：必须和主键组合使用。起始值为1
一张数据表中只存在一个主键，主键约束。
主键保证记录的唯一性。
主键自动为NOT NULL
root@localhost test>CREATE TABLE tb4(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.17 sec)
root@localhost test>
root@localhost test>show columns from tb4;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)
AUTO_INCREMENT 必须与主键一起使用，但是主键不一定与AUTO_INCREMENT一起使用；
2. 主键(PRIMARY KEY)是数据库表中的一种约束，另外一种约束是(UNIQUE KEY),主键是非空且唯一的，当同一张表中
要建立多个魏一建约束的时候UNIQUE KEY就有勇武之地了。
唯一约束(一张数据表可以存在多个)
唯一约束可以保证记录的唯一性。
唯一性约束的字段可以为空值（NULL）
3. 默认约束（DEFAULT），如果在插入记录是没有为该字段插入值，则系统插入默认值。
root@localhost test>
root@localhost test>CREATE TABLE tb7(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL UNIQUE KEY,
-> sex ENUM(‘1‘,‘2‘,‘3‘) DEFAULT ‘3‘
-> );
Query OK, 0 rows affected (0.05 sec)
root@localhost test>
root@localhost test>
root@localhost test>SHOW COLUMNS FROM tb7;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| sex | enum(‘1‘,‘2‘,‘3‘) | YES | | 3 | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
1. 约束：
约束保证了数据的完整性和一致性；
约束分为表级约束和列级约束；
约束类型包括：
NUT NULL（非空约束）
PRIMARY KEY(主键约束)
UNIQUE KEY（唯一约束）
DEFAULT(默认约束)
FOREIGN KEY(外键约束)
保证数据的一致性，完整性，实现一对一，多对多的关系。
外键约束的要求：
1. 父表和子表必须使用相同的存储引擎，而且禁止使用临时表；
2. 数据表的存储引擎必须为InoDB;//在配置文件中修改default-storage-engine=INNODB
3 外键列和参照列必须具有相似的数据类型其中数据的长度或是否有符号位必须相同；而字符的长度可以不同
4. 外间列和参照列必须创建索引。如果外键列不存在索引的话MySQL将自动创建索引。
测试：
首先创建一张“province”表：
mysql> CREATE TABLE province(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> pname VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.10 sec)
查看主表province表的信息：
mysql> SHOW CREATE TABLE province;//查看主表的存储类型是不是INNODB
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| province | CREATE TABLE `province` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`pname` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE users(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(20) UNSIGNED NOT NULL,
-> p_id BIGINT,
-> FOREIGN KEY (p_id) REFERENCES province(id)
-> );
创建子表：user表
ERROR 1072 (42000): Key column ‘p_id‘ doesn‘t exist in table
mysql> CREATE TABLE users(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY (pid) REFERENCES province (id)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql>
分析如下：user表称为子表(有外键列的表)，外键表（province表，称之为主表。）
外键列和参照列必须穿件索引：
查看主表的索引：
mysql> SHOW INDEXES FROM province\G;//查看表的索引
*************************** 1. row ***************************
Table: province
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
外键约束的参照操作：
1. CASCADE : 从父表删除或更新且自动删除或自动更新子表中匹配的行；
2. SET NULL : 从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
3. RESTRICT : 拒绝对父表的删除或更新操作。
4. NOT ACTION : 标准SQL的关键字，在mysql中与RESTRICT相同。
必须现在父表中先添加记录，然后才能添加子表记录；
在实际的开发过程中一般很少使用物理的外键约束，一般都是使用逻辑的外键约束，因为物理外键约束只有INNODB存储引擎才支持。
表级约束和列级约束：
对一个数据列建立的约束，称为列级约束；
对多个数据列建立的约束，称为表级约束；
列级约束既可以在列定义时声明，也可以在列定以后声明。
表级约束只能在列定义之后声明。
在实际开发过程中表级约束用的比较多。（DEFAULT,FOREGIN KEY ,PRIMARY KEY CHECK......）;
修改数据表：ALTER TABLE tbl_name ADD[COLUMN] col_name column_definition[FIRST | AFTER col_name]
例子：（添加一列）
mysql> SHOW COLUMNS FROM user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)
mysql> ALTER TABLE user1 ADD age TINYINT unsigned NOT NULL DEFAULT 10;
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
4 rows in set (0.06 sec)
mysql> ALTER TABLE user1 ADD password VARCHAR(32) NOT NULL AFTER username;
Query OK, 3 rows affected (0.17 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.05 sec)
mysql> ALTER TABLE user1 ADD truename VARCHAR(20) NOT NULL FIRST;
Query OK, 3 rows affected (0.18 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| truename | varchar(20) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
6 rows in set (0.05 sec)
添加多列：
ALTER TABLE tbl_name ADD [COLUUMN](col_namecolumn_definition,‘.....)
修改数据表：
ALTER TABLE tbl_nameDROP [COLUMN] col_name;
mysql> SHOW COLUMNS FROM user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| truename | varchar(20) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
6 rows in set (0.05 sec)
mysql> LTER TABLE user1 DROP truename;
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 ‘LTER TABLE user1 DROP truename‘ a
t line 1
mysql> ALTER TABLE user1 DROP truename;
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.04 sec)
mysql> ALTER TABLE user1 DROP password, DROP age;
Query OK, 3 rows affected (0.15 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.06 sec)
mysql> ALTER TABLE user1 DROP password, DROP age;
ERROR 1091 (42000): Can‘t DROP ‘password‘; check that column/key exists
mysql>
可以添加主键约束，唯一约束，外键约束，也可以删除这些约束。
删除主键约束：
ALTER TABLE tbl_name DROP PRIMARY KEY;(一张表只有一个主键)
删除唯一约束：
ALTER TABLE tbl_name DROP {INDEX | KEY} username;
删除外键约束：
ALTER TABLE tbl_name DROP foreign key key_info;
修改列定义：
ALTER TABLE tbl_name MODIFY[COLUMN] col_name column_definition [FIRST|AFTER col_name]
mysql> ALTER TABLE user1 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> ALTER TABLE user1 MODIFY pid SMALLINT UNSIGNED NOT NULL first;
Query OK, 3 rows affected (0.18 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| pid | smallint(5) unsigned | NO