mysql数据定义_Mysql——数据定义

————————————————————————————————

1、基本操作

删除数据元

delete from student where name = ‘Qiang’; 条件删除

delete from student;清空表格

更新数据

update student set height = 180 where name = ‘Hong’;

update student set age = 99 where name like "H%";

通配符：

select * from student where name like ‘Hong%’; 0或多个字符

select * from student where name like ‘Hong_’; 一个字符

————————————————————————————————

2、表的创建&删除

创建表

create table student(

id int primary key,

name varchar(20) not null,

weight decimal(5,2) default 0,

birthday date not null

);

--------------------

create table if not exists employee(

id int primary key auto_increment,

name varchar(50) not null,

gender enum(‘male‘, ‘female‘),

email varchar(100) not null unique,

salary decimal(6,2) default 0,

hiredate datetime not null,

hover set(‘A‘,‘B‘,‘C‘,‘D‘)

)engine InnoDB character set utf8;

默认是InnoDB

删除表操作

drop database mydb;

drop database if exists mydb;

————————————————————————————————

3、表的修改(alter):

修改表编码

alter table student character set = utf8;

修改表名称

alter table rename to|as new_name;

rename table tb_name to new_name;

修改列名称(change)

alter table student change [column] old_name new_name int;

修改列属性(modify)：

alter table student modify [column] height varchar(13);

添加一列

alter table employee add department_id int;

alter table employee add department_id int after name;

alter table employee add department_id int first;

删除一列

alter table tb_name drop [column] col_name;

插入数据

insert [into] employee value[s]();

insert employee values(null,‘TA‘,‘male‘,[email protected],100,‘1999-12-12‘,‘A,B,C‘)

插入一行数据

insert into employee(last_name, email, hiredate) values(‘TB‘,[email protected]);

连续插入多行数据：

insert into student values(005, ‘大头‘,45, 165),(007,"小白",34,178),(009, “小云",23,189);

————————————————————————————————

4、mysql相关命令：

desc tb_name; 查看相关字段属性

show create database db_name; 显示数据库创建信息

show create table tb_name; 显示表创建信息

status;("\s”) 当前连接相关信息

show columns from tb_name; 显示列的信息

show full columns from tb_name; 完整字段信息

show variables; 显示系统变量

show variables like ‘char%’; 查找系统变量

set character_set_server = utf8; 设置系统变量值

select * from tb_name \G 列的形式显示查询数据

? functions 查看帮助文档

? command 查看指定命令的帮助文档

source filename.sql 导入外部数据

原文：http://weijiancheng.blog.51cto.com/10190955/1701320