【无标题】

库管理

建库语法

库：存储表的文件夹

mysql> SHOW CREATE DATABASE tarena \G
*************************** 1. row ***************************
? ? ? ?Database: tarena
Create Database: CREATE DATABASE `tarena` /*!40100 DEFAULT CHARACTER SET latin1 */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

create database [if not exists] <数据库名> [[DEFAULT] CHARACTER SET <字符集名>] [[DEFAULT] COLLATE <校对规则名>];

[ ]? ?表示可选项

if not exists? ?假设库不存在 避免建库重名报错

character set? ?指定表使用的字符集（存储字符串的方式）

collate? ?指定校对规则（比较字符串的方式）

常用命令

查看已有的库? ? ? ? show databases;

切换库? ? ? ? use 库名;

删除库? ? ? ? drop database 库名;? ? ? ? （drop database if exists 库名;?）

创建库? ? ? ? create database 库名;

所在库? ? ? ? select database();

库名的命名规则

仅可以使用数字、字母、下划线，不能纯数字

区分大小写

具有唯一性

不可使用MySQL命令或特殊字符

创建表

就是创建存储数据的表头名

语法

create? ? table? ?库名.表名(

表头名? ?数据类型,

表头名? ?数据类型,

......

);

常用命令

删除表???????????????drop table 库名.表名;

删除表记录? ? ? ? delete from?库名.表名;

修改表记录? ? ? ? update?库名.表名 set 表头名="表头新值" where;

插入表记录? ? ? ? insert into 表名 values(表头的值,...,...),( )...;? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

? ? ? ? ? ? ? ? ? ? ? ? ??insert into 库.表(表头名,...) values(表头的值,...);? ? ? ? ? ? ? ? ??

修改表结构? ? ? ?

alter table 库名.表名 执行动作;

添加表头?

alter table 库名.表名 add 表头名 数据类型;

mysql> alter table user add email char(30);
mysql> select * from user limit 1;
+----+--------+----------+------+------+---------+----------------+---------------+-------+
| id | name   | password | uid  | gid  | comment | homedir        | shell         | email |
+----+--------+----------+------+------+---------+----------------+---------------+-------+
|  1 | root   | x        |    0 |    0 | root    | /root          | /bin/bash     | NULL  |

mysql> alter table user add sex char(3),add banji char(7);
mysql> select * from user limit 1;
+----+--------+----------+------+------+---------+----------------+---------------+-------+------+-------+
| id | name   | password | uid  | gid  | comment | homedir        | shell         | email | sex  | banji |
+----+--------+----------+------+------+---------+----------------+---------------+-------+------+-------+
|  1 | root   | x        |    0 |    0 | root    | /root          | /bin/bash     | NULL  | NULL | NULL  |

mysql> alter table user add school char(10) first;
mysql> select * from user limit 1;
+--------+----+--------+----------+------+------+---------+----------------+---------------+-------+------+-------+
| school | id | name   | password | uid  | gid  | comment | homedir        | shell         | email | sex  | banji |
+--------+----+--------+----------+------+------+---------+----------------+---------------+-------+------+-------+
| NULL   |  1 | root   | x        |    0 |    0 | root    | /root          | /bin/bash     | NULL  | NULL | NULL  |

mysql> alter table user add shenfenzheng char(18) after name;
mysql> select * from user limit 1;
+----+------+--------------+----------+---------+---------+-----------+-------+------+-------+
| id | name | shenfenzheng | password | comment | homedir | shell     | email | sex  | banji |
+----+------+--------------+----------+---------+---------+-----------+-------+------+-------+
|  1 | root | NULL         | x        | root    | /root   | /bin/bash | NULL  | NULL | NULL  |
+----+------+--------------+----------+---------+---------+-----------+-------+------+-------+

删除表头?

alter table 库名.表名 drop 表头名;

mysql> alter table user drop school;
mysql> select * from user limit 1;
+----+------+----------+------+------+---------+---------+-----------+-------+------+-------+
| id | name | password | uid  | gid  | comment | homedir | shell     | email | sex  | banji |
+----+------+----------+------+------+---------+---------+-----------+-------+------+-------+
|  1 | root | x        |    0 |    0 | root    | /root   | /bin/bash | NULL  | NULL | NULL  |
+----+------+----------+------+------+---------+---------+-----------+-------+------+-------+

mysql> alter table user drop uid,drop gid;
mysql> select * from user limit 1;
+----+------+----------+---------+---------+-----------+-------+------+-------+
| id | name | password | comment | homedir | shell     | email | sex  | banji |
+----+------+----------+---------+---------+-----------+-------+------+-------+
|  1 | root | x        | root    | /root   | /bin/bash | NULL  | NULL | NULL  |
+----+------+----------+---------+---------+-----------+-------+------+-------+

修改表名?

alter table 库名.表名 rename 库名.新表名;

mysql> show tables;
+------------------+
| Tables_in_tarena |
+------------------+
......
| wage_grade       |
+------------------+
5 rows in set (0.00 sec)

mysql> alter table wage_grade rename 工资表级表;

mysql> show tables;
+------------------+
| Tables_in_tarena |
+------------------+
......
| 工资表级表       |
+------------------+
5 rows in set (0.00 sec)

修改表头名

alter table 库名.表名 change?原表头名 新表头名 数据类型;

mysql> desc 工资表级表;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int     | YES  |     | NULL    |       |
| grade | char(1) | YES  |     | NULL    |       |
| low   | int     | YES  |     | NULL    |       |
| high  | int     | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table 工资表级表 change low 最低值 int;

mysql> desc 工资表级表;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| id        | int     | YES  |     | NULL    |       |
| grade     | char(1) | YES  |     | NULL    |       |
| 最低值    | int     | YES  |     | NULL    |       |
| high      | int     | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改表头位置

alter table 库名.表名 modify 表头名 数据类型 after 表头名;

mysql> alter table tarena.工资表级表 modify high int after grade;

mysql> select * from tarena.工资表级表;
+------+-------+---------+-----------+
| id   | grade | high    | 最低值    |
+------+-------+---------+-----------+
|    1 | A     |    8000 |      5000 |
|    2 | B     |   10000 |      8001 |
|    3 | C     |   15000 |     10001 |
|    4 | D     |   20000 |     15001 |
|    5 | E     | 1000000 |     20001 |
+------+-------+---------+-----------+
5 rows in set (0.00 sec)

修改数据类型?

alter table 库名.表名 modify 表头名 新数据类型; ? ? ? ??

如果表头下没有存储数据，类型随便改；反之 修改的新类型不能与已经存储的数据冲突，冲突的话不许修改

mysql> desc user;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
......
| name         | char(20)    | YES  |     | NULL    |                |
......
+--------------+-------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)

mysql> alter table user modify name varchar(3);
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> alter table user modify name varchar(12);
ERROR 1406 (22001): Data too long for column 'name' at row 14

mysql> alter table user modify name varchar(15);
Query OK, 27 rows affected (0.71 sec)
Records: 27  Duplicates: 0  Warnings: 0



mysql> desc user;    # 此时 shenfenzheng 表头值为空
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
......
| shenfenzheng | char(18)    | YES  |     | NULL    |                |
......
+--------------+-------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

mysql> alter table user modify shenfenzheng char(6);
Query OK, 27 rows affected (1.02 sec)
Records: 27  Duplicates: 0  Warnings: 0

复制表

和系统的cp命令功能一样

表由表头和数据组成

仅仅备份表头

create table 备份到哪个库.备份到哪个表 like 库名.原表;

mysql> create database db1;
mysql> create table db1.wage_garde like tarena.工资表级表;
mysql> use db1;
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| wage_garde    |
+---------------+
1 row in set (0.00 sec)

mysql> desc wage_garde;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| id        | int     | YES  |     | NULL    |       |
| grade     | char(1) | YES  |     | NULL    |       |
| 最低值    | int     | YES  |     | NULL    |       |
| high      | int     | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from  wage_garde;
Empty set (0.00 sec)

备份表头+数据

mysql> create table db1.gzdj2 select * from tarena.工资表级表;
mysql> show tables
    -> ;
+---------------+
| Tables_in_db1 |
+---------------+
| gzdj2         |
| wage_garde    |
+---------------+
2 rows in set (0.01 sec)

mysql> desc gzdj2;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| id        | int     | YES  |     | NULL    |       |
| grade     | char(1) | YES  |     | NULL    |       |
| 最低值    | int     | YES  |     | NULL    |       |
| high      | int     | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from gzdj2;
+------+-------+-----------+---------+
| id   | grade | 最低值    | high    |
+------+-------+-----------+---------+
|    1 | A     |      5000 |    8000 |
|    2 | B     |      8001 |   10000 |
|    3 | C     |     10001 |   15000 |
|    4 | D     |     15001 |   20000 |
|    5 | E     |     20001 | 1000000 |
+------+-------+-----------+---------+
5 rows in set (0.00 sec)

# 仅备份符合条件的数据
mysql> select employee_id,basic from tarena.salary where year(date)=2018 and month(date)=7 and basic > 15000;
+-------------+-------+
| employee_id | basic |
+-------------+-------+
......
62 rows in set (0.00 sec)

mysql> create table db1.salary7 select employee_id,basic from tarena.salary where year(date)=2018 and month(date)=7 and basic > 15000;

mysql> select * from salary7;
+-------------+-------+
| employee_id | basic |
+-------------+-------+
......
62 rows in set (0.00 sec)

数据类型

一、查看已有表里表头存储数据使用的类型

二、了解MySQL服务支持的数据类型

三、掌握常用数据类型的使用

每种数据类型使用固定的命令表示且由固定的存储数据范围

1、数值类型

存储数字的表头使用数值类型

1）整型类型

只存储数值的整数部分（遇小数四舍五入）

2）浮点类型

既存整数又能存储有小数点的数

mysql> create table db1.t12(
    -> 人物 varchar(10),
    -> 等级 tinyint unsigned,
    -> 点劵 float
    -> );
Query OK, 0 rows affected (0.33 sec)
mysql> insert into db1.t12 values("法师",-1,-98);
ERROR 1264 (22003): Out of range value for column '等级' at row 1
mysql> insert into db1.t12 values("法师",0,-98);
Query OK, 1 row affected (0.04 sec)
mysql> insert into db1.t12 values("战士",256,12.25);
ERROR 1264 (22003): Out of range value for column '等级' at row 1
mysql> insert into db1.t12 values("战士",25,12.25);
Query OK, 1 row affected (0.10 sec)
mysql> select * from db1.t12;
+--------+--------+--------+
| 人物   | 等级   | 点劵   |
+--------+--------+--------+
| 法师   |      0 |    -98 |
| 战士   |     25 |  12.25 |
+--------+--------+--------+
2 rows in set (0.00 sec)
mysql> insert into db1.t12 values("士兵",19.25,12.25);
Query OK, 1 row affected (0.05 sec)

mysql> select * from db1.t12;
+--------+--------+--------+
| 人物   | 等级   | 点劵   |
+--------+--------+--------+
| 法师   |      0 |    -98 |
| 战士   |     25 |  12.25 |
| 士兵   |     19 |  12.25 |
+--------+--------+--------+
3 rows in set (0.00 sec)

mysql> insert into db1.t12 values("士兵2",19.56,12.25);
Query OK, 1 row affected (0.05 sec)

mysql> select * from db1.t12;
+---------+--------+--------+
| 人物    | 等级   | 点劵   |
+---------+--------+--------+
| 法师    |      0 |    -98 |
| 战士    |     25 |  12.25 |
| 士兵    |     19 |  12.25 |
| 士兵2   |     20 |  12.25 |
+---------+--------+--------+
4 rows in set (0.00 sec)

2、字符类型

1）char

定长字符类型? ?最多255个字符

不够指定字符个数时在右边用空格补全字符个数，超出时无法写入数据

2）varchar? ?

变长字符类型? ?最多65532个字符

按数据实际大小分配存储空间，字符个数超出时无法写入数据

mysql> create table db1.t11(
    -> 姓名 char(4),
    -> 住址 varchar(20)
    -> );
Query OK, 0 rows affected (0.58 sec)

mysql> insert into db1.t11 values("牛牛","天桥下");
Query OK, 1 row affected (0.05 sec)

mysql> insert into db1.t11 values("牛牛爱锤子","天桥下");
ERROR 1406 (22001): Data too long for column '姓名' at row 1

mysql> select * from db1.t11;
+--------+-----------+
| 姓名   | 住址      |
+--------+-----------+
| 牛牛   | 天桥下    |
+--------+-----------+
1 row in set (0.00 sec)

mysql> desc db1.t11;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| 姓名   | char(4)     | YES  |     | NULL    |       |
| 住址   | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3、枚举类型

性别、爱好

表头的值必须在定义的范围内选择

单选 enum(值列表)

多选 set(值列表)

mysql> create table db1.t13( 姓名 char(10),性别 enum("男","女"),爱好 set("吃","睡","玩","钱") );
Query OK, 0 rows affected (0.56 sec)

mysql> desc db1.t13;
+--------+------------------------------+------+-----+---------+-------+
| Field  | Type                         | Null | Key | Default | Extra |
+--------+------------------------------+------+-----+---------+-------+
| 姓名   | char(10)                     | YES  |     | NULL    |       |
| 性别   | enum('男','女')              | YES  |     | NULL    |       |
| 爱好   | set('吃','睡','玩','钱')     | YES  |     | NULL    |       |
+--------+------------------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into db1.t13 values("a","男","吃,睡");
Query OK, 1 row affected (0.06 sec)

mysql> insert into db1.t13 values("b","公公","it,学习");
ERROR 1265 (01000): Data truncated for column '性别' at row 1
mysql> insert into db1.t13 values("b","女","it,学习");
ERROR 1265 (01000): Data truncated for column '爱好' at row 1
mysql> insert into db1.t13 values("b","女","玩");
Query OK, 1 row affected (0.05 sec)

mysql> select * from db1.t13;
+--------+--------+---------+
| 姓名   | 性别   | 爱好    |
+--------+--------+---------+
| a      | 男     | 吃,睡   |
| b      | 女     | 玩      |
+--------+--------+---------+
2 rows in set (0.00 sec)

4、日期时间类型