-
MySQL基础一
上一节主要了解了MySQL数据库的相关命令和数据库中存储的数据类型, 这一节主要是MySQL中对于表的操作。
数据表(或称表): 是数据库的重要储存部分, 是一张二维表格由行和列组成,行称呼为记录,列称之为字段。
在创建表之前 需要打开数据库,使用USE db_name;命令打开数据库。这样就能在db_name下创建表了。
创建表的命令:
CREATE TABLE [IF NOT EXISTS] t_name(
column_name data_type,
...
);
查看当前打开的数据库 SELECT DATABASE();
我们先创建一张工资表,用来练手:
CREATE TABLE t_salary(
username VARCHAR(20),
age TINYINT UNSIGNED,
salary FLOAT(8,2) UNSIGNED
);
解释一下上面的意思 CREATE TABLE 命令为创建表格, UNSIGNED为无符号位，即正数。FLOAT(8,2)表示8位数保留小数点后2位。
ps: 今天听后台说起，如果是涉及到钱的话 可以设置类型为int 然后单位为分。具体的咱也没有学习到就先不谈了~
那么既然创建好了数据表, 如何查看数据表呢?
查看数据表命令
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]
查看数据表的命令是否影响当前使用的数据库呢，可以使用
SELECT DATABASE();查看当前使用的数据库。
表有了 怎么查看数据表中的各个字段呢，也就是表结构如何查看。
查看数据表结构:
SHOW COLUMNS FROM table_name;
工资表结构
好了,既然表已经有了接下来就该如何存储数据了， 在mysql中可以使用insert 插入一条数据(记录)。
插入命令:
INSERT [INTO] table_name [(column1, column2...)] VALUES(value1, value2, ...)
从命令中可以看出 字段可以省略,那如果字段省略之后, 值一定要跟表中的数据一一对应,否则会发生错误。
插入数据(记录)之后,可以使用SELECT命令进行查找数据。SELECT 命令在数据库的使用中 非常普遍。
简单查找命令
SELECT expr,... FROM table_name;
表约束
空值与非空值
- NULL, 字段值可以为空
- NOT NULL， 字段值禁止为空
该约束不能同时出现在同一字段中, 字段默认为NULL我们可以使用一个小例子来进行测试
- 创建t_null表
CREATE TABLE IF NOT EXISTS t_null(
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED NULL
);
- 插入正常数据
INSERT t_null VALUES('Tom', NULL);
正常
- 插入非法数据
INSERT t_null VALUES(NULL, 12);
错误
自增约束
AUTOINCREMENT
- 自动编号, 并且必须与主键组合使用
- 默认情况下 起始为1, 每次的增量为1
主键
PRIMARY KEY
- 每张表只能存一个主键
- 主键保证记录的唯一性
- 主键自动为NOT NULL
- 自增约束必须组合主键使用, 但是主键不一定要组合自增使用
可以使用例子证明:
- 创建表格 (主键自增)
CREATE TABLE IF NOT EXISTS t_primary(
t_id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL
);
- 插入数据
INSERT t_primary (username) VALUES('c');
INSERT t_primary (username) VALUES('oc');
返回结果
- 查看数据
SELECT * FROM t_primary;
查询所有字段结果
ps: 这里的*号为过滤的字段 如果为* 则表示显示全部的字段
显示10的那里是因为我为主键t_id赋值为10, 因为是自增所以下面为11 ， 12， 13..
唯一约束
UNQUE KEY
- 唯一约束
- 唯一约束可以保证记录的唯一性
- 唯一约束的字段可以为空值(NULL)
- 每张数据表可以存在多个唯一约束
还是以一个例子来解释:
- 创建表格
CREATE TABLE IF NOT EXISTS t_unique(
t_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED UNQUE KEY
);
- 插入数据
INSERT t_unique (username, age) VALUES('tom', 20);
INSERT t_unique (username, age) VALUES('tom', 20);
- 错误
重复错误
默认约束
DEFAULT
- 当插入记录时，如果没有明确赋值,则自动赋予默认值
例子:
- 创建表格
CREATE TABLE IF NOT EXISTS t_default(
t_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCRMENT,
username VARCHAR(20) NOT NULL,
sex ENUM('1', '2', '3') DEFAULT '3'
);
- 插入数据
INSERT t_default (username) values ('tom');
- 查看表中数据
SELECT * FROM t_default;
- 数据返回结果
数据返回结果
外键约束
- 父表和字表必须使用相同的存储引擎，而且禁止使用临时表
- 数据表的存储引擎只能是INNODB
- 外键列和参照列必须具有相同的数据类型，其中数字类型的长度或者是否有符号位必须相同，而字符的长度则不同.
- 外键列和参照列必须创建索引，如果外键列不存在索引的话,MySQL将自动创建.
关于最后一条, 如果参照列没有所以MySQL不会创建外键列的索引
例子:
- 创建城市表
CREATE TABLE IF NOT EXISTS t_province(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
p_name VARCAHR(20) NOT NULL
);
- 创建用户表
CREATE TABLE IF NOT EXISTS t_users(
id SMALLINT UNSIGNED PRIMARY KEY AUT_INCREMENT,
username VARCHAR(20) NOT NULL,
p_id SMALLINT UNSIGN,
FOREIGN KEY (p_id) REFERENCES t_province (id)
);
如外键开头所说 如果参照列创建了索引,那么 MySQL会自动为外键列创建索引。虽然我们没有明显的为t_province表的id创建索引,但是id为主键, 主键会默认创建索引。所以t_users表中的p_id字段也会有索引。那么如何证明呢。 可以使用SHOW INDEXES FROM table_name命令查看表中的索引, 当然为了好看还可以使用 \G 让索引成网格状显示。
查看t_province表中的索引
SHOW INDEXES FROM t_province\G;
t_province中的索引
查看
t_users表中的索引
SHOW INDEXES FROM t_users\G;
t_users中的索引
外键约束的参照操作
- CASCADE: 从父表删除或者更新 时自动删除或者更新子表中匹配的行
- SET NULL: 从父表删除或者更新行,并设置字表中的外键列为NULL。如果使用该选项, 必须保证字表列没有指定为NOT NULL
- RESTRICT: 拒绝对父表的删除或更新操作
- NO ACTION : 标准的SQL 的关键字, 在MySQL中与RESTRICFT相同
以上参照操作都是指 在创建好外键列的时候, 在更新表的时候, 字表是否也进行相应的操作
例子:
- 创建车库表
CREATE TABLE IF NOT EXISTS t_garage(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_CREMENT,
garage_name VARCHAR(20) NOT NULL
);
- 创建车表
CREATE TABLE IF NOT EXISTS t_cars(
id SMALLINT UNSINGED PRIMARY KEY AUTO_CREMENT,
car_name VARCHAR(20) NOT NULL,
garage_id SMALLINT UNSIGNED,
FOREIGN KEY (garage_id) REFERENCES t_garage (id) ON DELETE CASCADE
);
因为车库表为父表,车表为字表 所以现在要父表中插入数据,再在子表中插入数据.
-
t_garage插入数据
INSERT t_garage (garage_name) VALUES('A');
INSERT t_garage (garage_name) VALUES('B');
INSERT t_garage (garage_name) VALUES('C');
使用SELECT * FROM t_garage;查看表中数据
t_garage表中数据
-
t_cars插入数据
INSERT t_cars (car_name, garage_id) VALUES ('红旗', 3);
INSERT t_cars (car_name, garage_id) VALUES ('models', 1);
INSERT t_cars (car_name, garage_id) VALUES ('吉利', 3);
使用SELECT * FROM t_cars;查看表中数据
t_cars中数据
- 删除父表中的记录
DELETE FROM t_garage where id = 3;
查看t_garage表中的数据 SELECT * FROM t_garage;
t_garage中的数据
查看
t_cars表中的数据SELECT * FROM t_cars;
t_cars中的数据
由此可以看出 只要是父表中删除了数据,字表中的记录也会被删除。
由于在实际项目中不常使用参照操作，所以也不过多进行了~
表级约束与列级约束
- 对于一个数据列建立的约束,称之为列级约束
- 对多个数据列建立的约束, 称之为表级约束
- 列级约束可以在列定义时声明,也可以在列定义后声明
- 表级约束只能在列定义后声明
修改数据表
- 添加单列
ALTER TABLE table_name ADD [COLUMN] column_name column_definition [FIRST | AFTER column_name]
- 添加多列
ALTER TABLE table_name ADD [COLUMN] (column_name, column_definition, ...)
添加多列的时候不能指定位置关系, 只能在字段的后方。
- 删除列
ALTER TABLE table_name DROP [COLUMN] column_name
- 删除多列
ALTER TABLE t_name DROP [COLUMN] c_name, DROP c_name
用一个例子演示一下:
- 创建表格
CREATE TABLE IF NOT EXISTS t_action(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL
);
- 添加
salary字段
ALTER TABLE t_action ADD salary FLOAT(8,2) UNSIGNED;
- 查看表结构
SHOW CLOUMNS FROM t_action;
表结构
- 添加
age字段
ALTER TABLE t_action ADD age SMALLINT UNSIGNED AFTER username;
- 查看表结构
SHOW COLUMNS FROM t_action;
表结构
可以看到使用AFTER把age字段添加到了username字段的后方.
- 删除
salary字段
ALTER TABLE t_action DROP salary;
- 查看表结构
SHOW COLUMNS FROM t_action;
表结构
- 删除
username添加area_name
ALTER TABLE t_action DROP username, ADD area_name VARCHAR(20) NOT NULL;
- 查看表结构
SHOW COLUMNS FROM t_action;
表结构
由此可见如果想进行多个字段的添加和删除只需要用,隔开即可。
- 添加主键
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type] (index_col_name, ...)
用一个例子说明:
- 创建一个
t_user_test表
CREATE TABLE IF NOT EXISTS t_user_test(
id SMALLINT UNSIGNED,
username VARCHAR(20) NOT NULL
);
- 为
id添加主键约束
ALTER TABLE t_user_test ADD CONSTRAINT PK_user_test PRIMARY KEY (id);
CONSTRAINT 为设置别名，目前还不知道
- 添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE KEY [INDEX|KEY] [index_name] [index_type] (index_col_name);
- 添加外键约束
ALTER TABLE tb_name ADD [CONSTRINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) reference_definition
- 添加/删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal| DROP DEFAULET}
- 删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY;
- 删除唯一约束
ALTER TABLE tbl_name DROP {INDEX | KEY} index_name;
- 删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
- 修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]
修改列定义需要注意的是 如果修改的数据类型是由大往小了更改 那么会造成部分数据丢失。
- 修改列名称
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
- 数据表更名
方法一
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
方法二
RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...
方法二可以更换多个表的名称
插入 insert
第一种方法
INSERT [INTO] tbl_name [(col_name1, ...)] {VALUES|VALUE} ({expr | DEFAULT}, ...), (...),...
举个🌰:
- 创建't_user'表
CREATE TABLE IF NOT EXISTS t_user(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
age SMALLINT UNSIGNED NOT NULL DEFAULT 10,
sex BOOLEAN
);
ps: 这里声明age字段时如果没有设置NOT NULL设置默认约束的时候 会出现语法错误
- 插入数据
- 简单插入
INSERT t_user VALUES(NULL, 'Tom', '123', 13, 1);
插入结果
- 插入(函数)
INSERT t_user VALUES (DEFAULT,'Join', md5('123456'), 14, 2);
插入结果
- 插入(表达式)
INSERT t_user VALUES (DEFAULT, 'left', '123456', 5*6-5, 1);
插入结果
- 插入(多条)
INSERT t_user VALUES (DEFAULT,'right', md5('123456'), 20, 1), (NULL, 'big', '123456', 21, 3);
插入结果
由此可见 mysql即支持表达式还支持函数, 对于自增字段可以使用'DEFAULT'或者NULL默认值。对于多条插入可以以','隔开。
第二种方法
INSERT [INTO] tbl_name SET col_name={expr| DEFAULT }, ...
第二种方法较第一种方法来说,此方法可以使用子查询, 但是只能插入一条数据。
- 插入
t_user表数据
INSERT t_user SET username='haha',password='123456';
因为id 和 age 都有默认值, sex允许为空 所以可以不赋值。
插入结果
第三种方法
INSERT [INTO] tbl_name [(column_name, ..)] SELECT ...
该方法可以把查询出的数据,插入到数据表中.
更新
- 更新记录(单表)
UPDATE [LOW_PRIORITY] [IGNORE] tbl_refrenece SET col_name1={expr1|DEFAULT} [, col_name2={expr2}|DEFAULT]... [WHERE where_condition]
UPDATE t_user SET age = age+10, sex = 0;
删除
- 删除记录(单表)
DELETE FROM t_user where id = 6;
查询
where查询表达式GROUP BY
查询结果分组
[GROUP BY {col_name|position} [ASC|DESC], ...]
ASC为升序 为默认
DESC为降序
🌰
- 创建
t_users表格
CREATE TABLE IF NOT EXISTS t_users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(40) NOT NULL,
age TINYINT UNSIGNED NOT NULL DEFAULT 10,
sex BOOLEAN
);
users表结构
- 插入数据
INSERT t_users VALUES (DEFAULT, 'tom', '123456', 20,1), (DEFAULT, 'alex', '123456', 20, 1), (DEFAULT, 'tornado', '123', 19, 1), (DEFAULT, 'fdfd', '123', 10, 0);
插入结果
- 以
sex分组
SELECT sex FROM t_users GROUP BY sex;
分组结果
-
HAVING分组条件
分组可以对全部记录做分组 也可以指定部分记录作为分组。
🌰
- 插入基础数据
INSERT t_users VALUES (DEFAULT,'pp', '123456', 10, 0),
(DEFAULT,'dd', '123456', 26, 0);
group by
SELECT sex,age FROM t_users GROUP BY sex,age having age >10;
-
order by排序
[ORDER BY {col_name|expr|position} [ASCC|DESC], ...]
🌰
- 按照id降序排列
SELECT * FROM t_users ORDER BY id DESC;
排序结果
- 按照age 降序如果相同按照id升序
SELECT * FROM t_users ORDER BY age DESC , id;
排序结果
限制查询LIMIT
[LIMIT{[offset,] row_cout|row_cout OFFSET offset}]
还是使用t_users来做例子
- 查询两条数据
SELECT * FROM t_users LIMIT 2;
- 从第三条数据开始 查询两条
SELECT * FROM t_users LIMIT 2,2;
因为数据库中记录的索引从0开始 所以索引为2的记录是第三条数据
-
limit只按照查询结果来计算索引 不管id为大还是小
=========================================
暂存有空做修改
