1.数据库的基本概念
1)数据库的英文单词:DataBase 简称:DB 2)什么是数据库? 用户存储和管理数据的仓库(内存也存储数据,但关机数据就消失了,数据不能持久) 3)数据库的特点 1.为了持久化存储数据,其实数据库就是一个文件系统,类似1.txt文件方式存储 2.方便存储和管理数据 3.使用了统一的方式操作数据库——即SQL语句 4)常见的数据库软件 类似买汽车,某具体品牌具体型号的汽车,才是真正汽车的产品,类似宝马X3才是具体化的车产品 Qracle:收费的大型数据库,Oracle公司的产品,SUN公司收购了MySQL,Oracle又收购了SUN公司 DB2:IBM公司的数据库产品,常应用于银行系统中 SQLServer:微软公司收费的中型数据库 SQLite:嵌入式的小型数据库,应用在手机端,保存浏览器用户的配置信息2.MySQL数据库软件
1)MySQL安装
下载地址:https://dev.mysql.com/downloads/mysql/ 选择与电脑相对应的64位操作系统 下载的MySQL软件包版本尾号64或32要与电脑的操作系统版本保持一致 不同电脑会由于分辨率与不兼容问题,导致下面安装框不在屏幕内,使用TAB键可以切换选项,再回车安装
下图密码为第一个:root,第二个为确认密码:root
当点击Execute后,下面4个选项头部圆圈出现绿色√号即为安装成功,出现红色×号,即为相应步骤安装失败。 报错:Dindows+r 1.清理以下三个目录: a. HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL b. HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL c. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL MySQL注册表中的ControlSet001,ControlSet002,不一定是001和002,可能是ControlSet005、006之类,删除的时候,都删除就可以 2.检查C:\WINDOWS目录下是否有my.ini文件,将其删除 3.在c盘查询MySQL(可能会隐藏,显示隐藏文件就行了),全部删除 4.重新安装MySQL,配置过程中选择之前未用过的名字(MySql4,若仍使用原来的MySql,则无法启动服务)2)MySQL卸载
mysql的安装目录找到my.ini 复制“datadir=/ProgramData”行,默认安装到C盘,找到对应的目录 卸载mysql 删除C盘下:/ProgramData目录下的MySQL文件夹3)MySQL配置
MySQL启动 1.手动 2.cmd-->services.msc 打开服务窗口 3.使用管理员打开cmd,启动MySQL:net start mysql <-->停止MySQL:net stop mysql MySQL登录 第一种:mysql -uroot -proot -u指定登陆用户,-p登录密码,使用root用户登录,密码是自己设置的root mysql -uroot -p 回车,密文输入密码 第二种:mysql -h127.0.0.1 -uroot -p连接目标的密码 -h连接目标的IP,下面案例为连接本地127.0.0.1 第三种:mysql --host=127.0.0.1 --user=root --password=root MySQL退出 exit、quit,下图演示exit效果,quti相同的效果,切记:Ctrl+c为强制中断服务退出,会中断MySQL服务。 MySQL目录结构 安装目录 my.ini存放配置文件信息 bin 二进制目录,存放二进制的可执行文件 data 数据目录,存放日志文件、数据文件 include 存放C语言头信息 lib 存放MySQL运行需要的库文件 share 存放MySQL的一些错误信息 数据目录 C:\ProgramData\MySQL 默认是隐藏的,可以在<查看>中设置,显示隐藏文件 数据库就是一个文件夹,表就是对应数据库(文件夹)内的文件 数据库:文件夹 表: 文件 数据: 文件内存储的数据3.SQL语句
(Structured Query Language:结构化查询语言)
1)什么是SQL
SQL其实就是定义了操作所有关系型数据库的规则,每一种数据库操作的方式存在不一样的地方,称为“方言”,类似家乡话只有故乡的人才能听懂。
2)SQL通用语法
1.SQL语句可以单行或多行书写,以分号;结尾。 2.可使用空格和缩进来增强语句的可读性。 3.MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。 4.3种注释 单行注释:-- 注释内容 或 # 注释内容(mysql特有,注意--后面要接一个空格,#后面可接可不接) 多行注释:/* 注释 */mysql> SHOW DATABASES;-- 查询数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> SHOW DATABASES;# 查询数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> SHOW DATABASES;#查询数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)
mysql> SHOW DATABASES;/* 查询数据库 */ +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)
3)SQL分类
1)DDL(Data Definition Languge)数据定义语言 用来定义数据库对象:数据库,表,列等。关键字:create,drop,alter等。 2)DML(Data Manipulation Languge)数据操作语言 用来对数据库中表的数据进行增删改。关键字:insert,delete,update等。 3)DQL(Data Query Languge)数据查询语言 用来查询数据库中表的记录(数据)。关键字:select,where等。 4)DCL(Data Control Languge)数据控制语言 用来定义数据库的访问权限和安全级以及创建用户,关键字:GRANT,REVOKE等。4.DDL:操作数据库、表
1)操作数据库:CRUD
1.C(Create):创建,添加,新增 ①CREATE DATABASE 数据库名称mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.01 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> SHOW CREATE DATABASE db1; # 查询创建数据库语句 +----------+--------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------+ | db1 | CREATE DATABASE db1 /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+--------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> CREATE DATABASE db1; # 再次创建会报错1007
ERROR 1007 (HY000): Can't create database 'db1'; database exists
②创建数据库,判断不存在再创建,已存在不报错
mysql> CREATE DATABASE if not exists db1; # 加上if判断,创建数据库,如果已存在不报错,如果不存在则创建 Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> CREATE DATABASE if not exists db2; # 加上判断创建新的数据库 Query OK, 1 row affected (0.00 sec) mysql> SHOW DATABASES;# db2之前不存在,创建成功 +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec)
③创建数据库并指定字符集
mysql> CREATE DATABASE db3 character set gbk; Query OK, 1 row affected (0.00 sec) mysql> SHOW CREATE DATABASE db3; +----------+-------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------+ | db3 | CREATE DATABASE db3 /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+-------------------------------------------------------------+ 1 row in set (0.00 sec)
④创建数据库,判断不存在再创建,已存在不报错并指定字符集
mysql> CREATE DATABASE if not exists db4 character set gbk; Query OK, 1 row affected (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | db4 | | mysql | | performance_schema | | test | +--------------------+ 8 rows in set (0.00 sec) mysql> SHOW CREATE DATABASE db4; +----------+-------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------+ | db4 | CREATE DATABASE db4 /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+-------------------------------------------------------------+ 1 row in set (0.00 sec)
2.R(Retrieve):查询
①查询所有数据库名称 SHOW DATABASES;②查询某个数据库的字符集、查询某个数据库的创建语句
SHOW CREATE DATABASE 数据库名称;mysql> SHOW CREATE DATABASE mysql; # 查询创建mysql数据库的语法 +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | mysql | CREATE DATABASE mysql /*!40100 DEFAULT CHARACTER SET utf8 */ | # /**/注释,安装数据库时默认选择字符集为utf8 +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec)
3.U(Update):修改
①修改数据库字符集 ALTER DATABASE 数据库名称 character set 新字符集名称mysql> SHOW CREATE DATABASE db3; +----------+-------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------+ | db3 | CREATE DATABASE db3 /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+-------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER DATABASE db3 character set utf8; Query OK, 1 row affected (0.00 sec) mysql> SHOW CREATE DATABASE db3; +----------+--------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------+ | db3 | CREATE DATABASE db3 /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+--------------------------------------------------------------+ 1 row in set (0.00 sec)
4.D(Delete):删除
①DROP DATABASE 数据库名称mysql> DROP DATABASE db3; Query OK, 0 rows affected (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db4 | | mysql | | performance_schema | | test | +--------------------+ 7 rows in set (0.00 sec) mysql> DROP DATABASE db3;# 不能重复删除,会报错 ERROR 1008 (HY000): Can't drop database 'db3'; database doesn't exist
②删除数据库,判断存在则删除,不存在不报错
mysql> DROP DATABASE if exists db3;# 即使db3数据库已不存在也不报错 Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> DROP DATABASE if exists db4; Query OK, 0 rows affected (0.00 sec) mysql> SHOW DATABASES;# db4数据库已删除 +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec)
5.使用数据库
①查询正在使用的数据库名称 SELECT DATABASE();mysql> SELECT DATABASE();# 此时还未使用数据库,所以不显示 +------------+ | DATABASE() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) mysql> USE db1;# 使用、切换数据库 Database changed mysql> SELECT DATABASE();# 当前使用数据库名称 +------------+ | DATABASE() | +------------+ | db1 | +------------+ 1 row in set (0.00 sec)
②使用数据库USE
USE 数据库名称mysql> USE db1;# 使用、切换数据库 Database changed mysql> SELECT DATABASE();# 当前使用数据库名称 +------------+ | DATABASE() | +------------+ | db1 | +------------+ 1 row in set (0.00 sec)
2)操作表
1.C(Create):创建,添加,新增 ①语法: CREATE TABLE 表名( 列名1 数据类型1, 列名2 数据类型2, ........... 列名n 数据类型n ); 注意:最后一列,不需要逗号(,) ②数据类型: 1.int 整数类型 age int, 2.double 小数类型 score double(5,2) 其中5 最多小数点后5位,2 小数点后保留2位小数 3.data 日期类型,只包含年月日,yyyy-MM-dd 4.datatime 日期,包含年月日时分秒yyyy-MM-dd HH:mm:ss 5.timestamp 时间戳类型,包含年月日时分秒yyyy-MM-dd HH:mm:ss 如果指定是timestamp字段,若将来不给这个字段赋值或赋值为null,则默认使用当前的系统时间来自动赋值 6.varchar 字符串类型,超过规定最大字符串长度会报错 name varchar(20):表示姓名最大20个字符 zhangsan 8个字符 张三 2个字符 ③创建表 create table student( id int, name varchar(32), age int, score double(4,1), birthday date, insert_time timestamp );mysql> use db1; Database changed mysql> select database(); +------------+ | database() | +------------+ | db1 | +------------+ 1 row in set (0.00 sec) mysql> create table student( id int, name varchar(32), age int, score double(4,1), birthday date, insert_time timestamp); Query OK, 0 rows affected (0.01 sec) mysql> show tables; +---------------+ | Tables_in_db1 | +---------------+ | student | +---------------+ 1 row in set (0.00 sec) mysql> desc student; +-------------+-------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+-------------------+-----------------------------+ | id | int(11) | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | age | int(11) | YES | | NULL | | | score | double(4,1) | YES | | NULL | | | birthday | date | YES | | NULL | | | insert_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------------+-------------+------+-----+-------------------+-----------------------------+ 6 rows in set (0.01 sec)④复制表内容 CREATE TABLE 表名 like 被复制的表名;
mysql> CREATE TABLE stu like student; Query OK, 0 rows affected (0.01 sec) mysql> SHOW TABLES; +---------------+ | Tables_in_db1 | +---------------+ | stu | | student | +---------------+ 2 rows in set (0.00 sec)
数据库与execl表格类似,当我们创建execl表格时,会先创建表名,表里有很多行,第一行为表头行,在表头行有很多列,每一列代表的含义不一样,按照每列的头部信息写入数据(头部信息例如:姓名、年龄,身高等)(创建表,先指定表名、每一列名字、那一列将要存储什么样的数据格式)
2.R(Retrieve):查询 ①查询数据库中所有表的名称 SHOW TABLES;mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) mysql> USE DB1; Database changed mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | db1 | +------------+ 1 row in set (0.00 sec) mysql> SHOW TABLES;# 只有到对应的数据库下才能操作对应数据库下的表 Empty set (0.01 sec) mysql> USE mysql; Database changed mysql> SHOW TABLES; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | ............................. # 太多已省略 | host | | user | +---------------------------+ 24 rows in set (0.01 sec)②查询表结构 desc 表名;
mysql> DESC host; +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | ........................................................................# 太多已省略 +-----------------------+---------------+------+-----+---------+-------+ 20 rows in set (0.01 sec)3.U(Update):修改 ①修改表名 ALTER TABLE 表名 rename to 新的表名;
mysql> SHOW TABLES; +---------------+ | Tables_in_db1 | +---------------+ | student | +---------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE student rename to stu; Query OK, 0 rows affected (0.00 sec) mysql> SHOW TABLES; +---------------+ | Tables_in_db1 | +---------------+ | stu | +---------------+ 1 row in set (0.00 sec)②修改表的字符集 SHOW CREATE TABLE stu;查看字符集类型 ALTER TABLE 表名 character set 字符集类型;
mysql> SHOW CREATE TABLE stu; +-------+----------------------------------------------------------------------------------------------------------------------+ | Table | Create Table id int(11) DEFAULT NULL, name varchar(32) DEFAULT NULL, age int(11) DEFAULT NULL, score double(4,1) DEFAULT NULL, birthday date DEFAULT NULL, insert_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | # 此处显示字符集类型为utf8 +-------+----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE stu character set gbk; # 修改字符集类型为gbk Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE stu; +-------+---------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------+ | stu | CREATE TABLE stu ( id int(11) DEFAULT NULL, name varchar(32) CHARACTER SET utf8 DEFAULT NULL, age int(11) DEFAULT NULL, score double(4,1) DEFAULT NULL, birthday date DEFAULT NULL, insert_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=gbk | # 此处显示字符集类型为gbk +-------+--------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)③添加一列 ALTER TABLE 表名 add 列名 数据类型;
mysql> ALTER TABLE stu add gender varchar(10); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC stu; +-------------+-------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+-------------------+-----------------------------+ | id | int(11) | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | age | int(11) | YES | | NULL | | | score | double(4,1) | YES | | NULL | | | birthday | date | YES | | NULL | | | insert_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | gender | varchar(10) | YES | | NULL | | +-------------+-------------+------+-----+-------------------+-----------------------------+ 7 rows in set (0.03 sec)④修改某一列名称 类型 ALTER TABLE 表名 change 列名 新列名 新数据类型;即改了列名又改了类型 ALTER TABLE 表名 modify 列名 新数据类型;只改了数据类型
mysql> ALTER TABLE stu change gender sex varchar(20); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC stu; +-------------+-------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+-------------------+-----------------------------+ | id | int(11) | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | age | int(11) | YES | | NULL | | | score | double(4,1) | YES | | NULL | | | birthday | date | YES | | NULL | | | insert_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | sex | varchar(20) | YES | | NULL | | +-------------+-------------+------+-----+-------------------+-----------------------------+ 7 rows in set (0.01 sec) mysql> ALTER TABLE stu modify sex varchar(10); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC stu; +-------------+-------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+-------------------+-----------------------------+ | id | int(11) | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | age | int(11) | YES | | NULL | | | score | double(4,1) | YES | | NULL | | | birthday | date | YES | | NULL | | | insert_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | sex | varchar(10) | YES | | NULL | | +-------------+-------------+------+-----+-------------------+-----------------------------+ 7 rows in set (0.01 sec)⑤删除列 ALTER TABLE 表名 drop 列名;
mysql> ALTER TABLE stu drop sex; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC stu; +-------------+-------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+-------------------+-----------------------------+ | id | int(11) | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | age | int(11) | YES | | NULL | | | score | double(4,1) | YES | | NULL | | | birthday | date | YES | | NULL | | | insert_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------------+-------------+------+-----+-------------------+-----------------------------+ 6 rows in set (0.01 sec)4.D(Delete):删除 ①DROP TABLE 表名;
mysql> DROP TABLE stu; Query OK, 0 rows affected (0.01 sec) mysql> DROP TABLE stu;# 重复删除会出现报错 ERROR 1051 (42S02): Unknown table 'stu' mysql> SHOW TABLES;# 确定已经删除 +---------------+ | Tables_in_db1 | +---------------+ | student | +---------------+ 1 row in set (0.00 sec)②DROP TABLE if exists 表名;
mysql> DROP TABLE IF EXISTS STU;# 加入判断不报错 Query OK, 0 rows affected, 1 warning (0.00 sec)
5.客户端图形化工具:SQLYog
6.DML:增删改表中数据
1)添加数据
语法:insert into 表名(列名1,列名2,.....列名n) values(值1,值2,......值n); 注意: 列名和值要一一对应 如果表名后,不定义列名,则默认给所有列添加值 insert into 表名 values(值1,值2,......值n); INSERT stu VALUES(2,'赵敏',17,99.9,NULL,NULL); INSERT stu VALUES(3,"张三丰",17,99.9,"1893-11-11",NULL);2)删除数据
语法:delete from 表名 [where 条件] 意为:当条件满足时,从表里删除数据 注意: 如果不加条件,则删除表中所有记录。 如果要删除所有记录, 1,delete from 表名 --不推荐,有多少条记录就会执行多少次删除操作 2,TRUNCATE TABLE 表名 --推荐 效率更高,先删除表,然后在创建一张一样的表。 DELETE FROM stu WHERE ID=1;mysql> select * from stu; +------+-----------+------+-------+------------+---------------------+ | id | name | age | score | birthday | insert_time | +------+-----------+------+-------+------------+---------------------+ | 1 | 张无忌 | 18 | NULL | NULL | 2022-11-01 23:27:49 | | 2 | 赵敏 | 17 | 99.9 | NULL | 2022-11-02 12:10:36 | | 3 | 张三丰 | 17 | 99.9 | 1893-11-11 | 2022-11-02 13:18:46 | +------+-----------+------+-------+------------+---------------------+ 3 rows in set (0.00 sec) mysql> DELETE FROM stu WHERE ID=1;# 删除第一条数据 Query OK, 1 row affected (0.01 sec) mysql> select * from stu;# 查看数据确定数据已删除 +------+-----------+------+-------+------------+---------------------+ | id | name | age | score | birthday | insert_time | +------+-----------+------+-------+------------+---------------------+ | 2 | 赵敏 | 17 | 99.9 | NULL | 2022-11-02 12:10:36 | | 3 | 张三丰 | 17 | 99.9 | 1893-11-11 | 2022-11-02 13:18:46 | +------+-----------+------+-------+------------+---------------------+ 2 rows in set (0.00 sec) mysql> DELETE FROM stu;# 后面不加条件,会默认删除所有数据 Query OK, 2 rows affected (0.01 sec) mysql> select * from stu;# 数据已被完全删除,不推荐使用 Empty set (0.00 sec) mysql> INSERT INTO stu(id,name,age) values(1,'zhangwuji',18); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO stu values(2,'zhaomin',17,99.9,NULL,NULL); Query OK, 1 row affected (0.01 sec) mysql> select * from stu;# 添加两条数据,查看数据 +------+-----------+------+-------+----------+---------------------+ | id | name | age | score | birthday | insert_time | +------+-----------+------+-------+----------+---------------------+ | 1 | zhangwuji | 18 | NULL | NULL | 2022-11-02 14:02:48 | | 2 | zhaomin | 17 | 99.9 | NULL | 2022-11-02 14:04:16 | +------+-----------+------+-------+----------+---------------------+ 2 rows in set (0.00 sec) mysql> TRUNCATE TABLE stu;# 同样也是删除数据,但先删除表再创建一个一模一样的表,节省资源,效率更高 Query OK, 0 rows affected (0.01 sec) mysql> select * from stu;# 查看数据已经被删除 Empty set (0.00 sec) mysql> SHOW TABLES; +---------------+ | Tables_in_db1 | +---------------+ | stu | +---------------+ 1 row in set (0.00 sec)
3)修改数据
语法:UPDATE 表名 SET 列名1=值1,列名2=值2,列名n=值n [where 条件]; 注意: 如果不加任何条件,则会将表中所有记录数据全部修改。 UPDATE STU SET SCORE=100 WHERE ID=1;mysql> INSERT INTO stu(id,name,age) values(1,'zhangwuji',18); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO stu values(2,'zhaomin',17,99.9,NULL,null); Query OK, 1 row affected (0.01 sec) mysql> select * from stu; +------+-----------+------+-------+----------+---------------------+ | id | name | age | score | birthday | insert_time | +------+-----------+------+-------+----------+---------------------+ | 1 | zhangwuji | 18 | NULL | NULL | 2022-11-02 14:24:00 | | 2 | zhaomin | 17 | 99.9 | NULL | 2022-11-02 14:24:16 | +------+-----------+------+-------+----------+---------------------+ 2 rows in set (0.00 sec) mysql> UPDATE STU SET SCORE=100 WHERE ID=1;# 基于ID更改数据内容,第一条添加分数 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from stu; +------+-----------+------+-------+----------+---------------------+ | id | name | age | score | birthday | insert_time | +------+-----------+------+-------+----------+---------------------+ | 1 | zhangwuji | 18 | 100.0 | NULL | 2022-11-02 14:25:16 | | 2 | zhaomin | 17 | 99.9 | NULL | 2022-11-02 14:24:16 | +------+-----------+------+-------+----------+---------------------+ 2 rows in set (0.00 sec) mysql> UPDATE stu SET age=20,score=99,birthday='1995-07-07' where id=1;# 基于ID更改多条数据记录 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from stu; +------+-----------+------+-------+------------+---------------------+ | id | name | age | score | birthday | insert_time | +------+-----------+------+-------+------------+---------------------+ | 1 | zhangwuji | 20 | 99.0 | 1995-07-07 | 2022-11-02 14:38:08 | | 2 | zhaomin | 17 | 99.9 | NULL | 2022-11-02 14:24:16 | +------+-----------+------+-------+------------+---------------------+ 2 rows in set (0.00 sec) mysql> UPDATE stu SET age=25;# 没有加任何条件 Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from stu; # 所有age数据都被更改 +------+-----------+------+-------+------------+---------------------+ | id | name | age | score | birthday | insert_time | +------+-----------+------+-------+------------+---------------------+ | 1 | zhangwuji | 25 | 99.0 | 1995-07-07 | 2022-11-02 14:56:52 | | 2 | zhaomin | 25 | 99.9 | NULL | 2022-11-02 14:56:52 | +------+-----------+------+-------+------------+---------------------+ 2 rows in set (0.00 sec)
7.DQL:查询数据
1)语法:
select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定2)基础查询
1.多个字段的查询 查询姓名和年龄 SELECT NAME,age FROM student; SELECT NAME,-- 姓名 age -- 年龄 FROM student;-- 指定的表 2.去除重复 去除重复的结果集 SELECT address FROM student; SELECT DISTINCT address FROM student; SELECT DISTINCT NAME,address FROM student;# 多列显示时,保证展示列的结果集完全一样才会去除重复 3.计算列 计算math和english分数之和 SELECT NAME,math,english FROM student; SELECT NAME,math,english,math + english FROM student;如果有NULL参与的运算,计算结果都为NULL SELECT NAME,math,english,math + IFNULL(english,0) FROM student; 4.起别名 起别名 SELECT NAME,math,english,math + IFNULL(english,0) AS 总分 FROM student; SELECT NAME 姓名,math 数学,english 英语,math + IFNULL(english,0) 总分 FROM student;# 或者使用空格CREATE TABLE student ( id INT, -- 编号 NAME VARCHAR(20), -- 姓名 age INT,-- 年龄 sex VARCHAR(5),-- 性别 address VARCHAR(100), -- 地址 math INT,-- 数学 english INT -- 英语 ); INSERT INTO STUDENT (id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭州',66,78), (2,'马化腾',45,'女','深圳',98,87), (3,'马景涛',55,'男','香港',56,77), (4,'柳色',20,'女','湖南',76,65), (5,'柳青',20,'男','湖南',86,NULL), (6,'刘德华',57,'男','香港',99,99), (7,'马德',22,'女','香港',99,99), (8,'德玛西亚',18,'男','南京',56,65);