-
常见的SQL语句:
1)DDL—数据定义语言(CREATE,ALTER,DROP)–>运维
2)DML—数据操作语言(SELECT,INSTERT,UPDATE,DELETE) -->开发
3)DCL—数据控制语言(GRANT,REVOKE,COMMT,ROLLBACK)–>运维 -
创建数据库:
语法:create database 库名 (注意:库名不可以以数字开头)
MariaDB [(none)]> create database aaa;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
查看创建库时的语句:
MariaDB [(none)]> show create database aaa\G;
*************************** 1. row ***************************
Database: aaa
Create Database: CREATE DATABASE `aaa` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)
创建一个名为jyy_gbk字符集为gbk的数据库;
MariaDB [(none)]> create database jyy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| jyy_gbk |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
- 删除数据库:
语法:drop database 数据库名;
MariaDB [(none)]> drop database jyy_gbk;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
- 连接数据库:
命令:use 数据库名 //相当于linux下的cd切换目录,use是切换数据库 - 查看各项信息:
select database(); //显示当前所在的数据库;相当于linux下面的pwd
select version(); //查看数据库版本;
select user(); //查看当前数据库用户;
select now(); //查看当前时间;
show tables; //查看数据库的表里的信息;
6.创建MySQL用户及赋予用户权限
GRANT ALL ON db_name.* TO ‘jyy’@’localhost’ IDENTIFIED BY ‘redhat’;
grant:授权命令;
all:对应关系;
on db_name.*:授权的库和表,*表示所有;
to ‘jyy’@‘localhost’:授权的用户和主机域;
identified by ‘redhat’:设置用户密码为redhat
MariaDB [(none)]> grant all on mysql.* to 'jyy'@'localhost' identified by 'redhat';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for 'jyy'@'localhost';
+------------------------------------------------------------------------------------------------------------+
| Grants for jyy@localhost |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jyy'@'localhost' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT ALL PRIVILEGES ON `mysql`.* TO 'jyy'@'localhost' |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
刷新授权表:
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
收回权限:
revoke all on mysql.* to ‘jyy’@‘localhost’ identified by ‘redhat’;
注意:收回的权限必须要与授予的权限相匹配
6.表的建立:
语法:
create table 表名(字段名1 类型1,字段名2 类型2,…);
MariaDB [aaa]> create table student(id int(4) NOT NULL,name char(20) NOT NULL,age tinyint(2) NOT NULL DEFAULT 0,dept varchar(16) DEFAULT NULL);
Query OK, 0 rows affected (0.00 sec)
MariaDB [aaa]> show tables;
+---------------+
| Tables_in_aaa |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)
注意:表如果没有指定字符集,则继承库的字符集;
查看创建的表的过程:
MariaDB [aaa]> show create table student\G;
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(4) NOT NULL,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
MySQL常用的数据类型:
1)INT[(M)]型:正常大小整数类型;
2)CHAR(M)型:定长字符串类型,当存储时,总是用空格填满右边到指定的长度;
3)VARCHAR型:变长字符串类型
查看表结构:
desc 表名;
MariaDB [aaa]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
7.插入数据
语法:insert into 表名(字段名1,字段名2…) varlues(数值1,数值2…)
MariaDB [aaa]> insert into student(id,name,age) values(1,'jyy',19),(2,'aaa',2),(3,'bbb',3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
8.查询表的数据:
语法:select 字段1,字段2… from 表名 where 判断条件;
MariaDB [aaa]> select id,name,age from student where id > 1;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 2 | aaa | 2 |
| 3 | bbb | 3 |
+----+------+-----+
2 rows in set (0.00 sec)
升序查看数据:
MariaDB [aaa]> select id,name,age from student order by age asc;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 2 | aaa | 2 |
| 3 | bbb | 3 |
| 1 | jyy | 19 |
+----+------+-----+
3 rows in set (0.00 sec)
倒序查看数据:
MariaDB [aaa]> select id,name,age from student order by age desc;+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | jyy | 19 |
| 3 | bbb | 3 |
| 2 | aaa | 2 |
+----+------+-----+
3 rows in set (0.00 sec)
8.修改表的数据
语法:update 表名 set 字段=新值,… where 条件(一定要注意条件)
MariaDB [aaa]> update student set age=30 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [aaa]> select id,name,age from studnet;
ERROR 1146 (42S02): Table 'aaa.studnet' doesn't exist
MariaDB [aaa]> select id,name,age from student;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | jyy | 19 |
| 2 | aaa | 30 |
| 3 | bbb | 3 |
+----+------+-----+
3 rows in set (0.00 sec)
!!!防止不加where条件误操作的方法:
在mysql命令加上选项-U后,当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时,mysql程序就会拒绝执行
登录数据库时:
mysql -uroot -predhat -U
还可以做命令别名操作:
alias ‘mysql’=‘mysql -U’
“echo alias mysql=’mysql -U’” >>/etc/profile
9.删除表中的数据:
语法:delete from 表名 where 匹配条件;
MariaDB [aaa]> delete from student where id=3;
Query OK, 1 row affected (0.00 sec)
MariaDB [aaa]> select id,name,age from student;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | jyy | 19 |
| 2 | aaa | 30 |
+----+------+-----+
2 rows in set (0.00 sec)
10.更改字符集乱码:
插入中文乱码时:set names latin1;
show variables\G; //查看变量
show global status; //查看数据库状态
set global key_buffer_size= //不重启修改MySQL
show variables like ‘key_buffer%’; //查询库表的字符集
show character set; //查看字符集
show variables like ‘character_set%’; //查看mysql的当前字符集设置情况
不乱码总结:统一MySQL数据库客户端以及服务器端字符集;
11.数据备份:
语法:mysqldump -u 用户名 -p 数据库名 > 备份的文件名
-B备份:作用是增加创建数据库和连接数据库的命令,表示连接多个库,并且·增加use db,和create database db的信息;要加上。
[root@yang ~]# mysqldump -uroot -p -B aaa > aaa_bak.sql
Enter password:
[root@yang ~]# ll aaa_bak.sql
-rw-r--r-- 1 root root 2081 Jan 21 12:34 aaa_bak.sql
[root@yang ~]#
备份单个表:
语法:mysqldump -u用户名 -p密码 数据库名 表名 > 备份的文件名
[root@yang ~]# mysqldump -uroot -p aaa student > aaa_stud_bak.sql
Enter password:
[root@yang ~]# ll aaa_stud_bak.sql
-rw-r--r-- 1 root root 1945 Jan 21 12:35 aaa_stud_bak.sql
[root@yang ~]#
备份多个表:
语法:mysqldump -u用户名 -p密码 数据库 表名1 表名2 > 备份的文件名
备份所有的库和表:
语法:mysqldump -u用户名 -p密码 -A -B --events | gzip > 备份的文件名
[root@yang ~]# mysqldump -uroot -p -A -B --events|gzip > all_bak.sql.gzEnter password:
[root@yang ~]# ll
total 140
-rw-r--r-- 1 root root 139816 Jan 21 12:38 all_bak.sql.gz
mysqldump的关键参数说明:
关键参数:mysqldump –help
1、-B指定多个库,增加建库语句和use语句。
2、–compact去掉注释,适合调试输出,生产不用。
3、-A备份所有库。
4、-F刷新binlog日志。
5、–master-data增加binlong日志文件名以及对应的位置点。
6、-x,锁表。
7、-l,只读锁表。
8、-d,只备份表结构。
9、-t,只备份数据。
10、–single-transaction,适合innodb事务数据库备份。
12.恢复数据
MariaDB [(none)]> drop database aaa;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> source aaa_bak.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
MariaDB [aaa]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [aaa]>
MariaDB [aaa]> select * from aaa.student;
+----+------+-----+------+
| id | name | age | dept |
+----+------+-----+------+
| 1 | jyy | 19 | NULL |
| 2 | aaa | 30 | NULL |
+----+------+-----+------+
2 rows in set (0.00 sec)