1.net stop mysql、net start mysql启动停止MySQL语句。
2.远程登陆mysql,如果不行可以试试关掉防火墙
mysql>mysql -u用户名 -p密码
mysql>mysql -h192.168.149.138 -uroot -pmysql -P3306(端口默认为3306)
mysql>\q 退出
3.show databases;查看mysql服务器管理多少数据库
create database db_name;创建数据库
show databases;查看mysql服务器管理多少数据库
use db_name;选择数据库
show tables;查看数据库下有多少表
select database();查看当前选择的数据库
select user();查看当前的用户,这条命令可以查到你的用户名
select @@datadir;查看mysql数据库目
select @@basedir:查看mysql安装位置
4.mysql帮助的用法:要知道DDL,DML的全拼
? contents;
5.创建表:
create table dep(id int not null auto_increment primary key,name varchar(25));
create table emp(
id int not null auto_increment primary key,
dep_id int,
name varchar(25),
age int(3) default 18,
sex enum('M','F') default 'M',
foreign key(dep_id) references dep(id)
);
其中fk_name
查看表结构:
desc dep;
查看创建表语句:
mysql> show create table dep \G
*************************** 1. row ***************************
Table: dep
Create Table: CREATE TABLE `dep` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
mysql> show create table emp \G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dep_id` int(11) DEFAULT NULL,
`name` varchar(25) DEFAULT NULL,
`age` int(3) DEFAULT '18',
`sex` enum('M','F') DEFAULT 'M',
PRIMARY KEY (`id`),
KEY `fk_emp_01` (`dep_id`),
FOREIGN KEY (dep_id) REFERENCES dep(id)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
对表结构的操作:ddL
create table stu(id int not null auto_increment primary key,name varchar(25),age int(3) default 22,sex enum('m','f'),birth date);
增加列
alter table stu add pro varchar(23) after age;
alter table stu add pro varchar(23) frist;
alter table stu add pro varchar(23);
修改列:
alter table stu modify pro int default 3;
对列重命名:
alter table stu change pro prof varchar(3) default 'art';
删除列:
alter table stu drop prof;
对表重命名:
alter table stu rename to student;
drop table stu;删除表
drop database db_name;删除数据库
DML:
insert into student(name,age,sex,birth) values('zhangsan',18,'m',now());
insert into student set name='lisi',age=22,sex='F',birth='1987-12-2';
update student set name='lisi',age=22,sex='F',birth='1987-12-2' where id=3;
insert into student values(null,'wangwu',24,'m','1983-01-02'),(null,'lida',26,'f','1984-03-04');
update student name='lisa' where id=4;
delete from student where id=4;
select * from student;
create table stu1 like student;创建stu1表
create table stu2 select * from student;
alter table stu2 modify id int not null auto_increment primary key;
insert into stu2(name,age,sex,birth) select name,age,sex,birth from stu2;