mysql使用语句_MySQL的使用语句

show databases; 查询所有的库

drop database mldn; 删除库

create database mldn;建立库

use mldn;选择用库

show tables;查询所有的表

desc 表名称;查看表结构

改变结构

alter table user change password password varchar(40);

alter table 表名称 change 表字段(Filed) 表字段(Filed) 类型(type);

drop table member ; 建立表

create table member(//建立表结构

userid varchar(32) notnull ,

password varchar(32),

realname varchar(200),

registdate date default'1990-01-01',

primary key (userid)

);

desc member(表名称);//查询表结构

insert into member values ('1','1','刘三','2012-12-12'); //插入数据

insert into member values ('2','2','何二','2013-05-10');

insert into member values ('3','3','孟大','2013-10-10');

select* from member; //查询表

drop table member;//删除表

drop table dept ;

desc dept ;

create table dept(

deptno int(2) notnull,

dname varchar(14),

loc varchar(14),

primary key(deptno)

);

insert into dept values (10,'ACCOUNTING','NEW YORK');

insert into dept values (20,'RESEARCH','DALLAS');

insert into dept values (30,'SALES','CHICAGO');

drop table emp purge;

create table emp(

empno intnotnull,

ename varchar(10),

job varchar(9),

mgr int(4),

hiredate date default'1900-01-01',

sal Double(7,2),

comm Double(7,2),

deptno int(2),

primary key (empno)

);

增加：INSERT INTO 表名称 VALUES (值 , 值 , ...) ;

insert into emp values(7369,'史密斯','组长', 7902, '2008-08-09',5000.00,3000.00, 10);

insert into emp values(7499, 'ALLEN','SALESMAN', 7698, '1981-01-02', 1600.00, 300.00, 30);

insert into emp values(7521, 'WARD','SALESMAN', 7698 ,'1981-02-22',1250.00, 500.00, 30);

修改：UPDATE 表名称 SET 字段=值 , 字段=值 , ... [WHERE 更新条件(s)] ;

UPDATE member SET password='3',realname='刘三',registdate='2011-11-11' WHERE userid='3';

C4CA4238A0B923820DCC509A6F75849B

C81E728D9D4C2F636F067F89CC14862C

ECCBC87E4B5CE2FE28308FD9F2A7BAF3

C81E728D9D4C2F636F067F89CC14862C

update member SET password='C4CA4238A0B923820DCC509A6F75849B' where userid='1';

update member SET password='C81E728D9D4C2F636F067F89CC14862C' where userid='2';

update member SET password='ECCBC87E4B5CE2FE28308FD9F2A7BAF3' where userid='3';

删除：DELETE FROM 表名称 [WHERE 删除条件(s)] ;

DELETE FROM member WHERE userid='3';

MySQL多人开发时，一般会建立一个单独的用户，供所有人一起使用，建立用户的命令：

CREATE USER 用户名 IDENTIFIED BY ‘密码

建立后还需要分配权限

GRANT ALL ON 库名.表名 TO 用户名@’%’

导入导出数据库命令

导出：

mysqldump –u用户名 –p密码 库名>位置

mysqldump-uroot -mysqladmin hr >d:test.sql

导入

mysqldump –u用户名 –p密码 库名

mysqldump-uroot -mysqladmin hr

修改表属性大小值

alter table 表名称 change 字段名 字段名 属性

alter table teacher change sex sex varchar(4);

alter table user change password password varchar(40);