mysql自己怎么练习_MySQL的练习

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)