1炉媒、整理今天講解的數(shù)據(jù)庫(kù)命令
2、整理今天講解的數(shù)據(jù)庫(kù)命令相關(guān)的單詞并解釋
3铺韧、創(chuàng)建數(shù)據(jù)庫(kù)表
1、一對(duì)一? ? 外鍵設(shè)置唯一約束
2灶搜、一對(duì)多? ? 外鍵
3祟蚀、多對(duì)多? ? 中間表
要求
1、sql語(yǔ)句創(chuàng)建
2割卖、每個(gè)數(shù)據(jù)庫(kù)表insert至少5條語(yǔ)句
3前酿、基本查詢
4、update測(cè)試
5鹏溯、delete測(cè)試
罢维、、丙挽、肺孵、、颜阐、平窘、、凳怨、瑰艘、是鬼、、紫新、均蜜、、芒率、囤耳、、偶芍、充择、、匪蟀、聪铺、、萄窜、、撒桨、查刻、、凤类、穗泵、、谜疤、佃延、、夷磕、
1履肃、mysql是管理數(shù)據(jù)庫(kù)的軟件
2、安裝
sudo apt install mysql-server
3坐桩、默認(rèn)是本地連接尺棋，如果需要，可以開啟遠(yuǎn)程連接
登陸
mysql -h127.0.0.1 -uroot -p
運(yùn)行命令
use mysql;
update user set Host="%" where User="root";
flush privileges;
重啟服務(wù)
service mysql restart
測(cè)試登陸
mysql -h192.168.0.134 -uroot -p
4绵跷、客戶端
默認(rèn)的客戶端是黑窗口膘螟，不方便。一般使用第三方的碾局，比如navicat荆残，有l(wèi)inux版的和windows版的
5、命令
1净当、連接數(shù)據(jù)庫(kù)
mysql -h ip地址 -P 端口號(hào) -u 用戶名 -p 密碼
2内斯、顯示所有數(shù)據(jù)庫(kù)
show databases;
3、創(chuàng)建數(shù)據(jù)庫(kù)
create database 數(shù)據(jù)庫(kù)名字 default charset=utf8;
4、使用數(shù)據(jù)庫(kù)
use 數(shù)據(jù)庫(kù)名字;
5嘿期、刪除數(shù)據(jù)庫(kù)
drop 數(shù)據(jù)庫(kù)名字;
6品擎、備份和恢復(fù)數(shù)據(jù)庫(kù)
mysqldump -h 192.168.0.138 -u root -p mydb > ./mydb.sql
mysql -h 192.168.0.138 -uroot -p mydb < ./mydb.sql
7、查詢數(shù)據(jù)庫(kù)下所有的表
use 數(shù)據(jù)庫(kù)名;
show tables;
8备徐、創(chuàng)建表
create table 表名(
列名 類型,
列名 類型,
列名 類型
);
9萄传、常用的類型
數(shù)字 int,float,decimal
字符串 char,varchar,longtext
日期 date,datetime
10、刪除表
drop table 表名
11蜜猾、描述表的信息
desc 表名
12秀菱、顯示表的創(chuàng)建sql語(yǔ)句
show create table 表名
13、約束
1蹭睡、主鍵約束
2衍菱、非空約束
3、唯一約束
4肩豁、默認(rèn)約束
5脊串、外鍵約束
、清钥、琼锋、、祟昭、缕坎、、篡悟、谜叹、、搬葬、荷腊、、踩萎、停局、、香府、董栽、、企孩、锭碳、、勿璃、擒抛、推汽、、歧沪、歹撒、、诊胞、暖夭、、撵孤、迈着、
/*1對(duì)1*/
drop table if exists husband;
drop table if exists wife;
create table husband(
id int primary key auto_increment,
name varchar(100) not null,
wid int,
foreign key(wid) references wife(id) on delete cascade
);
create table wife(
id int primary key auto_increment,
name varchar(100) not null
);
insert into wife(id,name) values(1,'小紅');
insert into wife(id,name) values(2,'小蘭');
insert into wife(id,name) values(3,'小亭');
insert into wife(id,name) values(4,'小晶');
insert into wife(id,name) values(5,'小美');
select * from wife;
update wife set name = '小妹' where id = 1;
insert into husband(id,name,wid) values(1,'小白',1);
insert into husband(id,name,wid) values(2,'小李',2);
insert into husband(id,name,wid) values(3,'小趙',3);
insert into husband(id,name,wid) values(4,'小錢',4);
insert into husband(id,name,wid) values(5,'小孫',5);
select * from husband;
delete from husband where id = 5;
/*1對(duì)m*/
drop table if exists emp;
drop table if exists dept;
create table emp(
id int primary key auto_increment,
name varchar(100) not null,
did int,
foreign key(did) references dept(id) on delete cascade
);
select * from emp;
insert into emp(id,name,did) values(1,'小白',3);
insert into emp(id,name,did) values(2,'小李',4);
insert into emp(id,name,did) values(3,'小趙',4);
insert into emp(id,name,did) values(4,'小錢',2);
insert into emp(id,name,did) values(5,'小孫',5);
create table dept(
id int primary key auto_increment,
name varchar(100) not null
);
select * from dept;
insert into dept(id,name) values(1,'市場(chǎng)部');
insert into dept(id,name) values(2,'人力部');
insert into dept(id,name) values(3,'采購(gòu)部');
insert into dept(id,name) values(4,'研發(fā)部');
insert into dept(id,name) values(5,'營(yíng)銷部');
/*m對(duì)n*/
drop table if exists student;
drop table if exists subject;
create table student(
id int primary key auto_increment,
name varchar(100) not null
);
insert into student(id,name) values(1,'小明');
insert into student(id,name) values(2,'小王');
select * from student;
create table subject(
id int primary key auto_increment,
name varchar(100) not null
);
insert into subject(id,name) values(1,'語(yǔ)文');
insert into subject(id,name) values(2,'數(shù)學(xué)');
insert into subject(id,name) values(3,'英語(yǔ)');
select * from subject;
create table middle(
id int primary key auto_increment,
stuid int not null,
subid int not null
);
insert into middle(id,stuid,subid) values(1,1,1);
insert into middle(id,stuid,subid) values(2,1,2);
insert into middle(id,stuid,subid) values(3,1,3);
insert into middle(id,stuid,subid) values(4,2,1);
insert into middle(id,stuid,subid) values(5,2,2);
insert into middle(id,stuid,subid) values(5,2,3);
select * from middle;
