1. 安装

2. 基本使用

提示符

常用格式:

格式	意义
\D	完整日期格式
\d	显示当前数据库
\h	服务器名
\u	当前用户名

1.登入时指定
mysql –prompt=zrd

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15404
Server version: 5.6.31-77.0-log Percona Server (GPL), Release 77.0, Revision 5c1061c

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

zrd

2.在线修改格式

PROMPT '\h=\u=\D=\d'
PROMPT set to ''\h=\u=\D=\d''
'localhost=root=Thu Aug 25 23:39:37 2016=(none)'

3.配置文件中指定

一些基本函数

语句	意义
SELECT VERSION ();	版本
SELECT USER ();	当前登入的用户名主机名
SELECT NOW();	当前时间
DELIMITER //	修改默认分隔符为//，原来是’ ; ‘
mysql>\T /tmp/mysql_sql_.log	Logging to file ‘/tmp/mysql_sql_.log’ 将此会话所执行的语句及结果全部保存到指定的文件中
\t	mysql>\t Outfile disabled. 取消保存执行语句记录到文件中

注释

注释的内容将不被执行

方法1
# 注释的内容
方法2
-- 注释的内容

3.数据库相关操作(DDL数据库定义语言)

{} 花括号必须要写写上相关的内容
| 二选一
[] 方括号是可选的

1.数据库创建

创建语法

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification]     [DEFAULT] CHARACTER SET [=] charset_name  | [DEFAULT] COLLATE [=] collation_name

mysql> CREATE DATABASE zrd_test;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE IF NOT EXISTS zrd_test;
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS ; #查看警告
+-------+------+---------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+---------------------------------------------------+
| Note  | 1007 | Can't create database 'zrd_test'; database exists |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)

指定字符编码
mysql> CREATE DATABASE IF NOT EXISTS zrd_test2 DEFAULT CHARACTER SET = 'GBK';

mysql> SHOW CREATE SCHEMA zrd_test2;
+-----------+-------------------------------------------------------------------+
| Database  | Create Database                                                   |
+-----------+-------------------------------------------------------------------+
| zrd_test2 | CREATE DATABASE `zrd_test2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+-----------+-------------------------------------------------------------------+

2.查看数据库

语法

SHOW {DATABASES | SCHEMAS} [like_or_where]

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| zabbix             |
| zrd_test           |
+--------------------+
6 rows in set (0.00 sec)

3. 查看指定数据库的定义

语法

SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

# 方法1
mysql> SHOW CREATE DATABASE zabbix;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| zabbix   | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

# 方法2
mysql> SHOW CREATE SCHEMA zrd_test ;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| zrd_test | CREATE DATABASE `zrd_test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

4.修改数据库的编码方式

语法

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
    UPGRADE DATA DIRECTORY NAME

alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

mysql> ALTER DATABASE zrd_test2 DEFAULT CHARACTER SET=utf8;
Query OK, 1 row affected (0.02 sec)


mysql> SHOW CREATE DATABASE zrd_test2;
+-----------+--------------------------------------------------------------------+
| Database  | Create Database                                                    |
+-----------+--------------------------------------------------------------------+
| zrd_test2 | CREATE DATABASE `zrd_test2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

5.切换数据库

mysql> USE zrd_test2; # 切换数据库
Database changed
mysql> SELECT DATABASE(); # 显示当前正在使用的数据库
+------------+
| DATABASE() |
+------------+
| zrd_test2  |
+------------+
1 row in set (0.00 sec)

6.删除数据库

语法:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

mysql> DROP DATABASE zrd_test2;
Query OK, 0 rows affected (0.03 sec)

3. mysql 数据类型

3.1 整数型

3.1.1 TINYINT

Name: 'TINYINT'
Description:
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

3.1.2 SMALLINT

Name: 'SMALLINT'
Description:
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.

3.1.3 MEDIUMINT

Name: 'MEDIUMINT'
Description:
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.

3.1.4 INT

Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

3.1.5 BIGINT

-->? BIGINT
Name: 'BIGINT'
Description:
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

3.1.6 BOOLEAN BOOL

Name: 'BOOLEAN'
Description:
BOOL, BOOLEAN
These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:

测试整型

-- 测试整型
CREATE TABLE IF NOT EXISTS `num`(
`num1` TINYINT  COMMENT '最小的整型',
`num2` SMALLINT,
`num3` MEDIUMINT,
`num4` INT,
`num5` BIGINT
) CHARSET=GB2312;

percona root@localhost:test> DESC num;
+---------+--------------+--------+-------+-----------+---------+
| Field   | Type         | Null   | Key   |   Default | Extra   |
|---------+--------------+--------+-------+-----------+---------|
| num1    | tinyint(4)   | YES    |       |    <null> |         |
| num2    | smallint(6)  | YES    |       |    <null> |         |
| num3    | mediumint(9) | YES    |       |    <null> |         |
| num4    | int(11)      | YES    |       |    <null> |         |
| num5    | bigint(20)   | YES    |       |    <null> |         |
+---------+--------------+--------+-------+-----------+---------+
5 rows in set
Time: 0.003s
# 插入数据
INSERT INTO `num` VALUES(-128,-32768,-8388608,-2147483648,-9223372036854775808);
Query OK, 1 row affected (0.02 sec)
# 查询数据
SELECT *  FROM `num`;
+------+--------+----------+-------------+----------------------+
| num1 | num2   | num3     | num4        | num5                 |
+------+--------+----------+-------------+----------------------+
| -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |
+------+--------+----------+-------------+----------------------+
1 row in set (0.00 sec)
# 插入错误不符合规范的数据
INSERT INTO `num` VALUES(-129,-32768,-8388608,-2147483648,-9223372036854775808);
Query OK, 1 row affected, 1 warning (0.02 sec)
# 可发现插入不规则的语句后有提示
-->SHOW warnings ;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1264 | Out of range value for column 'num1' at row 1 |
+---------+------+-----------------------------------------------+
1 row in set (0.01 sec)
-->SELECT * FROM num;
+------+--------+----------+-------------+----------------------+
| num1 | num2   | num3     | num4        | num5                 |
+------+--------+----------+-------------+----------------------+
| -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |
| -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |
+------+--------+----------+-------------+----------------------+
2 rows in set (0.00 sec)

# 无符号整型
-- 插入无符号整型 UNSIGNED
CREATE TABLE `num2`(
num1 TINYINT UNSIGNED COMMENT 'UNSIGNED 是无符号的,计数是从0开始',
num2 TINYINT 
) CHARSET=GB2312;
# 查看表结构，发现多了一个unsigned
-->DESC num2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| num1  | tinyint(3) unsigned | YES  |     | NULL    |       |
| num2  | tinyint(4)          | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 插入一个正常的值
-->INSERT INTO num2 VALUES(0,-12);
Query OK, 1 row affected (0.02 sec)

-->SELECT * FROM num2;
+------+------+
| num1 | num2 |
+------+------+
|    0 |  -12 |
+------+------+
1 row in set (0.00 sec)
#  插入一个不符合的值
-->INSERT INTO num2 VALUES(-10,-12);
ERROR 1264 (22003): Out of range value for column 'num1' at row 1


# 显示正常的长度 ZEROFILL , 它会设置成无符号，0填充
CREATE TABLE IF NOT EXISTS `num3` (
`num1` TINYINT  ZEROFILL COMMENT '最小的整型',
`num2` SMALLINT ZEROFILL,
`num3` MEDIUMINT ZEROFILL,
`num4` INT ZEROFILL,
`num5` BIGINT ZEROFILL
) CHARSET=GB2312;

-->DESC num3;
+-------+--------------------------------+------+-----+---------+-------+
| Field | Type                           | Null | Key | Default | Extra |
+-------+--------------------------------+------+-----+---------+-------+
| num1  | tinyint(3) unsigned zerofill   | YES  |     | NULL    |       |
| num2  | smallint(5) unsigned zerofill  | YES  |     | NULL    |       |
| num3  | mediumint(8) unsigned zerofill | YES  |     | NULL    |       |
| num4  | int(10) unsigned zerofill      | YES  |     | NULL    |       |
| num5  | bigint(20) unsigned zerofill   | YES  |     | NULL    |       |
+-------+--------------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
# 插入数据查看效果
-->INSERT INTO `num3` VALUES(1,1,1,1,1);
Query OK, 1 row affected (0.02 sec)

# 可以发现没有达到长度的会在前边补0(当做最大值来记录)   一般没用，美观
-->SELECT * FROM num3;
+------+-------+----------+------------+----------------------+
| num1 | num2  | num3     | num4       | num5                 |
+------+-------+----------+------------+----------------------+
|  001 | 00001 | 00000001 | 0000000001 | 00000000000000000001 |
+------+-------+----------+------------+----------------------+
1 row in set (0.00 sec)
# 当达到长度后,不会再补0

无符号整型 UNSIGNED

零填充ZEROFILL

3.2 浮点型

3.2.1 FLOAT

Name: 'FLOAT'
Description:
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

A small (single-precision) floating-point number. Permissible values
are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to
3.402823466E+38. These are the theoretical limits, based on the IEEE
standard. The actual range might be slightly smaller depending on your
hardware or operating system.
# 占用4个字节，单精度浮点型

3.2.2 DOUBLE

Name: 'DOUBLE'
Description:
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

A normal-size (double-precision) floating-point number. Permissible
values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and
2.2250738585072014E-308 to 1.7976931348623157E+308. These are the
theoretical limits, based on the IEEE standard. The actual range might
be slightly smaller depending on your hardware or operating system.
# 双精度浮点型

3.2.3 DECIMAL

Name: 'DECIMAL'
Description:
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

A packed "exact" fixed-point number. M is the total number of digits
(the precision) and D is the number of digits after the decimal point
(the scale). The decimal point and (for negative numbers) the "-" sign
are not counted in M. If D is 0, values have no decimal point or
fractional part. The maximum number of digits (M) for DECIMAL is 65.
The maximum number of supported decimals (D) is 30. If D is omitted,
the default is 0. If M is omitted, the default is 10.
# 占用的长度为字节长度+2 和float一样

测试浮点型

-- 测试浮点型
CREATE TABLE IF NOT EXISTS `num4` (
num1 FLOAT(6,2),
num2 DOUBLE(6,2),
num3 DECIMAL(6,2) COMMENT '定点数不进行四舍五入,对精度要求比较高'
) CHARSET=GB2312;

-->DESC num4;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| num1  | float(6,2)   | YES  |     | NULL    |       |
| num2  | double(6,2)  | YES  |     | NULL    |       |
| num3  | decimal(6,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 插入值测试
INSERT INTO `num4` VALUES(3.141526, 3.141526, 3.141526);
Query OK, 1 row affected, 1 warning (0.03 sec)
# 警告信息, 查看插入的数据被四舍五入截断
-->SHOW WARNINGS ;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Note  | 1265 | Data truncated for column 'num3' at row 1 |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)
-->SELECT * FROM num4;
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 3.14 | 3.14 | 3.14 |
+------+------+------+
1 row in set (0.00 sec)

# 验证
INSERT INTO `num4` VALUES(2.149526, 2.149526, 2.149526);
Query OK, 1 row affected, 1 warning (0.01 sec)

root@localhost Sat Aug 27 21:31:02 2016 [test] 
-->SELECT * FROM num4;
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 3.14 | 3.14 | 3.14 |
| 2.15 | 2.15 | 2.15 |
+------+------+------+
2 rows in set (0.00 sec)

3.3 字符型

3.3.1 CHAR 定长字符

速度快

-->? char
Name: 'CHAR'
Description:
[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

A fixed-length string that is always right-padded with spaces to the specified length when stored. M represents the column length in characters. The range of M is 0 to 255. If M is omitted, the length is 1.
# M个字节，0<=<=255

3.3.2 VARCHAR 变长字符

速度比较慢

Name: 'VARCHAR'
Description:
[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name ]

A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters.
# L+1字节，其中L<=M且0<=M<=65535

3.3.3 TINYTEXT

Name: 'TINYTEXT'
Description:
TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 255 (28 - 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each TINYTEXT value is stored using a 1-byte length prefix that indicates the number of bytes in the value.
# L+1 个字节， L小于2的8次方

3.3.4 TEXT

Name: 'TEXT'
Description:
TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 65,535 (216 - 1) characters. The
effective maximum length is less if the value contains multi-byte
characters. Each TEXT value is stored using a 2-byte length prefix that
indicates the number of bytes in the value.
# L+2 个字节， L小于2的16次方

3.3.5 MEDIUMTEXT

Name: 'MEDIUMTEXT'
Description:
MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 16,777,215 (224 - 1) characters.
The effective maximum length is less if the value contains multi-byte
characters. Each MEDIUMTEXT value is stored using a 3-byte length
prefix that indicates the number of bytes in the value.
# L+3 个字节， L小于2的24次方

3.3.6 ENUM

Name: 'ENUM'
Description:
ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE
collation_name]

An enumeration. A string object that can have only one value, chosen
from the list of values 'value1', 'value2', ..., NULL or the special ''
error value. ENUM values are represented internally as integers.
# 枚举类型，列举中的字节

3.3.7 SET

Name: 'SET'
Description:
Syntax:
SET variable_assignment [, variable_assignment] ...
# 和枚举类似，但是集合同时可以选择多个值

测试字符

-- 测试 CHAR 和 VCHAR
CREATE TABLE IF NOT EXISTS `cha1`(
`str1` CHAR(5),
`str2` CHAR(5)
);
Query OK, 0 rows affected (0.06 sec)

INSERT INTO cha1 VALUES('1','1');
Query OK, 1 row affected (0.01 sec)
# 插入超出存储范围
INSERT INTO cha1 VALUES('123456','123456');
-->show WARNINGS ;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'str1' at row 1 |
| Warning | 1265 | Data truncated for column 'str2' at row 1 |
+---------+------+-------------------------------------------+
2 rows in set (0.00 sec)

# CHAR 会把多余的空格去掉，而VCHAR会保留
# 后边的会被去掉
INSERT INTO cha1 VALUES('1  ','1  ');
# 前边的都会保留
INSERT INTO cha1 VALUES('  a','  a');

# 插入中文
-->INSERT INTO cha1 VALUES('我','我');
Query OK, 1 row affected (0.04 sec)

-->SELECT * FROM cha1;
+------+------+
| str1 | str2 |
+------+------+
| 1    | 1    |
| 1    | 1    |
|   a  |   a  |
| 我   | 我   |
+------+------+
4 rows in set (0.00 sec)

测试枚举类型

-- 测试枚举类型
CREATE TABLE IF NOT EXISTS `test7` (
sex ENUM('男','女','保密') COMMENT '枚举类型'
);

INSERT INTO test7 VALUES('男');
INSERT INTO test7 VALUES('女');
INSERT INTO test7 VALUES('保密');
SELECT * FROM test7;
+--------+
| sex    |
+--------+
| 男     |
| 女     |
| 保密   |
+--------+
3 rows in set (0.00 sec)


# 插入不符合情况时报错
INSERT INTO test7 VALUES('保密1');
Query OK, 1 row affected, 1 warning (0.03 sec)
-->show warnings ;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'sex' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
-->SELECT * FROM test7;
+--------+
| sex    |
+--------+
| 男     |
| 女     |
| 保密   |
|        |
+--------+

# 可选择对应的序列插入 1-->男, 2-->女 ， 3-->保密  从1开始计数， 只能从列表中选择一个对象，可以插入空值NULL
INSERT INTO test7 VALUES('1');
-->SELECT * FROM test7;
+--------+
| sex    |
+--------+
| 男     |
| 女     |
| 保密   |
|        |
| 男     |
+--------+
5 rows in set (0.00 sec)

# 插入空值
INSERT INTO test7 VALUES('NULL');
-->SELECT * FROM test7;
+--------+
| sex    |
+--------+
| 男     |
| 女     |
| 保密   |
| 男     |
| NULL   |
+--------+
5 rows in set (0.00 sec)

测试集合类型

# 测试集合类型 和 枚举不同的是 集合可以选择多个值
CREATE TABLE IF NOT EXISTS test8(
fav  set('A','B','C','D')
);
# 正常插入 从集合中选择元素
INSERT INTO test8 VALUES('A,B,C');
INSERT INTO test8 VALUES('D,B,A');

-->SELECT * FROM test8;
+-------+
| fav   |
+-------+
| A,B,C |
| A,B,D |
+-------+
2 rows in set (0.00 sec)

3.4 日期时间型

3.4.1 TIME

Name: 'TIME'
Description:
TIME[(fsp)]

A time. The range is '-838:59:59.000000' to '838:59:59.000000'. MySQL
displays TIME values in 'HH:MM:SS[.fraction]' format, but permits
assignment of values to TIME columns using either strings or numbers.

As of MySQL 5.6.4, an optional fsp value in the range from 0 to 6 may
be given to specify fractional seconds precision. A value of 0
signifies that there is no fractional part. If omitted, the default
precision is 0.
# 3个字符

3.4.2 DATTE

Name: 'DATE'
Description:
DATE

A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL
displays DATE values in 'YYYY-MM-DD' format, but permits assignment of
values to DATE columns using either strings or numbers.
# 3个字节

3.4.3 DATETIME

Name: 'DATETIME'
Description:
DATETIME[(fsp)]

A date and time combination. The supported range is '1000-01-01
00:00:00.000000' to '9999-12-31 23:59:59.999999'. MySQL displays
DATETIME values in 'YYYY-MM-DD HH:MM:SS[.fraction]' format, but permits
assignment of values to DATETIME columns using either strings or
numbers.

As of MySQL 5.6.4, an optional fsp value in the range from 0 to 6 may
be given to specify fractional seconds precision. A value of 0
signifies that there is no fractional part. If omitted, the default
precision is 0.
# 8个字节

3.4.4 TIMESTAMP

Name: 'TIMESTAMP'
Description:
TIMESTAMP[(fsp)]

A timestamp. The range is '1970-01-01 00:00:01.000000' UTC to
'2038-01-19 03:14:07.999999' UTC. TIMESTAMP values are stored as the
number of seconds since the epoch ('1970-01-01 00:00:00' UTC). A
TIMESTAMP cannot represent the value '1970-01-01 00:00:00' because that
is equivalent to 0 seconds from the epoch and the value 0 is reserved
for representing '0000-00-00 00:00:00', the "zero" TIMESTAMP value.
# 时间戳 4个字节

3.4.5 YEAR

Name: 'YEAR'
Description:
Syntax:
YEAR(date)

Returns the year for date, in the range 1000 to 9999, or 0 for the
"zero" date.
# 1 个字节

测试时间类型

-- 测试YEAR类型
CREATE TABLE IF NOT EXISTS `test9`(
`birth` YEAR
);

INSERT INTO test9 VALUES(1901);

SELECT * FROM test9;
+-------+
| birth |
+-------+
|  1901 |
+-------+
1 row in set (0.00 sec)


-- 插入一个超出范围的例子
INSERT INTO test9 VALUES(2156);
-->INSERT INTO test9 VALUES(2156);
Query OK, 1 row affected, 1 warning (0.02 sec)
-->SHOW WARNINGS ;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1264 | Out of range value for column 'birth' at row 1 |
+---------+------+------------------------------------------------+
# 当插入YEAR的值为0时 ， 它会转为0000
INSERT INTO test9 VALUES(0);
-->SELECT * FROM test9;
+-------+
| birth |
+-------+
|  1901 |
|  0000 |
+-------+
2 rows in set (0.01 sec)

-- TIME类型
CREATE TABLE  IF NOT EXISTS test10 (
test TIME
);

-- 插入1 天 12小时12分12秒
INSERT INTO test10 VALUES('1 12:12:12'); 
SELECT * FROM test10; 
SELECT * FROM test10; 
+----------+
| test     |
+----------+
| 36:12:12 |
+----------+
1 row in set (0.00 sec)

-- 11:11:00
INSERT INTO test10 VALUES('11:11');
SELECT * FROM test10; 
SELECT * FROM test10; 
+----------+
| test     |
+----------+
| 36:12:12 |
| 11:11:00 |
+----------+
2 rows in set (0.00 sec)

-- 12分34秒
INSERT INTO test10 VALUES('1234');
SELECT * FROM test10;
SELECT * FROM test10;
+----------+
| test     |
+----------+
| 36:12:12 |
| 11:11:00 |
| 00:12:34 |
+----------+
3 rows in set (0.00 sec)

-- 插入12秒
INSERT INTO test10 VALUES('12');

-- 00:00:00
INSERT INTO test10 VALUES('0');

3.5 BINARY

Name: 'BINARY'
Description:
BINARY(M)

The BINARY type is similar to the CHAR type, but stores binary byte
strings rather than nonbinary character strings. M represents the
column length in bytes.

4. 存储引擎

4.1 查看支持的存储引擎

SHOW ENGINES\G
***************************[ 1. row ]***************************
Engine       | PERFORMANCE_SCHEMA
Support      | YES
Comment      | Performance Schema
Transactions | NO
XA           | NO
Savepoints   | NO
***************************[ 2. row ]***************************
Engine       | MRG_MYISAM
Support      | YES
Comment      | Collection of identical MyISAM tables
Transactions | NO
XA           | NO
Savepoints   | NO
***************************[ 3. row ]***************************
Engine       | CSV
Support      | YES
Comment      | CSV storage engine
Transactions | NO
XA           | NO
Savepoints   | NO
***************************[ 4. row ]***************************
Engine       | BLACKHOLE
Support      | YES
Comment      | /dev/null storage engine (anything you write to it disappears)
Transactions | NO
XA           | NO
Savepoints   | NO
***************************[ 5. row ]***************************
Engine       | MEMORY
Support      | YES
Comment      | Hash based, stored in memory, useful for temporary tables
Transactions | NO
XA           | NO
Savepoints   | NO
***************************[ 6. row ]***************************
Engine       | MyISAM
Support      | YES
Comment      | MyISAM storage engine
Transactions | NO
XA           | NO
Savepoints   | NO
***************************[ 7. row ]***************************
Engine       | ARCHIVE
Support      | YES
Comment      | Archive storage engine
Transactions | NO
XA           | NO
Savepoints   | NO
***************************[ 8. row ]***************************
Engine       | InnoDB
Support      | DEFAULT
Comment      | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
Transactions | YES 	# 支持事务
XA           | YES 	# 支持分布式处理XA规范
Savepoints   | YES	# 保存点
***************************[ 9. row ]***************************
Engine       | FEDERATED
Support      | NO
Comment      | Federated MySQL storage engine
Transactions | None
XA           | None
Savepoints   | None

查看当前使用的存储引擎

SHOW VARIABLES LIKE 'STORAGE_ENGINE';
+-----------------+---------+
| Variable_name   | Value   |
|-----------------+---------|
| storage_engine  | InnoDB  |
+-----------------+---------+
1 row in set

4 表的操作

4.1 创建表

语法

CREATE TABLE [IF NOT EXISTS] table_name(
`字段名` 字段类型 [ UNSIGNED | ZEROFILL ] [ NULL | NOT NULL ] [DEFAULT '默认值'] [ [PRIMARY] KEY | UNIQUE [KEY]] [AUTO_INCREMENT]  
)ENGINE=InnoDB CHARSET=UTF8 AUTO_INCREMENT=100;

例子1

CREATE DATABASE IF NOT EXISTS  zrd_test DEFAULT CHARACTER SET 'UTF8';

USE zrd_test;

-- 创建学员表
-- 编号id
-- 用户名 username
-- 年龄 age
-- 性别 sex
-- 邮箱 email
-- 地址 addr
-- 生日 birth
-- 薪水 salary
-- 电话 tel
-- 婚否 married
-- 注意：当需要输入中文的时候，需要临时转换客户端的编码方式
-- SET NAMES GBK; 当前会话有效.
-- COMMENT 给字段添加注释

CREATE TABLE `user` (
`id` SMALLINT(5) UNSIGNED NOT NULL,
`username` VARCHAR(20) NOT NULL,
`age` TINYINT(200) UNSIGNED NOT NULL,
`sex` ENUM('男','女','保密') NOT NULL,
`email` VARCHAR(50) NOT NULL,
`addr` VARCHAR(200) NOT NULL,
`salary` FLOAT(8,2) NOT NULL,
`tel` INT(11) NOT NULL,
`married` TINYINT(1) NOT NULL COMMENT '0表示已婚,非0表示未婚'
)
COMMENT = '创建一张学员表信息'
CHARSET = 'utf8'
ENGINE 	= InnoDB
;

例子2

-- 创建课程表 course
-- 编号 cid
-- 课程名称 courseName
-- 课程描述 courseDesc
CREATE TABLE IF NOT EXISTS course (
cid TINYINT,
courseName VARCHAR(50),
courseDesc VARCHAR(200)
);

小练习

小练习
1.
-- 创建新闻分类表 cms_cate
-- 编号、分类名称、分类描述

2. 
-- 创建新闻表 cms_news
-- 编号、新闻标题、新闻内容、新闻发布时间、点击量、是否置顶、新闻所属分类、发布人

# 1
CREATE TABLE IF NOT EXISTS `use_cate` ( `id` TINYINT, 
	`cateName` VARCHAR(50), 
	`cateDesc` VARCHAR(200) 
) ENGINE = MYISAM CHARSET= UTF8;
# 2
CREATE TABLE IF NOT EXISTS `cms_news` (
`id` INT,
`title` VARCHAR(50),
`content` TEXT,
`pubTime` INT,
`clickNum` INT,
`isTop` TINYINT(1) COMMENT '0 is not top, 1 is top',
`cID` TINYINT,
`pubAuth` VARCHAR(50)
);

4.2 查看表

percona root@localhost:test> SHOW TABLES;
+------------------+
| Tables_in_test   |
|------------------|
| course           |
| user             |
+------------------+
2 rows in set
Time: 0.001s

到数据目录下可以看到两个文件相对于`INNODB来说

# innodb存储引擎  
~sehll#ls
user.frm  	# 表结构
user.ibd	# 表数据

# myisam存储引擎
└─> ls
use_cate.frm  	# 表结构
use_cate.MYD	# 表数据  
use_cate.MYI	# 索引

4.1.1 创建表测试整型

4.2.1 查看表结构

# 方法1
-->DESC cms_news;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| title    | varchar(50) | YES  |     | NULL    |       |
| content  | text        | YES  |     | NULL    |       |
| pubTime  | int(11)     | YES  |     | NULL    |       |
| clickNum | int(11)     | YES  |     | NULL    |       |
| isTop    | tinyint(1)  | YES  |     | NULL    |       |
| cID      | tinyint(4)  | YES  |     | NULL    |       |
| pubAuth  | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

# 方法2
-->DESCRIBE cms_news;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| title    | varchar(50) | YES  |     | NULL    |       |
| content  | text        | YES  |     | NULL    |       |
| pubTime  | int(11)     | YES  |     | NULL    |       |
| clickNum | int(11)     | YES  |     | NULL    |       |
| isTop    | tinyint(1)  | YES  |     | NULL    |       |
| cID      | tinyint(4)  | YES  |     | NULL    |       |
| pubAuth  | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
# 方法3
-->SHOW COLUMNS FROM cms_news;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| title    | varchar(50) | YES  |     | NULL    |       |
| content  | text        | YES  |     | NULL    |       |
| pubTime  | int(11)     | YES  |     | NULL    |       |
| clickNum | int(11)     | YES  |     | NULL    |       |
| isTop    | tinyint(1)  | YES  |     | NULL    |       |
| cID      | tinyint(4)  | YES  |     | NULL    |       |
| pubAuth  | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

# 第一段		--> 字段名
# 第二段		--> 字段类型
# NULL		  --> 是否为空
# key   	  --> 主键唯一
# default 	  --> 默认值
# extra	  	  --> 额外信息

4.3 INSERT

语法

Name: 'INSERT'
Description:
Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

4.4 表结构修改

4.4.1 重命名表

# 创建一张用户表
CREATE TABLE IF NOT EXISTS user10 (
id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email varchar(50) NOT NULL DEFAULT '1234@163.com',
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(200) NOT NULL DEFAULT '北京',
salary FLOAT(6,2),
regTime INT UNSIGNED ,
face CHAR(100) NOT NULL DEFAULT 'default.jpg',
sex ENUM('男','女','保密') DEFAULT '保密'

) ;

DESC  user10;
+----------+----------------------------+------+-----+--------------+----------------+
| Field    | Type                       | Null | Key | Default      | Extra          |
+----------+----------------------------+------+-----+--------------+----------------+
| id       | smallint(5) unsigned       | NO   | PRI | NULL         | auto_increment |
| username | varchar(20)                | NO   | UNI | NULL         |                |
| password | char(32)                   | NO   |     | NULL         |                |
| email    | varchar(50)                | NO   |     | 1234@163.com |                |
| age      | tinyint(3) unsigned        | YES  |     | 18           |                |
| addr     | varchar(200)               | NO   |     | 北京         |                |
| salary   | float(6,2)                 | YES  |     | NULL         |                |
| regTime  | int(10) unsigned           | YES  |     | NULL         |                |
| face     | char(100)                  | NO   |     | default.jpg  |                |
| sex      | enum('男','女','保密')     | YES  |     | 保密         |                |
+----------+----------------------------+------+-----+--------------+----------------+
10 rows in set (0.00 sec)


# 方法1 TO 将user10 重命名为user11
ALTER TABLE user10 RENAME TO user11; 
# 方法2 AS
ALTER TABLE user11 RENAME AS user10;
# 方法3 直接写
ALTER TABLE user10 RENAME user11;
# 以上 to 或者AS 可以省略


# 另一种方法修改 RENAME TABLE table_name_old TO table_name_new
RENAME TABLE user11 TO user10;
# TO 不能省略

4.4.2 添加字段

# 添加card 字段CHAR(18) 
DESC user8;
+----------+----------------------------+------+-----+---------+----------------+
| Field    | Type                       | Null | Key | Default | Extra          |
+----------+----------------------------+------+-----+---------+----------------+
| id       | int(10) unsigned           | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)                | NO   |     | NULL    |                |
| password | char(32)                   | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned        | YES  |     | 18      |                |
| addr     | varchar(50)                | NO   |     | beijing |                |
| sex      | enum('男','女','保密')     | NO   |     | 男      |                |
+----------+----------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

ALTER  TABLE `user8` ADD card CHAR(18); # 默认添加到末尾
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0
DESC user8;
+----------+----------------------------+------+-----+---------+----------------+
| Field    | Type                       | Null | Key | Default | Extra          |
+----------+----------------------------+------+-----+---------+----------------+
| id       | int(10) unsigned           | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)                | NO   |     | NULL    |                |
| password | char(32)                   | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned        | YES  |     | 18      |                |
| addr     | varchar(50)                | NO   |     | beijing |                |
| sex      | enum('男','女','保密')     | NO   |     | 男      |                |
| card     | char(18)                   | YES  |     | NULL    |                |
+----------+----------------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

# 添加完整性约束

# 添加到第一行
ALTER TABLE `user8` ADD COLUMN `test1` VARCHAR(100) NOT NULL FIRST;
# 添加到指定的字段(username之后)
ALTER TABLE user8 ADD test3 INT NOT NULL DEFAULT 100 AFTER username;
DESC user8;
+----------+----------------------------+------+-----+---------+-------+
| Field    | Type                       | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+---------+-------+
| test1    | varchar(100)               | NO   |     | NULL    |       |
| username | varchar(20)                | NO   |     | NULL    |       |
| test3    | int(11)                    | NO   |     | 100     |       |
| password | char(32)                   | NO   |     | NULL    |       |
| age      | tinyint(3) unsigned        | YES  |     | 18      |       |
| addr     | varchar(50)                | NO   |     | beijing |       |
| sex      | enum('男','女','保密')     | NO   |     | 男      |       |
| card     | char(18)                   | YES  |     | NULL    |       |
+----------+----------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
# 选中一次表 完成多个操作
ALTER TABLE user8
ADD test4 INT NOT NULL DEFAULT 123 AFTER password,
ADD test5 FLOAT(6,2) FIRST,
ADD test6 SET('A','B','C');

DESC user8;
+----------+----------------------------+------+-----+---------+-------+
| Field    | Type                       | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+---------+-------+
| test5    | float(6,2)                 | YES  |     | NULL    |       |
| test1    | varchar(100)               | NO   |     | NULL    |       |
| username | varchar(20)                | NO   |     | NULL    |       |
| test3    | int(11)                    | NO   |     | 100     |       |
| password | char(32)                   | NO   |     | NULL    |       |
| test4    | int(11)                    | NO   |     | 123     |       |
| age      | tinyint(3) unsigned        | YES  |     | 18      |       |
| addr     | varchar(50)                | NO   |     | beijing |       |
| sex      | enum('男','女','保密')     | NO   |     | 男      |       |
| card     | char(18)                   | YES  |     | NULL    |       |
| test6    | set('A','B','C')           | YES  |     | NULL    |       |
+----------+----------------------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

4.4.3 删除字段

# 删除字段test6 
ALTER TABLE user8  DROP test6;
DESC user8;
+----------+----------------------------+------+-----+---------+-------+
| Field    | Type                       | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+---------+-------+
| test5    | float(6,2)                 | YES  |     | NULL    |       |
| test1    | varchar(100)               | NO   |     | NULL    |       |
| username | varchar(20)                | NO   |     | NULL    |       |
| test3    | int(11)                    | NO   |     | 100     |       |
| password | char(32)                   | NO   |     | NULL    |       |
| test4    | int(11)                    | NO   |     | 123     |       |
| age      | tinyint(3) unsigned        | YES  |     | 18      |       |
| addr     | varchar(50)                | NO   |     | beijing |       |
| sex      | enum('男','女','保密')     | NO   |     | 男      |       |
| card     | char(18)                   | YES  |     | NULL    |       |
+----------+----------------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

# 一次选中表，删除多个字段
ALTER TABLE user8
DROP test3,
DROP test4,
DROP test5;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
DESC user8;
+----------+----------------------------+------+-----+---------+-------+
| Field    | Type                       | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+---------+-------+
| test1    | varchar(100)               | NO   |     | NULL    |       |
| username | varchar(20)                | NO   |     | NULL    |       |
| password | char(32)                   | NO   |     | NULL    |       |
| age      | tinyint(3) unsigned        | YES  |     | 18      |       |
| addr     | varchar(50)                | NO   |     | beijing |       |
| sex      | enum('男','女','保密')     | NO   |     | 男      |       |
| card     | char(18)                   | YES  |     | NULL    |       |
+----------+----------------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

# 添加test字段 删除card字段
ALTER TABLE user8
ADD test INT UNSIGNED NOT NULL DEFAULT 10 AFTER sex,
DROP card;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
+----------+----------------------------+------+-----+---------+-------+
| Field    | Type                       | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+---------+-------+
| test1    | varchar(100)               | NO   |     | NULL    |       |
| username | varchar(20)                | NO   |     | NULL    |       |
| password | char(32)                   | NO   |     | NULL    |       |
| age      | tinyint(3) unsigned        | YES  |     | 18      |       |
| addr     | varchar(50)                | NO   |     | beijing |       |
| sex      | enum('男','女','保密')     | NO   |     | 男      |       |
| test     | int(10) unsigned           | NO   |     | 10      |       |
+----------+----------------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

4.4.4 修改字段

# 修改字段
ALTER TABLE tbl_name MODIFY 字段名 字段类型[完整性约束] [FIRST|AFTER]

# 将Email 修改成VARCHAR(200)
ALTER TABLE user10 MODIFY email VARCHAR(200) NOT NULL DEFAULT '2920@qq.com';
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
desc user10;
+----------+----------------------------+------+-----+-------------+----------------+
| Field    | Type                       | Null | Key | Default     | Extra          |
+----------+----------------------------+------+-----+-------------+----------------+
| id       | smallint(5) unsigned       | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)                | NO   | UNI | NULL        |                |
| password | char(32)                   | NO   |     | NULL        |                |
| email    | varchar(200)               | NO   |     | 2920@qq.com |                |
| age      | tinyint(3) unsigned        | YES  |     | 18          |                |
| addr     | varchar(200)               | NO   |     | 北京        |                |
| salary   | float(6,2)                 | YES  |     | NULL        |                |
| regTime  | int(10) unsigned           | YES  |     | NULL        |                |
| face     | char(100)                  | NO   |     | default.jpg |                |
| sex      | enum('男','女','保密')     | YES  |     | 保密        |                |
+----------+----------------------------+------+-----+-------------+----------------+
10 rows in set (0.00 sec)

# 修改字段的位置 将 addr 移动到sex之后
ALTER TABLE user10 MODIFY addr VARCHAR(200) NOT NULL DEFAULT '北京' AFTER sex;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
DESC user10;
+----------+----------------------------+------+-----+-------------+----------------+
| Field    | Type                       | Null | Key | Default     | Extra          |
+----------+----------------------------+------+-----+-------------+----------------+
| id       | smallint(5) unsigned       | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)                | NO   | UNI | NULL        |                |
| password | char(32)                   | NO   |     | NULL        |                |
| email    | varchar(200)               | NO   |     | 2920@qq.com |                |
| age      | tinyint(3) unsigned        | YES  |     | 18          |                |
| salary   | float(6,2)                 | YES  |     | NULL        |                |
| regTime  | int(10) unsigned           | YES  |     | NULL        |                |
| face     | char(100)                  | NO   |     | default.jpg |                |
| sex      | enum('男','女','保密')     | YES  |     | 保密        |                |
| addr     | varchar(200)               | NO   |     | 北京        |                |
+----------+----------------------------+------+-----+-------------+----------------+
10 rows in set (0.00 sec)

# 将addr字段修改位CHAR(32) NOT NULL DEFAULT '123' 移动到第一个位置
ALTER TABLE user10 MODIFY addr CHAR(30) NOT NULL DEFAULT '123' FIRST;
DESC user10;
+----------+----------------------------+------+-----+-------------+----------------+
| Field    | Type                       | Null | Key | Default     | Extra          |
+----------+----------------------------+------+-----+-------------+----------------+
| addr     | char(30)                   | NO   |     | 123         |                |
| id       | smallint(5) unsigned       | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)                | NO   | UNI | NULL        |                |
| password | char(32)                   | NO   |     | NULL        |                |
| email    | varchar(200)               | NO   |     | 2920@qq.com |                |
| age      | tinyint(3) unsigned        | YES  |     | 18          |                |
| salary   | float(6,2)                 | YES  |     | NULL        |                |
| regTime  | int(10) unsigned           | YES  |     | NULL        |                |
| face     | char(100)                  | NO   |     | default.jpg |                |
| sex      | enum('男','女','保密')     | YES  |     | 保密        |                |
+----------+----------------------------+------+-----+-------------+----------------+
10 rows in set (0.00 sec)



# 字段名修改
ALTER TABLE tbl_name CHANGE 旧字段名称 新字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]
# 将test字段改为test1
ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';
DESC user10;
+----------+----------------------------+------+-----+-------------+----------------+
| Field    | Type                       | Null | Key | Default     | Extra          |
+----------+----------------------------+------+-----+-------------+----------------+
| addr     | char(30)                   | NO   |     | 123         |                |
| id       | smallint(5) unsigned       | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)                | NO   | UNI | NULL        |                |
| password | char(32)                   | NO   |     | NULL        |                |
| email    | varchar(200)               | NO   |     | 2920@qq.com |                |
| age      | tinyint(3) unsigned        | YES  |     | 18          |                |
| salary   | float(6,2)                 | YES  |     | NULL        |                |
| regTime  | int(10) unsigned           | YES  |     | NULL        |                |
| face     | char(100)                  | NO   |     | default.jpg |                |
| sex      | enum('男','女','保密')     | YES  |     | 保密        |                |
| test1    | char(32)                   | NO   |     | 123         |                |
+----------+----------------------------+------+-----+-------------+----------------+
11 rows in set (0.00 sec)

# 修改字段属性，但是不修改字段名
ALTER TABLE user10 CHANGE test1 test1 INT; 
DESC user10;
+----------+----------------------------+------+-----+-------------+----------------+
| Field    | Type                       | Null | Key | Default     | Extra          |
+----------+----------------------------+------+-----+-------------+----------------+
| addr     | char(30)                   | NO   |     | 123         |                |
| id       | smallint(5) unsigned       | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)                | NO   | UNI | NULL        |                |
| password | char(32)                   | NO   |     | NULL        |                |
| email    | varchar(200)               | NO   |     | 2920@qq.com |                |
| age      | tinyint(3) unsigned        | YES  |     | 18          |                |
| salary   | float(6,2)                 | YES  |     | NULL        |                |
| regTime  | int(10) unsigned           | YES  |     | NULL        |                |
| face     | char(100)                  | NO   |     | default.jpg |                |
| sex      | enum('男','女','保密')     | YES  |     | 保密        |                |
| test1    | int(11)                    | YES  |     | NULL        |                |
+----------+----------------------------+------+-----+-------------+----------------+
11 rows in set (0.00 sec)

4.4.5 添加删除默认值

# 添加默认值
ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值

CREATE TABLE IF NOT EXISTS user11 (
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
age TINYINT UNSIGNED
);

DESC user11;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned | YES  |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

ALTER TABLE user11 ALTER age SET DEFAULT 18;

DESC user11;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned | YES  |     | 18      |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
# 添加email默认值
ALTER TABLE user11 ADD email VARCHAR(50) ;
ALTER TABLE user11 ALTER email SET DEFAULT '2920@qq.com';
DESC user11;
+----------+---------------------+------+-----+-------------+----------------+
| Field    | Type                | Null | Key | Default     | Extra          |
+----------+---------------------+------+-----+-------------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL        |                |
| age      | tinyint(3) unsigned | YES  |     | 18          |                |
| email    | varchar(50)         | YES  |     | 2920@qq.com |                |
+----------+---------------------+------+-----+-------------+----------------+
4 rows in set (0.00 sec)

# 删除默认值
ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT

# 删除age默认值
ALTER TABLE user11 ALTER age DROP DEFAULT;
DESC user11;
+----------+---------------------+------+-----+-------------+----------------+
| Field    | Type                | Null | Key | Default     | Extra          |
+----------+---------------------+------+-----+-------------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL        |                |
| age      | tinyint(3) unsigned | YES  |     | NULL        |                |
| email    | varchar(50)         | YES  |     | 2920@qq.com |                |
+----------+---------------------+------+-----+-------------+----------------+
4 rows in set (0.00 sec)
# 删除email 默认值
ALTER TABLE user11 ALTER email DROP DEFAULT;
DESC user11;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned | YES  |     | NULL    |                |
| email    | varchar(50)         | YES  |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

4.4.6 添加删除主键

# 添加主键
CREATE TABLE IF NOT EXISTS test12(
id INT
);
DESC  test12;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
# 简单写法
ALTER TABLE test12 ADD PRIMARY KEY(id);
DESC  test12;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | 0       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)


CREATE TABLE IF NOT EXISTS test13 (
id INT,
card CHAR(20),
username  VARCHAR(20) NOT NULL
);

# 复合主键
ALTER TABLE test13 ADD PRIMARY KEY (id,card);


# 删除主键
ALTER TABLE tbl_name DROP PRIMARY KEY
# 删除tes12的主键
ALTER TABLE test12 DROP PRIMARY KEY; # 因为一张表中只有一个主键
DESC test12;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | 0       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

ALTER TABLE test13 DROP PRIMARY KEY;
DESC test13;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   |     | 0       |       |
| card     | char(20)    | NO   |     |         |       |
| username | varchar(20) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


# 添加主键，完整形式
ALTER TABLE test12 ADD CONSTRAINT symbol PRIMARY KEY index_type(id); 
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | 0       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

# 删除主键
CREATE TABLE IF NOT EXISTS test14(
id INT UNSIGNED KEY AUTO_INCREMENT
);
DESC test14;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.00 sec)

ALTER TABLE test14 DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
# 一张表中有且一列是自增长列且要被定义成主键
# 所以要先去掉自增长
ALTER TABLE test14 MODIFY id INT UNSIGNED ;
DESC test14;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | 0       |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

ALTER TABLE test14 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.11 sec)
ESC test14;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   |     | 0       |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

4.4.7 添加删除唯一索引

# 测试添加唯一索引
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [索引名称](字段名称,...)

CREATE TABLE IF NOT EXISTS user12 (
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
card CHAR(18),
test VARCHAR(20) NOT NULL,
test1 CHAR(32) NOT NULL
);

DESC user12;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   |     | NULL    |                |
| card     | char(18)            | YES  |     | NULL    |                |
| test     | varchar(20)         | NO   |     | NULL    |                |
| test1    | char(32)            | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

# 添加唯一性索引
ALTER TABLE user12 ADD UNIQUE(username);
DESC user12;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| card     | char(18)            | YES  |     | NULL    |                |
| test     | varchar(20)         | NO   |     | NULL    |                |
| test1    | char(32)            | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
# 添加唯一性索引(完整写法)
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]]  UNIQUE [INDEX|KEY] [索引名称] (字段名称,...)
ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE  KEY  uni_card(card);
desc user12;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| card     | char(18)            | YES  | UNI | NULL    |                |
| test     | varchar(20)         | NO   |     | NULL    |                |
| test1    | char(32)            | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

SHOW CREATE TABLE user12;
CREATE TABLE `user12` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`card` char(18) DEFAULT NULL,
`test` varchar(20) NOT NULL,
`test1` char(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `uni_card` (`card`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

# 添加联合索引
ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1(test,test1);
DESC user12;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| card     | char(18)            | YES  | UNI | NULL    |                |
| test     | varchar(20)         | NO   | MUL | NULL    |                |
| test1    | char(32)            | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

 CREATE TABLE `user12` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`card` char(18) DEFAULT NULL,
`test` varchar(20) NOT NULL,
`test1` char(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `mulUni_test_test1` (`test`,`test1`),
UNIQUE KEY `uni_card` (`card`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

# 删除唯一索引
ALTER TABLE user12  DROP INDEX username;
DESC user12;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   |     | NULL    |                |
| card     | char(18)            | YES  | UNI | NULL    |                |
| test     | varchar(20)         | NO   | MUL | NULL    |                |
| test1    | char(32)            | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
ALTER TABLE user12 DROP KEY uni_card ;
ALTER TABLE user12 DROP KEY mulUni_test_test1;
DESC user12;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   |     | NULL    |                |
| card     | char(18)            | YES  |     | NULL    |                |
| test     | varchar(20)         | NO   |     | NULL    |                |
| test1    | char(32)            | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

4.4.8 修改表存储引擎

# 表存储引擎修改
SHOW CREATE TABLE user12\G
*************************** 1. row ***************************
       Table: user12
Create Table: CREATE TABLE `user12` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `card` char(18) DEFAULT NULL,
  `test` varchar(20) NOT NULL,
  `test1` char(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# 修改为 myisam 没有事务支持
ALTER TABLE user12 ENGINE=MYISAM;
SHOW CREATE TABLE user12\G
*************************** 1. row ***************************
       Table: user12
Create Table: CREATE TABLE `user12` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `card` char(18) DEFAULT NULL,
  `test` varchar(20) NOT NULL,
  `test1` char(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

# 修改为InnoDB
ALTER TABLE user12 ENGINE=InnoDB;

4.4.9 修改自动增长值

# 修改自增长值
ALTER TABLE user12 AUTO_INCREMENT=100;
SHOW CREATE TABLE user12\G
*************************** 1. row ***************************
       Table: user12
Create Table: CREATE TABLE `user12` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `card` char(18) DEFAULT NULL,
  `test` varchar(20) NOT NULL,
  `test1` char(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

待整理-完整性约束

主键约束 PRIMARY KEY

唯一标识符，来找到此记录,一般主键会加到无意义的字段上，例如编号字段

要求:不能出现重复的值，被要求设置成主键的字段自动带有非空约束

mysql中可以设置为单字段，也可以多字段

-- 主键约束测试
CREATE TABLE IF NOT EXISTS user1(
id INT PRIMARY KEY,
name VARCHAR(20)
);
Query OK, 0 rows affected (0.06 sec)

DESC user1; # 可以看到KEY 为PRI, 非空约束
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- 查看创建表的定义
SHOW CREATE TABLE user1\G
*************************** 1. row ***************************
       Table: user1
Create Table: CREATE TABLE `user1` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

INSERT INTO user1 VALUES(1,'zrd');
INSERT INTO user1 VALUES(12,'King');

SELECT * FROM user1;
+----+------+
| id | name |
+----+------+
|  1 | zrd  |
| 12 | King |
+----+------+
2 rows in set (0.00 sec)

-- 插入重复的字段进行测试
INSERT INTO user1 VALUES(12,'King');
ERROR 1062 (23000): Duplicate entry '12' for key 'PRIMARY'


-- 多字段主键(复合主键)
-- 由两字段来创建字段，一张表中只能有一个主键
CREATE TABLE IF NOT EXISTS user2(
id INT,
username  VARCHAR(20),
card CHAR(18),
PRIMARY KEY(id,card)
);

DESC user2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | 0       |       |
| username | varchar(20) | YES  |     | NULL    |       |
| card     | char(18)    | NO   | PRI |         |       |
+----------+-------------+------+-----+---------+-------+

INSERT INTO user2 VALUES(1,'zrd','111');

--由于 user2 使用id 和card来进行约束，所以满足其一即可
INSERT INTO user2 VALUES(1,'zrd rudong','112');
SELECT * FROM user2;
+----+------------+------+
| id | username   | card |
+----+------------+------+
|  1 | zrd        | 111  |
|  1 | zrd rudong | 112  |
+----+------------+------+
2 rows in set (0.00 sec)

-- 插入两个复合主键都一样，则会报错
INSERT INTO user2 VALUES(1,'zrd rudong','112');
ERROR 1062 (23000): Duplicate entry '1-112' for key 'PRIMARY'


-- 直接使用 KEY 也表示是使用主键(primary key)
CREATE TABLE IF NOT EXISTS user3(
id INT KEY,
username  VARCHAR(20)
);

DESC user3;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

自增长 AUTO INCREMENT

-- 自增长 AUTO INCREMENT
默认从1开始，每次自增1
要求：一张表中只能有一个字段为自增长字段；
被标识成自增长的字段一定是主键

CREATE TABLE IF NOT EXISTS user4(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
);

-- 可以看到在 extra额外信息 发现auto_increment
DESC user4;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | smallint(6) | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

-- 插入数据几种方式
-- 1 手动指定值
INSERT INTO user4 VALUES(1,'tomcat');

-- 让auto_increment自动增长, 默认情况下在ID的最大值下加1
INSERT INTO user4(username) VALUES('zrd'); 
SELECT * FROM user4;
+----+----------+
| id | username |
+----+----------+
|  1 | tomcat   |
|  2 | zrd      |
+----+----------+
2 rows in set (0.00 sec)


INSERT INTO user4 VALUES(122,'tomcat2');
INSERT INTO user4(username) VALUES('zhourudong'); 
SELECT * FROM user4;
+-----+------------+
| id  | username   |
+-----+------------+
|   1 | tomcat     |
|   2 | zrd        |
| 122 | tomcat2    |
| 123 | zhourudong |
+-----+------------+
4 rows in set (0.00 sec)

-- 可以看到下次自动增长值为124
SHOW CREATE TABLE user4;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user4 | CREATE TABLE `user4` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=124 DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


-- 使用 NULL
INSERT INTO user4 VALUES(NULL,'nginx');
SELECT * FROM user4;
+-----+------------+
| id  | username   |
+-----+------------+
|   1 | tomcat     |
|   2 | zrd        |
| 122 | tomcat2    |
| 123 | zhourudong |
| 136 | nginx      |
+-----+------------+

-- 使用DEFAULT
INSERT INTO user4 VALUES(NULL,'Apache');
SELECT * FROM user4;
+-----+------------+
| id  | username   |
+-----+------------+
|   1 | tomcat     |
|   2 | zrd        |
| 122 | tomcat2    |
| 123 | zhourudong |
| 136 | nginx      |
| 137 | Apache     |
+-----+------------+
6 rows in set (0.00 sec)


-- 指定自增长从100 开始
CREATE TABLE `user5` (
`id` smallint(6) KEY  AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL
) AUTO_INCREMENT=100;

SHOW CREATE TABLE user5\G
*************************** 1. row ***************************
       Table: user5
Create Table: CREATE TABLE `user5` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

-- 修改自增长的值
ALTER TABLE user5 AUTO_INCREMENT=500;
SHOW CREATE TABLE user5\G
*************************** 1. row ***************************
       Table: user5
Create Table: CREATE TABLE `user5` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=utf8

非空约束

# 测试非空约束 NOT NULL 
CREATE TABLE IF NOT EXISTS user7 (
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL ,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED 
);

DESC user7;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   |     | NULL    |                |
| password | char(32)            | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned | YES  |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

# 插入记录
# id 自增 ， 只需要插入用户名和密码， age没有非空约束
INSERT user7 (username,password) VALUES('ZRD','ZHOURUDONG');

INSERT user7 (username,password,age) VALUES('tomcat','Apache',20);
SELECT * FROM user7;
+----+----------+------------+------+
| id | username | password   | age  |
+----+----------+------------+------+
|  1 | ZRD      | ZHOURUDONG | NULL |
|  2 | tomcat   | Apache     |   20 |
+----+----------+------------+------+
2 rows in set (0.00 sec)

# 测试不满足情况
INSERT user7 (username,password) VALUES(NULL,NULL);
ERROR 1048 (23000): Column 'username' cannot be null

默认值 DEFAULT

# 默认值
# 设置age默认值为18。 addr为beijing sex默认为男

CREATE TABLE IF NOT EXISTS user8 (
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL ,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(50) NOT NULL DEFAULT 'beijing',
sex ENUM ('男','女','保密') NOT NULL DEFAULT '男'
);

DESC user8;
+----------+----------------------------+------+-----+---------+----------------+
| Field    | Type                       | Null | Key | Default | Extra          |
+----------+----------------------------+------+-----+---------+----------------+
| id       | int(10) unsigned           | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)                | NO   |     | NULL    |                |
| password | char(32)                   | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned        | YES  |     | 18      |                |
| addr     | varchar(50)                | NO   |     | beijing |                |
| sex      | enum('男','女','保密')     | NO   |     | 男      |                |
+----------+----------------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

# 插入记录测试
INSERT INTO user8(username,password) VALUES('ZRD','A123456');

SELECT *  FROM user8;
+----+----------+----------+------+---------+-----+
| id | username | password | age  | addr    | sex |
+----+----------+----------+------+---------+-----+
|  1 | ZRD      | A123456  |   18 | beijing | 男  |
+----+----------+----------+------+---------+-----+
1 row in set (0.00 sec)

# 插入完整的记录
INSERT INTO user8 (username,password,age,addr,sex) VALUES('Alice','SDF123',23,'上海','女');
SELECT *  FROM user8;
+----+----------+----------+------+---------+-----+
| id | username | password | age  | addr    | sex |
+----+----------+----------+------+---------+-----+
|  1 | ZRD      | A123456  |   18 | beijing | 男  |
|  2 | Alice    | SDF123   |   23 | 上海    | 女  |
+----+----------+----------+------+---------+-----+
2 rows in set (0.00 sec)

# default 默认
INSERT INTO user8 (id,username,password,age,addr,sex) VALUES(3,'tomcat','SDF123',DEFAULT,DEFAULT,DEFAULT);
SELECT * FROM user8;
+----+----------+----------+------+---------+-----+
| id | username | password | age  | addr    | sex |
+----+----------+----------+------+---------+-----+
|  1 | ZRD      | A123456  |   18 | beijing | 男  |
|  2 | Alice    | SDF123   |   23 | 上海    | 女  |
|  3 | tomcat   | SDF123   |   18 | beijing | 男  |
+----+----------+----------+------+---------+-----+
3 rows in set (0.00 sec)

# 当用户没头像时给默认头像

唯一性约束

# 唯一性约束 UNIQUE KEY
# 一个主键，多个唯一性
# 被标识成唯一约束后， 不能出现重复

CREATE TABLE IF NOT EXISTS user9(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(18) UNIQUE
);

# 可以看到key 中UNI约束
DESC user9;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| card     | char(18)            | YES  | UNI | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

SHOW CREATE TABLE user9;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user9 | CREATE TABLE `user9` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `card` char(18) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `card` (`card`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


INSERT INTO user9 (username) VALUES('A');
Query OK, 1 row affected (0.02 sec)

INSERT INTO user9 (username) VALUES('A'); # 受唯一性约束第二条记录插入失败
ERROR 1062 (23000): Duplicate entry 'A' for key 'username'

# NULL 为特殊字符，不算重复
INSERT INTO user9(username,card) VALUES('B',NULL);
INSERT INTO user9(username,card) VALUES('B1',NULL);
INSERT INTO user9(username,card) VALUES('B2',NULL); 
SELECT * FROM user9;
+----+----------+------+
| id | username | card |
+----+----------+------+
|  1 | A        | NULL |
|  3 | B        | NULL |
|  4 | B1       | NULL |
|  5 | B2       | NULL |
+----+----------+------+

收回权限

指定到数据库名
REVOKE ALL PRIVILEGES  ON `bbs_db`.* FROM 'bbs_user'@'10.20.25.15';

查看权限

SHOW GRANTS FOR 'bbs_user'@'10.20.25.15';

授权

GRANT SELECT ON `bbs_db`.* TO 'bbs_user'@'10.20.25.15' IDENTIFIED BY '1YWRzZmFzZGYK@';

5.7

登录root账户后执行下面操作：

1、创建用户,名字为userone,密码为123456
　　create user userone identified by '123456';
2、创建名字为dbone的数据库
　　create database dbone;
3、授予dbone数据库的所有权限给userone用户：
　　grant all on dbone.* to userone

mysql基础使用