learn SQL(2)

1.数据库操作

以一个名为t1的db_name为例：
创建：CREATE DATABASE t1;
查询：SHOW CREATE DATABASE t1; SHOW DATABASEs
删除：DROP DATABASE t1;

内容回顾：

2.主键约束：每张数据表只能存在一个主键，主键保证记录的唯一性，主键自动为NOT NULL

3.数据表操作

3.1创建数据表：

例如：

mysql> SHOW DATABASEs;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| alien              |
| aliendatabase      |
| demo               |
| mysql              |
| performance_schema |
| t2                 |
| test               |
+--------------------+
8 rows in set

mysql> use t2;
Database changed
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| t2         |
+------------+
1 row in set

mysql> CREATE TABLE tb1(
    -> username VARCHAR(20),
    -> age TINYINT UNSGINED
    -> salary FLOAT(8,2) UNSIGNED
    -> );
1064 - Erreur de syntaxe près de 'UNSGINED

salary FLOAT(8,2) UNSIGNED

)' à la ligne 5
mysql> CREATE TABLE tb1(
    -> username VARCHAR(20),
    -> age TINYINT UNSIGNED,
    -> salary FLOAT(8,2) UNSIGNED
    -> );
Database changed
mysql> 

3.2查看数据表和数据表的结构

mysql> SHOW TABLES;
+--------------+
| Tables_in_t2 |
+--------------+
| tb1          |
+--------------+
1 row in set

mysql> SHOW COLUMNS FROM tb1;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
| salary   | float(8,2) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
3 rows in set

3.3记录的插入与查找

插入记录：INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

mysql> SHOW COLUMNS FROM tb1;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
| salary   | float(8,2) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
3 rows in set
mysql> INSERT tb1 VALUES('TOM',25,5000);//插入一条记录：tom，25岁，工资5000
Query OK, 1 row affected

记录查找：SELECT LastName,FirstName FROM Persons

mysql> SELECT * FROM tb1;
+----------+-----+--------+
| username | age | salary |
+----------+-----+--------+
| TOM      |  25 |   5000 |
+----------+-----+--------+
1 row in set

3.4空值与非空：NULL,字段值可以为空；NOT NULL:字段值禁止为空