MySQL的练习

2019-07-24 08:59:27来源:博客园 阅读 ()

新老客户大回馈,云服务器低至5折

mysql登录:
方法1:
使用Command Line Client登录,缺点:不显示报错信息

方法2:
使用cmd登录(cmd的常用:查看ip地址:ipcongfi-----定时关机:shutdown -s -t 10800,3小时=180分钟=180*60秒=10800秒)
1.切换到mysql\bin目录下
2.mysql -u root -p

方法3:带ip地址的登录,可以是本机ip,也可以是远程mysql服务器的ip地址
mysql -h 192.168.1.5 -u root -p

方法4:
1.查看mysql当前端口号:show global variables like 'port';
2.停止服务,更改端口号,my.ini,启动服务器
3.mysql -u root -P 3307 -p
4.查看mysql当前端口号:show global variables like 'port';

方法5:
1.解决1130报错
2.使用Navicat登录

数据的命令:
创建库:
create database 库名称;
create database test1;
create database if not exists test1;
修改库:
自行学习
删除库:
drop database 库名称;

创建表:
create table 表名称 (
字段名1 字段类型 字段长度 [字段约束],
字段名2 字段类型 字段长度 [字段约束],
字段名3 字段类型 字段长度 [字段约束]
);

create table linux (
cd datetime not null,
ls varchar(10) default "yes",
pwd int(4)
);
insert into linux values("2019-5-31","today","4444");
insert into linux values("2019-5-31","","4444");
insert into linux values("2019-5-31",default,"4444");

插入数据:
insert into 表名称 values(v1,v2,v3);
insert into linux values("2019-5-31","today","4444");
insert into linux values("2019-5-31 16:36:50","today","4444");
insert into linux values("2019-5-32","today","4444");
insert into linux values("2019-5-31","today","1234567890");
insert into linux values("2019-5-31","today","12345678901");

insert into linux(cd,ls) values("2019-5-31","today");
insert into linux values("2019-5-31","today",default);
insert into linux values("2019-5-31","today","");
insert into linux values("2019-5-31","today",null);


修改表字段类型:
ALTER TABLE 表名 MODIFY 字段名 数据类型;
alter table linux modify pwd int;
alter table linux modify cd date;

 

 

修改表字段排序:
ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST;
ALTER TABLE 表名 MODIFY 字段名1 数据类型 AFTER 字段名2;
ALTER TABLE linux MODIFY pwd int(11) AFTER cd;
ALTER TABLE linux MODIFY ls varchar(10) FIRST;

添加字段:
ALTER TABLE 表名 ADD 新字段名 数据类型;
ALTER TABLE 表名 ADD 新字段名 数据类型 [FIRST|AFTER 已存在字段名];
ALTER TABLE linux ADD mkdir float;
ALTER TABLE linux ADD rm double after cd;

删除字段:
alter tabel 表名 drop 原有字段名;
alter tabel linux drop column rm;

 

数据库字段的约束表达
not null 非空约束
default 默认约束
primary key 主键约束
auto_increment 自增长

create table linux (
pwd int(4) primary key auto_increment,
cd datetime not null,
ls varchar(10) default "yes"
);

insert into linux values(1,"2019-6-3","123");
insert into linux values(default,"2019-6-3","123");

以下是练习 

create table employee (
empid varchar(12) primary key comment "员工编号",
name varchar(12) not null comment "员工姓名",
sex int comment "性别",
title varchar(8) comment "职称",
birthday date comment "生日",
depid varchar(10) comment "部门编号"
);

 

create table department (
depid varchar(12) primary key comment "部门编号",
depname varchar(8) comment "部门名称",
info varchar(8) comment "部门简介",
);

create table salary(
empid varchar(12),
basesalary int comment "基本工资",
stationsalary int comment "岗位工资"
);

insert into employee values(1001,"张三","1","高级工程师","1975-1-1",111);
insert into employee values(1002,"李四","0","助理工程师","1985-1-1",111);
insert into employee values(1003,"王五","1","工程师","1978-1-1",222);
insert into employee values(1004,"赵六","1","工程师","1979-1-1",222);

insert into department values(111,"生产部","1");
insert into department values(222,"销售部","2");
insert into department values(333,"人事部","3");

insert into salary values(1001,2200,1100);
insert into salary values(1002,1200,200);
insert into salary values(1003,1900,700);
insert into salary values(1004,1950,700);

添加外键:
alter table salary add constraint FK_ID foreign key(empid) REFERENCES emoloyee (empid);

alter table employee add constraint FK_DEPID foreign key(empid) REFERENCES department(empid);
更改 表 员工表 添加 约束 约束名称employee 外键(员工表的empid) 关联 部门表(部门表的empid)

 


原文链接:https://www.cnblogs.com/KSH1/p/11086949.html
如有疑问请与原作者联系

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:使用Redis实现用户积分排行榜

下一篇:PHP操作redis实现的分页列表,新增,删除功能封装类与用法示例