1.以ATM引出DBMS
2、MySQL
-服务端
-客户端
3、通信交流
-授权
-SQL语句
-数据库
create database db1 default charset=utf8;
drop database db1;
-数据表
create table tb2;
create table tb1(
id int not null auto_increment primary key,
name char(10),
department_id int,
constraint fk_user foreign key(department_id) reference tb2(tid)
)engine=innodb default charset=utf-8;
补充外键:

什么时候用主键, 主键的用处?
一张表只能有一个主键
一个主键可以是多个列
CREATE TABLE t5 (
nid int(11) NOT NULL AUTO_INCREMENT,
pid int(11) not NULL,
name char(11),
primary key(nid,pid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



create table t6(
id int auto_increment primary key,
name char(10),
id1 int,
id2 int,
CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t1(nid,pid)
)engine=innodb default charset=utf8;
-数据行
insert into tb1(name,age) values('alex',19);
delete from tb1;
truncate talbe tb1;
delete from tb1 where id >10;
update tb1 set name = 'root' id >10;
select * from tb1;
select id,name from tb1;
mysql> desc t5;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| nid   | int(11) | NO   | PRI | NULL    | auto_increment |
| pid   | int(11) | NO   | PRI | NULL    |                |
| num   | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
mysql> alter table t6 AUTO_INCREMENT=2;
mysql> show create table t6 \G;
*************************** 1. row *********************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
mysql> select * from t6;
+----+------+
| id | num  |
+----+------+
| 20 |   15 |
+----+------+
对于自增:
        desc t6;
        show create table t6 \G;
         alter table t6 AUTO_INCREMENT=2;
Mysql :自增步长
    基于会话级别:
查看全局变量
mysql> show session variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
设置会话步长
mysql> set session auto_increment_increment=2;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t6(num) values(66);
Query OK, 1 row affected (0.44 sec)

mysql> select * from t6;
+----+------+
| id | num  |
+----+------+
| 20 |   15 |
| 21 |   88 |
| 23 |   66 |
+----+------+
起始值
mysql> set session auto_increment_offset=2;


基于全局级别步长:
mysql> set global auto_increment_increment=200;
mysql> show global variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 200   |
| auto_increment_offset    | 1     |
+--------------------------+-------+
mysql> show session variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 200   |
| auto_increment_offset    | 1     |
+--------------------------+-------+
起始值
mysql> set global auto_increment_offset=22;
唯一索引:约束不能重复(可以为空),加速查找;
主键也不能重复,主键不能为空,加速查找;

create table t1(
id int ....,
num int,
xxx int
unique uq1 (num,xxx)
)
一对一
create table userinfo1(
        id int auto_increment primary key,
        name char(10),
        gender char(10),
        email varchar(64)
)engine=innodb default charset=utf8;

create table admin(
    id int not null auto_increment primary key,
    username varchar(64) not null,
    password VARCHAR(64) not null,
    user_id int not null,
    unique uq_u1 (user_id),
    CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)
)engine=innodb default charset=utf8;

多对多
create table userinfo2(
        id int auto_increment primary key,
        name char(10),
        gender char(10),
        email varchar(64)
)engine=innodb default charset=utf8;

create table host(
    id int auto_increment primary key,
    hostname char(64)
)engine=innodb default charset=utf8;


create table user2host(
    id int auto_increment primary key,
    userid int not null,
    hostid int not null,
    unique uq_user_host (userid,hostid),
    CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
    CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
)engine=innodb default charset=utf8;
MySQL语句数据行操作的补充
增:
    insert into tb11(name,age) values('alex',12);
    insert into tb11(name,age) values('tim',12),('root',11);
    insert into tb12(name,age) select name,age from tb11;
create table tb12(
            id int auto_increment primary key,
            name varchar(32),
            age int
        )engine=innodb default charset=utf8;
删:
    delete from tb12;
    delete from tb12 where id=2;
    delete from tb12 where id=2 and name ='tim',
改:
    update tb12 set name='tim' where id >12 and name = 'xx'
查:
  select * from tb12;
  select id,name from tb12;
  select id,name from tb12 where id >10 or name ='xxx';
  select name,age  as cname from tb12;
  select name,age,111 from tb12;
  select * from tb12 where id not in (1,4,5);
  select * from tb12 where id between 1 and 201;
  select * from tb12 where id in (select id from tb11)

通配符:
    select * from tb12 where name like '%a'
    select * from tb12 where name like 'a_'

分页:
    select * from tb12 limit 2;
    select * from tb12 limit 1,3;
    select * from tb12 limit 2 offset 20;
排序:
    select * from tb12 order by id desc;  大到小
    select * from tb12 order by id asc;     小到大
    select * from tb12 order by id asc limit 2;
    select * from tb12 order by age desc,id desc;
# select mac(id),part_id from uerinfo5 group by part_id;
# count
# max
# min
# sum
# avg
# 对于聚合函数的结果进行二次筛选,必须用having
# select count(id),part_id from userinfor5  group by part_id where haveing count(id) >1;
# select count(id),part_id from userinfo5 where id >0 group by part_id haveing count(id) >1;
连表
select * from userinfo5 where userinfo5.part_id = department5.id;
select * from userinfo5 left join department5 on userinfo.part_id = department5.id;
userinfo5 左边全部显示

select * from userinfo5 right join department5 on userinfo.part_id = department5.id;
department5右边全部显示
select * from userinfo5 innder join department5 on userinfo.part_id = department5.id;
将出现null时一行隐藏

 

posted on 2019-02-24 20:35  Tim-code  阅读(204)  评论(0编辑  收藏  举报