学习mysql_day1

数据库的操作

链接数据库

mysql -u用户名 -p密码 -h数据库ip -p端口号
一般使用-p后面不写密码 直接-p 会弹出输入密码

[root@instance-s3gy70y7 ~]# mysql -uroot -p
Enter password: ------------这个地方会提示输入密码
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 168582
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

退出数据库

quit

MariaDB [(none)]> quit
Bye

使用数据库

use

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

查看当前使用的数据库

select database();

MariaDB [mysql]> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

显示数据库版本

select version();

MariaDB [mysql]> select version();
+----------------+
| version()      |
+----------------+
| 5.5.60-MariaDB |
+----------------+
1 row in set (0.00 sec)

显示时间

三种 方式 ：
select now();
select sysdate();
select current_date;

MariaDB [mysql]> select now() ;
+---------------------+
| now()               |
+---------------------+
| 2019-10-25 17:18:55 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [mysql]> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2019-10-25 17:19:04 |
+---------------------+
1 row in set (0.00 sec)
MariaDB [mysql]> select current_date;
+--------------+
| current_date |
+--------------+
| 2019-10-25   |
+--------------+
1 row in set (0.00 sec)

创建数据库 指定字符集 不是 utf-8

CREATE DATABASE 数据库名 charset=utf8;

MariaDB [(none)]> CREATE DATABASE test_demo1 charset=utf8; 
Query OK, 1 row affected (0.00 sec)

查看数据库的创建语句

show create database 数据库名;

MariaDB [mysql]> show create database test_demo1;
+------------+---------------------------------------------------------------------+
| Database   | Create Database                                                     |
+------------+---------------------------------------------------------------------+
| test_demo1 | CREATE DATABASE `test_demo1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

删除数据库

drop database 数据库名;

MariaDB [mysql]> drop database test_demo1;
Query OK, 0 rows affected (0.00 sec)

数据表的操作

查看当前数据库中所有表

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

环境创建

创建表之前我们先创建一个测试库 并使用该库

MariaDB [mysql]> create database test_demo2 charset=utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> use test_demo2;
Database changed

创建表时注意

auto_increment表示自动增长
create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);
多个约束 不分先后顺序
enum 表示枚举
最后一个字段不要添加逗号

创建students表

create table students(
	id int unsigned primary key auto_increment not null,
	name varchar(20) default '',
	age tinyint unsigned default 0,
	height decimal(5,2),
	gender enum('男','女','中性','保密') default '保密',
	cls_id int unsigned default 0,
	is_delete bit default 0
);

查看表的创建语句

show create tables 表名;

MariaDB [test_demo2]> show create table students;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                      |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT '',
  `age` tinyint(3) unsigned DEFAULT '0',
  `height` decimal(5,2) DEFAULT NULL,
  `gender` enum('男','女','中性','保密') DEFAULT '保密',
  `cls_id` int(10) unsigned DEFAULT '0',
  `is_delete` bit(1) DEFAULT b'0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8         |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看表结构

desc 表名;

MariaDB [test_demo2]> desc students;
+-----------+-------------------------------------+------+-----+---------+----------------+
| Field     | Type                                | Null | Key | Default | Extra          |
+-----------+-------------------------------------+------+-----+---------+----------------+
| id        | int(10) unsigned                    | NO   | PRI | NULL    | auto_increment |
| name      | varchar(20)                         | YES  |     |         |                |
| age       | tinyint(3) unsigned                 | YES  |     | 0       |                |
| height    | decimal(5,2)                        | YES  |     | NULL    |                |
| gender    | enum('男','女','中性','保密')       | YES  |     | 保密    |                |
| cls_id    | int(10) unsigned                    | YES  |     | 0       |                |
| is_delete | bit(1)                              | YES  |     | b'0'    |                |
+-----------+-------------------------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

修改表结构 alter

alter ables 表名 modify 列名 类型+约束;

MariaDB [test_demo2]> alter table students modify name varchar(200) default '';
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [test_demo2]> desc students;
+-----------+-------------------------------------+------+-----+---------+----------------+
| Field     | Type                                | Null | Key | Default | Extra          |
+-----------+-------------------------------------+------+-----+---------+----------------+
| id        | int(10) unsigned                    | NO   | PRI | NULL    | auto_increment |
| name      | varchar(200)                        | YES  |     |         |                |
| age       | tinyint(3) unsigned                 | YES  |     | 0       |                |
| height    | decimal(5,2)                        | YES  |     | NULL    |                |
| gender    | enum('男','女','中性','保密')       | YES  |     | 保密    |                |
| cls_id    | int(10) unsigned                    | YES  |     | 0       |                |
| is_delete | bit(1)                              | YES  |     | b'0'    |                |
+-----------+-------------------------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

修改表-添加字段

alter table 表名 add 列名 类型+约束;

MariaDB [test_demo2]> alter table students add phone_nb int(11);
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test_demo2]> desc students;
+-----------+-------------------------------------+------+-----+---------+----------------+
| Field     | Type                                | Null | Key | Default | Extra          |
+-----------+-------------------------------------+------+-----+---------+----------------+
| id        | int(10) unsigned                    | NO   | PRI | NULL    | auto_increment |
| name      | varchar(200)                        | YES  |     |         |                |
| age       | tinyint(3) unsigned                 | YES  |     | 0       |                |
| height    | decimal(5,2)                        | YES  |     | NULL    |                |
| gender    | enum('男','女','中性','保密')       | YES  |     | 保密    |                |
| cls_id    | int(10) unsigned                    | YES  |     | 0       |                |
| is_delete | bit(1)                              | YES  |     | b'0'    |                |
| phone_nb  | int(11)                             | YES  |     | NULL    |                |
+-----------+-------------------------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

删除表

drop tables 表名;

MariaDB [test_demo2]> drop tables students;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test_demo2]> show tables;
Empty set (0.00 sec)

数据增删改查(curd)

增加数据

三钟方法:
1:insert [into] 表名 values (值1,值2,…) --指定列插入 值和列一一对应
2:insert into 表名 (列1,…) values(值1,…) – 多行插入 批量插入
3:insert into 表名 (列1,…) values (值1,…),(值1,…), --和第一个方法类似 多个中间用豆号隔开

MariaDB [test_demo2]> insert into students values(0,'mack',13,175.22,2,0,0);
Query OK, 1 row affected (0.00 sec)
MariaDB [test_demo2]> insert into students (name,age,height,gender) values ('dylan',25,160.90,1);
Query OK, 1 row affected (0.00 sec)
MariaDB [test_demo2]> insert into students values (0,'xiaozhang',19,150.00,3,0,0),(0,'xiaohua',33,179.00,1,0,0);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
MariaDB [test_demo2]> select * from students;
+----+-----------+------+--------+--------+--------+-----------+
| id | name      | age  | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
|  1 | mack      |   13 | 175.22 | 女     |      0 |           |
|  2 | dylan     |   25 | 160.90 | 男     |      0 |           |
|  3 | xiaozhang |   19 | 150.00 | 中性   |      0 |           |
|  4 | xiaohua   |   33 | 179.00 | 男     |      0 |           |
+----+-----------+------+--------+--------+--------+-----------+
4 rows in set (0.00 sec)

修改 update

where 表示修改的范围
update 表名 set 列1=值1,列2=值2… where 条件
全表更新 不加where 条件 这个危险

MariaDB [test_demo2]> update students set age='20';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [test_demo2]> select * from students;
+----+-----------+------+--------+--------+--------+-----------+
| id | name      | age  | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
|  1 | mack      |   20 | 175.22 | 女     |      0 |           |
|  2 | dylan     |   20 | 160.90 | 男     |      0 |           |
|  3 | xiaozhang |   20 | 150.00 | 中性   |      0 |           |
|  4 | xiaohua   |   20 | 179.00 | 男     |      0 |           |
+----+-----------+------+--------+--------+--------+-----------+
4 rows in set (0.00 sec)
MariaDB [test_demo2]> update students set name='make11111' where id='1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test_demo2]> select * from students;
+----+-----------+------+--------+--------+--------+-----------+
| id | name      | age  | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
|  1 | make11111 |   20 | 175.22 | 女     |      0 |           |
|  2 | dylan     |   20 | 160.90 | 男     |      0 |           |
|  3 | xiaozhang |   20 | 150.00 | 中性   |      0 |           |
|  4 | xiaohua   |   20 | 179.00 | 男     |      0 |           |
+----+-----------+------+--------+--------+--------+-----------+
4 rows in set (0.00 sec)

删除 delete

DELETE FROM tbname [where 条件判断]
查询有哪些学生没有被删除

MariaDB [test_demo2]> delete from students where id='3';
Query OK, 1 row affected (0.01 sec)

MariaDB [test_demo2]> select * from students;
+----+-----------+------+--------+--------+--------+-----------+
| id | name      | age  | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
|  1 | make11111 |   20 | 175.22 | 女     |      0 |           |
|  2 | dylan     |   20 | 160.90 | 男     |      0 |           |
|  4 | xiaohua   |   20 | 179.00 | 男     |      0 |           |
+----+-----------+------+--------+--------+--------+-----------+
3 rows in set (0.00 sec)