基本语法
- 注意字段名,表名,数据库名不需要双引号,单引号包含。
- 单引号等于双引号
- sql语句分号结束,换行不结束
- value和表达式中的字符串需要引号包裹
- 关键字不区分大小写,只有名称和value才区分大小写
- 表和字段除了直接写,还可以采用
库.表
表.字段
的方式写(好像没什么用😂) - 表达式可以用括号包含,也可以直接空格进行分隔,如
where(a=0) 等同于where a=0
有三种注释方法:
select可以直接用于计算:
select 1;
该命令可以测试数据库连接是否有效。即键值对都为1且输出一列
库操作
- 创建数据库
create database 库;
//成功返回Query OK - 显示所有数据库
show databases;
- 选择使用某数据库
use 库;
//成功返回 Database changed - 删除数据库
drop database 库名;
-
表操作
创建数据表最好在最后(即分号前)加上
**ENGINE=INNODB DEFAULT CHARSET=utf8**
- ENGINE=InnoDB表示使用InnoDB引擎 (默认就是InnoDB)
- DEFAULT CHARSET=utf8 表示使用默认字符集
- 很重要的用法,记住,也常考
- 创建数据表
create table 表名 (字段1 类型(长度) , 字段2 类型(长度) ....);
只有int数据类型的长度可以不写,默认长度
- 查看数据表结构
show columns from 表名 [from 库名]
(未选择表所属数据库时要加上这个),或者直接
show columns form 库**.**表;
show columns可以查看当前数据库之外其他库的表;
且只能查看完整表结构
describe 表名;
describe只能查看当前库的表结构 ;但是可以只查看表内某一列的结构(记住是只能查看其中一列)
即 describe 表名 列名;
- 查看所有表
show tables;
- 查看表是否存在
show tables like '表名';
- 查看某个表创建表时的SQL语句
show table create 表;
删除表:
drop table 表;
快照即复制一份当前表的数据到一个新表,可以结合
CREATE TABLE
和SELECT
:-- 对class_id=1的记录进行快照,并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
数据表约束
用法大都是直接加在字段后
主键约束[primary key]
字段 primary key;
或者(字段1 ...,字段2 ....,primary key(列))
每个表都应该有一个主键,主键只能有一个主键必须包含唯一的值(类似unique)主键必须是not null约束
自增约束[auto_increment ]
作用于数值型,且被索引的字段(如主键),使其自增 ,如果插入数据时没有写,则默认使用上一条数据值的+1值(所以数值索引可以是不连续的)
非空约束[not null ]
默认值约束[default]
若插入数据时没写,则使用默认值
唯一约束[unique]
使一个字段中数据不能重复 ,可以使主键以外的列具有数据唯一性,所以可以有多个unique(主键只能有一个)
外键约束[foreign key]
值范围约束[check]
限制列中值的范围 如(xuehao int ,check(xuehao >18060201 ));
修改表结构
- 添加列
alter table 表名 add 列名 column 类型 [其他约束];
- 删除列
alter table 表名 drop column 列名;
- 添加约束
alter table 表名 add 约束名(列);
- 删除约束
alter table 表名 drop 约束名(列);
- 重命名表
rename table 旧 to 新;
- 删除表
drop table 表名;
修改列结构(如name,类型,增加约束…)
alter table 表名 change column 旧字段名 新字段名 新类型 [约束];
增删改-数据
增加数据 value
insert into 表(字段1 ,字段2,.....) values (值1 ,值2,.....) ;
值记得与字段对应,数据类型也要对应
可以批量添加数据,就...values(...),(...),(...);
insert into 表名 values(......);
上面的可以只插入部分字段,这个必须全部插入,即便有默认值约束的列或者其他约束,也要写入一个值
修改数据 set
批量修改update 表名 set 字段1=新值 , 字段2=新值 ,..... where 范围
如update 表名 set name="张三" where xuehao>10 and xuehao<15;
使学号10到15号的五个人的名字全改为张三update 表名 set score=10+2 where xuehao>10;
这是把学号大于10的人分数全改为12,并非把学号大于10且分数为10的人分数加2
单个修改update 表名 set xuehao=10 where name='张三';
把张三学号改为10
注意修改添加数据不要违背某些约束,比如主键,非空,唯一性
删除数据 delete
批量删除delete from 表名 where 范围;
删除全部数据 truncate table 表名
- truncate的效率比delete更高,因为truncate不需要写日志,因此效率比deletet要写日志更高
插入或替换
如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就先删除原记录,再插入新记录。此时,可以使用REPLACE
语句,这样就不必先查询,再决定是否先删除再插入:
若REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
id=1
的记录不存在,REPLACE
语句将插入新记录,否则,当前id=1
的记录将被删除,然后再插入新记录。插入或更新
如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就更新该记录,此时,可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...
语句:
若INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
id=1
的记录不存在,INSERT
语句将插入新记录,否则,当前id=1
的记录将被更新,更新的字段由UPDATE
指定。插入或忽略
如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就啥事也不干直接忽略,此时,可以使用INSERT IGNORE INTO ...
语句:
若INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
id=1
的记录不存在,INSERT
语句将插入新记录,否则,不执行任何操作。索引
创建索引
create [约束] index 索引名 on 表名(列);
可以同时对多列创建索引,用逗号隔开
可以增加约束创建约束索引 如加unique
创建唯一索引,加primary key
创建索引查看索引
explain select * from 表名;
删除索引
drop index 索引名;
索引不可修改,想改只能删除重建强制使用指定索引
在查询的时候,数据库系统会自动分析查询语句,并选择一个最合适的索引。但是很多时候,数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引,可以使用FORCE INDEX
强制查询使用指定的索引。例如:
指定索引的前提是索引SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;
idx_class_id
必须存在。
写入查询结果集
- 如果要把查询结果写入到一个表中,可以使用
insert into
结合select
- 注意类型对应
#如果是部分字段,需要指明字段与查询的字段
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
#如果全部插入的表的字段与查询的结果的全部字段都对应:
insert into `student2` select * from student
外部导入数据
- 这里以excel为例:
- 首先创建表,表字段。
- 外部excel也必须有数据的名称,这样才能选择对应关系,一般改为与sql字段相同即可,这样导入时自动导入相同名称的数据
- 然后导入向导不停下一步即可了