【Camel】...系统开始学后端【学】@Jav...

mysql的简单了解

2019-04-14  本文已影响13人  DeeJay_Y

数据库概述

mysql的简单使用

安装和卸载

安装：安装教程

卸载：卸载mysqlServer，删除mysql目录下所有文件，删除mysql所有存储数据。

mysql的一些语句

DDL(定义): create drop alter(修改)
DML(操作): insert update delete
DCL(数据控制语句): 定义访问权限，取消访问权限，安全设置，grant
DQL(查询)：select from子句 where子句

常用的数据库crud操作

创建数据库

mysql -uroot -proot  //(账号密码为root root)

create database DeeJay1;

创建数据库的时候要指定字符集的话可以这么写：create database DeeJay1 character set utf8;
create database DeeJay1 character set utf8 collate 校对规则;

drop database 数据库名;

mysql> drop database DeeJay1;
Query OK, 0 rows affected (0.19 sec)

mysql> show create database DeeJay1;
ERROR 1049 (42000): Unknown database 'deejay1'

常用的表的crud操作

创建表

create table 表名(
    列名 列的类型 约束,
    列名2 列2的类型 约束
);

列的类型(和java做下对比)：

java sql
int int
char/String char/varchar
double double
float float
boolean boolean
date date(YYYY-MM-DD)/time(hh:mm:ss)/datetime(YYYY-MM-DD hh:mm:ss 默认为null)/timestamp(YYYY-MM-DD hh:mm:ss 默认为当前时间)

char: 固定长度 varchar:可变长度
数据库中的列类型还有text(主要用来存储文本)和blob(主要用来存放二进制)

列的约束：
主键约束： primary key
唯一约束： unique
非空约束： not null

创建表举例：

  1. 分析实体： 学生
  2. 学生ID
  3. 姓名
  4. 性别
  5. 年龄
mysql> create table student(studentId int primary key, name varchar(25), gender boolean, age int);
Query OK, 0 rows affected (0.72 sec)

查看表

show tables;

mysql> show tables;
+-----------------+
| Tables_in_base1 |
+-----------------+
| student         |
+-----------------+
1 row in set (0.07 sec)

show create table 表名;

mysql> show create table student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table
                                                                                                                   |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `studentId` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`studentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

desc 表名;

mysql> desc student;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| studentId | int(11)     | NO   | PRI | NULL    |       |
| name      | varchar(25) | YES  |     | NULL    |       |
| gender    | tinyint(1)  | YES  |     | NULL    |       |
| age       | int(11)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

修改表

alter table 表名 修改的类型 列名 列的类型 列的约束;

修改的类型: 添加列(add), 修改列(modify), 修改列名(change), 删除列(drop), 修改表名(rename)

mysql> alter table student add score int not null; // 给student添加一列成绩，为int型，并且不能为null
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| studentId | int(11)     | NO   | PRI | NULL    |       |
| name      | varchar(25) | YES  |     | NULL    |       |
| gender    | tinyint(1)  | YES  |     | NULL    |       |
| age       | int(11)     | YES  |     | NULL    |       |
| score     | int(11)     | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table student modify gender int;// 修改student的gender列的类型为int          
Query OK, 0 rows affected (0.75 sec)                       
Records: 0  Duplicates: 0  Warnings: 0                     
                                                           
mysql> desc student;                                       
+-----------+-------------+------+-----+---------+-------+ 
| Field     | Type        | Null | Key | Default | Extra | 
+-----------+-------------+------+-----+---------+-------+ 
| studentId | int(11)     | NO   | PRI | NULL    |       | 
| name      | varchar(25) | YES  |     | NULL    |       | 
| gender    | int(11)     | YES  |     | NULL    |       | 
| age       | int(11)     | YES  |     | NULL    |       | 
| score     | int(11)     | NO   |     | NULL    |       | 
+-----------+-------------+------+-----+---------+-------+ 
5 rows in set (0.00 sec)                                   

mysql> alter table student change name studentname varchar(25);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| studentId   | int(11)     | NO   | PRI | NULL    |       |
| studentname | varchar(25) | YES  |     | NULL    |       |
| gender      | int(11)     | YES  |     | NULL    |       |
| age         | int(11)     | YES  |     | NULL    |       |
| score       | int(11)     | NO   |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table student drop score;
Query OK, 0 rows affected (0.68 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| studentId   | int(11)     | NO   | PRI | NULL    |       |
| studentname | varchar(25) | YES  |     | NULL    |       |
| gender      | int(11)     | YES  |     | NULL    |       |
| age         | int(11)     | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

rename table student to normalstudent;

alter table student character set gbk;

删除表

drop table 表名;

mysql> drop table student;
Query OK, 0 rows affected (0.24 sec)

mysql> show tables;
Empty set (0.00 sec)

sql对表中数据的CRUD操作

现有如下结构的一个表:

mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| ismale | tinyint(1)  | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

插入数据

insert into 表名(列名1,列名2,列名3) values(值1, 值2, 值3);

mysql> insert into student(id, name, ismale, age) values(1, "zhangsan", true, 20);
Query OK, 1 row affected (0.08 sec)

mysql> select * from student;
+----+----------+--------+------+
| id | name     | ismale | age  |
+----+----------+--------+------+
|  1 | zhangsan |      1 |   20 |
+----+----------+--------+------+
1 row in set (0.00 sec)

对于上述的表中所有列都插入数据的情况，可以简写为：

insert into 表名 values(值1, 值2, 值3);

mysql> insert into student values(2, "lisi", true, 18);
Query OK, 1 row affected (0.08 sec)

mysql> select * from student;
+----+----------+--------+------+
| id | name     | ismale | age  |
+----+----------+--------+------+
|  1 | zhangsan |      1 |   20 |
|  2 | lisi     |      1 |   18 |
+----+----------+--------+------+
2 rows in set (0.00 sec)

也可以灵活指定想插入的列。

批量插入：

insert into 表名 values(值1, 值2, 值3),(值1, 值2, 值3),(值1, 值2, 值3);

单条插入和批量插入的效率问题： 批量会比较快，但是如果某一条数据有问题的话会都失败。

mysql> insert into student values(3, "Yang", true, 23),(4, "Wen", false, 25);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+----------+--------+------+
| id | name     | ismale | age  |
+----+----------+--------+------+
|  1 | zhangsan |      1 |   20 |
|  2 | lisi     |      1 |   18 |
|  3 | Yang     |      1 |   23 |
|  4 | Wen      |      0 |   25 |
+----+----------+--------+------+
4 rows in set (0.00 sec)

删除记录

delete from 表名 [where 条件]

mysql> delete from student where name='zhangsan';
Query OK, 1 row affected (0.06 sec)

mysql> select * from student;
+----+------+--------+------+
| id | name | ismale | age  |
+----+------+--------+------+
|  2 | lisi |      1 |   18 |
|  3 | Yang |      1 |   23 |
|  4 | Wen  |      0 |   25 |
+----+------+--------+------+
3 rows in set (0.00 sec)

delete from student;如果没有where条件，执行这个语句的话会将数据一条一条全部删除。

delete删除数据和truncate删除数据的区别:delete属于DML,是一条一条删除表中数据, truncate属于DDL，先去删除表再去重建表。
如果数据较少那么delete较快，如果数据多，那么truncate较快。

修改表记录

update 表名 set 列名=列的值,列名2=值2 [where 条件]

mysql> update student set name="zhangsan" where id=2; // 将id为2的那一列的name改为zhangsan
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+----------+--------+------+
| id | name     | ismale | age  |
+----+----------+--------+------+
|  2 | zhangsan |      1 |   18 |
|  3 | Yang     |      1 |   23 |
|  4 | Wen      |      0 |   25 |
+----+----------+--------+------+
3 rows in set (0.00 sec)

update student set name="zhangsan",ismale=false;如果后面不跟where条件，那么表中所有的列的name和ismale列都会改。

查询记录

select [distinct] [ * ] [列名1,列名2] from 表名 [where 条件];

distinct: 去除重复的数据 distinct详细用法

首先创建一个商品分类的表：

create table category(
  cid int primary key auto_increment,
  cname varchar(25),
  cdesc varchar(31)
);

然后插入数据：

insert into category values(null, "手机数码", "电子产品");
insert into category values(null, "鞋靴箱包", "江南皮鞋厂打造");
insert into category values(null, "香烟酒水", "芙蓉王，茅台");
insert into category values(null, "酸奶饼干", "哇哈哈");
insert into category values(null, "馋嘴零食", "瓜子花生");

现在这个表的结构为：

mysql> select * from category;
+-----+--------------+-----------------------+
| cid | cname        | cdesc                 |
+-----+--------------+-----------------------+
|   1 | 手机数码     | 电子产品              |
|   2 | 鞋靴箱包     | 江南皮鞋厂打造        |
|   3 | 香烟酒水     | 芙蓉王，茅台          |
|   4 | 酸奶饼干     | 哇哈哈                |
|   5 | 馋嘴零食     | 瓜子花生              |
+-----+--------------+-----------------------+
5 rows in set (0.00 sec)

对于指定列的查询：

mysql> select cname,cdesc from category;
+--------------+-----------------------+
| cname        | cdesc                 |
+--------------+-----------------------+
| 手机数码     | 电子产品              |
| 鞋靴箱包     | 江南皮鞋厂打造        |
| 香烟酒水     | 芙蓉王，茅台          |
| 酸奶饼干     | 哇哈哈                |
| 馋嘴零食     | 瓜子花生              |
+--------------+-----------------------+
5 rows in set (0.00 sec)

再来创建一张商品的表，商品和商品分类的关系为所属关系:

create table product(
  pid int primary key auto_increment,
  pname varchar(25),
  price double, // 价格
  pdate timestamp, // 生产日期
  cno int // 商品分类的id
);

插入一些数据：

insert into product values(null, "小米mix2s", 2700, current_timestamp, 1);
insert into product values(null, "华为p30", 4788, current_timestamp, 1);
insert into product values(null, "阿迪王", 99, current_timestamp, 2);
insert into product values(null, "老村长", 88, current_timestamp, 3);
insert into product values(null, "劲酒", 35, current_timestamp, 3);
insert into product values(null, "小熊饼干", 3, current_timestamp, 4);
insert into product values(null, "卫龙辣条", 1, current_timestamp, 5);
insert into product values(null, "旺旺雪饼", 2, current_timestamp, 5);

当前product表为：

mysql> select * from product;
+-----+--------------+-------+---------------------+------+
| pid | pname        | price | pdate               | cno  |
+-----+--------------+-------+---------------------+------+
|   4 | 小米mix2s    |  2700 | 2019-04-14 13:14:31 |    1 |
|   5 | 华为p30      |  4788 | 2019-04-14 13:14:32 |    1 |
|   6 | 阿迪王       |    99 | 2019-04-14 13:14:32 |    2 |
|   7 | 老村长       |    88 | 2019-04-14 13:14:32 |    3 |
|   8 | 劲酒         |    35 | 2019-04-14 13:14:32 |    3 |
|   9 | 小熊饼干     |     3 | 2019-04-14 13:14:32 |    4 |
|  10 | 卫龙辣条     |     1 | 2019-04-14 13:14:32 |    5 |
|  11 | 旺旺雪饼     |     1 | 2019-04-14 13:14:32 |    5 |
+-----+--------------+-------+---------------------+------+
8 rows in set (0.00 sec)

sql语句的编写顺序和执行顺序

上一篇 下一篇

猜你喜欢

热点阅读