第一天学习MySql(2)-学会表的操作

时间：2022-08-31 18:25:45

2.MySql入门-学会表的操作

2.1 从创建到删除一个表

2.1.1 创建表的语法

创建表之前，我们首先要指定一个数据库。
use database_name；

CREATE TABLE database_name(
参数名 数据类型…,
….
);
如:
CREATE TABLE mydatabase(
num INT,
NAME VARCHAR(10),
);

或者是指明不存在时才创建:
CREATE TABLE IF NOT EXISTS database_name (
num INT,
NAME VARCHAR(10),
);
或者是希望两个连接中使用同名的表而互不干扰：
CREATE TEMPORARY TABLE temp_person (
num INT,
NAME VARCHAR(10),
);
将IF NOT EXISTS和TEMPORARY配合使用可以避免冲突。

2.1.2 MySQL较常用数据类型

数值类型：INT FLOAT DOUBLE
文本类型：CHAR VARCHAR
日期类型：DATE(具体到日)/DATETIME(具体到秒）/TimeStamp（时间戳，通常不用手动干预）

2.1.3 char(20) vs varchar(20)区别，int(5)和int的区别，ZEROFILL

char(20)：占用20个字符空间
varchar(20):根据实际存储数据大小而决定。
int(5)和int的区别：int(5)存储的数组大小固定，int随着长度改变而改变。
ZEROFILL：当int(5)声明的变量实际值当不满五位的时候，会用0进行填充。如把1放进int(5)和int中，在数据库中的显示分别为00005和5。

2.1.4 对表的数据进行约束

2.1.4.1 非空约束，唯一约束，主键约束

如果希望该表中的某样数据以后不为空，在 NAME VARCHAR(10) 后加入 NOT NULL，
或者希望某样数据是唯一的以便加以区分，在 NAME VARCHAR(10) 后加入 UNIQUE，
或者希望某样数据是能够随着插入而自动增长，在 NAME VARCHAR(10) 后加入 AUTO_INCREMENT，
或希望某样数据是唯一的并且不为空的话，在 NAME VARCHAR(10) 后加入 primary key(主键)。
通常情况下，每张表都只有一个主键，并且不建议将有业务含义的字段设为主键。
CREATE TEMPORARY TABLE temp_person (
id INT primary PRIMARY KEY AUTO_INCREMENT，
num INT NOT NULL,
NAME VARCHAR(10) UNIQUE
);

2.1.4.2 外键约束

当希望表的插入数据按照另外一张表为参考进行插入的时候，阻止我们不想要的插入的时候，我们可以用下面的语句。
CREATE TEMPORARY TABLE database1 (
id1 INT，
CONSTRAINT database1_database2_fk FOREIGN KEY(id1) REFERENCES database2(id2)
);
CREATE TEMPORARY TABLE database2 (
id2 INT
);
使用注意：
1. 当database2有某Id存在时，database1才能插入该Id。
2. database2不能删除database1依赖的Id。
3. database1不能修改database2中不存在的Id。
4. 需要删除database2某Id的时候，需要先将database1存在的某Id删除掉，修改同理。

2.1.4 查看表、修改表名

可以看到该数据库下的一些数据类型，参数名，约束信息等。
show create table 表名； 显示表的创建结构
以下三种都是以表格显示表的结构。
desc 表名;
show columns from 表名;
describe 表名;
修改表名:++alter table databasename1 rename to databasename2;++

2.1.5 表中字段的操作

2.1.5.1 向表中添加一个新的字段

alter table database_name add column name varchar(10),column name2 varchar(20)…;

2.1.5.2 修改表中字段名称

alter table database_name change column name newName varchar(10)…;

2.1.5.3 修改表中字段类型

alter table database_name modify column name INT…;

2.1.5.4 删除字段

alter table database_name drop column name…;

2.1.6删除表

drop table database_name;

2.2 操作表中的数据

CREATE TABLE mydatabase(
num INT,
NAME VARCHAR(10),
id INT
);

2.2.1插入数据

INSERT INTO mydatabase VALUES(1,’小铭’,2);
INSERT INTO mydatabase(id) VALUES(1);
INSERT INTO mydatabase(NAME) VALUES(‘小铭’);
注意：插入的时候必须按照创建表的时候的顺序，并且不能缺少，否则应该指明插入所数据所属的字段。

2.2.2修改数据

1.修改该列下所有的数据:
UPDATE mydatabase SET NAME=’小铭’;
2.修改满足条件所有的数据:
UPDATE mydatabase SET NAME=’小李’ WHERE id=1;
UPDATE mydatabase SET NAME=’小铭’,age=15 WHERE id=1;
UPDATE mydatabase SET NAME=’小铭 WHERE id=1 AND age=19;

2.2.3删除数据

1.将表中的所有列下的数据清除
DELETE FROM mydatabase;
TRUNCATE TABLE mydatabase;
2.按条件数据清除
DELETE FROM mydatabase WHERE id=2；

2.2.4注意DELETE与TRUNCATE，drop的区别：

a.TRUNCATE原理大致可以认为是先删除再创建表，因此前者可以回滚，后者不可以。
而且TRUNCATE可以重置自增长约束，DELETE不可以。
b.truncate 和 delete删除数据，不删除表的结构。drop直接删除表的结构，被依赖的约束、触发器、索引也会被删除。
c.其他区别将在以后提及。

2.2.5 什么是触发器？

当我们操作某个表的指定的事件发生，触发器会触发，并调用它的数据库对象。
CREATE TABLE log（content VARCHAR(10));
CREATE TRIGGER tri_Add AFTER 操作 ON mydatabase FOR EACH ROW
INSERT INTO log(content) VALUES(‘日志信息’);
如下：

CREATE TRIGGER tri_Add BEFORE INSERT ON mydatabase FOR EACH ROW
INSERT INTO log(content) VALUES('日志信息：准备添加一条记录');
CREATE TRIGGER tri_Up BEFORE UPDATE ON mydatabase FOR EACH ROW
INSERT INTO log(content) VALUES('日志信息：准备修改一条记录');
CREATE TRIGGER tri_Del BEFORE DELETE ON mydatabase FOR EACH ROW
INSERT INTO log(content) VALUES('日志信息：准备经删除一条记录');
CREATE TRIGGER tri_has_Add AFTER INSERT ON mydatabase FOR EACH ROW
INSERT INTO log(content) VALUES('日志信息：已经添加了一条记录');
CREATE TRIGGER tri_has_Up AFTER UPDATE ON mydatabase FOR EACH ROW
INSERT INTO log(content) VALUES('日志信息：已经修改了一条记录');
CREATE TRIGGER tri_has_Del AFTER DELETE ON mydatabase FOR EACH ROW
INSERT INTO log(content) VALUES('日志信息：已经删除了一条记录');

BEFORE触发器执行失败->SQL无法执行->AFTER触发器不触发->SQL回滚；

2.3查询表中顺序

SELECT *|{column1｜expression, column2｜expression，..} FROM table;
CREATE TABLE mydatabase(
num INT,
NAME VARCHAR(10),
a INT,
b INT
);

2.3.1 查询所有字段

SELECT * FROM mydatabase;

2.3.2 查询指定字段

SELECT id,name FROM mydatabase;
SELECT id AS ‘身份证’,name AS ‘姓名’ FROM mydatabase;//查询结果带别名。
SELECT id ‘身份证’,name ‘姓名’ FROM mydatabase;//查询结果带别名。
SELECT id ‘身份证’,name ‘姓名’,’6班’ ‘固定字段’ FROM mydatabase;//添加常量列

2.3.3 查询且去除重复记录

SELECT DISTINCT id FROM mydatabase;
SELECT DISTINCT(id) FROM mydatabase;

2.3.4 查询后合并记录

SELECT (a+b) AS ‘总和’ FROM mydatabase;

2.3.5 查询后排序

SELECT * FROM mydatabase ORDER BY id ASC;//升序
SELECT * FROM mydatabase ORDER BY id DESC;//降序
SELECT * FROM mydatabase ORDER BY id DESC，a ASC;//先按id降序排序，如果id中存在相同的，则按a进行升序排序。

2.3.6 MySql中的常用的运算符

1.> < <= >= =(比较特殊，可以用来比较是否相等) <>(比较特殊，不等于)
2.BETWEEN 100 AND 200：在100 和 200之间
3.IN(100，200)：在100 和 200之间
4.LIKE ‘张’:张加上一个任意字符都可以满足，一个对应一个字符
5.LIKE ‘张%’:张加任意个字符都可以满足
6.IS NULL：是否为空。
7.AND OR NOT

2.3.7 条件查询

SELECT * FROM mydatabase WHERE id=1 OR name=’张三’;
SELECT * FROM mydatabase WHERE id=1 AND name=’张三’;
SELECT * FROM mydatabase WHERE id=1 AND name<>’张三’;
(其中AND可以替换成OR，=可以替换成> < <= >= = <>)
SELECT * FROM mydatabase WHERE id IS NULL ;
SELECT * FROM mydatabase WHERE id IS NOT NULL ;
SELECT * FROM mydatabase WHERE name LIKE ‘张_’;
SELECT * FROM mydatabase WHERE name LIKE ‘张%’;

2.3.8 分页查询

即显示第几条到第几条数据
SELECT * FROM database_name LIMIT 开始,截取多少个字段;
SELECT * FROM mydatabase LIMIT 0,2;//查询第一条和第二天条数据

2.3.9 聚合函数查询

SELECT MAX(num) FROM mydatabase;//num列下的最大值
SELECT MIN(num) FROM mydatabase;//num列下的最小值
SELECT AVG(num) FROM mydatabase;//num列下的平均值
SELECT C0UNT(num) FROM mydatabase;//num列下的有多少个
SELECT C0UNT(*) FROM mydatabase;//表中的记录数量
注意：使用C0UNT会忽略NULL的字段
SELECT CURRENT_DATE ();//显示当前日期
SELECT CURRENT_TIME();//显示当前时间
SELECT CURRENT_TIMESTAMP ();//显示当前时间戳
SELECT ABS(num );//绝对值
SELECT LEAST (number , number2…);//求最小值
SELECT UCASE (str);//转换为大写
SELECT LCASE (str);//转换为小写

2.3.10 分组查询

SELECT name,COUNT(*) FROM mydatabase GROUP BY name;//统计相同name下有多少条不同的数据
SELECT name,COUNT() FROM mydatabase GROUP BY name HAVING COUNT()<=2 ;//只显示总数小于等于2的name分组
标签：

相关文章