MySQL语句
SQL语句是结构化的查询语言,mysql接口程序只负责接受sql,传送给sql层
SQL语句的种类:
DDL:数据库对象
定义语言
DCL:数据库控制
语言(grant revoke)
DML:数据行
操作语言(update delete insert)
DQL:数据查询
语言(show、select)
MySQL对象
1、数据库对象
库
能定义什么?- 库名
- 库的基本属性(字符集、排序规则)
- 如何定义?
- create database [dbname]
- create schema [dbname]
1.创建数据库
//创建库名为:case1和case2,以下两种方法功能一致
mysql> create database case1;
Query OK, 1 row affected (0.00 sec)
mysql> create schema case2;
Query OK, 1 row affected (0.00 sec)
2.创建数据库,指定字符集
字符集:[DEFAULT] CHARACTER SET [=] charset_name
排序规则:[DEFAULT] COLLATE [=] collation_name
mysql> create database case3 character set utf8;
Query OK, 1 row affected (0.01 sec)
3.查看创建的数据库的语句
mysql> show create database case3;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| case3 | CREATE DATABASE `case3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
4.删除数据库
mysql> drop database case3;
Query OK, 0 rows affected (0.01 sec)
4.修改数据库字符集(字符集不要乱修改)
mysql> alter database case1 charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> show create database case1;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| case1 | CREATE DATABASE `case1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
2、表对象
表数据:数据行
表属性(源数据):表名、列名、列定义、数据类型、约束、特殊列属性、表索引信息
表
能定义什么?- 定义表的属性
- 如何定义?
- create table t1(表结构)
1.创建表,前提是要use到指定的数据库
//切换case1数据库
mysql> use case1
Database changed
//在case1数据库中创建表
mysql> create table t1(
id int(4) not null,
name char(20) not null,
age tinyint(2) NOT NULL default '0',
dept varchar(16) default NULL
);
//查看case1数据库中的所有表
mysql> show tables;
+-----------------+
| Tables_in_case1 |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.00 sec)
//查看表的结构
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
//查看创建的表的语句
mysql> show create table t1;
CREATE TABLE `t1` (
`id` int(4) NOT NULL,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
2.修改表中的列
//在表中添加一列
mysql> alter table t1 add age1 int;
//在表中添加多列
mysql> alter table t1 add bridate int, add gender enum('M','F');
提示:以上方法都是在表的最后添加
//在指定列后添加一列
mysql> alter table t1 add stu_id int after id;
//在表中最前添加一列
mysql> alter table t1 add sid int first;
//删除列,直接加表明,不需要数据类型
mysql> alter table t1 drop sid;
//修改列名
mysql>alter table t1 change name stu_name char(20);
//修改列的属性
mysql> alter table t1 modify stu_name varchar(20);
3.修改表名
mysql> rename table t1 to stu;
mysql> show tables;
+-----------------+
| Tables_in_case1 |
+-----------------+
| student |
+-----------------+
1 row in set (0.00 sec)
4.删除表
mysql> drop table t1;
insert用法
insert语法:Insert into<表名>[(<字段名>[,..<字段名n>])] values(值1),[,(值n)]
1.创建一个表,用于测试
mysql> create database test;
mysql> create table test.t1 (
id int(4) NOT NULL AUTO_INCREMENT,
name char(20) NOT NULL,
PRIMARY KEY (id)
);
mysql> desc test.t1;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
2.insert实践
//切换到test数据库
mysql> use test;
//插入一行数据,如果不指定列,就要按规矩为每列都插入恰当的值
mysql> insert into t1 values(1,'zhangsan');
//插入多行数据,整数需要用单引号引起来 '整数/字符串'
mysql> insert into t1 values (2,'lisi'),(3,'wangwu'),(4,'maliu');
//只针对name这个列插入数据,由于id列为自增的,所以可以只在name列插入值
mysql> insert into t1(name) values ('xmh');
//查看插入的结果
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
| 4 | maliu |
| 5 | xmh |
+----+----------+
5 rows in set (0.00 sec)
//清空表数据(慎用)
mysql> truncate table t1;
补充强调:我们平时登录网站发帖子,发博文,实质上都是调用web网站的程序连接MySQL数据库,通过上述的insert语句把帖子博文数据存入数据库的。
update用法
update用于修改表中的数据
命令语法:update 表名 set 字段=新值,… where 条件(一定要注意条件)
1.查看表中的数据
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
| 4 | maliu |
| 5 | xmh |
+----+----------+
5 rows in set (0.01 sec)
2.update实践
//切换到test数据库
mysql> use test;
//修改表中id为3的行中的name为limao
mysql> select * from t1 where id=3;
mysql> update t1 set name='limao' where id=3;
3.update危险命令
//将表中name字段的的所有数据都更改(危险)
mysql> update t1 set name='zhang33';
//使用where条件,只更新id为1的行(推荐)
mysql> update t1 set name='zhang55' where id=1;
select用法
select很强大,能查询用户,数据等
命令语法:select<字段1,字段2, ...>from< 表名 >where<表达式>
其中select,from,where 是不能随便改的,是关键字,支持大小写。
1.查询数据
//查询表中的所有数据
mysql> select * from test.t1;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | limao |
| 4 | maliu |
| 5 | xmh |
+----+----------+
//查询表中id为5的数据
mysql> select * from test.t1 where id=5;
+----+------+
| id | name |
+----+------+
| 5 | xmh |
+----+------+
2.查询mysql用户信息
注意5.7版本查询密码不再是password,变更为了authentication_string
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| guest | 10.0.0.% | |
| zabbix | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
3.根据指定条件查询表中部分数据
//1.指定行查询
mysql> select * from t1 limit 2; #查询两行内容
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
+----+----------+
mysql> select * from t1 limit 1,3; #从第1行开始查找,查找三行内容
+----+-------+
| id | name |
+----+-------+
| 2 | lisi |
| 3 | limao |
| 4 | maliu |
+----+-------+
//2.指定固定条件查询
mysql> select * from t1 where id=1; #查找id为1的行
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
+----+----------+
mysql> select * from t1 where name='xmh'; #查找name为xmh的行,注意单引号
+----+------+
| id | name |
+----+------+
| 5 | xmh |
+----+------+
mysql> select * from t1 where id=2 and name="lisi"; #使用and 多固定条件查询,与的关系
+----+------+
| id | name |
+----+------+
| 2 | lisi |
+----+------+
mysql> select * from t1 where id=2 or name="xmh"; #使用and 多固定条件查询,或的关系
+----+------+
| id | name |
+----+------+
| 2 | lisi |
| 5 | xmh |
+----+------+
//3.按指定范围条件查找
mysql> select * from t1 where id>2 and id<5; #多条件,取id大于2小于5的内容,则3-4行
+----+-------+
| id | name |
+----+-------+
| 3 | limao |
| 4 | maliu |
+----+-------+
mysql> select * from t1 where id>2 or id<5; #多条件,or 并集
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | limao |
| 4 | maliu |
| 5 | xmh |
+----+----------+
4.其它查询功能
//1.排序
mysql> select * from t1 order by id ASC; #默认就是ASC
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | limao |
| 4 | maliu |
| 5 | xmh |
+----+----------+
mysql> select * from t1 order by id DESC;
+----+----------+
| id | name |
+----+----------+
| 5 | xmh |
| 4 | maliu |
| 3 | limao |
| 2 | lisi |
| 1 | zhangsan |
+----+----------+
参数解释:
ASC:从小到大排序(默认参数)
DESC:从大到小排序
delete用法
delete用于删除表中的数据
命令语法:delet from 表名 where 表名
1.查看表中的数据
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | limao |
| 4 | maliu |
| 5 | xmh |
+----+----------+
5 rows in set (0.00 sec)
2.update实践
//切换到test数据库
mysql> use test;
//删除表中id为1的行
mysql> delete from t1 where id=1;
//删除表中name为lisi的行
mysql> delete from t1 where name='lisi';
//删除表中所有行,比较危险,而且是一行一行的删除,删除速度慢
mysql> delete from t1;
//在物理上删除表中数据,删除速度比较快,但不容易恢复数据
mysql> truncate table t1;
Trunatertable和delete 的区别:
- trunatetable test; 删除速度更快,直接清空对应数据的物理文件。
- delete from test; 删除速度慢,逻辑清除,按行删除。
原文:https://www.cnblogs.com/jasonminghao/p/12359798.html
如果您也喜欢它,动动您的小指点个赞吧