查询所有数据库
show databases
创建数据库
create database db_name
DEFAULT CHARACTER SET charset_name
显示数据库
创建信息
show create database db_name
删除数据库
drop database db_name
修改数据库的
字符集
alter database db_name
DEFAULT CHARACTER SET charset_name
使用数据库
use db_name
查看所有表
show tables
创建表
CREATE TABLE table_name
(
field1 datatype,
field2 datatype,
field3 datatype
)
--field:指定列名datatype:指定列类型
查看表结构
desc table_name
删除表
drop table table_name
*修改表名称
alter table table_name
rename to teacher
添加字段
alter table student
add column sgender varchar(2)
删除字段
alter table table_name
drop column sgender
修改字段类型
alter table table_name
modify column remark varchar(100)
*修改字段名称
alter table table_name
change column sgender gender varchar(2)
插入所有字段
INSERT INTO student VALUES(1,'Tom','male',20)
插入部分字段
INSERT INTO student(id,NAME) VALUES(2,'Jack')
修改数据
UPDATE student
SET gender='Male'
WHERE id=1
*修改多个字段
UPDATE student
SET gender='男',age=30
WHERE id=2;
删除数据
DELETE FROM student
WHERE id=2;
开始一个事务
BEGIN
事务回滚
ROLLBACK
事务确认
COMMIT
创建一个保存点
SAVEPOINT identifier
删除一个事务的保存点
RELEASE SAVEPOINT identifier
回滚到标记点
ROLLBACK TO identifier
删除全表内容
truncate table
查询所有列
SELECT * FROM student;
查询指定列
SELECT id,NAME,gender FROM student;
查询时添加常量列
SELECT price,'Dollars' AS 'Price' FROM grocerys;
查询时合并列
(只能合并数值)
SELECT id,(NAME+servlet) FROM student;
查询时去重复
SELECT DISTINCT gender FROM student;
条件查询
SELECT * FROM student
WHERE id=2 AND name = ‘TOM’;
条件运算符
> < >= <= = <> between and or
判断null
SELECT * FROM student WHERE address IS NULL ;
判断空字符串
SELECT * FROM student WHERE address='';
查询有地址的学生
SELECT * FROM student
WHERE address IS NOT NULL AND address<>'';
模糊条件
SELECT * FROM student
WHERE NAME LIKE '张%';(任意个字符)
SELECT * FROM student
WHERE NAME LIKE '李_';(一个字符)
聚合函数
SELECT SUM(price) AS 'total_price' FROM shopping_kart;
求和:SUM() 求平均数:AVG()
找最大值:MAX()找最小值:MIN()
统计多少行(NULL不算):count()
*分页查询
SELECT * FROM student LIMIT 6,2;
(从第6个后面开始数,2个)
查询排序
SELECT * FROM student ORDER BY id ASC
ASC 升 从上往下 数字越来越大 字母A-Z
DESC 降 从上往下 数字越来越小 字母Z-A
分组查询
SELECT gender,COUNT(*)
FROM student GROUP BY gender;
*数据约束
CREATE TABLE table_name(
Field1 type1 /*HERE*/
)
默认值:DEFAULT ‘默认值’
非空:NOT NULL
唯一:UNIQUE (可以插入null)
主键: PRIMARY KEY (非空+唯一)
从1开始自增长:AUTO_INCREMENT
外键:
CONSTRAINT fk_name
FOREIGN KEY(deptId) REFERENCES dept(id)
交叉连接
(笛卡尔积)
cross join/join/inner join/,
内连接
交叉连接 + on
左外连接
table1 left join table2 on ...
右外连接
table1 right join table2 on ...
子查询
where .... in .....
联合查询
union
创建索引
CREATE INDEX indexName
ON table_name (column_name)