MySQL基础语法

原创已于 2022-05-12 22:12:29 修改 · 132 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2022-05-11 22:37:13 首次发布

MySQL 专栏收录该内容

7 篇文章

订阅专栏

MySQL基础

在这里插入图片描述

什么是数据库？

就是存储数据库的仓库

之前怎么存储数据?
	1)定义变量,类似局部变量,随着方法调用而结束;
	2)容器存储:
			数组--->查询快,增删慢
			集合--->单列集合Collection/双列集合Map
					长度可变,
							List<Student>
			字符串缓冲区:StringBuffer/StringBuilder  
						最终可能需要将字符串缓冲区类型---String
						
   3)IO流
   		Input
   		Output  
   			可以将内容存储磁盘上 文件中 (读写复制---很耗时)
	
   4)数据库来存储数据
   		  执行效率高
   		  底层有树结构在里面
   		  数据库支持 "事务",来解决 业务中存在问题  "转账操作"!
   		  即使关机了,数据永久保存!

数据库的分类

关系型数据库:
    书写sql语句  (Structure Query Language:结构化查询语言)
    	oracle :收费  大型公司企业居多(西安银行,汇丰,神州数码...)
    			买数据库,买oracle第三方服务...
		
mysql:免费  中小型公司使用居多
    sqlserver:跟.net平台兼容性比较好一些
    sqllite: 轻量级的关系数据库,嵌入式里面居多
    mariaDB:跟mysql同级别的
		
非关系型数据库:NoSQL数据库
    键值(Key-Value)存储数据库 : redis 做数据缓存------ 分布式系统架构 使用redis 分布式缓存
    文档类型数据库 : MongoDb

MySQL数据库的安装

MYSQL 8.0 数据库安装与配置教程参考文章

检查MySQL是否安装成功

安装mysql之后：
方式一：登录 dos窗口登录
方式二：方式登录 mysql自带的客户端登录

C:\Users\Administrator>mysql -uroot -p
Enter password: ******   
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.5.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

数据库的DDL语句(数据库的定义语句)之库的操作

-- :普通注释 当行注释
/* mysql的多行注释*/
#特殊注释
-- 查询当前mysql中自带的所有库有哪些
库在我们电脑磁盘上----> 文件夹
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |   mysql的默认配置库
| mysql              |   有user表 (管理员用户表) :root用户就在这个库中
| performance_schema |   mysql其他库(性能相关)
| test               |   测试库,但是不用它,自己创建新的库
+--------------------+
4 rows in set (0.00 sec)

-- 创建库
-- create database 库名;
mysql> create database myEE_2203 ;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myee_2203          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)


-- create database if not exists 库名;
mysql> create database if not exists  ee2203;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ee2203             |
| myee_2203          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)


-- 查询创建库的字符集
-- show create database  库名;
mysql> show create database myee_2203;
+-----------+--------------------------------------------------------------------+
| Database  | Create Database                                                    |
+-----------+--------------------------------------------------------------------+
| myee_2203 | CREATE DATABASE `myee_2203` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 修改库的字符集
-- alter database 库名 default character set 字符集名称;
mysql> alter database myee_2203 default character set gbk;
Query OK, 1 row affected (0.00 sec)

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

-- 删除库
-- drop database 库名 ;
mysql> drop database ee2203;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myee_2203          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

-- drop database if exists 库名 ; 如果存在这个库删除
mysql> drop database if exists myee_2203;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MySQL常见的数据类型

int：整数类型 默认最大长度11位字符,给int类型的数据的时候,当前存储的是值的真实长度
	举例：
		年龄字段age  int类型   			
	int(字符数)：整数类型，给int(3)这个值的时候，实际存储3位，但是赋值的时候不够3位  (使用很少)
		id字段 1-----int(3) -------------------001
     				
varchar(最大支持255个长度): 	字符串类型 
	指定varchar(指定长度)
date:仅仅是日期类型
datetime:日期+时间类型 
timestap:时间戳 (举例:管理员添加一个商品,商品上架的时间:当前系统瞬时时间 :2021-5-11 16:50分)
double:小数类型 
double(几位数,小数点后保留的位数)

举例:
	double(4,2): 4位,小数点后保留2位
	clob:大字符类型  
		支持 "大文本"
	blob:大字节类型
		最大支持4G

数据库DDL语句(数据库定义语句)之建表,修改表,查询表,删除表…

-- 建表之前,必须使用哪个库
-- use 库名;
mysql> use ee_2203;
Database changed
mysql>
/*
  create table 表名(
  		字段名称1 字段类型1,
  		字段名称2 字段类型2,
  		...
  		...
  		字段名称n 字段类型n
  ) ;
*/
mysql> create table student(
    -> id int,
    -> name varchar(10),
    -> age int,
    -> gender varchar(2),
    -> address varchar(50),
    -> socre double(3,1)
    -> );
    Query OK, 0 rows affected (0.02 sec)
    
-- 查询当前库中有哪些表
-- show tables ;
mysql> show tables ;
+-------------------+
| Tables_in_ee_2203 |
+-------------------+
| student           |
+-------------------+
1 row in set (0.00 sec)

-- 查看表的结构 
-- desc 表名;
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| gender  | varchar(2)  | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| socre   | double(3,1) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

-- 修改表的字段名称 
-- alter table 表名 change  旧字段名称 新的字段名称 以前的字段数据类型;

mysql> alter table student change gender sex varchar(2) ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| sex     | varchar(2)  | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| socre   | double(3,1) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

-- 修改表的字段类型 modify
-- alter table 表名 modify 字段名称 新的字段类型 ;
mysql> alter table student modify address varchar(100) ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | YES  |     | NULL    |       |
| name    | varchar(10)  | YES  |     | NULL    |       |
| age     | int(11)      | YES  |     | NULL    |       |
| sex     | varchar(2)   | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| socre   | double(3,1)  | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

-- 修改表:给表中添加一个新的字段
-- alter table 表名 add 字段名称 字段类型;
mysql> alter table student add description varchar(200) ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 复制一张表
-- create table  新表名  like 旧表名;
mysql> create table teachear like student ;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+-------------------+
| Tables_in_ee_2203 |
+-------------------+
| student           |
| teachear          |
+-------------------+
2 rows in set (0.00 sec)

-- 删除表
-- drop table 表名;
-- drop table if exists 表名;
mysql> drop table if exists teachear;
Query OK, 0 rows affected (0.01 sec)

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

练习：

一.表操作语法 
	创建一个库:MyEE_2203
	1)创建一个员工表
		字段     属性
		id        整形
		name     字符串（长度为20）
		gender    字符串（长度为2）
		birthday  日期型(date)
		email	   字符串（长度为10）
		remark     字符串（长度为50）

	2).修改表练习
		2.1 在员工表基础上增加age列
		2.2 修改email列长度为50
		2.3 删除remark列
		2.4 列名name修改为username
		
	1)给员工表添加三个员工信息
	2)通过指定id删除员工
	3)查询所有员工信息

--创建一个库:MyEE_2203
mysql> create database MyEE_2203;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cat                |
| exam               |
| mm_wiki            |
| myee_2203          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
8 rows in set (0.00 sec)

--1)创建一个员工表
--		字段     属性
--		id        整形
--		name     字符串（长度为20）
--		gender    字符串（长度为2）
--		birthday  日期型(date)
--		email	   字符串（长度为10）
--		remark     字符串（长度为50）
-- 建表之前,必须使用哪个库
-- use 库名;
mysql> use myee_2203;
Database changed

mysql> create table worker(
    -> id int,
    -> name varchar(20),
    -> gender varchar(2),
    -> birthday date,
    -> email varchar(10),
    -> remark varchar(50)
    -> );
Query OK, 0 rows affected (0.04 sec)

--查看表
mysql> show tables;
+---------------------+
| Tables_in_myee_2203 |
+---------------------+
| worker              |
+---------------------+
1 row in set (0.00 sec)

--查看表结构
mysql> desc worker;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| gender   | varchar(2)  | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| email    | varchar(10) | YES  |     | NULL    |       |
| remark   | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

--2).修改表练习
--		2.1 在员工表基础上增加age列
mysql> alter table worker add age int;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc worker;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| gender   | varchar(2)  | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| email    | varchar(10) | YES  |     | NULL    |       |
| remark   | varchar(50) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

--		2.2 修改email列长度为50
mysql> alter table worker modify email varchar(50);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc worker;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| gender   | varchar(2)  | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| email    | varchar(50) | YES  |     | NULL    |       |
| remark   | varchar(50) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

--		2.3 删除remark列
mysql> alter table worker drop remark;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc worker;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| gender   | varchar(2)  | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| email    | varchar(50) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
--		2.4 列名name修改为username
mysql> alter table worker change name username varchar(10);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc worker;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| username | varchar(10) | YES  |     | NULL    |       |
| gender   | varchar(2)  | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| email    | varchar(50) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

--1)给员工表添加三个员工信息
--插入给指定表中插入数据
mysql> insert into worker values (1,'豆三岁','男','1999/7/8','2583160759@qq.com',22);
Query OK, 1 row affected (0.01 sec)

--查询当前表中的数据
mysql> select * from worker;
+------+----------+--------+------------+-------------------+------+
| id   | username | gender | birthday   | email             | age  |
+------+----------+--------+------------+-------------------+------+
|    1 | 豆三岁   | 男     | 1999-07-08 | 2583160759@qq.com |   22 |
+------+----------+--------+------------+-------------------+------+
1 row in set (0.00 sec)

mysql> insert into worker values (2,'柠柠融','女','2000/3/13','414892494@qq.com',21);
Query OK, 1 row affected (0.01 sec)

mysql> insert into worker values (3,'赵大头','女','2022/5/11','123456789@163.com',1);
Query OK, 1 row affected (0.01 sec)

--3)查询所有员工信息
mysql> select * from worker;
+------+----------+--------+------------+-------------------+------+
| id   | username | gender | birthday   | email             | age  |
+------+----------+--------+------------+-------------------+------+
|    1 | 豆三岁   | 男     | 1999-07-08 | 2583160759@qq.com |   22 |
|    2 | 柠柠融   | 女     | 2000-03-13 | 414892494@qq.com  |   21 |
|    3 | 赵大头   | 女     | 2022-05-11 | 123456789@163.com |    1 |
+------+----------+--------+------------+-------------------+------+
3 rows in set (0.00 sec)

--2)通过指定id删除员工
mysql> delete from worker where id=3;
Query OK, 1 row affected (0.01 sec)

mysql> select * from worker;
+------+----------+--------+------------+-------------------+------+
| id   | username | gender | birthday   | email             | age  |
+------+----------+--------+------------+-------------------+------+
|    1 | 豆三岁   | 男     | 1999-07-08 | 2583160759@qq.com |   22 |
|    2 | 柠柠融   | 女     | 2000-03-13 | 414892494@qq.com  |   21 |
+------+----------+--------+------------+-------------------+------+
2 rows in set (0.00 sec)