Linux—MySQL数据库基本操作
一.SQL基本命令
1.SQL语言,是结构化查询语言,在关系型数据库中充当标准语言,用于维护管理数据库,如数据的增删查改
2.SQL分类:
DDL:数据定义语言;DML:数据操纵语言;DQL:数据查询语言;DCL:数据控制语言
二.DDL,数据定义语言
创建数据库,create database 数据库名;
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
创建数据表,create table 表名(字段定义…)
mysql> use school;
Database changed
mysql> create table info (
-> id int(4) not null,
-> name char(10) not null,
-> address varchar(50) default 'nj',
-> score decimal,
-> primary key (id));
Query OK, 0 rows affected (0.00 sec)
mysql> describe info;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| address | varchar(50) | YES | | nj | |
| score | decimal(10,0) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
删除表和库:
mysql>drop table info;
mysql>drop database school;
三.DML:数据操纵语言
使用insert ,插入新的数据
mysql> insert into info (id,name,address,score) values (1,'stu01','bj',88);
Query OK, 1 row affected (0.00 sec)
mysql> insert into info (id,name,address,score) values (2,'stu02','sh',98);
Query OK, 1 row affected (0.00 sec)
mysql> insert into info (id,name,address,score) values (3,'stu03','default',90);
Query OK, 1 row affected (0.00 sec)
mysql> insert into info (id,name,address,score) values (4,'stu04','',60);
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+-------+---------+-------+
| id | name | address | score |
+----+-------+---------+-------+
| 1 | stu01 | bj | 88 |
| 2 | stu02 | sh | 98 |
| 3 | stu03 | nj | 90 |
| 4 | stu04 | | 60 |
+----+-------+---------+-------+
4 rows in set (0.00 sec)
使用update ,替换原有数据
update 数据库名.表名 set 字段1=值1where 条件表达式
mysql> update info set address='hz' where id=4 and name='stu04';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from info;
+----+-------+---------+-------+
| id | name | address | score |
+----+-------+---------+-------+
| 1 | stu01 | bj | 88 |
| 2 | stu02 | sh | 98 |
| 3 | stu03 | nj | 90 |
| 4 | stu04 | hz | 60 |
+----+-------+---------+-------+
4 rows in set (0.00 sec)
使用delete ,删除指定的数据
delete from 表名 where 条件表达式
mysql> delete from info where name='stu04';
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+-------+---------+-------+
| id | name | address | score |
+----+-------+---------+-------+
| 1 | stu01 | bj | 88 |
| 2 | stu02 | sh | 98 |
| 3 | stu03 | default | 90 |
+----+-------+---------+-------+
3 rows in set (0.00 sec)
四.DQL:数据查询语言
select语句,用于从指定的表中查询符合条件的数据记录
select 字段1,字段2,… from 表名 where 条件表达式
mysql> select id,name from info where name='stu02';
+----+-------+
| id | name |
+----+-------+
| 2 | stu02 |
+----+-------+
1 row in set (0.00 sec)
五.DCL:数据控制语言
设置用户权限(用户不存在时,则新建用户)
grant 权限列表 on 数据库名 表名 to 用户名@来源地址 [ identified by ‘密码’]
(如果用户存在,则代表修改密码;如果用户不存在,则代表新建用户)
查看用户的权限:
show grants for 用户名@来源地址
撤销用户的权限:
revoke 权限列表 on 数据库名.表名 from 用户名@来源地址