三种sql语句:
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型: #1、DDL语句 数据库定义语言: 数据库、表、视图、索引、存储过程,例如CREATE DROP ALTER #2、DML语句 数据库操纵语言: 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT #3、DCL语句 数据库控制语言: 例如控制用户的访问权限GRANT、REVOKE
#1. 操作文件夹(库)
增:create database db1 charset utf8;
可以由字母、数字、下划线、@、#、$ 区分大小写 唯一性 不能使用关键字如 create select 不能单独使用数字 最长128位
查:show databases;
show create database db1;
select database();
选择数据库:USE db1;
删除: drop database db1;
改:alter database db1 charset latin1;
只能修改库的字符集,改不了其他。
操作文件(表)
先切换到文件夹下:use db1
create table t1(id int) engine=innodb default charset=utf-8; 增:create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
1. 在同一张表中,字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
复制表:create table t2 select * from t1; 不会复制主键、外键和索引
只复制表结构:create table t2 like t1;
create table t2 select * from t1 where 1=2; #设置一个不存在的条件
查:show tables
desc t1;
删:drop table t1;
改:alter table t1 rename t2; #修改表名
alter table t1 engine=innodb; #修改表引擎
修改字段:
alter table t1 modify name char(3); #修改字段约束条件,不能改字段名
alter table t1 change name name1 char(2); #change可以修改字段名同时可以修改字段约束条件
注意:修改约束条件的时候必须与创建字段时一样将各属性写完整了,但是有个例外,给主键设置自增时,不能写primary key,否则会报多主键错误。
删除主键也不用常规modify的方式,需要:alter table t1 drop primary key;
增加字段:
alter table t1 add id int;
alter table t1 add id int first; #增加字段作为第一个字段
alter table t1 add id int afer name; #在某字段后面增加新字段
删除字段:
alter table t1 drop id;
字段的约束条件
约束条件直接用空格分开
not null #非空
default xx #默认值
primary key 或 primary key() #设置为主键,创建复合主键:create table t1(id int,name varchar(10),primary key(id,name)) 增加:alter table t1 add primary key(c1,c2);
auto_increment #自增
unique 或 unique key() #唯一键
foreign key() references x() #外键
外键操作
1:创建一个父表,主键作为子表的外键:
create table province(
pId int primary key auto_increment,
pName varchar(20)
);
2:创建子表,外键是父表的主键:
create table user(
userId int primary key auto_increment,
userName varchar(40),
pid int,
foreign key(pid) references province(pId)
);
给一张表添加外键,即给子表的外键添加主键的规则:
在子表声明一个字段pid int,用于作为子表的外键,foreign key(子表的外键字段) references 父表的表名(父表的主键的字段名);
3:当创建好数据表时添加外键约束:
alter table user add foreign key(pid) references province(pId);
alter table 子表的数据表名 add foreign key(子表的外键名称) references 父表的数据表名称(父表的主键名称);
外键注意:
1.1 sql建表时必须先建立被关联表,插入数据必须先插入被关联表的数据
1.2 外键改变时动作cascade set null restrict no action
1.3 多对多,需要先创建第三张表,第三表储存前两张表的关系对应,第三张表创建两个外键指向前两张表。
1.4 一对一,两张表的关系为第二张表是第一张表信息的补充,在第二张表中创建外键指向第一张表,并且设置unique。
建立索引
普通索引INDEX:加速查找
聚集索引:主键索引,叶子节点存放了所有字段的数据
辅助索引:普通索引和唯一索引,叶子节点只存放该字段的数据
唯一索引:
-主键索引PRIMARY KEY:加速查找+约束(不为空、不能重复)
-唯一索引UNIQUE:加速查找+约束(不能重复)
联合索引:
-PRIMARY KEY(id,name):联合主键索引
-UNIQUE(id,name):联合唯一索引
-INDEX(id,name):联合普通索引
举个例子来说,比如你在为某商场做一个会员卡的系统。
这个系统有一个会员表
有下列字段:
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT
那么这个 会员编号,作为主键,使用 PRIMARY
会员姓名 如果要建索引的话,那么就是普通的 INDEX
会员身份证号码 如果要建索引的话,那么可以选择 UNIQUE (唯一的,不允许重复)
#除此之外还有全文索引,即FULLTEXT
会员备注信息 , 如果需要建索引的话,可以选择全文搜索。
用于搜索很长一篇文章的时候,效果最好。
用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以。
但其实对于全文搜索,我们并不会使用MySQL自带的该索引,而是会选择第三方软件如Sphinx,专门来做全文搜索。
#其他的如空间索引SPATIAL,了解即可,几乎不用
#方法一:创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
#方法二:CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
#方法三:ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
#删除索引:DROP INDEX 索引名 ON 表名字;
举例:
#方式一 create table t1( id int, name char, age int, sex enum('male','female'), unique key uni_id(id), index ix_name(name) #index没有key ); #方式二 create index ix_age on t1(age); #方式三 alter table t1 add index ix_sex(sex); #查看 mysql> show create table t1; | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` char(1) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` enum('male','female') DEFAULT NULL, UNIQUE KEY `uni_id` (`id`), KEY `ix_name` (`name`), KEY `ix_age` (`age`), KEY `ix_sex` (`sex`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
数据操作
插入数据:
1. 插入完整数据(顺序插入)
语法一:
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二:
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法:
INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
3. 插入多条记录
语法:
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4. 插入查询结果
语法:
INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;
更新数据:
语法:
UPDATE 表名 SET
字段1=值1,
字段2=值2,
WHERE CONDITION;
示例:
UPDATE mysql.user SET password=password(‘123’)
where user=’root’ and host=’localhost’;
删除数据:
语法:
DELETE FROM 表名
WHERE CONITION;
示例:
DELETE FROM mysql.user
WHERE password=’’;
注意:delete from table1 这条命令可以删除表中所有数据,但不会重置自增字段属性的记录。delete不是用来清空表的,而是用来删除特定的数据,如果要清空表用truncate table1。