在MySQL中最基础运用的就是增删改查命令，这篇文章我将详细的介绍mysqi的基础命令。
首先我们来了解一下SQL结构化查询语言：它是一种关系型数据库中的数据进行定义和操作的语言方法。
SQL结构化查询语言重要的几种：
- DQL (数据查询语言)：用以从表中获得数据，确定数据怎样在应用程序给出的。
例如：(select)。
- DDL（数据定义语言）：用于创建新表或者删除表，为表加索引等操作。
例如：create，drop
- DML (数据操作语言)：用于添加，修改，删除表中的行或者数据
例如：insert,update,delete
- DCL：数据控制语言：它通过语句grant或者revoke获得允许。
下面我们来具体看怎样使用这些语言：
1.登录方式
mysql的登陆方式有三种，如下：
- mysql
- mysql -uroot
- mysql -uroot -pzxc123
-
[root@mysql mysql-5.5.22]# mysql -uroot -p123123 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]>
2. 登录后会有MySQL [(none)]>的提示符，下面我们来修改一下自己的命令提示符：prompt命令
-
MySQL [(none)]> prompt \u@Carrie -> PROMPT set to '\u@Carrie ->' root@Carrie ->
3. 退出MySQL的方式
- quit
- ctrl+c
- ctrl +d
4.修改密码
第一种方式
- mysqladmin -uroot -p123123 password zxc123
-
[root@mysql ~]# mysqladmin -uroot -p123123 password zxc123 [root@mysql ~]# mysql -uroot -p123123 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [root@mysql ~]# mysql -uroot -pzxc123 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]>
第二种方式
- 在数据库中修改命令
-
错误示范：因为密码是加密的，这么直接修改。密码还是用不了。切记
MySQL [Carrie]> select user,host,password from mysql.user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | | root | mysql | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | mysql | | +------+-----------+-------------------------------------------+ 6 rows in set (0.00 sec) MySQL [Carrie]> update mysql.user set password='zxc123' where host='localhost'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0正确示范：加一个函数，让其有加密算法
-
MySQL [Carrie]> update mysql.user set password=password(123123) where host='localhost'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 MySQL [Carrie]> select user,host,password from mysql.user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | | root | mysql | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | | | mysql | | +------+-----------+-------------------------------------------+ 6 rows in set (0.00 sec)
第三种方法：直接用set解决
-
MySQL [Carrie]> set password=password('123123') ; Query OK, 0 rows affected (0.00 sec)
5.创建一个数据库
用create创建数据库
-
MySQL [(none)]> create database Carrie; Query OK, 1 row affected (0.00 sec)
- 数据库默认拉丁文，如果想加中文创建数据库时需要添加charset,例如：
-
MySQL [Carrie]> create table hanjiali (id int(3),name varchar(10)) default charset =utf8;
Query OK, 0 rows affected (0.00 sec)MySQL [Carrie]> insert into hanjiali values (1,'韩佳丽'); Query OK, 1 row affected (0.00 sec) MySQL [Carrie]> select * from hanjiali; +------+-----------+ | id | name | +------+-----------+ | 1 | 韩佳丽 | +------+-----------+ 1 row in set (0.00 sec)
6.查看数据库
用show 查看数据库
-
MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | Carrie | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
7.使用数据库
用use使用数据库
-
MySQL [(none)]> use Carrie; Database changed MySQL [Carrie]>
8.删除数据库Carrie
用drop删除数据库
-
MySQL [Carrie]> drop database Carrie; Query OK, 0 rows affected (0.00 sec)
9.创建数据库中的表
用creat创建表
-
MySQL [Carrie]> create table student (id int(3),name varchar(10),age int(3)); Query OK, 0 rows affected (0.00 sec)
10.查看表字段
表字段就是表中的结构，也是表中的表头信息。
用 desc查看表字段
-
MySQL [Carrie]> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | age | int(3) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
11.删除表
用drop删除表
-
MySQL [Carrie]> drop table student; Query OK, 0 rows affected (0.00 sec)
12.对表结构的增删改方法
- 添加入表字段，用alter table 表名 add进行添加
-
MySQL [Carrie]> alter table student add sex varchar(5); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0
- 添加表字段还可以选定添加的位置，如下
-
MySQL [Carrie]> alter table student add code int(10) after id; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
- 表结构最终成型如下
-
MySQL [Carrie]> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | code | int(10) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | varchar(5) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
- 删除表结构中的内容
-
MySQL [Carrie]> alter table student drop code; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL [Carrie]> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | varchar(5) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
- 修改表结构的类型
-
MySQL [Carrie]> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | varchar(5) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) MySQL [Carrie]> alter table student modify age int(10); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL [Carrie]> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | age | int(10) | YES | | NULL | | | sex | varchar(5) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
- 修改表结构的名称
-
MySQL [Carrie]> alter table student change name name_student varchar(10); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL [Carrie]> desc student; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | name_student | varchar(10) | YES | | NULL | | | age | int(10) | YES | | NULL | | | sex | varchar(5) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
13.修改表名
-
MySQL [Carrie]> alter table student rename to student_data; Query OK, 0 rows affected (0.00 sec) MySQL [Carrie]> desc student; ERROR 1146 (42S02): Table 'Carrie.student' doesn't exist MySQL [Carrie]> desc student_data; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | name_student | varchar(10) | YES | | NULL | | | age | int(10) | YES | | NULL | | | sex | varchar(5) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec
14.对表中数据进行增删改查
- 插入表中的数据
-
MySQL [Carrie]> insert into student_data (id,name_student,age,sex) values (1,'Carrie',18,'woman'); Query OK, 1 row affected (0.00 sec)
MySQL [Carrie]> insert into student_data values (3,'Matin',20,'man');
Query OK, 1 row affected (0.00 sec)此处用了了两种方式插入表信息
- 查看表中数据
-
MySQL [Carrie]> select * from student_data;
+------+--------------+------+-------+
| id | name_student | age | sex |
+------+--------------+------+-------+
| 1 | Carrie | 18 | woman |
| 2 | Susie | 19 | woman |
| 3 | Montin | 20 | man |
| 2 | Susie | 18 | woman |
| 3 | Matin | 20 | man |
+------+--------------+------+-------+
5 rows in set (0.00 sec) - 删除表中数据
-
MySQL [Carrie]> delete from student_data where id=3;
Query OK, 2 rows affected (0.00 sec) MySQL [Carrie]> select * from student_data; +------+--------------+------+-------+ | id | name_student | age | sex | +------+--------------+------+-------+ | 1 | Carrie | 18 | woman | | 2 | Susie | 19 | woman | | 2 | Susie | 18 | woman | +------+--------------+------+-------+ 3 rows in set (0.00 sec)delete from student_data;清空表
- 修改数据
-
MySQL [Carrie]> update student_data set id=1 where id=2; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 MySQL [Carrie]> select * from student_data; +------+--------------+------+-------+ | id | name_student | age | sex | +------+--------------+------+-------+ | 1 | Carrie | 18 | woman | | 1 | Susie | 19 | woman | | 1 | Susie | 18 | woman | +------+--------------+------+-------+ 3 rows in set (0.00 sec)
- 指定查看表中的数据
-
MySQL [Carrie]> select * from student_data; +------+--------------+------+-------+ | id | name_student | age | sex | +------+--------------+------+-------+ | 1 | Carrie | 18 | woman | | 1 | Susie | 19 | woman | | 1 | Susie | 18 | woman | +------+--------------+------+-------+ 3 rows in set (0.00 sec) MySQL [Carrie]> select name_student from student_data where age=18; +--------------+ | name_student | +--------------+ | Carrie | | Susie | +--------------+ 2 rows in set (0.00 sec)
- 去除表中重复的信息
-
MySQL [Carrie]> update student_data set name_student='Carrie' where age=18; Query OK, 1 row affected (0.00 sec) Rows matched: 2 Changed: 1 Warnings: 0 MySQL [Carrie]> select * from student_data; +------+--------------+------+-------+ | id | name_student | age | sex | +------+--------------+------+-------+ | 1 | Carrie | 18 | woman | | 1 | Susie | 19 | woman | | 1 | Carrie | 18 | woman | +------+--------------+------+-------+ 3 rows in set (0.00 sec) MySQL [Carrie]> select distinct *from student_data; +------+--------------+------+-------+ | id | name_student | age | sex | +------+--------------+------+-------+ | 1 | Carrie | 18 | woman | | 1 | Susie | 19 | woman | +------+--------------+------+-------+ 2 rows in set (0.00 sec)
- 给数据排序（默认升序排列，desc降序，asc升序，排序完成后，还可以加limit对数据进行限制，比如limit 2，3只显示2-3行）
-
MySQL [Carrie]> select *from student_data order by age desc; +------+--------------+------+-------+ | id | name_student | age | sex | +------+--------------+------+-------+ | 2 | Susie | 19 | woman | | 1 | Carrie | 18 | woman | | 1 | Carrie | 18 | woman | +------+--------------+------+-------+ 3 rows in set (0.00 sec)
15.聚合函数
常用的求最大，最小，平均数等聚合函数
| 函数名称 | 函数作用 |
|---|---|
| MIN | 查询指定列中的最小值 |
| MAX | 查询指定列中的最大值 |
| COUNT | 查询结果总行数统计 |
| SUM | 求和，返回指定列的总和 |
| AVG | 求平均数，返回指定列的平均值 |
为了后续实例中便于理解，先在 TEST 数据库中创建数据表 STUDENT，其表结构和表数据如下
select a.* from table a;
| id | name | score |
|---|---|---|
| 1 | zhangsan | 98 |
| 2 | lisi | 99 |
| 3 | wangwu | 100 |
- min() 函数
例：在 student 表中查询所有分数的最小值
例：在 student 表中查询所有姓名的最小值
注：min() 函数即可查找数值类型，也可用于字符类型；min() 函数可以判断字母大小，并返回最小的字符或字符串值，字符型数据比较时，按照ASCII码值大小进行比较，从a到z，a的ASCII码最小，z的ASCII码最大； - max() 函数
例：在 student 表中查询所有分数的最大值
例：在 student 表中查询所有姓名的最大值
注：与min() 函数类似 - count() 函数
例：在 student 表中查询所有分数的列表总和
- sum() 函数
例：在 student 表中查询所有分数的总和
- avg() 函数
例：在 student 表中查询所有分数的平均值
16.添加数据库中的用户
-
MySQL [Carrie]> create user 'Carrie'@'licalhost' identified by '123123'; Query OK, 0 rows affected (0.00 sec) MySQL [Carrie]> select user from mysql.user; +--------+ | user | +--------+ | root | | root | | Carrie | | | | root | | | | root | +--------+ 7 rows in set (0.00 sec)
17.删除数据库中的用户
-
MySQL [Carrie]> select user,host from mysql.user; +--------+---------------+ | user | host | +--------+---------------+ | root | 127.0.0.1 | | abc | 192.168.2.100 | | root | ::1 | | Carrie | licalhost | | | localhost | | root | localhost | | | mysql | | root | mysql | +--------+---------------+ 8 rows in set (0.00 sec) MySQL [Carrie]> drop user 'abc'@'192.168.2.100'; Query OK, 0 rows affected (0.00 sec) MySQL [Carrie]> select user,host from mysql.user; +--------+-----------+ | user | host | +--------+-----------+ | root | 127.0.0.1 | | root | ::1 | | Carrie | licalhost | | | localhost | | root | localhost | | | mysql | | root | mysql | +--------+-----------+ 7 rows in set (0.00 sec)
18.给用户授权
-
实例是给主机下的用户授权，如果是远程授权直接将localhost换成可以用的域名或者IP地址或者IP段进行授权。比如：
-
grant all privileges on student_data to Carrie@'192.168.2.%' identified by '123123';
-
grant all privileges on student_data to Carrie@'192.168.2.0/24' identified by '123123';
MySQL [Carrie]> grant all privileges on student_data to Carrie@'localhost' identified by '123123'; 授权 在student_data上的所有权限 给Carrie@'localhost' 确认其密码为123123 Query OK, 0 rows affected (0.00 sec) MySQL [Carrie]> select user,host from mysql.user; +--------+-----------+ | user | host | +--------+-----------+ | root | 127.0.0.1 | | root | ::1 | | Carrie | licalhost | | | localhost | | Carrie | localhost | | root | localhost | | | mysql | | root | mysql | +--------+-----------+ 8 rows in set (0.00 sec) MySQL [Carrie]> show grants for Carrie@localhost; +---------------------------------------------------------------------------------------------------------------+ | Grants for Carrie@localhost | +---------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'Carrie'@'localhost' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' | | GRANT ALL PRIVILEGES ON `Carrie`.`student_data` TO 'Carrie'@'localhost' | +---------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
一般all privileges 都会有哪些权限给用户呢？如下图：
19.刷新权限
-
MySQL [Carrie]> flush privileges; Query OK, 0 rows affected (0.00 sec)
20.撤回权限
用revoke权限将用户权限收回
-
MySQL [Carrie]> revoke select on student_data from Carrie@'localhost'; Query OK, 0 rows affected (0.00 sec) MySQL [Carrie]> flush privileges; Query OK, 0 rows affected (0.00 sec) MySQL [Carrie]> show grants for Carrie@localhost; +----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for Carrie@localhost | +----------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'Carrie'@'localhost' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' | | GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `Carrie`.`student_data` TO 'Carrie'@'localhost' | +----------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
21.数据备份操作
- mysqldump是逻辑备份，将数据以执行语句的形式备份出来到/Carrie的文件中，-B后加 数据库
-
[root@mysql ~]# mysqldump -uroot -p123123 -B Carrie >> /Carrie;
