一、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; |
---|---|