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;

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

©️2020 CSDN 皮肤主题: 游动-白 设计师:上身试试 返回首页