今日一言:你不能一直做一些烂事,
?????然后自己后悔,
?????好像后悔有用一样,
?????你需要变好。
??????????——《马男波杰克》
MySQL使用笔记(1)
MySQL是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。
前言
以下复习一下常用的SQL语句,助回忆用,如无其它需求不更新。
登录MySQL
mysql> mysql -u root -p
Enter password:*********
显示所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lentme |
| mysql |
| performance_schema |
| rcklos |
| sakila |
| sys |
| world |
+--------------------+
8 rows in set (0.20 sec)
创建数据库:create
mysql> create database mydatabase;
Query OK, 1 row affected (0.04 sec)
删除数据库:drop
mysql> drop database mydatabase;
Query OK, 0 rows affected (0.12 sec)
选择数据库:use
mysql> use rcklos;
Database changed
创建数据表:create
mysql> create table mytable( id int primary key auto_increment, name varchar(20) not null);
Query OK, 0 rows affected (0.12 sec)
删除数据表:drop
mysql> drop table mytable;
Query OK, 0 rows affected (0.06 sec)
查询数据表结构: desc
mysql> desc mytable;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
插入数据:insert into
mysql> insert into mytable( name ) value ( "LOS" );
Query OK, 1 row affected (0.03 sec)
查询数据:select
mysql> select * from mytable;
+----+------+
| id | name |
+----+------+
| 1 | LOS |
+----+------+
1 row in set (0.00 sec)
查询数据:where
mysql> select * from mytable where id > 3 AND id < 5 OR id = 1;
+----+------+
| id | name |
+----+------+
| 1 | LOS |
| 4 | 景田 |
+----+------+
2 rows in set (0.02 sec)
查询数据:like
mysql> select * from mytable where name like "L%S" OR id = 2;
+----+------+
| id | name |
+----+------+
| 1 | LOS |
| 2 | 小鱼 |
+----+------+
2 rows in set (0.01 sec)
查询数据:union
mysql> select * from mytable where id = 1
-> union
-> select * from mytable where name like "怡宝";
+----+------+
| id | name |
+----+------+
| 1 | LOS |
| 4 | 怡宝 |
+----+------+
2 rows in set (0.02 sec)
更新数据:update
mysql> update mytable set name="怡宝" where name like "景田";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
删除数据:delete
mysql> delete from mytable where name="米莱迪";
Query OK, 1 row affected (0.01 sec)
数据排序:order by
升序
mysql> select * from mytable order by id ASC;
+----+--------+
| id | name |
+----+--------+
| 1 | LOS |
| 2 | 小鱼 |
| 4 | 怡宝 |
| 5 | 便利贴 |
+----+--------+
4 rows in set (0.00 sec)
降序
mysql> select * from mytable order by id DESC;
+----+--------+
| id | name |
+----+--------+
| 5 | 便利贴 |
| 4 | 怡宝 |
| 2 | 小鱼 |
| 1 | LOS |
+----+--------+
4 rows in set (0.00 sec)