MYSQL之基础大入门(二)

4. 数据库表的操作
5. 约束,函数和运算符
6. 操作表中数据
7. 存储过程和触发器
8. 事务和字符集
9. 用户管理
10. 备份 恢复和日志

数据库表的操作

创建表

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];

##

CREATE TABLE 语句的主要语法及使用说明如下 ：

CREATE TABLE：用于创建给定名称的表，必须拥有表CREATE的权限。

<表名>

： 指定要创建表的名称，在 CREATE TABLE 之后给出，必须符合标识符命名规则。表名称被指定为 db_name.tbl_name，以便在特定的数据库中创建表。无论是否有当前数据库，都可以通过这种方式创建。在当前数据库中创建表时，可以省略 db-name。如果使用加引号的识别名，则应对数据库和表名称分别加引号。例如，'mydb'.'mytbl' 是合法的，但 'mydb.mytbl' 不合法。

<表定义选项>：

表创建定义，由列名（col_name）、列的定义（column_definition）以及可能的空值说明、完整性约束或表索引组成。 默认的情况是，表被创建到当前的数据库中。若表已存在、没有当前数据库或者数据库不存在，则会出现错误。 eg:

 CREATE TABLE tb_emp1
   -> (
   -> id INT(11),
   -> name VARCHAR(25),
   -> deptId INT(11),
   -> salary FLOAT
   -> );

在 MySQL 中可以使用 ALTER TABLE 语句来改变原有表的结构，例如增加或删减列、更改原有列类型、重新命名列或表等。

其语法格式如下：
ALTER TABLE <表名> [修改选项]

修改选项的语法格式如下：
{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名> }

修改表名

MySQL 通过 ALTER TABLE 语句来实现表名的修改，语法规则如下：

ALTER TABLE <旧表名> RENAME [TO] <新表名>；

修改字段名称

MySQL 中修改表字段名的语法规则如下：

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；

修改字段数据类型

修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。在 MySQL 中修改字段数据类型的语法规则如下：

ALTER TABLE <表名> MODIFY <字段名> <数据类型>;

删除字段

删除字段是将数据表中的某个字段从表中移除，语法格式如下：

ALTER TABLE <表名> DROP <字段名>

删除表

DROP TABLE 语句可以删除一个或多个数据表，语法格式如下：

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]

关联删除:
1.SHOW CREATE TABLE a; 命令查看表 的外键约束
2.删除被数据表 a关联的数据表b ，SQL 语句如下：
DROP TABLE b;
3.解除子表 a 的外键约束，SQL语句和运行结果如下：
ALTER TABLE a ROP FOREIGN KEY fk_emp4_emp5
4.
解除关联关系后，可以使用 DROP TABLE 语句直接删除父表 b，SQL 语句如下：
DROP TABLE b;

查看表结构:

DESCRIBE/DESC 语句会以表格的形式来展示表的字段信息，包括字段名、字段数据类型、是否为主键、是否有默认值等，语法格式如下：
DESCRIBE <表名>;
或简写成：
DESC <表名>;

SHOW CREATE TABLE 命令会以 SQL 语句的形式来展示表信息。

它可以查看表的存储引擎和字符编码；另外，你还可以通过\g或者\G参数来控制展示格式。
SHOW CREATE TABLE 的语法格式如下

SHOW CREATE TABLE <表名>;

数据表添加字段

一个完整的字段包括字段名、数据类型和约束条件。
MySQL 添加字段的语法格式如下：
ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];
MySQL 默认在表的最后位置添加新字段，

如果希望在开头位置（第一列的前面）添加新字段，那么可以使用 FIRST 关键字，语法格式如下：
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;
FIRST 关键字一般放在语句的末尾

在中间位置添加字段
MySQL 除了允许在表的开头位置和末尾位置添加字段外，还允许在中间位置（指定的字段之后）添加字段，此时需要使用 AFTER 关键字，语法格式如下：
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;

AFTER 的作用是将新字段添加到某个已有字段后面。

导出导入操作

MySQL导出导入命令的用例
1.导出整个数据库
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
2.导出一个表
mysqldump -u 用户名 -p 数据库名表名> 导出的文件名
mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
3.导出一个数据库结构
mysqldump -u wcnc -p -d --add-DROP-TABLE smgp_apps_wcnc >d:wcnc_db.sql
-d 没有数据 --add-DROP-TABLE 在每个create语句之前增加一个drop TABLE
4.导入数据库
常用source 命令
进入mysql数据库控制台,
如mysql -u root -p
mysql>USE 数据库
然后使用source命令,后面参数为脚本文件(如这里用到的.sql)
mysql>source d:wcnc_db.sql

---

约束,函数和运算符

MySQL 中，主要支持以下 6 种约束：
1）主键约束
主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。
2）外键约束
外键约束经常和主键约束一起使用，用来确保数据的一致性。
3）唯一约束
唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的，虽然只能有一个空值。
4）检查约束
检查约束是用来检查数据表中，字段值是否有效的一个手段。
5）非空约束
非空约束用来约束表中的字段不能为空。
6）默认值约束
默认值约束用来约束当数据表中某个字段不输入值时，自动为其添加一个已经设置好的值

主键约束:

在创建表时设置主键约束

1<

设置单字段主键
在 CREATE TABLE 语句中，通过 PRIMARY KEY 关键字来指定主键。

在定义字段的同时指定主键，语法格式如下：

<字段名> <数据类型> PRIMARY KEY [默认值]

eg:
 CREATE TABLE TableName
    -> (
    -> id INT(11) PRIMARY KEY,#设置id为主键;
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT
    -> );
    
   #最后写主键约束
[CONSTRAINT <约束名>] PRIMARY KEY [字段名]
CREATE TABLE TableName
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(id)
    -> );

2<

在创建表时设置联合主键

主键由多个字段联合组成，语法格式如下：

PRIMARY KEY [字段1，字段2，…,字段n]

eg:
> (
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(name,deptId)
    -> );

注意：当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。

在修改表时添加主键约束

在修改数据表时添加主键约束的语法格式如下：

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

删除主键约束

删除主键约束的语法格式如下所示：

ALTER TABLE <数据表名> DROP PRIMARY KEY;

主键自增长:

通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长。语法格式如下：

字段名 数据类型 AUTO_INCREMENT


eg

指定主键从 100 开始自增长。SQL 语句和运行结果如下：
mysql> CREATE TABLE tb_student2 (
    -> id INT NOT NULL AUTO_INCREMENT,#自增长
    -> name VARCHAR(20) NOT NULL,
    -> PRIMARY KEY(ID)
    -> )AUTO_INCREMENT=100;#从一百开始增长

外键约束

在 CREATE TABLE 语句中

，通过 FOREIGN KEY 关键字来指定外键，具体的语法格式如下：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

在修改表时添加外键约束

在修改数据表时添加外键约束的语法格式如下：

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

删除外键约束

当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系。

删除外键约束的语法格式如下所示：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

唯一约束

在定义完列之后直接使用 UNIQUE 关键字指定唯一约束，语法格式如下：

<字段名> <数据类型> UNIQUE

在修改表时添加唯一约束

在修改表时添加唯一约束的语法格式为：

ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);

删除唯一约束

在 MySQL 中删除唯一约束的语法格式如下：

ALTER TABLE <表名> DROP INDEX <唯一约束名>;

设置检查约束的字段

检查约束使用 CHECK 关键字，具体的语法格式如下：

CHECK <表达式>


#修改表时设置检查约束的语法格式如下：
ALTER TABLE tb_1 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)

#删除检查约束

#修改表时删除检查约束的语法格式如下：
ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;

默认值 default

创建表默认:

#创建表时可以使用 DEFAULT 关键字设置默认值约束，具体的#语法格式如下：
<字段名> <数据类型> DEFAULT <默认值>;

#在修改表时添加默认值约束
#修改表时添加默认值约束的语法格式如下：
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;


#删除默认值约束
#当一个表中的列不需要设置默认值时，就需要从表中将其删除。

#修改表时删除默认值约束的语法格式如下：
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;

非空约束

在创建表时设置非空约束
创建表时可以使用 NOT NULL 关键字设置非空约束，具体的语法格式如下：
<字段名> <数据类型> NOT NULL;

在修改表时添加非空约束
如果在创建表时忘记了为字段设置非空约束，也可以通过修改表进行非空约束的添加。

修改表时设置非空约束的语法格式如下：
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> NOT NULL;

删除非空约束
修改表时删除非空约束的语法规则如下：
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;


SHOW CREATE TABLE <数据表名>;#这个语句可以查看表中约束;

MySQL运算符

MySQL 支持 4 种运算符，分别是：

1. 算术运算符
   执行算术运算，例如：加、减、乘、除等。
2. 比较运算符
   包括大于、小于、等于或不等于、等等。主要用于数值的比较、字符串的匹配等方面。
3. 逻辑运算符
   包括与、或、非和异或、等逻辑运算符。其返回值为布尔型，真值（1 或 true）和假值（0 或 false）。
4. 位运算符
   包括按位与、按位或、按位取反、按位异或、按位左移和按位右移等位运算符。位运算必须先将数据转换为补码，然后在根据数据的补码进行操作。运算完成后，将得到的值转换为原来的类型（十进制数），返回给用户。

**和Java大同小异不一一演示了;

常用函数

函数名称	作 用
ABS	求绝对值
SQRT	求二次方根
MOD	求余
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号
POW 和 POWER	两个函数的功能相同，都是所传参数的次方的结果值
SIN	求正弦值
ASIN	求反正弦值，与函数 SIN 互为反函数
COS	求余弦值
ACOS	求反余弦值，与函数 COS 互为反函数
TAN	求正切值
ATAN	求反正切值，与函数 TAN 互为反函数
COT	求余切值
MySQL	字符串函数
LENGTH	计算字符串长度函数，返回字符串的字节长度
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
I	NSERT
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串
MySQL 日期和时间函数
CURDATE 和 CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW 和 SYSDATE	两个函数作用相同，返回当前系统的日期和时间值
UNIX_TIMESTAMP	获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
FROM_UNIXTIME	将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
MONTH	获取指定日期中的月份
MONTHNAME	获取指定日期中的月份英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
DAYOFWEEK	获取指定日期对应的一周的索引位置值
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH	获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR	获取年份，返回值范围是 1970〜2069
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE	两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE	两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME	时间加法运算，在原始时间上添加指定的时间
SUBTIME	时间减法运算，在原始时间上减去指定的时间
DATEDIFF	获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值
WEEKDAY	获取指定日期在一周内的对应的工作日索引
MySQL	聚合函数
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG 求平均值	，返回指定列数据的平均值
MySQL	流程控制函数
IF 判断	，流程控制
IFNULL	判断是否为空
CASE	搜索语句