SQL: Called Structured Query Language, it is internally divided into: DML statements: Data manipulation language, for increment (insert), delete (delete), check (select), Change (update) DDL statement: Data definition language for data storage, Create,drop, ALTERDCL statement: Data Control language, generally used for permission control. Grant,revoke,commit, rollback MySQL distribution is divided into commercial version (enterprise charge), Community Edition (community is free version) official website:www.mysql.comMySQL is the C/s architecture, there are clients and server-side MySQL software provided by three kinds of package format: 1. Package Manager unique format, such as RPM package, 2. Universal 2 software, no need to install a direct decompression configuration can be used. 3. The source program website provides the software packages required by different operating systems. Linux can download Linux Generic this is a generic binary format, we need to download the package compiled with GLIBC. Universal 2 binary Linux-generic (glibc 2.5) (x86, 32-bit), compressed TAR archive Red Hat Package Manager itself provides MySQL using the command yum list all |grep Mys QL View Where mysql.i686 represents a MySQL client mysql-server.i686 represents a MySQL server-side database mysql-bench.i686 represents a database performance test, a stress test component Corresponding to the official Red Hat, the most important is the MySQL mysql-server, is basically 5.1 version of the older, the latest package can only go to the MySQL official website to download. The source package is used only if you need to customize the use of MySQL. On the Red Hat system MySQL is the client, mysqld called the server side, MySQL listens on the tcp3306 port, generally runs with the MySQL user and the MySQL user group. The data for the default MySQL database on Red Hat system is saved in the/var/lib/mysql/directory. Using yum-y install Mysql-server will automatically install MySQL server side and client. After the installation is complete, MySQL will have an initialization action: After MySQL is created, there is a unique database name inside the MySQL stored in the source data, is to save some of the current database in how many databases, each database called what name, a total of more than one table, each table is called what name, And the total number of fields, field properties, just start the installation complete this MySQL database does not exist, so the initialization is to build this database, so the first boot. Using the command service mysqld start will perform initialization. MySQL has a root user and the root of the Linux operating system is not the same thing. Not the same user, the root password is empty after installation. Root is the highest-privileged user who uses Yum to complete the installation and tap the MySQL command directly. You can enter using-u to specify which user to use to connect. -P can specify the service with the password-hAddress, do not use the-H default connection locally such as Mysql-u root-p-H 192.168.1.111 quit MySQL using the QUIT command linux connect local MySQL is based on the socket connection, and the socket file is usually in/var/ Lib/mysql below if the client and server are not on a single host, you must use the TCP/IP protocol to connect  MYSQL client working mode: interactive mode, batch mode (can implement MySQL script) interactive mode command category: Client command: You can use \h in interactive mode to get the client command help the \ representative behind each command can use the shorthand command server-side command: Server-side commands require a command tone to end defaults think of a semicolon; sql interface: Oracle pl/sql& nbsp; relational database objects: tables, indexes, views, constraints, storage functions, triggers, cursors, users, permissions, transactional relational databases, the core is the table. There are rows and columns (column). mysql data type: character, value, date, Boolean character type using char notation, defining a character type also requires a specified length, such as char (5) varchar () variable long character type. Character types are stored in case insensitive, to be case sensitive using BINARY () VARBINARY () variable length text () large object character storage. can be used to store long characters numeric types: The exact numeric type: integer (tinyint,smallint,mediumint,int,bigint) integer modifier, which can be used unsigned is unsigned integer. Not NULL non-null approximate numeric type: floating-point (float,double) datetime datetimedatetimestamp Boolean: Using 0 and 1 for mysql Common commands: Not case-sensitive for MySQL. Creating database: Create database name; Create DB if not EXISTS database name; Delete a database The database name is dropped; note that the MySQL database does not have a recycle Bin, Delete will not be able to recover create table before creating table to choose which database to use to create the table. For example: We have a database called Python. We build a table above, first select this database use Python; CREATE table: CREate Table students (name char) not null,age tinyint unsignet,sex char (1) NOT null ) View table: Show tables; View table S in library How tables the From database name; View table Structure desc table name; Delete table: The name of the dropped table table, or drop table if exists name; Modify Table: Modify: Modify a field property change : Change field name add: Add Field drop: Delete field for example, add a field to the table students. alter Table Students Add course varchar; default Add field at the end, We added a course field to change the first letter of the course field to uppercase and put it in front of the name field. ALTER TABLE Students CHANG Course course varchar (+) After name; delete field: Drop field name; Insert data: INSERT into table name (Field 1 , Field 2, ..... Values (' characters ', numbers ...) If you want to insert a value for each field, you do not need to specify the field BULK INSERT: INSERT into table name (Field 1, Field 2, ...). Values (' characters ', numbers ...), (' characters ', numbers ...).........) For example, to the field of the students table, name, sex insert two names and gender inserts into students (Name,sex) value (' Py1 ', ' M '), (' Py2 ', ' F '); insert complete and then use SELECT * from students; Query all the data in the table to see Replacement data: Replace into Modify data: Update table name set field = value; where conditions; For example, we modify the students table in the field of the Py1 gender M to fuptate students set sex= ' F ' where name= ' py1 '; deleteExcept for a row: delete from students where name= ' py1 ' here is the delete name field contains py1 selection: Selcet field from table name WHERE Condition *: represents all fields created by user : create user ' username ' @ ' host ' identified by ' passwd '; So create can only connect to MySQL, do some viewing and the permissions are small delete users: Drop user ' username ' @ ' Host '; Host: can use IP, hostname, network address, wildcard _ : Indicates matching any single character%: matches any character of any length authorized user grant permissions 1, permissions 2, permissions ... on db_ Name.tab_name to ' username ' @ ' host ' [identified by ' passwd ']; grant all permissions: All privileges user does not exist automatically create view User rights: Show Grants for ' username ' @ ' host '; de-authorization: Revoke permissions 1, permissions 2,......on db_name.tab_name from ' username ' @ ' host ';
MySQL data types and SQL statements
