安装mysql之后
1)方式登录 dos窗口登录
2)方式登录 mysql自带的客户端登录
C:\Users\Administrator>mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands endwith;or \g.
Your MySQL connection id is19
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.
mysql>
4.DDL语句: 数据库操作语句
数据库的DDL语句(数据库的定义语句)之库的操作
-- :普通注释 当行注释/* mysql的多行注释*/#特殊注释-- 查询当前mysql中自带的所有库有哪些
库在我们电脑磁盘上----> 文件夹
mysql>showdatabases;+--------------------+|Database|+--------------------+| information_schema | mysql的默认配置库
| mysql | 有user表 (管理员用户表) :root用户就在这个库中
| performance_schema | mysql其他库(性能相关)| test | 测试库,但是不用它,自己创建新的库
+--------------------+4rowsinset(0.00 sec)-- 创建库-- create database 库名;
mysql>createdatabase myEE_2203 ;
Query OK,1row affected (0.00 sec)
mysql>showdatabases;+--------------------+|Database|+--------------------+| information_schema || myee_2203 || mysql || performance_schema || test |+--------------------+5rowsinset(0.00 sec)-- create database if not exists 库名;
mysql>createdatabaseifnotexists ee2203;
Query OK,1row affected (0.00 sec)
mysql>showdatabases;+--------------------+|Database|+--------------------+| information_schema || ee2203 || myee_2203 || mysql || performance_schema || test |+--------------------+6rowsinset(0.00 sec)-- 查询创建库的字符集-- show create database 库名;
mysql>showcreatedatabase myee_2203;+-----------+--------------------------------------------------------------------+|Database|CreateDatabase|+-----------+--------------------------------------------------------------------+| myee_2203 |CREATEDATABASE`myee_2203`/*!40100 DEFAULT CHARACTER SET utf8 */|+-----------+--------------------------------------------------------------------+1rowinset(0.00 sec)-- 修改库的字符集-- alter database 库名 default character set 字符集名称;
mysql>alterdatabase myee_2203 defaultcharacterset gbk;
Query OK,1row affected (0.00 sec)
mysql>showcreatedatabase myee_2203;+-----------+-------------------------------------------------------------------+|Database|CreateDatabase|+-----------+-------------------------------------------------------------------+| myee_2203 |CREATEDATABASE`myee_2203`/*!40100 DEFAULT CHARACTER SET gbk */|+-----------+-------------------------------------------------------------------+1rowinset(0.00 sec)-- 删除库-- drop database 库名 ;
mysql>dropdatabase ee2203;
Query OK,0rows affected (0.01 sec)
mysql>showdatabases;+--------------------+|Database|+--------------------+| information_schema || myee_2203 || mysql || performance_schema || test |+--------------------+-- drop database if exists 库名 ; 如果存在这个库删除
mysql>dropdatabaseifexists myee_2203;
Query OK,0rows affected (0.00 sec)
mysql>showdatabases;+--------------------+|Database|+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+4rowsinset(0.00 sec)
数据库DDL语句(数据库定义语句)之建表,修改表,查询表,删除表…
mysql常见的数据类型
int :整数类型 默认最大长度11位字符,给int类型的数据的时候,当前存储的是值的真实长度
举例
年龄字段age int类型
int(字符数): 整数类型, 给int(3)这个值的时候,实际存储3位,但是赋值的时候不够3位 (使用很少)
id字段 1-----int(3) -------------------001varchar(最大支持255个长度): 字符串类型
指定varchar(指定长度)date:仅仅是日期类型
datetime:日期+时间类型
timestap:时间戳 (举例:管理员添加一个商品,商品上架的时间:当前系统瞬时时间 :2021-5-1116:50分)double:小数类型
double(几位数,小数点后保留的位数)
举例:
double(4,2): 4位,小数点后保留2位
clob:大字符类型
支持 "大文本"blob:大字节类型
最大支持4G
-- 建表之前,必须使用哪个库-- use 库名;
mysql>use ee_2203;Database changed
mysql>/*
create table 表名(
字段名称1 字段类型1,
字段名称2 字段类型2,
...
...
字段名称n 字段类型n
) ;
*/
mysql>createtable student(-> id int,-> name varchar(10),-> age int,-> gender varchar(2),-> address varchar(50),-> socre double(3,1)->);
Query OK,0rows affected (0.02 sec)-- 查询当前库中有哪些表-- show tables ;
mysql>showtables;+-------------------+| Tables_in_ee_2203 |+-------------------+| student |+-------------------+1rowinset(0.00 sec)
mysql>-- 查看表的结构 -- 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||+---------+-------------+------+-----+---------+-------+6rowsinset(0.01 sec)-- 修改表的字段名称 -- alter table 表名 change 旧字段名称 新的字段名称 以前的字段数据类型;
mysql>altertable student change gender sex varchar(2);
Query OK,0rows affected (0.03 sec)
Records: 0 Duplicates: 0Warnings: 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||+---------+-------------+------+-----+---------+-------+6rowsinset(0.01 sec)-- 修改表的字段类型 modify-- alter table 表名 modify 字段名称 新的字段类型 ;
mysql>altertable student modify address varchar(100);
Query OK,0rows affected (0.03 sec)
Records: 0 Duplicates: 0Warnings: 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||+---------+--------------+------+-----+---------+-------+6rowsinset(0.01 sec)-- 修改表:给表中添加一个新的字段-- alter table 表名 add 字段名称 字段类型;
mysql>altertable student add description varchar(200);
Query OK,0rows affected (0.04 sec)
Records: 0 Duplicates: 0Warnings: 0-- 复制一张表-- create table 新表名 like 旧表名;
mysql>createtable teachear like student ;
Query OK,0rows affected (0.02 sec)
mysql>showtables;+-------------------+| Tables_in_ee_2203 |+-------------------+| student || teachear |+-------------------+2rowsinset(0.00 sec)-- 删除表-- drop table 表名;-- drop table if exists 表名;
mysql>droptableifexists teachear;
Query OK,0rows affected (0.01 sec)
mysql>showtables;+-------------------+| Tables_in_ee_2203 |+-------------------+| student |+-------------------+1rowinset(0.00 sec)``-----------------+2rowsinset(0.00 sec)-- 删除表-- drop table 表名;-- drop table if exists 表名;
mysql>droptableifexists teachear;
Query OK,0rows affected (0.01 sec)
mysql>showtables;+-------------------+| Tables_in_ee_2203 |+-------------------+| student |+-------------------+1rowinset(0.00 sec)