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;

image

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密码  数据库名称 < 文件路径

备份所有

image

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_teachers
  2 (
  3  tid  number(5) not null,
  4  tname varchar2(11),
  5  title   char(2) 
  6  );
  7 
  8 CREATE TABLE T_SCHOOL
  9 (
 10  sid VARCHAR(10),
 11  hid VARCHAR(10),
 12  sname VARCHAR(10)
 13  );
 14 
 15 
 16 CREATE TABLE T_HEADER
 17 (
 18  hid VARCHAR(10),
 19  hname VARCHAR(10)
 20  );
 21 
 22 
 23 CREATE TABLE T_BANJI
 24 (
 25  bid VARCHAR(10),
 26  room VARCHAR(10)
 27  );
 28 
 29 
 30 CREATE TABLE T_STUDENT
 31 (
 32  sid VARCHAR(10),
 33  bid VARCHAR(10),
 34  sname VARCHAR(10)
 35  );
 36 
 37  
 38  CREATE TABLE emp
 39 (
 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 teachers
 59 (
 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 student
 82 (
 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 declare
 93     cursor tea is
 94     select * from teachers;
 95     begin
 96     for i in tea
 97     loop
 98     if i.sex='M' then
 99     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_count
112     (in_sex in Teachers.sex%type)
113     as
114     out_num number;
115     begin
116     if in_sex='M' then
117      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   declare
131   create or replace trigger t_teachers
132     before insert or update of tid, tname on teachers;
133     for each row
134     when(new.tname='Jack')
135     begin
136     if sex='M' then
137     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 casc
166 ade;
167 存储过程是流控制和SQL语句书写过程，经过便宜存储后在数据库服务器，调用即可。
168 触发器是一种特殊的存储过程
169 视图还有一个好处就是重命名
170 角色(集合了多种权限)是相关权限的命名集合，简化用户权限的管理。
171 create view v_stu(学号，姓名，性别，年龄)
172 as
173 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 int constraint 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 null references goods(goodsid) on delete cascade ,
28     customerid int not null foreign 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

 

posted @ 2018-07-29 13:44  小a玖拾柒  阅读(253)  评论(0)  编辑  收藏  举报