MySQL学习笔记一：数据库基本操作

1、显示数据库列表

mysql root@localhost:(none)> show databases
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hive               |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set

2、删除数据库

mysql root@localhost:(none)> DROP DATABASE sakila
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 23 rows affected

3、创建数据库

mysql root@localhost:(none)> create database data1
Query OK, 1 row affected
Time: 0.003s

这里在进入数据库后再进行一系列操作

mysql root@localhost:(none)> use data1
You are now connected to database "data1" as user "root"
Time: 0.000s

4、创建数据表

mysql root@localhost:data1> create table pet(name VARCHAR(20), color VARCHAR(10), owner VARCHAR(20),species VARCHAR(20)
                            , sex CHAR(1))

Query OK, 0 rows affected
Time: 0.286s
mysql root@localhost:data1> show tables
+-----------------+
| Tables_in_data1 |
+-----------------+
| pet             |
+-----------------+
1 row in set
Time: 0.011s

创建的时候要给出至少一个列名，否则：(1113, 'A table must have at least 1 column')

5、查看数据表基本信息

mysql root@localhost:data1> descrble pet
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'descrble pet' at line 1")
mysql root@localhost:data1> show columns from pet
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | <null>  |       |
| color   | varchar(10) | YES  |     | <null>  |       |
| owner   | varchar(20) | YES  |     | <null>  |       |
| species | varchar(20) | YES  |     | <null>  |       |
| sex     | char(1)     | YES  |     | <null>  |       |
+---------+-------------+------+-----+---------+-------+

5 rows in set
Time: 0.018s

6、设置主键

mysql root@localhost:data1> alter table pet add primary key<name>
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!

7、删除主键

alter table pet drop primary key

8、往数据表中添加数据、修改（更新）数据

mysql root@localhost:data1> INSERT INTO pet (name,color,owner,species,sex) VALUES("Mimi","white","Sara","cat","f")
Query OK, 1 row affected
Time: 0.122s

mysql root@localhost:data1> UPDATE pet SET sex='m' WHERE name ="Mimi"
Query OK, 1 rows affected
Time: 0.001s

9、查询数据

1、读取所有记录

mysql root@localhost:data1> select * from pet
+------+-------+-------+---------+-----+
| name | color | owner | species | sex |
+------+-------+-------+---------+-----+
| Mimi | white | Sara  | cat     | m   |
+------+-------+-------+---------+-----+
1 row in set

2、条件查询

mysql root@localhost:data1> select * from pet where species = "cat"
+------+-------+-------+---------+-----+
| name | color | owner | species | sex |
+------+-------+-------+---------+-----+
| Mimi | white | Sara  | cat     | m   |
+------+-------+-------+---------+-----+

1 row in set
Time: 0.012s

10、导出数据

导出数据表出错,需要更改安全文件路径，具体见 https://blog.csdn.net/weixin_44595372/article/details/88723191

mysql root@localhost:data1> select * from pet INTO OUTFILE 'test1.txt'
(1290, 'The MySQL server is running with the --secure-file-priv option so it cannot execute this statement')
mysql root@localhost:data1>