MySQL基础知识
MySQL基础知识
MySQL参数
-D 打开指定数据库
-h 服务器名称（默认本地服务器127.0.0.1）
-p 密码（可不输入参数以隐藏）
-P 端口号（默认3306）
-u 用户名（参数root）
-V 输出版本信息并退出
MySQL退出
mysql>exit;
mysql>quit;
mysql>/q;
MySQL常用命令
SELECT VERSION ( ); 显示当前服务器版本
SELECT NOW ( ); 显示当前日期时间
SELECT USER ( ); 显示当前用户
SELECT 3+5;（计算3+5的值）
MySQL语句的规范
关键字和函数名称全部大写；
数据库名称、表名称、字段名称全部小写；
SQL语句必须以分号结尾；
CREATE DATABASE t1; 创建数据库t1，编码方式默认为utf8；
SHOW CREATE DATBASE t1; 显示创建数据库t1时的命令信息；
SHOW WARNINGS; 显示警告；
CREATE DATABASE IF NOT EXIXTS t2 CHARACTER SET gbk; 设置编码方式并创建数据库t2；
ALTER DATABASE t2 CHARACTER SET=utf8; 改变数据库t2的编码方式；
DROP DATABASE (IF EXISTS) t1; 删除数据库t1；
USE test; 使用/打开数据库test；
SELECT DATABASE () ; 显示当前打开的数据库；
创建数据表：
示例tb1：
CREATE TABLE tb1 (
username VARCHAR(20),
age TINYINT UNSIGNED,（无符号，即全部为正；）
salary FLOAT(8，2) UNSIGNED （【8,2】是指一共8位，其中小数点后占两位，无符号，全部为正；）
)；
示例tb2：
CREATE TABLE tb2 (
username VARCHAR(20) NOT NULL,（NOT NULL 表示记录必须有该字段的赋值）
age TINYINT UNSIGNED NULL（NULL可以省略）
);
示例tb3：
CREATE TABLE tb3 (
Id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,（PRIMARY可以省略；）
username VARCHAR(30) NOT NULL
);（设置主键约束和自动编号——自动编号的字段必须设置为主键；另外主键不一定和自动编号一起使用，主键可以单独使用，并且可以赋值，但赋值必须唯一，这也保证了记录的唯一性，需要注意的是一个表中只能有一个主键；）
示例tb4：
CREATE TABLE tb4 (
Id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
age TINYINT UNSIGNED
);（UNIQUE KEY 唯一约束，一个表中可以有多个，也是为了保证记录的唯一性，唯一约束的字段可以为空值；）
示例tb5：
CREATE TABLE tb5(
Id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
sex ENUM('1' '2' '3') DEFAULT '3'
);（DEFAULT默认约束，如果记录没有给该字段赋值，则默认字段的值为默认值；）
SHOW TABLES; 查看数据表；
SHOW TABLES FROM mysql; 查看数据库mysql中所有的数据表；
SHOW COLUMNS FROM tb1; 查看数据表tb1 的结构；
外键约束：
要求：
-
父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
-
数据表的存储引擎只能为InnoDB。
-
外键列和参照列必须具有相似的数据类型。其中数字的长度和是否具有符号位必须相同；而字符的长度则可以不同。
-
外键列和参照列必须创建索引。如果外键列不存在索引的话，mysql将自动创建索引。
示例：
USE test;
CREATE TABLE provinces(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20) NOT NULL
);（创建父表，其中PRIMARY KEY 为参照列自动创建索引）
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
pid SMALLLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES provinces (id)
);（创建子表，其中pid字段为外键列，其数据类型必须同参照列相同，且索引为系统自动创建）
CREATE TABLE users1(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
pid SMALLLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
);（CASCADE 表示从父表删除或更新且自动删除或更新子表中匹配的行；也可使用：SET NULL表示从父表删除或更新行，并设置子表中的外键列为NULL,当然前提是子表列没有指定NOT NULL;或者使用RESTRICT（或NO ACTION）表示拒绝对父表的删除或更新操作）
DELETE FROM provinces WHERE id=3;（删除操作，删除附表中的id=3的行）
增加单列：
例如：
ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
（绿色部分为新增加的列的定义，age为列的名字，users1为要修改的数据表）
ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;
（红色部分表示在名称为username的列之后插入新增加的列password）
ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;
（FIRST 表示把新的列truename添加在所有列的前面）
增加多列同上，不同的是添加的列要用小括号括起来，并且多列只能添加在表的最后。
删除列：
删除单列：ALTER TABLE users1 DROP truename;
删除多列：ALTER TABLE users1 DROP age，DROP password;
也可以删除一列的同时再增加一列，只需要用逗号隔开。
添加主键约束：
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);
（灰色和红色部分可以不加，其中红色部分为约束的名字；约束的字段为id）
添加唯一约束：
ALTER TABLE users2 ADD UNIQUE (username);
添加外键约束：
ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);
添加默认约束：
ALTER TABLE users2 ALTER age SET DEFAULT 15;
（设置字段age的默认值为15）
删除默认约束：
ALTER TABLE users2 ALTER age DROP DEFAULT;
删除主键约束：
ALTER TABLE users2 DROP PRIMARY KEY;
删除唯一约束：
先查看索引名字：SHOW INDEXES FROM users2;
再删除：ALTER TABLE users2 DROP INDEXE username；
（注意username是约束名而不是字段名）
删除外键约束：
先查看约束名字：SHOW CREATE TABLE users2;
再删除：ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
修改字段：
把id字段移动到最前面：ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
修改id字段定义：ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL；
修改pid字段名称：ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
修改数据表名称：
方法一：ALTER TABLE users2 RENAME users3;
方法二：RENAME TABLE users2 TO users3;（可以为多张数据表更名，用逗号隔开）
插入记录：
示例1：
INSERT tb1 VALUES('Tom',25，7863.25);（省略了每列的名称，故必须给所有列赋值；）
INSERT tb1 (username,salary) VALUES('Jhon',4500.69);（给表的部分字段赋值；）
（可同时插入多条记录，以逗号隔开）
示例2：
INSERT tb1 SET usersname='Ben' , age='22';
（与示例1不同的是可以使用子查询【SubQuery】 ，且每次只能插入一条数据）
SELECT * FROM tb1; 在tb1中进行记录查找（*是用来过滤字段）；
在一个表中插入另一个表中的记录：
INSERT test(username) SELECT usename FROM users WHERE age>=30;（要注意字段数量的匹配）
更新记录（单表更新）：
UPDATE users SET age=age+5;（表示表中所有的记录中的年龄都在原有基础上加上5）
UPDATE users SET age=age-id,sex=0;（表中所有记录的age=原有的age减去id值，并且所有的记录的sex都设置为0）
UPDATE users SET age=age+10 WHERE id%2=0;（蓝色部分表示条件，即id为偶数的记录）
删除记录（单表删除）：
DELETE FROM users WHERE id=6;（删除数据表中的第6条记录，如果不加WHERE条件则删除所有记录）
查询记录：
SELECT * FROM users；（查询users表中的所有字段的记录）；
SELECT id,username FROM users;（查询users表中的id和username字段的记录，二者顺序可调换是查询显示结果调换）
SELECT users.id,users.username FROM users;（方便多表查询）
SELECT id AS userId,username AS uname FROM users;（为字段id和username设置别名）
SELECT id username FROM users；（相当于id AS username，即把username作为id的别名）
查询结果分组：
SELECT sex FROM users GROUP BY sex;（按性别对记录进行分组，显示的是可以分为几类）
SELECT sex,age FROM users GROUP BY sex HAVING age>35;（对分组进行条件的指定，需要注意的是条件中的age必须是已出现的，或者也可以采用函数的形式，如下所示：）
SELECT sex FROM users GROUP BY sex HAVING count(id)>=2;
查询结果排序：
SELECT * FROM users ORDER BY id DESC;（DESC表示降序排列）
SELECT * FROM users ORDER BY age;（默认以年龄升序排列）
SELECT * FROM users ORDER BY age，id DESC;（年龄相同时，再以id降序排列）
限制查询结果返回数量：
SELECT * FROM users LIMIT 2;（返回前两条记录）
SELECT * FROM users LIMIT 3,2;（返回第4条和第5条记录，因为计数是从0开始的）
SELECT * FROM users ORDER BY id DESC LIMIT 3,2;（返回id降序排列后的第4条和第5条记录）
浙公网安备 33010602011771号