MySql操作数据

1、创建数据库

create database 数据库名

mysql> create database test;

2、创建表

使用前use选择数据库

mysql> use test;
Database changed
mysql> create table table_test(
    -> id int(10),
    -> age int(10),
    -> name char(10)
    -> );
Query OK, 0 rows affected (0.08 sec)

3、查看表结构

desc 表名

mysql> show tables
    -> ;
+----------------+
| Tables_in_test |
+----------------+
| table_test     |
+----------------+
1 row in set (0.00 sec)

mysql> desc table_test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(10)  | YES  |     | NULL    |       |
| age   | int(10)  | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

4、插入数据

insert into 表名(列名1,列名2) values(值1,值2);
【注意：在创建表时设定的字段类型是字符串要用引号（单双均可）】

mysql> insert into table_test(id,age,name)values(1,10,"zhangsan");
Query OK, 1 row affected (0.00 sec)
mysql> insert into table_test(id,age,name)values(2,20,"lishi");
Query OK, 1 row affected (0.00 sec)
mysql> insert into table_test(id,age,name)values(3,15,"wangwu");
Query OK, 1 row affected (0.00 sec)
mysql> select * from table_test;
+------+------+----------+
| id   | age  | name     |
+------+------+----------+
|    1 |   10 | zhangsan |
|    2 |   20 | lishi    |
|    3 |   15 | wangwu   |
+------+------+----------+
3 rows in set (0.00 sec)

5、删除数据

删除数据库
drop database 数据库名

删除表
drop table 表名
truncate table 表名;（只能操作表）
delete from 表名【where】（如果没有指定 WHERE 子句，表中的所有记录将被删除。
可以在 WHERE 子句中指定任何条件）
删除id=1的一行

mysql> delete from table_test where id=1;
Query OK, 2 rows affected (0.00 sec)
mysql> delete from table_test where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from table_test;
+------+------+--------+
| id   | age  | name   |
+------+------+--------+
|    2 |   20 | lishi  |
|    3 |   15 | wangwu |
+------+------+--------+
2 rows in set (0.00 sec)

6、更新数据

update 表名 set 字段=数据 【where 】（WHERE子句是可选的。 如果省略WHERE子句，则UPDATE语句将更新表中的所有行。）

mysql> update table_test set id=123 where age=20；
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from table_test;
+------+------+--------+
| id   | age  | name   |
+------+------+--------+
|  123 |   20 | lishi  |
|    3 |   15 | wangwu |
+------+------+--------+
2 rows in set (0.00 sec)

7、查询数据

查询数据

8、排序

asc升序，desc降序
按id正序排列

mysql> select * from table_test order by id asc【desc】;