sql语句使用
sql语句的分类
1、操作数据库 --- DDL
1.1创建:CREATE DATABASE 语句用于创建新的数据库:
mysql> create Database mydb1;
Query OK, 1 row affected (0.03 sec)
mysql> create Database mydb2 character SET GBK;
Query OK, 1 row affected (0.04 sec)
mysql> create Database mydb3 character set GBK collate gbk_chinese_ci;
Query OK, 1 row affected (0.05 sec)
1.2、查看
查看当前数据库服务器中的所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb2 |
| mydb3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
查看前面创建的mydb2数据的定义信息
mysql> show Create database mydb2;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| mydb2 | CREATE DATABASE `mydb2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+---------------------------------------------------------------+
1 row in set (0.03 sec)
1.3、修改
查看服务器中的数据库,并把mydb2的字符集改为utf8
mysql> alter database mydb2 character set utf8;
Query OK, 1 row affected (0.10 sec)
mysql> show create database mydb2;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| mydb2 | CREATE DATABASE `mydb2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.04 sec)
1.4、删除
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb2 |
| mydb3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.04 sec)
mysql> drop database mydb3;
Query OK, 0 rows affected (0.14 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.04 sec)
1.5、
查看当前使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.03 sec)
切换数据库
mysql> use mydb2;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mydb2 |
+------------+
1 row in set (0.03 sec)
2、操作表 --- DDM
2.1、CREATE TABLE语句创建新表。
语法:
CREATE TABLE 表名(
字段1 字段类型(长度) 约束,
字段2 字段类型(长度) 约束,
...
字段n 字段类型(长度) 约束,
);
mysql> create table Employees(
-> id int not null,
-> age int not null,
-> first varchar(255),
-> last varchar(255),
-> primary key(id));
Query OK, 0 rows affected (0.18 sec)
2.2 、查看表
查看数据库中所有表
mysql> show tables;
+-----------------+
| Tables_in_mydb2 |
+-----------------+
|