MySQL数据库学习笔记一
一、 Install MySQL.
Windows install mysql:download from the internet.
run the mysql
net start
this is to run the mysql server.(maybe needed root.)
login in the mysql
mysql -h localhost -u user_name -p
and then, input password.
or the code showed below is also available to login in :
mysql -u user_name -p
and then, input password.
二、 Manage MySQL.
Login
in my computer, i need to do the things below :
first step
cd C:\Program Files\MySQL\MySQL Server 8.0\bin
second step
mysql -u root -p
third step
671513
(this is the password.)
Add users(adding users)
this is to add an user :
root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed
mysql> INSERT INTO user
(host, user, password,
select_priv, insert_priv, update_priv)
VALUES ('localhost', 'guest',
PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host | user | password |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)
Select database
show databases;
use database_name;
show tables;
show columns from table_name;
show index from table_name;
三、Connect MySQL
normally connect mysql
mysql -u root -p
[root@host]# mysql -u root -p
Enter password:******
use port to connect remote mysql
for example:
mysql -u root -P 3307 -h 101.200.152.192 -p
or:
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -P 3306 -h localhost -p
Enter password: ******
(password is 671513(this is mine.) and the port of the mysql is 3306 if not changed.)
四、MySQL Database Operation
create database
create database database_name;
delete database
drop database database_name;
if the databses does not, we should use the sentence below :
mysql> drop database if exists database_name;
Query OK, 0 rows affected, 1 warning (0.00 sec)
show and select databse
for example :
mysql> create test_db_1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test_db_1' at line 1
mysql> create database test_db_1;
Query OK, 1 row affected (0.01 sec)
mysql> create database if not exists test_db_1;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| test_db_1 |
| world |
+--------------------+
7 rows in set (0.00 sec)
mysql> use test_db_1;
Database changed
mysql>
(attention that using if not exists can avoid some errors.)
五、MySQL的数据类型有关内容:
1、数字类型:
类型 |
大小 |
范围(有符号) |
范围(无符号) |
用途 |
TINYINT |
1Bytes |
(-128,127) |
(0,255) |
小整数值 |
SMALLINT |
2Bytes |
(-32768,32767) |
(0,65535) |
大整数值 |
MEDIUMINT |
3Bytes |
(-8388608,8388607) |
(0,16777215) |
大整数值 |
INT或INTEGER |
4Bytes |
(-2147483648,2147483647) |
(0,4294967295) |
大整数值 |
BIGINT |
8Bytes |
略 |
略 |
极大整数值 |
FLOAT |
4Bytes |
略 |
略 |
单精度浮点数值 |
DOUBLE |
8Bytes |
略 |
略 |
双精度浮点数值 |
2、时间类型:
类型 |
大小 |
范围(有符号) |
格式 |
用途 |
DATE |
3Bytes |
1000-01-01/9999-12-31 |
YYYY-MM-DD |
日期值 |
TIME |
3Bytes |
‘-838:59:59’/‘838:59:59’ |
HH:MM:SS |
时间值或持续时间 |
YEAR |
1Bytes |
1901/2155 |
YYYY |
年份值 |
DATETIME |
8Bytes |
1000-01-01 00:00:00/9999-12-31 23:59:59 |
YYYY-MM-DD HH:MM:SS |
混合日期与时间值 |
TIMESTAMP |
4Bytes |
1970-01-01 00:00:00/2038 |
YYYYMMDD HHMMSS |
混合日期和时间值,时间戳 |
3、字符串类型:
类型 |
大小 |
用途 |
CHAR |
0-255Bytes |
定长字符串 |
VARCHAR |
0-65535Bytes |
变长字符串 |
TINYBLOB |
0-255Bytes |
不超过255个字符的二进制字符串 |
TINYTEXT |
0-65535Bytes |
短文本字符串 |
BLOB |
0-65535Bytes |
二进制形式的长文本数据 |
TEXT |
0-65535Bytes |
长文本数据 |
MEDIUMBLOB |
0-16777215Bytes |
二进制形式的中等长度文本数据 |
MEDIUMTEXT |
0-16777215Bytes |
中等长度文本数据 |
LONGBLOB |
0-4294967295Bytes |
二进制形式的极大文本数据 |
LONGTEXT |
0-4294967295Bytes |
极大文本数据 |
(字符创的小问题:char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。)
六、总结
这是一些MySQL数据库的基本操作,希望对大家有一些帮助,谢谢大家的阅读与支持,后续还会继续推出相关的内容的。
谢谢大家的阅读与支持。