Mysql基础教程(一)

一.认识SQL

SQL(Structure Query Language)语言是数据库的核心语言，SQL是一个标准的数据库语言，是面向集合的描述性非过程化语言。它功能强，效率高，简单易学易维护，使用方便，已经成为了数据库操作的基础，并且现在几乎所有的关系型数据库均支持SQL。

二. SQL语言的分类

SQL语言共分为四大类：DQL(数据查询语言)，DML(数据操纵语言)，DDL(数据定义语言)，DCL(数据控制语言)。

三.SQL语法

1、创建新数据库

CREATE DATABASE [数据库名称]
mysql> CREATE DATABASE MYTEST;
Query OK, 1 row affected (0.42 sec)

2、切换数据库

USE MYTEST
mysql> USE MYTEST
Database changed

3、删除数据库

DROP DATABASE MYTEST
mysql> DROP DATABASE MYTEST;
Query OK, 0 rows affected (0.39 sec)

4、创建数据库表

#第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：
INSERT INTO table_name
VALUES (value1,value2,value3,...);
#第二种形式需要指定列名及被插入的值：
mysql> CREATE TABLE TEST1( ID INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(50) NOT NULL, AGE INT NOT NULL, SEX ENUM('M','F') NOT NULL DEFAULT 'F', HOBBY SET('BOOK','MUSIC','GAME') NOT NULL DEFAULT 'BOOK,MUSIC'  );
Query OK, 0 rows affected (0.48 sec)

5、查看表结构

mysql> DESC TEST1;
+-------+----------------------------+------+-----+------------+-------+
| Field | Type                       | Null | Key | Default    | Extra |
+-------+----------------------------+------+-----+------------+-------+
| ID    | int                        | NO   | PRI | NULL       |       |
| NAME  | varchar(50)                | NO   |     | NULL       |       |
| AGE   | int                        | NO   |     | NULL       |       |
| SEX   | enum('M','F')              | NO   |     | F          |       |
| HOBBY | set('BOOK','MUSIC','GAME') | NO   |     | BOOK,MUSIC |       |
+-------+----------------------------+------+-----+------------+-------+
5 rows in set (0.00 sec)

6、删除表

mysql> DROP TABLE TEST1;                                                                                
Query OK, 0 rows affected (0.02 sec)

7、向表中插入数据

mysql> INSERT INTO TEST1(NAME,AGE,SEX,HOBBY)VALUES ('ALEX',20,'M','BOOK,MUSIC'), ('JACK',21,'F','GAME,MUSIC');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

8、查看表中数据

mysql> SELECT * FROM TEST1;
+----+------+-----+-----+------------+
| ID | NAME | AGE | SEX | HOBBY      |
+----+------+-----+-----+------------+
|  1 | ALEX |  20 | M   | BOOK,MUSIC |
|  2 | JACK |  21 | F   | MUSIC,GAME |
+----+------+-----+-----+------------+
2 rows in set (0.00 sec)
#查看某一字段
SELECT [字段名1]，[字段名1] FROM [表名]
SELECT NAME FROM TEST1

9、SQL SELECT DISTINCT 语句

#在表中，一个列可能会包含多个重复值，有时您也许希望仅仅列出不同（distinct）的值。DISTINCT 关键词用于返回唯一不同的值，通俗来说就是去除某一列的重复元素
mysql> SELECT DISTINCT AGE FROM TEST1; #对TEST1表中的AGE字段去重
+-----+
| AGE |
+-----+
|  20 |
|  21 |
+-----+
2 rows in set (0.00 sec)

10、SQL WHERE 子句

#WHERE 子句用于过滤记录
mysql> SELECT NAME FROM TEST1 WHERE ID=1; #过滤id=1的成员姓名
+------+
| NAME |
+------+
| ALEX |
+------+
1 row in set (0.00 sec)

11、SQL AND & OR 运算符

mysql> SELECT NAME FROM TEST1 WHERE SEX='F' AND AGE>20;
+------+
| NAME |
+------+
| JACK |
+------+
1 row in set (0.00 sec)
#如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
mysql> SELECT NAME FROM TEST1 WHERE SEX='F' OR AGE>20;
+------+
| NAME |
+------+
| JACK |
| tom  |
+------+
2 rows in set (0.00 sec)
#如果第一个条件和第二个条件有一个条件成立，则 OR 运算符显示一条记录。

12、SQL ORDER BY 关键字

#ORDER BY 关键字用于对结果集进行排序。
mysql> SELECT *  FROM TEST1 ORDER BY AGE; #默认为升序
+----+------+-----+-----+------------+
| ID | NAME | AGE | SEX | HOBBY      |
+----+------+-----+-----+------------+
|  1 | ALEX |  20 | M   | BOOK,MUSIC |
|  3 | tom  |  20 | F   | BOOK,MUSIC |
|  2 | JACK |  21 | F   | MUSIC,GAME |
+----+------+-----+-----+------------+
3 rows in set (0.00 sec)
#ASC 升序
mysql> SELECT *  FROM TEST1 ORDER BY AGE ASC;
+----+------+-----+-----+------------+
| ID | NAME | AGE | SEX | HOBBY      |
+----+------+-----+-----+------------+
|  1 | ALEX |  20 | M   | BOOK,MUSIC |
|  3 | tom  |  20 | F   | BOOK,MUSIC |
|  2 | JACK |  21 | F   | MUSIC,GAME |
+----+------+-----+-----+------------+
3 rows in set (0.00 sec)
#降序 DESC
mysql> SELECT *  FROM TEST1 ORDER BY AGE DESC;
+----+------+-----+-----+------------+
| ID | NAME | AGE | SEX | HOBBY      |
+----+------+-----+-----+------------+
|  2 | JACK |  21 | F   | MUSIC,GAME |
|  1 | ALEX |  20 | M   | BOOK,MUSIC |
|  3 | tom  |  20 | F   | BOOK,MUSIC |
+----+------+-----+-----+------------+
3 rows in set (0.00 sec)
#对多列进行排序
mysql> SELECT *  FROM TEST1 ORDER BY AGE ,ID;
+----+------+-----+-----+------------+
| ID | NAME | AGE | SEX | HOBBY      |
+----+------+-----+-----+------------+
|  1 | ALEX |  20 | M   | BOOK,MUSIC |
|  3 | tom  |  20 | F   | BOOK,MUSIC |
|  2 | JACK |  21 | F   | MUSIC,GAME |
+----+------+-----+-----+------------+
3 rows in set (0.00 sec)

13、SQL UPDATE 语句

#UPDATE table_nameSET column1=value1,column2=value2,...WHERE some_column=some_value;
mysql> UPDATE TEST1 SET NAME='SB' WHERE ID=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
+----+------+-----+-----+------------+
| ID | NAME | AGE | SEX | HOBBY      |
+----+------+-----+-----+------------+
|  1 | SB   |  20 | M   | BOOK,MUSIC |
|  2 | JACK |  21 | F   | MUSIC,GAME |
|  3 | tom  |  20 | F   | BOOK,MUSIC |
+----+------+-----+-----+------------+

14、SQL DELETE 语句

#DELETE 语句用于删除表中的记录。
mysql> DELETE FROM TEST1 WHERE ID=2;
Query OK, 1 row affected (0.42 sec)
+----+------+-----+-----+------------+
| ID | NAME | AGE | SEX | HOBBY      |
+----+------+-----+-----+------------+
|  1 | SB   |  20 | M   | BOOK,MUSIC |
|  3 | tom  |  20 | F   | BOOK,MUSIC |
+----+------+-----+-----+------------+
#删除表中所有数据
DELETE FROM table_name;  或  DELETE * FROM table_name;

在删除记录时要格外小心哦！