数据库mysql
一、mysql常用语句
| 登录MySQL | mysql -u root -p |
|---|---|
| 列出所有数据库 | SHOW DATABASES; |
| 创建一个新数据库 | CREATE DATABASE test; |
| 删除一个数据库 | DROP DATABASE test; |
| 对一个数据库进行操作时 | USE test; |
| 列出当前数据库的所有表 | SHOW TABLES; |
| 要查看一个表的结构 | DESC students; |
| 创建表 | CREATE TABLE |
| 删除表 | DROP TABLE |
| 退出MySQL | EXIT |
二、sql常用语句
- 查询
| 所有数据 | SELECT * FROM students; |
|---|---|
| 条件查询 | SELECT * FROM students WHERE score >= 80; |
| 更多的条件 | SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = ‘M’; |
| 从students表中返回id、score和name这三列： | SELECT id, score, name FROM students; |
| 排序 | SELECT id, name, gender, score FROM students ORDER BY score; |
| 加上DESC表示“倒序” | SELECT id, name, gender, score FROM students ORDER BY score DESC; |
- 插入
| 插入一条新记录 | INSERT INTO students (class_id, name, gender, score) VALUES (2, ‘大牛’, ‘M’, 80); |
|---|---|
| 一次性添加多条记录 | INSERT INTO students (class_id, name, gender, score) VALUES (1, ‘大宝’, ‘M’, 87), (2, ‘二宝’, ‘M’, 81); |
- 修改
| 基本语法 | UPDATE students SET name=‘大牛’, score=66 WHERE id=1; |
|---|---|
| 更新字段时可以使用表达式 | UPDATE students SET score=score+10 WHERE score<80; |
- 删除
| 删除students表中id=1的记录 | DELETE FROM students WHERE id=1; |
|---|---|