mysql 基础

我很丑
199 阅读7分钟

mysql 基础

创建数据库

create database '数据库名';
//root登录后可以使用如下sql语句创建数据库
CREATE DATABASE IF NOT EXISTS ‘数据库名’ DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

删除数据库

drop database '数据库名';

创建表

create table table_name(column_name dolumn_type);
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

  • 如果不想字段为NULL，可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL，就会报错。字段应尽量设置为NOT NULL。

  • AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。

  • PRIMARY KEY关键字定义列为主键，可以使用多列来定义主键，列之间用逗号分隔。

  • ENGINE 这只存储引擎，CHARSET设置编码。

删除数据表

drop table table_name;

插入数据

insert into table_name(field1, field2,...fieldN) values (value1, value2, ...valueN);
//插入多列
INSERT INTO table_name  (field1, field2,...fieldN)  VALUES  (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......;
  • 如果添加过主键自增（PRINARY KEY AUTO_INCREMENT）第一列在增加数据的时候，可以写为0或者null，这样添加数据可以自增， 从而可以添加全部数据，而不用特意规定那几列添加数据。

查询数据

select column_name, column_name from table_name [where clause] [limit n][offset m];

  • 查询语句中你可以使用一个或多个表，表之间使用逗号(,)分割，并使用where语句来设定查询条件。

  • select 命令可以读取一条或多条记录。

  • 你可以使用星号(*)来代替其它字段，select语句会返回表的所有字段数据。

  • 你可以使用where语句来包含任何条件

  • 你可以使用limit属性来设置返回的记录数

  • 你可以通过offset指定select语句开始查询的数据偏移量，默认情况下偏移量为0

Where子句

select field1, field2,...fieldN from table_name1,table_name2...[where condition1 [and [or]] condition2.....]

  • 查询语句中可以使用一个或者多个表，表之间使用逗号，分割，并使用where语句来设定查询条件。

  • 可以在where子句中指定任何条件。

  • 可以使用and或者or指定一个或者多个条件。

  • where子句也可以运用于sql的delete或者update命令。

  • where子句类似于程序语言中的if条件，根据mysql表中的字段值来读取指定的数据。

Update 更新

update table_name set field1=new-value1,field2=new-value2[where clause]

  • 可以同时更新一个或者多个字段

  • 可以在where子句中指定任何条件

  • 可以在一个单独表中同时更新数据

Delete 语句

delete from table_name [where clause]

  • 如果没有指定where子句，mysql表中的所有记录将被删除

  • 可以在where子句中指定任何条件

  • 可以在单个表中一次性删除记录

Like 子句

select field1, field2,...fieldN from table_name where field1 likt condition [and [or]] field2='somevalue'

  • 可以在where子句中指定任何条件

  • 可以在where子句中使用like子句

  • 可以使用leke代替等号=

  • like通常于%一同使用，‘%’表示任意字符

  • 可以使用and或者or指定一个或多个条件

  • 可以在delete或update命令中使用where ...like子句来指定条件

Union 操作符

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据

select expression1, expression2, ...expression_n from tables [where conditions] union [all | distinct] select expression1, expression2, ...expression_n from tables [where conditions]

  • expression1,expression2,...expression_n:要检索的列。

  • tables:要检索的数据表

  • where conditions: 可选，检索条件

  • distinct: 可选，删除结果集中重复的数据，默认情况下union操作符已经删除了重复数据，所以distinct修饰符对结果没啥影响。

  • all:可选，返回所有结果集，包含重复数据。

排序

select field1,field2,...fieldN from table_name1, table_name2... order by field1[asc [desc][默认asc]], [field2...] [asc [desc][默认asc]]

  • 可以使用任何字段来作为排序的条件，从而返回排序后的查询结果

  • 可以设定多个字段来排序

  • 可以使用asc或desc关键字来设置查询结果时按升序(asc)或降序()desc)排列,默认情况下，他是按升序排列

  • 可以添加where...like子句来设置条件

Group By语句

select column_name, function(column_name) from table_name where column_name operator value group by column_name;
  • 在分组的列上可以使用conut，sum，avg等函数
mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+
3 rows in set (0.01 sec)
  • 使用with rollup
mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.00 sec)

mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)
  • select coalesce(a,b,c);参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。上面实例中如果名字为空我们使用总数代替：

连接的使用

  • inner join(内连接，或等值连接)：获取两个表中字段匹配关系的记录

  • eft join(左连接)：获取左表所有记录，即使右表没有对应匹配的记录

  • right join(右连接)：与左连接相反，用于获取右表所有记录，即使左表没有对应匹配的记录

测试示例数据

mysql> use RUNOOB;
Database changed
mysql> SELECT * FROM tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程  | 10           |
| RUNOOB.COM    | 20           |
| Google        | 22           |
+---------------+--------------+
3 rows in set (0.01 sec)

mysql> SELECT * from runoob_tbl;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1         | 学习 PHP    | 菜鸟教程  | 2017-04-12      |
| 2         | 学习 MySQL  | 菜鸟教程  | 2017-04-12      |
| 3         | 学习 Java   | RUNOOB.COM    | 2015-05-01      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
| 5         | 学习 C      | FK            | 2017-04-05      |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.01 sec)

inner join

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
+-------------+-----------------+----------------+
4 rows in set (0.00 sec)

where 子句，以上sql等同于

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
+-------------+-----------------+----------------+
4 rows in set (0.01 sec)

left join

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
| 5           | FK              | NULL           |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)

right join

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
| NULL        | NULL            | 22             |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)

NULL值处理

  • is null： 当前的值时null，此运算返回ture

  • is not null：当前的值不为null，运算符返回true

  • <=>:比较操作符（不同于=运算符）当比较的两个值相等或者都为null时返回true

  • 不能使用=null或!=null在列中查找null值

  • null值与任何其它值的比较（即使是null）永远返回null，即null=null返回null

正则表达式

  • 查找name字段以'st'为开头的所有数据
select name from person_tb1 where name regexp '^st';
  • 查找name字段中以'ok'为结尾的所有数据
select name from person_tb1 where regexp 'ok$';
  • 查找name字段包含'mar'字符串的所有数据
select name from person_tb1 where name regexp 'mar';
  • 查找name字段中以元音字符开头或以‘ok’字符结尾的所有数据
select name from person_tb1 where name regexp '^[aeiou]|ok$';

Alter命令

  • 修改表名或者修改表字段是需要用到alter命令

  • 删除表的i字段,如果表中只剩一个字段则无法用drop删除

alter table testalter_tb1 drop i;
  • 添加i字段并定义数据类型
alter table testalter_tb1 add i int;
alter table testalter_tb1 add i int first;//设定位于第一列
alter table testalter_tb1 add i int after c;//设定位于c字段之后
  • 修改字段类型及名称
alter table testalter_tb1 modify c char(10);//修改数据类型
alter table testalter_tb1 change i j bigint;//修改字段名和类型
  • 修改字段可以指定是否包含值或者是否设置默认值,如果不设置默认值，MySQL会自动设置该字段默认为null
alter table testalter_tb1 modify i bigint not null default 100;
  • 修改字段默认值
alter table testalter_tb1 alter i set default 1000;
  • 删除默认值
alter table testalter_tb1 alter i drop default;
  • 修改表名
alter table testalter_tb1 rename to alter_tb1;

索引

  • 创建普通索引
create index indexname on mytable(username(length));//如果是bolb和text类型，必须指定length。
  • 修改表结构添加索引
alter table tablename add index indexname(columnName);
  • 创建表的时候直接指定索引
create table mytable( 
id int not null, 
username varchar(16) not null, 
index [indexName] (username(length))
);
  • 删除索引
drop index [indeName] on mytable;
  • 创建唯一索引
create unique index indexName on mytable(username(length));
  • 修改表结构唯一索引
alter table mytable add unique [indexName] (username(length));
  • 创建表的时候指定唯一索引
create table mytable(
    id int not null,
    username varchar(16) not null,
    unique [indexName] (username(length))
);
  • 添加索引
alter table testalter_tb1 add index (c);
  • 删除索引
alter table testalter_tb1 drop index c;
  • 添加主键
alter table testalter_tb1 modify i int not null;
alter table testalter_tb1 add primary key (i);

  • 删除主键

    删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

alter table testalter_tb1 drop primary key;
  • 显示索引信息
show index from table_name;

参考

avatar
文章
阅读
粉丝