MySQL基本指令
第一章 MySQL概述
1.1 MySQL简介
由瑞典MySQL AB公司开发，后被Oracle收购。
开源工具，不具有正版和盗版之分。分为社区办和企业版。
是世界上最流行的开源数据库。全称为My structured query language，用于存储数据、数据的查询、更新和管理关系数据库系统（relational database management system）
1.2 基本概念
数据库：电子化的文件柜；
关系型数据库：采用二位表格结构来组织数据的数据库，有行和列。即将数据组织为相关的行和列；
表可以当做一个文件柜，将其中的一行数据看做一个文件，也称之为记录；
列：即一个字段；
主键：为了确保表中每一条记录的唯一性，可对多个字段进行主键设置；
索引：对表中数据进行排序，以快速查找信息；
外键：如果表中的一个字段是另外一个表中的主键，则该字段为外键。可以建立两个表之间的联系。
1.3 安装及配置
- MSI安装（Windows Installller）（容易掌握）
- ZIP安装：可访问后面的网址，有详细教程：http://www.cnblogs.com/Deribs4/p/5415156.html
1.4 MySQL目录结构
bin: 存储可执行文件；
data: 存储数据文件；
docs: 存储文档；
include: 存储包含的头文件；
lib: 存储库文件；
share: 错误消息和字符集文件
1.5 使用MySQL
1.5.1停止和启动MySQL服务
1. 通过【计算机-服务-MySQL】右击进行关闭/启动；
2. 通过win+R 呼出cmd ，使用管理员权限，输入命令行：
启动：net start mysql
关闭：net stop mysql
1.5.2 MySQL的登陆登出
- mysql -uroot -p -P3306 -h :端口号默认3306，本地地址为127.0.0.1
2.mysql>exit mysql>quit mysql>\q 登出数据库。
3.可以用键盘的上下键翻阅历史命令行。
4.使用cls回车进行命令行清屏。
1.5.3修改 MySQL提示符
1. mysql -uroot -ppassword --prompt \h
用户名 密码 修改命令 指定为localhost
2. mysql>PROMPT 提示符。
1.5.4 MySQL常用命令
1.5.5 MySQL语句规范
1.5.6 MySQL操作
1.5.6.1 创建数据库
mysql>CREATE DATABASE test1;
1.5.6.2 查看当前服务器下的数据库列表
show databases;
show create database test;
1.5.6.3 修改数据库
alter database test character set = utf8;
1.5.6.4 删除数据库
drop database test;
第二章 数据类型和操作数据表
2.1 MySQL数据类型
整型、 浮点型、日期格式、字符型
2.2 创建数据表
2.2.1 打开数据库 USE
C:\Windows\system32>mysql -uroot -proot -P3306 -h127.0.0.1
mysql>SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
mysql> USE test1; //打开数据库
mysql>SELECT DATABASE(); //显示当前选择的数据库名称
2.2.2 创建数据表 CREATE TABLE
CREATE TABLE[IF NOT EXIT] table_name(
column_name data_type,
...
)
mysql> CREATE TABLE table1(
-> username VARCHAR(20),
-> age TINYINT UNSIGNED,
-> salary FLOAT(8,2) UNSIGNED //最后一个字段后面不需要加逗号
-> );
2.3 删除数据表DROP TABLE
DROP TABLE table1;
2.4 修改数据表
2.4.1 添加单列 ADD
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
在不指定新加入列位置的情况下，将会默认建在数据表的最下方。
mysql> ALTER TABLE user1 ADD password VARCHAR(32) NOT NULL AFTER id;
Query OK, 0 rows affected (1.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| password | varchar(32) | NO | | NULL | |
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
2.4.2 添加多列
ALTER TABLE tbl_name ADD [COLUMN]( col_name column_definition, ...)
不能指定位置关系，默认创建在数据表的最下方。
2.4.3 删除列 DROP
ALTER TABLE tbl_name DROP col_name
mysql> ALTER TABLE user1 DROP id,DROP age;
2.4.4 修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
mysql> ALTER TABLE user2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
//将大类型的数据改为小类型时，可能会发生数据丢失，需要注意。
2.4.5 修改列名称
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
mysql> ALTER TABLE user2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
2.4.6 修改数据表名
ALTER TABLE tbl_name RENAME TO new_tbl_name;
ALTER TABLE uer2 RENAME TO user2gogogo;
2.5 查看数据表SHOW TABLES
SHOW TABLES [ FROM db_name]
mysql> SHOW TABLES FROM mysql; //当前数据库位置不会发生改变
如果用户当前在test1的数据库中，选择查看其它数据库中的数据表，是不会改变当前所在的位置的。
2.5.1 查看数据表结构SHOW COLUMNS FROM
SHOW COLUMNS FROM table1; //接上述2.1.5.2
2.6 记录的插入和查找
2.6.1 插入记录INSERT
INSERT [INTO] table1_name[(col_name,...)] VALUES(val,...)
当省略上述col_name所对应的字段名时，默认要对所有的字段都进行赋值。
mysql> INSERT table1 VALUES("Tom",27,5566.23); //对所有的字段进行赋值
mysql> INSERT table1(username,age) VALUES("Kate",23); //部分字段赋值
2.6.2 查找记录 SELECT
SELECT expr, ... FROM tb1_name
mysql> SELECT * FROM table1;
上述*号指的是所有的字段，而不是所有的记录。
2.7 必填项设置NULL & NOT NULL
NULL:允许为空值；
NOT NULL:不允许为空值；
mysql> CREATE TABLE table2(
-> username VARCHAR(20) NOT NULL,
-> age TINYINT UNSIGNED NULL
-> );
mysql> INSERT table2 VALUES(NULL,23);
ERROR 1048 (23000): Column 'username' cannot be null //此时不允许名字为空
2.8 Mysql自动编号 AUTO_INCREMENT
自动编号，必须与主键结合使用；
默认情况下，起始值为1，增量为1；
必须为数值型，可以为float和double型，但是其小数位数必须为0。例如float(6,0)
自动编号中间被删除后，即时不连续，也不会再自动补充
mysql> CREATE TABLE table3(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, //自动编号
-> username VARCHAR(30) NOT NULL
-> );
mysql> INSERT table3(username) VALUES("Jerry"); //为uername赋值，自动编号不管
Query OK, 1 row affected (0.38 sec)
mysql> INSERT table3(username) VALUES("Mary");
Query OK, 1 row affected (0.05 sec)
mysql> INSERT table3(username) VALUES("Mike");
Query OK, 1 row affected (0.04 sec)
mysql> SELECT* FROM table3;
+----+----------+
| id | username |
+----+----------+
| 1 | Jerry |
| 2 | Mary |
| 3 | Mike |
+----+----------+
2.9 主键约束 PRIMARY KEY
一张数据表中只能有一个主键；
主键保持记录的唯一性；
自动为NOT NULL，不允许为空；
接上述2.6章节中的内容，由于创建的table3中，指定id为主键PRIMARY_KEY。
mysql>SHOW COLUMNS FROM table3;
+----------+--------------------------------------+-------+-------+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------------------------------+-------+-------+------------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(30) | NO | | NULL | |
+----------+--------------------------------------+--------+-------+------------+----------------+
2 rows in set (0.00 sec)
主键表示在该数据表中，该条记录的唯一性。在一个数据表中，2条记录的主键不允许相同。
2.10 唯一约束 UNIQUE KEY
保证记录的唯一性；
字段可以为空值，为NULL；
一张数据表上可以存多个unique key；
mysql> CREATE TABLE table5(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) UNIQUE KEY,
-> age TINYINT UNSIGNED NOT NULL
-> );
mysql> SHOW COLUMNS FROM table5;
+-------------+-------------------------------+----------+--------+-----------+----------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------------------+---------+---------+-----------+----------------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | UNI | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
+--------------+-------------------------------+---------+---------+-----------+-----------------------+
mysql> INSERT table5 (username,age) VALUES("Tom",23);
Query OK, 1 row affected (0.37 sec)
mysql> INSERT table5 (username,age) VALUES("Tom",23);
ERROR 1062 (23000): Duplicate entry 'Tom' for key 'username'
//限制数值唯一。虽然可以为空值，但是unique key往往整个数据表中只有一条记录的unique key为空。
2.11 默认约束 DEFAULT
当插入记录时，如果没有明确为字段赋值，则自动赋予其默认值。
2.12 外键约束 FOREIGN KEY
保持数据完整性，一致性；
实现数据一对一或者一对多的关系；
决定了MySQL是一种关系型数据库（Relational database management system）。
外键约束的要求：
1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表；
（注：具有外键链的表称为子表，子表所参照的表称为父表）
2.数据表的存储引擎必须是InnoDB；
3.外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同；
4.外键列和参照列必须创建索引。如果参照列不存在索引的话，MySQL将会自动创建索引。
2.12.1 编辑数据表默认存储引擎
在MySQL编辑文件my.ini中找到
default-storage-engine = INNODB
修改完成后重启MySQL
2.12.2 设置外键
mysql> CREATE TABLE province(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL
-> );
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) //指定pid为外键，并链接到province表
-> );
上述代码中，包含外键的user表为子表，而子表所参照的表province为父表。
2.12.3 索引查看
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:
即：创建主键时，会自动创建索引。
2.12.4 外键约束的参照操作
即：在外键约束创建以后，在更新表的时候，子表是否也进行相应的操作。
mysql> CREATE TABLE user1(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY (pid) REFERENCES province (id) ON DELETE CASCADE //指定pid为外键
-> );
mysql> INSERT province (name) VALUES("Apple");
mysql> INSERT province (name) VALUES("Watermallon");
mysql> INSERT province (name) VALUES("Banana");
mysql> SELECT* FROM province;
+------+-------------+
| id | name |
+------+-------------+
| 1 | Apple |
| 2 | Watermallon |
| 3 | Banana |
+----+-------------+
mysql> INSERT user1(username,pid) VALUES("Huawei",3);
mysql> INSERT user1(username,pid) VALUES("iPhone",1);
mysql> INSERT user1(username,pid) VALUES("OPPO",2);
mysql> SELECT* FROM user1;
+----+--------------+---------+
| id | username | pid |
+----+--------------+----------+
| 2 | Huawei | 3 |
| 3 | iPhone | 1 |
| 4 | OPPO | 2 |
+----+---------------+---------+
mysql> DELETE FROM province WHERE id=3; //从父表中删除id=3的记录
mysql> SELECT* FROM province;
+----+-------------+
| id | name |
+----+-------------+
| 1 | Apple |
| 2 | Watermallon |
+----+-------------+
2 rows in set (0.00 sec) //父表中id=3的记录被删除了
mysql> SELECT* FROM user1;
+----+----------+------+
| id | username | pid |
+----+----------+------+
| 3 | iPhone | 1 |
| 4 | OPPO | 2 |
+----+----------+------+
2 rows in set (0.00 sec) //子表中也被删除
第三章 约束
3.1约束的概念及分类
保证数据的完整性和一致性；
约束分为表级约束和列级约束：针对1个字段的成为列级约束；针对2个及以上字段来进行使用，称为表级约束；
列级约束：既可以在列定义时声明，也可以在列定义后声明；
表级约束：只能在列定义以后再声明；表级约束在实际开发过程中几乎很少用到。
按照功能划分，分为：
NOT NULL 非空约束；
PRIMARY KEY 主键约束；
UNIQUE KEY 唯一约束；
DEFAULT 默认约束；
FOREIGN KEY 外键约束；
3.2添加/删除主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY[index_type](index_col_name, ...)
mysql> ALTER TABLE user2 ADD CONSTRAINT PK_user2_id PRIMARY KEY(id); //添加主键约束
mysql>ALTER TABLE user2 DROP PRIMARY KEY; //删除主键约束
3.3添加/删除唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY][index_name][index_type](index_col_name, ...)
ALTER TABLE tbl_name DROP {INDEX|KEY}index_name
3.4添加/删除外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY[index_type](index_col_name, ...) reference_definition
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
3.5添加/删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFULT literal | DROP DEFAULT}
mysql> ALTER TABLE user2 ALTER id SET DEFAULT 15; //设定默认值
mysql> ALTER TABLE user2 ALTER id DROP DEFAULT; //去除默认值
第四章 记录的操作
4.1插入记录
4.1.1 INSERT
INSERT [INTO] tbl_name [(col_name,...)] {VALUES |VALUE}({expr|DEFAULT},...),(...),...
单条插入：
mysql> INSERT user4 VALUES(NULL,"Jerry","password",34,1);
多条插入：
mysql> INSERT user4 VALUES(NULL,"Alin","11223344",19,0),(NULL,"Bob","12345677",DEFAULT,1);
//多条数据之间使用逗号作为分隔符，使用DEFAULT赋值时默认使用默认值。
4.1.2 INSERT SET
INSERT [INTO] tbl_name SET col_name = {expr|DEFAULT},...
与上述直接使用INSERT语句插入的区别在于，此方法可以使用子查询（SubQuery）。在实际开发过程中，这种方式使用较少，一次只能插入一条记录。
mysql> INSERT user4 SET username="Cherry",password="password";
4.1.3 INSERT SELECT
INSERT [INTO] tbl_name [(col_name, ... )] SELECT ...
先按照select语句查找出结果，再将新的记录添加在查找到的数据附近。
4.2删除记录
DELETE FROM tbl_name [WHERE where_condition]
mysql> DELETE FROM user4 WHERE id=3;
4.3更新记录
UPDATE [LOW PROIRITY] [IGNORE] table_reference SET col_name1 = {expr1 | DEFAULT}[col_name2 = {expr2 | DEFAULT}]...[WHERE where condition]
mysql> UPDATE user4 SET age=age+5;
mysql>UPDATE user4 SET age=age+id WHERE id%2=0;
4.4查找记录
数据表的操作中，查找操作几乎占到了80%，本节是重中之重。
4.4.1 SELECT_expr
查询表达式：
每个表达式表示想要的一列，必须至少有一个；
多个列之间以英文逗号分隔；
星号（*）表示所有列。tbl_name.*可以表示命名表的所有列； //user4.id,user4.username
查询表达式可以使用[AS]alias_name为其赋予别名，别名可用于BROUP BY，ORDER BY，或HAVING子句；
- 查询列：
SELECT id,username,password FROM user4;
查询时，列显示的先后顺序可以和原表有所不同。
2.关于别名：
mysql> SELECT username AS name,password AS mima FROM user4;
+-----------+--------------+
| name | mima |
+-----------+--------------+
| Jerry | password |
| Alin | 11223344 |
| Cherry | password |
+-----------+-------------+
使用别名时，一定要加上AS关键词，否则如果忘记了逗号，数据表会错把第二个名字当做别名显示。
4.4.2 WHERE
where用于对条件的过滤，如果没有使用where则默认显示所有的记录
在where表达式中，可以使用MySQL支持的函数或运算符
4.4.3 GROUP BY
SELECT sex FROM user4 GROUP BY sex;
对查询到的结果依据by后面的条件进行分组。
4.4.4 HAVING
对查询到结果进行分组时，使用having对分组进行条件的指定。
4.4.5 ORDER BY
[ORDER BY {col_name | expr |position}[ASC | DESC], ... ]
用于对查询结果进行排序。
mysql> SELECT* FROM user4 ORDER BY id DESC;
依次按照第一个条件、第二个条件进行排序。
4.4.6 LIMIT
限制查询结果返回的数量。默认情况是返回查询到的所有记录的。
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
SELECT* FROM user4 LIMIT 3; //从查询结果中返回3条数据
SELECT* FROM user4 LIMIT 3,2; //从查询到的第4条记录开始，返回2条记录。和数组相同从0开始计数。
