mariadb mysql表_mysql/mariadb學習記錄——創建刪除數據庫、表的基本命令

查看已有的數據庫：

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| test |

+--------------------+

2 rows in set (0.05 sec)

新建數據庫語句:

mysql>create database wzu;

Query OK,1 row affected (0.05sec)

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| test |

| wzu |

+--------------------+

3 rows in set (0.05 sec)

要對某個數據庫進行操作時首先要選中這個數據庫,使用use ：

mysql>use wzu

Database changed

創建表格:

命令:create table ＜表名＞ ( ＜屬性名　屬性類型　［修飾］＞ );

create table student(sno char(9) primary key,sname char(20) unique,ssex char(2),sage smallint, sdept char(20));

create table course(cno char(4) primary key,cname char(40) not null,cpno char(4),ccredit smallint,foreign key(cpno) references course(cno));

create table sc(sno char(9),cno char(4),grade smallint,primary key(sno,cno),foreign key(sno) references student(sno),foreign key(cno) references course(cno));

刪除表格:

命令：drop table ＜表名＞;

mysql>drop table emp;Query OK, 0 rows affected (0.41 sec)

查看數據庫中有哪些表格:

mysql> show tables;+---------------+

| Tables_in_wzu |

+---------------+

| course |

| sc |

| student |

+---------------+

更改外鍵約束:

1 set foreign_key_checks=0; //忽略外鍵檢查

2 set foreign_key_checks=1; //重啟外鍵檢查

更改表中某屬性的數據類型:

命令:alter table ＜表名＞ modify ＜需要更改的屬性＞＜想要更改的類型＞；

如:student 表中的sno字段改成varchar(11)

mysql> alter table student modify sno varchar(11);

Query OK,0 rows affected (0.15sec)

Records:0 Duplicates: 0 Warnings: 0

可以用desc ＜表名＞來查看表格結構：

mysql>desc student;+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| sno | varchar(11) | NO | PRI | | |

+-------+-------------+------+-----+---------+-------+

表格插入數據:

命令：insert into values( 填,入,相,應,的,值 );

1 insert into student values('1024','測試','男','18','CS');

刪除一行數據:

命令:delete from where [條件];

1 mysql> delete from emp where empno='232';

修改一行數據：

命令：update set [屬性名]=[值] [where] [條件]

1 mysql> update course set cpno=null where cpno='NULL';

表格中的簡單數據查詢:

select * from [表名]

如:select * from student;

* 表示查詢表格中所有的列,也可以用列名加','隔開來選擇自己需要的數據

mysql> select * fromstudent;+-------------+--------+------+------+-------+

+-------------+--------+------+------+-------+

| 1024 | 測試 | 男 | 18 | CS |

| 1621116 | 周帥 | 男 | 21 | CS |

+-------------+--------+------+------+-------+mysql> select sno,sname fromstudent;+-------------+--------+

| sno | sname |

+-------------+--------+

| 1024 | 測試 |

| 1621116 | 周帥 |

+-------------+--------+

給已創建的表格增加一欄屬性:

alter table add ;

如:

mysql> alter table dept add type varchar(11);

Query OK,4 rows affected (0.62sec)

Records:4 Duplicates: 0 Warnings: 0mysql>desc dept;+--------+--------------+------+-----+---------+-------+

+--------+--------------+------+-----+---------+-------+

| loc | varchar(13) | YES | | NULL | |

+--------+--------------+------+-----+---------+-------+

在已創建的表格中刪除一欄屬性:

alter table drop ;

如:

mysql>alter table dept drop type;

Query OK,4 rows affected (0.57sec)

Records:4 Duplicates: 0 Warnings: 0mysql>desc dept;+--------+--------------+------+-----+---------+-------+

+--------+--------------+------+-----+---------+-------+

| loc | varchar(13) | YES | | NULL | |

+--------+--------------+------+-----+---------+-------+

當發現一個操作有warning時可以通過show warnings；來查看警告信息：

mysql> show warnings;

+---------+------+-----------------------------------------------------------------------------------+

| Level | Code | Message |

+---------+------+-----------------------------------------------------------------------------------+

| Warning | 1366 | Incorrect string value: '\xE6\x9D\x8E\xE5\x8B\x87' for column 'sname' at row 1 |

| Warning | 1366 | Incorrect string value: '\xE7\x94\xB7' for column 'ssex' at row 1 |

| Warning | 1366 | Incorrect string value: '\xE6\x95\xB0\xE6\x8D\xAE...' for column 'cname' at row 1 |

+---------+------+-----------------------------------------------------------------------------------+

此處的問題主要是因為插入的編碼格式不兼容(插入的數據編碼為utf8 表格的編碼為latinl)

mysql查看當前選擇的數據庫:

mysql> selectdatabase();+------------+

| database() |

+------------+

| groupdb |

+------------+

//或者：

mysql> show tables;

+-------------------+

| Tables_in_groupdb |//此處為當前數據庫

+-------------------+

| course? ? ? ? ? ? |

| dept? ? ? ? ? ? ? |

| emp? ? ? ? ? ? ? ?|

| jwc? ? ? ? ? ? ? ?|

| salgrade? ? ? ? ? |

| sc? ? ? ? ? ? ? ? |

| student? ? ? ? ? ?|

| temp? ? ? ? ? ? ? |

+-------------------+

//或者：

mysql> status;

--------------

mysql Ver 14.14 Distrib 5.7.14, for Win64 (x86_64)

Connection id: 2721

Current database: groupdb //此處為當前數據庫

Current user: group@122.228.131.86

SSL: Not in use

Using delimiter: ;

Server version: 5.5.56-MariaDB MariaDB Server

Protocol version: 10

Connection: AAA.AAA.AAA.AAA via TCP/IP

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. characterset: utf8

TCP port: 3306

Uptime: 2 days 5 hours 25 min 51 sec

Threads: 2 Questions: 549 Slow queries: 0 Opens: 26 Flush tables: 2 Open tables: 44 Queries per second avg: 0.002

2018-04-30

判斷一個值是否為NULL：

使用 is 關鍵字而不能用 = 來判斷

mysql> select * from course where cpno is null;+-----+--------------+------+---------+

+-----+--------------+------+---------+

| 2 | 高等數學 | NULL | 2 |

| 6 | 數據處理 | NULL | 2 |

+-----+--------------+------+---------+

2 rows in set (0.05sec)

mysql> select * from course where cpno is not null;+-----+--------------+------+---------+

+-----+--------------+------+---------+

| 1 | 數據庫 | 5 | 4 |

| 3 | 信息系統 | 1 | 4 |

| 4 | 操作系統 | 6 | 3 |

| 5 | 數據結構 | 7 | 4 |

| 7 | C語言 | 6 | 4 |

+-----+--------------+------+---------+

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/529549.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/529549.shtml
英文地址，請注明出處：http://en.pswp.cn/news/529549.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！