Mysql的基础操作笔记
自从用了一些sql工具后，sql命令语句打的越来越少了，有些命令我都快忘了。为了不让自己学的东西还给书本，我决定这段时间抽时间把mysql的命令符知识再复习一下。
还好学的时候记了笔记，有时候遇到忘记的命令翻翻笔记就可以解决。但是因为自己的笔记记得有些杂乱，我正好趁这个机会好好整理下，最大程度上让命令语句看了就会用。
SQL语句的写法规范：
1.SQL语句系统命令必需大写，名称小写（我使用时用的小写也可以的）
2.结束分隔符以分号或 \g结束
3.支持折行，但函数或名称或引号不能折行
4.名称不能用关键字或保留字，但如果使用用反引号括起来(``) 例`users`
一、Mysql登陆与退出
1.登陆
(1).mysql -u用户名 -p密码
例：mysql -uroot -proot
(2).mysql -u用户名 -p密码 -P端口号 -D选择的数据库
例：mysql -uroot -proot -P3306 -Dtest
2.退出
(1).quit
(2).exit
(3).\q
3.注释
(1). #注释内容
(2). -- 注释内容
4.修改提示符(Prompt)
这个很少用到，但是既然笔记里记了我还是写出来好了。
(1).进入MySQL写命令
例：mysql>prompt 提示符名称
(2). 登录MySQL 时的命令
例：mysql -uroot -p --prompt=提示符名称
说明:
(1)恢复 prompt mysql>
(2)命令
\u：将提示符修改为MySQL用户名
\h：将提示符修改为主机名称
\D: 将提示符修改为系统时间
\d: 将提示符修改为选择的数据库名称
二、SQL（结构化查询语言 Structrued Query Language）
(1) DDL（数据定义语言）:创建数据库，创建表、创建视图等，创建(CREATE)，删除(DROP)，修改(ALTER)。
(2) DML（数据操作语言）:表中的数据的增删改操作，增(INSERT)，删除(DELETE)，更新(UPDATE)。
(3) DQL（数据从查询语言):表中的数据的查询，查询(SELECT)。
(4) DCL（数据控制语言）:用户权限的分配(GRANT)及权限的撤销（REVOKE）。
三、库的操作
1.查看数据库 SHOW DATABASES;
2.创建数据库 CREATE DATABASE [IF NOT EXISTS] 数据库名 [[DEFAULT] CHARACTER SET [=] 编码];
例： CREATE DATABASE IF NOT EXISTS test CHARACTER SET=UTF8;
3.查看创建数据库的命令 SHOW CREATE DATABASE 数据库名；
例: SHOW CREATE DATABASE test;
4.删除数据库 DROP DATABASE [IF EXISTS] 数据库名;
例：DROP DATABASE test;
5.修改数据库编码 ALTER DATABASE [IF EXISTS] 数据库名称 [DEFAULT] CHARACTER SET [=]编码;
例：ALTER DATABASE test CHARACTER SET=gbk;
6.选择数据库 USE 数据库名
例：USE test;
7.查看当前选择的数据库 SELECT DATABASE();
三、数据类型
1.整型
(1)TINYINT (2)SMALLINT (3)MEDIUMINT (4)INT (5)BIGINT
(1) TINYINT([M]) [UNSIGNED|ZEROFILL]
范围: 大小1个字节(Byte 2^8)
值:
有符号位 -128~127
无符号位(正数 UNSIGNED) 0～255
(2) SMALLINT([M]) [UNSIGNED|ZEROFILL]
范围: 大小2个字节(Byte 2^16)
值:
有符号位 -32768~32767
无符号位(正数 UNSIGNED) 0～65535
(3) MEDIUMINT([M]) [UNSIGNED|ZEROFILL]
范围: 大小3个字节(Byte 2^24)
值:
有符号位 -8388608~8388607
无符号位(正数 UNSIGNED) 0～16777215
(4) INT([M]) [UNSIGNED|ZEROFILL]
范围: 大小4个字节(Byte 2^32)
值:
有符号位 -2147483648~2147483647
无符号位(正数 UNSIGNED) 0～4294967295
(5) BIGINT([M]) [UNSIGNED|ZEROFILL]
范围: 大小8个字节(Byte 2^64)
说明:
UNSIGNED：无符号位，正数ZEROFILL :当显示的长度小于指定长度用0填补，注意如果字段属性是ZEROFILL一定是UNSIGNED（自动添加UNSIGNED）
2.浮点型
(1)FLOAT([M,D]) [UNSIGNED|ZEROFILL]：
单精度浮点数，-3.40e38 ~ 3.40e38
(2)DOUBLE([M,D]) [UNSIGNED|ZEROFILL]：
双精度浮点数，-1.79e308 ~1.79e308
(3)DECIMAL([M,D]) [UNSIGNED|ZEROFILL]
定长浮点
说明: 单精度, 双精度当显示的浮点大于指定的位数四舍五入近似值，定长浮点大于指定的位数警告报错，存精确值
3.字符类型
(1)定长字符:CHAR([M]) （0-255字节，一个字母占一个字节）
(2)变长字符:VARCHAR([M])(0-65535字节)
字符字段最多能存的字符
a.一个字段 最大 65535字节
b.与编码有关
gbk:至多 2个字节
utf8:至多 3个字节 65532/3 =21844字节
c.多个字符字段总长度不能超过 65535字节（21844字节）
(3)TINYTEXT：0-255字节
(4)TEXT: 0- 65535字节
(5)MEDIUMTEXT ：0-16777215字节
(6)LONGTEXT: 0～4294967295字节
说明: CHAR定长字符效果高但占空间，VARCHAR变长字符省空间但效果低，TEXT效率最低
(4)布尔类型
a.BOOLEAN
b.TINYINT(1)
(5)枚举类型
ENUM:最多65535字节
例如: sex ENUM('man','woman','secret')
四、约束条件
1. NOT NULL :不能为空
2. DEFAULT ：默认值
3. [PRIMARY] KEY ：主键，一个表中必需有 一个主键，而且主键一定是NOT NULL和不重复
4. UNIQUE [KEY]：不重复，唯一型（例如:用户名,邮箱唯一型 ），一个表中可以有多个不重复
5. AUTO_INCREMENT：自动增长，如果字段是自动增长一定是主键
五、表结构的操作
1.查看表 SHOW TABLES;
2.查看表结构 (1)DESC 表名； (2) DESCRIBE 表名;(3)SHOW COLUMNS FROM 表名;
例：DESC test;
3.创建表结构
CREATE TABLE [IF NOT EXISTS] 表名称(
字段名称(列名称 Field) 数据类型 [字段属性|约束条件],
字段名称(列名称 Field) 数据类型 [字段属性|约束条件],
...
)[ENGINE=InnoDB DEFAULT CHARSET =utf8];
说明:MySQL引擎 InnoDB，MyISAM (实现外键，事务等效果)
例：CREATE TABLE IF NOT EXISTS test (
id INT(10) NOT NULL KEY AUTO_INCREMENT UNIQUE,
usname VARCHAR(20) NOT NULL,
psword VARCHAR(10) NOT NULL ,
sex ENUM('男','女','保密') DEFAULT '保密'
)ENGINE=InnoDB DEFAULT CHARSET =utf8;
4.查看表的创建代码 SHOW CREATE TABLE 表名;
例：SHOW CREATE TABLE test；
5.添加字段
(1)添加单个字段
ALTER TABLE 表名
ADD 字段名称 数据类型[字段属性|约束条件][FIRST|AFTER 字段名称];
例：ALTER TABLE test
ADD fid INT(10) NOT NULL DEFAULT 1 AFTER id；
(2) 添加多个字段
ALTER TABLE 表名
ADD 字段名称 数据类型[字段属性|约束条件][FIRST|AFTER 字段名称],
ADD 字段名称 数据类型[字段属性|约束条件][FIRST|AFTER 字段名称]......;
例：ALTER TABLE test
ADD fid INT(10) NOT NULL DEFAULT 1 AFTER id,
ADD class INT(10) NOT NULL DEFAULT 1 FIRST usname；
6.删除字段
ALTER TABLE 表名
DROP 字段名称,
DROP 字段名称......；
例：ALTER TABLE test
DROP fid ,
DROP class；
7.修改字段
ALTER TABLE 表名
MODIFY 字段名称 数据类型 [字段属性|约束条件] [FIRST|AFTER 字段名称];
例： ALTER TABLE test
MODIFY fid int(10) NOT NULL DEFAULT 2;
8.修改字段名称
ALTER TABLE 表名
CHANGE 旧字段名称 新字段名称 数据类型 [字段属性|约束条件][FIRST|AFTER 字段名称];
例：ALTER TABLE test
CHANGE fid cid INT(10) NOT NULL DEFAULT 0 AFTER id;
9.添加默认值
ALTER TABLE 表名
ALTER 字段名称 SET DEFAULT ‘值’;
例：ALTER TABLE test
ALTER cid SET DEFAULT 3；
10.删除默认值
ALTER TABLE 表名
ALTER 字段名称 DROP DEFAULT;
11.添加主键
ALTER TABLE 表名
ADD PRIMARY KEY(字段名称);
12.删除主键
ALTER TABLE 表名
DROP PRIMARY KEY；
说明:如果主键字段有AUTO_INCREMENT,必需先删除AUTO_INCREMENT才能删除主键。
13.添加唯一性
(1).ALTER TABLE 表名
ADD UNIQUE KEY|INDEX(字段名称);
(2).ALTER TABLE 表名
ADD [CONSTRAINT 约束名称] UNIQUE KEY|INDEX(字段名称);
14. 删除唯一性
ALTER TABLE 表名
DROP KEY|INDEX 唯一性的约束名称；
说明:添加唯一性没有约束名称，那默认将字段名称作为约束名称。
15.重命名表名
(1) ALTER TABLE 表名
RENAME [AS|TO] 新表名;
(2) RENAME TABLE 旧表名TO 新表名;
16.自增重置
ALTER TABLE 表名
AUTO_INCREMENT = 值;
六、表数据的操作
1.添加(INSERT):
(1). 不列出字段名称的添加(从第一个字段到最后一个字段都要赋值)
INSERT 表名 VALUE[S](值,值,值...);
(2). 列出字段名称一条记录
INSERT 表名(字段名称,字段名称...) VALUE[S] (值,值,值...);
(3). 列出字段名称多条记录
INSERT 表名(字段名称,字段名称...) VALUE[S] (值,值,值...),(值,值,值...)...;
(4).INSERT ...SET 只能添加一条记录
INSERT 表名 SET 字段名称=值，字段名称=值...;
2.删除(DELETE)
(1)DELETE FROM 表名 [WHERE 条件]
例：DELETE FROM test WHERE id=1；
(2) TRUNCATE 表名
说明: DELETE如果没有WHERE条件，清空整个表数据，不能重置AUTO_INCREMENT,
TRUNCATE清空整个表数据，能重置AUTO_INCREMENT。
3.更新(UPDATE)
UPDATE 表名 SET 字段名称=值,字段名称=值...[WHERE 条件]；
例： UPDATE test SET usname='小强'，pasword='000000' WHERE id=1；
说明:UPDATE如果没有WHERE 条件修改的字段中所有记录都是一个值
4.表中数据的查询(DQL)
1. 查询所有字段(效率低)
SELECT * FROM 表名;
例：SELECT * FROM test;
2.列出字段名称的查询
SELECT 字段名称|expr ,字段名称|expr ... FROM 表名;
SELECT id,usname,pasword FROM test；
3. 给字段起别名
SELECT 字段名称 [AS]别名 ... FROM 表名;
例：SELECT usname AS '用户名',pasword AS '密码' FROM test;
4. 给表起别名
SELECT 字段名称 ... FROM 表名 [AS] 别名;
例：SELECT id,usname,pasword FROM test AS '表1'；
5. 表名.字段名称（字段来自哪个表）
SELECT 表名.字段名称 ... FROM 表名;
例：SELECT test.id,usname FROM test;
6. 数据库名.表名（表来自哪个数据库）
SELECT 字段名称 ... FROM 数据库名.表名;
例：SELECT id,usname,psword FROM test.test；
7.SELECT 字段名称|expr ,字段名称|expr ... FROM 表名
[WHERE 条件]
[GROUP BY 字段]
[HAVING 条件]
[ORDER BY 字段]
[LIMIT [$offset],$length]
7.1 WHERE 条件：条件过滤
(1)比较运算符: > >= < <=
= != <>
<=> (判断null值)
(2) IS [NOT] NULL 判断null值
(3) [NOT] BETWEEN ...AND 范围值
(4) [NOT] IN(值,值...); 列出值
(5) 逻辑运算符
!（非）
AND && （与，并且）
OR || （或者）
(6) [NOT] LIKE '字符串' 模糊查询
关键符号:
_ ： 任意一个字符
% ： 任意多个字符（0个 1个，多个）
7.2 GROUP BY 字段名称：分组
说明:
(1)将某一个字段中相同的值分为一组，分组只显示一个结果值,而且显示小编号的值。一般显示分组的那个字段。
(2)GROUP BY分组结合聚合函数使用COUNT(*):获得每组中的个数，包含null
COUNT(字段)获得每组中的个数，不包含null
AVG(字段)：获得每组中平均值
MAX(字段)：获得每组中最大值
MIN(字段)：获得每组中最小值
SUM(字段)：获得每组中和
7.3 HAVING 条件 ：二次过滤
说明:
(1)WHERE条件对字段过滤
(2) HAVING 对个结果过滤一般结合GROUP BY一起使用
7.4 ORDER BY 字段名称：对字段排序
说明: ORDER BY 字段名称
ASC 默认值升序|DESC 降序
7.5 LIMIT [$offset,]$length：显示前几条记录
说明:
(1)$offset：偏移量，起始编号，编号从0开始
(2) $length：显示的记录(行)数
(3) WEB程序中实现分页
后面还有 后面抽时间补更。
浙公网安备 33010602011771号