mysql 排空函数_Mysql学习---基础操作学习2

基本数据类型

Mysql基本数据类型：二进制，数值[整数，小数]、字符串[定长，变长]、 二进制数据、时间和枚举集合

bit[(M)]

二进制位(101001)，m表示二进制位的长度(1-64)，默认m＝1

tinyint[(m)] [unsigned] [zerofill]

小整数，数据类型用于保存一些范围的整数数值范围：

有符号：-128 ～ 127.

无符号：0～ 255

特别的： MySQL中无布尔值，使用tinyint(1)构造。

int[(m)][unsigned][zerofill] num int unsigned

整数，数据类型用于保存一些范围的整数数值范围：

【默认】有符号： -2147483648 ～ 2147483647

无符号： -4294967295~4294967295

特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002

bigint[(m)][unsigned][zerofill]

大整数，数据类型用于保存一些范围的整数数值范围：

数据库操作

创建数据库

REATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

use 数据库名称;

创建表： 主键，null，默认值, 引擎，默认字符集

[单个主键]

create table hhh(

id int not null auto_increment primary key, # 设置主键，自增长序列，自增长一个表只能有一个，自增默认不为空

name varchar(12) null, # 可以为空

sex varchar(2) default 'M' # 设置默认值为M

)ENGINE=INNODB DEFAULT CHARSET=utf8 # 设置引擎为INNODB，字符为utf8[utf-8报错]

[多列作为主键]

create table hhh(

id int not null auto_increment, # 是主键，所以不能为空，自增长序列，且一个表只能有一个

name varchar(12) not null, # 因为设置了主键，所以不能为空

sex varchar(2) default 'M' # 设置默认值为M

primary key(id, name) # 设置主键,2列作为一个索引

)ENGINE=INNODB DEFAULT CHARSET=utf8

# 一般用法：id int auto_increment primary key

修改表

添加列：alter table 表名 add 列名 类型

删除列：alter table 表名 drop column 列名

修改列：

alter table 表名 modify column 列名 类型; -- 类型

alter table 表名 change 原列名 新列名 类型; -- 列名，类型

添加主键：

alter table 表名 add primary key(列名);

删除主键：

alter table 表名 drop primary key;

alter table 表名 modify 列名 int, drop primary key;

添加外键：

alter table 从表 add constraint 外键名称(形如：FK_从表_主表) foreign key 从表(外键字段) references 主表(主键字段);

删除外键：alter table 表名 drop foreign key 外键名称

修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

SQL语句之基本增删改

增：

insert into 表 (列名,列名...) values (值,值,值...)

insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)

insert into 表 (列名,列名...) select (列名,列名...) from 表 # 查询一个插入一个

删：

delete from 表

delete from 表 where id＝1 and name＝'hhh';

改：

update 表 set name ＝ 'alex' where id>1

查：

select * from 表

select * from 表 where id > 1

select nid,name,gender as gg from 表 where id > 1

SQL语句之条件、分页以及排序

条件查询： in not int BETWEEN and < > != =

select * from user where id > 1 and name = 'hhh'

select * from t_user where 1 < id < 3

select * from t_user where id BETWEEN 1 and 3

select * from user where name in ('hhh', 'xupt', 'world')

select * from user where name not in ('hhh', 'xupt', 'world')

select * from user where id in (select id from teacher)

通配符: 2种

select * from user where name like 'hh_' #--匹配一个字符

select * from user where name like '%hh%' #--匹配多个字符

分页: 3种

select * from user limit 5 #查询5个数据

select * from user limit 5 offset 2 #从第二行开始往后取出5个数字[不包含第二个]

select * from user limit 2,5 #从第二个数字开始往后取出5个数[不包含第二个]

升序/降序

select id from user order by id desc #利用order by实现 降序

select id from user order by id asc #利用order by实现 升序

select * from user order by id desc, age asc #先利用id实现降序，如果内容相同则利用age实现升序

select id, name from user GROUP BY id #group by分页的时候关键字必须是前面查询的字段

select * from user GROUP BY id DESC

分组: group by 必须在where之后，order by之前；使用having配合实现where语句功能

select id from user group by id

select * from t_user GROUP BY sex; # 用*来查找全部，5.6默认取出第一个值 5.7之后会报错

select * from t_user;

select id, username from t_user GROUP BY sex;

select id, username from t_user GROUP BY ;

select num from 表 group by num having max(id) > 10;

select id, count(sex) from user group by sex having count(sex) > 10 # 使用having来进行group之后的分组

聚合函数: sum(), min(), max(), count()等 对聚合函数进行分组筛选的时候用having

select max(id), min(id) from user

联合： union去重排序 union all 不排序不去重

select * from user union select * from teacher

select * from user union all select * from teacher

去重复：distinct, groupBy

select student_id from score where num < 60 group by student_id;

select DISTINCT student_id FROM score where num < 60;

多表联查: 4中select A,B inner join, left join, right join

select * from user,hhh; # 笛卡尔 ret = A * B user表中的每条数据对应hhh里面的所有数据

select * from user,hhh where user.id = hhh.id; # 连表操作，选出需要的结果

select a.id, a.name, b.salary from user a, hhh b where a.id = b.id # AB交集，无重合结果则不显示；有则显示id相同的部分

select a.id, a.name, b.salary from user a inner join hhh b where a.id = b.id # AB取全部列，无对应则不显示, 永远不会出现Null

# Inner Join 实际上是对Left Join的排空操作，如果有Null，Inner Join则自动清除这条记录

select a.id, a.name, b.salary from user a left join hhh b where a.id = b.id # A取全部列，如果B中无对应关系，则值为null

select a.id, a.name, b.salary from user a right join hhh b where a.id = b.id # B取全部列，如果A中无对应关系，则值为null

细节学习--自增：

自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增

show session variables like 'auto_inc%';

set session auto_increment_increment=2;

set session auto_increment_offset=10;

show global variables like 'auto_inc%';

set global auto_increment_increment=2;

set global auto_increment_offset=10;

自增注意：

1、对于自增列，必须是数字而且必须是索引(含主键)

2、对于自增可以设置步长和起始值

3、一个表只能有一个主键，且主键不能重复，不能为null,一般自增列

4、使用delete清除数据后，自增长还是从原来的位置开始自增；

使用truncate清除数据后，自增长恢复到0，从1开始计算

细节学习--唯一索引

可以为null，一张表可以有多个唯一列

是一种约束，但作为索引加快了查找速度

细节学习--主键

主键唯一且不能重复，

一张表只有一个主键且不能为null(可以多列组成主键)一般设自增为主键

主键是特殊的索引[1.是一种约束  2.主键是特殊的索引，加快查找速度]

细节学习--外键：一个特殊的索引[加快查找速度]，只能是指定内容

1.创建时候添加

2. alter修改添加外键

数据库备份：在外部执行，非Mysql终端内

导出现有数据库数据：

mysqldump -u用户名 -p密码 数据库名称 >导出文件路径 # 结构+数据

mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径 # 结构

导入现有数据库数据：

mysqldump -uroot -p密码 数据库名称 < 文件路径

备份所有

Mysql的分页操作

原理：根据上次查找的最后一个id值作为下一个where语句的条件，然后进行limit查找

select * from student LIMIT 3, 3;

select * from student where sid > 10 LIMIT 3; # 跳过前面10条数据，分页取出3条

例如：

select * from tb1 where nid < (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc limit 每页数据 *【页码 - 当前页】) A order by A.nid asc limit 1) order by nid desc limit 10;

小Demo

创建表的时候创建外键

create table userinfo(

id int not null auto_increment primary key,

name varchar(12),

dept_id int,

FOREIGN key userinfo(dept_id) REFERENCES dept(id)

)ENGINE=INNODB default charset=utf8

create table dept(

id int not null auto_increment primary key,

name varchar(12)

)ENGINE=INNODB default charset=utf8

INSERT INTO userinfo(name, dept_id) VALUES('hhh', 1)

INSERT INTO userinfo(name, dept_id) VALUES('ftl', 2)

INSERT INTO userinfo(name, dept_id) VALUES('xupt', 2)

# INSERT INTO userinfo(name, dept_id) VALUE ('peking', 100) # (`test`.`userinfo`, CONSTRAINT `FK_u_d` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))

insert INTO dept(name) values('工具部')

insert INTO dept(name) values('研发部')

修改表添加外键

#添加外键

ALTER table userinfo add CONSTRAINT FK_u_d FOREIGN key userinfo(dept_id) REFERENCES dept(id)

创建表[增删改查]

1 CREATE TABLE female_teachers2 (3 tid number(5) not null,4 tname varchar2(11),5 title char(2)6 );7

8 CREATE TABLE T_SCHOOL9 (10 sid VARCHAR(10),11 hid VARCHAR(10),12 sname VARCHAR(10)13 );14

15

16 CREATE TABLE T_HEADER17 (18 hid VARCHAR(10),19 hname VARCHAR(10)20 );21

22

23 CREATE TABLE T_BANJI24 (25 bid VARCHAR(10),26 room VARCHAR(10)27 );28

29

30 CREATE TABLE T_STUDENT31 (32 sid VARCHAR(10),33 bid VARCHAR(10),34 sname VARCHAR(10)35 );36

37

38 CREATE TABLE emp39 (40 empno number(5) not null,41 ename VARCHAR(10),42 job VARCHAR(9),43 hiredate DATE,44 sal number(5)45 );46

47

48 INSERT INTO emp (empno,ename,job,hiredate,sal) VALUES (6060,'李兴华','经理',sysdate,2000.30) ;49 INSERT INTO emp (empno,ename,job,hiredate,sal) VALUES (7369,'董鸣楠','销售',sysdate,1500.90) ;50 INSERT INTO emp (empno,ename,job,hiredate,sal) VALUES (8964,'李祺','分析员',sysdate,3000) ;51 INSERT INTO emp (empno,ename,job,hiredate,sal) VALUES (7698,'张惠','销售',sysdate,800) ;52 INSERT INTO emp (empno,ename,job,hiredate,sal) VALUES (7782,'杨军','分析员',sysdate,2500) ;53 INSERT INTO emp (empno,ename,job,hiredate,sal) VALUES (7762,'刘明','销售',sysdate,1000) ;54 INSERT INTO emp (empno,ename,job,hiredate,sal) VALUES (7839,'王月','经理',sysdate,2500) ;55

56

57

58 CREATE TABLE teachers59 (60 tid number(5) not null,61 tname varchar2(11),62 sex char(2),63 score number(5)64 );65

66

67

68

69

70

71

72

73 insert into teachers(tid,tname,sex,score) values(2,'wer','M',101);74 insert into teachers(tid,tname,sex,score) values(3,'qw','W',103);75 insert into teachers(tid,tname,sex,score) values(4,'yuw','W',104);76 insert into teachers(tid,tname,sex,score) values(5,'table','M',105);77 insert into teachers(tid,tname,sex,score) values(6,'jkh','W',203);78 insert into teachers(tid,tname,sex,score) values(7,'tr','W',204);79

80

81 create table student82 (83 sno char(7) primary key,84 sname varchar2(9),85 sex char(2),86 age int,87 dept varchar2(8)88 )89

90

91

92 declare93 cursor tea is94 select *from teachers;95 begin96 fori in tea97 loop98 if i.sex='M'then99 insert into male_teachers values(i.tid, i.tname,i.sex) ;100 else

101 insert into female_teachers values(i.tid,i.tname,i.sex);102 end if;103 end loop;104 end ;105 /

106

107

108

109 SQL>

110 set serveroutput on;111 create or replace procedure tea_count112 (in_sex in Teachers.sex%type)113 as114 out_num number;115 begin116 if in_sex='M'then117 select count(sex) into out_num from teachers where sex='M';118 dbms_output.put_line('Number of Male: ' ||out_num);119 else

120 select count(sex) into out_num from teachers where sex='W';121 dbms_output.put_line('Number of FeMale: ' ||out_num);122 end if;123 end tea_count;124 /

125

126 create index in_teachers on teachers(tid); select tid from teachers;127 create view v_teachers as select tid, tname, score from teachers;128 select *from v_teachers;129

130 declare131 create or replace trigger t_teachers132 before insert or update of tid, tname on teachers;133 foreach row134 when(new.tname='Jack')135 begin136 if sex='M'then137 insert into male_teachers values(tid,tname, sex);138 else

139 insert into female_teachers values(tid, tname, sex);140 end if;141 end t_teachers;142 /

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162 //外键的设置需要父表中设置主键或者unique约束，父表中没有的数据子表不能插入数据

163

164

165 alter table null_emp add constraint nullemp_fk foreign key(sno) references dep on delete casc166 ade;167 存储过程是流控制和SQL语句书写过程，经过便宜存储后在数据库服务器，调用即可。168 触发器是一种特殊的存储过程169 视图还有一个好处就是重命名170 角色(集合了多种权限)是相关权限的命名集合，简化用户权限的管理。171 create view v_stu(学号，姓名，性别，年龄)172 as173 select sno,sname,sex,age from sys.student;174

175 create public synonym stu for student; 创建同义词

View Code

创建表[含外键]

1 create table customers (2 customersId int identity(1,1) primary key ,3 cname varchar(8) not null,4 address varchar(50) ,5 city varchar(10) ,6 tel varchar(10) unique ,7 company varchar(50) ,8 birthday datetime ,9 type tinyint default 1

10 );11

12

13

14 create table goods (15 goodsid intconstraint c1 primary key ,16 goodsname varchar(20) not null,17 price money ,18 description varchar(200) ,19 storage int,20 provider varchar(50) ,21 status tinyint default(0)22 );23

24

25 create table orders (26 orderid int identity(1,1) constraint c2 primary key ,27 goodsid int not nullreferences goods(goodsid) on delete cascade ,28 customerid int not nullforeign key(customerid)29 references customers (customersId) on delete no action ,30 quantity int not null constraint c3 check(quantity >0) ,31 ordersum money not null,32 orderdate datetime default(getdate())33

34 ) ;

View Code