1、基本语法
数据库操作
- 连接数据库
mysql -u root -p
Enter password:*****
- 创建数据库
CREATE DATABASE db_mrgui;
- 删除数据库
DROP DATABASE db_mrgui;
- 查看数据库
show databases;
- 选择数据库
use db_mrgui;
增删改查
- 创建数据表
CREATE TABLE tbl_mrgui(
`id` INT UNSIGNED AUTO_INCREMENT,
`title` VARCHAR(100) NOT NULL,
`author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
- 删除数据表
DROP TABLE tbl_mrgui;
- 插入数据
INSERT INTO tbl_mrgui
(title, author, submission_date)
VALUES
("学习 PHP", "教程", NOW());
INSERT INTO tbl_mrgui
(id, title, author, submission_date)
VALUES
(0, "学习 MySQL", "教程", NOW());
INSERT INTO tbl_mrgui
VALUES
(0, "JAVA 教程", "COM", '2016-05-06');
- 插入多条数据
INSERT INTO tbl_mrgui
(title, author, submission_date)
VALUES
("play", "111", NOW()),
("吃饭", "222", NOW());
- 读取数据表
select * from tbl_mrgui;
- 查询数据
select * from tbl_mrgui where submission > date('2020-01-01');
- 更新数据
update 表名称 set 列名称=新值 where 更新条件;
update tbl_mrgui set submission = date('2020-8-1') where submission > date('2020-8-1');
update tbl_mrgui set submission_date = submission_date + 1;
update tbl_mrgui set title = replace(title, '学习', 'study'); # 替换某个字段中的某些字符
- 删除数据
delete from tbl_mrgui where length(author) < 4;
子句
- LIKE 子句
select * from tbl_mrgui where author like '%程';
- UNION 操作符
select title from tbl_mrgui
union all # 加 all 不去重,不加 all 默认去重
select author from tbl_mrgui;
- ORDER BY 排序
select * from tbl_mrgui order by submission_date ASC; # ASC 升序, DESC降序
- GROUP BY 语句
根据一个或多个列对结果集进行分组。
select name, count(*) from employee_tbl group by name;
select name, count(*) as c1, sum(singin) as c2 from employee_tbl group by name with rollup; # 在末尾追加一行统计
连接
- 内连接
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
# 等价于
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;
- 左连接
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
- 右连接
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
ALTER命令
- 创建表
create table testalter_tbl
(i INT,c CHAR(1) );
- 显示字段信息
SHOW COLUMNS FROM testalter_tbl;
- 删除字段
ALTER TABLE testalter_tbl DROP i;
- 添加字段
ALTER TABLE testalter_tbl ADD i INT;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST; # 新增字段设定为第一列
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c; # 新增字段设定于某个字段之后
- 修改字段类型及名称
ALTER TABLE testalter_tbl MODIFY c CHAR(10);
ALTER TABLE testalter_tbl CHANGE i j BIGINT;
ALTER TABLE testalter_tbl CHANGE j j INT;
- 修改表名
ALTER TABLE testalter_tbl RENAME TO alter_tbl;
其他
- NULL值处理
IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符(不同于 = 运算符),当比较的的两个值相等或者都为 NULL 时返回 true。
-
正则表达式
-
事物
1、用 BEGIN, ROLLBACK, COMMIT来实现 BEGIN 开始一个事务 ROLLBACK 事务回滚 COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式: SET AUTOCOMMIT=0 禁止自动提交 SET AUTOCOMMIT=1
开启自动提交
- 索引…