MySQL基本命令行和操作语句
这是一篇关于MySQL基本命令行和操作语句的Blog
MySQL基本命令行
连接服务器:mysql -u root -p
λ mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)
查看当前服务器下有哪些库（databases）:show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.02 sec)
创建一个数据库:create database DBname;(字符集声明，整理说明)
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
删除数据库:drop database DBname;
mysql> drop database test1;
Query OK, 0 rows affected (0.23 sec)
选择数据库：use DBname;(比如要对一个表进行操作前要先选择数据库)
mysql> use test;
Database changed
查看一下库下面的所有表：show tables;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| class |
+----------------+
1 row in set (0.00 sec)
创建一张表: create table tableNmae;
mysql> create table stu(
-> id int,
-> name varchar(10)
-> );
Query OK, 0 rows affected (0.31 sec)
删除一张表：drop table tableName;
mysql> drop table class;
Query OK, 0 rows affected (0.27 sec)
mysql> show tables;
Empty set (0.00 sec)
修改表名：rename table oldName to newName;
mysql> rename table stu to student;
Query OK, 0 rows affected (0.17 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student |
+----------------+
1 row in set (0.00 sec)
查看表结构：desc tableName;
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
设置使用的字符集命令: set names utf8;
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
SQL操作语句
SQL SELECT 语句
SELECT 语句用于从数据库中选取数据。结果被存储在一个结果表中，称为结果集。
SQL SELECT 语法
选取部分数据：
SELECT column_name,column_name
FROM table_name;
例如:
mysql> select name from student;
+------+
| name |
+------+
| 张三 |
+------+
1 row in set (0.00 sec)
选取全部数据：
SELECT * FROM table_name;
例如：
mysql> select * from student;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
+------+------+
1 row in set (0.04 sec)
SQL WHERE 子句
WHERE 子句用于过滤记录,提取那些满足指定标准的记录。
SQL WHERE 语法
SELECT * FROM table_name
WHERE column_name operator value;
例如：
mysql> select * from student where id=1;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
+------+------+
1 row in set (0.05 sec)
SQL INSERT INTO 语句
INSERT INTO 语句用于向表中插入新记录。
SQL INSERT INTO 语法
INSERT INTO 语句可以有两种编写形式。 第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：
INSERT INTO table_name
VALUES (value1,value2,value3,...);
例如：
mysql> insert into student
-> values(2,'王五');
Query OK, 1 row affected (0.06 sec)
mysql> select * from student;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| NULL | 李四 |
| 2 | 王五 |
+------+------+
3 rows in set (0.00 sec)
第二种形式需要指定列名及被插入的值：
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
例如：
mysql> insert into student(name)
-> values('李四');
Query OK, 1 row affected (0.06 sec)
mysql> select * from student;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| NULL | 李四 |
+------+------+
2 rows in set (0.00 sec)
SQL UPDATE 语句
UPDATE 语句用于更新表中已存在的记录。
SQL UPDATE 语法
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
例如：
mysql> update student
-> set id=3
-> where name='李四';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| 3 | 李四 |
| 2 | 王五 |
+------+------+
3 rows in set (0.00 sec)
SQL DELETE 语句
DELETE 语句用于删除表中的行。
SQL DELETE 语法
DELETE FROM table_name
WHERE some_column=some_value;
例如：
mysql> delete from student
-> where name='李四';
Query OK, 1 row affected (0.10 sec)
mysql> select * from student;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| 2 | 王五 |
+------+------+
2 rows in set (0.00 sec)