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时一行隐藏