MySQL的基础语法

1.创建数据库
创建数据库lol
mysql> CREATE DATABASE lol;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lol |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql>

创建数据库如果存在则不创建,不会报错
mysql> CREATE DATABASE IF NOT EXISTS lol;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>

创建数据库完整的写法
mysql> CREATE DATABASE IF NOT EXISTS lol2 DEFAULT CHARSET UTF8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW CREATE DATABASE lol2;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| lol2 | CREATE DATABASE lol2 /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

2.删除数据库lol 删除指定的数据库,创建和删除database没有s复数的,查询有s
mysql> DROP database lol;
Query OK, 0 rows affected (0.00 sec)

mysql>

4.查看数据库里面有多少张表,以及查看user信息
mysql> show tables;

4.1.查看user数据表的信息
查看数据表的结构
mysql> desc user;

查看user表中的所有信息

• 表示所有的字段的意思
  mysql> select * from user;

指定字段查询 user,host,password
mysql> select user,host,password from user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *A23AD8335836A5AB6123EFE033BF7345077222F3 |
| root | master01 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | master01 | |
| root | 10.0.0.% | *A23AD8335836A5AB6123EFE033BF7345077222F3 |
+------+-----------+-------------------------------------------+
7 rows in set (0.00 sec)

6.查询当前数据库的字符集信息
mysql> show variables like 'char%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /application/mysql-5.6.40/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)