数据表的操作

数据库表的操作

创建表　　

　　CREATE TABLE tab_name(
　　filed1 TYPE [约束条件], /*名字，类型，每项用逗号隔开*/
　　filed2 TYPE
　　);
　　约束条件：
　　　　PRIMARY KEY （非空且唯一，能够唯一区分当前内容的字段称之为主键）
　　　　UNIQUE
　　　　NOT NULL
　　　　AUTO_INCREMENT （主键字段必须为数字类型）
　　　　外键约束 foreign key

查看表

　　desc tab_name;　　 /*查看表结构*/
　　show columns from tab_name; 　　 /*查看表结构*/
　　show tables;　　 /*查看当前数据库中所有的表*/
　　show create table tab_name; 　　 /*查看创建表的代码语句*/

修改表的结构　

　　增加字段
　　　　alter table tab_name add 列名约束条件 [frist|after 字段名]　　　　 /*把新增的字段放到某个字段前或后*/
　　增加多个字段
　　　　alter table tab_name add 列名1,
　　　　add 列名2;
　　删除字段
　　　　alter table tab_name drop 列名;
　　删除多个字段
　　　　alter table tab_name drop 列名1,
　　　　drop 列名2;
　　删除表
　　　　drop table tab_name;
　　修改字段
　　　　alter table tab_name modify 列名类型约束条件 first|after 字段名; 　　　　 -- 改类型
　　　　alter table tab_name change 列名新列名类型约束条件 first|after 字段名; -- 改列名
　　修改表名
　　　　rename table 表名 to 新表名
　　修改表用的字符集
　　　　alter table tab_name character set utf8
　　删除主键
　　　　alter table tab_name modify 列名类型;
　　　　alter table tab_name drop primary key; 　　 -- 两句合在一起才会生效

表记录的增删改查

　　新增表记录

　　增加一条记录
　　　　insert [into] tab_name (filed1,filed2....) values (value1,value2,....);
　　　　注意字段名和值要一一对应
　　增加多条记录
　　　　insert [into] tab_name (filed1,filed2....) values (value1,value2,....),
　　　　　　　　　　　　　　　　　　　　　　　　 (value1,value2,....);
　　　　/*多条记录用逗号隔开*/
　　set 插入
　　　　insert [into] tab_name set name=value; -- 通过键值对插入

　　
　　修改表记录
　　　　update tab_name set filed1=value1,filed2=value2,.... [where 语句];
　　　　update tab_name set filed1=filed1+value [where 语句];
　　　　/*
　　　　*update 可以将每行的各列更新新值替换原有的值
　　　　*也可以在原有的基础上再加减变动调整
　　　　*where 指定更新哪些行，如果不加where语句，则更新全部行
　　　　*/

　　删除表记录
　　　　delete from tab_name [where filed1=value1 or filed2=value2];
　　　　/*
　　　　*如果不加where语句，则删除整张表中的数据
　　　　*注意delete不能删除表，删除表用drop
　　　　*truncate table 也可以删除整张表的数据，此语句首先是摧毁表，再建新表，不可在事务中恢复
　　　　*/

　　查看表记录
　　　　select [distinct] *|filed1,field2... from tab_name;
　　　　　　-- 查看表记录,*代表所有
　　　　　　-- distinct代表剔除重复项，只展示表中值不同的行
　　　　select name,age+10 from tab_name;
　　　　select name as 姓名,age+10 as 年龄 from tab_name;
　　　　　　-- 将查询到的结果加10，注意只是显示出加10，库中的表数据还是原来的
　　　　　　-- 字段名 as 别名，将字段用别名显示出来
　　　　select * from tab_name where field1=value1;
　　　　　　-- 通过where语句过滤出符合的
　　　　　　/*
　　　　　　*where中的运算符
　　　　　　* 比较运算符：> < >= <= !=
　　　　　　* between value and value 　 not between value and value
　　　　　　* in(value1,value2,...) 　　　　not in 　　　　在指定的值中
　　　　　　* like 'ss%' 　　　　　　　　 not like% 　　　搜索匹配含目标字符串的数据，%代表后面可有任意字符 _ 代表一个字符
　　　　　　* is null 　　　　　　　　　 is not null 　　空值判断
　　　　　　* and not or 　　　　　　且非或
　　　　　　*/

　　　　　　在 sql 中 and 的操作符要比 or 的优先级高，因此会出现不理想的过滤结果

　　　　　　　　例：select school,age from tab_name where school = 'ss' or school = 'yy'' and age >= 15

　　　　　　　　　　这种情况下，sql会认为需筛选出school为ss的所有的数据，school为yy且age大于等于15的数据

　　　　　　　　　　相要修改上面的筛选结果，可以使用圆括号()调整优先级，这样就可以筛选出school 为ss或yy的age大于等于15的数据

　　　　　　　　　　select school,age from tab_name where (school = 'ss' or scholl = 'yy') and age >= 15

　　　　　　例：select age from tab_name where between 15 and 25;

　　　　　　--instr(源字符串,'目标字符串','开始位置','第几次出现') 查找目标字符串在源字符串是否存在，并返回位置。

　　　　　　　　　　　　　　　　　　　后两个参数不写时默认为1，表示从源字符串第一个位置开始查找，目标字符串第一次出现的位置

　　　　　　　　　　　　　　　　　　　没有查到返回 0，所以可以通过instr(,) > 0 过滤出含目标字符串的数据

　　　　　　例：select * from tab_name where instr(a.name,'ss') > 0

　　　　　　replace(a.name,substr(a.name,1,2),'*') --替换字符：字段，被替换的字符，新字符。substr提取字符，参数：字段，第几位开始，提多少个

　　　　order by 字段名 [asc|desc] -- 排序[正序|倒序]
　　　　　　例：select name from tab_name order by name;
　　　　group by -- 分组
　　　　　　注意：按照分组条件分组
　　　　　　　　后面接字段名
　　　　　　例：select name from tab_name group by name;
　　　　　　having语句，过滤符合条件的，分组后过滤
　　　　　　例：select name from tab_name group by name having sum(总成绩)>80;

　　　　　　　　-- 按 name 分组后，再过滤出总成绩大于80的
　　　　　　/*
　　　　　　*sum 聚合函数
　　　　　　*where语句可以用的地方，having也可以
　　　　　　*where是先过滤再计算，having是先分组再过滤
　　　　　　*where不能使用聚合函数
　　　　　　*/

　　　　case ... when ... then ... else ... end　　--什么条件下是什么，否则是什么，和其他语言的if...else类似

　　　　　　例：select case a.name when 'ss' then '1' when 'yy' then '2' else '3' end from tab_name a

　　　　　　　　也可以将字段名放在when里

　　　　　　　　select case when a.name = 'ss' then '1' when a.name = 'yy' then '2' else '3' end from tab_name a

　　聚合函数
　　　　count(字段名) 　　　　-- 统计个数，注意它不能统计null
　　　　　　select count(ifnull(成绩,0)) from tab_name;
　　　　　　　　-- 如果要统计null，用ifnull将null转换成0
　　　　sum() -- 求和
　　　　avg() -- 平均值
　　　　max()，min() -- 不统计null
　　正则表达式查询
　　　　select * from tab_name where name RegExp '^ss';

外键约束

设置外键：在创建子表时
　　[CONSTRAINT 外键名] FOREIGN KEY (子表字段名) REFERENCES 主表名(主表字段名);
　　-- 注意：用外键约束的子表字段名要和关联的主表字段名数据类型相同

已经创建好表之后额外增加外键和删除外键
　　ALTER TABLE 子表名 ADD CONSTRAINT 外键名
　　　　　　　　　　　　　　 FOREIGN KEY (子表字段名)
　　　　　　　　　　　　　　 REFERENCES 主表名(主表字段名);
　　ALTER TABLE 表名 DROP FOREIGN KEY 外键名

外键作用：
　　对子表：在主表中找不到对应的候选键，则不允许update/insert
　　对主表：如果子表关联了主表的相应字段，则主表的这个字段不能update/delete

级联删除：
　　主子表关联时不能随便删除，可以在子表添加外键约束时
　　CONSTRAINT 外键名 FOREIGN KEY (子表字段名) REFERENCES 主表(主表字段名)
　　　　　　　　　　　　　　　　　　　　　　　　　ON DELETE CASCADE;
　　-- 设置级联删除后，主表删除时会将子表的相应的也删除

SET NULL 方式：
　　外键约束加上：ON DELETE SET NULL;
　　-- 主表删除后，子表不会删除，但关联的相应字段会变成null

restrict方式：拒绝对主表删除操作
no action方式：在mysql中与restrict作用相同，拒绝对主表删除/更新操作

多表操作

创建两张表

CREATE TABLE tableA(
      id TINYINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
      name VARCHAR(25)
);
CREATE TABLE tableB(
      id TINYINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
      class VARCHAR(25),
      tA_id TINYINT
);

INSERT INTO tableA (id,name) VALUES (1,'ss'),
      (2,'yy'),
      (3,'zz');

INSERT INTO tableB (class,tA_id) VALUES ('class1',2),
      ('class2',1),
      ('class3',4);

笛卡尔积查询：
　　select * from tableA,tableB;
　　将两张表以行*行的形式全部显示出来

内连接：
　　select * from tableA,tableB where tableA.id=tableB.tA_id;
　　select * from tableA inner join tableB on tableA.id=tableB.tA_id;
　　两种方式结果相同，根据过滤条件查询出两表对应的部分，只返回两个表中联结字段相等的行

外连接：
　　select * from tableA left join tableB on tableA.id=tableB.tA_id;
　　select * from tableA right join tableB on tableA.id=tableB.tA_id;
　　-- left join：以左边的表为基准显示，左边表若与右边表没有对应的则显示null
　　-- right join：以右边表为基准显示，右边表若与左边表没有对应的则显示null
　　-- tableA left join tableB与tableB right join tableA结果相同

全外连接：
　　full join：oracle支持，mysql不支持
　　MySQL可以用union将左右连接结合模拟出full join
　　select * from tableA left join tableB on tableA.id=tableB.tA_id
　　UNION
　　select * from tableA right join tableB on tableA.id=tableB.tA_id;

mysql索引

索引：键，创建索引会消耗时间和磁盘空间，但会大大的缩减查询时间

索引分为普通索引，唯一索引（unique），全文索引（fulltext），空间索引（spatial），多列索引，主键索引（primary）

可以在创建表的时候直接设置索引

CREATE TABLE 表名(
　　字段名 数据类型 [完整性约束条件],
　　字段名 数据类型 [完整性约束条件],
　　[UNIQUE|FULLTEXT|SPATIAL] INDEX [索引名] (字段名[(长度)]) 
);

如创建一个普通索引

CREATE TABLE t1(
            id TINYINT NOT NULL,
            name VARCHAR(25),
            INDEX index_name (name) 
            );

在表已经存在的时候添加索引

　　CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX [索引名] ON 表名(字段名[(长度)]);

　　ALTER TABLE 表名 ADD INDEX [索引名] (字段名[(长度)]);

　　例：CREATE INDEX index_name ON t1(name);

删除索引

　　DROP INDEX 索引名 ON 表名

oracle和MySQL两者之间的不同

获取不同时间格式

Oracle中的to_char()来获取到时间的不同的形式

　　TO_CHAR (date, 'yyyy-mm-IW-dd-Day HH24:mi:ss' )

　　'yyyy-mm-IW-dd-Day HH24:mi:ss' 对应年-月-周-日-星期时-分-秒

Mysql中data_format()来获取到时间的不同的形式

　　DATE_FORMAT(date,'%Y-%m-%v-%d-%W %H:%M:%s')

　　'%Y-%m-%v-%d-%W %H:%M:%s' 对应年-月-周-日-星期时-分-秒

多列拼接

oracle中可以用 || 连接多列

　　select a.name || '拼接', a.name || a.age from name_table a

MySQL使用函数concat连接

　　select conact(a.name,a.age,'拼接') from name_table a

空值

oracle里空值只有null，筛选空值是：is null

MySQL里空值里有 null 和 '' ，筛选空值是：is null　和　=''

获取数据库前n条数据

oracle中使用rownum，rownum在oracle中是作为where的一部分使用

　　select * from table_A where rownum = 1　　（获取到第1行数据）

　　select * from table_A where rownum <= 10　　（获取到前10条数据）

rownum不支持查询后几条或者第n条（n>1）数据

　　rownum = 3或者rownum > 3这种是不支持的

MySQL中使用limit，limit在MySQL中不作为where的一部分使用

　　select * from table_A limit 3　　（查询前3条数据）

　　select * from table_A limit 2,4　　（从第2条（序号从0开始）开始，查4条数据）

posted @ 2019-10-31 22:11 saber゛ Views(200) Comments(0) Edit 收藏举报

刷新页面返回顶部

saber゛

数据表的操作

数据库表的操作

创建表

查看表

修改表的结构

表记录的增删改查

外键约束

多表操作

mysql索引

oracle和MySQL两者之间的不同

公告

创建表　　

修改表的结构　