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）

创建表[增删改查]

CREATE TABLE female_teachers
(
 tid  number(5) not null,
 tname varchar2(11),
 title   char(2) 
 );

CREATE TABLE T_SCHOOL
(
 sid VARCHAR(10),
 hid VARCHAR(10),
 sname VARCHAR(10)
 );


CREATE TABLE T_HEADER
(
 hid VARCHAR(10),
 hname VARCHAR(10)
 );


CREATE TABLE T_BANJI
(
 bid VARCHAR(10),
 room VARCHAR(10)
 );


CREATE TABLE T_STUDENT
(
 sid VARCHAR(10),
 bid VARCHAR(10),
 sname VARCHAR(10)
 );

 
 CREATE TABLE emp
(
 empno  number(5) not null,
  ename      VARCHAR(10),
   job        VARCHAR(9),
   hiredate     DATE,
    sal  number(5)
 );


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



 CREATE TABLE teachers
(
 tid  number(5) not null,
 tname varchar2(11),
 sex   char(2),
 score  number(5)
 );








 insert into teachers(tid,tname,sex,score) values(2,'wer','M',101);
 insert into teachers(tid,tname,sex,score) values(3,'qw','W',103);
 insert into teachers(tid,tname,sex,score) values(4,'yuw','W',104);
 insert into teachers(tid,tname,sex,score) values(5,'table','M',105);
 insert into teachers(tid,tname,sex,score) values(6,'jkh','W',203);
 insert into teachers(tid,tname,sex,score) values(7,'tr','W',204);
 
 
create table student
(
sno char(7) primary key,
sname varchar2(9),
sex char(2),
age int,
dept varchar2(8)
)



declare
    cursor tea is
    select * from teachers;
    begin
    for i in tea
    loop
    if i.sex='M' then
    insert into male_teachers values(i.tid, i.tname,i.sex) ;
    else
   insert into female_teachers values(i.tid,i.tname,i.sex);
   end if;
   end loop;
   end ;
   /



SQL>
    set serveroutput on;
    create or replace procedure tea_count
    (in_sex in Teachers.sex%type)
    as
    out_num number;
    begin
    if in_sex='M' then
     select count(sex) into out_num from teachers where sex='M';
     dbms_output.put_line('Number of Male: ' || out_num);
    else
    select count(sex) into out_num from teachers where sex='W';
    dbms_output.put_line('Number of FeMale: ' || out_num);
   end if;
   end tea_count;
   /
 
  create index in_teachers on teachers(tid);      select tid from teachers;
  create view v_teachers as select tid, tname, score from teachers;
  select * from v_teachers;

  declare
  create or replace trigger t_teachers
    before insert or update of tid, tname on teachers;
    for each row
    when(new.tname='Jack')
    begin
    if sex='M' then
    insert into male_teachers values(tid,tname, sex);
    else
    insert into female_teachers values(tid, tname, sex);
   end if;
   end t_teachers;
   /
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
//外键的设置需要父表中设置主键或者unique约束，父表中没有的数据子表不能插入数据


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

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

创建表[含外键]

create table customers (
    customersId int identity(1,1)  primary key ,
    cname varchar(8) not null ,
    address varchar(50) ,
    city varchar(10) ,
    tel varchar(10) unique ,
    company varchar(50) ,
    birthday datetime , 
    type tinyint default 1 
);



create table goods (
    goodsid int constraint c1 primary key , 
    goodsname varchar(20) not null , 
    price money , 
    description varchar(200) ,
    storage  int ,
    provider varchar(50) ,
    status tinyint default(0) 
);


create table orders (
    orderid int identity(1,1) constraint c2 primary key ,
    goodsid int not null references goods(goodsid) on delete cascade ,
    customerid int not null foreign key(customerid)
       references customers (customersId) on delete no action ,
    quantity int not null constraint c3 check(quantity >0) ,
    ordersum money not null ,
    orderdate datetime default(getdate())

) ;