MYSQL的基础增删查改操作

进入mysql

mysql -u root -p

mysql对数据库操作

查看数据库

mysql> show databases;

创建数据库

mysql> create database test;

删除数据库

mysql> drop database test;

进入数据库

mysql> use test;

mysql 对表操作

查看列表

mysql> show tables;

创建表

mysql> create table test_table(
    -> id int not null,
    -> title varchar(20) not null,
    -> sub_date date,
    -> primary key(id)
    -> ) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.00 sec)

插入数据

mysql> insert into test_table (id, title, sub_date)
    -> values
    -> (1,"haha",now());
Query OK, 1 row affected, 1 warning (0.02 sec)

读取表中所有数据

mysql> select * from test_table;
+----+-------+------------+
| id | title | sub_date   |
+----+-------+------------+
|  1 | haha  | 2018-11-07 |
+----+-------+------------+
1 row in set (0.00 sec)

查看表的字段

mysql> SHOW COLUMNS from test_table;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| title    | varchar(20) | NO   |     | NULL    |       |
| sub_date | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

对表进行条件查询

mysql> select id, title
    -> from test_table
    -> where id=1;
+----+-------+
| id | title |
+----+-------+
|  1 | haha  |
+----+-------+
1 row in set (0.00 sec)

更新表中数据

mysql> update test_table set title='aiya' where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

删除表中数据

mysql> delete from test_table where title='aiya';
Query OK, 1 row affected (0.00 sec)

删除表

mysql> drop table test_table;
Query OK, 0 rows affected (0.01 sec)