下面关于字符的设置中 : [charset 等效于 character set ]
【创建数据库和创建表】
创建数据库:
create database `database` charset utf8;
注意这里数据库名-database使用了反引号 `` ;如果不使用反引号,那么SQL语句不能使用关键字或者保留字。 ------------------------------------------------------
创建中文数据库;
set names gbk;
create database 中国 charset utf8; -----------------------------------------------------
utf8字符集,校对集依赖字符集。
修改数据库字符集:
alter database database_name charset GBK;
创建表
创建成功后,在数据库对应的文件夹下,会产生对应表的结构文件(扩展名为.frm,跟存储引擎有关系)。
create table t_user( id int(10) PRIMARY KEY NOT NULL UNSIGNED AUTO_INCREMENT , //主键,不为空,无符号,自动递增 name VARCHAR(20) NOT NULL DEFAULT '',//默认为'' age int(2) NOT NULL, dept_id int, CONSTRAINT `dept_id` FOREIGN KEY (`dept_id`) REFERENCES `department` (`department_id`) ON DELETE CASCADE ON UPDATE CASCADE;
--添加外键约束,外键名为‘dept_id’(可改,如deptId);
--外键列(dept_id)对应表department的列department_id;
--级联删除和更新
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
//设置存储引擎与表字符集和校对集
【重命名表】
关键字:rename
rename table emp to emp2
Mysql 不支持检查约束,添加 check不起作用!
【表结构与数据复制】
高级复制表语句;
create table table_name like 数据库.表名;
-- 将某个数据库下的某个表进行复制(表结构)。
复制数据(蠕虫复制)
insert into new_table select * from 数据库.old_name -- 将同一链接下的某个数据库的某个表数据复制到具有同样表结构的new_table。
【修改表选项】
字符集、校对集和存储引擎、自增长
alter table table_name 表选项;
alter table p_user charset=utf8,ENGINE=MYISAM,COLLATE=utf8_general_ci;
--修改表字符集表示表存储数据的时候使用该字符集。如utf8,表明使用三个字节存储一个汉字;gbk,表明使用两个字节存储一个汉字。
--修改自增长
alter table table_name auto_increment = 值 ;
【修改表字段编码】
关键字:change,不是modify!
[charset 等效于 character set ];
可以修改表字段列位置。
alter table p_user change name name varchar(20) character set utf8;
【添加/删除普通索引-index】
关键字为index;
不能添加constraint关键字;
-- 添加索引,指定名字为index_name;
alter TABLE p_user_2 add index index_name(name);
-- 删除索引,如果没有指定名字,默认使用列名
alter TABLE p_user_2 drop index index_name
查看表创建语句
CREATE TABLE `p_user_2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL COMMENT '名字', `age` int(4) DEFAULT NULL, `sex` varchar(10) DEFAULT NULL, `p_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_p_id` (`p_id`) USING BTREE, KEY `index_name` (`name`),-- 这里对应普通index CONSTRAINT `fk_id` FOREIGN KEY (`p_id`) REFERENCES `p_user` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8
【为表添加属性约束语法】—-【为表添加属性约束语法】—-【为表添加属性约束语法】—-【为表添加属性约束语法】
语法格式如下:
alter table [table_name] add [constraint] [constraint_name] [unique| primary key|foreign key] ([column_name])
【主键操作】
【为表增加主键】
给表增加主键,大体分为三种:
① 创建表的时候在字段后面使用 primary key关键字;
create table my_default( id int PRIMARY KEY,--使用关键字 name VARCHAR(20) not null unique key , gender varchar(4) DEFAULT '男' )charset utf8;
② 在创建表的时候,在所有的字段之后使用 primary key(主键的字段 列表)来创建主键(如果有多个字段作为主键,称之为复合主键)
create table my_pri( number char(10), course char(10), score TINYINT, PRIMARY KEY(number,course)--复合主键 )charset utf8;
desc my_pri;
测试如下:
insert into my_pri VALUES('001','IT','10');--插入成功
insert into my_pri VALUES('001','IT','10');--插入失败
insert into my_pri VALUES('001','IT2','10');--插入成功
③ 创建表之后,为表追加主键。有两种方式,第一位修改表字段属性;第二直接追加。
第一种方式示例如下:
alter TABLE my_pri MODIFY number char(11) PRIMARY KEY;
第二种方式示例如下:
alter table table_name add primary key(column) ;
alter TABLE my_pri add PRIMARY KEY(number,course);
--增加复合主键
alter table my_pri add CONSTRAINT pk_score PRIMARY KEY(score) --使用关键字CONSTRAINT 且指定主键名字pk_score alter table my_pri add CONSTRAINT PRIMARY KEY(score) --使用关键字CONSTRAINT 且使用默认名字
【删除主键】
alter table my_pri drop PRIMARY KEY;--不能根据主键名字删除主键
【唯一约束】
【为表增加唯一约束】
增加单一约束
alter TABLE my_pri add CONSTRAINT nuc UNIQUE KEY(number);
--使用指定名字
alter TABLE my_pri add CONSTRAINT UNIQUE KEY(course);
--使用默认名字
alter TABLE my_pri add UNIQUE KEY(score);
--不使用CONSTRAINT 关键字
查看增加约束后的表结构
show create TABLE my_pri;
CREATE TABLE `my_pri` ( `number` char(11) NOT NULL, `course` char(10) NOT NULL DEFAULT '', `score` tinyint(4) DEFAULT NULL, UNIQUE KEY `nuc` (`number`), UNIQUE KEY `course` (`course`), UNIQUE KEY `score` (`score`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
增加复合约束
create table my_pri( number char(10), course char(10), score TINYINT, PRIMARY KEY(number,course) )charset utf8;
alter TABLE my_pri add UNIQUE KEY(number,course);--添加复合unique key,使用默认名字为第一个列名-number
alter TABLE my_pri add CONSTRAINT nuc UNIQUE KEY(number,course);--添加复合唯一约束,自定义名字 nuc
show create TABLE my_pri;--查看表结构
--第一次
CREATE TABLE `my_pri` ( `number` char(11) NOT NULL, `course` char(10) NOT NULL DEFAULT '', `score` tinyint(4) DEFAULT NULL, UNIQUE KEY `number` (`number`,`course`)--unique key 名字为 number ) ENGINE=InnoDB DEFAULT CHARSET=utf8 --第二次 CREATE TABLE `my_pri` ( `number` char(11) NOT NULL, `course` char(10) NOT NULL DEFAULT '', `score` tinyint(4) DEFAULT NULL, UNIQUE KEY `nuc` (`number`,`course`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 删除复合UNIQUE KEY number alter table my_pri drop index number;
删除复合UNIQUE KEY nuc
alter table my_pri drop index nuc;
【删除属性约束同时增加一列】
alter table my_default drop index name,--删除unique key name add column id int first --增加id列位置为第一列
注意,普通索引和唯一索引删除的时候使用的关键字为index,主键和外键则使用对应关键字。
【请空表数据】
truncate table_name;//index归1
delete from table_name;//index不会归1
【删除表】
drop table 表名1,表名2 drop table t_user DROP PROCEDURE procName ;-- 删除存储过程
drop table table_name;-- 删除表
drop view view_name; -- 删除视图
drop database name;-- 删除数据库
drop function name;-- 删除函数
drop trigger name;-- 删除触发器
【修改表】
增加列:
ALTER TABLE t_user add COLUMN `born_date_time` datetime after sex(在sex列后添加该列) ALTER TABLE t_user add COLUMN `born_date_time` datetime first(添加该列为第一列)
修改列:
ALTER TABLE t_user MODIFY COLUMN username VARCHAR(15) [位置];
-- 可以在修改列的时候同时改变其列位置
删除列
ALTER TABLE t_use drop COLUMN `born_date_time1`
重命名列
ALTER TABLE p_user CHANGE COLUMN `NAME` `name` varchar(20)(带上数据类型)[位置:first];
-- 可以在改变列的时候同时改变其列位置
建表后给列添加注释
ALTER TABLE p_user MODIFY COLUMN `sex` CHAR(2) COMMENT '0男1女'
查看表结构:
desc/describle t_user;
//
show columns from t_user;
查看表创建语句:
show create table table_name;
【数据操作】
① 插入数据
【方案1】
给全表字段插入数据,不需要指定字段列表,但是插入数据值顺序与字段顺序一致;且非数值型数据需用引号包括(建议单引号);
insert into table_name values(值列表)[,(值列表)];
--可一次性插入多条记录;
insert into p_user values (1,'tom','B001'), (2,'Janus','B002')
【方案2】
给部分字段插入数据,需选定字段列表。字段列表出现的顺序与字段的顺序无关;
但是值列表的顺序必须与选定的字段列表顺序一致。
insert into table_name (字段列表) values(值列表)[,(值列表)];
--可一次性插入多条数据。
insert INTO p_user (name,age,sex) VALUES ('tom',12,'male'),('lily',15,'female')
【字段默认值】
如果字段设计的时候赋予了默认值,要想默认值起作用,有如下两种方式(假设这里age字段默认值为18;):
① 跳过使用默认值的字段
insert INTO p_user (name,sex) VALUES ('tom','male');
② 为该字段赋予值-default
insert INTO p_user (name,age,sex) VALUES ('tom',default,'male');
以上两种方式都能成功插入('tom',18,'male')。
【主键为ID,自增长】
插入时为该字段赋予值-null
insert INTO c_user VALUES (null,'male',18);
注意:该情况适用于id为 主键自增长的情况
插入字符串日期格式型的数据时,数据库会自动转换成对应的日期(日期时间)型:
插入日期型(date):
INSERT INTO t_user(born_date) VALUES('2016-10-14') 插入日期时间型(datetime) INSERT INTO t_user(born_date_time) VALUES('2016-10-14 09:10:12')
② 更新数据
update table_name set 字段名= 值[where 条件];--如果不是更新全部,一般都有where;
③ 删除数据
delete from table_name [where 条件];--一般都有where;